xlf | xlfAddNameScope macro


xlfAddNameScope - Name Manager view


Here is the VBA code to add Defined Names to a workbook with Scope as Workbook or Worksheet. The resultant names appear in figure 1.


defined names scope
Fig 1: Name Manager - showing Workbook (WBscope) and Worksheet (WSscope) scope defined names

xlfAddNameScope - the VBA code



Code 1: Macro xlfAddNameScope add defined names to workbook with WS and WS scope 
Sub xlfAddNameScope()

    ' WorkBook scope ========
    With ActiveWorkbook
        .Names.Add Name:="WBscope", _
            RefersToR1C1:="=Sheet1!R6C4"    ' absolute reference
        .Names("wbscope").Comment = " - name is unique in the workbook"
    End With

    ' WorkSheet scope =======
    With ActiveWorkbook.Worksheets("Sheet1")
        .Names.Add Name:="WSscope", _
            RefersToR1C1:="=Sheet1!R8C4"
        .Names("wsscope").Comment = " - name is unique in the worksheet"
    End With

End Sub