Using Excel VBA to Populate an Array with Cell Values – 4 Examples

The sample dataset showcases Month, Cost, and Sales.

Example 1 – Applying a While Wend Loop to Populate an Array with Cell Values in Excel VBA

Steps:

• Save your Excel file as an Excel Macro-Enabled Workbook (*xlsm).
• Go to the Developer tab >> select Visual Basic.

• In the Insert tab >> select Module.

• Enter the Code below in 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

• Save the code and go back to the Excel File.

• In the Developer tab >> select Macros.

• Select the Macro (show_cellValues) and click Run.

You will see a window with the value of the 1st cell in the Array.

• Click OK to see more values.

The the 2nd cell value in the Array is displayed.

• Click OK to see more values.

• Click OK till you see the last value in the Array.

Read More: How to Check If Array Is Empty with VBA in Excel

Example 2 – Using the For Each Next Loop to Populate an Array with Cell Values

Steps:

• Save your Excel file as an Excel Macro-Enabled Workbook (*xlsm).
• Go to the Developer tab >> select Visual Basic.

• In the Insert tab >> select Module.

• Use the Code below in 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

• Save the code.
• Run the code.

• In View >> select Immediate Window to see the result.

You will see the Array in the Immediate window.

You can see the values in the Array.

Read More: Excel VBA: Determine Number of Elements in Array

Example 3 – Applying a VBA For Next Loop to Populate an Array with Cell Values in Excel

Steps:

• Follow the steps described in Example 1.
• Enter the following Code in 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

• Save the code.
• Run the code.

In the Macros window:

• Select Array_with_CellValues and click Run.

• Press CTRL+G to open the Immediate window.

This is the result.

Example 4 – Using the Range Feature to Populate an Array with Cell Values in Excel

Steps:

• Follow the steps described in method-1.
• Enter the following Code in 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

• Save the code and go back to the Excel File.
• In the Developer tab >> select Macros.

• Select Macro (Array_Populating) and click Run.

This is the output.

 Things to Remember

• To include a new Array, use Example 4.

Download Practice Workbook

Download the practice workbook.

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