Guides/OLE Server for Excel
These examples have been created with Excel 2010 and J804 on Windows 8.1.
Introduction
In Excel you can load J and then send it expressions for execution. The results can be obtained either as results of Excel macro functions, or written directly to an Excel worksheet.
It is useful to have Excel utility macros for standard tasks such as loading J, reading and writing cell ranges and so on. Thus you typically program with a mixture of Excel macros and J functions.
Troubleshooting
To start with, you create a J Automation object. If all goes well, you can use this same object throughout the session. However, sometimes when you are editing Excel macros, Excel closes the J Automation object, which needs to be created again. This should occur only when you are developing Excel macros, not in production use.
Also, when working with J sentences, Excel may not recognize the J entry and try to correct it. For example, "i.5" may be changed into "I.5". To work around this, enter more letters, then backspace and delete the extra entries, for example, instead of "i.5" try entering "ii.5", then delete the first "i".
The debugging environment for Excel macros is very primitive. In practice it is best to thoroughly debug your J programs before trying to call them from Excel.
Entering Excel Macros
To enter Excel macros, first ensure that the Developer tab is visible. If not, select menu File|Options|Customize Ribbon and add the Developer tab. Next, select the Developer tab and click the Macros button.
Utility Macros
Some utility macros for calling J are available in File:Jole excel utils.txt. You should cut and paste these into the Excel macro editor. Later on, you can customize and add to them as required.
There are the following definitions:
js - this declares js as an object, which will represent the J server:
Public js As Object
jopen - this initializes the J server. In production it need only be called once at outset. When developing, it needs to be called whenever Excel resets the server. Note that this call loads the standard profile, though it is not essential to do so, and can omitted if preferred:
Sub jopen() On Error GoTo Fini Set js = CreateObject("jdllserver") jloadprofile Fini: End Sub
jloadprofile - this loads the standard profile.
Sub jloadprofile() jdo "BINPATH_z_=:1!:46''" jdo "ARGV_z_=:,<'oleclient'" jdo "(3 : '0!:0 y')<BINPATH,'\profile.ijs'" End Sub
jdo - this runs a J sentence. If a result is required, assign it and read it in another call.
Sub jdo(s As String) ec = js.Do(s) If ec Then MsgBox "Error code: " & Str(ec) End Sub
jgetb - this returns the value of a J variable.
Function jgetb(s As String) As Variant ec = js.GetB(s, v) If ec Then MsgBox "Error code: " & Str(ec) jgetb = v End Function
jcmd, jcmdc, jcmdr - these are cover functions for jdo followed by jgetb.
- jcmd returns a single result, e.g. a single number
- jcmdc gets a table from J and writes it to an Excel range, given as row and column numbers.
- jcmdr is like jcmdc, but writes to Excel using the traditional range format, e.g. "D3:E7"
Function jcmd(s As String) As Variant jdo "JXP=: " & s jcmd = jgetb("JXP") End Function Sub jcmdc(s As String, r As Integer, c As Integer, h As Integer, w As Integer) Dim x As Integer, y As Integer ActiveSheet.Range(Cells(r, c), Cells(r + h - 1, c + w - 1)) = jcmd(s) End Sub Sub jcmdr(s As String, r As String) ActiveSheet.Range(r) = jcmd(s) End Sub
jsetc, jsetr - these write values in a cell range to a J variable, and correspond to jgetc and jgetr:
Sub jsetc(s As String, r As Integer, c As Integer, h As Integer, w As Integer) Dim x As Integer, y As Integer v = ActiveSheet.Range(Cells(r, c), Cells(r + h - 1, c + w - 1)).Value ec = js.Setb(s, v) If ec Then MsgBox "Error code: " & Str(ec) End Sub Sub jsetr(s As String, r As String) v = ActiveSheet.Range(r).Value ec = js.Setb(s, v) If ec Then MsgBox "Error code: " & Str(ec) End Sub
Examples
In the module sheet, cut and paste in the J examples in File:Jole excel examples.txt. Ensure that your Excel worksheet is visible and clear it of any contents. Then in the Immediate box, step through the examples below, by entering their names, i.e.
jopen print jcmd("2+3") jtest1 jtest2 ... jtest7
Note that jopen should be rerun whenever Excel resets the server, but otherwise it is needed only at outset.
In some of the examples, J generates random numbers. For these, you can try assigning a shortcut key to the example, then repeatedly pressing the key.
1. open the J server:
jopen
2. test J is working:
print jcmd("2+3")
3. a subroutine that calls J and returns a random number in the cell "A1":
Sub jtest1() ActiveSheet.Cells(1, 1) = jcmd("10+?10") End Sub
Rerunning this should give different results.
4. these subroutines call J, returning random tables to the worksheet:
Sub jtest2() jcmdc "?3 4$10", 2, 2, 3, 4 End Sub Sub jtest3() jcmdr "o.?3 4$10", "b6:e8" End Sub
5. this writes values from the worksheet to J, adds the columns, and returns the total to the spreadsheet. You can change the values in the table, then rerun the subroutine to update the totals:
Sub jtest4() jsetr "Y", "b6:e8" jcmdr "+/>Y", "b9:e9" End Sub
6. these call J, returning character and boxed data:
Sub jtest5() ActiveSheet.Cells(11, 1) = jcmd("'abc'") End Sub Sub jtest6() Dim js As Object jcmdr "123;'abc';1000", "a12:c12" End Sub
7. this is an example of writing out the J result cell by cell:
Sub jtest7() Dim y As Variant y = jcmd("2 2$123;'abc';1000") ActiveSheet.Cells(13, 2) = y(0, 0) ActiveSheet.Cells(13, 3) = y(0, 1) ActiveSheet.Cells(14, 2) = y(1, 0) ActiveSheet.Cells(14, 3) = y(1, 1) End Sub