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?

Langlauf

Here's a solution for running Scala with .Net. Presumably, this could be adapted for use with VBA. – Librarianship

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: #11344269 – Photosensitive

Answer 1

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

Harms

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? – Langlauf

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 :) – Harms

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? :/ – Langlauf

Mhm, when I write command = "C:\program files\...\bin\scala.bat C:\example.scala 123" everything works. – Langlauf

Answer 2

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

Govern

Answer 3

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

Engen