You are reading the article Excel Vba Arrays: What Is, How To Use &Amp; Types Of Arrays In Vba updated in September 2023 on the website Nhahang12h.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Excel Vba Arrays: What Is, How To Use &Amp; Types Of Arrays In Vba
What is VBA Array?
An array is defined as a memory location capable of storing more than one value. The values must all be of the same data type. Let’s say you want to store a list of your favourite beverages in a single variable, you can use VBA array to do that.
By using an array, you can refer to the related values by the same name. You can use an index or subscript to tell them apart. The individual values are referred as the elements of the Excel VBA array. They are contiguous from index 0 through the highest index value.
This tutorial assumes you are using Microsoft Excel version 2013. The knowledge still applies to other versions of Microsoft Excel as well.
In this VBA Programming tutorial, you will learn-
What are Advantages of arrays?The following are some of the benefits offered by VBA array function
Group logically related data together – let’s say you want to store a list of students. You can use a single array variable that has separate locations for student categories i.e. kinder garden, primary, secondary, high school, etc.
Arrays make it easy to write maintainable code. For the same logically related data, it allows you to define a single variable, instead of defining more than one variable.
Better performance – once an array has been defined, it is faster to retrieve, sort, and modify data.
Types of Arrays in VBA
VBA supports two types of arrays namely;
Static – These types of arrays have a fixed pre-determined number of elements that can be stored. One cannot change the size of the data type of a Static Array. These are useful when you want to work with known entities such as the number of days in a week, gender, etc.For Example: Dim ArrayMonth(12) As String
Dynamic – These types of arrays do not have a fixed pre-determined number of elements that can be stored. These are useful when working with entities that you cannot predetermine the number.For Example: Dim ArrayMonth() As Variant
Syntax to declare arrays
Static arrays
The syntax for declaring STATIC arrays is as follows:
Dim arrayName (n) as datatypeHERE,
Code Action
Dim arrayName (n) datatype
It declares an array variable called arrayName with a size of n and datatype. Size refers to the number of elements that the array can store.
Dynamic arrays
The syntax for declaring DYNAMIC arrays is as follows:
Dim arrayName() as datatype ReDim arrayName(4)HERE,
Code Action
Dim arrayName () datatype
It declares an array variable called arrayName without specifying the number of elements
ReDim arrayName(4)
It specifies the array size after the array has been defined.
Array Dimensions
An array can be one dimension, two dimensions or multidimensional.
One dimension: In this dimension, the array uses only one index. For example, a number of people of each age.
Two dimensions: In this dimension, the array uses two indexes. For example, a number of students in each class. It requires number of classes and student number in each class
Multi-dimension: In this dimension, the array uses more than two indexes. For example, temperatures during the daytime. ( 30, 40, 20).
How to use Array in Excel VBAWe will create a simple application. This application populates an Excel sheet with data from an array variable. In this VBA Array example, we are going to do following things.
Create a new Microsoft Excel workbook and save it as Excel Macro-Enabled Workbook (*.xlsm)
Add a command button to the workbook
Set the name and caption properties of the command button
Programming the VBA that populates the Excel sheet
Let do this exercise step by step,
Step 1 – Create a new workbook
Open Microsoft Excel
Save the new workbook as VBA Arrays.xlsm
Step 2 – Add a command button
Note: This section assumes you are familiar with the process of creating an interface in excel. If you are not familiar, read the tutorial VBA Excel Form Control & ActiveX Control. It will show you how to create the interface
Add a command button to the sheet
Set the name property to cmdLoadBeverages
Set the caption property to Load Beverages
Your GUI should now be as follows
Step 3 – Save the file
Choose Excel Macro-Enabled Workbook (*.xlsm) as shown in the image below
Step 4 – Write the code
We will now write the code for our application
Dim Drinks(1 To 4) As String
Drinks(1) = “Pepsi” Drinks(2) = “Coke” Drinks(3) = “Fanta” Drinks(4) = “Juice”
Sheet1.Cells(1, 1).Value = “My Favorite Beverages” Sheet1.Cells(2, 1).Value = Drinks(1) Sheet1.Cells(3, 1).Value = Drinks(2) Sheet1.Cells(4, 1).Value = Drinks(3) Sheet1.Cells(5, 1).Value = Drinks(4) End Sub
HERE,
Code Action
Dim Drinks(1 To 4) As String
It declares an array variable called Drinks. The first array index is 1 and the last array index is 4.
Drinks(1) = “Pepsi”
Assigns the value Pepsi to the first array element. The other similar code does the same for the other elements in the array.
Sheet1.Cells(1, 1).Value = “My Favorite Beverages.”
Writes the value My Favorite Beverages in cell address A1. Sheet1 makes reference to the sheet, and Cells(1,1) makes reference to row number 1 and column 1 (B)
Sheet1.Cells(2, 1).Value = Drinks(1)
Writes the value of the array element with index 1 to row number two of column 1
Testing our applicationSelect the developer tab and ensure that the Design mode button is “off.” The indicator is, it will have a white background and not a coloured (greenish) background. (See image below)
You will get the following results
Download Excel containing above code
Download the above Excel Code
Summary
An array is a variable capable of storing more than one value
Excel VBA supports static and dynamic arrays
Arrays make it easy to write maintainable code compared to declaring a lot of variables for data that is logically related.
You're reading Excel Vba Arrays: What Is, How To Use &Amp; Types Of Arrays In Vba
Update the detailed information about Excel Vba Arrays: What Is, How To Use &Amp; Types Of Arrays In Vba on the Nhahang12h.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!