Formulas and Macros Formulas and MacrosVBScript (Language Reference) is used to in grid formulas, macros and in the command line. Context sensitive lists and tooltip information (Intellisense) are available as you type in the Command line and the Script editors. Windows Script Host (WScript) and the File System object (Fso) are supported.
A Flask Basic File (*.dfb) can be paired with a Flask Grid File (*.dfg) to support formulas. Formula's are functions that accept (nRow and nCol) as parameters. The value returned by the function will appear in the grid. There are three types of Formulas: Global, Column, and Cell. All formulas accept the same parameters (nCol, nRow). They differ only in name.
Macros are functions that are called manually, and may be assigned to a toolbar or keyboard shortcut.
Editorial Comment. Data Flask attempts to make using (and possibly learning) VBScript as simple as possible. If you are new to VBScript, the single most important thing to understand is: Whatever value is set equal (=) to the formula name, which by default is the text string "Value", should be replaced with your own expression. Experiment by first replacing "Value" with a single statement such as time, date, or grid.cell(1,1) to get an idea of how simple using VBScript can be.
Where Data Flask's primary goal is to provide a robust data authoring and migration tool, a secondary goal is to bring the automation interface up to the level of the mouse and keyboard interface. Data Flask seeks to make scripting almost as simple as clicking through the menus.
VBScript Links: Scripting: Your First Steps (external), Download (external), Debugger (external), File System Object (external).
To create a new formula, pick Grid/Formula and choose a formula type. For example: From a new grid, if cell 2,10 (B, 10) is highlighted, picking Grid/Formula/Cell...
Function FormulaCellB10(nCol, nRow)
FormulaCellB10 = "Value"
End Function
would load the Script Edit dialog with the function FormulaCellB10. Formulas are set to return "Value" as a default.
All formula functions differ only in name. The global formula function is always Formula A column formula might be FormulaColumnB. All accept the same parameters, nCol and nRow. For the column formula, the nCol parameter may be redundant, and both parameters for the cell, however passing the nCol and nRow parameters is inexpensive, and being able to assume the same call signature for all formulas makes the formulas easier to manage, because they differ only in name.
When defining a column in the Grid/Column/Properties dialog (Figure 1-1), a Formula may be assigned as a default value. You can override the default formula with another formula, or explicitly stating that no formula is desired by indicating Grid/Formula/Type/None
When formulas are active FX will appear left of CAP in the status bar.
When a .dfg Grid file and a .dfb Basic file have the same name, for example c:\data\flask.dfg and c:\data\flask.dfb, the basic file is called a peer script. Formulas are saved into the peer script file, not into the grid file.
When a .dfg grid file is opened, and a peer script exists, the peer script is loaded into a hidden window. Depending on the setting of Script/Activate Automatically, the script will start and become available to be used for formulas referenced in the peer grid. For safety, peer scripts will not autoload load from an inet temporary directory.
If any peer script windows are open, the Script/Show Hidden menu pick will become available. Show Hidden would cause all hidden windows will become visible.
If you pick Script/Edit Peer Script the peer script will be activated, and created if necessary.
Expressions are not saved in the peer script as functions. They are held as in the dfg, not the dfb. For security reasons, expressions must be activated manually with Grid/Formula/Expression, regardless of the Activate Automatically setting.
Figure 5-1 Basic .dfb File Editor
The script editor supports syntax colorization, bookmarks, breakpoint indicators, customizable font and color settings, automatic casing, Intellisense and tooltip features. Drop lists at the top of the screen allow you to quickly enter into pre-determined function names and events.
The .dfb Basic file editor is a larger version of the Script editor available from the Grid /Formula menus, and the Command line. The smaller version exposes a single function or procedure, where the Basic File editor allows you to edit everything. Any formulas added from the Grid/Formula menu will appear in the peer Basic file.
Two areas are special in a .dfb Basic file because they are loaded automatically. With (General) selected in the left Drop-List, Main, (Declarations), and Formula will appear in the right Drop-List. (Declarations) is evaluated as the script is first loaded. If you have any global variables to declare, you would Dim them there. sLastClip and sUserName are global as declared in figure 5-1.
You can create a new .dfb basic file using File/New Document/Basic. Basic files are not required to have a peer grid to run, but they will not be loaded automatically.
Main is called after the Basic file is loaded, thus Main can call Functions and Subs declared anywhere in the file. Main is run each time Run/Run is selected. (Declarations) is evaluated as part of script loading, and therefore only evaluated when first loaded, or after the script stops. Script stops by picking Run/Stop, by disabling formulas, or with script error.
Figure 5-2 Windows Explorer Context Menu.
If you run a .dfb file from Windows Explorer, or set a .dfb file to run at startup using Tools/Customize/Tray Icon/Startup Hidden, anything in (Declarations) would be loaded and Sub Main would be run. In figure 5-2 above, the file Macros.dfb will be loaded into a hidden document window, and Sub Main run.
As you type, language context features activate to help you identify language and objects features. Additional context features are available by activating the (Right Click) context menu.
Bookmarks are available for longer scripts, allowing you to jump between locations by pressing F2.
Functions can be added into a Flask Basic File (*.dfb) manually by picking Script/Functions and using the Functions Dialog.
Extra Script functions that can be used as Macros or Formulas have a fixed signature. They are functions declared with (nRow, nCol) as a parameter. This restriction is for safety and simplicity. It's easier to be able to state that all Functions called by the host will be have two parameters, rather than attempting to classify the formula types and macros.
If you wish to define functions or procedures with a different declaration, you can do so by entering them into (Declarations) section. The special treatment given to Formulas and Macros by the editor is just that. Special treatment by the editor. As far as the VBScript loader is concerned, anything placed into the declarations section is an equal part of the same VBScript.
Macros are functions that are called manually, rather than in response to the peer grid requesting a cell value. With a Flask Basic File loaded, pick Tools/New Macro to add a new function to the Basic File. Like Script/Functions this adds a new function, but it also registers the macro such that is appears in Tools menu, and becomes available for toolbar and keyboard customization.
Macros can be registered as Global. A standard, local macro must be present in the peer script in order to be called, and thus may be defined differently for each grid.
Global macros will automatically load the script file that contains the macro.
When Data Flask is run minimized into the tray icon, registered macros will appear when the icon is mouse right-clicked. This allows you to run macros while the application stays in the background.
Figure 5-2 Timer Event
The App object is the only object that fires events. App_Load is called when the application loads, App_Exit is called when the program exits. App_Load is not called when the script document is loaded as the Main function is.
App_Timer is called when App.TimerInterval is set to something other than zero. In the example above, customers.dfg would Reload Data (ReadData) every 60 seconds. ReloadData returns true if the grid document is modified.
The App.Flash function is designed to get the attention of the user when Data flask is operating as a tray icon or minimized. Pick Tools/Customize/Tray icon to set Data flask to operate in the tray icon when minimized, or on windows startup (login).
When the timer is active Run will appear left of CAP in the status bar at the bottom of the window. When Paused, the timer event will not fire, but grid formula's will continue to operate and macros can still be called. Stopping script causes both formulas and events to stop.
Flask Basic File (*.dfb) contain binary formatting markers. The markers are used to support drop lists and allow Formulas to be edited individually in the Script Edit dialog. You can save as .vbs text, but the script will no longer load as a peer.
-
This feature is activated with product registration.
Script breakpoints can be set with Run/Toggle Breakpoint in the script editor, or by clicking in the left margin of a script dialog. Data Flask supports the script debugger host interface, but the debugging application is separate. You can download a free script Debugger (external). When a breakpoint is hit, you will be presented with a list of available debuggers.
Figure 5-3 Script Debugger
Editorial Comment. If you do much script debugging, I recommend that you use the MS Visual Studio.NET debugger. Watch and local debug windows are available to view script variables, and the command window is comparatively stable. When debugging, be aware that cell formulas are activated with both redraws and recalcs. You may find it easier to debug complex formula's as macros rather than catching breakpoints from redraws.
Data Flask can be controlled through external automation from a program like Visual Studio.NET, WSH, or any applicaion that supports Visual Studio for Applications (VSA), including Visual Studio Tools for Office (VSTO). Type library information is registered under Interscape Data Flask 0.9 Type Library with the .TLB resource held in Flask.exe. Using Visual Studio you can create the application object with
Dim oxapp As New Flask.Application()
oxapp = CreateObject("Interscape.Flask.Application")
Open a grid file with
Dim oxgrid As Flask.GridDoc
oxgrid = oxapp.Documents.Open("C:\Temp\Flask.dfg")
using the application object or
Dim oxgrid As Flask.GridDoc
oxgrid = GetObject("C:\Temp\Flask.dfg")
Data Flask supports standard (Kraig Brockschmidt) document object model, which should be familiar if you have used automation to control other applications such as MS Office.
Data
Flask Copyright © 2006 Interscape
Corporation