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
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
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
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
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
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