CODEWITHSUNDEEP
excelvbaworksheet
johny why
johny why

Reputation: 2201

How to obtain workbook-scoped name?

In Excel, i have defined two names. Both have the same name, but different scope and value. One is scoped to a sheet, the other to the workbook.

enter image description here

I'm unable to retrieve the book-scoped name, either in a cell formula, or in VBA.

in VBA, the following returns the sheet-scoped name: ThisWorkbook.names("myName") enter image description here

in a cell, when i enter the following: =book.xlsb!myName Excel rewrites it as: Sheet1!myName

How to retrieve the workbook scoped name, in a cell formula and/or VBA?

Upvotes: 3

Views: 1758

Answers (3)

johny why
johny why

Reputation: 2201

This conflict only occurs with the default first sheet in a new workbook. The conflict does not occur with names defined on any other sheet.

If you rename the default 1st sheet, you'll still get the name conflict on that sheet-- it's not about the sheet-name.

You can delete the default 1st sheet, and the new-1st-sheet-used-to-be-2nd-sheet, will not have this issue. It's specific to the default 1st sheet in the new book.

If you never delete the default 1st sheet, then forevermore, on that sheet, the sheet-scoped defined name will override a book-scoped same-name.

Therefor, to answer the OP, the simplest, easiest way to obtain a book-scoped name when there's an identical sheet-scoped name, is to not attempt it on the default 1st sheet.

No VBA is needed.

Tested only on Excel 2010.

A comment and Microsoft link above led me to the answer. Thanks to that commenter.

Upvotes: 2

Julian Kuchlbauer
Julian Kuchlbauer

Reputation: 895

If you paste my code in a new Module, you can use this function within VBA and within Formulas, to obtain Names at Workbook-Scope:

Public Function §(ra As String)
Application.Volatile
Dim tName As Name
For Each tName In ThisWorkbook.Names
    If tName.Name = ra Then
        § = tName.RefersToRange.Value
        Exit Function
    End If
Next tName
End Function

Just use it with "=§("myRange")" in formulas or with §("myRange") in VBA. Using §("Sheet1!myRange") obtains the sheet scope Name as well.

Upvotes: 2

maksood alam
maksood alam

Reputation: 1

use this ThisWorkbook.Worksheets("Sheet1").range("myName").value

Upvotes: 0

Related Questions