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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
" y= ubound (filter(split(c00,"|"),activesheet.parent.name & "." & activesheet.name)+1"
This part is shown in red and the message is "Compile error: Expected: list separator"
 
Upvote 0
the number of (( must match the number of ))

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
ok, sorry for that.
still not working - I get the same number each time, which doesn't start at 1...so it doesn't cound the number of times the macro has been run from a worksheet
 
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]

AlphaFrog, can you figure out some small modification to this code, to make it work ?
I tried some things, but it still doesn't work, I don't think it assings the name to the static a variable.
 
Upvote 0
You could put this line in the macro being counted
Code:
ActiveSheet.Names.Add Name:="MacroRunCount", RefersTo:="=" & (1 + Val(CStr(Evaluate("=MacroRunCount"))))

Then, either the formula =MacroRunCount in the worksheet or the code
Code:
MsgBox Val(CStr(Evaluate("=MacroRunCount")))
will give you how many times the macro has been run in the active sheet.

The reset to zero code would be
Code:
Sub ResetMacroRunCount()
    ActiveSheet.Names.Add Name:="MacroRunCount", RefersTo:="=0"
End Sub

And this will show the data for all open sheets.
Code:
Sub RecordAllSheets()
    Dim oneSheet As Worksheet
    Dim oneBook As Workbook
    Dim recordSheet As Worksheet
    Set recordSheet = Sheet3
    
    recordSheet.Range("A:B").ClearContents
    For Each oneBook In Application.Workbooks
        For Each oneSheet In oneBook.Worksheets
            With recordSheet.Range("A65536").End(xlUp).Offset(1, 0)
                .Cells(1, 1).Value = "[" & oneBook.Name & "] " & oneSheet.Name
                On Error Resume Next
                .Cells(1, 2).Value = Evaluate(oneSheet.Names("MacroRunCount").RefersTo)
            End With
        Next oneSheet
    Next oneBook
    
End Sub
 
Last edited:
Upvote 0
mikerickson you are a genius, it works
Thank you so much, this little piece of code saved me so much time :)

If you could provide a (brief) explanation of how it actually works, it would be even more helpful
 
Upvote 0
Every time I run this macro it adds 1 if the active sheet hasn;t changed.

If I change the active sheet it sstart counting form1.

If I return to a sheet I have been befor it resumes couning where it had lest the last time it was the active sheet.

Code:
Private c00

Sub M_snb()
   c00 = c00 & "|" & ActiveSheet.Parent.Name & "." & ActiveSheet.Name
   MsgBox UBound(Filter(Split(c00, "|"), ActiveSheet.Parent.Name & "." & ActiveSheet.Name)) + 1
End Sub

@Rick why 'just' noting what had been noted already ?
 
Upvote 0
The code I posted creates a sheet level Named value and increments it each time that the macro in question is run.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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