Home > Advanced, Excel VBA Resources, Factoid, VBA > Do you use protection? Really useful factoid – 002

Do you use protection? Really useful factoid – 002

We are all told that using protection is a good thing.  This applies to both real life and also programming, both in full OOP and the quasi OOP world of VBA / VB. It can be possible in some cases for your variables to be changed inside the sub / function you have called, even the ones one may have designated in your human mind as ‘input’ variables.   This can lead to odd behaviour and hard to find bugs.

So.. what am I going  on about?  Well I am talking about passing by value or by reference.  (Experienced VBA Programmers may want to skip this bit).   In VBA you can use two keywords ByRef or ByVal when defining your Sub or Function.   These determine what happens to the variables in the calling function.  This is the code area where some variables are passed into another function.

Let me give an example.

Public Sub Test()
    Dim Num1 As Long
    Dim Num2 As Long
    Dim Sum As Long

    'Note:  ***Don't declare in the style below*** .  Only Sum2 is a Long
    'Num3 & Num4 are both Variants.  You cannot chain assignments like C/C++/C#
    Dim Num3, Num4, Sum2 As Long

    Num1 = 5
    Num2 = 10
    Sum = -1

    Call AddingByValue(Num1, Num2, Sum)

    'Sum will not be changed as it is passed by Value
    Debug.Print "Value of Sum = " & Sum

    Call AddingByRef(Num1, Num2, Sum)

    'Sum will now be changed here in the 'calling function'
    'as it is passed by reference
    Debug.Print "Value of Sum = " & Sum

End Sub

Private Sub AddingByValue(ByVal Num1 As Long, ByVal Num2 As Long, ByVal Sum As Long)

    Sum = Num1 + Num2

End Sub

Private Sub AddingByRef(ByRef Num1 As Long, ByRef Num2 As Long, ByRef Sum As Long)

    Sum = Num1 + Num2

End Sub

Immediate Window

Value of Sum = -1
Value of Sum = 15

So now it should be clear what the difference is in passing by reference and by value. Passing by reference is the default behaviour for VBA.    I’d let that sink in for a moment.   So if you just write out a simple Sub, or more importantly are using one written by someone else that already exists in a Module then you may well be passing your variables ByRef without explicitly wanting to.  Consider the case below.

Public Sub Test2()
    Dim Num1 As Long
    Dim Num2 As Long
    Dim Sum As Long

    Num1 = 5: Num2 = 10

    Debug.Print "Pre Num1 = " & Num1 & ", Num2 = " & Num2 & ", Sum = " & Sum
    Call MaliciousAdding(Num1, Num2, Sum)
    Debug.Print "Post Num1 = " & Num1 & ", Num2 = " & Num2 & ", Sum = " & Sum

End Sub

Public Sub MaliciousAdding(Num1 As Long, Num2 As Long, Sum As Long)
    Sum = Num1 + Num2

    'Assuming the Sub is non trivial an accidental or intentional assignment may
    'be made to the input variables which is not clar on calling.
    Num1 = Num2
End Sub

Output in Immediate Window:

Pre Num1 = 5, Num2 = 10, Sum = 15
Post Num1 = 10, Num2 = 10, Sum = 15

So, we can see here that the input values have changed.  I have made things easy by calling the Sub MaliciousAdding and it doesn’t have very much in it to get confused about.  However, if this was an Enterprise scale workbook with many logic paths, not written by you, it is possible to imagine this happening in some cases?

This can be a real pain.  You probably don’t want to change the sub / function’s signature to make it safe in case some of this behaviour is expected by another caller elsewhere.

Also it is a pain to have temp variables and to make checks before and after the call each time as this is wasteful, confusing and reduces code readability.

So, we can use a little known feature to give ourselves the needed protection.  

See below:

Public Sub Test2()

    Dim Num1 As Long
    Dim Num2 As Long
    Dim Sum As Long

    Num1 = 5: Num2 = 10

    Debug.Print "Pre Num1 = " & Num1 & ", Num2 = " & Num2 & ", Sum = " & Sum
    Call MaliciousAdding(Num1, Num2, Sum)
    Debug.Print "Post Num1 = " & Num1 & ", Num2 = " & Num2 & ", Sum = " & Sum

    Num1 = 5: Num2 = 10: Sum = 0

    Debug.Print "Pre Num1 = " & Num1 & ", Num2 = " & Num2 & ", Sum = " & Sum
    Call MaliciousAdding((Num1), (Num2), Sum)
    Debug.Print "Post Num1 = " & Num1 & ", Num2 = " & Num2 & ", Sum = " & Sum

End Sub

Output from Immediate Window:

Pre Num1 = 5, Num2 = 10, Sum = 0
Post Num1 = 10, Num2 = 10, Sum = 15
Pre Num1 = 5, Num2 = 10, Sum = 0
Post Num1 = 5, Num2 = 10, Sum = 15

So, we are using the same Sub, with the same signature as before but the input variables have been left alone this time.  If one surrounds their variable with parentheses inside the call VBA will always treat that (variable) as being passed by value, even if the called Sub / Function is specified as ByRef and makes changes inside itself.

This is a very useful function and if you are planning to use several subs / functions in a row with the same set of inputs, a lot of heart-ache can be avoided by judicious use of this feature to ensure that certain variables stay the way that you would like them.

Advertisements
  1. dinesh
    January 10, 2011 at 4:14 pm

    Please contact me if you are looking for an excellent opportunity with financial company in NYC. They are looking for good vba/excel professionals.

    Thanks

    Dinesh

  2. Colan
    January 27, 2011 at 10:36 am

    Good stuff

  3. Jon
    April 21, 2011 at 2:32 pm

    I purposefully use byref when I have large data crunching projects that need to work quickly. I’ve found this increases the speed of the application significantly.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: