我在VBA
工作了很长时间,我对它有很丰富的经验。然而,有时,他的异想天开仍然会让我大吃一惊。。。为了让我的所有同事都能访问到一个文件,我选择在脚本上进行每一次计算。为此,我用一些double
数据填充数组:
Dim DT01() As Double: ReDim DT01(1 To N03) As Double
Dim R As Long: R = 1: Dim N As Long: Dim P As Double
For x = 1 To N01
N = SH01.Cells(x + 4, 15).Value: P = SH01.Cells(x + 4, 14).Value
For y = 1 To N: DT01(R) = P: R = R + 1: Next y
Next x
DT01
数组很长(N03 = 495258
(,前面的例程按照我的意图填充数组。我甚至制作了以下内容:
For x = 1 To 495258
SH01.cells(x, 1).FormulaR1C1 = DT01(x)
Next x
以检查我是否在填充数组的脚本中出错。这段代码可以用数组的正确值写入工作表SH01
的第一列。
如果我计算列的和,我得到了292547224.4
,这是正确的值。然而,如果我在VBA
上使用Application.Sum(DT01)
,我得到了1535172.8
。当我看到这一点时,我试图计算其他东西,结果总是不同的:
'On Excel:
=AVERAGE(A1:A495258) = 598,6
'On VBA:
Application.Average(DT01) = 42.1
'On Excel:
=MAX(A1:A495258) = 3622.7
'On VBA:
Application.Max(DT01) = 186.8
'On Excel:
=COUNT(A1:A495258) = 495258
'On VBA:
Application.Count(DT01) = 36506
当我看到最后的结果时,我立刻知道我必须问别人这件事。。。有人知道这里发生了什么吗??
更新:
我试图通过loop
的所有项来计算数组的和:
Dim SIGMA As Double: SIGMA = 0
For x = 1 To UBound(DT01)
SIGMA = SIGMA + DT01(x)
Next x
我得到了正确的结果(292547224.4
(,那么为什么我仍然得到1535172.8
和Application.Sum(DT01)
呢?
这不是一个理想的答案,所以有些人可能会用像样的文档来充实。我发现VBA中数组上的SUM只能工作到长度36506(从1开始(。
但是,您可以将值数组写入一个范围,然后将该范围引用传递给CCD_ 14并得到正确的值。
Option Explicit
Public Sub test()
Dim rng As Range, c As Range, i As Long
Set rng = Range("A1:A495258") 'A1 has value 1, A2 has 2 etc.
Debug.Print "Application.Sum(rng) " & Application.Sum(rng) '<== 122640490911
Debug.Print " Application.Sum(Application.Transpose(rng.Value)) " & Application.Sum(Application.Transpose(rng.Value)) '<== 666362271
Dim arr3()
ReDim arr3(1 To 495258)
For Each c In rng
i = i + 1
arr3(i) = c.Value
Next
Debug.Print " Application.Sum(arr3) with arr3(1 To 495258) " & Application.Sum(arr3)
Debug.Print "Application.WorksheetFunction.Sum(arr3) " & Application.WorksheetFunction.Sum(arr3)
End Sub
或者编写自己的自定义1d求和函数:
Option Explicit
Public Sub test()
Dim arr3(), c As Range, rng As Range, i As Long
Set rng = Range("A1:A495258")
ReDim arr3(1 To 495258)
For Each c In rng
i = i + 1
arr3(i) = c.Value
Next
Debug.Print SumArray(arr3)
End Sub
Public Function SumArray(ByVal arr As Variant) As Variant ' double
Dim i As Long
For i = LBound(arr, 1) To UBound(arr, 1)
SumArray = SumArray + arr(i)
Next
End Function