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, Mar 30 '16 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, Mar 30 '16 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, Apr 01 '16 at 13:16

Florent B. · Answer 1 · 2016-03-31T11:36:49.240

8

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 '16 at 11:36

answered Mar 30 '16 at 18:12

Florent B.

41,537
7
86
101

Nice one. Just need to think outside the box. – Comintern Mar 30 '16 at 18:25
`lcid` looks like it should be a `LongPtr` in x64 to me. – SierraOscar Mar 31 '16 at 11:31
@Macro Man, No the lcid is type U4, but I fixed the ProcName which should have been. Thx – Florent B. Mar 31 '16 at 11:44
@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 – maracuja Mar 31 '16 at 11:59
1

@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`. – Comintern Mar 31 '16 at 12:27
@maracuja, the VBA Declare statement provides a way to directly call a function in a dll. In this case, it doesn't alter the function, but provides a way to change the arguments type. Regarding the crash, is it after or before I updated the answer and which version of Excel do you have? is it 32 bits or 64bits? mac or windows? – Florent B. Mar 31 '16 at 12:28
@florentB , I have it working now, I was passing the array incorrectly, so I'm passing ProcName As LongPtr now. Thank you for your help! – maracuja Apr 01 '16 at 13:15
@FlorentB, I have run into a new problem with the above. When I call methods where the expected output/result is the object passed back with one or more properties changed this works perfectly. However I am now calling a method where I pass three arguments ByRef that should be passed back populated with values of double type. For some reason the method is called but the arguments are passed back unpopulated. Any idea? – maracuja May 17 '16 at 11:46

SierraOscar · Answer 2 · 2016-03-30T16:25:15.967

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 '16 at 16:25

answered Mar 30 '16 at 16:14

SierraOscar

17,507
6
40
68

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. – SierraOscar Mar 30 '16 at 16:34
My bad it works, the signature is incorrect in the object explorer and documentation: https://msdn.microsoft.com/en-us/library/office/gg278760.aspx – Florent B. Mar 30 '16 at 16:43
1

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... – Comintern Mar 30 '16 at 17:50
@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 – SierraOscar Mar 30 '16 at 17:53

Passing an array of Arguments to CallByName VBA

2 Answers2

Linked