Sometimes, you may need to populate an Array with cell values in Excel VBA. Thus, I will explain how to populate an Array with cell values in Excel VBA in this article.
How to Populate Array with Cell Values in Excel VBA: 4 Examples
Here, I will describe 4 suitable examples to populate an Array with cell values in Excel VBA. Also, for your better understanding, I’m going to use sample data which has 3 columns. Those are Month, Cost, and Sales.
1. Applying While Wend Loop to Populate Array with Cell Values in Excel VBA
You can employ a VBA code using the While Wend loop for populating Array with cell values in Excel. The steps are given below.
Steps:
- Firstly, you need to open your worksheet. Here, you must save the Excel file as an Excel Macro-Enabled Workbook (*xlsm).
- Secondly, you have to choose the Developer tab >> then select Visual Basic.
- At this time, from the Insert tab >> you have to select Module.
- After that, write down the Code given below in the Module1.
Sub show_cellValues()
Dim myary As Variant
myary = Range("c5:c11").Value
counter = 1
While counter <= UBound(myary)
MsgBox myary(counter, 1)
counter = counter + 1
Wend
End Sub
Code Breakdown
- Here, I have created a Sub Procedure named show_cellValues.
- Next, I declare a variable myary as Variant.
- Here, the Array holds the data range C5:C11.
- After that, I used a While Wend Loop to call the cell values of the Array.
- Now, Save the code then go back to Excel File.
- Now, from the Developer tab >> select Macros.
- At this time, select Macro (show_cellValues) and click on Run.
Finally, you will see the following window named Microsoft Excel, which has the 1st cell value of the Array.
- Then, click on OK to see more values.
The following result is the 2nd cell value of the Array.
- Similarly, click on OK to see more values.
- You should press OK till the last value of the Array.
Lastly, you will see the following value. Which is the last component of the Array.
Read More: How to Check If Array Is Empty with VBA in Excel
2. Employing For Each Next Loop to Populate Array with Cell Values
You can apply another VBA code using the For Each Next loop to populate an Array with cell values in Excel. The steps are given below.
Steps:
- Firstly, you need to open your worksheet. Here, you must save the Excel file as an Excel Macro-Enabled Workbook (*xlsm).
- Secondly, you have to choose the Developer tab >> then select Visual Basic.
- At this time, from the Insert tab >> you have to select Module.
- After that, write down the Code given below in the Module2.
Sub Array_with_CellValue()
Dim myary(1 To 7) As String
Dim var As Integer
Dim myrang As Range, myC As Range
var = 1
Set myrang = Range("C5:C11")
For Each myC In myrang
myary(var) = myC.Value
var = var + 1
Next myC
For var = LBound(myary) To UBound(myary)
Debug.Print myary(var)
Next var
End Sub
Code Breakdown
- Here, I have created a Sub Procedure named Array_with_CellValue.
- Next, declare some variables myary as String, var as Integer, and myrang and myC as Range.
- Here, the Array is the data range C5:C11.
- After that, I used a For Each Next loop to call the cell values of Array.
- Now, Save the code.
- Then, you must Run the code.
- After that, from the View option >> you may select Immediate Window to see the result.
At this time, you will see the Array in the Immediate window. Also, you can see all the values using the scroll bar.
In the below, you can see some other values of the Array.
Read More: Excel VBA: Determine Number of Elements in Array
3. Applying VBA For Next Loop for Populating Array with Cell Values in Excel
You can use the For Next loop to populate an Array with cell values in Excel. The steps are given below.
Steps:
- Firstly, you should follow the steps of method-1.
- Secondly, write down the following Code in the Module3.
Sub Array_with_CellValues()
Dim myAry() As Variant
Dim var_i As Integer
Dim myrng As Range
var_i = 1
Set myrng = Range("C5:C11")
myAry = myrng
For var_i = LBound(myAry) To UBound(myAry)
Debug.Print myAry(var_i, 1)
Next var_i
End Sub
Code Breakdown
- Here, I have created a Sub Procedure named Array_with_CellValues.
- Next, declare some variables myAry as Variant, var_i as Integer, and myrng as Range.
- Here, the Array is the data range C5:C11.
- After that, I used a For Next loop to call the cell values of Array.
- Now, Save the code.
- Then, Run the code.
At this time, you will see the following window named Macros.
- Now, you have to select Array_with_CellValues and then Click on Run.
- After that, you may press the CTRL+G keys to open the Immediate window.
- Finally, you will see the result in that window.
4. Using Range Feature to Populate Array with Cell Values in Excel
You can use the Range object to populate an Array with cell values in Excel. The steps are given below.
Steps:
- Firstly, you should follow the steps of method-1.
- Secondly, write down the following Code in the Module4.
Sub Array_Populating()
Dim myAry As Variant
myAry = VBA.Array(1, 2, 3, 4, 5, 6, 7)
myAry = Application.WorksheetFunction.Transpose(myAry)
Range("B5:B11").Value = myAry
End Sub
Code Breakdown
- Here, I have created a Sub Procedure named Array_Populating.
- Next, I declared a variable myAry as Variant.
- Here, the Array is the given data.
- After that, I used a Range object to keep the cell values of Array in the B column.
- Now, Save the code then go back to the Excel File.
- Now, from the Developer tab >> select Macros.
- At this time, select Macro (Array_Populating) and click on Run.
Finally, you will get the following Array.
💬 Things to Remember
- Here, you can apply any of methods 1, 2 or 3 to call the cell values using Array.
- But, when you want to include a new Array then you should use method 4.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
I hope you found this article helpful. Here, I have explained 4 easy ways to Populate an Array with Cell Values in Excel VBA. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Find Lookup Value in Array in Excel VBA
- VBA Array Size Limit in Excel
- VBA to Get Array Dimensions in Excel
