如何从声明变量的级联中获得值？

Question

我声明了一些变量：

Dim SRC_A3 As Variant: SRC_A3 = Worksheets("SOURCES").Range("A3") ' Get filename
Dim MergeKey1 As Variant: MergeKey1 = Right(SRC_A3, 36) ' Trim the cst key out of this filename
Dim SRC_A4 As Variant: SRC_A4 = Worksheets("SOURCES").Range("A4") ' Get filename
Dim MergeKey2 ... 

稍后，我需要通过连接创建变量来获得这些变量的值：

For MKey = 2 To 6
    Dim MKeyNo As Variant: MKeyNo = "MergeKey" & (MKey - 1)
    Dim MGK As As Variant: MGK = MKeyNo
    MsgBox "MKey:" & MKey & " + " & "MKeyNo:" & MKeyNo & " + " & "MGK:" & MGK
        If IsEmpty(MGK) = False _
        Then
        Dim UnDelete1 As Range: Set UnDelete1 = Worksheets("SQL_UPDATES").Range("$B$" & MKey)
        Dim UnDelete2 As Range: Set UnDelete2 = Worksheets("SQL_UPDATES").Range("$B$" & (MKey + 1))
        Dim UnDelCode1 As String
            UnDelCode1 = "USE [nfAPCO] UPDATE co_individual SET ind_delete_flag = 0 WHERE ind_cst_key = '" & MGK & "'"
        Dim UnDelCode2 As String
            UnDelCode2 = "USE [nfAPCO] UPDATE co_customer SET cst_delete_flag = 0 WHERE cst_key = '" & MGK & "'"
        UnDelete1.Value = UnDelCode1
        UnDelete2.Value = UnDelCode2
        End If
    MKC = MKey + 1
    Cells(MKC, 1).Activate
Next

但是，级联会创建一个字符串，并且不会返回到声明的变量(例如"MergeKey1")，从而生成一个值(或没有值)。这使得IsEmpty(MGK)总是错误的，并且在我的其他连接中不会产生值。消息框显示"MKey:2 + MKeyNo:MergeKey1 + MKG:MergeKey1"，但我希望它生成“值”"MergeKey1“等。如果我能用MKeyNo做这件事，而忘记MGK，那就好了。

任何帮助都是值得感激的！

Answer 1

这在VBA中是不可能的--看看如何使用数组。

下面是一个简单的示例，它用A1:A6的值填充数组MergeKeys。

Dim MergeKeys(1 To 6) 
Dim idx As Long

    For idx = 1 To 6
        MergeKey(idx) = Range("A" & idx).Value
    Next idx