Nr of times a macro is run from a sheet

jb007

New Member
Joined
May 31, 2013
Messages
22
Hello everybody

I need to count how many times a macro is run from a specific worksheet.
For example if I am in sheet A and I run the macro 3 times, it ok to get the value 3, but if I move to sheet B and run it for the first time, I should get the value 1. Then if I move back and run from sheet A, value 4 should appear.

Using a static variable like the one from below that holds its value until the worbook is closed doesn't get the job done, because it counts every macro run, no matter from where.

Static a As Long
a = a + 1
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
You could have a static array. One element for each sheet.

Code:
[COLOR=darkblue]Static[/COLOR] a(1 [COLOR=darkblue]To[/COLOR] 3) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]    [COLOR=green]'number of sheets[/COLOR]
a(ActiveSheet.Index) = a(ActiveSheet.Index) + 1

A bit more code would be required if you want to add sheets and track those as well.

Alternatively, store the count in a cell on each sheet. Clear it when opening or closing the workbook.
 
Last edited:
Upvote 0
Also possible:

write to a sheet
write to a text file
write to the registry
 
Upvote 0
Actually some problems appear when I have multiple workbooks open at the same time, because using this formula, Excel can't tell the difference between worksheets from different workbooks.
So I need it to take into consideration every single worksheet from every workbook open.
 
Upvote 0
Actually some problems appear when I have multiple workbooks open at the same time, because using this formula, Excel can't tell the difference between worksheets from different workbooks.
So I need it to take into consideration every single worksheet from every workbook open.

This will track up to 100 sheets (increase if need) based on the workbook name and sheet name.


Code:
    [COLOR=darkblue]Static[/COLOR] a(1 [COLOR=darkblue]To[/COLOR] 100, 1 [COLOR=darkblue]To[/COLOR] 2) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]With[/COLOR] ActiveSheet
        [COLOR=darkblue]For[/COLOR] I = [COLOR=darkblue]LBound[/COLOR](a, 1) To [COLOR=darkblue]UBound[/COLOR](a, 1)
            [COLOR=darkblue]If[/COLOR] a(I, 1) = "" [COLOR=darkblue]Then[/COLOR]
                [COLOR=green]'First instance[/COLOR]
                a(I, 1) = .Parent.Name & ", " & .Name   [COLOR=green]'Workbook and worksheet names[/COLOR]
                a(I, 2) = 1                             [COLOR=green]'Start counter[/COLOR]
                [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
            [COLOR=darkblue]ElseIf[/COLOR] a(I, 1) = .Parent.Name & ", " & .Name [COLOR=darkblue]Then[/COLOR]
                a(I, 2) = a(I, 2) + 1                   [COLOR=green]'count[/COLOR]
                [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] I
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
Last edited:
Upvote 0
Counter can be saved in the hidden sheet's name,
thus moving sheet to another workbook will not erase that sheet's counter
Rich (BB code):
Function MacroUsageCount(Optional Sh As Worksheet) As Long
  Dim i As Long
  If Sh Is Nothing Then Set Sh = ActiveSheet
  On Error Resume Next
  i = Sh.[MacroCounter]
  If i = 0 Then
    ' Add invisible name to the sheet
    i = 1
    Sh.Names.Add "MacroCounter", i, Visible:=False
    Err.Clear
  Else
    ' Increnent macro usage count and save in in the sheet's name
    i = i + 1
    Sh.Names("MacroCounter").RefersTo = i
  End If
  ' Return the usage count
  MacroUsageCount = i
End Function
 
Sub Test()
  Debug.Print MacroUsageCount(ActiveSheet)
End Sub
 
Upvote 0
This will track up to 100 sheets (increase if need) based on the workbook name and sheet name.


Code:
    [COLOR=darkblue]Static[/COLOR] a(1 [COLOR=darkblue]To[/COLOR] 100, 1 [COLOR=darkblue]To[/COLOR] 2) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]With[/COLOR] ActiveSheet
        [COLOR=darkblue]For[/COLOR] I = [COLOR=darkblue]LBound[/COLOR](a, 1) To [COLOR=darkblue]UBound[/COLOR](a, 1)
            [COLOR=darkblue]If[/COLOR] a(I, 1) = "" [COLOR=darkblue]Then[/COLOR]
                [COLOR=green]'First instance[/COLOR]
                a(I, 1) = .Parent.Name & ", " & .Name   [COLOR=green]'Workbook and worksheet names[/COLOR]
                a(I, 2) = 1                             [COLOR=green]'Start counter[/COLOR]
                [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
            [COLOR=darkblue]ElseIf[/COLOR] a(I, 1) = .Parent.Name & ", " & .Name [COLOR=darkblue]Then[/COLOR]
                a(I, 2) = a(I, 2) + 1                   [COLOR=green]'count[/COLOR]
                [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] I
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
It's not working, the concatenated name, a(I, 1), always appears to be 0, therefore a (I, 2) is always 1.
 
Upvote 0
No limitation:
Code:
private c00

Sub M_snb()
   c00=c00 & "|" & activesheet.parent.name & "." & activesheet.name
   y=ubound(filter(split(c00,"|"),activesheet.parent.name & "." & activesheet.name)+1
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top