Создание личной панели быстрого доступа в Excel с помощью макросов VBA

Автоматизация рутинных операций в Excel с помощью макросов VBA и их размещение на панели быстрого доступа (QAT) значительно повышает эффективность работы. В этом руководстве вы научитесь создавать личную книгу макросов, писать полезные процедуры и настраивать удобный доступ к ним.

Создание личной книги макросов (Personal Macro Workbook)

Начните с любого открытого файла. Перейдите на вкладку «Вид», нажмите «Макросы» и выберите «Запись макроса».

1. В диалоговом окне «Запись макроса» введите имя, например, HelloWorld.
2. В поле «Сохранить в» выберите «Личная книга макросов».
3. Нажмите «ОК».

Введите «Hello» в активную ячейку и нажмите Enter. Затем выберите «Вид» → «Макросы» → «Остановить запись». Эти действия создадут файл Personal.xlsb на вашем компьютере для хранения макросов.

Откройте редактор VBA и найдите Module1 в Personal.xlsb

В Excel нажмите Alt + F11. Если комбинация не работает, добавьте команду «Visual Basic» на панель быстрого доступа.

1. В редакторе VBA выберите «Вид» → «Окно проекта».
2. Найдите запись VBAProject (PERSONAL.XLSB) и разверните её, нажав на значок «+».

3. Разверните папку «Modules», щёлкните правой кнопкой мыши на Module1 и выберите «Просмотреть код».

Вы увидите код макроса HelloWorld. После строки End Sub добавьте следующие полезные процедуры:

Sub VeryHideActiveSheet()
    ActiveSheet.Visible = xlVeryHidden
End Sub

Sub ShowAllSheets()
    For Each sh In ActiveWorkbook.Worksheets
        sh.Visible = ИСТИНА (True)
    Next sh
End Sub

Sub UpperSelection()
    For Each ЯЧЕЙКА (cell) In Selection.SpecialCells(2, 2)
        ' 2, 2 означает xlCellTypeConstants, ТЕКСТ (Text)
        ЯЧЕЙКА (cell).ЗНАЧЕН (Value) = UCase(ЯЧЕЙКА (cell).ЗНАЧЕН (Value))
    Next
End Sub

Sub LowerSelection()
    For Each ЯЧЕЙКА (cell) In Selection.SpecialCells(2, 2)
        ЯЧЕЙКА (cell).ЗНАЧЕН (Value) = LCase(ЯЧЕЙКА (cell).ЗНАЧЕН (Value))
    Next
End Sub

Sub ProperSelection()
    For Each ЯЧЕЙКА (cell) In Selection.SpecialCells(2, 2)
        ЯЧЕЙКА (cell).ЗНАЧЕН (Value) = Application.WorksheetFunction.ПРОПНАЧ (Proper)(ЯЧЕЙКА (cell).ЗНАЧЕН (Value))
    Next
End Sub

Добавление макросов на панель быстрого доступа

При настройке панели быстрого доступа выберите «Макросы» в выпадающем меню в левом верхнем углу.

1. Выберите нужный макрос и нажмите «Добавить».
2. Иконка по умолчанию — блок-схема. Нажмите «Изменить» в правом нижнем углу, выберите новую иконку и введите понятную подсказку.

Описание полезных макросов

• Very Hide: Листы могут быть видимыми, скрытыми или очень скрытыми. Очень скрытые листы не отображаются в меню «Главная» → «Формат» → «Скрыть или отобразить» → «Листы».
• Show All ЛИСТЫ (Sheets): Позволяет отобразить все скрытые листы (включая очень скрытые) одной командой.
• ПРОПИСН (Upper) Selection: Преобразует весь текст в выделенном диапазоне в верхний регистр.
• СТРОЧН (Lower) Selection: Преобразует весь текст в выделенном диапазоне в нижний регистр.
• ПРОПНАЧ (Proper) Selection: Преобразует весь текст в выделенном диапазоне в правильный регистр (каждое слово с заглавной буквы).

Бонус: Принудительная компиляция VBA при каждом открытии книги

В 2022-2024 годах встречается проблема с повреждением проекта VBA, из-за чего макросы могут пропадать. Решение — добавить параметр в реестр, чтобы Excel перекомпилировал проект при каждом открытии.

1. Откройте RegEdit и перейдите по пути: HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options\.
2. Создайте новый параметр DWORD с именем ForceVBALoadFromSource и установите значение 1.
3. Перезапустите Excel и откройте книгу.

Примечание: Компиляция при каждом открытии может замедлить запуск Excel. Для сложных проектов можно вернуть значение параметра в 0.

Полезные настройки в меню «Параметры Excel»

Рассмотрите следующие настройки после выбора «Файл» → «Параметры»:

• Общие → «Параметры запуска»: Снимите галочку «Показывать начальный экран при запуске этого приложения», чтобы Excel открывался сразу с новой книгой.
• Дополнительно → «Параметры правки»: Снимите галочку «После нажатия клавиши Enter переходить к другой ячейке», чтобы оставаться в той же ячейке после ввода данных.
• Дополнительно → «Параметры правки»: Используйте опцию «Автоматически вставлять десятичную запятую» для быстрого ввода дробных чисел.

Использование личной книги макросов и настройка панели быстрого доступа — это мощные инструменты для автоматизации и ускорения работы в Excel. Начните с простых макросов и постепенно расширяйте их функционал под свои задачи.