Loop through variables

[mikke3141]

How could I get this type of function to loop through all the variables names


Function test(Supplier As String, Optional Cost1 As String, _
Optional Cost2 As String, Optional Cost3 As String, Optional Cost4 As String, _
Optional Cost5 As String, Optional Cost6 As String, Optional Cost7 As String)

For loopper = 1 To 7
If Not IsMissing(Cost & loopper) Then
test = test + (Cost & loopper)
End If
Next

End Function
Thank you for your help.

[mdmackillop]

Pass the costs as an array and use a For Each loop

[mikke3141]

How can I do it when some costs are numbers and some are references to cells in different sheets?

[mdmackillop]

Your code says you are passing strings. Can you post a sample workbook to calify what you are proposing?

[Bob Phillips]

How can I do it when some costs are numbers and some are references to cells in different sheets?

Surely those cells in different sheets would still resolve to numbers.

[Paul_Hossler]

Depending on how you're using it, maybe ParamArray would work

There is an assumed dependance on the order that may or may not be an issue, but it's someting to look at


Option Explicit
Dim sVar As String

Sub drv()
MsgBox test("AAAA", 1, 2, 3, 4)

MsgBox test("BBBB", 1, 2, , 4, Worksheets("sheet1").Range("A1").Value)

MsgBox test("CCCC", 1, 2, 3, 4, "ZZZZ", 100)

End Sub

Function test(Supplier As String, ParamArray Parms()) As Double
Dim i As Long

For i = LBound(Parms) To UBound(Parms)
If IsNumeric(Parms(i)) Then
test = test + Parms(i)
Else
'if not a number
sVar = CStr(Parms(i))
End If
Next
End Function


Paul