时间戳

首页 > 解决方案 > 调整数组内的数组大小

vba - 调整数组内的数组大小

问题描述

我有一个带有键：值对的 VBA 字典。键是动物名称，值是如下所示的数组数组：[[吃掉的公斤数]，获得的公斤数]]。我正在尝试调整内部数组的大小，以便可以通过循环将项目添加到这些数组中。文档指出“要调整 Variant 中包含的数组的大小，您必须在尝试调整其数组大小之前显式声明 Variant 变量。” 我在代码的前面已经完成了这个，但我仍然有一个语法错误。

            With animalDict
                'Check if the animal is already in the dict
                'If not, create a key:value entry that is equal to (animal name):[[kilos eaten],[kilos gained]]
                If Not .Exists(animalName) Then
                    ReDim kilosEatenArr(0)
                    ReDim kilosGainedArr(0)
                    ReDim outerArr(1)
                    kilosEatenArr(0) = kilosEatenCount
                    kilosGainedArr(0) = kilosGainedCount
                    outerArr(0) = kilosEatenArr
                    outerArr(1) = kilosGainedArr
                    .Add Key:=animalName, Item:=outerArr
                Else
                    outerArr = .Item(animalName)
                    ReDim Preserve outerArr(0)(LBound(outerArr(0)) To UBound(outerArr(0)) + 1) 'Sytnax error here
                    ReDim Preserve outerArr(1)(LBound(outerArr(1)) To UBound(outerArr(1)) + 1) 'Another sytnax error here
                    kilosEatenArr(UBound(kilosEatenArr)) = kilosEatenCount
                    kilosGainedArr(UBound(kilosGainedArr)) = kilosGainedCount
                    outerArr(0) = kilosEatenArr
                    outerArr(1) = kilosGainedArr
                    animalDict(animalName) = outerArr
                End If
            End With

标签： vba

解决方案

@Warcupine 是正确的。您必须将数组从字典中拉出，重新调整它，设置该索引的值，然后将其添加回字典。

我没有对此进行测试，但它应该可以工作：

If Not .Exists(animalName) Then
    ReDim kilosEatenArr(0)
    ReDim kilosGainedArr(0)

    kilosEatenArr(0) = kilosEatenCount
    kilosGainedArr(0) = kilosGainedCount

    outerArr = Array(kilosEatenArr(0),kilosGainedArr(0))

    animalDict(animalName) = outerArr   'implict adds are faster

Else 

    outerArr = animalDict(animalName)

    kilosEatenArr = outerArr(0)
    kilosGainedArr = outerArr(1)

    ReDim Preserve kilosEatenArr(LBound(kilosEatenArr) To UBound(kilosEatenArr) + 1)    
    ReDim Preserve kilosGainedArr(LBound(kilosGainedArr) To UBound(kilosGainedArr) + 1)

    kilosEatenArr(UBound(kilosEatenArr)) = kilosEatenCount
    kilosGainedArr(UBound(kilosGainedArr)) = kilosGainedCount

    outerArr = Array(kilosEatenArr, kilosGainedArr)

    animalDict(animalName) = outerArr

End If 


Erase outerArr
Erase kilosEatenArr
Erase kilosGainedArr

推荐阅读