Как да запишете макрос в Excel - Ръководство стъпка по стъпка

Дори ако сте напълно начинаещ в света на Excel VBA, можете лесно да запишете макрос и да автоматизирате част от работата си.

В това подробно ръководство ще разгледам всичко, което трябва да знаете, за да започнете да записвате и използвате макроси в Excel.

Ако се интересувате да научите VBA по лесния начин, вижте моя Онлайн обучение по Excel VBA.

Какво е макрос?

Ако сте начинаещ във VBA, първо ще ви кажа какво е макрос - в края на краищата ще продължа да използвам този термин в целия урок.

Макросът е код, написан във VBA (Visual Basic за приложения), който ви позволява да стартирате парче код, когато се изпълнява.

Често ще откриете, че хората (включително и аз) се отнасят към VBA код като макрос - независимо дали е генериран с помощта на макрорекордер или е написан ръчно.

Когато записвате макрос, Excel внимателно следи стъпките, които предприемате, и го записва на език, който разбира - който е VBA.

И тъй като Excel е наистина добър бележник, той създава много подробен код (както ще видим по -късно в този урок).

Сега, когато спрете записа, запазете макроса и го стартирате, Excel просто се връща към генерирания от него VBA код и следва точно същите стъпки.

Това означава, че дори и да не знаете нищо за VBA, можете да автоматизирате някои задачи, просто като оставите Excel да записва стъпките ви веднъж и след това да ги използвате повторно по -късно.

Сега нека се потопим и да видим как да запишем макрос в Excel.

Получаване на раздел Developer в лентата

Първата стъпка за запис на макрос е да получите раздела Developer в лентата.

Ако вече виждате раздела за програмисти в лентата, преминете към следващия раздел, иначе следвайте стъпките по -долу:

• Щракнете с десния бутон върху някой от съществуващите раздели в лентата и кликнете върху опцията „Персонализиране на лентата“. Той ще отвори диалоговия прозорец Опции на Excel.
• В диалоговия прозорец Опции на Excel ще имате опциите Персонализиране на лентата. Вдясно, в панела Основни раздели, проверете опцията Разработчик.
• Щракнете върху OK.

Горните стъпки биха направили раздела Developer наличен в областта на лентата.

Записване на макрос в Excel

Сега, когато имаме всичко на място, нека научим как да записваме макрос в Excel.

Нека запишем много прост макрос - такъв, който избира клетка и въвежда в нея текста „Excel“. Използвам текста „Excel“, докато записвам този макрос, но не се колебайте да въведете вашето име или друг текст, който ви харесва.

Ето стъпките за записване на този макрос:

1. Щракнете върху раздела Разработчик.
2. В групата Код щракнете върху бутона Макрос. Това ще отвори диалоговия прозорец „Запис на макрос“.
3. В диалоговия прозорец Запис на макрос въведете име за вашия макрос. Използвам името EnterText. Има някои условия за именуване, които трябва да спазвате, когато именувате макрос. Например, не можете да използвате интервали между тях. Обикновено предпочитам да запазя имената си на макроси като една дума, с различни части с първа азбука с главни букви. Можете също да използвате подчертаване, за да разделите две думи - например Enter_Text.
4. (Незадължителна стъпка) Можете да зададете клавишна комбинация, ако желаете. В този случай ще използваме пряк път Control + Shift + N. Не забравяйте, че зададеният тук пряк път ще замени всички съществуващи преки пътища във вашата работна книга. Например, ако зададете пряк път Control + S, няма да можете да го използвате за запазване на работната книга (вместо това, всеки път, когато я използвате, тя ще изпълнява макроса).
5. В опцията „Съхраняване на макроса в“ се уверете, че е избрана „Тази работна книга“. Тази стъпка гарантира, че макросът е част от работната книга. Той ще бъде там, когато го запазите и отворите отново, или дори ако го споделите с някого.
6. (По избор стъпка) Въведете описание. Обикновено не правя това, но ако сте изключително организирани, може да искате да добавите за какво става въпрос в макроса.
7. Щракнете върху OK. Щом щракнете върху OK, той започва да записва вашите действия в Excel. Можете да видите бутона „Спиране на записа“ в раздела „Разработчик“, който показва, че записът на макрос е в ход.
8. Изберете клетка A2.
9. Въведете текста Excel (или можете да използвате вашето име).
10. Натиснете клавиша Enter. Това ще избере клетка А3.
11. Кликнете върху бутона „Спиране на записа“ в раздела „Разработчик“.

Честито!

Току -що сте записали първия си макрос в Excel. Вече не си макро девствена.

Въпреки че макросът не прави нищо полезно, той ще послужи за целта си да обясни как работи макрорекордерът в Excel.

Сега нека продължим и тестваме този макрос.

Следвайте стъпките по -долу, за да тествате макроса:

1. Изтрийте текста в клетка А2. Това е за проверка дали макросът вмъква текста в клетка A2 или не.
2. Изберете всяка клетка - различна от A2. Това е за проверка дали макросът избира клетка A2 или не.
3. Щракнете върху раздела Разработчик.
4. В групата Код щракнете върху бутона Макроси.
5. В диалоговия прозорец Макрос щракнете върху Името на макроса - EnterText.
6. Щракнете върху бутона Run.

Ще забележите, че веднага щом щракнете върху бутона Изпълнение, текстът „Excel“ се вмъква в клетка A2 и клетка A3 се избира.

Сега всичко това може да се случи за част от секундата, но в действителност макросът - точно като послушен елф - следваше точно стъпките, които сте му показали, докато записвате макроса.

Така макросът първо избира клетката A2, след което въвежда текста Excel в него и след това избира клетката A3.

Забележка: Можете също да стартирате макроса с помощта на клавишната комбинация Control + Shift + N (задръжте клавишите Control и Shift и след това натиснете клавиша N). Това е същата клавишна комбинация, която зададохме на макроса, когато го записвахме.

Какво прави записът на макрос в бекънда

Сега нека да преминем към бекенда на Excel - VB Editor - и да видим какво всъщност прави записът на макрос.

Ето стъпките за отваряне на VB Editor в Excel:

1. Щракнете върху раздела Разработчик.
2. В групата Код щракнете върху бутона Visual Basic.

Или можете да използвате клавишната комбинация - ALT + F11 (задръжте клавиша ALT и натиснете F11), вместо горните две стъпки. Този пряк път също отваря същия VB Editor.

Сега, ако виждате VB Editor за първи път, не се претоварвайте.

Позволете ми бързо да ви запозная с анатомията на VB Editor.

• Лента с меню: Тук имате всички опции на VB Editor. Помислете за това като лентата на VBA. Той съдържа команди, които можете да използвате, докато работите с VB Editor.
• Лента с инструменти - Това е като лентата с инструменти за бърз достъп на VB редактора. Той идва с някои полезни опции и можете да добавите още опции към него. Неговото предимство е, че опцията в лентата с инструменти е само на един клик разстояние.
• Прозорец на Project Explorer - Тук Excel изброява всички работни книги и всички обекти във всяка работна книга. Например, ако имаме работна книга с 3 работни листа, тя ще се покаже в Project Explorer. Тук има някои допълнителни обекти, като модули, потребителски формуляри и модули на класа.
• Прозорец за код - Тук VBA кодът се записва или записва. Има прозорец с код за всеки обект, изброен в Project Explorer - като работни листове, работни книги, модули и т.н. По -късно в този урок ще видим, че записаният макрос влиза в кодовия прозорец на модул.
• Прозорец за свойства - Можете да видите свойствата на всеки обект в този прозорец. Често използвам този прозорец за имена на обекти или промяна на скритите свойства. Може да не виждате този прозорец, когато отворите VB редактора. За да покажете това, щракнете върху раздела изглед и изберете прозорец Свойства.
• Незабавен прозорец - Често използвам непосредствения прозорец, докато пиша код. Полезно е, когато искате да тествате някои изявления или докато отстранявате грешки. Възможно е да не се вижда по подразбиране и можете да го направите, като щракнете върху раздела Изглед и изберете опцията Незабавен прозорец.

Когато записахме макроса - EnterText, следните неща се случиха във VB Editor:

• Вмъкна се нов модул.
• Записан е макрос с името, което посочихме - EnterText
• Кодът е написан в кодовия прозорец на модула.

Така че, ако щракнете двукратно върху модула (в този случай модул 1), ще се появи прозорец с код, както е показано по-долу.

Ето кода, който ни даде макрорекордерът:

Sub EnterText () '' EnterText Macro '' Клавишна комбинация: Ctrl+Shift+N 'Range ("A2"). Изберете ActiveCell.FormulaR1C1 = "Excel" Range ("A3"). Изберете End Sub

Във VBA всяка линия, която следва ‘ (знак апостроф) не се изпълнява. Това е коментар, който е само с информационна цел. Ако премахнете първите пет реда от този код, макросът ще продължи да работи според очакванията.

Сега нека бързо да разгледам какво прави всеки ред код:

Кодът започва с Sub, последван от името на макроса и празни скоби. Sub е съкращение от Subroutine. Всяка подпрограма (наричана още процедура) във VBA започва със Sub и завършва с End Sub.

• Обхват (“A2”). Изберете - Този ред избира клетката A2.
• ActiveCell.FormulaR1C1 = „Excel“ - този ред въвежда текста Excel в активната клетка. Тъй като избрахме A2 като първа стъпка, той се превръща в нашата активна клетка.
• Обхват (“A3”). Изберете - Това избира клетка А3. Това се случва, когато натиснем клавиша Enter след въвеждане на текста, резултатът от което беше да изберете клетка A3.

Надявам се, че вече имате някои основни разбирания за това как да записвате макрос в Excel.

Имайте предвид, че кодът, написан от макрорекордер, в никакъв случай не е ефективен код.

Макрорекордерът понякога добавя много пух в кода, което понякога не е необходимо. Но това не означава, че не е полезно. За някой, който изучава VBA, макрорекордерът може да бъде чудесен начин да анализира как работят нещата във VBA.

Абсолютен срещу относителен макро запис

Вече знаете за абсолютни и относителни препратки в Excel … нали?

Ако не - първо прочетете този урок за препратки.

Прочети го? Нека да продължим.

По -късно в този раздел ще видим как да записваме макроси в абсолютни и относителни препратки. Но преди това, позволете ми бързо да обобщя разликата между абсолютната и относителната препратка във VBA (в случай, че сте мързеливи и не сте прочели връзката, която дадох преди няколко реда):

Ако използвате абсолютна референтна опция за запис на макрос, кодът VBA винаги ще се отнася за същите клетки, които сте използвали. Например, ако изберете клетка A2, въведете текста Excel и натиснете Enter всеки път - без значение къде се намирате в работния лист и без значение коя клетка е избрана, кодът ви първо ще избере клетка A2, ще въведе текста Excel и след това преминете към клетка А3.

Ако използвате опция за относителна референция за запис на макрос, VBA няма да кодира твърдо препратките към клетки. По -скоро тя ще се фокусира върху движението в сравнение с активната клетка. Да предположим например, че вече сте избрали клетка А1 и започвате записването на макроса в режим на относителна референция.

Сега избирате клетка A2, въвеждате текста Excel и натискате клавиша enter. Сега, когато стартирате този макрос, той няма да се върне в клетка A2, вместо това ще се премести спрямо активната клетка. Например, ако е избрана клетка K3, тя ще се премести в K4, ще въведе текста Excel и накрая ще избере клетка K5.

Сега нека ви кажа как да записвате макрос в режим на относителни препратки:

1. Изберете клетка A1.
2. Щракнете върху раздела Разработчик.
3. В групата „Код“ кликнете върху бутона „Използване на относителни препратки“. Той ще стане зелен, което показва, че е включен.
4. Щракнете върху бутона Запис на макрос.
5. В диалоговия прозорец Запис на макрос въведете име за вашия макрос. Използвам името EnterTextRelRef.
6. В опцията Съхранение на макрос в, уверете се, че е избрана „Тази работна книга“.
7. Щракнете върху OK.
8. Изберете клетка A2.
9. Въведете текста Excel (или можете да въведете името си).
10. Натиснете клавиша Enter. Това ще отведе курсора до клетката A3.
11. Кликнете върху бутона „Спиране на записа“ в раздела „Разработчик“.

Това ще запише макроса в режим на относителна референция.

Сега направете това.

1. Изберете всяка клетка (различна от A1).
2. Щракнете върху раздела Разработчик.
3. В групата Код щракнете върху бутона Макроси.
4. В диалоговия прозорец Макрос щракнете върху Името на макроса - EnterTextRelRef.
5. Щракнете върху бутона Run.

Какво става? Върна ли се курсорът в клетка А3.

Не би станало - защото сте записали макроса в режим на относителна справка. Така курсорът ще се движи спрямо активната клетка. Например, ако направите това, когато е избрана клетка K3, тя ще въведе текста Excel е клетка K4 и в крайна сметка ще избере клетка K5.

Ето кода, който се записва в бекенда (прозорец с код на модул VB Editor):

Sub EnterTextRelRef () '' EnterTextRelRef Macro '' ActiveCell.Offset (1, 0) .Range ("A1"). Изберете ActiveCell.FormulaR1C1 = "Excel" ActiveCell.Offset (1, 0) .Range ("A1"). Изберете End Sub

Имайте предвид, че целият този код не се отнася до клетките K3 или K4 никъде. Вместо това, той използва Activecell за препратка към избраната клетка и Offset за преместване спрямо активната клетка.

Не се притеснявайте за частта Range („A1“), която има кодът. Това е един от онези ненужни кодове, които макрорекордерът добавя, който няма никаква цел и може да бъде премахнат. Кодът би работил добре без него.

Бутонът „Използване на относителна справка“ в раздела „Разработчик“ е бутон за превключване. Можете да го изключите (и да се върнете към абсолютна справка), като щракнете върху него.

Какво не може да направи макрорекордерът

Макрорекордерът е чудесен да ви следва в Excel и да записва вашите точни стъпки, но може да ви провали, когато имате нужда от него, за да направите повече.

• Не можете да изпълните код, без да изберете обекта. Ако искате макрорекордерът да премине към следващия работен лист и да маркира всички попълнени клетки в колона А, без да напуска текущия работен лист, тогава той няма да може да направи това. Това е така, защото ако ви помоля да направите това, дори вие няма да можете да направите това (без да напускате текущия лист). И ако не можете да го направите сами, как макрорекордерът ще улови вашите действия. В такива случаи трябва ръчно да отидете и да създадете/редактирате кода.
• Не можете да създадете персонализирана функция с макрорекордер. С VBA можете да създавате персонализирани функции, които можете да използвате в работния лист като обикновени функции. Можете да създадете това, като напишете кода ръчно.
• Не можете да стартирате кодове въз основа на събития: Във VBA можете да използвате много събития - като отваряне на работна книга, добавяне на работен лист, двукратно щракване върху клетка и т.н., за да стартирате код, свързан с това събитие. Можете да използвате макрорекордер, за да направите това.
• Не можете да създавате цикли с макрорекордер. Когато ръчно въвеждате кода, можете да използвате силата на цикли във VBA (като For Next, For Every Next, Do While, Do until). Но не можете да направите това, когато записвате макрос.
• Не можете да анализирате условията: Можете да проверите за условия в кода с помощта на макрорекордер. Ако пишете VBA код ръчно, можете да използвате операторите IF Then Else, за да анализирате условие и да стартирате код, ако е истина (или друг код, ако е невярен).
• Не можете да предавате аргументи в макро процедура: Когато записвате макрос, той никога няма да има аргументи. Подпрограма може да приема входни аргументи, които могат да се използват в макроса за изпълнение на задача. Докато записвате макрос, това не може да се направи, тъй като записаните макроси са независими и не са свързани с други съществуващи макроси.

Разширения на файлове с активирани макроси

Когато записвате макрос или пишете ръчно VBA код в Excel, трябва да запишете файла с разширение на файла с активиран макрос (.xlsm).

Преди Excel 2007 имаше един единствен файлов формат, който беше достатъчен - .xls.

Но от 2007 г. нататък .xlsx беше въведен като стандартно разширение на файла. Файловете, запазени като .xlsx, не могат да съдържат макрос в него. Така че, ако имате файл с разширение .xlsx и записвате/пишете макрос и го запазвате, той ще ви предупреди да го запишете във формат с активиран макрос и ще ви покаже диалогов прозорец (както е показано по-долу):

Ако изберете Не, Excel ви позволява да го запишете във формат, поддържащ макроси. Но ако щракнете върху Да, Excel автоматично ще премахне целия код от работната ви книга и ще го запише като работна книга .xlsx.

Така че, ако имате макрос във вашата работна книга, трябва да го запишете във .xlsm формат, за да запазите този макрос.

Различни начини за стартиране на макрос в Excel

Досега видяхме само един начин за стартиране на макрос в Excel - който използва диалоговия прозорец Макрос.

Но има няколко начина, по които можете да стартирате макроси.

1. Стартирайте макрос от лентата (раздел за програмисти)
2. Използване на клавишна комбинация (която трябва да зададете)
3. Присвойте макроса на форма
4. Задайте макроса на бутон
5. Стартирайте макрос от VB Editor

Заключение - Запишете макрос, когато се задържите

Вече споменах, че макрорекордерът е полезен инструмент за всеки, който работи с VBA в Excel.

Докато използвате макрорекордера няколко пъти, ще забележите, че той изплюва много ненужен код. Въпреки това, той все още е полезен и ви дава някои идеи откъде да започнете. Например, ако ви помоля да филтрирате колона от клетки, използвайки VBA, и нямате представа какъв е синтаксисът, можете бързо да запишете макрос и да проверите кода.

Макрорекордерът е незаменим инструмент и дори след години опит в кодирането на VBA под моя колан, често прибягвам до помощта на макрорекордера.