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 .
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 VBA code, I generate a form with three text fields, where you enter the A,B and C for the calculation:
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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Private Sub cBtn_Click() If CDbl(Me.TextBox1) And CDbl(Me.TextBox2) And CDbl(Me.TextBox3) Then Call CalculateMe(Me.TextBox1, Me.TextBox2, Me.TextBox3) lblResult.Caption = Me.TextBox1 & " + " & Me.TextBox2 & " / " & Me.TextBox3 & " = " & Me.TextBox1 + Me.TextBox2 / Me.TextBox3 Else MsgBox "Enter valid input, please" End If End Sub Private Sub CommandButton1_Click() Unload Me End Sub |
And the code of the sub CalculateMe, called from the form:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
Sub CalculateMe(lFirst As Double, lSecond As Double, cDivider As Double) Dim wWorksheet As Worksheet Dim wLogSheet As Worksheet Dim lCounter As Long Dim lCol As Long Dim lRow As Long Dim sCellAddress As String Application.EnableEvents = False Application.EnableAnimations = False Set wWorksheet = ActiveSheet Set wLogSheet = Sheets("Log") lCol = ActiveCell.Column lRow = ActiveCell.Row sCellAddress = ActiveCell.Address Sheets("Log").Activate lCounter = Sheets("Log").Cells(1, 12) + 1 Sheets("Log").Cells(1, 12) = Sheets("Log").Cells(1, 12) + 1 Sheets("Log").Cells(lCounter, 1) = CStr(Date) Sheets("Log").Cells(lCounter, 2) = lFirst Cells(lCounter, 3) = lSecond Cells(lCounter, 4) = cDivider Cells(lCounter, 5) = lFirst + lSecond / cDivider Cells(lCounter, 6) = Application.UserName Cells(lCounter, 7) = wWorksheet.Name Cells(lCounter, 8) = lCol Cells(lCounter, 9) = lRow Cells(lCounter, 10) = sCellAddress If (wWorksheet.Name <> wLogSheet.Name) Then wWorksheet.Activate Set wWorksheet = Nothing Set wLogSheet = Nothing Application.EnableEvents = True Application.EnableAnimations = True End Sub |
Enjoy it! 😀