Our community of experts have been thoroughly vetted for their expertise and industry experience.
Published:
Browse All Articles > 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:
Find Method (for finding and then working with specific data)
AutoFilter (applying a logical test to extract specific data)
SpecialCells (readymade collections of cells with certain characteristics, ie Blanks, Errors, Formulas, etc.)
Variant Arrays (for data manipulation on a large scale where every cell within the range of interest is to be processed)
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.
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
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
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 KillLeadingZerosSub 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 = NothingEnd Sub
Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.
Martin LissKeep everyone healthy; Wear a mask - Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.
Comments (3)
Commented:
Commented:
Commented: