Macro (SVB) Programs Example - Accessing Statistica Visual Basic Functions from Microsoft Excel

The References dialog box is used to select the libraries (objects) that you want to be visible inside the Visual Basic program. To make Statistica visible, select Statistica Object Library and Statistica Basic Statistics Library (for the current version of Statistica); then click OK.

Now type or paste in the following program into the program editor:

Sub ExcelTest() 
' Run the Statistica application; create the Statistica 
' Application object and assign it to variable (object) StatApp.

Dim StatApp As New Statistica.Application
' Create a Statistica Basic Statistics object (i.e., run the 
' Basic Statistics module; start it with data file exp 
' (note: the actual location of that data file may be 
' different on your installation).

Dim s As Spreadsheet 
Set s = StatApp.Spreadsheets.Open _ (StatApp.Path & "\Examples\DataSets\Exp.sta") 
Dim BasStat As Statistica.Analysis Set BasStat = StatApp.Analysis(scBasicStatistics, s)

' the following 7 lines of code will produce a summary results 
' Spreadsheet from the Statistica Basic Statistics module.

BasStat.Dialog.Statistics = scBasDescriptives BasStat.Run 
BasStat.Dialog.Variables = "5-8" 
Dim out Set out = BasStat.Dialog.Summary

' Select all rows and columns in the Statistica results Spreadsheet.
out.Item(1).SelectAll

' Copy the highlight selection (all rows and columns in the ' Summary results Spreadsheet.
out.Item(1).Copy

' Set the cursor to cell A1 in the currently active Excel Spreadsheet.
Range("A1").Select

' Paste in the summary statistics.
ActiveSheet.PasteSpecial Format:="Biff4" s.Close

End Sub

When you run this Visual Basic program from Microsoft Excel (via the Visual Basic Editor), it will paste the results from the Summary results spreadsheet of the Basic Statistics - Descriptive Statistics analysis into the current Excel Spreadsheet. Note that this is accomplished without the user ever seeing or having to interact with the Statistica application - the program runs entirely invisibly, and the results appear inside the Excel spreadsheet. This simple example illustrates the power and versatility of the Statistica Visual Basic object model. All analysis and graphics options and methods available in Statistica are fully exposed in the respective object libraries, and even advanced and complex analyses can be automated and performed routinely "behind the scenes" from within any other Visual Basic compatible application.

Some limitations and differences between SVB and Visual Basic in other applications

While the objects of the Statistica libraries are fully exposed, and accessible to all compatible Visual Basic programming environments in other applications, there are a few functions that are only available in SVB, i.e., when the Visual Basic program is run from within Statistica. (Likewise, there are some functions in other applications that are not accessible in the SVB environment.) Specifically, all user-interface functions, such as user-defined dialogs or functions for retrieving variable lists or value lists (via dialogs from the user) are closely tied to the Statistica application itself. Therefore, when designing a Visual Basic program to run from a "foreign" application (e.g., from within Microsoft Excel), it is best to design the user interface (dialogs) using the tools available in that application; those tools are usually designed such that they allow you to program interfaces with an overall "look-and-feel" that makes them compatible with all other interfaces (dialogs) used in the respective application. Refer also to Custom Dialogs; Custom User Interfaces for additional details.