在VBA中向变体列表/数组添加元素 [英] Adding an element to variant list/array in VBA

问题描述

我想将一个项目添加到列表中.

I want to add an item to list.

vList as variant
vList = RefData.NameList
iCount = UBound(vList)
If RefData.Mode = "On" Then
  vList.Add("-2")

RefData是带有参考数据的工作表.我的名称列表返回值3和9.如果mode为"on"，我希望它返回3,9和-2.

RefData is a sheet with reference data.My name list returns values 3 and 9.If mode is "on" I want it to return 3,9 and -2.

它抛出对象错误.请帮忙.

It is throwing object error.Please help.

推荐答案

数组不是对象，因此没有可调用的任何属性或方法.您需要调整数组的大小，然后像这样添加项目

An array is not an object, and therefore doesn't have any properties or methods you can call. You need to resize the array and then add the item like this

Dim counter as long
vList as variant
vList = RefData.NameList
iCount = UBound(vList)
If RefData.Mode = "On" Then
  vlist = Application.transpose(vlist)
  counter = ubound(vList)
  redim preserve vlist(1 to counter +1)
  vlist(counter + 1) = -2
  vlist = Application.transpose(vlist)
End If

转置是必要的，因为将范围分配给Variant总是会创建2D数组，并且如果使用Preserve来维护其内容，则只能调整数组的最后一个元素的大小.

The transposing is necessary because assigning a range to a Variant always creates a 2D array and you can only resize the last element of an array if you use Preserve to maintain its contents.

这篇关于在VBA中向变体列表/数组添加元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！