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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
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:

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Also possible:

write to a sheet
write to a text file
write to the registry
 

jb007

New Member
Joined
May 31, 2013
Messages
22

ADVERTISEMENT

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.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
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:

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,722
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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
 

jb007

New Member
Joined
May 31, 2013
Messages
22
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.
 

snb_

Well-known Member
Joined
Nov 9, 2009
Messages
567
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,026
Messages
5,526,324
Members
409,695
Latest member
EmmaFos

This Week's Hot Topics

Top