A question on Matlab and Visual Basic from Excel Please Help

Hello to everyone, I want to request your help in order to solve a problem with a link between Visual Basic and Matlab. Let me explain what I need to solve.

I have an Excel file which contains a visual basic macro; this macro contains instructions for the multiobjective optimization of two distillation columns in terms of 2 objectives: Total Annual Cost and Condition Number, these values are obtained from a function in Matlab this Matlab function needs 8 input numerical arguments namely decision variables in the optimization procedures, the 8 values are obtained from a Excel sheet called: “Informacion”, So far the instructions in the link between Matlab and Visual Basic are able to take the 8 numerical values from a Excel sheet run the Matlab Function and retrieve data from this function. The main macro for multiobjective optimization demands multiple executions for the Matlab function ComP2Col=C1C2CATNCMAXminSing2014Modulo2(X) and of course to get the values from this function . However a few iterations the macro execution shows the following message:

Error 424 An object is required The error is specific on this instruction Set MatLab = CreateObject("Matlab.Application") '

Here is the code in visual basic:

Sub PO13082014()

    Dim MatLab As Object 

    Dim Result As String 

    Dim XReal(1 To 8) As Double 

    Dim XImag(1 To 8) As Double 

    Dim mFilePath       As String

    Dim i As Integer 

    For i = 1 To 8
        XReal(i) = Worksheets("Informacion").Cells(4, i).Value
    Next i
     ' Working with MATLAB
  Set MatLab = CreateObject("Matlab.Application") ' Creates the MATLAB object   
    mFilePath = ThisWorkbook.Path
    Call MatLab.PutFullMatrix("X", "base", XReal, XImag) 
    MatLab.Execute ("cd('" & mFilePath & "\')")
    Result = MatLab.Execute("ComP2Col=C1C2CATNCMAXminSing2014Modulo2(X);")
    Worksheets("Informacion").Range("I4:T4").Value = MatLab.GetVariable("ComP2Col", "base")
    Range("Informacion!U4").Select
    ER = Range("Informacion!U4").Value   
    If ER = 0 Then
If ActiveSheet.Range("C1").Value >= 0 Then
    Range("A4:AC4").Copy
    Sheets("Resultados").Select
    Variable = Range("A1").Value
    Range("B" & Variable).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 Application.CutCopyMode = False
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[3]C+2"
Range("A1") = Range("A1").End(xlDown).Offset(0, 0).Row
Sheets("Informacion").Select
End If
    End If
End Sub

Please help I do not what is wrong with the visual basic code

Thank you

Answer 1

[Geoff Hayes]

Pepe - I created a very simple VB macro in Excel as

 Sub myFunction()

    Dim MatLab As Object

    Set MatLab = CreateObject("Matlab.Application") 

    MatLab.Execute ("cd C:\Users\Geoff\Development")

 End Sub

I ran the code (in the Microsoft Visual Basic IDE) and observed the same error as you. From the menu (within the IDE), I selected Tools-->References... and looked for the Matlab Application (Version 8.3) Type Library in the list of Available References. I checked the box beside this reference and pressed OK.

I re-ran the code, and the MATLAB Command Window appeared, and did execute the above statement.

Note that the 8.3 from above is what appeared in my list; it may be different for you. I am running MATLA R2014a, Microsoft Visual Basic 6.5, and Excel 2007.

Ensure that a similar reference has been added to your VB code, and try it again!

[Pepe Vázquez]

Thank you for asking, I have already done the procedure you suggest,

 I am currently using R2011a Matlab and Visual Basic in Excel 2010.

I don´t know wether the link between VB and Matlab is posible for a situation like mine, where the Matlab function needs to handle many data.

Thank you again

[Geoff Hayes]

Pepe - I misunderstood, sorry. I now understand what you mean - repeated calls to PO13082014 was resulting in the error. It would work a handful of times, and then stop. I observed the same thing when I called myFunction from within a loop - it worked the first and/or second time, and then stopped. Though if I paused long enough in the debugger, I could "wait" for the CreateObject to work.

Is there another subroutine or function that is calling PO13082014? If so, then you can instantiate the MATLAB object in the caller, and then use GetObject in PO13082014 to get to the MATLAB instance. For example, suppose I have one module defined as

 Dim k As Integer

 Sub myFunction()

    Dim Matlab As Object

    k = k + 1

    Set Matlab = GetObject(, "Matlab.Application")

    Matlab.Execute ("x" & k & " = " & k)

 End Sub

 Sub myFunctionMain()

    Dim j As Integer

    Dim Matlab As Object

    Set Matlab = CreateObject("Matlab.Application")

    For j = 1 To 8
        myFunction
    Next j

 End Sub

In the above, myFunctionMain instantiates the MATLAB application object and then repeatedly calls myFunction which now uses GetObject to get the MATLAB application instance. (The code for k is just "filler" to create variables x1,x2,x3,... at each call just to ensure that myFunction was in fact being called 8 times.)

Perhaps this will be a little more helpful!