Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) -- ----- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) -- ----- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) -- ----- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) -- ----- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) -- ----- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) -- ----- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) -- ----- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) -- ----- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel - the need for speed | Excel Discussion (Misc queries) | |||
Speed of Excel | Excel Discussion (Misc queries) | |||
Speed up Excel execution | Excel Programming | |||
Can you speed UP drag speed? | Excel Discussion (Misc queries) | |||
Excel Writing speed | Excel Programming |