Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Make Excel VBA faster?

Posted on 2003-03-29
7
Medium Priority
?
284 Views
Last Modified: 2012-05-04
Hi,

  I have a VBA macro in Excel that loops through many ranges. Is there a way to dramatically speed this up? Is there some way to turn updating off? Or can I open the workbook in some kind of non-visual mode using COM, will that speed things up?

  Any other performance tips?
0
Comment
Question by:jskramer
7 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 8232687
0
 
LVL 81

Accepted Solution

by:
byundt earned 660 total points
ID: 8232817
As Bruintje suggests, two good places to begin:

Application.ScreenUpdating=False     'Turns off screen updating
Application.Calculation=xlManual      'Turns off spreadsheet recalculation

If you created your macro using the macro recorder, be aware that many of the lines in the macro are probably irrelevant to your purpose. This is especially true when you use Page Setup or click around the spreadsheet to get your job done. The best approach is to look at the instructions that got recorded, figure out what is going on, then be ruthless in pruning out the deadwood.

I have also had good success bringing all of the variables into my function or sub as one big range. I then put the data into VBA variables for calculation purposes (I do engineering programming). Once the calcs are done, I return all of the results as one big block. Each time you go back and forth between Excel and VBA, you create needless overhead. The time savings for my approach get larger if you would otherwise refer to cell ranges repeatedly in loops.

Finally, pay attention to the efficiency of your algorithm. The engineering problems I work on frequently involve nested do loops and iterative calculations. The extra programming time I spend developing a better next "guess" is rewarded by quicker running code.

If your macro is a function used in several places on the worksheet, consider returning an array. The spreadsheet would have an array formula reference to the function.

Function call:
{=myFunction(inputRange)}

function myFunction(inputRange)
dim line1, line2, line3
line1=Array(xxx,yyy,zzz)
line2=Array(aaa,bbb,ccc)
line3=Array(fff,ggg,hhh)
myFunction=Array(line1, line2, line 3)
end function
0
 
LVL 3

Expert Comment

by:leojl
ID: 8233219
hello,

It sounds like you are dealing with data
that should be in an array. You can take data
from spreadsheet cells and put that in an array.
then you can do all the looping things to the array
which is much, much faster than looping thru cells.

after you do the job with the array then put the
array results into the spreadsheet cells.

leo
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:cri
ID: 8233274
See the accepted answer here: http://www.experts-exchange.com/Applications/MS_Office/Q_20421681.html

As already stated: Put the whole 'range en bloc' in an array (see example in my link) and manipulate it.
0
 
LVL 13

Expert Comment

by:cri
ID: 8233276
And: Make sure you do not use Select, especially not inside loops.
0
 

Author Comment

by:jskramer
ID: 8242906
Unfortunately I can't take advantage of arrays as my code is very strange but I can see how that would be very useful.

ScreenUpdating is what I was looking for. Turning off recalculation also sounds like a great idea.

Thanks for the other answers as well.
0
 
LVL 3

Expert Comment

by:leojl
ID: 8243515
you are welcome.
we all learn from this.
leo
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question