Breaking News: Grepper is joining You.com. Read the official announcement!

excel vba reset array to zeros fastest way

Excel Hero answered on October 11, 2020 Popularity 10/10 Helpfulness 2/10

answer excel vba reset array to zeros fastest way

excel vba reset array to zeros fastest way

Comment

'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 a 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

'
'
'

Popularity 10/10 Helpfulness 2/10 Language vb

Source: academy.excelhero.com

Tags: excel reset vba

Link to this answer
Share Copy Link

Contributed on Oct 11 2020

Excel Hero

0 Answers Avg Quality 2/10

excel vba reset array to zeros fastest way

Contents

More Related Answers

excel vba reset array to zeros fastest way

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.