Building COMSOL Multiphysics Models with Excel® and Visual Basic®

March 15, 2017

Have you ever thought about customizing or building a model in the COMSOL Multiphysics® software that is based on data from Excel® spreadsheet software? Excel® spreadsheets are used to present, collaborate, and store data within many science and engineering applications. Using Microsoft® Visual Basic® for Applications and LiveLink™ for Excel®, we can build and control model settings and parameters within the Excel® spreadsheet software. In this blog post, we illustrate this process and go over a few examples.

Editor’s note: This blog post was updated on June 20, 2024, to reflect features and functionality available as of version 6.0 of the COMSOL Multiphysics® software.

Using LiveLink™ for Excel® with Microsoft® Visual Basic® for Applications

LiveLink™ for Excel® is an interfacing product that enables you to connect your Excel® data to COMSOL Multiphysics simulations. If you are a new user of LiveLink™ for Excel®, you can get started by reading the following documentation (accessible after installing the software):

  • Introduction to LiveLink™ for Excel®
  • LiveLink™ for Excel® User’s Guide

The Excel® spreadsheet software also provides the functionality to define and run VBA (Microsoft® Visual Basic® for Applications) from within an Excel® workbook. Although we can write the VBA scripts manually, it is also possible to generate them from an existing model using the user interface of the COMSOL Multiphysics® software. As we will see with the help of some examples, it is easy to implement the use of VBA with LiveLink™ for Excel®. We will look into a common application to retrieve and update parameters in a COMSOL Multiphysics model.

Although this functionality is useful, VBA and LiveLink™ for Excel® can be used for a lot more. We will, for example, see how it is possible to build a COMSOL Multiphysics model and define the model geometry with some basic shapes inserted in the Excel® workbook.

Note: The examples discussed here are shown with Excel® version 2019, but the process is the same in other versions.

With VBA, it is possible to interface Component Object Model (COM) components. When LiveLink™ for Excel® is installed, it also installs a COM interface component that can be used to interface with COMSOL Multiphysics®. Two essential COM objects for interfacing between a COMSOL Multiphysics server and COMSOL Multiphysics models are:


By using comsolcom.comsolutil, it is possible to start a COMSOL Multiphysics server, connect, and disconnect from the server. With comsolcom.modelutil, we can interface with COMSOL Multiphysics models.

Using the VBA Editor

We can write and edit VBA scripts in Excel® workbooks with the help of the editor that is installed with the Excel® spreadsheet software. The editor window can be accessed in a couple of different ways. For example, the editor is shown if we right-click an Excel® worksheet tab and select View Code. The editor is also displayed if we create or edit a macro. It is also possible to enable a Developer tab in the toolbar in Excel® spreadsheet software that contains buttons for accessing the editor and other development-related functionality.

Screenshot of a sheet in Excel.

Accessing COM Components in VBA

We can create dynamic instances of the comsolcom.comsolutil and comsolcom.modelutil objects in VBA with the following declaration.

Set comsolutil = CreateObject("comsolcom.comsolutil")
Set modelutil = CreateObject("comsolcom.modelutil")

Screenshot showing how to access COM components in VBA.

The advantage with this declaration is version independence. The latest installed versions of comsolcom.comsolutil and comsolcom.modelutil are used at runtime.

It is also possible to declare comsolcom.comsolutil and comsolcom.modelutil with a static COM reference using

Dim comsolutil As comsolutil
Set comsolutil = CreateObject("comsolcom.comsolutil")
Dim modelutil As  modelutil
Set modelutil = CreateObject("comsolcom.modelutil")

An advantage of using this declaration is that help will be available in VBA when using the defined types.

Screenshot showing the help options in VBA for defined types.

In order to be able to define static types for comsolutil and modelutil, we must add a COM reference to ComsolCom. We can do so by opening the VBA editor in the Excel® spreadsheet software, selecting the Tools menu, selecting References, and selecting ComsolCom for the installed version.

Screenshot demonstrating how to add a COM reference.

Start a COMSOL Multiphysics Server, Connect, and Disconnect with VBA

The following short VBA script illustrates how to start a COMSOL Multiphysics server, connect to the started server, and then disconnect from the server. The line call comsolutil.TimeOutHandle(True) applies a timeout handler that tells Excel® spreadsheet software to wait for long-running commands to return.

Set comsolutil = CreateObject("comsolcom.comsolutil")
Set modelutil = CreateObject("comsolcom.modelutil")
Call comsolutil.TimeOuthandler(True)
Call comsolutil.StartComsolServer(True)
Call modelutil.connect
Call modelutil.Disconnect

Screenshot of Visual Basic showing how to start a COMSOL Multiphysics server.

Migrating from COMSOL API for Use with Java® and Application Methods

If you have experience with the COMSOL API for use with Java® or writing code in application methods, there is a syntax difference that is good to know about. When retrieving a list of model features, for example, the syntax is similar for studies in the model. Thus, for retrieving studies in a model, the following syntax works:

However, when accessing a specific study, the syntax is different. For example, when retrieving a study with the study tag std1 with the COMSOL API for use with Java® or code in applications, the syntax"std1") works. However, with VBA and LiveLink™ for Excel®, the following syntax must be used instead:


Interface Parameters in COMSOL Multiphysics Models

A common application of VBA and LiveLink™ for Excel® is to retrieve and update parameters in a COMSOL Multiphysics model. Here, we will see how easily this can be achieved.

The following VBA script starts a COMSOL Multiphysics server, connects to the started server, loads the Electrical Heating in a Busbar Using the LiveLink™ for Excel® model from the same directory as the active Excel® workbook, solves the model with an updated length parameter, and saves the updated model with another file name.

VBA script for starting and connecting to a COMSOL Multiphysics server.

The following VBA script extracts parameter data for parameters in the model and inserts them into the Excel® workbook.

VBA script for extracting COMSOL Multiphysics model parameter data and adding them to Excel.

How to Build COMSOL Multiphysics Models with Excel® and Visual Basic®

In the next example, we create a COMSOL Multiphysics model and solve a 2D simulation using the Heat Transfer in Solids interface. The process involves defining geometry within Excel® spreadsheet software by adding a text box with some instructions, an outer temperature boundary, an inner temperature boundary, and a button for solving the simulation. When the model is solved, a results plot is inserted in the Excel® workbook. Let’s go through these steps in detail.

1. First, we create a text box with instructional text and insert it into the Excel® workbook.

Inserting a text box into an Excel workbook.

2. Then, we define a region for the simulation. We select a freeform shape and insert it in the Excel® workbook. Then, we select SimulationRegion as the name for the shape. We make the polygon editable by right-clicking on the shape and selecting Edit Polygon. Then, we edit the shape as shown below.

Editing a polygon in an Excel workbook.

3. We create an inner boundary with a higher temperature. To do so, we use an oval shape, create a circle, and insert it inside the freeform. We select HeatSource as the name for the shape. The oval shape must reside inside the SimulationRegion shape.

Creating an inner boundary in the polygon in the Excel workbook.

4. We then add a text box shape with the text Solve to use as a button. We right-click on the button, select Assign Macro, and create a new macro named Solve_Click.

5. Next, we open the assigned macro in the VBA editor and replace the content with the following script:

Option Explicit
Sub Solve_Click()
Dim node
Dim coordinates
Dim index
Dim newPolygonTable() As Double
Dim newHeatSource(1 To 2) As Double
Dim model As ModelImpl
newHeatSource(1) = Sheets("Sheet1").Shapes("HeatSource").DrawingObject.Left + (Sheets("Sheet1").Shapes("HeatSource").DrawingObject.Width / 2)
newHeatSource(2) = Sheets("Sheet1").Application.Height - (Sheets("Sheet1").Shapes("HeatSource").DrawingObject.Top + (Sheets("Sheet1").Shapes("HeatSource").DrawingObject.Height / 2))
Dim nNodes As Long
nNodes = Sheets("Sheet1").Shapes("SimulationRegion").Nodes.Count
ReDim Preserve newPolygonTable(1 To nNodes, 1 To 2)
For Each node In Sheets("Sheet1").Shapes("SimulationRegion").Nodes
    coordinates = node.points
    index = index + 1
    newPolygonTable(index, 1) = coordinates(1, 1)
    newPolygonTable(index, 2) = Sheets("Sheet1").Application.Height - coordinates(1, 2)
Set model = SetModel(newPolygonTable, newHeatSource)
Call model.get_study("std1").Run
If Not ContainsTag(model.result().tags(), "pg1") Then
    Call model.result().Create("pg1", "PlotGroup2D")
   Call model.get_result("pg1").feature().Create("surf1", "Surface")
    Call model.get_result("pg1").Label("Temperature (ht)")
    Call model.get_result("pg1").set("data", "dset1")
   Call model.get_result("pg1").get_feature("surf1").Label("Surface")
    Call model.get_result("pg1").get_feature("surf1").set("colortable", "ThermalLight")
    Call model.get_result("pg1").get_feature("surf1").set("data", "parent")
    Call model.get_result("pg1").get_feature("surf1").Run
End If
Call model.get_result("pg1").Run
Call Range("J10").Select
Dim tempPng As String
tempPng = Environ("Temp") & "\PolygonHeat" & Format(Now(), "yyyymmddhhmmss") & ".png"
Dim exportTag As String
exportTag = model.result().Export.uniquetag("export")
Call model.result().Export().Create(exportTag, "Image2D")
Call model.result().get_export(exportTag).set("plotgroup", "pg1")
Call model.result().get_export(exportTag).set("pngfilename", tempPng)
Call model.result().get_export(exportTag).Run
If Dir(tempPng)  "" Then
   Call Application.ActiveSheet.Pictures.Insert(tempPng)
    SetAttr tempPng, vbNormal
    Kill tempPng
End If
Call model.result().Export().Remove(exportTag)
End Sub
Private Function SetModel(ByRef newPolygonTable() As Double, ByRef newHeatSource() As Double) As Variant
Dim comsolutil As comsolutil
Set comsolutil = CreateObject("comsolcom.comsolutil")
Dim modelutil As modelutil
Set modelutil = CreateObject("comsolcom.modelutil")
Dim model As ModelImpl
If Not IsConnected(modelutil) Then
    Call ConnectServer(comsolutil, modelutil)
End If
If Not ContainsTag(modelutil.tags(), "PolygonHeatModel") Then
    Set SetModel = CreateModel(modelutil, "PolygonHeatModel", newPolygonTable, newHeatSource)
    Exit Function
End If
Set model = modelutil.model("PolygonHeatModel")
Call model.get_geom("geom1").get_feature("pol1").set("table", newPolygonTable)
Call model.get_geom("geom1").get_feature("c1").set("x", newHeatSource(1))
Call model.get_geom("geom1").get_feature("c1").set("y", newHeatSource(2))
Call model.get_geom("geom1").runAll
Set SetModel = model
End Function
Private Function CreateModel(ByRef modelutil As modelutil, ByRef modelTag As String, ByRef newPolygonTable() As Double, ByRef newHeatSource() As Double) As Variant
Dim model As ModelImpl
Set model = modelutil.Create(modelTag)
Call model.ModelNode().Create("comp1")
Call model.geom().Create("geom1", 2)
Call model.mesh().Create("mesh1", "geom1")
Call model.get_geom("geom1").Create("pol1", "Polygon")
Call model.get_geom("geom1").get_feature("pol1").set("source", "table")
Call model.get_geom("geom1").get_feature("pol1").set("table", newPolygonTable)
Call model.get_geom("geom1").Selection().Create("csel1", "CumulativeSelection")
Call model.get_geom("geom1").get_feature("pol1").set("contributeto", "csel1")
Call model.get_geom("geom1").get_run("pol1")
Call model.get_geom("geom1").Create("c1", "Circle")
Call model.get_geom("geom1").get_feature("c1").set("r", 0.01)
Call model.get_geom("geom1").get_feature("c1").set("x", newHeatSource(1))
Call model.get_geom("geom1").get_feature("c1").set("y", newHeatSource(2))
Call model.get_geom("geom1").Selection().Create("csel2", "CumulativeSelection")
Call model.get_geom("geom1").get_feature("c1").set("contributeto", "csel2")
Call model.get_geom("geom1").Run
Call model.get_geom("geom1").get_run("fin")
Call model.Material().Create("mat1", "Common", "comp1")
Call model.get_material("mat1").set("family", "copper")
Call model.get_material("mat1").get_propertyGroup("def").set("heatcapacity", "385[J/(kg*K)]")
Call model.get_material("mat1").get_propertyGroup("def").set("density", "8960[kg/m^3]")
Call model.get_material("mat1").get_propertyGroup("def").set("thermalconductivity", "400[W/(m*K)]")
Call model.Physics().Create("ht", "HeatTransfer", "geom1")
Call model.get_physics("ht").Create("temp1", "TemperatureBoundary", 1)
Call model.get_physics("ht").Create("temp2", "TemperatureBoundary", 1)
Call model.get_physics("ht").get_feature("temp2").set("T0", "293.15[K]+20")
Call model.get_physics("ht").get_feature("temp1").Selection().named("geom1_csel1_bnd")
Call model.get_physics("ht").get_feature("temp2").Selection().named("geom1_csel2_bnd")
Call model.get_study("std1").Create("stat", "Stationary")
Call model.get_study("std1").Run
Call model.result().Create("pg1", "PlotGroup2D")
Call model.get_result("pg1").Label("Temperature (ht)")
Call model.get_result("pg1").set("data", "dset1")
Call model.get_result("pg1").feature().Create("surf1", "Surface")
Call model.get_result("pg1").get_feature("surf1").Label("Surface")
Call model.get_result("pg1").get_feature("surf1").set("colortable", "ThermalLight")
Call model.get_result("pg1").get_feature("surf1").set("data", "parent")
Set CreateModel = model
End Function
Private Function IsConnected(modelutil As modelutil) As Boolean
'Try to access model tags. If not connected to a server this will throw an error.
On Error GoTo ErrorHandler
Call modelutil.tags
IsConnected = True
Exit Function
IsConnected = False
End Function
Private Function ConnectServer(comsolutil As comsolutil, modelutil As modelutil)
On Error GoTo ErrorHandler
Call modelutil.connect
If Not comsolutil.isGraphicsServer() Then
    MsgBox prompt:="The running COMSOL Multiphysics Server is not a graphics server. Exporting results will not work.", Buttons:=vbOKOnly, Title:="COMSOL"
End If
Exit Function
Call comsolutil.TimeOuthandler(True)
Call comsolutil.StartComsolServer(True)
Call modelutil.connect
End Function
Private Function ContainsTag(tags() As String, tag As String) As Boolean
ContainsTag = False
If (UBound(Filter(tags, tag)) > -1) Then
    ContainsTag = True
End If
End Function

6. After inputting the code, we click on the Solve button. This executes the VBA script defined in the macro and creates the model based on the shapes in the Excel® workbook. The model is solved and the graphics are inserted into the worksheet.

A COMSOL Multiphysics model is inserted into an Excel worksheet.

If the SimulationRegion shape is changed and the HeatSource shape is moved to another location inside the SimulationRegion, the model and results will be different.

It is easy to imagine how we can control and program this model based on other shapes, charts, and data in an Excel® workbook. It is also possible to extract numerical results from the COMSOL Multiphysics model and generate Excel® workbook content, e.g., for reporting purposes.

Using COMSOL LiveLink Ribbon Functions in VBA

It is possible to combine the ease of interacting with a COMSOL Multiphysics model from the LiveLink™ for Excel® ribbon with the full functionality of using the COMSOL API from VBA. This can be done with the functions found in the RibbonUtil class. For almost every button on the LiveLink™ for Excel® ribbon, there is a corresponding VBA command. The tooltips for ribbon buttons contain short snippets of VBA code that show the equivalent VBA function.

A screenshot of a LiveLink ribbon function in VBA.

The best example of how this approach reduces the complexity of accessing COMSOL Multiphysics models via VBA can be found in the busbar_llexcel.mph model from the COMSOL Application Libraries. In this model, the VBA code run by the Update button combines RibbonUtil functions with the general API to load and modify a sweep, run and update all linked results, and extract the new sweep interpolation data from the short code shown below:

Sub busbarUpdate()
Dim ModelUtil As ModelUtil
Dim ComsolUtil As ComsolUtil
Dim RibbonUtil As IRibbonUtil

Set index ModelUtil = CreateObject("comsolcom.modelutil")
Set ComsolUtil = CreateObject("comsolcom.comsolutil")
Set RibbonUtil = ComsolUtil.GetRibbonUtil

' Allow long running jobs
ComsolUtil.TimeOutHandler True
' Open linked model (if ribbon not already connected to the COMSOL server)
If Not RibbonUtil.IsConnected Then 
End If

' Create a link with the model with the tag Model in the COMSOL server
Set Model = ModelUtil.Model("Model")

' Update model parameter set in A4

' Keep only the fifth first columns for both sweep parameters

' Update parametric sweep parameters set in A8
RibbonUtil.Sweep "std1", , True

' Enable progress bar
ModelUtil.ShowProgress True

' Compute solution

' Displa plot group pg3

' Insert graphics of plot group pg3
RibbonUtil.InsertGraphics "pg3"

' Update all numerical results in current sheet

' Retreive parametric sweep data (for later formating)
Vtot = Sheets("Sheet1").Range("B10:F10").Value
For I = 0 To 4
    If Not IsEmpty(Sheets("Sheet1").Range("B9").Offset(, I).Value) Then 
    wbbLength = I + 1
    End If 
    If Not IsEmpty(Sheets("Sheet1").Range("B10").Offset(, I).Value) Then
    VtotLength = I + 1
    End If
wbb = Sheets("Sheet1").Range(Cells(9, 2), Cells(9, 2 + wbbLength)).Value
wbb = Sheets("Sheet1").Range(Cells(10, 2), Cells(10, 2 + VtotLength)).Value

' Clear Sheet2 except interpolation coordinates

' Update interpolation results
For I = 0 To wbbLength - 1
    Range("D4").Offset(, I * VtotLength).Select
    RibbonUtil.ResultsInterpolation "dset2", "ht.Qtot", "A4:C21", , "wbb", wbb(1, I + 1)

' Set cell format
Cells(1, 4) = "Qtot [W]"
Cells(1, 4).Font.Bold = True
Cells(1, 4).HorizontalAlignment = xlCenter
Cell2 = 4 + VtotLength * wbbLength - 1
Range(Cells(1, 4), Cells(1, Cell2)).Merge
For I = 0 To wbbLength - 1
    Idx = I * wbbLength
    Title = "wbb = " & wbb(1, I + 1) & "[m]"
    Cell1 = 4 + VtotLength * I
    Cell2 = 4 + (I + 1) * VtotLength - 1
    Cells(2, Cell1) = Title
    Cells(2, Cell1).Font.Bold = True
    Cells(2, Cell1).HorizontalAlignment = xlCenter
    Range(Cells(2, Cell1), Cells(2, Cell2)).Merge
    Range(Cells(2, Cell1), Cells(2, Cell2)).Borders.Weight = xlThick
    For j = 1 To VtotLength
        Idx2 = I * VtotLength + j - 1
        Title = "Vtot = " & Vtot(1, j) & "[m]"
        Range("d3").Offset(, Idx2).Value = Title
        Range("d3").Offset(, Idx2).Font.Bold = True
        Range("d3").Offset(, Idx2).Borders.Weight = xlThick
End Sub

This blog post just scratches the surface with regard to what you can do using VBA and Excel® spreadsheet software. As a user, you have access to the entire COMSOL API, which gives access to all model settings and parameters. This makes it possible for you to define any type of model and extract its data after having solved it using COMSOL Multiphysics.

Microsoft, Excel, and Visual Basic are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Oracle and Java are registered trademarks of Oracle and/or its affiliates.

Comments (0)

Leave a Comment
Log In | Registration