Need for speed - Beyond Excel VBA
 

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)

--
-----

Need for speed - Beyond Excel VBA
 

Turn screen updating off and try again. You can get the V6 visual basic
compilerCompiler and compile the code to gain speed.

"BR" wrote:

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)

--
-----

Need for speed - Beyond Excel VBA
 

For lots of information about Excel speed and how to improve that, visit
Charles Williams' site:

www.decisionmodels.com


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"BR" wrote in message
...
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)

--
-----

Need for speed - Beyond Excel VBA
 

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,

Not sure what that means but maybe some redesign.

Regards,
Peter T



"BR" wrote in message
...
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)

--
-----

Need for speed - Beyond Excel VBA
 

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

"BR" wrote in message
...
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)

--
-----

Need for speed - Beyond Excel VBA
 

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

"Joel" wrote in message
...
Turn screen updating off and try again. You can get the V6 visual basic
compilerCompiler and compile the code to gain speed.

"BR" wrote:

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)

--
-----

Need for speed - Beyond Excel VBA
 

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" wrote:

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

"BR" wrote in message
...
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)

--
-----

Need for speed - Beyond Excel VBA
 

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.

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



"Charles Williams" wrote in message
...
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

"Joel" wrote in message
...
Turn screen updating off and try again. You can get the V6 visual basic
compilerCompiler and compile the code to gain speed.

"BR" wrote:

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)

--
-----