В представленном ниже примере выделены красным шрифтом все формулы, содержащиеся на рабочем листе.
Рассмотрим процедуру. Итак:
Данная процедура работает, но имеет некоторые недочеты. Особенно это будет заметно, если пользователь удаляет строки или столбцы, что приводит к тому, что диапазон Target может включать множество ячеек. Проверка каждой из этих ячеек с использованием цикла ForEach может быть скажем так «времязатратной» и неэффективной.
Давайте улучшим версию вышеуказанной процедуры. Недостаток решается путем изменения диапазона Target так, чтобы он представлял собой пересечение Target и диапазона, используемого самим рабочим листом. Это предотвращает случай, когда Target равен Nothing. Также исключается возможность воздействия на пустые строки или столбцы за пределами используемого диапазона.
Хочу отметить, что в обеих версиях кода цвет шрифта установлен как красный (vbRed). Если желаете изменить цвет, можно воспользоваться другими константами цветов.
Давайте разберем код пошагово:
1. Private Sub Worksheet_Change(ByVal Target As Excel.Range): Это объявление событийной процедуры в языке VBA. Эта процедура срабатывает каждый раз, когда происходит изменение на рабочем листе Excel.
2. Dim cell As Range: Объявление переменной cell, которая будет использоваться для итерации по ячейкам в диапазоне Target.
3. Set Target = Intersect(Target, Target.Parent.UsedRange):
- Target: Это параметр, передаваемый в событийную процедуру Worksheet_Change. В данном контексте, Target представляет измененный диапазон ячеек, который вызвал изменение события.
- Target.Parent: Это свойство объекта Target, которое возвращает родительский объект. В данном случае, это возвращает объект листа (Worksheet), на котором произошли изменения.
- UsedRange: Это свойство объекта листа, возвращающее диапазон, который содержит все используемые ячейки на листе. Это включает в себя ячейки с данными и формулами, но может включать также пустые строки или столбцы.
- Intersect(Target, Target.Parent.UsedRange): Это функция пересечения двух диапазонов. Она возвращает новый диапазон, который представляет собой общую область между двумя диапазонами. В данном случае, это пересечение измененного диапазона Target и используемого диапазона на родительском листе.
- Set Target = ...: Здесь результат операции пересечения присваивается обратно переменной Target. Таким образом, переменная Target теперь представляет только те ячейки, которые действительно используются на листе и пересекаются с измененным диапазоном.
4. If Not Target Is Nothing Then: Это условие проверяет, не является ли Target пустым (не Nothing). Если Target не равен Nothing, то выполняется следующий блок кода.
5. For Each cell In Target: Начало цикла For Each, который итерирует по каждой ячейке в диапазоне Target.
6. If cell.HasFormula Then: Это условие проверяет, содержит ли текущая ячейка формулу.
7. cell.Font.Color = vbRed: Если условие в пункте 6 истинно (если ячейка содержит формулу), то устанавливается цвет шрифта этой ячейки в красный (vbRed).
8. Next cell: Завершение цикла For Each.
9. End If: Завершение условия.
10. End Sub: Завершение событийной процедуры.
Таким образом, этот код применяет изменение цвета шрифта в красный для всех ячеек в диапазоне Target, которые содержат формулы. Он также обеспечивает оптимизацию, исключая из рассмотрения пустые строки или столбцы за пределами фактически используемого диапазона на листе.