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 .

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:

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:

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! 😀

VBA – create a log sheet of Excel with specific actions

Related posts: