V tomto návode sa dozvieme o funkcii Excel VBA
1) Čo je to Visual Basic v programe Excel?
2) Ako používať VBA v programe Excel?
3) Ako vytvoriť funkciu definovanú používateľom?
4) Ako písať makro?
Ako napísať kód VBA
Excel poskytuje používateľovi rozsiahlu zbierku hotových funkcií, viac ako dosť na to, aby uspokojil priemerného používateľa. Mnoho ďalších je možné pridať inštaláciou rôznych dostupných doplnkov. Väčšinu výpočtov je možné dosiahnuť pomocou toho, čo je k dispozícii, ale netrvá dlho a zistíte, že si želáte funkciu, ktorá plní konkrétnu úlohu, a v zozname nemôžete nájsť nič vhodné. Potrebujete UDF. UDF (User Defined Function) je jednoducho funkcia, ktorú si vytvoríte sami pomocou VBA. UDF sa často nazývajú „vlastné funkcie“. UDF môže zostať v module kódu pripojenom k zošitu. V takom prípade bude vždy k dispozícii, keď bude tento zošit otvorený. Prípadne si môžete vytvoriť svoj vlastný doplnok obsahujúci jednu alebo viac funkcií, ktoré môžete nainštalovať do Excelu rovnako ako komerčný doplnok. Prístup k UDF je možný aj prostredníctvom kódových modulov. Vývojári často vytvárajú UDF tak, aby pracovali výlučne v rámci kódu postupu VBA a používateľ si nikdy nie je vedomý ich existencie. Rovnako ako každá funkcia, UDF môže byť taký jednoduchý alebo komplexný, ako chcete. Začnime jednoducho…
Funkcia na výpočet plochy obdĺžnika
Áno, viem, že by ste to mohli urobiť vo svojej hlave! Koncept je veľmi jednoduchý, takže sa môžete sústrediť na techniku. Predpokladajme, že potrebujete funkciu na výpočet plochy obdĺžnika. Prezreli ste si zbierku funkcií Excelu, ale nenašla sa žiadna vhodná. Toto je výpočet, ktorý je potrebné vykonať:
OBLASŤ = DĹŽKA x ŠÍRKA
Otvorte nový zošit a potom otvorte editor jazyka Visual Basic (Nástroje> Makro> Editor jazyka Visual Basic alebo ALT+F11).
Budete potrebovať modul, do ktorého chcete napísať svoju funkciu, takže si vyberte Vložiť> Modul. Do prázdneho typu modulu: Funkčná oblasť a stlačte VSTÚPIŤ. Editor jazyka Visual Basic doplní riadok za vás a pridá riadok ukončovacej funkcie, ako keby ste vytvárali podprogram. Zatiaľ to vyzerá takto …
Funkčná oblasť () Koniec funkcie
Umiestnite kurzor medzi zátvorky za „Oblasť“. Ak ste sa niekedy pýtali, na čo slúžia zátvorky, čoskoro to zistíte! Špecifikujeme „argumenty“, ktoré bude naša funkcia potrebovať (napr argument je informácia potrebná na výpočet). Napíšte Dĺžka ako dvojnásobná, šírka ako dvojnásobná a kliknite na prázdny riadok pod ním. Všimnite si toho, že počas písania sa zobrazí rolovacie pole so zoznamom všetkých vecí, ktoré zodpovedajú tomu, čo píšete.
Táto funkcia sa nazýva Automatický zoznam členov. Ak sa nezobrazí, je buď vypnutý (zapnite ho o Nástroje> Možnosti> Editor) alebo ste urobili chybu pri písaní skôr. Je to veľmi užitočná kontrola vašej syntaxe. Nájdite požadovanú položku a dvojitým kliknutím ju vložte do kódu. Môžete to ignorovať a písať, ak chcete. Váš kód teraz vyzerá takto …
Funkčná oblasť (dĺžka ako dvojnásobok, šírka ako dvojnásobok) Koniec funkcie
Deklarovanie údajového typu argumentov nie je povinné, ale dáva zmysel. Mohli ste napísať Dĺžka šírka a nechal to tak, ale varovanie Excelu, aký typ údajov očakávať, pomôže vášmu kódu bežať rýchlejšie a zachytí chyby pri zadávaní. The dvojnásobok dátový typ odkazuje na číslo (ktoré môže byť veľmi veľké) a umožňuje zlomky. Teraz k samotnému výpočtu. V prázdnom riadku najskôr stlačte tlačidlo TAB kľúč na odsadenie kódu (uľahčenie čítania) a zadanie textu Plocha = dĺžka * šírka. Tu je kompletný kód…
Funkčná oblasť (dĺžka ako dvojnásobná, šírka ako dvojnásobná) plocha = dĺžka * šírka koncová funkcia
Všimnete si, že pri písaní sa objaví ďalšia z pomocných funkcií editora jazyka Visual Basic, Automatické rýchle informácie…
Tu to nie je relevantné. Jeho účelom je pomôcť vám napísať funkcie vo VBA tým, že vám povie, aké argumenty sú požadované. Svoju funkciu si môžete hneď vyskúšať. Prepnite do okna programu Excel a zadajte údaje o dĺžke a šírke do samostatných buniek. V tretej bunke zadajte svoju funkciu, ako keby to bola jedna zo vstavaných. V tomto prípade bunka A1 obsahuje dĺžku (17) a bunka B1 šírku (6,5). V C1 som písal = plocha (A1, B1) a nová funkcia vypočítala plochu (110,5)…
Niekedy môžu byť argumenty funkcie voliteľné. V tomto prípade by sme mohli vytvoriť Šírka argument nepovinný. Predpokladajme, že obdĺžnik je náhodou štvorec s rovnakou dĺžkou a šírkou. Aby sme ušetrili používateľovi zadávanie dvoch argumentov, mohli by sme ho nechať zadať iba dĺžku a funkciu použiť túto hodnotu dvakrát (t. J. Vynásobiť dĺžku x dĺžku). Aby funkcia vedela, kedy to dokáže, musíme zahrnúť znak IF vyhlásenie pomôcť mu rozhodnúť sa. Zmeňte kód tak, aby vyzeral takto …
Funkčná oblasť (dĺžka ako dvojnásobná, voliteľná šírka ako variant) Ak chýba (šírka), potom plocha = dĺžka * dĺžka iná oblasť = dĺžka * šírka koniec, ak funkcia koniec
Všimnite si toho, že typ údajov pre Šírku bol zmenený na Varianta povoliť nulové hodnoty. Funkcia teraz umožňuje užívateľovi zadať iba jeden argument, napr. = plocha (A1). Príkaz IF vo funkcii kontroluje, či bol zadaný argument Šírka, a podľa toho vypočítava …
Funkcia na výpočet spotreby paliva
Rád si kontrolujem spotrebu paliva svojho auta, takže keď si kúpim palivo, poznamenám si najazdené kilometre a koľko paliva potrebujem na naplnenie nádrže. Tu sa vo Veľkej Británii predáva palivo v litroch. Milometer automobilu (OK, je to teda počítadlo kilometrov) zaznamenáva vzdialenosť v míľach. A pretože som príliš starý a hlúpy na to, aby som sa zmenil, rozumiem iba MPG (míle na galón). Teraz, ak si myslíte, že je to všetko trochu smutné, čo s týmto. Keď prídem domov, otvorím Excel a zadám údaje do pracovného hárka, ktorý mi vypočíta MPG a zmapuje výkon auta. Výpočet je počet kilometrov, ktoré auto prešlo od posledného naplnenia, vydelený počtom galónov použitého paliva…
MPG = (MILES THIS FILL - MILES LAST FILL) / GALLONS OF FUEL
ale pretože palivo je v litroch a v galóne je 4,546 litra …
MPG = (MILES THIS FILL - MILES POSLED FILL) / LITRY PALIVA x 4,546
Takto som napísal funkciu …
Funkcia MPG (StartMiles ako celé číslo, FinishMiles ako celé číslo, litre ako samostatné) MPG = (FinishMiles - StartMiles) / litre * 4,546 Koncová funkcia
a takto to vyzerá v pracovnom liste …
Nie všetky funkcie vykonávajú matematické výpočty. Tu je jeden, ktorý poskytuje informácie…
Funkcia, ktorá dáva meno dňa
Často sa ma pýtajú, či existuje funkcia dátumu, ktorá udáva deň v týždni ako text (napr. Pondelok). Odpoveď je nie*, ale je celkom jednoduché ho vytvoriť. (*Dodatok: Povedal som nie? Pozrite sa na poznámku nižšie a pozrite sa na funkciu, na ktorú som zabudol!). Excel má funkciu WEEKDAY, ktorá vracia deň v týždni ako číslo od 1 do 7. Môžete si vybrať, ktorý deň je 1, ak sa vám nepáči predvolená hodnota (nedeľa). V nižšie uvedenom príklade funkcia vráti „5“, čo, ako náhodou viem, znamená „štvrtok“.
Ale nechcem vidieť číslo, chcem vidieť „štvrtok“. Výpočet by som mohol upraviť pridaním funkcie VLOOKUP, ktorá odkazovala na tabuľku niekde obsahujúcu zoznam čísel a zodpovedajúci zoznam mien dní. Alebo by som mohol mať celú vec samostatnú s viacerými vnorenými príkazmi IF. Príliš komplikované! Odpoveďou je vlastná funkcia…
Funkcia DayName (InputDate As Date) Dim DayNumber As Integer DayNumber = Weekday (InputDate, vbSunday) Select Case DayNumber Case 1 DayName = "Sunday" Case 2 DayName = "Monday" Case 3 DayName = "Tuesday" Case 4 DayName = "Wednesday" Prípad 5 DayName = "Štvrtok" Prípad 6 DayName = "Piatok" Prípad 7 DayName = "Sobota" Koniec Vyberte funkciu ukončenia
Svoju funkciu som nazval „DayName“ a je potrebný jediný argument, ktorý nazývam „InputDate“, čo (samozrejme) musí byť dátum. Funguje to takto…
- Prvý riadok funkcie deklaruje premennú, ktorú som nazval „DayNumber“, čo bude celé číslo (t.j. celé číslo).
- Nasledujúci riadok funkcie priradí danej premennej hodnotu pomocou funkcie WEEKDAY v Exceli. Hodnota bude číslo od 1 do 7. Napriek tomu, že predvolená hodnota je 1 = nedeľa, kvôli prehľadnosti som ju aj tak zahrnul.
- Nakoniec a Prípadová správa skúma hodnotu premennej a vráti príslušný kus textu.
Takto to vyzerá v pracovnom hárku …
Prístup k vašim vlastným funkciám
Ak je k zošitu pripojený modul kódu VBA, ktorý obsahuje vlastné funkcie, tieto funkcie je možné ľahko riešiť v rámci rovnakého zošita, ako je to znázornené na vyššie uvedených príkladoch. Názov funkcie používate, ako keby to bola jedna z vstavaných funkcií programu Excel.
Môžete tiež nájsť funkcie uvedené v Sprievodcovi funkciami (niekedy sa nazýva aj nástroj Prilepiť funkciu). Pomocou sprievodcu vložte funkciu normálnym spôsobom (Vložiť> Funkcia).
Posuňte sa nadol v zozname kategórií funkcií, ktoré chcete nájsť Definované užívateľom a jeho výberom zobrazíte zoznam dostupných UDF…
Môžete vidieť, že používateľom definovaným funkciám chýba iný popis ako nepotrebná správa „Žiadna pomoc nie je k dispozícii“, ale môžete pridať krátky popis …
Uistite sa, že ste v zošite, ktorý obsahuje funkcie. Ísť do Nástroje> Makro> Makrá. Tu neuvidíte svoje funkcie, ale Excel o nich vie! V Názov makra do poľa v hornej časti dialógového okna, zadajte názov funkcie a potom kliknite na dialógové okno možnosti tlačidlo. Ak je tlačidlo sivé, buď ste nesprávne napísali názov funkcie, alebo ste v nesprávnom zošite, alebo neexistuje! Otvorí sa ďalšie dialógové okno, do ktorého môžete zadať krátky popis funkcie. Kliknite OK uložte popis a (tu je mätúci kúsok) kliknite Zrušiť zatvorte dialógové okno Makro. Nezabudnite uložiť zošit obsahujúci funkciu. Keď nabudúce prejdete na sprievodcu funkciami, váš UDF bude mať popis …
Rovnako ako makrá, funkcie definované používateľom je možné použiť v akomkoľvek inom zošite, pokiaľ je zošit obsahujúci ich otvorené. Nie je však vhodné to robiť. Zadanie funkcie do iného zošita nie je jednoduché. K názvu funkcie musíte pridať názov hostiteľského zošita. Nie je to ťažké, ak sa spoliehate na Sprievodcu funkciami, ale je nešikovné písať ručne. Sprievodca funkciami zobrazuje úplné názvy všetkých UDF v iných zošitoch…
Ak otvoríte zošit, v ktorom ste funkciu používali, v čase, keď je zošit obsahujúci funkciu zatvorený, v bunke, v ktorej ste funkciu použili, sa zobrazí chybové hlásenie. Excel na to zabudol! Otvorte zošit hostiteľa funkcie, prepočítajte a všetko je opäť v poriadku. Našťastie existuje lepší spôsob.
Ak chcete napísať funkcie definované používateľom na použitie vo viac ako jednom zošite, najlepšou metódou je vytvoriť program Excel Doplnok. Zistite, ako to urobiť v tutoriále Vytvorenie doplnku programu Excel.
Dodatok
Naozaj by som to mal vedieť lepšie! Nikdy, nikdy nehovor nikdy! Keď som vám povedal, že neexistuje funkcia, ktorá by poskytovala názov dňa, teraz som si spomenul na tú, ktorá to dokáže. Pozrite sa na tento príklad…
Funkcia TEXT vracia hodnotu bunky ako text v konkrétnom číselnom formáte. Takže v príklade, ktorý som si mohol vybrať = TEXT (A1, "ddd") vrátiť „Štv“, = TEXT (A1, "mmmm") vrátiť „september“ atď. Pomocník programu Excel obsahuje niekoľko ďalších príkladov spôsobov, ako používať túto funkciu.
Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku.
Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na e -mailová stránka