Loop through Variables

[Brad]

Thanks for taking the time to read my question.

I'd like to loop through my variables. My variable names are F1, F2, F3 and
so on.

How can I make a loop to check the value of each variable? I tried
Control() but I think that's only for forms. I can't remember, or find, what
it is for functions.

Thanks,

Code:
Function FindValue(F1 As Variant, Optional F2 As Variant, Optional F3 As
Variant, Optional F4 As Variant, Optional F5 As Variant, Optional F6 As
Variant, Optional F7 As Variant, Optional F8 As Variant, Optional F9 As
Variant, Optional F10 As Variant, Optional F11 As Variant, Optional F12 As
Variant, Optional F13 As Variant, Optional F14 As Variant, Optional F15 As
Variant, Optional F16 As Variant, Optional F17 As Variant, Optional F18 As
Variant) As Integer
Dim x As Integer

Debug.Print F & x
Do Until x = 18
If Not IsNull(F & x) Then
FindValue = F1
Control
Exit Do
End If

x = x + 1
Loop


End Function

Brad

 

[Daniel]

Try something like

Dim lngLoop As Long
For lngLoop = 1 To 18
Debug.Print Me.Controls("F" & lngLoop).Value
Next lngLoop

 

[Brad]

Thanks for the reply Daniel. I believe that "Me." is reserved for objects or
something like that. At any rate, it applies to forms and reports. I gave
it a shot anyway, but it told me that it was an improper use of "Me."

Thanks for the suggestion.

Brad

 

[Douglas J. Steele]

What are you trying to do? What you've got returns F1 regardless which
parameter is non-null: is that what you really want, or do you want to
return the position of the first non-null parameter? Assuming the latter,
try:

Function FindValue(ParamArray F()) As Integer
Dim x As Integer

Do Until x = UBound(F)
If Not IsNull(F(x)) Then
FindValue = x
Exit Do
End If
x = x + 1
Loop

End Function

You'd call it the same way as your existing function.

 

[George Nicholson]

You could add your variables to a Collection object, and then retrieve
values either by index position or name. Retrieval via a loop then becomes
as easy as For Each... Next. Collection objects can hold any data type, and
those data types do not have to match.

copied from the Immediate window:
********
f18 = "test"
set colVariables = new Collection

'Add f18 variable to collection
'use variable's name as string-key
colvariables.add f18, "F18"

?colvariables(1)
test
?colvariables("F18")
test
*************

Con: Unless you are storing objects in the collection, you can't retrieve
the item name "f18", just its value, so you might have to be careful about
adding variables in "proper" order to get coherent loop results.
Alternative: the Scripting.Dictionary object, which I am less familiar with,
but I believe is similar but a little more powerful.

HTH,