Call VBA Procedure From Another App (and Get Return Values!)?

[Visio Guy]

Hi All,

I have a situation where I want to call macros in a Visio document's VBA project from Excel.

The Visio document has an .ExecuteLine method that works fine, but you can't get any data returned. Does anybody know if there is another way to call Subs and Functions in another app's VBA project?

Cheers,

- Chris

[JuneTheSecond]

Hi,

Though this is a very awkward way and ugly code.

Code Select Expand


Sub test()
    Dim visObject As Visio.Application
    Dim myText As String
    Set visObject = GetObject(, "visio.application")
    visObject.ActiveDocument.ExecuteLine "ActivePage.Shapes(1).text= Module1.myText"
    myText = visObject.ActivePage.Shapes(1).Text
    Debug.Print myText
End Sub




[scott]

Chris,

Each Excel instance has a hidden data store that can be accessed from VBA. The means for doing so is obscure but it does work. One way to achieve your goal might be to:

• modify Visio VBA to attach to the running Excel instance and write one or more values to Excel's hidden data store
• modify Excel VBA to read one or more values from the hidden data store

Having done that the sequence of events would be:

• Excel calls Visio
• Visio VBA does its thing then writes one or more values to Excel's hidden data store
• Visio terminates
• Excel retrieves hidden data values

If this sounds useful, let me know and I can provide the skeleton code.

Regards,
Scott

[scott]

So... this problem kind of intrigued me... see the attached files.

Open the Visio drawing.
Open the Excel workbook.
Type CTRL+SHIFT+V to run the Excel macro.

Scott

[scott]

So, Chris -- I'm curious... is the technique in my sample code of any use? I thought it was kinda cool when I ran across it, but then maybe that's just me.   

[Yacine]

This topic is now more than 9 years old, but I ran into this same problem. I used Junichi's solution (as so many others ... thank you J.) and thought I could post my resulting code.

VBA:

Code Select Expand


Function functionResult(FUNC As String, Optional ARGS As String) As String

    On Error Resume Next
    If ActiveDocument.DocumentSheet.CellExists("user.temp", False) = False Then
        ActiveDocument.DocumentSheet.AddNamedRow visSectionUser, "temp", visTagDefault
    End If
    If IsMissing(ARGS) Or ARGS = "" Then
        t = "ActiveDocument.DocumentSheet.Cells(" & Chr(34) & "user.temp" & Chr(34) & ").FormulaU = Chr(34) & " & FUNC & "() & Chr(34)"
    Else
        t = "ActiveDocument.DocumentSheet.Cells(" & Chr(34) & "user.temp" & Chr(34) & ").FormulaU = Chr(34) & " & FUNC & "(" & Chr(34) & ARGS & Chr(34) & ") & Chr(34)"
    End If
    Debug.Print t
    ActiveDocument.ExecuteLine (t)
    res = ActiveDocument.DocumentSheet.Cells("user.temp").ResultStr("")
    ActiveDocument.DocumentSheet.Cells("user.temp").FormulaU = ""
    functionResult = res
End Function

I actually needed it for my Visio-IPython library, whom's code would be as follows.

Code Select Expand

def vFunction(func, args = ''):
    # Since ExecuteLine does not return values.
    visSectionUser = 242
    visTagDefault = 0
    tempCell = 'funcResult'
    if vDoc.DocumentSheet.CellExists("user." + tempCell, False) == False :
        vDoc.DocumentSheet.AddNamedRow (visSectionUser, tempCell, visTagDefault)
   
    if args == None or args == "":
        t = "ActiveDocument.DocumentSheet.Cells(" + chr(34) + "user." + tempCell + chr(34) + ").FormulaU = Chr(34) & " + func + "() & Chr(34)"
    else:
        t = "ActiveDocument.DocumentSheet.Cells(" + chr(34) + "user." + tempCell + chr(34) + ").FormulaU = Chr(34) & " + func + "(" + chr(34) + args + chr(34) + ") & Chr(34)"
    #print(t)
    vDoc.ExecuteLine (t)
    res = vDoc.DocumentSheet.Cells("user." + tempCell ).ResultStr("")
    vDoc.DocumentSheet.Cells("user." + tempCell).FormulaU = ""
    return res
vDoc stands for activeDocument, it is defined elsewhere and belongs to the core library.

I tested the code with a drawing of abt. 2500 shapes and 500 relevant ones (having data). Iterating from outside (MS Access or IPython) takes quite a long time (Access: roughly 1/2 min, IPython with win32com well over 1 min).

Letting Visio doing the heavy lifting job reduced it to a bat of an eye.
I'm so thrilled, I thought I could share it.