Macro Development: Looping Two Arrays in Excel VBA
In this article, we will explore how to develop a macro in Excel VBA that loops through two arrays. The example dataset we will use consists of raw data found in cells C4 to C1500 and another dataset in cells D4 to D1500.
Understanding Arrays
In VBA, an array is a variable that stores multiple values. There are two types of arrays: fixed-size and dynamic. In this example, we will use dynamic arrays, which can change size during runtime.
Creating the Macro
To create the macro, follow these steps:
- Press ALT + F11 to open the Visual Basic Editor.
- Click Insert and then select Module to create a new module.
- Copy and paste the following code into the module:
Sub LoopTwoArrays()
Dim rawData() As Variant
Dim dataSet() As Variant
Dim i As Long
' Load raw data into an array
rawData = Range("C4:C1500").Value
' Load data set into an array
dataSet = Range("D4:D1500").Value
' Loop through both arrays
For i = 1 To UBound(rawData)
If rawData(i, 1) = dataSet(i, 1) Then
' Perform some action here
End If
Next i
End Sub
Explanation of the Code
The code begins by declaring two dynamic arrays, rawData() and dataSet(). The UBound() function is used to determine the size of each array.
Next, the Range().Value property is used to load the raw data and data set into their respective arrays. The For loop is then used to iterate through both arrays simultaneously.
Inside the loop, an If statement is used to compare the values of the two arrays. If the values are equal, some action can be performed. In this example, we have left this section blank for you to customize based on your needs.
In this article, we have covered the basics of macro development in Excel VBA, with a focus on looping through two arrays. We have provided a detailed example using the provided dataset and explained the key concepts and code used in the example.