If it wasn’t already clear, the world of big data and analytics has brought home the point that applications can no longer function alone. Many applications must live, work, and play together seamlessly in what some folks call an “ecosystem”. Today we will see that it is very easy to use the advanced capabilities of Matlab functions when performing an analysis in Microsoft Excel.
In the Windows operating system, Matlab can run as an automation server, which is to say that, using VBA, Excel can start an instance of Matlab and send data and commands. Curiously, the Matlab documentation focuses primarily on a somewhat dated method called “late binding”, which has disadvantages in development, debugging, and runtime execution.
In late binding, VBA code declares a variable of type “Object” which is set to refer to a Matlab application object at runtime using the CreateObject function. We shall not take the time to discuss the details of early and late binding, but we can enjoy several advantages if we create a variable of type “matlab.application” so that VBA knows about the object and its methods before the code actually runs. To do this, however, we must provide VBA with a precise description of the object and what it does. We do this by providing a reference to a type library using the Tools|References… menu choice in the VBA editing environment.
Once we have set this reference, we will declare a module-level variable for Matlab and write a function to start Matlab as an automation server.
Option Explicit
Private mMatlab As MLApp.MLApp ‘ early binding requires a reference in Tools | References
Const bDebuggingMode As Boolean = True
Sub StartServer()
On Error GoTo BailOnFail
If mMatlab Is Nothing Then
Set mMatlab = New MLApp.MLApp
End If
If bDebuggingMode Then
mMatlab.Visible = 1 ‘ a visible console is useful for study and debugging
Else
mMatlab.Visible = 0
End If
Exit Sub
Exit Sub
BailOnFail:
Debug.Print Err.Description
End Sub
Matlab will start up without the familiar user interface. This is generally what we want. The user interface takes a long time to load, consumes memory, and serves no purpose if our goal is to access Matlab programmatically from Excel. The Matlab console that starts by default can, however, be very useful in our development and debugging stages. Once our code is working the console might only get in the way, so we can remove it from the screen by setting the Visible property to 0.
There are only a few tasks we need to know how to do. We must be able to send data to Matlab, we must be able to execute Matlab commands from Excel, and we must be able to evaluate Matlab functions and retrieve the results into Excel. Once we can perform these tasks, virtually all of Matlab becomes accessible to us from Excel.
The slowest part of the processing in our Excel/Matlab system will be the communication between the two applications. There we do not want to send individual data points into Matlab, we want to build an array in VBA and send it to Matlab in a single communication. One large communication of data will always be faster than many individual small communications.
Here is an example VBA sub that receives a variable name and a range of cells as an argument and copies the data into Matlab as an array:
Public Sub RangeToMatlabMatrix(MatlabVariableName As String, CellArray As Range, Optional Scope As Variant)
Dim RealArray() As Double
‘ if you are not a MatLab afficianado, best to stick to the default here
If IsMissing(Scope) Then Scope = “base”
If Scope <> “base” And Scope <> “global” Then
Err.Raise Number:=666, Description:=”Scope must be ‘base’ or ‘global'”
End If
Dim i As Integer, j As Integer
Dim rows As Integer, columns As Integer
rows = CellArray.rows.Count
columns = CellArray.columns.Count
‘ If you assign a variant pointing to an array directly to Matlab, it will appear as a set of cells rather than a Matlab matrix
‘ We must, therefore, copy the range into an actual array
‘ Remember that VBA arrays start at index 0 by default; the range given by Cells(i,j) starts at 1,1.
ReDim RealArray(rows – 1, columns – 1)
For i = 0 To rows – 1
For j = 0 To columns – 1
RealArray(i, j) = CDbl(CellArray.Cells(i + 1, j + 1).Value)
Next j
Next i
StartServer
mMatlab.PutWorkspaceData MatlabVariableName, Scope, RealArray
End Sub
We can test this sub and them use the Matlab console to confirm that the new matrix we decided to call “M” actually exists.
Sub RangeToMatlabMatrixTest01()
StartServer
RangeToMatlabMatrix “M”, Sheets(“SimpleTestValues”).Range(“A1:B6”)
End Sub
The Matlab automation server “Execute” function simply runs whatever Matlab command you supply as a string. If the command is a simply literal string, this is generally very easy. If you construct the command string by concatenating strings and VBA variables together, it is very easy to make a syntax error. In such cases it is wise to put the concatenated command string into a variable and print the variable into the VBA editor immediate window. Any syntax errors will be easier to spot when looking at the completed command.
Sub CreateHistogramTest01()
StartServer
mMatlab.Execute “x=-20:120”
mMatlab.Execute “y=50+20*randn(1, 100000)”
mMatlab.Execute “hist(y,x)”
End Sub
As you know, many Matlab functions return vectors of result values rather than a single value. Therefore, when we wish to retrieve the results of a function evaluation into Excel VBA, not only must we use the variant datatype, but we must also specify to Matlab the number of elements in this vector. Of course, we will not generally be using Matlab for a cosine evaluation, but it serves as a basic example.
Dim Out As Variant
mMatlab.Feval “cos”, 1, Out, 0#
The first argument is the name of the function to be evaluated. The second argument is the dimension of the result, and the third argument is the VBA variable into which the result(s) will be deposited. After the third argument comes the list of arguments for the function, in this case only one.
Note that you will have to be more aware of datatypes here; the zero argument cannot be an integer datatype, it must be a double, so we add the # to the literal value.
Here is a complete example Sub for calling Feval:
Sub fevalTest01()
StartServer
Dim Out As Variant
mMatlab.Feval “cos”, 1, Out, 0#
Debug.Print CStr(Out(0)) ‘ notice that the Out value is an array
End Sub
Retrieving variable values is elementary using the GetVariable method. Suppose that you succumbed to some sort of unfortunate mathematical ailment and decided you wanted a Hilbert matrix in your worksheet.
Dim vHilbert As Variant
mMatlab.Execute “H=hilb(10)”
vHilbert = mMatlab.GetVariable(“H”, “base”)
Sheets(“SimpleTestValues”).Range(“E1:N10”).Value = vHilbert
We obtain the matrix by direct assignment to a VBA variable of type variant. As usual, if the value retrieved is an array, the variant takes care of the work automatically.
Using Matlab’s sophisticated functionality from Excel is surprisingly easy. In general, it is best to keep communications to a minimum. For this reason, a complex calculation should be implemented as a Matlab function which Excel could simply call once. Repeatedly calling Matlab from Excel to implement the same functionality from the Excel side would be decidedly inefficient.
Our quick look has been just that, quick. A workbook containing the above code and additional examples is available here.
