topic Re: Excel VBA - Subset data by column in Discussions

Excel VBA - Subset data by column

jb — Mon, 28 Sep 2015 19:10:51 GMT

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:

Why does "doc = app.OpenDocument(fname)" cause a run-time error "Object variable not set"?
How do the datatable subset methods work? I can see these in the Excel VBA Object Browser, but there's not much detail.
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

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

Re: Excel VBA - Subset data by column

briancorcoran — Mon, 28 Sep 2015 19:32:57 GMT

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

Re: Excel VBA - Subset data by column

jb — Mon, 28 Sep 2015 22:57:12 GMT

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:

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

Thanks again!