змест
Нават калі вы не ўмееце праграмаваць, ёсць шмат месцаў (кнігі, вэб-сайты, форумы), дзе вы можаце знайсці гатовы макрасок VBA для велізарнай колькасці тыповых задач у Excel. З майго досведу, большасць карыстальнікаў рана ці позна збіраюць сваю асабістую калекцыю макрасаў для аўтаматызацыі руцінных працэсаў, няхай гэта будзе перавод формул у значэнні, адлюстраванне сум словамі або сумаванне вочак колерам. І тут узнікае праблема - макракод у Visual Basic трэба дзесьці захоўваць, каб потым выкарыстоўваць у працы.
Самы просты варыянт - захаваць код макраса непасрэдна ў працоўны файл, перайшоўшы ў рэдактар Visual Basic з дапамогай спалучэння клавіш Alt+F11 і даданне новага пустога модуля праз меню Устаўка – модуль:
Аднак у гэтага метаду ёсць некалькі недахопаў:
- Калі працоўных файлаў шмат, і ўсюды патрэбен макрас, напрыклад, макрас для пераўтварэння формул у значэння, то прыйдзецца скапіяваць код у кожнай кнізе.
- Нельга забываць захаваць файл у фармаце з падтрымкай макрасаў (xlsm) або ў бінарным фармаце кнігі (xlsb).
- Пры адкрыцці такога файла макраабарона будзе кожны раз выдаваць папярэджанне, якое трэба пацвердзіць (ну, ці цалкам адключыць абарону, што не заўсёды можа быць пажадана).
Больш элегантным рашэннем будзе стварэнне ваша ўласная надбудова (надбудова Excel) – асобны файл спецыяльнага фармату (xlam), які змяшчае ўсе вашы «любімыя» макрасы. Перавагі гэтага падыходу:
- Гэтага будзе дастаткова падключыце надбудову адзін раз у Excel – і вы можаце выкарыстоўваць яго працэдуры і функцыі VBA ў любым файле на гэтым кампутары. Перазахаванне працоўных файлаў у фарматах xlsm і xlsb, такім чынам, не патрабуецца, т.к. зыходны код будзе захоўвацца не ў іх, а ў файле надбудовы.
- абарона вас таксама не будуць турбаваць макрасы. дапаўненні, па вызначэнні, з'яўляюцца надзейнымі крыніцамі.
- Можа зрабіць асобная ўкладка на стужцы Excel з прыгожымі кнопкамі для запуску дадатковых макрасаў.
- Надбудова - гэта асобны файл. Яго лёгка насіць з сабой з кампутара на камп'ютар, дзяліцеся з калегамі ці нават прадавайце 😉
Давайце крок за крокам разгледзім увесь працэс стварэння ўласнай надбудовы Microsoft Excel.
Крок 1. Стварыце файл надбудовы
Адкрыйце Microsoft Excel з пустой кнігай і захавайце яе пад любым прыдатным імем (напрыклад MyExcelAddin) у фармаце надбудовы з камандай Файл - Захаваць як або ключы F12, з указаннем тыпу файла Надбудова Excel:
Звярніце ўвагу, што па змаўчанні Excel захоўвае надбудовы ў тэчцы C:UsersYour_nameAppDataRoamingMicrosoftAddIns, але, у прынцыпе, вы можаце паказаць любую іншую зручную для вас тэчку.
Крок 2. Падключаем створаную надбудову
Цяпер надбудова, якую мы стварылі на апошнім этапе MyExcelAddin павінен быць падлучаны да Excel. Для гэтага зайдзіце ў меню Файл – Параметры – Дапаўненні (Файл — Параметры — Надбудовы), націсніце на кнопку аб (Ідзі) у ніжняй частцы акна. У якое адкрылася акне націсніце кнопку агляд (Агляд) і пакажыце месцазнаходжанне нашага файла надбудовы.
Калі вы ўсё зрабілі правільна, то наша MyExcelAddin павінна з'явіцца ў спісе даступных дапаўненняў:
Крок 3. Дадайце макрасы ў надбудову
Наша надбудова падключана да Excel і паспяхова працуе, але ў ёй пакуль няма ніводнага макраса. Давайце запоўнім. Для гэтага адкрыйце рэдактар Visual Basic з дапамогай спалучэння клавіш Alt+F11 або кнопкай Візуальны Бейсик таб распрацоўшчык (Распрацоўшчык). Калі ўкладкі распрацоўшчык не бачны, яго можна адлюстраваць праз Файл – Параметры – Налада стужкі (Файл — Параметры — Наладзіць стужку).
У левым верхнім куце рэдактара павінна быць акно Праект (калі не бачна, то ўключыце праз меню View - Project Explorer):
У гэтым акне адлюстроўваюцца ўсе адкрытыя працоўныя кнігі і запушчаныя надбудовы Microsoft Excel, у тым ліку наша. VBAProject (MyExcelAddin.xlam) Вылучыце яго мышкай і дадайце ў яго новы модуль праз меню Устаўка – модуль. У гэтым модулі мы будзем захоўваць код VBA нашых дадатковых макрасаў.
Вы можаце альбо ўвесці код з нуля (калі вы ўмееце праграмаваць), альбо скапіяваць яго з гатовага месца (што значна прасцей). Давайце, для тэставання, увядзем код простага, але карыснага макраса ў дададзены пусты модуль:
Пасля ўводу кода не забудзьцеся націснуць на кнопку захавання (дыскета) у левым верхнім куце.
Наш макрас Формулы да значэнняў, як вы можаце лёгка сабе ўявіць, пераўтворыць формулы ў значэнні ў загадзя выбраным дыяпазоне. Часам гэтыя макрасы таксама выклікаюцца Працэдуры. Для яго запуску неабходна вылучыць вочкі з формуламі і адкрыць спецыяльнае дыялогавае акно Макрас з таб распрацоўшчык (Распрацоўшчык — Макрасы) або спалучэнне клавіш Alt+F8. Звычайна ў гэтым акне паказваюцца даступныя макрасы з усіх адкрытых кніг, але дадатковыя макрасы тут не бачныя. Нягледзячы на гэта, мы можам увесці назву нашай працэдуры ў поле імя макраса (Імя макраса)а затым націсніце кнопку прагон (бег) – і наш макрас будзе працаваць:
| |
Тут таксама можна прызначыць спалучэнне клавіш для хуткага запуску макраса - за гэта адказвае кнопка параметры (Параметры) у папярэднім акне Macro:
Пры прызначэнні клавіш майце на ўвазе, што яны адчувальныя да рэгістра і раскладкі клавіятуры. Такім чынам, калі вы прызначыце камбінацыю, як Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally Зрухатрымаць вялікую літару.
Для зручнасці мы таксама можам дадаць кнопку для нашага макраса на панэль хуткага доступу ў левым верхнім куце акна. Для гэтага абярыце Файл – Параметры – Панэль хуткага доступу (Файл — Параметры — Наладзіць панэль хуткага доступу), а затым у выпадальным спісе ў верхняй частцы акна опцыю Макрас. Пасля гэтага наш макрас Формулы да значэнняў можна размясціць на панэлі кнопкай Дадаваць (Дадаць) і абярыце значок для яго з дапамогай кнопкі Мяняць (Рэдагаваць):
Крок 4. Дадайце функцыі ў надбудову
Але макрапрацэдуры, ёсць таксама функцыі макрасаў ці як іх яшчэ называюць UDF (Вызначаная карыстальнікам функцыя = вызначаная карыстальнікам функцыя). Давайце створым асобны модуль у нашым дадатку (каманда меню Устаўка – модуль) і ўстаўце туды код наступнай функцыі:
Няцяжка заўважыць, што гэтая функцыя патрэбна для вылічэння ПДВ з сумы з ПДВ. Вядома, не біном Ньютана, але ён падыдзе нам у якасці прыкладу, каб паказаць асноўныя прынцыпы.
Звярніце ўвагу, што сінтаксіс функцыі адрозніваецца ад сінтаксісу працэдуры:
- кастр Функцыя…. Канчатковая функцыя замест Sub … End Sub
- пасля назвы функцыі ў дужках паказваюцца яе аргументы
- у целе функцыі выконваюцца неабходныя вылічэнні, а затым вынік прысвойваецца зменнай з імем функцыі
Таксама звярніце ўвагу, што гэтая функцыя не патрэбна, і яе немагчыма запусціць праз дыялогавае акно, як папярэднюю працэдуру макраса Макрас і кнопка прагон. Такую макрафункцыю трэба выкарыстоўваць як стандартную функцыю працоўнага ліста (СУМА, КАЛІ, ВПР…), г. зн. проста ўвесці ў любую ячэйку, указаўшы ў якасці аргументу значэнне сумы з ПДВ:
... або ўвайсці праз стандартнае дыялогавае акно для ўстаўкі функцыі (кнопка fx у радку формул), выбраўшы катэгорыю Карыстальнік (Вызначаецца карыстальнікам):
Адзіны непрыемны момант тут - гэта адсутнасць звыклага апісання функцыі ў ніжняй частцы акна. Каб дадаць яго, вам трэба будзе зрабіць наступнае:
- Адкрыйце рэдактар Visual Basic з дапамогай спалучэння клавіш Alt+F11
- Выберыце надбудову на панэлі праекта і націсніце клавішу F2каб адкрыць акно аглядальніка аб'ектаў
- Выберыце свой праект надбудовы з выпадальнага спісу ў верхняй частцы акна
- Пстрыкніце правай кнопкай мышы на функцыі, якая з'явіцца, і абярыце каманду Ўласцівасці.
- Увядзіце апісанне функцыі ў акне Апісанне
- Захавайце файл надбудовы і перазапусціце excel.
Пасля перазапуску функцыя павінна адлюстроўваць уведзенае намі апісанне:
Крок 5. Стварыце ўкладку надбудовы ў інтэрфейсе
Апошнім, хоць і не абавязковым, але прыемным штрыхом стане стварэнне асобнай ўкладкі з кнопкай для запуску нашага макраса, якая з'явіцца ў інтэрфейсе Excel пасля падлучэння нашай надбудовы.
Інфармацыя пра ўкладкі, якія адлюстроўваюцца па змаўчанні, змяшчаецца ў кнізе і павінна быць адфарматавана ў спецыяльны код XML. Прасцей за ўсё пісаць і рэдагаваць такі код з дапамогай спецыяльных праграм - XML-рэдактараў. Адной з самых зручных (і бясплатных) з'яўляецца праграма Максіма Новікава Рэдактар XML стужкі.
Алгарытм працы з ім наступны:
- Зачыніце ўсе вокны Excel, каб не было канфлікту файлаў пры рэдагаванні XML-кода надбудовы.
- Запусціце праграму Ribbon XML Editor і адкрыйце ў ёй файл MyExcelAddin.xlam
- З кнопкай ўкладкі у левым верхнім куце дадайце фрагмент кода для новай укладкі:
- Трэба ставіць пустыя двукоссі id наша ўкладка і група (любыя ўнікальныя ідэнтыфікатары), і ў этыкетка – назвы нашай укладкі і групы кнопак на ёй:
- З кнопкай кнопка на левай панэлі дадайце пусты код для кнопкі і дадайце да яе тэгі:
— ярлык - гэта тэкст на кнопцы
— вобразМсо — так умоўна называецца малюнак на кнопцы. Я выкарыстаў чырвоны значок кнопкі пад назвай AnimationCustomAddExitDialog. Назвы ўсіх даступных кнопак (а іх некалькі сотняў!) можна знайсці на вялікай колькасці сайтаў у інтэрнэце па ключавым слове «imageMso». Для пачатку вы можаце зайсці сюды.
- onAction – гэта назва працэдуры зваротнага выкліку – спецыяльнага кароткага макраса, які будзе запускаць наш асноўны макрас Формулы да значэнняў. Вы можаце называць гэтую працэдуру як заўгодна. Мы дадамо яго крыху пазней.
- Праверыць правільнасць усяго зробленага можна з дапамогай кнопкі з зялёнай галачкай у верхняй частцы панэлі інструментаў. Там жа націсніце на кнопку з дыскетай для захавання ўсіх змяненняў.
- Зачыніце рэдактар XML стужкі
- Адкрыйце Excel, перайдзіце ў рэдактар Visual Basic і дадайце ў наш макрас працэдуру зваротнага выкліку KillFormulasтак што ён запускае наш асноўны макрас для замены формул значэннямі.
- Захоўваем змены і, вяртаючыся ў Excel, правяраем вынік:
Вось і ўсё - надбудова гатовая да выкарыстання. Напоўніце яго ўласнымі працэдурамі і функцыямі, дадайце прыгожыя кнопкі - і выкарыстоўваць макрасы ў працы стане значна прасцей.
- Што такое макрасы, як іх выкарыстоўваць у сваёй працы, дзе ўзяць код макрасаў у Visual Basic.
- Як зрабіць застаўку пры адкрыцці кнігі ў Excel
- Што такое Personal Macro Book і як ёю карыстацца