Scribd Logo
Upload

Welcome to Scribd!

  • 62 views

    Managing Range Names With VBA

    Uploaded by

    prsiva2420034066
    This document discusses how to manage range names in VBA. It explains how to create, list properties of, and delete range names programmatically. Code examples are provided to demonstrate creating workbook-level and sheet-level names, dynamically generating names from a table, and deleting names with invalid references. Managing range names with VBA allows automating common tasks and looping through or manipulating names in code.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd

    Managing Range Names With VBA

    Uploaded by

    prsiva2420034066
    62 views4 pages
    This document discusses how to manage range names in VBA. It explains how to create, list properties of, and delete range names programmatically. Code examples are provided to demonstrate creating workbook-level and sheet-level names, dynamically generating names from a table, and deleting names with invalid references. Managing range names with VBA allows automating common tasks and looping through or manipulating names in code.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses how to manage range names in VBA. It explains how to create, list properties of, and delete range names programmatically. Code examples are provided to demonstrate creating workbook-level and sheet-level names, dynamically generating names from a table, and deleting names with invalid references. Managing range names with VBA allows automating common tasks and looping through or manipulating names in code.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    62 views4 pages

    Managing Range Names With VBA

    Uploaded by

    prsiva2420034066
    This document discusses how to manage range names in VBA. It explains how to create, list properties of, and delete range names programmatically. Code examples are provided to demonstrate creating workbook-level and sheet-level names, dynamically generating names from a table, and deleting names with invalid references. Managing range names with VBA allows automating common tasks and looping through or manipulating names in code.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 4

    Managing range names with VBA -- DataWright Information Services

    Page 1 of 4

    Managing range names with VBA


    If you start using range names extensively, and you find yourself needing to add or remove names from workbooks, knowing how to do it in code will save you a lot of time. When you manipulate or loop through range names in VBA you will need to use the Names collection. The code samples on this page should help you to become proficient with using names in VBA.

    Creating range names in VBA


    Range names have a number of properties in VBA. The two that must be defined when you create a name in code are the Name and the RefersTo properties. Note: If you have a particular reason for hiding a name from general view and making it accessible only from VBA, you can also use the Visible property and set it to False. The Visible property hides the name from the Name box drop-down and the Insert Names dialog. It will not display if you list the names using F3 > Paste list. However, you can refer to the name in VBA and use its value in VBA routines. As an example, the following code creates a name referring to Sheet2!$A$1:$F$50
    Sub MakeName() ActiveWorkbook.Names.Add Name:="PTable", RefersTo:="Sheet2!$A$1:$F$50" End Sub

    This will create the name, using the current selection as the reference
    Sub MakeName_Selection() Sheets("Sheet2").Activate Range("A1").CurrentRegion.Select ActiveWorkbook.Names.Add Name:="PTable", RefersTo:=Selection End Sub

    Let's assume that you need to find a specific cell first, then create a range 60 columns wide, offset from the found cell. You could use code like this:
    'find row for Lot Profile Range("A1").Select Cells.Find(What:="Lot Profile", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate ActiveCell.Offset(1,5).Resize(1,60).Select ActiveWorkbook.Names.Add Name:="Lots", RefersTo:=Selection 'you can also create names based on an offset of the current selection: ActiveWorkbook.Names.Add Name:="Deposits", RefersTo:=Selection.Offset(1,0)

    Listing properties of names


    The following code attempts to list all of the possible properties of a given name. As you can see in the output table below the code, not all properties can be retrieved for all names.

    http://www.datawright.com.au/excel_resources/excel_range_names_vba.htm 11/21/2012

    Managing range names with VBA -- DataWright Information Services

    Page 2 of 4

    Sub NameProperties() Dim nm As Name Set nm = Names("Sheetlist") On Error Resume Next With nm Debug.Print "Category: " & nm.Category 'valid only with XLM macros Debug.Print "CategoryLocal: " & nm.CategoryLocal Debug.Print "Creator: " & nm.Creator Debug.Print "Comment: " & nm.Comment 'new in Excel 2007 Debug.Print "Index: " & nm.Index Debug.Print "MacroType: " & nm.MacroType 'valid only with XLM macros. Returns xlNone ot Debug.Print "Name: " & nm.Name Debug.Print "NameLocal: " & nm.NameLocal Debug.Print "Parent: " & nm.Parent Debug.Print "RefersTo: " & nm.RefersTo Debug.Print "RefersToLocal: " & nm.RefersToLocal Debug.Print "RefersToR1C1: " & nm.RefersToR1C1 Debug.Print "RefersToR1C1Local: " & nm.RefersToR1C1Local Debug.Print "RefersToRange: " & nm.RefersToRange Debug.Print "ShortcutKey: " & nm.ShortcutKey 'valid only with XLM macros Debug.Print "ValidWorkbookParameter: " & nm.ValidWorkbookParameter 'new in Excel 2007 Debug.Print "Value: " & nm.Value Debug.Print "Visible: " & nm.Visible Debug.Print "WorkbookParameter: " & nm.WorkbookParameter 'new in Excel 2007 End With End Sub

    Value Creator 1480803660 (code for XLCreator) Index 1327 MacroType -4142 (code for xlNone) Name Sheetlist NameLocal Sheetlist RefersTo =OFFSET(Scenario!$D$4,0,0,COUNTA(Scenario!$D:$D)-1,1) RefersToLocal =OFFSET(Scenario!$D$4,0,0,COUNTA(Scenario!$D:$D)-1,1) RefersToR1C1 =OFFSET(Scenario!R4C4,0,0,COUNTA(Scenario!C4)-1,1) RefersToR1C1Local =OFFSET(Scenario!R4C4,0,0,COUNTA(Scenario!C4)-1,1) ValidWorkbookParameter False Value =OFFSET(Scenario!$D$4,0,0,COUNTA(Scenario!$D:$D)-1,1) Visible True WorkbookParameter False

    Property

    Comments on the properties


    Some properties (Category, MacroType and ShortcutKey) are only valid if the name is used in an XLM macro. Others (Comment, WorkbookParameter and ValidWorkbookParameter) are new to Excel 2007. The properties ending in Local return the same result as their counterparts without Local. The difference is that, for example, RefersTo will use the language currently being used in the VBA code; RefersToLocal will use the language in the user's regional settings.

    http://www.datawright.com.au/excel_resources/excel_range_names_vba.htm 11/21/2012

    Managing range names with VBA -- DataWright Information Services

    Page 3 of 4

    Creating workbook-level names


    To create a workbook-level name, you add the name to the ActiveWorkbook.
    Sub MakeName_Workbook() ActiveWorkbook.Names.Add Name:="Stages", RefersTo:=Selection End Sub

    Creating sheet-level names


    To create a sheet-level name, you add it to the ActiveSheet. Otherwise, you create sheetlevel names exactly as you would for workbook-level names.
    Sub MakeName_Worksheet() ActiveSheet.Names.Add Name:="Stages", RefersTo:=Selection End Sub

    Creating dynamic named ranges


    If you need to create a large number of dynamic ranges for charting, it can be tedious to do it by hand. The following code will create dynamic ranges from a table with headings in Row 1. The headings will be used as the range names.
    Sub DynamicNames() Dim LastCol As Long, _ LabelRow As Long, _ Col As Long Dim sName As String Dim c As Range Dim Sht As String 'assign row and column parameters '**adjust for the row containing your headings LabelRow = 1 LastCol = Range("IV1").End(xlToLeft).Column 'grab sheet name Sht = "'" & ActiveSheet.Name & "'" For Each c In Range(Cells(LabelRow, 1), Cells(LabelRow, LastCol)) Col = c.Column sName = c.Value If Len(sName) > 1 Then 'replace spaces with underscores sName = Replace(sName, " ", "_", 1) 'create the name ActiveWorkbook.Names.Add Name:=sName, RefersToR1C1:= _ "=OFFSET(" & Sht & "!R2C" & Col & ",0,0,COUNTA(" & Sht & "!C" & Col & ")-1,1)" End If Next c End Sub

    Assuming that you ran this on a sheet called Chart Data, with NSW Sales in Column D, the corresponding range name would be called NSW_Sales. Its reference as created by the above code would be
    =OFFSET('Chart Data'!$D$2,0,0,COUNTA('Chart Data'!$D:$D)-1,1)

    http://www.datawright.com.au/excel_resources/excel_range_names_vba.htm 11/21/2012

    Managing range names with VBA -- DataWright Information Services

    Page 4 of 4

    Deleting names in VBA


    Deleting names with invalid references
    Over time, as you delete worksheets and ranges, you will end up with names that have broken references. To clean them out, use this code:
    Sub DeleteBadRefs() Dim nm As Name For Each nm In ActiveWorkbook.Names If Instr(1, nm.RefersTo, "#REF!")>0 Then 'List the name before deleting Debug.Print nm.Name & ": deleted" nm.Delete End If Next nm End Sub

    http://www.datawright.com.au/excel_resources/excel_range_names_vba.htm 11/21/2012

    You might also like