Excel VBA constant as argument

Question

Sorry in advance, I'm a self-taught VBA programmer, and I'm not sure how to phrase my question! I've declared constants which have similar names, i.e.

Public Const BP1Enable = "something1something:someotherthing1someotherthing"
public const BP2Enable = "something2something:someotherthing2someotherthing"

etc.

I have 10 of these constants. I have a sub with these constants as arguments:

Sub FieldEnable (ByVal enableconst)

Now I want to call the FieldEnable sub in a loop using i as counter:

For i = 1 To 10

 BPfieldname = "BP" & i & "Enable"
 FieldEnable enableconst:=BPfieldname
Next i

This does not work, what is happening is that the "value" assigned to enableconst in the sub FieldEnable, is "BP1Enable" instead of the value of the constant BP1Enable namely "something1something:someotherthing1someotherthing".

How can I use the variable BPfieldname when calling the sub FieldEnable?

I hope this makes sense. Any help appreciated.

Answer 1

Also, if you are going to be using this "constant" in more than one place, more than one function, you can effectively make an array constant, that can even be called that way.

Public Sub Test()
    For i = LBound(BPEnable) To UBound(BPEnable)
        Debug.Print BPEnable(i)
    Next i
End Sub

Public Function BPEnable() As Variant
    BPEnable = Array("One", "Two", "Three", "Pi", "Four")
End Function

Immediate Window:

One
Two
Three
Pi
Four

Answer 2

Transform your variables into a single Array.

See this http://msdn.microsoft.com/en-us/library/wak0wfyt.aspx

EDIT: as @sina has correctly pointed out, VBA does not allow constant arrays,

so instead of trying this

Dim BPEnable = {
  "something1something:someotherthing1someotherthing",
  "something2something:someotherthing2someotherthing",
  ....
}

you should try this

Dim BPEnable
BPEnable = Array( _
  "something1something:someotherthing1someotherthing", _
 "something2something:someotherthing2someotherthing", _
 "..."
)


For i = 0 To UBound(BPEnable)
 BPfieldname = BPEnable(i)
Next i

Answer 3

The best guess would be to use a constant array, but constant arrays are not supported by VBA.

Therefore you could go with building an array out of your constants before you start your loop:

Dim BPEnable
BPEnable = Array(BP1Enable, BP2Enable)
For i = 0 To UBound(BPEnable)
 FieldEnable enableconst:=BPEnable(i)
Next i

Another option would be to declare all constants as one long string with some defined separator and use a split function on that string to generate the array.