xl vba reset array to zeros fastest way code example

Example: excel vba reset array to zeros fastest way

'In VBA to reset a dynamic array of Longs or Doubles to all zeros, the simplest way 
'is to use the Redim() function. It's also extremely fast, roughly four times 
'faster than iterating over the array to set each element.

Sub Test_ArrayZeroing()
  	Dim i&, k&, a() As Long
    Dim time1#, time2#

    k = 100000000						'<--100 million elements
    ReDim a(1 To k)
    For i = 1 To k: a(i) = i: Next      '<--Fill array

    time1 = Timer
		'For i = 1 To k: a(i) = 0: Next '<--Method 1: 1125 ms
		ReDim a(1 To k)                 '<--Method 2:  260 ms (easy and faster)
    time2 = Timer
    
    Debug.Print "Test_ArrayZeroing: " & (time2 - time1) * 1000
End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'If you are willing to use an external call to Windows then an much faster
'method exists:

Private Declare PtrSafe Sub AssignZero Lib "kernel32" Alias "RtlZeroMemory" (pDst As Any, Optional ByVal CB& = 4)

Sub Test_ArrayZeroing()
    Dim i&, k&, a() As Long
    Dim time1#, time2#

  	k = 100000000						'<--100 million elements
    ReDim a(1 To k)
    For i = 1 To k: a(i) = i: Next      '<--Fill array

    time1 = Timer
        'For i = 1 To k: a(i) = 0: Next '<--Method 1: 1125 ms
        'ReDim a(1 To k)                '<--Method 2:  260 ms
		AssignZero a(1), k * 4          '<--Method 3:   74 ms (super fast)
    time2 = Timer
    
    Debug.Print "Test_ArrayZeroing: " & (time2 - time1) * 1000
End Sub

'Note that when using AssignZero() with an array of Doubles, remember that
'Doubles require 8 bytes of memory each, as opposed to the 4 bytes required
'for Longs.

'So the call to AssinZero() would like this for and array of Doubles:
AssignZero a(1), k * 8

'Note that the first argument of AssignZero() should be the first element
'of the array to be reset to zeros. The lowerbound in the above examples is 1, 
'but your array may have a lowerbound of 0... or some other number.

'Note that all three methods here work for arrays of Longs and Doubles. But to
'zero out an array of Variants, the only option is Method 1. This is because
'the default value for a Variant is EMPTY, not zero... and AssignZero() will
'not work because Variants store and require metadata in addition to 
'the value... and that metadata would be wiped out by AssignZero().

'Note that to reset an array to some value other than zero, the only
'option is to use Method 1.

'Note that this entire post is about Dynamic arrays. If you wish to zero out a Static
'array of Longs or Doubles you may also use the 'Erase' statement:
Erase a

'
'
'