arrays - Excel VBA - 将公式结果分配给数组

Question

这可以将值放在列中:

Sub JR_ArrayToDebugPint2()  
     ' written by Jack in the UK for [url]www.OzGrid.com[/url]  
     ' our web site [url]www.excel-it.com[/url]  
     ' Excel Xp+ 14th Aug 2004  
     ' [url]http://www.ozgrid.com/forum/showthread.php?t=38111[/url]  
    Dim JR_Values(500)  
    Dim JR_Count As Integer  
    Dim R As Long  
    R = 2  
    For JR_Count = 1 To 500 Step 1  
        JR_Values(JR_Count) = Evaluate("=INDEX('Client'!$O$2:$O$347473,MATCH(1,(('Client_Cost'!$D$2:$D$347473=BC" & CStr(R) & ")*('Client_Cost'!$E$2:$E$347473=BE" & CStr(R) & ")),0))")  
        Sheet1.Range("BG" & CStr(R) & "").Value = JR_Values(JR_Count)  
        R = R + 1  
        'Debug.Print JR_Values(JR_Count)  
        Next JR_Count  
End Sub

我修改了在 mrexcel.com 上找到的原始代码

无论是 Debug.Print 还是打印到工作表，我都会得到正确的值列表。所以在我看来，我应该能够在计算值时将它们放入一个数组中，然后使用 Range("BG2:BG500").Value = Application.Transpose(myarray)。

我假设如果我这样做，值将一次全部放在列中的单元格中，而不是一次一个，这就是这段代码和我尝试过的所有其他代码正在做的事情。我还假设，如果将值一次全部放入列中的单元格中，则比一次将值放入单元格中要快得多。

一旦计算公式，我无法做的是获取代码以将值放入数组中。我尝试了以下变体但没有成功 - 设置数组并让数组获取计算值的语句大写并由 ==> 标记。我得到的最常见的错误是类型不匹配。

Sub JR_ArrayToDebugPint2()  
    Dim JR_Values(500)  
    Dim JR_Count As Integer  
    Dim R As Long  
 ==>   DIM arrPRICE(0 TO 500) AS VARIANT  
    R = 2  
    For JR_Count = 1 To 500 Step 1  
        JR_Values(JR_Count) = Evaluate("=INDEX('Client'!$O$2:$O$347473,MATCH(1,(('Client_Cost'!$D$2:$D$347473=BC" & CStr(R) & ")*('Client_Cost'!$E$2:$E$347473=BE" & CStr(R) & ")),0))")  
     ==>   arrPRICE(R) = JR_VALUES(JR_COUNT)  
        R = R + 1  
        'Debug.Print JR_Values(JR_Count)  
    Next JR_Count  
End Sub  

Answer 1

当你像 Dim JR_Values(500) 这样的变量数组维度时您正在创建一个基于从零开始的索引的一维数组。数组中的第一个元素是 JR_Values(0)最后一个是 JR_Values(500)总共 501 个数组元素。虽然您可以通过一些数学计算从 0 到 499，但您也可以通过以这种方式声明变量数组来强制对变量数组进行从 1 开始的索引。

使用 Application Evaluate 时，假设的工作表父级的 BC 和 BE 列(单个行数据标准来自)不是确定的。就像在工作表上使用相同的公式一样。工作表知道它是谁； VBA 可能知道也可能不知道您所暗示的工作表。

Sub JR_ArrayToDebugPint2()
    Dim olr As Long, rws As Long, JR_Count As Long, JR_Values As Variant

    'get some dimensions to the various data ranges
    With Worksheets("Client_Cost")
        'only use as many rows as absolutely necessary
        olr = Application.Min(.Cells(Rows.Count, "C").End(xlUp).Row, _
                              .Cells(Rows.Count, "E").End(xlUp).Row)
    End With
    With Worksheets("Client")
        rws = Application.Min(.Cells(Rows.Count, "BC").End(xlUp).Row, _
                              .Cells(Rows.Count, "BE").End(xlUp).Row)
        'override the above statement unless you want to run this overnight
        rws = 500
    End With

    ReDim JR_Values(1 To rws)   'force a one-based index on the array
    'Debug.Print LBound(JR_Values) & ":" & UBound(JR_Values)

    For JR_Count = LBound(JR_Values) To UBound(JR_Values) Step 1
        'Debug.Print Evaluate("INDEX('Client'!O2:O" & olr & _
                 ", MATCH(1, (('Client_Cost'!D2:D" & olr & "='Client'!BC" & JR_Count+1 & ")" & _
                            "*('Client_Cost'!E2:E" & olr & "='Client'!BE" & JR_Count+1 & ")), 0))")

        'R would be equal to JR_Count + 1 if R was still used (starts as R = 2)
        JR_Values(JR_Count) = _
          Evaluate("INDEX('Client'!O2:O" & olr & _
                 ", MATCH(1, (('Client_Cost'!D2:D" & olr & "='Client'!BC" & JR_Count + 1 & ")" & _
                            "*('Client_Cost'!E2:E" & olr & "='Client'!BE" & JR_Count + 1 & ")), 0))")
        'Debug.Print JR_Values(JR_Count)
    Next JR_Count

    With Worksheets("Client")
        .Range("BG2").Resize(UBound(JR_Values), 1) = Application.Transpose(JR_Values)
    End With

End Sub

我留下了很多评论供您查看并随后清理。我最近在 How to assign the variable length of an array to integer variable 中写了一篇声明一维和二维变体数组的叙述。 .