Jump to content




Recent Status Updates

  • Photo
      30 Sep
    rhossis

    laptop hard disk seated beneath motherboard but with no access panel. 7 hours to replace :(

    Show comments (3)
  • Photo
      19 Sep
    Chall

    I love it when you go to write a help thread, then while writing, you reach an enlightenment, and figure it out yourself.

    Show comments (3)
View All Updates

Developed by Kemal Taskin
Photo
- - - - -

Excel logging module

logging module vba

This topic has been archived. This means that you cannot reply to this topic.
7 replies to this topic

#1 christhedon

christhedon

    CC Newcomer

  • Member
  • PipPip
  • 10 posts

Posted 31 January 2013 - 03:58 AM

How would you improve this logging module?


Option Explicit
Public Enum LogLevel
  logDebug
  logInfo
  logwarn
  logError
  logFatal
  logNone
End Enum
Private mLogLevel As LogLevel
Const gLogToImmediate = False
Private mLogFullPath As String
Public Sub SetLogLevel(ByVal sLogLevel As String)
  Const sRoutineName As String = "SetLogLevel"
  Dim enumLogLevel As LogLevel
 
  On Error GoTo ErrHandler
 
  Select Case sLogLevel
   
    Case "logDebug"
    enumLogLevel = logDebug
    Case "logInfo"
    enumLogLevel = logInfo
    Case "logWarn"
    enumLogLevel = logwarn
    Case "logError"
    enumLogLevel = logError
    Case "logFatal"
    enumLogLevel = logFatal
    Case "logNone"
    enumLogLevel = logNone
    Case Else
    Err.Raise Errors.BadLogLevelChosen, sRoutineName, sLogLevel & " is not a valid log level"
  End Select
 
  mLogLevel = enumLogLevel
  Logger.Log "Log level set to " & sLogLevel & " i.e. " & mLogLevel, logInfo, True, True
Exit Sub
ErrHandler:
  ErrPersist.ReraiseError sRoutineName & " line:" & Erl & ", "
End Sub
Private Sub SetLogFullPath()
  mLogFullPath = GetLogDir() & GetTitle() & ".log"
End Sub
Public Function GetLogDir() As String
GetLogDir = Globals.GetTempDir()
End Function
Public Function GetLogFullPath() As String
  GetLogFullPath = GetLogDir() & GetTitle() & ".log"
End Function
Public Sub Log(ByVal logStr As String, ByVal loggingLevel As LogLevel, Optional ByVal displayOnStatusBar As Boolean = False, Optional ByVal displayInImmediate As Boolean = False)
'TODO:LOW update the error handling here. If logging fails it isn't critical for now.
Dim logStrWithTime As String
logStrWithTime = Now & "| " & logStr
On Error Resume Next
If loggingLevel >= mLogLevel Then
  'TODO:LOW Look into making this better
  If mLogFullPath = "" Then SetLogFullPath
  Open mLogFullPath For Append As 1
  Print #1, logStrWithTime
End If
If displayOnStatusBar Then
  Application.StatusBar = logStrWithTime
End If
If displayInImmediate And gLogToImmediate Then
  Debug.Print logStr
End If
On Error GoTo 0
End Sub
Public Sub CloseLogFile()
  On Error Resume Next
  Close 1
End Sub
Public Sub DeleteLargeLogFile(ByVal lAcceptableFileSzInBytes As Long)
On Error Resume Next
  Dim sFile As String
  sFile = GetLogFullPath()
  Debug.Print FileLen(sFile)
  If FileLen(sFile) > lAcceptableFileSzInBytes Then
    DeleteFile sFile
  End If
End Sub
Private Sub DeleteFile(sDeleteFile As String)
  On Error Resume Next
  If Len(Dir$(sDeleteFile)) > 0 Then
    SetAttr sDeleteFile, vbNormal
    Kill sDeleteFile
  End If
End Sub



#2 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3,397 posts

Posted 31 January 2013 - 08:56 PM

I don't remember if in VBA you can set your enum as string, but if you could you wouldn't need the switch :D

 

and just in case, in the deletelargefile maybe you should check if the file isn't already open, because that will generate an error



#3 christhedon

christhedon

    CC Newcomer

  • Member
  • PipPip
  • 10 posts

Posted 01 February 2013 - 02:25 AM

Thanks for responding. Unfortunately Enums can't have strings in VBA. I've got the on error resume next in the delete log file methods. If the file is locked I won't delete it. I kind of treated logging as something which if it fails its not critical. How would you change it?



#4 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3,397 posts

Posted 01 February 2013 - 08:09 PM

Maybe you can make log files with the date, like: 2013-02-01.log and so on, and then every time you start you logs beyond a month.



#5 christhedon

christhedon

    CC Newcomer

  • Member
  • PipPip
  • 10 posts

Posted 04 February 2013 - 04:11 AM

Thanks I might include the date as suggested. I'll update this post. Anyone feel free to update this post to make it better.



#6 christhedon

christhedon

    CC Newcomer

  • Member
  • PipPip
  • 10 posts

Posted 06 March 2013 - 02:45 AM

Option Explicit
 
Public Enum LogLevel  logdebug  logInfo  
logwarn  logError  logFatal  logNoneEnd Enum
 
Private mLogLevel As LogLevelConst gLogToImmediate = FalsePrivate 
mLogFullPath As StringPrivate mFileNum As IntegerPublic Sub 
SetLogLevel(ByVal sLogLevel As String)
 
  Const sRoutineName As String = "SetLogLevel"  Dim 
enumLogLevel As LogLevel   On Error GoTo 
ErrHandler   Select Case 
sLogLevel       Case 
"logDebug"    enumLogLevel = logdebug    
Case "logInfo"    enumLogLevel = 
logInfo    Case "logWarn"    enumLogLevel 
= logwarn    Case "logError"    
enumLogLevel = logError    Case 
"logFatal"    enumLogLevel = logFatal    
Case "logNone"    enumLogLevel = 
logNone    Case Else    Err.Raise 
Errors.BadLogLevelChosen, sRoutineName, sLogLevel & " is not a valid log 
level"  End Select   mLogLevel = 
enumLogLevel  Logger.Log "Log level set to " & sLogLevel & " 
i.e. " & mLogLevel, logInfo, True, TrueExit Sub
 
ErrHandler:  ErrPersist.ReraiseError sRoutineName & " line:" 
& Erl & ", "
 
End Sub
 
Public Function GetLogLevel(ByVal eLogLevel As LogLevel) As 
String     Select Case 
eLogLevel       Case 
LogLevel.logdebug      GetLogLevel = 
"logDebug"    Case 
LogLevel.logInfo      GetLogLevel = 
"logInfo"    Case 
LogLevel.logwarn      GetLogLevel = 
"logWarn"    Case 
LogLevel.logError      GetLogLevel = 
"logError"    Case 
LogLevel.logFatal      GetLogLevel = 
"logFatal"    Case 
LogLevel.logNone      GetLogLevel = 
"logNone"  End Select End Function
 
Private Sub SetLogFullPath()  mLogFullPath = GetLogDir() & "\" 
& GetTitle() & ".log"End Sub
 
Public Function GetLogDir() As StringGetLogDir = Environ("TEMP")End 
Function
 
Public Function GetLogFullPath() As String  GetLogFullPath = 
GetLogDir() & "\" & GetTitle() & ".log"End Function
 
Public Sub Log(ByVal logStr As String, ByVal loggingLevel As LogLevel, 
Optional ByVal displayOnStatusBar As Boolean = False, Optional ByVal 
displayInImmediate As Boolean = False)
 
'TODO:LOW update the error handling here. If logging fails it isn't critical 
for now.Dim logStrWithTime As StringDim logStrWithLvlAndTime As 
String
 
On Error Resume Next
 
If loggingLevel >= mLogLevel Then  'TODO:LOW Look into making 
this better  
If mLogFullPath = "" Then SetLogFullPath  
logStrWithLvlAndTime = Now & "| " & GetLogLevel(loggingLevel) & " | 
" & logStr  mFileNum = FreeFile  Open mLogFullPath For 
Append As #mFileNum  Print #mFileNum, logStrWithLvlAndTime  
Close #mFileNum  DoEventsEnd If
 
If displayOnStatusBar Then  logStrWithTime = Now & "| " & 
logStr  Application.StatusBar = logStrWithTimeEnd If
 
If displayInImmediate And gLogToImmediate Then  Debug.Print 
logStrEnd If
 
On Error GoTo 0
 
End Sub
 
Public Sub CloseLogFile()  On Error Resume Next  Close 
#mFileNumEnd Sub
 
Public Sub DeleteLargeLogFile(ByVal lAcceptableFileSzInBytes As Long)On 
Error Resume Next  Dim sFile As String  sFile = 
GetLogFullPath()  Debug.Print FileLen(sFile)  If 
FileLen(sFile) > lAcceptableFileSzInBytes Then    
DeleteFile sFile  End IfEnd Sub
 
Private Sub DeleteFile(sDeleteFile As String)  On Error Resume 
Next  If Len(Dir$(sDeleteFile)) > 0 Then    
SetAttr sDeleteFile, vbNormal    Kill sDeleteFile  
End IfEnd Sub
 

I updated it so I can tail the log. Closes the file and uses free file num now and also I can see what level of log it is in the log so will be able to filter if need be. It seems better. Not sure if I need doevents or not.


Edited by christhedon, 06 March 2013 - 02:46 AM.


#7 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3,397 posts

Posted 06 March 2013 - 08:23 PM

I think you got it! pin yourself a medal, you've done a good logger.

 

I wonder what will you be doing next



#8 Mitchell

Mitchell

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 50 posts

Posted 28 March 2013 - 11:31 PM

No, there is no option from within the application to enable logging. You are right, you have to use the Event Viewer.

Whenever there is a application crash, the error message would contain the log file for the same.

The reason why Outlook/Outlook express has a logging feature is because it will trace the path for email sent/recieved and find if the error lies in the application or other network devices.