In this post, I discussed how to use Excel VBA with EbsOpen to automate edits of EbsScripts found within various objects. This type of automation is useful if you have many EbsScripts to edit, possibly spread across many models.
You can also use Excel VBA with EbsOpen to make changes to the list of Specifications variables, Result variables, and even Characteristic Lines.
Get the model “Macro manipulation via EbsOpen.ebs” (which contains just a macro) and the Excel file “EbsOpen + VBA example 2.xlsm” from my Ebsilon Tips OneDrive.
The macro has just two Specification variables:
It has no Result variables.
There is one Characteristic Line, with no data:
The Excel file “EbsOpen + VBA example 2.xlsm” has VBA code to delete SPEC1, give existing SPEC2 a description, and add three new Specification variables of different types (including one with some combo-box options):
… and created some Result variables:
… and to finish the definition of that first CharLine, I gave it a name, description, data, units, and order of Interpolation and Extrapolation.
I also added a second CharLine and gave it a name and description.
The code is in the VBA module named “modMacroManipulation”. Comments describe the steps.
I’ll paste it here so you can get a quick view:
Option Explicit
Sub DoIt()
' These are enabled via a Reference to the Ebsilon TypeLib
Dim app As EbsOpen.Application
Dim model As EbsOpen.model
Dim mac As EbsOpen.Macro
Dim MacInt As EbsOpen.MacroInterface
Dim path As String
Dim r As Range
Dim ans As Variant
Dim i As Integer
Dim j As Integer
Dim CL As EbsOpen.Charline
'Create an EBSILON Instance
Set app = New EbsOpen.Application
app.Visible = True ' I like to have the model visible so I can review the changes.
' March down the list of models given in the "ModelsToProcess" range.
For Each r In Intersect(ActiveSheet.UsedRange, Range("ModelsToProcess"))
If Right(r.Value, 4) = ".ebs" Then
path = r.Value
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
' It's time to rock and roll
r.Offset(0, 1) = "Processing"
' Set the macro and the MacroInterface inside it which we will manipulate.
Set mac = model.ObjectByContext("::Macro_Object")
Set MacInt = model.ObjectByContext("::Macro_Object::MacroInterface")
Debug.Print mac.Name
Debug.Print MacInt.Name
' Let's do some stuff with Specifications
With MacInt.SpecValueTypes
' Delete the specification named "SPEC1"
' Go backwards (Step -1) because the count will get messed up when we delete.
For i = .Count To 1 Step -1
If MacInt.SpecValueTypes(i).Name = "SPEC1" Then
MacInt.SpecValueTypes.Remove (i)
ElseIf MacInt.SpecValueTypes(i).Name = "SPEC2" Then
MacInt.SpecValueTypes.Item(i).Description = "This is Specificatino 2"
End If
Next i
' Add a Specification
.Append
i = .Count
.Item(i).ChangeName "NewSpec1"
.Item(i).Description = "This is a description"
.Item(i).QuantityDimension = epDIM_bar ' make it a Pressure
' Add another Specification
.Append
i = .Count
.Item(i).ChangeName "NewSpec2"
.Item(i).Description = "This is another description"
.Item(i).QuantityDimension = epDIM_kg_s ' make it a Mass Flow
' Add another Specification
.Append
i = .Count
.Item(i).ChangeName "NewSpec3"
.Item(i).Description = "This is another description"
.Item(i).QuantityDefinition = epQuantityDefinitionCombobox ' make it a Combo Box
' Now add some options to the Combo Box
With .Item(i).FlagAlternatives
.Append: .Item(1).SetValue (10): .Item(1).SetText ("Ten")
.Append: .Item(2).SetValue (11): .Item(2).SetText ("Eleven")
.Append: .Item(3).SetValue (12): .Item(3).SetText ("Twelve")
End With
End With
' Let's do some stuff with Results
With MacInt.ResultValueTypes
' Add a Result variable
.Append
i = .Count
.Item(i).ChangeName "NewResult1"
.Item(i).Description = "This is a description"
.Item(i).QuantityDimension = epDIM_kW ' make it a Power
' Add another Result variable
.Append
i = .Count
.Item(i).ChangeName "NewResult2"
.Item(i).Description = "This is another description"
.Item(i).QuantityDimension = epDIM_K ' make it a temperature
' Add another Result variable, but put between the first and second, not at the end of the list
.Insert (2)
.Item(i).ChangeName "NewResult1A"
.Item(i).Description = "This is inserted between 1 and 2"
.Item(i).QuantityDimension = epDIM_kJ_kg ' make it an enthalpy
End With
' Let's play with Charlines
With MacInt.CharlineTypes
' Remove all existing CharLihnes
Do While .Count > 0
.Remove (1)
Loop
' Add a CharLine.
.Append
i = .Count ' The number of CharLines we now have
With .Item(i)
.ChangeName ("A_new_CharLine")
.Description = "blah blah blah"
.QuantityDimensionX = epDIM_kg_s ' Let's make it enthalpy vs. mass flow
.QuantityDimensionY = epDIM_kJ_kg
' Add some points. Note, we do this at the upper-level (where you'd manually enter
' points, not in the Macro Interface (where you design the CharLine). Hence we're using
' the "mac" object, NOT the "MacInt" object.
Set CL = mac.Charlines(i)
' Now add some x and y values
For j = 1 To 10
CL.Points.Append
CL.Points(j).X = j
CL.Points(j).Y = j * j
Next j
' Set the interpolation and extrapolation methods
CL.InterpolationType = epInterpolationTypeOrdersGiven
CL.InterpolationOrder = 2
CL.ExtrapolationOrder = 2
End With
' Add another CharLine, give it a name and description
.Append
i = .Count
.Item(i).ChangeName ("Aother_new_CharLine")
.Item(i).Description = "yada yada yada"
End With
' 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
End If
Next r
Set app = Nothing
MsgBox "Done", vbInformation
Exit Sub
ErrHandler:
MsgBox ("Unable to load EbsOpen. Please check your EBSILON License / Dongle.")
End Sub
