Office-JS & VBA Addin How to mix together

Question

Is it possible to place OfficeJS command button to Custom VBA Addin panel built like .xlam file?
* I still have hope to mix VBA and OfficeJS

Answer 1

This is untested, but I believe something along these lines will do the job:

Use the CustomXMLPart.dataNodeReplaced event:

function addNodeReplacedEvent() {
    Office.context.document.customXmlParts.getByIdAsync("vbaJSBridge", function (result) {
        var xmlPart = result.value;
        xmlPart.addHandlerAsync(Office.EventType.DataNodeReplaced, function (eventArgs) {
            // do stuff with xmlPart
            // here you should be able to receive information sent from VBA,
            // and return any data necessary.
        });
    });
}

In VBA use:

Dim part As CustomXMLPart 

' Returns a custom xml part by its ID:
Set part= ActiveDocument.CustomXMLParts.SelectByID("vbaJSBridge") 

part.LoadXML("<data id="vbaJSBridge">some data</data>")

As said I am unsure whether this will work totally, but it's a nice (kinda hacky) method of doing the job. Alternatively you could set up a HTTP server with VBA, which you can send HTTP requests to the JavaScript. To do that, you'd have to use Mswsock.dll and call the accept method, (I think).

---

EDIT:

So many moons have passed since my initial answer and now I do have a little more detail. I tried using the NodeAfterInsert and NodeAfterReplace VBA events alongside customXmlPart.setXml(sXML);, however this does not trigger an event. This is an issue which Microsoft has chosen not to fix. As such there is no event-driven communication possible currently.

In which case the only option, via XML, is to poll for changes with Application.OnTime() in Excel VBA and setInterval() in Javascript.

The HTTP server is still a possibility but would at least require hosting a HTTP server from VBA. Entirely possible (here is a C++ example), but requires deep knowledge of Winsock API and low level programming.

---

Edit 2

I've created a library which facilitates 1-way message sending.

1. Download and import the VBA code
2. Import the scriptlab gist
3. Download and run the sample VBA code.

---

'Requires JsBridge from here:             https://github.com/sancarn/VbaJsBridge
'ScriptLab test can be downloaded here:   https://gist.github.com/sancarn/b974b650f4b451ff2de51861af1671b1
Sub test()
  Dim js As JsBridge: Set js = JsBridge.Create("test")
  Call js.SendMessageSync("hello world")

  Dim col as new collection
  For i = 1 to 10
    'Add action to a collection so we can check it's status later
    col.add js.SendMessage("hello " & i)
  next
End Sub

You should see that we can print "hello world" and "hello 1" ... "hello 10", in the JavaScript console.

Sending data from JavaScript to VBA can use the same bridge but with the for="vb" attribute. However this isn't connected up yet, mind that this would require polling on the VBA side, likely from a workbook/worksheet object.

Answer 2

No, you cannot interact with VBA macros or COM add-ins from a web add-in. Remember that web add-ins are just like web pages, in that they are sandboxed and cannot communicate with the OS or installed programs (at least without helper or broker applications/libraries).

Answer 3

So, this is a bit old, but I've discovered a method of sending a message to VBA from officeJS. It relies on the CustomXMLParts.PartAfterAdd event.

Essentially in vba create a monitor class something like::

Public WithEvents parts as CustomXMLParts
Private Sub Class_Initialize
  set parts = ActiveWorkbook.CustomXMLParts
End Sub
Private Sub parts_PartAfterAdd(ByVal objPart As CustomXMLPart)
  MsgBox("Message received!")
end Sub
Private Sub parts_PartBeforeDelete(ByVal objPart As CustomXMLPart)
  MsgBox("Message received!")
end Sub

Now you can add or remove customXML and this event will fire, but the way officeJS adds a customXMLPart is a bit odd. It creates a blank message and then alters it. But the event fires with the message blank. Also execution is frozen while in the method, so you can't just use Application.Wait until the message is non-empty. You also should not delete the CustomXMLPart from vba as that will throw an error in javascript.

What you can also do is use this in conjunction with a CustomProperty to send a message, with caveats. The value must be between 1 and 255 characters, 0 causes an OOM exception, and anything above 255 will be truncated.

I need this hacky workaround to send a message from officeJS to call some VBA to do functionality only available in VBA. Since the javascript won't resume until the eventhandler finishes, I can also write a message on the same property back to officeJS to report status or whatnot.

Whatabout initiating communication from VBA? Sadly, I haven't found a non-intrusive way to do that.

Edit: The documentation around these events are awful, but CustomXMLParts.PartAfterLoad will fire after the XML was populated. Meaning you can simplify everything and just have a single XML event to listen on to send messages to vba.

I've included a gist with the vba monitor that will listen to xml messages of a particular namespace: https://gist.github.com/mburbea/5300f41173a2eaae477dc4eb7cc69db1