VBA – create a log sheet of Excel with specific actions

When you work with professionally built VBA tool, sometimes logging your actions (or the actions of some colleague) if the file is shared, can be of use. The idea is not to go into spying, but simply to check who has done what with the file. There are many ways to do this, but in the current article I will simply show you how to build a log file for the usage of a macro, which simply does the following calculation a+b/c .

Confused_detective

 

Why do you need to do it? Really, for such a calculation it is a waste of time, but for other reasons you may want to know a bit more…

In my macro, I generate a form with three text fields, where you enter the A,B and C for the calculation:

calculator

Once you click on “Calculate”, you get a label, displaying the message. If you are a normal user, that is all you get. If you are the developer, you get to know a little bit more :

  • You get information for the date the “Calculate” button was pressed.
  • The entered variables
  • The result
  • The activesheet name
  • The Windows User Name
  • The column and the row of the active sheet
  • The address
  • Plus anything you like : (e.g. whether events are enabled or what is the value of the third cell in the second sheet… )

Just a sample:

log

This is how logs are done. The logs with excel are really simple, but just imagine what a PHP programmer can understand about you from your internet sessions. Quite a lot actually.

Last but not least, here comes the code of the form:

And the code of the sub CalculateMe, called from the form:

Enjoy it! 😀

About

VBA Developer

Tagged with: , , , ,