Call a VBA object in Matlab with Activex server.

Hello,

I already know we can call functions/subs from a VBA module within Matlab using activex server with this command: Excel.Application.Run('function_sub',var1,var2,...,varn).

Now I want to know if that's possible for Microsoft Excel Objects (the VBA code that's contained in the sheets, not a module).

Suppose I want to run the following code contained in Sheet1 (see image above):

Function sum(val1 As Single, val2 As Single) As Single
sum = val1 + val2
End Function

How can I do that?

Thanks for your time! :)

Answer 1

[Aditya]

Hi Ajpaezm,

I understand that you are looking to execute a VBA function that is embedded within an Excel sheet object, rather than a standard module, using MATLAB's ActiveX server interface. This can indeed be done by referencing the sheet and function directly when calling the Run method of the Excel application object. Below is the step-by-step code that demonstrates how to achieve this:

% Start Excel application
e = actxserver('Excel.Application');
e.Visible = 1;

% Open the workbook. Make sure to provide the full path to the file.
workbook = e.Workbooks.Open(fullfile(pwd, 'YourWorkbook.xlsm'));

% Define the input values for the function
val1 = 10; % Example value 1
val2 = 20; % Example value 2

% Run the VBA function from Sheet1 and retrieve the result
result = e.Run('Sheet1.sum', val1, val2);

% Display the result in MATLAB
disp(['The result of the sum is: ' num2str(result)]);

% Save the workbook if needed
workbook.Save();

% Quit Excel
e.Quit;

% Release the COM object
e.release;

Make sure to replace 'YourWorkbook.xlsm' with the actual name of your Excel workbook, and provide the actual values for val1 and val2 that you wish to pass to the VBA function.

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!