VBA Arrays

VBA Arrays. The C programming language provides a data structure called the array, which can store a fixed-size sequential collection of elements of the same type.

First Element Array(0)

Array(1)

Array(2)

..,

Last Element Array(99)

Types of Array

1. Static Array

Depending on whether the array is created or allocated, its size or length can also be determined. For this reason, static arrays may also be called fixed-length arrays or fixed arrays. When an array is defined, array values can be specified or the array size can be specified without specifying array content, which is useful for describing known attributes such as gender, number of days in a week, etc.

Example: To create a static array, execute the following code below

Step 1: Firstly, insert a Command button on the worksheet you’re using.

 

 

Step 2: Then you will get a code window and add the following code.

Step 3: Click on the Command button and you will see the type of output shown in the below screenshot.

2. Dynamic Array

The dynamic array is the one that does not have a fixed, pre-determine number of elements that can be stored. These types of arrays are very important when working with entities that you cannot pre-determine the number.

Example, to run a dynamic array, pay attention to the following steps:

Step 1: For a dynamic array let’s read the names from the sheet.

Step 2: Click on the placed Command button on your worksheet and add the following code line.

 

 

Step 3: Click on the Command button. Your output will look like the result in the below screenshot.

ReDim Statement

ReDim statements are mostly used to declare the dynamic array variable, use to allocate or reallocate the storage space and thereby increase the size of the array.

See More: VBA Dim

Syntax of ReDim

ReDim [Preserve] varname (subscripts) [As type], [varname (subscripts) [As type]]

The ReDim statement syntax has these parts

Part	Description
Preserve	Optional. The modifier is used to preserve the data in the existing array when you change the size of only the last dimension.
Name	Required. Name of the variable; follows standard variable naming conventions.
Subscripts	Required. Dimensions of an array variable; may be declared up to 60 multiple dimensions. The subscripts argument uses the following syntax: [lower To] upper [, [lower To] upper ] . . . When not explicitly stated in lower, the lower bound of an array is controlled by the Option Base statement. If no option base statement is present, then the lower bound is zero.
Type	Optional. The data type of the variable; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported), Date, String, Object, Variant, a user-defined, or an object type.

The ReDim can be used to change the size of a dynamic array that has already been declared by using a Private, Public, or Dim statement with null parentheses.

ReDim can be used frequently to change the number of elements and dimensions in an array.

Note that if you declare a variable to be of a typed array, there’ll be insufficient type information to come up with a new array for ReDim.

Array Dimensions

One Dimension: One dimension array can be defined as an array that has all the elements in a single row or a single column.

For Example

1.  Dim OneDimensionArray(0 To 5) As Integer

Create an array with locations 0,1,2,3,4,5 that will accept integer values.

Defaults from 0 to 5 and creates an array with locations 0, 1, 2, 3, 4, and 5 that will accept string values.

Two Dimension: Two-dimensional array is known as a matrix. The array declaration in both the array i.e. in a two-dimensional array two subscripts are used.

Data_type Array Name (row)(column);

For Example, int Array(6) (12); Here we declare a two-dimensional array in C, named A which has 6 rows and 12 columns.

Multi Dimension: A multi-dimensional array is an array that has more than one dimension. It is an array of arrays; an array that has multiple levels.

For example, the InternetSpeed during day time (28, 30, 33).

Dim InternetSpeed (28, 31, 33) as a Single

Advantages of Array

• Below are some advantages of the array
• Arrays store multiple data of similar types with the same name.
• Arrays can be used for sorting data elements.
• Two-dimensional arrays are used to represent matrices.
• Arrays are used to implement other data structures like stack and queue.

VBA Array Example

Let’s start with a simple application. This application contains an excel sheet with data from an array variable. We have required the following things in this example,

• Create a new workbook in Microsoft Excel and save it as excel macro-enabled workbook (*.xlsm)
• Add a command button in the workbook.
• Set the name and caption properties of the command button.
• Then write the code that populates the excel sheet.

Let’s execute the following steps, such as

Step 1: Create a new workbook. On your devices, open Microsoft Excel and save the new workbook with the .xlsm extension.

Step 2: Add a Command button to the sheet.

 

Step 3: Save the Excel file.

1.  Click on the Save As button to choose the style you want.

2.  And select the Excel Macro-Enabled Workbook (*.xlsm) as the file type as shown in the below screenshot.

 

Step 4: Write the code on the code window.

1. Click on the Macros button and select the “Step Into” option.

2. Add the following code to the click event of CommandButton1_Click.

 

Step 2: The indicator background will change from greenish to a white background as shown in the below screenshot.

Step 3: Click on the CommandButton1_Click button.

Step 4: It displays the output in the Excel worksheet, as shown in the following screenshot.