Using Julia with BERT

Starting with BERT version 2, we support the Julia language as well as R. Julia support is new, and less refined, so some things may not work as expected and some things may change.

You can use R and Julia at the same time, and use functions from both languages in Excel.

We recommend running BERT with Julia version 0.6.2 (current). As of version 2.3.0, BERT also supports Julia 0.7.0, but that’s even more experimental. If you want to run with Julia 0.7, see this section at the end.

Installing Julia

If you have already installed Julia, you can use your current installation. Otherwise, you can install using the default windows installer.

If you have Julia installed to the default location (in your home directory), and it’s the right version, BERT should pick it up automatically. If you’ve installed somewhere else, you’ll need to set the home directory.

To do that in BERT, open the console and then open the preferences file using the menu View > Preferences.

Set a value for home in the julia block. You can use environment variables for special directories:


Then restart Excel and open the console. You should see a new shell tab for Julia:


(If you don’t see the Julia tab, double-check your preferences file). The BERT Julia shell is not the default Julia REPL, but it’s similar. Most things that work in the Julia REPL will work.

Julia Functions in Excel

Any function defined in the Main module (i.e. the default namespace) will be exported to Excel automatically. There are some sample functions provided in the functions directory.

Julia functions will be prefaced with Jl. in Excel, so the example Julia function TestAdd is installed as Jl.TestAdd.

When writing functions for use in Excel, a couple of things to know:

  • BERT supports positional arguments only, not named arguments.

  • Arguments are required. If you want an argument to be optional, you must provide a default value.

  • If you want to support variable arguments, use a spread argument.


Here’s the example function Test.Add, which uses a spread argument:

function TestAdd(a...)
  sum(collect(Base.Iterators.flatten(a)))
end

This function adds together all the arguments. The flatten call is for flattening arrays, which would be necessary if you pass in an Excel range as an argument.

Talking to Excel from Julia

You can talk to Excel from the Julia console using the EXCEL object. This basically works the same way as the R scripting object, but with Julia syntax. An example scripting file is included in the examples directory (in your Documents directory, under BERT).

A quick example:

EXCEL.Application.get_Range("B2:C3").put_Value("Hello")

Julia and VBA

If you want to call Julia from VBA, use the spreadsheet functions BERT.Exec.Julia and BERT.Call.Julia. The exec function executes arbitrary Julia code. The call function calls a Julia function, optionally with arguments.

Because these are spreadsheet functions, in VBA you must call them with Application.Run. So for example,


' execute arbitrary code
MsgBox Application.Run("BERT.Exec.Julia", "2+3*4")

' call a function, with arguments
Application.Run "BERT.Call.Julia", "print", "Hello from vba" & vbCrLf

Julia 0.7

BERT now supports Julia 0.7.0 (development) as well. Until there’s an official release, we still default to 0.6.2 (current). If you have a development release installed and want to use it with BERT, here is what you need to do.

If you have installed Julia 0.7.0 with an installer, to the default location (%localappdata%\Julia-0.7.0-DEV) and you don’t have an earlier version of Julia installed, BERT should find it automatically.

If you have both 0.6.2 and 0.7.0 installed on your machine, you can tell BERT which one to use in the Preferences file. Open the BERT console and open Preferences with View > Preferences from the menu. In the Julia section, add a tag field with the value 0.7, like so:


"Julia": {
  "tag": "0.7"
},

Use the tag 0.6 for that version.

If you’ve installed from source or you installed to a non-standard directory, you’ll need to add the home directory to preferences as well. Use both the tag and the home fields:


"Julia": {
  "tag": "0.7",
  "home": "c:\\path\\to\\julia\\home\\directory"
},

Once you’ve updated Preferences, save the file and make sure it says “Preferences OK” in the status bar. Then restart Excel. The banner in the Julia console will indicate the version.