Solution to speed up your marco

[JuniorASA]

Sometimes, I found that the macro run very slow especially there are many rounds to run.

Are there any common solution to speed up the marco?

Some teaches me to disable the screen update function to make it faster?

Is there any way out for this problem. Thank you for sharing for this issue!

[Gandalf]

Disabling screen updating during the macro speeds it up. Changing calculation to manual during the macro may speed it up. You can use the "Calculate" statement during the macro if you do need the sheet to calculate at certain points before proceeding. Change calculation back to automatic before ending the macro.

[Can't Barely Add]

I'd put it the other way around. A well-designed macro will not use variables, properties, and methods that make the GUI change (such as using "Activate", "ActiveSheet", etc), unless some very fast internal VBA function requires it (Paste comes to mind, if you need everything . However, no matter how many of these you get rid of, if you have a "large" set of calculations in an open workbook, Excel may rerun them all every time a value in a cell is changed. I'd amend Gandalf's recommendation to turn the calculation back to whatever state it was in before your macro was called, but that depends on whether your code will ever be used by anyone else. This can change minutes to seconds (or less).

After you do that, I'd set the option requiring you to declare all your variables and compile your code. If you're using variables that aren't declared properly, they will be implicitly declared as variants, and require intermediate steps (testing for their data type) whenever they are used. It's difficult to tell how much of an improvement compiling provides, as MS is not specific about what optimizations it provides if compiling allows the function to have more direct interaction with the spreadsheet (instead of having a high cost of communication between the VBA interpreter and the Excel application). There's probably some benefit though, and declaring your variables is just good programming practice. If nothing else, it will help you catch typos.

[Gandalf]

If I'm running macros in Excel, how do I compile the code? In the past I had a stand-alone Basic with a compiler, but didn't know that option was available via Excel.

[James Joel]

There is a "compile" menu option in VBA, but I think all it does is syntax check the code.

As far as I know, you are right, Gandalf, you'd need the "real" VB to make compiled code. With it, you could put some macro code in a COM object, but I'd think it would have to be some very processor intensive code before it would be worth the aggrevation.

[Gandalf]

Originally Posted by James Joel

There is a "compile" menu option in VBA, but I think all it does is syntax check the code.

As far as I know, you are right, Gandalf, you'd need the "real" VB to make compiled code. With it, you could put some macro code in a COM object, but I'd think it would have to be some very processor intensive code before it would be worth the aggrevation.

Captain Nemo puzzles that he expects to be solved via insight but which are only solvable by mere mortals with brute force tend to be very processor intensive. Occasionally I have work-related macros that run for a couple of hours. I don't know what the trade-offs would be, but it doesn't matter if true compiles don't exist.

[Can't Barely Add]

Originally Posted by Gandalf

Captain Nemo puzzles that he expects to be solved via insight but which are only solvable by mere mortals with brute force tend to be very processor intensive. Occasionally I have work-related macros that run for a couple of hours. I don't know what the trade-offs would be, but it doesn't matter if true compiles don't exist.

Try it and see if it provides any speed up. MS claims its compiler is better in 2003 than prior versions in the Excel VBA help menus. I don't have Excel handy. Try something simple like
function test1() as double
dim i as long, x as double, y as double
y = 5.0
for i = 1 to 1000000000
x = y
next i
test1 = y
end function

versus
function test2() as double
dim i as long, x as double, y as double
y = 5.0
x=y
for i = 1 to 1000000000
next i
test2=x
end function

versus
function test3() as double
test3=5
end function

Try running them each compiled and uncompiled. If the compiler does any optimizing, all 3 should run equally fast after compilation, but test3 faster than test2 faster than test1 if literally interpreted.

I'm pretty sure the compiler does something when you load a compiled excel workbook as an add-in, because I've gotten an error that only should arise when you call a com file. It was weird. Something like "Bad DLL call".

[Can't Barely Add]

Another piece of evidence indicating that compiles aren't mere syntax checks is that when I compile a 1.2 meg Excel file (containing only code), saving it yields something like a 1.6 meg file. If I then make a slight change and resave, it's back to 1.2 meg.
This suggests the workbook contains a bytecode version of the code at least, if not full-blown binary. Also, the "Bad DLL Call" error results from VBA checking the run-time stack after calling a function in a DLL and finding that the stack does not have the structure it would if the DLL function were declared correctly. In this case the function being called was in a regular module, but it was being called from an instance of a class module in the same workbook.

So I think compiling is doing more than a syntax check.

[Brad Gile]

http://j-walkblog.com/old/2004/06/12/index.html

An Excel Compiler

Savvysoft has released an Excel spreadsheet compiler called TurboExcel. Designed primarily for complex calculation-intensive models, they claim the product can make your spreadsheet run 300 times faster.

How does it work?

TurboExcel takes an Excel spreadsheet and outputs a DLL file which can be read and used by other systems (your original spreadsheet stays intact), including front-, middle-, and back-office systems. This new file runs as fast as if a programmer had rewritten the spreadsheet model in C++, and it's just as portable and cross-platform. That's because the file is written in C++.

I looked through the entire Web site, and found no mention of pricing. It seems that you need to contact the company to get that kind of information. Conclusion: It must be very expensive.

They do, however, offer a free trial -- but you'll need to contact them to get it.

I think I'll pass, but....

Brad

[Brad Gile]

Originally Posted by James Joel

There is a "compile" menu option in VBA, but I think all it does is syntax check the code.

As far as I know, you are right, Gandalf, you'd need the "real" VB to make compiled code. With it, you could put some macro code in a COM object, but I'd think it would have to be some very processor intensive code before it would be worth the aggrevation.

I used to do exactly this when I wanted a fast function. Write a Com dll with VB6 or, much faster, a Win32 dll with C++. VBA can call either one.

Brad