VARIABLES
Generally

There are four PRIMARY types of Variables in EstimatorVJ
Referenced Worksheet Variables
These are pointers to a Value in the current worksheet. Their scope is the current worksheet only.

Worksheet Variables
These are defined in the Worksheet to which they apply. Their scope is the current worksheet and ALL it's children unless redefined in the child worksheet.

Project Variables
These are defined at the Project Level. Their scope is Project wide.
          Program Variables
                    These are reserved and predefined within the Program. Their scope is PROGRAM wide.
DEFINING VARIABLES

REFERENCED VARIABLES
USED IN CALCULATIONS

A Referenced Worksheet Variable points to a value in either the Calc, Qty, Rate, or Cost column, OR a SubTotal at a selected row.
The 'reference' which points to the variable, is contained as a unique identifier in an Invisible and protected column called 'VarRef'.

THE COLUMN ATTRIBUTES FOR "VarRef" MUST NOT BE MODIFIED.

The following simply illustrates this during the measurement of quantities for excavation of a stormwater drain.

Here are the items we need to measure:




We setup our sheet and insert all the variables for the activity.





We now enter and quantify each of the items we need to price:

  1. The drain:
    1. Double click in the CALC field which puts you in edit mode
    2. Reference the QTY of the drain length by CTRL+clicking on the CALC entry for the 250 dia pipe



      This now places a "Referenced Variable" into the calculation for us. The second character "Q" means that the value this represents is the QTY of the item on the line we selected.
      We could have clicked on this item in the RATE column instead; this would have "referenced" the rate and the "Q" would become an "R".
      Similarly if we selected the COST column.

      Referencing the SubTotal on that line will be illustrated later.

    3. Exit the edit mode and the referenced variable will be transformed into a more readable form. Also note that the cell will have a white background to signify that it contains a Referenced Variable.




    4. The same process is used to quantify the excavation volume (l*w*d)



      1. CTRL+click on 25.0
      2. Type '*'
      3. CTRL+click on 0.90
      4. Type '*'
      5. CTRL+click on 1.25

    5. Exit the edit mode and the referenced variable will be transformed into a more readable form.



      When we edit this cell again, the program senses a Referenced Variable and unlike normal editing, the 'readable format' will be changed back into the 'edit format' and editing will be carried out further in the 'editor panel' at the top of the worksheet as shown below.  Hit the 'ENTER' when finished editing.

      Also when in this editing mode, you do not need to hold down the 'CTRL' key when selecting a cell to reference.



    6. Complete the rest of the items and our worksheet will look like this.

USED AS SUBTOTALS

Using Referenced Variables as SubTotals is similar to those used in calculation expressions. The only difference being the COLUMN you use to specify the type of the lookup.
In the Section Names worksheet you use the "CO" column, whereas in any other worksheet you use the "UNIT" column.

Using our example above, we will illustrate subtotals by adding 10% to the cost of the group of items we have just quantified.

Here is the procedure:

  1. Edit the cell to receive the calculation, then click the "(" in the "operators" panel as highlighted, and a pair of brackets are inserted with the insertion caret placed between them ready to receive more expressions.



  2. Now CTRL+click on the previous item IN THE UNIT COLUMN.



    This gives us the subtotal ("S") of all items up to and including the item "Cart away surplus.....".

  3. Now enter "-" and CTRL+click on the item previous to "250 dia RCP...." giving the subtotal of all items up to and including the item previous to "250 dia RCP....".



  4. This now gives us the total of all the items we want 10% of, so we step over the ")" and enter "*10/100"





The same procedure is relevant for the Section Names worksheet, the only difference here is that you create the Referenced Variable by clicking in the "CO" column instead of the "UNIT" column.

IMPORTANT CONSIDERATIONS

Referenced Variables can ONLY BE USED IN THE WORKSHEET WHERE THEY ARE CREATED. In other words you cannot copy and paste calculations/entries using Referenced Variables to other worksheets.

Do not remove the "VarRef" column. This column is initially hidden and protected be default. If you do remove it, OR change it's contents, you may destroy the values of Referenced Variables in this worksheet.

Do not remove items which have been "referenced". Attempting to do so should bring up a Warning, and if removed will generate a Warning similar to below:


and a calculation expression like this

WORKSHEET VARIABLES
There are three types of Worksheet variables:
Static            These are defined once in a Table attached to the Worksheet
Dynamic       These can be defined multiple times in a Worksheet
Referenced   These are defined by 'pointing' to them in the worksheet.
Static Worksheet Variables
Defining: While in the Worksheet you want the variable to apply, in the left panel click the 'Variables' TAB, as illustrated below. In the Table that appears, you enter the code, description, unit, and value for each of the variables. The code and value are mandatory. Also remember that the code must not include the '{}' brackets, and must be in lower case.

The Description is not mandatory, but is essential if you are going to use the 'variable lookup' feature explained later.


Dynamic Worksheet Variables
Defining:
A dynamic worksheet variable can be defined or re-assigned a new value anywhere in a Worksheet as long as the Column containing the definition has the following characteristics:
Data type Text
No formula set
The format of the definition is:
"variable_name<=>variable_value;description;unitname"
Any "description" can be included after the ";" and is excluded in the defining algorithm,
Any "unit" can be included after a SECOND ";" and is excluded in the defining algorithm
The "variable_name" must be in lower case,
The "variable_value" can either be literal, or derived from an in-cell formula

Examples of derived values are:
    [QTY]                        The value in the 'QTY' column on the same line
    {var}                          The value of another Project/Worksheet variable
    @MANHOURS()       A formula which gets evaluated and assigned
Here are some examples in one worksheet


Item 1:
'varname' was manually entered into the Variables List panel on the left and given an initial value of 1.23

Item 2:
The entry in the DESCRIPTION column assigns a new value to 'varname' of 2.5. All calculations from here on in this worksheet will use this value for 'varname' until a new reassignment is made.
Item 3:
Shows the use of 'varname'
Item 4:
The entry in the DESCRIPTION column assigns a new value to 'varname'. This value comes from the QTY column (defined by the column reference "[QTY]") which at the moment is '52.00'
Item 6:
The entry in the DESCRIPTION column assigns a new value to 'varname'. This value comes from the formula "@MANHOURS()" which by definition will get the TOTAL in the "MHRS" column in the COST workup sheet. In this exercise it is "10.0".
Items 5 & 7:
Shows the use of 'varname'.
PROJECT VARIABLES
These can be defined from anywhere in the Project by clicking the 'Projects->Variables' button. You enter the name, description, unit, and value for each of the variables. The name and value are mandatory. Also remember that the name must not include the '{}' brackets, and is case dependent. Also, the 'VARNAME' must be unique within both the Project and Dynamic Project Variables.



Dynamic Project Variables

 These are defined by placing the entry '=VARNAME' in the 'F' column of any worksheet, where 'VARNAME' is your assigned name for the variable. The variable's value will be assigned by default the value from the 'VALUE' column of the worksheet. You can override this default by placing the override specification ':COLUMNNAME' after the 'VARNAME' (e.g. the entry '=C32:QTY' would maintain a value for the Dynamic Project Variable 'C32' from the 'QTY' column).

NOTE: The column name is case dependent and must match exactly the name of the appropriate column. Also, the 'VARNAME' must be unique within both the Project and Dynamic Project Variables.

Illustrated below are a couple of examples:


PROGRAM VARIABLES
 
These are listed in the following table:
VARIABLE NAME
 IT'S VALUE


Q
 The value in the 'QTY' column of the PARENT Worksheet
DJC
 The value in the Project Summary (Project Window) in the 'COST' column on the line containing a sub-total definition ending in 'DJC' in the 'CO' column
IJC
 The value in the Project Summary (Project Window) in the 'COST' column on the line containing a sub-total definition ending in 'IJC' in the 'CO' column
PI
 The value of 'π' (3.1416....) or '22/7'







Application of Variables

Apply a variable by typing the variable name (enclosed within curly brackets) into the desired worksheet cell. If the cell is in a column defined of type other than 'text', and if the variable is defined either within the Worksheet or Project variables, then it's value will be shown. If it is a 'text' column, then it's name will be shown.

Special attention must be taken with respect to Variables. They have a similar consequence to Rates in the Resource Library in that if the Rate or Variable value is changed, then the project must be Recalculated to make sure that the change is reflected in the Estimate
Applied Value (Project and Worksheet Variables only)

When the program encounters a Variable, it substitutes the placeholder {name} with it's value. It first looks in the current worksheet's variables list for the variable name, and if found uses the associated value. If the variable does not exist in the worksheet variables,  it then looks in the project variables, and if found in there, uses it's value. If not found in either, then the value becomes 'undefined'.

VARIABLE DISPLAY OPTIONS

By default, variables are shown 'as their value' in an expression; e.g.  (in each of these cases there is a variable called 'example' specified with a value of '1.234')




You can change the default setting for Variables display from with the System 'Preferences'. Here is the default setting:


This is what it looks like if the Preference's setting is 'Format as Added Value':



...and if the 'Highlight Value' is ticked:





'LOOKING UP' PROJECT AND WORKSHEET VARIABLES


Instead of having to type in the variable definition (e.g. {mycodeforit} ), you can look it up from a popup list as described in the following steps:

  1. Start editing the cell

  2. Press the CTRL+{ key combination

  3. Press the down arrow to select ‘Select Worksheet Variable’

  4. Press the right arrow key to list the worksheet variable ‘descriptions’

  5. Move to the desired variable and press ENTER

  6. The variable ‘code’ will then be inserted into the editing cell

  7. Done