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.

Excel VBA Populate Array with Cell Values


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.

Excel VBA Populate Array with Cell Values

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

Excel VBA Populate Array with Cell Values

  • Now, from the Developer tab >> select Macros.

  • At this time, select Macro (show_cellValues) and click on Run.

Excel VBA Populate Array with Cell Values

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.

Excel VBA Populate Array with Cell 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.

Excel VBA Populate Array with Cell Values

  • 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

Excel VBA Populate Array with Cell Values

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.

Excel VBA Populate Array with Cell Values

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.

Excel VBA Populate Array with Cell Values

Read More: Excel VBA: How to Filter with Multiple Criteria in Array (7 Ways)


Similar Readings


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.

Excel VBA Populate Array with Cell Values

  • 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

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.

Excel VBA Populate Array with Cell Values

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

Musiha Mahfuza Mukta

Musiha Mahfuza Mukta

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo