Макрорекордерът на Excel има много мощност, но има своите ограничения. Както е описано в друга статия, макрорекордерът често записва ненужен код и не може да записва неща като логика или взаимодействие с други програми. Също така може да бъде трудно да се използва за по -дълги макроси - може да се окажете, че предварително разказвате вашите действия, само за да избегнете скъпи грешки.
Тази статия има за цел да ви помогне да започнете да кодирате макроси от нулата във VBA. Ще научите къде се съхраняват макроси, ще напишете основен макрос и ще научите основите на програмирането във VBA, използвайки променливи, логика и цикли.
Приготвяме се да започнем
VBA и редактора на Visual Basic
VBA или Visual Basic for Applications е езикът, на който са написани макросите. Всички макроси се съхраняват като код VBA, независимо дали са ръчно кодирани или създадени с макрорекордера.
Можете да получите достъп до целия код на VBA в работна книга, като използвате редактора на Visual Basic. Това е специален текстов редактор и инструмент за отстраняване на грешки, който е вграден във всички офис приложения, включително Excel. Обикновено ще отворите този редактор с ALT+F11 клавишна комбинация в Excel, но можете да получите достъп до нея и от Excel Разработчик раздел, ако сте го активирали.
Проекторът на проекта
The Project Explorer е прозорец във VB Editor, който ви показва всички елементи, които могат да имат VBA код в тях. Ако не виждате този прозорец, натиснете F5 за да се покаже или изберете Project Explorer от Изглед меню.
Двойното щракване върху елемент в Project Explorer ще покаже кода за този елемент. В Project Explorer могат да се появят няколко типа елементи:
- Работни тетрадки
- Работни листове
- UserForms
- Класови модули
- Модули (макросите се съхраняват в тези елементи)
Въпреки че всички тези типове елементи могат да включват VBA код, най -добрата практика е да кодирате макроси в модули.
Създаване на първия ви макрос
Използване на списъка с макроси
Списъкът с макроси ви показва всички макроси във вашата работна книга. От този списък можете да редактирате съществуващ макрос или да създадете нов.
За да създадете нов макрос, използвайки списъка с макроси:
- Изберете раздела Разработчик и щракнете Макроси (или натиснете ALT+F8)
- Въведете ново име за вашия макрос, след което щракнете върху „Създаване“
След като щракнете върху „Създаване“, ще се появи VB Editor, показващ новосъздадения макрос. Excel ще създаде нов модул за макроса, ако е необходимо.
Ръчно в редактора на VB
Можете да добавите нов макрос ръчно без списъка с макроси. Това е по -добрият вариант, ако искате да посочите модула, в който е записан макросът.
За да добавите макрос ръчно:
- Отворете VB Editor (ALT+F11)
- Или:
- Добавете нов модул, като щракнете върху Вмъкване> Модул в менюто (модулът ще се отвори автоматично)
-
- ИЛИ, щракнете двукратно върху съществуващ модул в Project Explorer, за да го отворите
- В модула въведете кода за новия си макрос
Sub MyMacro () Край Sub
Тези два реда показват началото и края на макрос с име „MyMacro“ (обърнете внимание на скобите, които са задължителни). Това ще се покаже в диалоговия прозорец „Преглед на макроси“ в Excel и може да бъде присвоено на бутон (въпреки че все още не прави нищо).
Добавете малко код към макроса
Сега, нека добавим малко код между редовете „Sub“ и „End Sub“, за да накараме този макрос действително да направи нещо:
Sub MyMacro () Диапазон („A1“). Стойност = „Здравей, свят!“ End Sub
Основни кодови структури
Обектът Range
Excel VBA използва Range Object за представяне на клетки в работен лист. В горния пример се създава обект Range с кода Диапазон („A1“) за достъп до стойността на клетка A1.
Range Objects се използват предимно за задаване на стойности на клетки:
Диапазон („A1“). Стойност = 1
Диапазон („A1“). Стойност = „Първа клетка“
Забележете, че когато определяте стойностите на клетката като числа, просто въвеждате числото, но когато въвеждате текст, трябва да го заобиколите с кавички.
Диапазоните могат да се използват и за достъп до много свойства на клетки като техния шрифт, рамки, формули и др.
Например, можете да зададете шрифта на клетка на Bold така:
Диапазон („A1“). Font.Bold = Вярно
Можете също да зададете формулата на клетка:
Обхват („A1“). Формула = „= Сума (A2: A10)“
В Excel можете да изберете блок от клетки с курсора (да речем, от A1 до D10) и да ги настроите на получер. Обектите на обхвата имат достъп до блокове от клетки по следния начин:
Обхват (“A1: D10”). Font.Bold = True
Можете също да се обърнете към няколко клетки/блока наведнъж:
Диапазон („A1: D10, A12: D12, G1“). Font.Bold = True
Форматът за това е същият като формата, който бихте използвали, когато избирате клетки за формулата SUM () в Excel. Всеки блок е разделен със запетая, а блоковете са обозначени с горната лява и долната дясна клетки, разделени с двоеточие.
И накрая, Range обектите имат вградени методи за извършване на общи операции на работен лист. Например, може да искате да копирате някои данни от едно място на друго. Ето един пример:
Диапазон („A1: D10“). Обхват на копиране („F1“). PasteSpecial xlPasteValues Диапазон („F1“). PasteSpecial xlPasteFormats
Това копира клетки A1: D10 в клипборда и след това прави PasteSpecial (), започвайки от клетка C1 - точно както бихте направили ръчно в Excel. Обърнете внимание, че този пример показва как да използвате PasteSpecial (), за да поставите само стойности и формати - има параметри за всички опции, които ще видите в диалоговия прозорец Paste Special.
Ето пример за поставяне на „Всички“ в друг работен лист:
Диапазон (“A1: D10”). Копиране на листове (“Sheet2”). Диапазон (“A1”). PasteSpecial xlPasteAll
Ако изявления
С един Ако изявление, можете да накарате част от кода да се изпълнява само „ако“ определено твърдение е вярно.
Например, може да искате да направите клетката удебелена и да я оцветите в червено, но само „ако“ стойността в клетката е по -малка от 100.
Ако Range (“A4”). Value <100 then Range (“A4”). Font.Bold = True Range ("A4"). Interior.Color = vbRed End If
Правилната структура на оператора If е следната (квадратните скоби показват незадължителни компоненти):
Ако тогава
[Иначе тогава]
[Иначе]
Край Ако
Можете да включите толкова Иначе блокира, както искате да тествате множество условия. Можете също да добавите Иначе блок, който се изпълнява само ако не са изпълнени нито едно от другите условия в оператора If.
Ето още един пример, базиран на предишния, където клетката се форматира по няколко различни начина в зависимост от стойността:
Ако Range ("A4"). Стойност <100 Тогава Range ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed ElseIf Range ("A4"). Стойност <200 Тогава Range ( "A4"). Font.Bold = False Range ("A4"). Interior.Color = vbЖълт друг диапазон ("A4"). Font.Bold = False Range ("A4"). Interior.Color = vbGreen End If
В горния пример клетката не се удебелява в блоковете ElseIf, където стойността не е под 100. Можете да гнездо Ако изявления за избягване на дублиране на код, като това:
Ако Range ("A4"). Стойност <100 Тогава Range ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed Else Range ("A4"). Font.Bold = False ' разгъване на шрифта само веднъж Ако Range ("A4"). Стойност <200 Тогава Range ("A4"). Interior.Color = vb Жълт друг диапазон ("A4"). Interior.Color = vb Зелен край, ако End Ако
Променливи
А Променлива е част от паметта, използвана за съхраняване на временна информация, докато се изпълнява макрос. Те често се използват в цикли като итератори или за задържане на резултата от операция, която искате да използвате няколко пъти в макроса.
Ето пример за променлива и как можете да я използвате:
Sub ExtractSerialNumber () Dim strSerial As String 'това е декларацията на променливата' 'As String' означава, че тази променлива е предназначена за задържане на текст 'настройване на преструвателен сериен номер: Range ("A4"). Value = “serial# 804567-88 ”'Анализирайте серийния номер от клетка А4 и го присвойте на променливата strSerial = Mid (Range (“ A4 ”). Value, 9)“ сега използвайте променливата два пъти, вместо да се налага да анализирате серийния номер два пъти Range (“ B4 ”). Стойност = strSerial MsgBox strSerial End Sub
В този основен пример променливата „strSerial“ се използва за извличане на серийния номер от клетка A4 с помощта на функцията Mid () и след това се използва на две други места.
Стандартният начин за декларирам променлива е както следва:
Дим каквото и да е име [Като Тип]
- каквото и да е име е името, което решавате да дадете на променливата си
- Тип е типът на данните на променливата
„[As Тип] ”Част може да бъде пропусната - ако е така, променливата се декларира като тип Variant, който може да съдържа всякакъв вид данни. Въпреки че са напълно валидни, типовете варианти трябва да се избягват, тъй като могат да доведат до неочаквани резултати, ако не внимавате.
Има правила за имена на променливи. Те трябва да започват с буква или знак за подчертаване, не могат да имат интервали, точки, запетаи, кавички или знаци “! @ & $ #”.
Ето някои примери за декларации на променливи:
Dim strFilename As String "стил на добро име - описателен и използва префикс Dim i As Long" стил на лошо име - приемлив само за някои итератори Dim SalePrice As Double "добре име стил - описателен, но не използва префикс Dim iCounter" добре име - не е твърде описателен, използва префикс, няма тип данни
Всички тези примери използват малко различни схеми за именуване, но всички са валидни. Не е лоша идея да добавите префикс към името на променлива с кратка форма на нейния тип данни (според някои от тези примери), тъй като това прави кода ви по -четим с един поглед.
VBA включва много основни типове данни. Най -популярните включват:
- Низ (използва се за съхраняване на текстови данни)
- Дълго (използва се за съхраняване на цели числа, т.е. без десетични знаци)
- Двойна (използва се за съхраняване на числа с плаваща запетая, т.е. десетични знаци)
Пълен списък на вътрешните типове данни на VBA можете да намерите тук: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Обхват на обектни променливи
Възможно е да се създадат променливи, които да се позовават и на Range Objects. Това е полезно, ако искате да се обърнете към определен диапазон в кода си на няколко места - по този начин, ако трябва да промените диапазона, трябва само да го промените на едно място.
Когато създавате обектна променлива на Range, трябва да я „настроите“ на екземпляр на Range. Например:
Dim rMyRange As Range Set rMyRange = Диапазон (“A1: A10; D1: J10”)
Оставянето на изявлението „Set“ при задаване на променлива Range ще доведе до грешка.
Цикли
Цикли са блокове, които повтарят кода вътре в тях определен брой пъти. Те са полезни за намаляване на количеството код, който трябва да напишете, и ви позволяват да напишете едно парче код, който изпълнява същите действия върху много различни свързани елементи.
For-Next
А For-Next блок е цикъл, който се повтаря определен брой пъти. Той използва променлива като итератор за да преброите колко пъти е изпълнено и тази променлива на итератора може да се използва вътре в цикъла. Това прави циклите For-Next много полезни за итерация през клетки или масиви.
Ето пример, който циклично преминава през клетки в редове 1 до 100, колона 1 и задава техните стойности на стойността на променливата итератор:
Dim i As Long For i = 1 To 100 Cells (i, 1). Value = i Next i
Редът „For i = 1 To 100“ означава, че цикълът започва от 1 и завършва след 100. Можете да зададете всички начални и крайни числа, които харесвате; можете също да използвате променливи за тези числа.
По подразбиране циклите For-Next се броят с 1. Ако искате да броите с различен номер, можете да напишете цикъла с изрично Стъпка клауза:
За i = 5 до 100 Стъпка 5
Този цикъл ще започне от 5, след което ще добавя 5 към „i“ всеки път, когато цикълът се повтаря (така че „i“ ще бъде 10 при второто повторение, 15 при третото и т.н.).
Използвайки Стъпка, можете също да направите цикъл на цикъл назад:
За i = 100 към 1 стъпка -1
Можете също гнездо Цикли For-Next. Всеки блок изисква своя собствена променлива, с която да се брои, но можете да използвате тези променливи където пожелаете. Ето пример за това как това е полезно в Excel VBA:
Dim i As Long, j As Long For i = 1 To 100 For j = 1 To 100 Cells (i, j). Value = i * j Next j Next i
Това ви позволява да преминете през редове и колони.
ВНИМАНИЕ: въпреки че е позволено, НИКОГА не трябва да променяте променливата на итератора в блок For-Next, тъй като тя използва този итератор, за да следи цикъла. Промяната на итератора може да причини безкраен цикъл и да окачи вашия макрос. Например:
За i = 1 До 100 i = 1 Следващо i
В този цикъл „I“ никога няма да надхвърли 2, преди да бъде нулиран на 1, а цикълът ще се повтаря завинаги.
За всеки
За всеки блоковете са много подобни на блоковете For-Next, с изключение на това, че не използват брояч, за да определят колко пъти се циклират. Вместо това блокът „За всеки“ взема „колекция“ от обекти (като диапазон от клетки) и се изпълнява толкова пъти, колкото има обекти в тази колекция.
Ето един пример:
Dim r като обхват за всеки r в обхват ("A15: J54") Ако r.Value> 0 Тогава r.Font.Bold = True End Ако Next r
Обърнете внимание на използването на обектната променлива „r“ на Range. Това е променливата на итератора, използвана в цикъла For -Each - всеки път през цикъла „r“ получава препратка към следващата клетка в диапазона.
Предимство от използването на цикли For-Each в Excel VBA е, че можете да преминете през всички клетки в диапазон без вложени цикли. Това може да бъде удобно, ако трябва да преминете през всички клетки в сложен диапазон като Обхват („A1: D12, J13, M1: Y12“).
Един недостатък на циклите For-Each е, че нямате контрол върху реда, в който се обработват клетките. Макар че на практика Excel ще премине през клетките в ред, на теория може да обработва клетките в напълно случаен ред. Ако трябва да обработвате клетки в определен ред, вместо това трябва да използвате цикли For-Next.
Do-Loop
Докато блоковете For-Next използват броячи, за да знаят кога да спрат, Do-Loop блоковете се изпълняват, докато не бъде изпълнено условие. За да направите това, използвайте До клауза в началото или в края на блока, която тества условието и кара цикъла да спре, когато това условие е изпълнено.
Пример:
Dim str As String str = "Buffalo" Do Until str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" Loop Range ("A1"). Стойност = str
В този цикъл „Buffalo“ се свързва към „str“ всеки път през цикъла, докато не съответства на очакваното изречение. В този случай тестът се извършва в началото на цикъла - ако 'str' вече е очакваното изречение (което не е така, защото не сме го започнали по този начин, но ако) цикълът дори няма да работи .
Можете да накарате цикъла да се изпълнява поне веднъж, като преместите клаузата Until до края, по следния начин:
Do str = str & "" & "Buffalo" Loop До str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo"
Можете да използвате каквато версия има смисъл във вашия макрос.
ВНИМАНИЕ: можете да предизвикате безкраен цикъл с блок Do-Loop, ако условието До никога не е изпълнено. Винаги пишете кода си така, че условието До определено да бъде изпълнено, когато използвате този тип цикъл.
Какво следва?
След като сте разбрали основите, защо не опитате да научите някои по -напреднали техники? Нашият урок на адрес https://easyexcel.net/excel/learn-vba-tutorial/ ще надгражда всичко, което сте научили тук и ще разшири уменията ви със събития, потребителски формуляри, оптимизация на кодове и много други!
