Как отобрать и отформатировать все формулы на листе в Excel

Что делает макрос: При просмотре рабочей книги Excel нужно иметь четкое представление о всех формулах на каждом листе. Найти все формулы — трудная задача, если все делать вручную. Но Excel предоставляет нам удобный способ поиска и маркировки всех формул на листе. Макрос в этом разделе использует эту функцию, чтобы динамически отобрать и отформатировать все формулы на листе.

Как макрос работает

В Excel есть набор предопределенных «специальных ячеек», которые можно выбрать с помощью диалогового окна «Перейти к специальному элементу». Чтобы вручную выбрать специальные ячейки, перейдите на вкладку «Главная» на ленте и выберите «Перейти к специальным». Это вызывает диалоговое окно Go To Special. Здесь вы можете выбрать набор ячеек на основе нескольких определяющих атрибутов. Одним из определяющих атрибутов являются формулы. Опция эффективно выделяет все ячейки, которые содержат формулы.

Этот макрос программно делает одно и то же для всей книги одновременно. Вот, мы используем метод SpecialCells из коллекции Cells. Метод SpecialCells требует параметр типа, который представляет тип специальной ячейки. В этом случае мы используем xlCell- TypeFormulas.

Короче говоря, мы имеем в виду специальный диапазон, который состоит только из ячеек, содержащих формулы. Мы ссылаемся в этот специальный диапазон с помощью оператора With … End With. Это утверждение говорит Excel, что любое выполняемое вами действие относится только к тому диапазону, на который вы указали. Здесь мы выделяем ячейки в выбранном диапазоне.

Код макроса

Sub OtformatirovatFormuli()
'Шаг 1: Объявляем переменные
Dim ws As Worksheet
'Шаг 2: Избегаем ошибки, если формулы не найдены
On Error Resume Next
'‘Шаг 3: Запускаем цикл через рабочие листы
For Each ws In ActiveWorkbook.Worksheets
'Шаг 4: Выбираем ячейки и выделяем их
With ws.Cells.SpecialCells(xlCellTypeFormulas)
.Interior.ColorIndex = 36
End With
' Шаг 5: Переходим к следующему листу
Next ws
End Sub

Как этот код работает

1. Шаг 1 объявляет объект под названием WS. Это создает контейнер памяти для каждого листа макросов.
2. Если таблица не содержит формулы, Excel выдает ошибку. Чтобы избежать ошибки, мы говорим Excel, чтобы продолжить с помощью макроса, если инициируется ошибка.
3. Шаг 3 начинает цикл, говоря Excel оценить все рабочие листы в активной книге.
4. В этом шаге, макрокоманда выбирает все ячейки, содержащие формулы, а затем форматирует их.
5. На шаге 5, мы выполняем цикл заново, чтобы получить следующий лист. После того, как все листы оценены, макрос заканчивает работу.

Как использовать

1. Активируйте редактор Visual Basic, нажав ALT + F11.
2. Щелкните правой кнопкой мыши personal.xlb в окне Project.
3. Выберите Insert➜Module.
4. Введите или вставьте код.