String Arrays and Function Calls

[Bill_Gates]

I have some very important code that I need to write myself but am relatively new to VBA.

I need to be able to:

1.) Obtain the field names of a query
2.) Place those field names in a string array
3.) Use the string array in looped a function call

I found some good tutorials on the first two, however I cannot find anything on the last. I know it is possible to use variables of a string array in a function call in the C languages, but is it in VBA and if so, how can I do it?

Thanks,

--Bill

 

[ChrisO]

Sub Test()
    Dim Procedures(3) As String
     
    Procedures(0) = "Sam"
    Procedures(1) = "Tom"
    Procedures(2) = "Mary"
    Procedures(3) = "Jill"
    
    ' Call as Function
    MsgBox Eval(Procedures(0) & "()")
    MsgBox Eval(Procedures(1) & "()")
    
    ' Call as Subroutine
    Application.Run Procedures(2)
    Application.Run Procedures(3)

End Sub


Public Function Sam() As String

    Sam = "Hi from Sam"

End Function


Public Function Tom() As String

    Tom = "Hi from Tom"

End Function


Public Sub Mary()

    MsgBox "Hi from Mary"
    
End Sub


Public Sub Jill()

    MsgBox "Hi from Jill"
    
End Sub

 

[Bill_Gates]

Okay that does teach me some things about how to use variable strings.

Just to be clearer, I kind of wanted something like this:

Strings1(0) = "C"
Strings1(1) = "C#"
Strings1(2) = "F++"

Strings2(0) = "Table 1"
Strings2(1) = "Table 2"

For i=0, i<2, i++
{
For j=0, j<3, j++
{
Function("[Strings1(j)]", "[Strings2(i)]")
}
}

 

[ChrisO]

I’m not at all sure what exactly is required here but this seems to be the closest I can make of the C code: -

Sub Test()
    Dim Strings1(2) As String
    Dim Strings2(1) As String
    Dim i As Integer
    Dim j As Integer
    
    Strings1(0) = "C"
    Strings1(1) = "C#"
    Strings1(2) = "F++"
    
    Strings2(0) = "Table 1"
    Strings2(1) = "Table 2"
    
    For i = 0 To 1
        For j = 0 To 2
            MsgBox MyFunctionName(Strings1(j), Strings2(i))
        Next j
    Next i
    
End Sub


Function MyFunctionName(strIn1 As String, strIn2 As String) As String

    MyFunctionName = strIn1 & "   " & strIn2
    
End Function

Hope that helps.

Chris.

 

[ChrisO]

Maybe: -

MsgBox DLookup("[" & Strings1(j) & "]", "[" & Strings2(i) & "]")

Chris.

 

[Bill_Gates]

Thank you Chris. Your method worked just fine and helped me to learn more about VBA.