Водич за Екцел ВБА - Како написати код у прорачунској табели помоћу Висуал Басиц-а

Увод

Ово је водич о писању кода у Екцел табелама помоћу Висуал Басиц-а за апликације (ВБА).

Екцел је један од најпопуларнијих Мицрософтових производа. 2016. године извршни директор Мицрософта рекао је „Размислите о свету без Екцел-а. То је за мене једноставно немогуће.“ Па, можда свет не може да размишља без Екцел-а.

  • 1996. године било је преко 30 милиона корисника програма Мицрософт Екцел (извор).
  • Данас постоји око 750 милиона корисника Мицрософт Екцел-а. То је мало више од становништва Европе и 25 пута више корисника него што их је било 1996. године.

Ми смо једна велика срећна породица!

У овом упутству ћете научити о ВБА и како написати код у Екцел табелу помоћу Висуал Басиц-а.

Предуслови

За разумевање овог водича није вам потребно претходно искуство у програмирању. Међутим, требат ће вам:

  • Основно до средње познавање Мицрософт Екцел-а
  • Ако желите да следите примере ВБА у овом чланку, биће вам потребан приступ програму Мицрософт Екцел, по могућности најновијој верзији (2019), али Екцел 2016 и Екцел 2013 ће радити сасвим у реду.
  • Спремност да испробате нове ствари

Циљеви учења

Током овог чланка научићете:

  1. Шта је ВБА
  2. Зашто бисте користили ВБА
  3. Како се подесити у програму Екцел за писање ВБА
  4. Како решити неке стварне проблеме са ВБА

Важни концепти

Ево неколико важних концепата са којима бисте требали бити упознати да бисте у потпуности разумели ово упутство.

Објекти : Екцел је објектно оријентисан, што значи да је све објект - Екцел прозор, радна свеска, лист, графикон, ћелија. ВБА омогућава корисницима да манипулишу и извршавају радње са објектима у програму Екцел.

Ако немате искуства са објектно оријентисаним програмирањем, а ово је потпуно нови концепт, одвојите секунду и пустите да то потоне!

Поступци : процедура је део ВБА кода, написан у Висуал Басиц Едитор-у, који извршава задатак. Понекад се ово назива и макро (више о макроима у наставку). Постоје две врсте поступака:

  • Подпрограми: група ВБА израза која извршава једну или више радњи
  • Функције: група ВБА израза која изводи једну или више радњи и враћа једну или више вредности

Напомена: функције могу да раде унутар потпрограма. Видећете касније.

Макрои : Ако сте неко време провели учећи напредније Екцел функције, вероватно сте се сусрели са концептом „макронаредбе“. Корисници Екцел-а могу снимати макронаредбе које се састоје од корисничких команди / притиска тастера / кликова и репродукују их брзином муње да би извршавали задатке који се понављају. Снимљени макронаредбе генеришу ВБА код који затим можете испитати. Заправо је прилично забавно снимити једноставан макро, а затим погледати ВБА код.

Имајте на уму да је понекад можда лакше и брже снимити макро, а не ручно кодирати ВБА процедуру.

На пример, можда радите у управљању пројектима. Једном недељно морате да претворите сирови извезени извештај из система управљања пројектима у лепо обликован, чист извештај за вођство. Имена прекомерних буџетских пројеката морате форматирати подебљаним црвеним текстом. Можете да забележите промене у обликовању као макро и покренете их кад год желите да извршите промену.

Шта је ВБА?

Висуал Басиц за апликације је програмски језик који је развио Мицрософт. Сваки софтверски програм у пакету Мицрософт Оффице испоручује се са језиком ВБА без додатних трошкова. ВБА омогућава корисницима Мицрософт Оффице-а да креирају мале програме који раде у оквиру софтверских програма Мицрософт Оффице.

Замислите ВБА као пећницу за пицу у ресторану. Екцел је ресторан. Кухиња долази са стандардним комерцијалним апаратима, попут великих фрижидера, шпорета и обичних пећница - то су све стандардне карактеристике програма Екцел.

Али шта ако желите да направите пицу на дрва? То не можете учинити у стандардној комерцијалној рерни за печење. ВБА је пећ за пицу.

Њам.

Зашто користити ВБА у програму Екцел?

Јер је пица на дрва најбоља!

Али озбиљно.

Многи људи проводе пуно времена у програму Екцел као део свог посла. Време у програму Екцел такође се креће другачије. У зависности од околности, 10 минута у програму Екцел може се осећати као вечност ако нисте у стању да урадите оно што вам треба или 10 сати може врло брзо да прође ако све иде одлично. Тада се треба запитати, зашто, побогу, проводим 10 сати у програму Екцел?

Понекад су ти дани неизбежни. Али ако свакодневно у програму Екцел проводите 8-10 сати радећи понављајуће задатке, понављајући многе исте процесе, покушавајући да очистите друге кориснике датотеке или чак ажурирате друге датотеке након што су у Екцел датотеку унете промене ВБА поступак би могао бити решење за вас.

Размислите о коришћењу ВБА ако вам је потребно:

  • Аутоматизујте понављајуће задатке
  • Створите једноставне начине за интеракцију корисника са вашим прорачунским таблицама
  • Манипулишите великим количинама података

Подешавање писања ВБА у програму Екцел

Картица програмера

Да бисте писали ВБА, мораћете да додате картицу Девелопер на траку, па ћете видети траку попут ове.

Да бисте додали картицу Девелопер на врпцу:

  1. На картици Датотека идите на Опције> Прилагоди траку.
  2. У оквиру Прилагоди врпцу и под Главне картице потврдите избор у пољу за програмере.

Након што прикажете картицу, картица Девелопер остаје видљива, осим ако не уклоните ознаку из поља за потврду или морате поново инсталирати Екцел. За више информација погледајте Мицрософт-ову документацију за помоћ.

ВБА Едитор

Дођите до картице Девелопер и кликните на дугме Висуал Басиц. Појавиће се нови прозор - ово је Висуал Басиц Едитор. За потребе овог водича, само требате бити упознати са окном Пројецт Екплорер и окном Својства својства.

Екцел ВБА примери

Прво, хајде да креирамо датотеку у којој ћемо се играти.

  1. Отворите нову Екцел датотеку
  2. Сачувајте га као радну свеску са омогућеним макронаредбама (. Кслсм)
  3. Изаберите картицу Девелопер
  4. Отворите ВБА Едитор

Хајде да се позабавимо неким једноставним примерима како бисте натерали писање кода у табелу помоћу Висуал Басиц-а.

Пример # 1: Прикажите поруку када корисници отворе Екцел радну свеску

У ВБА Едитор-у одаберите Инсерт -> Нев Модуле

Напишите овај код у прозор Модула (не лепите!):

Суб Ауто_Опен ()

МсгБок („Добро дошли у КСИЗ радну свеску.“)

Енд Суб

Сачувајте, затворите радну свеску и поново отворите радну свеску. Овај дијалог треба да се прикаже.

Та да!

Како то ради?

У зависности од вашег познавања програмирања, можда имате неке претпоставке. Није нарочито сложен, али догађа се доста тога:

  • Суб (скраћено од „Суброутине): запамтите од почетка,„ група ВБА изјава која извршава једну или више радњи. “
  • Ауто_Опен: ово је специфична потпрограм. Аутоматски покреће ваш код када се отвори Екцел датотека - ово је догађај који покреће процедуру. Ауто_Опен ће се покренути само када се радна свеска отвори ручно; неће се покренути ако се радна свеска отвори помоћу кода из друге радне свеске (Воркбоок_Опен ће то урадити, сазнајте више о разлици између те две).
  • Приступ потпрограму је подразумевано јаван. То значи да било који други модул може да користи ову потпрограм. Сви примери у овом упутству биће јавне потпрограме. Ако је потребно, потпрограме можете прогласити приватним. Ово ће можда бити потребно у неким ситуацијама. Сазнајте више о модификаторима приступа потпрограмима.
  • мсгБок: ово је функција - група ВБА израза која изводи једну или више радњи и враћа вредност. Враћена вредност је порука „Добро дошли у КСИЗ радну свеску“.

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

Када бих ово могао да користим?

Можда имате врло важну датотеку којој се приступа ретко (рецимо једном у тромесечју), али се аутоматски ажурира свакодневно помоћу другог ВБА поступка. Када му приступе, приступају многи људи из више одељења, широм компаније.

  • Проблем: Већину времена када корисници приступају датотеци, они су збуњени око сврхе ове датотеке (зашто постоји), како се често ажурира, ко је одржава и како треба да комуницирају са њом. Новозапослени увек имају гомилу питања, а ви их морате постављати изнова и изнова.
  • Решење: креирајте корисничку поруку која садржи сажет одговор на свако од ових често одговараних питања.

Примери из стварног света

  • Користите функцију МсгБок да бисте приказали поруку када се деси било који догађај: корисник затвара Екцел радну свеску, корисник штампа, нови лист се додаје у радну свеску итд.
  • Помоћу функције МсгБок прикажите поруку када корисник треба да испуни услов пре затварања Екцел радне свеске
  • Користите функцију ИнпутБок да бисте добили информације од корисника

Пример # 2: Дозволите кориснику да изврши другу процедуру

У ВБА Едитор-у одаберите Инсерт -> Нев Модуле

Напишите овај код у прозор Модула (не лепите!):

Суб УсерРепортКуери ()

Затамните унос корисника дуго

Затамни одговор као целобројни

УсерИнпут = вбИесНо

Одговор = МсгБок („Обрадити КСИЗ извештај?“, Кориснички унос)

Ако је одговор = вбДа, онда обрадите извештај

Енд Суб

Подпроцес Извештај ()

МсгБок („Хвала на обради извештаја КСИЗ.“)

Енд Суб

Сачувајте и вратите се на картицу Девелопер у програму Екцел и изаберите опцију „Буттон“. Кликните на ћелију и тастеру доделите макро УсерРепортКуери.

Сада кликните на дугме. Ова порука треба да прикаже:

Кликните „да“ или притисните Ентер.

Још једном, тада!

Имајте на уму да секундарна потпрограм, ПроцессРепорт, може бити било шта . Показаћу више могућности у примеру # 3. Али прво...

Како то ради?

Овај пример се надовезује на претходни пример и има доста нових елемената. Пређимо на нове ствари:

  • Дим УсерИнпут Ас Лонг: Дим је скраћеница од „дименсион“ и омогућава вам декларацију имена променљивих. У овом случају, УсерИнпут је име променљиве, а Лонг је тип података. Обично на енглеском, овај ред значи „Ево променљиве која се зове„ УсерИнпут “и то је тип променљиве Лонг.“
  • Затамни одговор као целобројно: проглашава другу променљиву која се зове „Одговор“, са типом података Интегер. Овде сазнајте више о типовима података.
  • УсерИнпут = вбИесНо: додељује вредност променљивој. У овом случају, вбИесНо, који приказује дугмад Иес и Но. Постоји много врста дугмади, сазнајте више овде.
  • Одговор = МсгБок („Обрадити КСИЗ извештај?“, УсерИнпут): додељује вредност променљиве Ансвер да буде функција МсгБок и променљива УсерИнпут. Да, променљива унутар променљиве.
  • Ако је одговор = вбДа, онда ПроцессРепорт: ово је „Иф статемент“, условна наредба, која нам омогућава да кажемо да ли је к тачно, онда учините и. У овом случају, ако је корисник одабрао „Да“, извршите потпрограм ПроцессРепорт.

Када бих ово могао да користим?

Ово се може користити на много, много начина. Вредност и свестраност ове функционалности је више дефинисана оним што ради секундарна потпрограм.

На пример, можда имате датотеку која се користи за генерисање 3 различита недељна извештаја. Ови извештаји су форматирани на драматично различите начине.

  • Проблем: Сваки пут када треба генерисати један од ових извештаја, корисник отвори датотеку и промени обликовање и графиконе; тако даље, и тако даље. Ова датотека се обимно уређује најмање 3 пута недељно и потребно је најмање 30 минута сваки пут када се уређује.
  • Решење: направите 1 дугме по типу извештаја, које аутоматски реформише потребне компоненте извештаја и генерише потребне графиконе.

Примери из стварног света

  • Направите дијалошки оквир за корисника да аутоматски попуњава одређене информације на више листова
  • Користите функцију ИнпутБок да бисте од корисника добили информације које се затим попуњавају на више листова

Пример # 3: Додајте бројеве у опсег помоћу петље за следећу

Јер су петље врло корисне ако требате да изводите понављајуће задатке на одређеном опсегу вредности - низима или опсезима ћелија. На једноставном енглеском, петља каже „за сваки к, уради и“.

У ВБА Едитор-у одаберите Инсерт -> Нев Модуле

Напишите овај код у прозор Модула (не лепите!):

Суб ЛоопЕкампле ()

Дим Кс Ас Интегер

За Кс = 1 до 100

Опсег ("А" & Кс) .Вредност = Кс

Следећи Кс

Енд Суб

Сачувајте и вратите се на картицу Девелопер у програму Екцел и изаберите дугме Мацрос. Покрените макро ЛоопЕкампле.

Ово би се требало догодити:

Итд, до 100. реда.

Како то ради?

  • Дим Кс Ас Интегер: проглашава променљиву Кс као тип података Интегер-а.
  • За Кс = 1 до 100: ово је почетак петље Фор. Једноставно речено, говори петљи да се понавља све док Кс = 100. Кс је бројач . Петља ће се извршавати све док Кс = 100, изврши се последњи пут, а затим заустави.
  • Опсег ("А" & Кс) .Валуе = Кс: ово проглашава опсег петље и шта треба ставити у тај опсег. Будући да је Кс = 1 у почетку, прва ћелија ће бити А1, у ком тренутку ће петља ставити Кс у ту ћелију.
  • Следећи Кс: ово говори петљи да се поново покрене

Када бих ово могао да користим?

Петља Фор-Нект једна је од најмоћнијих функционалности ВБА; постоје бројни потенцијални случајеви употребе. Ово је сложенији пример који би захтевао више слојева логике, али он преноси свет могућности у петљама Фор-Нект.

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

Треба да схватите која би требало да буде малопродајна цена сваког производа. Мислите да би то требало да буду трошкови састојака плус 20%, али ако је могуће и 1,2% испод тржишног просека. Петља Фор-Нект омогућила би вам да извршите ову врсту прорачуна.

Примери из стварног света

  • Користите петљу са угнежденом наредбом иф да бисте додали одређене вредности у засебни низ само ако испуњавају одређене услове
  • Извршите математичке прорачуне за сваку вредност у опсегу, нпр. Израчунајте додатне набоје и додајте их вредности
  • Прелистајте сваки знак у низу и извуците све бројеве
  • Насумичним одабиром броја вредности из низа

Закључак

Сад кад смо разговарали о пици и кифлама и ох-да, како написати ВБА код у Екцел табелама, направимо проверу учења. Погледајте да ли можете да одговорите на ова питања.

  • Шта је ВБА?
  • Како да се подесим да почнем да користим ВБА у програму Екцел?
  • Зашто и када бисте користили ВБА?
  • Које проблеме бих могао да решим помоћу ВБА?

Ако имате поштену идеју како да одговорите на ова питања, онда је ово успело.

Без обзира да ли сте повремени или напредни корисник, надам се да је овај водич пружио корисне информације о томе шта се може постићи са само мало кода у вашим Екцел прорачунским табелама.

Срећно кодирање!

Ресурси за учење

  • Екцел ВБА програмирање за лутке, Јохн Валкенбацх
  • Започните са ВБА, Мицрософт Доцументатион
  • Учење ВБА у програму Екцел, Линда

Мало о мени

Ја сам Цхлое Туцкер, уметница и програмерка у Портланду у држави Орегон. Као бивши васпитач, континуирано трагам за пресеком учења и поучавања или технологије и уметности. Позовите ме на Твиттер @_цхлоетуцкер и погледајте моју веб страницу на цхлое.дев.