Code Library

LogToTempFile() Function

A quick and dirty method for debugging large strings in VBA.

Mike Wolfe

Jul 4, 2022 • 2 min read

I output debugging messages to the Immediate Window in VBA all the time, but some strings–such as custom ribbon XML–exceed its 200-line limit.

If I want to take a quick peek at the contents of a large string, I use the LogToTempFile() function shown below. It saves the string to a uniquely named file in the Temp folder, registers that file for deletion when the application closes, and returns the name of the created file.

Quick and easy.

Usage

Opening the String in Your Default Text Editor

When combined with Dev Ashish's fHandleFile function, you can use a one-line statement to create the temp file and open it in your default viewer:

fHandleFile LogToTempFile(txt), WIN_MAX

Opening the String in Your Default XML | HTML | CSS Editor

If your string represents some specific type of markup file or code language, you can pass the file extension to the function and use the default editor for that file type:

fHandleFile LogToTempFile(txt, "xml"), WIN_MAX

Maintaining a Running Log

Sometimes you don't want to output a single huge string to the immediate window, but rather a single line every time through a loop. To do that, you grab the file name and then pass that to my FileAppend function each time through the loop:

Dim fpDest As String
fpDest = LogToTempFile("")

Do While Rnd() < 0.9
    FileAppend fpDest, Rnd()
Loop

fHandleFile fpDest, WIN_MAX

The Code

' ----------------------------------------------------------------
' Procedure : LogToTempFile
' Author    : Mike
' Date      : 7/1/2022
' Purpose   : Writes the contents of TextToLog to a temp file and
'               registers it for cleanup on program exit.
' Returns   : Full path to the temp file.
' Notes     - Quick and dirty way to log strings too big for the immediate window.
'           - Combine with fHandleFile to show in default editor.
'           - Creates a time-stamped filename to avoid overwriting of previous files
'               (files may be overwritten if more than one per second is generated)
' Usage     - fHandleFile LogToTempFile(txt), WIN_MAX
' ----------------------------------------------------------------
Public Function LogToTempFile(TextToLog As String, _
    Optional FileExt As String = "txt", _
    Optional FileNamePrefix As String = vbNullString) As String
    
    Dim Prefix As String
    Prefix = FileNamePrefix
    Prefix = Conc(Prefix, Format(VBA.Now, "yyyy-mm-dd_hh-nn-ss"), "_")
        
    Dim fpDest As String
    fpDest = TempFileName(Ext:=FileExt, Prefix:=Prefix)
    
    If FileWrite(fpDest, TextToLog) Then
        LogToTempFile = fpDest
        
        'Register the file for deletion on application close;
        '   see https://nolongerset.com/app-registertempfile/
        App.RegisterTempFile fpDest
    End If
End Function

Dependencies

There are quite a few dependencies for this small block of code, but that's what keeps it compact and readable:

Optional but Helpful Dependencies

The RegisterTempFile method of the clsApp singleton class
Dev Ashish's fHandleFile() subroutine
The FileAppend() function

Image by Yerson Retamal from Pixabay