count procedure calls..........

**welchs101** · 12-14-2011, 10:45 AM

Hi, is there a way in excel vba to count the number of time a procedure/function gets called?

trying to update some code and i want to remove those procedures which are not used any longer.

**jeffreybrown** · 12-14-2011, 10:47 AM

Are you referring to different Subs within a module?

**welchs101** · 12-14-2011, 11:16 AM

yes..... subs in a given module

**jeffreybrown** · 12-14-2011, 11:26 AM

My thought would be to use a counter within the sub and write the value to a cell on a sheet to see how many time that sub was called.

**MarvinP** · 12-14-2011, 11:39 AM

Hi welchs101,

You can also declare a Global Variable that you can use as a counter. Each time you enter a Sub or Function you would increment the global variable that counts the number of times that code is run. See Global Variable at http://www.cpearson.com/excel/scope.aspx

**welchs101** · 12-14-2011, 11:59 AM

actually those are good ideas......

i have some code...........i had to make some MAJOR changes to the code.......now there are subs which i am no longer using..........so those that i am no longer using i want to get rid of............

i have a lot of subs.......i would rather not put code in each sub to count if its used...........if i was going to do that i would just go to each sub and do a find on its name.....to see if its being called anywhere.......

i was just hoping VBA would have a function that indicates what subs were being called by the program and which were not.......

**MarvinP** · 12-14-2011, 01:28 PM

Hi Welchs101,

I've installed two add-ins that help with my code and discovering stuff about my workbooks.
Look at: http://www.mztools.com/v3/mztools3.aspx and http://spreadsheetpage.com/index.php/pupv7/home
I'm not sure either has a Procedure hit counter but both have other useful tools to help in making better code and sheets.