looping through functions

[NIMANIMA50]

assume that we have 10 user defined functions as below:

F1(X)=...
F2(X)=...
.
.
F10(X)=...

how can i use these functions in a loop as demonstrated below:

DIM ARR(1 TO 10)
For i=1 to 10
  ARR(i)=F&i(i)
next i

[georgiboy]

One method:

Sub test()    
    Dim ARR(1 To 4)
    Dim i As Long
    
    For i = 1 To 4
      ARR(i) = Evaluate("Func" & i & "(""Hi"")")
    Next i
    MsgBox Join(ARR, vbNewLine)
End Sub




Function Func1(str As String)
    Func1 = "F1: " & str
End Function
Function Func2(str As String)
    Func2 = "F2: " & str
End Function
Function Func3(str As String)
    Func3 = "F3: " & str
End Function
Function Func4(str As String)
    Func4 = "F4: " & str
End Function

[arnelgp]

why do you need 10 functions?
you can try using only one:

private sub test()
dim arr(1 to 10) 
dim i as integer
for i = 1 to 10
    arr(i) = funcX(i)
next


public function funcX(byval i As Integer) As Variant
    dim retVal As Variant
    ' if you have different calculation for each i, then
    ' you can use Select Case
    Select Case i
        Case is = 1
            retVal = 'the calculation here
        Case is = 2
            retVal = 'the calculation here
        Case is = 3
            retVal = 'the calculation here
        Case is = 4
            retVal = 'the calculation here
        Case is = 5
            retVal = 'the calculation here
        Case is = 6
            retVal = 'the calculation here
        Case is = 7
            retVal = 'the calculation here
        Case is = 8
            retVal = 'the calculation here
        Case is = 9
            retVal = 'the calculation here
        Case is = 10
            retVal = 'the calculation here
    End Case
    funcX = retVal
End Function

[snb]

Preferably use the builtin VBA methods; in this case: 'callbyname'

Sub test()
    Dim sn(3)
    
    For j = LBound(sn) To UBound(sn)
      sn(j) = CallByName(ActiveSheet, "Func" & j, 1, j)
    Next
    
    MsgBox Join(sn, vbLf)
End Sub

Function Func0(c00)
    Func0 = "F0: " & c00
End Function

Function Func1(c00)
    Func1 = "F1: " & c00
End Function

Function Func2(c00)
    Func2 = "F2: " & c00
End Function

Function Func3(c00)
    Func3 = "F3: " & c00
End Function

[NIMANIMA50]

why do you need 10 functions?
you can try using only one:

private sub test()
dim arr(1 to 10) 
dim i as integer
for i = 1 to 10
    arr(i) = funcX(i)
next


public function funcX(byval i As Integer) As Variant
    dim retVal As Variant
    ' if you have different calculation for each i, then
    ' you can use Select Case
    Select Case i
        Case is = 1
            retVal = 'the calculation here
        Case is = 2
            retVal = 'the calculation here
        Case is = 3
            retVal = 'the calculation here
        Case is = 4
            retVal = 'the calculation here
        Case is = 5
            retVal = 'the calculation here
        Case is = 6
            retVal = 'the calculation here
        Case is = 7
            retVal = 'the calculation here
        Case is = 8
            retVal = 'the calculation here
        Case is = 9
            retVal = 'the calculation here
        Case is = 10
            retVal = 'the calculation here
    End Case
    funcX = retVal
End Function

This is excatly what i was looking for. Works great. Thank you all for your input.

[snb]

No need for functions:

Sub M_snb()
   ReDim sn(6)
   
   For j = LBound(sn) To UBound(sn)
      sn(j) = Choose(j + 1, "aaa", j * 12, j ^ 6, 100 + j, 2 ^ 10, "Euro", Date - 2)
   Next
   
   MsgBox Join(sn, vbLf)
   
   For j = LBound(sn) To UBound(sn)
      sn(j) = Array("bbb", j * 12, j ^ 6, 100 + j, 2 ^ 10, "Euro", Date + 2)(j)
   Next
   
   MsgBox Join(sn, vbLf)
   
   For j = LBound(sn) To UBound(sn)
      Select Case j
      Case 0
         sn(j) = "ccc"
      Case 1
         sn(j) = j * 12
      Case 2
         sn(j) = j ^ 6
      Case 3
         sn(j) = 100 * j
      Case 4
         sn(j) = 2 ^ 10
      Case 5
         sn(j) = "Euro"
      Case 6
         sn(j) = Date + 10
      End Select
   Next
   
   MsgBox Join(sn, vbLf)
End Sub