Как использовать Личную Книгу Макросов
Если вы еще не знакомы с макросами в Excel, то я вам даже немного завидую. Ощущение всемогущества и осознание того, что ваш Microsoft Excel можно прокачивать почти до бесконечности, которые придут к вам после знакомства с макросами - приятные чувства.
Однако, эта статья для тех, кто уже "познал мощь" и начал использовать макросы (чужие или написанные самостоятельно - не важно) в своей повседневной работе.
Макрос - это код (несколько строк) на языке Visual Basic, которые заставляют Excel сделать то, что вам нужно: обработать данные, сформировать отчет, скопипастить много однообразных таблиц и т.п. Вопрос - где эти несколько строк кода хранить? Ведь от того, где макрос хранится будет потом зависеть где он сможет (или не сможет) работать.
Если макрос решает небольшую локальную проблему в отдельно взятом файле (например обрабатывает внесенные в конкретный отчет данные особым образом), то логично хранить код внутри этого же файла. Без вопросов.
А если макрос должен быть относительно универсален и нужен в любой книге Excel - как, например, макрос для конвертирования формул в значения? Не копировать же его код на Visual Basic каждый раз в каждую книгу? Кроме того, рано или поздно, почти любой пользователь приходит к мысли, что неплохо было бы сложить все макросы в одну коробку, т.е. иметь их всегда под рукой. И может быть даже запускать не вручную, а сочетаниями клавиш? И вот тут может здорово помочь Личная Книга Макросов (Personal Macro Workbook).
Как создать Личную Книгу Макросов
На самом деле, Личная Книга Макросов (ЛКМ) - это обычный файл Excel в формате двоичной книги (Personal.xlsb), который автоматически в скрытом режиме открывается одновременно с Microsoft Excel. Т.е. когда вы просто запускаете Excel или открываете любой файл с диска, на самом деле открываются два файла - ваш и Personal.xlsb, но второго мы не видим. Таким образом все макросы, которые хранятся в ЛКМ оказываются доступы для запуска в любой момент, пока открыт Excel.
Если вы еще ни разу не пользовались ЛКМ, то изначально файл Personal.xlsb не существует. Самый легкий способ его создать - это записать рекордером какой-нибудь ненужный бессмысленный макрос, но указать в качестве места для его хранения Личную Книгу - тогда Excel будет вынужден автоматически ее для вас создать. Для этого:
- Откройте вкладку Разработчик (Developer). Если вкладки Разработчик не видно, то ее можно включить в настройках через Файл - Параметры - Настройка ленты (Home - Options - Customize the Ribbon).
- На вкладке Разработчик нажмите кнопку Запись макроса (Record Macro). В открывшемся окне выберите Личную книгу макросов (Personal Macro Workbook) как место для хранения записанного кода и нажмите OK:
- Остановите запись кнопкой Остановить запись (Stop Recording) на вкладке Разработчик (Developer)
Проверить результат можно, нажав на кнопку Visual Basic там же на вкладке Разработчик - в открывшемся окне редактора в левом верхнем углу на панели Project - VBA Project должен появиться наш файл PERSONAL.XLSB. Его ветку которого можно развернуть плюсиком слева, добравшись до Module1, где и хранится код только что записанного нами бессмысленного макроса:
Поздравляю, вы только что создали себе Личную Книгу Макросов! Только не забудьте нажать на кнопку сохранения с дискеткой в левом верхнем углу на панели инструментов.
Как использовать Личную Книгу Макросов
Дальше все просто. Любой нужный вам макрос (т.е. кусок кода, начинающийся на 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 - и мы увидим папку с нашим файлом Личной Книги Макросов:
P.S.
И несколько практических нюансов вдогон:
- при использовании Личной Книги Макросов Excel будет запускаться чуть медленнее, особенно на слабых ПК
- стоит периодически очищать Личную Книгу от информационного мусора, старых и ненужных макросов и т.п.
- у корпоративных пользователей бывают иногда сложности с использованием Личной Книги, т.к. это файл в системной скрытой папке
Ссылки по теме
- Что такое макросы, как их использовать в работе
- Полезности для VBA-программиста
- Тренинг "Программирование макросов на VBA в Microsoft Excel"
У меня только форматирование в числовой формат с 0 знаков после запятой стоят.
"Личная Книгу Макросов" уже не модно.
Надстройки реально рулят = и себе посмотреть и другим показать 8)
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И макрос сохранения листов как отдельная книга
"Мастер VBA". Я б точно у Вас ее купил. Может Вы начали писать такую книгу?
Информация по VBA гуглится лучше, чем тот же DAX из Power Pivot или Power BI. Хотя Power Pivot сделали бесплатным. Должна увеличиться аудитория. Информация сейчас преимущественно вся на английском.
Павел, а праздничного подарка на Первомай не планируете в виде новой версии Plex?
Я уже заждался обновления, в котором Вы обещали встроенную функцию regexp.((
Пока у меня не получается. Изменение пути в экселе - не работает.
Один батник при старте операционной системы, копирует Personal.xlsb из облака в ... Excel\XLSTART\
Другой батник по расписанию копирует Personal.xlsb в облако.
Или жёсткие ссылки (Hard Link).
Сам в облаке держу надстройки.
ЛФМ сохраняем туда и он будет просто открываться при загрузке.
2. ещё одна ПРОБЛЕМА! Бывает пропадает список ранее загружаемых (используемых) файлов на панели задач и/или <Ctrl+o> как быстро восстановить эту опцию Windows & Office