March 5, 2021
Hot Topics:

Discovering Arrays

  • By Karl Moore
  • Send Email »
  • More Articles »

So you got a 'Subscript Out Of Range' error, eh? Hey, just a wild guess.

We're now going to make room for that sixth element, in code.

Now we already know we can do this, as so:

ReDim FavouriteThings(1 To 6)

But this would clear everything current in the array, including summer breeze and the number nine. And I don't want that.

So instead we can use the Preserve keyword to ensure everything in the array is kept in the array.

  • Add another bloomin' Command Button to your Form
  • Change it's Caption property to: Make Room for Sixth Element

Top Tip: If you ain't labelling these button blighters, the project will soon become awfully messy. You really are labelling them, right? If not, you might want to work from the project download here.

  • Insert the following code behind your Command Button:
Private Sub Command4_Click()    ReDim Preserve FavouriteThings(1 To 6)End Sub

This tells Visual Basic to redimension the array to hold elements one to six, whilst preserving anything currently within it.

  • Run your application and click buttons one, two and three

These buttons (1) redimension your array to hold five elements, (2) put data into the array, (3) attempt to put information into the non-existent sixth element of the array.

  • Now hit the fourth button

This redimensions the array to make room for a sixth element, whilst still preserving its other information.

  • Now try to put information into the sixth element again, by clicking that third button once more

Ahah! Does it work now? If you don't receive an error, you've just altered the sixth element of your array. Wahoo!

But hold on just one lil' minute. We did that preserve thing to ensure all the information in the array stayed put. How do we know it's still there? By testing:

  • Add another (yes, another) Command Button to your Form
  • Change it's Caption property to: Display an Element
  • Throw the following code behind the button:
Private Sub Command5_Click()On Error GoTo PickOneErrDim intNumber As IntegerintNumber = InputBox("Which element do you wish to display?", _"Pick a Number", 4)MsgBox FavouriteThings(intNumber)    Exit SubPickOneErr:MsgBox Err.Description    End Sub

This simply accepts a number and displays the appropriate element of your array.

Try displaying the sixth element of an array before you redimension it with the fourth button. Then throw information into the sixth element with the third button.

Now use this last button to display the various elements of the array. Notice how after you redimension the array using the Preserve keyword all the array values are saved?

But try clicking that first button once more which redimensions the array to hold elements one to five, yet doesn't include the preserve keyword. Then display any of the elements using that last button once more. Notice how there isn't any information in any element? In other words, nothing has been preserved.

That's all whizzy and wonderful but how does this relate to real life programming, I hear you ask?

Well, let's say I have a user entering customer names on my form. Instead of adding them all to the database individually, I want to store them then do one mass update.

So I might decide to store all the customer names in an array then throw into a database later on.

Now, every time the user wants to enter a new customer, my program will typically redimension the array to hold one extra slot of information. Naturally, I wouldn't want to lose all the previously entered customer names, so that's where the Preserve keyword comes in handy.

So, you typically use the Preserve keyword when you want to resize (redimension) an array, whilst keeping anything currently within it.

Once more, let's briefly review what we've learnt over the past couple of pages:

  • You can declare dynamic arrays like this: Dim MyArray() as DataType
  • To alter the dimensions of an array, use: ReDim MyArray(x To y)
  • To alter the dimensions of an array whilst preserving any information currently within it, use: ReDim Preserve MyArray(x To y)

Before we move onto the next section, let's quickly add one more feature to our project. This one allows you to edit any individual item in the array. If the element you try to edit doesn't exist, it offers to redimension the array for you.

It's the longest piece of code we've written so far. Do you understand how it works?

  • Add another Command Button to your Form
  • Change it's Caption property to: Edit an Element (you are labelling these, right?)
  • Insert the following code behind the button:
Private Sub Command6_Click()On Error GoTo EditErrDim intArrayNumber As IntegerintArrayNumber = InputBox("Which array element " & _"would you like to change?", "Pick a Number")FavouriteThings(intArrayNumber) = _InputBox("What is the new value of element " & intArrayNumber, _"Enter Something", FavouriteThings(intArrayNumber))Exit Sub    EditErr:If Err.Number = 9 ThenIf MsgBox("Element number " & intArrayNumber & _" not present in the array. " & _"Would you like me to resize the array to hold elements 1 to " & _intArrayNumber & "?", vbYesNo + vbQuestion) = vbYes ThenReDim Preserve FavouriteThings(1 To intArrayNumber)End IfElseMsgBox Err.DescriptionEnd If    End Sub

Page 6 of 11

This article was originally published on November 20, 2002

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date