Macro (SVB) Programs
Example - Creating a Cell-Function Spreadsheet
One of the most basic functions of the designated spreadsheet software
(such as Microsoft Excel) is to automatically recompute cells in the data
file when any of the input data (cells) are changed.
For example, you can set up a complex budget for a project so that when
you change the values for particular budget items, the entire budget will
be recomputed based on the newly supplied values.
The same functionality can be programmed into STATISTICA
Spreadsheets
by attaching an SVB macro
to certain spreadsheet events as demonstrated in this example. Note that
practically all spreadsheet (and other) events can be customized, thus
providing the tools to build very sophisticated and highly customized
automated data operations right "into" the spreadsheet.
How is the macro created?
First, create a data file and set up the necessary cells. Data file Cell Function Demo.sta (available in
the Examples\Macros\Document Event Examples
directory, which can be found in the directory in which you installed
STATISTICA) contains prices for
various items on a holiday shopping list.

Note that in the spreadsheet shown, the Item
Cost and Coupon variables
are data input variables, and the Final
Item Cost and Total Cost of All
Items variables are derived or computed variables.
Entering the computations
for derived cells (programming the DataChanged event). After entering
the basic information, select View Code
from the View - Events menu.
This displays the SVB
program editor for document-level events (i.e., events that apply
to the newly created spreadsheet document). Select Document
in the Object box of the SVB
editor (Document Events window); select the DataChanged
event in the Proc box.
Now type the following program into the SVB editor.
Private Sub Document_DataChanged(ByVal
Flags As Long, _
ByVal FirstCase As
Long, ByVal FirstVar As Long,
_
ByVal LastCase As Long, ByVal
LastVar As Long,
_
ByVal bLast As Boolean)
'
Only process the data if there was a change in
' the data area of the spreadsheet.
If (Flags And
scNotifyDCData) Then
If FirstVar = 3 Or
FirstVar = 4 Or _
LastVar = 4 Or
LastVar = 4 Then
MsgBox "These fields cannot be edited."
End
If
Const V1 As
Long = 1
Const V2 As Long = 2
Const VDest As Long = 3
Const VResult As Long = 4
Dim s As
Spreadsheet
Set s = ActiveSpreadsheet
'
We need to recalculate first to update the final cost of each item;
' we only need to do so for the range of cases that have changed.
Dim j As
Long
For j = FirstCase To
LastCase
Dim x1 As
Double
Dim x2 As Double
'
If the source data is missing data, then substitute 0.
If (s.MissingData(j, V1)) Then
x1
= 0
Else
x1
= s.Value(j,V1)
End
If
'
If the source data is missing data, then substitute 0.
If (s.MissingData(j,V2)) Then
x2
= 0
Else
x2
= s.Value(j,V2)
End
If
'
Calculate new destination variable
s.Value(j,
VDest) = x1 - x2
Next j
Dim i As
Long
Dim TotalVal As Double
'
Iterate through each cell in variable 3 and add it to
' TotalVal.
For i = 1 To
s.NumberOfCases
TotalVal
= TotalVal + s.Value(i, VDest)
Next i
' Update the cell's value to reflect total cost
of all items.
s.Value(s.NumberOfCases,
VResult) = TotalVal
End
If
End
Sub
This macro defines the computations for the cells in the spreadsheet
that will be performed every time the data in the input variables are
changed.
Write protecting the
derived cells. Also, we want to make sure that certain cells are
protected, i.e., users should not be able to type values into the cells
that are derived (by computation from other cells). Some of that protection
is already implemented in the macro shown above, which checks whether
the user attempted to type a value into a derived cell. For this example,
let's also "catch" the BeforeDoubleClick
event for the cells in the third and fourth variables of our example spreadsheet.
Select the BeforeDoubleClick
event in the Proc field of the
SVB editor, and then enter the code as shown below:
Private Sub Document_BeforeDoubleClick(ByVal Flags As
Long, _
ByVal CaseNo As Long, ByVal VarNo As
Long, Cancel As Boolean)
If VarNo = 3 Or
VarNo = 4 Then
MsgBox "These
are derived fields and they cannot be edited."
Cancel = True
End
If
End
Sub
Saving the spreadsheet
and AutoRun. Finally, before saving
the macro and the data file, click on the data spreadsheet once more,
and select Autorun from the View - Events menu.
This will cause the new macro to run automatically every time you open
the data spreadsheet. Next, save the spreadsheet and run the macro. You
are now ready to compute your holiday shopping budget using your customized
spreadsheet. If you try to "cheat" by double-clicking on one
of the computed fields to enter a (lower) total value, a message will
be displayed.

This simple example illustrates how you could build very sophisticated
"cost models" that can also include dialogs, automatic analyses
with the STATISTICA statistical
or graphics functions, or any of the more than 14,000 automation functions
available in the STATISTICA system,
thus vastly expanding the functionality of ordinary spreadsheets.