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.

Rory · Accepted Answer · 2017-03-06 09:48:49Z

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.

answered Mar 6, 2017 at 9:48

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding an element to variant list/array in VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related