This tutorial has been created with Excel 2002 and J501. Expect different behavior with other versions!
It will help to have the Excel help file readily available.
Introduction
The purpose of COM Automation is to allow a client program to run functions in a server program, and the basic idea is pretty straightforward - simply load J from Excel, then send it the required J functions for execution. In practice is it helpful to create Excel macros that provide cover functions for the basic tasks such as loading J, reading cells for transmission to J and so on. Thus you typically program with a mixture of J functions and Excel macros.
In Excel, you can enter these method names in upper or lowercase. When you enter names in Excel, it gives them its default capitalization. Here we use lowercase throughout.
Most of the time when things go wrong, you can simply shut down J and Excel and start again.
Sometimes when you edit Excel macros, Excel closes down J - it closes the COM Automation object which may in turn cause J to close. You will then need to re-open the J COM Automation object. If J has closed and you try to run an COM command, the error message is "Object variable not Set". This problem occurs only while you are developing Excel macros, and should not occur when your application is in use.
One of the "user-friendly" features of Excel is to change your entry in a cell if it thinks it may be incorrect. For example, "i.5" gets changed into "I.5". To get around this, enter more letters, then backspace and delete the extra entries, for example, instead of "i.5' try entering "ii.5".
Tutorial
Start by unloading all applications, then loading Excel. Open a new workbook if none is shown.
Bring up Visual Basic (Alt-F11 or Tools|Macro|Visual Basic Editor), and insert a new module sheet (Insert|Module).
With the module sheet visible, select menu item Tools|References and check J DLL Serve and click OK.
In the module sheet, enter:
Public js As Object
Sub jopen()
Set js = CreateObject("jdllserver")
js.Quit
js.Do "BINPATH_z_=:1!:46''"
js.Do "ARGV_z_=:,<'oleclient'"
js.Do "(3 : '0!:0 y')<BINPATH,'\profile.ijs'"
End Sub
The function jopen will be used to load the JDLLServer. Note that you can only run this once - you will get an error at this point if you try to open the server twice.
The first statement declares the name js that will be used for the JDLLServer.
js.Quit ensures that when Excel is closed, the J server will automatically terminate.
To load J, enter: jopen
Sending commands to J
The required function is do, which takes a J sentence as its argument. Note that Excel strings are delimited by the double quote, so that J quotes can be entered as is, and need not be doubled. Try:
js.do "i.4 5"
Retrieving values from J
The function get retrieves a value from J, as a Variant datatype. Variants cannot be displayed directly in the Immediate window, but can be assigned to a worksheet range. For example:
Set value of x in J:
js.do "x=: i.4 5"
Retrieve value of x into Excel variant y:
js.get "x",y
Set value of y into the worksheet:
Worksheets("sheet1").Range("a1:e4")=y
Now switch to Sheet1 to see that the value of y has been written in.
Utilities
Now lets take a look at the J OLE utilities in file system\examples\ole\excel\jsutil.txt. Copy and paste the contents of this file into your Excel module.
The utilities available are:
jdopen | open JDLLServer |
jcmd (string) | execute J command, return result as variant |
jcmdc string,r,c,h,w | execute J command, store result in active sheet at row,col,height,width |
jcmdr string,range | execute J command, store result in active sheet at range |
jdo string | execute J command |
jget(x) | get J noun x |
jloadprofile | load standard J profile |
You can customize these or add your own utilities.
jcmd
In Excel, switch to Sheet1 and in cell B3 enter:
=jcmd("+/2 3 5 7")
The statement should be executed in J, and the result (17) displayed in Excel.
Try:
In cell B5 enter: 12
In cell B6 enter: 15
In cell B7 enter: =jcmd(B5 & "*" & B6)
B7 displays the result (180). Note that if you now change B5 or B6, then B7 will be recalculated.
In general, jcmd can be used for calculations which return a single value to be displayed in the current cell. The right argument is the sentence to be sent to J.
This method is really only suitable for simple calculations. Typically, you will want to run calculations that return a range of results to Excel and you set up such calculations by invoking an Excel macro explicitly, for example, by selecting Tools|Macro|Macros|Run or pressing an assigned hot-key.
jcmdc, jcmdr
These utilities execute a J expression, displaying the result in a range in the active sheet. Function jcmdc specifies the range as 4 numbers: topleft row, column, number of rows, number of columns. Function jcmdr specifies the range in the traditional alphanumeric notation, for example: C6:E10.
We will create a macro run to test these and subsequent expressions. Switch to the module and enter:
Sub run()
jcmdc "?3 4$10", 2, 3, 3, 4
End Sub
Next, return to the worksheet, select Tools|Macro|Macros, highlight run and click Options. Enter Ctrl-r as the shortcut key and click OK. If J has been closed down, then either reload the spreadsheet or re-run auto_open to load it again. Close the Macro dialog, switch to Sheet1 and press Ctrl-r. The macro should run and display the result. Press Ctrl-r again to re-run the macro.
jsetc, jsetr
These utilities set values in J, from a range in the active sheet. As with jcmdc and jcmdr above, jsetc specifies the range as 4 numbers and function jsetr specifies the range in the traditional notation. Switch to the module and edit run to:
Sub run()
jsetr "Y", "D3:F4"
End Sub
Switch to Sheet1 and as before use Tools|Macro to select Ctrl-r as a shortcut key for the macro. In the worksheet, press Ctrl-r. Then click on the J session and display Y (these are random numbers so the exact values will likely differ):
Y +-+-+-+ |4|8|8| +-+-+-+ |7|3|1| +-+-+-+Change run to:
Sub run()
jsetc "Y", 2, 3, 3, 4
jcmdc "+/\>Y", 7, 3, 3, 4
End Sub
Switch to Sheet1, use Tools|Macro to select Ctrl-r as the shortcut key, then in the worksheet, press Ctrl-r. The macro will read the numbers in the upper range and display the sum scan in the lower range. Now if you change one of the numbers in the upper range, for example E2, press Ctrl-r to update the lower range.