View source for VBA Automation example

[[Category:Automation]] [[Category:Examples]]
{{Navigation|[[User Guide]]|[[COM Automation]]|altname=VBA Automation}}
{{COM Header Tabs |currentpage=4}}
----
{{TOC}}
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 [[#Using this Example|these instructions]].

== Example ==
<syntaxhighlight lang="vb">
 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 (NB After Project Load)..
  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 (i.e. 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
</syntaxhighlight>

== Using this Example ==

Follow these steps to get started with using the above Example code in a blank workbook.
#Create a a new Excel file
#Open VBA editor (i.e. "Microsoft Visual Basic for Applications").  Shortcut to do this is Alt+F11.
#Insert a new Module. Right click on "VBAProject" in the tree view and select "Insert|Module" from the context menu.
#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 [[COM_Automation#Setting_up_SysCAD_COM|register SysCAD COM]] for the correct location.
#Select Tools|References menu. Then select "Microsoft Scripting Runtime".
#Now copy and paste the full example code [[#Example|above]] into the Module (Code) window.
#Select menu "Debug|Compile VBAProject".  Code should compile without any errors.
#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.
#Ensure no copies of SysCAD are currently open.
#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".