Input argument in Public funtion

[evalent]

Hi,

I have an issue with the number of input argument allowed for public function. Apparently it only allows for 31 inputs for 1 public function.
Unfortunately, it is necessary for me to have more than 31 inputs.

The input is transferred from Matlab.

Anyone knows how to deal with it? Or how can I have more than 31 input for one module running?

Thanks in advance!

Elizabeth

[SamT]

Use an Array for input.

[evalent]

Hi SamT,

I tried to do that, but VBA doesnt seem to accept it.
I make an array in matlab and pass it to vba.

Can you help by explaining a bit more? Thanks!

[SamT]

Sub Demo)
Dim MyVar As Variant
MyVar = Array(var1, var2, var3)
Test(MyVar)
End Sub

Function Test(MyInput As Variant)
Dim X, Y, Z
X = MyVar(0)
Y = MyVar(1)
Z = MyVar(2)
End Function

It may be that a User Defined Type is better for you


Public Type typeMyVar
MyObject As Object
ConnectionString As String
EmpName As String
End Type

Sub Demo()
Dim MyVar As typeMyVar

With MyVar
Set .MyObject = Workbooks("MyBook").Sheets("Sheet1")
.ConnectionString = "Connect to Database1"
.EmpName = "Tom Jones"
End With

Test(MyVar)
End Sub

Function Test(Var1 As typeMyVar)
Dim MyConnection As String
Dim MyRangeName As String
Dim X, Y

MyRangeName =Join(Split(Var1.EmpName, " ")) = "TomJones"
X = Split(Var1.EmpName, " ") '=Array("Tom", "Jones")
Y = Join(X(1), X(0)) ' "JonesTom"

'TomJones is a named Range of a single Column
MyConnection = Var1.ConnectionString & Var1.MyObject.Range(MyRangeName).Cells(3).Offset(, 2)
End Function

[evalent]

Hi SamT,

thanks for helping! I will try it now.

[evalent]

Thanks SamT for giving information.

It is solved by using Split function.

What I learned: As split function works for string, so converting all the numbers from Matlab are necessary
After transferred to VBA, Split function was used. Afterwards, I converted back the string to Double.



Public Function ax(input As String) as Variant

Dim input_split() As String
Dim A, B As Double

input_split = Split(input, "%")
A = CDbl(Val(input_split(0)))
B = CDbl(Val(input_split(1)))

End Function


Thanks!