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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

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

Excel VBA Populate Array with Cell Values

  • After that, you may press the CTRL+G keys to open the Immediate window.
  • Finally, you will see the result in that window.

Read More: VBA to Get Array Dimensions in Excel


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.


💬 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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has been working with the ExcelDemy project for 1.5 years. She has written over 97+ articles for ExcelDemy. Currently, she is working as the Excel and VBA Content Developer, creates contents, solves user problems, writes articles etc. Her work and learning interest varies from Microsoft Office Suites, and Excel to Data Analysis, and developing Excel Applications with VBA codes.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo