Access JumpStart 2.0 | Blog

A Rapid Development Framework for Microsoft Access

Refactoring in VBA with RubberDuck: Reorder Parameters

by | Jun 6, 2024 | MS Access, MS Access VBA Coding | 0 comments

Using some sample code I was playing with in my last Refactoring article, we will see what RubberDuck does when reordering parameters. Here’s the code:

HAL_BasicEncryption

Option Compare Database
Option Explicit

Implements IHAL_Encryption

Private Function IHAL_Encryption_decrypt(sEncryption As String, sKey As String) As String
    Debug.Print TestFunction("One", "Two", , "Four")
End Function

Private Function IHAL_Encryption_encrypt(sInput As String, sKey As String) As String
    Debug.Print TestFunction("First", "Second", "Three")
End Function

Private Function TestFunction(Param1 As String, Param2 As String, Optional Param3, Optional Param4) As String
    Debug.Print Param1
    Debug.Print Param2
    Debug.Print Param3
    Debug.Print Param4
    TestFunction = Param1 & Param2 & Param3 & Param4
End Function
Visual Basic

My TestFunction is where I will attempt to re-order parameters. Since I set some as optional, that could be an issue. If I try to put an Optional parameter in front of a required parameter, that would create an invalid parameter order.

Optional parameters must come at the end of the parameter list.

Let’s see what happens:

I will switch just Param1 and Param2 first. I suspect that will reverse the order in the function and also change the calling functions.

Here’s the new code:

Option Compare Database
Option Explicit

Implements IHAL_Encryption

Private Function IHAL_Encryption_decrypt(sEncryption As String, sKey As String) As String
    ' RubberDuck switched the order of parameters called.
    Debug.Print TestFunction("Two", "One", , "Four")
End Function

Private Function IHAL_Encryption_encrypt(sInput As String, sKey As String) As String
    ' RubberDuck switched the order of parameters called.
    Debug.Print TestFunction("Second", "First", "Three")
End Function

' RubberDuck correctly switched the 2 parameters
Private Function TestFunction(Param2 As String, Param1 As String, Optional Param3, Optional Param4) As String
    Debug.Print Param1
    Debug.Print Param2
    Debug.Print Param3
    Debug.Print Param4
    TestFunction = Param1 & Param2 & Param3 & Param4
End Function
Visual Basic

That’s wonderful. Now I’ll try switching Param1 with Param3 and see what happens.

Nice! RubberDuck doesn’t activate the OK button and doesn’t let me do this. Let me try adding a default value for the optional parameter and see if it lets me reorder them then.

And RubberDuck still has the OK button deactivated. Which is the correct behavior because you must have optional parameters at the end of the list.

However, I should be able to swap the optional parameters. The dialog box allows me to do this and to hit the OK button and the window closes, however the parameters are not swapped. It appears that the optional parameters must all be used in every function call for the reorder to work, or they must be the last parameter. When I added the fourth parameter to the function call that was missing the fourth parameter, it worked and I ended up with this code:

Option Compare Database
Option Explicit

Implements IHAL_Encryption

Private Function IHAL_Encryption_decrypt(sEncryption As String, sKey As String) As String
    ' Was       TestFunction("Two", "One", , "Four")
    Debug.Print TestFunction("Two", "One", "Four")
End Function

Private Function IHAL_Encryption_encrypt(sInput As String, sKey As String) As String
    Debug.Print TestFunction("Second", "First", "Four", "Three")
End Function

Private Function TestFunction(Param2 As String, Param1 As String, Optional Param4, Optional Param3) As String
    Debug.Print Param1
    Debug.Print Param2
    Debug.Print Param3
    Debug.Print Param4
    TestFunction = Param1 & Param2 & Param3 & Param4
End Function
Visual Basic

Note that in the first TestFunction call, the empty 3rd parameter was removed and replaced with the fourth parameter, and the last comma was then dropped.

Sign up For a Daily Email Adventure in Microsoft Access

Every business day (typically M-F), I'll send you an email with information about my ongoing journey as an advanced Access application developer. It will be loaded with my tips and musings.

    We won't send you spam. Unsubscribe at any time.
    Built with Kit

    Submit a Comment