Compiling Excel VBA-code?

[Captain Haddock]

Hi,

I have developed an application in Excel 2002/XP to handle a
spreadsheet.In order to make the execution faster I want to compile the
VBA-code into mashinecode. The ”Visual Basic Editor” in Excel can only
find compiling errors, but not ”compile”. The application works fine,
but I want it faster.

- Which ”tool/s” do I need to compile code written in Excel 2002 and
2003?
- Are there any free downloads so I can try out the technique before
bying something?
- Is there any software that only compiles (I actually don’t need a
complex developing environment)?

Thanks

 

[Charles Williams]

You can compile VB code using VB6.

But unless you are doing intensive iterative mathematical calculations such
as solving PDEs or optimisations it is unlikely to run any faster: most of
the time slow vba applications are caused by the overhead of transferring
data from Excel to VBA and back.

Maybe you are already doing this, but try:
- switching off screen updating and setting excel calculation to manual at
the start of your VBA code, reverse at the end
- do not select or activate excel objects before referencing them
- get all the data you need from an excel range into a variant in one
assignment statement
- minimise the number of dots in your calls to the Excel object model using
Set or With .. End With

regards
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"Captain Haddock"

 

[John Skewes]

--
The major part of getting the right answer lies in asking the right
question...

Hi,

I have developed an application in Excel 2002/XP to handle a
spreadsheet.In order to make the execution faster I want to compile the
VBA-code into mashinecode. The â€Visual Basic Editor†in Excel can only
find compiling errors, but not â€compileâ€. The application works fine,
but I want it faster.

- Which â€tool/s†do I need to compile code written in Excel 2002 and
2003?
- Are there any free downloads so I can try out the technique before
bying something?
- Is there any software that only compiles (I actually don’t need a
complex developing environment)?

Thanks

In the VBE window click Debug -> Compile VBA Project then Save (You do NOT
get a 'Stand-alone' compilation - the compilation's always MS Office
dependent)

Follow Charles Williams tips above, for more details on optimizing VBA code
read: http://xlvba.3.forumer.com/index.php?showtopic=18

 

[Captain Haddock]

Thanks for the answer