I just came here in the course of investigating the feasibility of changing the scope of a set of named ranges from Workbook scope to Worksheet scope. Having found that it works, I suspected that I knew why. As the following report shows, the reason that this is possible is that the trick creates a duplicate name that has worksheet scope.
Since the worksheet scoped name trumps the identical workbook scoped name when the worksheet to which it is scoped is active, it hides the workbook scoped name. On closer inspection, you will see that the worksheet scoped name is prefixed with the name of the sheet, followed by an exclamation point. It is this prefix that enables the Names collection, which is a subsidiary of the Workbook object, to store worksheet scoped names for copies of worksheets in its key-value collection. In programming terms, the Names collection is implemented as a Dictionary object.
Name 1 of 35: Name = _xlfn.CONCAT, Address = =#NAME?, Scope = Workbook
Name 2 of 35: Name = _xlfn.IFERROR, Address = =#NAME?, Scope = Workbook
Name 3 of 35: Name = Sheet2!achPlainText, Address = =Sheet2!$F$16, Scope = Worksheet
Name 4 of 35: Name = achPlainText, Address = =Sheet2!$F$16, Scope = Workbook
Name 5 of 35: Name = Ciphertext_length, Address = =Sheet2!$F$36, Scope = Workbook
Name 6 of 35: Name = Sheet1!CreateEncryptedMessage_EBP, Address = =Sheet1!$C$2, Scope = Worksheet
Name 7 of 35: Name = CreateEncryptedMessage_EBP, Address = ='Memory Map'!$E$7, Scope = Workbook
Name 8 of 35: Name = Sheet1!CreateEncryptedMessage_Offse_to_intPlaintextLen, Address = =Sheet1!$C$3, Scope = Worksheet
Name 9 of 35: Name = Sheet1!ebp_4018h_as_Seen_from_Main_Routine, Address = =Sheet1!$C$8, Scope = Worksheet
Name 10 of 35: Name = Sheet1!ebp_4018h_computed, Address = =Sheet1!$C$4, Scope = Worksheet
Name 11 of 35: Name = Sheet1!ebp_4018h_per_CodeView, Address = =Sheet1!$C$5, Scope = Worksheet
Name 12 of 35: Name = Sheet2!intPlainTextLength, Address = =Sheet2!$F$59, Scope = Worksheet
Name 13 of 35: Name = intPlainTextLength, Address = =Sheet2!$F$59, Scope = Workbook
Name 14 of 35: Name = Sheet2!lpMainEBP, Address = =Sheet2!$F$57, Scope = Worksheet
Name 15 of 35: Name = lpMainEBP, Address = =Sheet2!$F$57, Scope = Workbook
Name 16 of 35: Name = lpSubEBP, Address = =Sheet2!$F$14, Scope = Workbook
Name 17 of 35: Name = lpszPlainText, Address = =Sheet2!$F$60, Scope = Workbook
Name 18 of 35: Name = MAIN_OFFSET_TO_PLAINTEXT, Address = =Sheet2!$F$52, Scope = Workbook
Name 19 of 35: Name = MAIN_OFFSET_TO_PLAINTEXT_LENGTH, Address = =Sheet2!$F$49, Scope = Workbook
Name 20 of 35: Name = Sheet1!Main_routine_EBP, Address = =Sheet1!$C$6, Scope = Worksheet
Name 21 of 35: Name = Sheet1!Main_routine_EBP_less_CreateEncryptedMessage_EBP, Address = =Sheet1!$C$7, Scope = Worksheet
Name 22 of 35: Name = Sheet2!Message_1, Address = =Sheet2!$F$18, Scope = Worksheet
Name 23 of 35: Name = Message_1, Address = =Sheet2!$F$18, Scope = Workbook
Name 24 of 35: Name = Sheet2!Message_2, Address = =Sheet2!$F$19, Scope = Worksheet
Name 25 of 35: Name = Message_2, Address = =Sheet2!$F$19, Scope = Workbook
Name 26 of 35: Name = Offset_of_thief_s_EBP_to_callee_s_EBP, Address = ='Memory Map'!$E$5, Scope = Workbook
Name 27 of 35: Name = Offset_of_thief_s_ESP_to_callee_s_EBP, Address = ='Memory Map'!$E$4, Scope = Workbook
Name 28 of 35: Name = Sheet2!OFFSET_TO_FUNCTION_EBP, Address = =Sheet2!$F$55, Scope = Worksheet
Name 29 of 35: Name = OFFSET_TO_FUNCTION_EBP, Address = =Sheet2!$F$55, Scope = Workbook
Name 30 of 35: Name = Plaintext_length, Address = =Sheet2!$F$35, Scope = Workbook
Name 31 of 35: Name = Reference, Address = ='Location Usage per Disassembly'!$D$1, Scope = Workbook
Name 32 of 35: Name = Sheet2!SUB_OFFSET_TO_PLAINTEXT, Address = =Sheet2!$F$53, Scope = Worksheet
Name 33 of 35: Name = SUB_OFFSET_TO_PLAINTEXT, Address = =Sheet2!$F$53, Scope = Workbook
Name 34 of 35: Name = Sheet2!SUB_OFFSET_TO_PLAINTEXT_LENGTH, Address = =Sheet2!$F$50, Scope = Worksheet
Name 35 of 35: Name = SUB_OFFSET_TO_PLAINTEXT_LENGTH, Address = =Sheet2!$F$50, Scope = Workbook
Following is the VBA macro and private function that generated the report shown above.
Option Explicit
Public Sub EnumerateNamedRanges()
Dim lngOrd As Long: lngOrd = 0
Dim lngAll As Long: lngAll = ActiveWorkbook.Names.Count
Dim rn As Name
For Each rn In ActiveWorkbook.Names
lngOrd = lngOrd + 1
Debug.Print "Name " & lngOrd & " of " & lngAll & ": Name = " & rn.Name _
& ", Address = " & rn.RefersTo _
& ", Scope = " & RangeScopeShow(rn)
Next ' For Each rn In ActiveWorkbook.Names
EnumerateNamedRanges_End:
MsgBox "EnumerateNamedRanges Done!", _
vbExclamation, _
ActiveWorkbook.FullName
Exit Sub
EnumerateNamedRanges_Err:
MsgBox "Error encountered in subroutine EnumerateNamedRanges: " & Err.Description & " (Error # " & Err.Number, _
vbExclamation, _
ActiveWorkbook.FullName
Err.Clear
Resume EnumerateNamedRanges_End
End Sub ' EnumerateNamedRanges
Private Function RangeScopeShow(ByRef prn As Name) As String
If TypeOf prn.Parent Is Worksheet Then
RangeScopeShow = "Worksheet"
Else
RangeScopeShow = "Workbook"
End If ' If TypeOf prn.Parent Is Worksheet Then
End Function ' RangeScopeShow
The above code could easily be simplified by folding function
RangeScopeShow into the macro.
<strike></strike>
<strike></strike>
