Use Console as debug window in VBA

Question

So I have some macros run inside an Excel document and wanted to know if there is a way to frequently output text to a Console window (Basicly using it like the immediate window).

I know there are multiple ways of writing text to files, I just want to display some info on the running process without using the immediate window or other windows inside Excel itself.

Using this helps me to display a single line, but I dont want to open a new window for every line:

Call Shell("cmd.exe /K echo testInfo", vbNormalFocus)

I do NOT want to run a command (except echo maybe?) to execute a task, its just supposed to display text.

Thank you in advance for any advice.

EDIT:

As an addition to @JohnRC 's post I found a solution without external applications:

Call Shell("PowerShell.exe -noexit -command get-content " + strPath + " -wait")

Logging information to a textfile at the location after running the command above does the trick.

Answer 1

This topic is old, but I propose you an alternative solution, that I recently found.

The idea is to have a logger able to handle log messages by streaming them to the Windows console (cmd.exe) via a client/server architecture based on HTTP.

• Client side: the VBA client sends the log records as HTTP requests to the server.
• Server side: the server handles these requests by simply displaying the log messages directly in the console output.

This approach is much faster, and less subject to the vagaries of key simulation on the keyboard.

I implemented it in a framework, called VBA Monologger. To understand the details of this architecture, take a look here: https://6i-software.github.io/vba-monologger/under-the-hood/handler.html#handlerconsole

And below is an example to create a logger for Windows console with this framework.

Public Sub howto_use_logger_console()
    ' Initialize the Logger for windows console
    Dim Logger As VBAMonologger.LoggerInterface
    Set Logger = VBAMonologger.Factory.createLoggerConsole("App")

    ' Use the logger for each severity levels
    Logger.trace "Authentication function call for user 'Bob Morane'." 
    Logger.info "User 'UltraVomit' has logged in successfully."
    Logger.notice "Process completed successfully with minor issues."
    Logger.warning "'Beetlejuice' should not be called more than 3 times."
    Logger.error "An error occurred with the user 'DRZCFOS2'."
    Logger.critical "System is in an unstable state."
    Logger.alert "Action required: unable to generate the dashboard."
    Logger.emergency "A critical failure occurred in the application."
End Sub

When you run this code, it launches a cmd.exe, starts an HTTP web server in it (the web server is coded in Powershell) and waits for logs sent using HTTP requests.

Bonus, the log formatter used in this logger supports ANSI colors ^^.

Answer 2

OK, as I got a couple of downvotes on my earlier answer, I thought I should attempt to provide an actual answer to the request, namely to provide a way of sending log messages to a command prompt window. Here goes...

This solution is implemented as a VBA class that will send messages as comment lines to a separately-running command prompt window that has the text "ExcelLog" in the title. This command prompt must be started separately. The easiest way to do this is to create a shortcut named "ExcelLog" to just run CMD, then when this shortcut is opened the command prompt window will have "ExcelLog" in the title.

Inside the spreadsheet add the code for the class cConsole (below), and then in your VBA code create a global instance of the class and use the method .W "message" to send a text message to the console as a comment line (in this case using the prefix :: to the line to identify it as a comment).

The cConsole class looks for any command prompt window with the requisite title, then sends the comment message to that window. If the window cannot be found, it simply skips the action so the Excel VBA code continues to execute without reporting an error. Also if you open the command prompt window after Excel VBA has started running, cConsole will automatically connect to the window and start/resume sending messages. This means you can close and reopen the command prompt ExcelLog window any time without interrupting the execution of the VBA code.

This seems to work on my setup OK. I think it is a bit more trouble than simply tailing a text file, but - hey, you pays your money and takes your choice.

Here is the code of the cConsole class.

Option Explicit

'// cConsole class
'// This class wraps an interface to a separately-started command prompt
'// window to which messages are sent as comments, so that the command prompt
'// window can be used as a real-time scrolling log from Excel.

'// Each instance of this class creates its own connection to the
'// command prompt window which must have a title containing the text
'// "ExcelLog". If such a window is not open then messages are not
'// logged. The command prompt window can be opened after messages
'// have started, and it will be connected when the next message is
'// sent.

'// The simplest way to set up the necessary command prompt window is to
'// create a shortcut on the desktop the name "ExcelLog" which runs CMD

'// Usage - - - - - - - - - - - -
'//
'//     Dim oConsole As New cConsole
'//     :
'//     oConsole.W "Message to be written to the console"
'//


'// Windows functions to get window handles etc
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" _
(ByVal hWnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Private Declare Function SetForegroundWindow Lib "user32" _
  (ByVal hWnd As Long) As Long


'// Handle of the excel log window
Private hLogWindow As Long


Private Sub Class_Initialize()
'// On instantiation, attempts to find the ExcelLog window
    findExcelLogWindow

End Sub

Public Sub W(sMsg As String)
    '// Public function used to send the given message
    '// as a comment line to the linked window
   SendToConsole ":: " & sMsg
End Sub
Private Sub SendToConsole(Command As String)
    '// Connects to and sends a command line to the command prompt
    '// window that is being used as the log

    Dim res As Boolean

    '// Check that a connection has been made and
    '// attempt to connect if not
    If hLogWindow = 0 Then
        findExcelLogWindow
        If hLogWindow = 0 Then Exit Sub
    End If

        On Error Resume Next

                Do
                    '// Attempt to bring the logging window to the foreground
                     res = SetForegroundWindow(hLogWindow)

                    '// Check if successful, and send the command if so
                    If res Then
                        SendKeys Command & vbCrLf
                        Exit Do
                    Else
                        '// Not successful, so try reconnecting to the logging window
                        findExcelLogWindow

                        '// If we cannot connect, just exit without sending anything
                        If hLogWindow = 0 Then Exit Sub
                    End If

                Loop

                '// Check if there has been any error
                If Err.Number <> 0 Then
                    hLogWindow = 0
                    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
                End If

        On Error GoTo 0

End Sub
Private Function findExcelLogWindow() As Long
    '// This function looks for a command prompt window that has the text
    '// ExcelLog in the title
    Dim nLen As Long
    Dim sData As String

    Dim Class As String
    Dim Title As String

    '// Get handle to the first window
    hLogWindow = 0

    '// Check each window in turn
    Do

            hLogWindow = FindWindowEx(0&, hLogWindow, vbNullString, vbNullString)

            '// Check that a window was found
            If hLogWindow = 0 Then Exit Do

            '// Get the class name of the window
            sData = String$(100, Chr$(0))
            nLen = GetClassName(hLogWindow, sData, 100)
            Class = Left$(sData, nLen)

            '// Get the title of the window
            sData = String$(100, Chr$(0))
            nLen = GetWindowText(hLogWindow, sData, 100)
            Title = Left$(sData, nLen)

            '// Check if the required window has been found
            If Class = "ConsoleWindowClass" And InStr(Title, "ExcelLog") > 0 Then

                '// Initialise the window to remove any prompt text
                SendToConsole "PROMPT $S"

                '// Write some initial messages
                Me.W "*******************"
                Me.W "[" & ThisWorkbook.Name & "] connected to console at " & Now
                Me.W ""

                '// Return the handle to the log window
                findExcelLogWindow = hLogWindow
                Exit Function


            End If



    Loop

    '// The log window was not found, so return zero
    findExcelLogWindow = 0

End Function

I tested this out by handling MouseMove events on an image control in a worksheet:

Option Explicit

Private oCons As New cConsole

Private Sub Image1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    oCons.W "MouseMove " & X & ", " & Y

End Sub

And here is the result

Answer 3

Instead of using a shell to act as a console to log messages, I have used a text file to hold the log, and monitored the output to the file with a tail utility (I have used WinTail from http://www.baremetalsoft.com/wintail/ but I am sure there are others). This is the code, which I put in a separate vba module named Log. Then call Log.W "Message" to log a message.

Option Explicit

'// You need a reference to "Microsoft Scripting Runtime" library in VBA
Private oLog As Scripting.TextStream
Private bErr As Boolean


Private Sub INIT()
    '// Initialise the output log file

    '// Check if log file is already open, or there has been an error
    If bErr Then Exit Sub
    If Not oLog Is Nothing Then Exit Sub


    '// Open the log file for appending
    Dim ofso As New Scripting.FileSystemObject
    On Error Resume Next
    Set oLog = ofso.OpenTextFile("excel.log", ForAppending, True)

    '// Check that open was successful
    If Err.Number <> 0 Then
        MsgBox "Log file error: " & Err.Number & ": " & Err.Description
        bErr = True

        Exit Sub
    End If
    On Error GoTo 0

    '// Write a starting block to the log
    oLog.WriteLine "*"
    W "********************************** START"
    W "* Start of log " & Format(Date, "YYYY-MM-dd")
    W ""

End Sub

Public Sub W(sMsg)
    '// Writes a single line message to the log
    '// Initialize if required
        INIT
    '// Check for log file error
        If bErr Then Exit Sub

    '// Create the log line and write to log file
        Dim st As String
        st = Format(Now, "hh:mm:ss ")
        oLog.WriteLine st & sMsg

End Sub

Public Function ReportErr(Optional Loc As Variant = "") As Boolean
    '// Reports information from the Err object, if an error has occured

    '// Check if error has occurred, exit if not
    If Err.Number = 0 Then ReportErr = False: Exit Function

    '// Set return value
    ReportErr = True

    '// Initialize if required
    INIT

    '// Check for log file error
    If bErr Then Exit Function

    '// Write the error block to the log
    W "*********** ERROR ******* " & IIf(Len(Loc) > 0, "[" & Loc & "]", "")
    W "* Error #" & Err.Number

    If Len(Err.Description) > 0 Then
        W "* : " & Err.Description
        W "*************************"
    End If

End Function

Tailing the log file with WinTail means that output to the log appears immediately it is written, so you can monitor the log as the program is running.