Can MATLAB parallel computing be used to speed up an Excel VBA loop which calculates, for each of many model points, a complex calculation formulated in an Excel worksheet?

4 次查看(过去 30 天)
An existing process uses only Excel and for each model point it takes about one second to perform a complex calculation which is specified in an Excel worksheet, and involves formulas in hundreds of Excel cells. Excel VBA is used just to sequentially loop through each of the many thousands of model points. At the end, the results of each model point are displayed in a results worksheet, as well as the combined sum of all the model point results. Can MATLAB parallel computing be used with Excel to make this process much faster? Cheers
  3 个评论
Tim Berry
Tim Berry 2021-7-25
Many thanks DB. I can imagine that approach working well if the calculation formulas are contained within MATLAB. However, there can been many formulas in the Excel worksheet. For example imagine an Excel calculation using say 10 input cells, and formulas in 1000 rows and 20 columns, and that 1000x20 range representing the output for that model point. I wonder if it would be a complex challendge to codify the Excel calculation in MATLAB.
dpb
dpb 2021-7-25
Depends on just what it is, obviously. Most of the time in my experience, the multiple cells in Excel are simply it's form of array processing in that one has to duplicate the cell to hold the data or the formula to reference the data and its output that is the content of the cell.
In ML if one starts with the inputs as arrays, then those same output cells are the result of a vectorized calculation in MATLAB.
This obviously isn't always so, but I'd think worthy of thinking about in a perusal of what the spreadsheet actually does.
It could, of course, lead to a nightmare; I've also seen such that were truly incredible edifices that anybody would have thunk to have built...the functionality could have been written from a specification document, but to translate from the Excel spreadsheet implementation instead would be nightmare -- one would instead have to go at from a base design document.

请先登录,再进行评论。

采纳的回答

Jason Ross
Jason Ross 2021-7-28
Overy complex spreadsheets can be nightmares to debug and maintain. At some point if you could get the thing running to call out to Parallel Server, you would end up with adding complexity to an already complex document. Getting the complex formulas out and separating the data from the code would be a huge step forward, but also would require a decent amount of work and verification that the results are correct.
Since this sounds like it's an all Excel/MS/VB setup already, you might be able to benefit from using Microsoft HPC Server. They do have a plug-in for Excel, which allows the computations to be farmed out to a cluster -- but you would still need to do some refactoring.
I have a feeling, though, that the best long term fix is going to be to get the complex logic out of the spreadsheet where it's hard to debug and into a programming language that's easier to understand ... you can still use spreadsheets as inputs and outputs, but things like iteration, cycling through values, and producing output are much easier in a programming language. You can also version control the code and set up regression tests to ensure the model is working like it should be.
  1 个评论
Tim Berry
Tim Berry 2021-7-28
Thanks Jason, that's a really helpful reply. Yes, Microsoft HPC could be one option, but probably better to get the spreadsheet formulas into a programming language. I had expected this would the case and it is very helpful to get this reply as it makes me think I'm not missing something obvious. Much appreciated.

请先登录,再进行评论。

更多回答(0 个)

类别

Help CenterFile Exchange 中查找有关 Data Import from MATLAB 的更多信息

产品


版本

R2021a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by