Passing an array of Arguments to CallByName VBA

Question

I'm using callByName I VBA to dynamically call different methods of a class. Depending on the method, I will have a different number of arguments which will be held in an array. Unfortunately CallByName accepts a param array, therefore it's not straightforward to pass a variable number. Is there a way around this, I found a solution using the Type Information Library but this does not seem to work on VBA even though I have added it as a reference. Below is an illustration of what I want

 Public Sub Initialize_Object(ByRef TaskObject, Task_Collection)

 Dim Task_begin As Variant, Method_Parameters As Variant

 Task_begin = Task_Collection("Method")

 CallByName TaskObject, Task_begin, VbMethod, Method_Parameters

Did you read up on what a "param array" is? I'm unable to follow this statement of yours: "Unfortunately CallByName accepts a param array, therefore it's not straightforward to pass a variable number." — MikeC
– MikeC, Commented Mar 30, 2016 at 16:01
Hi Mike, apologies for the confusion. it is not straightforward to pass a variable number of arguments without knowing how many there are, essentially dynamically. Ideally what I need to do is to pass an array of my arguments. If I could get TypeLib information working on my computer then that would be doable but I don't have admin rights so can't register it — maracuja
– maracuja, Commented Mar 30, 2016 at 17:00
@MikeC I, did not write the routines, these are external and there will be a large variable number that are updated daily in a database which is then called by this vba program. By actually changing the method signature (as below) I was able to pass the array of arguments — maracuja
– maracuja, Commented Apr 1, 2016 at 13:16

Florent B. · Accepted Answer · 2016-03-31 11:36:49Z

10

You could use CallByName with an array as argument by changing the method signature :

#If VBA7 Or Win64 Then
  Private Declare PtrSafe Function rtcCallByName Lib "VBE7.DLL" ( _
    ByVal Object As Object, _
    ByVal ProcName As LongPtr, _
    ByVal CallType As VbCallType, _
    ByRef args() As Any, _
    Optional ByVal lcid As Long) As Variant
#Else
  Private Declare Function rtcCallByName Lib "VBE6.DLL" ( _
    ByVal Object As Object, _
    ByVal ProcName As Long, _
    ByVal CallType As VbCallType, _
    ByRef args() As Any, _
    Optional ByVal lcid As Long) As Variant
#End If

Public Function CallByName2(Object As Object, ProcName As String, args() As Variant)
   AssignResult CallByName2, rtcCallByName(Object, StrPtr(ProcName), VbMethod, args)
End Function

Private Sub AssignResult(target, result)
  If VBA.IsObject(result) Then Set target = result Else target = result
End Sub

Here is a usage example:

Sub UsageExample()
  Dim obj As Object, arguments()

  Dim obj As New Class1
  arguments = Array(1, 3)

  CallByName2 obj, "MyMethod", arguments
End Sub

edited Mar 31, 2016 at 11:36

answered Mar 30, 2016 at 18:12

Florent B.

42.7k7 gold badges92 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Comintern Over a year ago

Nice one. Just need to think outside the box.

SierraOscar Over a year ago

lcid looks like it should be a LongPtr in x64 to me.

Florent B. Over a year ago

@Macro Man, No the lcid is type U4, but I fixed the ProcName which should have been. Thx

maracuja Over a year ago

@Comintern I'm fairly new to VBA so haven't encountered declaring Ptrsafe functions before or changing the method signatures. Is this actually allowing me to change the method arguments within the dll file itself? Do you know where I can read up on this? my program also crashes when it goes to call rtcCallByName

Comintern Over a year ago

@maracuja - The calling convention for the dll function is the same, but it's specifying the marshalling signature that the compiler uses. VBA treats a ParamArray as distinct from a Variant(), but under the hood they are the same thing. The declare essentially bypasses the built in typedef and allow COM to decide if the calling convention is valid. As for your error, try changing ByVal ProcName As LongPtr to ByRef ProcName As String.

|

SierraOscar · Accepted Answer · 2016-03-30 16:25:15Z

1

You can't do this dynamically because different methods will require a different amount of arguments and you can't pass arguments where they aren't expected.

If you know the amount of arguments required, then you could call each item of the array and pass that:

CallByName TaskObject, Task_begin, VbMethod, Method_Parameters(0), Method_Parameters(1), Method_Parameters(2)

but you would probably have to set up a Select Case block or similar to handle all the different methods:

Select Case Method_Name
    Case "Method_1": CallByName TaskObject, Task_begin, VbMethod, Method_Parameters(0), Method_Parameters(1)
    Case "Method_2": CallByName TaskObject, Task_begin, VbMethod, Method_Parameters(0)
    Case "Method_3": CallByName TaskObject, Task_begin, VbMethod, Method_Parameters(0), Method_Parameters(1), Method_Parameters(2)
End Select

Which can get messy quite easily.

edited Mar 30, 2016 at 16:25

answered Mar 30, 2016 at 16:14

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

4 Comments

SierraOscar Over a year ago

That's actually wrong, and completely contradicts what you previously said. I suggest you read the MSDN article about the CallByName method in VBA where they use 2 arguments in the working example.

Florent B. Over a year ago

My bad it works, the signature is incorrect in the object explorer and documentation: msdn.microsoft.com/en-us/library/office/gg278760.aspx

Comintern Over a year ago

If Method_Parameters is an array, a Select Case UBound(Method_Parameters) would make much more sense. Switching on Method_Name defeats the purpose of using CallByName. You could just call the function directly...

SierraOscar Over a year ago

@Comintern It was more to demonstrate the point of having to account for the different amount of arguments but you're point is very valid so +1

Collectives™ on Stack Overflow

Passing an array of Arguments to CallByName VBA

2 Answers 2

9 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related