VBA dynamic array

A dynamic array is declared with empty parentheses. Then, by using Redim Preserve key phrase, you are able to expand the array. You can use the Redim keyword alone, but without Preserve keyword the old elements of the array will be removed.

Example:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim nums() As Integer 'declare a dynamic array
ReDim nums(9) 'now the array size is 10
Dim i As Integer
For i = 0 To 9
   nums(i) = i 'initialize array
Next
printArray nums, "Before expanding:" & vbCrLf 'show all array elements before expanding the array
'Expand the array and add more elements
ReDim Preserve nums(19)
For i = 10 To UBound(nums)
     nums(i) = i
Next
printArray nums, "After expanding:" & vbCrLf 'show all array elements after expanding the array

End Sub
Sub printArray(arr() As Integer, str As String)
   For Each Item In arr
      str = str & Item & vbTab
   Next
   MsgBox str
End Sub

Posted by: Dara | post date: 02-27-2013 | Subject: VBA for MS Excel




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.