Съдържание
Дори и да не знаете как да програмирате, има много места (книги, уебсайтове, форуми), където можете да намерите готов VBA макро код за огромен брой типични задачи в Excel. Според моя опит повечето потребители рано или късно събират личната си колекция от макроси, за да автоматизират рутинни процеси, независимо дали става дума за преобразуване на формули в стойности, показване на суми с думи или сумиране на клетки по цвят. И тук възниква проблемът – макрокодът във Visual Basic трябва да бъде съхранен някъде, за да се използва по-късно в работата.
Най-лесният вариант е да запазите кода на макроса директно в работния файл, като отидете в редактора на Visual Basic с помощта на клавишната комбинация Друг+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 с клавишната комбинация Друг+F11 или чрез бутон Visual Basic етикет предприемач (Разработчик). Ако раздели предприемач не се вижда, може да се покаже през Файл – Опции – Настройка на лентата (Файл — Опции — Персонализиране на лентата).
Трябва да има прозорец в горния ляв ъгъл на редактора Проект (ако не се вижда, включете го през менюто Изглед — Project Explorer):
Този прозорец показва всички отворени работни книги и работещи добавки на Microsoft Excel, включително нашите. VBAProject (MyExcelAddin.xlam) Изберете го с мишката и добавете нов модул към него чрез менюто Вмъкване – Модул. В този модул ще съхраняваме VBA кода на нашите макроси за добавки.
Можете или да въведете кода от нулата (ако знаете как да програмирате), или да го копирате от някъде готов (което е много по-лесно). Нека, за тестване, въведете кода на прост, но полезен макрос в добавения празен модул:
След като въведете кода, не забравяйте да кликнете върху бутона за запазване (дискета) в горния ляв ъгъл.
Нашият макрос Формули Към Стойности, както лесно можете да си представите, преобразува формули в стойности в предварително избран диапазон. Понякога тези макроси също се извикват процедури. За да го стартирате, трябва да изберете клетки с формули и да отворите специален диалогов прозорец Макроси от раздела предприемач (Програмист — Макроси) или клавишна комбинация Друг+F8. Обикновено този прозорец показва наличните макроси от всички отворени работни книги, но макросите за добавки не се виждат тук. Въпреки това можем да въведем името на нашата процедура в полето име на макрос (име на макрос)и след това щракнете върху бутона бягане (бягам) – и нашият макрос ще работи:
| |
Тук можете също да зададете клавишна комбинация за бързо стартиране на макрос - бутонът отговаря за това параметри (Настроики) в предишния прозорец Макро:
Когато присвоявате клавиши, имайте предвид, че те са чувствителни към главни и малки букви и клавиатурна подредба. Така че, ако зададете комбинация като Ctrl+Й, тогава всъщност в бъдеще ще трябва да се уверите, че сте включили оформлението и да натиснете допълнително Превключванеза да получите главната буква.
За удобство можем също да добавим бутон за нашия макрос към лентата с инструменти за бърз достъп в горния ляв ъгъл на прозореца. За да направите това, изберете Файл – Опции – Лента с инструменти за бърз достъп (Файл — Опции — Персонализиране на лентата с инструменти за бърз достъп), а след това в падащия списък в горната част на прозореца опцията Макроси. След това нашия макрос Формули Към Стойности може да се постави на панела с бутона Добави (Добавяне) и изберете икона за него с бутона промяна (Edit):
Стъпка 4. Добавете функции към добавката
Но макропроцедури, също така има функционални макроси или както се наричат СДС (Дефинирана от потребителя функция = дефинирана от потребителя функция). Нека създадем отделен модул в нашата добавка (команда от менюто Вмъкване – Модул) и поставете там кода на следната функция:
Лесно се вижда, че тази функция е необходима за извличане на ДДС от сумата, включително ДДС. Не биномът на Нютон, разбира се, но ще ни свърши работа като пример, за да покажем основните принципи.
Имайте предвид, че синтаксисът на функция е различен от процедура:
- използва се конструкция Функция…. Крайна функция вместо Sub … End Sub
- след името на функцията нейните аргументи са посочени в скоби
- в тялото на функцията се извършват необходимите изчисления и след това резултатът се присвоява на променлива с името на функцията
Също така имайте предвид, че тази функция не е необходима и е невъзможно да се изпълни като предишната макро процедура през диалоговия прозорец Макроси и бутон бягане. Такава макро функция трябва да се използва като стандартна функция за работен лист (SUM, IF, VLOOKUP…), т.е. просто въведете в произволна клетка, като посочите стойността на сумата с ДДС като аргумент:
… или влезте през стандартния диалогов прозорец за вмъкване на функция (бутон fx в лентата с формули), като изберете категория Потребителски дефиниран (дефиниран от потребителя):
Единственият неприятен момент тук е липсата на обичайното описание на функцията в долната част на прозореца. За да го добавите ще трябва да направите следното:
- Отворете редактора на Visual Basic с клавишна комбинация Друг+F11
- Изберете добавката в панела на проекта и натиснете клавиша F2за да отворите прозореца на браузъра на обекти
- Изберете вашия проект за добавка от падащия списък в горната част на прозореца
- Щракнете с десния бутон върху функцията, която се появява и изберете команда Имоти.
- Въведете описание на функцията в прозореца Описание
- Запазете файла с добавката и рестартирайте ексел.
След рестартиране функцията трябва да покаже описанието, което сме въвели:
Стъпка 5. Създайте раздел за добавки в интерфейса
Последното, макар и не задължително, но приятно докосване ще бъде създаването на отделен раздел с бутон за стартиране на нашия макрос, който ще се появи в интерфейса на Excel след свързване на нашата добавка.
Информацията за разделите, които се показват по подразбиране, се съдържа в книгата и трябва да бъде форматирана в специален XML код. Най-лесният начин да напишете и редактирате такъв код е с помощта на специални програми - XML редактори. Една от най-удобните (и безплатни) е програмата на Максим Новиков XML редактор на лентата.
Алгоритъмът за работа с него е следният:
- Затворете всички прозорци на Excel, така че да няма конфликт на файлове, когато редактираме XML кода на добавката.
- Стартирайте програмата Ribbon XML Editor и отворете нашия файл MyExcelAddin.xlam в нея
- С бутон разделите в горния ляв ъгъл добавете кодовия фрагмент за новия раздел:
- Трябва да поставите празни кавички id нашия раздел и група (всякакви уникални идентификатори) и в етикет – имената на нашия раздел и група бутони в него:
- С бутон бутон в левия панел добавете празен код за бутона и добавете тагове към него:
— етикет е текстът на бутона
— изображениеMso — това е условното име на изображението върху бутона. Използвах икона на червен бутон, наречена AnimationCustomAddExitDialog. Имената на всички налични бутони (и има няколкостотин от тях!) могат да бъдат намерени на голям брой сайтове в Интернет, ако търсите по ключовите думи „imageMso“. Като за начало можете да отидете тук.
- onAction – това е името на процедурата за обратно извикване – специален кратък макрос, който ще изпълни основния ни макрос Формули Към Стойности. Можете да наричате тази процедура както желаете. Ще го добавим малко по-късно.
- Можете да проверите правилността на всичко направено чрез бутона със зелена отметка в горната част на лентата с инструменти. На същото място щракнете върху бутона с флопи диск, за да запазите всички промени.
- Затворете XML редактора на лентата
- Отворете Excel, отидете в редактора на Visual Basic и добавете процедура за обратно извикване към нашия макрос KillFormulasтака че да изпълнява основния ни макрос за замяна на формули със стойности.
- Запазваме промените и, връщайки се в Excel, проверяваме резултата:
Това е всичко – добавката е готова за използване. Напълнете го със собствени процедури и функции, добавете красиви бутони – и ще стане много по-лесно да използвате макроси в работата си.
- Какво представляват макросите, как да ги използвате в работата си, къде да получите код на макроси във Visual Basic.
- Как да направите начален екран при отваряне на работна книга в Excel
- Какво е Personal Macro Book и как да го използвате