Variables¶
A variable holds a value while the expression runs. A multi-line expression uses one to work out an intermediate result and return it on the last line:
One type only¶
VBScript has a single data type, the Variant. A Variant holds a string, a number, a date or a Boolean, and takes whichever of them you assign to it. You cannot declare a String, an Integer or a Boolean, and a variable that changes from a number to a string partway through an expression is legal.
A variable you have declared but not assigned holds Empty. Empty compares equal to
zero and to the empty string. If an expression tests a variable that was never
assigned, both comparisons are true.
A field with no value¶
A field reference never gives Null or Empty. A field with no value gives the
default for its type:
an empty string for Text, 0 for Integer and Decimal, False for Boolean, and VBScript's
zero date for Date. A database NULL and a blank value both leave a field with no
value. IMan does not store an empty value.
The plain tests therefore work. %CustomerRef = "" is True for a Text field with no
value, and %GrossTotal - %TaxTotal counts a missing tax total as 0.
The default also hides the difference between a field with no value and one that holds 0, False or the zero date. When that difference matters, test the field with IsFieldNull. This expression uses the list price when a line has no price, and keeps a price of zero:
IsFieldNull takes the field's name in quotes. IsFieldNull(%UnitPrice) passes the
field's value as the name and fails.
Empty and Null are different things¶
A Variant also holds Null, and Null is not Empty. Empty means the variable has
not been assigned. In an IMan expression, Null comes from a database. A field
reference never gives one, but a database
Lookup
does: when it finds a row whose return column is NULL, Lookup, LookupDB and
LookupWhere return Null. When they find no row, they return an empty string.
Null behaves unlike any other value in the language. None of the following raises an
error:
IsNull(Null) ' True — the only reliable test
IsEmpty(Null) ' False — Null is not Empty
Null = Null ' Null — not True
Null = "" ' Null — not True, and not False either
Null + 1 ' Null — arithmetic carries the Null through
Null & "x" ' "x" — concatenation does not
A comparison against Null is never True, and the Else branch runs
Null = "" is Null rather than False, and If treats anything that is not True
as False. A test for an empty lookup result with = "" therefore takes the wrong
branch when the column was NULL. Test with IsNull as well:
Dim Terms
Terms = Lookup("CUSTTERMS", "TERMSCODE", %CustomerCode, False)
If IsNull(Terms) Or Terms = "" Then Terms = "30DAYS"
Terms
VBScript evaluates both sides of an Or, whatever their order. This test works
because True Or Null is True. A second test that raises on Null, such as
CStr(Terms) = "", still fails behind an IsNull.
CStr(Null) raises error 94, "Invalid use of Null". Use & to build a string from
a value that may be Null. & converts a Null to an empty string rather than failing.
Declaring a variable¶
Dim declares one:
Several names separated by commas declare several at once:
The declaration is optional. Assigning to a name that was never declared creates
it. Declare anyway. Without a Dim, a name misspelt further down the expression
creates a second variable holding Empty.
Naming rules¶
- Start with a letter.
Dim 2Varfails with "Expected identifier". - Use letters, digits and underscores. A full stop is not allowed, and
Dim My.Varfails with "Expected end of statement". - Up to 255 characters.
- Each name once per expression.
Case does not distinguish two variables. NetValue and netvalue are the same one.
Assigning a value¶
The name goes on the left of an equals sign and the value on the right.
Write a number bare, with no quotes:
Put a string in double quotes:
Put a date or a time between hash marks:
Both are Dates. A time written without a date carries the base date of 30 December 1899.
A date literal is month first, whatever the server is set to
#02/01/2020# is 1 February and #01/02/2020# is 2 January. The hash-mark form
is US-ordered by definition and ignores the machine's regional settings.
CDate("01/02/2020") does the opposite and reads them. On a UK server it returns
1 February.
Write DateSerial(year, month, day) to avoid the ambiguity.
DateSerial(2020, 2, 1) is 1 February on every machine:
Assign a field reference like any other value:
Constants¶
Const names a value that never changes:
The value must be a literal. Const Rate = 1 / 5 fails to compile with
"Expected literal constant". An assignment to a constant further down fails at run
time with "Illegal assignment", error 501.
Scope¶
A variable belongs to the expression it is declared in and to the single evaluation of it. IMan evaluates a field expression once per record, and a variable set on one record has no value on the next. Nothing a variable holds reaches another field, another transform or another run.
To carry a value between records, use a Counter. To share logic rather than a value, put a function in Common Functions.
An inline expression cannot declare a variable
The single-line expressions in transform setup fields, such as a Writer's file
name, take no Dim and no local variable. Write the whole calculation as one
expression. See
the three kinds of expression.
Objects¶
A few values are objects rather than plain values: a dictionary, a regular expression, a file. An object carries methods and properties, reached with a full stop after its name.
Assign an object with Set. A dictionary suits a list of codes too long for a
Select Case:
Dim Depots, Depot
Set Depots = CreateObject("Scripting.Dictionary")
Depots.Add "MAIN", "Birmingham"
Depots.Add "NORTH", "Glasgow"
Depot = "Unknown"
If Depots.Exists(%WarehouseCode) Then Depot = Depots(%WarehouseCode)
Depot
With %WarehouseCode of NORTH, the expression returns Glasgow. Without the Set,
the assignment fails with "Wrong number of arguments or invalid property
assignment", error 450.
CreateObject
covers the other objects a server can provide.
New creates a regular expression. RegExp is the one object VBScript builds
itself. New Dictionary fails with "Class not defined", and a dictionary always
comes from CreateObject.
With saves repeating the object's name. Each line inside it starts with a full
stop:
Dim Digits
Set Digits = New RegExp
With Digits
.Pattern = "[^0-9]"
.Global = True
End With
Digits.Replace(%OrderRef, "")
With %OrderRef of ORD-10432/A, the expression returns 10432.
Nothing is the empty object. Set Depots = Nothing lets the object go, and
Depots Is Nothing tests for one. Is compares objects only. On a variable that has
never held an object it raises "Object required", error 424.