Trouble running an Excel VBA macro from Matlab

Hi,

I am running an optimization in excel through a macro and want to run it from matlab. I can sucessfully run the macro (name SolverMacro) from excel, but when I run it from matlab I get different values from those that I get when running it directly through excel. The macro does not have any inputs, but pastes the solution from the optimization in a chosen sheet in excel, and matlab then reads those values using the xlsread function.

The matlab code I use is:

function runOptimization()
%filename defined in master script
global excelFileName
% Create object.
 ExcelApp = actxserver('Excel.Application');
% Show window (optional).
  ExcelApp.Visible = 1;
% Open file located in the current folder.
  ExcelApp.Workbooks.Open(fullfile(pwd,excelFileName));
% Run Macro, defined in the module "SolverMacro" with no parameters.
ExcelApp.Run('SolverMacro');
% Quit application and release object.
ExcelApp.Quit;
ExcelApp.release;

end

Can anyone help understand as to why I would get different values when running a macro through excel compared to running the macro directly through excel?

The SolverMacro is (in VBA) :

Sub SolverMacro()
'**************************************************************************************
'This sub finds the optimal plateau rates for each case by first
'finding an initial seed for the optimization and then running the optimization
'using the solver tool.
'Finding an initial seed is important as the optimization is not linear, and the solver tool might return a loca minima/maxima
'if an appropriate starting point is not deifned
'*************************************************************************************

Dim sheetName As String
sheetName = "Production_profile2" 'Sheetname of production profiles
Worksheets(sheetName).Activate


Dim seeds(0 To 3), ENPVs(0 To 3) As Double   'Vector of seeds for plateau rates

For i = 0 To 3
    seeds(i) = 0  'A priori, given mean most probable ,...., given high most probable
    ENPVs(i) = 0
Next i


'Run two for loops to find seed. One for aPriori and one for the other 3 cases.
'Need two loops since appraisal programme affects discounting

'Set appraisal program to off
Range("E45").Value = 0

For plateauRate = 0.25 To 30 Step 0.25
        Range("E44").Value = plateauRate
        
        If (Range("D107").Value > ENPVs(0)) Then 'check if ENPV is higher by changing rate
            ENPVs(0) = Range("D107").Value
            seeds(0) = plateauRate
        End If

Next plateauRate


'Given appraisal
Range("E45").Value = 1

For plateauRate = 0.25 To 30 Step 0.25
        Range("E44").Value = plateauRate
        
        'Given result j, chech if ENPV is higher by changing plateau rate
        For j = 1 To 3
            
            If (Range("E107").Offset(0, j - 1) > ENPVs(j)) Then 'check if ENPV is higher by changing rate
                ENPVs(j) = Range("D107").Value
                seeds(j) = plateauRate
            End If
        Next j
        
Next plateauRate


For i = 0 To 3
   
    If i = 0 Then
        Range("E45").Value = 0
    Else
        Range("E45").Value = 1
    End If
    

    Range("E44").Value = seeds(i)
        
    ' Run solver maximising npv by changing plateuau rate
         Application.Run "SolverReset" 'reset solver
         Application.Run "SolverAdd", "$D$2", 1, "$E$2"  'add constraints
        
        'SolverOk SetCell:="$D$107", MaxMinVal:=1, ValueOf:=0, ByChange:="$E$44", Engine _
                :=1, EngineDesc:="GRG Nonlinear"
        Application.Run "SolverOk", Range("$D$107").Offset(0, i), 1, "0", "$E$44", 1, "GRG Nonlinear" 'Specify solver goal cell
        Application.Run "SolverSolve", True 'skip solver summary window
     
    Range("A184").Offset(11 * i, 0).Value = Range("E44").Value 'store optimal plateau rate in seed vector
    Range("A186").Offset(11 * i, 0).Value = Range("D107").Offset(0, i).Value
    
    Range("D185:R191").Offset(11 * i, 0).Value = Range("D17:R23").Value
    

Next i


Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.Quit

End Sub

[Guillaume]

but pastes the solution from the optimization in a chosen sheet in excel

As far as I can tell your macro only ever affects the Production_profile2 sheet and doesn't give you the option of modifying a chosen sheet.

and matlab then reads those values using the xlsread function

Well, that's a big waste of time! You'd be better off not quitting excel with the macro and just reading the values in the still open workbook directly from matlab.

Considering the macro is set to quit excel when run, is it also set to autostart when the workbook is opened? Is there any macro that runs when the workbook is opened?

Are you sure that you're passing the correct file name to your matlab function. You're passing the filename as a global variable, which is a big no-no since it makes it more difficult to track the value of the variable. There's certainly no reason why the filename could not be a normal input to the matlab function.

Other than that, there's nothing that immediately jumps out. I personally would avoid relying in the active sheet in macros, but you don't have a choice with the solver. And you could call the solver methods directly rather than relying on Application.Run but that should make no difference.

[Partik Singh]

The sheet Production_profile2 is my choosen sheet.

I removed the quitting from the macro in excel, added the xlsread in the matlab code so it reads values from an open workbook as you suggested. I still do not get the same values from the solver when I run the macro from matlab as I do from running the macro directly from excel.

I also removed the global variable and wrote the name of my file instead. I am confident that I have the right filname as I can see the window open.

I am aware of the other methods that I can use to run the solver, but as you point out, it should not affect the solution.

function runOptimization()
% Create object.
%global 
excelFileName='\RO model (9).xlsm';

sheet= 'Production_profile2';

 ExcelApp = actxserver('Excel.Application');
% Show window (optional).
  ExcelApp.Visible = 1;
% Open file located in the current folder.
  ExcelApp.Workbooks.Open(fullfile(pwd,excelFileName));
% Run Macro3, defined in the module "Module1" with no parameters and a return value.
retVal = ExcelApp.Run('SolverMacro');
a=xlsread(excelFileName, sheet,'A184')
% Quit application and release object.
ExcelApp.Quit;
ExcelApp.release;



end

Appreciate your help!

[Guillaume]

My suggestion about not quitting excel was so that you don't xlsread anything. You already have an interface to excel, with the worksheet open, so you can just get the values you want.

At the same time, my suggestion about not using global would be to pass the filename as an input, so the function would be something like:

function result = runOptimisation(excelFileName)  %inputs come from your script
    ExcelApp = actxserver('Excel.Application');
    ExcelApp.Visible = true;  %actually not needed and dangerous if the macro relies on ActiveSheet. if the user click on another sheet while the macro is running, ActiveSheet will change
    workbook = ExcelApp.Workbooks.Open(fullfile(pwd, excelFileName)); 
    ExcelApp.Run('SolverMacro');
    worksheet = workbook.Worksheets.Item('Production_profile2');
    result = worksheet.Range('A:D').Value;  %not idea what range you want. Adjust as necessary
    ExcelApp.Quit;
end

With regards to other methods to run the solver my suggestion was to replace

Application.Run "SolverReset"

by simply

SolverReset

and so on. See Using the solver VBA functions.

However, with regards to your problem, perhaps the best course of action is for your to attach a demo excel file and your matlab code, so we can see for ourselves what's going on.