VBA: arrays

Declaring arrays

An array might be declared like so:

dim arrayName() as dataType

The following example uses this syntax to declare an array and calls split() to initialize the array with some strings.

option explicit

sub main() ' {

    dim arrayOfStrings() as string

    dim i as integer

    arrayOfStrings = split("foo/bar/baz", "/")

    for i = lBound(arrayOfStrings) to uBound(arrayOfStrings)

        debug.print i & ": " & arrayOfStrings(i)

    next i

end sub ' }

Github repository about-VBA, path: /language/arrays/declare.bas

In order to declare an array with a fixed size, the dim a(n to m) as … construct might be used. This has also the benefit, that we don't have to rely on the option base statement.

option explicit

sub main() ' {

    dim ary(0 to 2) as integer
    dim i           as integer
    dim sum         as integer

    ary(0) = 42
    ary(1) = 99
    ary(2) = -8

    sum = 0
    for i = 0 to 2
        sum = sum + ary(i)
    next i

    debug.print "sum = " & sum

end sub ' }

Github repository about-VBA, path: /language/arrays/declare-n-to-m.bas

Multi-dimensional arrays

option explicit

sub main() ' {

    dim xyz(0 to 3, _
            0 to 4, _
            0 to 5) as long

    dim x as long, y as long, z as long

    for x = lbound(xyz, 1) to ubound(xyz, 1)
    for y = lbound(xyz, 2) to ubound(xyz, 2)
    for z = lbound(xyz, 3) to ubound(xyz, 3)

           xyz(x, y , z) = x*y + 2*z + y
           debug.print("xyz(" & x & ", " & y & ", z) = " & xyz(x, y, z))

    next z
    next y
    next x

end sub ' }

Github repository about-VBA, path: /language/arrays/declare-multi-dimensional.bas

Creating arrays

An easy way to create an array is to use the array() function. It returns a variant, however.

Using bracket notation

An interesting way to create arrays is using brackets (which is a short hand notation for Application.evaluate):

option explicit

sub main() ' {

   dim ary as variant

   ary = [{ 1, 1, 2, 3, 5, 8 }]

   dim i as integer
   for i = lBound(ary) to uBound(ary)
       debug.print ary(i)
   next i

end sub ' }

Github repository about-VBA, path: /language/arrays/create-1d-array-with-brackets.bas

In a similar spirit, a two dimensional array can be created. A comma separates the element within an array, the semicolon separates the arrays:

option explicit

sub main()

   dim ary_2d as variant

   ary_2d = [ { 1,2,3 ; "foo","bar","baz" }]

   dim i as integer
   for i = 1 to 3
       debug.print ary_2d(1, i) & ": " & ary_2d(2, i)
   next i

end sub

Github repository about-VBA, path: /language/arrays/create-2d-array-with-brackets.bas

Function returning an array

It's possible to create a function that returns an array.

option explicit

function fibonacci(n as integer) as long() ' {

    dim f() as long
    dim i   as integer

    redim f(1 to n)

    f(1) = 1
    f(2) = 1

    for i = 3 to n ' {
        f(i) = f(i-2) + f(i-1)
    next i ' }

    fibonacci = f

end function ' }

sub main() ' {

   dim ary() as long
   dim i as integer

   ary = fibonacci(10)

   for i = lBound(ary) to uBound(ary) ' {

       debug.print i & ": " & ary(i)

   next i ' }

end sub ' }

Github repository about-VBA, path: /language/arrays/return.bas

Determining the size of an array

There is no built-in function that returns the size (length) of an array. Two functions are needed to calculate the size of an array: lBound and uBound. These return the lower and upper index of the elements in an array, respectively.

Thus, the size of array is:

uBound(ary) - lBound(ary) + 1

TODO

option base statement.