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