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