Tracking macro Usage

[RobMatthews]

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]

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]

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]

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
'==============

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]

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]

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]

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]

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]

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]

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.