office-js

Office-JS & VBA Addin How to mix together


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


Solution

  • 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.