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 is 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.
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.