CODEWITHSUNDEEP
excelvba
Caleb Edwards
Caleb Edwards

Reputation: 19

VBA - Adding Counter For Tracking

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

Answers (3)

TFrazee
TFrazee

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

urdearboy
urdearboy

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

Alan
Alan

Reputation: 3042

Use static when declaring your variable.

From "EXCEL" Ytics:

Can we declare a variable to retain its value from a previous run?

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

Related Questions