Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub NamedRangeScopes()
- 10 Call FukOffNames
- 20 Call getWbNames
- 30 Rem 1 Add 3 named ranges, 1(i) '_-in the Workbooks name object collection, and 1(ii) in the first worksheet name object collection and 1(iii) '_-in the second worksheet name object collection
- 40 '1(i) Add a Workbook names object in the Workbook name object collection of this workbook
- 50 ThisWorkbook.Names.Add Name:="Name1", RefersTo:=ThisWorkbook.Worksheets.Item(1).Range("A1") '_-in the Workbooks name object collection
- 60 'The form above is like ThisWorkbook.Names.Add Name:="Name1", RefersTo:=Worksheets(Sheet1).Range("A1")
- 70 '1(ii) Add a name object in the first worksheet's name object collection
- 80 ThisWorkbook.Worksheets.Item(1).Names.Add Name:="Name1", RefersTo:=ThisWorkbook.Worksheets.Item(1).Range("A1") '_-in the first worksheet name object collection
- 90 'The form above is like Worksheets("Sheet1).Names.Add Name:="Name1" , RefersTo:=Sheet1.Range("A1")
- 100 '1(iii) Add a name object in the second worksheet's name object collection
- 110 ThisWorkbook.Worksheets.Item(2).Names.Add Name:="Name2", RefersTo:=ThisWorkbook.Worksheets.Item(2).Range("A1") '_-in the second worksheet name object collection
- 120 'The form above is like Worksheets("Sheet2).Names.Add Name:="Name2" , RefersTo:=Sheet2.Range("A1")
- 130 Rem 2 Change the string name of a named range
- 140 Call GetChaNameObjects(140) ' Check out Info for all Name objects
- 150 '2a) Use Workbook names objects to Change the worksheet names object name that has the same name as the workbook names object name, change it twice, first using the workbook names object collection and then the worksheet names object collection
- 160 Let ThisWorkbook.Names(ThisWorkbook.Worksheets.Item(1).Name & "!" & "Name1").Name = "Name1_1"
- 170 ' The form above is like ThisWorkbook.Names("Sheet1!Name").Name = "Name1_1"
- 180 Call GetChaNameObjects(180)
- 190 Let ThisWorkbook.Worksheets.Item(1).Names(ThisWorkbook.Worksheets.Item(1).Name & "!" & "Name1_1").Name = "Name1_2"
- 200 Call GetChaNameObjects(200)
- 210 Let ThisWorkbook.Worksheets.Item(1).Names("Name1_2").Name = "Name1_3"
- 220 Call GetChaNameObjects(220)
- 230 '2b) use a Worksheet's (in this example the second worksheet's) name objects to Change the second worksheet's names object, ( we gave it "Name2", but Excel adds a bit so it looks like Sheet2!Name2" which you can get from a VBA code line like ThisWorkbook.Worksheets.Item(2).Name & "!" & "Name2" I do this just in case your second worksheet has a tab name other than Sheet2
- 240 Let ThisWorkbook.Worksheets.Item(2).Names("Name2").Name = "Name2_2"
- 250 ' Note: you could have equally done this: Let ThisWorkbook.Worksheets.Item(2).Names(ThisWorkbook.Worksheets.Item(2).Name & "!" & "Name2").Name = "Name2_2" , which is like Let ThisWorkbook.Worksheets.Item(2).Names("Sheet2!Name2").Name = "Name2_2"
- 260 Call GetChaNameObjects(260)
- 270 Rem 3 Change the string name of a named range, for example the one in the second worksheet names collection whichg we just renamed to "Name2_2" ,(which Excel holds as like "Sheet2!Name2_2")
- 280 '3a) Use Workbook names objects
- 290 Let ThisWorkbook.Names(ThisWorkbook.Worksheets.Item(2).Name & "!" & "Name2_2").RefersTo = ThisWorkbook.Worksheets.Item(2).Range("Z123")
- 300 Call GetChaNameObjects(300)
- 310 '3b) Use the second worksheets's names objects
- 320 Let ThisWorkbook.Worksheets.Item(2).Names("Name2_2").RefersTo = ThisWorkbook.Worksheets.Item(2).Range("X23")
- 330 Call GetChaNameObjects(330)
- End Sub
- Sub getWbNames()
- Dim Nme As Name, Cnt As Long
- For Each Nme In ThisWorkbook.Names
- Let Cnt = Cnt + 1
- Dim strNames As String: Let strNames = strNames & Cnt & " "
- If TypeOf Nme.Parent Is Worksheet Then ' https://stackoverflow.com/questions/8656793/progammatically-determine-if-a-named-range-is-scoped-to-a-workbook
- Let strNames = strNames & """" & Nme.Name & """ refers to the range ref """ & Nme & """ and can be referenced only from worksheet with tab Name """ & Nme.Parent.Name & """ ( Worksheet Scope ). ( That worksheet is in the workbook """ & Nme.Parent.Parent.Name & """ )" & vbCrLf & vbCrLf ' To get the ref range you can do Nme.RefresTo or Nme.Value or if you just do Nme , then it will default probably to Nme.Value
- Else
- Let strNames = strNames & """" & Nme.Name & """ refers to the range ref """ & Nme & """ and can be referenced from any sheet in the Workbook """ & Nme.Parent.Name & """ ( Workbook Scope )" & vbCrLf & vbCrLf
- End If
- Next Nme
- If strNames = "" Then
- MsgBox prompt:="I don't think you have any Names at the moment luvy"
- Else
- MsgBox prompt:=strNames, Title:="Spreadsheet Named range objects in " & ThisWorkbook.Name & " are:-": Debug.Print strNames
- End If
- End Sub
- Sub FukOffNames()
- Dim Nme As Name
- For Each Nme In ThisWorkbook.Names
- Nme.Delete
- Next Nme
- End Sub
- Sub GetChaNameObjects(ByVal CodLn As Long)
- Dim Nme As Name, strOut As String
- ' Name objects belonging in Workbook Names Colection (Workbooks scope)
- For Each Nme In ThisWorkbook.Names
- If InStr(1, Nme.Name, "!", vbBinaryCompare) > 0 Then ' we will see that a name for a worksheet scope, has an extra bit added onto the name we gave it which includes a "!"
- Let strOut = strOut & "Name object Name is """ & Nme.Name & """ (you gave """ & Mid(Nme.Name, 1 + InStr(1, Nme.Name, "!", vbBinaryCompare)) & """)" & vbCrLf & "It has worksheet scope and" & vbCrLf & "it refers to range """ & Nme.RefersTo & """" & vbCrLf & vbCrLf & vbCrLf
- Else ' we will see that a name for a workbook scope, remains just as we gave it
- Let strOut = strOut & "Name object Name is """ & Nme.Name & """ (the same as you gave)" & vbCrLf & "It has workbook scope and" & vbCrLf & "it refers to range """ & Nme.RefersTo & """" & vbCrLf & vbCrLf & vbCrLf
- End If
- Next Nme
- MsgBox prompt:="Workbook names situation at Code Line " & CodLn & vbCrLf & vbCrLf & strOut, Title:="Name objects in Workbook """ & ThisWorkbook.Name & """ Names Colection are:-": Debug.Print "Name objects in Workbook """ & ThisWorkbook.Name & """ Names Colection are:-" & vbCr & strOut
- ' Name objects belonging in Workbooks Names Colection (Worksheets scope)
- Dim Ws As Worksheet: Let strOut = ""
- For Each Ws In ThisWorkbook.Worksheets
- For Each Nme In Ws.Names
- Let strOut = strOut & "Name object name is """ & Nme.Name & """ (you gave """ & Mid(Nme.Name, 1 + InStr(1, Nme.Name, "!", vbBinaryCompare)) & """)" & vbCrLf & "It has worksheets scope and" & vbCrLf & "it belongs to the Names collection of worksheet """ & Ws.Name & """" & vbCrLf & "and it refers to range """ & Nme.RefersTo & """" & vbCrLf & vbCrLf
- Next Nme
- Next Ws
- MsgBox prompt:="Worksheets names situation at Code Line " & CodLn & vbCrLf & vbCrLf & strOut, Title:="Name objects in all the worksheets Names Colections are:-": Debug.Print "Name objects in all the worksheets Names Colections are:-" & strOut
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement