IE-based debug code: how to replace the use of IE?

[EdNerd]

I have incorporated a debug code based on Debugging VBScript that allows a running record of what is going on as code runs. The benefit of using IE is getting a separate window that persists even if the code errors out and forces the Excel application to quit; following the chain of "debug" writes allows you to see at what step the error occurred.

Now that IE is no longer supported - it's still on our computers at work, but we have no idea when it will disappear, what else should I look at to replace it? Would Edge work? Would Notepad be better? What would you recommend?

 

[Dan_W]

If you're encountering problems with your code (and with Excel constantly(?) crashing, which is pretty extreme), you may want to try stepping through the code with the F8 button to track exactly what is happening with the code at each stage. Also, you may already have done this, but to the extent that your code contains any "On Error Resume Next" lines in it, I'd advise deleting those until you've got to the bottom of (and solved) the errors.

As for a persisting Debugging window, I've not come across this approach to debugging before.
If you wanted a record of what is happening with the code that persists after Excel crashes, you could always just log the information to a text file. You could then load that text file in Notepad or a browser, I suppose. If you're comfortable with Internet Explorer, a further option is using a WebBrowser control on a userform. My understanding is that the webbrowser control is going to be supported until 2028. For this reason, I don't expect Internet Explorer is going to disappear until then given that they're powered by the same Trident Engine, though I suspect this may depend on whether or not you're using Windows 11, The problem with using a userform is that in would usually belong to the same instance as the one that you're running the code in, and that is crashing, so that wouldn't take you much further. You could, however, instantiate another instance of Excel that would be separate from the one you're running your code in - it would not crash when the primary instance of Excel crashes, but this seems like a very roundabout way of getting to this result.

Just a few thoughts. Does that help at all? Am happy to find some logging code or something if you think it might be useful.

 

[EdNerd]

Hi, Dan. Most of the time when my code encounters a problem and crashes, it's because a user has introduced an "exception" I would never have thought of and so did not factor in a trap or work-around. I thought of loading all my "debug" comments into an array and then writing it to a text file. But if the code crashes and the application quits, the array vanishes as well. That's why a separate window not affected by Excel or VBA works so well -- I can walk down the comments (which are liberally scattered throughout the code to tell me what variable names are set to & what actions have been performed), see what activity the code was in the middle of when it quit, examine variables, and generally troubleshoot that way. The problem with F8 is that it always works well on *my* computer!! But I need to know what happened on their computer in real-time.

If IE is likely to be hanging around for some time (it's been removed from all the menus and shortcuts on our computers at work, but it's still there in its directory), then I won't worry about it too much. Just hate to be caught by surprised.

 

[Dan_W]

Well, on the offchance that you or someone else might need it, here is a very basic logging subroutine. To run it, you just need to call it with:

Log "This is the message"

This will, by default, append the log message to a log textfile which exists as D:\Temp\VBALog.Txt with a time/date stamp. It will also, by default, output the message with the time/date stamp to the immediate window:

[06-09-2022 23:58:27] This is the message

You can customise it how you like. For example:

Log "This is the message", False, False, "C:\MyRealLog.Txt"

This won't add a timestamp or output the message to the immediate window, but will output the simple message to the designated textfile. If the text file doesn't already exist, it will create it.
I would also suggest adding the Log code to the error handling portion of the code.

Public Sub Log(ByRef LogMessage As String, _
               Optional AddTimeStamp As Boolean = True, _
               Optional OutputToImmediateWindow As Boolean = True, _
               Optional FileName As String = "D:\Temp\VBALog.TXT")
    
    On Error GoTo ErrHandler
    Dim FileNum As Long
    If AddTimeStamp Then LogMessage = "[" & Format(Now, "dd-mm-yyyy hh:nn:ss") & "] " & LogMessage
    If OutputToImmediateWindow Then Debug.Print LogMessage
    FileNum = FreeFile
    Open FileName For Append As #FileNum
    Print #FileNum, LogMessage
    Close #FileNum

ErrHandler:
    If Err.Number <> 0 Then MsgBox "Error " & Err.Number & vbNewLine & Err.Description
End Sub

Hope that helps.