ByVal in Excel event-handler

Question

I am a little confused about the usage of ByVal keyword in some event-handlers in Excel.

The NewSheet event example (Copied from Excel 2019 Power Programming with VBA, Wiley)

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    If TypeName(Sh) = "Worksheet" Then
        Sh.Cells.ColumnWidth = 35
        Sh.Range("A1") = "Sheet added " & Now()
    End If
End Sub

This code works as expected, as seems trivial. But after some thought I am getting more and more confused.

Based on my understanding ByVal means Sh is just copy of the the original worksheet object, and procedures using ByVal arguments won't result in change to the original object. In other words, what the code does should have no effect.

Only when references are passed to procedures will they be able to modify objects referred to by those references.

Am I missing something there? Thanks

P.S. Most of my understanding of passing by reference/value comes from other programming languages such as C#. There might be some peculiarity in VBA I am not aware of.

Answer 1

Since you're passing an Object, ByVal passes a copy (the value) of the reference. The copy (value) of the reference still points to what the original reference is pointing to. Hence your method works and changes what the original reference is pointing to.

If you know Java, this is similar in nature to Java, where everything can be said to 'pass by value' but when you pass an Object as a parameter, what you are actually doing is passing a copy of the reference, and not a copy of what the reference is pointing to.