Visual Basic Editor

Sometimes relations between fields cannot be defined by a simple equation. In such cases, the user can write a user-defined function. The required parameters are passed to the function which calculates it output. For example, in the default template, the item color is automatically changed based on an Urgency value (itself calculated)

ItemColor = CalcColor( [Urgency] )

CalcColor is itself defined by a VBScript equation::

Function CalcColor(Urgency) ' calculation of the itemcolor color code based on the Urgency level

  • if isnull(Urgency) then
    • CalcColor = ""
  • else
    • if Urgency < -10 then
      • CalcColor=""
    • elseif Urgency <=-3 then CalcColor="Yellow"
    • elseif Urgency <= 2 then CalcColor="Blue"
    • elseif Urgency <=10 then CalcColor="Purple"
    • else CalcColor="Red"
    • end if
  • end if

End Function
 
This way, as a task approaches its deadline it changes color from none, to purple, to blue, to yellow and finally to red indicating that it late. No user intervention. Completely user-definable

Function Code Stores

VBScript code is store in three different locations:

  1. System code: Supplied with InfoQube and updated with each new release. Read-only
  2. User code: Generic functions created by each user. Common to all IQBases
  3. Database code: Specific functions to a particular IQBase

In case of name conflict, the database code has highest priority, followed by user and finally, system code. This allows for worry-free function overloading

VB Editor

The built in VB Editor can be used to view the functions that come with IQ or that you have added. Take a look at the built-in scripts already defined and explore the many possibilities.

Function Development Tips

  • Full VBScript reference (from Microsoft) can be found here: https://msdn.microsoft.com/en-us/library/d1wf56tt(v=vs.84).aspx
  • All VBScript functions should return a value. If no value is returned, the function will return "Empty" and an error message will be pop-up (Options setting to disable it). A useful return value is "Null" which is different than 0 or an empty string. Null will clear the field value. See also the section above on "Hierarchy Equations with Parameters" how Null is used.
  • All string functions are case sensitive by default. To do case insensitive comparisons, use (1) LCase/UCase or (2) set the function compare parameter to 1 (0=case sensitive, 1 = case insensitive)
  • Press Ctrl+Space to show Intellisense: shows a list of VBScript functions

Unfortunately IQ's built-in VBScript tools are very basic and its not very good at letting you know when you have made a mistake. A recommended practice is to write and test your functions in another tool with better support then then when you have it working copy it to IQ.

IQ uses Microsoft's VB Script engine. See this page from stack overflow for tools that work with VB Script: VBScript Tools. There are many online sources for VBScript language reference, but this link is one of the best: VBScript Tutorial (tutorialspoint.com)

One good approach is to use the VBA (VisualBasic for Applications) Editor built right into Excel. It gives you full highlighting, code completion, and debugging. Note that VB Script is a subset of VBA, so there are some things you will need to avoid in the code you copy to IQ (see Differences Between VBScript and VBA - VBScript in a Nutshell for the main differences).