Can I allow events in Excel while running Matlab script using spreadsheet link?

3 次查看（过去 30 天）
Brett
Brett 2018-8-13
评论： Ming Yue 2018-8-17
I am using Spreadsheet Link to run a Matlab function from Excel with VBA. The script takes a while to run and sometimes I want to stop it before it completes. I would like to be able to do this from an Excel userform.
Here is how my Excel sheet works:
  • User fills in cells in an Excel column with values to be used as inputs to a Matlab function. The same Matlab function will be called for each row in the column.
  • User clicks a command button, starting a Macro
  • The macro displays a userform, telling the user that the Matlab script is running. This is all the user sees until the Macro is finished.
  • Macro gets the first input from the excel sheet. This input is used to call a Matlab function with spreadsheet link.
  • Macro calls the Matlab function and waits for it to return a value. User can't do anything until the Matlab function returns a value. Excel is essentially frozen.
  • When a value is returned, macro goes to the next input in the excel sheet and runs the Matlab function again with this new input. It continues to do this for each row value in the Excel column.
  • When the Matlab function is finished for the last input in the excel sheet, all of the results are put in the sheet, formatted, etc. (VBA stuff)
  • Macro ends and user can go back to using Excel
The bold step is what I want to change. The Matlab function only takes about 5 seconds to run but sometimes the user asks for a few hundred iterations so it can take 30 min or so to finish them all. If the user starts the macro, there is no way to stop it without Ctrl+break or something similar.
I would like to have a "Stop" button on my userform that allow the user to stop the Matlab script from within Excel so they don't have to wait for all iterations to complete. The only thing stopping me from doing this is Matlab is the primary process while the function is running. If I put a button in the userform, clicking it does nothing while the Matlab function is running. My cursor is stuck on "waiting" until the macro is complete.
I don't necessarily need to stop the Matlab function in its tracks. I would be happy if when I press the "Stop" button, the current iteration of the Matlab function finishes, then the remaining ones are skipped. I just want to be able to cancel the remaining iterations, mid-macro.
Any help is appreciated!

请先登录，再回答此问题。

采纳的回答

Ming Yue
Ming Yue 2018-8-17
Hello Brett,
From my understanding, you have a VBA loop where some MATLAB function is called in each iteration.
A limitation of Excel is that no action is allowed while a Macro is executing. But we can stop the VBA loop by checking some condition and use “DoEvents” function to allow the condition to update while the loop is running. Consider the VBA code below:
Private m_blnLooping As Boolean
Private Sub CommandButton1_Click()
m_blnLooping = True
Set MatLab = CreateObject("Matlab.Application")
Do While m_blnLooping
DoEvents
MatLab.Execute ("x = eigs(magic(2000)*rand(2000));")
MatLab.Execute ("msgbox('Hi')")
Loop
End Sub
Private Sub CommandButton2_Click()
m_blnLooping = False
End Sub
By inserting the DoEvents function in the loop, you can click on the “CommandButton2” while the infinite loop is running. I tried the code and sometimes you need to click on the “CommandButton2” twice for Excel to respond.
Cheers
  2 个评论
Brett
Brett 2018-8-17
Thanks! Exactly what I was looking for.
The only thing I'd be worried about is it being slightly slower because I wouldn't be using Spreadsheet Link anymore. I'll compare to my existing script to see what the timing difference is.
Do you know if there is a way to do it with Spreadsheet link?
Ming Yue
Ming Yue 2018-8-17
Hi Brett,
I think you are right, this implementation could be slightly slower than normal due to the calls to "DoEvents". However, I think you can still use Spreadsheet Link following the same workflow. The implementation I posted uses the MATLAB COM service. Instead of calling MATLAB functions with the "Matlab.Execute", you should be able to call Spreadsheet Link functions.

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Data Export to MATLAB 的更多信息

标签

产品


版本

R2017b

Community Treasure Hunt

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

Start Hunting!

Translated by