Need for speed - Beyond Excel VBA

  • Thread starter Thread starter BR
  • Start date Start date
B

BR

My spreadsheet has grown too big, and VBA takes too long to run stress tests
on it !

Can I use Excel for deal presentation purposes only, run the algorithms
faster in another language and interface that to Excel, please suggest
directions

(My company doesn't have an IT desk, apologies if this is too basic a query)
 
Turn screen updating off and try again. You can get the V6 visual basic
compilerCompiler and compile the code to gain speed.
 
Maybe your VBA code can be optimised, sometimes that can have the effect of
speeding things up very considerably, x10, x100 fold or more is often
possible.

If the exercise is only number crunching, a C++ dll or xll might be the way
to go.
My spreadsheet has grown too big,
Click to expand...

Not sure what that means but maybe some redesign.

Regards,
Peter T
 
The VBA code is not usually the bottleneck in stress testing (unless you are
doing things cell-by-cell rather than in large range blocks), its probably
the Excel calculations that you need to optimise.

Significant gains in calculation speed are usually achieveable by optimising
the calculation bottlenecks and adapting the Calculation method to minimise
the calculation sheets/ranges etc.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com
 
Joel,

So far I have found very few cases where compiled VB6 runs any faster than
VBA: usually 99% of the time is being used in either the VB runtime (which
is the same) or in transferring data between VB and Excel (which is the
same) or in operations in Excel itself (such as Calculation of formulae
etc).

The only significant case I know of is using Automation addins for large
numbers of UDF calls in Automatic calc mode, which bypasses the VBA IDE
refresh slowdown bug.
Also in theory if you are doing very intense numeric calculations (for
example iteratively solving partial differential equations) then compiled
VB6 will be somewhat faster than VBA.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com
 
Thx all, I've got useful suggestions here. My spreadsheet models what's
called a priority of payments waterfall in the ABS/CDO world (yes some people
still are into such work to earn a living!). It's a cell-by-cell decision
tree for computations.

Today, I'll disable the screen-update, remove any redundancies & .calculate
by range/worksheet
AM Tomorrow, I'll try the VB6 compiler
PM Tomorrow, I'll start writing C++ routines for computations.

--
-----


Charles Williams said:
The VBA code is not usually the bottleneck in stress testing (unless you are
doing things cell-by-cell rather than in large range blocks), its probably
the Excel calculations that you need to optimise.

Significant gains in calculation speed are usually achieveable by optimising
the calculation bottlenecks and adapting the Calculation method to minimise
the calculation sheets/ranges etc.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com
Click to expand...
 
Hi Charles,
Also in theory if you are doing very intense numeric calculations (for
example iteratively solving partial differential equations) then compiled
VB6 will be somewhat faster than VBA.
Click to expand...

I think for some things like that, or for manipulating large amounts of data
using memory swaps, VB6 can be very significantly faster than VBA when all
the compile options turned off (faster even C++ though that might be more
due to optimization). This is more a case of "I've seen it done" rather than
"I know how to do it" !

Regards,
Peter T
 
Back
Top