How to write macro using Excel VBA with sample code - Dynamic Array

In the below code snippet, it shows how to create dynamic array in VBA with a simple example of storing list of people from the excel range and displaying it in message box.

Step:1 
Open excel sheet and provide list of people names in range starting from A1. 

Step:2

Now go to Developer tab and select visual basic to write macro.

Step:3

After seeing the below window, create a module (to create macro) by following the below steps.

Step:4

Now copy and paste the below code snippet in the module.

Sub ListOfPeople()
'declare array of unknown length
Dim PersonNames() As String
'initially there are no people
Dim NumberOfPeople As Integer
NumberOfPeople = 0
'loop over all of the people cells
Dim PersonNameInCell As Range
Dim TopCell As Range
Dim ListofPeopleNamesRange As Range
Set TopCell = Range("A1")
Set ListofPeopleNamesRange = Range(TopCell, _
TopCell.End(xlDown))
For Each PersonNameInCell In ListofPeopleNamesRange
'for each person found, extend array
NumberOfPeople = NumberOfPeople + 1
ReDim Preserve PersonNames(NumberOfPeople - 1)
PersonNames(NumberOfPeople - 1) = PersonNameInCell.Value
Next PersonNameInCell
'list out contents to show worked
Dim msg As String
Dim i As Integer
msg = "People in array: " & vbCrLf
For i = 1 To NumberOfPeople
msg = msg & vbCrLf & PersonNames(i - 1)
Next i
MsgBox msg
End Sub


Step: 5

Close the coding window and go to Developer tab, click macros to run the macro you have created just now.

Step: 6

Here is the final output, you have read the list of people from the excel range, stored it in dynamic array and finally displayed those list of people from the array.

Hope it would have been helpful for you, will come up with another blog soon.