ProActiveX

Using Auxiliary Tools > Power Tools > ProActiveX

ProActiveX

Overview

The ProActiveX spreadsheet is designed to help you understand how to use ProModel’s ActiveX components. The worksheets and macros give clear and functional examples of how to access all currently enabled data elements.

The ProActiveX spreadsheet may be used “as is” or modified to suit your specific needs. With ProActiveX and a fundamental knowledge of Visual Basic for Applications, you can create customized user interfaces for ProModel–or let our consulting division do it for you!

ProActiveX.xls has nineteen data worksheets corresponding to the module with the same name in ProModel. Additionally, there is one hidden sheet with data for drop-down lists used on the other sheets.

Please Note: ProActiveX is only compatible with Excel 2000 or newer.

Worksheets

Drop-down lists, check boxes and text boxes

Many of the worksheets in ProActiveX have drop-down lists which make it easier to fill in the required data. In all cases, the value you select will be inserted into the selected cell or range of cells. However, the assigned macros do not prevent you from selecting a different column than the one with the drop-down used.

Using a combo box with a pre-defined list is one way to ensure consistency in the data. In most cases, the numeric value of the list selection is temporarily recorded on the hidden sheet (named Hidden Sheet). On the Simulation Options sheet, instead of filling in a cell or range, the value shows in the drop-down box itself and the numeric value is hidden in the cell behind it.

The Simulation Options sheet also contains several check boxes and a text box. The values for the check boxes are hidden in the cells behind them. The method used for hiding the values is simply to make the text color the same as the background color.

Not Enabled and Partially Enabled

A few of the worksheets have data columns defined for methods which have not yet been implemented in the ProModel Data Object. These columns have been included to acknowledge that these elements were not forgotten when ProActiveX was created–these data elements will be enabled in future releases of ProModel.

Calendar Editor Button

The Shift Assignments worksheet has a button to the far right labeled Calendar Editor. Clicking on this button will show the Calendar Editor, just like when you invoke the menu call from your default ProModel Corporation product.

Browse Button

The General Info worksheet has a Browse... button that uses the Microsoft Common Dialog to display a file selection window.

Comments

Several worksheets have comments in the cells of the title rows. These comments are included to give you important information about the data contained in that column–like limitations or formatting requirements.

Panes and Sections

In each worksheet where the data columns go beyond the right-hand border of a single screen, separate panes have been created and ‘frozen’ to allow you to scroll through the columns without moving the column that identifies the record. The title rows have also been frozen, so that the user can scroll down the sheet without losing sight of the titles.

In each worksheet, title labels spanning more than one column indicate that all the columns beneath that title pertain to that type of information. For example, on the Resources worksheet, there are several columns for downtime data which have duplicate titles, but different section labels. In some cases, the labels are used merely to make the titles easier to read.

Controls Sheet

The first sheet of the ProActiveX file is the Controls Sheet. The options available on this sheet are described below.

Get (All)

The Get button copies ActiveX enabled data elements from the currently open model to the ProActiveX spreadsheet.

ProModel must be running and a model loaded. When all of the data has been copied to the spreadsheet, a message box will appear saying, “Process Complete.”

Build Model

The Build Model button copies all ActiveX enabled data elements from the ProActiveX spreadsheet to the currently open model.

ProModel must be running and a model loaded. When all of the data has been copied to the model, a message box will appear saying, “Process Complete.”

Save Model

The Save Model button saves all updates to the loaded model. ProModel must already be open with a model loaded.

Run Simulation

The Run Simulation button starts the simulation for the loaded model. To view the simulation, you will have to manually select ProModel from your Windows Task Bar, to bring it forward.

Open ProModel

The Open ProModel button starts ProModel, but does not load a model.

Load Model

The Load Model button gives the user an opportunity to specify a model and then open it in ProModel. If ProModel is not already running, the Load Model button will start ProModel and then load the selected model. This button gives you an example of a custom dialog box.

Macros

All of the processing logic for ProActiveX is contained in Excel macros. In most cases each macro is in a separate module. The controlling modules are named Get_ALL_Model_Data and Pop_ALL_Model_Data. There are also macros in some of the Excel Sheet Objects. For example, Sheet04 (Resources) has subroutines that dynamically fill in the Path Network drop-down list (using the information on the Path Networks sheet) every time the Resources sheet is activated. This is one method for keeping the drop-down list in sync whenever you update the data.

When the user clicks on the Build All button on the Controls Sheet, the create_model subroutine is started (found in the POP__ALL_Model_Data module). This subroutine then calls subroutines in other modules to process each type of data. The order in which the subroutines are called is important, since many of the modules in ProModel depend on information in other modules. For example, the Locations module must be built before the Path Networks module, because the Path Networks module references Locations.

All of the subroutines in ProActiveX use the constants established in the PM_CONSTANTS module. You will also find this module recorded in the file called PM_CONSTANTS.bas. The main purpose of the constants is to provide a method for accessing the parameters to the ProModel properties and methods data, without having to remember the numbers associated with each table and field.

There are some other useful subroutines that are used throughout the program to do things like showing the right error message or replacing Excel style carriage returns with ProModel style carriage returns.

In many cases when you look at the tables in ProModel, what you actually see on the screen is a main table and one or more sub tables.

The subroutines in ProActiveX must process the data for all tables simultaneously by using For...Next or Do While... loops.

In many of the data tables, the first field is a Name or ID field that identifies each record in the table and does not allow duplicates. In the subroutines that populate these types of tables, the logic is designed to search for an existing ProModel record with the same name or ID. If a record is found, all of its fields will be updated. If no record is found, a new one will be appended. In tables that have no identifying field, records are always appended.