Excel - Почему присвоение имени объединенному диапазону отличается от объединения диапазона с последующим его присвоением?

Я собираюсь нанести удар по этому поводу, так как считаю, что я достаточно протестировал ваш код, чтобы понять, что происходит.

Начнем с того, как Excel обрабатывает различные именованные диапазоны при выполнении назначенных вами действий. Вы можете видеть, что после установки copyRange на константу MERGE_THEN_NAME и использования .Copy (в целях тестирования) для вновь определенного copyRange, ячейка A2 имеет движущиеся пунктирные линии вокруг нее.

Option Explicit

Public Sub PerformTests()

    Const NAME_THEN_MERGE As String = "Name_Then_Merge"
    Const MERGE_THEN_NAME As String = "Merge_Then_Name"
    Const PASTE_NAME_THEN_MERGE As String = "Paste_Name_Then_Merge"
    Const PASTE_MERGE_THEN_NAME As String = "Paste_Merge_Then_Name"

    TestNames MERGE_THEN_NAME, PASTE_MERGE_THEN_NAME
    '/ Result: Error 1004, cannot do that to a merged cell


End Sub

Public Sub TestNames(ByVal copyName As String, ByVal pasteName As String)

    Sheets("wsPasteTest").Activate

    Dim copyRange As Range, pasteRange As Range
    Set copyRange = Sheets("wsPasteTest").Range(copyName)
    copyRange.Copy    'This is the last step executed before error
    Set pasteRange = Sheets("wsPasteTest").Range(pasteName)

    CopyPasteCell copyRange, pasteRange

End Sub

Это ожидаемо, поскольку определенный диапазон читается как =wsPasteTest!$A$2 в диспетчере имен.

Это определение диапазона означает, что, когда Excel повторно посещает именованный диапазон для извлечения значения, он делает это, интерпретируя адрес буквально, т. Е. Учитывает только значение ячейки A2, поскольку значение объединенных ячеек определяется в" верхнем левом " адресе ячейки. Эта логика может быть подтверждена правильным методом возврата значения объединенной ячейки с помощью процедур VBA (адрес которой указан в приведенной выше гиперссылке), когда необходимо использовать что-то вроде Range("A2:E2").Cells(1).Value, а не просто Range("A2:E2").Value. Последний вариант возвращает массив, поскольку учитывает значение каждой отдельной ячейки в диапазоне.

В следующей части рассказывается, почему может быть опасно использовать .Select или .Activate во всем коде, но также показано, как добиться желаемого поведения. Когда мы .Select copyCell, мы можем видеть, что он выбирает всю объединенную ячейку по сравнению с A2 в методе .Copy (который имитирует то, как пользователь взаимодействует с диапазоном в самой электронной таблице). Итак, теперь, если мы выберем copyCell, а затем используем Selection.Copy, будет скопирована вся объединенная ячейка, а не только A2, поскольку Excel получил указание захватить всю область массива. Excel также только что показал, насколько значительный выбор элемента, а затем его использование может кардинально изменить его интерпретацию диапазона, а не явную работу с диапазоном.

Код в PerformTests() и TestNames() идентичен вашему, следующая подпрограмма была изменена:

Public Sub CopyPasteCell(ByRef copyCell As Range, ByRef pasteCell As Range, Optional ByVal pasteRowHeights As Boolean = False)

    copyCell.Select
    Selection.Copy 'this step is shown in the first screenshot below
    pasteCell.Select
    Selection.PasteSpecial xlPasteAll 'this step is shown in the second screentshot below

    If pasteRowHeights Then
        Dim sourceRowHeight As Long
        sourceRowHeight = copyCell.RowHeight
        pasteCell.RowHeight = sourceRowHeight
    End If

End Sub

Вот результат выбора, а затем копирования copyCell.

Вот результат того, что pasteCell был выбран, а затем вставлен в. Это показывает желаемый результат при использовании диапазона Merge_Then_Name.

В конечном итоге вы можете видеть, что когда вы явно ссылаетесь на именованный диапазон, который был определен после слияния, он буквально интерпретирует адрес именованного диапазона (давая значение одной ячейки с форматированием одной ячейки), что явно не может быть наклеивается на желаемый размер области, используя предложенный метод наклеивания. Если вы измените метод вставки на xlPasteValues, вы можете избежать использования .Select. Причина этого в том, что он позволяет определению Paste_Merge_Then_Name оставаться как =wsPasteTest!$A$8, а также позволяет переносить значение в область одной ячейки. Этот метод сохраняет форматирование целевой ячейки таким же, просто заменяя значение. Это не совсем то, что вы планировали, но довольно близко.

Public Sub CopyPasteCell(ByRef copyCell As Range, ByRef pasteCell As Range, Optional ByVal pasteRowHeights As Boolean = False)

    copyCell.Copy
    pasteCell.PasteSpecial xlPasteValues

    If pasteRowHeights Then
        Dim sourceRowHeight As Long
        sourceRowHeight = copyCell.RowHeight
        pasteCell.RowHeight = sourceRowHeight
    End If

End Sub

Диапазон Name_Then_Merge работает как с одиночной ячейкой, так и с вставками именованного диапазона по одной основной причине; сколько заполнителей он содержит для значений. Когда вы выполняете .Copy метод Name_Then_Merge, он выбирает всю объединенную ячейку , потому что это указано в ее определении =wsPasteTest!$A$5:$E$5. Это хорошо, потому что вставка этого в именованный диапазон равного размера будет работать, потому что они совпадают, а вставка в одну ячейку будет работать, потому что есть одно непустое значение.

Сводка: именованные диапазоны определяются их начальными значениями ячеек и удерживают это количество «мест» даже после объединения ячеек. Таким образом, если вы называете диапазон, он ссылается на значения каждой ячейки в диапазоне (в данном случае 5 значений), и слияние ячейки не изменяет определение именованного диапазона (но изменяет все значения массива после первого ячейку на "" в качестве заполнителя). Если вы объединяете ячейки и затем называете диапазон, значение сохраняется в верхней левой ячейке объединенной области и поэтому используется в качестве определения объединенной ячейки для именованного диапазона. В этом случае Excel запутается, если вы попытаетесь просто .Paste или xlPasteAll это в объединенную ячейку, потому что он скопирует значение источника как область одной ячейки и попытается вставить его в место назначения, которое представляет собой область из пяти ячеек.

Решите эту проблему, скопировав всю область именованного диапазона, выделив его, или используя другой метод вставки, но будьте осторожны с тем, что производят эти методы вставки. Например. xlPasteAllUsingSourceTheme разъединит целевые ячейки, но по-прежнему удовлетворяет определению именованного диапазона.