Това са моите бележки за шоуто от появата ми в епизод 33 от Call for Help на TechTV Canada.
VBA Macro Recorder
Всяко копие на Excel, продадено от 1995 г., включва невероятно мощния Visual Basic for Applications (VBA), скрит зад клетките. Ако имате Excel, имате VBA.
VBA ви позволява да автоматизирате всичко, което можете да правите в Excel, плюс да правите повече неща, които обикновено не са възможни в Excel.
За наш късмет Microsoft включи макрорекордер с Excel. Това ви позволява да пишете VBA код, без да сте програмист. За съжаление, Macro Recorder е с недостатък. Той няма да създаде код, който ще работи всеки път. Макрорегистраторът ще ви приближи, но в много случаи трябва да поправите малко кода, за да работи надеждно. В други случаи трябва да знаете кога да използвате относителния запис, за да накарате макроса да работи.
С Трейси Сирстад написахме VBA и Macros Microsoft Excel 2016, за да ви помогнем да разберете ограниченията на записания код и да ви научим как да фиксирате записания код в нещо, което ще работи всеки път.
В шоуто демонстрирах процеса на запис на макрос, който ще работи перфектно, ако знаете как да използвате бутона Relative Reference. Някой ми беше дал работен лист на Excel със стотици имена и адреси. Искаха пощенски етикети. Ако някога сте използвали функцията за обединяване на поща в Microsoft Word, знаете, че имате нужда от всяко поле в нова колона в работния лист. Вместо това в този конкретен работен лист данните се спускаха надолу в колона А.
Процесът за поправяне на един етикет е доста досаден.
- Започнете с показалеца на клетката върху името в колона А.
- Натиснете стрелката надолу, за да се придвижите до адреса
- Ctrl + x за изрязване
- Стрелка нагоре, стрелка надясно, за да се придвижите до колона B до името
- Ctrl + v за поставяне
- Стрелка надолу два пъти, стрелка наляво веднъж, за да се придвижите до града
- Ctrl + x за изрязване
- Стрелка нагоре два пъти, стрелка надясно три пъти
- Ctrl + v за поставяне
- Стрелка наляво два пъти, стрелка надолу веднъж, за да преминете към вече празния ред.
- Задръжте натиснат клавиша Shift, докато натискате стрелката надолу два пъти
- Alt + edr за изтриване на празните редове
- Стрелка нагоре и стрелка надолу, за да изберете отново само името.
Ето анимацията, показваща тези стъпки:
Настройка на Excel за разрешаване на макроси
Въпреки че всяко копие на Excel съдържа VBA, по подразбиране Microsoft изключва възможността за стартиране на макроси. Трябва да направите еднократна корекция, за да позволите на макросите да се изпълняват. Отворете Excel. От менюто изберете Tools> Macro> Security. Ако в момента нивото на защита на макроса е настроено на високо, променете го на Средно.
Подготовка за запис на макроса
Помислете за стъпките, необходими за постигане на задачата. Искате да сте сигурни, че започвате с указателя на клетка върху име и завършвате с него на следващото име. Това ще ви позволи многократно да стартирате макроса. Когато сте подготвили стъпките, включете макрорекордера. От менюто изберете Tools> Macro> Record New Macro.
Ако ще използвате този макрос в продължение на много дни, трябва да му дадете полезно име. Ако е еднократен макрос за автоматизиране на еднократна задача, тогава оставянето на името на макроса като Macro1 вероятно е добре. Важното поле тук е полето на клавишната комбинация. Ако създавате макрос за еднократна употреба, задайте макроса на клавиш за бърз достъп, като Ctrl + a - доста лесно е да натиснете Ctrl + a многократно, тъй като клавишите са близо един до друг. Ако ще създавате макрос, който ще използвате всеки ден, тогава искате да присвоите макроса на ключ, който все още не е важна комбинация от клавишни комбинации. Ctrl + j или Ctrl + k са добър избор.
Макросите за еднократна употреба трябва да се съхраняват в ThisWorkbook. Ако трябва да използвате макроса многократно в много различни работни книги, можете да съхраните макроса в личната работна книга за макроси.
Вече сте представени с най-малката лента с инструменти в целия Excel; лентата с инструменти за спиране на записа. Той има само две икони и изглежда така:
Ако тази лента с инструменти ви попречи, не натискайте X, за да я затворите. Вместо това вземете синята лента и плъзнете лентата с инструменти на ново място. Актът на преместване на лентата с инструменти не се записва в макроса. Ако случайно затворите лентата с инструменти, върнете я с Изглед> Ленти с инструменти> Спиране на записа. Това действие обаче ще бъде записано.
Първият бутон в лентата с инструменти е бутонът „Спиране на записа“. Това се обяснява само по себе си. Когато приключите със записа на макроса, натиснете лентата с инструменти за спиране на записа.
По-важният (и рядко разбираем) бутон е бутона "Относителна справка".
В нашия текущ пример трябва да натиснете бутона Relative Reference преди да започнете. Ако записвате макрос с включени относителни препратки, Excel ще запише стъпки по следния начин:
- Премести една клетка надолу
- Изрежете текущата клетка
- Премести една клетка нагоре
- Премести една клетка надясно
- Поставете
- и т.н.
Ако вместо това запишете макроса без относителни препратки, той ще запише тези стъпки:
- Преместване в клетка A4
- Изрежете клетка A4
- Преместване в клетка A3
- Преместване в клетка B3
- Поставете в клетка B3
- и т.н.
Това би било ужасно погрешно - трябва ни макросът да работи върху клетки, които са 1 и 2 клетки от началната точка на макроса. Докато стартирате макроса отново и отново, началната точка ще бъде ред 4, ред 5, ред 6 и т.н. Записването на макроса без включени относителни препратки ще доведе до това, че макросът винаги се изпълнява на ред 3 като начална точка.
Следвай тези стъпки:
- Изберете относителния референтен бутон в лентата с инструменти за спиране на записа
- Показалецът на клетката вече трябва да е върху име в колона А.
- Натиснете стрелката надолу, за да се придвижите до адреса
- Ctrl + x за изрязване
- Стрелка нагоре, стрелка надясно, за да се придвижите до колона B до името
- Ctrl + v за поставяне
- Стрелка надолу два пъти, стрелка наляво веднъж, за да се придвижите до града
- Ctrl + x за изрязване
- Стрелка нагоре два пъти, стрелка надясно три пъти
- Ctrl + v за поставяне
- Стрелка наляво два пъти, стрелка надолу веднъж, за да преминете към вече празния ред.
- Задръжте натиснат клавиша Shift, докато натискате стрелката надолу два пъти
- Alt + edr за изтриване на празните редове
- Стрелка нагоре и стрелка надолу, за да изберете следващото име в списъка.
- Натиснете лентата с инструменти Stop Stop
- Запазете работната книга
- Тествайте макроса, като натиснете клавишната комбинация, зададена по-горе. Тя трябва да фиксира идеално следващото име в списъка
След като видите, че макросът работи по желание, можете да задържите Ctrl + a за бързо фиксиране на няколко имена в секунда. Целият списък с 500 имена може да бъде фиксиран за минута или две.
Бонус съвет - Не в шоуто
Можете лесно да увиете макроса в обикновен цикъл, за да коригира всички 500 имена, без да се налага да натискате Ctrl + няколкостотин пъти.
Натиснете клавиша Alt + F11, за да отворите редактора на макроси.
Ако не виждате прозореца Project - VBAProject, натиснете Ctrl + r.
Щракнете с десния бутон върху Module1 и изберете View Code. Ще видите код, който изглежда така:
Сега не е нужно да разбирате какво прави този код, но знаете, че искаме да изпълним всичко в този макрос веднъж за всяко име, което имаме. Ако знаете, че има 462 имена, можете да добавите 2 реда, за да стартирате кода 462 пъти. В горната част на макроса поставете нов ред с думите " For i = 1 to 462". В долната част на макроса поставете ред, който казва " Next i". Това казва на VBA да изпълнява кода вътре в 462 пъти.
Заключение
След този прост макрос показах пример в шоуто на много сложен макрос. Този макрос създава обобщени таблици и диаграми за 46 отдела на дадена компания. Този доклад отнемаше 40 часа всеки месец. Макросът VBA вече работи и създава всички 46 отчета за по-малко от 2 минути.
Книгата VBA и макроси Microsoft Excel 2016 ще ви преведе нагоре по кривата на обучение, за да можете да преминете от запис на прости макроси като този до изготвяне на много сложни отчети, които могат да ви спестят стотици часове годишно. Разбира се, ако не искате да научите VBA, наемете консултант на Excel, който да ви изготви отчет.
