将值写入动态数组VBA

Question

代码在固定的dim数组中运行良好，但当我向Redim添加行以使Results数组动态时，代码将不允许将结果添加到出现"Type Mismatch"错误的数组中。我确信一个简单的修复，已经玩过了，但我能看到它吗…

GP(毛利(从单列范围中提取的值的范围GPRange和Price从单行PriceRange更改它(。

Option Explicit
Sub CalcVolTable()
'Macro to produce table that shows relationship between price and volume  change
Dim PriceChangeAr As Variant, GPAr As Variant
Dim GPList, GPNum, PriceIndex, PriceChNum As Integer
Dim GP As Double

With ThisWorkbook
' Read all PriceChanges into a 1-dimensional array
PriceChangeAr = (.Worksheets("Results").Range("PriceRange").Value2)
' Read all GP range into a 1-dimensional array
GPAr =     Application.Transpose(.Worksheets("Results").Range("GPRange").Value)
'Clear Previous Results
Range("VolTable").ClearContents
Range("Output").Select
'Set up Results Array
'Dim VolResultsAr(1 To 8, 1 To 7) As Variant - this worked before I tried to make array dynamic
Dim VolResultsAr As Variant
PriceChNum = UBound(PriceChangeAr, 2)
GPNum = UBound(GPAr)
ReDim VolResults(1 To GPNum, 1 To PriceChNum) As Variant
For GPList = LBound(GPAr) To UBound(GPAr)
GP = GPAr(GPList)
'Set Cost per Unit value to get right GP in calc
Range("CostPerUnit").Value = 100 * (1 - GP)
' Now loop through each pricechange
For PriceIndex = LBound(PriceChangeAr, 2) To UBound(PriceChangeAr, 2)
'Reset Price and Vol adjt cell to zero
Range("ChPrice") = 0
Range("Chvol") = 0
'enter new Price Cahnge value
Range("ChPrice").Value = PriceChangeAr(1, PriceIndex)
'Use goal seek to calc vol chage req'd to bring GP back to same preset value
Range("GP").GoalSeek Goal:=GP, ChangingCell:=Range("ChVol")
'Writes result to cells in table in spreadsheet
Range("Output").Offset(GPList - 1, PriceIndex - 1).Value = Range("ChVol").Value
'CODE FALLING DOWN HERE - TRYING TO WRITE EACH RESULT INTO ARRAY
VolResultsAr(GPList, PriceIndex) = Range("ChVol").Value

Next PriceIndex
Next GPList
Range("Output2").Resize(UBound(VolResultsAr, 1), UBound(VolResultsAr, 2)) = VolResultsAr
Range("Output").Select

End With
MsgBox "done!"
End Sub

Answer 1

假设数组需要匹配，则需要ReDim VolResultsAr

ReDim VolResults(1 To GPNum, 1 To PriceChNum) As Variant
ReDim VolResultsAr(1 To GPNum, 1 To PriceChNum) As Variant

---

对于以下行：

Dim GPList, GPNum, PriceIndex, PriceChNum As Integer . 

只有PriceChNum是Integer，其他都是变体。这就是你的意图吗？应将Integer替换为Long，以避免潜在的溢出