You can pass different variable types to the compiled VBA code, even static arrays.


Suppose you have the following VBA code in the VBA compiler:


Function TestMultipleParams(Param1, Param2, Param3)
  MsgBox( Param2[1] )    
  TestMultipleParams = Param3 ^ 2 
End Function


In the Excel VBA code, we can invoke it by passing the array.

Important: the array must be defined as Variant.

Sub MySubSample4()
Dim XLSPadlock As Object
Set XLSPadlock = Application.COMAddIns("GXLSForm.GXLSFormula").Object
Dim NomTableau(2) As Variant
NomTableau(0) = "a"
NomTableau(1) = "b"
NomTableau(2) = "c"
MsgBox XLSPadlock.PLEvalVBA3("TestMultipleParams", "Param1", NomTableau, 3)
Set XLSPadlock = Nothing
End Sub


Accessing Excel objects from compiled VBA code