Log File

Depending on the amount of functionality you may want to create a simple log file.
One way of implementing this is to use a generic error handling routine in conjunction with a text file.
At the top of every subroutine and function add the following two lines of code:

Const sProcName As String = "MySubroutine" 
   On Error GoTo ErrorHandler

At the bottom of every subroutine and function add the following lines of code:

   Exit Sub / Exit Function 
   Call Error_Handle(sProcName, Err.Number, Err.Description)
End Sub / End Function

Generic Error Handling Subroutine

Add the following subroutine to your project:

Public Sub Error_Handle(ByVal sRoutineName As String, _ 
                        ByVal sErrorNo As String, _
                        ByVal sErrorDescription As String)
Dim sMessage As String
   sMessage = sErrorNo & " - " & sErrorDescription
   Call MsgBox(sMessage, vbCritical, sRoutineName & " - Error")
   Call LogFile_WriteError(sRoutineName, sMessage)
End Sub

Using the Scripting Runtime Library

Add the following reference to your project Microsoft Scripting Runtime.

microsoft excel docs

This reference is required if you want to use the File System Object library.

Add the following two global variables to your project.

Public g_objFSO As Scripting.FileSystemObject 
Public g_scrText As Scripting.TextStream

Writing to the Log File

Add the following function to your project:

Public Function LogFile_WriteError(ByVal sRoutineName As String, _ 
                                   ByVal sMessage As String)
Dim sText As String
   On Error GoTo ErrorHandler
   If (g_objFSO Is Nothing) Then
      Set g_objFSO = New FileSystemObject
   End If
   If (g_scrText Is Nothing) Then
      If (g_objFSO.FileExists("C:\temp\mylog.txt") = False) Then
         Set g_scrText = g_objFSO.OpenTextFile("C:\temp\mylog.txt", IOMode.ForWriting, True)
         Set g_scrText = g_objFSO.OpenTextFile("C:\temp\mylog.txt", IOMode.ForAppending)
      End If
   End If
   sText = sText & "" & vbCrLf
   sText = sText & Format(Date, "dd MMM yyyy") & "-" & Time() & vbCrLf
   sText = sText & " " & sRoutineName & vbCrLf
   sText = sText & " " & sMessage & vbCrLf
   g_scrText.WriteLine sText
   Set g_scrText = Nothing
   Exit Function
   Set g_scrText = Nothing
   Call MsgBox("Unable to write to log file", vbCritical, "LogFile_WriteError")
End Function

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext