Here is an Excel VBA routine for converting a named range from Workbook level scope to Worksheet level scope.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Sub FixRangeNames() Application.Calculation = xlManual Call ConvertNamedRangeToLocalScope("N") Call ConvertNamedRangeToLocalScope("patch_size") Call ConvertNamedRangeToLocalScope("region_offset_X") Call ConvertNamedRangeToLocalScope("region_offset_Y") Call ConvertNamedRangeToLocalScope("H_image") Call ConvertNamedRangeToLocalScope("W_image") Call ConvertNamedRangeToLocalScope("H_region") Call ConvertNamedRangeToLocalScope("W_region") Call ConvertNamedRangeToLocalScope("X_spacing") Call ConvertNamedRangeToLocalScope("Y_spacing") Application.Calculation = xlAutomatic End Sub Sub ConvertNamedRangeToLocalScope(RangeName As String) Dim SheetNameString As String Dim RangeAddress As String Dim NamePropertyString As String Dim AddressPropertyString As String SheetNameString = "Information!" RangeAddress = Range(RangeName).Address AddressPropertyString = SheetNameString + RangeAddress NamePropertyString = SheetNameString + RangeName Names(RangeName).Delete Range(AddressPropertyString).Name = NamePropertyString End Sub |