Как использовать Личную Книгу Макросов

Если вы еще не знакомы с макросами в Excel, то я вам даже немного завидую. Ощущение всемогущества и осознание того, что ваш Microsoft Excel можно прокачивать почти до бесконечности, которые придут к вам после знакомства с макросами - приятные чувства.

Однако, эта статья для тех, кто уже "познал мощь" и начал использовать макросы (чужие или написанные самостоятельно - не важно) в своей повседневной работе.

Макрос - это код (несколько строк) на языке Visual Basic, которые заставляют Excel сделать то, что вам нужно: обработать данные, сформировать отчет, скопипастить много однообразных таблиц и т.п. Вопрос - где эти несколько строк кода хранить? Ведь от того, где макрос хранится будет потом зависеть где он сможет (или не сможет) работать.

Если макрос решает небольшую локальную проблему в отдельно взятом файле (например обрабатывает внесенные в конкретный отчет данные особым образом), то логично хранить код внутри этого же файла. Без вопросов.

А если макрос должен быть относительно универсален и нужен в любой книге Excel - как, например, макрос для конвертирования формул в значения? Не копировать же его код на Visual Basic каждый раз в каждую книгу? Кроме того, рано или поздно, почти любой пользователь приходит к мысли, что неплохо было бы сложить все макросы в одну коробку, т.е. иметь их всегда под рукой. И может быть даже запускать не вручную, а сочетаниями клавиш? И вот тут может здорово помочь Личная Книга Макросов (Personal Macro Workbook).

Как создать Личную Книгу Макросов

На самом деле, Личная Книга Макросов (ЛКМ) - это обычный файл Excel в формате двоичной книги (Personal.xlsb), который автоматически в скрытом режиме открывается одновременно с Microsoft Excel. Т.е. когда вы просто запускаете Excel или открываете любой файл с диска, на самом деле открываются два файла - ваш и Personal.xlsb, но второго мы не видим. Таким образом все макросы, которые хранятся в ЛКМ оказываются доступы для запуска в любой момент, пока открыт Excel.

Если вы еще ни разу не пользовались ЛКМ, то изначально файл Personal.xlsb не существует. Самый легкий способ его создать - это записать рекордером какой-нибудь ненужный бессмысленный макрос, но указать в качестве места для его хранения Личную Книгу - тогда Excel будет вынужден автоматически ее для вас создать. Для этого:

  1. Откройте вкладку Разработчик (Developer). Если вкладки Разработчик не видно, то ее можно включить в настройках через Файл - Параметры - Настройка ленты (Home - Options - Customize the Ribbon).
  2. На вкладке Разработчик нажмите кнопку Запись макроса (Record Macro). В открывшемся окне выберите Личную книгу макросов (Personal Macro Workbook) как место для хранения записанного кода и нажмите OK:

    Записываем макрос в Личную Книгу Макросов

  3. Остановите запись кнопкой Остановить запись (Stop Recording) на вкладке Разработчик (Developer)

Проверить результат можно, нажав на кнопку Visual Basic там же на вкладке Разработчик - в открывшемся окне редактора в левом верхнем углу на панели Project - VBA Project должен появиться наш файл PERSONAL.XLSB. Его ветку которого можно развернуть плюсиком слева, добравшись до Module1, где и хранится код только что записанного нами бессмысленного макроса:

Личная Книга Макросов в редакторе VBA

Поздравляю, вы только что создали себе Личную Книгу Макросов! Только не забудьте нажать на кнопку сохранения с дискеткой в левом верхнем углу на панели инструментов.

Как использовать Личную Книгу Макросов

Дальше все просто. Любой нужный вам макрос (т.е. кусок кода, начинающийся на Sub и заканчивающийся End Sub) можно смело копировать и вставлять либо в Module1, либо в отдельный модуль, добавив его предварительно через меню Insert - Module. Хранить все макросы в одном модуле или раскладывать по разным - исключительно вопрос вкуса. Выглядеть это должно примерно так:

Полезный макрос в Личной Книге Макросов

Запустить добавленный макрос можно в диалоговом окне, вызываемом с помощью кнопки Макросы (Macros) на вкладке Разработчик:

Запуск макросов

В этом же окне, нажав кнопку Параметры (Options), можно задать сочетание клавиш для быстрого запуска макроса с клавиатуры. Будьте внимательны: сочетания клавиш для макросов различают раскладку (русская или английская) и регистр.

Кроме обычных макросов-процедур в Личной Книге можно хранить и пользовательские макро-функции (UDF = User Defined Function). В отличие от процедур, код функций начинаются с оператора Function или Public Function, а заканчиваются на End Function:

Пример макро-функции

Код необходимо аналогичным образом скопировать в любой модуль книги PERSONAL.XLSB и затем можно будет вызвать функцию обычным образом, как любую стандарную функцию Excel, нажав кнопку fx в строке формул и выбрав функцию в окне Мастера Функций в категории Определенные пользователем (User Defined):

Выбор пользовательской функции в окне Мастера Функций

Примеры таких функций можно в больших количествах найти в интернете или здесь же на сайте (сумма прописью, приблизительный текстовый поиск, ВПР 2.0, конвертация кириллицы в транслит и т.п.)

Где хранится Личная Книга Макросов

Если вы будете использовать Личную Книгу Макросов, то рано или поздно у вас возникнет желание:

  • поделиться своими накопленными макросами с другими пользователями
  • скопировать и перенести Личную Книгу на другой компьютер
  • сделать ее резервную копию

Для этого нужно будет найти файл PERSONAL.XLSB на диске вашего компьютера. По умолчанию, этот файл хранится в специальной папке автозапуска Excel, которая называется XLSTART. Так что все, что нужно - это добраться до этой папки на нашем ПК. И вот тут возникает небольшая сложность, потому что местоположение этой папки зависит от версии Windows и Office и может различаться. Обычно это один из следующих вариантов:

  • C:\Program Files\Microsoft Office\Office12\XLSTART
  • C:\Documents and Settings\Computer\Application Data\Microsoft\Excel\XLSTART
  • C:\Users\имя-вашей-учетной-записи\AppData\Roaming\Microsoft\Excel\XLSTART

Как вариант, можно спросить о положении этой папки сам Excel с помощью VBA. Для этого в редакторе Visual Basic (кнопка Visual Basic на вкладке Разработчик) нужно открыть окно Immediate сочетанием клавиш Ctrl+G, ввести туда команду ? Application.StartupPath и нажать на Enter:

Путь к личной книге макросов

Полученный путь можно скопировать и вставить в верхнюю строку окна Проводника в Windows и нажать Enter - и мы увидим папку с нашим файлом Личной Книги Макросов:

Файл Личной Книги Макросов в папке XLSTART

P.S.

И несколько практических нюансов вдогон:

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

Ссылки по теме




02.04.2018 11:31:15
Поделитесь кто что хранит в личной книге макросов?
У меня только форматирование в числовой формат с 0 знаков после запятой стоят.
11.04.2018 01:41:25
Привет!
"Личная Книгу Макросов" уже не модно.
Надстройки реально рулят = и себе посмотреть и другим показать 8)
02.04.2018 16:55:17
У меня, например, редизайнер таблиц (из двумерной в плоскую)


Option Explicit
Sub Redesigner()


            
        Dim inpdata As Range, realdata As Range, ns As Worksheet
        Dim i&, II&, c&, r&, hc&, hr&, nSt&, nT&
        Dim out(), dataArr(), hcArr(), hrArr(), shapka ', shapkaFirst As Boolean
        On Error GoTo line1
            
            Set inpdata = Application.InputBox("Выберите обрабатываемый диапазон:", "Выбор диапазона", Selection.Address, Type:=8)
            hr = InputBox("Сколько строк с подписями данных сверху", , 1)
            hc = InputBox("Сколько столбцов с подписями данных слева?", , 1)
            nSt = InputBox("Сколько столбцов с данными будет в правой части таблицы? (например: если Ваша таблица уходит вправо на 24 месяца то указав тут 12 - месяцы разобьются по столбцам, а год перенесется по строкам", , 1)
'    Проверка шапки если  nSt = 1
        If nSt = 1 And hr > 1 Then
                If MsgBox("Выбрано только один столбец повторения, уменьшить шапку?", vbYesNo) = vbYes Then
                shapka = inpdata.Cells(hr, 1).Resize(1, hc).Value 'realdata.Value
                Else
                shapka = inpdata.Resize(hr, hc).Value 'realdata.Value
                End If
            Else
            shapka = inpdata.Resize(hr, hc).Value 'realdata.Value
        End If
            
        Application.ScreenUpdating = False
        If inpdata.Rows.Count <= hr Or inpdata.Columns.Count <= hc Then Exit Sub
        Set realdata = inpdata.Offset(hr, hc).Resize(inpdata.Rows.Count - hr, inpdata.Columns.Count - hc)
        dataArr = realdata.Value
            
        If hr Then hrArr = inpdata.Offset(0, hc).Resize(hr, inpdata.Columns.Count - hc).Value
        If hc Then hcArr = inpdata.Offset(hr, 0).Resize(inpdata.Rows.Count - hr, hc).Value
            
'    Проверка шапки
        For i = 1 To UBound(hrArr)
        For II = 1 To UBound(hrArr, 2)
            hrArr(i, II) = Проверка_слова(CStr(hrArr(i, II)))
        Next II, i
'    Проверка справочника
        For i = 1 To UBound(hcArr)
        For II = 1 To UBound(hcArr, 2)
            hcArr(i, II) = Проверка_слова(CStr(hcArr(i, II)))
        Next II, i
'====================================
        ReDim out(1 To realdata.Count / nSt, 1 To hr + hc + nSt)
'Начало основного цикла
        hr = 0
        For i = 1 To UBound(hcArr)
                hc = 1
                For II = 1 To Int(UBound(dataArr, 2) / nSt)
                hr = hr + 1
                    For r = 1 To UBound(hrArr): out(hr, r) = hrArr(r, hc): Next r
                    For c = 1 To UBound(hcArr, 2): out(hr, c + r - 1) = hcArr(i, c): Next c
                    For nT = 1 To nSt
                        '   Добавление данных если не ошибка
                        If Not IsError(dataArr(i, hc)) Then out(hr, c + r + nT - 2) = dataArr(i, hc)
                        hc = hc + 1
                    Next
                Next
        Next
        Set ns = Worksheets.Add '   Добавление листа
            
            If IsArrayEmpty(shapka) = False Then
                ns.Cells(1, r).Resize(UBound(shapka), UBound(shapka, 2)) = shapka
                If nSt = 1 Then ns.Cells(1, r + c - 1).Resize(UBound(shapka), nSt) = "Значения" Else ns.Cells(1, r + c - 1).Resize(UBound(shapka), nSt) = hrArr '    Выгрузка шапки столбцов
                r = UBound(shapka) + 1
            Else
                ns.Cells(1, r) = shapka '    Выгрузка шапки строк
                If nSt = 1 Then ns.Cells(1, r + c - 1) = "Значения" Else ns.Cells(1, r + c - 1).Resize(UBound(hrArr), nSt) = hrArr '    Выгрузка шапки столбцов
                r = 2
            End If
            ns.Cells(r, 1).Resize(UBound(out), UBound(out, 2)) = out '  Выгрузка данных
        'Удобности:
            ns.Cells(1, 1).Resize(r - 1, UBound(out, 2)).Interior.ColorIndex = 44 ' Закрашивание шапки
            ns.Cells(r, UBound(hrArr) + c).Select: ActiveWindow.FreezePanes = True '    Закрепление шапки
            ns.Range(Cells(r - 1, 1), Cells(UBound(out), UBound(out, 2))).AutoFilter '  Установка Автофильтра

' Установка границ
        With ns.Range(Cells(1, 1), Cells(Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row, UBound(out, 2))).Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
        End With
Application.ScreenUpdating = True
line1:
End Sub

И макрос сохранения листов как отдельная книга

Sub SplitSheets()
'
'Сохраняет выделенные листы как отдельный файл


Dim CurW As Window
    Dim TempW As Window
    Set CurW = ActiveWindow
    Set TempW = ActiveWorkbook.NewWindow
    CurW.SelectedSheets.Copy
    TempW.Close
End Sub
есть и другие, но они более узкоспециализированные
12.04.2018 12:14:48
Николай, хорошо было б если Вы сами написали книгу по программированию на VBA с Вашим стилем изложения с названием, например,
"Мастер VBA". Я б точно у Вас ее купил. Может Вы начали писать такую книгу?
28.04.2018 17:26:58
Николай, я бы тоже купил у Вас книжечку "про макросы". Предыдущие 2 Ваших книги у меня уже есть. Если бы была третья книга, описывающая тонкости работы с "макросами", - она бы стала хорошим дополнением в моей коллекции. Всё дело в том, что Вы подробно излагаете основные проблемы, которые возможно могли бы возникнуть при осуществлении тех или иных действий при работе в Екселе. У других авторов такого не встретишь. Давно слышу о великих возможностях, открывающихся пользователям, при использовании макросов в Екселе. Но пока-что не освоил их в деле. Надеюсь, что когда-нибудь Вы выпустите такую книжку.
28.04.2018 18:45:40
Бурно, буйно поддерживаю!
09.09.2018 19:57:31
А смысл ему делать книжку про макросы, если язык VBA не развивается более 10 лет? и существует доля вероятности (пока на уровне слухов), что когда-то в Excel она умрет.
Информация по VBA гуглится лучше, чем тот же DAX из Power Pivot или Power BI. Хотя Power Pivot сделали бесплатным. Должна увеличиться аудитория. Информация сейчас преимущественно вся на английском.
28.04.2018 18:44:49
Спасибо за очередную прекрасную (нужную) статью!
Павел, а праздничного подарка на Первомай не планируете в виде новой версии Plex?
Я уже заждался обновления, в котором Вы обещали встроенную функцию regexp.((
18.11.2018 12:36:31
Странно по команде показывает C:\Users\имя-вашей-учетной-записи\AppData\Roaming\Microsoft\Excel\XLSTART, а работает когда в C:\Program Files\Microsoft Office\Office12\XLSTART.
28.11.2018 10:47:08
Файл personal.xlsb есть и в Users, и в Program files, но макросы почему-то недоступны :facepalm:. Что ещё проверить? В чем может быть проблема?
Наверх