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)
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.
It is possible to return cell formulas (in A1 or R1C1 notation respectively) rather than cell values by using
X = rng1.Formula
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
Dim rng1 As Range
Dim rngArea As Range
Dim lngRow As Long
Dim lngCol As Long
Dim lngCalc As Long
Dim objReg As Object
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
lngCalc = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
'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)
'Dump the updated array sans leading zeroes back over the initial range
rngArea.Value2 = X
'caters for a single cell range area. No variant array required
rngArea.Value = objReg.Replace(rngArea.Value, vbNullString)
'cleanup the Application settings
.ScreenUpdating = True
.Calculation = lngCalc
.EnableEvents = True
Set objReg = Nothing