Using Excel VBA to Populate an Array with Cell Values

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

Use a VBA code with the While Wend loop:

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

Code Breakdown

The show_cellValues Sub Procedure is created.
The variable myary is declared as Variant.
The Array holds C5:C11.
The While Wend Loop calls the cell values in the Array.

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.

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

Use the For Each Next loop:

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

Code Breakdown

The Array_with_CellValue Sub Procedure is created.
The variable myary is declared as String, var as Integer, and myrang and myC as Range.
The Array is C5:C11.
The For Each Next loop calls the cell values in theArray.

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.

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

Use the For Next loop:

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

Code Breakdown

The Array_with_CellValues Sub Procedure is created.
The variable myAry is declared as Variant, var_i as Integer, and myrng as Range.
The Array is C5:C11.
The For Next loop calls the cell values in the Array.

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