如何在循环中根据整数调用模块中声明的常量？

Hey there, let's sort out this VBA issue you're facing! The problem here is that VBA doesn't allow you to dynamically reference constant names by string concatenation (like strFolderA & i). Constants are resolved at compile time, so runtime tricks to build their names won't work. Here are two straightforward solutions:

Solution 1: Use an Array to Store Folder Paths #

This is the most flexible and maintainable approach. Instead of declaring separate constants, store all your folder paths in an array (you can still keep the individual constants if you want, or just populate the array directly):

' Keep your existing constants (optional)
Public Const strFolderA1 = "C:\ABCD\One"
Public Const strFolderA2 = "C:\ABCD\two"

' Declare an array to hold the paths (initialize this somewhere like Workbook_Open)
Public arrFolderPaths As Variant

' Example initialization (put this in a sub or workbook event)
Sub InitializeFolders()
    arrFolderPaths = Array(strFolderA1, strFolderA2)
End Sub

' Your revised loop code
Sub LoopThroughFolders()
    Dim i As Integer
    Dim strFile As String
    Dim filenm As String
    filenm = "your_target_file.ext" ' Replace with your actual filename
    
    ' Call initialization if you haven't already
    InitializeFolders
    
    For i = LBound(arrFolderPaths) To UBound(arrFolderPaths)
        strFile = Dir(arrFolderPaths(i) & "\" & filenm)
        
        ' Process found files (loop to get all matches)
        Do While strFile <> ""
            Debug.Print "Found: " & arrFolderPaths(i) & "\" & strFile
            strFile = Dir ' Get next matching file
        Loop
    Next i
End Sub

Why this works: #

• Arrays let you index directly into your list of paths using the loop variable i.
• Adding new folders later just requires updating the array (no need to rewrite loop logic).

Solution 2: Map Loop Variable to Constants with Select Case #

If you need to keep the individual constants and don't want to use an array, use a Select Case block to map each loop number to the corresponding constant:

' Your existing constants
Public Const strFolderA1 = "C:\ABCD\One"
Public Const strFolderA2 = "C:\ABCD\two"

Sub LoopThroughFoldersWithSelect()
    Dim i As Integer
    Dim strFolder As String
    Dim strFile As String
    Dim filenm As String
    filenm = "your_target_file.ext"
    
    ' Note: Only loop up to 2 since you have 2 constants (your original loop to 3 would hit an invalid case)
    For i = 1 To 2
        Select Case i
            Case 1: strFolder = strFolderA1
            Case 2: strFolder = strFolderA2
            ' Add new Case entries here if you add more constants later
            Case Else: strFolder = "" ' Handle invalid loop numbers
        End Select
        
        If strFolder <> "" Then
            strFile = Dir(strFolder & "\" & filenm)
            
            Do While strFile <> ""
                Debug.Print "Found: " & strFolder & "\" & strFile
                strFile = Dir
            Loop
        End If
    Next i
End Sub

Why this works: #

• It explicitly maps each loop iteration to the constant you want, avoiding the dynamic name resolution issue.
• Good for small, fixed sets of constants where you don't expect frequent changes.

Quick Note on Your Original Loop #

Your original loop runs from 1 to 3, but you only have 2 constants defined. Make sure to adjust the loop upper bound to match the number of paths you have (otherwise you'll hit empty/invalid folder paths).

内容的提问来源于stack exchange，提问作者Ko Nayaki