VBA

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

Excel VBA Best Practice - performance issues

I have learnt the hard way over many years ways of speeding up VBA code. Mostly these lessons have come from necessity – the old “go and have a cup of coffee while it runs” comment doesn’t always endear your code to the customer. We have in the past written thousands of lines of code that sometimes takes minutes or even 10s of minutes to run.

IMG_5310-800 Some of the quicker ways to speed things up are in turning off things in excel like calculation, screen updating and the like. Others involve using arrays in code to handle slabs of data and manipulate them in code before returning the results back to the spreadsheet. Others are little things like not selecting ranges etc before using them.

Chad Rothschiller has written a great article on Excel VBA Performance Coding Best Practices going through all these items and explaining how to optimise your code for efficiency. These are beginning tasks. There are certainly lot more complex ways to deal with some of these things but for anybody who started writing VBA by using the record macro function, as I did way back in the early 90’s these tips certainly allow you to speed up some of the very inefficient code that that method creates.

I have highlighted some of them here and added some additional notes.

jethro's picture

Cool content for the week of 21 March 2009

And once again clearing my browser of all the interesting tabs I opened this week. Not many – I have been working too hard on deadlines!

 

IMGP7023-800 Microsoft Technologies

IE8 has been released and being rolled out. I have been using the beta and RC1 for months. Time to get the real thing now. Download IE8 now.

The Windows Experience Blog has some page load time tests for IE8 versus Firefox and Chrome.

Embed your live calendar on your website or blog. Sarah Perez posts a how to.

Sarah also posts on the instructions to setup POP3 for hotmail – now available worldwide.

Some interesting comments over at Daily Dose of Excel about the future of VBA.

Doug posts up a cool excel window minimise /maximise animation piece of VBA.

 

Gadgets

Doug shows off a portable wireless hotspot in a backpack.

 

Internet

The Technium posts a treatise on the concept of building a fan base of 1,000 true fans in order to generate a living. There is a follow up with some reality checks on depending on True Fans.

Live side gives us Face book comes to Windows Live Frameit Power Toy.

jethro's picture

Excel function of the week INT

I am combining the function of the week with a request I had to write some VBA code to use it. (Note the VBA function is not always the same as the application function).

CBR001292_LoRes Here is the question I was asked:

I have two columns - One "AA" and the other "AB"
I want to fill down the column AB based on the results in "AA" but I'm
confused about how to get the individual data from AA2 into AB2 - for
example...
Dim AAcell As Range
Dim ABcell As Range
For Each AAcell In Range("AA:AA")
If IsEmpty(AAcell) Then
    ABcell.Value = ""
Else
    ABcell.Value = Int(AAcell.Value)
End Sub
I'm sure this isnt' right/correct - as it isn't working - LOL - but I'm not
sure how to put it...

Reading this through I understood that the reader wanted to be able to fill a column based on another column. If the first column had a blank cell he wanted that repeated in the new column. All other entries in the new column were to be the INTEGER of the number in the first column. I have provided the code used to make this work at the end of this article. Click Read More to continue.

jethro's picture

Using the VBA recorder and editor in Excel

I promised to start writing a series of VBA primers. This is the first one.

For many using Excel is a daily thing. From office secretaries who manage the petty cash through to financial whizzkids who monitor stocks and bonds, Excel is a very versatile tool. It also has a lot of potential for automating repetitive tasks easily by being able to create macros.

imageA macro is a simply a “recorded set of instructions”. These can be as simple as saving two different sets of print options so you don’t have to continually change them. Basically the concept of a Macro is that you can get the computer to record the key and mouse strokes you use and save them so they can be repeated. While the reality is that the macro that is recorded is written in a language called VBA (visual Basic for Applications) this is irrelevant to people who first start using it.