Visio crashes if Excel says "Microsoft Excel is waiting for another application"

[Hey Ken]

Folks:

   I have a Visio application that opens a spreadsheet and does a lot of stuff with it.  I mentioned the basics of the method for accessing Excel from Visio in a recent post. 

   It takes a long time for my Visio app to do its processing, so I tried using the spreadsheet’s StatusBar to display percent complete (native Visio has no such capability).  That worked fine, but after a while Excel gets impatient and says, "Microsoft Excel is waiting for another application to complete an OLE action."  In general the popup can be ignored—except if you’re writing to the StatusBar.  If you do that while the OLE popup is being displayed, Visio crashes and says, “Run time error ‘50290’.  Application-defined or object-defined error.”  Take out the line that updates StatusBar and it works fine, OLE popup or not.  So much for my percent complete.

   Don’t ask me why it crashes; it just does.  Think of it as a pothole in the VBA road that you need to steer around.  I’m using Visio Professional 2016 and Excel Office 365 ProPlus.

   - Ken

[Croc]

This doesn't seem like a Visio developer error.
It's just that the two apps are blocking each other.
Visio cannot access the Excel window due to an open message modal window. And Excel cannot complete its operation because Visio is holding onto it.
If you use a Word-Excel pair instead of a Visio-Excel pair, then most likely you will see a similar error.

[Hey Ken]

Croc:

   Excellent point.  I’d agree.

   But that leaves me with an unasked question: WHY is Excel telling me that?  It doesn’t need to; I already know it’s taking a while to run the Visio code.  I just wish it would do it silently.  Looks like a question for an Excel forum.

   Thanks for the thought,

   - Ken

[Nikolay]

Do you use DoEvents in Visio when processing things (periodically)? If you don't, then you could try that. Calling this may get rid of the "OLE" popup.

I mean, instead of:

Code

For shape in Page.Shapes
  ' do something with shape
Next

You could try this:

Code

For shape in Page.Shapes
  ' do something with shape
  DoEvents
Next

[Croc]

Hey Ken, how long after the start does the error appear?
I am using Visio 2019 and Excel 2019. I write data to an Excel cell and status bar within some minutes. I am unable to see the error.
Here is my test program

Code

Sub AccessExcelFromVisio()
    Dim oldT As Long
    Dim newT As Long

    Dim ExcelApplication As Variant
    Dim YourWorkbook     As Workbook
    Const FileName = "C:\Work\Ken\Test.xlsx"
    
    Set ExcelApplication = CreateObject("excel.application")
    ExcelApplication.Visible = True
    Set YourWorkbook = ExcelApplication.Workbooks.Open(FileName)
    
    YourWorkbook.Sheets(1).Range("A1").Value = "Whatever"
    oldT = 0
    For i = 1 To 100
        Do
            newT = Timer()
            YourWorkbook.Sheets(1).Range("A1").Offset(i, 1).Value = newT
            ExcelApplication.StatusBar = "step " & i
        Loop Until newT <> oldT
        oldT = newT
    Next
    
    YourWorkbook.Close
    Set YourWorkbook = Nothing
    ExcelApplication.Quit
    Set ExcelApplication = Nothing

End Sub

[Hey Ken]

Croc:

   Thanks for looking into this.  It takes about a minute or so before the message appears. 

   My apologies, but I did leave out one important fact.  Once Visio creates the Excel instance, the next step is for the Excel instance to asynchronously connect to Visio and execute a Visio Sub (via right-click menu choice).  It’s that Visio Sub that takes a long time to run, and Excel is waiting for Visio to finish what Excel initiated.

   Here is the tested Excel VBA code for invoking a Visio Sub from the Excel instance that was created by the Visio Sub:

Code

Sub TalkToVisio ()

Dim VisioApp        As Object
Dim VisioAppDoc     As Object

Set VisioApp = GetObject(, "Visio.Application") 'returns Visio app if it is running, error if not
If Err <> 0 Then
    MsgBox " Visio is not available. ", vbInformation
Else
    Set VisioAppDoc = VisioApp.ActiveDocument
    VisioAppDoc.ExecuteLine "ThisDocument.LongRunningVisioSub"
    End If
Set VisioApp = Nothing

End Sub

   As an aside, I’d be happy to hear of a better way of connecting Visio and Excel and having Excel execute the Visio Sub.  This approach has the problem that only one instance of Visio can be running, or else the Excel macro could possibly latch onto the wrong instance of Visio.  But this approach works, and the user instructions tell them to first close all other instances of Visio.  And if it does grab the wrong instance, Excel crashes with an “I told you so!” message.

   Here's an extra credit question: Do the same connection, except have Excel create the Visio instance, and Visio runs an Excel Sub.  But that has the same flaw: how does Visio know which instance of Excel created it?

   Someday I will stop living on the extreme edges of Visio functionality.  But not today.  Besides, it's way too much fun!

   Thanks again,

   - Ken

[Croc]

Hey Ken,
You can try transferring Visio Document as an object to Excel.
Program in Visio:

Code

Dim ExcelApplication As Variant
Dim YourWorkbook     As Workbook

Dim oldT As Long
Dim newT As Long

Dim Flag As Boolean

Sub AccessExcelFromVisio()

    Const FileName = "C:\Work\Ken\Test.xlsm"
    
    Set ExcelApplication = CreateObject("excel.application")
    ExcelApplication.Visible = True
    Set YourWorkbook = ExcelApplication.Workbooks.Open(FileName)
    
    Flag = False
    YourWorkbook.Sheets(1).Range("A1").Value = "Whatever"
    YourWorkbook.Application.Run "ThisWorkbook.ex", ActiveDocument
    
    Do
    Loop Until Flag
    
    YourWorkbook.Close
    Set YourWorkbook = Nothing
    ExcelApplication.Quit
    Set ExcelApplication = Nothing

End Sub

Sub LongRunningVisioSub()
    oldT = 0
    For i = 1 To 10
        Do
            newT = Timer()
            YourWorkbook.Sheets(1).Range("A1").Offset(i, 1).Value = newT
            ExcelApplication.StatusBar = "step " & i
        Loop Until newT <> oldT
        oldT = newT
    Next
    Flag = True
End Sub

Program in Excel:

Code

Sub ex(arg1 As Visio.Document)
    arg1.ExecuteLine "ThisDocument.LongRunningVisioSub"
End Sub

Visio will call the macro in Excel and pass the Visio document as a parameter.
Excel can then call macros from that document. Including LongRunningVisioSub.