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)
Sheets("--").Range("A4").Resize("Range("---").Rows.Count, Range("---").Columns.Count + 1).Name = "-----"
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 Excel.Name
Activeworkbook.Names.Add Name:="MyRange1", _
Set objName = Activeworkbook.Names("MyRange1")
objName.Name = "MyRange2"
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.
objRange = "Worksheeets("Sheet1").Range("A2:E6")
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.
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.
Hidden Named Ranges
It is possible to create hidden named ranges that do not appear in the Name Manager 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", _
This technique can be useful for storing worksheet passwords.
Resizing Named Ranges
Range("NamedRange").Resize(3,3).Name = "NamedRange"
Using the Evaluate Method
It is possible to use the Evaluate method to obtain the value for a specific named range.
Dim sValue As String
sValue = Application.Evaluate(MyNamedRange1)
sValue = Application.Evaluate("wbklevel")
sValue = Application.Evaluate("wshlevel")
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext