Reputation: 19
New to writing code in VBA, and I need to add in a way to count/track how often a macro is used. I'm struggling to find any examples of a code that would perform this function. Requesting any examples there might be for this.
Upvotes: 1
Views: 2708
Reputation: 807
Excel VBA doesn't have any built-in storage that persists across sessions - when the application is closed, all variables are released. However you do have a readily available source of storage - the workbook itself. Here's one way to do it:
Make a worksheet called Counter
(or something). Optionally, hide it so no one messes with it. Then, when your macro runs, increment the value of Cell(1,1)
, which will hold your counter value.
Dim runCount As Integer
Dim counterWS As Worksheet
Set counterWS = ThisWorkbook.Worksheets("Counter")
If counterWS.Cells(1,1) <> vbNullString And IsNumeric(counterWS.Cells(1,1)) Then
runCount = counterWS.Cells(1,1) + 1
Else
runCount = 1
End If
counterWS.Cells(1,1) = runCount
Upvotes: 1
Reputation: 14590
This will build a table for you on a hidden sheet showing all historical uses of the macro up to the nth time the macro was used along with a time stamp so you know when the macro was used. With this, you can use equations to extract exact data that you want as well. If you want a cell to show usage count, just to "Countif("A:A","*")-1” ... (minus 1 to ignore the header in A1)
To Implement this macro:
Create a new sheet titled "MacroCount"
Set A1 = "Instance"
Set B1 = "Time Stamp"
I would personally make this the last thing the macro does so it will only count the instance once the macro completes all of its assigned duties.
Also, Remove the "Sub MacroCount()" & "End Sub" from the below code.
Sub MacroCount()
Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("MacroCount")
Application.Screenupdating = False
WS.Visible = True
Dim LRow As Long
LRow = WS.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
WS.Range("A" & LRow).Value = LRow - 1
WS.Range("B" & LRow).Value = Now()
WS.Visible = False
Application.Screenupdating = True
End Sub
Upvotes: 0
Reputation: 3042
Use static
when declaring your variable.
From "EXCEL" Ytics:
Static Declaration within a procedure limits the variable to that procedure – but helps retain values of variables from previous runs, until forcefully reinitialized by explicit declaration using ‘ReDim’ or value setting.
Sub MySub( )
Static Cnt As Integer
Dim Msg As String
Cnt = Cnt + 1
Msg = “You’ve run the procedure ” & Cnt & ” times”
MsgBox Msg
End Sub
Upvotes: 0