Изменение размера именованного диапазона удаляет структуру имени диапазона

Всякий раз, когда я пытаюсь изменить размер или иным образом изменить расположение ячейки для диапазона, например От «A1: B1» до «A1: B2» Структура имени, связанная с диапазоном, удаляется. запись наблюдения для диапазона. Имя будет отображаться в столбце значений как

Ошибка, определяемая приложением или объектом

Кажется, не имеет значения, связано ли с ним имя или нет. Фактически, изменение имени с помощью RefersTo = автоматически удаляет структуру имени. Следующий код был получен из ответа на вопрос 11031891, Как переопределить именованный диапазон с помощью VBA?

    Option Explicit

Enum eColumn
  FIRSTCOLUMN = 1
  SECONDCOLUMN = 2
End Enum

Enum eRow
  FIRSTROW = 1
  SECONDROW = 2
End Enum

  Public Const scFred As String = "Fred"
  Public Const scFreda As String = "Freda"

  Public Const scAbsoluteValueRange As String = "AbsoluteValueRange"
  Public Const scResizeRange As String = "ResizeRange"

Sub ResizeIt()

  Dim iRow As Integer
  Dim nAbsoluteValue As Name
  Dim nResize As Name
  Dim rAbsoluteValue As Range
  Dim rResize As Range
  Dim ws As Worksheet

  Set ws = ActiveSheet

  '      ****                   Create Ranges                    ***

  ws.Names.Add Name:=scAbsoluteValueRange, RefersTo:=Cells(FIRSTROW, FIRSTCOLUMN)
  ws.Names.Add Name:=scResizeRange, RefersTo:=Cells(SECONDROW, SECONDCOLUMN)
  Set rAbsoluteValue = Range(scAbsoluteValueRange)
  Set rResize = Range(scResizeRange)
  Set nAbsoluteValue = ws.Names.Item(scAbsoluteValueRange)
  Set nResize = ws.Names.Item(scResizeRange)

  '      ****                   Change Ranges                    ***

  ' give an absolute reference:
  With nAbsoluteValue
    .RefersTo = ws.Cells(SECONDROW, FIRSTCOLUMN)
  End With

  iRow = FIRSTROW
  rAbsoluteValue(iRow) = scFred

  ' or, resize relative to old reference:
  With nResize
    .RefersTo = .RefersToRange.Offset(-1).Resize(2).Select
  End With

  iRow = FIRSTROW
  rResize(iRow) = scFreda

End Sub

Если я запускаю код до конца этапа создания диапазонов, я получаю два диапазона:

AbsoluteValue, занимающий "A1" Изменить размер, занимающий "B2"

Окно часов подтверждает это

Смотрите: +: nAbsoluteValue: "= Sheet1! $ A $ 1": Имя / Имя: Module1.ResizeIt

Смотрите: +: rAbsoluteValue.Name: "= Sheet1! $ A $ 1": Variant / Object / Name: Module1.ResizeIt

Смотрите: +: nResize: "= Sheet1! $ B $ 2": Имя / Имя: Module1.ResizeIt

Смотрите: +: rResize.Name: "= Sheet1! $ B $ 2": Variant / Object / Name: Module1.ResizeIt

Затем я изменяю расположение AbsoluteValue с помощью объекта Name на «A2».

В окне просмотра я получаю

Смотрите: +: nAbsoluteValue: "= Sheet1! $ A $ 2": Имя / Имя: Module1.ResizeIt

Смотрите:: rAbsoluteValue.Name:: Variant / Integer: Module1.ResizeIt

Однако, когда я пытаюсь записать в ячейку «A2» (то, что должно быть первой строкой в ​​диапазоне), результат, Fred, переходит в «A1». Другими словами, расположение диапазона не изменилось. Для диапазона изменения размера я получаю аналогичный результат:

Смотрите: +: nResize: "= Sheet1! $ B $ 1: $ B $ 2": Имя / Имя: Module1.ResizeIt

Смотрите:: rResize.Name:: Variant / Integer: Module1.ResizeIt

Однако, когда я пытаюсь записать в ячейку «B1» (то, что должно быть первой строкой в ​​диапазоне), результат, Freda, переходит в «B2». Опять же, местоположение дальности не изменилось.

Диспетчер имен указывает, что ячейки перешли в правильное расположение в обоих случаях.


excel named-ranges vba excel-2007
person Steve Roberts    schedule 12.03.2019    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Твой вопрос сложно понять, но я не вижу никаких проблем:

Sub Tester()

    Dim theName As Name

    Set theName = ActiveSheet.Names.Add(Name:="myName", RefersTo:=ActiveSheet.Range("A1"))

    showIt theName  '>> Name:Sheet1!myName          Address:$A$1

    theName.RefersTo = ActiveSheet.Range("B1:B3")

    showIt theName  '>> Name:Sheet1!myName          Address:$B$1:$B$3

    theName.RefersTo = theName.RefersToRange.Resize(6, 4)

    showIt theName  '>> Name:Sheet1!myName          Address:$B$1:$E$6

End Sub

Sub showIt(nm As Name)
    Debug.Print "Name:" & nm.Name, "Address:" & nm.RefersToRange.Address
End Sub

Кстати, это похоже на опечатку:

.RefersTo = .RefersToRange.Offset(-1).Resize(2).Select

Select там не место

ИЗМЕНИТЬ. Вы делаете это:

 Set rResize = Range(scResizeRange)

теперь rResize указывает на диапазон, определенный в scResizeRange, но нет связи между rResize и вашим именованным диапазоном - они независимы. Итак, если вы измените именованный диапазон, а затем сделаете это позже:

 rResize(iRow) = scFreda

... тогда можно было бы ожидать, что вы напишете в исходное расположение именованного диапазона

person Tim Williams    schedule 12.03.2019
comment
Тим, спасибо за быстрый ответ на этот вопрос, как и на другой из моих недавних вопросов по другому вопросу. - person Steve Roberts; 13.03.2019
comment
во-первых, спасибо за быстрый ответ на этот вопрос, как и на другой из моих недавних вопросов по другой проблеме. Во-вторых, я понимаю, что если бы я использовал Debug.Print вместо ввода данных Watch, это упростило бы отслеживание. В-третьих, приносим свои извинения за то, что не удалили Select. Я думал, что так и сделал. К сожалению, вы, кажется, подтвердили мои мысли, если я не упускаю что-то фундаментальное. Диапазоны, обозначенные rResize, уже существуют на рабочем листе ... - person Steve Roberts; 13.03.2019
comment
Итак, учитывая то, что вы сказали о том, что объекты Name и Range независимы (хотя я думаю, что показал в своем сообщении, что изменение объекта Name вызывает изменение объекта Range), как мне изменить направление объекта Range, чтобы что rResize (iRow) = scFreda действительно записывает в заданную ячейку. Должен ли я удалять объект диапазона и воссоздавать его каждый раз, когда я хочу изменить его границы? Большое спасибо за ваше терпение. - person Steve Roberts; 13.03.2019
comment
Если вы хотите изменить диапазон, на который ссылается именованный диапазон, вам нужно использовать имя, чтобы перейти к этому новому диапазону. Вы использовали rResize, который был просто указателем на старый диапазон и не имел связи с объектом имени - person Tim Williams; 13.03.2019