Macro in excel is getting removed while running MATLAB

I am using MATLAB and Excel interface to run excel macro from MATLAB. I did not have any problem with this for a long time. However, recently (maybe due to some securrity or windows update), after few hours of runing the code, MATLAB fails to connect to excel and run the macro (it can happen after 2 hrs or 24 hr). Then when I check the macro in excel, I notice it has been deleted. There is no macro code available.

Also VBA macros are enabled.

The error is this:

Error using COM.Excel_Application/Run

Invoke Error, Dispatch Exception:

Source: Microsoft Excel

Description: Cannot run the macro 'Sheet1.FirstExample'.

The macro may not be available in this workbook or all

macros may be disabled.

Help File: xlmain11.chm

Help Context ID: 0

[Walter Roberson]

Are you writing to the file that contains the macro?

[Mohammad Azadi Tabar]

Yes, simple version of the code is as follow:

function [Obj] = Fun_opt (F)
e = actxserver('Excel.Application');
e.Visible = 1;
e.Workbooks.Open(fullfile(pwd,'\Aspen run CC.xlsm'));

eSheets = e.ActiveWorkbook.Sheets;
eSheet1 = eSheets.get('Item',1);
eSheet1.Activate

eActivesheetRange = get(e.Activesheet,'Range','A2');
eActivesheetRange.Value = F;

e.Run('Sheet1.FirstExample');

eRange = get(e.Activesheet,'Range','G2');
Obj = eRange.Value;

info=[F Obj];
dlmwrite('CS.txt',info,'delimiter',',','-append');


e.ActiveWorkbook.Save
Quit(e)
e.release;

end

[Walter Roberson]

I wonder if it would be more robust if you were to use .NET instead of activex ?

Answer 1

[Aditya]

Hi Mohammad,

I understand that you're facing issues with a VBA macro being deleted or not executing correctly when using MATLAB with Excel. To address this issue, you can follow these troubleshooting steps:

• Check Excel's Trust Center Settings: Ensure that Excel's Trust Center settings permit the execution of macros
• Test Macro in Excel Manually: Before running the macro from MATLAB, verify that it works correctly in Excel

Here's a sample VBA script you can use to test:

Sub FirstExample() 
' This macro writes "Hello, World!" into cell G2 of the active sheet. 
Dim ws As Worksheet 
Set ws = ThisWorkbook.Sheets("Sheet1") 

' Write the string "Hello, World!" to cell G2. 
ws.Range("G2").Value = "Hello, World!" 

' Inform the user that the macro has run successfully. 
MsgBox "Macro 'FirstExample' has run successfully!", vbInformation, "Macro Complete" 
End Sub 

• Check for File Corruption: Save the workbook under a new name and see if the issue persists.
• Monitor File Access: Make sure the Excel file isn't being accessed by multiple processes concurrently

After confirming that macros are enabled in Excel and the macro runs correctly within Excel itself, use the following MATLAB code with a “try-catch-finally” block to handle errors gracefully and ensure resources are released properly:

try 
    % Start Excel application 
    e = actxserver('Excel.Application'); 
    e.Visible = 1; 
    
    % Open the workbook 
    workbook = e.Workbooks.Open(fullfile(pwd, 'AspenRunCC.xlsm')); 
    
    % Run the macro from a module 
    e.Run('FirstExample'); 
    
    % Alternatively, run the macro from a sheet 
    % e.Run('Sheet1.FirstExample'); 
    
    % Save the workbook 
    workbook.Save(); 
    
    % Quit Excel 
    e.Quit; 
catch ME 
    disp('An error occurred:'); 
    disp(ME.message); 
    finally 
    % Release the COM object 
    if exist('e', 'var') 
        e.release; 
    end 
end 

For further reference on running Excel macros from MATLAB, please refer to the following MATLAB Central Link:

• https://www.mathworks.com/matlabcentral/answers/100938-how-can-i-run-an-excel-macro-from-matlab

Hope this helps!

[Mohammad Azadi Tabar]

Thanks Aditya,

As I mentioned I have done all this steps, and it is runing well. The problem is that after while, it removes the macro. For example, as it is an optimization code, MATLAB runs the macro for 100 times without problem, but after that, the macro is removed and consequtently MATLAB gives out the Error.