Visual Basic for Applications (VBA) is an application scripting language defined by Microsoft, that allows automation of processes by VBA-enabled applications. It supports Visual Basic syntax, OLE automation, and function calls for external DLLs. It is Microsoft's common application (macro) language for Access, Excel, Word, Project, and the Visual Basic programming environment. VBA is an important tool for the interaction of Pro-Designer with other Windows applications such as Excel.
Examples are given for VBA functions/subroutines that can either be called directly from an Excel cell (or another Windows application) or they can be used in another VBA function / subroutine. These scripts make use of the Pro-Designer COM functions/subroutines that are members of the Designer Type Library. These VBA script examples include:
A Variant data type variable is the data type for all variables that are not explicitly declared. You can use the Variant data type in place of any data type to work with data in a more flexible way. More information can be found in the VBA Documentation of Excel.
In VBA the Variant data type is automatically specified if you don't specify a data type for a variable. However it is advised to declare it as such for more efficient memory use. You can use the Dim, Public, or Private statement for this as shown:
Dim var1 As Variant
When variable var1 is set to a value VBA automatically converts the data type to the proper type, i.e. String, Double, Long, Boolean, etc. However in many cases errors in the way the data was entered leads to misinterpretation of the data-type, which may result in delirious results. Therefore it is highly recommended that when you assign values to Pro-Designer variables you use the correct type.
If you are assigning a value of a variant variable from a VBA script the make sure you get it from a type variable or you cast it to the right type. This is demonstrated in the following script, which is a subroutine that can be used for demonstration purposes.
Sub Demonstrate()
Set SuperProDoc = DocumentObject()
Dim var1 As Variant
Dim var2 As Variant
Dim str As String 'declare a string datatype
Dim nVal As Integer 'declare an integer datatype
Dim bVal As Boolean 'declare a Boolean datatype
Dim dblVal As Double 'declare a double datatype
Dim dateVal As Date 'declare a date datatype
nVal = CLng(74)
bVal = True
dblVal = CDbl(5467.77)
dateVal = CDate(#12/31/2007#) ‘(December 31st, 2007)
'Set value of some variables
var1 = dblVal
SuperProDoc.SetFlowsheetVarVal VarID.AOTAvailable_VID, var1
dblVal = CDbl(var1) 'double or float
var1 = nVal
SuperProDoc.SetFlowsheetVarVal VarID.numberOfCampaigns_VID, var1
nVal = CLng(var1) 'integer or long
var1 = bVal
SuperProDoc.SetFlowsheetVarVal VarID.isNumberOfBatchesSBU_VID, var1
bVal = CBool(var1) 'boolean
var1 = dateVal
SuperProDoc.SetFlowsheetVarVal VarID.lastSavedDate_VID, var1
dateVal = Cdate(var1) 'date
'Retrieve value of variables and display in a message box
SuperProDoc.GetFlowsheetVarVal VarID.AOTAvailable_VID, var1
dblVal = CDbl(var1) 'double or float
MsgBox (dblVal)
SuperProDoc.GetFlowsheetVarVal VarID.numberOfCampaigns_VID, var1
nVal = CLng(var1) 'integer or long
MsgBox (nVal)
SuperProDoc.GetFlowsheetVarVal VarID.isNumberOfBatchesSBU_VID, var1
bVal = CBool(var1) 'boolean
MsgBox (bVal)
SuperProDoc.GetFlowsheetVarVal VarID.longestUPName_VID, var1
str = CStr(var1) 'string
MsgBox (str)
SuperProDoc.GetFlowsheetVarVal VarID.lastSavedDate_VID, var1
dateVal = CDate(var1) 'date
MsgBox (dateVal)
End Sub
In the script above the following VBA functions were used for casting an expression to a specific data type and returning a value of that data type:
|
VBA function |
Returns a value of type |
|---|---|
|
CDbl (expression) |
Double |
|
CLng (expression) |
Long |
|
CBool (expression) |
Boolean |
|
CStr (expression) |
String |
|
CDate (expression) |
Date |
If you are setting the value of a variable through an Excel cell you can generally expect that any string you enter will be taken as a String, any False/True as a Boolean, and any number as a Double unless otherwise specified. But you can still cast the variant types variables as shown in the above script . When you pass arguments using VBA functions you can specify the type of the argument in the function statement. If you don’t specify the type of the argument in the function statement then its type is a Variant. (Look at the second example of Scripts For Procedure Variables)
Examples of general-purpose application related scripts that use the Pro-Designer application related COM methods. In order to use the Pro-Designer OLE Automation Server it is essential to create the server object and open an existing processs file file (example 1).
Example 1: Creating the Server Object and Opening a Document
Sub OpenSPDFile(spfFile As String)
Dim superProApp As Designer.Application
Set superProApp = CreateObject("SPDFlowsheet")
superProApp.ShowApp
superProApp.OpenDoc (spfFile)
End Sub
or
Sub OpenSPDFile(spfFile As String)
Dim superProApp As Designer.Application
Set superProApp = New Designer.Application
superProApp.ShowApp
superProApp.OpenDoc (spfFile)
End Sub
or
Sub OpenSPDFile(spfFile As String)
Dim superProApp As New Designer.Application
superProApp.ShowApp
superProApp.OpenDoc (spfFile)
End Sub
In this VBA example the VBA subroutine OpenSPDFile() is used for opening an existing Pro-Designer processs file file. The VBA function CreateObject("SPDFlowsheet") is used for creating and returning a reference to the Application server object. In this case SPDFlowsheet is the application type class string of the SuperPro Designer Server (use EPDFlowsheet for the EnviroPro Designer Server), and superProApp is the server object that represents an instance of the Pro-Designer application. The ShowApp() Pro-Designer COM subroutine is used to activate the Pro-Designer application and display it in its current size and position. The OpenDoc (spfFile) COM function is used for opening the Pro-Designer file and making it the active object. This function will also return a reference to a Document server object. The file name spfFile is a string and specifies the full path and name of the object to be retrieved. Alternatively one could create a new instance of Pro-Designer application and assign it to the superProApp server object (Set superProApp = New Designer.Application) or you may use the New keyword to declare and create an instance of the object (Dim superProApp As New Designer.Document) at the same time.
Example 2: Get a reference to an object provided by the Pro-Designer server.
Public superProApp As Designer.Application
Public superProDoc As Designer.Document
Sub OpenSPDFile(spfFile As String)
Set superProApp = New Designer.Application
superProApp.ShowApp
Set superProDoc = superProApp.OpenDoc (spfFile)
End Sub
or
Public superProDoc As Designer.Document
Sub OpenSPDFile(spfFile As String)
Dim superProApp As New Designer.Application
superProApp.ShowApp
Set superProDoc = superProApp.OpenDoc (spfFile)
End Sub
Similar to Example1 you could create a new instance of Pro-Designer and assign it to the superProApp server object (Set superProApp = New Designer.Document) or you may use the New keyword to declare and create an instance of the object (Dim superProApp As New Designer.Application), and also initialize the document object superProDoc by assigning it the reference of the document object from the OpenDoc COM function (Set superProDoc = superProApp.OpenDoc (spfFile). Note that the superProDoc and superProApp have been declared as global variables in all the above scripts except the last one where the application object variable superProApp is local. By using the keyword Public the objects can be used in all the procedures and modules of the VBA project.
The file name spfFile specifies the full path and name of the object to be retrieved.
In the following VBA example the VBA function GetObject() is used to return a reference to an object provided by an ActiveX component in this case the Pro-Designer case files.
Function DocumentObject(spfFile As String) As Object
Set DocumentObject = GetObject(spfFile)
End Function
Example 3: Closing the Pro-Designer file
Sub CloseSPDFile(spfFileOne As String)
Dim superProDoc As Designer.Document
Set superProDoc = GetObject(spfFileOne)
superProDoc.CloseDoc false
End Sub
In this VBA example the GetObject function is used to get a reference to the desired Pro-Designer case file, and the CloseDoc function to close this file without saving it. The file name spfFileOne specifies the full path and name of the object to be retrieved.
Sub CloseSPDFile(spfFileOne As String)
superProDoc.CloseDoc true
superProApp.CloseApp
End Sub
Here the Pro-Designer file is saved first and then closed by using the COM document subroutine CloseDoc and passing true as an argument. To close the Pro-Designer app, the application subroutine CloseApp is called with the application object superProApp. Both superProApp and superProDoc have been previously declared and defined as global variables and initialized elsewhere. See Examples 1 & 2.
Sub CloseSPDFile(spfFileOne As String)
superProApp.CloseAllDocs false
superProApp.CloseApp
End Sub
This script demonstrates how you may close all the open Pro-Designer files without saving them with the use of the application function CloseAllDocs. It also calls CloseApp to close the application. Note that both CloseAllDocs and CloseApp functions are called with the application object superProApp.
Example 1: Performing material balances and economic calculations after setting a flowsheet variable
Function SetAndGetThroughput(throughput As Double) As Double
Dim var1 As Variant
Set superProDoc = DocumentObject()
var1 = throughput
superProDoc.SetFlowsheetVarVal VarID.annualThroughput_VID, var1
superProDoc.DoMEBalances var1
superProDoc.DoEconomicCalculations
superProDoc.GetFlowsheetVarVal VarID.annualThroughput_VID, var1
SetAndGetThroughput = CDbl(var1)
End Function
The above script is an example of a function that performs several tasks. The SetAndGetThroughput function takes as an argument the desired value of the throughput. It can be called from an Excel cell or another VBA function. First the SetFlowsheetVarVal COM function is used to set the value of the throughput. Then the DoMEBalances and DoEconomicCalculations COM subroutines are called to solve the simulation case and perform economic calculations. Finally in order to check that the throughput was correctly set GetFlowsheetVarVal COM function is used to retrieve the value of the throughput from the Designer file and its value is returned with the SetAndGetThroughput function. Note that in the function statement of SetAndGetThroughput it is specified that the throughput is a Double data type and therefore you do not need to cast var1 to a Double.
Data exchange VBA script examples include the following:
● Scripts for Flowsheet variables
● Scripts For Section Variables
● Scripts For Equipment Variables
● Scripts For Equipment Contents Variables
● Scripts For Ingredient Variables
● Scripts For Operation Variables
● Scripts For Procedure Variables
● Scripts For Stream Variables
For information on how to properly use the variant data type to access the Pro-Designer COM variables look at How to Use Variant Data Types in VBA Scripts.