Call Scala Functions from Excel/VBA

Question

I know that with C++ one can create DLL files that contain certain functions, which can then be imported into Excel (e.g. via VBA). Let us take the following C++ function

double __stdcall square_it(double &x)
{
    return x*x;
}

which we assume is incorporated in square.dll so that we can use the following VBA import

Declare PtrSafe Function square_it Lib "square.dll" (ByRef x As Double) As Double
Private Sub TestSub()
    MsgBox square_it(4.5)
End Sub

So my question is: is it possible to code a function in Scala and then call it from VBA in a similar fashion?

Here's a [solution](http://stackoverflow.com/questions/11664072/step-by-step-guide-to-get-scala-to-run-on-net) for running Scala with .Net. Presumably, this could be adapted for use with VBA. — David, Sep 14 '15 at 19:17
Scala can produce jar files, since it produces jvm bytecode. So look for ways to run java jars, and they'll apply. A quick scan turned up this article: http://stackoverflow.com/questions/11343769/microsoft-excel-macro-to-run-java-program — Gene, Sep 19 '15 at 23:11

score 4 · Accepted Answer · edited May 23 '17 at 11:52

4

Scala is not going to be any different from Java here, and looking at Java questions such as Can you use Java libraries in a VB.net program? and Calling Java library (JAR) from VBA/VBScript/Visual Basic Classic, there are not good solutions to get the same level of integration you have with VBA/C++ in VBA/Java or VBA/Scala.

You can always use any external channel to comunicate between your VBA and your Scala, such at a shell, the file system, or even http, but that's not going to be as straightforward and efficient as your VBA/C++ example.

Here is what it could look like comunicating through a shell:

In example.scala:

object Example {
  def main(args: Array[String]): Unit = {
    val d = args.head.toDouble
    println(d * d)
  }
}

In example.vba:

Module Example
  Sub Main()
    Dim command As String 
    command = "scala /path/to/example.scala 123"
    Range("A1").Value = CreateObject("WScript.Shell").Exec(command).StdOut.ReadAll
  End Sub 
End Module

edited May 23 '17 at 11:52

Community

1
1

answered Sep 20 '15 at 13:08

OlivierBlanvillain

7,701
4
32
51

Thank you for your answer, but this is still a bit unclear to me. Where exactly (or how) would one reference the example.scala file in the VBA example? – Phil-ZXX Sep 22 '15 at 17:40
The argument of the `Exec` is a command to be run in a shell, so it basically depends on how you would normally run your scala from a terminal. `sbt run` will run a .scala file in the current directory with a main method. I updated the answer with an alternative, more explicit way to run `example.scala` :) – OlivierBlanvillain Sep 22 '15 at 18:56
When I type `scala C:\example.scala 123` into the command line (i.e. cmd) I get the correct output 15129.0 as one would expect. But when I run the VBA script with `command = "scala C:\example.scala 123"` I get the error "The system cannot find the file specified, runtime error -2147024894 (80070002)". Any idea? :/ – Phil-ZXX Sep 24 '15 at 09:54
Mhm, when I write `command = "C:\program files\...\bin\scala.bat C:\example.scala 123"` everything works. – Phil-ZXX Sep 24 '15 at 10:49

Uri Goren · Answer 2 · 2015-09-21T10:34:53.093

Scala runs on the JVM, and VBA does not, Therefore there is no native way to pass objects between them.

There are two alternatives to communicate between Scala and VBA:

One option is to start a new process for each procedure call and parse the output, as @OlivierBlanvillain suggested in his answer.

I think that the preferred method, is communicating in web sockets, and particularly http using a web server.

Web Server

Use a scala web server (there are tons of them), for example scalatra, and each procedure call should be mapped to a http request to localhost:

Scala server code (with scalatra)

class ScalaCall extends ScalatraServlet {

  get("/:func/:params") {
       Calling function {params("func")} with parameters {params("params")}
  }

}

VBA Client code

Public Function ScalaCall(ByVal func As String, ByVal params As String) As String
  Dim WinHttpReq As Object
  Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
  WinHttpReq.Open "GET",   "http://127.0.0.1/" & func & "/" & params, False
  WinHttpReq.Send
  If WinHttpReq.Status = 200 Then
      ScalaCall = WinHttpReq.ResponseBody
  End If
End Function

Calling ScalaCall("f","x") on VBA should invoke a request to the scala server, that will output Calling function f with parameters x

score 1 · Answer 3 · answered Sep 24 '15 at 14:09

1

You could use Obba - A Java Object Handler for Excel, LibreOffice and OpenOffice.

answered Sep 24 '15 at 14:09

Federico Pellegatta

3,977
1
17
29

Call Scala Functions from Excel/VBA

3 Answers3

Web Server

Scala server code (with scalatra)

VBA Client code