Call constant using literals in runtime

Question

I have declared constants like the below in the module,

Public Const strFolderA1 = "C:\ABCD\One"
Public Const strFolderA2 = "C:\ABCD\two"

I am trying to call this in a loop,

For i = 1 To 3
strFile = Dir(strFolderA & i & "\" & filenm)

Loop

The above Dir code is wrong, but I want to call the constant based on the looping integer. Could someone help? Please let me know if the question is not clear.

That method of appending a suffix to a var name actually works with [tag:vb-script] (or maybe it was asp-classic) but not in [tag:vba]. — , Mar 26 '18 at 01:28
See this discussion: https://stackoverflow.com/questions/38254337/how-to-create-dynamic-variable-names-vba — shrivallabha.redij, Mar 26 '18 at 07:42

score 3 · Accepted Answer · answered Mar 26 '18 at 01:38

VBA does not provide any method for concatenating a string to be used as a dynamic variable name. You could create a string constant with a delimiter then split it before use.

Option Explicit
Public Const strFolderA As String = "C:\ABCD\One|C:\ABCD\Two|C:\ABCD\Three"

Sub abcs()
    Dim i As Long, fldrs As Variant

    fldrs = Split(strFolderA, "|")
    For i = LBound(fldrs) To UBound(fldrs)
        Debug.Print fldrs(i)
    Next i
End Sub

@Gary'sStudent well the idea is that Jeeped is clever :) – L42 Mar 26 '18 at 01:50 — L42, Mar 26 '18 at 01:50

Call constant using literals in runtime

1 Answers1