DPL-Excel Links When Incorporating Macros
This memo describes how to set up DPL-Excel links for a probabilistic
analysis when the Excel spreadsheet is updated by a macro.
The built-in links in DPL (Links tab in the Node Definition dialog box)
don't work with command macros. The reason is that DPL will not know
that anything has changed in the spreadsheet when a macro runs and thus DPL
import variables will not be up to date. There are two ways to get around this
problem.
Function Macros
The first approach is to make your command macro a function macro and call
it from the spreadsheet (for more on function macros, refer to your Excel manual).
Whenever data is passed from DPL to the spreadsheet, a recalculate command is
also sent forcing the function macro to re-run. After making this change, you
can simply use DPL's built-in link to your Excel spreadsheet. The macro will
be called each time your spreadsheet is updated.
Although you will probably find using function macros an easier method than
building links explicitly when linking to DPL (method to be discussed),
please note that there are certain Excel functions which are supported by command
macros but not by function macros. If your macro contains one of these commands,
you will not be able to use this technique. For more information on commands
not supported by function macros, please refer to your Excel manual. Also, Excel
will not let a function macro modify cells in the spreadsheet other than the
cell from which it is called.
Building Links Explicitly
This method requires that you initiate a DDE channel. This channel will serve
as the link between DPL and Excel -- it will be used to send/retrieve data and
commands to/from the spreadsheet. (Note: in DPL 6.0, the actual communication will
normally be by OLE automation, depending on the setting in Tools | Options |
Spreadsheet, but the method is the same.)
There are four steps:
-
In order to initiate a DDE channel, define a value node (e.g., value name
"Channel"). If the spreadsheet is named "macroxl5.xls" then the data for
this value would be:
node data for Channel
initiate("excel","c:\\excel\\macroxl5.xls", "[OPEN(\"c:\\excel\\macroxl5.xls\")]","excel")
If the spreadsheet is already open (and will always be open) in another window,
the initiate command can be simplified as follows:
initiate("excel","c:\\excel\\macroxl5.xls")
When the channel is initiated, a channel number will be assigned under
the name of the value node. This name (containing the channel number) will
be needed whenever DPL communicates with the spreadsheet.
-
The following command sends the data from the chance node Sales to the
spreadsheet cell named Sales:
poke(Channel,"Sales",Sales)
Note that Sales in quotes refers to the spreadsheet cell name while Sales
at the end is a DPL chance node name. To send all of your chance node data
to the spreadsheet at once, create a value node (e.g., value name "Send")
with a poke statement for each chance node:
node data for Send
poke(Channel,"Sales",Sales) + poke(Channel,"Price",Price) + ...
-
The command for running a macro is defined in a value node (e.g., value
name "Update") with the following data statement:
node data for Update
execute(Channel + Send,"[RUN(\"calculate_profits\")]")
Each time this value node appears in a get/pay expression DPL tells Excel
to run the macro named calculate_profits. Because Send is inside the execute
statement all chance node data will be sent to the spreadsheet before the
macro is run.
-
To import data into DPL, define the last value node (e.g., value name "Profit")
with the following data statement:
node data for Profit
request(Channel + Update,"Profit")
The presence of Update in this statement assures that data will be sent
to spreadsheet and the macro will be run before Profit is imported.
Example
This technique is illustrated in the influence diagram and decision tree below.
In this example the Excel workbook has two sheets: sheet1 and module1. These
are shown below:
Sub calculate_profits()
sales = Worksheets("sheet1").Range("sales").Value
price = Worksheets("sheet1").Range("price").Value
costs = Worksheets("sheet1").Range("costs").Value
Worksheets("sheet1").Range("profit").Value = price * sales - costs
End Sub
This macro is written in Visual Basic for Applications.
Below is a listing of the DPL code for the example influence diagram.
value Channel=initiate("excel","c:\\dpl\\macroxl5.xls");
chance Sales.{Low,Nominal,High}={.25,.5,.25},
=
8, // Sales.Low
10, // Sales.Nominal
12; // Sales.High
chance Price.{Low,Nominal,High}={.25,.5,.25},
=
4, // Price.Low
5, // Price.Nominal
6; // Price.High
chance Costs.{Low,Nominal,High}={.25,.5,.25},
=
25, // Costs.Low
30, // Costs.Nominal
35; // Costs.High
value Send=poke(Channel,"sales",Sales) + poke(Channel,"price",Price)
+ poke(Channel,"costs",Costs);
value Update=execute(Channel+Send,"[RUN(\"calculate_profits\")]");
value Profit=request(Channel + Update, "profit");
sequence:
gamble on Sales then
gamble on Price then
gamble on Costs then
get Profit
DDE Time Out
When Excel encounters a problem during an analysis, it will put up an error
message and wait for a response. If you are looking at DPL, you have no way
of knowing that there is a problem with Excel and you could be left waiting
indefinitely. To counter this potential problem, DPL has a parameter called
DDE time out which it uses to determine if there is a problem with an analysis.
This parameter may need to be changed in certain instances.
If the time it takes to run your macro and update your spreadsheet at each
path is longer than your DDE time out time, you will get an error message and
the analysis will halt. The default DDE time out is 20 seconds. In order to increase
the time out period, select Tools | Options and go to the Spreadsheet tab.
Enter the desired time out time in seconds in the DDE time out box. If you find your model
requires more time, enter a larger number.
|
