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

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!

Answer 1

[Ming Yue]

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

[Brett]

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]

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.