Passing arrays to the compiled VBA code

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