# Tracking macro Usage



## RobMatthews (Jun 21, 2011)

Hey all.

What are your respective thoughts on tracking the usage of tools that you create for a user group? In the last little while it has occurred to me that something like recording the number of times any give tool is used might be useful; for such things as monitoring which tools are used more often, and which users take advantage of which tools, not to mention at Review time.

To that end, I have started retro-actively adding the following snippet of code to each tool that I release, with the date of tracking included at the top of each text file. (Separate file for each tool, obviously files live on a freely available network drive). 

What do you do? (or how can i do this better? Like into an excel file open, add data and close in double-quick time, invisibly..)

```
'============== Append a file to keep track of usage
      Dim oFS, TS, FileObj
      Set oFS = CreateObject("Scripting.FileSystemObject")
      Set FileObj = oFS.GetFile("NetworkPath\DescriptiveFileName.txt<NETWORKPATH\DESCRIPTIVEFILENAME.TXT>")
      Set TS = FileObj.OpenAsTextStream(8, -2) 'ForWriting, TristateUseDefault)
      TS.writeline Application.UserName & ", with " & Cells.SpecialCells(xlCellTypeLastCell).Row & " Rows on " & Date
      TS.Close
      Set TS = Nothing
      Set FileObj = Nothing
      Set oFS = Nothing
'==============
```


----------



## Domski (Jun 22, 2011)

Distribute the code as an add-in with instructions not to install it locally and then every now and then delete it and see how many people shout 

Dom


----------



## arkusM (Jun 22, 2011)

Domski said:


> Distribute the code as an add-in with instructions not to install it locally and then every now and then delete it and see how many people shout
> 
> Dom


 
Haha, you are a b@st@rd!!


----------



## arkusM (Jun 22, 2011)

RobMatthews said:


> Hey all.
> 
> What are your respective thoughts on tracking the usage of tools that you create for a user group? In the last little while it has occurred to me that something like recording the number of times any give tool is used might be useful; for such things as monitoring which tools are used more often, and which users take advantage of which tools, not to mention at Review time.
> 
> ...


 
I have thought about this as well, I have a tracking macro that I have put into a few of my bigger models that track many changes and it writes to a txt file I have not noticed performance issue when the tracker is running.

However I was thinking of releasing some addins and I wanted to know if anyone was using them and I thought about making the file queitly sending me an email when the addin is installed, though I do not want to track usage. But maybe writing to a txt file would be easier... more to thank about.


----------



## Atroxell (Jun 22, 2011)

I have written a couple of text "trackers" for people in my department. Since each department has its own shared drive, I really can't do anything about any code I write for other departments.

What I have done is to create a text file on the shared network drive. The file resides deep, deep down in a folder group that I own and that folder contains only that text file. Every time a macro workbook is opened, a line is generated in the text file similar to yours, stating the username and which workbook is being used and a date-time stamp. Whenever a macro is executed, I have a procedure that is called to enter a record for that macro. It also logs any failures in the macro because if and when one fails, 99.99% of users will not bother to write down the error message. (btw: Anyone know how to get a solid pointer to what line the error occurs on without writing a bunch of code?) All of this occurs without the users being aware that anything is being logged. Whenever I want to see who is using what or what happened, I just open the text file and read through it. (I also have a piece of software that does data mining and the text file is formatted to be useful with that software, creating a single record for each user's usage.)

Of course, I am taking a chance that there could be a collision if two people are working in a macro workbook at the same time and writing to a single text file but that has not happened in the last 2 years, so I'm not too concerned about it. I know I should correct that, but.. 

If you are, you can create a single text file for each macro workbook and keep them all in the same folder.


----------



## ChrisOswald (Jun 22, 2011)

It looks like I'm not the only one out there thats had this idea. I set up something a couple of years ago where I was logging to an tiny Access table some usage stats: macro name, username, time of use, number of lines (if applicable), whether it was an Excel or Attachmate macro, etc. This was also my first intro to doing any thing with vba and access, which was 1/2 of the reason I did it -- if I couldn't get it to work, then no promises broken.  The other 1/2, of course, was to help document my value to the company.

Anyhow, I found out some really interesting data. For example, it seems like the more 'timid' the requester for the creation of the macro was, the more the macro was actually used, and even some of my 'off the cuff' use estimates were WAY, WAY off (one attachmate macro i thought would be used ~100 times a week ended up being used ~1000 times a week).

I eventually got lazy and stopped putting it in everything I was doing. Then again, the stuff I was doing started getting larger in scope, and macro/addin usage stopped having a solid correlation with labor savings.  

On a somewhat related note:  Does anybody else that distributes macros as addin's have a good way of keeping track of which versions users are using?  For example, I have a couple of 'generic task' addins that each contain ~10 sub-menu choices that I add to occasionally, and I'd like to be able to keep track of who has what, in case of a hard drive crash or whatnot.


----------



## ZVI (Jun 22, 2011)

Atroxell said:


> Anyone know how to get a solid pointer to what line the error occurs on without writing a bunch of code?


Just for interest, such possibility is present.
If code lines are numbered, then function Erl can be used to return the number of the code line caused error.

```
Sub Test()
        Dim a, b, c
10      On Error GoTo exit_
20      a = 1
30      b = 0
40      c = a / b
50      c = c + 1
exit_:
60      If Err Then MsgBox "Error in line " & *Erl* & vbLf & Err.Description
End Sub
```
 MZ-Tools freeware Add-in can be used to auto add/remove line numbers.

As to the tracking, it’s useful for statistic and for the fast supporting of the code.
I’ve used the similar method with appending of logon username and date-time stamp to the shared tracking text file named as Logon.bmp.
The extention BMP was just against of someone's curiosity.
But to not be evaluated as spying it’s desired to indicate at least in help/manual about the goals of statistics and it parameters.

BTW, there is a little disadvantage of the method.
Without access to tracking file, for example at working off-line or outside the LAN, the 5...10 seconds delay comes at trying to access tracking file.
It’s uncomfortably. May be asynchronous writing to the tracking file is more convenient.


----------



## Atroxell (Jun 23, 2011)

Hmmm..wow! I have seen people numbering their lines in code, but have not seen it done regularly since the days when I used HDOS (Heathkit Disk Operating System for your young'ns out there!) and some of the BASIC languages. 

Well, I guess I have a reason to number the lines now. Of course, the question remains as to whether I will actually have the patience to do so.

But thanks for the tip! I will try to be more diligent, at least with code I write for others.


----------



## arkusM (Jun 23, 2011)

ZVI said:


> MZ-Tools freeware Add-in can be used to auto add/remove line numbers.


 
Wow that add-in has a lot of features... Thanks for sharing.


----------



## Darren Bartrup (Jun 23, 2011)

I don't use usage tracking, more success/failure tracking on some projects.

E.g.  If I've written code that's performing a task on all workbooks within a folder I'll end up with a text file showing which files were opened, if any couldn't be opened for editing, if a required column was in the wrong format or missing - all that kind of stuff.

I don't track how often my code is used though - I just write it and put it out there, if it's not being used then that's not my problem.  I just enjoyed writing it.


----------



## Auracle (Jun 23, 2011)

Atroxell said:


> Hmmm..wow! I have seen people numbering their lines in code, but have not seen it done regularly since the days when I used HDOS (Heathkit Disk Operating System for your young'ns out there!) and some of the BASIC languages.


 
Yes!  I was just thinking that too!  Brings me back to the days when I learned programming in BASIC!


----------



## ZVI (Jun 23, 2011)

Auracle said:


> Yes!  I was just thinking that too!  Brings me back to the days when I learned programming in BASIC!


Yea, Erl function comes from the deep happy time when our grandmothers programmed  
Never used it but have found that it adds a bit security to the not tabbed code.
It was a code I saw with lines numbering, obfuscating  and not tabbed.
There was difficult to understand even the structure of that code.



arkusM said:


> Wow that add-in has a lot of features... Thanks for sharing.


Happy to share useful info!
Smart Indenter is another great utility the most frequently used by me for auto indention of the code.
It is very useful for analyzing of poor tabbed code sometimes posted in the forum.
BTW, it works even with numbered code.


----------



## ZVI (Jun 23, 2011)

ZVI said:


> *it* adds a bit security to the not tabbed code


l*ines numbering* adds a bit security to the not tabbed code


----------



## arkusM (Jun 23, 2011)

ZVI said:


> Happy to share useful info!
> Smart Indenter is another great utility the most frequently used by me for auto indention of the code.
> It is very useful for analyzing of poor tabbed code sometimes posted in the forum.
> BTW, it works even with numbered code.


 

I second that. So much so that I have had it in my sig for a while now. SO USEFUL..


----------



## RobMatthews (Mar 19, 2012)

So i used my tracking data, assigned a time-saved value against each tool that i wrote, and it turns out this year to date, i might have saved the company about 37 hours, so far, in just the selected tools. Saw the boss yesterday to plead my case for a rise, and he was very interested to see those numbers, and I'm very glad to have the data... 

It's nice to be able to somewhat quantify the value I'm adding.


----------



## ZVI (Mar 20, 2012)

RobMatthews said:


> So i used my tracking data, assigned a time-saved value against each tool that i wrote, and it turns out this year to date, i might have saved the company about 37 hours, so far, in just the selected tools. Saw the boss yesterday to plead my case for a rise, and he was very interested to see those numbers, and I'm very glad to have the data...
> 
> It's nice to be able to somewhat quantify the value I'm adding.


Congrats, RobMatthews!
Nice idea of the tracking statistics usage.
Never thought of that direction, thanks for sharing!
Vlad


----------



## Irfansh (Jun 27, 2013)

Darren Bartrup said:


> I don't use usage tracking, more success/failure tracking on some projects.
> 
> E.g.  If I've written code that's performing a task on all workbooks within a folder I'll end up with a text file showing which files were opened, if any couldn't be opened for editing, if a required column was in the wrong format or missing - all that kind of stuff.
> 
> I don't track how often my code is used though - I just write it and put it out there, if it's not being used then that's not my problem.  I just enjoyed writing it.



I have a macro file that is placed in a sharedrive & this file has many macros.The users will access the file from the sharedrive only.
Can anyone give me the code which captures the macro usage (date,time,user & the macro which was run) in a log file or in a sheet of the same file,I will add the code in my macro.

Thanks


----------



## TinaP (Jun 28, 2013)

ChrisOswald said:


> On a somewhat related note:  Does anybody else that distributes macros as addin's have a good way of keeping track of which versions users are using?  For example, I have a couple of 'generic task' addins that each contain ~10 sub-menu choices that I add to occasionally, and I'd like to be able to keep track of who has what, in case of a hard drive crash or whatnot.



I don't necessarily track which versions people are using, but when they install the add-in, I make sure they don't install it locally.  Like you, I've written add-ins with generic tasks.  I wanted to be able to revise my code without having to reinstall the add-in on every single computer.  So far, the only problem I've encountered is when the drives haven't mapped correctly on our network.


----------



## RobMatthews (Jun 30, 2013)

Irfansh said:


> I have a macro file that is placed in a sharedrive & this file has many macros.The users will access the file from the sharedrive only.
> Can anyone give me the code which captures the macro usage (date,time,user & the macro which was run) in a log file or in a sheet of the same file,I will add the code in my macro.
> 
> Thanks



See my first post. I posted the code that I use. Obviously, change "NetworkPath\DescriptiveFileName.txt<NETWORKPATH\DESCRIPTIVEFILENAME.TXT>" to something meaningful, create your log file(s), and away you go. I have log files for each different macro, to minimise the chance of two concurrent users trying to access the file (I have about 16 users). You can put in as much or as little information as you want. In that example, I only have user name, the number of rows manipulated, and the Time/Date. (That was from a mcros that changed and AutoCAD drawing.)

You can see usage purely by file size...


----------

