CODEWITHSUNDEEP
excelvba
Simon Streicher
Simon Streicher

Reputation: 2826

CallByName not working for all objects/functions

In VBA, most Excel functions are either accessible through Application.WorksheetFunction or VBA

Take sinh and sin for example:

Worksheet Function

Debug.Print Application.WorksheetFunction.sinh(5)
 74,2032105777888

VBA Function

Debug.Print VBA.sin(5)
-0,958924274663138

Question:

Why does CallByName not work on both Worksheet functions and VBA functions?

Worksheet Function

Debug.Print CallByName(Application.WorksheetFunction, "sinh", VbGet, 5)
 74,2032105777888

VBA Function

Debug.Print CallByName(VBA, "sin", VbGet, 5)

enter image description here

Upvotes: 0

Views: 770

Answers (2)

BigBen
BigBen

Reputation: 50162

The call signature of CallByName... which is actually a member of VBA.Interaction as seen below (so your snippet is equivalent to VBA.Interaction.CallByName(VBA, "sin", VbGet, 5) or just VBA.CallByName..., in any case a side point):

enter image description here

is

CallByName(Object As Object, ProcName As String, CallType As VbCallType, Args() As Variant)

As VBA is not an Object, but the standard VBA library, this throws a type mismatch error.

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

In VBA, most Excel functions are either accessible through Application.WorksheetFunction or VBA

No. Excel functions are accessible as late-bound member calls against the global Excel.Application object (if you're hosted in Excel), and then some have an early-bound "equivalent" (error handling strategy will need to differ) in the Excel.WorksheetObject interface (you get it from Application.WorksheetFunction indeed).

The members of the VBA library, global-scope or not, have nothing to do with Excel: the VBA standard library is referenced by every VBA project, regardless of its host application (Word, Excel, Access, ...SolidWorks, Sage300, etc.). If a function looks like it exists in both the VBA and the Excel libraries, the VBA function should probably/theoretically be preferred.

Use the object browser (F2) to discover the members of the VBA standard library, including and perhaps particularly its Math module.

Upvotes: 3

Related Questions