Macro (SVB) Programs Example - Creating a Cell-Function Spreadsheet

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.

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 are 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 causes 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.