Урок за Excel VBA - Как да напиша код в електронна таблица с помощта на Visual Basic

Въведение

Това е урок за писане на код в електронни таблици на Excel с помощта на Visual Basic for Applications (VBA).

Excel е един от най-популярните продукти на Microsoft. През 2016 г. главният изпълнителен директор на Microsoft каза: „Мислете за свят без Excel. Това е просто невъзможно за мен.“ Е, може би светът не може да мисли без Excel.

  • През 1996 г. имаше над 30 милиона потребители на Microsoft Excel (източник).
  • Днес има около 750 милиона потребители на Microsoft Excel. Това е малко повече от населението на Европа и 25 пъти повече потребители, отколкото през 1996 г.

Ние сме едно голямо щастливо семейство!

В този урок ще научите за VBA и как да пишете код в електронна таблица на Excel с помощта на Visual Basic.

Предпоставки

За да разберете този урок, не ви е необходим никакъв предварителен опит в програмирането. Ще ви трябва обаче:

  • Основно до средно запознаване с Microsoft Excel
  • Ако искате да следвате заедно с примерите за VBA в тази статия, ще ви е необходим достъп до Microsoft Excel, за предпочитане най-новата версия (2019), но Excel 2016 и Excel 2013 ще работят добре.
  • Готовност да опитате нови неща

Цели на обучението

По време на тази статия ще научите:

  1. Какво е VBA
  2. Защо бихте използвали VBA
  3. Как да се настроите в Excel за писане на VBA
  4. Как да решим някои реални проблеми с VBA

Важни понятия

Ето някои важни концепции, с които трябва да сте запознати, за да разберете напълно този урок.

Обекти : Excel е обектно-ориентиран, което означава, че всичко е обект - прозорецът на Excel, работната книга, лист, диаграма, клетка. VBA позволява на потребителите да манипулират и извършват действия с обекти в Excel.

Ако нямате никакъв опит с обектно-ориентирано програмиране и това е чисто нова концепция, отделете секунда, за да оставите това да потъне!

Процедури : процедурата е част от кода на VBA, написана в редактора на Visual Basic, която изпълнява задача. Понякога това се нарича и макрос (повече за макросите по-долу). Има два вида процедури:

  • Подпрограми: група VBA изрази, която изпълнява едно или повече действия
  • Функции: група VBA изрази, която изпълнява едно или повече действия и връща една или повече стойности

Забележка: можете да имате функции, работещи вътре в подпрограмите. Ще видите по-късно.

Макроси : Ако сте прекарали известно време в изучаване на по-усъвършенствана функционалност на Excel, вероятно сте се сблъскали с концепцията за „макрос“. Потребителите на Excel могат да записват макроси, състоящи се от потребителски команди / натискания на клавиши / щраквания, и да ги възпроизвеждат със светкавична скорост, за да изпълняват повтарящи се задачи. Записаните макроси генерират VBA код, който след това можете да разгледате. Всъщност е доста забавно да запишете прост макрос и след това да разгледате кода на VBA.

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

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

Какво е VBA?

Visual Basic for Applications е език за програмиране, разработен от Microsoft. Всяка софтуерна програма в пакета Microsoft Office се доставя с езика VBA без допълнителни разходи. VBA позволява на потребителите на Microsoft Office да създават малки програми, които работят в рамките на софтуерни програми на Microsoft Office.

Мислете за VBA като за фурна за пица в ресторант. Excel е ресторантът. Кухнята се предлага със стандартни търговски уреди, като големи хладилници, печки и обикновени фурни - всичко това са стандартните функции на Excel.

Но какво, ако искате да направите пица на дърва? Не можете да го направите в стандартна търговска фурна за печене. VBA е фурната за пица.

Нм.

Защо да използвам VBA в Excel?

Защото пицата на дърва е най-добрата!

Но сериозно.

Много хора прекарват много време в Excel като част от работата си. Времето в Excel също се движи по различен начин. В зависимост от обстоятелствата 10 минути в Excel могат да се почувстват като вечност, ако не сте в състояние да направите това, от което се нуждаете, или 10 часа могат да минат много бързо, ако всичко върви чудесно. Кое е, когато трябва да се запитате, защо, по дяволите, прекарвам 10 часа в Excel?

Понякога тези дни са неизбежни. Но ако прекарвате 8-10 часа всеки ден в Excel, като правите повтарящи се задачи, повтаряте много от същите процеси, опитвате се да почистите след други потребители на файла или дори актуализирате други файлове, след като са направени промени във файла на Excel, процедура VBA може да е решението за вас.

Трябва да обмислите използването на VBA, ако трябва:

  • Автоматизирайте повтарящите се задачи
  • Създайте лесни начини за взаимодействие на потребителите с вашите електронни таблици
  • Манипулирайте с големи количества данни

Настройка за писане на VBA в Excel

Раздел за програмисти

За да напишете VBA, ще трябва да добавите раздела Developer към лентата, така че ще видите лентата така.

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

  1. В раздела Файл отидете на Опции> Персонализиране на лентата.
  2. Под Персонализиране на лентата и под Основни раздели поставете отметка в квадратчето Разработчик.

След като покажете раздела, раздел Разработчик остава видим, освен ако не премахнете отметката или не трябва да преинсталирате Excel. За повече информация вижте помощната документация на Microsoft.

VBA редактор

Придвижете се до раздела за програмисти и щракнете върху бутона Visual Basic. Ще се появи нов прозорец - това е редакторът на Visual Basic. За целите на този урок просто трябва да сте запознати с прозореца Project Explorer и екрана Properties Properties.

Примери за Excel VBA

Първо, нека създадем файл, в който да играем.

  1. Отворете нов файл на Excel
  2. Запазете го като работна книга с активиран макрос (. Xlsm)
  3. Изберете раздела за програмисти
  4. Отворете редактора на VBA

Нека да разтърсим с няколко лесни примера, за да ви накараме да пишете код в електронна таблица с помощта на Visual Basic.

Пример # 1: Показване на съобщение, когато потребителите отворят работната книга на Excel

В редактора на VBA изберете Вмъкване -> Нов модул

Напишете този код в прозореца на модула (не поставяйте!):

Sub Auto_Open ()

MsgBox („Добре дошли в работната книга на XYZ.“)

Крайна под

Запазете, затворете работната книга и я отворете отново. Този диалогов прозорец трябва да се покаже.

Та да!

Как се прави това?

В зависимост от запознатостта ви с програмирането може да имате някои предположения. Не е особено сложно, но се случва доста:

  • Под (съкратено от „Подпрограма): запомнете от самото начало,„ група от оператори на VBA, които изпълняват едно или повече действия. “
  • Auto_Open: това е конкретната подпрограма. Той автоматично стартира вашия код, когато се отвори файлът на Excel - това е събитието, което задейства процедурата. Auto_Open ще работи само когато работната книга се отваря ръчно; няма да се стартира, ако работната книга се отваря чрез код от друга работна книга (Workbook_Open ще направи това, научете повече за разликата между двете).
  • По подразбиране достъпът на подпрограмата е публичен. Това означава, че всеки друг модул може да използва тази подпрограма. Всички примери в този урок ще бъдат публични подпрограми. Ако е необходимо, можете да декларирате подпрограмите като частни. Това може да е необходимо в някои ситуации. Научете повече за модификаторите за достъп до подпрограмата.
  • msgBox: това е функция - група от VBA изрази, която изпълнява едно или повече действия и връща стойност. Върнатата стойност е съобщението „Добре дошли в XYZ Workbook.“

Накратко, това е проста подпрограма, която съдържа функция.

Кога мога да използвам това?

Може би имате много важен файл, който е достъпен рядко (да речем веднъж на тримесечие), но се актуализира автоматично ежедневно от друга процедура на VBA. Когато е достъпен, това е от много хора в множество отдели, из цялата компания.

  • Проблем: По-голямата част от времето, когато потребителите имат достъп до файла, те са объркани относно целта на този файл (защо съществува), как се актуализира толкова често, кой го поддържа и как трябва да си взаимодействат с него. Новите служители винаги имат много въпроси и вие трябва да задавате тези въпроси отново и отново и отново.
  • Решение: създайте потребителско съобщение, което съдържа кратък отговор на всеки от тези често отговарящи въпроси.

Примери от реалния свят

  • Използвайте функцията MsgBox, за да покажете съобщение, когато има някакво събитие: потребителят затваря работна книга на Excel, потребител отпечатва, нов лист се добавя към работната книга и т.н.
  • Използвайте функцията MsgBox, за да покажете съобщение, когато потребителят трябва да изпълни условие, преди да затвори работна книга на Excel
  • Използвайте функцията InputBox, за да получите информация от потребителя

Пример # 2: Позволете на потребителя да изпълни друга процедура

В редактора на VBA изберете Вмъкване -> Нов модул

Напишете този код в прозореца на модула (не поставяйте!):

Sub UserReportQuery ()

Затъмнете потребителското въвеждане толкова дълго

Затъмнете отговора като цяло число

UserInput = vbYesNo

Отговор = MsgBox („Обработка на отчета XYZ?“, UserInput)

Ако отговор = vbДа, тогава ProcessReport

Крайна под

Доклад за подпроцеса ()

MsgBox („Благодаря за обработката на отчета XYZ.“)

Крайна под

Запазете и се върнете обратно в раздела за програмисти на Excel и изберете опцията „Бутон“. Щракнете върху клетка и присвойте макроса UserReportQuery на бутона.

Сега щракнете върху бутона. Това съобщение трябва да показва:

Щракнете върху „да“ или натиснете Enter.

Още веднъж, тада!

Моля, обърнете внимание, че вторичната подпрограма, ProcessReport, може да бъде всичко . Ще демонстрирам повече възможности в пример # 3. Но първо ...

Как се прави това?

Този пример се основава на предишния пример и има доста нови елементи. Нека да разгледаме новите неща:

  • Dim UserInput As Long: Dim е съкращение от „dimension“ и ви позволява да декларирате имена на променливи. В този случай UserInput е името на променливата, а Long е типът данни. На обикновен английски този ред означава „Ето променлива, наречена„ UserInput “, и това е тип Long променлива.“
  • Dim Answer As Integer: декларира друга променлива, наречена "Answer", с тип данни Integer. Научете повече за типовете данни тук.
  • UserInput = vbYesNo: присвоява стойност на променливата. В този случай vbYesNo, който показва бутоните Да и Не. Има много типове бутони, научете повече тук.
  • Answer = MsgBox („Обработка на отчета XYZ?“, UserInput): присвоява стойността на променливата Answer да бъде функция MsgBox и променлива UserInput. Да, променлива в променлива.
  • Ако Answer = vbYes Тогава ProcessReport: това е “If statement”, условно изявление, което ни позволява да кажем дали x е вярно, тогава направете y. В този случай, ако потребителят е избрал „Да“, изпълнете подпрограмата ProcessReport.

Кога мога да използвам това?

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

Например, може би имате файл, който се използва за генериране на 3 различни седмични отчета. Тези отчети са форматирани по драматично различни начини.

  • Проблем: Всеки път, когато трябва да се генерира един от тези отчети, потребителят отваря файла и променя форматирането и диаграмите; и така нататък. Този файл се редактира обширно поне 3 пъти седмично и отнема поне 30 минути всеки път, когато се редактира.
  • Решение: създайте 1 бутон за всеки тип отчет, който автоматично преформатира необходимите компоненти на отчетите и генерира необходимите диаграми.

Примери от реалния свят

  • Създайте диалогов прозорец за потребителя за автоматично попълване на определена информация в множество листове
  • Използвайте функцията InputBox, за да получите информация от потребителя, която след това се попълва в множество листове

Пример # 3: Добавяне на числа към диапазон с цикъл For-Next

За цикли са много полезни, ако трябва да изпълнявате повтарящи се задачи за определен диапазон от стойности - масиви или диапазони от клетки. На обикновен английски, цикълът казва „за всяко x направете y“.

В редактора на VBA изберете Вмъкване -> Нов модул

Напишете този код в прозореца на модула (не поставяйте!):

Sub LoopExample ()

Затъмнете X като цяло число

За X = 1 до 100

Обхват ("A" & X). Стойност = X

Следваща X

Крайна под

Запазете и се върнете обратно в раздела за програмисти на Excel и изберете бутона Макроси. Стартирайте макроса LoopExample.

Това трябва да се случи:

И т.н., до 100-ия ред.

Как се прави това?

  • Dim X As Integer: декларира променливата X като тип данни на Integer.
  • За X = 1 до 100: това е началото на цикъла For. Просто казано, той казва на цикъла да продължава да се повтаря, докато X = 100. X е броячът . Цикълът ще продължи да се изпълнява до X = 100, изпълнява се за последен път и след това спира.
  • Обхват ("A" и X). Стойност = X: това декларира обхвата на цикъла и какво да поставите в този диапазон. Тъй като X = 1 първоначално, първата клетка ще бъде A1, в който момент цикълът ще постави X в тази клетка.
  • Следващ X: това казва на цикъла да се стартира отново

Кога мога да използвам това?

Цикълът For-Next е една от най-мощните функционалности на VBA; има многобройни потенциални случаи на употреба. Това е по-сложен пример, който ще изисква множество слоеве логика, но той съобщава света на възможностите в цикли For-Next.

Може би имате списък на всички продукти, продавани във вашата пекарна в колона А, вида на продукта в колона Б (торти, понички или кифли), цената на съставките в колона В и пазарните средни разходи за всеки вид продукт в друг лист.

Трябва да разберете каква трябва да бъде цената на дребно на всеки продукт. Мислите, че цената на съставките трябва да бъде плюс 20%, но също така и 1,2% под средното ниво на пазара, ако е възможно. Цикъл For-Next ще ви позволи да направите този вид изчисление.

Примери от реалния свят

  • Използвайте цикъл с вложен оператор if, за да добавите конкретни стойности към отделен масив само ако отговарят на определени условия
  • Извършете математически изчисления за всяка стойност в диапазон, например изчислете допълнителни такси и ги добавете към стойността
  • Повтаряйте всеки символ в низ и извличайте всички числа
  • Случайно изберете брой стойности от масив

Заключение

Сега, когато говорихме за пица и кифли и о-да, как да напишем VBA код в електронни таблици на Excel, нека направим проверка на обучението. Вижте дали можете да отговорите на тези въпроси.

  • Какво е VBA?
  • Как да се настроя да започна да използвам VBA в Excel?
  • Защо и кога бихте използвали VBA?
  • Кои са някои проблеми, които бих могъл да реша с VBA?

Ако имате добра представа как да отговорите на тези въпроси, това е успешно.

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

Приятно кодиране!

Учебни ресурси

  • Програмиране на Excel VBA за манекени, John Walkenbach
  • Започнете с VBA, документация на Microsoft
  • Изучаване на VBA в Excel, Lynda

Малко за мен

Аз съм Клоу Тъкър, художник и разработчик в Портланд, Орегон. Като бивш преподавател непрекъснато търся пресечната точка на ученето и преподаването или технологиите и изкуството. Свържете се с мен в Twitter @_chloetucker и проверете уебсайта ми на chloe.dev.