Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Downloads > Name Manager
Deze pagina in het Nederlands

Name Manager: Range names made easy

For Microsoft® Excel®

Description

If you are in need of a utility to manage defined names in your Excel models, this one is a must-have. List all names in your active workbook. Filter them using 13 filters, e.g. "With external references", "With errors", Hidden, Visible. Show just names that contain a substring. Show just names unused in worksheet cells. Edit them in a simple dialog or make a list, edit the list and update all names in one go. Delete, hide, unhide selected names with a single mouse click.

Created in collaboration with Charles Williams, www.decisionmodels.com and Matthew Henson (mhenson@mac.com) who ensured the utility works on Mac Excel too. See a screenshot here.

The Name Manager is freeware, all I'd like to ask you is to tell everyone if you like it and to tell me if you don't.

Current version downloads

Name Manager for Excel 2007, 2010, 2013 and 2016 V4.3 (Build 655, Dec 13, 2016, downloaded: 87,974 times)

Name Manager for Excel 97, 2000, XP and 2003 V4.3 (Build 655, Dec 13, 2016 Downloaded: 121,661 times)

Tip: Excel 97 users can download Name Manager version 3.2 below if they experience compile errors.

Solving Compile errors related to Name Manager

Some users get a compile error during load of Name Manager: "Compile Error in hidden module, fxlNameManager".

This error is mostly resolved by:

Excel 2003: open Excel and select Help, detect and repair from the menu.
Excel 2007/2010/2013: Open Office Setup from Control Panel, Programs and do a repair of the Office installation.

After doing so, Name Manager should work as expected.

New in this version (4.3):

Features:

Old versions

Not sure version 4.2 is stable? Name Manager 3.2 (Downloaded: 9,699 times) is still available!

Mac users, please note : version 4.2 does not work in the Mac environment, due to the use of Windows API calls. For your convenience, Name Manager 2.3 (Downloaded: 8,865 times) is still available. Note that unfortunately NM is NOT compatible with Office 2016 for Mac.

Instructions

Download the zip file by clicking the link above that says "NameManager.zip". Unpack the files in the zip file to a folder of your liking and open the file called "Setup Name Manager.xls". Press the button in that file to install.

Click here to request support or issue comments or suggestions on this product.


Comments

All comments about this page:


Comment by: Jason (4/17/2006 7:02:34 AM)

Excellent tool!

 


Comment by: Jan Karel Pieterse (4/18/2006 5:26:56 AM)

Thanks Jason!

 


Comment by: Michael (4/19/2006 12:59:51 PM)

Nice work. This tool saves me hours of work.

 


Comment by: Jan Karel Pieterse (4/19/2006 10:56:46 PM)

Thanks Michael, much appreciated.

 


Comment by: Marco F. (4/27/2006 7:55:18 AM)

Very useful utility!
Solved easily my problem in managing names no more used

 


Comment by: doco (4/28/2006 1:28:45 PM)

Don't know how I got along without it. Honestly, it is handier than a pocket on your shirt!

 


Comment by: Jan Karel Pieterse (4/29/2006 6:37:44 AM)

Hi doco,

:-)

 


Comment by: Hamilton R. Romano (5/2/2006 8:11:53 AM)

Congratulations for your addin.
I would like to suggest a new feature:
Eliminating the selected names and converting the content of the cells that make reference to these names.

 


Comment by: Jan Karel Pieterse (5/2/2006 10:32:46 AM)

Thanks Hamilton.
Your suggestion was one of the things I had in mind for future addition. Now that the search functionality is in place, this will not be hard to implement.

 


Comment by: PRITI GALA (5/10/2006 7:03:51 AM)

EXCELLENT FEATURE.
MANAGES WORK VERYWELL
THANKS.

 


Comment by: William (5/10/2006 9:48:24 PM)

Perfect.

Many thanks

 


Comment by: Kian (5/16/2006 12:48:24 PM)

Excellent tool, highly recommended. Should have been part of the Excel application to begin with.

 


Comment by: Dave (5/19/2006 6:53:44 AM)

Please contact microsoft and sell this to them for future releases of Excel. This utility is a must have if you ever work with names in Excel.

 


Comment by: Jan Karel Pieterse (5/19/2006 11:11:45 AM)

Hi Dave,

Well, in fact I have and you'll see quite a better interface in Excel 2007. Not as good as this one, but it is an improvement. Didn't get any fee though.

 


Comment by: Tony Evans (6/12/2006 2:48:28 PM)

I consider this excellent tool to be an essential add-in and recommend it to any Excel user.
I like the new "Rename a name" feature, very useful.

Thanks.

 


Comment by: Kanwaljit Singh Dhunna (6/13/2006 3:11:41 AM)

Dear JKP,

May you live 1000 years !!!!!!! I am waiting for the same to be a part of Fast Excel (I am a FastExcel V 2 user also).

 


Comment by: Kevin Roth (6/13/2006 5:36:20 AM)

Re: "didn't get any fee though"...

Too bad. It's not like Micro$~1 can't afford it! ;-)

 


Comment by: Clayton Lock (6/13/2006 12:23:26 PM)

I used names in Excel loads and loads and I have been looking for a tool like this for ages. It is fantastic and will save me hours. I will tell my ImproveYourExcel.com newsletter subscribers about it in one of the next editions.

Many, Many, Many Thanks

 


Comment by: Jan Karel Pieterse (6/19/2006 7:59:02 AM)

To
Clayton Lock,
Kanwaljit Singh Dhunna,
Tony Evans:

Thanks to you all!

Regards,

Jan Karel

 


Comment by: Barbara Allen (6/27/2006 3:49:37 PM)

THANK YOU!!!
I've had an unused name that I did not knowingly create infesting my worksheets for years and now it's GONE!
Thank you!!!!

 


Comment by: Ivan F Moala (7/12/2006 10:12:22 PM)

Jan
Have to say this again, thanks for this, an invaluable tool, one of the only ones I recommend.

 


Comment by: kamal (7/21/2006 8:36:36 AM)

hi sir,
this is kamal from india
i m very happy you r providing free stuff for excel student. I hope you will send me more tutorials

thanks
kamal singh
from india

 


Comment by: OO7-kanwal (7/23/2006 7:09:54 AM)

Dear JKP,
I have licensed version of Fast Excel 2.1 installed at my desktop. It contains the embedded 3.2 version of name manager. Recently I also installed 4.0 version. But now the 4.0 version is not appearing despite being installed at a difference location. Everytime I try to use the version 4.0, version 3.2 appears on the screen. I don't know how to get access to version 4.0. I feel it would be better enough if the Fast Excel V2 also contains the new version. Please help. I have deleted and installed version 4.0 many times.

Regards
kanwaljit Singh Dhunna

 


Comment by: Mr. Mike (7/30/2006 9:30:53 AM)

Useful, useful, useful!!!

 


Comment by: Gabriel Raigosa (8/2/2006 10:19:26 PM)

Interesante y util aplicación, falta el ESPAÑOL. Es facil usar en ingles.

Calificacion "AAA"

Colombia - Sur America

 


Comment by: Jan Karel Pieterse (8/3/2006 1:17:58 AM)

Hi Gabriel,

Thanks for your comment (Muchas Gracias is all the Spanish I can write)!

If you want the utility in Spanish, simply add the Spanish translations to the file "NameManager translations.xls" and mail the file to me!

Regards,

Jan Karel Pieterse

 


Comment by: Roger Govier (8/18/2006 4:59:42 AM)

Jan
Absolutely brilliant.
I had changed the definitions of many named ranges in the latest version of a clients Workbook, to get rid of volatile functions and speed things up.
I was wondering how I could easily transport those same revised definitions to the same workbook for earlier years.
Your latest version of Name Manager with "Pickup" was the answer. I just copied the sheet with all of the Names to the older workbooks, used Pickup and Voila! the job was done.
Saved me hours of work.
Very many thanks

Regards
Roger

 


Comment by: Jan Karel Pieterse (8/18/2006 8:24:11 AM)

Hi Roger,

Thanks for the compliments!!

Regards,

Jan Karel

 


Comment by: Helena (8/30/2006 11:41:49 PM)

This is so amazing! It's really a big help to me. Thank you so much!

 


Comment by: Cesar Julio Candanedo (9/2/2006 6:08:21 PM)

Superb!

 


Comment by: Tony Fuller (9/3/2006 10:03:19 AM)

Amazing tool. However, I don't see the rename function referred to above. I still have to manually rename unusable names manually one by one.

 


Comment by: Jan Karel Pieterse (9/4/2006 12:25:20 AM)

Hi Tony,

There is no button for renaming a name, you simply double click a name, or highlight one and hit F2.
I agree it is not very discoverable.

 


Comment by: Thusitha Fernando (9/11/2006 7:31:14 PM)

Thank you veru much for your excel package

 


Comment by: DaveT (9/14/2006 6:06:28 PM)

This is a excel(ent) product. Saved so much time.

Thanks very much.

 


Comment by: Travis Morien (9/15/2006 8:43:47 AM)

Thanks a lot for a very handy utility. Excel can be a real pain when trying to keep track of large numbers of cell names in complex spreadsheets, but your utility is a vast improvement.

 


Comment by: Chandan (9/15/2006 7:11:41 PM)

splendid

 


Comment by: chars Joe (9/16/2006 9:28:33 AM)

thanks for your free offer.

 


Comment by: ARSHAD MASOOD (9/18/2006 3:40:53 AM)

I appreciate your efforts to provide these services free of charge.

 


Comment by: ganesh (9/26/2006 11:09:48 AM)

I shall be very grateful if the product is of help to me

 


Comment by: A Martin Charles (9/28/2006 4:29:26 AM)

great. I was wondering someone will help me in doing something in this line.great that i got it all readymade.Thanks

 


Comment by: George Lynch (10/10/2006 9:48:37 AM)

Hi Jan Karel,

I have been remiss in not sharing my thoughts and comments with others, given that I have been using your Name Manager tool for several years. As the other posters have mentioned, it is invaluable, especially to those of us who deal with large groups of names, and it adds a dimensionality to Excel that is much needed.

I, too, thank you not only for your efforts but also for your generosity in giving this excellent utility to the user community free of charge.

I agree fully that your utility remains superior to the (much improved) native one that will be found in Excel 2007.

Thanks again and keep up the great work.

George

 


Comment by: Jan Karel Pieterse (10/10/2006 9:53:24 AM)

Hi George,

Gee, thanks. Much appreciated.

And thanks to everyone else who took the time to write up something here!

Regards,

Jan Karel Pieterse

 


Comment by: kanwaljit (10/26/2006 1:59:16 AM)

Hi Jan,

I am using Name Manager 4.0, though I have licensed version of FastExcelV2.0 too. Whenever I get a list of unused names in the workbook, it provides me with a list of names which are being used in validation. Why it is happening ?

Regards
Kanwaljit

 


Comment by: Jan Karel Pieterse (10/26/2006 4:05:22 AM)

Hi kanwaljit,

To be honest, the unused names filter has not yet been enhanced to search in Excel's objects and properties(Validation is one of those). Thus, the unused filter lists names that are not used in cell formulas or in names.
All other places where a name might be in use are not checked with the unused filter.
I plan to add this feature in the next version though.

 


Comment by: kanwaljit (11/1/2006 4:21:26 AM)

Hi,

I opened the name manager 4.0 and then minimized it. Then I clicked on X ("close") button on the minimized window and en error message appeared.
"Run time error 384. A form can be moved or sized while minimized or maximized."

Is that any bug ?

Kanwaljit

 


Comment by: kanwaljit (11/1/2006 4:27:41 AM)

Hi Jan,

I have noticed that whenever you use advance filter on any range, excel automatically creates "Extract" and "Criteria" named ranges (names local to that particular sheet). Is there any way to prevent such occurences ?

Kanwaljit

 


Comment by: Jan Karel Pieterse (11/1/2006 5:59:50 AM)

Hi kanwaljit,

Sounds like a bug. Thanks for reporting.

 


Comment by: Jan Karel Pieterse (11/1/2006 6:01:13 AM)

Hi Kanwaljit,

Those names are indeed handled by Excel. It is not wise to remove them, unless you know what you are doing.

 


Comment by: Kanwaljit (11/1/2006 10:15:54 AM)

Hi Jan,

Before doing anything I defined a name (local to the sheet) for copyto as per my requirements and named it "Extract". But when I applied the advance filter, range name defined by me was overwritten by the default excel name. Why does that happen ? I again want to know, Whether there is any way to prevent the automatic creation of such names ?

Kanwaljit

 


Comment by: Jan Karel Pieterse (11/1/2006 2:13:06 PM)

Hi Kanjwaljit,

No, you cannot prevent that from happening. There is a couple of names you cannot keep and this is one of them.

Others are:

_FilterDatabase
Print_Area
Print_Titles
Criteria

 


Comment by: AL (11/8/2006 7:59:48 AM)

thanks for the good service

 


Comment by: Tim (11/8/2006 10:28:04 AM)

This is a very useful tool. Showed me where there were problems and code problems immediately.

 


Comment by: kanwaljit (11/9/2006 7:12:40 AM)

Hi Jan,
Is it possible to name the defined names by using some external source.
E.g., I defined some range as "ABC". I have cell A1 containing "ABC". Is it possible that when I write "DEF" in cell A1, the defined name automatically changes to "DEF"

Kanwaljit

 


Comment by: Jan Karel Pieterse (11/9/2006 9:10:42 AM)

Hi Kanwaljit,

I don't think you can. You would need to write VBA code for that to happen, which will be quite complex if you also want to "rename" that name everywhere the name is used.

 


Comment by: kanwaljit (11/9/2006 9:59:52 PM)

Hi Jan,

We can hide or unhide names through name manager. Where these names are stored when they are hidden. Is there any option in Name Manager so that the names get unhidden only through a password. If not, please consider including it in next version.

Regards
Kanwaljit

 


Comment by: Jan Karel Pieterse (11/9/2006 10:49:31 PM)

Hi Kanwaljit,

You cannot password protect defined names. So doing it through Name Manager is also impossible.

 


Comment by: kanwaljit (11/9/2006 10:56:59 PM)

Hi Jan,

Where these hidden names are stored ? Can't that location be protected ?

kanwaljit

 


Comment by: Jan Karel Pieterse (11/10/2006 4:04:28 AM)

Hi Kanwaljit,

Names are stored inside the workbook somehow and cannot be protected.

 


Comment by: Torsten (11/15/2006 12:48:24 AM)

Hi there!

This is an excellent utility which makes working with names much easier and more fun.

Apparently there is a small bug when using the name manager with Office XP MUI: When I change the Office XP Language settings from german to english name manager generates a type mismatch error and is not shown in the menu (probably because the menu name changed from 'Extras' to 'Tools' due to the language switch).

Thanks for the great work!

Torsten

 


Comment by: Jan Karel Pieterse (11/15/2006 6:29:52 AM)

Hi Thorsten,

Strange. NM does not use the names of the menus, but their Id's, so language should make no difference (I use 2003 MUI with Dutch and English and haven't seen this problem yet).

 


Comment by: Jan Karel Pieterse (11/15/2006 6:56:42 AM)

Hi Thorsten,

Found the culprit, it was caused by a small bug when retrieving settings from the registry. I forgot to explicitly convert one boolean to an integer. When you switch language, VBA tries to convert "Wahr" to a real boolean value, which fails with English UI.

This is now fixed in the latest build.

 


Comment by: Greg (11/17/2006 10:39:14 AM)

Great tool! I just started using it and have run into a problem I can't figure out. I have one sheet out of about 9 that doesn't seem to be seen by Name Manager. When I choose Name Scope as All, and Name Type(s) as all, I do see the 4 names. However, when I select that tab, and select "Local to active sheet", nothing shows up. Also, in viewing all/all it says "Showing 78 of 96 names". Where are the other 18??

Thanks!
Greg

 


Comment by: Jan Karel Pieterse (11/17/2006 12:18:59 PM)

Hi Greg,

The names do refer to that worksheet, but obviously aren't local to the worksheet. Check out the article on defined names on this website for more information on names' scope.

You've likely unchecked "Show system names", hence the count.

 


Comment by: Atanis (11/30/2006 9:16:52 AM)

Dear Jan Karel,

I first want to thank you for having developped this tool, which is indeed invaluable.
I wanted to upgrade build 535 (which works perfectly) to build 577, but I get systematically the following error:
Fout 13 tijdens uitvoering
Typen komen niet met elkaar overheen
It appears just after the windows, with all names listed, is displayed.
When I close Excel, I then get:
Fout 440 tijdens uitvoering
Automatiseringsfout

I am using a dutch version of excel 2000 and windows xp. May you need more information to catch this bug, please contact me.

Best regards,
Atanis.

 


Comment by: Oscar Jaime (12/31/2006 11:14:17 PM)

Thanks a lot

 


Comment by: ODRAN (1/2/2007 3:12:48 PM)

Hallo !
I've just started to use this intelligent add in , but, do not seem to be able to find something that is absolutely essential if one is working with many (many) names: a way to print them. Is ther any ?
thnks
Odran

 


Comment by: Jan Karel Pieterse (1/2/2007 11:21:11 PM)

Hi odran,

Well, there is no direct way to print the names from within Name Manager, but you can use a simple workaround: List the names on a worksheet and print the worksheet.

On the Name Manager, there is a "List" button (find it next to the delete button). Click that button. Then format the resulting worksheet to your wishes and print that.

 


Comment by: Odran Rez (1/3/2007 8:16:38 AM)

Thanks Jan !
Great way of printing !

 


Comment by: Tony (1/3/2007 12:25:34 PM)

Is there a way to rename multiple unusable names all at once rather than one by one? I have inherited files with hundreds of unusable names.

Thanks again.

 


Comment by: Jan Karel Pieterse (1/3/2007 12:41:46 PM)

Hi Tony,

Yes there is. I have a tool for that, but it isn't free.

If you send me an email I'll quote you the price. (goto the contact page to find a link to email me).

 


Comment by: Alan Bradshaw (1/4/2007 9:16:47 AM)

Hi Jan Karel

If you were an english man you would be knighted.

 


Comment by: Jan Karel Pieterse (1/4/2007 10:51:01 AM)

Hi Alan,

Thanks!

 


Comment by: Erik (1/5/2007 9:38:19 AM)

Excellent Add In. I've been using it for a while now with no problems. However, now when I attempt to change a reference, I get an hourglass, and my system freezes. I have to ctrl alt del to get out of it. Once I do that, a blank dialog box comes up, with ok and cancel buttons. I hit ok, and then a define name dialog box (native excel I believe) pops up. The short of it is it stopped working. I've attempted to delete and reload, and still the same issue. Any suggestions?

 


Comment by: Jan Karel Pieterse (1/6/2007 2:40:00 AM)

Hi Erik,

Is this an issue with the current version?

 


Comment by: Erik (1/8/2007 7:02:04 AM)

I haven't switched version of anything, I downloaded the most up to date Name Manager, and I use Exel 2003 as I always have.

 


Comment by: Jan Karel Pieterse (1/8/2007 7:57:11 AM)

Hi Erik,

Could you give me the steps to reproduce the error please?

 


Comment by: Connie (1/8/2007 2:35:19 PM)

Jan,
I am really excited to get first hand experience of how this wonderful tool can help me but it keeps crashing my Exel. I have managed to inherit some files with hundreds of unused names. I need to get rid of them, please let me know if you have any suggestions.

 


Comment by: Jan Karel Pieterse (1/9/2007 12:36:20 AM)

Hi Connie,

You can send the file to me by email and I'll have a look. Look on my contact page for my email address.

 


Comment by: Erik (1/15/2007 7:07:03 AM)

Lets say I want to rename, or change a formula. I use your tool to do so as usual. When I hit the ok button, i get an hourglass, that will just sit there forever if I let it. I finally attempt to ctrl/alt/delete, and when I do, the task manager comes up which is expected, But so does an excel window titled "Microsoft Excel". It has no text, just an input box. If I enter something there (not sure what its looking for) an hit ok, the Define Name Dialog box then comes up. If I then hit ok, there, the program no longer hangs, but my mouse continues to be an hourglass, until I go back into your program. Very strange, would like to fix it as I have many names and would like to continue to use your excellent tool.

 


Comment by: Nis Jespersen (2/1/2007 10:01:26 AM)

I've tried your tool with grat success :-). Now I would like to know a little bit about how you do it. We are working with a project dealing with a lot of names (40000+) and has build our own name manager(C#) with a special functionality. The only pain actually is the time it takes to open out name manager. So if you could tell us how you can load 40000 names in 2-3 seconds, we would really appreciate it. Our namemanager uses 50-60 secs. to open with 40000 names.

 


Comment by: Jan Karel Pieterse (2/2/2007 7:48:55 AM)

Hi,

This may be of interest to others too:

Nis uses a For i=1 to... loop that adds the names to a listbox one-by-one, using the listbox's AddItem method.

NM uses a For Each..Next loop that pulls all names into an array and then uses the List property to add the array to the listbox in one fell swoop.

 


Comment by: Erik Thorne (2/2/2007 3:26:33 PM)

Jan-
You don't have to post this, as this is a very good tool, and I don't want it to appear that there are issues with the product. But I am going crazy. I manage a large spreadsheet for my company, and am still getting locked every time I attempt to either change a formula or add a formula. I need Name Manager to work!!! Please help.

 


Comment by: Jan Karel Pieterse (2/4/2007 3:08:35 AM)

Hi Erik,

I do have to post, as you haven't entered an email address I could reply to.

Please send me the offending file (you can remove all information in it, I only need the names) so I can have a look.

 


Comment by: Laurel (2/20/2007 11:46:47 AM)

Hi,
I really like your program especially after hours of frustration with trying to use Microsoft's poorly designed system.

Maybe I've missed this somewhere, but is there a way to use your program to perform the "Apply" command as found in Excel's name menu? I want to apply all or nearly all of the names to my spreadsheet.

Thanks.

 


Comment by: Jan Karel Pieterse (2/21/2007 1:26:15 AM)

Hi Laurel,

You haven't missed a thing, it isn't in Name Manager. Sorry!

 


Comment by: MotorXX (2/23/2007 11:23:18 AM)

Hi there! Author, yesterday I just thought about it too :) .
And great design, interesting site name www.jkp-ads.com :), I see you you're are not newbe. Keep up with the good work!

 


Comment by: ahmed fayyad (2/24/2007 3:21:53 AM)

very Thanks

 


Comment by: Fxr (3/21/2007 6:13:57 PM)

Thank you so much for such a useful & nicely designed tool. Somehow, a functionality that MS should integrate in Excel.

 


Comment by: Sab (3/23/2007 7:57:24 AM)

Excellent tool...thank you.

 


Comment by: Jamshed Iqbal Bagera (4/3/2007 4:28:38 AM)

Its very good but there is a need to improve it or make it more convenient for the begginers like me. Although I am working on excel but i want to improve my Excel skills and i hope u will guide me in right directions.

 


Comment by: Jan Karel Pieterse (4/3/2007 5:14:31 AM)

Hi Jamshed,

Well, Name Manager is indeed aimed at the more proficient Excel user.

What enhancements did you have in mind?

Regards,

Jan Karel Pieterse

 


Comment by: Rachael Vance (4/5/2007 4:07:03 PM)

The "Goto selected names" function doesn't seem to work as soon as I protect the sheets in my workbook. Is there something I am doing wrong?

 


Comment by: Jan Karel Pieterse (4/6/2007 2:38:03 AM)

Hi Racheal,

There may be 2 reasons why it doesn't work:

1. NM has difficulty resolving the refersto formula into an address to go to

2. The name points to a range you cannot select because of the protection settings.

I suspect it is 2 here and this can only be resolved by changing your protection settings. After all, NM does not know your protection password and hence cannot change protection to try to select the range.

 


Comment by: Binay Patra (4/10/2007 10:24:39 PM)

Excellent !
Many Thanks !

 


Comment by: SW, China (4/13/2007 12:10:18 AM)

Excellent tool ~ Thanks a lot!

 


Comment by: BG, Netherlands (4/18/2007 6:22:03 AM)

Great tool. Saves time and effort.

Ran into two problems:
Could not handle R1C1 reference style
Could not handle formula's using ";" parameter seperator like: =COUNTIF(tblPersonsComplete;F5)

The first problem has a simple work-around (turn of R1C1 style temporary)
But I did not find a work-around for the second problem. In the Netherlands the formula-parameter separator is a ";" (semi-colon) instead of a "," (comma)

 


Comment by: Jan Karel Pieterse (4/18/2007 7:37:17 AM)

Hi BG,

The ; should NOT cause you any trouble, the tool is designed to work with any regional setting/UI language.

Does it work properly in A1 mode?

 


Comment by: Ed Steinberg (4/24/2007 7:19:38 AM)

Awesome tool! Save hours of time and reduces errors.

BTW, one of my long time friends and colleagues is also "Jan Karel"

 


Comment by: Kanwaljit Singh Dhunna (4/28/2007 12:35:02 PM)

Hi Jan,
I am using Build 581. It seems you have enhanced the unused names facility to look into excel's object properties like validation ? Am I right ?

Regards
Kanwaljit

 


Comment by: Jan Karel Pieterse (4/30/2007 1:30:44 AM)

Hi Kanwaljit,

No, unused names only checks formulas and other names.

 


Comment by: kanwaljit (5/16/2007 4:14:05 AM)

Hi Jan,
It would help if you could mention the changes made in the new Build. That would help track any unknown problems in the database which might have gone unnoticed.

Regards
Kanwaljit

 


Comment by: Jan Karel Pieterse (5/16/2007 6:27:30 AM)

Hi Kanwaljit,

I'm not sure I understand:
What database are you referring to?

If you want full support for this tool, we can always make some arrangements on a commercial basis.

 


Comment by: Kanwaljit (5/16/2007 10:39:54 AM)

Hi Jan,

What I meant was that many a times you add new utilities / options, which might have helped uncover an error / speed some process, had they been known earlier. Many a times you remove some bug which might have remained suppressed or may have suppressed/supported an error. It would be better if you can through some light upon the changes made by you in new builds. I am a licensed user of Fast Excel and frequently downloads the new Builds of Fast Excel containing new builds of Name Manager. That's why the issue crop up in my mind.
As far as the commercial arrangements are concerned, you are on my hit list. Whenever I will start my own company, I will appoint you as my IT Head, provided , Off Course, if you agree then.

Regards
Kanwaljit

 


Comment by: Bob (6/1/2007 12:09:07 PM)

Great tool .... I've helped many people rid themselves of errant links hiding in range names, and "invalid" range names with this tool. Thanks!

 


Comment by: Xie from China (6/2/2007 8:31:02 PM)

Great tool I need it.

 


Comment by: Daniel Nunn (6/5/2007 6:54:12 AM)

I have Excel 97, and was not able to set up the add-in. After I manually add the add-in, then click OK, I get, "Can't find project or library". Is there a simple fix for this error?

 


Comment by: Jan Karel Pieterse (6/5/2007 7:32:32 AM)

Hi Daniel,

Might be best if you download the older 3.2 version or maybe even the 2.3 version.

See "Old Versions" near the top of this page.

 


Comment by: Daniel Nunn (6/6/2007 1:48:59 PM)

Thanks for the feedback Jan. Version 3.2 worked like a charm!

 


Comment by: DHMHTRHS TSIOUSTAS (6/13/2007 11:19:56 AM)

congratulations, thank you

 


Comment by: Sam Benson (7/8/2007 5:45:36 PM)

Hello

Something on an error in NM 4.0 build 586.

Some of my defined names have double quotes around them when I am adding them with NM. This is causing an error in Excel 2003.

Regards

 


Comment by: Stephanie Edelmann (7/10/2007 8:39:56 AM)

This program is amazing! I ran into problems with "compatibility issues" with Excel 2007 but could not figure out what erroneous "defined names" excel was having issues with. This has been bugging me for weeks! Your program (version 3.2; I could not get 4.0 to run) fixed the problem in 10 seconds flat.

Thank you so much,
Stephanie

 


Comment by: Sam Benson (7/24/2007 8:47:51 PM)

Many many thanks for this utility. It certainly makes managing lot of named ranges so much easier.

 


Comment by: Charith Perera (7/28/2007 3:40:21 AM)

Great tool, extremely helpful :)

 


Comment by: JF Bieber (7/31/2007 8:09:10 AM)

Great - save time and easy to use

 


Comment by: Tony (8/3/2007 11:28:57 PM)

Great Tool. Now I can easily manage the names in a spreadhsset that I didnt create but want to make some changes to it. Your tool allowes me to easyl see what the name definitions are saving me heaps of time.

 


Comment by: D.J.Nyambo (8/7/2007 9:26:04 AM)

Great tool, A must have in the office.

 


Comment by: Bruce (8/7/2007 5:43:48 PM)

Brilliant. Can't recommend this enough. Anyone developing sophisticated excel apps simply can't look past this labour saver. And if it doesn't save you time and headaches, your not using names as much as you should!

 


Comment by: Darren (9/12/2007 1:33:42 AM)

I'm trying to get this to work in Excel 2007, but when I click the Name Manager button, the form pops up and immediately closes down again.

 


Comment by: Jan Karel Pieterse (9/12/2007 1:44:54 AM)

Hi Darren,

Did you download the 2007 version?

 


Comment by: Darren (9/12/2007 9:22:44 AM)

Yes. I've just uninstalled it, redownloaded it, and tried to install it again with the same result.
If there are no names in the workbook, it pups up the "no names in workbook" dialog, then after clicking OK, the form appears and vanishes.
If there are names, the form just appears and vanishes.

 


Comment by: Jan Karel Pieterse (9/12/2007 9:39:22 AM)

Hi Darren,

Hmm. Did you use the button in "Setup Name Manager.xls" to do the uninstall?
If not, please try that before installing again.
Otherwise, I have no clue at the moment what might cause that. Works fine on my Excel 2007.

 


Comment by: Darren (9/12/2007 9:54:28 AM)

That file doesn't exist NameManager2007.zip file at the top of this page.

 


Comment by: Jan Karel Pieterse (9/12/2007 10:02:49 AM)

You're correct of course. Please download the Excel 97-2003 version and use that one.

 


Comment by: Darren (9/12/2007 10:16:47 AM)

Ok, i've tried that and I see the same behaviour.
Obviously the name manager doesn't appear in the Formulas Tab anymore, and when i tried to run it from the Add-ins tab - whoosh, the form appears and is gone.

 


Comment by: Darren (9/12/2007 10:32:32 AM)

Oops - I misunderstood your last message. I used the excel97-2000/setup name manager.xls to uninstall Name Manager, and now that I've added it again, it's working.
Thanks for your help.

 


Comment by: Jan Karel Pieterse (9/12/2007 11:48:00 AM)

Excellent!

 


Comment by: Matthew Pfluger (9/14/2007 7:39:06 AM)

This utility has proven invaluable to me as an engineer. Formulae are much easier to debug when using named ranges (or variables). TIP: I have also found that using the Insert-->Symbol command and a simple Copy & Paste, Greek characters (or any other symbol) may be used as named ranges.

I would like to caution Name Manager users on one flaw I have seen. Though the add-in can rename named ranges and replace most cell and VBA references, I found that it doesn't replace references to named ranges in PivotTables or PivotCharts. This caused one of my reports to crash recently.

Overall, though, a fantastic utility. Please pardon this comment's length.

 


Comment by: Jan Karel Pieterse (9/14/2007 10:11:50 AM)

Hi Matthew,

Thanks for the compliments!
I did strive to do Pivot tables though, could you send me a sample workbook and some instructions?

Thanks,
Jan Karel

 


Comment by: Matthew Pfluger (9/18/2007 10:41:06 AM)

I have discovered another bug. I am encountering a "Run-time Error '13'. Type mismatch" while attempting to rename any named range. At first I thought it was my macros, but after removing all code, the problem persisted. I unhid everything in the workbook, but the problem persisted. Only after I removed the Chart Sheets did the Rename Utility work as expected. I can send you the file I'm working on so you can verify the error.

Thanks also for posting an update!

 


Comment by: Jan Karel Pieterse (9/19/2007 7:15:20 AM)

Hi Matthew,

Bug has been fixed in the current download (build number unchanged).

 


Comment by: Matthew Pfluger (9/19/2007 10:31:46 AM)

Thank you very much for your swift responses and bug fixes. What a great add-in! It should probably be included with Excel.

 


Comment by: April (9/28/2007 2:46:44 PM)

Is it correct that you can only resolve the 'name' errors on the current worksheet? Is there any feature in the tool to prevent it from reoccuring on future/all worksheets with this problem?

 


Comment by: Jan Karel Pieterse (9/30/2007 10:30:28 AM)

Hi April,

Not sure what you mean? #Name errors occur when you:
- delete a range name which is used in a formula
- mistype a function (or range-)name.

 


Comment by: Adam Slim (10/2/2007 8:39:52 AM)

This is a great app that I use on a regular basis - thanks! There is one function that would be really useful to add: an 'Unapply' feature. This would replace the range name with its reference; aspects to watch out for include:
- local vs global ranges
- where Excel is looking up the row/column on a linear range
- dynamic ranges

It would be greatly appreciated by many in my team if there were a simple unapply function that took only the easy cases (global non-dynamic named ranges, either single cell or linear) and replaced them with cell references.

I've had a look for formula parsers and think that this could be done that way, but I think you're 98% of the way there already (especially considering your replace names macro). Any chance? :)

 


Comment by: Jan Karel Pieterse (10/2/2007 9:41:07 AM)

Hi Adam,

I have thought about this option. No time now, but I'll put it on the list, just as an Apply names function might be useful.

 


Comment by: Felix (10/8/2007 6:32:34 AM)

Is there a way to disable the short-cut key? I have macros defined for almost all CTRL-SHIFT-combinations and the add-in interferes with them.

 


Comment by: Jan Karel Pieterse (10/9/2007 6:48:46 AM)

Hi Felix,

I am sorry, not at the moment. You can change the key though. Check the menu: Tools, Name Manager, Change Shortcut key.

 


Comment by: JM Beauchemin (10/25/2007 7:25:40 PM)

Super! specially for managing dynamic range and finding error range name
It will we also useful to help duplicate name in Excel 2007

 


Comment by: Joshuah Nelson (10/30/2007 12:46:57 PM)

What a great program! Question: On some of my workbooks after using Name Manager to identify and delete all ranges with errors, the workbook thinks it has been updated everytime I open it now and prompts me to save the file, even though I have not made any changes. Any idea as to what might be causing this?

 


Comment by: Jan Karel Pieterse (10/31/2007 4:48:05 AM)

Hi Joshuah,

I'd be most surprised if that is caused by Name Manager. It is more likely that your workbook contains volatile functions, like NOW() and TODAY().

 


Comment by: Terry Rust (11/2/2007 1:23:28 PM)

Great utility - has saved me a lot of time debugging spreadsheets for users.

Thanks very much !

 


Comment by: Kanwaljit Singh Dhunna (11/14/2007 8:04:32 PM)

Hi Jan,

Doing wonderful job. Following comments are not relevant to the utility of the program, but useful for a perfectionist like you.
1. The main split screen of Name Manager (containing names) doesn't support mouse scrolling.
2. The Scrollbar in Name Scope split screen tends to suggest that there is another item, if we scroll down but there isn't any. (Either the scroll bar shouldn't be there or it should fill the entire space)
3.Name Type Split Screen contains one empty space at the end after "Refer to Active Sheet" item. Is that intentional ?
4. Similar to 3, the main Split screen contains one empty entry at the end, though we couldn't select it. Is that intentional too ?
5. Would love to see the tool tip appearing when we hover over the individual items in the "Name Scope", "Name Type(s)" and last unnamed split screen in Name Manager interface.
6. Lastly, I would be delighted if the width of this Comments box is widened to atleast double the present width.

Please don't mind if you find any of above unnecessary. I am a perfection fanatic type of person, hence the feedback.

Regards
CA Kanwaljit Singh Dhunna

 


Comment by: Jan Karel Pieterse (11/15/2007 2:19:06 AM)

Hi Kanwaljti,

You wrote:
> Doing wonderful job. Following comments are not relevant to the utility of the program, but useful for a perfectionist like you.
> 1. The main split screen of Name Manager (containing names) doesn't support mouse scrolling.

I know and it is a terrible pain to implement that too, which is why it isn't supported.

> 2. The Scrollbar in Name Scope split screen tends to suggest that there is another item, if we scroll down but there isn't any. (Either the scroll bar shouldn't be there or it should fill the entire space)
> 3.Name Type Split Screen contains one empty space at the end after "Refer to Active Sheet" item. Is that intentional ?
> 4. Similar to 3, the main Split screen contains one empty entry at the end, though we couldn't select it. Is that intentional too ?

These three all all related to a bug in VBA userforms, that the last item of a listbox is only partly shown if you set a certain property
(which I need set).

> 5. Would love to see the tool tip appearing when we hover over the individual items in the "Name Scope", "Name Type(s)" and
> last unnamed split screen in Name Manager interface.

Unfortunately, tooltips cannot differ for entries in a normal listbox control.

> 6. Lastly, I would be delighted if the width of this Comments box is widened to atleast double the present width.

Point taken.

 


Comment by: Berci (11/15/2007 9:36:41 AM)

Great job guys! You made my life so much easier.
I am missing only one thing: the ability to manage (at least renaming, listing) names of chart objects that I gave to the charts... (not like 'Chart %')
Do you think such functionality could fit into your current approach?
Thanks!
B

 


Comment by: Jan Karel Pieterse (11/16/2007 2:43:46 AM)

Hi Berci,

I can see where you are coming from, but that is outside the scope of this tool. It is only about range names and aimed just at that.

 


Comment by: Matthew Pfluger (12/4/2007 9:09:27 AM)

I'm frustrated that the VB Editor doesn't keep track of named ranges the way it keeps track of Forms Controls. I know I keep dumping suggestions on you, but I believe that this is a common issue that falls well within the scope of NM. I wonder how difficult this would be to implement. Could code be reused?

Matthew Pfluger

 


Comment by: Jan Karel Pieterse (12/5/2007 2:51:50 AM)

Hi Matthew,

I am not sure what you mean. As far as I know the VBE doesn't care much about the forms controls either, at least any code pertaining to them does not change if anything is changed to the control?

 


Comment by: Matthew Pfluger (12/5/2007 7:13:04 AM)

I captured a set of screenshots that should describe what I was talking about., and I will send them to your general email. I'd like an easy way to insert references to named ranges in the VBE without having to switch back to the Excel UI.

 


Comment by: Jan Karel Pieterse (12/5/2007 7:15:30 AM)

Ah, I suspect you'd like to be able to pick from a list of available range names whilst developing VBA, right?

 


Comment by: Mostafa Rageh (12/10/2007 12:56:43 AM)

very good

 


Comment by: Amit Sethia (12/14/2007 11:56:56 AM)

Awesome tool. It is helping me a lot so far. Would say, a simple thought but worth a lot more than what we can imagine.Thanks

 


Comment by: kanwaljit Singh Dhunna (12/15/2007 8:08:45 AM)

Hi Jan,

Nice Improvement done. It is handy to enter names in VB now. But is it necessary to show all the names in the List. I feel listing only the "Global" and "Local" names would be sufficient instead of all names including "Excel names like "print area / print title / filter area."

Also is it possible to highlight only the selected name instead of all the names in the workbook.

Regards
CA Kanwaljit Singh Dhunna
India.

 


Comment by: Matthew Pfluger (12/23/2007 10:06:41 AM)

I agree that a search functionality for inserting named ranges into the VBE would be helpful, but not in all projects. I think both methods of insertion are appropriate; the list for small workbooks, and a search pop-up, similar to the Excel interface userform, for larger, complex projects.

All in all, not a bad first iteration, especially since he turned it out pretty much in less than a day!

 


Comment by: Jan Karel Pieterse (12/24/2007 4:26:48 AM)

Hi Matthew,

I was thinking on including some sort of filter, so you can -say- only pick from the names starting with "VBA_" or similar.

 


Comment by: Kanwaljit (12/26/2007 6:18:40 PM)

Hi Jan,

Using the latest version of FastExcel. Appear like a bug. I maximized the NM window and then used the EvalPar utility. The result was #Ref (as it should have been), but on clicking "ok", the NM screen disappeared with a message that "Form can't be resized"

Regards
CA Kanwaljit Singh Dhunna
India

 


Comment by: Fayez (12/27/2007 7:53:17 AM)

really,
I appreciated so much and hope you always to be the best

 


Comment by: Spiros (12/28/2007 3:05:03 AM)

Hi Jan,

I have been using Names Manager for years. An excellent utility and has saved me a lot of valuable time. I have recently downloaded Version 4.1 but cannot seem to get the toolbar in the VBE to list anything. The toolbar appears but thats about it. I have tried everything. I am using Xl 2000 but have also tried it on XL 2003. Your assistance would be appreciated.

Regards/Spiros

 


Comment by: Jan Karel Pieterse (12/28/2007 4:11:42 AM)

Hi Spiros,

Odd, works fine for me. Are you sure you have a workbook with range names in it open?

 


Comment by: Scott Clune (12/30/2007 6:04:53 PM)

Hi,
Just downloaded your addin as it looks great but cannot get it working. I have installed the addin in excel but when I start excel i get an error message saying Compile error in hidden module:fxlNameManager. and it stops there.

Any help would be greatly appreciated.
I am using Office 2003.

Cheers

 


Comment by: Jan Karel Pieterse (1/1/2008 4:51:41 AM)

Hi Scott,

Since I get more of these lately, I am planning to try and figure out a way to resolve them.
Please don't hold your breath though, it may take a while.

 


Comment by: Owen (1/2/2008 3:33:35 PM)

I have trouble with the "Compile error in hidden module:fxlNameManager" also.

On a TS machine, It works under the administrator account, but not a regular user account. :(

 


Comment by: Jan Karel Pieterse (1/3/2008 3:48:03 AM)

Hi Owen,

Could you try version 3.2 (see above)?
If that generates an error too, I suspect a problem with your Office setup. Try running Setup, Detect and repair if so.

 


Comment by: Owen (1/3/2008 7:47:06 AM)

Hi Jan,
V3.2 does the same thing.
I think there must be some sort of security/permissions issue. If I figure out what it is I'll post the information here.

 


Comment by: Jan Karel Pieterse (1/3/2008 8:06:30 AM)

Hi Owen,

What Office version are you using?
NB: The fact that it runs on admin and gives a compile error on same machine for another user indicates a faulty installation/profile (I think).

 


Comment by: Ralph (1/11/2008 2:45:28 AM)

The Name Manager utility used to work properly for months, but for some reason it now generates a compile error in a hidden module and is not working anymore... I checked the references, but nothing is missing. Probably the only change is the addition of an Exact add-in and ofcourse some updates from Microsoft itself.

Any idea about this problem? I also tried the older version, but without success!

 


Comment by: Kirk Reed (1/11/2008 8:19:31 AM)

I found your tool because I did not know how to delete named cells. I would try renaming the name, but did not realize it just added new ones. Your utility works great for what I need, but I would like to suggest a feature. How about sorting by "Refers to" column so if duplicate names are used for the same "Refers to" contents, then duplicates could be found easier (so I can delete duplicates...). Thanks!

 


Comment by: Jan Karel Pieterse (1/11/2008 11:50:54 AM)

Hi Kirk,

It already does that, click the small arrows above the names list to sort.

 


Comment by: David Peters (1/15/2008 7:47:32 AM)

I find your tool very useful for a recurring problem we have with embedded names and being able to delete them out to reduce file size.

Is there a quick way to rename invalid names or delete them without having to change the names?

 


Comment by: Jan Karel Pieterse (1/15/2008 8:30:56 AM)

Hi David,

I have a tool for that purpose but it is not for free.
Let me know if you're interested and we can discuss pricing and such.

Also, see <a target="_blank" href="http://www.jkp-ads.com/ProductsRemediation.asp">this page</a> for information on another tool which might be of interest and includes the "corrupt" range name removal option.

 


Comment by: Keng Jin (1/31/2008 12:48:07 AM)

This add in is fantastic. Thanks and appreciate your hard work.

 


Comment by: Ann (1/31/2008 1:34:30 PM)

This is a wonderful utility. We use up to 400 nested names at a time to streamline validation lists, formulas and look up tools. However, have encountered huge performance problems, e.g. up to 6 hours to delete or insert a worksheet with no active names. Is there a limit on nested or embedded names? Is there some other explanation for the extreme performance degradation in Excel? Thanks, Ann

 


Comment by: Jan Karel Pieterse (2/3/2008 10:15:39 PM)

Hi Anne,

No, not as far as I know. I've seen files with as much as 6000 names work properly. But it depends heavily on what your range names contain. If they're all calculation intensive, then you might be up for delays.

 


Comment by: Matthew Pfluger (2/8/2008 10:19:50 AM)

I found a slight issue. I created a name while in A1 notation, switched to R1C1 notation, and tried to rename the name. The Insert Name dialog box popped up and complained that the A1-style address I originally used in the RefersTo of the name was no longer valid.

Just letting you know in case you want to address the issue. Thanks!

Matthew Pfluger

 


Comment by: kanwaljit Singh Dhunna (2/14/2008 3:59:29 AM)

Hi Jan,

Whether renaming a name will change it everywhere in the current version including the validation objects.

Regards
CA Kanwaljit Singh Dhunna

 


Comment by: Jan Karel Pieterse (2/14/2008 9:23:00 AM)

Hi Kanwaljit,

It should do that, yes. Might depend a bit on your Excel language though, some objects have a bug that may prevent NM to work on those in other languages than English.

 


Comment by: DAN (2/15/2008 2:26:03 PM)

I get a visual basic errror when i try to install:

system error &s80004005 (-2147467259). unspecified error


And then it gives me a compile error.
Any ideas? (running 2007)

 


Comment by: Jan karel Pieterse (2/17/2008 4:02:09 AM)

Hi Dan,

Find this folder on your system:

C:\Documents and Settings\[your Username]\Application Data\Microsoft\Forms

And remove all .EXD files from it. Do the same for your temp folder.

Then retry.

 


Comment by: Deb (2/21/2008 11:30:38 AM)

Suddenly when I try to open up the name manager I get a message box that says "text" and when I click the OK button I get the vba runtime error 9 "subscript out of range". My only options there are end and help - no debug.

 


Comment by: Dave Barton (2/22/2008 8:26:16 AM)

Thank you very much for this utility, I have a sheet with hundreds of names and your utility is by far the best to handle these. However, with the installation of the recent version I am getting an error whenever I startup Excel (v 2003) it says: "Compile Error in hidden module Cmenuhandler". It is clearly linked with Name Manager.xla because when I remove it, the error message goes away. Also, the new VBE feature that is supposed to list names in VBE is not present. Even with this startup error message Name Manager is working fine. I am wondering if the error message is linked to the new feature in VBE. Can you make a version that makes the installation of this feature optional? Thanks, Dave

 


Comment by: Jan karel Pieterse (2/23/2008 12:01:02 PM)

Hi Dave,

The new feature requires you to set "Allow Access to visual basic project" in Tools, macro, security, trusted sources tab. But even if that option is not checked, it should NOT give you an error.

Check out the advice on removing EXD files above.

 


Comment by: Dave B (2/23/2008 7:35:49 PM)

The "Allow Access to Visual basic project" was already checked.

I had already tried the suggestion of removing the EXD files that was posted in a previous comment and it didn't help.
Would it be worth trying to install version 4.0 instead? Is there a way to get the URL to the previous versions?

 


Comment by: Jan karel Pieterse (2/24/2008 6:22:25 AM)

Hi Dave,

OK, didn't realise that. What happens if you log on as a different user?

(there is no build 4 to download I'm afraid, but you can dl version 3.2)

 


Comment by: Matthew Pfluger (2/26/2008 3:00:12 PM)

When does the VBE Insert Names toolbar refresh its list? I'm having issues where it doesn't have the most up-to-date listing of names?

Also, I'm afraid that I'm going to have to request that an interface through the VBE similar to the Excel interface be included soon. It's quite difficult to weed through the many names through a drop down box.

Thanks again for a great tool! I use it daily.

 


Comment by: Jan karel Pieterse (2/26/2008 11:17:14 PM)

Hi Matthew,

It refreshes with switching of workbooks. Might be nice to have a refresh button though.

 


Comment by: THughes (3/5/2008 2:51:40 PM)

Just downloaded and installed the name manager - version 4.1 build 596.

All works well in Excel however I am un able to use or find for that matter the "niffty" feature added to the VBE - the insert name drop down tool bar. That would be most usefull. Is there some action within VBE that must be taken to make it work? or is there some place to select and enable it?

Many Thanks for your hard work,

Tom

 


Comment by: Jan karel Pieterse (3/6/2008 9:00:58 AM)

Hi Tom,

I think you need to set "Allow access to Visual basic project" in Tools, Macro, security, trusted sourcs tab.

 


Comment by: Dave B (3/11/2008 11:44:32 AM)

Jan,
Sorry it took so long to get back to you, but I did try to install under another user and it had the same issue. I don't know if this helps, but I have seen issues with several programs installing on my machine due to the Windows File Encryption applied to the User Folders in our Corporate environment. Usually, this is an issue with programs that attempt to authenticate the file that is being installed because the encrpytion alters the size or signature slightly and when the installer compares it to what is expected it doesn't match.

 


Comment by: Matthew (3/14/2008 7:14:52 AM)


When searching for unused named ranges, would it be possible to enable searching of INDIRECT, conditional formulae, etc. with the understanding
that the process will take a long time? Or has this process not been included due to its complexity?

 


Comment by: Jan karel Pieterse (3/14/2008 7:15:42 AM)

Hi Matthew,

Of course we could. We just haven't got round to it, that's all.

Thanks.

 


Comment by: Ng C C (3/22/2008 9:50:45 PM)

Hi,

Judging from what I read in this webpage, I certainly believe that this is a great tool.

I have downloaded it and add to my excel 2003 but hit an error Massage as follows:-

"Compile Error in Hidden Module: fx|NameManager."

Kindly advise what is the cause of it and how I can have it fixed.

Thanks
Ng C C

 


Comment by: Jan karel Pieterse (3/24/2008 7:42:54 AM)

Hi NG,

Try cleaning out your temp folder and removing all .exd files from this folder:

C:\Documents and Settings\[your Username]\Application Data\Microsoft\Forms

 


Comment by: Dave Mackmiller (4/1/2008 8:18:16 AM)

Jan,

Thanks for the useful tool. I've been using it for a couple of years now. Funny, I also wrote a utility similar to your "Highlight" function, and never noticed that you had one too. The only difference, which I prefer, is that the name of the range is displayed in the upper right hand corner of the text box in 10 point Arial bold. This means that I don't have to scroll around and/or zoom to find the name. Most of my ranges are of different sizes, so this makes it easier to work with.

Also, I only highlight ranges on the active sheet, for speed purposes.

Another thing I found handy is a "Highlight for 5 seconds" command. I'm usually in the area where I know the range is that I'm looking for, so I only need to be reminded briefly (it also closes the utility userform). I just use application.ontime to call my sub for clearing highlights.

Finally, it wasn't too hard to reuse the textbox code to highlight cells that are unlocked. I know it's outside of the scope of NameManager, but it's very useful if you have to protect your worksheets. The trick there is to combine blocks of unlocked cells into a single textbox so you don't potentially wind up with a zillion textboxes.

Anyhow, thanks again for a great tool.

Dave

 


Comment by: Headtoadie (4/2/2008 8:23:54 AM)

On the "Filter names containing" text box, could you make it so there is an option to exclude items. I usually don't want to see print areas and there is no simple way to not display them in the range name list.

 


Comment by: Jan Karel Pieterse (4/3/2008 1:05:13 AM)

You can do that by unchecking the "Show system names box in the tiny settings list.
But the "Exclude names" filter IS a good idea!

 


Comment by: Jan Karel Pieterse (4/3/2008 1:06:15 AM)

Hi Dave,

Good point on the position of the text.

 


Comment by: kp (4/22/2008 6:57:08 PM)

Thank you so much for sharing the fruit of your hard work to others.

I've just road tested it and found that this is an excellent tool. Thanks again.

Regards


kp

 


Comment by: kanwaljit Singh Dhunna (4/29/2008 5:33:37 AM)

Hi Jan,

Do mention the changes made in the new version by default. That would be useful I feel.

Regards
CA Kanwaljit Singh Dhunna

 


Comment by: David (4/30/2008 1:41:23 PM)

I'm not getting the tool bar in the VBE, can you tell me how to load it (using 2003)?

Thanks!

David

 


Comment by: Roemer (5/13/2008 5:17:17 PM)

Hi there.
Perfect, I've been using this utility for more than a year and am very satisfied.
However, I cannot find the "tiny but extremely handy toolbar to the VBE" in the
newest version. Office 2007.

 


Comment by: Jan Karel Pieterse (5/14/2008 2:05:19 AM)

Hi Roemer,

It shows up in the Visual Basic Editor if you have the option "Allow Access To
Visual Basic project" checked in Excel's security center.

 


Comment by: Josh (6/4/2008 6:50:57 AM)

Thank you for this utility. It has saved my life at work enabling me to manage hundreds of trend models that
change dynamically off of named ranges that utilize indexes and other programming. Great job!

 


Comment by: Alan Bradshaw (6/15/2008 3:23:15 AM)

Jan Karel,
Firstly thank you for a wonderful programme, I use Virgin Media PCGuard and it
lists Name Manager 2007.xlam as a possible virus, so I have to keep reloading it. I
would be interested to know if anyone else experiences this.

Regards

Braddy

 


Comment by: Jan Karel Pieterse (6/15/2008 5:11:24 AM)

Hi Braddy,

I guess you should try to set an exception to name manager in your virus software,
as NM contains no virusses.

 


Comment by: Anton de la R (6/19/2008 1:19:33 AM)

Hi Jan-Karel,

Many thankx for this great tool!

Kind Regard/Met Vriendelijke Groet
Anton

 


Comment by: George (6/23/2008 1:46:05 PM)

This is a GREAT tool!

 


Comment by: Kanwaljit Singh Dhunna (7/4/2008 6:14:04 AM)

Hi Jan,

Recently I tried to rename a Name and by Mistake I used some invalid character ("-"
Hyphen) in the renamed name. Immediately NM gave me message that the name contains
invalid character. But the cursor icon changed its shape and took the form of the
icon which the system shows while processing. It retained that processing mode shape
even when the file was closed and I had to restart Excel. Any guess what happened ?

Regards
CA Kanwaljit Singh Dhunna
India

 


Comment by: Liz Tomlin (7/14/2008 3:20:12 AM)

Dear Kan Karel,

I love this tool it makes such a difference. I have one area that is a problem: If
I have names that hold long formulae, by the time excel has added the sheet
reference and made them even longer I can't ever see the whole formula - in Excel
Names it's about 232 character in your tool it's a few more but I seem to have lost
some commas towards the end:

e.g. =IF('Problems & Issues'!IH6="Y",IF('Problems & Issues'!IK6="Y",(10-'Problems &
Issues'!II6)*'Problems & Issues'!IJ6,(10-'Problems & Issues'!II6)*'Problems &
Issues'!IJ6*0.05),0)0'Problems & Issues'!IL6="Y" 'Problems & Issues'!IO6="Y" 10

Usually I have learnt to copy my original formula once I am sure of it as a text
field on a sheet that holds such things as this and refernece data. This means that
if I want to adjust things I can easily test changes by copying the literal to an
approprate cell, make any changes then copy to the name.

However we all fail in out good intentions sometimes so is there any way you toool
can or could display the complete formula? (or can I get it another way

Thank you,

Liz

p.s. I will ask the question on WOPR as well (about getting it out of excel

 


Comment by: bricol (7/14/2008 4:48:19 AM)

Great Tool which I've been using for a few years (& excel versions!)Having only just
upgraded to 2007, i see they are attempting to emulate this tool, but not as functional!
QUERY/SUGGESTION: As an ex quattro user, the default "ABSOLUTE RANGE NAMES" format of
excel is very frustrating. Would an option to "bulk" convert selected names to
relative references be feasible? (would like to be able to copy formula's containing
names referring to a table and have the references adjust)
Many Thx again!
bricol

 


Comment by: Ben (7/16/2008 11:17:35 AM)

Jan Karel,

The name manager is one of the greatest tools I ever used! After struggling with
the names that I could not delete using other ways for more than an hour, I find
your website. The tool is wonderful, as well as the guidence along the way! When I
failed to delete the last invalid names, the direction you gave led me to change
the option settings and I eventually solved the problem.

Many thanks!

 


Comment by: Doug Glancy (7/18/2008 5:54:53 PM)

I use this tool more and more. I'm sure VBE toolbar will be useful.
One small bug I noticed is with using the "edit the refers to..." box. The rename
takes over the contents of the clipboard, so if a had text on the clipboard it is
overwritten by the new name. This was especially confusing the first time, as I was
using text I had copied to paste into several names, and they kept getting longer ...
Thanks again for a great tool!

 


Comment by: Jan Karel Pieterse (8/6/2008 10:56:22 AM)

Hi Doug,

The clipboard thing is unfortunately "by design", as NM uses the clipboard and
SENDKEYS (yes really!) to work around a couple of bugs in Excel VBA when defining
range names.

 


Comment by: Jan Karel Pieterse (8/6/2008 10:58:51 AM)

Hi Bricol,

You can bulkchange names by using the list option, doing a S&R in the resulting
worksheet and afterwards using the pick-up button. A bit of a workaround, but it
works I expect. Not sure if making them relative will work though, as the result
depends on the active cell.

 


Comment by: Jan Karel Pieterse (8/6/2008 11:01:09 AM)

Hi Liz,

Currently NM indeed limits the number of characters in it's refersto box, because
otherwise you may get into trouble creating useless names.

One way to quickly get at your intricate formula is by temporarily renaming your
worksheet tom something very short.

 


Comment by: Austin Eaves (8/7/2008 12:28:26 PM)

Howdy,

I recently downloaded the name manager and have begun using named ranges. How do I
reference named ranges in other workbooks? Our goal is to keep a master set of
named ranges that multiple people are able to utilize without having to open the
master workbook.

Thanks,
Austin

 


Comment by: Jan Karel Pieterse (8/7/2008 9:16:22 PM)

Hi Austin,

The syntax is: (first open the source workbook):

='Workbook Name.xls'!RangeName

 


Comment by: Austin Eaves (8/8/2008 7:03:17 AM)

Jan,

Fantastic. Thank you for your help.

Our thought process is to have the master list open automatically and hide when
excel is started (XLSTART). Is there a better way to accomplish this?

Austin

 


Comment by: Jan Karel Pieterse (8/8/2008 7:43:44 AM)

Hi Austin,

I see nothing wrong with that.

 


Comment by: Andy (8/18/2008 7:30:37 AM)

Hi, NameManager is a great utility, many thanks!

I do have one little issue. I have a defined name that is used in a formula in a
conditional format. When I rename the name, the search and replace dialogue seems
to find where the name is used, but then it doesn't adjust the conditional format to
use the new name.

I'm using Excel 2002 (XP).

thanks, Andy

 


Comment by: Jan Karel Pieterse (8/18/2008 7:49:21 AM)

Hi Andy,

Odd, it works for me. What formula is defined in your conditional format?
Could you perhaps send a stripped down version of the file?

 


Comment by: John Williams (8/19/2008 10:16:01 PM)

Pretty nice site, wants to see much more on it! :)

 


Comment by: SivaN (8/28/2008 7:10:49 PM)

Great tool. Thanks.

I have hundred cells that I need to set names for (Name1, Name2, Name100). Is there
a way I can multicreate named cells other than by creating them one by one?
I cannot use NameRange for cells A1 to A100 because of a limitation from another
application.

thanks.

 


Comment by: Jan Karel Pieterse (8/29/2008 2:26:54 AM)

Hi SivaN,

Check the manual and see what you can do with the List and Pick up buttons.

 


Comment by: Square Peg (9/1/2008 1:40:17 AM)

I clicked on the reating stars thinking it would take me to the ratings. That;s how
it works on Amazon. Instead, this one actually submits a rating. I happened to click
on star #4, go it got a 4 rating, which I did not intend.

This is poor human factors. At the very least, it should confirm that a rating is
being submitted.

 


Comment by: Tak (9/5/2008 1:04:32 PM)

Wonderful Tool......MANY thanks! I am using it primarily with the "Renaming many
range names" macro from the manual and it works great. Is there a way that I can
accomplish the same thing without having to manually re-associate all my hyperlinks
with the new range names afterward?

 


Comment by: Jan Karel Pieterse (9/8/2008 12:50:22 AM)

Hi Tak,

It should do the hyperlinks, but maybe you found a bug! Alternatively, if you use
the HYPERLINK worksheet function instead of fixed hyperlinks, your problem should be
solved too.

 


Comment by: Ken (9/11/2008 5:20:10 AM)

Great add in! Saved my bacon!

 


Comment by: Joe (9/24/2008 9:24:04 PM)

When I moved from Excel 2003 to Excel 2007, I thought the Microsoft Excel Name
Manager was a great step forward, but it pales in comparison to this utility. Thanks
for sharing this very impressive and effective module.

 


Comment by: Rory (9/26/2008 2:30:02 AM)

Hi Jan Karel,
Does the add-in identify errors in names by lookin gfor the # symbol? I ask because
I have some names that use the TEXT function to concatenate numbers in #,##0 format
and they are always flagged as containing errors!

 


Comment by: Jan Karel Pieterse (9/26/2008 4:58:59 AM)

Hi Rory,

Yes indeed you are right. I know this is not exactly the right way to discern error
names. Maybe when we next update NM...

 


Comment by: Jez Hancock (10/5/2008 3:59:07 PM)

Hi,

First up thanks for the tool, didn't manage to quite do what I was after. Can you
tell me is it possible to have 'global' names that I create in the personal.xlbs
workbook which are available to all open workbooks and if so how would I go about
doing it? I've searched long and hard and not found a great deal (with the exception
of 'hidden' *application* (see here: http://www.cpearson.com/excel/hidden.htm) which
sounds like overkill?).


Anyway the other reason for posting this comment - after uninstalling name manager in
Excel 2007 I still have an 'add-ins' menu on the ribbon with a 'Name Manager' drop
down in it. Is there some way to remove this entry from the 'Add-ins' ribbon item?

Also how do I hide the 'Add-ins' item on the ribbon, I'm sure there's a way but I
can't remember for the life of me or find it in the options!?

Many thanks in advance,

Jez

 


Comment by: Jan Karel Pieterse (10/5/2008 9:13:56 PM)

Hi Jez,

You can only refer to a range name in another book by prepending it with the book's
name:

=Personal.xlsb!TheName

Seems you installed the 2000-2003 version. Rightclick the entry on the addins tab
and select remove (or delete, I forgot which it is). The addins tab will disappear
as soon as no more entries are there.

 


Comment by: Jez Hancock (10/6/2008 5:52:19 AM)

Hi Jan,

Many thanks for your quick reply. I did install the 2007 version (.xlam), apologies
it appears as soon as I restarted Excel the tab was indeed removed.

As for 'global names', that's a shame, yes that's the way I'm doing things at the
moment - referring to the name within the other workbook. I have a currency table
which is updated every day from a remote data source (actually yahoo.com!) in my
personal.xlsb workbook and then a number of names defined in personal.xlsb like:

Name 'EUR2GBP':
=INDEX(Currency!currency, MATCH(" 1 Euro",Currency!$A$1:$A$9,), MATCH("U.K.
£",Currency!$A$1:$P$1,))

etc. I was hoping there was some way of making these names defined within
personal.xlsb accessible to any other open workbook without having to duplicate the
names in each of the workbooks (which I've done ok for a couple of names but it's
hellishly tedious doing this for more than one or two currency conversion names!).

All the best.

 


Comment by: Amit (10/8/2008 1:43:21 AM)

Hi, I have been using Name Changer for quite sometime, but recently I updates my MS
Office with an update named "SP3", after this update am getting an error
message "System Error &H80004005 (-2147467259). Unspecified error" and not able to
use "Name Changer" any more. I am using MS Office 2003. Please help

 


Comment by: Jan Karel Pieterse (10/8/2008 2:28:06 AM)

Hi Amit,

I Assume you mean Name Manager. Open Excel and select Help, Detect and repair.

 


Comment by: Gordon (10/22/2008 5:44:46 AM)

Truly amazing. This tool has allowed my to find over 6000(!) weirdly-named ranges
in one of our workbooks. Needless to say, these unwanted and hitherto unseen ranges
were causing all sorts of problems with slowdown, file bloat etc. that traditional
techniques had failed to identify, much less correct.

Thanks Jan!

 


Comment by: Darryl (10/22/2008 3:57:33 PM)

I have used name manager for years so firstly, thanks. Just wanted to say nice work
once again. Really pleased with the VBE Toolbar to 'addin' names in this version.
Most Excellent. Still think MS Should buy both this and Fast Excel from you and
Charles and incorporate them in the release versions of Excel.

 


Comment by: Jax (10/26/2008 1:40:50 PM)

Excellent add-in! Thanks alot!
It takes much longer time to create variables but once you start doing that you
actually have chance to understand your formulas a few weeks later. Other people will
also have a chance to understand. (It does require that you come up with good names
but that's another story...)
Your Name Manager addin is a great tool for handling names! Excel almost becomes a
new application! It's a shame that Microsoft hasn't included this functionality to
start with.

 


Comment by: Jim (11/4/2008 10:33:53 AM)

I have installed Name manager for Excel 2007 but when it loads it produces an error
dialog (Microsoft Visual Basic) with the following message:
"Object library invalid or contains references to object definitions that could not
be found"
Any ideas what might cause this?
Thanks
Jim

 


Comment by: Jan Karel Pieterse (11/4/2008 11:56:13 AM)

Hi Jim,

This may indicate a problem with your Excel. Please click teh Office button, select
Excel options, choose Resources and click the diagnose button

 


Comment by: Kristin McIntire (11/16/2008 3:09:49 PM)

This program is sweeeet! I copy sheets from one file to another, all the time bringing along lots of junk (unused NAMEs). This allows me to easliy clean them up. THANKS!!!

 


Comment by: Sal Paradise (12/8/2008 7:26:46 PM)

It seems as if any range named with Japanese characters causes problems.

I have a named range "&#12450;temp" (that's a katakana "A" if you can't read it). When I try to delete it, it says:

"This Name: '&#12450;temp' is unuseable."

When I click 'OK' to rename all unuseable names in the workbook, it doesn't rename this (or the other dozen Japanese named ranges I have).

If I try to rename it manually (to 'atemp'), I get this error:
"Compile Error in hidden module: fxlNameManager"

Excel then needs to be restarted (constant hourglass).

I'm guessing you just didn't consider people using Japanese names, but it does put a small dent in an otherwise marvelous piece of software.

 


Comment by: Jan Karel Pieterse (12/8/2008 10:20:34 PM)

Hi Sal,

I'd be most happy to update Name Manager so it'll work with katakana!

What I need is a list of all katakana characters one can use to define a range name, so characters that may not be used (which cause an error) must be omitted from that list.

Could you please send me a workbook that contains the katakana character set, preferrably as a single string in one cell? I don't know how many characters the katakana alphabet contains, if more than 32000, then please use more cells.

 


Comment by: Randy (1/28/2009 5:53:38 AM)

Hi Jan, Great product- I've successfully removed several hundred strange names/names with bad invalid REFs from a workbook. However now when I close the file it crashes excel every time. Note that I can work/save changes in the file and it only crashes upon exiting Excel. Have you heard of this or seen this before?

 


Comment by: Jan Karel Pieterse (1/28/2009 5:54:25 AM)

Hi Randy,

Tried to send you an email, but the address bounced...

Odd, I suspect this may have to do with a problem in your file. Does it contain any VBA code?

 


Comment by: Elan (2/20/2009 5:28:18 AM)

Hi Jan Karel,
This tool is awesome, it saved me a lot of time managing names with this.
Anyway, is it possible to change a cell name partially automatically if, for instance, I copied a worksheet that contain a global cellname? For example, I have a cell named alphaValue in sheet "Alpha" but when I copy the sheet, I want all names containing "Alpha" to be replaced with "Bravo" in the new sheet.
I tried to look for sort of help documentation but I couldn't find it.
Takk.

 


Comment by: Jan Karel Pieterse (2/20/2009 6:12:59 AM)

Hi Elan,

Am I correct in assuming Alpha is the worksheet name and Bravo is the new worksheet's name in this case?
If so, prior to copying the worksheet, make sure you remove the Alpha from the range names and make those range names local to the worksheet Alpha. Then copy the sheet and the new worksheet will also have local range names (with same names).
If you misplaced the manual, just download NM again, it is in the zip file.

 


Comment by: Todd Baumann-Fern (2/23/2009 8:26:54 AM)

Very nice tool! This is a great help on my spreadsheet.

 


Comment by: Werner Prystav (2/28/2009 2:42:57 PM)

Hi JKP,
working with Excel 97 SR2 (German Language Version) and NameManager Build 606, I get the compile error during load of Name Manager: "Kompilierungsfehler in verborgenem Modul: CFormReziser". That means in English: ""Compile Error in hidden module: CFormReziser".
In case of "Compile Error in hidden module, fxlNameManager" you suggest "detect and repair". But there is no "detect and repair" in the Help menu of Excel 97.
Can you help me to run Namemanger 4.1 together with Excel 97 SR2?
Werner

 


Comment by: Jan Karel Pieterse (3/1/2009 11:30:23 AM)

Hi Werner,

Unfortunately, I no longer support Excel 97 with newer releases of Name Manager. However, version 3.2 still works fine with Excel 97 If I recall correctly.

 


Comment by: Werner Prystav (3/2/2009 3:21:15 AM)

Ok, I will use a newer version of Excel .

If you do not longer support Excel 97, it may be a good idea to delete Excel from the list at the Top of this Page, where you have written:
"Current version downloads
Name Manager for Excel 97, 2000, XP and 2003"

Or does Name Manager v4.1 work together with Excel 97 normaly , but not on my installation?

Werner





 


Comment by: Jan Karel Pieterse (3/2/2009 4:46:40 AM)

Hi Werner,

Good suggestion, updated the page.
To answer your question: No, version 4.1 does not work with Excel 97.

 


Comment by: A.M (3/4/2009 5:30:28 AM)

This utility is a very useful - many thanks for developing it.

However, one really useful function would be the ability to pick specific names out of the main Name Manager view and rename in bulk (maybe change them to numbered sequence so they can be sorted easily after).

When using sheets with old Lotus 123 references (which allow spaces in the names, as opposed to Excel) each name has to manually renamed. When there are hundreds of these it takes a long time to do this, when all I really want to do is delete them.

I don't know if it's possible to do this in the current version?

 


Comment by: Jan Karel Pieterse (3/4/2009 5:45:42 AM)

Hi A.M,

You can do this with some VBA code. This is described in detail in the manual which is included with the zip file you downloaded.

 


Comment by: William Griffith (3/10/2009 7:11:54 AM)

Very nice tool!

Is there a way to filter to exclude an item? We have many interconnected spreadsheets and the hidden names spread from past endeavors spread like a virus and greatly increase file sizes. We would like to mass delete names from simple spreadsheets but would like to leave the Print_Area names intact.

Thanks!

 


Comment by: Jan Karel Pieterse (3/10/2009 10:53:40 AM)

Hi William,

YOu can uncheck the "Show Excel system names" box in the little options list bottom-right of the main window and then remove everything.

 


Comment by: Mary Kennedy (4/3/2009 12:54:49 PM)

I know how to print the tab name in a header or footer but how do you put that name in a cell?

 


Comment by: Jan Karel Pieterse (4/6/2009 12:45:09 AM)

Hi,

Search google for "Sheet name in cell". You will find many relevant hits.

 


Comment by: Nelusa (4/6/2009 9:07:10 AM)

Great job,
Now I can adjust my range names easy.

 


Comment by: Mike Cypret (4/23/2009 8:56:05 AM)

I have several spreadsheets with cells that require names, each individual cell must have its own name. I have used the concatenate function to put the desider name in each cell. But I can't find a way to automatically name each cell with the text in it's cell. I know that I can use Alt-Insert, Name, Define, but that takes forever when doing it for over 2500 cells. Any ideas?

 


Comment by: Jan Karel Pieterse (4/23/2009 11:24:28 AM)

Hi Mike,

You can use Insert, Name, Create for that purpose. It is best to put the names either to the immediate left or to the immediate right of the cells you wish to name.

NB: Why name such a huge number of cells?

 


Comment by: Bart (6/3/2009 1:01:34 AM)

Thank you so much! So nice of you to share this!

 


Comment by: Lynda Maynard (6/4/2009 8:43:25 AM)

This thing has been an absolute lifesaver - I love it!

Any chance of being able to rename groups of ranges all at once? Like if you had "Jack_This", "Jack_That" & "Jack_TheOtherThing" and you wanted to reuse everything as "Jill_This", "Jill_That" & "Jill_TheOtherThing" in the next project?

 


Comment by: Lynda Maynard (6/4/2009 9:40:05 AM)

RE: renaming many ranges -- never mind, I found it in the manual... ;^)

 


Comment by: Jan Karel Pieterse (6/4/2009 11:02:42 AM)

Hi Lynda,

Thanks for the compliments and great that you found the answer to your question in the manual!

 


Comment by: Ian Howie (6/5/2009 7:50:07 AM)

Excellent, many thanks!
I had problems with names that were global and local, and this has helped me see what's happening and clear up some of my 330 names!

 


Comment by: Charlie Harris (6/9/2009 12:11:44 AM)

I am using named ranges, but need to extend by rows the range name as new entries are added to the sheet.

I believe you add something in the dropdown for new named range in the box 'Refers to'

Does the Addin for XL97 work for doing this ?

Thanks

 


Comment by: Jan Karel Pieterse (6/9/2009 1:49:17 AM)

Hi Charlie,

What you are looking for is a dynamic range name. Please see my range name article:

http://www.jkp-ads.com/Articles/ExcelNames07.asp

 


Comment by: Mark Daysh (6/16/2009 6:41:54 AM)

Excellent tool.
This error has been bugging me for ages.
Thank you so much.

 


Comment by: Jan Karel Pieterse (6/17/2009 1:43:53 AM)

Hi Mark,

You're most welcome!

 


Comment by: Russ Ford (6/17/2009 12:15:48 PM)

Hi Jan,

I've been using Name Manager for a number of years now and I think it should be included with Excel. It's so handy and is very intuitive. The interface is really robust and well designed. Is all the development done in VBA?

Keep up the awesome work.

Russ

 


Comment by: Jan Karel Pieterse (6/17/2009 9:23:29 PM)

Hi Russ,

Thanks!
It has all been done in VBA indeed.
Part of the ideas of our Name Manager have been adopted by Microsoft when they were designing their Name Manager for Excel 2007. They even copied our product name!

 


Comment by: Guy Verlinden (6/21/2009 3:05:24 AM)

When filtering for the names that are "not used", the searchresult of names that are listed, can be dangerous because names used in data validation are still marked as "not used". Or am I doing something wrong?

Anyway, i'm an enthousiastic fan of this add-in!

 


Comment by: Jan Karel Pieterse (6/21/2009 3:06:35 AM)

Hi Guy,

Thanks for reminding me. Indeed Validation may be an omission from the unused names search.

 


Comment by: Tai (6/26/2009 3:59:35 AM)

This Add-in is very cool. I have been looking for this for awhile. I started with a simple template with a few defined names. Before long it grew into a long list and unmanageable. Anyhow, I had a hard time keeping track of the names. Until now. Thanks.

I notice that you can output the names in a new worksheet. It would be cool, if I can also do the reverse. For example, I can create a list of name in a table and then turn them into define names.

Again, Thanks.

Tai

 


Comment by: Jan Karel Pieterse (6/26/2009 4:04:32 AM)

Hi Tai,

Use the button that is right next to the list button (this button appears as soon as you have created a list sheet).

 


Comment by: Mustafa (7/17/2009 5:33:56 AM)

I will thank you for these usefull lesons in Excel
My Request is if It is Avaialable How to progam in Excel by VBA

Thank You

Sncerly
Mustafa

 


Comment by: Jan Karel Pieterse (7/17/2009 6:16:37 AM)

Hi Mustafa,

I think the best way is to start with a beginner book on Excel VBA. The Excel 2007 VBA for Dummies is a good start, and the Step by step series by Microsoft are good too.

 


Comment by: AJ (7/20/2009 1:57:01 PM)

I want to name a range in one sheet and use the same range with the same name in all sheets. How do I do that?

 


Comment by: Jan Karel Pieterse (7/20/2009 11:06:27 PM)

Hi AJ,

As it is now, you would have to do each sheet in turn.
Good suggestion for an addition to Name Manager though.

 


Comment by: michell (7/21/2009 12:56:02 PM)

refering to the compile error: "This error is mostly resolved by opening Excel and selecting Help, detect and repair from the menu."

The "detect and repair" option does not show up on my help menu. What am I doing wrong that I cannot find it?

 


Comment by: Ron Chayer (7/21/2009 5:39:55 PM)

I loose the link when I send an excel workbook to another computer. when the name is created it saves the location of the file on my computer; i.e. it points to my documents on my computer. these same workbooks are going to be saved on many computers. how do we resolve this. thanks

 


Comment by: Jan Karel Pieterse (7/22/2009 3:57:25 AM)

Hi Michell,

The alternative route is to close Excel, goto control panel, add/remove programs and have Office setup do a repair.

 


Comment by: Jan Karel Pieterse (7/22/2009 4:00:05 AM)

Hi Ron,

Is the file in the same relative position on the different systems? If not, then Excel will show the entire path by default.

 


Comment by: Paula Adkins (7/23/2009 12:29:45 PM)

Excel 2003

Created macro and want to share with others. Is the easiest way to do this by putting the macro in the workbook and sending the workbook to others?

Not sure how creating an add-in work. What is the best way?

Thanks!

 


Comment by: Jan Karel Pieterse (7/24/2009 2:35:38 AM)

Hi Paula,

Cick "Articles" in the links on the left and look for an article series named "Create Addins". You'll find all the information you need there.

 


Comment by: Ed DiTomas (7/29/2009 5:43:55 PM)

Hello Jan,

I'm using Excel 2004 on a Mac with the latest updates of Excel 2004 and Mac OS 10.5.7.

I am curious about the follow comment:

"Comment by: AJ (7/20/2009 1:57:01 PM)
I want to name a range in one sheet and use the same range with the same name in all sheets. How do I do that?
Comment by: Jan Karel Pieterse (7/20/2009 11:06:27 PM)
Hi AJ,

As it is now, you would have to do each sheet in turn.
Good suggestion for an addition to Name Manager though."

Isn't this the same as the use of 3-D referencing as shown in the Excel Help file:

1. On the Insert menu, point to Name, and then click Define.
2. In the Names in workbook box, type the name.
3. If the Refers to box contains a reference, select the equal sign (=) and the reference and press DELETE.
4. In the Refers to box, type = (an equal sign).
5. Click the tab for the first worksheet to be referenced.
6. Hold down SHIFT and click the tab for the last worksheet to be referenced.
7. Select the cell or range of cells to be referenced.
8. Click Add to define more names or OK to close the dialog box.

I have a workbook with 13 worksheets and want to use the same naming conventions for 12 of the sheets - January thru December. I follow the instructions in the Help file but the names do not show up in the Name Box or the Go To dialog. They do show up in the Insert Name dialog the next time I enter it.

If you have any thoughts on my confusion I would most appreciate hearing them.

Regards,
Ed DiTomas

 


Comment by: Ayrton (7/30/2009 6:03:43 AM)

Thanks for developing this tool; I'll try it; it meets exactly my need for deleting a large range of names at once.
Thanks.

 


Comment by: Jan Karel Pieterse (7/30/2009 11:34:21 AM)

Hi Ed,

No that isn't the same. What AJ wants is to define a name ON EACH SHEET that is local to that sheet.

You define a name local to a sheet by prepending the name with the sheetname:

SheetName!RangeName

Or, when there are spaces in the sheetname:

'Sheet Name'!RangeName

Name manager can do this automatically for you.

 


Comment by: John Frey (7/31/2009 12:53:21 AM)

Hi,

I've tried out Name Manger in Excel 2003 and it's pretty good beans :).

Just wondering why ranges used in charts aren't shown in the "Where is name used" function.

 


Comment by: Jan Karel Pieterse (7/31/2009 3:14:38 AM)

Hi John,

This is a known issue, in some occasions it is very hard to extract range names from a charts SERIES formula.

I'm afraid the check for names in use isn't 100% reliable in those cases. Other areas might be validation and conditional formatting.

 


Comment by: Murthy (7/31/2009 2:36:10 PM)

Hello all -

I am trying to define named ranges on 75 columns of data. Is there a quick way to define named ranges? It takes me a while to define named range for each column of data.

E2:CD475 is my data range and the labels are in the first row E1:CD1.

Thanks a lot for your help.

Regards,
murthy

 


Comment by: Jan Karel Pieterse (8/1/2009 3:08:02 AM)

Hi Murthy,

If you are using Excel 2003, try Insert, Name Create.

If on Excel 2007: Formulas tab, "Defined names" group, "Create from Selection" button.

 


Comment by: Conrad Brits (8/12/2009 2:43:02 PM)

Hi Jan,
I use Bastien's ASAP Utilities and would like to install your Name Manager as well. Do you know of any conflicts between the two add-ins?

 


Comment by: Roja (8/17/2009 11:46:52 AM)

This is how my data appears as an example

Name Property
PersonA Land1
PersonA Land2
PersonB Land1
PersonC Land1
PersonC Land2
PersonC Land3


I want to create a Named Range .
I should have only 3 defined names, PersonA, PersonB, PersonC.

When I click on PersonA, it Should Select both the rows of PersonA
PersonA Land1
PersonA Land2
When I click on PersonB, it Should Select only one row for PersonB
PersonB Land1
When I click on PersonC, it Should Select 4 rows for PersonC
PersonC Land1
PersonC Land2
PersonC Land3

I know to do this semi-manually. I was wondering if this software can help me on the above.

 


Comment by: Bill (8/21/2009 8:00:34 AM)

Thought I would give your name manager a try, it doesn't work with Excel 2008 for Mac as VBA is no longer available...

Thanks!

 


Comment by: Peter (8/21/2009 8:01:08 AM)

Hello Jan Karel,

This is a fantastic utility, but nevertheless I have a question.
I use Excel 2003 on windows XP and in my options the reference type is set to R1K1.
Now, if I try to create a name and select cells in my worksheet, the RefersTo window shows the reference in the A1 type. This couses problems when I click "Add" on the "Add names" form.
Am I missing something here or is the R1K1 format not supported?

Best regards,
Peter

 


Comment by: dbrett (8/22/2009 1:37:38 PM)

Does the current build (610) work in Excel Mac 2004?

I tried installing it and get the message:

Compile error in hidden module:
CFormResizer

Saw your replies to people with similar errors in Windows and was wondering if there is a fix for OSX? (Not sure which files in which locations I should trash)

Looks like a great tool and hoping to use it soon!

Thanks.

 


Comment by: Prashant Vijay (8/23/2009 11:14:58 PM)

I'm writing one VBA to code to copy few rows in another file. Both the files have cells with validation criteria as defined name.
It shows the Name Conflict and prompt a message for every cell which has same name. I want to get use the name of destination file without asking . Is it possible.

 


Comment by: Wimpie (8/24/2009 11:47:35 PM)

When trying to open Name Manager for Excel 2000, XP and 2003 v4.1 the following error message appears:
"Cannot open file: it does not appear to be a valid archive. If you downloaded this file, try downloading it again"

downladed 5+ times as well as try running it directly. same message.

Please advise

 


Comment by: David C. (9/2/2009 12:54:29 AM)

How do I "apply names" across sheets?
The method seems to work only for names defined on the sheet..
Thanks a lot for your help,
Regards

 


Comment by: portella@optonline.net (9/5/2009 10:54:56 PM)

up to how many range names can a worksheet have? thank you

 


Comment by: Jan Karel Pieterse (9/6/2009 10:06:35 AM)

Hi Portella,

There is no other limit than memory, so hundreds and even thousands can be used. Your file will grow however.

 


Comment by: Jan Karel Pieterse (9/7/2009 8:51:13 AM)

Hi David,

Indeed you cannot apply names accross sheets, you have to do that sheet-by-sheet.

Might be something to add to Name Manager.

 


Comment by: Jan Karel Pieterse (9/7/2009 9:39:42 AM)

Hi Conrad,

I know of no conflicts between ASAP utilities and Name Manager. Let me know if you find any!

 


Comment by: Jan Karel Pieterse (9/7/2009 9:42:20 AM)

Hi Roja,

No, Name Manager does not offer an automatic way for this particular problem.

 


Comment by: Jan Karel Pieterse (9/7/2009 9:47:59 AM)

Hi Peter,

Good question and well spotted bug!
I'll check if I can update NM to fix this.

 


Comment by: Jan Karel Pieterse (9/7/2009 9:49:34 AM)

Hi dbrett,

For MAC Excel you need to download the MAC version as indicated above (see the "Old Versions" section).

 


Comment by: Jan Karel Pieterse (9/7/2009 9:51:46 AM)

Hi Prashant,

Before issuing the copy command (I assume this is in VBA), use this line:

Application.DisplayAlerts = False


Make sure you turn that back on afterwards:

Application.DisplayAlerts = True

 


Comment by: Jan Karel Pieterse (9/7/2009 9:52:41 AM)

Hi Wimpie,

Please try if the latest download is OK.

 


Comment by: Stephen (9/22/2009 3:06:16 AM)

Fantastic Excel information

 


Comment by: PD (10/6/2009 1:27:38 PM)

either through install file or copying to addin folder I get

Compile error in hidden module: CMenuHandler

V4.1 only
V3.2 works fine

 


Comment by: Jan Karel Pieterse (10/7/2009 12:13:45 AM)

Hi PD,

This is probably caused by a problem with your Office installation. Open Excel and (assuming you have 2003)select Help, Detect and repair.

 


Comment by: Jeff (10/7/2009 12:51:44 PM)

I got a message the first time I used the find button, about having it look through an Excel's file VBA if certain options in my VBA editor were selected. The notification only appeared once, and I don't know exactly what options I need to enable to have it scan the VBA project in addition to the worksheet.

Thanks. This is an awesome product.

 


Comment by: Jan Karel Pieterse (10/7/2009 9:22:00 PM)

Hi Jeff,

Tools, Macro, Security, trusted sources tab, check box "Allow Access to Visual Basic Project".

 


Comment by: Eric Davey (10/8/2009 5:28:20 PM)

Congratulations on a great product.

When I try to create a range name I receive the following error. "Can't find project or library".

The error only seems to have occurred since I installed Name Manager (Version 4.1).

Is there a solution to this problem?

regards

Eric

 


Comment by: Eric Davey (10/8/2009 5:56:28 PM)

Further to my previous error I am now receiving the following message when I try to create a range name:

A dialog box opens with "NameManagerPassword" in the top of the box and it requests a password.

Can you help me with this error?

Regards

Eric

 


Comment by: Jan Karel Pieterse (10/9/2009 5:15:40 AM)

See my answer to PD higher up: I expect your Office installation has a problem, which a Help, Detect and repair will rpobably fix.

 


Comment by: Peter Thompson (10/20/2009 8:23:24 AM)

THanks, this is brilliant after all the trouble ive had trying to paste into the name manager in XL 2007 this worked straight away. Its a shame MS couldn't design such an easy to use interface.

 


Comment by: Will Hamlin (10/21/2009 1:02:41 PM)

Thanks for setting this up guys this is incredibly helpful!

 


Comment by: Tim (11/17/2009 12:00:39 PM)

Hi, thanks for the add-on. I am working on a project that needs to be web-based where people can input data for calculations. The cells need to be shown as words or picture buttons.
Do you know of a software program that will help me build this site? I'm not a programmer.
Thanks!

 


Comment by: Ravi (11/17/2009 11:21:19 PM)

Cool tool really helpful

 


Comment by: Jan Karel Pieterse (11/18/2009 1:21:35 AM)

Hi Tim,

You mean some sort of online spreadsheet?
Microsoft delivers this as a part of Sharepoint Services 2007, but it is very expensive to host. If your company has a Sharepoint services license, then you're in luck. Otherwise, you might be helped with Google apps, they have an online spreadsheet application.

 


Comment by: Kanwaljit (12/2/2009 8:42:53 AM)

Hi Jan,

I wonder if you can help me. I am having a strange problem on hand. It presently relates to 2 files (there may be more files like it, but I haven't found too many). Whenever I go to Insert - >Name ->Define and click in the "Refers to" box, Excel immediately crashes. Don't why ? Can you please guide me. I have a licensed version of Fast Excel installed.

Regards
Kanwaljit

 


Comment by: Jan Karel Pieterse (12/2/2009 11:35:31 AM)

Hi Kanwaljit,

How many range names does the file contain?
Is this Excel 2003? If so, try saving as html and opening the html from Excel and saving back to normal Excel file format.

 


Comment by: Jan Karel Pieterse (12/3/2009 2:30:12 AM)

Hi all,

An exchange in emails and some testing has revealed that Kanwaljit's problem was caused by this Excel security update:
KB973475.
Uninstalling that update from control panel fixed the problem

 


Comment by: John Smithman (12/11/2009 7:17:01 PM)

I loaded your excl 2007 version of Name Manager; it asked me for my Name Manager password????

What do I do now?

 


Comment by: Jan Karel Pieterse (12/13/2009 10:17:56 PM)

Hi John,

This is either caused by another addin, or by your anti virus sofware. I know there's an addin by Oracle causing this problem, which is considered a bug in the Oracle addin.

 


Comment by: Jamie Walker (12/18/2009 1:06:19 PM)

"Compile Error in CMenuHandler" resolved.

The cause of this particular error is Excel (I am using Excel 2003 on Vista) cannot find the file MSO9.DLL (which is part of the Office 2000 Suite)

I fixed the problem by downloading the file and placing it in the folder C:\Program Files\Microsoft Office\Office\

I then searched for all instances of MSO9.DLL in the registry and updated the values of the following keys;

HKEY_CLASSES_ROOT\TypeLib\{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}\2.1\0\win32\(Default)

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib\{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}\2.1\0\win32\(Default)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Components\349E92CC2CB71D119A12000A9CE1A22A\904000001E872D116BF00006799C897E

to

C:\Program Files\Microsoft Office\Office\MSO9.DLL

and hey presto, no more compile errors and the VBA Toolbar is now working.

For the technically minded amonsgt you the Microsoft Visual Basic for Applications Extensibility 5.3 Library contained in the VBE6EXT.OLB file contains a reference to MSO9.DLL file.

 


Comment by: Jan Karel Pieterse (12/19/2009 11:41:14 AM)

Hi Jamie,

Thanks for letting us know!

Regards,

Jan Karel

 


Comment by: Matthew Ward (12/21/2009 1:41:58 PM)

How do I copy names between worksheets?

 


Comment by: Jan Karel Pieterse (12/22/2009 6:49:32 AM)

Hi Matthew,

1. Open Name Manager and have it create a list of range names in the current workbook.
2. Copy that worksheet to the new workbook
3. Make sure the new workbook does have all sheet names the list of names you want to create refer to
4. Open Name Manager again and click the "Pick up" button next to the list button.

 


Comment by: Nick A (1/15/2010 7:22:55 AM)

Hi, I wrote something similar, but nowhere near as comprehensive, quite a few years ago before realising that so much of this type of stuff can be found online, written by people much more experience and qualified than me!

One thing I have in my sheet with a list of names is to have a hyperlink from the name cell to that range, so you can easily check a name, the formula/range it refers to and then the actual region. I find this useful when using dynamic named ranges and it may not always be clear from the formula what the actual range is.

I also have the actual cell value (or "-range-") if not a single cell, which I find useful as another way to check/list multiple named single cell input variables.

Hope you may find that a useful idea and something to consider adding.

 


Comment by: Jan Karel Pieterse (1/15/2010 7:47:43 AM)

Hi Nick,

Thanks for the suggestions!

 


Comment by: Dennis (1/26/2010 7:52:21 AM)

What a wonderful utility !
Thank you so very much for providing it free!

 


Comment by: Andrew Blundon (2/3/2010 8:53:34 AM)

Hi there, I've been using Name Manager for years. I would be lost without it.

We just upgraded to Excel 2007. I've installed NM but when I open excel I get: Compile Error in hidden module, fxlNameManager.

I tried the Excel 2007 Diagnostics (no Detect and Repair in 2007) but there were no errors found.

Any suggestions?

 


Comment by: Jan Karel Pieterse (2/3/2010 10:45:38 PM)

Hi Andrew,

I'm afraid somethingmust be off with your installation of Office anyway. Try going to Microsoft Update, see if any Office updates are available.

 


Comment by: Andrew Blundon (2/4/2010 5:44:34 AM)

Excellent Jan,

Office 2007 SP2 wasn't installed. As soon as I installed it, Name Manager came back to life.

Thanks!

 


Comment by: Aaron (2/5/2010 12:07:52 PM)

I just installed Office 2010 Beta, and I keep getting a "Code Interrupted" error every time I try to add names with Name Manager, and then Excel 2010 crashes. Any ideas? Thank you so much for this utility--it is an enormous time saver!

 


Comment by: Jan Karel Pieterse (2/6/2010 6:21:12 AM)

Hi Aaron,

Thanks for letting me know, I'll check this when I find some time.

 


Comment by: Ron Brown (2/9/2010 10:15:42 AM)

Hello,
I have a spreadsheet with macros from a vendor that I use to compile info that we gather. The problem is that when I import my data into the workbook I get hundreds of excel pop-ups that I must ok.
" a formula or sheet you want to move or copy contains the name ......"
Is this something that I should use your add-in for?

Thanks for any help
Ron

 


Comment by: Jan Karel Pieterse (2/9/2010 11:02:44 AM)

Hi Ron,

No, Name Manager does not have a tool for that.
What I usually do in such a situation is hold down the y key (y for yes) until the activecell starts filling up with all y's. Then I hit escape to avoid overwriting whatever was in that cell.
If you only need values and no formulas, maybe you can copy and then paste-special, values into your target sheet.

 


Comment by: TonyDataMan (2/10/2010 9:32:34 AM)

I am using names extensively because of the need to produce many different templates pointing to source data on a master sheet.
The tiny Excel 2003 names box and the rather tortuous Insert Name utility in Excel has really been holding me up.
Then I found the Name manager and what a major difference it has made.
I also found some code to widen the range name box here
"http://www.cpearson.com/excel/DefinedNames.aspx"

What I would really like is to be able to see the range name for a cell displayed somewhere when I am on a cell, but the Name Manager has gone a long way towards that.

Thank you for a brilliant utility

TDM

 


Comment by: Pat (2/10/2010 2:55:31 PM)

Hello

Please tell what I do wrong when I try to use the Name manager for my case.
I have 12 columns in one sheet at row 1
eg. A1 = Company, A2 = address and so on

I wanted to add names for these columns via the 'add a define name' functions at the Name manager add-in.
So I was typing
New name - Company
RefersTo - =$A$1
Scope    - name of selected sheet

and press add button. When I turned back, I could see at the main window this

Name                         RefersTo
NameOfSelectedSheet!Company =1

I use MS Excel 2000 EN SP3
Thanks

 


Comment by: Earl Takasaki (2/15/2010 6:27:27 PM)

I have been using this utility for years. Suddenly, when I try to invokeit, the window flashes, then immediately closes! What could be causing this? (I have try reinstalling, etc.)

 


Comment by: Jan Karel Pieterse (2/22/2010 7:31:46 AM)

Hi Earl,

No idea. What Excel version are you using?

 


Comment by: rob (3/8/2010 10:44:54 AM)

Hi,

I have a workbook with names that referenced files that had since been deleted. Excel wouldn't let me modify/delete these names. Thanks to your add in, I was able to delete the corrupt references.

Thanks.

 


Comment by: steve (3/9/2010 7:01:33 PM)

Hi,

how is it possible to edit references for existing names?
Do I always have to go to "Add new name", type in the existing name and then update the reference?

Most convenient would obviously be to activate the "refers to" field and then just drag the desired range with the mouse.

This would make a good tool great!

Cheers
Steve

 


Comment by: Jan Karel Pieterse (3/9/2010 10:19:29 PM)

Hi Steve,

You can manually edit the refesto in the associated box at the bottom of the window, but I take it you want to be able to point-and-click to set the range. Indeed Name Manager only has that in the add-name dialog. It is tricky to set up.
Good point though.

 


Comment by: GULAQIL (3/13/2010 9:10:46 PM)

That is why i can't see my Comment.

 


Comment by: Jan Karel Pieterse (3/14/2010 11:07:06 AM)

Hi GULAQIL,

You don't see your comments because I look at them before releasing them to my site.

 


Comment by: Tim (3/24/2010 1:30:32 AM)

Using Excel 2002 and Name Manager 4.1 build 615
I have a workbook with a named range containing a relative address:
While cell L11 is the activecell, the name
'Stmt (0)'!Cumulative
refers to
=ROUND(SUBTOTAL(9,'Stmt (0)'!$L$10:$L11),2)
I wanted to change the "refers to" definition as follows:
While cell S11 is the activecell, the name
'Stmt (0)'!Cumulative
needs to refer
=ROUND(SUBTOTAL(9,'Stmt (0)'!$S$10:$S11),2)
To attempt to achieve this desired result I selected S11 as the active cell, opened name manager, selected the name Cumulative, then clicked in the edit window where it displays the current "refers to" definition.
As expected, a dialog box appears asking me to confirm that S11 is the desired active cell, and I click "OK".
Next appears a dialog box which displays:
Run-time error '384': A form can't me moved or sized while minimised or maximised.
I click on "End" (the only available option) and this closes Name Manager. If I try to re-open Name Manager I get
Run-time error '429': ActiveX control can't create object.
If I close the Excel file and re-open it then it allows Name Manager to load, but it reproduces the above sequence each time I try to edit this name. I can edit other names in the same workbook.

 


Comment by: Jan Karel Pieterse (3/24/2010 1:55:16 AM)

Hi Tim,

This is due to a bug in Name Manager, if you do not maximize Name Manager's main window, then it will work.

 


Comment by: Ryan (3/31/2010 7:20:04 AM)

I today downloaded the name Manager add-in for Excel 2007. It seems to work fine, but for 1 of my files, I get an error when i try to use the name manager.

"Run time error '7'"

Out of memory

Since it works for other workbooks, I'm wondering what the issue is. Its a pretty important and widely used workbook, so re-creating is not optimal. Curious if this error is common or is based on any previously seen issues.

 


Comment by: Jan Karel Pieterse (3/31/2010 7:36:30 AM)

Hi Ryan,

That certainly is uncommon. Could you email me the file?
You can empty the cells if you like, All I need is the range names anyway.

Hunch: Does the file contain VBA code and utilize events? Try if it works if you open your file with macro's disabled.

 


Comment by: AlexJ (4/1/2010 9:45:16 PM)

Jan Karel, (Not critical)
I just installed build 619, and I get a number of new buttons which only work with FastExcel. Never saw these before. Is this a change or my install? Could we, maybe, disable and backlight, or even hide these items?

 


Comment by: Jan Karel Pieterse (4/2/2010 4:22:09 AM)

Hi Alex,

Yes, these are new. NM is freeware, but also a part of FastExcel, with more options as you can see. We've decided to make that more apparent.

 


Comment by: Lisa (4/4/2010 12:03:59 AM)

I'm trying to use your utility to rename many names at once. Here is the error I get Subscript out of range. how do I fix that?

 


Comment by: Jan Karel Pieterse (4/5/2010 6:36:35 AM)

Hi Lisa,

On what line does it fail exactly?

 


Comment by: AlexJ (4/7/2010 9:22:12 AM)

Jan Karel,
It appears to me that the option to "Go To Selected Name" is not available in Build 615. Is this correct?

 


Comment by: Jan Karel Pieterse (4/7/2010 11:28:05 PM)

Hi Alex,

In older versions, you have to check the option "Goto Selected names" in the list of options and then click on a name in the list. It caused trouble in some situations, which is why we changed the behaviour.

 


Comment by: alexJ (4/8/2010 7:29:10 AM)

Oh - I get it. Using the Highlight button adds a text shape to define the range, as in previous releases. The 'select' function can be left out. I think that's just fine.

 


Comment by: Lou Sander (4/12/2010 8:22:51 AM)

Name Manager seemed to install properly, and it shows up on my list of add-ins. Now, how do I start it up? I don't see any icons or obvious menu items.

 


Comment by: Jan Karel Pieterse (4/12/2010 9:12:38 AM)

Hi Lou,

Depends on your Excel version (as stated in the manual :-) )
Excel 2003: Look in the Tools menu.
Excel 2007, 2010: if you installed the proper version, find a group on the Formulas tab of the ribbon. If not, find it on the Add-ins tab.

 


Comment by: Alonso González Núñez (4/28/2010 12:31:27 PM)

Hello,
do yu have an excel vista version?

thanks

 


Comment by: Jan Karel Pieterse (4/29/2010 1:24:16 AM)

Hi Alonso,

I do not sell Microsoft Office, I'm sorry.
Also, there is no specific Office version for Vista.

 


Comment by: Karim (5/7/2010 12:21:16 PM)

Hi there,
Is there a way of "batch renaming" names? For example, I have 30 names starting with a common prefix and the rest of the name is different, like:

Prefix.xxxxxxx

I would like to change the prefix in all of them, in one go... is that possible?

Thanks for a superb utility!

 


Comment by: Jan Karel Pieterse (5/8/2010 2:59:53 AM)

Hi Karim,

The manual explains exactly how to do that.

 


Comment by: Karim (5/11/2010 7:40:39 AM)

Jan, thanks so much for pointing me to the right place!

I didn't know there was a manual, lol !    I was using the help button but it says "Only available with FastExcel" and I don't know what FastExcel is...

I used the batch renaming macro from the manual and it works nicely!! Saved me hours of work!

Thanks again,
Karim

 


Comment by: Mope (5/20/2010 8:19:57 AM)

Jan,

Just wanted to thank you for this great utility. It's fantastic & I've been using it for a couple of years now. I work with financial models and had some which had been corrupted by an accumulation of unused names & had all sorts of problems with copying & moving worksheets. I was able to "clean" out my models with Name Manager & I use it all the time now. Thanks a lot - for a great tool & for sharing so freely.

Much appreciated, Mope

 


Comment by: Bishwajeet Kumar Naik (6/28/2010 1:35:22 PM)

I used the batch renaming macro in excel 2007 from the manual. name manager is also installed in my excel 2007 But its showing "subscript out of range" error.

can some body help me

 


Comment by: Jan Karel Pieterse (6/29/2010 12:34:21 AM)

Hi Bishwajeet Kumar Naik,

You probably need to adjust this line of code:

Application.Run "'" & Workbooks("Name Manager.xla").FullName & "'!InitNameManager"


to:

Application.Run "'" & Workbooks("Name Manager 2007.xlam").FullName & "'!InitNameManager"

 


Comment by: David Bates (7/1/2010 7:44:47 AM)

Jan - Great tool!
I been able to get to work on two machines.
But I have one machine that keeps erroring out after the add-in is installed.

The error is : System Error &H80004005 (-2147467259) Unspecified error.

All there machines are running Excel 2007, I used the v4.2. I did a automatice install using your setup macro and also tried it manually.

Any ideas what needs to be done.
Thanks.

 


Comment by: Jan Karel Pieterse (7/2/2010 2:06:35 AM)

Hi David,

I have seen a number of cases where running a detect and repair of Office setup solves issues like this one.

 


Comment by: seow kian chye (7/14/2010 8:00:13 PM)

hi, tried the batch rename sub in your manual, i have rename the

Application.Run "'" & Workbooks("Name Manager.xla").FullName & "'!InitNameManager"

to:

Application.Run "'" & Workbooks("Name Manager 2007.xlam").FullName & "'!InitNameManager"

and the                

Application.Run "'" & Workbooks("Name Manager.xla").FullName & "'!replacename", sOldname, sNewName, True

to:

Application.Run "'" & Workbooks("Name Manager 2007.xlam").FullName & "'!replacename", sOldname, sNewName, True

i get a error 1004 : the name you enter is invalid.

Pls help

Thanks

Kian Chye

 


Comment by: Jan Karel Pieterse (7/15/2010 4:55:52 AM)

Hi Kian Chye,

This indicates you got the VBA code right, but that one of the names you are trying to change is invalid (the new name).

 


Comment by: eschmidt@harrisassoc.com (7/27/2010 12:25:54 PM)

Hello,
I am trying to use the batch rename to rename range names that have a space in the name. These spreadsheet were converted from Lotus 123 to excel. I have over 2500 spreadsheets where the named range needs updated due to a space in the name. I tried the batch rename in your manual and keep getting the name is not valid. It is also adding a new namedrange and not removing the old named range with the space in it. Here is my code..


Sub BatchRename()
'Uses functions from JKP's Name Manager addin:
'www.jkp-ads.com/officemarketplacenm-en.asp

'Takes a list of names (selected cells on worksheet in the file).
'Renames those names with the name which is in a column to the immediate right
'of the selected range
    Dim nm As Name
    Dim soldname As String
    Dim snewname As String
     Dim ws As Worksheet
    Dim newname As String
     Application.Run "'" & Workbooks("Name Manager.xla").FullName & "'!InitNameManager"
    For Each nm In ThisWorkbook.Names
    soldname = nm.Name
    snewname = Replace(soldname, " ", "", 1, 1, vbTextCompare)
    

     If soldname <> snewname And soldname <> "" And snewname <> "" Then
     Application.Run "'" & Workbooks("Name Manager.xla").FullName & "'!replacename", soldname, snewname, True
     'nm.Delete
     End If
    Next
    Application.StatusBar = False
    Application.Visible = True

End Sub


Pls help

Thanks

 


Comment by: Jan Karel Pieterse (8/16/2010 3:17:41 AM)

Hi,

Unfortunately, Excel VBA does not allow you to delete a range name with a space.

 


Comment by: Pierre (8/17/2010 10:06:06 AM)

Is it within Name Manager's capabilities to print the list of names, but also include all the contents(values) of the range that each name refers to? Like a pivot table. Great for comparing named ranges between 2 spreadsheets. Range is one criteria. Contents would be another. Many thanks.

Pierre

 


Comment by: Jan Karel Pieterse (8/17/2010 10:47:20 AM)

Hi Pierre,

No, not "out of the box", but you could use the result of the list names button and build some formulas yourself to extract values from ranges.

 


Comment by: G. Michael Guy (8/24/2010 8:06:45 AM)

I was unable to get your install to work. I'm running Windows 7 64bit and Office 2007. Your install file was apparently trying to copy somewhere it couldn't. I changed a line in your setup macro to fix it.

AddInLibPath = Application.LibraryPath & "\" & sFilename

to

AddInLibPath = Replace(Application.UserLibraryPath & "\" & sFilename, "\\", "\")


I included the Replace statement since it already had a "\" at the end of the path. I could have just removed the
& "\" &
part but I wasn't sure if it was always there. This made sure it worked no matter.

and now it installs just fine. Someone else may find this handy.

 


Comment by: Jan Karel Pieterse (8/25/2010 12:04:28 AM)

Hi Michael,

OK, that makes sense, both Vista/Win 7 do not allow you to save files in all locations and the Application library path is normally located in the program files folder, which is locked in Vista/Win 7.

What I don't get is the need for removing the double backslash. As far as I know, all Excel versions (as from Excel 5, up to 2010) omit the trailing backslash from the path. You're saying this isn't always the case?

 


Comment by: G. Michael Guy (8/25/2010 5:36:38 AM)

Hi Jan,

Thanks for the response.

I do occasionally lose my mind or confuse myself with too many things going on, but here is a direct copy and paste from my immediate window

debug.Print Application.UserLibraryPath
C:\Users\gmichaelguy\AppData\Roaming\Microsoft\AddIns\


and just for fun (with a different result!)


debug.Print Application.Path
C:\Program Files (x86)\Microsoft Office\Office12


I'm sure there must be some reason for this difference with trailing \, but I don't know it.

Hope that helps.

 


Comment by: Jan Karel Pieterse (8/25/2010 11:39:26 PM)

Hi Michael,

Ah, it is the userlibrarypath that does have the trailing space! I'll update my page accordingly.

 


Comment by: Tony Sutcliffe (8/26/2010 8:35:22 AM)

Hi,

I have just downloaded Name manager and I'm very impressed - I'm sure that I will find it very useful.

However it has raised a question in that I see that there a lot of hidden names that I don't understand in a file that I have written These are of the form
Transferors!9810D1E0_FFFB_476_A1D1_A3F6B5D3EE88_.wvu.FilterData    = Transferors!$A5:$R$147

The Transferors sheet has data in it to row 162 and has no Auto Filter applied. Mail Merge is used with the spreadsheet.

Have you seen these "wvu" ranges before and do you know what they are please?

Thanks,
Tony

 


Comment by: Jan Karel Pieterse (8/26/2010 11:31:06 AM)

Hi Tony,

Yes and if you turn off "show system names" in the settings section of NM (bottom-right of screen) you' should see those names disappear from view. Those are range names managed by Excel if you use custom views if I recall correctly.

 


Comment by: Tony Sutcliffe (8/26/2010 11:50:18 AM)

Hi Jan,

Thank you for the very quick response - you are quite right. They do disappear if I turn off "show system names" or delete the custom views.

To be honest I took over an existing spreadsheet and I didn't know the custom views were there. They have multiplied as I have copied sheets. I will now find out more about them as they could be useful elsewhere. Thanks again - the more I use NM the more I see what a time saver it really is - absolutely brilliant.

Tony

 


Comment by: Shane (8/27/2010 8:01:20 AM)

This saved my bacon!!!!
My pricelist uses HUGE amounts of named cells

 


Comment by: fran (9/1/2010 11:39:30 PM)

very useful

 


Comment by: doris fredrick (9/2/2010 6:25:23 AM)

I thank you very much very helpful

 


Comment by: Jaya Radhe (9/14/2010 9:10:07 AM)

Thank you so much! This macro is an excellent time saver!

 


Comment by: Dave Bardell (10/11/2010 4:48:22 AM)

Having overcome the "Compile Error in hidden module, fxlNameManager" problem by using Office Diagnostics, now getting "Compile Error in hidden module, CMMenuHandler" which OD is not fixing? (Win XP, Excel 2007) Have use NM for several years - one of the best tools available, thanks, so desperate to fix this problem!!

 


Comment by: Jan Karel Pieterse (10/11/2010 8:00:31 AM)

Hi Dave,

Odd, the menu handler class contains nothing out of the ordinary.
Does your system have VBA installed (does alt+F11 take you to the editor)?

 


Comment by: Sheldon (10/14/2010 4:25:42 AM)

I had a problem with ghost links, created when copying a sheet refering to named ranges. Found them and got rid of them in Name Manager in a snap!

I just discovered that they can be seen in Define in Excel as well (if you click on each) but as usual things are a lot easier in Name Manager. :-)

Excel 2003

 


Comment by: Arthur Yip (10/14/2010 12:54:00 PM)

Hi,

Name Manager is great - can see it saving me lots of time and effort!

I am running into this problem when I try to Add a name

Run-time error '32809':

Application-defined or object-defined error

Everything else seems to work ok. Is this a known problem?

Thanks,

Arthur Yip

 


Comment by: Jan Karel Pieterse (10/15/2010 2:06:05 AM)

Hi Arthur,

Odd. Which Excel version is this and can you tell me what name you tried to define?

 


Comment by: Carlos Olguin (10/19/2010 6:24:12 AM)

The tool is great, but it's unable to delete names in other characters such as chinese or korean.
I work in a multinational company and the amount of names in korean are increasing and I cannot get rid of them.


I don't complain, you have saved me hours, but this is a trend that will grow and your product should be prepared.
Regards
Carlos

 


Comment by: Jan Karel Pieterse (10/19/2010 7:49:23 AM)

Hi Carlos,

It should not be a problem to add that functionality.
What I would need to be able to do that is a list of valid Chinese and Korean characters for range names.

 


Comment by: Arthur (10/20/2010 11:53:57 AM)

Hi Jan,

I am working in Excel 2003 SP3

The error shows when I click the Add button =(

 


Comment by: Jan Karel Pieterse (10/20/2010 11:32:35 PM)

Hi Arthur,

I strongly suspect a problem with your Office setup.
Please try (from Excel) to run Help, Detect and Repair.

 


Comment by: Henk Adriaenssens (11/1/2010 9:38:46 AM)

Hi Jan,

Is there a way I can disable the shortcut key? I have a bunch of macros with shortcut keys already assigned and shift n is one of them.

Thx
Henk

 


Comment by: Jan Karel Pieterse (11/1/2010 12:03:43 PM)

Hi Hank,

You can change NM's shortcut key. How depends on your Excel version. See the NM manual.
Unfortunately I have no control over the position of the tooltips.

 


Comment by: Arthur Yip (11/17/2010 11:53:05 AM)

Hi

Is there a way to stop the auto-copy of the formula reference?

 


Comment by: Jan Karel Pieterse (11/18/2010 3:05:07 AM)

Hi Arthur,

Please ask your question here:
www.eileenslounge.com

 


Comment by: Monir G. (11/18/2010 8:38:18 AM)

Hi Jan;

I've recently downloaded and successfully installed your Name Manager Add-In V4.2.
(XL 2003 SP3, Win XP SP3)

It is an excellent tool, and I'd like to thank you for developing the freeware utility and making it accessible.
I've already posted a note on a DG Forum recommending it.

Kind regards.

 


Comment by: Jan Karel Pieterse (11/18/2010 10:12:54 AM)

Hi Monir,

Thanks!!

 


Comment by: Arthur (11/18/2010 4:36:19 PM)

Hi Jan

I was referring to Name Manager, where I am finding the formula reference of any named cell on my copy-and-paste clipboard whenever I select a name. Or am I dealing with a bug / am I mistaken ?

 


Comment by: Michael Bujol (11/18/2010 11:40:04 PM)

Excel crashes when I try to install the add-in on Office 2010 for Mac via the setup spreadsheet. I tried to install via the add-in menu as well but it doesn't work.

With an older version, I was able to install without errors but it never appeared in my formula ribbon.

Now it crashes Excel. Where is the add-in stored? I would like to remove it until the add-in is more up-to-date.


I use your product nearly every day in Windows and love it!

 


Comment by: Jan Karel Pieterse (11/19/2010 7:31:31 AM)

Hi Arthur,

Ah, indeed. Name Manager uses the clipboard to re-define a name, because that is (unfortunately) the only way to work around a bug in Excel VBA.
So - as much as I dislike saying this- it is "by design".

 


Comment by: Jan Karel Pieterse (11/19/2010 7:32:56 AM)

Hi Michael,

I'm sorry, the current version does not support MAC Excel.
See the web page for the MAC version, which is way behind the Windows version I'm afraid.

 


Comment by: William McNair (11/19/2010 9:51:06 PM)

i have installed and un-installed name manager v4.2 on my system witl no luck. My system is Windows7 64bit with Excel 2010 64bit. The error message is similer as you mention above... "Compile Error in hidden module: fxlHelp";Any suggestions?

 


Comment by: Jan Karel Pieterse (11/21/2010 10:20:24 PM)

Hi William,

I am not aware of any compile errors in NM, so thanks for reporting! We'll look into this as soon as we can.

 


Comment by: Gunder Sønsteby (11/22/2010 3:55:57 PM)

Thanks for a utility that seems very nice! I have just installed it and have one question: In the Name Manager in excel 2010 I see several identical names but the have different scopes. In your Name Manager I only get to see the Global one. Why?

If I delete that there are no signs of local names. If I choose "Duplicate global local" its shows up there, but only with the global value. What do I do wrong?

Thanks in advance!

 


Comment by: Jan Karel Pieterse (11/22/2010 10:23:21 PM)

Hi Gunder,

You're welcome!
I can't really tell why the names don't show in Name Manager. Maybe you have unchecked "System names", or they are not true Range names, but table names (our Name Manager treats those differently)?

 


Comment by: Timm Severin (12/2/2010 6:05:47 AM)

I'm not completely sure that the following problem is the fault of Name Manager, but it seems to disappear when I deactivate it:

I'm using Excel 2003 on a WinXP machine, and regulary, but not predictable when Closing VBA-Editor/Excel I get about 3 errors like "failed to read/write from disk" (only in german, don't know the exact translation in english).
Every few times this happens excel crashes and restarts..

I couldn't track this down any further, but it could be because i have quite limited access to the machine and it's disks.

For now i removed the AddIn, and Excel seems to work fine, so maybe you can have a look if you have any operations in your code that cause this problem.

Anyway, nice tool, and thanks for all the work!

 


Comment by: Jan Karel Pieterse (12/2/2010 6:33:45 AM)

Hi Timm,

Odd indeed. All I can think of is two things.
1. NM tries to save settings to the registry (but that would not cause write errors I guess).
2. NM changes the Excel UI and thus when Excel closes it tries to update its Excel11.xlb file. Maybe yours has become corrupt?

 


Comment by: Timm Severin (12/2/2010 7:05:10 AM)

Wow, fast answer ;)

I just got the error again, though name Manager is not active. But it definitly does not occur that often, so I by now assume that it is a problem with Defined Names (Excel seems to have some of them, just spend 1 hour solving a problem i'd refer to as bug).

Concerning your guesses:
1. I think i should be able to write at least the user-tree, otherwise excel shouldn't work properly i guess
2. This would be possible, but how do i repair it?

But as it still occurs, i think it's none of your problems to worry about. And I by now got used to saving every few minutes.

 


Comment by: Jan Karel Pieterse (12/2/2010 12:18:35 PM)

Hi Timm,

You can rename the xlb to something else (because you'll loose your toolbar and menubar customisations I'd rename rather than delete it). Excel will recreate it if it is renamed.
If the problem remains, it wasn't the xlb and you can replace the new copy with the old one.

Check out if any of these help:
www.jkp-ads.com/articles/startupproblems.asp

 


Comment by: Thomas R. Glass (12/12/2010 10:10:43 PM)

I cannot figure out how to change the scope of a name in Excel 2007.    I want to change some names from workbook to worksheet and some from worksheet to workbook. The "scope" is displayed in Excel's Name Manager, but not editable. Would your name manager help me with that?

 


Comment by: Jan Karel Pieterse (12/12/2010 11:08:32 PM)

Hi Thomas,

Yes it will.

 


Comment by: Tim Reczek (12/14/2010 2:44:39 AM)

Hi Jan,

Thanks for a great tool, however I seem to have found an issue.

When using name manager to rename a defined name, it also renames the literal strings for the pivot table name and pivot table field names in GETPIVOTDATA formulas if the defined name is contained as a substring of any of these.

Example:

I have a defined name called "Servers".

I also have the following formula which uses no defined names in it:

=GETPIVOTDATA("Sum of Servers Touched",'Servers Pivot'!$A$3,"New Servers",$A6,"Servers per Region",AY$4)

Now if I rename the defined name "Servers" to something else, like MyTestName, it also renames the field names, pivot table name, etc in the GETPIVOTDATA formula and I get:

=GETPIVOTDATA("Sum of MyTestName Touched",'MyTestName Pivot'!$A$3,"New MyTestName",$A6,"MyTestName per Region",AY$4)

I'm using Excel 2003 on XP SP3 with the latest version of name manager.

Thanks,
Tim


 


Comment by: Jan Karel Pieterse (12/14/2010 9:33:00 AM)

Hi Tim,

That is not the way it is supposed to work indeed. All Name Manager checks for is whether the character before and after the name is within a very specific list of characters, so as to determine of it is really a range name we've found. Indeed, names within a quoted string seem to be improperly handled.

The only way to prevent this with the current version is to acknowledge each and every replacement in turn so you get a chance to skip such entries.

Very cumbersome though.

 


Comment by: Cameron Baillie (1/11/2011 9:00:17 AM)

Hello,

I recently installed Name Manager 4.2 on a Win7-Pro-64 computer and my initial comment is that I cannot use my mouse's scroll wheel to scroll through the names.

Thanks,
Cam

 


Comment by: Jan Karel Pieterse (1/12/2011 12:01:05 AM)

Hi Cam,

Thank you for your comment.

Unfortunately, Office VBA does not recognize the scroll wheel "out of the box". It is possible to make it work, but I've been told the code in question may be unstable so we decided not to use it.

 


Comment by: Andrew (1/12/2011 4:08:59 PM)

Mac owner with office 2011 for mac: only error message but no window or other signal of life.

 


Comment by: Jan Karel Pieterse (1/12/2011 10:26:49 PM)

Hi Andrew,

The current version of Name Manager does not work on a MAC. But there is an older -MAC compatible- version available (see above).

 


Comment by: Phil Justice (1/15/2011 9:07:06 PM)

@ Cameron Baillie - Install Wizmouse from Antibody Software to enable significant improvement in uses for the scroll wheel. Allows scrolling of VBA screens and other windows even when they're visible but not focused.

@ Jan Karel Pieterse - Just found your website and tools from Chip Pearson's site. Looking forward to utilizing Name Manager as I'm learning how valuable Names can be in managing spreadsheets and VBA code. Thank you for allowing others to use your efforts freely!

 


Comment by: David Isaak (1/18/2011 9:51:49 AM)

Does this software allow me to do a workbook-wide change of a substring in a name? For example, could I change all the instances of .CA to .FL with a search and replace?

Imports.Prod1.CA
Exports.Prod1.CA
Bunkers.Prod1.CA

Thanks,
David

 


Comment by: David Isaak (1/18/2011 12:11:46 PM)

I am running Office 2010. Immediately after activating the Name Manager add-in, I get the following error (reported in a box labeled "Microsoft Visual Basic for Applications"):

Compile error in hidden module: CMenuHandler

I seem to get this same error whenever I open a new workbook.

 


Comment by: Jan Karel Pieterse (1/18/2011 12:28:30 PM)

Hi Isaak,

On the replace: Yes it does.
On the error: try running repair from Office setup.

 


Comment by: David Isaak (1/18/2011 2:05:32 PM)

Thanks for your quick responses.

I have downloaded the software (very nice, far better than the native Excel Name Manager) and looked at the manual.

But I'm afraid I still don't see how to do a workbook-wide replace of one substring with another. I'm probably just not as smart as I might be, but all I have found are ways to change one name at a time...

Thanks,

David

 


Comment by: Jan Karel Pieterse (1/18/2011 10:54:41 PM)

Hi Isaak,

You are right of course, a s&r to replace multiple names' names isn't available. However, you can use the bulk rename example that is in the manual for this purpose.

 


Comment by: Rodrigo (1/22/2011 8:54:06 AM)

Dear Jan, I've downloaded the Name Manager 2.3 version since this suppose to work with mac. However, I have Excel 2008 and it seems the VB macros do not work with this version. Is there anything I can do or an updated version of Name Manager that I can download?. Regards,

 


Comment by: Jan Karel Pieterse (1/23/2011 6:37:25 AM)

Hi Rodrigo,

Can you give a bit more detail: what does not work, do you get an error message of some sort?

 


Comment by: Gojak (1/29/2011 7:33:17 AM)

Hi Jan, thanks for your many years of helpful advice and great addins. Until now! For some reason after many reinstalls of Excel (2002 yeah, I'm stuck on it) this time I can't get Name Manager to appear on the tools menu.
I've tried every version on offer and just don't know where to go from here. It's there - all present and correct in VB but I can't access it. I've checked every menu in case some toolbar customisation I've done has put it elsewhere but I'm clueless. Any answer? btw, all my other xlas are showing up where they should. (I'd trade 'em all for yours!)

 


Comment by: Jan Karel Pieterse (1/30/2011 10:18:46 PM)

Hi Gojak,

Maybe your toolbar customisation file has gained a corruption. See this page on where to find it:

www.jkp-ads.com/articles/startupproblems.asp

 


Comment by: Gojak (2/1/2011 2:11:42 AM)

Thanks Jan, but I couldn't find a solution there - although it was interesting reading and I tried a few of the things. I really am stumped!
Since the xla is loading and appears in my addins, it is simply a missing menu problem. Is there any way I create a new one manually?

 


Comment by: Jan Karel Pieterse (2/1/2011 5:18:17 AM)

Hi Gojak,

Aha! The easiest is to download and install the 2007/2010 version of Name Manager (remove your current copy first). The Userinterface can then be found on the Formulas tab of the ribbon.

 


Comment by: Gojak (2/1/2011 6:42:45 AM)

Eek Jan, I guess you think I'm using Win7 with all that talk of ribbons n such... sorry I didn't say, I'm using XP. And as mentioned XL02 (cringe). I use newer OS and XL at work, and don't like it as much as my old faithful. Which is why I don't understand what is happening - as you can imagine I have reinstalled this same setup many times (and computers) over the years and Name Manager always along with it.
Ah well, I think I've tried everything possible now. If you (XL genius) can't figure what's up, I'm not surprised my feeble efforts haven't worked!
I really appreciate your help anyway. Thankyou very much.

 


Comment by: Jan Karel Pieterse (2/1/2011 7:16:12 AM)

Hi Gojak,

Sorry, my mistake, you did mention Excel XP and it should work.

Does hitting control+shift+n work to launch the name manager form (of course NM must be installed)?

Check disabled items (Help, about, button on screen), is Name Manager listed by any chance? If so, enable NM there.

 


Comment by: Gojak (2/1/2011 8:09:56 AM)

AHHHHH. Yes! Hotkey worked! Hooray... Now why didn't I think of that? It was there all along as I thought. All those versions, I bet they all worked, I just couldn't get to them. I can live with not having a toolbar menu. I was ready to accept not having any at all. THANKYOU Jan. Thankyou.

 


Comment by: Jan Karel Pieterse (2/1/2011 9:26:14 AM)

Hi Gojak,

Great. Still, you should have an entry in the tools menu.
I suggest you to check your toolbar customisations file as mentioned on the page I referred you to earlier.

 


Comment by: Gojak (2/1/2011 8:18:10 PM)

Hi Jan, yes I did go through all the options - disabled items, coms, etc. There is nothing wrong with my xlb, it just seems the customisations themself have thrown Name Manager off the perch. Other addins not.
I might try next reinstall to put it in first then customise. Maybe that's what I did all the other times and not really taken note of the fact. Actually, it's the only thing I can think of now having exhausted everything else. But! the main thing is I can use it again. Yahoo!
Thanks again, not just for this but all the other things I've learn't from your generous sharing of your v a s t knowledge.

 


Comment by: Jan Karel Pieterse (2/1/2011 10:20:41 PM)

Ho Gojak,

Sounds like another addin is resetting the tools menu after Name Manager has added its menu to it. Bad programming.

 


Comment by: Gojak (2/1/2011 11:10:51 PM)

Oh you are so right! Many times I've diced something, because it messed with my toolbars - not because I couldn't find a use for it. Especially when they they create an extra bar that pushes the stuff you DO want all over the place and worse, create a new line wasting window space. Grr-have to close them every time.
It's often with major apps that offer components to Office - so I think it's not always bad programming - I reckon it's arrogance. Look at me look at me! I'm so clever and useful and you're too stupid to find me on your own. It's a common complaint, when I've looked for answers online I find heaps of irritated others trying to wrangle the bloomin things too.
Oops, sorry - started raving - I think you opened a vent there! ;)

 


Comment by: Jan Karel Pieterse (2/2/2011 12:59:58 AM)

Hi Gojak,

<smile>.
For me, arrogance equals bad programming or at least bad design.
So I try to avoid that. If a tool of mine adds a toolbar, I try to put it back where the user last left it. Not easy, but I think the user deserves the courtesy.

 


Comment by: Gojak (2/2/2011 1:13:31 AM)

Ummm.... well I suspect that would be why you have pages and pages like this of people bowing and thanking you and thousands of fans all over the world, wouldn't it then.
:) !!!

 


Comment by: Rodrigo (2/9/2011 3:55:24 PM)

Dear Jan,

I wrote you on 22/1/2011 about this problem, " I've downloaded the Name Manager 2.3 version since this suppose to work with mac. However, I have Excel 2008 and it seems the VB macros do not work with this version. Is there anything I can do or an updated version of Name Manager that I can download?. Regards,"

I download the 2.3 version and unzip, but when i open the file "VBA is not accesible for this version" any suggestion, i have Snow Leopard 10.6.6 and Excel 2008 for Mac.

Regards,

 


Comment by: Jan Karel Pieterse (2/10/2011 5:32:05 AM)

Hi Rodrigo,

I'm afraid there is not much I can do. Office 2008 for MAC does not have VBA at all. I advise you to upgrade to the latest version of Mac office, which has VBA included again.

 


Comment by: Venus (2/15/2011 11:19:15 AM)

Your NameManager and ASAP Utilites are invaluable to me! Thank you for sharing your talents with us.

 


Comment by: Jan Karel Pieterse (2/16/2011 1:32:10 AM)

Hi Venus,

Thanks!
(ASAP utilities is not mine however)

 


Comment by: Brian (2/16/2011 7:51:49 PM)

Thank you for providing such a useful and well documented tool.

Cheers!

 


Comment by: Chris Cannon (2/21/2011 6:28:38 PM)

Hi,

I have been unsuccessfully trying to use the Name Manager addin. Installation appears to work fine (have done both automated and manual instalation) and it appears in my addins list as installed, however the option does not appear for Name Manager + in the Formula ribbon.

I am using Office 2007 with Windows 7 operating system.

Thanks in advance,

Chris

 


Comment by: Jan Karel Pieterse (2/21/2011 11:04:16 PM)

Hi Chris,

Hmm. Is it by any chance listed on the Add-ins tab?

 


Comment by: Steve James (2/22/2011 5:50:28 AM)

I'm having the same issue as Chris. I was successfully using build 622 of the 2007/2010 version. However, upgrading to build 627 has removed Name Manager from my Formulas ribbon tab, although the add-in shows as installed and active in the Excel Add-Ins dialog.

 


Comment by: Jan Karel Pieterse (2/22/2011 6:21:21 AM)

Hi Steve, Chris,

Apologies, I simply forgot the Ribbon code in the 2007 version. I have corrected this. Please download the 2007/2010 version again!

 


Comment by: Dag (3/1/2011 4:25:43 PM)

Well done Sir! Great one!! Migrating from Global to Local etc... extremely useful!

 


Comment by: Ryan Farquharson (3/2/2011 4:47:39 PM)

We've use names extensively and would like to use the Comments to provide explanations. You can mannually enter comments into Excel 2007 name manager, but you can't get them back out again without copying and pasting individually.
Any chance of incorporating Comments capability into future versions of your name manager? Particularly an ability to list the comments.

 


Comment by: Jan Karel Pieterse (3/2/2011 11:17:39 PM)

Hi Ryan,

Thanks for the suggestion. It has been listed.

In the mean time, maybe you can put these two macros to use?

Sub ListNamesAndComments()
    Dim oNm As Name
    Dim lCt As Long
    For Each oNm In ThisWorkbook.Names
        Range("A1").Offset(lCt).Value = oNm.Name
        Range("A1").Offset(lCt, 1).Value = oNm.Comment
        lCt = lCt + 1
    Next
End Sub

Sub UpdateNamesWithNewCOmments()
    Dim oCell As Range
    If Selection.Column <> 1 Then
        MsgBox "Please select cells in column A"
        Exit Sub
    End If
    For Each oCell In Selection
        ActiveWorkbook.Names(oCell.Value).Comment = oCell.Offset(, 1).Value
    Next
End Sub

 


Comment by: Ryan Farquharson (3/3/2011 10:14:12 PM)

Thanks
Worked a treat except made the following change:
Range("B1").Offset(lCt, 1).Value = oNm.Comment
Also switched calculation to manual and turned of screen refresh first (we have thousands of names).
R.

 


Comment by: Ryan Farquharson (3/3/2011 10:26:39 PM)

Sorry - ignore my cell change in last comment

 


Comment by: Ted Howell (3/13/2011 5:10:03 PM)

Does this version 4.2 work in Excel:Mac 2011 ?

 


Comment by: Jan Karel Pieterse (3/14/2011 4:59:34 AM)

Hi Ted,

I don't think so, it contains windows API calls a MAC doesn't know about.

 


Comment by: habibkhan (3/21/2011 11:14:07 AM)

I m thankful for this support
wish u the best of knowledge

 


Comment by: amy (4/11/2011 1:10:18 AM)

hello, im new here..

ive setup for my data validation and drop down list. its working..but the problems is now when i open the same file from a different pc in a network, the reference from the name manager suddenly changed and the value becom #REF! .it cause my drop down does not appear..

Additional Info,
1.the worksheet contains pivot table
2.and the list/source of drop down is in different folder..

anyone can help me..pls

 


Comment by: Jan Karel Pieterse (4/12/2011 1:38:38 AM)

Hi Amy,

How have you set up the source list for the validation?

 


Comment by: PJ_in_FL (4/20/2011 4:49:37 PM)

Hello Jan Karel,

I've just installed NameManager and do have one comment - could the functions that are disabled in the free version and only available with FastExcel be gray'ed out? Even having to use the text version of the buttons to see which are disabled would be an improvement.

Thanks for sharing this work with the Excel community! I wish I was in a position to show my appreciation in a more relevant way.

Sincerely,
PJ_in_FL

 


Comment by: Jan Karel Pieterse (4/20/2011 9:46:34 PM)

Hi PJ,

You're welcome!
Thanks for the feedback on the disabled buttons. HOwever, there's a commercial reason behind the fact that they do not work: we want to promote FastExcel!

 


Comment by: PatrickOfLondon (5/18/2011 1:28:54 AM)

Jan Karel,

I downloaded Name Manager yesterday and after a day's use it's already indispensable to me.

Thank you so much for doing yourself what Microsoft themselves should have done years ago, and still haven't done in Excel 2010: provided comprehensive, easy-to-use, names management facilities.

I can sense a few small suggestions-for-enhancement just beginning to form in my mind but I'll reserve those till I've thoroughly learned my way around your wonderful add-on.

Thanks again.
Patrick

 


Comment by: RodP (5/25/2011 7:31:45 AM)

Hi, I tried both version 4.2 and 3.2 and also tried the detect and repair function to stop the 'Compile Error in hidden module, fxlNameManager' error but to no avail. Please could you suggest if there is anything else I could try. I'm using Windows 7 with Excel 2000 (!). It seems to work ok on Windows XP with Excel 2000 and so please could you help me understand what DLLs I might need to copy over to associate in Excel? Thanks in advance

 


Comment by: Jan Karel Pieterse (5/25/2011 7:32:00 AM)

Hi Rod,

Odd that it doesn't work. I guess we haven't had many people using Office 2000 on Windows 7 yet!

Hard to say which dll is causing this problem however.

I'll try to send you a sample file to test this.

 


Comment by: Dietmar (5/31/2011 1:20:01 AM)

Thanks a lot for this tool! I just spent two days with a broken sheet. It kept complaining about unresolved references. Now your tool told me about a hidden name pointing to a different, unrelated file. Once the name was deleted, the sheet works fine again. Kind regards from Germany!

 


Comment by: Marco (5/31/2011 7:37:58 AM)

Thanks guys for your fantastic tool!!
with Excel 2007 I have the compiling error you mentioned: "Compile Error in hidden module, fxlNameManager". Unfortunately I can not fix it using the diagnostic (in office 2007: excel options --> resources --> run office diagnostics). Do you have any suggestion?

Thanks

Marco

 


Comment by: Jan Karel Pieterse (5/31/2011 9:06:21 AM)

Hi Marco,

Try going to Microsoft update. You may need to update Office.

 


Comment by: Zalma (5/31/2011 11:13:15 PM)

Namemanager is great but atleast in 2010 version there is really annoying bug. If you close excel while namemanager is on. It stars to ask about namemanager password and don't let excel close.

Only way to close is to terminate whole excel prosess manually.

 


Comment by: Jan Karel Pieterse (5/31/2011 11:57:04 PM)

Hi Zalma,

Unfortunately, this is not a bug in Name Manager, but in another piece of software on your system, which is trying to access the VBA project of Name Manager when you quit Excel. There used to be a Google desktop search utility that caused this. Do you by any chance have that installed on your system?

 


Comment by: Shane Fagan (6/2/2011 10:03:42 AM)

Seems like a great tool; I'm wondering if there's a way to retroactively apply a new name across an entire workbook. In other words, I have cell references all over the place and am only now applying names to the source cells. Excel's built-in Apply Names button only works within a single tab, which seems useless to me. Greatly appreciate it! Thanks.

 


Comment by: Jan Karel Pieterse (6/5/2011 11:59:01 PM)

Hi Shane,

Currently the tool does not support that. But you could adopt this macro so it runs through all sheets and their used ranges:

Sub ApplyAllNames()
'-------------------------------------------------------------------------
' Function : ApplyAllNames
' Company : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created : Feb 2008
' Purpose : Applies range names to all formulas in selected cells
'-------------------------------------------------------------------------
    Dim oName As Name
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    If TypeName(Selection) = "Range" Then
        On Error Resume Next
        For Each oName In ActiveWorkbook.Names
            Selection.ApplyNames oName.Name
        Next
    End If
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

 


Comment by: Shane Fagan (6/6/2011 12:48:56 PM)

Wow thank you so much! I really appreciate your time.

 


Comment by: Shane Fagan (6/7/2011 4:08:34 PM)

Hi Jan,

I hate to bother you again but would love some more input on how to implement the macro if you have time.

Say I have a very simple spreadsheet with three (currently unnamed) values in Sheet1 cells A1, A2, and A3. I then go to Sheets 2 & 3 and perform some simple calculations involving those values. Of course the formulas reference the cells as 'Sheet1'!A1 etc. I then name the cells in Sheet1: x, y, and z. I load this macro into the workbook; then how do I get it to update the formulas in Sheets 2 & 3 so that they read in terms of x, y, z rather than 'Sheet1'!A1 etc.? Thanks in advance!

Shane

 


Comment by: Jan Karel Pieterse (6/8/2011 12:46:08 AM)

Hi Shane,

Well, you could expand the macro to work on all worksheets and on all formula cells:
Sub ApplyAllNames2AllSheets()
'-------------------------------------------------------------------------
' Function : ApplyAllNames2AllSheets
' Company : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created : Feb 2008
' Purpose : Applies range names to all formulas in all sheets
'-------------------------------------------------------------------------
    Dim oName As Name
    Dim oSh As Worksheet
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For Each oSh In Worksheets
        On Error Resume Next
        For Each oName In ActiveWorkbook.Names
            oSh.UsedRange.ApplyNames oName.Name
        Next
    Next
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

 


Comment by: Hagen (6/15/2011 4:05:33 AM)

Hi,

Great program!

One question though: Is it possibe to not have absolute cell references in formulas when deleting names. For example: Cell names are A1=a, A2=b and A3=ab. ab= a+b. When I delete the names the formula is updated to A3= $A$1+$A$2. I want it to read A3 = A1+A2.

Thanks!

 


Comment by: Jan Karel Pieterse (6/15/2011 11:53:41 PM)

Hi Hagen,

Theoretically that is possible, but I prefer the way we have currently implemented it and that is replacing the name with the actual formula of the name.

 


Comment by: Liroy (6/17/2011 8:03:23 AM)

Hello JKP

Simple question.

I want to copy a few worksheets from a workbook to a new workbook that is shared to allow people to update it.

There are several names (Workbook Scope) in the original file.

Then I want to copy those worksheets back to the original file, but maintain the defined name ranges.

Any way to keep the definition of defined name ranges to be the same across multiple workbooks?

Thank you

 


Comment by: Jan Karel Pieterse (6/18/2011 11:14:45 AM)

Hi Liroy,

First of all: I always advise against using the shared workbook "feature". It is buggy and error-prone. The client version of Excel is not fit for editing a file with more than a single person.
If you copy worksheets from file A to file B, Excel prompts you what to do with duplicate names in both files. To keep the ones in the target file, just click Yes (default option). Is that what you're after?

 


Comment by: Andy Rice (6/27/2011 9:19:13 AM)

Thank you very much for providing this great product.
One thing is that the Short Cut Key button seems to be missing in the Name Manager Excel 2007 Ribbon. Can you check that?

 


Comment by: Jan Karel Pieterse (6/27/2011 11:16:00 PM)

Hi Andre,

It does show up on my system. Which build do you have? (click the about button on the main dialog)

 


Comment by: Andy Rice (6/30/2011 9:44:32 AM)

Jan: The about box displays "Version 4.2, build 628." I am running Windows 7 and Excel 2007. The Name Manager Excel Ribbon contains two buttons: one for Name Manager and one for Reset.
Thanks for looking into this.

 


Comment by: Jan Karel Pieterse (7/4/2011 1:50:05 AM)

Hi Andy,

In both 2007 and 2010 Name Manager shows up on the RIbbon on the FOrmulas tab, on the far right of the ribbon in a group called "Name Manager+". There are three controls on that group: a big red N to start Name Manager, a small button that looks like a keyboard key to change the keyboard shortcut and a button called "Reset" to reset the psoition and size of the window of Name Manager.

 


Comment by: Andy Rice (7/5/2011 8:33:07 AM)

Jan: I thought the word "Reset" referred to that small button. So that small button was calling the keyboard change code all this time. Thanks, Andy.

 


Comment by: Jan (7/5/2011 2:29:44 PM)

Jan great product just wondering will it be possible to make it as fast when changing names as the excel in built name manager. I work in large spreadsheets with circa 4000 range names and I love your product for filtering but its very slow when I want to rename a range and make changes to formulas, in comparison to the excel version this is very fast but it takes a long time to find the name in the list. Do you know why the Microsoft version is so much quicker?

Thanks Graham, keep up the good work.

 


Comment by: Jan Karel Pieterse (7/6/2011 4:43:50 AM)

Hi,

Well, anything built into the product has a big chance in being faster than what is built using VBA.
My renaming function is slower because it has to scan through all cells with formulas and all objects (like Pivot tables). The built-in system can do that much more efficiently I expect. Unfortunately, there is no VBA way to use the built-in renaming system directly.

 


Comment by: John (7/28/2011 12:23:15 AM)

I'm using Excel 2007 on Windows XP (Pro x64)

I just tried to install your Name Manager - I'm hoping it will help me audit names to find named ranges that are not referenced (by name) in a very large workbook with many worksheets and many, many names.

Sadly I am getting the aforementioned compile error "System Error &H80004005 (-2147467259)" with buttons for "OK" and "Help". Clicking Help loads a page from Microsoft Online Help which is less than helpful. Clicking on "OK" then results in a further error message - "Conplie error in hidden module: fxlNameManger" with further "OK" and "Help" buttons. This Help button leads to a different yet equally unhelpful page from Microsoft online help, and this OK button then allows the workbook to open; however attempts to use Name Manager result in the same sequence of errors.

I have run Microsoft Office Diagnostics from the main Office menu (Excel Options > Resources > run Microsof Office Diagnostics) however this reported no errors found.

Can you offer any further advice on how to rectify this problem.

Thanks heaps

John.

 


Comment by: Sorin (7/31/2011 12:34:00 AM)

I installed your application. It's a must have for all Excel 2007 users. It solved me an old and embarrassing problem with the range names. When I perform the analysis of range names in my workbook I discovered tens of unused and wrong (i.e. #REF) rangenames which created me problems (i.e. display wrong values).
Thank you very much for sharing this marvelous application.

 


Comment by: Jan Karel Pieterse (8/22/2011 2:58:58 AM)

Hi John,

Unfortunately, the error you get is a problem with your Excel or VBA installation. Try reinstalling Office.

 


Comment by: John (8/23/2011 5:41:27 PM)

Jan,

Thank you for your response to my problem. Since the time of my initial problem, my machine has suffered a hard drive failure. Accordingly the drive was replaced and the machine re-imaged (with the corporate standard image for my place of employment). Inspired by your confidence I have today re-tried to install Name Manager - this time with apparent complete success :)

Two points of note however:-
1) the VBA install routine (in Setup Name Manager 2007.xls), in my configuration at least, adds an extra slash to the install (copy to) path & file name;

AddInLibPath = Application.UserLibraryPath & "\" & sFilename

?addinlibpath
C:\Documents and Settings\<USERNAME>\Application Data\Microsoft\AddIns\\Name Manager 2007.xlam


which causes the install to fail (unless manually corrected).

2) If I may be so bold as to suggest - when selecting the option to display 'Unused names only' - it would be convenient to be able to cancel at the point of the dialog prompt to allow trusted acess to VBA - so this could be effected before continuing (if desired). With the current singular 'OK' option in that dialogue, in the case of very large workbooks with many many names it is a case of kiling Excel via the Task Manager, or waiting hours for the operation to complete before allowing the trusted acces and retrying.

Thank you for this marvelous tool.

Regards
John.

 


Comment by: Baljit Sehbi (9/20/2011 8:53:44 AM)

I want to use cell names (as arrays) in creating a formula in a different row. Would you know how to do this. It works in the same row.
Thanks for your help.
Regards,
Baljit

 


Comment by: Jan Karel Pieterse (9/22/2011 12:19:49 AM)

I expect you have to enter the formula as an array formula to ensure it does not "work on the same row", by confirming the formula with control+shift+enter.

 


Comment by: Joanne (9/27/2011 6:08:11 PM)

I am creating a workbook and have created a 'master' worksheet with some defined names. I copy this worksheed and edit it as needed. I would like to find a method to change the scope from the worksheet name to workbook. I downloaded the Name Manager 4.2 and installed it in my Excel 2007. I can't seem to find a way to change the scope. Can you help me please?

 


Comment by: Jan Karel Pieterse (9/28/2011 12:06:36 AM)

Hi Joanne,

You cannot have global range names for each copy of your master sheet with the same name; this is what local range names are for: have the same range name point to different sheets.
Maybe I misunderstood what you are trying to achieve?

 


Comment by: Deb (10/3/2011 1:25:42 PM)

my location is the US, at work we have users in Canada.

there is a single user in Canada that cannot access the drop down look-up lists in an excel spreadsheet that is located on a shared network drive. she is the only one in Canada having this issue

She sent us a screen print of her Name Manager and there are several entries under the Name column with #Ref!in the Value column. FYI, she recently had the help desk install Excel 2007.

How can I fix this?

Thank you!!

 


Comment by: Jan Karel Pieterse (10/4/2011 4:28:36 AM)

Hi Deb,

Since this is just the one user that cannot access the dropdown lists, I doubt if the range names are the culprit here. How exactly is the DD list tied to the external file?
Is she the only one on Excel 2007? Which version are the other users on?

 


Comment by: merle (10/14/2011 3:25:11 AM)

Thanks for this free utility.

 


Comment by: ved (10/16/2011 3:54:24 AM)

I have got a report file exported by Siebel as an Excel work-sheet.

I wish to conevrt it into an oracle table to be used in a VB project. The problem is that there are some spaces in many Column Names of the work sheet. How to get rid of these embedded space in filed names at VB level and not at EXCEL level ?

Thanx in advance.

 


Comment by: Jan Karel Pieterse (10/16/2011 11:53:36 PM)

Hi ved,

Not sure what you need, do you want to change the field names *after* the data has been imported into Oracle?

 


Comment by: Mark (10/20/2011 7:00:50 AM)

Jan, this should be a simple question but I can't find the answer anywhere. I have a spreadsheet which is replicating a separate model, and needs to include 4 variables named a45, b45, c45, and d45. I want to name the cells containing these values, and I realize that I can't simply use "a45" as a name, since it conflicts with a cell address. So I tried to name these cells "a45Length", "b45Length", etc. This works fine for all of them EXCEPT "c45Length", which Excel 2010 will not allow me to use. Error message says it conflicts with a built-in name. I tried other names, like c45Lever, c45LeverLength, c45Junk123, etc. and it seems like ANY name that starts with "c45" is prohibited. What built-in name am I conflicting with?!? I would simply use some other designation, but I want the names to match the names in the original model (as much as possible). Can you offer any advice? Thanks!

 


Comment by: Syed (10/20/2011 9:13:24 PM)

Hi,

This is an excellent add-in and I have been using for some time now.

Recently,whenever I boot excel (2007 version) I get an error message "Compile error in hidden module: C Menu Handler".

When I start excel in safe mode the above error does not appear at all. Then just out of curiosity I reboot excel again and disabled Name Manger (2007 version) and the error did not appear.

I reconfirmed the above by disabling the addin and re-enable it and every time when excel start with Name Manger addin enabled I get the error.

Is there a way to resolve this issue?

I greatly appreciate your time and response.

 


Comment by: Jan Karel Pieterse (10/20/2011 9:57:23 PM)

Hi Mark,

The same error occurs in Excel 2003. It is because of the C45 bit. In fact any C followed by a number will cause the error. Excel thinks your name looks like a cell reference. It has something to do with Excel's R1C1 cell reference mode, but in my opinion is just a bug.

 


Comment by: Jan Karel Pieterse (10/20/2011 9:58:06 PM)

Hi Syed,

Looks like you need to do a detect and repair of your Office installation.

 


Comment by: Valerie (10/21/2011 3:21:09 PM)

Oh my gosh you are a lifesaver!!! I have inherited workbooks that when I try copying sheets I receive around 30 name messages that are not in my named ranges box. I "cleaned" out the Styles in the workbook as there were about 50 and I know these can be "hidden" sources of trouble. I have looked and looked for these weird names - "a", "aaaa", "BB" etc. and have been unsuccessful in locating them. I installed the Name Manager and it listed them all! When I checked where they were used, it said they couldn't be found. But I was able to delete them all!!! Now I can copy my sheets with no problem! Thank you!! I hate being stumped so you saved me from a life of frustration!

 


Comment by: Morpheus (10/24/2011 11:17:24 PM)

This is an excellent tool, and indispensible in Excel 2003 version where the built-in functionality is quite basic and inadequate for the needs of even somewhat advanced users.

Best regards

 


Comment by: Tharg (12/9/2011 5:32:53 AM)

Very nice add on.

A note : I did not think it was working for me but then I checked my add-ons and saw it was disabled. Not sure why but it's possible I set something up to do this by default or perhaps anti virus did it.

After enabling it everything works great.

 


Comment by: Jan Karel Pieterse (12/9/2011 5:36:49 AM)

Hi Tharg,

Sometimes Excel diables addins by itself, especially after a crash of Excel (even if the crash has nothing to do with the addin in question!).

 


Comment by: Pankaj Handique (12/14/2011 7:16:00 AM)

Hi,

My query relates to seeing a number of names in the Name Manager that are in old workbooks. In the previous versions,only that names in the current workbook I would be working on would appear in the name manager screen. However, in excel 2010 Name Manager, names in workbooks I am not working on also appears on the screen and this is very confusing. Also, the filter "Names scoped to workbook" does not seem to be working. Please help.

Regards,
Pankaj

 


Comment by: Pankaj Handique (12/14/2011 8:03:42 AM)

Hi again

I just realised the issue with the random names. These names appeared in my current workbook because I had copied a few worksheets from the other workbooks into my current workbook. So excel copied the names from the parent workbooks into my current workbook, which I think can be quite a useful feature. Had me confused for a bit though.

Thanks,
Pankaj

 


Comment by: Jan Karel Pieterse (12/14/2011 8:05:28 AM)

Hi Pankaj,

Name Manager only shows the names in the active workbook. That has not changed in Excel 2010. So all names you see are really in the active workbook. And were there in previous Excel versions as well!

 


Comment by: Arjun Whorra (12/18/2011 11:55:10 AM)

hi,
My query is in regard of the limitations in naming ranges in excel 2007.I have a vba code which adds a name on a particular sheet based on the value of a cell on that sheet.
The value of what is in that cell changes every time the code is run.My problem is that that particular value might contain a "-" sometimes .For eg : "50101-01" or "50108-02" . Excel does not allow the use of spaces or special characters for defined names.I could change the the value to be just "5010101" or "5010802" .But that would mean re-writing atleast 3 dozens vba codes and atleast 20 different excel worksheets on which i have these lists.Is there a vba FUNCTION i could write to overcome this limitation excel has?? any type of help would be appreciated.

 


Comment by: Jan Karel Pieterse (12/18/2011 11:03:18 PM)

Hi Arjun,

You'll have to work around the name restrictions Excel has, there is no way around it. I'd suggest to first replace the dashes with an underscore.

 


Comment by: Dino (12/20/2011 11:30:30 AM)

Excelent tool!!!

 


Comment by: Masood Ahmad Dar (12/30/2011 12:17:09 PM)

sir i want to search for shortcuts command if you have to hilp me in this regard please send me ms excel commands/shortcuts keys in example.
thanks

 


Comment by: Jan Karel Pieterse (12/30/2011 12:30:04 PM)

Hi Masood,

See:

www.cpearson.com/excel/shortcuts.htm

 


Comment by: Bill Benson (12/30/2011 3:02:51 PM)

Hi Jan Karl, I looking forward to this software which comes highly recommended by our mutual friend R.P.

I have 420 named ranges in a workbook, some at sheet level some at workbook level; I am about to see how easy your app makes things!

PS... if I like it a lot, where do I donate?

:)

Bill

 


Comment by: Jan Karel Pieterse (1/1/2012 11:59:30 AM)

Hi Bill,

Thanks. There is a donate button on my downloads page:

http://www.jkp-ads.com/Download.asp

 


Comment by: Guestevez (1/16/2012 10:57:53 AM)

Thank you very much!!!, Works in Excel 2010!

 


Comment by: Phil Bowen (1/19/2012 5:00:18 AM)

Does this great sounding bit of kit work with mac2011?

 


Comment by: Jan Karel Pieterse (1/19/2012 10:04:56 AM)

Hi Phil,

Don't know really. I think not. But the MAC version should work, does it?

 


Comment by: JH (2/1/2012 9:46:43 AM)

Awesome! I had a spreadsheet with a bunch of named ranges that I could not delete with Excel's built-in Name Manager (did not show up). However, with this tool all the names showed up and I was able to delete the problems. Great work putting this together!

 


Comment by: mai (2/8/2012 12:51:55 AM)

it did not work
it could not change the name from local to global.
so don't distribute it please......

 


Comment by: Jan Karel Pieterse (2/8/2012 2:36:54 AM)

Hi Mai,

Can you please tell me which name you tried to globalise exactly (inclusing sheetname)?

Perhaps the name itself is corrupt, or an Excel system name.

 


Comment by: Chris Lada (2/17/2012 2:05:02 PM)

I got it installed and ran the "Setup Name Manager" function, but I am not sure how I run the tool.

 


Comment by: Jan Karel Pieterse (2/20/2012 7:42:45 AM)

Hi Chris,

The zip file you downloaded contains a manual...

 


Comment by: Irene C. (3/1/2012 10:38:06 PM)

Superb! I had this file which was inherited from another person who had inherited it from yet another person, etc.
There is a reference to range name here which I can't see in the standard Excel Name. Whenever I need to copy a worksheet, I would have to click on "Yes" x about 40times as there is about 40 name range referenced. Sometimes I need to duplicate the worksheet about 8 times, so imagine the number of mouse clicks that I had to do!
A note of caution for users, you need to be very familiar with name range function. Otherwise, you may have accidentally delete name range that you really need for other purpose.

 


Comment by: Valerie Robbins (3/6/2012 7:58:33 AM)

I love this program and as I have inherited many workbooks that have made many rounds that contain many invalid names. I have a workbook I am "cleaning", but have run across names that will not delete and they are not valid as they have been deleted in many other workbooks. What would cause some names to be deleted and others not? Thanks for your help.

 


Comment by: Jan Karel Pieterse (3/6/2012 9:08:41 AM)

Hi Valerie,

Those names are corrupted. I have a tool that can remove them, just email the workbook in question to the address listed at the bottom of this page.

 


Comment by: gerdami (3/7/2012 11:23:33 AM)

The rename function does not succeed in changing the names in charts.
Problem occurs in both XP and W7 versions.
Hence, when I use the rename function, I have to manually change the names of the ranges used for my charts.

 


Comment by: Jan Karel Pieterse (3/8/2012 2:52:17 AM)

Hi gerdami,

That is indeed a bug in Name Manager. Thanks for reporting.

 


Comment by: Jan Karel Pieterse (3/8/2012 3:39:56 AM)

Hi Gerdami,

I have updated name manager to fix the bug you reported.

 


Comment by: gerdami (3/8/2012 7:53:09 AM)

Hi JKP,
I downloaded build 633 but I think the bug is still there.

 


Comment by: Jan Karel Pieterse (3/9/2012 5:46:47 AM)

Hi Gerdami,

Pity, it worked for me. Can you please send me the file that it doesn't work with?

 


Comment by: gerdami (3/14/2012 2:18:48 AM)

Small bug when trying to rename a range with string containing non allowed characters such as / * - +
It provokes a run-time error 1004 and mouse cursor is displayed as "hourglass", after having clicked on the End button. However, launching and closing NameManager reset the cursor to a pointer.

Excel 2003, under XP.

 


Comment by: shahein (4/11/2012 5:56:36 AM)

Thank you very much

 


Comment by: Dr. Matt Wenham (5/1/2012 6:55:42 AM)

Love this Add-In, many thanks. Would it be possible for it to recognise the so-called 'short circuit' or square-bracket method of referencing Named Ranges in VBA:


[Test].value

is for instance equivalent to:

Range("Test")


Hoping this is possible!

 


Comment by: Jan Karel Pieterse (5/1/2012 9:23:41 AM)

Hi Matt,

Thanks for your suggestion. We'll consider doing this.

I must say I never use this shorthand code, as it is slower than fully qualified referencing using the Range object, (about 20 percent).

 


Comment by: Matt (5/8/2012 10:17:17 AM)

Hi JKP,

I am trying to develop my own ribbon tab in Excel 2007 using xml and was hoping to have a button to activate your excellent Name Manager tool.

Unfortunately to do so I need to know the name of the subroutine in your code that activate the main Name Manager form. Is this something you'd be willing to share? I'm not after the password to your code, just the name of the routine so I can call it from xml.

Kind Regards
Matt

 


Comment by: Jan Karel Pieterse (5/8/2012 10:58:10 PM)

Hi Matt,

Start it like so:

Application.Run "'NameManager.xlam'!ManageNames"

 


Comment by: Matt (5/9/2012 1:11:54 AM)

Thanks! That works great.
KR
Matt

 


Comment by: Ruud (5/14/2012 2:20:49 AM)

Hi Jan Pieter,

Nice and very useful tool!

If I can make 2 suggestions for additional features (not sure if these have been suggested in the past by other persons):
* List that scrolls with mouse wheel;
* Some way to select/copy the name of the named range, so that it can be pasted into formulas. (When typing a cell formula the named range of course appears after having entered the first few characters, but in some cases, like using a named range in for the values of a chart series, this is not the case and one needs to correctly 'type' the entire name)

Best regards,

Ruud

 


Comment by: Jan Karel Pieterse (5/14/2012 2:49:25 AM)

Hi Ruud,

Thanks for the suggestions.
Unfortunately, though enabling scrolling with a mouse on a listbox is possible, it yields the application unstable (crashes). Which is why we obvioulsy have not implemented that.
In my Excel 2010, the formula autocomplete does autocomplete range names in the chart series formula.

 


Comment by: Valerie Robbins (6/8/2012 11:42:32 PM)

I love this program! However, I have some workbooks that have names I can't seem to get deleted. I think I wrote you before and have lost your email address - I believe you asked that I send you my workbook? Can you please help me with this?

Thank you!
Valerie

 


Comment by: Jan Karel Pieterse (6/9/2012 8:59:21 PM)

Hi Valerie,

Yes I think I did. You can send them to the address listed at the bottom of this page.

 


Comment by: Tony Lorusso (6/20/2012 5:30:05 PM)

Hi,

First, thank you for this excellent free utility.

When I use the rename facility if the range name is used in a form control on a worksheet such as a check box, the first letter is omitted. For example if I rename a range to ClassOption the control is set to =lassOption as it's formula. I'm using Excel 2007.

 


Comment by: Jim Tate (6/21/2012 2:42:32 AM)

I used an older version but can't seem to get version for Excel 2010 to run. It's installed but I don't find icon/entry to run pgm.

thanks,

Jim Tate

 


Comment by: Jan Karel Pieterse (6/21/2012 8:00:21 AM)

Hi Tony,

Thanks for letting me know!

 


Comment by: Jan Karel Pieterse (6/21/2012 8:01:39 AM)

Hi Jim,

If the installation went well, you should be able to find the icon on the Formulas tab of the ribbon, on the far right. If not, check the Add-ins list: File, Options, Addins tab, Excel addins dropdwon, Go button.

 


Comment by: Jeff Roth (7/2/2012 1:23:07 AM)

This is a wonderful tool.

I was receiving "A formula or sheet you want to move or copy contains the name "...", which already exists on the destination worksheet."

Your tool found names that the Excel Name Manager would not show and allowed me to delete them and fixing my problem.

Thank you!

 


Comment by: Anish (7/10/2012 1:58:54 PM)

Hey :)

It still gives me complie error inspite of repairing office suite as stated.

Using MS-Office 2007 & OS Windows XP

Thanks

 


Comment by: Jon Peltier (7/11/2012 6:50:41 PM)

Name manager 2007 does not work in Excel 2010 64-bit. Upon installation there is a compile error in CMenuHandler, and the description hints that the error often occurs if code is incompatible with version, platform, or architecture of the application.

 


Comment by: Venus Childress (7/30/2012 7:01:55 PM)

I appreciate your Name Manager addin more than I can say!

Recently I began using the Sumifs function in a formula. Shortly thereafter I noticed a name that I cannot delete, _xlfn.sumifs. Would you have a suggestion on how I can get rid of the name, or why it's there? I use Excel 2007 exclusively, the file is not saved as compatible with earlier versions. I am using Name Manager 4.2, build 628.

Thank you for such a wonderful tool and any assistance you can provide.

 


Comment by: Jan Karel Pieterse (8/7/2012 11:19:29 AM)

Hi Venus,

These range names are inserted by Excel 2007 and 2010 when you use functions (like SUMIFS) that do not exist in Excel 2003 and older. They cannot be deleted.

 


Comment by: Jan Karel Pieterse (8/7/2012 11:41:03 AM)

@Anish: I'm sorry, I'm out of ideas. Perhaps one of the older versions does work?

@ Jon: I'll check this out and let you know.

 


Comment by: Default300 (8/22/2012 12:46:46 AM)

Hi. Thanks very much for your very useful free tool.

I have two questions.

When Name Manager checks whether a name is used, or if you rename a name and opt to replace the old version with the new, then...

[1] HIDDEN CELLS

If a worksheet has hidden cells, you get the message:

"Warnings: Worksheet [X] has hidden cells."

Can / does Name Manager check the hidden cells?


[2] PROTECTED SHEETS

If a workbook has protected sheets, you get the message:

"Warnings: Sheet [X] is protected. Search results may be incomplete."

Can / does Name Manager check those protected sheets?

 


Comment by: Jan Karel Pieterse (8/22/2012 10:24:05 AM)

Hi Default300,

No, Name manager does not unhide cells or unprotect sheets, it tries to maintain your file's integrity. It is up to you to take these actions, so you stay in control of your workbook all the time.

 


Comment by: Default300 (8/22/2012 10:57:59 AM)

Thanks Jan Karel

Actually I wasn't asking whether Name Manager could unhide cells or unprotect sheets.

Rather, I was asking whether Name Manager could and would SEARCH / CHECK the hidden cells and protected sheets, or whether it has to skip them.

If it can't / doesn't, then the user should manually unhide / unprotect them in advance of the search to ensure file integrity.

 


Comment by: Jan Karel Pieterse (8/22/2012 3:58:16 PM)

I think it does not look in hidden cells and whether or not it finds anything in protected sheets depends on the protection settings.

 


Comment by: David Onder (9/4/2012 10:37:34 PM)

Just installed this on a Windows 7 machine running 2010. After using Name Manager, I am prompted for a password 11 times. I have this installed on another machine and it does not prompt me at all. Old machine running Build 621, new machine running Build 635.

Any help would be greatly appreciated!
Thanks, David

 


Comment by: Jan Karel Pieterse (9/5/2012 10:07:51 AM)

Hi David,

This cannot be caused by Name Manager itself, it is probably another add-in(s) that causes this.

Try unchecking your add-ins (don't forget COM add-ins) to see which is causing havoc.

 


Comment by: David Onder (9/5/2012 2:07:25 PM)

Well, I unloaded all add-ins and added them back one at a time. The error message went away. Thanks for the help!

 


Comment by: Jan Karel Pieterse (9/6/2012 11:56:17 AM)

Hi David,

I'm glad you could solve the issue.
Thanks for letting me know!

 


Comment by: Chuck Reimer (9/20/2012 9:28:57 PM)

I am using Name Manager 4.2 build 621 and I LOVE it.
I do have a question -I have a name that I cannot delete (and I did not create it).
the name is _xlfn.IFERROR and it refers to =#NAME?

Have you seen this before?

Thanks,
Chuck

 


Comment by: Jan Karel Pieterse (9/21/2012 11:59:53 AM)

Hi Chuck,

Excel adds a range name like that whenever you use a function that is not available to older versions of Excel.
There is no way to change that, nor can you remove them.

 


Comment by: Chuck Reimer (9/21/2012 1:41:28 PM)

Jan Karel,
Thanks for the fast response. I was worried I had some kind of corruption.
Thank you for all that you do to help the Excel users of the world.

Chuck

 


Comment by: Ian (9/24/2012 10:07:59 PM)

Hi Jan,

I am going to download your utility and take it for a spin.

I have a Workbook that we use at work for tracking flight delays. It has multiple sheets and a few graphs - it worked for the longest time and then I saved it into .xlsm format and it has started giving me the following error:

A formula in your worksheet contains one or more invalid references.
Verify that your formulas contain a valid path, workbook, ramge name and cell reference.

There are no visible issues and no #Errors anywhere in teh workbook.

I deleted sheets and formaulas one by one until I had one blank worksheet and no errors and the error persisted. Hoping your utility may help identify what is causing the issue.

Wondering if you have come across this before?

Regards
Ian

P.S. Will provide feedback after I have used the tool.

 


Comment by: Jan Karel Pieterse (9/25/2012 9:21:10 AM)

Hi Ian,

This error is proably caused by a chart on one of your worksheets. Note that the chart may be invisible.

 


Comment by: Luke (10/1/2012 11:23:19 AM)

This is an amazing tool... I had some problems with a copied worksheet making all the named ranges local, and this was the only way I could find of converting 50 named ranges to global without having to manually delete and recreate them. A timesaver, and I will certainly reccommend it to others! Thanks!

 


Comment by: Alan W (10/2/2012 5:08:28 PM)

I have used this for some time and find it very useful.

Since moving to Excel 2010, and a different Thin client structure that may be the cause, I have found that with the add-in enabled when I open Excel it opens/creates Book1, automatically closes it and then opens/creates Book2 and is fine.

This does not happen if Name Manager is disabled, however as it also seems to happen if Analysis ToolPak - VBA is enabled, so I suspect is nothing to do with Name Manager, but something deeper in the system

 


Comment by: Jan Karel Pieterse (10/3/2012 9:23:16 AM)

Hi Alan,

My Autosafe seems to cause this issue. It has been fixed today and you should be prompted for the update within a couple of days.

 


Comment by: Jan Karel Pieterse (10/11/2012 9:44:45 AM)

Hi everyone,

Just got a note from a user experiencing the compile error.

He was able to fix it by deleting the "2.7" entry in the registry under this section:

HKEY_CLASSES_ROOT\TypeLib\{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}

Note that this user had just removed a beta version of Office 2013.

 


Comment by: Patrick (10/27/2012 4:07:16 AM)

Hi Jan, is version 2.3 supposed to work for excel for mac 2011 version 14.2.4 (120824) latest update 14.2.4 running under osx 10.7.5?

Firstly, it did not install via the button in the excel file. I had to move it manually per the instructions in the errmsg.

Secondly, it did not allow me to change the definition of a name, ie from "apple"=Sheet1!$a$1 to "apple"=Sheet1!$a$2. I tried it multiple times, each time clicking "yes" to the prompt to confirm but it does not get changed.

Thirdly, I don't see a way to change the name of a name, ie if I had "apple"=Sheet1!$a$1, to change "apple" to "banana" without having to search and replace everywhere I used "apple" into "banana".

Thanks.

 


Comment by: Patrick (10/27/2012 4:10:21 AM)

... just to add, in relation to my question whether v2.3 is meant to work with excel for mac 2011, the reason I ask is that after installing the name manager, almost all the time when I try to enter a name by clicking into the box near top left where the cell address shows up, the cell address there does not get highlighted as it usually does. When I then proceed to type a name, the text I type gets entered into the cell instead. Thanks.

 


Comment by: Jan Karel PIeterse (10/27/2012 1:56:50 PM)

Hi Patrick,

I'm sorry, NM 2.3 was never tested on this version of Office on the MAC.
I have spoken with a colleague who has a Mac about updating NM 4.2 to work on a MAC, but he was too tied up right now.

 


Comment by: james (11/2/2012 9:01:45 AM)

JKP

Does the name manager allow me to replace cell references in formulas with range names?

Cheers
James

 


Comment by: Jan Karel PIeterse (11/2/2012 10:09:50 AM)

Hi James,

No, that functionality is not built into Name Manager.
However, it is not hard to write a macro that does this.

The code below picks up ALL range names and applies them to all formula in all worksheets. It is up to you to decide how to put this to use :-)
Sub ApplyAllNames2AllSheets()
'-------------------------------------------------------------------------
' Function : ApplyAllNames2AllSheets
' Company : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created : Feb 2008
' Purpose : Applies range names to all formulas in all sheets
'-------------------------------------------------------------------------
    Dim oName As Name
    Dim oSh As Worksheet
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For Each oSh In Worksheets
        On Error Resume Next
        For Each oName In ActiveWorkbook.Names
            oSh.UsedRange.ApplyNames oName.Name
        Next
    Next
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

 


Comment by: Gilbert Havens (11/19/2012 8:51:01 PM)

Trying to install Add In for Excel 2010. Had to copy manually into Add In folder as instructed. Can see the Add In on the menu, but when I highlight and click "ok" it does NOT get added to by formula ribbon/toolbar and I can't find it on the individual commands either. Do you know what might be blocking the install? Thank you.

 


Comment by: Jan Karel Pieterse (11/20/2012 8:30:41 AM)

Hi Gilbert,

Perhaps your macro security is preventing the code to run. Check File, Options, Trust Center, Trust center settings, none of the checboxes there are checked. Also, on the Trusted Locations tab, normally there is an entry for Add-ins. Finally, what are the macero settings? I have the second option set (disable with notification).

 


Comment by: Gilbert Havens (11/20/2012 6:12:20 PM)

Was able to add it (after manually installing in Add-Ins folder), by going to "Manage Excel Add-ins" at the bottom of the Options/Add-Ins page.

More importantly used it and found more than 7,000 "names" that Excel's name manager couldn't locate. Most of them were invalid so I deleted the whole lot and now I can copy worksheets without crashing Excel because of that inane "invalid name" message. Early tests are encouraging!

Thanks!

 


Comment by: Jan Karel Pieterse (11/21/2012 8:31:04 AM)

Hi Gilbert,

Great to hear you could install the tool successfully!

 


Comment by: Stacy (11/27/2012 8:11:36 PM)

Can you export from Name Manager (I know how to get the list), update and import back into NM with additional names?

 


Comment by: Jan Karel Pieterse (11/28/2012 8:46:11 AM)

Hi Stacy,

Yes to both questions. Click the List button and edit the resulting list. Then click the pickup button next to it to update and add the edits.

 


Comment by: Senor Lama (11/30/2012 7:48:26 PM)

After converting (blech!) from XL 2003 to 2010 in mid-2011 and getting overwhelmed by the crappy "new and improved" user interface, I lost track of all the tweaks I had made in XL 2003...including Name Manager. I rediscovered Name Manager today and am SO grateful it has been rewritten to work in 2007/2010! Thank you very much for making this add-in.

 


Comment by: murrough (12/4/2012 4:44:16 PM)

Has anyone come across this problem (OK found this site whilst searching for said problem), but its worth a shot.

I define a name in Excel VBA either using ReferTo or ReferToR1C1 on an english Excel but as soon as the form pass across countries and re-opens in a German Excel in German the name range is 'screwed' and hence i get the dreaded #Name? message on the form everywhere.

Cheers

Murrough

 


Comment by: Jan Karel Pieterse (12/5/2012 8:36:01 AM)

Hi murrough,

Never seen that one before. Perhaps the range name uses a formula from the Analysis toolpak?

 


Comment by: Daniel (12/6/2012 6:04:25 PM)

Good day Jan Karel,
I installed Name Manager v4.2 for Office 2010 and Icon is showing no problem in the Formula section.
When I click on it, I get the message that "No names in Active Workbok" - that is fine but when I click "OK", I see very rapidly the border of a window opening and closing.
Closed all application to see if it was hidden but no.

when I create a name in my workbook, I do not see the message "No names..." but same thing, the border of a window opening & closing.

Any ideas what might cause this?

Thank you.

 


Comment by: Jan Karel Pieterse (12/7/2012 1:06:14 PM)

Hi Daniel,

That is really odd. I've never hear dof this one before and NM works fine on my own Excel 2010 installation, so I can hardly troubleshoot your problem.

Have you tried a detect and repair of Office?

 


Comment by: Ryan (12/20/2012 4:08:05 PM)

I just installed your add-in, but I can't see the new buttons in the "Formulas" ribbon as you describe. I've tried repairing Microsoft Office and restarting my PC, but I still get nothing. It does show up in the Add-Ins for Excel. How do I get this to work?

 


Comment by: Jan Karel Pieterse (12/20/2012 5:08:36 PM)

Hi Ryan,

Is it checked in the add-ins list?
Does control+shift+n launch the dialog?

Of yes to both then probably you installed the 2003 version. In that case find the menu items on the Add-ins tab of the ribbon.

 


Comment by: Ryan (12/20/2012 7:58:59 PM)

Thanks. I figured it out. It was not checked in the add-ins list. That is another obscure setting in Excel. Why wouldn't it be automatically enabled when I install a new add-in? Thanks for your help.

 


Comment by: Jan Karel Pieterse (12/21/2012 4:50:18 PM)

Hi Ryan,

Odd, the add-in should have been checked after the installation finished. This may fail if you had another instance of Excel open at that taime and close that other instance last. Excel then stores its settings without the add-in being marked as active.

 


Comment by: Charles Leonard (2/5/2013 4:21:02 PM)

After uninstalling Office 2013 preview I get the following error when starting up excel:
Complie error in hidden module: CMenuHandler

 


Comment by: Jan Karel Pieterse (2/5/2013 5:34:46 PM)

Hi Charles,

You probably need to remove and install your current Office version.

 


Comment by: Matt Wilkinson (2/8/2013 12:45:48 PM)

Hi,

I've noticed a minor issue with the Rename Range functionality - if you rename a range then the range itself and references to it are renamed correctly, unless a reference is hidden by an autofilter, in which case it isn't changed and causes a reference error.

Hopefully that will be a simple thing to fix in the next update!?

Kind Regards
Matt

 


Comment by: Jan Karel Pieterse (2/10/2013 8:34:50 PM)

Hi Matt,

Thanks for letting me know!

 


Comment by: Michael Griffith (2/11/2013 5:33:51 PM)

Consider adding a line to the installation instructions.

For 2013 users, to activate name manager, you need to navigate to Microsoft Add Ins (File Options>Excel Options>Add Ins) In the Add Ins page, choose Manage Excel Add Ins, hit "Go" This brings up Add Ins box, click next to Name Manager Utility and click OK. Name Manager Utility will now appear on your Formulas ribbon

 


Comment by: Jan Karel Pieterse (2/11/2013 7:48:56 PM)

Hi Michael,

Thanks. But basically, that should happen automatically when you use the setup tool.

 


Comment by: Scot (2/11/2013 10:18:04 PM)

Thank you! I spent hours trying to find and delete a corrupted range name that kept me from copying worksheets. With your add-in, it was fixed in SECONDS!!!! Thank you!

Scot

 


Comment by: Bill (2/16/2013 4:18:00 PM)

I was wanting to change the scope of a named range (not in VBA). Don't see a way to do this with the Named Range Manager in Excel - using your add-in

 


Comment by: Jan Karel Pieterse (2/18/2013 8:15:43 AM)

Hi Bill,

There is a Global to local and a Local to global button (the globe and the crossed out globe) exactly for this purpose.

 


Comment by: gerdami (2/22/2013 1:37:57 PM)

Names of tables (formerly known as lists) do not appear in NameManager 4.2. Warning message "No names in active workbook".

I created a quick table with one header row and a few rows of data. With Ctrl-L I convert the range to a table. Table1 is attributed to the table. It appears within the drop-down box left to the formula icon (Fx). However, the name does not appear with F3, for example when I want to select the range from the Insert pivot-table dialog, nor within NameManager.

 


Comment by: Jan Karel Pieterse (2/22/2013 2:24:10 PM)

Hi Gerdami,

We deliberately do not show table names in our Name Manager, as they behave different from the "pure" range names.

 


Comment by: John (3/1/2013 5:29:44 PM)

I am running Excel for Mac 2011. I downloaded the 2.3 version & when I open it, it says "cannot be accessed" What am I doing wrong?
John

 


Comment by: Jan Karel Pieterse (3/3/2013 7:37:31 PM)

Hi John,

Unfortunately, I don't have access to a MAC so cannot test. I'll ask around.

 


Comment by: frank pytel (3/5/2013 12:49:11 PM)

Very Very Nice. Thank you. You saved me some mucho time on a rework of a worklog.

Frank Pytel

 


Comment by: Rudolph Hubert (3/6/2013 1:51:21 PM)

Very impressive, but it does not appear to find Named Ranges which are only referenced in UserForms.

I have 10 Named Ranges (Assign1 - Assign10) that are referenced in the ControlSource property on a UserForm, yet when I checked "Unused names only" they were all displayed as "Unused". Also, I selected one of the names (Assign1), clicked the "Is Used ?" button (5.5.24 in the Manual) and the analysis results returned: "The Name 'Assign1' was not found".

I thought maybe I could try modifying the code to check for the presence of UserForms and examine the ControlSource fields, but the code is locked down.

I think this would be a good feature to add because reporting UserForm-only referenced Named Ranges as "Unused" might cause someone to delete the Name, rendering the UserForm inoperative.

Other than that, this is an excellent program - very impressed!

 


Comment by: Jan Karel Pieterse (3/6/2013 7:33:47 PM)

Hi Rudolph,

While you do have a valid comment, I don't think we will update Name Manager with that.

There is a simple reason for that: I never use the ControlSource property, but always push the content to listboxes and comboboxes using VBA. Most developers do by the way.

 


Comment by: Rudolph Hubert (3/7/2013 2:07:38 PM)

Jan Karel,

Thank you for posting my comment (and responding).

I did want to clarify, the workbook I mentioned is one that I inherited and am trying to "clean up". Based on the volatility of some of the lists I would have probably taken a different approach, if not chosen an altogether different platform than Excel for this particular application.

I've noticed some of the Named Ranges are not referenced so apparently they were created and abandoned, but not deleted. I was hoping your tool could help me identify which ones could be safely removed, but I do understand your point – a lot of development work to modify a tool only to locate instances that could have been designed better.

Again, please accept my compliments on an excellent tool, and if you know of any applications that will find Name Ranges identified in ControlSource or RowSource properties it would be greatly appreciated.

 


Comment by: Jan Karel Pieterse (3/8/2013 1:24:02 PM)

Hi Rudolf,

It shouldn't be too hard to write a bit of code that traverses the controls of your userforms to extract their Controlsources and list them on a worksheet somewhere in your file.

If you prepend them with an equal sign to make them a formula, Name Manager will detect they are used and then you can safely remove all others.

 


Comment by: david Linnebur (3/11/2013 11:57:51 AM)

I am trying to create a two dimensional array in memory using the name manager. The dimensions are 25 x 25, but the largest I can get the name manager to accept is 21 x 25. Everytme I go above that I get the "bell" audio but no error message. Does the name manager have a size limit that is causing this?

 


Comment by: Jan Karel Pieterse (3/11/2013 3:34:19 PM)

Hi David,

The refersto string of a defined name cannot contain more than approximately 245 characters, perhaps that is the cause of your problem?

 


Comment by: David Linnebur (3/11/2013 5:54:17 PM)

Jan,

Thanks, but I don't think that is my problem. I know the syntax rules state a name cannot exceed 255 characters, but my problem is creating a constant (two dimensional array) in the "Refers To" block and I exceed 255 character long before I enter a 2-D array of 21 x 25, which works. Essetionaly, I am inserting interest rates where the first row has 25 entries and the first number is 0.025 and the remaing 24 are zeros, the next row has 0.025,0.025 then the remaining 23 are zeros. This goes on until the 25th row has 25 of 0.025. I then use "," to define the columns and ";" to define the rows and then add "={" at the front and "}" at the end to define an array. I just can't get past 21 rows of this type of data.

 


Comment by: Jan Karel Pieterse (3/12/2013 8:50:27 AM)

Hi David,

In that case, the limit has probably been raised a bit, but not as much as you seem to need.

I would put the matrix on a sheet and point to the cells, that should work equally well.

NB. This range name should give you the same matrix:

=IF(ROW($A$1:$Y$25)>=COLUMN($A$1:$Y$25),0.025,0)

 


Comment by: Rick (3/20/2013 5:03:38 PM)

Thank you! You just saved me several hours of what would have been error prone work with the GLOBALIZATION button!

 


Comment by: Stuart Luxmore (3/26/2013 1:04:40 PM)

When using the Name Manager in the VBE Editor I find the only way I can paste the name is to right click, copy and paste. The Name Manager manual states "Selecting a name inserts that name at the current insertion point in your code." - I am using Excel 2007 on Windows XP.

 


Comment by: Jan Karel Pieterse (3/26/2013 3:21:21 PM)

Hi Stuart,

Odd, works fine on Excel 2010 and 2003. Also works fine on my Virtual machine running Windows XP and Excel 2007.

 


Comment by: John (4/23/2013 3:17:08 PM)

Sigh!

Why does it seem so difficult to just print out the names from name manager?

I just wanted a sheet of paper with the list of names in front of me so that i could go through a multi-tab workbook and follow all the links etc.

Any ideas appreciated..

 


Comment by: Jan Karel Pieterse (4/23/2013 3:40:41 PM)

Hi John,

You are not referring to the fifth button from the left of the Name Manager you can download from this page (the button is conveniently called "List" button)?

 


Comment by: John (4/23/2013 5:24:38 PM)

Hi Jan,

No. Perhaps I was not clear. I meant from Excel itself without any add-ons.

But I have solved it now, so my bad :-).

I just needed to go to a new sheet, click on F3, insert all names, and then print out that sheet.

But thanks just the same for your prompt reply!

 


Comment by: Jan Karel Pieterse (4/23/2013 5:33:11 PM)

Hi John,

Great you were able to solve your problem.

NB: I highly recommend my Name Manger! :-)

 


Comment by: Sarah Goulding (4/26/2013 11:01:19 AM)

Hi John,

I have an issue with Excel 2010 where every time I save a copy of a monthly report and open it to add data, several of my range names which use a custom formula change to refer to the name manager add in.

For example: Range Name - Month13_ATCC
Refers To - =OFFSET('A&TCC'!$B$43,1,13,countcontigrows('A&TCC'!$P$43)-2,1)

Comes up as =OFFSET('A&TCC'!$B$43,1,13,'\\UKHARFAP01\home\SGouldingCTX1\Application Data\Microsoft\AddIns\Name Manager 2007.xlam'!countcontigrows('A&TCC'!$P$43)-2,1)

I don't understand why it should refer to the add in file, I didn't use the add in to create the range name, in fact I downloaded the add in AFTER creating the range name! It only happens on the range names that use a custom formula.

Can you explain why it would do this and how I go about rectifying the issue so that I don't have to amend my range names every time I update the report?

Many thanks


 


Comment by: Jan Karel Pieterse (4/26/2013 2:25:59 PM)

Hi Sarah,

Perhaps you do not need this convoluted dynamic range name using a UDF to begin with. If you convert the range in question to a table (Format as Table on the Home tab), any formula using that range will automatically expand when the table grows.

 


Comment by: Sarah Goulding (4/26/2013 3:47:46 PM)

Hi Jan,

The range is counting the rows in a pivot table on one of the report display pages so unfortunately the suggestion above would not work.

Thanks

Sarah

 


Comment by: Jan Karel Pieterse (4/26/2013 5:07:45 PM)

Hi Sarah,

Where did you find this countcontigrows function exactly?

 


Comment by: Sarah Goulding (4/29/2013 10:09:48 AM)

Hi Jan,

It was a long time ago and it wasn't me that actually found it, it was a predecessor of mine but it came from this website.

http://www.decisionmodels.com/downloads.htm

Regards

Sarah

 


Comment by: Jan Karel Pieterse (4/29/2013 2:08:57 PM)

Hi Sarah,

I suggest you contact Charles Williams (at decisionmodels) about the problem, since this function is part of his FastExcel.

 


Comment by: Eva (6/4/2013 4:06:00 PM)

I installed the add-in in Excel 2010, and it appears on the "add-in available" list, but I not able to run it.

 


Comment by: Jan Karel Pieterse (6/4/2013 4:25:44 PM)

Hi Eva,

If you click the "Go" button on that screen, is the add-in listed there (and checked)?

 


Comment by: Ken Kast (6/8/2013 8:30:48 PM)

I installed Name Manager 4.2 in Mac Excel 2011. There is no pointer to it on the Tools menu or on the ribbon. More importantly, when I execute it with ctrl-shift-n, the Name Manager dialog opens with panels on it overlapping, i.e., there are problems with the layout in the window. It's like the window is smaller than was used when the UI was designed. Most importantly, I get an error message dialog from Visual Basic saying: Run-time error '53'/File not found.

BTW, the automatic install spreadsheet crashed, so I put the add-in in /Users/Ken/Documents/Microsoft User Data/Excel/Startup.

 


Comment by: Jan Karel Pieterse (6/10/2013 9:44:40 AM)

Hi Ken,

Version 4.2 does not work on MAC Excel, but v3.2 should (also available from this page).

 


Comment by: gerdami (6/11/2013 9:54:02 AM)

When I change the status of a name from global to local-to-a-sheet, formulas that used the global names are not updated accordingly.

 


Comment by: Jan Karel Pieterse (6/11/2013 12:33:17 PM)

Hi gerdami,

I understand your concern.

I find it rather unusual to have a formula in one sheet point to a locally defined rangename on another sheet, which is why we have not included a full rename when localising a global name. Also, it would require a check to see whether the local name is the only one in the workbook or not.
However, you can get what you need by renaming the global name instead of localising it. Just click on the name and hit F2.

 


Comment by: gerdami (6/11/2013 1:55:36 PM)

Hi Jan Karel,
I tested your workaround and it worked perfectly.

However, it is not that unusual to address local range names from other sheets. The good thing with local names is that you can create "cloned" local names by simply copying a sheet.

Example: I have a sheet named "Monthly" with monthly data queries. I named the whole sheet "Monthly!data" while I have also a column named "Monthly!codes" and a row named "Monthly!dates". I made a copy of this sheet and renamed it "Annual" and changed the query accordingly.

In a summary sheet, I retrieve wanted data with
INDEX(Monthtly!data, MATCH(MyCode, Monthly!codes, 0), MATCH(MyDate1, Monthly!Dates, 0)) and also with
INDEX(Annual!data, MATCH(MyCode, Annual!codes, 0), MATCH(MyDate2, Annual!dates, 0))

Anyway, thanks for this awesome NameManager.

 


Comment by: Jan Karel Pieterse (6/11/2013 9:12:40 PM)

Hi gerdami,

That makes sense indeed. Glad I could help!

 


Comment by: Sean Picht (8/1/2013 7:03:11 PM)

This is exactly what I needed. the Name Manager in Excel was not giving me anywhere near the number of names that this add-in has shown. I had one file that had 26k names in it, most of which were errors. I imagine the file that I inherited had been around and around and modified by multiple users. I cleared it out and reset my print ranges and everything is great. Thank you!

 


Comment by: Conan (8/9/2013 9:01:10 PM)

Jan, Thanks for Name Manager. It is very useful

I want a keyboard shortcut to the Name box (http://www.cpearson.com/Excel/NameBoxShortcut.htm)

Name Manager defaults to [Ctrl] + [Shift] + N
I changed it to [Ctrl] + [Shift] + M

Now both open Name Manager.

How do I remove custom keyboard shortcuts? Registry tweak (I'm comfortable with them)? Behind the scenes of the VBA module somewhere?

Thanks for any help you can provide,

Conan

 


Comment by: Jan Karel Pieterse (8/11/2013 4:56:22 PM)

Hi Conan,

Perhaps restarting Excel fixes the control+shift+N shortcut?

 


Comment by: Conan (8/16/2013 1:32:04 AM)

JKP,

That seems to be the case. That day I made the change, both shortcuts were opening Name Manager. Now, just the new shortcut opens the manager.

Thanks again,

Conan

 


Comment by: Bryn Baker (9/9/2013 2:28:09 PM)

Love the tool, and whenever I need it, I need it a LOT!

When a sheet is copied to another workbook , it takes wih it names from its source into the target. (I suspect this behaviour is variable, and it seems prompting me is optional).

It sometimes also adds local names in the target where there was a perfectly good global one with the same refersTo.

Finally when it warns it doesn't allow Cancel of copy. You can't fix the last issue (!) but have you advice on reducing the problem, or is there VBA to rationalise local names that are effectively the same as global ones? As I write, I'm dealing with hundreds of them!

I could probably write the VBA but I still find the name object rather confusing. If I succeed (clues welcome) I'll offer the result to the world.

Thanks.

 


Comment by: Jan Karel Pieterse (9/9/2013 3:11:29 PM)

Hi Bryn,

All I can think of is a way to use NM to get rid of the 'duplicates': Filter the rangenames on duplicate global/local AND on local names. The select all of them and hit the delete button.

 


Comment by: Bryn Baker (9/9/2013 4:26:14 PM)

Thanks for the prompt response; it sounds suitable. I've been manually deleting names like mad all afternoon!

I've found since posting (always the way) your observatons on the bug(s) in Name object; that explains part of my confusion.

 


Comment by: Bryn Baker (9/9/2013 4:45:25 PM)

That was perfect; 6 more sheets copied, three dozen more names to delete, gone in a click. Most grateful!

 


Comment by: Topher (9/11/2013 11:33:07 PM)

Hi,

Mac user here. When I click the "Multi" button, I get the following error: http://i.imgur.com/LcPq2JX.png and it crashes (no app buttons work, can only close window with red button) Using the latest 14.3.7 version of Excel on OS X.7.5.

Are you even supporting the Mac version still?

Thanks for a very useful piece of software, in any case.

-T

 


Comment by: Jan Karel Pieterse (9/12/2013 10:43:25 AM)

Hi Topher,

I'm sorry to hear that. Unfortunately I do not own a MAC so I cannot troubleshoot your problem.
Will ask around.

 


Comment by: Claire (9/18/2013 7:01:32 PM)

A million times Thank You for this utility!!!

I faced deleting hundreds of invalid references today. I was ready to stab myself in the eyes.

Thank you so very much!!!

 


Comment by: Jan Karel Pieterse (9/18/2013 7:25:28 PM)

Hi Claire,

You're welcome!

 


Comment by: Lars Jensen (10/9/2013 5:31:38 PM)

Hi.

Good application, but I have trouble with two names.
_xlfn.IFERROR and xlfn.SUMIFS both refering to =#NAME?. I cannot rename or delete these items.

Why not?

 


Comment by: Jan Karel Pieterse (10/9/2013 6:05:43 PM)

Hi Lars,

Those are range names managed by Excel which cannot be removed. They are there to support backward compatibility with Excel 2003 and are added if you use functions which were added after Excel 2003.

 


Comment by: Al S. Bacon, III (10/11/2013 6:13:59 PM)

Will the Name Manager Add In allow me to make scope changes in Excel 2010. I have it loaded but I am not quite sure how to navigate this feature in the Add In. I have created numerous names in Global Format but I want them to be select just to the worksheet I am currently working on.

 


Comment by: Jan Karel Pieterse (10/12/2013 4:47:04 PM)

Hi Al,

Yes, use the button with the tiny globe to make a name global. The crossed out globe localises the name(s).

 


Comment by: Anurag (10/30/2013 7:41:27 PM)

Hi

How do I change the shortcut key. I need to modify it as it is interrupting with another shortcut that I have.

Thanks

 


Comment by: Jan Karel Pieterse (10/31/2013 11:36:15 AM)

Hi Anurag,

Look in the Ribbon on the formulas tab (far right) or if you are using Excel 2003 or older Tools, Name Manager.

There is a button (menu entry in 2003) to change the shortcutkey.

 


Comment by: Ed Mooney (11/5/2013 5:16:12 PM)

Thank you! The Names were not available to delete without this add-on. The add-on worked great!

 


Comment by: Tim (11/25/2013 1:09:29 AM)

Hi, Jan,

This is probably a really dumb question, but . . .

I am running Excel 2010 under Windows 8, installed Name Manager and confirmed that the Excel Add-In Manager recognizes it. However, I don't see how I "start" Name Manager in order to use it instead of Excel's name manager.

Thank you!

 


Comment by: Jan Karel Pieterse (11/25/2013 9:08:13 AM)

Hi Tim,

After restarting Excel, look in the add-ins list (alt+t, i). If Name Manager isn't checked, check it. The button should show up in the formula tab.

 


Comment by: Tim (11/25/2013 2:26:06 PM)

Follow up comment to my question about where I find Name Manager in the Excel menus:

"I see," and what a beautiful sight/product it is! :)

 


Comment by: Rowland (12/4/2013 9:33:26 PM)

Hello, I'm trying to work with table names in the name manager but they are not visible. Is this feature available in the current version? Thanks

 


Comment by: Jan Karel Pieterse (12/5/2013 7:50:34 AM)

Hi Rowland,

Table names do not belong to the range names collection and are therefore not "true" range names. This is why Name Manager does not show them.

 


Comment by: Jerome (12/18/2013 9:06:22 PM)

Thank you so much for providing this "life saving" add-in. It really made my work with Names much-much easier!!!

 


Comment by: Peter Campin (12/25/2013 9:18:30 PM)

Hello,

I have been a hobby VBA user for 20 years and always appreciate those who share their code.

I just used your highlight range function and could not be more pleased with how it functions -- a perfect fit for my need.

Thank you for sharing your VBA creation!

Sincerely,
Peter

 


Comment by: Jan Karel Pieterse (12/25/2013 10:07:35 PM)

Hi Peter,

You're welcome!

 


Comment by: Mason Guy (1/7/2014 9:59:07 PM)

I'm using a worksheet with 20K+ nameed cells. I'm getting an "out of memory" error. Is there anything I can do?

 


Comment by: Jan Karel Pieterse (1/9/2014 10:53:35 AM)

Hi Mason,

Are those range names in use? If not it isn't too hard to get rid of them.
Alternatively, if you like you can email that file to me and I'll try to scrub it.

 


Comment by: Randy (1/21/2014 7:03:28 PM)

Your range name add-in tool was very helpful. Thank you for sharing your talent. I got a garbage file from someone, somewhere in my company - over 5,000+ unused range names deleted.

 


Comment by: Feiga (2/7/2014 5:58:41 PM)

Thank you so much for providing this very helpful NameManager add-in. I have been trying to eliminate a rogue link for a very long time. Your name add-in did the trick.

 


Comment by: Don Fannon (2/10/2014 6:27:25 PM)

I deeply appreciate your sharing the "old version" that still works for Mac Excel 2011. Names make formulas so much clearer and easier to debug and your tool makes managing those names possible. My hat is off to you, JKP-ers (all the more since MS should have included this in the first place!)

 


Comment by: Jan Karel Pieterse (2/10/2014 6:39:24 PM)

Hi Don,

You're welcome!

 


Comment by: Paul Downs (2/11/2014 9:50:35 PM)

Name manager is awesome - I just used 3.2 in Mac Excel 2011 and it saved me from hanging myself trying to manage a list of names. Is there anyway I can make a small contribution to show my gratitude?

 


Comment by: Jan Karel Pieterse (2/12/2014 10:50:34 AM)

Hi Paul,

You're welcome!
If you want to make a donation, I have a paypal button here:
http://www.jkp-ads.com/Download.asp

 


Comment by: Andy Boston (2/25/2014 5:51:15 PM)

Having just migrated to Mac and finding that Excel 2011 is a step backwards I have downloaded Name Manager. Used v 3.2 because 4.2 doesn't work (would be useful to have a comment at the top for Mac users about this).

The xla installed Ok but a very basic question. Where is the icon/menu item to fire up name manager?

thanks

Andy

 


Comment by: Jan Karel Pieterse (2/26/2014 7:26:42 AM)

Hi Andy,

This is a bit hard to answer for me since I do not own any Mac. But in Excel 2003 the menu entry should appear in the Tools menu. Not sure if Mac Excel has similar menu?

 


Comment by: Andy Boston (2/26/2014 4:37:37 PM)

OK Fixed it. I uninstalled 3.2 and went back to v 2.3. To be fair that's the version you recommend for Macs, I was following a previous comment that said v3.2 worked for them, but it clearly doesn't for me.

Thanks for a useful utility - surprising MS don't have this - I don't know how else you can localise or globalise names.

Andy

 


Comment by: Jan Karel Pieterse (2/26/2014 4:53:15 PM)

Hi Andy,

You're welcome!

 


Comment by: Rachel (3/6/2014 4:59:52 PM)

I love love love this tool!

But I can't seem to see anything about what FastExcel is and how I can get it...I get the notification that certain tools in Name Manager only are available with FastExcel.

What is FastExcel? Where can I get it?

 


Comment by: Jan Karel Pieterse (3/6/2014 5:23:32 PM)

Hi Rachel,

Thanks! :-)
FastExcel is sold by my co-author of the tool Charles Williams: http:\\decisionmodels.com

 


Comment by: Dan (3/7/2014 12:51:22 AM)

Found your marvelous add-in from another forum ... I needed a quick way to print out the list for some sheet auditing. So far so good (install had to be done manually but no big deal .. dialog instructions were great!) I'm looking forward to doing some more exploration with the tool .. it would appear that it will greatly facilitate some of my auditing!

Dan

 


Comment by: Josh (3/7/2014 4:07:05 AM)

Thanks goodness! This tool reallys help me to clean those hidden names that not even inhouse name manager can find.

My only question is that how were those names arrive when MS inhouse name manager cant detect them at all.

Thanks

 


Comment by: Jan Karel Pieterse (3/7/2014 9:54:53 PM)

Hi Josh,

The built-in Name manager does ot show them, because they are -well- hidden. :-)

 


Comment by: Keith Kenny (5/12/2014 2:44:31 PM)

This works fine, many thanks.
I have one annoyance with it installed - when I start Excel I get the same message three times about an object could not be loaded because it could not be found on the machine.
Everything runs fine though and this is Office 2010 under Windows 7 64 bit.
Other than that, very happy!

 


Comment by: Jan Karel Pieterse (5/12/2014 5:27:18 PM)

Hi Keith,

That is an error you should not be getting.
If you find some time, try a detect and repair of Office setup (control Panel).

 


Comment by: David Miley (5/14/2014 3:45:24 PM)

Love it!

What technique do you use to determine if a Name is an external reference?

 


Comment by: Jan Karel Pieterse (5/15/2014 7:57:39 AM)

Hi David,

I use a test like:

If TheName.RefersTo Like "*[[]*[]]*[!]*") Or TheName.RefersTo Like "*.xl?!*") _
Or TheName.RefersTo Like "*.xl??!*" Then
'External
Else
'Not External
End If

 


Comment by: David Miley (5/15/2014 1:53:35 PM)

Thanks Jan!

I am only a casual user when it comes to regular expressions and don't usually think about using them as a first approach.

Thanks for the response!

David

 


Comment by: Jan Karel Pieterse (5/16/2014 12:38:46 AM)

Hi David,

These aren't true regex-es, and you'll find the help about the Like operator is quite good.

 


Comment by: Gabriele (5/23/2014 12:48:02 PM)

thanks a lot...! it solve my issues regarding hidden names completely!
Great add in!

 


Comment by: Heather (6/3/2014 1:20:43 AM)

Name Manager RULES!!! Has so much functionality that one would think Excel should have natively... A true life-saver for complex workbooks!

 


Comment by: Jan Karel Pieterse (6/3/2014 7:22:25 AM)

Hi Heather,

Thank you!

 


Comment by: Stephen Ma (6/17/2014 11:43:11 PM)

The Name Manager 4.2 works wonder. I provide informal support to a limited number of Excel users in my office. One of the file I created has a number of names with #REF! as value and I cannot even trace back to where the names come from. With your Add-On, I was able to delete all these names in one click. I receive praises which should be credit to all of you developers!!

Stephen Ma

 


Comment by: Pradeep (7/18/2014 8:01:37 AM)

Your add-in is indeed useful. (1) I was looking to download all named ranges in order to create a macro which recreates the names again. We prefer macro so as to dynamically set size of each name range. Your add-in allowed easy downloading of named ranges as an excel sheet. With such an elaborate listing possible, I feel it is easy to create a macro simultaneously wherein definitions of name ranges are specified. Would you be able to provide such an option? (2) Opening a complex excel file usually takes time and progress is indicated on Excel desktop poster in the form of loading percentage. But your add-in interferes with the poster and does not allow loading percentage to be seen. Could you address this issue? I have a feeling that excel loading time also increased due to add-in in case of my 100MB file.

Thanks,
Pradeep.

 


Comment by: Shabs (8/25/2014 3:14:27 PM)

My excel 2011 (mac) keeps on shutting down when i try to install the add-in via the setup file... can you help?

 


Comment by: Jan Karel Pieterse (8/25/2014 3:25:39 PM)

Hi Shabs,

Perhaps you downloaded the version intended for Windows Excel? If you did DL the MAC version, the associated documentation file does contain a description on how to manually install the file.

BTW: There is no need to install the addin to use it, you can also just open it when you need it.

 


Comment by: Shabs (8/25/2014 3:41:46 PM)

Hi Jan,

Thanks for the quick response.

Not sure if I did install the wrong add-n but just for clarity I downloaded: Name Manager for Excel 2007, 2010 and 2013. And it has a xlam file so was pretty sure it was the correct add-in. I use this add-in a work (PC) all the time and quite used to it. But it just doesn't install on my Mac.

Hope this clarifies my question?
Thanks

 


Comment by: Jan Karel Pieterse (8/25/2014 4:54:15 PM)

Hi Shabs,

That is not the right version for Mac Excel. Please look for a heading called "Old versions", there is a download there specific for Mac Excel.

 


Comment by: Lex Harrison (9/9/2014 12:19:31 PM)

Your name manager add-on for Excel looks very useful indeed, but although I have successfully installed on Office Excel for Mac 2011 ver 14.4.4 I have not yet succeeded in make a change to the definition of a named range. Clicking the RefersTo box and then making the change brings up the confirmation dialogue box. On clicking "yes" to accept the change is not made.

Any suggestions?

 


Comment by: Jan Karel Pieterse (9/9/2014 9:26:16 PM)

Hi Lex,

Hmm. I'm afraid I am unable to try this, since I do not own a Mac!

 


Comment by: JohnnyHerz (9/28/2014 2:12:36 AM)

Thank You!
Thank You!
Thank You!
Thank You!
Thank You!

Mac Excel 2011 user

so excited to start using this tool!

 


Comment by: Eric (10/5/2014 6:36:35 AM)

Thanks so much for this add-in. Very helpful.

 


Comment by: Adrian (10/21/2014 5:12:02 AM)

I'm very bummed you didn't update v4.2 for Mac, but I am looking forward to installing v3.2. During my initial attempt I got the "Compile Error in hidden module, fxlNameManager" error. I know you don't support the Mac version, but do you have any suggestions for getting over that error? I'm on Office 2011 for Mac (v14.4.5, build 141003, latest as at writing) and "Detect and Repair" is decidedly absent from my menus.

 


Comment by: Jan Karel Pieterse (10/21/2014 8:16:28 AM)

Hi Adrian,

I'm afraid there isn't much I can do to help! I have no Mac available at all, so I cannot try or suggest anything.

 


Comment by: Sujoy (11/4/2014 9:54:20 AM)

Cool!

 


Comment by: DM/Diddy (11/4/2014 10:18:07 PM)

Hi Jan,

BIG fan and heavy user of NameManger here! Thanks for your hard work.

I installed the Name Manager 2007.xlam file from the "Name Manager for Excel 2007, 2010 and 2013" link above. However, the build is 645, not 644 as you specify near that link. Hopefully this is just an oversight - I want to make sure I have the 2010 version.

Also, when I use the "Is Used?" button, I don't get the results in the treeview as shown above and in the doc. Instead I get a regular listbox. I assume this is because I'm missing a library somewhere?

Finally, I have a Excel file that gives me an "Overflow" error whenever I use the "Is Used?" function. It's less than 1Mb in file size, but it uses lots of VBA, PW protection, hidden objects, etc. It's proprietary, but I'd be happy to track down the bug if you'd like.

Thanks!

 


Comment by: amanda redwine (11/7/2014 6:04:55 PM)

I selected about a hundred links with errors and asked the tool to delete them and it's just churning away. Task manager says around 29 CPUs are being used, so I guess that means it's still working? how do I know when to go nuclear with the Ctrl-Alt-Del?

 


Comment by: Jan Karel Pieterse (11/7/2014 6:28:31 PM)

Hi Amanda,

I would expect the delete to be instantanious, but this depends on whether or not you chose to have cells with formulas updated with the formula of the deleted names. Also it depends on the number of range names in your file.

 


Comment by: Robert Perkins (11/9/2014 3:38:22 PM)

Thanks. It took out all but one of a few dozen name links. The program did freeze when I tried doing too many at a time, and I had to select 3-4 to avoid that, and then one by one till I found the one that wouldnt delete as it was freezing the program as well.

 


Comment by: Jan Karel Pieterse (11/10/2014 11:43:33 AM)

Hi David,

The screenshot is wrong indeed. We decided not to use the treeview because some users reported problems with it.

In order to track down the overflow error we'd need a sanitized copy of that file.

 


Comment by: DM/Diddy (11/10/2014 7:47:39 PM)

Jan,

I tracked down part of the Overflow error. I didn't see it before because my project was protected.

I have a sheet with column C hidden. The overflow happens in the code window of a worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        'do something
    End If
End Sub

It crashes on the "If" line. Trace-back shows <non-basic code> then <hidden code>.

Typing ?target.Address in the immediate window gives:
$A:$B,$D:$XFD

Hope this helps.

Dave

 


Comment by: Jan Karel Pieterse (11/11/2014 9:57:09 AM)

Hi David,

This is a programming error. The Count property returns a Long result, but the number of cells you are counting is larger than a Long can hold. In Excel 2007 and up you should be using the CountLarge property instead.

 


Comment by: gerdami (11/14/2014 4:11:57 PM)

Filter for name with errors seems to be based on the # sign.

However, if I create a name for a Table / List, it will refer to something like =Table1[#All], which is actually not an error.

Anyway, thanks again for this wonderful tool.

Version 4.2 build 635.


 


Comment by: Jan Karel Pieterse (11/17/2014 9:48:13 AM)

Hi Gerdami,

It does not flag that as an error on my version, which build of NM are you using?

 


Comment by: gerdami (11/17/2014 2:51:45 PM)

Just upgraded from Version 4.2 build 635 to V4.3 build 646 and the issue is fixed. Thanks.

 


Comment by: David (11/20/2014 6:15:05 PM)

Hi,

I have a number of workbooks which contain a name called 'Print_Area'. Note that this is not preceded by a sheet name, so it is not the normal print area definition for a sheet. Using the Name Manager I cannot edit or delete it, nor can I do this in VBA. Do you know how to remove this (it contains invalid references)?

Thankyou.

 


Comment by: Jan Karel Pieterse (11/20/2014 8:17:55 PM)

Hi David,

You can probably delete them using Excel's own Name Manager.

 


Comment by: Max (11/27/2014 3:00:27 PM)

Hi,

I have a file with more than 100.000 entry ... the "Name Manager" of Office 2013 won't open, and yours crash with "insufficient memory".

Do you have an idea ? I use the 4.3.

Thanks.

 


Comment by: Jan Karel Pieterse (11/27/2014 3:44:36 PM)

Hi Max,

Would you be willing to send that file to me?

 


Comment by: Max (11/27/2014 3:54:46 PM)

Yes no problem, the file with no data also contain the 100.000 references.

 


Comment by: Mike Eardley (12/5/2014 11:27:37 AM)

Hi, I just installed Name Manager 4.3 and it now only allows me to select the top most name in the list, if I select below that, say item 5 or 7 it goes straight back o selecting the first item again? (although the names below appear to work I cant edit or delete them, as the selection always changes back to the 2st item?)

 


Comment by: Jan Karel Pieterse (12/5/2014 12:06:50 PM)

Hi Mike,

Thank you for reporting this, I have just uploaded a fix for the issue.

 


Comment by: Mike Eardley (12/5/2014 12:33:50 PM)

Thanks, however, it still does not work, looking at the uploads it looks like the earlier version has a date of 5/12/14 now but the Excel 2010 version is still at 2/12/14? which is the one I need?

Regards Mike

 


Comment by: Jan Karel Pieterse (12/5/2014 2:36:47 PM)

Hi Mike,

Perhaps you were quicker to get to my site than my webserver was to update its caches :-)
It should be there now.

 


Comment by: David (12/10/2014 3:35:36 AM)

Running Name Manager 2.3 on Office 2004, Mac OS 10.4.11. Clicking the 'Add' button returns:

Run-time error '1004':
Method 'Show' of object 'Dialog' failed.

Tried using Name Manager because attempting to Define Name in any Excel workbook crashes Excel. However, Define Name does work on Excel 4.0 worksheets.

 


Comment by: Jan Karel Pieterse (12/10/2014 9:23:13 AM)

Hi David,

I'm really sorry to hear that. Unfortunately, for now, we are not investing in MAC development for NM.

It does sound as if either your Excel installation has a problem or your workbook.

 


Comment by: Pete A (12/22/2014 7:20:00 PM)

I installed as directed in office 365 (2013) excel, on a 64 bit windows 8.1 machine.

While it shows as installed in the File/Options window, the icon does not appear anywhere in the ribbon (instructions say it will be in the Formulas ribbon. But only the original Names Manager from MS is there.

Thanks!
Pete A

 


Comment by: Jan Karel Pieterse (12/22/2014 10:15:42 PM)

Hi Pete,

Excel 2013 is a bit quirky sometimes. Try closing all Excel windows but one, uncheck the add-in, close and reopen Excel and check it again. Hopefully that helps.

 


Comment by: Patrice (1/25/2015 5:02:42 PM)

Just installed Name Manager but it don't work. :(

When launching Ecel with only Name Manager activated, I get
"Erreur système &H80004005. not specified error" 3 times, then
"compilation error in hidden module CFormResizer" once

Name Manager.xla 2015/01/15 09:52
Windows 7 French SP1
MS Office 2003 French SP3

 


Comment by: Jan Karel Pieterse (1/27/2015 7:22:56 AM)

Hi Patrice,

Please try these two things:
- Delete all files with extension .exd from your system (they are in the temp folders)
- if that fails, repair Office.

 


Comment by: Patrice (1/27/2015 7:58:38 PM)

Hi Jan,

Sorry, I have seen "Solving Compile errors related to Name Manager" only after posting my message.
It solved the problem.
Thanks

 


Comment by: Jan Karel Pieterse (1/28/2015 6:46:41 AM)

Hi Patrice,

Glad you were able to resolve the issue.

 


Comment by: Liz (3/13/2015 9:47:08 PM)

I love your add-in and use it frequently. However, I have a name "_xlfn.IFERROR" that will not delete. I imagine it is due to the underscore at the beginning. I have tried renaming and it simply creates a new name and leaves the old one as well. Any thoughts on how to delete a name with invalid characters?

 


Comment by: Jan Karel Pieterse (3/16/2015 9:36:37 AM)

Hi Liz,

That is a name added by Excel which cannot be deleted. It has something to do with functions that became available in Excel 2007 and are there for backwards compatibility.

 


Comment by: per (3/16/2015 11:20:25 PM)

i have a problem with "compile error in hidden module: CMenuHandler" when opening excel or switching sheets

windows 7 64 bit, office 2010 64 bit, installed by button, repaired office, installed manually, repaired office, still no go

ideas?

great program btw

 


Comment by: Jan Karel Pieterse (3/17/2015 6:18:05 AM)

Hi Per,

This should not happen. Try turning off "break in class modules" in the options of the VBA editor.

 


Comment by: Per (3/17/2015 6:02:19 PM)

yeah, <<Try turning off "break in class modules">> was not set, was set to unhandled errors,

i am on Office 32 bit not 64, would that have an impact?

 


Comment by: Jan Karel Pieterse (3/17/2015 6:16:11 PM)

Hi Per,

No bitness makes no difference. Name Manager runs on both 32 and 64 bit Office.
I'm afraid the problem is with your installation somehow.

Does it work if you logon as a different user?

 


Comment by: Maxime Gauthier (4/7/2015 8:03:23 PM)

Hello, thank you for the add-in, it has definitely helped me manage named selections in Excel, I am glad I found this.

 


Comment by: Jan Karel Pieterse (4/8/2015 8:05:40 AM)

Hi Maxime,

You're welcome!

 


Comment by: Peter Puchner (5/30/2015 1:44:28 AM)

Hi Jan,
I am trying to get the module working, on Excel for Mac 2011. I added the code using the tools/add-ins method as the button crashed my Excel, it runs but then halts with a 'run time error '53'' then 'File not found: User32'
Please help!

 


Comment by: Jan Karel Pieterse (6/1/2015 8:39:39 AM)

Hi Peter,

Which version did you download?

 


Comment by: Brian Pfeffer (6/4/2015 10:19:08 PM)

Hi Jan,

I am trying to install Name Manager for Excel 2007, 2010 and 2013 V4.3 (Build 650, Feb 17 2015, downloaded: 65855 times) for my Dell Computer which uses Windows 7.0 Professional along with Microsoft Office 2007 and I keep getting an error.

Every time I add the Add-In to Excel 2007 it crashes and I get the following error message:

Microsoft Office Excel has stopped working
Windows can check online for a solution to the problem
--> Check online for a solution and close the program
--> Close the program

View Problem Details

Problem signature:
Problem Event Name: APPCRASH
Application Name: EXCEL.EXE
Application Version: 12.0.6341.5001
Application Timestamp: 49a5e049
Fault Module Name: VBE6.DLL
Fault Module Version: 6.5.10.20
Fault Module Timestamp: 45187577
Exception Code: c0000005
Exception Offset: 000a5a59
OS Version: 6.1.7601.2.1.0.256.48
Locale ID: 1033

Additional information about the problem:
LCID: 1033
Brand: Office12Crash
skulcid: 1033

Read our privacy statement online:
http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409

If the online privacy statement is not available, please read our privacy statement offline:
C:\Windows\system32\en-US\erofflps.txt

Any idea what could be causing this problem. I have installed it no problem on my other Windows 7.0 machines but they use Office 2013.

Appreciate any help or suggestions you can provide.
Thank You,
Brian Pfeffer

 


Comment by: pggirvin (6/5/2015 1:03:15 AM)

wow, this is extremely useful and powerful. thank you, thank you, thank you. This will save me a few hours even just today, plus eliminate lots of possible editing errors (changing named ranges from global to local).

 


Comment by: Richard (6/7/2015 3:01:16 PM)

Hi Jan,

I have tried to install name manager 2007 (Version 2.3) in Excel 2007, but both manual and automatic installation give error message after connecting the add-in:

I get the error:
Microsoft Excel cannot access the file c:\users\RvK\addIn\Name Manager 2007.xlam
There are several possible reasons:
The file name or path does not exist
The file is beiing used by another program
The workbook you are trying to save has the same name as a currently open workbook.

All of above reasons do not apply. The Macro have been activated and the location is trusted.

What would be the reason.

Richard

 


Comment by: Jan Karel Pieterse (6/8/2015 6:12:35 AM)

Hi Brian, Richard,

Many of the issues we get reported are due to incompatibilities and are resolved by making sure Office has all of its updates installed. Also, sometimes a reinstall (or repair) of Office helps.

 


Comment by: Riaan Vermeulen (6/10/2015 1:13:18 PM)

After a windows update on Tuesday I get the following error:

"Compile error in hidden module: CMenuHandler."

It fails on this line:

Application.CommandBars(1).FindControl(ID:=943, recursive:=True).Execute


DO you have any suggestions on how to resolve this?

 


Comment by: Jan Karel Pieterse (6/10/2015 5:08:39 PM)

Hi Riaan,

I am wondering what code you are referring to as it isn't in Name Manager as far as I know?

If it were, Name Manager is password protected and we really do not appreciate people breaking into our code.

 


Comment by: Riaan Vermeulen (6/10/2015 5:14:24 PM)

Hi Jan

When I uninstalled it and then tried to reinstalling it after getting the error message, the debugger screen opened and this line was highlighted. Was not on purpose, it just popped up, so I thought I'll send it on to you.

 


Comment by: Jan Karel Pieterse (6/11/2015 11:40:47 AM)

Hi Riaan,

OK, apologies. Odd error though, are you using the latest version?

 


Comment by: LJ Smolen (6/23/2015 11:34:39 PM)

Jan-

Love your product, have used it extensively over the past few years. Finally got my co-worker to install it, and he is getting the same errors as Brian and Richard above. Vista computer with Office 2007 installed. All current on updates. Any suggestions short of an Office re-install?

Thanks in advance.

Lee

 


Comment by: Jan Karel Pieterse (6/24/2015 11:25:18 AM)

Hi Lee,

I think making sure all updates have been applied should do the trick.

Also, try this:

Close all Office applications (Word, Outlook, Excel, ...).
Open Explorer, type in address bar: %temp% and hit enter.
Delete everything in there (they are all temporary files, should be no problem at all).
Do a search on entire C drive for .exd files and delete all of them.
Reboot system and then retry.

 


Comment by: Daniel Houle (8/1/2015 11:20:41 PM)

Hi Jan!
Hope you can help me.
I use Excel 2010.
My office updates are ok.
I deleted all files in %temp%.
Deleted all .exd files on C and rebooted system.

Here is what is happening.
The first time I open Excel, I receive this "Compile error in hidden module: CMenuHandler.... This error happens ... etc.." and (of course) I click the OK button.
I am then presented with the VBA window where the title shows ..."Name Manager 2007.xlam[arrêt] i.e. [stop]
I have to click on the blue square (Reinitialize button] to be able to proceed further.

Then it looks like your addin is working fine.
Any idea why I am stuck with this process every time I open
the first file in an Excel instance?
Merci
DAniel

 


Comment by: Riaan Vermeulen (8/3/2015 11:27:59 AM)

Hi Jan

After a lot of digging around I've found that the error message I received when trying to install Name Manager on a Windows 8 machine was down to the fact that the following two OCX files are missing from the system32 folder: MSCOMCT2.OCX & MSCOMCTL.OCX. After adding them to the system32 and SysWOW64 folder and registering them the add-in is now working again.

 


Comment by: Jan Karel Pieterse (8/3/2015 4:17:31 PM)

Hi Riaan,

Thanks for letting us know!

 


Comment by: Jan Karel Pieterse (8/3/2015 4:25:11 PM)

Hi Daniel,

Have you tried a repair of Office yet?

 


Comment by: Daniel Houle (8/3/2015 8:41:12 PM)

Hi Jan
Thanks for this quick reply.
Forgot to mention I went through the repair Office process and same errors.
One new thing: In the Add-Ins window, I unchecked the "Name Manager Utility" and all went fine. When I recheck the "Name Manager Utility" and press the OK button, I receive immediately the error "Compile error in hidden module: CMenuHandler.
Though this new "feature" might help in finding a cure ???
Thanks for taking the time for us.
Daniel

 


Comment by: Daniel Houle (8/3/2015 8:43:57 PM)

Hi Jan,
Just in case, I looked at the Riaan Vermeulen's comment and I was ok with both files in both folders.
Daniel

 


Comment by: Jan Karel Pieterse (8/4/2015 12:25:46 PM)

Hi Daniel,

I'm afraid there isn't much I can do for you, as this error indicates a problem in your Office or Windows installation.

What settings do you have in the VBA editor's Tools, Options, General tab, Error trapping section and Compile section?

 


Comment by: gerdami (8/13/2015 3:38:38 PM)

V4.3 (Build 651, September 3, 2015)
Back to the future ?
;-)

 


Comment by: Jan Karel Pieterse (8/13/2015 4:01:15 PM)

Hi Gerdami,

Well spotted :-)
I fixed the dates.

 


Comment by: Philippe Morel (8/14/2015 1:29:30 PM)

Hi Jan,
I am trying to install Name Manager for Excel 2007, 2010 and 2013 V4.3 (Build 651, August 3, 2015, downloaded: 67552 times) for my Dell Computer which uses Windows 7.0 Professional along with Microsoft Office 2007 and I keep getting the same proble as described by Brian Pfeffer (6/4/2015 10:19:08 PM) :
Every time I add the Add-In to Excel 2007 it crashes and I get the following error message:
Microsoft Office Excel has stopped working
I've tried your suggestion to remove %temp%, repaired Office ... Unfortunately with no success.
Common with Brian is DELL computer and Windows 7.0 Professional
Another observation : All installed (and working) add-ins are in C:\Program Files (x86\ ... on my computer, NOT in C:\Users\...\AppData\Roaming\Microsoft\AddIns

Feedback are so positive on your add-in that I feel frustrated no to be able to install it !!

 


Comment by: Jan Karel Pieterse (8/17/2015 10:23:02 AM)

Hi Philippe,

I'm sorry to hear you are having problems. I'll get in touch through email, see if we can find the root cause.

 


Comment by: michael (9/1/2015 6:05:36 PM)

Hi Jan,

I am having the same problem as Philippe mentioned on 8/14. Using Office 2007, and same error, Excel crashes.

Did you find a solution?

Thanks.

Michael

 


Comment by: Jan Karel Pieterse (9/2/2015 10:20:09 AM)

Hi Micheal,

We're working on it!

 


Comment by: Susanne (9/3/2015 9:28:07 AM)

Hi Jan,

I am having the same issues as Philippe and Michael.
No Dell computer, but an Acer with Windows 7 64bit.
Excel crashes every time I try to activate this Add-In.

Susanne

 


Comment by: PhilUK (9/4/2015 3:34:58 PM)

I have used your NameManager addin for a number of years with huge success. I recently downloaded your NameManager v4.3 (for 2007). When I try to install the addin (using Windows 7, Office 2007) Excel 'crashes' with the error message
"Microsoft Office has stopped working - Windows can try to recover your information and restart the program"
I can successfully install v4.3 for Excel 2003, using the utility from the Addins Tab.
Have other users had problems with v4.3 for 2007?

 


Comment by: Jan Karel Pieterse (9/7/2015 9:59:45 AM)

Hi Phil,

Can you please try the current version? I have changed the protection method slightly.

 


Comment by: mitch (9/10/2015 4:01:29 PM)

I desperately want this to work , but it seems too buggy to me.
I am running excel 2013 and have a worksheet with almost 1000 named ranges. The filter to show only ranges on the active sheet does not work reliably.
I have one more than one occasion tried to add a new range , only to have name manager crash leaving the add dialog showing with no way of closing it , aside from closing the worksheet and starting over

 


Comment by: Jan Karel Pieterse (9/11/2015 11:41:38 AM)

Hi Mitch,

The add name problem is very likely caused by a messagebox hiding behind the form (an Excel 2013 problem). I'll write this down as something that needs fixing :-)

In what way is the activesheet filter not working as expected?

 


Comment by: mitch (9/11/2015 4:30:22 PM)

Jan

no the activesheet filter does not seem to work as expected at all.

When selecting "refer to activesheet" the "refers to" results show ranges that are on all other sheets, even if I select other sheets the results are the same

Also, there was no messagebox hidden , in fact I was able to close the main name manager dialog and the "add" dialog remained but could not be dismissed.

 


Comment by: Jan Karel Pieterse (9/11/2015 4:42:02 PM)

Hi Mitch,

OK, thanks. I agree the refer to Activesheet filter is incorrect. ALso the Local to activesheet seems to misbehave.

A workaround is to select all cells of a sheet and use the refer to selected cells filter.

What steps do I need to take to repro the add name problem?

 


Comment by: PhilUK (9/16/2015 2:50:00 PM)

Hi Jan
Thank you for your response on 7th September, I downloaded the file again today (apologies for the delay) as you suggested and tried to install v4.3 (2007) again but had the same problem as I described previously: "Microsoft Office has stopped working - Windows can try to recover your information and restart the program"
While I am happy to continue using the 2003 version for now it seems a shame that I cannot install the 2007 version.
Meanwhile thank you for sharing this wonderful utility, it has been first on my list of installations in all my recent work assignments

 


Comment by: Jan Karel Pieterse (9/18/2015 11:28:35 AM)

Hi Phil,

Thank you for letting me know!

 


Comment by: Vicki Sowinski (9/18/2015 4:32:03 PM)

I am having the same issue with v4.3. This is my first install. The product looks like just what I need so I was anxious to try it, but it appears I cannot yet until this issue is resolved. I'm not sure I can use the older versions because I only have Excel 2007. Looking forward to the fix!
Thanks!

 


Comment by: Jan Karel Pieterse (9/18/2015 8:15:49 PM)

Hi Vicky and all others experiencing problems:

Can you please try the one I uploaded today?

 


Comment by: PhilUK (10/23/2015 3:46:12 PM)

Hi Jan
Following on from the problems I reported recently (4th Sept & 16th Sept) I downloaded your latest version as you suggested on 18th Sept
It now works!
Many thanks for your efforts, I hope this has resolved the problems for other users too
Again, many thanks for sharing this great utility

 


Comment by: Paul Dawson (11/30/2015 3:17:16 PM)

Jan
The name manager window never displays until the workbook is closed and even then it can never be given focus. I have office 365 64bit and windows7 64bit. I have repaired office. Am I missing something simple?
Thanks for a great add in.
Paul

 


Comment by: Lori Greenberg (12/10/2015 4:40:02 PM)

I'm lost without this tool! I tried to update to the most current version and it failed (for various reasons - issues on my end, not the tool) but our PCs are locked down and I can't get access to the folder to install the add-on. Is there any way to run this without having to put it in my add-on folder? Right now if I try to simply run the add-in file and click on it on my add-on tab, I just get a message box that says "text" with an "okay" button. Is there a way to revert to an earlier version? Should I try the 97 version?

Many thanks for ALL of the great things I've learned from you, Jan!

 


Comment by: Jan Karel Pieterse (12/10/2015 10:54:18 PM)

Hi Lori,

Thanks for the compliments :-)

Can't you simply open the xlam file from -for instance- your Documents folder? That should just work.

 


Comment by: Lori Greenberg (12/14/2015 8:32:56 PM)

Jan,

Absolutely I can and I feel like a moron because I figured that out just a soon as I submitted my comment! I just didn't know how to contact you to say "never mind"!

Keep up the GREAT work - I learn so much from you on this site!

 


Comment by: Jan Karel Pieterse (12/15/2015 8:23:47 AM)

Hi Lori,

Well, "Never mind" you just said :-) I'm just glad you got it working again.

 


Comment by: Jan Karel Pieterse (12/31/2015 5:22:52 PM)

Hi Mark,

You can simply copy the name manager 2007.xlam file to e.g. your documents folder. Then open Excel, open the add-ins dialog and click the browse button to go to the documents folder and select that file.

 


Comment by: Jeffrey Travis (1/6/2016 9:21:46 PM)

Hello, Jan --

Let me start off by saying that I have found the Name Manager to be and invaluable tool for Excel development! Thank you for making it available.

I have a couple of questions, below. They are absolutely not urgent, whenever you have time to consider them is OK. Any suggestions that occur to you I would greatly appreciate hearing. I suspect the 2 problems are related but have no proof other than that they manifest at the same time.

Thanks,

Jeff Travis

**************************

1. Are you aware of any specific conditions which would prevent Name Manager from loading?

I have an Excel application I have been working on for several years, and sometimes when I replace a VBA module in the application and re-initialize it, Name Manager chokes on something and will not load, crashing VBA. I can usually get around the issue by restarting my application one or more times (i.e. Save and Close all workbooks except my add-in, then re-open the main workbook to trigger a re-initialization of the application.

2. Have you ever observed that a worksheet-scoped name can change to workbook-scoped, spontaneously and for no obvious reason?

In one of my application workbooks I have a sheet that has worksheet-scoped names, but sometimes they "slip" into workbook scope. This happens often enough that I had to write a sub that runs automatically during initialization, detects the "slippage" and restores the names to worksheet-scope.

I'm not sure, but I suspect that it has something to do with the fact that there is only one copy of the worksheet. I have multiple copies of another sheet with sheet-scoped names, and these sheets do not exhibit the problem -- until I delete all but one copy. At that point many of the sheet-scoped names will become workbook-scoped. I handle this in a similar fashion as above -- I have a sub that "repairs" the names to worksheet scope.

 


Comment by: Brandon Narveson (1/7/2016 12:16:42 AM)

Hello -

I have a relatively small excel file (4mb) that is taking forever to open. After some research, I think I've pinpointed it down to the large number of named ranges. I personally don't use these types of ranges so they must have somehow been imported/linked in. Anyway, this is the first tool that has generated a list of the ranges, so thank you for that!

After the list has been generated, the tool will not allow me to select all named ranges and delete. When I attempt this, excel is "not responding" and I eventually have to crash it (I tried letting it run overnight as well). Is there something I'm missing? I tried deleting the named ranges one at a time and it is taking about 45-60 seconds for each one. And unless I'm reading it incorrectly, there are about 100,000 named ranges in this specific file.

I'm using version 4.3 for excel 2007. Any help would be much appreciated.

Thanks,
Brandon

 


Comment by: Jan Karel Pieterse (1/7/2016 10:50:09 AM)

Hi Brandon,

Would it be possible to send that file to me?
I'd love to test it.

 


Comment by: Jan Karel Pieterse (1/7/2016 10:55:33 AM)

Hi Jeffrey,

Thanks for your message. To your questions:

1. I have the habit of never editing add-ins directly. Rather I edit a "normal" workbook and when done I turn it into an add-in. This is because having another add-in loaded which has a class module which has a global application event handler may prevent you from saving your add-in (a bug in Excel VBA methinks). This means I haven't seen your problem.

2. Never seen that happen before, but then again, I am not doing development work on very recent Excel versions, I still use 2010 as my main production platform.

 


Comment by: Brandon Narveson (1/12/2016 12:47:52 AM)

Hi Jan,

I apologize for the delay in response, for some reason I was looking for an email to come through. I can send you the file. Can you provide an email address to send it to?

Thanks,
Brandon

 


Comment by: Jan Karel Pieterse (1/12/2016 7:30:15 AM)

Hi Brandon,

You can send it to the email address shown at the bottom of this page.

 


Comment by: Tom (1/14/2016 1:16:52 PM)

On one particular page, a named range is highlighted and within that range I cannot use the cursor. How do I remove that? Also, where can I find the program on my computer if I decide to uninstall it. I'm using Excel 2007. Thanks.

 


Comment by: Jan Karel Pieterse (1/14/2016 1:37:13 PM)

Hi Tom,

If you open Name Manager and press the button to remove highlights, can you then click on that rangename again?

To remove NM, simply open File, Options, Add-ins tab and press "Go...". Then uncheck NM there.

 


Comment by: Tom (1/15/2016 12:01:23 PM)

Thanks, Jan, I was able to clear the highlighted named range.

Another question. When I change the parameters of a named range in excel (ie. add a row), the formulas that used the named range don't update, they use the previous parameters and drop the named range in the formula. Is there something in Name Manager that can help me with that? Thanks.

 


Comment by: Jan Karel Pieterse (1/15/2016 3:00:26 PM)

Hi Tom,

I am not sure I understand what you mean, perhaps you can show me a formula before and after the change and what you changed to the range name and how you made that change?

 


Comment by: Tom (1/16/2016 1:05:24 PM)

Jan:

I'll try and example below.

Formula is =vlookup(a5,TABLE,5,false) where TABLE = a5.z100

I change the range name "TABLE" to a5.z110.

The formula is now =vlookup(a5,a5.z100,5,false).

When I adjusted the range name "TABLE," the formulas don't update to the new range, instead keeping the old parameters of "TABLE" (a5.z100, not the updated a5.z110).

Thanks for your help.

Tom

 


Comment by: Jan Karel Pieterse (1/16/2016 3:48:37 PM)

Hi Tom,

I'm confused.
If you let your formula be =vlookup(a5,TABLE,5,false), then whenever you update the range name called Table, it will be that range the formula uses. So in fact you replace the cell reference in your formula with the name of the range.

In other words. Suppose you have this formula:

=vlookup(a5,$A$1:$E$100,5,false)

and you define a name which points to $A$1:$E$100 called "TheName", then your formula should become:

=vlookup(a5,TheName,5,false)

By using the name in the formula, as soon as you change the definition of the range name TheName to point to something else, there is no need to update that formula, as it already uses TheName.

 


Comment by: Tom (1/18/2016 1:09:50 PM)

Jan:

The way your describe it is the way it should work, and the way it did work with these particular programs. At some point it changed to how I describe it in my previous note: when I change the parameters of the named range, the formulas drop the named range and stay with the previous parameters.

I can't figure out why it happened or what to do to get it back to the way it should be.

I really appreciate any help you can give.

Tom

 


Comment by: Jan Karel Pieterse (1/18/2016 2:16:34 PM)

Hi Tom,

This sounds like a bug in Excel to me. What Excel version are you using precisely?

 


Comment by: Tom (1/18/2016 8:12:28 PM)

Jan:

I use Excel 2007.

Thanks.

Tom

 


Comment by: Jan Karel Pieterse (1/19/2016 9:48:08 AM)

Hi Tom,

I am unable to reproduce your problem, I'm sorry!

 


Comment by: Hal Koberinski (1/23/2016 2:59:33 AM)

The utility won't activate in my version of Office 2010 64 bit running under windows 10. It appears as an EXCEL Addin but doesn't appear on the ribbon as advertised.

 


Comment by: Jan Karel Pieterse (1/23/2016 11:23:50 AM)

Hi Hal,

Odd. What happens if you redo the installation after closing all your Excel windows?

 


Comment by: Rakesh Jain (2/1/2016 9:30:29 PM)

Hi Jan,

The latest version does not work on my machine, Office 2016 64 bit on Windows 10. It appears as an Excel Addin but doesn't appear on the ribbon. version 3.2 does not work either, but 2.3 does. I am not sure why?

Thanks,
Rakesh

 


Comment by: Jan Karel Pieterse (2/2/2016 3:52:33 PM)

Hi Rakesh,

What happens precisely?

 


Comment by: Rakesh Jain (2/4/2016 3:51:29 PM)

Hi Jan,

Version 2.3 - works fine.

version 3.2 - Shows "Compile Error in hidden module, fxlNameManager", my apologies, I see that you have explained that already, however I have not tried to fix it so do not know if the solution works.

Version - 4.3 - this is where I started, The add-in does not appear on the ribbon, however it is there in the list of installed add-ins. So if the name manager does not appear on the ribbon there is no way to use it AFAIK. I tried adding and removing a few time, never workerd. I have 64 bit Excel 2016 version 16.0.6366.2062 updated to date on Windows 10.

Thanks for looking into it. I am using version 2.3 till it is fixed. Let me know if any further details can help.

Rakesh

 


Comment by: Jan Karel Pieterse (2/4/2016 4:07:34 PM)

Hi Rakesh,

With NM checked in the add-ins, if you open the VBA editor, Is NM listed in the project explorer window?

 


Comment by: Rakesh Jain (2/4/2016 9:52:38 PM)

Jan, Yes the Name Manager is listed there as a project, but still no add-in tab in the ribbon.

After your comments today, I gave another try, still the same thing. Then I did something additional which made it work finally. I went to add-in windows (Alt+L+H), un-checked Name Manager, and clicked ok to close it. Then I went to add-in windows again to check the Name Manager, and now it works. And, it still works even if I restart Excel.

So the problem only seems to be at the initial setup, where it needed a couple of extra steps.

 


Comment by: Jan Karel Pieterse (2/5/2016 10:02:47 AM)

Hi Rakesh,

I've seen that happen with Excel 2013 and 2016 on occasion. Mostly if other instances are already open when installing an add-in.

 


Comment by: Michael Gray (2/9/2016 4:33:29 AM)

I'm having the same issue of missing Add-in in ribbon. Excel 2016 MSO (16.0.4312.1000) 32-bit. Yes, I've tried to check and uncheck, verified that it is in the Project file.

 


Comment by: Jan Karel Pieterse (2/9/2016 11:08:11 AM)

Hi Mike,

Close ALL instances of Excel. Then open one and go into the add-ins dialog (alt+t,i). Uncheck Name Manager. Close the dialog. Reopen the dialog and check the add-in.

 


Comment by: Terry Copeck (2/9/2016 8:21:21 PM)

Hi Jan. Running into the compile error problem after reinstalling Excel 2003 yesterday; 2.3 works, 3.2 and above do not. Detect and repair from within Excel does not solve it; when checked, NM is listed in the VB 6.5 editor project explorer.

 


Comment by: Jan Karel Pieterse (2/10/2016 7:24:41 AM)

Hi Terry,

If you haven't done so yet, perhaps it helps to have Office look for updates?

 


Comment by: Terry Copeck (2/10/2016 8:30:24 AM)

I can see the logic: a fresh installation from the CD rolls the code back to the original release. Windows Update today offers for three updates for Office 2003: SP3, KB2543854, and KB907417. ... I'm leery about WU now; it's getting pretty coercive about Windows 10.

However my own investigation of the problem didn't lead me in the direction of outdated code because NM 4.3 runs in an second new installation of Excel 2003 made in the last few hours in a spare partition and that installation would be equally out of date.

But--updating the problematic Excel 2003 installation with SP3 did the trick. Who knows why? Your fresh perspective was what was needed, though. Thank you.

 


Comment by: Jan Karel Pieterse (2/10/2016 8:35:51 AM)

Hi Terry,

Who knows. Some updates are Windows updates, but affect Office as well.

 


Comment by: Terry Copeck (2/10/2016 8:47:54 AM)

"Some updates are Windows updates, but affect Office as well", which might explain it. The spare partition is necessarily not as up-to-date as a partition used every day.

 


Comment by: Joe OConnor (3/16/2016 7:56:29 PM)

I started getting a compile error a few days ago as Excel opens and tries to launch Name Manager. I've been using Name Manager 4.3, Build 651, for some time without issue. Office version is 15.0.4805.1003.

The complete error message is: "Compile error in hidden module: CMenuHandler. This error commonly occurs when code is incompatible with the version, platform, or architecture of this application. Click "Help" for information on how to correct this error."

I've already tried your suggestion to uncheck and re-check Name Manager in Manage Add-ins (2/9/2016 11:08:11 AM).

 


Comment by: Jan Karel Pieterse (3/17/2016 8:21:18 AM)

Hi Joe,

Perhaps a recent update has caused the problem? You could try to restore your Windows PC to a previous point in time. Alternatively, often a repair of Office solves a problem like this one.

 


Comment by: Martin Nolan (5/15/2016 11:03:53 PM)

An Office update ran on my Windows 7 x64 machine today and I started experiencing the same issue as Joe. Uninstalling and reinstalling the add-in did not help, a full Office repair did not help, and a restore is not available. There is no Office update showing in the updates list although there were some .Net security updates.

 


Comment by: Jan Karel Pieterse (5/16/2016 4:45:42 PM)

Hi Martin,

Well, you could try uninstalling those .NET updates, but I doubt if it'll help :-). Try searching your system for .exd files and removing those (They are temporary so can be safely deleted).

 


Comment by: Pete Walton (5/27/2016 7:28:48 PM)

NM 4.3 works fine (so far?) in Office 2016. Really can't live without NM, co verified its working in Excel 2016 before I committed to Office 2016.

Thanks for a great and useful add-in.

 


Comment by: Jan Karel Pieterse (5/29/2016 4:00:26 PM)

Hi Pete,

Glad it is working for you!

 


Comment by: Mahboob Mahmood (6/24/2016 11:15:54 AM)

Hi

I have the latest excel for mac. (Is it 2016)? I have a bunch of unwanted names in my define name utility. As a result I cannot move or copy any screen.

I downloaded name manager but it does not open up or install. What should I do!?

Thanks!

Mahboob
Knowledge Platform
Singapore

 


Comment by: Jan Karel Pieterse (6/24/2016 11:18:00 AM)

Hi,

Did you download the Mac version?

 


Comment by: Colby (7/4/2016 1:35:18 AM)

Today the annoying Office update bar got to me and I updated my PC's Office 2016 to the latest version.

Apparently the new version of Excel disables addons from modifying the existing toolbars. Can we get a version of Name Manager that uses the Add-ins section instead?

 


Comment by: Jan Karel Pieterse (7/4/2016 10:20:47 AM)

Hi Colby,

Luckily the shortcut-key will still work: control+shift+N starts Name Manager.
I'll check what gives with that update.

 


Comment by: Jan Karel Pieterse (7/4/2016 4:47:18 PM)

Hi Colby,

I just updated my Excel 2016 and the NM icons are there on the formula tab.

 


Comment by: Colby (7/4/2016 8:25:49 PM)

Thanks Jan, didn't see it there and also for the shortcut!

 


Comment by: Jeff Nguyen (7/29/2016 6:29:59 PM)

Hi!

I have been using your Name Manager 2007 for several years now and I love it! I have installed it on my PC at work and find it easier to use than the native name manager built-in Excel. Sometimes there are errors with named ranges that are displayed in your Name Manager and not in the Excel name manager.

My company recently upgraded to MS Office 365 (Excel 2016). I tried downloading and installing your product again, but it does not work :(

Do you plan to release another version in the near future that will be compatible with Excel 2016? If you do, could you please inform me?

Thank you very much once again for sharing your wonderful and very useful product!

sincerely,

Jeff Nguyen
(NYC, USA)

 


Comment by: Jan Karel Pieterse (7/30/2016 9:36:56 AM)

Hi Jeff,

It should work, try opening the add-ins dialog, unchecking, closing, opening the dialog and checking it again.

 


Comment by: Chris (8/2/2016 7:52:19 AM)

I found that updating this add-in from an older version caused the icon on the Formula's tab to disappear. Although the shortcut control+shift+N works, I don't know that I'll remember it every time I would like to run this really great tool.

I'm running Excel 2016

 


Comment by: Jan Karel Pieterse (8/3/2016 11:41:48 AM)

Hi Chris,

A recent update has broken the addition of custom items to the ribbon of Excel, it should be fixed with a subsequent update in due time.

Somtimes it helps to uncheck and re-check the add-in in the add-ins dialog.

 


Comment by: Steve Fee (9/4/2016 12:52:02 AM)

How do I remove Name Manager 2007 from my Excel 2010?

 


Comment by: np_vijayakumar@yahoo.com (9/4/2016 8:32:26 AM)

hi bro

i cant see the reset button. can u pls upload the vdos . how to reset the sheet. still i cant copy the sheet. if u post vdo means i can easily understand. i m using office 2007. advance thanks.

 


Comment by: Jan Karel Pieterse (9/5/2016 7:58:14 AM)

Hi Steve,

Open the addins dialog (alt t, i) and uncheck it should do the trick.

 


Comment by: Jan Karel Pieterse (9/5/2016 7:59:03 AM)

Hi,

The reset button is on the Formulas tab, far right.

 


Comment by: Jeff Klein (10/23/2016 7:13:31 PM)

Hi Jan,

I absolutely love your NameManager add-in and use it extensively in developing my own Excel apps and add-ins. Recently, however, I have been getting the following error during Excel startup:

Compile error in hidden module: CMenuHandler. This error commonly occurs when code is incompatible with the version, platform, or architecture of this application.

I can still access NM using the Add-Ins tab --> Name Manager dropdown in Menu Commands, so this only seems to affect the ribbon customizations. So this is not a killer error, more FYI.

I am running Excel 2010 version 14.0.7173.5000 and I just downloaded the latest build of NM (652). Thanks again for all the great help you have provided over the years to VBA learners like me.

Best regards,
Jeff Klein, Massachusetts, USA

 


Comment by: Jan Karel Pieterse (10/23/2016 11:16:06 PM)

Hi Jeff,

This can normally be solved by doing a repair of Office.

 


Comment by: Stuart (11/2/2016 10:15:02 AM)

Hi Jeff,

Such a great add-in! I've just upgraded to Excel 2016, and on installing the latest "Name Manager 2007.xlam" add-in, and/or starting Excel once it is installed, I get the following dialog box twice in a row:

Microsoft Forms
Could not load an object because it is not available on this machine

Any ideas?

Strangely/happily as far as I can tell the add-in works perfectly regardless!

Cheers

Stuart

 


Comment by: Jan Karel Pieterse (11/2/2016 10:23:53 AM)

Hi Stuart,

Odd, the add-in does not contain any third party controls. Try if repairing your Office installation helps resolve this error.

 


Comment by: Steve James (11/14/2016 11:41:53 AM)

I'm running NM 4.3/652 with Excel 2016 64-bit, but I'm getting an automation error when I try to access NM via the ribbon. When I exit Excel I get a "Excel has stopped working" error message.

I've tried doing a repair of Office 2016 as suggested above, but this has no effect.

 


Comment by: Jan Karel Pieterse (11/14/2016 4:33:12 PM)

Hi Steve,

What precisely does the Automation error say?

 


Comment by: David Hawkins (11/15/2016 6:16:59 PM)

Hi,
For those looking at Excel 2016 here is my experience.
Have just got a new PC at work with latest Excel 2016, 64bit Windows 7 professional , but 32 bit excel - selected for compatibility - name manager installed with no apparent problems.


Thank you for a fantastic product - have some workbooks with over 150 named fields and tables - without the name manager I would be lost !

Thanks
Dave

 


Comment by: Sandy (11/28/2016 6:01:08 PM)

What a wonderful product, thank you!

I am having a challenge running the suggested macro (BatchRename) from Page 20 of the user guide - I have 1000s of names to update in a large workbook, and started with a small batch of 100 or so.

When I export a list of names (central to a worksheet in the book), place them on the worksheet, place a set of replacement names in the column to the immediate right, and then select the names list I want to change then execute the script, I get:

Run-Time Error 9: Subscript out of range.

Debugging highlights the row:

Application.Run "'" & Workbooks("Name Manager.xlam").FullName & "'!InitNameManager"

I am running Office 2010. I have tried a few things here... I have renamed .xla to xlam (here as you can see and in the latter row it appears). For fun, I tried copying the .xlam file into the same directory as my excel workbook, which did not work either. I even changed the cell formats (with the names I want to change) from text to general in case that was the issue.

Any ideas....?? Your assistance would be deeply appreciated!!!

That aside, the tool itself has been a godsend!!!!!!

 


Comment by: Jan Karel Pieterse (11/28/2016 6:32:54 PM)

Hi Sandy,

Thanks for the compliments :-)
Perhaps your Windows is set to hide extensions, try omitting the .xlam part.

 


Comment by: Sandy (11/28/2016 7:49:12 PM)

Unfortunately omitting the extensions did not work. :-( I appreciate your trying though!

 


Comment by: Jan Karel Pieterse (11/29/2016 8:07:20 AM)

Hi Sandy,

My bad, the name should be identical to the filename of the add-in. SO if you have installed Name Manager 2007.xlam, please make sure to use that precise name:

    Application.Run "'" & Workbooks("Name Manager 2007.xlam").FullName & "'!InitNameManager"


and

                Application.Run "'" & Workbooks("Name Manager 2007.xlam").FullName & "'!replacename", sOldname, sNewName, True


NB: please do not click Debug should any runtime error occur, it may crash Excel!

 


Comment by: Don (12/22/2016 7:27:09 PM)

I am using Excel 2016. I can install Name Manager 4.3 (build 652,655) and it initially works, installing a button on the 'Formulas' ribbon. But if I close and re-open Excel, the Name Manager 4.3 button disappears from the ribbon. It still shows as an active Add-in (Developer ribbon, Add-ins, Excel Add-ins). Selecting and deselecting from the Add-ins list does not make the ribbon button reappear. I do not know how to activate Name Manager+ without the ribbon button or how to get the button back without re-installing again.

(Also, the setup script did not work, I had to install the xla file manually.)

For the record, I have been using Name Manager+ since Excel 2007 (2007, 2010, 2013 and now 2016) Great add-in!

 


Comment by: Jan Karel Pieterse (12/30/2016 3:13:34 PM)

Hi Don,

Perhaps you got bitten by that new update that disables add-ins when you download them? See near the top of this page how to unblock add-ins.

 


Comment by: Samade (1/3/2017 7:10:44 AM)

Awsome tool. Really helped me.
Thanks so much.

 


Comment by: Chris (1/5/2017 2:24:03 PM)

I would appreciate it very much if you could assure compatability with 2016x64.

Thanks!

 


Comment by: Jan Karel Pieterse (1/6/2017 1:47:40 PM)

Hi Chris,

WHat isn't working for you? NM is tested and works on 64 bit Office 2016.

 


Comment by: Enyapham (1/9/2017 6:56:20 AM)

This version is not working on Office 2016. Hope to see new version!

 


Comment by: Jan Karel Pieterse (1/9/2017 9:08:30 AM)

Hi Enyapham,

Can you please tell me what happens? I just tried and it works fine for me.

 


Comment by: Enyapham (1/12/2017 7:47:51 AM)

Hi,
I'm using Office 64 bits.
I got error as below:
Microsoft Excel cannot access the file '...*\AppData\Roaming\Microsoft\AddIns\Name Manager 2007.xlam'. There are several possible reasons:
- The file name or path does not exist. (event i copied to this folder)
- The file is being used by another program. (dont have any other program using that one)
- The workbook you are trying to save has the same name as a currently open workbook.

I also try Office 2013, but it still not working ((

 


Comment by: Jan Karel Pieterse (1/12/2017 10:53:50 AM)

Hi Pham,

Please open Excel, click OK on the error and do the following:
Open File, Options, click Add-ins and click the Go... button. Try to select Name Manager in that list. Excel will ask if it should remove it. Please allow Excel to do that.
Now download the latest version of NM. Follow these steps:
- Open ZIP and copy ALL files from the zip to any folder you have access to. Tip: add a folder to your My Documents folder for this
- In that folder open the file called "Setup Name Manager 2007", enable macros and click the setup button.

 


Comment by: Sandra (1/19/2017 2:12:02 PM)

Ik filter op 'met fouten' en kom niet af van:
_xlfn.COUNTIFS =#NAAM?
_xlfn.IFERRO     =#NAAM?

Hoe los ik dat op?

 


Comment by: Jan Karel Pieterse (1/20/2017 11:50:06 AM)

Hoi Sandra,

Dat klopt, want die namen worden door Excel gemaakt indien je in een Excel versie later dan Excel 2003 functies gebruikt die Excel 2003 nog niet kende en je je bestand in het xls format opslaat. Deze namen kunnen niet worden verwijderd.

 


Comment by: Helmut (2/23/2017 4:12:59 PM)

Using Namemanager 4.3 Build 655: when trying to change a name or to create a new one, I get a requester similar to the "define new name"-Requester of Excel, showing the first letter of the name above all, the name in the list below and the formula in R1C1-Notation within '-characters in "refers to"-field (='R1C1' or better ='Z1S1', since I'm using a german version). Older Versions of Namemanager worked fine, unfortunatley I don't have a copy of These.

I'm using Excel 2013 32-Bit and Win 8.1 64-Bit.

 


Comment by: Jan Karel Pieterse (2/27/2017 10:03:11 AM)

Hi Helmut,

Hmm, not much has been changed to NM in the past years. Can you perhaps provide a sample workbook and tell us which name you tried to edit?

 


Comment by: Helmut (3/1/2017 1:52:55 PM)

Hi Jan Karel,

the strange thing is: Even in a new Excel-File I get this error. in Namemanager I just click on the "+"-Icon to add a new name. I name it "test" and give it the formula "=$A$1". Then i choose "global" and click on "Add" ("Hinzufügen"). And then i get the error. I click on "Close" ("Schließen"). Then the name is definded as "=1".
In an older build of Namemanager all worked fine. Perhaps you could give me an older build and i will check this again. Unfortunatley I don't have an older Version any more.

My Excel-Version: 15.0.4885.1000 32-Bit
No other Add-Ins. COM-Add-Ins: "Teamcenter Excel Add-In" and "Teamcenter Extensions for MS Office 2013". Both I can't remove, because I don't have admin-rights.

I would be pleased to gove you some screen-shots...

 


Comment by: Jan Karel Pieterse (3/2/2017 10:24:36 AM)

Hi Helmut,

This must be something specific to your system. I'll send an older version to you as soon as I can. Do you have an indication when it last worked properly?

 


Comment by: John Eriksen (3/2/2017 11:47:13 AM)

I observed a problem with the Namemanager enabled in Excel 2016.
I had some Grouped pictures on a sheet in Excel, but activating the Namemanager, these Groups where Ungrouped without any notice and caused some problems in my VBA-programming.

 


Comment by: Helmut (3/2/2017 4:28:40 PM)

Hi Jan Karel,

I just recently updated NM, the last time, I did this is about one year ago.
I guess it is my system. In November I got a new Computer with Win8.1 the old one had Win7. A colleague still has Win7 and on his machine NM works fine...

 


Comment by: Jan Karel Pieterse (3/2/2017 6:46:13 PM)

Hi John,

Thanks for letting us know. This probably only happens if you let it search for unused names or when you rename a name.

 


Comment by: Jan Karel Pieterse (3/2/2017 6:47:55 PM)

Hi Helmut,

Perhaps a keyboard setting fools Name Manager. Are you using "accented keys"? It means if you type the ", nothing happens until you type e.g. the o to give you an ö.

 


Comment by: graham (3/3/2017 11:45:38 PM)

Hi
Just downloaded and used to update copied worksheet with 30+ named ranges, resetting the scope. Saved me 1/2 hour at least! Thank you.

 


Comment by: Brett (3/24/2017 10:45:52 PM)

Thanks for making Name Manager available, it has come in handy when needing to edit large numbers of named ranges.

I use both Windows and Mac versions and I recently noticed odd behavior in the Mac version. When opening Excel (2016), Excel presents the Enable Macros dialog before it shows the Gallery window. I've traced this to Name Manager, my other add-ins don't do this. If I unload Name Manager, this behavior goes away. If I recheck Name Manager in the Add-Ins dialog, I immediately get the Enable Macros dialog.

Name Manager seems to be behaving more like a hidden macro-enabled workbook than an add-in (xlam). Can anyone on the development side replicate this?

 


Comment by: Jan Karel Pieterse (3/25/2017 6:42:08 PM)

Hi Brett,

Odd indeed. I assume this is version 2.3 you are talking about, correct?

 


Comment by: Brett (3/28/2017 5:18:40 PM)

Hi Jan:

My apologies, I had mistakenly installed v4.3, which isn't Mac-compatible. v4.3 must have been opening something that caused the Enable Macros dialog to appear. As noted at the top of the page, this does not work in Excel 2016 on the Mac.

 


Comment by: Jan Karel Pieterse (3/29/2017 7:45:12 AM)

Hi Brett,

No problem!

 


Comment by: Geoff (4/8/2017 2:16:55 PM)

Thanks. This has solved the reporting of apparently non-existent names when copying worksheets.
At last copying goes ahead without annoying messages.
Thanks.

 


Comment by: Deana (4/14/2017 4:29:49 PM)

Thank you!!! I have been working with a legacy file with literally thousands (2,730) of hidden "names". I used your tool and for the first time ever have been able to see and remove the unneeded and invalid names.

YOU are a lifesaver!!!

Thank you thank you thank you, a thousand times thank you!!!

Deana

 


Comment by: Des Murphy (5/11/2017 11:25:22 AM)

I get a message saying that I need FastExcel for some features. What is fastExcel?

 


Comment by: Jan Karel Pieterse (5/11/2017 1:43:00 PM)

Hi Des,

FastExcel is aproduct sold by Charles Williams, you can find out more here:
http://sites.fastspring.com/decisionmodels/product/fastexcelspeedtoolsproducts?source=jkpads

 


Comment by: Michael McFadden (5/17/2017 3:59:14 PM)

Jan,
I love your name manager add-on, and have used previous versions. My current computer's c:/ hard drive crashed and I lost the application. I have downloaded the new one and installed it, but it did not install in the Add-ins menu tabs like it did when I first installed it 5 or 6 years ago. It installed in a new group tab called Name Manager + under the Main Formula Tab. However, the new tab does not appear on the Customize the Ribbon box when opening "Customize the Ribbon" commands. Is there anyway to move it to another Tab? I appreciate it.

 


Comment by: Jan Karel Pieterse (5/17/2017 4:19:10 PM)

Hi Michael,

I'm afraid there is no option to move that button elsewhere. Can I ask why you want to do so?

 


Comment by: Dieter Rainer (8/2/2017 1:31:09 PM)

Hi Jan,

thanks a lot for the pleasant tool! Your ool helped me to solve a nasty _Filterdatabase-name-error-problem ...
Thank You! Thank You! Thank You

Dieter

 


Comment by: Jerry Gage-Anderson (8/16/2017 2:57:43 AM)

I love Name manager and it works like a charm. I usually use it for large name ranges but for some reason (17760) seems to crash..I have let it run for 5 hours and still didn't finish....is there a trick I should try to avoid this or something I am doing wrong?

 


Comment by: Jan Karel Pieterse (8/16/2017 9:55:17 AM)

Hi Jerry,

Is this on a workbook with a large amount of range names?

 


Comment by: Barb Reinhardt (8/17/2017 2:41:56 PM)

I've been using Name Manager for some time, but it now seems to be hidden when I try to use it. Any suggestions?

 


Comment by: Jan Karel Pieterse (8/17/2017 2:47:23 PM)

Hi Barb,

It sometimes helps to go into the add-ins tab, uncheck the add-in, OK the dialog and reopen it to check the add-in again.

 


Comment by: Barb Reinhardt (8/17/2017 3:49:09 PM)

I've tried that. I've sometimes found that it's off the page, but this time I just can't find it. I know it's present, but not visible. I'll keep playing with it and let you know what eventually works.

 


Comment by: Jan Karel Pieterse (8/17/2017 4:29:52 PM)

Hi Barb,

Are you saying the NM window is off-screen? In that case, press the reset button on the Name Manager+ group on the formulas tab.

 


Comment by: Barb Reinhardt (8/17/2017 4:58:49 PM)

I've done that multiple times and all I see is

Please enter the shortcut key to show the Name Manager.
(Press control + Shift + the letter you enter to start the name Manager).

There is an n in the field. I click OK. I've also tried CTRL SHIFT n, but no luck.

I've never defined a letter to start name manager, so am not sure what to do next.

 


Comment by: Jan Karel Pieterse (8/17/2017 5:30:45 PM)

Hi Barb,

Just click the *word* Reset :-)

 


Comment by: Carlos Terre (8/25/2017 2:09:50 PM)

I am now on office 2016 and name manager does not show anywhere. I have tried to deactivate and reactivate the add-in already (closing the dialogue in between). And nothing seems to work.
Is this a known issue?

 


Comment by: dan (8/29/2017 5:15:15 PM)

i installed the utility but when i go to Formula's, it doesn't show the name manager icon. i am using excel 2016.

thoughts?

 


Comment by: Jan Karel Pieterse (8/29/2017 5:43:45 PM)

Hi Dan,

Unfortunately I hear this a lot from 2016 users :-(

- Close all Excel instances
- Open Excel once
- Press alt+t, i to open the add-ins dialog
- Uncheck NM
- Close the dialog
- Reopen the same dialog
- Check NM
- Close and restart Excel

 


Comment by: Jan Karel Pieterse (8/29/2017 5:44:31 PM)

Hi Carlos,

Did you try the options mentioned above this message?

 


Comment by: Thomas Claussen (9/5/2017 2:51:47 PM)

Is this admin working for Excel 2011 (Mac)?
Regards,
Thomas

 


Comment by: Klaus (9/6/2017 7:33:31 PM)

Hi,
I succesfully installed the current version 2007 4.3 build 655 on a Mac for Excel Version 15.37 (latest update received) in German version. However, after pressing the Name Manager button in the ribbon formula section I get a message as follows:

"Runtime error "53". File not found: user32"

Any chance you might assist?
Best regards, Klaus

 


Comment by: Aymeric (9/7/2017 5:45:11 PM)

Hi Jan,

Thanks for this very helpful tool.
I used to have it on former excel version and working fine. But since I have migrated to Excel 2016, I have a compilation error just after installation, saying :

"Compile error in hidden module: CMenuHandler.
This error commonly occurs when code is incompatible with version, platform, or architecture of this application..."

I have tried to multiply install/uninstall after excel reboot but nothing work.

Any idea ?
Cheers

Aymeric

 


Comment by: Jan Karel Pieterse (9/8/2017 7:40:00 PM)

Hi Klaus,

That version is not compatible with Mac Excel I'm afraid, please look for version 2.3, which should work on a Mac: http://www.jkp-ads.com/downloadscript.asp?filename=Namemanager23.zip

 


Comment by: Jan Karel Pieterse (9/8/2017 7:41:05 PM)

Hi Thomas,

There is an old version that works on Mac Excel: http://www.jkp-ads.com/downloadscript.asp?filename=Namemanager23.zip

 


Comment by: Jan Karel Pieterse (9/8/2017 7:42:15 PM)

Hi Aymeric,

I'm sorry to hear that! We're considering removing the functionality that causes this compile error for some people. We'll publish an update here when we do!

 


Comment by: Klaus (9/8/2017 9:01:36 PM)

Hi Jan,

thanks for your advice. However, NM it still does not work with version 2.3.

In your instructions I further above could read: "Note that unfortunately NM is NOT compatible with Office 2016 for Mac."

Now, Excel vers. 15.nn is ultimately Office 2016. So finally there is currently no chance to get NM running on a Mac with Offic e2016. Too sad. Any future plans to make it working?

Best regards, Klaus

 


Comment by: Jan Karel Pieterse (9/9/2017 2:00:09 PM)

Hi Klaus,

No plans as of yet I'm afraid, I do not have any Apple devices.

 


Comment by: Carlos N (9/24/2017 4:13:43 PM)

Similar problem to others before.

I'm running Office 365 Version 1707

I installed Name Manager. Saw no apparent changes to interface, although NM appeared checked in Add Ins list.

Restarted Excel. Still no NM in Formulas tab of ribbon, where I'm used to it residing. INstead, I went to the Add-Ins tab of ribbon. Found a new group "Menu Commands" that had a "Name Manager" drop down with four commands (Name Manager, Change Shortcut Key, Create Toolbar, Reset Position on Screen).

Tried "Create Toolbar". It created a new group within the Add Ins ribbon. The group is "Custom Toolbars" and includes one small icon for "Name Manager"

Tried un-checking in Options->Add Ins, Restarting, and re-checking. No change.

In short, functionality is all there, including keyboard shortcut, but icon never shows up in "Formulas" ribbon.

 


Comment by: Jan Karel Pieterse (9/26/2017 11:15:33 AM)

Hi Carlos,

Perhaps you have downloaded the Office 97-2003 version of NM, which does not include a ribbon?

 


Comment by: Andreas (10/4/2017 11:29:44 AM)

Hi Jan,

I also have the problem, that I can not find NM in ribbon. Add in is instaled correctly - without any failure message.
It seems that both links lead to the same source. Both zip files are identical.
Could you please help ?

Thanx in advance !

Andreas

 


Comment by: Jan Karel Pieterse (10/4/2017 3:30:50 PM)

Hi Andreas,

Please remove all traces of Name Manager and download the one currently on the site (I have just updated it).

Then start Excel and:
- Open the add-ins dialog
- If NM is there try to select it and if Excel complains the file is missing, click Yes to delete
- Close add-ins dialog and close Excel
- Now install NM.

 


Comment by: Andreas (10/4/2017 4:43:42 PM)

Hi Jan,

thank you for the quick response.
The problem is still there. I did the steps you adviced.
But it seems to me, that the file date is the same as before. I can not see any change in date. Are there different links on the page? So, maybe I got a wrong one ?

Regards,
Andreas

 


Comment by: Andreas (10/4/2017 4:54:25 PM)

Hi Jan,

I tried the manual way for installing. Contrara to to the installer I took the .xsam file for office2013. That seem to run well. Now I have to work with it ;o)
Thank you for the support and a great tool.

Regards,
Andreas

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.