如何用For循环批量设置VBA非编号变量的值？

我想知道能不能不用给变量名加整数后缀的方式，通过For循环批量给大量VBA变量赋值。自己试了半天没找到办法，猜测只有像Variable1、Variable2这种带编号的变量，才能用Variable & I = ...的方式循环赋值。

我尝试的代码如下：

' 模块变量
Private ADwsLoadType As String
Private ADwsShipDate As Date
Private ADwsCustomer As String
Private ADwsBOL As String
Private ADwsLocation As String
Private ADwsSOproduct As String
Private ADwsSOsize As String
Private ADwsSOweight As Long
Private ADwsSOadjust As Long
Private ADwsScaleWeight As Long
Private ADwsLoadWeight As Long
Private ADwsProduct1 As String
Private ADwsProduct2 As String
Private ADwsProduct3 As String
Private ADwsProduct4 As String
Private ADwsQty1 As Long
Private ADwsQty2 As Long
Private ADwsQty3 As Long
Private ADwsQty4 As Long
Private ADwsSize1 As String
Private ADwsSize2 As String
Private ADwsSize3 As String
Private ADwsSize4 As String
Private ADwsLoadEst1 As Long
Private ADwsLoadEst2 As Long
Private ADwsLoadEst3 As Long
Private ADwsLoadEst4 As Long
Private ADwsLoadAct1 As Double
Private ADwsLoadAct2 As Double
Private ADwsLoadAct3 As Double
Private ADwsLoadAct4 As Double
Private ADwsToteAct1 As Double
Private ADwsToteAct2 As Double
Private ADwsToteAct3 As Double
Private ADwsToteAct4 As Double
Private ADwsTLproduct As String
Private ADwsTLweight As String
Private ADwsTL90 As String

Private Sub SetVariables()
  Call OperationCwal    ' 禁用计算与屏幕刷新
  Call ShowMeTheMoney   ' 设置公共变量值
  With ADws
'**************************************************************************************************
    ' 私有变量数组
    Dim NamesArr As Variant
    NamesArr = Array(ADwsLoadType, ADwsShipDate, ADwsCustomer, ADwsBOL, ADwsLocation, _
                    ADwsSOproduct, ADwsSOsize, ADwsSOweight, ADwsSOadjust, _
                    ADwsScaleWeight, ADwsLoadWeight, ADwsProduct1, ADwsProduct2, _
                    ADwsProduct3, ADwsProduct4, ADwsQty1, ADwsQty2, ADwsQty3, ADwsQty4, _
                    ADwsSize1, ADwsSize2, ADwsSize3, ADwsSize4, ADwsLoadEst1, _
                    ADwsLoadEst2, ADwsLoadEst3, ADwsLoadEst4, ADwsLoadAct1, ADwsLoadAct2, _
                    ADwsLoadAct3, ADwsLoadAct4, ADwsToteAct1, ADwsToteAct2, _
                    ADwsToteAct3, ADwsToteAct4, ADwsTLproduct, ADwsTLweight, ADwsTL90)
    
    ' 值数组
    Dim ValuesArr As Variant
    ValuesArr = Array(.[B7], .[B11], .[B15], .[B19], .[B23], .[F5], _
                      .[F6], .[J5], .[F6], .[F8], .[J8], .[E13], _
                      .[E15], .[E17], .[E19], .[F13], .[F15], .[F17], .[F19], _
                      .[G13], .[G15], .[G17], .[G19], .[I13], .[I15], .[I17], _
                      .[I19], .[J13], .[J15], .[J17], .[J19], .[K13], _
                      .[K15], .[K17], .[K19], .[E23], .[G23], .[J23])
                      
    For I = LBound(NamesArr) To UBound(NamesArr)
        If ValuesArr(I) = "" Then
            NamesArr(I) = ""
        Else
            NamesArr(I) = ValuesArr(I)
        End If
    Next I
    
    ' 立即窗口预期返回"TANK"
    Debug.Print ADwsLoadType    ' 实际返回值 = ""
    Debug.Print NamesArr(0)     ' 返回值 = "Tank"（注：数组下标从0开始）
    Debug.Print ValuesArr(0)    ' 返回值 = "Tank"

    ' 值已写入数组，但未同步到原模块变量
'**************************************************************************************************
 End With
 Call BlackSheepWall    ' 启用计算与屏幕刷新
End Sub

原代码失效原因 #

VBA数组存储的是值的副本，而非变量的引用。当你把模块变量放进NamesArr时，数组里只是复制了变量当时的数值，后续修改数组元素只会改变数组内部数据，不会同步到原模块变量，这就是ADwsLoadType始终为空的核心原因。

两种可行解决方案 #

方案1：用自定义类型（Type）整合变量 #

把相关变量封装到自定义类型里，既方便统一管理，也能通过循环批量处理重复模式的变量：

' 模块顶部定义自定义类型
Private Type ADwsData
    LoadType As String
    ShipDate As Date
    Customer As String
    BOL As String
    Location As String
    SOproduct As String
    SOsize As String
    SOweight As Long
    SOadjust As Long
    ScaleWeight As Long
    LoadWeight As Long
    Product(1 To 4) As String
    Qty(1 To 4) As Long
    Size(1 To 4) As String
    LoadEst(1 To 4) As Long
    LoadAct(1 To 4) As Double
    ToteAct(1 To 4) As Double
    TLproduct As String
    TLweight As String
    TL90 As String
End Type

' 定义类型实例
Private ADwsVars As ADwsData

Private Sub SetVariables()
    Call OperationCwal    ' 禁用计算与屏幕刷新
    Call ShowMeTheMoney   ' 设置公共变量值
    
    With ADws
        ' 赋值非批量成员
        ADwsVars.LoadType = IIf(.[B7] = "", "", .[B7])
        ADwsVars.ShipDate = IIf(IsEmpty(.[B11]), 0, .[B11])
        ADwsVars.Customer = IIf(.[B15] = "", "", .[B15])
        ADwsVars.BOL = IIf(.[B19] = "", "", .[B19])
        ADwsVars.Location = IIf(.[B23] = "", "", .[B23])
        ADwsVars.SOproduct = IIf(.[F5] = "", "", .[F5])
        ADwsVars.SOsize = IIf(.[F6] = "", "", .[F6])
        ADwsVars.SOweight = IIf(.[J5] = "", 0, .[J5])
        ADwsVars.SOadjust = IIf(.[F6] = "", 0, .[F6])
        ADwsVars.ScaleWeight = IIf(.[F8] = "", 0, .[F8])
        ADwsVars.LoadWeight = IIf(.[J8] = "", 0, .[J8])
        ADwsVars.TLproduct = IIf(.[E23] = "", "", .[E23])
        ADwsVars.TLweight = IIf(.[G23] = "", "", .[G23])
        ADwsVars.TL90 = IIf(.[J23] = "", "", .[J23])
        
        ' 循环批量处理带编号的成员
        Dim i As Integer
        For i = 1 To 4
            ADwsVars.Product(i) = IIf(.[E12 + i] = "", "", .[E12 + i])
            ADwsVars.Qty(i) = IIf(.[F12 + i] = "", 0, .[F12 + i])
            ADwsVars.Size(i) = IIf(.[G12 + i] = "", "", .[G12 + i])
            ADwsVars.LoadEst(i) = IIf(.[I12 + i] = "", 0, .[I12 + i])
            ADwsVars.LoadAct(i) = IIf(.[J12 + i] = "", 0, .[J12 + i])
            ADwsVars.ToteAct(i) = IIf(.[K12 + i] = "", 0, .[K12 + i])
        Next i
    End With
    
    ' 测试输出，现在能返回正确值
    Debug.Print ADwsVars.LoadType
    
    Call BlackSheepWall    ' 启用计算与屏幕刷新
End Sub

方案2：用类模块+CallByName函数 #

如果想保留原变量名的命名方式，可把变量放到类模块中，通过CallByName函数按名字字符串批量赋值：

1. 新建类模块，命名为clsADwsData，在类中定义变量：

Public LoadType As String
Public ShipDate As Date
Public Customer As String
Public BOL As String
Public Location As String
Public SOproduct As String
Public SOsize As String
Public SOweight As Long
Public SOadjust As Long
Public ScaleWeight As Long
Public LoadWeight As Long
Public Product1 As String
Public Product2 As String
Public Product3 As String
Public Product4 As String
Public Qty1 As Long
Public Qty2 As Long
Public Qty3 As Long
Public Qty4 As Long
Public Size1 As String
Public Size2 As String
Public Size3 As String
Public Size4 As String
Public LoadEst1 As Long
Public LoadEst2 As Long
Public LoadEst3 As Long
Public LoadEst4 As Long
Public LoadAct1 As Double
Public LoadAct2 As Double
Public LoadAct3 As Double
Public LoadAct4 As Double
Public ToteAct1 As Double
Public ToteAct2 As Double
Public ToteAct3 As Double
Public ToteAct4 As Double
Public TLproduct As String
Public TLweight As String
Public TL90 As String

2. 在标准模块中调用：

Private ADwsVars As clsADwsData

Private Sub SetVariables()
    Set ADwsVars = New clsADwsData
    
    Call OperationCwal
    Call ShowMeTheMoney
    
    With ADws
        ' 定义变量名和对应单元格的映射数组
        Dim varNames As Variant
        Dim cellRefs As Variant
        
        varNames = Array("LoadType", "ShipDate", "Customer", "BOL", "Location", _
                        "SOproduct", "SOsize", "SOweight", "SOadjust", _
                        "ScaleWeight", "LoadWeight", "Product1", "Product2", _
                        "Product3", "Product4", "Qty1", "Qty2", "Qty3", "Qty4", _
                        "Size1", "Size2", "Size3", "Size4", "LoadEst1", _
                        "LoadEst2", "LoadEst3", "LoadEst4", "LoadAct1", "LoadAct2", _
                        "LoadAct3", "LoadAct4", "ToteAct1", "ToteAct2", _
                        "ToteAct3", "ToteAct4", "TLproduct", "TLweight", "TL90")
        
        cellRefs = Array(.[B7], .[B11], .[B15], .[B19], .[B23], .[F5], _
                        .[F6], .[J5], .[F6], .[F8], .[J8], .[E13], _
                        .[E15], .[E17], .[E19], .[F13], .[F15], .[F17], .[F19], _
                        .[G13], .[G15], .[G17], .[G19], .[I13], .[I15], .[I17], _
                        .[I19], .[J13], .[J15], .[J17], .[J19], .[K13], _
                        .[K15], .[K17], .[K19], .[E23], .[G23], .[J23])
        
        Dim i As Integer
        For i = LBound(varNames) To UBound(varNames)
            Dim cellVal As Variant
            cellVal = cellRefs(i)
            
            If IsEmpty(cellVal) Or cellVal = "" Then
                CallByName ADwsVars, varNames(i), vbLet, Empty
            Else
                CallByName ADwsVars, varNames(i), vbLet, cellVal
            End If
        Next i
    End With
    
    Debug.Print ADwsVars.LoadType ' 正确返回目标值
    Call BlackSheepWall
End Sub

内容的提问来源于stack exchange，提问作者Quincy Starcruiser