copy

Wednesday, 11 September 2013



Running MATLAB Functions In Excel Using VBA


Introduction



According to Wikipedia, “MATLAB is a numerical computing environment and fourth-generation programming language”. For many engineers MATLAB is a quite useful tool that makes their everyday work easier. If you are an engineer and work on industry or academia I am sure that you have heard about it, or you might have already use it.

The motivation behind this post came from an Excel – MATLAB automation problem that one of my colleagues had the previous week. I decided to write 3 small macros that will demonstrate 3 ways of using MATLAB from VBA. So, below you will find some VBA code that:
  • Runs a built-in MATLAB function.
  • Runs a custom MATLAB function (m file) with one output.
  • Runs a custom MATLAB function (m file) with two outputs.
The idea behind the 3 macros is more or less the same; the VBA code uses the MATLAB COM Automation Server in order to execute the function. Then, the function result is being further processed using the Excel/VBA Mid, Right, Len and Find functions in order to remove unnecessary spaces and, finally, get the required output. You can find more info about the MATLAB COM Automation Server here, as well as about its available functions here.



VBA code



A. Built-in MATLAB function

The VBA code below uses the trapz function of MATLAB in order to perform numerical integration using the trapezoidal rule. So, although in Excel you might need a custom VBA function to perform numerical integration, in MATLAB environment this is done by using a simple built-in function.

Sub BuitlInFunction()
    
    '-----------------------------------------------------------------------------------------------------------------------------------
    'This macro executes a built-in MATLAB function using the MATLAB COM Automation Server.
    'After running the function the result is being further processed in order to remove unnecessary spaces and get the required value.
    
    'Here the trapz function is used as an example - trapezoidal numerical integration.
    'Z = trapz(Y) computes an approximation of the integral of Y via the trapezoidal method (with unit spacing).
    'To compute the integral for spacing other than one, multiply Z by the spacing increment (from MATLAB help).
    
    'It goes without saying that in order to use this macro you must have installed MATLAB at your computer...
    
    'Written by:    Christos Samaras
    'Date:          09/09/2013
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '-----------------------------------------------------------------------------------------------------------------------------------
       
    'Declaring the necessary variables.
    Dim Xs()    As Variant
    Dim Ys()    As Variant
    Dim i       As Integer
    Dim inpX    As String
    Dim inpY    As String
    Dim Matlab  As Object
    Dim Result  As String
    Dim temp    As String
    
    'Get the input values.
    Xs = Sheet1.Range("N5:N14")
    Ys = Sheet1.Range("O5:O14")
    
    'Transform the Xs array in the form 1,2,3...etc.
    inpX = Xs(LBound(Xs), 1)
    For i = LBound(Xs) + 1 To UBound(Xs) - 1
        inpX = inpX & "," & Xs(i, 1)
    Next i
    inpX = inpX & "," & Xs(UBound(Xs), 1)
    
    'Transform the Ys array in the form 1,2,3...etc.
    inpY = Ys(LBound(Ys), 1)
    For i = LBound(Ys) + 1 To UBound(Ys) - 1
        inpY = inpY & "," & Ys(i, 1)
    Next i
    inpY = inpY & "," & Ys(UBound(Ys), 1)
    
    'Set the MATLAB object (the COM server).
    On Error Resume Next
    Set Matlab = CreateObject("matlab.application")
    'In the case of error inform the user and exit the macro.
    If Err.Number <> 0 Then
        MsgBox "Could not open MATLAB!", vbCritical, "MATLAB Error"
        Exit Sub
    End If
    On Error GoTo 0
    
    'Execute the built-in MATLAB funciton - trapz(X, Y), where X and Y are arrays with the same number of elements.
    'The line below actually simulates the following code:
    'Result = MATLAB.Execute("trapz([0,1,2,3,4,5,6,7,8,9],[0,4,16,36,64,100,144,196,256,324])")
    Result = Matlab.Execute("trapz([" & inpX & "],[" & inpY & "])")
        
    'Remove the unnecessary spaces from the string Result.
    temp = WorksheetFunction.Substitute(WorksheetFunction.Substitute(Result, Chr(10), ""), " ", "")
    
    'Display the function result to the user.
    MsgBox "Trapezoidal Numerical Integration Result = " & Right(temp, Len(temp) - WorksheetFunction.Find("=", temp)), vbInformation, "MATLAB Result"

End Sub

B. Custom MATLAB function - one output

The next code uses a custom m file that contains a simple function that calculates the area of a trapezoid based on its main dimensions (large base, small base and height).  

Sub CustomFunctionOneOutput()

    '-----------------------------------------------------------------------------------------------------------------------------------
    'This macro executes the custom MATLAB function "TrapezoidArea" using the MATLAB COM Automation Server.
    'After running the function the result is being further processed in order to remove unnecessary spaces and get the required value.
    
    'The "TrapezoidArea" cusotm function is used for demonstration purposes.
    'It calculates the area of trapezoid using the formula Area = 0.5*(LargeBase + SmallBase)*Height.
    
    'It goes without saying that in order to use this macro you must have installed MATLAB at your computer...
    
    'Written by:    Christos Samaras
    'Date:          09/09/2013
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '-----------------------------------------------------------------------------------------------------------------------------------
       
    'Declaring the necessary variables.
    Dim LargeBase   As Double
    Dim SmallBase   As Double
    Dim Height      As Double
    Dim Matlab      As Object
    Dim mFilePath   As String
    Dim Result      As String
    Dim temp        As String
    
    'Get the input values.
    LargeBase = Sheet1.Range("O17").Value
    SmallBase = Sheet1.Range("O18").Value
    Height = Sheet1.Range("O19").Value
    
    'Set the MATLAB object (the COM server).
    On Error Resume Next
    Set Matlab = CreateObject("matlab.application")
    'In the case of error inform the user and exit the macro.
    If Err.Number <> 0 Then
        MsgBox "Could not open Matlab!", vbCritical, "Matlab Error"
        Exit Sub
    End If
    On Error GoTo 0
    
    'Specify the location of the m file that will be used. If the file is in a location different
    'than the current workbook you can use the full path, for example:
    'mFilePath = "C:\Users\Christos\Desktop"
    mFilePath = ThisWorkbook.Path

    'Load the m file in MATLAB.
    Matlab.Execute ("cd('" & mFilePath & "\')")
    
    'Execute the custom function.
    Result = Matlab.Execute("TrapezoidArea(" & LargeBase & "," & SmallBase & "," & Height & ")")
    
    'Remove the unnecessary spaces from the string Result.
    temp = WorksheetFunction.Substitute(WorksheetFunction.Substitute(Result, Chr(10), ""), " ", "")
    
    'Display the function result to the user.
    MsgBox "Trapezoid Area = " & Right(temp, Len(temp) - WorksheetFunction.Find("=", temp)), vbInformation, "MATLAB Result"
    
End Sub

C. Custom MATLAB function - two outputs

Finally, the last code uses another custom function to transform Cartesian coordinates (x, y) to polar (radius, theta angle).

Sub CustomFunctionTwoOutputs()

    '-----------------------------------------------------------------------------------------------------------------------------------
    'This macro executes the custom MATLAB function "CartesianToPolar" using the MATLAB COM Automation Server.
    'After running the function the result is being further processed in order to remove unnecessary spaces and get the required values.
    
    'The difference compared with the previous macro is that the output of the MATLAB function has two variables (radius and theta).
    'So, the "CartesianToPolar" function transforms the Cartesian coordinates (x,y) to polar (r, รจ) using the formulas
    'r = sqrt(x^2 + y^2) and theta = atan2(y,x). CartesianToPolar" is also used for demonstration purposes.
    
    'It goes without saying that in order to use this macro you must have installed MATLAB at your computer...
    
    'Written by:    Christos Samaras
    'Date:          09/09/2013
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '-----------------------------------------------------------------------------------------------------------------------------------
       
    'Declaring the necessary variables.
    Dim x           As Double
    Dim y           As Double
    Dim Matlab      As Object
    Dim mFilePath   As String
    Dim Result      As String
    Dim temp        As String
    Dim Radius      As Double
    Dim Theta       As Double

    'Get the input values.
    x = Sheet1.Range("N23").Value
    y = Sheet1.Range("O23").Value
    
    'Set the MATLAB object (the COM server).
    On Error Resume Next
    Set Matlab = CreateObject("matlab.application")
    'In the case of error inform the user and exit the macro.
    If Err.Number <> 0 Then
        MsgBox "Could not open Matlab!", vbCritical, "Matlab Error"
        Exit Sub
    End If
    On Error GoTo 0
    
    'Specify the location of the m file that will be used. If the file is in a location different
    'than the current workbook you can use the full path, for example:
    'mFilePath = "C:\Users\Christos\Desktop"
    mFilePath = ThisWorkbook.Path

    'Load the m file in MATLAB.
    Matlab.Execute ("cd('" & mFilePath & "\')")
    
    'Execute the custom funciton (two outputs here, a and b).
    Result = Matlab.Execute("[a,b]=CartesianToPolar(" & x & "," & y & ")")
    
    'Remove the unnecessary spaces from the string Result.
    temp = WorksheetFunction.Substitute(WorksheetFunction.Substitute(Result, Chr(10), ""), " ", "")
    
    'Find the output values in the string temp using Mid, Right, Len and Find functions.
    With WorksheetFunction
        Radius = Mid(temp, .Find("=", temp) + 1, .Find("b", temp) - .Find("=", temp) - 1)
        Theta = Right(temp, Len(temp) - .Find("b", temp) - 1)
    End With
    
    'Display the function result to the user.
    MsgBox "Polar Coordinates:" & vbNewLine & "Radius = " & Radius & vbNewLine & "Theta = " & Theta, vbInformation, "MATLAB Result"
    
End Sub

The selected MATLAB functions are probably quite simple. However, they were selected only for demonstration purposes. If you understand the concept behind the macros, it is quite easy to adapt the code according to your own needs.



Download it from here



Download

The zip file contains the two MATLAB functions (m files) as well as an Excel workbook that contains the VBA code presented above. The workbook can be opened with Excel 2007 or newer. Please enable macros before using it. In order to run properly the 3 macros you must have installed MATLAB at your computer.

Did you like this post? If yes, then share it with your friends. Thank you!


Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info