1. Sub FoxySingleCellNamedRanges()
  2. 10   Rem -2 Range Info etc.
  3. 20   Dim WbMain As Workbook, dataWb1xls As Workbook, dataWb2xlsx As Workbook
  4. 30    Set WbMain = Workbooks("MasturFile.xlsm") 'Set WbMain = ThisWorkbook
  5. 40    Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
  6. 50    Set dataWb1xls = Workbooks("Data1.xls")
  7. 60    Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
  8. 70    Set dataWb2xlsx = Workbooks("Data2.xlsx")
  9. 80 '
  10. 90   Dim LisWkBkPath As String: Let LisWkBkPath = "=" & "'" & ThisWorkbook.Path & "\"
  11. 100  '-2b) Some variables to hold a full reference string which we will use in places where we might need any of these variations for a cell reference  Sheet7!B5  [myWorkbook.xlsm] Sheet4!B5  'G:\Desktop\MyFolder\[DataFile.xlsx]Tabelle1'!B5   The last one is the form we hold in the variables. Excel and Excel VBA , usually has no issues if you use the full reference in situations where one of the shorter versions may have been sufficient. But on the other hand,  you may get unexpected problems if you used a shorter version , and Excel then  guesses wrongly  for the remaining part, which I believe it always adds internally, ( possibly at some compiling stage ) , before it uses it.
  12. 110  Dim MBkTab1B5 As String ' To hold full string reference to B5 in Master Workbook
  13. 120   Let MBkTab1B5 = "=" & "'" & ThisWorkbook.Path & "\" & "[" & "MasturFile.xlsm" & "]" & "Tabelle1" & "'" & "!" & "B5"
  14. 130  Dim Dat1Tab1B5 As String ' B5 in data1 workbook
  15. 140   Let Dat1Tab1B5 = "=" & "'" & ThisWorkbook.Path & "\" & "[" & "Data1.xls" & "]" & "Tabelle1" & "'" & "!" & "B5"
  16. 150
  17. 160  Rem -1 Error handler
  18. 170   On Error GoTo ErrorHandlerCodeSection:
  19. 180  GoTo PastErrorHandler
  20. 190 ErrorHandlerCodeSection:
  21. 200   MsgBox prompt:="Code errored at line  " & Erl & " , error was:" & vbCrLf & vbCrLf & Err.Number & "     " & Err.Description
  22. 210   Debug.Print Err.Number & "     " & Err.Description
  23. 220   Resume Next
  24. 230 PastErrorHandler:
  25. 240  Rem 0 Clean up
  26. 250  Dim WkBk As Workbook
  27. 260      For Each WkBk In Workbooks
  28. 270       Call FukYaWkBkNames(WkBk)
  29. 280       'Call GeTchaNms(280, WkBk)
  30. 290      Next WkBk
  31. 300   Workbooks("Data1.xls").Close savechanges:=True
  32. 310   Workbooks("Data2.xlsx").Close savechanges:=True
  33. 312  '0b) clear the entire data ranges in the first worksheet in the main workbook, both headers and data
  34. 315   ThisWorkbook.Worksheets.Item(1).Range("B5:C12").ClearContents
  35. 320  Rem _1) Data1 "Food" header
  36. 330  '1a) Data1 cell Workbook Scoped to its workbook : Info needed for a range in that data file is held in the workbooks name objects collection object of that workbook
  37. 340   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
  38. 350   Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
  39. 360   dataWb1xls.Names.Add Name:="Dta1Foodheader", RefersTo:=Application.Range(Dat1Tab1B5) ' A personal preference of mine is , once again, to use a full reference. This time it is  in the Refers To range. This Refers To:= argument would never need the full file path reference, as the range referenced must be to a range in an open book. Never the less, as usual, VBA accepts the full reference
  40. 370   Call GeTchaNms(370, dataWb1xls)
  41. 380   dataWb1xls.Close savechanges:=True ' I don't need the workbook open for the next line to work, but I made Added a named range object so I must save the changes for the next line to work as that named range is referenced
  42. 390   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' "Going" to Workbook  Data1.xls
  43. 400   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "[Data1.xls]Tabelle4'!Dta1Foodheader" ' "Going" to any worksheet in  Data1.xls
  44. 410  '1b) Data1 cell Worksheet Scoped to one of its worksheets: Info needed is held in the named objects object of its second worksheets
  45. 420   Rem _1 Add some named ranges
  46. 430   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
  47. 440   Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
  48. 450   dataWb1xls.Worksheets.Item(2).Names.Add Name:="Ws2Dta1Foodheader", RefersTo:=Application.Range(Dat1Tab1B5)
  49. 460   Call GeTchaNms(460, dataWb1xls)
  50. 470   dataWb1xls.Close savechanges:=True ' I don't need the workbook open for the next line to work, but I made Added a named range object so I must save the changes for the next line to work as that named range is referenced
  51. 480   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "[Data1.xls]Tabelle2'!Ws2Dta1Foodheader"
  52. 490  '1b)(ii)
  53. 500   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
  54. 510   Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
  55. 520   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "[Data1.xls]Tabelle2'!Ws2Dta1Foodheader"
  56. 530   dataWb1xls.Close savechanges:=False ' I made no changes intentionally , so save without changes in case I accidentally changed anything
  57. 540  '1c) Data1 cell Workbook Scoped to a different (open) workbook : Info needed for a range in the data 1 file is held in the workbooks name objects collection object of that workbook, the main file in this case
  58. 550   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
  59. 560   Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
  60. 570   WbMain.Names.Add Name:="MainDta1Foodheader", RefersTo:=Application.Range(Dat1Tab1B5)
  61. 580   dataWb1xls.Close savechanges:=False ' I had this open for the  Refers To:=  above, but I did not change anything, for example, this time i was not doing anything to any of its named range objects, so just iin case I accidentally changed anything I will close without saving any changes
  62. 590   Call GeTchaNms(590, WbMain)
  63. 600   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "MasturFile.xlsm'!MainDta1Foodheader" ' "Going" to Workbook  MasturFile.xlsm
  64. 610   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "[MasturFile.xlsm]Tabelle4'!MainDta1Foodheader" ' "Going" to any worksheet in MasturFile.xlsm
  65. 620  '1d) This is an attempt to get at the named range object in a roundabout sort of a way. Here the data 1 cell s scoped to the second data file, "Data2.xlsx" ( Workbooks scoped to workbook "Data2.xlsx" )
  66. 630   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
  67. 640   Set dataWb1xls = Workbooks("Data1.xls")
  68. 650   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
  69. 660   Set dataWb2xlsx = Workbooks("Data2.xlsx")
  70. 670   dataWb2xlsx.Names.Add Name:="Dta2Dta1Foodheader", RefersTo:=Application.Range(Dat1Tab1B5)
  71. 680   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "Data2.xlsx'!Dta2Dta1Foodheader" ' "Going" to Workbook  MasturFile.xlsm
  72. 690   dataWb1xls.Close savechanges:=False ' I had this open for the  Refers To:=  above, but I did not change anything, for example, this time i was not doing anything to any of its named range objects, so just iin case I accidentally changed anything I will close without saving any changes
  73. 700   dataWb2xlsx.Close savechanges:=True ' A name object was Added, so I have a change to save
  74. 710   Let Application.Range(MBkTab1B5).Value = Application.Range(MBkTab1B5).Value ' I have done this here to "catch" the value put in, as it seems to vanish if I re enter the formula ??
  75. 720  Rem 2 Experiments with named ranges in the LHS , like in Range("rngNamed") =
  76. 730  '2a) scope to a data file
  77. 740   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
  78. 750   Set dataWb2xlsx = Workbooks("Data2.xlsx") ' Open an arbritrary data file to use one if its names objects as the place to go to get the info about the named range
  79. 760   dataWb2xlsx.Names.Add Name:="Dta2MainFoodheader", RefersTo:=Application.Range(MBkTab1B5)
  80. 770   Call GeTchaNms(770, dataWb2xlsx)
  81. 780   Let Application.Range(LisWkBkPath & "Data2.xlsx'!Dta2MainFoodheader").Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' LHS is going to workbook Data2.xlsx     RHS is "Going" to Workbook  Data1.xls
  82. 790   dataWb2xlsx.Close savechanges:=True ' A name object was Added, so I have a change to save
  83. 800   Let Application.Range(LisWkBkPath & "Data2.xlsx'!Dta2MainFoodheader").Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' LHS is going to workbook Data2.xlsx      RHS is "Going" to Workbook  Data1.xls
  84. 810  '2b) Workbooks Scope to main workbook: Info for named range is in Name Objects collection of Main workbook
  85. 820   WbMain.Names.Add Name:="MainFoodheader", RefersTo:=Application.Range(MBkTab1B5)
  86. 830   Let Application.Range(LisWkBkPath & WbMain.Name & "'!MainFoodheader").Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' LHS is going to workbook Data2.xlsx      RHS is "Going" to Workbook  Data1.xls
  87. 840   Call GeTchaNms(840, WbMain)
  88. 850  Rem 3 Bring in Header "Suppliment" from data 2 workbook directly without named ranges
  89. 860   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
  90. 870   Set dataWb2xlsx = Workbooks("Data2.xlsx") ' Needed for next line
  91. 880   Let Application.Range("=" & "'" & WbMain.Path & "\" & "[" & WbMain.Name & "]" & WbMain.Worksheets.Item(1).Name & "'" & "!" & "B10").Value = "=" & "'" & dataWb2xlsx.Path & "\" & "[" & dataWb2xlsx.Name & "]" & dataWb2xlsx.Worksheets.Item(1).Name & "'" & "!" & "B10"
  92. 890   dataWb2xlsx.Close savechanges:=False
  93. End Sub
  94.  
  95.  
  96.  
  97. Sub FoxyMultiCellNamedRanges()
  98. 10   Rem -2 Range Info etc.
  99. 20   Dim WbMain As Workbook, dataWb1xls As Workbook, dataWb2xlsx As Workbook
  100. 30    Set WbMain = Workbooks("MasturFile.xlsm") 'Set WbMain = ThisWorkbook
  101. 40    Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
  102. 50    Set dataWb1xls = Workbooks("Data1.xls")
  103. 60    Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
  104. 70    Set dataWb2xlsx = Workbooks("Data2.xlsx")
  105. 80   '
  106. 90   Dim LisWkBkPath As String: Let LisWkBkPath = "=" & "'" & ThisWorkbook.Path & "\"
  107. 100  '-2b) Some variables to hold a full reference string which we will use in places where we might need any of these variations for a cell reference  Sheet7!B5  [myWorkbook.xlsm] Sheet4!B5  'G:\Desktop\MyFolder\[DataFile.xlsx]Tabelle1'!B5   The last one is the form we hold in the variables. Excel and Excel VBA , usually has no issues if you use the full reference in situations where one of the shorter versions may have been sufficient. But on the other hand,  you may get unexpected problems if you used a shorter version , and Excel then  guesses wrongly  for the remaining part, which I believe it always adds internally, ( possibly at some compiling stage ) , before it uses it.
  108. 110  Dim MBkTab1B5 As String ' To hold full string reference to B5 in Master Workbook
  109. 120   Let MBkTab1B5 = "=" & "'" & ThisWorkbook.Path & "\" & "[" & "MasturFile.xlsm" & "]" & "Tabelle1" & "'" & "!" & "B5"
  110. 130  Dim Dat1Tab1B5 As String ' B5 in data1 workbook
  111. 140   Let Dat1Tab1B5 = "=" & "'" & ThisWorkbook.Path & "\" & "[" & "Data1.xls" & "]" & "Tabelle1" & "'" & "!" & "B5"
  112. 150 '
  113. 160  Rem -1 Error handler
  114. 170   On Error GoTo ErrorHandlerCodeSection:
  115. 180  GoTo PastErrorHandler
  116. 190 ErrorHandlerCodeSection:
  117. 200   MsgBox prompt:="Code errored at line  " & Erl & " , error was:" & vbCrLf & vbCrLf & Err.Number & "     " & Err.Description
  118. 210   Debug.Print Err.Number & "     " & Err.Description
  119. 220   Resume Next
  120. 230 PastErrorHandler:
  121. 240  Rem 0 Clean up
  122. 250  '0a) remove any name objects made in last routine in the main file or the two data files
  123. 260  Dim WkBk As Workbook
  124. 270      For Each WkBk In Workbooks
  125. 280       Call FukYaWkBkNames(WkBk)
  126. 290       'Call GeTchaNms(280, WkBk)
  127. 300      Next WkBk
  128. 310   Workbooks("Data1.xls").Close savechanges:=True
  129. 320   Workbooks("Data2.xlsx").Close savechanges:=True
  130. 330  '0b) clear the entire data ranges in the first worksheet in the main workbook, both headers and data
  131. 340   ThisWorkbook.Worksheets.Item(1).Range("B5:C12").ClearContents
  132. 350  Rem _1) Data1 "Food" header
  133. 360  '1a) Data1 cell Workbook Scoped to its workbook : Info needed for a range in that data file is held in the workbooks name objects collection object of that workbook
  134. 370   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
  135. 380   Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
  136. 390   dataWb1xls.Names.Add Name:="Dta1Foodheader", RefersTo:=Application.Range(Dat1Tab1B5) ' A personal preference of mine is , once again, to use a full reference. This time it is  in the Refers To range. This Refers To:= argument would never need the full file path reference, as the range referenced must be to a range in an open book. Never the less, as usual, VBA accepts the full reference
  137. 400   dataWb1xls.Close savechanges:=True ' I don't need the workbook open for the next line to work, but I made Added a named range object so I must save the changes for the next line to work as that named range is referenced
  138. 410   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' "Going" to Workbook  Data1.xls
  139. 420   Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "[Data1.xls]Tabelle4'!Dta1Foodheader" ' "Going" to any worksheet in  Data1.xls
  140. 430  Rem 2 Experiments with named ranges in the LHS , like in Range("rngNamed") =
  141. 440  '2b) Workbooks Scope to main workbook: Info for named range is in Name Objects collection of Main workbook
  142. 450   WbMain.Names.Add Name:="MainFoodheader", RefersTo:=Application.Range(MBkTab1B5)
  143. 460   Let Application.Range(LisWkBkPath & WbMain.Name & "'!MainFoodheader").Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' LHS is going to workbook Data2.xlsx      RHS is "Going" to Workbook  Data1.xls
  144. 470  Rem 3 Bring in Header "Suppliment" from data 2 workbook directly without named ranges
  145. 480   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
  146. 490   Set dataWb2xlsx = Workbooks("Data2.xlsx") ' Needed for next line
  147. 500   Let Application.Range("=" & "'" & WbMain.Path & "\" & "[" & WbMain.Name & "]" & WbMain.Worksheets.Item(1).Name & "'" & "!" & "B10").Value = "=" & "'" & dataWb2xlsx.Path & "\" & "[" & dataWb2xlsx.Name & "]" & dataWb2xlsx.Worksheets.Item(1).Name & "'" & "!" & "B10"
  148. 510  '3b) "Fixed vector" B11 into main workbook at B11
  149. 520   Let Application.Range("=" & "'" & WbMain.Path & "\" & "[" & WbMain.Name & "]" & WbMain.Worksheets.Item(1).Name & "'" & "!" & "B11").Value = "=" & "'" & dataWb2xlsx.Path & "\" & "[" & dataWb2xlsx.Name & "]" & dataWb2xlsx.Worksheets.Item(1).Name & "'" & "!" & "B11"
  150. 530  '3c) "Fixed vector" B11 into main workbook into  B11 C11 B12 and C12
  151. 540   Let Application.Range("=" & "'" & WbMain.Path & "\" & "[" & WbMain.Name & "]" & WbMain.Worksheets.Item(1).Name & "'" & "!" & "B11:C12").Value = "=" & "'" & dataWb2xlsx.Path & "\" & "[" & dataWb2xlsx.Name & "]" & dataWb2xlsx.Worksheets.Item(1).Name & "'" & "!" & "B11"
  152. 550   dataWb2xlsx.Close savechanges:=False
  153. 560 '
  154. 570   Application.Range("=" & "'" & WbMain.Path & "\" & "[" & WbMain.Name & "]" & WbMain.Worksheets.Item(1).Name & "'" & "!" & "B11:C12").ClearContents ' remove the data from the main file from data file 2 so as to do the same again using named ranges in the next code section, Rem 4
  155. 580  Rem 4 named ranges for data ranges in data workbooks and main file
  156. 590  '4a) Workbook to store name range object
  157. 600  Dim WbNmeObjs As Workbook
  158. 610   Workbooks.Open Filename:=ThisWorkbook.Path & "\StoredNamedRangeNameObjects.xls"
  159. 620   Set WbNmeObjs = Workbooks("StoredNamedRangeNameObjects.xls")
  160. 630   Call FukYaWkBkNames(WbNmeObjs)
  161. 640   Call GeTchaNms(640, WbNmeObjs)
  162. 650  '4b) named ranges for data in data range from data 1 workbook, "Data1.xls
  163. 660   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
  164. 670   Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
  165. 680   WbNmeObjs.Worksheets("DataFileNameObjects").Names.Add Name:="NmsObjDta1Data", RefersTo:=Application.Range("='" & ThisWorkbook.Path & "\[Data1.xls]Tabelle1'!B6:C7")
  166. 690   Call GeTchaNms(690, WbNmeObjs)
  167. 700  '4c) named ranges for data in data range from data 2 workbook, "Data2.xlsx
  168. 710   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
  169. 720   Set dataWb2xlsx = Workbooks("Data2.xlsx") ' We need this open for the referred to range in the RefersTo:= range reference below
  170. 730   WbNmeObjs.Worksheets("DataFileNameObjects").Names.Add Name:="NmsObjDta2Data", RefersTo:=Application.Range("='" & ThisWorkbook.Path & "\[Data2.xlsx]Tabelle1'!B11:C12")
  171. 740   Call GeTchaNms(740, WbNmeObjs)
  172. 750  '4d) named ranges for data import ranges in main workbook, ( This workbook )
  173. 760  '4d(i) data from Data 1 file import range in main book
  174. 770   WbNmeObjs.Worksheets("MainFileNameObjects").Names.Add Name:="NmsObjDta1Import", RefersTo:=Application.Range("='" & ThisWorkbook.Path & "\[MasturFile.xlsm]Tabelle1'!B6:C7")
  175. 780  '4d(ii) data from Data 2 file import range in main book
  176. 790   WbNmeObjs.Worksheets("MainFileNameObjects").Names.Add Name:="NmsObjDta2Import", RefersTo:=Application.Range("='" & ThisWorkbook.Path & "\[MasturFile.xlsm]Tabelle1'!B11:C12")
  177. 800  Call GeTchaNms(800, WbNmeObjs)
  178. 810  ' Close data books - I don't need them open to get at their named range data or their named range data
  179. 820   dataWb1xls.Close savechanges:=False ' I needed the workbook open for the referes to range reference and the  GeTchaNms( )  to work, but i added no names to it, so I did not intentiionally make any changes, so I will close with changes false in case I acidentally changed anything
  180. 830   dataWb2xlsx.Close savechanges:=False ' I needed the workbook open for the referes to range reference and the  GeTchaNms( )  to work, but i added no names to it, so I did not intentiionally make any changes, so I will close with changes false in case I acidentally changed anything
  181. 840  Rem 5 Using the Added data named ranges to bring in data from the data files into the main workbook
  182. 850  '5a) Food data data range ( B6:C7 in main File and B6:C7 in data 1 file )
  183. 860   Let Application.Range("='" & ThisWorkbook.Path & "\[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta1Import").FormulaArray = "='" & ThisWorkbook.Path & "\[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta1Data"
  184. 870  '5a)(ii) As file "StoredNamedRangeNameObjects.xls" is open we can also use
  185. 880   Let Application.Range("='[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta1Import").FormulaArray = "='[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta1Data"
  186. 890  '5b) Food data data range ( B11:C12 in main File and B11:C12 in data 2 file )
  187. 900   Let Application.Range("='" & ThisWorkbook.Path & "\[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta2Import").FormulaArray = "='" & ThisWorkbook.Path & "\[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta2Data"
  188. 910  '5b)(ii) As file "StoredNamedRangeNameObjects.xls" is open we can also use
  189. 920   Let Application.Range("='[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta2Import").FormulaArray = "='[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta2Data"
  190. 930  '5c)
  191. 940   WbNmeObjs.Close savechanges:=True ' Save the named range info on closing
  192. 950  '5d) Optional Change all formulas to their values
  193. 960   Let WbMain.Worksheets.Item(1).UsedRange.Value = WbMain.Worksheets.Item(1).UsedRange.Value
  194. 970  Rem 6 Final check of all named ranges
  195. 980  '6a) Open all workbooks so as to access Named range objects in them
  196. 990   Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
  197. 1000   Set dataWb1xls = Workbooks("Data1.xls")
  198. 1010  Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
  199. 1020  Set dataWb2xlsx = Workbooks("Data2.xlsx")
  200. 1030   Workbooks.Open Filename:=ThisWorkbook.Path & "\StoredNamedRangeNameObjects.xls"
  201. 1040  Set WbNmeObjs = Workbooks("StoredNamedRangeNameObjects.xls")
  202. 1050 '6b) Loop through all open workbooks and check named range object info
  203. 1060 Dim Wbtemp As Workbook
  204. 1070     For Each Wbtemp In Workbooks ' Going through each workbook in the Workbooks collection object of open workbooks
  205. 1080      Call GeTchaNms(1080, Wbtemp)
  206. '1085        If Wbtemp.Name <> ThisWorkbook.Name Then Wbtemp.Close savechanges:=False ' Close all but this workbook - can't do this here - I might need them in the next use of GeTchaNms
  207. 1090     Next Wbtemp
  208.       'close workbooks
  209. 1100     For Each Wbtemp In Workbooks ' Going through each workbook in the Workbooks collection object of open workbooks
  210. 1110       If Wbtemp.Name <> ThisWorkbook.Name Then Wbtemp.Close savechanges:=False ' Close all but this workbook
  211. 1120     Next Wbtemp
  212.  
  213. End Sub
  214.  
  215. Sub FukYaWkBkNames(ByVal WnkBuk As Workbook)
  216. Dim Nme As Name
  217.     For Each Nme In WnkBuk.Names
  218.      Nme.Delete
  219.     Next Nme
  220. End Sub
  221. Sub GeTchaNms(ByVal CodLn As Long, ByVal WnkBuk As Workbook) ' To get info aboout all Name objects in a Workbook,m WnkBuk
  222. Dim Cnt As Long, Nme As Name, strOut As String
  223. ' Name objects in Workbook Names Colection object (Workbooks scope and Worksheets scope)
  224.     For Each Nme In WnkBuk.Names '   For convenience it goes through the Workbook named  objects collection object  for a workbook, as this has "its own" named range objects, that is to say the Workbooks scoped named range objects, and also the  named range objects for all the worksheets. So I do not need to go through the named range objects collection object of every worksheet in that workbook separately for every worksheet.
  225.      Let Cnt = Cnt + 1 ' A simple count number of each workbooks collection names objects in order it finds in looping them
  226.      ' We look now for a "!" in the string name, ...  Excel adds a bit onto the name we give to a name Added to a Worksheet’s named objects collection ( Scoped to a Worksheet’s named objects collection = worksheet “scoping”     We scoped to the Names object of a particular Worksheet = We Added the named range Name object to the names objects collection object of that particular Worksheet( and also indirectly  the names objects collection object of the workbook in which that worksheet is) = We scoped that named range to that Workbook = That named range has Workbook Scope  ). That added bit is something like “Sheet1!” . In other words, if you had given Name:=”MyName” in a code line for a worksheets scope Named range object Addition, like, …_   Worksheets("Sheet2").Names.Add Name:="FoodHeader", RefersTo:=____    _.. Then excel seems to hold and use a name like “Sheet2!FoodHeader"
  227.        If InStr(1, Nme.Name, "!", vbBinaryCompare) > 0 Then ' A name for a worksheet scope, has an extra bit added onto the name we gave it which includes a "!"
  228.         Let strOut = strOut & Cnt & "  Name object Name is  """ & Nme.Name & """" & vbCrLf & "(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 & "and if in a spreadsheet formula you only want to use" & vbCrLf & """" & Mid(Nme.Name, 1 + InStr(1, Nme.Name, "!", vbBinaryCompare)) & """  without any preceding info about" & vbCrLf & "where that named range is," & vbCrLf & "then you must  be in spreadsheet with tab name  """ & Nme.Parent.Name & """" & vbCrLf & "If you want to be sure to access this named range from anywhere," & vbCrLf & "you should use   """ & "=" & "'" & WnkBuk.Path & "\" & "[" & WnkBuk.Name & "]" & Nme.Parent.Name & "'" & "!" & Mid(Nme.Name, 1 + InStr(1, Nme.Name, "!", vbBinaryCompare)) & """"
  229.             If Nme.Parent.Name <> Application.Range(Nme.RefersTo).Parent.Name Then Let strOut = strOut & vbCrLf & "Note: The refered to range is in worksheet  """ & Application.Range(Nme.RefersTo).Parent.Name & """"
  230.             If Nme.Parent.Parent.Name <> Application.Range(Nme.RefersTo).Parent.Parent.Name Then Let strOut = strOut & vbCrLf & "Note also: The refered to range is in File  """ & Application.Range(Nme.RefersTo).Parent.Parent.Name & """"
  231.         Else ' Assume we have a workbook scoped name... we will see that a name for a workbook scope, remains just as we gave it
  232.         Let strOut = strOut & Cnt & "  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 & "and if in a spreadsheet formula you only want to use" & vbCrLf & """" & Nme.Name & """" & vbCrLf & "with no preceding info " & vbCrLf & "about where that named range is," & vbCrLf & "then you must be in any spreadsheet in workbook  """ & Nme.Parent.Name & """" & vbCrLf & "If you want to be sure to access this named range from anywhere," & vbCrLf & "you should use   """ & "=" & "'" & WnkBuk.Path & "\" & WnkBuk.Name & "'" & "!" & Nme.Name & """" & vbCrLf & "or alternatively use a similar string like this with any of the worksheets in it:" & vbCrLf & """" & "=" & "'" & WnkBuk.Path & "\" & "[" & WnkBuk.Name & "]" & WnkBuk.Worksheets.Item(1).Name & "'" & "!" & Nme.Name & """"
  233.             If WnkBuk.Name <> Nme.Parent.Name Then Let strOut = strOut & vbCrLf & "Note the refered to range is in" & vbCrLf & """" & Application.Range(Nme.RefersTo).Parent.Parent.Name & """  worksheets  """ & Application.Range(Nme.RefersTo).Parent.Name & """  !!"
  234.         End If
  235.      Let strOut = strOut & vbCrLf & vbCrLf & vbCrLf ' To clearly seperate each name object
  236.    Next Nme
  237.     If strOut = "" Then
  238.      MsgBox prompt:="The workbooks names object collection object is empty," & vbCrLf & "and so there are no named range objects in" & vbCrLf & "workbook   """ & WnkBuk.Name & """", Title:="At " & CodLn & " , for File  """ & WnkBuk.Name & """": Debug.Print "'_= ========" & vbCrLf & "You have no named range Name objects in workbook " & WnkBuk.Name & vbCrLf & vbCrLf
  239.     Else
  240.      MsgBox prompt:=strOut, Title:="At " & CodLn & " , """ & WnkBuk.Name & """ Names Collection has:-": Debug.Print "'_= ========" & vbCrLf & "You have " & Cnt & " named range Name objects in workbook " & WnkBuk.Name & vbCrLf & strOut
  241.     End If
  242. End Sub