In this tutorial we will guide you on how to create and clear arrays in VBA Excel.
Arrays are basically a group of variables which have similar properties. Instead of using multiple variables, it’s better to use a single array. By using arrays, we can use a single variable for several values. It can store from 1 to more than thousands of values depending on the size of program. Arrays use a loop function to work which helps to store, retrieve and modify values in it. It is also a good practice to clear arrays in VBA programs at the end to free up the memory used to store it. This is very helpful when working on large data or spreadsheets as it slows down the overall performance of the program. Here we are using arrays in VBA Excel to input more than one values in a single variable with the help of macros.
Step 1 – Insert the Arrays statement
Now let’s work on a simple code which asks you for student name and enters it on the sheet.
In VBA, we declare an array the same way in which we declare a single variable, that is, through the Dim statement.
The name of the array over here is Student Name. Furthermore, we have defined the array as a string.
Next, we will use the “for next” loop so that we can accept 5 inputs for the value. Now instead of storing each and every input in a different variable, we have called upon the array that we created earlier to store and represent the value on the screen.
Step 2 – Insert the value to be entered in the cell
Now let’s head back to the sheet and see how it works. Great! the macro asked us for the input. Notice that as we enter the names, the data is entered on the sheet.
Step 3 – Insert a loop function
Similarly you can assign multiple arrays by separating them using Comma.
Over here, let’s use the same code but instead of using a single array, we will declare 3 arrays for different values that is the Student Name, Student ID and his Marks.
With that done, we will include the array names in the “for next” loop as well so that the user is prompted for the ID and marks as well.
Step 4 – Enter values to be entered in the cell
Let’s open up the Excel sheet and double click anywhere to run the code. You will notice over here that after the name, the user is prompted for the ID and the marks as well.
Step 5 – Clear out arrays by using Erase keyword
You can also clear the array in VBA at the end of the code by using the Erase keyword followed by the Array name which you want to clear. This will basically free up the memory used to store the value.
If you run the code now, it will prompt you for the data but won’t store any of the values entered.
And that’s basically how you can create and clear an array using VBA in Excel.
Home