Bevezetés

Ez egy ismertető a kód Excel-táblázatokba írásáról a Visual Basic for Applications (VBA) használatával.

Az Excel a Microsoft egyik legnépszerűbb terméke. 2016-ban a Microsoft vezérigazgatója azt mondta: "Gondoljon egy Excel nélküli világra. Ez számomra egyszerűen lehetetlen." Nos, talán a világ nem tud gondolkodni Excel nélkül.

1996-ban több mint 30 millió felhasználó volt a Microsoft Excel (forrás).
Becslések szerint ma mintegy 750 millió felhasználója van a Microsoft Excelnek. Ez valamivel több, mint Európa lakossága és 25-szer több felhasználó, mint 1996-ban volt.

Egy nagy boldog család vagyunk!

Ebben az oktatóanyagban megismerheti a VBA-t és azt, hogyan kell kódot írni egy Excel-táblázatba a Visual Basic használatával.

Előfeltételek

A bemutató megértéséhez nincs szüksége előzetes programozási tapasztalatokra. Szüksége lesz azonban:

A Microsoft Excel alapszintű és középszintű ismerete
Ha követni szeretné a cikk VBA-példáit, akkor hozzáférnie kell a Microsoft Excel programhoz, lehetőleg a legújabb verzióhoz (2019), de az Excel 2016 és az Excel 2013 rendben működik.
Hajlandóság új dolgok kipróbálására

Tanulási célok

A cikk során megtudhatja:

Mi a VBA
Miért használja a VBA-t?
Hogyan állítható be az Excel programban a VBA írásához
Hogyan lehet megoldani néhány valós problémát a VBA-val

Fontos fogalmak

Íme néhány fontos fogalom, amelyet ismernie kell az oktatóanyag teljes megértéséhez.

Objektumok : Az Excel objektumorientált, ami azt jelenti, hogy minden objektum - az Excel ablak, a munkafüzet, egy lap, egy diagram, egy cella. A VBA lehetővé teszi a felhasználók számára, hogy az Excel objektumokkal manipuláljanak és műveleteket hajtsanak végre.

Ha még nincs tapasztalata az objektumorientált programozással kapcsolatban, és ez egy teljesen új koncepció, szánjon egy percet arra, hogy ez elsüllyedjen!

Eljárások : az eljárás egy darab VBA kód, amelyet a Visual Basic Editorba írtak, és amely elvégzi a feladatot. Néha ezt makróként is emlegetik (a makrókról alább alább). Kétféle eljárás létezik:

Alprogramok: egy vagy több műveletet végrehajtó VBA utasítások csoportja
Funkciók: VBA utasítások csoportja, amely egy vagy több műveletet hajt végre, és egy vagy több értéket ad vissza

Megjegyzés: A szubrutinokon belül működhetnek funkciók. Majd később meglátod.

Makrók : Ha valamilyen időt töltött az Excel fejlettebb funkcióinak elsajátításával, valószínűleg találkozott a „makró” fogalmával. Az Excel felhasználói makrókat rögzíthetnek, amelyek felhasználói parancsokból / billentyűleütésekből / kattintásokból állnak, és villámgyorsan lejátszhatják őket az ismétlődő feladatok elvégzése érdekében. A rögzített makrók VBA kódot generálnak, amelyet aztán megvizsgálhat. Valójában nagyon szórakoztató egy egyszerű makrót rögzíteni, majd megnézni a VBA kódot.

Kérjük, ne feledje, hogy néha könnyebb és gyorsabb lehet makrót rögzíteni, mint VBA-eljárást kézzel kódolni.

Például talán a projektmenedzsmentben dolgozik. Hetente egyszer meg kell változtatnia egy nyers exportált jelentést a projektmenedzsment rendszeréből egy gyönyörűen formázott, tiszta jelentéssé a vezetés számára. A túlköltségvetésű projektek nevét vastag vörös szöveggel kell formáznia. A formázási változásokat makróként rögzítheti és futtathatja, amikor csak szükséges.

Mi a VBA?

A Visual Basic for Applications egy programozási nyelv, amelyet a Microsoft fejlesztett ki. A Microsoft Office programcsomag minden szoftvere külön költség nélkül, a VBA nyelvhez van kötve. A VBA lehetővé teszi a Microsoft Office felhasználók számára, hogy kis programokat hozzanak létre, amelyek a Microsoft Office szoftver programjain belül működnek.

Gondoljon a VBA-ra, mint egy pizzasütő egy étteremben. Az Excel az étterem. A konyhában szokásos kereskedelmi készülékek találhatók, például nagy hűtőszekrények, kályhák és szokásos olajsütők - ezek mind az Excel alapjellemzői.

De mi van, ha fatüzelésű pizzát szeretne készíteni? Ezt nem lehet megtenni egy szokásos kereskedelmi sütőben. A VBA a pizza kemence.

Yum.

Miért érdemes használni a VBA-t az Excelben?

Mert a fatüzelésű pizza a legjobb!

De most komolyan.

Sokan sok időt töltenek munkájuk részeként az Excel programban. Az Excelben eltöltött idő is másképp mozog. A körülményektől függően az Excelben töltött 10 perc örökkévalóságnak érezheti magát, ha nem tudja megtenni, amire szüksége van, vagy 10 óra nagyon gyorsan elmúlhat, ha minden remekül megy. Akkor kérdezd meg magadtól, miért töltök 10 órát az Excelben?

Néha elkerülhetetlenek azok a napok. De ha mindennap 8-10 órát tölt el az Excel programban ismétlődő feladatok elvégzésével, sok ugyanazon folyamat megismétlésével, a fájl más felhasználói után történő megtisztítással, vagy akár más fájlok frissítésével az Excel fájl módosítása után, egy VBA eljárás lehet a megoldás az Ön számára.

Fontolja meg a VBA használatát, ha:

Automatizálja az ismétlődő feladatokat
Készítsen egyszerű módszereket a felhasználók számára a táblázatokkal való interakcióra
Nagy mennyiségű adat kezelése

Felkészülés a VBA írására Excelben

Fejlesztő lap

A VBA megírásához hozzá kell adnia a Fejlesztő fület a szalaghoz, így így láthatja a szalagot.

A Fejlesztő lap hozzáadása a szalaghoz:

A Fájl fülön lépjen az Opciók> A szalag testreszabása elemre.
A A szalag testreszabása és a Fő lapok alatt jelölje be a Fejlesztő jelölőnégyzetet.

A lap megjelenítése után a Fejlesztő fül látható marad, hacsak nem törli a jelölőnégyzet törlését, vagy újratelepítenie kell az Excel programot. További információt a Microsoft súgó dokumentációjában talál.

VBA szerkesztő

Keresse meg a Fejlesztő lapot, és kattintson a Visual Basic gombra. Új ablak jelenik meg - ez a Visual Basic Editor. Ennek az oktatóanyagnak a használatához csak ismernie kell a Project Explorer és a Tulajdonságok paneleket.

Excel VBA példák

Először hozzunk létre egy fájlt, amiben játszhatunk.

Nyisson meg egy új Excel fájlt
Mentse makróképes munkafüzetként (. Xlsm)
Válassza a Fejlesztő fület
Nyissa meg a VBA szerkesztőt

Nézzünk körbe néhány egyszerű példával, hogy a Visual Basic segítségével kódot írhasson táblázatba.

1. példa: Üzenet megjelenítése, amikor a felhasználók megnyitják az Excel munkafüzetet

A VBA szerkesztőben válassza a Beszúrás -> Új modul lehetőséget

Írja be ezt a kódot a Modul ablakba (ne illessze be!):

Sub Auto_Open ()

MsgBox ("Üdvözöljük az XYZ munkafüzetben.")

End Sub

Mentse el, zárja be a munkafüzetet, és nyissa meg újra a munkafüzetet. Ennek a párbeszédpanelnek meg kell jelennie.

Ta da!

Hogy csinálja ezt?

Attól függően, hogy ismeri a programozást, van néhány tippje. Nem különösebben összetett, de elég sok minden történik:

Sub (rövidítve: „Subroutine”): emlékezzen a kezdetektől fogva „egy vagy több műveletet végrehajtó VBA utasítások csoportjára”.
Auto_Open: ez a konkrét alprogram. Az Excel fájl megnyitásakor automatikusan futtatja a kódot - ez az esemény váltja ki az eljárást. Az Auto_Open csak akkor fut, ha a munkafüzet manuálisan megnyílik; nem fog futni, ha a munkafüzetet egy másik munkafüzet kódja nyitja meg (a Workbook_Open ezt megteszi, tudjon meg többet a kettő közötti különbségről).
Alapértelmezés szerint egy alprogram hozzáférése nyilvános. Ez azt jelenti, hogy bármely más modul használhatja ezt az alprogramot. Az oktatóanyag összes példája nyilvános szubrutin lesz. Ha szükséges, nyilváníthatja a szubrutinokat privátként. Erre szükség lehet bizonyos helyzetekben. Tudjon meg többet az alprogram hozzáférési módosítóiról.
msgBox: ez egy függvény - egy VBA utasítások csoportja, amely egy vagy több műveletet hajt végre, és visszaad egy értéket. A visszaadott érték a „Welcome to the XYZ Workbook” üzenet.

Röviden: ez egy egyszerű szubrutin, amely tartalmaz egy függvényt.

Mikor használhatnám ezt?

Lehet, hogy van egy nagyon fontos fájlja, amelyhez ritkán (mondjuk negyedévente egyszer) fér hozzá, de naponta automatikusan frissíti egy másik VBA eljárás. Amikor elérik, sok ember dolgozik a részlegeken, a vállalat egész területén.

Probléma: Legtöbbször, amikor a felhasználók hozzáférnek a fájlhoz, zavarban vannak a fájl céljától (miért létezik), arról, hogy milyen gyakran frissítik azt, ki tartja karban és hogyan kell vele együttműködni. Az új alkalmazottaknak mindig rengeteg kérdése van, és ezeket a kérdéseket újra és újra meg kell adnia.
Megoldás: Hozzon létre egy felhasználói üzenetet, amely tömör választ tartalmaz a gyakran ismételt kérdések mindegyikére.

Példák a valós világra

Az MsgBox funkció segítségével üzenetet jeleníthet meg, ha bármilyen esemény történik: a felhasználó bezár egy Excel munkafüzetet, a felhasználó kinyomtatja, új lapot ad a munkafüzethez stb.
Az MsgBox funkció segítségével üzenetet jeleníthet meg, amikor a felhasználónak teljesítenie kell egy feltételt, mielőtt bezárná az Excel munkafüzetet
Az InputBox funkció segítségével információkat szerezhet a felhasználótól

2. példa: Engedélyezze a felhasználónak egy másik eljárás végrehajtását

A VBA szerkesztőben válassza a Beszúrás -> Új modul lehetőséget

Írja be ezt a kódot a Modul ablakba (ne illessze be!):

Sub UserReportQuery ()

Dim UserInput Long

Gyengén válaszoljon egész számként

UserInput = vbYesNo

Válasz = MsgBox ("Feldolgozza az XYZ jelentést?", UserInput)

Ha Válasz = vbIgen, akkor a ProcessReport

End Sub

Sub ProcessReport ()

MsgBox ("Köszönjük, hogy feldolgozta az XYZ jelentést.")

End Sub

Mentse és lépjen vissza az Excel Fejlesztő fülére, és válassza a „Gomb” lehetőséget. Kattintson egy cellára, és rendelje hozzá a UserReportQuery makrót a gombhoz.

Most kattintson a gombra. Ennek az üzenetnek a következőket kell tartalmaznia:

Kattintson az „igen” gombra, vagy nyomja meg az Enter billentyűt.

Még egyszer, tada!

Felhívjuk figyelmét, hogy a másodlagos szubrutin, a ProcessReport, bármi lehet . Több lehetőséget mutatok be a 3. példában. De először...

Hogy csinálja ezt?

Ez a példa az előző példára épít, és jó néhány új elemet tartalmaz. Nézzük át az új dolgokat:

Dim UserInput As Long: A Dim a „dimenzió” rövidítése, és lehetővé teszi a változó nevek deklarálását. Ebben az esetben a UserInput a változó neve, a Long pedig az adattípus. Egyszerű angol nyelven ez a sor azt jelenti: "Itt van egy" UserInput "nevű változó, és ez egy hosszú változó típus."
Dim Answer As Integer: deklarál egy másik „Answer” nevű változót egész szám adattípussal. Tudjon meg többet az adattípusokról itt.
UserInput = vbYesNo: értéket rendel a változóhoz. Ebben az esetben a vbYesNo, amely az Igen és a Nem gombokat jeleníti meg. Vannak sok gombtípusa, további információk itt.
Answer = MsgBox („Feldolgozzuk az XYZ jelentést?”, UserInput): a Answer változó értékét MsgBox függvénynek és UserInput változónak rendeli hozzá. Igen, egy változó egy változón belül.
Ha a Válasz = vbIgen, akkor a ProcessReport: ez egy „If utasítás”, egy feltételes utasítás, amely lehetővé teszi számunkra, hogy megmondjuk, ha x igaz, akkor tegyük meg az y-t. Ebben az esetben, ha a felhasználó az „Igen” lehetőséget választotta, hajtsa végre a ProcessReport alprogramot.

Mikor használhatnám ezt?

Ezt sok-sok módon fel lehetne használni. Ennek a funkcionalitásnak az értékét és sokoldalúságát jobban meghatározza az, amit a másodlagos szubrutin végez.

Például lehet, hogy van egy fájlja, amelyet 3 különböző heti jelentés generálására használnak. Ezeket a jelentéseket drámai módon formázzák.

Probléma: Minden alkalommal, amikor e jelentések egyikét elő kell állítani, a felhasználó megnyitja a fájlt, és megváltoztatja a formázást és a diagramokat; így tovább és így tovább. Ezt a fájlt hetente legalább háromszor széles körben szerkesztik, és minden egyes szerkesztéskor legalább 30 percet vesz igénybe.
Megoldás: Jelentéstípusonként hozzon létre egy gombot, amely automatikusan átformálja a jelentések szükséges összetevőit, és létrehozza a szükséges diagramokat.

Példák a valós világra

Hozzon létre egy párbeszédpanelt a felhasználó számára, hogy bizonyos információkat automatikusan feltöltsön több lapra
Az InputBox funkció segítségével információkat szerezhet a felhasználótól, amelyet ezután több lapra tölt fel

3. példa: Számok hozzáadása egy tartományhoz egy For-Next hurokkal

A ciklusok nagyon hasznosak, ha ismétlődő feladatokat kell végrehajtania egy meghatározott értéktartományon - tömbökön vagy cellatartományokon. Egyszerű angol nyelven egy hurok mondja: „minden x-ért csináld az y-t”.

A VBA szerkesztőben válassza a Beszúrás -> Új modul lehetőséget

Írja be ezt a kódot a Modul ablakba (ne illessze be!):

AlhurokPélda ()

Dim X egész számként

X = 1-től 100-ig

Tartomány ("A" és X). Érték = X

Következő X

End Sub

Mentse és lépjen vissza az Excel Fejlesztő fülére, és válassza a Makrók gombot. Futtassa a LoopExample makrót.

Ennek meg kell történnie:

Stb., A 100. sorig.

Hogy csinálja ezt?

Dim X As Integer: az X változót egész szám adattípusként deklarálja.
X = 1 és 100 között: ez a For ciklus kezdete. Egyszerűen fogalmazva, azt mondja, hogy a hurok folyamatosan ismétlődő, amíg az X = 100 X a számláló . A hurok X = 100-ig fog végrehajtani, utoljára végrehajtja, majd leáll.
Tartomány ("A" és X). Érték = X: ez deklarálja a hurok tartományát és mit kell ebbe a tartományba tenni. Mivel X = 1 kezdetben, az első cella A1 lesz, ekkor a hurok X-et rak be abba a cellába.
Következő X: ez azt mondja a hurok számára, hogy fusson újra

Mikor használhatnám ezt?

A For-Next ciklus a VBA egyik legerősebb funkciója; számos lehetséges felhasználási eset létezik. Ez egy összetettebb példa, amely több logikai réteget igényelne, de a For-Next ciklusokban kommunikálja a lehetőségek világát.

Lehet, hogy van egy listája a pékségében az A. oszlopban eladott összes termékről, a B. oszlop terméktípusáról (sütemények, fánk vagy muffin), az összetevők költségéről a C. oszlopban és az egyes terméktípusok piaci átlagköltségeiről. egy másik lap.

Meg kell találnia, hogy mi legyen az egyes termékek kiskereskedelmi ára. Arra gondol, hogy az összetevők költsége plusz 20%, de ha lehetséges, a piaci átlag alatt 1,2% is legyen. A For-Next ciklus lehetővé teszi az ilyen típusú számítások elvégzését.

Példák a valós világra

Használjon beágyazott if utasítással rendelkező ciklust, ha csak külön feltételekhez ad hozzá konkrét értékeket egy külön tömbhöz
Végezzen matematikai számításokat egy tartomány minden egyes értékére, pl. Számoljon ki további díjakat, és adja hozzá őket az értékhez
Hurkolja végig a karakterlánc minden karakterét, és vonja ki az összes számot
Véletlenszerűen válasszon ki egy értéket egy tömbből

Következtetés

Most, hogy beszéltünk a pizzáról és a muffinról, és ja, igen, hogyan kell VBA kódot írni Excel táblázatokba, végezzünk el egy tanulási ellenőrzést. Nézze meg, tud-e válaszolni ezekre a kérdésekre.

Mi a VBA?
Hogyan állíthatom be a VBA használatának megkezdését az Excelben?
Miért és mikor használná a VBA-t?
Milyen problémákat tudnék megoldani a VBA-val?

Ha van egy jó elképzelése arról, hogyan válaszolhatna ezekre a kérdésekre, akkor ez sikeres volt.

Akár alkalmi, akár energiafelhasználó vagy, remélem, hogy ez az oktatóanyag hasznos információkat nyújtott arról, hogy mit lehet elérni csak egy kis kóddal az Excel táblázatokban.

Boldog kódolást!

Tanulási források

Excel VBA programozás bábuknak, John Walkenbach
Kezdő lépések a VBA, a Microsoft Documentation használatával
A VBA tanulása az Excelben, Lynda

Kicsit rólam

Chloe Tucker vagyok, művész és fejlesztő Portlandben, Oregonban. Volt oktatóként folyamatosan keresem a tanulás és a tanítás, vagy a technológia és a művészet metszéspontját. Lépjen kapcsolatba velem a Twitteren @_chloetucker, és nézze meg a webhelyemet a chloe.dev címen.