Use Console as debug window in VBA

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.

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