?
Solved

Make Excel VBA faster?

Posted on 2003-03-29
7
Medium Priority
?
281 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

765 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