Macro to log macro usage?

[Mr_Phil]

Hi. I think this has been asked before but I could not find the thread. I have a workbook with about 15 tabs and about 2000 (at least) lines of VBA macro code. I need to update the workbook and whilst doing that I'd like to delete out old macros that no longer apply. I am thinking that I'd like to be able to see a list of macros and then a number to tell me how many times they have run within a set period of time. It would be nice to know the last time it ran but that isn't essential. If it ran since implementing the "Log" it gets kept or it gets rewritten/updated or replaced with power querys. This is involved. So a pointer to a tutorial or article for study puposes would be awesome. Thanks for looking.

 

[Jeffrey Mahoney]

You would have to add code to each SUB that saved information to a Log.

With the code below, all you would have to do is add SaveToLog "SubName" to each SUB you want to log. Where SubName is the actual name of the Subroutine.

Book4
	A	B	C
1	SUB Name	Date of Run	User Name
2	FakeSubName	2/27/2024 7:58	Mahoney, Jeffrey
3	FakeSubName2	2/27/2024 7:59	Mahoney, Jeffrey
4	FakeSubName3	2/27/2024 7:59	Mahoney, Jeffrey
Log

Sub SaveToLog(SubName As String)
  Dim Cel As Range
  Dim UN As String
  Dim LogSht As Worksheet
  
  UN = Application.UserName
  Set LogSht = Sheets("Log")
  
  Set Cel = LogSht.Cells(LogSht.Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
  
  Cel.Value = SubName
  Cel.Offset(0, 1).Value = Now()
  Cel.Offset(0, 2).Value = UN
  
  
End Sub

Sub TestLogSave()
  SaveToLog "FakeSubName3"
End Sub

 

[Mr_Phil]

You would have to add code to each SUB that saved information to a Log.

With the code below, all you would have to do is add SaveToLog "SubName" to each SUB you want to log. Where SubName is the actual name of the Subroutine.

Book4
	A	B	C
1	SUB Name	Date of Run	User Name
2	FakeSubName	2/27/2024 7:58	Mahoney, Jeffrey
3	FakeSubName2	2/27/2024 7:59	Mahoney, Jeffrey
4	FakeSubName3	2/27/2024 7:59	Mahoney, Jeffrey
Log

Sub SaveToLog(SubName As String)
  Dim Cel As Range
  Dim UN As String
  Dim LogSht As Worksheet
 
  UN = Application.UserName
  Set LogSht = Sheets("Log")
 
  Set Cel = LogSht.Cells(LogSht.Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
 
  Cel.Value = SubName
  Cel.Offset(0, 1).Value = Now()
  Cel.Offset(0, 2).Value = UN
 
 
End Sub

Sub TestLogSave()
  SaveToLog "FakeSubName3"
End Sub

I like it. This will help with the updating of the old workbook. Thank you very much.

 

[Jeffrey Mahoney]

You are welcome

 

[Mr_Phil]

You would have to add code to each SUB that saved information to a Log.

With the code below, all you would have to do is add SaveToLog "SubName" to each SUB you want to log. Where SubName is the actual name of the Subroutine.

Book4
	A	B	C
1	SUB Name	Date of Run	User Name
2	FakeSubName	2/27/2024 7:58	Mahoney, Jeffrey
3	FakeSubName2	2/27/2024 7:59	Mahoney, Jeffrey
4	FakeSubName3	2/27/2024 7:59	Mahoney, Jeffrey
Log

Sub SaveToLog(SubName As String)
  Dim Cel As Range
  Dim UN As String
  Dim LogSht As Worksheet
 
  UN = Application.UserName
  Set LogSht = Sheets("Log")
 
  Set Cel = LogSht.Cells(LogSht.Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
 
  Cel.Value = SubName
  Cel.Offset(0, 1).Value = Now()
  Cel.Offset(0, 2).Value = UN
 
 
End Sub

Sub TestLogSave()
  SaveToLog "FakeSubName3"
End Sub

Ok, if you ever thought that your advice was blithely dismissed or that you were not truly helpful ... neither is true. I did what you said, then I did a control H to insert the on liner into each of 57 Macros. Then I made a log sheet. Then added a column to list and count everything that gets logged. Over the next day or two I will be able to confidently delete the ones that have outlived their useful life. Please don't laugh too much at my Macro names. I am just a logistics guy with no formal training. I truly appreciate the help.