Automate EbsScript edits in a suite of models
Experts: use Excel VBA to find and edit EbsScript code. Yes, really!
This is a long post. In case of TLDR, here’s a 3-minute video. Keep reading if you want to know how the magic works!
The Problem
Suppose you have some code in a Comp 93 (Kernel Scripting), or a pre- or post-run Macro EbsScript, or maybe in a EADAPT specification in some component.
What if you need to make a change to that code to fix a bug or make some other improvement? If that code was in just one place in one model, you would just edit it manually, of course.
But what if you have MANY instances of that code in your model?
… and you have a LOT of models to maintain
… and you don’t know which models have the things that need changes.
… and you don’t know where those components are within those models?
This would be quite difficult to do manually.
Example
There’s some EbsScript code in some Component 93s that looks at a calculated mass flow, and if it’s zero or negative, forces it to 1e-6:
if (rM6 < 1e-6) then rM6:=1e-6;
There’s nothing wrong with that, but when testing for and fixing invalid values in this way, I like to have a “ksSignalNotConverged(true)” statement, which will prevent the model from converging in this “forced good” state.
I want that one line of code to be become:
if (rM6 < 1e-6) then
begin
ksSignalNotConverged(true) ;
rM6:=1e-6;
end ;
The Solution
Introduction
You can use Excel VBA code do this by using “EbsOpen”. From Ebsilon’s Help pages:
EbsOpen is a comprehensive COM class library that offers access to all of the application, model, stream and component data in EBSILON Professional… EbsOpen provides over 200 classes with 3,500 methods and properties that can be used to read, run, edit and interact with EBSILON and EBSILON models.
You could use a platform other than Excel VBA to access EbsOpen such as c++, C#, or python, but Excel VBA is easy, available, and works very well for this task.
Requirement: Attach EbsOpen to your code
From the Excel VBA editor: Tools… References, then scroll down the list looking for the version of “EbsOpen EBSILON Professional-TypeLib x.xx” that matches the version of Ebsilon that your models are saved with.
If you don’t see EbsOpen on this list, hit the Browse button, then look for “EbsOpen.tlb” in the Program Files\Ebsilon folder.
The full VBA Code
Download the Excel file “EbsOpen + VBA example 1.xlsm” from my Ebsilon Tips OneDrive. and open it in Excel on your computer.
The code, explained
I’ll discuss the key parts of the code now, starting with some of the declarations
These three lines declare variables as objects in the EbsOpen library. “app” is the Ebsilon program itself. “model” is an Ebsilon model, and “cmp93” will be used for each Comp 93s we will look at.
Dim app As EbsOpen.Application
Dim model As EbsOpen.model
Dim cmp93 As EbsOpen.Comp93
This next line is how you launch Ebsilon. It is created as an Object (so it needs the “Set”). Note that this will be a completely new Ebsilon session — it does not do anything with the Ebsilon windows you might already have running.
Set app = New EbsOpen.Application
This line makes this new Ebsilon instance visible. It’s not necessary (Ebsilon can run “invisibly”), but I like seeing this process run, and also will want to check if this automated editing does what I want.
app.Visible = True
The next lines go through the list of models (with full paths) given in the Excel sheet (in the column having the name “ModelsToProcess”). The variable “r” is defined as a Range. The Intersect makes sure we don’t go through EVERY cell in the entire column. In the file attached to the post, there is only one model, but you can list as many as you want.
It checks if the last 4 characters in the text are “.ebs” before trying to open the model.
For Each r In Intersect(ActiveSheet.UsedRange, Range("ModelsToProcess"))
If Right(r.Value, 4) = ".ebs" Then
path = r.Value
Now we tell “app” to open the model in the current cell. If this statement fails, “model” will be “Nothing”, which allows us to do an error check and terminate if the file can’t be opened.
Set model = app.Open(path, True)
If model Is Nothing Then
MsgBox (path & " could not be opened! Check the path and model name")
End
End If
“OldCode” and “NewCode” were declared as strings and hold the source code I want to replace and the edited version of that code. “indent” was declared as a string constant holding 4 spaces and allows the new code to line up nicely after the edit is made (it’s not necessary, but I like readable code).
OldCode = "if (rM6 < 1e-6) then rM6:=1e-6;"
NewCode = "if (rM6 < 1e-6) then" & vbCrLf & _
indent & "begin" & vbCrLf & _
indent & indent & "ksSignalNotConverged(true) ;" & vbCrLf & _
indent & indent & "rM6:=1e-6;" & vbCrLf & _
indent & "end ;"
So now we have a model on the screen, and we know what the code is we need to look for (and what to change it to). Now we need a list of all the Comp 93s in the current model. The “getObjects2” method does that (the two True arguments mean we’re really sure we want Comp 93s, and we want to look WITHIN all macro layers).
We’re using the “cmp93” variable to loop through the list of Comp93s found by the getObjects2 method.
For Each cmp93 In model.getObjects2( epObjectKindComp93, True, True )
Now we get the entire contents of the cmp93’s Kernel EbsScript code and put it into the variable “txt” (declared as a String):
txt = cmp93.KernelEbsScript.Value
If the OldCode was found in this cmp93’s EbsScript, we do the edit by replacing the OldCode with the NewCode:
If InStr(txt, OldCode) Then
Debug.Print " Editing code in "; cmp93.Name
txt = Replace(txt, OldCode, NewCode)
Now “txt” holds the full EbsScript code with the modification. This next line puts the updated code into this cmp93:
cmp93.KernelEbsScript.Value = txt
An “End If” and “Next cmp93” cycle us through the remaining Component 93s.
We done editing! All Component 93s having the old code have been updated in the current model. The VBA code now has a message pop up asking if you want to save the model or not (using the “model.Save” method). Finally, it closes the model with the “model.Close” method (we want to close it whether or not we made changes).
This mesage prompt is nice to do during development. It gives you a chance to open some Comp 93s to see if the edits were done correctly — yes, you can do that while the VBA code is paused.
' Pause so you can review the modifications
ans = MsgBox("Save model? (Cancel to terminate)", vbYesNoCancel + vbQuestion)
Select Case ans
Case vbYes
model.Save
Case vbCancel
End
End Select
model.Close
Ultimately, if you have many models to update, and were sure the code was was working correctly, you can eliminate the MsgBox. If you do, you should use a flag variable so you Save the model only if OldCode was found — you should not Save every model. If you save only the models with edits, you’ll be able to look at the file “Date Modified” and see how many models got updated.
Other changes you can automate
In future posts, I’ll describe how to
make similar edits to Macros (which can contain KernelScript code in the same manner as a Comp 93).
edit Macro pre- and post-run EbsScripts
Make changes to a macro’s list of Specification and Result variables
Add, remove, or change Charlines.
Final Thoughts
You can do this sort of thing from an EbsScript, and thus remain fully within the Ebsilon environment. The Ebsilon Object model is invoked in an EbsScript by having
uses EbsOpen ;
at the top of the code. I’ve tried this and found it very difficult. I’ve been able to discover syntax for some functions in other standard “interface units” by look at their definition files from the EbsScript editor via
but the EbsOpen definitions are 73,000 lines long, with syntax that I find confusing.
I find the VBA editor environment MUCH easier:
The Object Browser (hit F2 to open it) lets you view and search the EbsOpen library’s objects and find properties and methods.
Even nicer is the VBA IntelliSense feature, which shows you what properties and methods are available for the object as you type code. For example, here’s the “model” object”:
This is EXTREMELY useful. I often can’t remember the syntax of something (or wonder if some sort of method is possible), so I just scroll through the list until I find what I’m looking for. I also learn more about the objects by looking at the list.
That’s how I found that to make the “getObjects2” method look for Component 93s. I wasn’t sure what the first argument should be, but I saw “epObjectKindComp93” in the list:
IntelliSense to the rescue!