如何在Excel VBA中遍历所有命名区域并批量设置同名变量？

当然可以优化你的重复代码，不过VBA无法直接动态创建同名的公共变量，这里提供两种实用的替代方案：

方案1：用字典存储（推荐） #

无需提前声明大量变量，直接把所有目标命名区域的名称和对应值存入字典，调用时通过名称索引即可取值，简洁易维护。

代码示例 #

' 模块顶部可选引用「Microsoft Scripting Runtime」（也可改用CreateObject创建字典）
Public InputsDict As Dictionary

Sub LoadAllNamedRangeValues()
    Dim nm As Name
    Set InputsDict = New Dictionary
    
    ' 遍历工作簿中属于「CS_INPUTS」工作表的命名区域
    For Each nm In ThisWorkbook.Names
        If nm.Parent.Name = "CS_INPUTS" Then
            InputsDict(nm.Name) = nm.RefersToRange.Value
        End If
    Next nm
End Sub

使用方式 #

后续需要取值时直接调用字典：

' 示例：获取Development的值
MsgBox InputsDict("Development")

方案2：自动生成赋值代码（一次性消除重复） #

如果必须保留独立的公共变量，可以用以下代码自动生成所有变量声明和赋值语句，直接复制到你的模块即可，不用手动敲100行。

代码示例 #

Sub GenerateAssignmentCode()
    Dim nm As Name
    Dim codeOutput As String
    
    ' 生成公共变量声明行
    codeOutput = "Public "
    For Each nm In ThisWorkbook.Names
        If nm.Parent.Name = "CS_INPUTS" Then
            codeOutput = codeOutput & nm.Name & ", "
        End If
    Next nm
    ' 移除末尾多余的逗号和空格
    codeOutput = Left(codeOutput, Len(codeOutput) - 2) & vbCrLf & vbCrLf
    
    ' 生成With块内的赋值代码
    codeOutput = codeOutput & "    With Worksheets(""CS_INPUTS"")" & vbCrLf
    For Each nm In ThisWorkbook.Names
        If nm.Parent.Name = "CS_INPUTS" Then
            codeOutput = codeOutput & "        " & nm.Name & " = .Range(""" & nm.Name & """).Value" & vbCrLf
        End If
    Next nm
    codeOutput = codeOutput & "    End With"
    
    ' 代码输出到立即窗口（按Ctrl+G打开）
    Debug.Print codeOutput
End Sub

使用方式 #

运行该宏后，打开立即窗口（Ctrl+G）即可看到自动生成的完整代码，复制粘贴到你的模块中即可。

内容的提问来源于stack exchange，提问作者Ryan MacDicken