NameBadgeArgumentFinal

Being the 2nd in a series of posts on “Names” in Excel.

Excel uses several “Names”. In a previous post, I wrote about Named Formulas. Today, I’ll take a look at Named Arguments.

Named Arguments

Named arguments are that descriptive tags you sometimes see in VBA code snippets. They are not required, hence, “sometimes”. Here’s a sample of a few:

NamedArguments1

In the sample snippet, I created a function to get a Cell as a Range Object from the user at run-time using the Application.InputBox Method. The InputBox Method actually has 1 required parameter and 8 optional parameters – I am only using 3 parameters – 1 required and 2 optional.


    Read more on the Application.InputBox Method here


Named Arguments Are Not Required

As I stated previously, Named Arguments are not required. Here is the Function rewritten without the Named Arguments. I also added a bit of error handling in case the user clicks the cancel button of the InputBox:

Public Function GetSelectedRangeNoNmArgs() As Range

    'Declare variables
        Dim rng                         As Range
    
    'Users - select a cell on a worksheet
        On Error Resume Next
        Set rng = Application.InputBox _
                        ("Please Select Range", _
                         "Range Select", _
                         8)
        If rng Is Nothing Then
            Exit Function
        End If
    
    'Pass the name of the worksheet to the function
        Set GetSelectedRangeNoNmArgs = rng
    
    'Tidy up
        Set rng = Nothing

End Function

Do you see the difference (other than the error handling) ? Here is a comparison of just the 4 lines of the InputBox of both snippets:

'No Named Arguments
Application.InputBox _
                        ("Please Select Range", _
                         "Range Select", _
                         8)

'Even worse - yikes!
Application.InputBox _
                        ("Please Select Range", "Range Select", 8)

'Named Arguments - Best!
Application.InputBox _
                        (Prompt:="Please Select Range", _
                         Title:="Range Select", _
                         Type:=8)

That makes the parameter values a bit more clear, doesn’t it?

Here’s A Dumb Reason

One reason given on MSDN for using Named Arguments is that you can change the order of the parameters to the function. Like this:

'Original parameter order
Application.InputBox _
                        (Prompt:="Please Select Range", _
                         Title:="Range Select", _
                         Type:=8)

'Rearranged parameters
Application.InputBox _
                        (Type:=8, _
                         Prompt:="Please Select Range", _
                         Title:="Range Select")

IMHO, that is a load of hooey. I cannot think of a single valuable reason to do that – can you?

The Real Reason

The real reason to use Named Arguments is that it makes things clearer – they are self-documenting. My example here is a little silly, maybe you are very use to the InputBox Method and you know the order of the parameters and can rattle ’em off in your sleep like I rattle off the batting order of the ’72 Cinci Reds.


    Petition to get Pete Rose reinstated to Major League Baseball here. Pete was one of the greatest to ever play the game.


Tidy Up

I’m sure you can find some Internet Breadcrumbs of mine, where I have not always used Named Arguments. My code and style have evolved and continue to evolve over time. I now always use Named Arguments – I encourage you to do the same.

, , , , , , , ,