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", _
        .Names("wsscope").Comment = " - name is unique in the worksheet"
    End With

End Sub