VB and VBA Users Source Code: Modifying Excel's Range Names

VB/VBA Source Code
Modifying Excel's Range Names

By:	Andrew Baker
Email (spam proof):	Email the originator of this post
Date:	Tuesday, November 27, 2001
Hits:	941
Category:	Office

Article:
The following code demonstrates how to create/modify Excel's Named Ranges dynamically. 'Purpose : Creates or modifies the range refered to by a Named Range 'Inputs : wkbAlter The name of the workbook containing the Range Name. ' sRangeName The name of the Range Name. ' rngNewRange The range that "sRangeName" is to refer to. ' [bCreateName] If True will create a new Range Name if it doesn't already exist. ' [bNameVisible] If False the name won't appear in either the Define Name, Paste Name, ' or Goto dialog box 'Outputs : Returns True on success. 'Author : Andrew Baker 'Date : 5/Nov/2001 03:17 'Notes : 'Revisions : Function RangeNameSet(wkbAlter As Excel.Workbook, sRangeName As String, rngNewRange As Excel.Range, Optional bCreateName As Boolean = True, Optional bNameVisible As Boolean = False) As Boolean On Error Resume Next 'Try altering the existing name wkbAlter.Names(sRangeName).RefersTo = rngNewRange If Err.Number > 0 And bCreateName = True Then 'Create a new named range Err.Clear wkbAlter.Names.Add sRangeName, rngNewRange End If 'Hide/unhide the Named Range wkbAlter.Names(sRangeName).Visible = bNameVisible RangeNameSet = (Err.Number = 0) On Error GoTo 0 End Function Sub Test() 'Create a new Range Name called "TESTRANGE" RangeNameSet ActiveWorkbook, "TESTRANGE", Range("B1:B5") End Sub
Terms and Conditions
Support this site Download a trial version of the Excel Workbook Rebuilder