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: [email protected]
'site: https://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: [email protected]
'site: https://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:UsersChristosDesktop"
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: [email protected]
'site: https://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:UsersChristosDesktop"
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
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.
