‘Preserve’ing an Array in Excel VBA

Arrays in VBA are generally initialized with a Dim & a ReDim statement. But arrays also do have a list of other keywords that they are associated with – Preserve being one among them!

(I know I’ve got a post earlier on some clarifications required on static & public arrays. Today, I stand to answer my own earlier post!)

Let’s first tackle the problem of what kinds of keywords exist. The first in the list that I’d like to mention is the “Public” keyword. This is a fairly common one, and I believe many would be aware of it. A host of other keywords include – Private, Static/Dynamic, and Preserve. And the scope of this discussion will be limited to these alone.

Public, as the name suggests, is used to make available the array to other procedures and functions. (Public is supposed to make it available across modules, for otherwise, I would declare a Private array at the Declarations Page, and it would be available to only that module, but every procedure and function within! )

Private, on the other hand, is used to limit the scope of a variable within a certain context. For eg, if I were to declare

Private myarray() as Variant

within a module (as my suggestion above), this should be limiting the scope of usage of myarray to just that module. As a matter of fact, if I were to just declare

Dim myarray() as Variant

I would still be doing the same as a Private! Then why the name?! Just to remind us every time we look at it, so we don’t take it for granted! (Something every experienced coder would appreciate!!)

Static & Dynamic are just types of the arrays, in that the former lets you define your array size, while the latter gives you flexibility to redefine the array size as per requirement. This brings us to an important question – how do you redefine?

REDIM is the keyword to redefine a dynamic array. The statement would go like –

Dim MyArray() as Variant                    ‘Notice that we don’t mention a size

ReDim MyArray(100) as Variant

This lets us redefine the size anywhere in the code, hence the name dynamic array.

Now, this pops another question open – can we use variables to redefine the dynamic array? Ofcourse Yes! (Why else did we need that functionality in the first place)

So, something like

ReDim MyArray(counter) as Variant

works wonders (keep in mind, this is not possible with the Dim statement).

Alright, so where does the “Preserve” come in?? Just right here!!

Imagine a case where you ‘ReDim’ed an array to size 100, and half way in the code, you understand that there’s a possibility the size could exceed that. What do you do? That’s precisely why we need a dynamic array, but here’s the hitch, you just don’t know the size unless you run the loop/code everytime – in essence, the size is actually dynamic, and changes with every run!

Solution – We use the ReDim statement just where we get to know the required size. We start with a 100 items, but we could always rewrite that to whatever the size of the ‘counter’ variable! That’s the key! But that doesn’t solve our problem, actually poses another! Take a look at the code below –

Sub ArrayFill()

    Dim MyArray() As Variant   
    ReDim MyArray(100) As Variant   
    i = 0
    Cell = Cells(i + 1, 1)

    Do While Cell <> “”

        Cell = Cells(i + 1, 1)

            ReDim Preserve MyArray(i) As Variant       
            MyArray(i) = Cell.Value

        i = i + 1       
    Loop   

End Sub

Just a ReDim would just wipe clear the contents of the Array~! And hence, the need for “Preserve”!

Sorry I took this all around like a ‘Guy Ritchie’ movie, I just love story telling – engraves concepts hard! So, yeah, using a

ReDim Preserve MyArray(counter) as Variant

keeps your data intact, while also resizing your array at will!

Sub ArrayFill()

    Dim MyArray() As Variant   
    ReDim MyArray(100) As Variant   
    i = 0
    Cell = Cells(i + 1, 1)

    Do While Cell <> “”

        Cell = Cells(i + 1, 1)

        If i > 100 Then
            ReDim Preserve MyArray(i) As Variant
        Else
            MyArray(i) = Cell.Value
        End If

        i = i + 1       
    Loop

End Sub

So, for once, you can, both, have the apple and eat it!!!

Some Reference Materials –

For a crash course on Arrays in VBA, some of the links below would greatly help you in the subject –

http://www.cpearson.com/excel/passingandreturningarrays.htm

http://vba-corner.livejournal.com/3314.html

http://patorjk.com/programming/tutorials/vbarrays.htm

http://visualbasic.freetutes.com/learn-vb6/lesson6.html

Advertisements

2 thoughts on “‘Preserve’ing an Array in Excel VBA

  1. neck pain relief

    Hi there! I could have sworn I’ve been to this site before but after checking through some of the post I realized it’s new to
    me. Anyways, I’m definitely happy I found it and I’ll be bookmarking and
    checking back often!

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s