Создание личной панели быстрого доступа в Excel с помощью макросов VBA
Автоматизация рутинных операций в Excel с помощью макросов VBA и их размещение на панели быстрого доступа (QAT) значительно повышает эффективность работы. В этом руководстве вы научитесь создавать личную книгу макросов, писать полезные процедуры и настраивать удобный доступ к ним.
Шаги настройки панели и сочетания клавиш ниже относятся к Excel для Windows. Личная книга макросов доступна и в настольном Excel для Mac, но интерфейс отличается. В Excel для Интернета нельзя создавать, редактировать или запускать VBA-макросы.
Создание личной книги макросов (Personal Macro Workbook)
Откройте новую пустую книгу для упражнения. На вкладке «Разработчик» выберите «Запись макроса»; если вкладка скрыта, включите её в настройках ленты. В Windows команда также доступна через «Вид → Макросы → Запись макроса».
- В диалоговом окне «Запись макроса» введите имя, например,
HelloWorld. - В поле «Сохранить в» выберите «Личная книга макросов».
- Нажмите «ОК».
Для создания личной книги не нужно записывать изменение ячейки: сразу нажмите «Разработчик → Остановить запись». Если личной книги ещё нет, Excel создаст PERSONAL.XLSB. При завершении работы согласитесь сохранить изменения в ней. Это скрытая книга на вашем компьютере: она открывается в фоне при запуске Excel, а её макросы доступны для работы с другими книгами.
Откройте редактор VBA и найдите модуль в PERSONAL.XLSB
В Excel для Windows нажмите Alt + F11 или выберите «Разработчик → Visual Basic». На ноутбуке для функциональной клавиши может потребоваться Fn.
- В редакторе VBA выберите «Вид» → «Окно проекта».
- Найдите запись
VBAProject (PERSONAL.XLSB)и разверните её, нажав на значок «+».
- Разверните папку «Modules» и откройте модуль с записанным макросом. В новой личной книге это обычно
Module1; в существующей книге номер модуля может быть другим.
Вы увидите код макроса HelloWorld. После его строки End Sub, вне других процедур, добавьте весь блок ниже. Ключевые слова VBA, имена свойств и методов пишутся по-английски и в русской версии Excel. Переменные в примере объявлены явно; при желании добавьте Option Explicit в самом начале модуля.
Перед запуском сохраните копию рабочей книги: изменение значений макросом обычно нельзя отменить стандартной командой «Отменить». Эти процедуры работают с активной книгой и текущим выделением, а не с книгой, содержащей код. Для упражнений используйте обычные незащищённые листы и небольшие диапазоны; скрытые строки и столбцы внутри выделения тоже обрабатываются.
Public Sub VeryHideActiveSheet()
Dim wb As Workbook
Dim sh As Object
Dim visibleCount As Long
Set wb = ActiveWorkbook
If wb Is Nothing Then Exit Sub
If wb Is ThisWorkbook Then Exit Sub
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
If wb.ProtectStructure Then
MsgBox "Workbook structure is protected."
Exit Sub
End If
For Each sh In wb.Sheets
If sh.Visible = xlSheetVisible Then visibleCount = visibleCount + 1
Next sh
If visibleCount <= 1 Then
MsgBox "At least one sheet must remain visible."
Exit Sub
End If
ActiveSheet.Visible = xlSheetVeryHidden
End Sub
Public Sub ShowAllSheets()
Dim wb As Workbook
Dim sh As Object
Set wb = ActiveWorkbook
If wb Is Nothing Then Exit Sub
If wb Is ThisWorkbook Then Exit Sub
If wb.ProtectStructure Then
MsgBox "Workbook structure is protected."
Exit Sub
End If
For Each sh In wb.Sheets
sh.Visible = xlSheetVisible
Next sh
End Sub
Public Sub UpperSelection()
ChangeSelectionCase 1
End Sub
Public Sub LowerSelection()
ChangeSelectionCase 2
End Sub
Public Sub ProperSelection()
ChangeSelectionCase 3
End Sub
Private Sub ChangeSelectionCase(ByVal caseMode As Long)
Dim ws As Worksheet
Dim scope As Range
Dim cell As Range
Dim result As String
If TypeName(Selection) <> "Range" Then Exit Sub
Set ws = Selection.Parent
If ws.Parent Is ThisWorkbook Then Exit Sub
If ws.ProtectContents Then
MsgBox "This example does not change protected worksheets."
Exit Sub
End If
Set scope = Application.Intersect(Selection, ws.UsedRange)
If scope Is Nothing Then Exit Sub
For Each cell In scope.Cells
If Not cell.MergeCells And Not cell.HasFormula Then
If VarType(cell.Value2) = vbString Then
Select Case caseMode
Case 1
result = UCase(cell.Value2)
Case 2
result = LCase(cell.Value2)
Case 3
result = Application.WorksheetFunction.Proper(cell.Value2)
End Select
' The apostrophe prefix keeps the result as literal text.
cell.Value2 = "'" & result
End If
End If
Next cell
End Sub
Добавление макросов на панель быстрого доступа
Откройте Файл → Параметры → Панель быстрого доступа. В списке «Выбрать команды из» над левым списком выберите «Макросы», а в области настройки панели — вариант для всех документов. Выбирайте процедуры из PERSONAL.XLSB. Вспомогательная процедура ChangeSelectionCase объявлена Private и не предназначена для отдельной кнопки.
- Выберите нужный макрос и нажмите «Добавить».
- Выделите добавленный макрос в правом списке и нажмите «Изменить». Выберите значок и задайте понятное отображаемое имя, которое появится в подсказке. Подтвердите изменения кнопкой «ОК».
Описание полезных макросов
- Very Hide: присваивает активному рабочему листу состояние
xlSheetVeryHidden. Такой лист не появляется в обычном диалоге «Отобразить лист». Процедура не скрывает последний видимый лист и не изменяет книгу с защищённой структурой. Это управление видимостью, а не защита конфиденциальных данных. - Show All Sheets: отображает все листы активной книги, включая очень скрытые и листы диаграмм, если структура книги не защищена. Обе процедуры видимости пропускают саму личную книгу макросов.
- UPPER Selection: переводит текстовые константы в выделении в верхний регистр с помощью VBA-функции
UCase. - LOWER Selection: переводит текстовые константы в выделении в нижний регистр с помощью
LCase. - PROPER Selection: вызывает
WorksheetFunction.Proper— аналог функции листа ПРОПНАЧ. Она делает заглавной первую букву текста и буквы после небуквенных символов, а остальные — строчными. Это не лингвистическая проверка имён и аббревиатур. Все три процедуры пропускают формулы, числа, ошибки и объединённые ячейки; на защищённых листах они завершаются без изменений. Они перезаписывают текстовые значения и не предназначены для сохранения смешанного форматирования символов.
Если VBA-проект не компилируется или книга не сохраняется
Ошибки компиляции, блокировка макросов и повреждение файла имеют разные причины. Изменение параметра реестра ForceVBALoadFromSource не следует использовать как универсальную настройку личной книги. Сначала проверьте конкретную ошибку:
- Сохраните резервную копию. В редакторе VBA выполните Debug → Compile VBAProject и исправьте отмеченные ошибки кода. В частности, названия вроде «ЗНАЧЕН / VALUE» и «ПРОПНАЧ» недопустимы как замена английским членам объектной модели.
- Проверьте, что макросы сохранены в
PERSONAL.XLSB,.xlsmили другом формате с поддержкой VBA и что их запуск разрешён для проверенного файла. Формат.xlsxкод VBA не сохраняет. - Если проблема сохраняется, проверьте обновления Office и официальные рекомендации по восстановлению приложения. Для корпоративной установки обратитесь к администратору с точным текстом ошибки и номером сборки.
Примечание: настройки панели не переносят сам код VBA на другой компьютер. Для переноса отдельно скопируйте нужные модули или личную книгу; не заменяйте существующую PERSONAL.XLSB без сохранения её макросов.
Полезные настройки в меню «Параметры Excel»
Рассмотрите следующие настройки после выбора «Файл» → «Параметры»:
- Общие → «Параметры запуска»: если в вашей версии есть флажок «Показывать начальный экран при запуске этого приложения», снимите его, чтобы пропускать начальный экран при обычном запуске Excel.
- Дополнительно → «Параметры правки»: снимите флажок «После нажатия клавиши Enter переходить к другой ячейке», чтобы оставаться в той же ячейке после ввода данных.
- Дополнительно → «Параметры правки»: параметр «Автоматическая вставка десятичной запятой» меняет ввод новых чисел. При двух десятичных разрядах ввод
1234даёт12,34; существующие значения не меняются. Включайте его только для подходящей задачи и отключайте после неё. Название разделителя зависит от локали.
Использование личной книги макросов и настройка панели быстрого доступа — это мощные инструменты для автоматизации и ускорения работы в Excel. Начните с простых макросов и постепенно расширяйте их функционал под свои задачи.