VBA clear array

Normally, an array is cleared from memory when the procedure in which the array is defined terminates. If you want to clear the array before the procedure terminates, one solution is to declare the array as a dynamic array and use Redim to erase all elements of the array.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim RandomValues() As Variant 'declare a dynamic array
ReDim RandomValues(10) 'expanding the array for 10 elements
Dim i As Integer
For i = 0 To UBound(RandomValues)
   Randomize 'initialize random seed based on system time
   RandomValues(i) = Int(51 * Rnd + 50) 'store random values between 50 and 100 in array
Next
  
'display array
printArray RandomValues, "Random Values:" & vbCrLf
'Clear arrays
ReDim RandomValues(0)
  
End Sub

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

Posted by: Dara | post date: 03-01-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.