Excel VBA Performance Coding Best Practices

Anith » Excel VBA Performance Coding Best Practices

Anith » Excel VBA Performance Coding Best Practices — Fri, 13 Mar 2009 04:53:47 GMT

PingBack from http://www.anith.com/?p=18366

re: Excel VBA Performance Coding Best Practices

sam — Fri, 13 Mar 2009 05:33:23 GMT

For simple functions used by your code in high frequency, implement them yourself in VBA instead of using the WorksheetFunction object.

I thought it was the other way around....where ever possible use built in functions rather than the other way around...

re: Excel VBA Performance Coding Best Practices

sam — Fri, 13 Mar 2009 08:02:13 GMT

Dont declare variables as Integer Data types

From VB6 onwards they get converted in to Long any way... so declare them as long

re: Excel VBA Performance Coding Best Practices

Doug Jenkins — Fri, 13 Mar 2009 11:33:48 GMT

Sam - "I thought it was the other way around....where ever possible use built in functions rather than the other way around..."

Yes, use built in VBA functions, but for Excel functions that do not have a VBA equivalent it is about 10 times quicker (in XL 2007) to write your own, rather than use worksheetfunction.

In previous versions the speed hit was about 3X. There must be some scope there for at least getting back to where we used to be.

More details at:

http://newtonexcelbach.wordpress.com/2008/02/23/worksheetfunction-vs-udf/

http://newtonexcelbach.wordpress.com/2008/05/24/worksheetfunction-vs-udf-2/

Re: Discovering functionality via John Walkenbach's blog

Gordon — Fri, 13 Mar 2009 15:19:10 GMT

Surely MS Program Managers shouldn't be learning about fundamental functions in their programs...from blog posts?

Aren't you guys meant to, you know, have the inside track on these things?

re: Excel VBA Performance Coding Best Practices

Mathias — Fri, 13 Mar 2009 23:35:49 GMT

Nice post, thank you. I always used the "trick " to turn off unnecessary functionality during calculations, but your list looks much more exhaustive!

Oh , and a good way to enforce explicit variable declaration is to systematically add "option explicit" on top of the modules.

Turning things off

kurt.thomas — Sat, 14 Mar 2009 03:00:56 GMT

Great list! But remember to also point your error handler at the code that will restore state, otherwise if there is an error, you just leave it all up to Excel to restore.

re: Excel VBA Performance Coding Best Practices

Clippy :) — Sat, 14 Mar 2009 03:07:30 GMT

Gordon, do you as well expect that the designed and engineers behind F-1 cars and fighter jets are THE best drivers and THE best pilots? :))

re: Excel VBA Performance Coding Best Practices

sam — Sat, 14 Mar 2009 13:28:01 GMT

"Range.SpecialCells() to scope down the number of cells your code needs to work with"

But always check for 8192 areas - Expecially with 2007....Its only the grid thats got bigger...not the other associated limits

re: Excel VBA Performance Coding Best Practices

Gordon — Sat, 14 Mar 2009 20:51:40 GMT

@Clippy

No, but if they were posting a blog post called "How best to drive F1 cars/fly fighter jets" I wouldn't expect that they would admit to recently learning that "I found out on a blog post that it's best to go in slow, fast out" and, erm whatever the equivalent in fighter jet technique might be!

Loading a range into a variant as opposed to using a loop is covered in Excel 101, and all I was doing is expressing surprise that anyone who has such an intimate relationship with Excel wouldn't realise that telling world+dog that they didn't have even a basic grasp on their own product might reinforce negative impressions that people might already have about MS and their development culture, and the poorly-regarded software that sometimes results.

re: Excel VBA Performance Coding Best Practices

sam — Mon, 16 Mar 2009 08:07:23 GMT

Gordon...

"they didn't have even a basic grasp on their own product"

Go through this entire blog...Exclude post on a)Excel Services

b)Sharepoint

c)posts on more rows, more columns, more colors , more nested ifs ...in 007

and show me one post related to Excel(+VBA) which has NEW content ...something not posted on other blogs, newsgroups, books etc...

I mentioned earlier...I learn more from the comments than from the main post...

re: Excel VBA Performance Coding Best Practices

Tom Bizannes — Tue, 17 Mar 2009 07:01:38 GMT

Wow, this is kind of dangerous!

I mean specifically turning off calculation!

If you are copying values all around which are changed by formulae, you will get some erroneous results. This this happens more than not in the large spreadsheets we have to manipulate for clients aroudn the globe....

Other items , like screen updating sure makes an app run faster, but clients like to see movement and so you need to make sure the status bar reflects what's going on....

re: Excel VBA Performance Coding Best Practices

Chad Rothschiller — Tue, 17 Mar 2009 20:10:40 GMT

Regarding F-1 cars, fighter jets, and Excel 101: I wanted to post a note to give some more context on the research I did that I hope will help frame a better understanding of why I attributed some of my findings to other people.

It starts and ends with the customer. For me and this bit of research, it started in the context of what people, our customers, are doing with Excel. After hearing that Excel 2007 was slower in some scenarios, I hit as many avenues as I could to get good feedback / examples so we could debug the issues and see what people were running into most frequently. I sent emails to people I know, wrote on the Excel blog, and searched the internet, looking and asking for examples.

In the natural course of this process, I came across examples of the slowdowns *and* what people were doing to mitigate them. As I interacted with a wide variety of people I realized that while some were experts, others would probably appreciate and benefit from a clear and simple statement of the basics of best practices in VBA coding.

Once I wrote the content for this post, I sent it around to several other folks here on the Excel team for review. They confirmed that I had addressed the classic / basic cases and hadn't missed any big areas.

Before posting I did take some time to consider whether or not I should claim that I had singlehandedly come up with the mitigations and workarounds for everyone, even though most of the examples, issues, and discussion of the solutions took place with other people, our customers, in the context of their work. I had a hunch that referencing other people's work might open up a can of worms, but in the end I decided that it was the right thing to do, since it more accurately reflects the conversations I had with people.

re: Excel VBA Performance Coding Best Practices

Nate Oliver — Wed, 18 Mar 2009 02:20:55 GMT

"Wow, this is kind of dangerous!

I mean specifically turning off calculation!"

Note that this blog entry is not advising you to leave Excel in Manual Calc-mode unless you found it that way. See the following code that was presented:

Application.Calculation = calcState

That resets Excel's Calculation mode to the state it was found in (calcState being the variable). And, as noted here:

http://www.decisionmodels.com/calcsecretsh.htm

"Resetting calculation to xlCalculationAutomatic will trigger a recalculation."

Also, as you can see, there's a variety of ways to recalc. in a controlled, one-time manner, vs. doing it over and over again in your procedure, which really is part of the point.

"Other items , like screen updating sure makes an app run faster, but clients like to see movement and so you need to make sure the status bar reflects what's going on...."

It's my personal opinion that if people spent more time optimizing their code and less time building shiny status bars and code that generates a ton of screen redraw, there wouldn't be nearly as much need to do that; as your code would be so fast, it wouldn't matter.

Take the following:

http://www.utteraccess.com/forums/showthreaded.php?Number=1146152

That moves 260,000 records (520,000 fields) from Access to Excel in 9 seconds, and a decent chunk of that time is chewed up by saving (writing to the disk). I haven't tested it, but we could be talking 5-7 seconds to move said data and make visible.

I don't know many average end-users that would have a panic attack in that period of time.

Nice to meet you the other week, Chad, thanks for your time. :)

Best,

Nate Oliver

Microsoft Excel MVP

re: Excel VBA Performance Coding Best Practices

Shasur — Thu, 19 Mar 2009 09:59:33 GMT

Thanks for the Nice post:)

I think 'for each' would also improve the performance

Dim oWS As Worksheet

For Each oWS In ActiveWorkbook.Sheets

instead of

For i1 = 1 To ActiveWorkbook.Sheets.Count

Set oWS = ActiveWorkbook.Sheets(i1)

Next i1

and also

For Each oCEll In oWS1.Range("L3:L100") etc

Excel VBA Performance Coding Best Practices

Griselda Taborda — Mon, 23 Mar 2009 12:41:36 GMT

Hi there

the "Read/Write Large Blocks of Cells in a Single Operation" piece of code is magnificent.

I've been running several loops in my macros, which were taking over 1 minute to run...they now take less than 5 seconds !!!!!

Wow !!!!!!

Thanks again for such great piece of work

re: Excel VBA Performance Coding Best Practices

Rick Wargo — Mon, 23 Mar 2009 16:40:46 GMT

Beware: even with Calculation set to manual, using multiple combo boxes on different sheets, all pointing to the same LinkedCell will temporarily turn calculation to automatic.

Actually; I think this may be a bug and would love to see a solution (or workaround). I've posted about it at http://www.mrexcel.com/forum/showthread.php?t=378549

re: Excel VBA Performance Coding Best Practices

Nate Oliver — Tue, 24 Mar 2009 05:59:48 GMT

"I think 'for each' would also improve the performance

Dim oWS As Worksheet

For Each oWS In ActiveWorkbook.Sheets"

Hi Shasur, have you tested this - in terms of performance? I haven't as it seems like micro-optimization to me.

But along the same lines, you can iterate through an Array with a For Each syntax against the elements, but it's quite a bit faster to go For i = LBound(Array) to UBound(Array), in my limited testing. In my experience a For Each iteration provides a simpler syntax, but not faster. (in this case, however, retrieving the Count might be a littler slower)

Also be careful, you're declaring your Variable as a Worksheet and iterating through Sheets - those are two different Collections within the Workbook. A Sheet could be anything, a Worksheet, A Chart Sheet, A Dialog Sheet, an old Macro Sheet...

If you want to iterate through Worksheets, only, you want something like:

For each ws in ThisWorkbook.Worksheets

Versus Sheets.

Regards,

Nate Oliver

Microsoft Excel MVP

re: Excel VBA Performance Coding Best Practices

Nate Oliver — Tue, 24 Mar 2009 22:07:26 GMT

Quick follow-up, I don't think one can use "For Each is faster" as a rule of thumb.

As I was saying last night, take the following:

'--------------------

Sub foo()

Dim varEle As Variant, varArr() As Variant

Let varArr = [{1,2,3}]

For Each varEle In varArr

Debug.Print varEle,

Debug.Print

End Sub

Sub bar()

Dim i As Long, varArr() As Variant

Let varArr = [{1,2,3}]

For i = LBound(varArr) To UBound(varArr)

Debug.Print varArr(i),

Debug.Print

End Sub

'--------------------

I haven't retimed this, but I'm 99% certain bar() is faster than foo() - while the outcome is the same. However, both are so fast, it's not going to be noticeable.

So, I'm certainly not saying not to use For Each, it's very readable and a nice use of the OM. But, it seems to me that your biggest optimization gains are going to occur within that iteration, not the iteration itself.

And a different thought on Chad's code, here:

Application.ScreenUpdating = screenUpdateState

Application.DisplayStatusBar = statusBarState

Application.Calculation = calcState

As I mentioned here, in a thread about writing R1C1 functions to a Range in a single go:

http://www.mrexcel.com/forum/showthread.php?t=379262

The point in temporarily disabling screen redraw is to minimize that process to a single occurrence, at the end of your procedure.

So, conceptually, the order of operations should be changed, so that ScreenUpdating being reset to True should be done after you've reset the StatusBar and Calculation Mode, i.e.,

Application.DisplayStatusBar = statusBarState

Application.Calculation = calcState

Application.ScreenUpdating = screenUpdateState

And using a With Statement will save you some keystrokes. More micro-optimization, but just a thought on order of operations as it's not a major burden to order this in a manner that's conceptually faster (to some extent).

Regards,

Nate Oliver

Microsoft Excel MVP

re: Excel VBA Performance Coding Best Practices

Rachmim — Thu, 26 Mar 2009 22:02:53 GMT

Hello

purchased EXCEL 2007 I want to know how to use VB with the panel's work EXCEL ie video annotations Books.

Thank you very much

Rachmim Ben Artsi

e-maile yofiel@017.net.il

re: Excel VBA Performance Coding Best Practices

Rachmim — Thu, 26 Mar 2009 22:02:58 GMT

Hello

purchased EXCEL 2007 I want to know how to use VB with the panel's work EXCEL ie video annotations Books.

Thank you very much

Rachmim Ben Artsi

e-maile yofiel@017.net.il

re: Excel 2007 VBA Enhancements

George Boynton — Fri, 10 Apr 2009 00:05:37 GMT

I previously used Excel VBA 2003 for Windows, which had some neat features such as anticipating and auto-typing properties and methods after object names, a Watch Window, and easy-to-use indent and comments tools. Then I purchased a Mac with Excel 2004 for Mac installed on it. The Mac Excel 2004 VBA has none of the nice features of the Windows 2003 VBA I just mentioned, so I am very disappointed.

Can I regain those features plus other enhancements by buying Excel 2008 for Mac , or must I buy the Excel for Windows 2007 version.

Thank you. George Boynton

re: Excel VBA Performance Coding Best Practices

Gavin Shearer — Sat, 11 Apr 2009 02:40:01 GMT

George,

My name is Gavin Shearer, and I'm a Program Manager here in Microsoft’s Macintosh Business Unit. I work on Mac Excel.

Unlike Excel 2004, Excel 2008 for Mac does not support Visual Basic (VB was removed from Office 2008 for technical reasons).

As you might imagine, we have heard from some of our customers that VB is critically important to their use of the Office, and we have publicly committed to bring VB back in our next major release of Mac Office (http://www.microsoft.com/presspass/press/2008/may08/05-13MacBU2008PR.mspx).

To your specific question: if you need VB right now, I would stick with Office 2004 or Office 2007. However, if you can wait a while, I think you’ll be pleased with what we’re delivering in our next release.

Thanks for your question!