Using Variant Arrays in Excel VBA for Large Scale Data Manipulation

Introduction

One of the most common VBA techniques is the use of a For .. Next loop to iterate through all the cells in a range.  The syntax is straightforward and easily applied.  Unfortunately, for large data sets it can also be the programming equivalent of using a sledgehammer to crack a walnut -- a lazy, inefficient and time-consuming approach for dealing with data.  

There are a number of superior techniques available in Excel VBA that will typically cut For .. Next loop run time by 95.0-99.9% such as:



 

Variant Arrays

This Article provides a brief overview of a code sample for using variant arrays with Excel VBA, in this case, removing all leading zeroes from a user selected range.  There are five basic steps in the attached code:

1) The user selects a range, which may be contiguous, or have multiple range areas.

2) If there is more than one cell in the range area then a variant array X() is used to read in the values from each range area.  The variant array is a 2-dimensional array containing the same amount of rows and columns as the parent range.

3) A For .. To loop using UBound for the array size limit is used to iterate through each element of the array (rows are looped first, followed by columns).

4) A simple Regular Expression replacement of any leading zeroes with vbNullString is performed.   Note that a For Next loop approach is suboptimal for variant arrays, see the Microsoft Support article:  Using For Each to Loop Through Arrays Is Not Recommended.

5) The modified variant array is written back over the original range area.
 

Potential issues with using variant arrays notes:

See: You may receive a "Run-time error 1004" error message when you programmatically set a large array string to a range in Excel 2003

I have experienced this when modifying very long formulae with arrays.  The workaround was to move any such strings into a second array, and then write this second array back to the spreadsheet, cell by cell. All other strings are dealt with as per the code below, the array is written back to the range in a single stage.
 

Other notes:

It is possible to return cell formulas (in A1 or R1C1 notation respectively) rather than cell values by using
X = rng1.Formula
or
X = rng1.FormulaR1C1

My Mappit! addin article uses the later approach to quickly determine potential spreadsheet errors  Mappit! - a free Excel model auditing addin

Readers of this article will find Aikimark's Fast Data Push to Excel case study article very interesting reading.

For more information on using Regular Expressions in VBA please see Patrick Matthew's excellent article,   Using Regular Expressions in Visual Basic

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author, please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

'Press Alt + F11 to open the Visual Basic Editor (VBE)
'From the Menu, choose Insert-Module.
'Paste the code into the right-hand code window.
'Press Alt + F11 to close the VBE
'In Xl2003 Goto Tools … Macro … Macros and double-click KillLeadingZeros

Sub KillLeadingZeros()
    Dim rng1 As Range
    Dim rngArea As Range
    Dim lngRow As Long
    Dim lngCol As Long
    Dim lngCalc As Long
    Dim objReg As Object
    Dim X()

  
    On Error Resume Next
    Set rng1 = Application.InputBox("Select range for the replacement of leading zeros", "User select", Selection.Address, , , , , 8)
    If rng1 Is Nothing Then Exit Sub
    On Error GoTo 0

    'See Patrick Matthews excellent article on using Regular Expressions with VBA
    Set objReg = CreateObject("vbscript.regexp")
    objReg.Pattern = "^0+"

   'Speed up the code by turning off screenupdating and setting calculation to manual
   'Disable any code events that may occur when writing to cells
    With Application
        lngCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    'Test each area in the user selected range
    
    'Non contiguous range areas are common when using SpecialCells to define specific cell types to work on
    For Each rngArea In rng1.Areas
        'The most common outcome is used for the True outcome to optimise code speed
        If rngArea.Cells.Count > 1 Then
           'If there is more than once cell then set the variant array to the dimensions of the range area
           'Using Value2 provides a useful speed improvement over Value. On my testing it was 2% on blank cells, up to 10% on non-blanks    
            X = rngArea.Value2
            For lngRow = 1 To rngArea.Rows.Count
                For lngCol = 1 To rngArea.Columns.Count
                    'replace the leading zeroes
                    X(lngRow, lngCol) = objReg.Replace(X(lngRow, lngCol), vbNullString)
                Next lngCol
            Next lngRow
            'Dump the updated array sans leading zeroes back over the initial range
            rngArea.Value2 = X
        Else
            'caters for a single cell range area. No variant array required
            rngArea.Value = objReg.Replace(rngArea.Value, vbNullString)
        End If
    Next rngArea

    'cleanup the Application settings
    With Application
        .ScreenUpdating = True
        .Calculation = lngCalc
        .EnableEvents = True
    End With

    Set objReg = Nothing
End Sub

Select all Open in new window