У меня есть немного кода VBA, который пытается установить границу LEFT и TOP для группы несмежных ячеек, ранее добавленных к объекту Range на основе некоторых критериев. Для контекста аргумент «rng» представляет собой набор ячеек, значения которых удовлетворяют определенному критерию. Код, который почти полностью работает:
Private Sub set_cell_borders(rng As Range, change_cat As Long)
Dim border_colour As Long, the_borders As Variant, i As Long
the_borders = Array(xlEdgeLeft, xlEdgeTop, xlInsideHorizontal, xlInsideVertical)
On Error GoTo errHandler
Select Case change_cat
Case 1: border_colour = RGB(160, 160, 160) ' Trivial change (grey)
Case 2: border_colour = RGB(255, 192, 0) ' Minor change (orange)
Case 3: border_colour = RGB(255, 0, 0) ' Major change (red)
Case 4: border_colour = RGB(102, 0, 204) ' String change (purple)
End Select
For i = LBound(the_borders) To UBound(the_borders)
With rng.Borders(the_borders(i))
.Color = border_colour
.Weight = xlThick
.LineStyle = xlContinuous
End With
Next i
Exit Sub
errHandler:
' Assume we got here if rng is format-protected, so simply exit
Err.Clear
End Sub
Все ячейки форматируются, как и ожидалось, за исключением границ xlInsideVertical и xlInsideHorizontal, кажется, признается только строка .LineStyle = xlContinuous, а не атрибуты .Width или .Color, т.е. я получаю все ВЕРХНИЕ и ЛЕВЫЕ границы каждого непрерывного блока ячеек, отформатированных как и ожидалось, но между любыми соседними ячейками, которые должны быть отмечены, есть просто тонкая черная линия. Для ясности, все ячейки начинаются с состояния стертого формата (вообще без границ), поэтому я знаю, что атрибут .LineStyle применяется моим кодом, а не просто уже существует в этих ячейках, если это имеет смысл?
Я думал, что это работало раньше, поэтому я полагаю, что это может быть ошибка Excel в Excel 365 версии 2202, но хотел проверить здесь, не упускаю ли я что-то более фундаментальное.
Обновление: добавлены изображения, чтобы прояснить предполагаемое (верхнее изображение) и фактическое (нижнее изображение) поведение.
Я не могу это воспроизвести. Можете ли вы опубликовать скриншот?
Я могу подтвердить это поведение. Для меня это происходит только для xlInsideVertical
-границы (это работает для xlInsideHorizontal
), и это происходит только тогда, когда у вас несмежный диапазон. Проверка с помощью отладчика показывает, что свойства Веса и Цвета не сохраняются. Я не могу сказать, это ошибка или специально.
Однако есть простой обходной путь: просто переберите все области в пределах вашего диапазона. Каждый диапазон имеет набор областей (диапазонов), которые представляют собой (непрерывные) фрагменты диапазона этого диапазона. Использование областей будет работать и для смежных диапазонов: у них есть коллекция с одним членом, поэтому ваш код будет работать в любом случае.
Dim area As Range
For Each area In rng.Areas
For i = LBound(the_borders) To UBound(the_borders)
With area.Borders(the_borders(i))
.LineStyle = xlContinuous
.Weight = xlThick
.Color = border_colour
End With
Next i
Next area
Обновление: я провел несколько экспериментов: проблема не возникает, если вы выбрали (несмежный) диапазон и используете Selection
(или Set rng = Selection
). Однако, как только вы используете Union
, возникает проблема. Во всяком случае, цикл по областям сработал во всех случаях. А между тем у меня тоже были случаи, когда xlInsideHorizontal
не работал.
Спасибо @FunThomas, я очень не хочу зацикливаться, так как мой код был почти таким же несколько недель назад, за исключением того, что я зацикливал каждую ячейку в диапазоне, и я подозреваю, что зацикливание на областях будет немного быстрее. Проблема заключалась в том, что это было заморожено из-за размера коллекции - варьируется от сотен до тысяч элементов, в зависимости от конкретной рабочей книги, в которой выполняется это форматирование. Я попробую использовать Selection и посмотрю, позволяет ли это операцию с одним диапазоном без проявления проблемы.
Чтобы прокомментировать далее, использование rng.Select, а затем действие над Selection не работает, если первый элемент выбора является ячейкой сам по себе, т.е. если ActiveCell является одинокой ячейкой, это не работает. Поэтому я попробую зациклить области и посмотреть, каков штраф за скорость...
Спасибо @FunThomas за некоторые эксперименты - похоже, основная причина в том, что первая ячейка в диапазоне «rng» является одинокой ячейкой. Если первые несколько ячеек являются смежными, Excel не беспокоится о том, что одиночные ячейки являются частью коллекции, он по-прежнему будет применять внутренние границы для блоков смежных ячеек, как и ожидалось.
Поэтому правильным (но медленным на больших диапазонах) решением является то, что предлагает @FunThomas, - перебирать области.
В моем случае я буду думать о том, чтобы искусственно гарантировать, что первая включенная ячейка не изолирована от остальных, возможно, продублировав ее в строке ниже, а затем подчистив в конце. Это стоит усилий, потому что мой диапазон составляет тысячи ячеек, и разница в скорости заметна для пользователей, для которых это упражнение должно быть незаметно быстрым.
Вы установили Application.ScreenUpdating = False
во время выполнения?
Конечно :) Не первое мое родео. Это просто огромная коллекция ячеек, состоящая из множества маленьких фрагментов (т. е. количество областей ненамного меньше, чем количество ячеек — обычно 20% или около того). Так что это все еще много сотен операций форматирования против одной...
Если я правильно рассчитал, это означает, что по крайней мере 80% — это отдельные ячейки (у которых нет внутренних границ). Вы можете ускорить процесс, установив внешние границы для всего диапазона, а затем перебрать области и установить внутренние границы, только если область содержит не менее 2 столбцов (по вертикали), соответственно. 2 ряда (по горизонтали).
Я думаю, что может быть проще (и быстрее) зацикливаться, пока я не найду первые смежные ячейки, а затем установить одну из них как ActiveCell (если это возможно в ячейке, которая не является левым верхним целым Range), и тогда кажется, что это работает как одна операция... Но ваше частичное зацикливание - хорошая запасная позиция.
P.S. Я подозреваю, что проблема может быть связана с тем фактом, что «rng» не является смежным и, следовательно, может содержать некоторые ячейки, у которых нет соседей (таким образом, внутренние границы не имеют значения для этих ячеек)