Excel VBA to Populate Array with Cell Values (4 Suitable Examples)

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.

Download Practice Workbook

You can download the practice workbook from here:

4 Examples to Populate Array with Cell Values in Excel VBA

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 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

• 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 Create an Array in Excel VBA (4 Ideal Methods)

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 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

• 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: How to Filter with Multiple Criteria in Array (7 Ways)

Similar Readings

• Excel VBA to Create Data Validation List from Array
• Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
• Sort Array with Excel VBA (Both Ascending and Descending Order)
• How to Split a String into an Array in VBA (3 Ways)
• 22 Macro Examples in Excel VBA

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

• 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 CTRL+G keys to open the Immediate window.
• Finally, you will see the result in that window.

Read More: How to Use Arrays Instead of Ranges in Excel VBA

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

• 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.

Read More: How to Convert Range to Array in Excel VBA (3 Ways)

💬 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.

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. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• VBA Read Text File into Array (2 Suitable Methods)
• How to ReDim Preserve 2D Array in Excel VBA (2 Easy Ways)
• Name a Table Array in Excel (With Easy Steps)
• Excel VBA to Read CSV File into Array (4 Ideal Examples)
• VBA to Get Unique Values from Column into Array in Excel (3 Criteria)
• List of 10 Mostly Used Excel VBA Objects (Attributes & Examples)
• How to Write VBA Code in Excel (With Easy Steps)