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:
comsolcom.comsolutil comsolcom.modelutil
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.
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")
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.
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.
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
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:
model.study()
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 model.study("std1") works. However, with VBA and LiveLink™ for Excel®, the following syntax must be used instead:
model.get_study("std1")
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.
The following VBA script extracts parameter data for parameters in the model and inserts them into the Excel® workbook.
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.
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.
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.
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) Next 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.study().Create("std1") 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 ErrorHandler: 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 ErrorHandler: 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.
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.
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 RibbonUtil.OpenLinkedModel 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 Sheets("Sheet1").Activate Range("A4").Select RibbonUtil.UpdateDefinitions ' Keep only the fifth first columns for both sweep parameters Range("G9:J10").Clear ' Update parametric sweep parameters set in A8 Range("A8").Select RibbonUtil.Sweep "std1", , True ' Enable progress bar ModelUtil.ShowProgress True ' Compute solution Model.get_study("std1").Run ' Displa plot group pg3 Model.get_result("pg3").Run Sheets("Sheet1").Range("L4").Select ' Insert graphics of plot group pg3 RibbonUtil.InsertGraphics "pg3" ' Update all numerical results in current sheet RibbonUtil.UpdateAllResults ' 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 Next 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 Sheets("Sheet2").Activate Range("D1:AB21").Delete ' 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) Next ' 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 Next Next 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)