Using VBA automation

[#1]

This is a follow-on VBA automation question:

I need to populate jmp data tables from Excel, using VBA automation.

Currently, I am doing this with "SetDataVector" and looping through each element of the source arrays. All attempts to use the vector form "SetDataVector" have been unsuccessful.

Your help to properly utilize the "SetDataVector" function would be greatly appreciated.

Thanks,

Pete

[#2 · Accepted solution]

The Data Table automation sample program that is included with your JMP installation has an example of the syntax.  If you look at the code behind the "Set Vector" button you will see it.  It is too length to really post the whole thing here.  The sample program is found under the installation directory.  For JMP 11, this would be C:\Program Files\SAS\JMP\11\Samples\Automation\Visual Basic Samples\Data Table .NET .

I hope that helps.

Brian Corcoran

JMP Development

[#3]

Yes, this helped. I can now successfully use the function “SetDataVector” (provided the array is either type ‘Double’ or ‘String’). Unfortunately, to pass data between an array and an Excel range, the array needs to be a ‘Variant’. As such, ‘SetCellVal’ and looping through the array may still be the best option in my case.

Thank you.

[#4]

Most variables use the underlying variant type in VB/VBA.  I think you will find the performance to be MUCH faster if you use a loop to populate an array on the VB side, and then pass that array, as opposed to setting cell values individually.  The inter-process communication time between automation calls will cause a dramatic slowdown when using the individual cell case.

In the example that I referenced, it is true that I create the arrays as Double or String.  However, they are passed as variant arrays (the conversion is implicit).  If you look at the method signature for SetDataVector in the object browser, you will see that JMP is in fact expecting a variant array.  So, you should at least try just passing your variant array from the Excel range to JMP.  If the type can't be implicitly converted, then you will have to copy the elements individually.

Brian Corcoran

JMP Development

[#5]

Thank you for the follow-up.

It appears that ‘SetDataVector’ needs its source array to be type double or a string to work. If it is a variant, the procedure will execute but no contents are transferred into the table. Sequentially transferring the array elements into a second array, while converting them to type double (CDbl), works well.

It should also be noted that the array must be base0 to work as expected. If it is base1, then it shifts the data and misses the first row / adds an invalid last row.

Thanks

[#6]

As I travel along the VBA automation path, it is helpful to know there is implicit conversion of data types in the communications between Excel and JMP. Thanks!