VBA Arrays

Anees Hameed

You were happily working on Javascript and one day your manager found that you have some Excel VBA skills and asked you to write some VBA code. You started VBA and soon found that arrays are hell there. You can’t do push, pop, splice just as you do it in Javascript. And you are in trouble. But there is a helper class, Yay!

All you have to do is get code from Git Hub, add it to your VBA project as a class.

Keep in mind that what you are going to create is not an array, but a class that helps to work with the one-dimensional array.
Below is a list of operations that you can do with VBAArray class.

'Initializes Fruits as new VBAArray class
Dim Fruits as new VBAArray

'Adds items to Fruits
Fruits.Push "Apple"Fruits.Push "Banana", "Grape", "Dates"

'Add items to be the beginning of array
Fruits.UnShift "Orange", "Mango"

'Removes item from the end of array
Fruits.Pop

'Removes item from the beginning of array
Fruits.Shift

'Get value of an index
Dim ThisFruit as String
ThisFruit = Fruits.Value(0)

'Set value of an index
ThisFruit = Fruits.Value(0, "New Mango")

'Search and get value of an index
Dim Pos as IntegerPos = Fruits.IndexOf("Mango")

'Get length of an array
Dim Length as IntegerLength = Fruits.Length

'Convert the VBAArray class to an array
Dim FruitsAsArray() As Variant
FruitsAsArray = Fruits.Arrayify
Debug.Print FruitsAsArray(0)

'Slice an arrayDim 
NewFruits As New VBAArray
Set NewFruits = Fruits.Slice(0, 3)

'Splice an array
Fruits.Splice 2, 2 Array("Watermelon", "Pineapple", "Strawberry")
Fruits.Splice -3, 1, Array("Watermelon", "Pineapple", "Strawberry")
  