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