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.

, , , , , , , ,

NameBadgeFinal

Excel uses several Names. I’ll take a look at a few beginning with Named Formulas. When developing budgets and forecasts, it is convenient to create an annualized amount for various accounts and spread the amount over time in your model whether it be 52 weeks, 12 months, 360 days, whatever your model calls for – generally driven by your domain or sphere of activity.

Named Formulas

Spread1

I have entered a name for my formula in $E$3 and I entered some random values in $F$3:$Q$3 that SUM to 1.

Spread2F

I highlighted the name that I will use for the Named Formula and the 12 random values that I created.

Spread3

I hit [Ctrl]+[Shift]+[F3] on my keyboard and the Create Names From Selection Dialog popped up. The Name for my Named Formula is in the left-most column, so I accepted the default, Create names from values in the: Left Column.

Spread4

I entered the random value of 98,7654.32 in $D$5 and entered the formula: =$D$5*MySpread in $F$5:$Q$5 below my spread technique. You can see that the 98K is allocated properly to each month based on the percentage I entered for each month in the spread technique – that my friends is wicked cool!

Imagine, if this were a 52 week model, I just went from 52 entries to 1. Additionally, I can go back and change my spread technique anytime and any formulas that use that Named Formula will calculate new values – huge time saver!

Business Case

In reality, I would not have Named Formulas on the same worksheet with inputs and analysis – so I’ll separate them.

Spread5

I moved the Named Formula to the Formula Tab. I left the inputs and spread analysis on the Analysis Tab.

Spread7

The spread is working correctly. I added a formula to sum up all cells in the spread less the input value to make sure 100% of the input value has been spread. Looking good!

Storing Fomulas, Constants, Etc…

Recall, I started my Named Formula on cell $E$3. In reality, I normally start such as items in $A of $B, I’ll move the Named Formula to $B.

Spread8

That’s better – but look what happened to my spread:

Spread9

Since the Named Formula and the Analysis are now starting in different Columns, the Analysis formula is not working correctly. I need a better formula that is more flexible and dynamic that can be used anywhere in the model and not return #VALUE! errors.

The INDEX() and COLUMNS() Functions

Spread10

In my new formula, I begin by using the INDEX() Function. As you see in the image, the INDEX() function may have up to 3 arguments,

  1. Array : I’m using the Named Formula as the array
  2. Row Number : I’m using 0 since the Named Formula is really a Vector Array with 1 dimension
  3. Column Number : I’m using the COLUMNS() Function to count the number of Columns I want and passing that number to the function.

Note in the COlUMNS() Function I am using COLUMNS($F1:F1) this will return 1. As I continue to copy the formula to the right the F1 will update to G1, H1 and so forth thus returning 2 and 3 and so forth respectively.

Spread11

I copied my formula to all cells in my Analysis worksheet and everything ties out once again.

Many Named Formulas

Generally, a budget or forecast model is going to need many different Named Formulas to handle many different spreads. Sales, Revenues and Expenses may, and most likely will, behave in many different ways. I may have a depreciation expense that is straight-line for 5 years at the same time that I am opening or closing for the Fall Semester at a university.

You may also use this strategy for quickly changing slight variations of the same spread technique to tweak timing of expenses and the amount that is allocated to each time period. For example, assume you setup 12 different spreads to allocate expenses related to some investment. All 12 may (should) tie-out to allocate 100% of the expense, they just allocate the expense a little bit differently to different time periods.

Spread12

I quickly added 11 more Named Formulas to my workbook. These names have no purpose and are demonstrative only. In reality, your Named Formulas should be well planned and self-documenting – describing to the reader precisely what the intent of the Formula is. Note in the scrren shot that the values in each of the Named Formulas are the same. I just copied down the original values from the first Named Formula.

Spread12

Spread13

I highlighted the list of all of the Named Formulas and named the list, “lstNamedFormulas” because I am creative like that. Normally, I would make this a dynamic Named Range, but that is another topic for another day.

Spread14

I selected a cell on my Analysis worksheet and added some Data Validation to allow only items from the Named Range I just created.

Spread15

Spread16

I updated my Formula one last time to use the INDIRECT() Function to look at the value that the user selected from the Data Validation List and return the name as the Array back to the INDEX() Function. I’m no fan of the INDIRECT() Function and I use it as sparingly as possible – however, it is appropriate in this context.

Tidy Up

That’s it for today. What are your thoughts on Named Formulas? Do you use them in your daily work for other purposes? Do you abuse the INDIRECT() Function? Let us know.

, , ,