JKP Application Development Services.

                    Microsoft Office Application Development

Conclusion

Up • Introduction • Define • Use • Absolute/Relative • Context • Special Names • Formula In Name • Dynamic Names • XLM In Names • Arguments • Bugs Name Obj. • Conclusion
•  •

Search Zoeken

WWW
This site

Go to Ga Naar
Home
Up

Donate Donaties
Did you find something helpful on my site? Consider a donation!
Heeft u iets gevonden waar u wat aan had? Overweeg dan een donatie!

 

Range Names In Excel

Introduction 1 2 3 4 5 6 7 8 9 10 Conclusion

Conclusion

This article is focused on giving insight in the power of range names in Excel. Starting off with simply naming a range of cells, continued with the powerful option to put a formula in a name and topped off with showing how to put XLM macro functions to use in defined names, enabling you to do things you can normally only achieve using VBA.

By making proper use of defined names, you can build powerful and easy to maintain Excel solutions.

Name Manager

Creating and editing Range Names in Excel is much easier if you start using the Name Manager!

Links

There are various sites that have extensive information about Excel's capability to define a name that refers to a range of cells or a formula. Two instructive ones are:

Chip Pearson (English)

Ingrid Baplue (Dutch)

Comments

Since you've managed to get all the way to here, maybe you'd like to send me a comment!

Click here to send me an email.

You may also rate this article:

(Rated: 280 times, average rating: 6.4)

Comments

All comments about this page:

Comment by: Jan Karel Pieterse (3/8/2006 11:54:06 AM)

Dear Reader,

I invite you to comment to this page. It will help me to improve its quality!

Thanks.

Jan Karel Pieterse

Comment by: Dave Docherty (3/18/2006 3:42:30 PM)

Thank you for the useful description of your findings with Excel. Dave

Comment by: Graham (3/29/2006 1:51:55 PM)

Thanks for a very clear and useful intro to names. I'm sure a large number of people will find this very valuable.

I have a question, though, about referring to named ranges in VBA.

If I select Sheet1, cell A1 and type "MyName" into the name box, I create a global name that refers to Sheet1!$A$1.

Logically I would have thought that this would be a property of the Workbook object, and that I would refer to it as:

ThisWorkbook.Range('MyRange")

But this doesn't work because, I think, the Workbook object doesn't have a Range property.

So one option is to refer to the range including its sheet name:

Sheets("Sheet1").Range("MyRange")

This works, but it seems redundant to have to refer to Sheet1 again since this is already in the definition of the name.

Another option is to use the Application object:

Application.Range("MyRange"), which also works, and is something I use most of the time.

Which option do you use? Lately I've been wondering why no-one else seems to use the second approach. I wonder if this has something to do with having multiple workbooks open?

Is this something you would care to shed some light on?

Thanks

Graham

Comment by: Jan Karel Pieterse (3/30/2006 2:01:29 AM)

Hi Graham,

Thanks for your compliments.

If I want to refer to a name, I never use Application.Range, because that will refer to a range on the active sheet/workbook.

I tend to use:

Workbooks("mybook.xls").Names("MyName").RefersToRange

To refer to a global named range in mybook.xls.

If I want a local name, I use

Workbooks("mybook.xls").Worksheets("mysheet").Range("myName")

Comment by: Graham Long (3/30/2006 1:03:04 PM)

Well, I never knew that. I knew there had to be a better way. Thanks!

Graham

Comment by: Anant Basant (2/3/2007 8:24:27 AM)

A beautiful article. Truly amazing, particularly the information about using XLM functions through defined names. I request you to please keep writing such articles for our benefit.

Comment by: Armando Gutierrez (5/1/2007 10:56:15 AM)

FANTASTIC, explanation, Im from Colombia South America.

I was wonder to found this interesting solutions and how you explain this Very nice. sometimes I have a question may I using this way?? regards --AG

Comment by: Jan Karel Pieterse (5/1/2007 11:12:37 AM)

Hi Armadno,

Thanks!

If you want to ask questions, check out the newsgroup links on my links page

Comment by: Danny (11/8/2007 4:49:30 AM)

Thank you for your very impressive introduction to Excel defined names. There were several pieces of information that were quite new to me and very powerful: for instance that you can define names local to the sheet *and* the names are copied appropriately when you copy the sheet.

One thing I've wondered about that you didn't address: exactly how does Excel force a column to produce a single value when needed? I have created user-defined functions that expect a single value and work fine on named columns, but produce a DEF? error when I try to run "Evaluate formula".

Comment by: Jan Karel Pieterse (11/10/2007 8:12:06 AM)

Anytime you enter a formula that should return an array in a single cell it will either produce #Value! or just return the first value of the array.

A range name always returns an array if the formula in there is an array, you don't have to "control+shift+enter" like with a cell.

Comment by: Phil (4/4/2008 5:59:26 AM)

Thanks so much for this wonderfull explanation. Sharing my files with my colleagues was a kind a nightmare when trying to explain formulas and links. It is quite a lot easier now!

Kind Regards

Phil

Add a comment too!!!

Please enter your name (required, will be shown):

Your E-Mail address (optional; will not be shown, nor be used to send you unsolicited information):

Your comments on this page (will be shown):

<< Previous

    Subscribe in a reader

powered by longhead.com

Use the contact page to issue questions or comments about this website.
Copyright © 2003-2007 JKP Application Development Services.