2

I'm trying to gather a Ms Project tasks' durations from Matlab through a VBA function. The idea is to gather all durations in an array (variant in VBA), and then transfer this variable to matlab by calling a function. The VBA code includes a Subprocess and a function. I can invoKe the Subprocess from matlab, but it does not return any value since is a subprocess, not a function. However, when I directly invoke the function, I get an error.

The VBA code is:

Sub TsksDur()
  Dim TsksDur As Variant
  Dim x As Long        
  TsksDur = GatherTsksDur()
End Sub

    Public Function GatherTsksDur()

    Dim TsksDur As Variant
    Dim x As Long
    Dim Tsk As Task

    'Resize Array prior to loading data
      ReDim TsksDur(ActiveProject.Tasks.Count)

    'Loop through each cell in Range and store value in Array
      For Each Tsk In ActiveProject.Tasks
        TsksDur(x) = Tsk.Duration
        x = x + 1
      Next Tsk

    'Print values to Immediate Window (Ctrl + G to view)
     'For x = LBound(TsksDur) To UBound(TsksDur)
       'Debug.Print TsksDur(x)
      'Next x

     GatherTsksDur = TsksDur

The matlab code is:

    MSP_a = actxserver('MSProject.Application');
    invoke(MSP_a,'FileOpen',[out_path filesep fname fext]); % Open the Ms Project defined by fname

    TskDur=invoke(MSP_a,'Run','TsksDur'); % Runs properly but returns nothing
    TskDur=invoke(MSP_a,'Run','GatherTsksDur'); % Does not work

Anyone can help me understanding what is happening, and how to solve this problem?

Thanks, Jorge

jorge salas herranz
  • 21
  • 1
  • What is the error? From a MS Project-perspective, the VBA code is sound, although you could explicitly declare the Function to return a Variant. You could try passing in a variant [by reference](https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/procedures/passing-arguments-by-value-and-by-reference) to the Sub and have the Sub update it rather than calling the function version. – Rachel Hettinger Apr 29 '20 at 20:17
  • Hello Rachel, thanks for your answer. The error comes up when I call the VBA function (GatherTsksDur) from Matlab (TskDur=invoke(MSP_a,'Run','GatherTsksDur');). I can invoke the Sub procedure (TsksDur), which in turn calls GatherTsksDur, from Matlab, but when I try to call the Function (GatherTsksDur) directly from Matlab I get the error. – jorge salas herranz Apr 30 '20 at 08:27

0 Answers0