Get started using Formulas, Macros, and the Command line. Create an Offline Basic (VBScript) or JavaScript (JScript) document.
Formulas and Macros are saved in OLB (Basic) and OLJ (JavaScript) files.
Follow the steps in this tutorial to create these files:
| http://inters.com/samples/formulatutorial.olz |
The package contains OL and OLB files.
Select Enabled in the Formula tab to run after opening. You can also select Startup to automatically run formulas.
Skip to Command Line below to use JavaScript.
To specify Basic: In the Formula ribbon, clear the JavaScript box.
A dialog displays to confirm that you want to set the default formula language.
Exit and restart SQL Offline. Restarting resets the command window.
To display the command line window, enable macro recording, and enter commands:
In the Formula tab, select Command and Record to display the Command window and enable macro recording.
In the Command window, type in the following three lines:
VBS
msgbox "hello, world"
app.documents.new
grid.cell(1, 1) = "hello"
JS
WSH.Echo("hello, world");
App.Documents.New;
Grid.Cell(1, 1) = "hello";
Press Enter after each line. Click OK to close the Message Box that displays.
As you type, notice that SQL Offline supports autocompletion (Intellisense). It is not necessary to understand vbscript in depth to use the command line. SQL Offline records to the command line as you work.
Click in the first cell (1, 1) of the Offline document. Type goodbye and press Enter to replace hello with goodbye.
In the Command window, in the new line that was added, click and drag to select "goodbye".
Replace "goodbye" with:
VBS
time
JS
Date()
and press Enter. The current time displays in cell 1,1.
For more information see Command Line.
To create a formula in an Offline document:
In the OL tab, click New to create a new Offline document. Click Format to display the Format Grid dialog box.
In the Format Grid dialog box, in the Columns box, enter 3. Enter 3 in the Rows box. Click OK.
In the Edit tab, click Select ► Select All to select all nine cells. In the Formula tab, click Global. The Script Edit dialog displays.
In the Script Edit dialog box, replace "Value" with:
VBS
cint(rnd*100)
JS
Math.round(Math.random()*100)
Click OK.
Your 3x3 grid displays random numbers between zero and one hundred (see below for a more practical formula).
In the Formula tab, click Recalc (F5) and notice that the cell values change with each refresh.
In the Edit tab, click Select ► Select All to select all nine cells. In the Formula tab click Type. In the Formula dialog box, click None. Click OK. The global formula is replaced with static text.
NOTE: If a Run-Time Error displays after closing the Script Edit dialog box, click End. In the Formula tab, click Global again. You can then correct the error.
If you save the Offline document, both an OL and an OLB file are created. In the Formulas tab, you can select Startup to allow formulas to run automatically.
Follow the steps in the FORMULA tutorial above. Except in the Script Edit dialog box enter the following:
VBS
nrnd = cint(rnd*100)
Formula = nrnd
If nrnd = 100 Then
msgbox "WINNER: (" & nCol & "," & nRow & ")"
End If
JS
nrnd = Math.round(Math.random()*100);
if (nrnd == 100)
{
WSH.Echo("WINNER: (" + nCol + "," + nRow + ")");
nWins=nWins+1;
}
return nrnd;
Recalc and click until you are a winner!
Calling msgbox from a formula function is not recommended under normal circumstances. Want to up the Ante anyway? Instead of 3 rows, enter 100,000 (one hundered thousand) rows in the Format Grid dialog box.
You will not win as much as you might guess with 100,000 rows: the formula is not called for off-screen cells.
The random number formula is experimental and intentended to illustrate how a formula refreshes. You can create a subtotal function for something more practical:
Create a new Offline document (10 x 10 default) and click the last row in column A (A10). In the Formula tab, click Cell. In the Script Edit dialog enter:
VBS
fTotal=0.0
For nr = 1 To nRow - 1
On Error Resume Next 'ignore non-numbers
fTotal=fTotal+CDbl(grid.CellText(nCol,nr))
Next
FormulaCellA10=fTotal 'modify to match the formula name if not A10
JS
var fTotal=0.0;
for (nr=6; nr<nRow; nr++)
{
var v=Grid.Cell(nCol, nr);
if (!isNaN(v)) //Number
fTotal=fTotal + Number(v);
}
return fTotal;
Enter numeric values in column A to display a total in A10.
For more information see Formulas.
