Excel VBA - Subset data by column

[#1]

Hello, I'm looking at a sample program included with JMP 11:

  C:\Program Files\SAS\JMP\11\Samples\Automation\Visual Basic Samples\Data Table .NET\datatab.vb

Since I don't have Visual Studio (just Excel), I opened "datatab.vb" with Notepad and attempted to translate the "Subset_Click" procedure from Visual Basic (VB) to Visual Basic for Applications (VBA). Please see the code example below.

I have a few questions:

1. Why does "doc = app.OpenDocument(fname)" cause a run-time error "Object variable not set"?
2. How do the datatable subset methods work? I can see these in the Excel VBA Object Browser, but there's not much detail.
3. How is the datatable handle used?

Thanks for your help.

'--------------------------------------------------------

'Subset bigclass by Weight and Height

'Save new datatable handle in NewDT, then save table to disk as subset.jmp

'--------------------------------------------------------

Sub Subset()

  Dim app      As JMP.Application

  Dim doc      As JMP.Document

  Dim dt       As JMP.DataTable

  Dim newDT    As JMP.DataTable

  Dim dtDoc    As JMP.Document

  Dim dir      As String

  Dim fname    As String

  'Start JMP

  Set app = CreateObject("JMP.Application")

  app.Visible = True

  'Get data table

  dir = "C:\Program Files\SAS\JMP\11\Samples\Data\"

  fname = dir & "Big Class.jmp"

  doc = app.OpenDocument(fname)

  dt = doc.GetDataTable

  'Subset data

  dt.Activate

  dt.SubsetSetRandomSelection(2, False)

  dt.SubsetStratifyAddColumn ("age")

  dt.AddToSubList ("Name")

  dt.AddToSubList ("Weight")

  dt.AddToSubList ("Height")

  newDT = dt.Subset

  dtDoc = newDT.Document

  dtDoc.SaveAs ("C:\JMP\subset.jmp")

End Sub

'--------------------------------------------------------

[#2 · Accepted solution]

Hi JB,

There is a full automation reference installed in the Documentation directory where JMP 11 is installed.  If you are getting a bad reference, that means either JMP failed to start (App object is empty) or it could not find the Big Class data table and that reference is empty.  Please keep in mind that if you are using JMPPro, the directory will be different JMPPRO\11, not JMP\11.  The same goes for other JMP products.

This sample is doing a subset, with a Random sample size of 2.  Stratify is set, with the age column being used for the stratified sample.  Only the selected columns will be output.  In this case, name, height and weight are the selected columns.  I am attaching a picture of what this would look like if you were to do this interactively.

I hope this helps,

Brian Corcoran

JMP Development

[#3]

Wow, there's a lot of good stuff in the JMP install directory! The method descriptions in the Automation Reference PDF are certainly useful.

In regards to my original questions:

1. The "Set" keyword was needed to assign an object reference to the variable.
2. Your commentary and picture helped me understand the Subset code.

Thanks again!