Extremely slow VBA code when formatting cells

Question

When attempting to format 2 columns in a workbook, the execution of the macro is extremely slow. To format approximately 4000 rows, it takes over 10 minutes.

The dates are populated from an external source that stores them as strings.

When commenting the code, it loads under 60 seconds.

The code

'Discover last row of data
    RowsToProcess = Range("A" & Rows.Count).End(xlUp).Row

    For i = 6 To RowsToProcess
    Worksheets("Data").Range("B" & i).Select
    Selection.NumberFormat = "dd/mm/yy;;"
    Selection.Value = CDate(Selection.Value)

    Worksheets("Data").Range("C" & i).Select
    Selection.NumberFormat = "dd/mm/yy;;"
    Selection.Value = CDate(Selection.Value)

    Next i

The code below does not format cells in the required format either.

Worksheets("Data).Columns("C").NumberFormat = dd/mm/yy;;"

http://stackoverflow.com/questions/8178161/what-is-the-most-efficient-quickest-way-to-loop-through-rows-in-vba-excel — aelgoa, Aug 03 '14 at 20:05
What do the dates look like when they are initially loaded into col B and col C as strings? "dd/mm/yy;;", like "03/08/14;;"? — Dan Wagner, Aug 03 '14 at 20:08
@Dan Wagner - They are loaded in a number of formats e.g. 03/08/2014, 03 July 2014, 05/06/2014 (MM/DD/YYYY). — Motivated, Aug 03 '14 at 20:33
Cool -- thanks for clarifying. Another question haha. How do you ensure correct handling between mm/dd vs dd/mm? For example, consider `05/06/2014`, as you mentioned above. How do you know if that date is May 6th, 2014 or June 5th, 2014? — Dan Wagner, Aug 03 '14 at 20:53
@Dan Wagner - For some odd reason although the dates are stored as dd/mm/yyyy, some of them come through as mm/dd/yyyy. I'm unsure why Excel in some instances treats the data differently. — Motivated, Aug 03 '14 at 22:00
[Here is one way to make Excel format quicker. The trick is to identify all target range first and then do the formatting in one go](http://stackoverflow.com/questions/24378866/quickly-format-cells-in-excel/24379194#24379194). [Also you might want to avoid using select as much as possible which slows down code execution specially if you're not utilizing the *Application.ScreenUpdating* on/off](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). A link was posted by Dan in his post. — L42, Aug 03 '14 at 23:54
@DanWagner and Motivated if dates are appearing in two different formats (mm/dd vs. dd/mm) then the raw data that is inputting these dates is likely from a different system locale. Simplest solution is to switch to that locale before working with those dates. It is otherwise impossible to "know" with any degree of reliability, whether 05/06 = May 6 (US notation) or June 5 (rest of world). — David Zemens, Aug 04 '14 at 02:46
I'm guessing that the dates in dd/mm format are dates which **cannot possibly** be represented in the US locale, like: 31/12/2013, etc. These would give some clue that the origin of the data used another locale setting, and that locale setting should be assumed for **all** dates of same provenance, even when they *appear* to be correct US-formatted dates like 01/02/2013 (which is probably February 1, **not** January 2). — David Zemens, Aug 04 '14 at 02:48
I think @DavidZemens has got the date handling pinned down, but I'm interesting in seeing the results. I'm also interested in seeing the side-by-side between `Variant` arrays and the original design! — Dan Wagner, Aug 04 '14 at 17:21

score 1 · Accepted Answer · edited May 23 '17 at 12:11

The post @aelgoa linked to is spot on. When the standard Application.ScreenUpdating options for speeding up your code aren't enough, I turn to Variant arrays.

(If you wanted to see how I use Application.ScreenUpdating etc., wrapped in a GoFast function, check out my answer here: VBA code optimization)

The script below works like this:

Load the Range defined in columns B and C into a Variant array
Apply CDate logic there (rather than accessing the Sheet every time)
Write the CDate-modified array out to Sheet

One caveat though -- my question in the comment above about differentiating between mm/dd and dd/mm (say May 6th, 2014 vs June 5th, 2014) still stands. I'll modify the code below based on your thoughts there. Thanks!

Option Explicit
Sub ProcessDates()

Dim AryColBandC As Variant
Dim DateFormatB As Date, DateFormatC As Date
Dim RngColBandC As Range
Dim LastRow As Long, Counter As Long
Dim MySheet As Worksheet

'set references up-front
Set MySheet = ThisWorkbook.Worksheets("Sheet1")
With MySheet
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    Set RngColBandC = .Range(.Cells(6, 2), .Cells(LastRow, 3))
End With

'load the B-C column range into a variant array
AryColBandC = RngColBandC

'loop through the variant array, applying the date
'conversion to each entry in the array and writing back
For Counter = LBound(AryColBandC) To UBound(AryColBandC)
    DateFormatB = CDate(AryColBandC(Counter, 1)) '<~ temporarily store
    DateFormatC = CDate(AryColBandC(Counter, 2)) '<~ dates here

    AryColBandC(Counter, 1) = DateFormatB
    AryColBandC(Counter, 2) = DateFormatC
Next Counter

'write the results out to the sheet
For Counter = LBound(AryColBandC) To UBound(AryColBandC)
    MySheet.Cells(5 + Counter, 2) = AryColBandC(Counter, 1)
    MySheet.Cells(5 + Counter, 3) = AryColBandC(Counter, 2)
Next Counter

End Sub

Thanks Dan. Will give that a try. Is the assumption though that the date columns are contiguous? What if dates are stored in different columns e.g. B,C,F,J,etc? — Motivated, Aug 03 '14 at 22:06
This design does assume that the info you'd like to transform is in columns B and C, but you can tweak that by adjusting the definition of `RngColBandC`, or, if necessary, adding new `Range` variables to store columns that aren't neighbors. The process: (1) Load `Range(s) into `Variant Array(s)`, (2) Apply `CDate` logic to `Variant Array(s)`, (3) Write results out shouldn't change much. — Dan Wagner, Aug 03 '14 at 22:10
-Thanks. I gave the code a go and it's equally as slow. It takes as long as the original code does. — Motivated, Aug 04 '14 at 01:07
Honestly, that is a VERY interesting result, and I'd love to help you get to the bottom of this performance issue. **Would you mind posting the full script that you originally ran as well as the variant-array-based code that you ran to see if there are any additional clues that might point out the bottleneck?** There are many, MANY more talented developers here as well -- I'm sure they'll have input too... — Dan Wagner, Aug 04 '14 at 01:11

Extremely slow VBA code when formatting cells

1 Answers1

Linked