How to create VBA Array in Excel [Complete Guide] for beginners

Variable can store more than one value at a time. When you store series of values in a variable then it is called as an array variable. To declare an array, we use the same process as we declare a variable with only change is of parenthesis. While declaring an array variable, we use parenthesis. Here we will be going to discuss VBA Array in Excel.

Excel VBA

VBA Array: Define

The VBA Array is a type of variable that stores the list of data and values of similar types. Normally a variable can store many values at a time but in terms of VBA, a normal variable can store a single value at a time.

For example, if you want to store the marks of the students via VBA then you need many variables.

How to use the VBA Array?

  • Let’s take an example of three students. Read these marks and write it on the immediate window.

Student Marks

  • To open immediate window, click on the developer tab.

Also check Pivot Chart

  • In the developer tab, you will find the Visual Basic option.

Excel VBA

  • Click on this option, find an immediate window where you can write the code.

Excel VBA

  • Write code

Public Sub StudentMarks( )

With ThisWorkbook.Worksheets(“Sheet1”)

Declare variable for every student

Dim Student1 as Integer

Dim Student2 as Integer

Dim Student3 as Integer

Read marks

Student1 = .Range(“C2”).Offset(1)

Student2 = .Range(“C2”).Offset(2)

Student3 = .Range(“C2”).Offset(3)

Print marks

Debug.Print “Student Marks”

Debug.Print Student1

Debug.Print Student2

Debug.Print Student3

End With

End Sub

This code will show you the result in the Immediate Window.

Student Marks

45

54

34

In this, we have added code for every student. Think if you have 1000 of students then you need to write 3000 approx lines of code. Hence to make it easier we use arrays. With arrays, you can store lots of data items in a single structure. Check the VBA code:

Public Sub StudentMarksArr( )

With ThisWorkbook.Worksheets(“Sheet1”)

Declare an array

Dim Students(1 to 3) as integer

Read student marks

Dim i as integer

For I = 1 to 3

Students(i) = .Range(“C2”).Offset(i)

Next i

Print Student Marks

Debug.Print “Students Marks”

For I = Lbound(Students) to Ubound(Students)

Debug.Print Students(i)

Next i

End With

End Sub

Hence this loop will work until the completion of the array. It will continue till the table gets completed.

Different Types of VBA Arrays

There are two types of arrays available:

Static: The static array is an array that has fixed size.

Dynamic: The array whose size was set at the runtime.

How to assign values to an Array

For both static and dynamic types of arrays we assign values in a similar way:

Public Sub AssignValue( )

Dim arrMarks(0 To 3) As long

arrMarks(0) = 5

arrMarks(3) = 46

arrMarks(4) = 99

End Sub

Array Methods

You will find various inbuilt functions are there in VBScript that helps the developers in handling arrays. These inbuilt functions are

Also Check Excel functions list

LBound: Returns an integer that has the smallest subscript.

UBound: Returns an integer that has the largest subscript.

Join: Returns a string that contains the number of substrings.

Split: Returns an array with the specified number of values.

IsArray: Returns Boolean value.

Filter: Returns a zero-based array consisting subset of string arrays depending on the specific filter criteria

Erase: Recovers the allocated memory of array variables.

Leave a Reply

Your email address will not be published. Required fields are marked *