Adding an element to variant list/array in VBA

Question

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 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.

Answer 1

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

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.