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.