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 for running Scala with .Net. Presumably, this could be adapted for use with VBA. — David
– David, Commented Sep 14, 2015 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: stackoverflow.com/questions/11343769/… — Gene
– Gene, Commented Sep 19, 2015 at 23:11

Community · Accepted Answer · 2017-05-23 11:52:16Z

4

+100

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, 2017 at 11:52

CommunityBot

11 silver badge

answered Sep 20, 2015 at 13:08

OlivierBlanvillain

7,7784 gold badges34 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Phil-ZXX Over a year ago

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?

OlivierBlanvillain Over a year ago

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

Phil-ZXX Over a year ago

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 Over a year ago

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

Uri Goren · Accepted Answer · 2015-09-21 10:34:53Z

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

Federico Pellegatta · Accepted Answer · 2015-09-24 14:09:47Z

1

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

answered Sep 24, 2015 at 14:09

Federico Pellegatta

4,0171 gold badge20 silver badges30 bronze badges

Collectives™ on Stack Overflow

Call Scala Functions from Excel/VBA

3 Answers 3

4 Comments

Web Server

Scala server code (with scalatra)

VBA Client code

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Web Server

Scala server code (with scalatra)

VBA Client code

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related