Range Names : Good or Bad programming practice?

Quote:

---

Originally Posted by brettdj

I took the wording from your other thread. I think we could still edit the title if you like, the discussion has been broader than a programming discussion

---

:think: OK, but I never said programming. I said "spreadsheet modelling".

This thread has gone mostly down the path of exploring good/bad ideas related to using defined names in VBA. But, it is indeed pretty entertaining. Probably even a more interesting discussion than my original intent.

Programming best practice? ehhh... I think it's a looser topic with far more gray area. I'm pretty liberal when it comes to coding. (and not too liberal on anything else)

Change the title? I dunno, would any of you have voted differently had the poll been titled, "Range Names: Spreadsheet modelling best practice?

Granted, from the responses, I gather that there were already at least a few who voted that were already thinking in that sense.

John,

NameMaster is a fantastic tool for deleting the bloated names. http://www.decisionmodels.com/Downlo...Manager_32.zip

If you have a file that has been used for a while as the basis for generating new modules then running Name Manager can be quite an eye opener as to how much junk gets accumulated

As an aside, a sheet copy method, either manually or in VBA will replicate the names in the new book. This is normally useful as a copy and paste won't copy the names. But as per John's note the opposite effect is needed

Cheers

Dave

Quote:

---

Originally Posted by johnske

However, an objection to them is as a potential source of corruption for workbooks/spreadsheets. I've recently had to cope with several workbooks (from others asking for help) that contained many rogue range names.

These rogue names are impossible to delete and bloated the file-size of the corrupted books by several mega-bytes, the only way to get rid of them was to copy and paste the entire contents of the workbooks (+ code modules) to a new (uncorrupted) workbook.

John

---

Interesting you should mention that. Actually, there's a trick to killing the demon names. In TOOLS|OPTIONS if you toggle the R1C1 display format it will bring up this wonderful little window asking you to rename the rogues (believe it or not). Once given a suitable name, you can delete them at leisure.

Quote:

---

Originally Posted by Aaron Blood

Interesting you should mention that. Actually, there's a trick to killing the demon names. In TOOLS|OPTIONS if you toggle the R1C1 display format it will bring up this wonderful little window asking you to rename the rogues (believe it or not). Once given a suitable name, you can delete them at leisure.

---

Thanx Aaron :thumb didn't know that one - unfortunately (unless you could put it in a procedure {must look at that}) it wouldn't have helped in this instance cos there were around 8000 of them!!! :rotlaugh: :rofl:

Regards,
John :beerchug:

I could have sworn I saw the word programming in your thread starter. But now that you mention it I can't see it

As the discussion has been about non-VBA and VBA I've changed the title to be clearer

Cheers

Dvae

BTW: Dave - thanx for the link, just downloaded name manager and I'll have to try it on a copy of one of those books (if I can find one now)

I think I remember looking for this at the time it was needed and all I could find was a 'pay-for' version - which was not an option.

Regards,
John

Quote:

---

Originally Posted by johnske

Thanx Aaron :thumb didn't know that one - unfortunately (unless you could put it in a procedure {must look at that}) it wouldn't have helped in this instance cos there were around 8000 of them!!! :rotlaugh: :rofl:

Regards,
John :beerchug:

---

Actually, yes I think it could be automated.


8000 named ranges.... You hear that!

Go ahead, keep telling the CPA grads how great they are. They'll use em.
I know, isolated case right? Happens everywhere!

Ok I added my two cents worth to the Yes faction.
Basically I figure that things are generally simpler with named ranges.
Improved readability of code, of worksheet formulas etc.

Where ZI use named ranges badly in the past, it has been my error, not the use of named ranges that was bad practice.

I use FastExcel (paid version) which includes Name Manager (from decisionmodels.com) to reduce file size. It automates even the unused named ranges.

One problem I find is that if there are data pulls from databases, it will add named ranges to the Excel file. I think most people assume that once you have deleted the names, they are gone. They are - until you refresh the data, and the named ranges come back.

Overall, named ranges are a Good Thing, but when misused they can be more trouble than they are worth.

Something I am doing more and more often is using dynamic named ranges. That defeats the "what if they add/subtract data" problem. Of course, having too many dynmaic ranges can hurt performance. I also recently ran into a surprising problem with dynamic named ranges:

http://www.experts-exchange.com/Appl..._21414425.html

Patrick

I use range names extensively for my own work or for spreadsheets that are used as a fixed template for others to use. The reason is that when re-looking at them after some period of time it doesn't require much thought to understand how I had constructed it. So it's more for speed at a later date.

When creating spreadsheets that others might modify I tend to completely avoid range names as others may well be totally unaware of their existence and it tends to add confusion rather than enlightenment.

So perhaps not surprisingly my answer is yes and no, but in this context 'No', as ranges names are not too helpful to others and can be more confusing than helpful.

Quote:

---

Originally Posted by matthewspatrick

I also recently ran into a surprising problem with dynamic named ranges:

http://www.experts-exchange.com/Appl..._21414425.html

---

Patrick,

It is not so surprising, just think about what a dynamic range name. By virtue of the fact that you are using OFFSET, you are returning an array of values not a range.

The INDIRECT function accepts ranges or strings that evaluate to a range, such as A2:A100, but not an array such as {1,2,3}.

QED - the two do not go together.

Named ranges?been using them since they were invented. Only use direct cell references when no other option is available such as when reading other workbooks that are outside my control. Here are some thoughts:

Readability: As coders we refrain from using variables like i, j, and k and use more descriptive names for a reason, range names have the same benefit.

Flexibility: Range names adjust just like direct cell references in worksheet formula. But in VBA, that direct cell reference sticks like a fly to flypaper whereas range names just do the right thing no matter where they are defined. With named ranges and you can move them all over the place without impacting the formulas or the VBA code.

Auditability: Yes, there is a level of indirection one has to wade through...but we're smart are we not? Write an auditing function. I have one that marks all cells belonging to a named range with a red grid pattern. I save the previous grid and pattern settings in each cell's ID property so they can be restored with an undo mark routine. This function is managed from a non-modal dialog that has a list of all names in a drop down so I can quickly see which cells belong to a named range - it's actually easier to audit this way than just looking at direct cell references. There is also the paste list option from the Paste Name dialog. Still not happy? Use a routine to convert names to direct cell references:

[VBA]Public Sub ConvertNamesToCellReferences()

Dim Name As Name
Dim Sheet As Worksheet

For Each Name In ThisWorkbook.Names
For Each Sheet In ThisWorkbook.Sheets
Sheet.Cells.Replace What:=Name.Name, Replacement:=Mid(Name.RefersToLocal, 2), LookAt:=xlPart
Next Sheet
Next Name

End Sub[/VBA]

Productivity: Ease of coding in VBA is the one single most important reason for using named ranges. If it wasn't for VBA I probably wouldn't be as adamant. One amazing thing about names is you can leverage their existence to auto-generate sheet initialization code and quickly take advantage of the sheet's class properties. One development aid I built looks at a sheet and produces a list of names as public variables along with code to initialize them. Result looks something like:

[VBA]Option Explicit

' ##### Start of Generated Code #####

Public Caption As Range
Public CommandBar As Range
Public CommandBars As Range
Public Divider As Range
Public FaceID As Range
Public Level As Range
Public PositionMacro As Range
Public ShortcutKey As Range

Public Sub Initialize()

On Error Resume Next

Set Caption = Me.Range("Menus_Caption")
Set CommandBar = Me.Range("Menus_CommandBar")
Set CommandBars = Me.Range("Menus_CommandBars")
Set Divider = Me.Range("Menus_Divider")
Set FaceID = Me.Range("Menus_FaceID")
Set Level = Me.Range("Menus_Level")
Set PositionMacro = Me.Range("Menus_PositionMacro")
Set ShortcutKey = Me.Range("Menus_ShortcutKey")

End Sub

' ##### End of Generated Code #####[/VBA]

This instantly gives me access to all named ranges from within VBA as simple variable references, both from inside and outside the sheet module. I also have development aids that, with a hot key, create named ranges using headers over the current selection. Bottom line, I can take a sheet of data and with a few selections and keystrokes, have all my ranges named and sheet initialization code generated so I?m ready to start coding.

Paste Formula Problems: (This one is for my EE buddy Dave.) When you have a list/column of data, never name just the first cell. Always name the entire column excluding only the header. This way the formula:

C1=ColumnA+ColumnB

will copy/paste and Excel will do the right thing. With all due respect, the notion that this method is "counter intuitive and a dangerous default" is just plain wrong. Naming only part of a collection of data elements is counter intuitive and a dangerous default. There are many things in this world, and in Excel, that are counter intuitive...until one changes one's perspective. Dave, please noodle this a little before responding ? look at it from the perspective of a software engineer. If I ever need to name the first cell because I feel the need to use OFFSET instead of INDEX then I name it in addition to the whole column.

Need more? Dynamic named ranges. Love this range name formula for creating a menu from a variable list of elements (note the use of a full column range name to begin with):

=OFFSET(INDEX(Lists_Countries,1,1),0,0,COUNTA(Lists_Countries),1)

Kevin

:)

Kevin,

As I posted in that EE thread I use range names when they offer a clever solution or an approach that is otherwise impossible, ie

- conditional formatting with a range on a different sheet
- dynamic range names for graphs
- XLM functions

But when people use them to name single cells, such as XLD's earlier VAT example I think they add no value but increase the chance of errors - such as the hardcoding example I mentioned earlier

The "counter intuitive" quip was not from my perspective, it was from the average Excel user. In this case, the modeller was calculating discount factors for an NPV, and he managed to hardcode all the factors to the first year by copying through a range name. Rather justifiably, he thought that Excel contributed to his mistake.

I think this debate boils down to who will be using the range named model. If its for general release in formulae then for mine they are a firm no-no as they add complexity for the average user and increase the chance of errors. If its in VBA then its less of an issue as the VBA has probably already confounded the user enough.

Cheers

Dave

Quote:

---

Originally Posted by brettdj

But when people use them to name single cells, such as XLD's earlier VAT example I think they add no value but increase the chance of errors - such as the hardcoding example I mentioned earlier

---

That was naming a constant value, not a cell, and de facto a constant can only be a singleton. This is akin to assigning constant values to variables in code, actually a good practice. What sort of error can arise from that? Not a programming logic error, or spreadsheet logic error (i.e. human error), but an application error, beacuse if you mean human error, then that is nothing to do with names and can arise in any function, and thus can only be avoided by not doing anything.

BTW, it is a common misconception to call them range names, presumably because they are most often applied to ranges, but they are actually workbook or worksheet names. They can, as in my VAT example, have nothing to do with ranges.

LOL, John! That would have been fun, huh?

Aaron said:

Quote:

---

A lot of people suggest in their books and at universities that newbie excel modellers should be coached to use defined range names in developing spreadsheet solutions/models. Many (MANY) would suggest the concept of using defined range names in formulas is a "best-practice" technique.

---

And I recall something... stupid of me to forget... is it 2003? Or planned for a newer version? Where they are really souping up (or already did) the named ranges feature? Admittedly, I don't use 2003 enough to develop in (as if anything *I* do could be called development--put some red text in there!), so I haven't named many ranges...

(Small note: The deleted posts in this thread are a howl!!)

Quote:

---

Originally Posted by Dreamboat

(Small note: The deleted posts in this thread are a howl!!)

---

Aren't they just :)

According to me, it is a good practice for an experienced user and bad practice for a new user.

A user will not understand where the range is and how the code is working unless he knows the range or he himself is the writer of the code.

SO IT IS A BAD PRACTICE:dunno

Quote:

---

Originally Posted by brettdj

I use range names when they offer a clever solution

---

That is the kind of solution that is most likely to confuse the newbies that you express so much concern for!

Quote:

---

Originally Posted by brettdj

I use range names when they offer a clever solution ...

---

I like 'clever' solutions but that is for my own amusement and I would not recommend them as best practice.

Quote:

---

Originally Posted by brettdj

... or an approach that is otherwise impossible

---

Now that's a good point. Range names do offer solutions to otherwise intractable problems.

Quote:

---

Originally Posted by brettdj

But when people use them to name single cells, such as XLD's earlier VAT example I think they add no value but increase the chance of errors

---

Leaving aside that it is not a single cell at all, I have to disagree completely here. Having, say, VAT as a constant does add value and REDUCES the chance of errors.

Quote:

---

Originally Posted by xld

... and de facto a constant can only be a singleton ...

---

That's not entirely correct - you can name a array of constants.


Finally, one point which I don't think has been mentioned so far. Range names can be helpful to users - they can go to a named range very easily without having to scroll aimlessly looking for information or work out what address to use and go to that address - and when they do so, they can see at a glance whether that named range contains what they expect.

Quote:

---

Originally Posted by TonyJollans

That's not entirely correct - you can name a array of constants.

---

Then that is not a constant, it is an array, which is a set of values/objects.

Tony,

I don't know if you saw my earlier example about a user mistakenly copying a formula referring to a one cell named range and erronesly absolute referencing this cell. I've seen this happen more than once.

And if I can beat my drum one more time, range names add complexity for the end user.

The clever solution I referred to was a dynamic range chart. As you would have gathered by my posts to date, I'm not a big fan of imposing "clever me" techniques on others. In the business evaluation work that I do, good modelling is about good structure (same years in the same columns in different sheets), error checking (graphs, formulas checks, conditional formatting), full explanation (detailed notes rather than hidden comments), consistent cell formats for different intents (ie input area, formulas etc) and reduced complexity (avoiding linked files, macros, long winded IF formulas etc) as the model is not mine alone. Name ranges fall into the complexity pile.

Having dumped all that out there, if the model is for my use alone, I'll play to my hearts content. If I do use any code or links and the model has to go to someone else I ensure that they have a functioning copy without these attributes.

For me this debate reduces to who will be using the model. If its not just me then range names are to be used only as a last resort. I think that the poll results so far support this position, my guess is that a "good pratice" would be looking at a pass mark of at least 90% if not higher.

Cheers

Dave

Quote:

---

Originally Posted by xld

Then that is not a constant, it is an array, which is a set of values/objects.

---

If it's not variable, it's constant in my book :)

Dave,

Yes, range names add complexity. So do the alternatives. So does every other feature. The question, surely, should be do they add unnecessary complexity. Sometimes they do, sometimes they don't. Use them when they don't.

I agree comletely that the target audience is a big factor. Who is it for? What are they to do with it? Who will have to amend it? etc. Again I would say, sometimes they're right, sometimes not.

I'm not sure that there's an answer to the simple question of whether they're good practice or not, as it has been posed; the debate is interesting but seems to miss the point somewhere. Is it good practice to use indicators when you're driving a car? Well, yes if you want to inform other drivers of your intentions and you actually indicate correctly but no, if you give the wrong indication or indicate at the wrong time. Using indicators adds complexity to driving (so you probably don't do it if you're in a field) but it reduces the chances of an accident out there on the road so you do it then even though it's extra effort.

I reckon that name ranges are more like a sun roof than indicators, pretty, not integral to the operation and a potentially dangerous distraction:whip

Dave,

This is really more a question of preference and desire than whether or not it is good practice. As with most subjective methodologies, it really boils down to how willing one is to accept and use the methodology and how much it is accepted in the community. As with the Hungarian notation that was the rage a decade ago, these kinds of things will ebb and flow. There is really no tangible benefit or cost, just what we want to make of it. You, for example, find naming individual cells "add no value but increase the chance of errors." I, on the other hand, find that it makes the formulas more readable and decreases the chance of errors. We are both correct given our respective views and habits, and the audiences we serve and how we serve them.

It is, in the end, only a level of indirection above direct cell references - something a seasoned software engineer welcomes but a novice finds confusing. Meaningful name or explicit name. We used to look at our variables as single or double bytes at address $A182. Now we just give them names. Today we often don't even care about the variable?s type (Variant.)

In the beginning I felt the same way about COBOL relative to BAL. But now the issue of whether to use and how much to use assembly or machine code has been rendered mute for a number of reasons and we readily, as a community, accept higher-level languages as the standard. Now the debate is over VB versus C++ and that is even being muddled of late with VB.NET and C#.

Can you imagine a worksheet environment where you can't use direct cell references in a formula at all? Where you HAVE to name everything? When I look at history and how we have progressed from BAL to COBOL to C to VB/C# I think I can see it.

Kevin

Gee, Kevin, BAL, COBOL?? I'll bet you're a guy who has used PL/1 too. (I did.) Those were the days (sigh).

Quote:

---

Originally Posted by TonyJollans

If it's not variable, it's constant in my book

---

Methinks you confuse nouns with adjectives :think: .

Quote:

---

Originally Posted by Zorvek

Today we often don't even care about the variable?s type (Variant.)

---

We do, or we d*!#n well should. If we care about performance, code logic, or maintainability, we need to type our variables.

Next thing I'll hear is that it doesn't matter about declaring variables, as VBA will declare them at runtime!

Kevin,

For the most part I agree with you and I like the part about maybe one day having to name everything - it does kind of continue the trend towards ever higher levels of abstraction (which perhaps only us old programmers can see :yes) but, like xld, I have to disagree about not caring about data typing - the trend is towards stronger typing; do you want to go back to S0C7 abends and the like?

Continuing your historical perspective, ever since I can remember there have been standards imposed on developers. Some of these were for good reasons and are still considered good practice. Oftentimes, though, installations restricted various language features for the simple (and stated) reason that they were too difficult to understand. Part of the reason for this was that demands for developers far exceeded the availability of skills and many developers barely understood what they were doing; commercial reality was the driving force.

I have never seen any comprehensive standards for Excel development, which in itself is interesting, but if there were any, I wonder whether the the same view would prevail? The prevalent corporate view seems to be that any idiot can do Excel (and certainly many idiots do!) but should there be standards, and are the commercial realities the same? And if so, what would be allowed? Or is there some fundamental difference between, say, Cobol and Excel?

Quote:

---

Originally Posted by Dreamboat

(Small note: The deleted posts in this thread are a howl!!)

---

Oh maaaan! Did I miss some good ones?

Quote:

---

Originally Posted by Zorvek

Dave,
This is really more a question of preference and desire than whether or not it is good practice. As with most subjective methodologies, it really boils down to how willing one is to accept and use the methodology and how much it is accepted in the community. As with the Hungarian notation that was the rage a decade ago, these kinds of things will ebb and flow. There is really no tangible benefit or cost, just what we want to make of it. You, for example, find naming individual cells "add no value but increase the chance of errors." I, on the other hand, find that it makes the formulas more readable and decreases the chance of errors. We are both correct given our respective views and habits, and the audiences we serve and how we serve them.
Kevin

---

I agree - its all about the audience.

When our group builds models we try to make the logic as transparent as possible. So rather than referring to cell A182 on sheet 'Inputs' I would copy a link to that row accross to each major section where it was used. So a user would see say four lines in Excel say, Saleable Product, Product Price, Foreign Exchange and CPI which are used to derive revenue rather than one long formula linking to several sheets. And there would be notes as to where the data was sourced, what date is was current from and an explanation of what the calc was doing. I think this is as "readable" as it gets as the major drivers are can be viewed together without delving into the formula and audit bar.

My perspective on this has been shaped by the work I now do. I used to build complex models for my use alone .... but when you are submitting or reviewing a billion dollar investment you want to be understanding how the inputs drive the valuation, not dicking about trying to follow the model architecture.

Named ranges are certainly not a big problem compared to poor layout, lack of comments, poor fill right procedures (I kid you not), complex algorithims, macros and linked files which lead to almost all the mistakes that we come accross on a project review. But in our modelling guidelines we don't recommend that people use names - the readability comes from good layout and notes as per my example above.

Quote:

---

Originally Posted by Aaron Blood

Oh maaaan! Did I miss some good ones?

---

Yep :)

Cheers

Dave

Hi all :hi:,

The only answer is: Yes

What most people seems to miss is the need for system documentation of all kind of applications, which should always include a list of used range names and their contents.

The key to have a good start for maintance and further development is:
- System documentation.
- Well commented code.
- Overall / Detailed schemas.

Here I could provide the board with a nice VBA-solution to document range names but instead I suggest that some of the more frequent members write a KB-article covering it.

However, another close related issues is when to and how to apply range names. This may be subject to another poll / discussion.


Kind regards,
Dennis

Dennis,

How nice to see you :)

And a couple of excellent points:

• No solution is complete without documentation, which should of course include details of any named formulae and their uses.
• The naming of ranges and the application of those names are different. This has been hinted at a couple of times in the discussion but not stated as clearly as that.

Quote:

---

Originally Posted by TonyJollans

it does kind of continue the trend towards ever higher levels of abstraction

---

Tony, Glad to see someone agrees with me, I started to doubt myself (not) :)). Just a small point, but not sure you are using abstraction correctly here, abstraction is the process of picking out (abstracting) common features of objects and procedures. I would go further and say that it is isolating those features into a separate object (a class - now there's a subject to get us going :devil:) so as to make the rest of the application oblivious to those features, just know how to interact with that object via a strongly defined interface. What is being discussed here is feature of a product, not a development methodology, a methodolgy would equally apply to another product that does not have this feature.

Quote:

---

Originally Posted by TonyJollans

I have never seen any comprehensive standards for Excel development, which in itself is interesting, but if there were any, I wonder whether the the same view would prevail?

---

I have. There was once such a beast produced by Price Waterhouse (IIRC) which was very good. Haven't checked it out in a while, but you can see it at http://www.eusprig.org/smbp.pdf. There is a good site with a few such links http://www.sysmod.com/sslinks.htm

Quote:

---

Originally Posted by XL-Dennis

Here I could provide the board with a nice VBA-solution to document range names but instead I suggest that some of the more frequent members write a KB-article covering it.

---

No need, Jan Karel Pieterse has already done it with his NameManager utility http://www.decisionmodels.com/downloads.htm

Hi Dennis,

Great to hear from you :)

Yes, documentation is a necessity for a good model, it isn't an optional extra.

Take care

Dave

Quote:

---

Originally Posted by xld

Just a small point, but not sure you are using abstraction correctly here, abstraction is the process of picking out (abstracting) common features of objects and procedures. I would go further and say that it is isolating those features into a separate object (a class - now there's a subject to get us going :devil:) so as to make the rest of the application oblivious to those features, just know how to interact with that object via a strongly defined interface.

---

Goodness, I'm not sure I want to digress to a discussion of English! Abstraction, surely, means making abstract, or conceptualizing. Using a name instead of a specific reference to a location (or value or formula) is doing that isn't it? The user doesn't need to know what the name refers to, merely that it returns the desired result - analagous to Class in many respects.

Quote:

---

Originally Posted by xld

What is being discussed here is feature of a product, not a development methodology, a methodolgy would equally apply to another product that does not have this feature.

---

'tis true! What the discussion started out as was a feature of a product, but it has widened to include the development methodology behind the feature. There are two issues I suppose - the methodology and the product's implementation of it and I think that many of the negative comments have been about the product feature rather than the concept. Now, if that's so, how should Excel Names be improved?

Quote:

---

Originally Posted by TonyJollans

Using a name instead of a specific reference to a location (or value or formula) is doing that isn't it?

---

Not to me it isn't, it is a difference between concept and implementation.

Quote:

---

Originally Posted by TonyJollans

'tis true! What the discussion started out as was a feature of a product, but it has widened to include the development methodology behind the feature.

---

That is true, but I was simply referring to the use of the word abstraction here when I talked of features versus methodologies, not the overall discussion.

Quote:

---

Originally Posted by TonyJollans

There are two issues I suppose - the methodology and the product's implementation of it and I think that many of the negative comments have been about the product feature rather than the concept. Now, if that's so, how should Excel Names be improved?

---

We are getting confused, the product does not implement a methodology, it implements a set of features and functions. The methodology relates to, or should relate to, spreadsheet modelling, not the product, or a feature of the product. It is perfectly possible, and legitimate if so decided, to have riules that say you do not use names in an Excel spreadsheet, but that is not methodology, it is procedure, and local procedure at that. The methodology would not concern itself with a specific feature.

I have no idea as to how to improve names as I have yet to hear of anything wrong with them. The two examples I have seen were poor programming/development in the first case, and a lack of understanding of another Excel function in the other. I keep hearing that names are bad, but no evidence of why names are bad, just plenty of general points about spreadsheet modelling (read that paper, I think you will get much from it :clap:).

Of course, it is possible to give examples of problems, such as a worksheet is slow when it has 200 worksheets, 3,000 names, etc., etc. But does that make names bad?

And of course, every feature might benefit from further functionality, but IMO Excel have it about right in that most functions are relatively simple, but by mixing and matching, and if necessary by use of VBA, can be extended almost indefinitely. Just look at what we have done with SUMPRODUCT.

Here's my view on named ranges. I believe that using them within a worksheet formula tends to lend some readablility to the formulas.
=Vlookup(A2,TotalsSheet!A1:B200,2,false)
is more difficult to understand then,
=Vlookup(A2,WageRate,2,false).
So just like a well named variable in VBA, you have a better understanding based on the name. Just by viewing the formula you know you are looking up WageRate.

The other issue is with Office 2003, have you ever tried to do a mail merge without a named range??? It's darn near impossible now.

As far as using named ranges in code, I wouldn't recommend it, not because you shouldn't , but just because in code you have so many better tools to work with then in worksheet formula's. Give me a dynamic range object any day over a named range.

Cal