VBA Code

Named ranges can be very useful when using macros.
A specific cell or range of cells referred to in VBA code will become invalid if any rows or columns are inserted or deleted in front of it.
The advantage of referring to named ranges is that this does not happen and the cells are still valid.


Named Ranges can be particularly useful in VBA code when you need to refer to a particular cell or range of cells.
It is also easy to change the definition of a named range using VBA code to allow for any changes that you might make to the cells.



Removing the Equal Sign

This doesn't work you must use:

sValue = ActiveSheet.Names("MyNamedRange").RefersTo 

or remove the equal sign from the front

sValue = ActiveSheet.Names("MyNamedRange").Value 
sValue = Right(sValue,Len(sValue)-1)


Resizing

Sheets("--").Range("A4").Resize("Range("---").Rows.Count, Range("---").Columns.Count + 1).Name = "-----" 




Workbook Specific Named Ranges



Workbook Specific Named Ranges



Changing the Name

It is possible that after a named range has been created, you might want to change the actual name.
This can be done by using the Name property of a Name object.

Dim objName As Name 
Activeworkbook.Names.Add Name:="MyRange1", _
                         RefersTo:=2
Set objName = Activeworkbook.Names("MyRange1")
objName.Name = "MyRange2"
MsgBox ActiveWorkbook.Names("MyRange2").Value


You can return the cell range of a named range by using string =Sheets("SheetName").Range("NamedRange").Address.
If you reference Range("D4").Value in your VBA code it will be safer to create a names for the range "D4" and refer to that. If rows or columns get inserted / deleted then your VBA code will still work.


(Insert > Name > Create)

objRange = "Worksheeets("Sheet1").Range("A2:E6" 
objRange.CreateNames Left:=True

Creates names in the specified range, based on text labels in the sheet.
If you don't specify one of Top, Left, Bottom, or Right, Microsoft Excel guesses the location of the text labels, based on the shape of the specified range.



Hidden Named Ranges

It is possible to create hidden named ranges that do not appear in the (Insert > Name > Define) dialog box.
These can be a useful way to store worksheet and workbook specific information which the user cannot see.

ActiveSheet.Names.Add Name:="HiddenName" 
                      RefersTo:="$B$4"
                      Visible:=False

This technique can be useful for storing worksheet passwords.


It is possible to hide a named range after it has been created

Names.("VisibleName").Visible = False 

If a named range is created with the same name then the previous one will be over-written. This can be prevented by protecting the worksheet.



Resizing Named Ranges

Range("NamedRange").Resize(3,3).Name = "NamedRange" 



Using the Evaluate Method

It is possible to use the Evaluate method to quickly obtain a list of all the workbook specifc named ranges.


Dim arList As String() 
   Set arList = Evaluate[ActiveWorkbook.Names()]

You can also use it to obtain the value for a specifc named range

sValue = Evaluate[MyNamedRange1] 
sValue = [MyNamedRange1]


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext