Linking Excel and HYSYS 1

Linking Excel and HYSYS

© 2001 Hyprotech Ltd. - All Rights Reserved. 1

3.1.4 Linking Excel and HYSYS_1.pdf

2 Linking Excel and HYSYS

Introduction
One of the most common uses of the Automation capabilities in HYSYS
is linking an Excel spreadsheet to the HYSYS program. This allows the
user to run a simulation from the Excel environment, utilizing Excel’s
presentation and mathematical capabilities.

All of the power of HYSYS can be accessed while the complexity of the
program is hidden from the user.

Learning Objectives
By completing this module, you will learn:

• The differences between early and late binding

• How to link Excel and HYSYS
• How to use the GetObject and CreateObject functions

Prerequisites
Before beginning this module, you should be:

• Familiar with the VB syntax presented in a previous module

• Familiar with the HYSYS Type Library and the VB Object
Browser

2
 Linking Excel and HYSYS 3

Early and Late Binding

Up to this point, the format of an object variable declaration has been:

Dim (or Public/Private) objVariable As Object

All object variables were declared as generic objects. This is known as

late binding; the compiler does not associate the object variable with
the object type until the program is run. This is acceptable in most
cases, but there is a better way.

The VB language allows for early binding. This means that the object
variables are declared not as generic objects, but as specific objects.
Early binding allows the compiler to associate the object variable with
its proper type at design time. The object variable declaration will look
like this:

Dim (or Public/Private) objVariable As objType

The following object variable declarations are examples of early

binding:

Dim hyCase As SimulationCase

Public hyFlowsheet As Flowsheet

Private hyStream As ProcessStream

At first glance, it may seem that early binding of the object variables will
add to the complexity of the program and increase the possibility of
errors. However, early binding the object variables should actually
reduce the errors in the program and increase the program’s operating
speed. Because of these advantages, early binding will be used in all
future programs developed in this course.
The terms early and late
binding only apply to object Early binding of the object variables in VB allows the program to use the
variables. Intellisense tool developed by Microsoft. This tool will reduce the
amount of typing needed when developing a program, and should
decrease the number of syntax errors in the program.

Early and late binding can be used together in the same program. It is a
good idea to use early binding for objects that will be used quite often

3
4 Linking Excel and HYSYS

in a program. Using late binding for an object that will be used only
infrequently will not significantly decrease the program’s speed.

Determining the Correct Object Type

At this point in the course you may have noticed that an object
variable’s name often had something to do with the type of object to
which it referred. For example, hyApp has been used to reference the
HYSYS Application and hyCase has been used to reference the HYSYS
Simulation Case.

This naming scheme is completely for the benefit of the code developer
and anyone who might be trying to read and understand the code.
Using these simple names does not help VB to associate the object with
its proper type.

It is necessary to choose the right object type to match the object

variable. For example, VB will accept the following declaration without
any error:

Dim hyStream As SimulationCase

If, however, the following line of code is added to the program, an error
will occur when the program is compiled.

Set hyStream = hyCase.FlowSheet.Streams.Item(0)

The error will occur because the declared object type (SimulationCase)
cannot hold a reference to a stream object.

Choosing the right object type to match the object variable will become
much easier as you gain experience with the HYSYS Type Library. For
now, a few examples of the most commonly used object variables will

4
 Linking Excel and HYSYS 5

be given here to help match the right object type to the object variable.

Object Variable Object Type

The HYSYS Application HYSYS.Application

The referenced HYSYS Case SimulationCase

The HYSYS Flowsheet Flowsheet

Any material or energy stream ProcessStream

Any real or hypothetical component Component

If the correct object type is not known, it is better to declare the object
generically (use late binding) rather than risk an error when the
program is compiled. Late binding must also be used whenever the
object type will not be known until the macro is run.

Important notes on early binding:

1. Early binding of object variables will result in a faster running
program.
2. Early binding allows the user to utilize the power of the
Intellisense tool when writing a VB macro.
3. Early and late binding can be used together in the same macro.
4. The greatest benefit will be seen when early binding is used on
the most frequently used object variables.
5. Before early binding is used, it is important to ensure that the
HYSYS Type Library is included in the reference list. The
reference list is accessed by choosing References from the Tools
menu in the main menu bar.

5
6 Linking Excel and HYSYS

Exercise
The following are several generic object variable declarations. Change
these declarations to specific object variable declarations. Assume, for
the purposes of this exercise, that the object variable’s name represents
the reference that the object will hold. For example, hyCase will hold a
reference to the simulation case, nothing else.

1. Dim hyCase As Object

2. Dim hyFlowsheet As Object
3. Dim hyStream As Object
4. Dim hyApp As Object
5. Dim hyOperation As Object (the operation here is a distillation
column)
6. Dim hyFluidPkg As Object
7. Dim hyBlends As Object
8. Dim hySpreadsheetCell As Object

Solution
1. Dim hyCase As ________________
2. Dim hyFlowsheet As ________________
3. Dim hyStream As ________________
4. Dim hyApp As ________________
5. Dim hyOperation As ________________
6. Dim hyFluidPkg As ________________
7. Dim hyBlends As ________________
8. Dim hySpreadsheetCell As ________________

6
 Linking Excel and HYSYS 7

Linking Excel and HYSYS

There are several ways of creating a link between Excel and HYSYS. The
CreateObject function is commonly used to start a new instance of the
desired application (HYSYS), and the GetObject function is commonly
used to connect to a running instance of the desired application. Each
of these functions must be used with the Set keyword.

The return value of these two functions depends on whether HYSYS is

running when these functions are called or not. Both CreateObject and
GetObject return a reference to a particular object. If the application in
question is HYSYS, the reference can be to either the Application or the
SimulationCase.

Application and SimulationCase are special objects in that they are the
two possible entry points for HYSYS Automation. In other words, these
objects represent doors that must be opened before other objects in
HYSYS can be accessed. The object hierarchy diagram given previously
illustrated this graphically.

The following two tables outline the various command forms, what the
function does and whether the function will reference the Application
or SimulationCase.

The vast majority of HYSYS objects are accessible through the

SimulationCase object. (The one exception is the UnitConversion-
SetManager and the objects related to it.) Therefore, it is recommended
that you become familiar with the linking functions that will reference
the Simulation-Case object.

7
8 Linking Excel and HYSYS

If HYSYS is Running:

Reference
Function Syntax Result
Returned

CreateObject ("HYSYS.Application") Attaches to the Application

running instance of
HYSYS

GetObject ( , "HYSYS.Application") Attaches to the Application

running instance of
HYSYS

GetObject ("", "HYSYS.SimulationCase) Creates a new (blank) SimulationCase

case in the running (the new blank
instance of HYSYS case)

GetObject ("", "HYSYS.Application") Attaches to the Application

running instance of
HYSYS

GetObject ("C:\hysys\mycase.hsc") Opens the named SimulationCase

If the file name is incorrect an error will occur. case in the running (the named
instance of HYSYS case)

GetObject ("C:\hysys\mycase.hsc", _ Same as Above Same as Above

"HYSYS.SimulationCase")

CreateObject _ Creates a new Application (the

("HYSYS.Application.NewInstance") instance of HYSYS new instance of
HYSYS)

Which three commands given above have identical

functionality when HYSYS is running?
1) ___________________________________________________
2) ___________________________________________________
3) ___________________________________________________

Write one of the commands that will open a specified

HYSYS Case and create a reference to the SimulationCase
when HYSYS is running.
_____________________________________________________
This is the command type that will be used most often in
this course.

8
 Linking Excel and HYSYS 9

If HYSYS is Not Running:

Reference
Function Syntax Result
Returned

CreateObject ("HYSYS.Application") Creates an instance Application

of HYSYS

GetObject ( , "HYSYS.Application") Generates an Error None

GetObject ("", "HYSYS.SimulationCase) Starts a new instance SimulationCase

of HYSYS, and opens (the new blank
a new case case)

GetObject ("", "HYSYS.Application") Starts a new instance Application

of HYSYS

GetObject ("C:\hysys\mycase.hsc") Starts up HYSYS and SimulationCase

If the file name is incorrect an error will occur. opens the named (the named
case case)

GetObject ("C:\hysys\mycase.hsc", _ Same as Above Same as Above

"HYSYS.SimulationCase")

CreateObject _ Creates a new Application

("HYSYS.Application.NewInstance") instance of HYSYS

Which three commands given above have identical

functionality when HYSYS is not running?
1) ___________________________________________________
2) ___________________________________________________
3) ___________________________________________________

Write out one of the commands that will start HYSYS, open
a specified case and create a reference to that case.
_____________________________________________________
This is the command structure that will be used most often
in this course.

9
10 Linking Excel and HYSYS

All of the commands given above must be used with the Set keyword. If
the command returns a reference to the Application, the syntax of the
command will be similar to:

Dim hyApp As HYSYS.Application ’uses early binding

The variable names used here
(hyApp and hyCase) are only Set hyApp = Command from the tables above
suggestions. Using names like
these allows the user to
instantly recognize what the If the command returns a reference to the SimulationCase object, the
names represent. syntax will be similar to:

Dim hyCase As SimulationCase ’uses early binding

Set hyCase = Command from the tables above

The majority of HYSYS objects are accessible through the

SimulationCase object. If the linking function (CreateObject or
GetObject) references the HYSYS Application and does not open a case
(new or otherwise) then a simulation case will have to be opened. A
simulation case can be opened using the Open function, which is a
method in the SimulationCases collection object. The syntax for this
function is given here, (if hyApp is set as the Application object):

Set hyCase = _
hyApp.SimulationCases.Open("c:\hysys\mycase.hsc")

This command will open the specified case through the hyApp object.
It must be placed after the line that contains the linking function, i.e.
the Set hyApp = ... line.

If a case is already open, it can be linked using the ActiveDocument

property of the Application Object. The syntax for this property is quite
simple:

Dim hyCase As SimulationCase ’uses early binding

Set hyCase = hyApp.ActiveDocument

’assuming that hyApp is already set as the Application object.

10
 Linking Excel and HYSYS 11

Conversely, you may want to access the Application object, but your
linking function has referenced the SimulationCase object. There is a
simple way to do this; use the command syntax as given below:

Set hyApp = hyCase.Application

This command will allow you to access the Application object from the
SimulationCase object.

There are other ways of linking Excel and HYSYS (the New keyword and
the As New statement); however, they will not be used here. More
information about the methods presented here can be obtained from
the VB on-line help.

Exercise
In this exercise, you are asked to provide two lines of code that will
complete a VB macro. When complete, this macro will open a
hypothetical HYSYS case named "knock-out.hsc" located on the root
directory of the C: drive, and display the temperature of the first stream
in that case. The macro is partially completed as follows:

Dim hyCase As SimulationCase

Dim TempVal As Double

MsgBox "The temperature of the stream is " & TempVal

Does this macro use early or late binding, or a combination

of the two? __________
What is the first missing line?
Hint: it starts with Set ....
_____________________________________________________
What is the second missing line?
Hint: it starts with TempVal = ....
_____________________________________________________

11
12 Linking Excel and HYSYS

Open File Dialog

The previous pages detailed the procedure that can be used to open
and link to an existing HYSYS case. There are problems with this
procedure.

In order to used the various commands outlined above, you must know
the name of the HYSYS case and its exact location in the directory
before you can open it.

Also, specifying the name and location of the HYSYS case in the
program code will work well if the VB program is meant to run only
with the specified HYSYS case. Using the program with another HYSYS
case will require the programmer to return to the code base and rewrite
the filename. If it is likely that the program will be used with several
different HYSYS cases, a better solution must be found.

That better solution uses the Open File Dialog. This feature is available
through Excel and allows the user to find and specify the HYSYS case
that the program will use.

This feature is called by replacing the Dim hyCase ... and Set hyCase =
... lines with the following eight lines of code.

The Dim hyCase ... is replaced by:

Dim hyCase As SimulationCase

Dim filename As String
Dim xlApp As Excel.Application

The Set hyCase = ... is replaced by:

Set xlApp = GetObject(, "Excel.Application")

filename = xlApp.GetOpenFilename
If filename <> "False" Then
Set hyCase = GetObject(filename)
End If

12
 Linking Excel and HYSYS 13

When the GetOpenFilename function is called, a window will appear

that will look something like this:

The user can now navigate the directory structure and determine which
HYSYS file they want to open.

What role does the

Set xlApp = GetObject(, "Excel.Application")
line have in the macro? What purpose does it serve?
_____________________________________________________

Could the Set hyCase = ... line in the code on the previous
page be rewritten as
Set hyCase = GetObject (filename, "HYSYS.Application")?
_________________
Why or why not?
_____________________________________________________
What about rewriting it as:
Set hyCase = GetObject (filename, _
"HYSYS.SimulationCase")? _________________

13
14 Linking Excel and HYSYS

14