I always use locally named range in all my spreadsheets, in fact I wrote a post about it earlier. I extend my love for named ranges even while writing VBA code for spreadsheet applications. Using a pure Offset function based code, or a Cell Reference based code, in my opinion, is not the best way to go. Having named ranges in worksheets, and updating them to include more data before processing is the best way to go.
I have to admit that setting your application up may cost extra time initially, but it has many advantages.
- No need for messy Count() and CountA() functions; or Range.End() methods.
- No need to worry about updating your loops, if you shift the ranges around.
- Neat, tractable code. Helps auditing the code easier; for instance, you can simply use the Find dialogue box to search for the range name.
The one thing that discourages me from using range objects in VBA is having to declare and initialize each of them. It will be incredibly time-consuming to type out the following statements for every range that you intend to use in your code:
'The Declaration Statement Dim VBAName as Range 'The Initialization Statement Set VBAName = Workbooks("...").Worksheets("...").Range("SheetRangeName")
Many a times, I have chickened out of writing proper code out of pure laziness. More importantly, clients may not always have the budget to afford immaculately structured code. Fueled by guilt, I realized I needed to create little tools to aid me in providing economic, but robust solutions. 
My first feather in the hat is this life saver of a tool that I created to help me declare Ranges in my VBA code. This user form lists all the locally named ranges in each sheet. And there are two text boxes which display the respective Declaration, and Initialization statements for the selected range names. The name for the range object, in VBA, is the same as the named range in excel; there is an option to add a prefix and or suffix. I usually prefix “rng” to all the range objects in my VBA projects. You may use the prefix and suffix text boxes to to specify which sheet the range belongs to. There are a couple of options for workbook and worksheet names. You may can choose to:
- Ignore the workbook and worksheet objects.
- Use the CodeName for worksheets
- Type in custom names, if they have already declared objects already.
- Users can type in a space to get a “.”, if they prefer using the With statement.
Finally, there are two buttons to copy the statements to clipboard. Users can jump to the active module in the Visual Basic Editor (VBE), by pressing ‘OK‘ in the message prompt that follows. I have to confess that I stole most of the set up from my infamous Range Name Navigator. Download VBA Range Declare.xla from Dropbox and let me know if it helps.
Reblogged this on Sutoprise Avenue, A SutoCom Source.
Except of course using sendkeys after opening the dialog in question with which you edit a property. Horrible.
I have no idea what that is. I should look it up!
Have you tried my Name Manager?
http://Www.jkp-ads.com/officemarketplacenm-en.asp
I am yet to. It keeps popping up everywhere, its high time I check it out.
It has been downloaded a massive number of time, Congratulations on that!.
Will let you know what I think about it. Cheers!
Haven’t really looked yet, but there is a typo on the menu and the userform: Declerations should be Declarations. Spotted that type somewhere else too.
Also, I get a runtime error when a range name does not point to a range directly but rather uses OFFSET to make the reference dynamic.
The Name object has lot sof bugs to worry about I can assure you. My Name Manager’s manual lists the ones I know of.
Wow, 2 typo’s in a post to report a typo. 🙂
Its an honour to have you look at my code! Thanks Jan!
How I wish the VBE had a spell checker!
And I only use rectangular ranges in my applications. I use a CurrentRange property based Range Name updating macro to avoid using dynamic ranges – Not a big fan or dynamic ranges!
But I sure have to look at your manual and familiarize myself with the disadvantages of the Name object. I used that to check for #Ref errors, but apparently I need to rethink that.
Hi Jan, I just tested it out with a simple Dynamic Range, I did not get a runtime error. Now I am confused, would you give me some more details?
Turned out the range name in question evaluated to an error (#REF!)
Perhaps a bit of error handling would help…
I will work on that. I actually borrowed a lot of code from my Range Name navigation tool.
http://strugglingtoexcel.wordpress.com/2014/01/25/range-navigator/
I have error handling code in place, but it works only on the click event. I need to change that. Thanks for letting me know.
Hi Jan, I just tested out your tool. It is AMAZING! Very detailed. I loved the fact that it searches for the range names in VBA code too. I also have one manager similar to that, haven’t published that yet. I created it to Clean up a massive spreadsheet application that was used to compare annuity prices for different yield curves.
The only additional thing that it does, is it would check if the named range has a label to the left or top, which is the same as the range’s name.
I am using Excel 2010, and ‘Where Names are used” dialogue box didn’t look like the screenshot in the manual: no tree design, it was a listbox. May be I am looking at the wrong window. The other thing I noticed was that the list box couldn’t scroll horizontally.
I like the highlight feature, but I prefer to directly jump to the range when I click the listbox, helps me quickly confirm its in the right place.
Brilliant tool. I keep getting a lot of comments that I am wasting my time reinventing your tools. I should up my game a lot I think. Its just that when you code something up from scratch, you get a deep feeling of satisfaction that no other feeling can match! 😀
I did have a treeview, but it causes compatibility problems, which is why we replaced it with a listbox. Of course we could now use my home-brewn treeview:
http://www.jkp-ads.com/articles/treeview.asp.
My tool takes a double-click to highlight the named range.
I can understand you like to develop your own, it is very rewarding.
A small warning before you decide to publish your own: Some bugs in the Name object require you to use sendkeys to let international users properly edit the refersto formula in their locale. Ugly, but the only way (really). And that requires even more workarounds regarding accented keys and keyboard settings. It is a mess, but Name Manager successfully caters for all of that.
What is the workaround to using the Name object?
Not sure what you mean?
Is there an alternative to using the name object?
Not that I know of: if you want to work with Names in VBA, well, you need the Name object 🙂
There is no disputing that! 😀