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.