VBA Automation example

From SysCAD Documentation
Jump to navigation Jump to search

Navigation: User Guide -> COM Automation -> VBA Automation Example

Below is a VBA example used within Excel.

Note that it is assumed that no copies of SysCAD are open when the Macro is executed. The Macro can test for this using the function SysCAD93ProcessesRunning (commented out in the example below).

See Testing if SysCAD is open in VBA (COM Automation) for a description of this.

For detailed examples of using the example in Excel follow these instructions.

Example

Option Explicit
Public App As SysCAD93.ScdApplication ' NOTE: 9.3 syntax
Public Prj As ScdProject
Public Solver As ScdSolver
Public Tags As ScdAppTags
Public ProBal As ScdProbal
'Public Dynamic As ScdDynamic
#If VBA7 Then
'this is required to support 32 and 64 bit versions of Excel
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Function CheckProBalLicense() As Boolean
 '== Function used in main Macro Sub to test for a valid license for the project to be loaded
 Dim LicMsg As String
 Dim LicOK As Boolean
 LicOK = False
 If (App.License.InitCrypkeyOK = 0) Then
   LicMsg = "Crypkey not initialised"
 ElseIf (App.License.IsLicensed = 0) Then
   LicMsg = "Not licensed"
 ElseIf (App.License.IsDemoMode <> 0) Then
   LicMsg = "Demo mode"
 ElseIf (App.License.SolveModeLicensed(eScdLicenseSolveMode_Probal) = 0) Then
   LicMsg = "ProBal not licensed"
 ElseIf (App.License.AddOnLicensed(eScdLicenseAddOn_HeatExchange) = 0) Then
   LicMsg = "Heatexchange add-on not licensed"
 Else
   LicOK = True
 End If
 If (LicOK) Then
   'MsgBox "SysCAD: " & App.License.Description & Chr(13) & Chr(13) & "Model add ons: " & App.License.GeneralAddOnsDescription
 Else
   MsgBox "Problem with SysCAD License:" & LicMsg & ".  Exit SysCAD."
 End If
 CheckProBalLicense = LicOK
End Function
 
Sub ProBalExample()
 '== Sample Excel Macro to load and solve a SysCAD ProBal project
 '== Kenwalt : Last updated April 2020
 
 Dim PrjFolder As String
 Dim PrjName As String
 Dim s As String
 Dim Version As String
 Dim i As Long
 Dim Iter As Long
 
 '== Check if SysCAD is already open. If it is, exit now!
 'If SysCADProcessesRunning <> 0 Then
 '  MsgBox "SysCAD is open!  Please close all copies of SysCAD (and/or SysCAD processes)."
 '  Exit Sub
 'End If

 '== Set the project name to be loaded..
 PrjFolder = "C:\SysCAD138\Examples\40 Nickel\Demo Nickel Copper Project.spf"
 PrjName = PrjFolder & "\project.spj"
 
 '== check if the specified project exists..
 'Scripting object requires Tools|References "Microsoft Scripting Runtime" to be selected
 Dim fs As Scripting.FileSystemObject
 Set fs = CreateObject("Scripting.FileSystemObject")
 If Not (fs.FileExists(PrjName)) Then
   MsgBox "Project folder not found!"
   Exit Sub
 End If

 
 '----------------------------------------------------------
 '== Start SysCAD..
 Application.StatusBar = "Starting SysCAD..."
 Set App = New ScdApplication

 '== Get the version number
 Version = "SysCAD " & App.VersionNumber(0) & "." & App.VersionNumber(1) & " Build " & App.VersionNumber(2) & "." & App.VersionNumber(3)
 Application.StatusBar = "Started " & Version
 'MsgBox Version

 '== Test Build number, and Exit if Build number is less than required Build
 Const RequiredBuildMajor = 138
 Const RequiredBuildMinor = 25446
 If (App.VersionNumber(2) < RequiredBuildMajor Or App.VersionNumber(3) < RequiredBuildMinor) Then
   MsgBox Version & " is too old.  Require Build " & RequiredBuildMajor & "." & RequiredBuildMinor & " or newer!"
   Set App = Nothing 'Close SysCAD
   Exit Sub
 End If
 
 '== Test if there is a valid License ==
 If (Not CheckProBalLicense) Then
   Set App = Nothing 'Close SysCAD
   Exit Sub
 End If
 
 
 '== Open the project..
 Application.StatusBar = Version & " - Loading project..."
 Set Prj = App.OpenProject(PrjName)
 
 '== Get a reference to the objects of interest..
 Set Solver = Prj.Solver 'the general solver
 Set Tags = Prj.Tags 'all the model tags
 Set ProBal = Solver.ProBal 'the ProBal solver
   
 '== Example of setting a tag in SysCAD..
 Tags.TagValue("PlantModel.EqpDesc") = "Test"
 
 '== Example of retrieving a tag from SysCAD..
 s = Tags.TagValue("PlantModel.System.Version")
 'MsgBox "Version:" & s
 
 
 '----------------------------------------------------------
 '== Next section of code starts the ProBal solver and then loops waiting
 '   for the model to converge. The use of Sleep is important because this
 '   tells Excel to give CPU access to other applications (ie SysCAD).
 
 Const MaxLoop = 5000 'maximum number of loops before giving up
 Const MinLoop = 2 'minimum number of loops

 '== Start the ProBal solver..
 ProBal.Start
 Call Sleep(500)  ' give SysCAD a chance to get started
 
 For i = 1 To MaxLoop
   '== Wait for 1 second before checking if solved
   Call Sleep(1000)  ' makes Excel "sleep" for one second ( 1000 milliseconds )

   '== Check if solver has stopped..
   If (i > MinLoop And ProBal.IsStopped) Then
     Exit For 'ProBal solver has stopped, exit the loop
   End If

   '== Display status of progress on Excel status bar..
   Application.StatusBar = "Solving " & i
 Next i

 '== Retrieve the number of iterations to solve
 Iter = Tags.TagValue("PlantModel.Stats.Iterations")
    
 If (i = MaxLoop) Then
   ' We did not manage to solve within MaxLoop iterations
   ' Bomb out and let user know
   ProBal.Stop
   MsgBox ("Stop ProBal solver after " & Iter & " iterations.")
 Else
   ' Model solved and stopped correctly
   Application.StatusBar = "Solved"
   '== Retrieve the solve time and display as a message
   s = Tags.TagValue("PlantModel.Stats.SolveTimeDesc")
   MsgBox "ProBal solve iterations:" & Iter & "    SolveTime:" & s
 End If

 '== Save Scenario
 'Application.StatusBar = Save Scenario..."
 'Call Prj.SaveScenario("Case001")

 Application.StatusBar = Version & " - Close project and exit SysCAD..."

 '== Close the SysCAD project without saving the changes
 App.CloseProject (False) 'Important - must close project before exiting SysCAD

 '== To ensure that SysCAD exits correctly, ensure ALL referenced objects are set to Nothing..
 Set ProBal = Nothing
 Set Solver = Nothing
 Set Tags = Nothing
 Set Prj = Nothing
 Set App = Nothing
End Sub

Using this Example

Follow these steps to get started with using the above Example code in a blank workbook.

  1. Create a a new Excel file
  2. Open VBA editor (ie "Microsoft Visual Basic for Applications"). Shortcut to do this is Alt+F11.
  3. Insert a new Module. Right click on "VBAProject" in the tree view and select "Insert|Module" from the context menu.
  4. Select Tools|References menu. Then select "SysCAD 9.3 Application Library" and "SysCAD 9.3 Solver Library". When selecting them, check that the full paths for these are correct, if not you need to register SysCAD COM for the correct location.
  5. Select Tools|References menu. Then select "Microsoft Scripting Runtime".
  6. Now copy and paste the full example code above into the Module (Code) window.
  7. Select menu "Debug|Compile VBAProject". Code should compile without any errors.
  8. The example is configured to use one of the distributed example projects. This is on approximately line 61. You may need to check that the folder is correct.
  9. Ensure no copies of SysCAD are currently open.
  10. Now ready to run the VBA Macro "Sub ProBalExample()". In the code module window, click on any line in Sub ProBalExample() and then select menu "Run|Run Sub/UserForm".