How can I interrupt VBA routines via C# Interop Automation? RRS feed

  • Question

  • Good Day,

    I have a program that automates excel quite extensively via C# interop automation. 

    A feature I must build requires that I run a VBA method if it exists in the workbook that part is working fine by using Application.Run("ThisWorkbook.SubroutineName")

    I am most interested in being able to put a time limit on how long the VBA routine is aloud to run so that I can cleanly cancel the VBA and return control to the main application.

    Any assistance anyone may have would be greatly appreciated.

    Tuesday, September 17, 2019 11:44 AM

All replies

  • To:  Scott
    Re:  stopping VBA code

    Your posted question does not provide details.
    Normally VBA code does not just keep running, it should do something and exit.
    Some options:
    If you have an endless loop of some sort,
    you could add a loop counter (Long type) and exit after x loops.

    If you have error handling (strongly recommended) in the code,
    you could add the DoEvents function, within the code, so the code can capture pressing the Escape key and exit.

    If you definitely want a time limitation the
    Timer function returns a Single representing the number of seconds elapsed since midnight.
    Sub Demo()
    Dim PauseTime As Single
    Dim Start     As Single

    PauseTime = 5    ' Set duration.
    Start = Timer      ' Set start time.

    Do While Timer < Start + PauseTime
      ' do important stuff
    DoEvents             ' Yield to other processes.
    MsgBox "times up"
    End Sub

    Free Excel add-ins and workbooks at MediaFire
    ('Calculate Payments' is worth a look)

    Tuesday, September 17, 2019 1:18 PM
  • The point is that the VBA will not be my own, I am writing the C# Automation. Workbook Developers will write the VBA in workbooks managed by my application. My interest is in being able to provide application responsiveness despite issues in VBA code that I do not control.

    Tuesday, September 17, 2019 2:38 PM