How can I run a Statistica analysis from Microsoft Excel?

First, if you want to run Statistica from within the Visual Basic environment in Microsoft Excel, be aware of the issues (and some limitations) described in Can I execute an SVB program from within other Visual Basic compatible Applications?.

To illustrate, we will create an example program that can be run from Visual Basic within Excel. After starting Excel, create a new worksheet. Then select Macro - Visual Basic Editor from the Tools menu. From the Visual Basic Tools menu, select References.

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

Now type or paste the following program into the program editor.

Sub ExcelTest() ' Run the Statistica application; create the StatisticaA ' 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 inside Microsoft Excel (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 are displayed 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.