Returns a number, vector or 2-dimensional array of Constant values by querying Microsoft Excel for the values. The number of values brought in is determined by the subscripts that are used in the left hand side.
Restrictions: Must appear directly following the equal sign = (or the == sign for Unchangeable Constants) and not be followed by anything else.
When a model is checked, GET XLS CONSTANTS queries Microsoft Excel for the values. If no values are available from Microsoft Excel an error is reported. If you are reading multiple values for a subscripted variable all cells must contain numbers, a blank or nonnumeric cell within the array will generate an error. For normal Constants the values are also queried each time a simulation is made and a warning is given if the values are not obtainable. For Unchangeable Constants there is no query performed at simulation time. If Vensim has trouble getting values with this function you should try opening the named file in Microsoft Excel first.
All of the arguments to GET XLS CONSTANTS must be Literals (enclosed in single quotes ') or String Variables. 'file' names a file with complete extension to read from (use '?Tag' to indirectly reference a file and resolve the reference in Model>Settings>XLS Files). This can be an Excel file or any other type of file Microsoft Excel is capable of opening. If no directory is specified (best practice) Vensim will append directory information for the current model. 'tab' names the tab that contains the Constants. This function will not work with older versions of Excel that do not support tabs. 'cell' names the cell that the first Constant value is on. Vensim will, as necessary, read additional cells to the right (on last subscript range) and down (on the second last subscript range) to get all values. You can force Vensim to transpose this reading by ending the ‘Cell’ name with a *.
NOTES
| • If Microsoft Excel is not running Vensim will attempt to start it. |
| • | If Microsoft Excel does not have the specified file open Vensim will try to get Microsoft Excel to open it. |
| • | The constants read from Excel will be overwritten if you make changes in setup mode or specify constant input (.cin) files. |
| • | Changes to String Variables made in .cin files (or through Venapp commands) will not be used when determining file, tab or cell. |
| • | You can substitute named Excel ranges for 'time row or col' and 'cell'. To do this just create the named ranges in Excel and use those names in place of the Letter/Number designators. |
| • | If you have defined only Unchangeable Constants Excel is not queried for simulations and providing a .vmf file or published model to someone without the associated spreadsheet will not generate any messages. |
Units: GET XLS CONSTANTS is not part of units checking. Specify units for the left hand side variable.
Example
|
|
A |
B |
C |
D |
|
1 |
Test for GET… |
|
|
|
|
2 |
Cap Cost |
100 |
|
|
|
3 |
|
Small |
Med |
Large |
|
4 |
Material Consumption |
10 |
20 |
30 |
|
5 |
Init Inventory |
Small |
Med |
Large |
|
6 |
Round |
33 |
19 |
12 |
|
7 |
Square |
24 |
13 |
44 |
size : small,med,large ~~|
shape : round,square ~~|
Cap Cost = GET XLS CONSTANTS('test.xls','Sheet1','B2') ~~|
Material Consumption[size] = GET XLS CONSTANTS('test.xls',
'Sheet1','B4') ~~|
Init Inventory[shape,size] = GET XLS CONSTANTS('test.xls',
'Sheet1','B6') ~~|
Init Small Inventory[shape] = GET XLS CONSTANTS('test.xls',
'Sheet1','B6*') ~~|
NOTE GET XLS CONSTANTS reads files directly on the Macintosh and will not work if the file is already open in Excel.
Availability: Not PLE.
See also: GET DIRECT CONSTANTS, TABBED ARRAY
Sample model: GET XLS CONSTANTS.mdl and GET XLS CONSTANTS[ss].mdl in FunctionExamples
