Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Автоматизация (VBA / Office Scripts)

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

forest_paw 14.04.2026 1054

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

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

Шаги настройки панели и сочетания клавиш ниже относятся к Excel для Windows. Личная книга макросов доступна и в настольном Excel для Mac, но интерфейс отличается. В Excel для Интернета нельзя создавать, редактировать или запускать VBA-макросы.

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

Откройте новую пустую книгу для упражнения. На вкладке «Разработчик» выберите «Запись макроса»; если вкладка скрыта, включите её в настройках ленты. В Windows команда также доступна через «Вид → Макросы → Запись макроса».

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

Для создания личной книги не нужно записывать изменение ячейки: сразу нажмите «Разработчик → Остановить запись». Если личной книги ещё нет, Excel создаст PERSONAL.XLSB. При завершении работы согласитесь сохранить изменения в ней. Это скрытая книга на вашем компьютере: она открывается в фоне при запуске Excel, а её макросы доступны для работы с другими книгами.

Откройте редактор VBA и найдите модуль в PERSONAL.XLSB

В Excel для Windows нажмите Alt + F11 или выберите «Разработчик → Visual Basic». На ноутбуке для функциональной клавиши может потребоваться Fn.

  1. В редакторе VBA выберите «Вид» → «Окно проекта».
  2. Найдите запись VBAProject (PERSONAL.XLSB) и разверните её, нажав на значок «+».
В окне проекта разверните VBAProject (PERSONAL.XLSB), нажав на плюс.
В окне проекта разверните VBAProject (PERSONAL.XLSB), нажав на плюс.
  1. Разверните папку «Modules» и откройте модуль с записанным макросом. В новой личной книге это обычно Module1; в существующей книге номер модуля может быть другим.
В личной книге макросов разверните папку Modules и откройте код Module1.
В личной книге макросов разверните папку 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 и не предназначена для отдельной кнопки.

В диалоговом окне настройки панели быстрого доступа выберите Макросы в меню команд.
В диалоговом окне настройки панели быстрого доступа выберите Макросы в меню команд.
  1. Выберите нужный макрос и нажмите «Добавить».
  2. Выделите добавленный макрос в правом списке и нажмите «Изменить». Выберите значок и задайте понятное отображаемое имя, которое появится в подсказке. Подтвердите изменения кнопкой «ОК».
На панели быстрого доступа добавлены пять макросов с пользовательскими иконками: Very Hide, Show All Sheets, UPPER Select, Lower Selection, Proper Selection.
На панели быстрого доступа добавлены пять макросов с пользовательскими иконками: Very Hide, Show All Sheets, UPPER Selection, LOWER Selection, PROPER Selection.

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

  • Very Hide: присваивает активному рабочему листу состояние xlSheetVeryHidden. Такой лист не появляется в обычном диалоге «Отобразить лист». Процедура не скрывает последний видимый лист и не изменяет книгу с защищённой структурой. Это управление видимостью, а не защита конфиденциальных данных.
  • Show All Sheets: отображает все листы активной книги, включая очень скрытые и листы диаграмм, если структура книги не защищена. Обе процедуры видимости пропускают саму личную книгу макросов.
  • UPPER Selection: переводит текстовые константы в выделении в верхний регистр с помощью VBA-функции UCase.
  • LOWER Selection: переводит текстовые константы в выделении в нижний регистр с помощью LCase.
  • PROPER Selection: вызывает WorksheetFunction.Proper — аналог функции листа ПРОПНАЧ. Она делает заглавной первую букву текста и буквы после небуквенных символов, а остальные — строчными. Это не лингвистическая проверка имён и аббревиатур. Все три процедуры пропускают формулы, числа, ошибки и объединённые ячейки; на защищённых листах они завершаются без изменений. Они перезаписывают текстовые значения и не предназначены для сохранения смешанного форматирования символов.

Если VBA-проект не компилируется или книга не сохраняется

Ошибки компиляции, блокировка макросов и повреждение файла имеют разные причины. Изменение параметра реестра ForceVBALoadFromSource не следует использовать как универсальную настройку личной книги. Сначала проверьте конкретную ошибку:

  1. Сохраните резервную копию. В редакторе VBA выполните Debug → Compile VBAProject и исправьте отмеченные ошибки кода. В частности, названия вроде «ЗНАЧЕН / VALUE» и «ПРОПНАЧ» недопустимы как замена английским членам объектной модели.
  2. Проверьте, что макросы сохранены в PERSONAL.XLSB, .xlsm или другом формате с поддержкой VBA и что их запуск разрешён для проверенного файла. Формат .xlsx код VBA не сохраняет.
  3. Если проблема сохраняется, проверьте обновления Office и официальные рекомендации по восстановлению приложения. Для корпоративной установки обратитесь к администратору с точным текстом ошибки и номером сборки.

Примечание: настройки панели не переносят сам код VBA на другой компьютер. Для переноса отдельно скопируйте нужные модули или личную книгу; не заменяйте существующую PERSONAL.XLSB без сохранения её макросов.

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

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

  • Общие → «Параметры запуска»: если в вашей версии есть флажок «Показывать начальный экран при запуске этого приложения», снимите его, чтобы пропускать начальный экран при обычном запуске Excel.
  • Дополнительно → «Параметры правки»: снимите флажок «После нажатия клавиши Enter переходить к другой ячейке», чтобы оставаться в той же ячейке после ввода данных.
  • Дополнительно → «Параметры правки»: параметр «Автоматическая вставка десятичной запятой» меняет ввод новых чисел. При двух десятичных разрядах ввод 1234 даёт 12,34; существующие значения не меняются. Включайте его только для подходящей задачи и отключайте после неё. Название разделителя зависит от локали.

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

Поделиться:

Мы используем cookies для улучшения работы сайта. Продолжая использовать сайт, вы соглашаетесь с политикой конфиденциальности.