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?

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