Is VBA slowing down your workbooks? Do you spend hours starting at Excel while the macros finish running? As part of our Speedy Spreadsheet Week, today lets talk about optimization techniques for Excel VBA & Macros.
Optimization Techniques for Excel VBA & Macros
Lets break this in to 2 sections. (1) Quick optimization techniques & tactics (2) Optimization ideas for the long run
Quick optimization techniques & tactics for VBA & Macros
1. Turn off the features you don’t want before running your macro
This is easy pick. Even while your macros are running, Excel does a lot of work to update the spreadsheet screen, re-calculate the formulas, display alerts etc. You can turn-off these things before running your macro and this would instantly speed up your code.
This is how you can do it:
Application.ScreenUpdating = false
Application.DisplayAlerts = false
and use these lines at the bottom of your code to turn on the alerts & screenupdating.
Application.ScreenUpdating = true
Application.DisplayAlerts = true
Things to remember:
- If your macro changes a part of the spreadsheet (for example animation, chart updation), turning off screenupdating is not the way to go.
- Set calculation mode to manual if it is ok (application.Calculation =xlCalculationManual)
- Make sure your turn-on everything at the end of your macro.
2. Do not select cells & objects
Range("A1").value = 10 is faster than
Range("A1").select
Selection.Value = 10
Many times, we select cells, objects in our code because the macro recorder produced such code. There is no need to select cell(s) to access or update the value(s).
3. Use built-in functions & features if possible
Do not re-invent the wheel. If there is a built-in formula or function, use it instead of developing your own. Objects like Application have many useful methods that can do what you want. For example, if you want to check if 2 ranges overlap, you an use Application.intersect instead of doing the math yourself.
4. Loop carefully
Some people say avoid loops. I say loop carefully. If you can do the same thing without a loop, do it. Else, make the loop as light-weight as possible. Think thru the problem and see what is the best way to loop. Use below guidelines when you are writing macros with loops:
- To search: Use built in methods like search, find, VLOOKUP, MATCH to find a value in a range instead of looping thru it.
- To copy: Use array to range copy method instead of looping thru the array and copying one element at a time. See example below:
Dim myArr(1 to 1000) as String
'do something and have a lot of values in myArr
'Copy all values in myArr to worksheet range myRange in one step!
Range("myRange").Value = Application.WorksheetFunction.Transpose(myArr) - To copy: If you want to copy one range of values to another, just assign the values to second range instead of looping. See below:
Range("r2").value = Range("r1").value - To sort: Use Range.sort method or any other built-in methods to sort if possible.
- Nesting loops: avoid them if you can. Else, take coffee breaks when you run the 14,000 times loop inside 17,345 times loop.
5. Use with block
When you want to do several operations on same object, use With block. This keeps your code clean, tells Excel to cache the object for a bunch of operations.
VBA & Macros – Optimization techniques for long run
Optimization is never ending process. So a good coder constantly learns techniques & follows sound principles to keep her code light-weight & fast.
1. Copy good ideas
There are tons of good code samples, example macro code on various sites, forums or books. Copy any good ideas you come across to speed up your code than trying to re-invent.
2. Divide and conquer
Sometimes a macro is slow because you are trying to do everything in one go. Try doing the task in small chunks. These ideas help:
- Break down your application in to smaller modules / macros.
- Show progress to end users thru a progress bar, frequent screen updates or status messages.
- Render most important aspects of the output first. Then do the rest in background.
3. Less is better
The less code you have, the lesser memory you use, the lesser objects, variables you deal with, the faster your code becomes. As an exercise, take your most complex macro and see if you can delete a line. Repeat this until there is nothing else you can remove. That alone improves the performance. Some ideas to consider:
- Plan your code before you write it. Think thru all steps.
- Do not write code for lame users (unless you are developing something to sell to larger public). Most users in workplace are smart and reasonable. So you can lessen error handling etc.
- Release objects you no longer want to clear memory.
- Negotiate with users and reduce features if possible.
4. Learn & Practice
Merriam-Webster dictionary defines “optimize” as,
to make perfect … more
Learning & practicing is a proven path to perfection. You can learn by examining others code, reading books or helping others. Very soon, you see that your own code becomes better & faster.
5. Know when you cannot optimize
Optimization is like an itch. If you do not resist in time, it consumes you. For most of us running the code in shortest time is not the goal. Our goal is to meet end user needs & get things done. So as long as your code runs fast enough leave it in peace and move to next challenge.
Also, some times no matter what you do, your Excel macro takes time to run. May be its time you considered other languages / tools to solve the problem
More on Excel Optimization & Speeding up:
Read these articles too,
- Optimization & Speeding-up Tips for Excel Formulas
- Charting & Formatting Tips to Optimize & Speed up Excel
- Excel Optimization tips by Experts
- Excel Optimization tips submitted by our readers
How do you speed up your VBA Macros?
Personally, I try to stay away from VBA in my workbooks. But I find that with just a few lines of VBA, we can add a lot of wow factor, convenience to the spreadsheets. So, once in a while I add VBA to make my workbooks even more awesome. I also use VBA to clean up data, process it or generate reports. In such cases, by using above ideas I saved a lot of time & made my workbooks nimble.
What about you? How do you speed up your VBA & Macros? Please share using comments.
For more information on VBA & Macros
Check out our Excel VBA section or join our VBA Classes online program.
14 Responses to “How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]”
in C3=2016
in C4=3
in C5=1 (the first next year with three Friday the 13ths)
=SMALL(IF(MMULT(--(MOD(DATE(C3+ROW(1:1000),COLUMN(A:L),13),7)=6),ROW(1:12)^0)=C4,C3+ROW(1:1000)),C5)
formula check in the next 1000 years
This will generate a table of counts of Friday the 13th's by year. If I didn't screw it up the next year with three is 2026.
I created a simple parameter table with a start date and end date that I wanted to evaluate. That calculates the number of days and generates a list of those days. Then filter and group. The generation of the list in power query (i.e. without populating a date table in excel) is pretty cool, otherwise this isn't really doing anything than creating a big date and filtering/counting.
let
Source = List.Dates(StartDateAsDate, Days2, #duration(1,0,0,0)),
ConvertDateListToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
AddDayOfMonthColumn = Table.AddColumn(ConvertDateListToTable, "DayOfMonth", each Date.Day([Column1])),
AddYearColumn = Table.AddColumn(AddDayOfMonthColumn, "Year", each Date.Year([Column1])),
AddDayOfWeekColumn = Table.AddColumn(AddYearColumn, "Day of Week", each Date.DayOfWeek([Column1])),
FilterFriday13 = Table.SelectRows(AddDayOfWeekColumn, each ([DayOfMonth] = 13) and ([Day of Week] = 5)),
Friday13thsByYear = Table.Group(FilterFriday13, {"Year"}, {{"Number of Friday the 13ths!", each Table.RowCount(_), type number}})
in
Friday13thsByYear
With the parameters replaced by values should you want to play along at home. This runs for 20 years starting on 1/1/2016.
let
Source = List.Dates(#date(2016,1,1), 7300, #duration(1,0,0,0)),
ConvertDateListToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
AddDayOfMonthColumn = Table.AddColumn(ConvertDateListToTable, "DayOfMonth", each Date.Day([Column1])),
AddYearColumn = Table.AddColumn(AddDayOfMonthColumn, "Year", each Date.Year([Column1])),
AddDayOfWeekColumn = Table.AddColumn(AddYearColumn, "Day of Week", each Date.DayOfWeek([Column1])),
FilterFriday13 = Table.SelectRows(AddDayOfWeekColumn, each ([DayOfMonth] = 13) and ([Day of Week] = 5)),
Friday13thsByYear = Table.Group(FilterFriday13, {"Year"}, {{"Number of Friday the 13ths!", each Table.RowCount(_), type number}})
in
Friday13thsByYear
=MATCH(3,MMULT(N(WEEKDAY(DATE(C3+ROW(1:100)-1,COLUMN(A:L),13))=6),1^ROW(1:12)),)+C3-1
It should be pointed out that Alex's solution, unlike some others, has the additional advantage of being non-array. My solution was nearly identical but with -- and SIGN instead of N and 1^.
=C3-1+MATCH(3,MMULT(--(WEEKDAY(DATE(C3-1+ROW(1:25),COLUMN(A:L),13))=6),SIGN(ROW(1:12))),0)
Sub Friday13()
Dim StartDate As Date
Dim EndDate As Date
Dim x As Long
Dim r As Long
Range("C7:C12").ClearContents
StartDate = CDate("01/01/" & Range("C3"))
EndDate = CDate("31/12/" & Range("C3"))
r = 7
For x = StartDate To EndDate
If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then
Cells(r, 3) = Month(x)
r = r + 1
End If
Next
End Sub
Calculate next year with 3 Friday 13th. Good for 100 years different from year entered in cell C3
Sub ThreeFriday13()
Dim StartDate As Date
Dim EndDate As Date
Dim x As Long
Dim WhatYear As Integer
Dim Counter As Integer
Range("E7").ClearContents
StartDate = CDate("01/01/" & Range("C3") + 1)
EndDate = CDate("31/12/" & Range("C3") + 100)
Counter = 0
For x = StartDate To EndDate
If WhatYear Year(x) Then
WhatYear = Year(x)
'Different year so reset counter
Counter = 0
End If
If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then
Counter = Counter + 1
If Counter = 3 Then
WhatYear = Year(x)
Exit For
End If
End If
Next
Range("E7") = WhatYear
End Sub
*RE-POST as not equal did not show earliuer
Calculate next year with 3 Friday 13th. Good for 100 years different from year entered in cell C3
Sub ThreeFriday13()
Dim StartDate As Date
Dim EndDate As Date
Dim x As Long
Dim WhatYear As Integer
Dim Counter As Integer
Range("E7").ClearContents
StartDate = CDate("01/01/" & Range("C3") + 1)
EndDate = CDate("31/12/" & Range("C3") + 100)
Counter = 0
For x = StartDate To EndDate
If WhatYear NE Year(x) Then
WhatYear = Year(x)
'Different year so reset counter
Counter = 0
End If
If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then
Counter = Counter + 1
If Counter = 3 Then
WhatYear = Year(x)
Exit For
End If
End If
Next
Range("E7") = WhatYear
End Sub
earlier*
I've a doubt with using array formula here.
In sample workbook, I tried to replicate the formula again.
=IFERROR(SMALL(IF(WEEKDAY(DATE($C$3,ROW($A$1:$A$12),13))=6,ROW($A$1:$A$12)),$B7),"")
For this I selected C7 to C12, and typed the same formula and pressed ctrl+alt+Enter. But in all cells it is taking $B7 (and not $B7, $B8, $B9.... etc)
and since it is array formula I can't edit individual cell.
Please guide.
Thanks
Hi Chandoo,
Cool stuff. You need to clarify that the answer of 5 represents the 1st month in the year that has a Friday the 13th, and not the number of Fridays the 13th in the year. Subtle, but important difference.
Thanks,
Pablo
I like the MMULT() function far more, but here's how I would have tackled it. It uses an EDATE() base and MODE() over 100 years. I'm assuming that 100 years is enough time to catch the next year with 3 friday 13th's. Array entered, of course.
{=MODE(IFERROR(YEAR(IF((WEEKDAY(EDATE(DATE(C3, 1, 13), ROW(INDIRECT("1:1200"))))=6), EDATE(DATE(C3, 1, 13), ROW(INDIRECT("1:1200"))), "")), ""))}
Finding all the Friday the 13ths in a Year:
=SUMPRODUCT((DAY(ROW(INDIRECT(DATE(C3,1,1)&":"&DATE(C3,12,31))))=13)*(TEXT(ROW(INDIRECT(DATE(C3,1,1)&":"&DATE(C3,12,31))),"ddd")="Fri"))
{=sum(if(day.of.week(DATe($YEAR;{1;2;3;4;5;6;7;8;9;10;11;12};13);1)=6;1;0))}
just list the years