Alapvetően VBA-t főleg az Office-programok manipulálására, adatok mozgatására, formázására, levelek küldésére, Access-adatbázisok frissítésére stb. használunk, ha ki kell nyúlnunk mondjuk Excelen kívülre, akkor ott azért beleütközhetünk korlátokba, ezért általában bizonyos, Officeon kívüli dolgok megvalósításához inkább már más megoldások szoktak képbe jönni. Ettől függetlenül simán megtörténhet, hogy egy makró részeként meg akarunk nyitni egy weblapot, majd a weblapon be akarunk pötyögni valamit és esetleg egy gombra is rá akarunk nyomni. A weblap megnyitásról korábban már volt szó itt a blogon, úgyhogy a mai posztban az egér kurzorját fogjuk megmozdítani és kattintunk is vele kettőt, felhasználva az erre a célra leggyakrabban használt standard módszert.

Csak azért, hogy legyen valamilyen keretünk, adott a következő gomb:

Megnyomása után szeretnénk, ha az egérkurzorunk elmozdulna lefelé és duplán belekattintana az F14-es cellába. Ennek érdekében rendeljünk hozzá egy makrót a gombhoz, majd az ALT+F11 lenyomásával lépjünk be VBA-ba. Mielőtt a konkrét mozgatós és kattintós szubrutinba ugranánk, deklarálunk kell egy funkciót a kurzorunk pozíciójának beállításához és az egérgombok eseményeihez egy szubrutint. Ez alapvetően standard, tehát be is másolom ide:

Public Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)

Az Excelnek ugyebár nincs beépített funkciója a kurzor pozíciójának meghatározásához vagy beállításához, de egy API segítségével hozzáférhetünk a user32.dll-ben tárolt SetCursorPos funkcióhoz (a GetCursosPos segítségével a jelenlegi pozíciót kaphatjuk vissza), ami ugyebár segíteni fog beállítani a kurzor helyét. ByVal X és ByVal Y a konkrét koordináták változói. A második deklaráció szintén a user32.dll-ben tárolt mouse_event szubrutint fogja megszólítani, ami segít az egér lehetőségeit használni. A dwFlags paraméter a konkrét egéreseményeket jelenti, ide kerülhet például egy MOUSEEVENTF_LEFTDOWN esemény, ami egy bal egérgomb lenyomását jelenti. A dx az egér kurzorának abszolút helyzetét jelenti az X tengely mentén, a dy pedig az y tengely mentén, dwExtraInfo pedig esetlegesen szükséges extra információk begyűjtését teszi lehetőve a dll-ből. Ezután pedig jöjjön a két, bal egérgomb lenyomásához kapcsolódó esemény deklarálása, ez a MOUSEEVENTF_LEFTUP és MOUSEEVENTF_LEFTDOWN.

Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4

A kérdés biztos felmerül bennetek, hogy mi a fene az a &H2 vagy éppen &H4. Amikor egy szám előtt &H áll, az azt jelenti, hogy az utána következő karakter egy hexadecimális szám, tehát &H2 konkrétan a kettes szám.

Ha megvannak ezek a deklarációk, akkor jöhet a konkrét mozgató szubrutin:

Private Sub Mozgatas()
SetCursorPos 400, 180
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
End Sub

A SetCursorPos funkció értelemszerűen X és Y tengelyen megadott pixelszám szerint elmozdítja a kurzort, példánk esetében tehát a kurzor új koordinátája a képernyőn X: 400 és Y: 180. A mouse_event funkció első paramétere a konkrét egéresemény, jelen esetben a bal egérgomb lenyomása, második paramétere az x, harmadik paramétere az y koordináta, de mi most csak kattintani szeretnénk, ezért ezek nullák. A negyedik paraméter a dwData, itt csak akkor vesz fel értéket, ha az egér görgőjével akarunk valamit csinálni, tehát az első paraméter a MOUSEEVENTF_WHEEL lenne. Az utolsó nulla pedig a dwExtraInfo, nekünk erre nincs szükségünk.

És azért dupláztuk meg a sorokat, mert lenyomjuk-felengedjük-lenyomjuk-felengedjük, tehát duplakattintást szimulálunk.

Simán lehet, hogy a következő két kis szösszenet unalomig ismételt ujjgyakorlat lehet egyes Olvasóknak, de egyrészt ismétlés a tudás anyja, másrészt a múlt heti tapasztalataim alapján hajlamos az ember magától értetődő dolgokat roppant gyorsan elfelejteni, ha nem fut időnként bele egy kapcsolódó kérdésbe. Adott egy kis példatábla:

Direkt láthatóvá tettem a formulákat, hogy a kérdés is egyértelmű legyen - hangsúlyozva, hogy egyébként több tucat sheetet és több száz kalkulált cellát tartalmazó Excel munkafüzetnél is simán működik a következő jótanács. Ha valamilyen oknál fogva valamelyik képletünkben le kell cserélnünk egy range nevét, egy konstans értéket vagy egy cellahivatkozást, akkor semmiképpen ne álljunk neki egyesével másolgatni, hanem szimplán nyomjunk egy CTRL+H-t és hívjuk elő a Replace funkciót.

A mostani példában az egyik rátacellát akarjuk lecserélni a másikra, így a Replace ablakban megadhatjuk, hogy pontosan mit keresünk a munkafüzet-munkalap formuláiban, majd azt egy gombnyomással le is tudjuk cserélni. Ne felejtsük el, totálisan egyértelműnek tűnik, de simán meg lehet róla feledkezni.

A másik kérdés a Ribbonunk Data füle alól elérhető Data Tools szekció Data Validation funkciójára vonatkozik, azt próbáljuk ugyanis megvalósítani, hogy többféle feltételnek megfelelő adatot tudjunk az adatvalidációt tartalmazó cellába befogadni. Hogy értsük pontosan miről is van szó, álljon itt ez a kis összefoglaló kép:

Tehát azt szeretném elérni, hogy a Data Validationt tartalmazó cellába 0 és 1000 közötti számok vagy a második feltétel-oszlopban megadott szövegek kerülhessenek csak, minden más esetben tagadja meg az Excel az adatbevitelt. Fontos tudni, hogy ez csak egyetlen példa, akár három feltétellel, sokkal hosszabb listákkal és komplexebb szabályokkal is felépíthetünk adatvalidációt. Szóval most lépjünk be a Data Validation menübe:

Az Allow legördülő menüben válasszuk a Custom opciót, majd a Formula sorba vigyük be a szükséges formulát:

=OR(AND(C10>$F$13,C10<$F$14),COUNTIF($G$13:$G$15,C10))

Azt ugye már megtanultuk, hogy az OR funkció segítségével azt tudjuk megnézni, hogy a paramétereiként megadott argumentumok bármelyike igaz-e és ennek megfelelően ad vissza IGAZ-HAMIS értéket. Az AND hasonló, csak ott a megadott argumentumok mindegyikének teljesülése esetén jön csak vissza IGAZ érték. A COUNTIF pedig megszámolja, hogy az első paramétereként megadott tartományban hány olyan érték van, amelyik megfelel a második paramétereként megadott feltételnek. És ha ezt így együtt nézzük, látjuk, hogy a függvényünk csak abban az egy esetben adhat vissza TRUE értéket, ha a számunk 0 és 1000 között van vagy a 2. feltételben szereplő szövegek közül valamelyiket tartalmazza a bevitt adat. Mert ha nem így van, akkor ez történik:

Az Excel egyik legnagyobb fegyverét a benne rejlő szabadság és a funkciók sokasága adja, már itt a blogon is többször néztük meg, hogy egyetlen problémára hány különféle megoldást lehet adni - és nincs ez másként a chartok esetén sem, ugyanis az alapból beépített diagramokhoz képest jóval többet érhetünk el egy kis kreativitással, abból pedig nem szenved hiányt az a sok ezer Excel-fanatikus bütykölő, akik újabbnál-újabb megoldásokkal állnak elő. Egy ilyen újdonság az ún. joyplot-chart, amelyet nem túl könnyű előállítani és kizárólag egyetlen típusú adathalmaz esetén van értelme használni, ettől függetlenül a következő posztban ennek a diagramnak az elkészítésén fogunk végigszaladni.

Adott a következő táblázat, amely három, különböző méretű bolt vevőszámát mutatja adott időpillanatokban:

Erre az Insert ribbonfül Charts szekciójából szúrjunk be egy kétdimenziós Area chartot:

Már ez is megfelelő ábrázolás, célunknak megfelelő vizualizáció lenne, de a joyplot lényege még csak most következik. Ahány boltunk (vagy bármilyen egységünk) van, szúrjunk be annyi "különbség-sort", azaz azokat a sorokat, amelyek el fogják választani egymástól az area chartunk területi egységeit:

Figyeljünk rá, hogy a pontos elválasztás érdekében az értékek minden időpontnál ugyanazok legyenek és "különbség-soronként" növekedjen az értékük.

Ha ezzel megvagyunk, akkor a Chart Tools ribbonszekció Design füle alól bökjünk a Select Data lehetőségre, majd az Add gomb lenyomásával az egyes "különbség-sorokat" adjuk seriesként hozzá a chartunkhoz, valahogy így:

Figyeljünk rá oda, hogy a legkisebb "különbség-sor" legyen az első boltunk (egységünk) után, és felváltva jöjjön a többi. A sorrendet a Legend Entries ablakocska jobb felső sarkában található nyilakkal tudjuk menedzselni.

Innentől kezdve már látható, hogy mit is kell tennünk, szépen egyesével kattintsunk a "különbség-soraink" területeire a chartban, majd a Format ribbonfül Shape Styles szekciójában a Shape Fill menüpontból állítsuk a kitöltésüket fehérre (vagy amilyen a chartunk alap háttérszíne):

Ezután már csak meg kell formáznunk kicsit a diagramot és elő is állt valami ilyesmi:

Ahogy látható, a joyplot-chartnak akkor van igazán létjogosultsága, ha több különféle egység bizonyos időpillanataiban mutatott eredményét, darabszámát stb. szeretnénk különlegesebb módon megjeleníteni.

Az Excel és a PowerPoint között nem csak az a közös, hogy egy programcsomagba tartoznak és egy cég fejlesztései, hanem az is, hogy mindennapos gyakorlat Excel adathalmazok valamilyen formában történő bemutatása diákon keresztül, így a mai posztban Excel-adatokat fogunk átbűvészkedni PowerPointba egy roppant egyszerű VBA-kód segítségével, ami jó alapot fog szolgáltatni későbbi, hasonló Excel-PowerPoint kapcsolatos posztokhoz. 

Szóval adott a következő egyszerű kis tábla:

Ezt és mondjuk tucatnyi másik társát szeretnénk áthegesztni egy PowerPoint diasorozatra automatikusan, hiszen akár rendszeres időközönként is várhatnak tőlünk ilyen jellegű tömény adathalmazt egy prezentáció sokadik diáján. 

ALT+F11 lenyomásával lépjünk be VBA-editorba, majd hozzuk össze a kis kódunkat, amit aztán gombhoz rendelhetünk és a megfelelő táblák elkészítése után automatikusan legenerálhatjuk a templatünket is. A kódunk szokás szerint a változók deklarálásával kezdődik:

Sub ExcelbolPPT()

Dim rng As Range

Dim PP As Object

Dim prezi As Object

Dim dia As Object

Dim kep As Object

Az rng nevű változónk maga a dashboardunk tartománya lesz az Excel munkalapon, a PP-t magának a PowerPoint applikációnak hozzuk létre, a prezi nevű változót egy prezentációnak a PowerPointon belül, a dia nevűt ezen prezentáció egy diájának, a kep nevűt pedig a dashboardunk képformátumának, amikor már a dián leszünk. 

A következő szegmensünkben egyrészt kicsit optimalizálni fogjuk a kódot (vagyis inkább gyorsítani) valamint a változókat feltölteni:

Application.ScreenUpdating = False

Set rng = ThisWorkbook.ActiveSheet.Range("A1:H7")

Set PP = CreateObject(class:="PowerPoint.Application")

Set prezi = PP.Presentations.Add

Set dia = prezi.Slides.Add(1, 11)

Azt már tudjuk, hogy az Application.ScreenUpdating kikapcsolásával nem fogjuk látni, mit csinál a makró, ergó egy kicsit gyorsabb lesz. Az rng változónk értelemszerűen felveszi a makrónknak is helyt adó munkafüzet aktív sheetjének megfelelő tartományát, a PP, objektum típusú változó pedig egy PowerPoint elindítása lesz a CreateObject funkció segítségével - a class paraméter fogja megadni, hogy milyen applikációról is beszélünk. A prezi nevű változó az elindított PowerPoint applikáción belül fog létrehozni egy új prezentációt, míg a dia változó a prezi változóban lévő prezentációhoz fog felvenni egy diát (a két paraméter közül az egyes a dia indexét jelenti, azaz ez lesz az első számú dia, a 11-es pedig az adott dia kinézetének paramétere, jelen esetben azt mutatja, hogy csak címsorunk van).

Ezután jön a kódunk harmadik része, az érdemi másolás és beillesztés:

rng.Copy

dia.Shapes.PasteSpecial DataType:=2

Set kep = dia.Shapes(dia.Shapes.Count)

kep.Left = 300

kep.Top = 152

PP.Visible = True

PP.Activate

End Sub

Az nem kérdés tehát, hogy az rng változóban lévő Excel-táblát fogjuk a vágólapra helyezni (másolni), majd utána ezt beillesztjük a PowerPoint-diánkra a Shapes.PasteSpecial metódus segítségével, amelynek DataType paraméterével határozhatjuk meg, hogy milyen formában szeretnénk is beilleszteni (képként, értékként stb.).

Ezután jön a feladat legkevésbé szeretett része, a pixelenkénti hegesztgetés, hogy hogyan is akarjuk látni a Dashboardunkat a dián. Elsőként a kep változónkba betöltjük a legutóbb beillesztett Shape-t (látjuk, hogy először megszámoljuk, hány Shape van egyáltalán a dián, hiszen ha van 3 darab, akkor minket a harmadik érdekel, mint legutoljára beillesztett), majd ezt fogjuk tologatni a dián mondjuk balról és fentről. Ha szépen meghatároztuk az elhelyezkedést, akkor már csak meg kell jelenítenünk a PowerPointot és azt kell aktív ablakba hoznunk, hogy lássuk az eredményünket.

Volt pár komolyabb projekt és változás, ami miatt sajnos nem tudtam annyit beletenni a blogba (konkrétan semmit), mint szerettem volna, nem tudtam levelekre sem válaszolni, de most úgy tűnik, hogy ismét folytatódhat az Office-bütykölgetés. A visszatérés alkalmából két olyan egyszerű kis Pivot-táblás tanáccsal kezdenék, ami az elmúlt üresjárati időszak során nekem időt takarított meg különféle feladatok során.

Az első megismeréséhez nézzük a következő adathalmazt:

Csináljunk belőle az Insert ribbonfül alatt, a Tables szekcióban található PivotTable funkcióval egy Pivot-táblát:

Ide jutottunk:

Ahogy látható, szűrőként használjuk a Régió oszlopban szereplő három értéket:

Ennél a háromnál talán még nem is ennyire látható a funkció hasznossága, de jóval több filter esetén mindenképpen hasznos annak a funkciónak az ismerete, amellyel szűrőfeltételként külön-külön sheetre szét tudjuk dobálni a Pivot-táblánkat. Ehhez nem kell mást tenni, mint a Pivot-táblán állva a megjelenő Analyze ribbonfül alatt kattintsunk a PivotTable szekcióban található Options menüre, majd azon belül a Show Report Filter Pages lehetőségre:

És íme, meg is van az eredmény:

Nagyon fontos tudnivaló, hogy ilyenkor az adat nem változik, tehát az összes sheeten elérhető az összes régió adata!

A következő Pivot-tanács is egy egyszerű adattáblával kezdődik:

Csinálunk belőle egy teljesen normális Pivot-táblát:

Ismét igaz, hogy egy jóval komplexebb táblánál sokkal jobban látható lenne a bemutatásra kerülő funkció hasznossága, de a kérdés az, hogy hogyan adhatjuk meg a Pivot-táblán belül a a teljes darabszámra jutó sérült darabok százalékos arányát? A Pivot-táblán állva az Analyze ribbonfül alatt található Calculations szekcióban válasszuk ki a Fields, Items & Sets menüt, majd a Calculated Field lehetőséget:

Ezután már csak az új mező (oszlop) nevét és a kalkuláció alapját kell megadnunk:

És meg is van a kalkulált oszlopunk, amelyre aztán további kalkulációkat építhetünk fel.

Exceles TextBoxokról korábban már volt szó itt a blogon, akinek van kedve, nyugodtan szemezgessen:

TextBox témák

Mindenesetre a mai rövid, "gyorssegély" posztban ezekből szemezgetve készítek el egy apró kódsort (vagyis kettőt), amely segítségével Exceles TextBoxunk tartalmát fogjuk elmenteni egy fájlba, például egy .txt kiterjesztésű szövegfájlba illetve onnan vissza is fogunk olvasni szövegeket. 

Adott tehát a következő TextBox:

Nyugodtan pakolhatunk mellé egy gombot, amelynek megnyomása után kerül át a szövegünk a fájlunkba, de akár magára a TextBoxra való kattintással is indíthatunk egy ilyen eseményt:

Jelen példámban utóbbinál maradunk, azaz a TextBox1_Click eseményhez rendeljük hozzá VBA-editorban a következő aprócska kódot:

Látható, hogy elsőként a "szovegem" nevet viselő változónk felveszi az aktív munkalapunkon található, TextBox 1 nevet viselő textboxunk tartalmát, majd megnyitjuk a C meghajtónkon található x.txt nevű fájlt, amelyhez az Open utasítást fogjuk használni, a megfelelő szintaxis alapján, amelyben a For után következő Append azt jelzi majd, hogy egy már meglévő fájl szövegéhez akarunk hozzápakolni (ellenkező esetben Output jön a For után), az As után következő #1 pedig a paraméterként elvárt fájl sorszáma.

A Print utasítás tehát ebbe a megnyitott fájlba rakja bele a "szovegem" változó tartalmát, tehát a TextBoxunk szövegét, majd Close utasítással be is zárjuk. Értelemszerűen ez nem csak .txt kiterjesztésű fájl esetén működik, használhatjuk Word-dokumentumnál is.

Ha a másik irányba szeretnénk kommunikálni, az a fenti kód alapján egyébként könnyen összerakható, de kicsit megkeverjük tanulási célzattal - szóval a lényeg, hogy egy .txt kiterjesztésű fájlból akarunk szöveget a TextBoxunkba varázsolni. 

Elsőként hozzunk létre három változót:

Sub txttoTextBox()

Dim file As String, ff As Integer, szoveg As String

file = "C:\x.txt" 

szoveg = Space(FileLen(file))

ff = FreeFile

Mit is csinálunk? A "file" névre hallgató változónk fogja a fájlunkhoz tartalmazni az elérési útvonalat, a "szoveg" névre hallgató változó pedig annyi szóközt fog felvenni, ahány byte méretű a megadott fájlunk (ezt adja meg a FileLen utasítás), nagyjából előkészítve a terepet a "betöltésre", hiszen ennyi karaktert fogunk ide betolni. Az ff = FreeFile pedig nem mást fog megadni, mint amit az előbb #1-ként, fájl sorszámként hivatkoztunk meg a másik kódban, ez ugyanis megadja a a következő szabad fájl-sorszámot nekünk. 

A kód további részében három utasítást fogunk használni:

Open file For Binary As #ff

Get #ff, ,szoveg

Close #ff
ActiveSheet.TextBoxes("TextBox 1").Text = szovegEnd Sub

Az Open utasítás szintaxisát követve most a megadott fájlunkat bináris módban fogjuk megnyitni a paraméterként elvárt sorszámot az ff változóból kinyerve. A Get utasítás az egész kód lelke, ami a megadott sorszámú fájlból (ezt hozza az ff változó) olvassa be az adatokat a harmadik paraméterként megadott változóba (ez a szoveg változó). Vegyük észre, hogy a két vessző között nincs semmi, oda kerülne ugyanis az opcionális második paraméter, amellyel megadhatnánk, hogy honnan kezdje el a fájlból kiszedni az adatokat. 

A Close utasítást már ismerjük fentebbről, ezzel zárjuk be a fájlt, az utolsó sorunk pedig az aktív munkalapunk TextBox 1 névre hallgató textboxába tölti be "szoveg" változónk tartalmát. 

Az év ezen időszakában (még akkor is ha az utolsó pillanatokig megy a munka) általában mindenki kienged egy kicsit, lazít, igyekszik töltődni a következő évre, úgyhogy én sem fogok komoly témákat feszegetni itt a blogon, a következő posztban szórakozni fogunk egy kicsit, méghozzá egy karácsonyfával. Nem is akármilyennel, az Excelben farigcsálunk egy kezdetlegeset.

Első lépésként szimplán színezzünk ki néhány cellát zölddel a fenyőt tűleveleinek, illetve néhányat barnával a törzsének:

Ha ez megvan, akkor itt az idő a díszítésre, amelyhez először is az összes zöld cellát töltsük ki egy RANDBETWEEN függvénnyel, amellyel generáljunk egy és három közötti számokat. Valahogy így:

Ezután a Home ribbonfülünk Conditional Formatting funkcióját felhasználva szúrjunk be egy új szabályt (New Rule), méghozzá egy "Format all cells based on their values" típusút, úgy, hogy a szabályunk formázási stílusa ikonkészlet (Icon Sets) legyen és azon belül is mondjuk a három színes köralakú ikon:

Értelemszerűen a szabályokat jól kell beállítanunk, tehát valószínűleg célszerűbb nem százalékos formátumban küszködni, hanem a kalkuláció típusát Numberre állítani. Ha ezzel megvagyunk és érvényesítjük is a Conditional formattingot, akkor valami ilyesmit kapunk:

Azt ugye tudjuk, hogy alapból az Excelünk automatikus kalkulációra van beállítva, amit egyrészt a File menü Options almenüjében tudnánk kikapcsolni vagy a Formulas ribbonfülünk Calculation Options menüjében:

De most nem az a célunk, hogy ezt kikapcsoljuk, ellenkezőleg, azt akarjuk, hogy a RANDBETWEEN egy bizonyos ideig folyamatosan újrakalkulálódjon, tehát az F9 nyomogatása vagy más cellák kiválasztása helyett, teljesen magától frissüljön a "fánk".

Ehhez pedig csak egy nagyon egyszerű kis kód kell VBA-ban, méghozzá egy ilyen:

Dim i As Long

   For i = 1 To 10

      Application.Calculate

     Application.Wait Now + #12:00:01 AM#

Next i

Azaz van egy For ciklusunk, amiben nem más történik, minthogy újrakalkulálunk egyet (Application.Calculate) majd várunk egy másodpercet (Application.Wait) és utána ismételjük ezt többször, jelen esetben tízszer. Tehát itt szépen be tudjuk állítgatni, hogy hány másodpercig szeretnénk ezt futtatni, mert ha ezt lefuttatjuk, akkor szépen villogni is fog a fánk.

Ezzel a poszttal kívánok tehát minden kedves Olvasónak boldog karácsonyt és jó pihenést erre a pár napra!

Egyik kedves Olvasóm kérdése volt az ötletadója ennek a posztnak, ugyanis bár ő maga már jóval túllépett az alapokon, úgy gondoltam, egy ilyen jellegű megoldás kezdetleges kialakításának bemutatása mindenki számára hasznos lehet. Adott egy adatbázis, oszlopokkal, sorokkal, mindenféle adatokkal, mint a következő példa:

Ebben a posztban készíteni fogunk egy olyan userformot, amelynek a segítségével a felhasználónk roppant egyszerűen, akár már csak az első betű beírása után szűrni tud az adatbázis bármelyik oszlopában. Első lépésként a Developer ribbonfülünk Controls funkciójának Insert menüjéből szúrjunk be egy Command Buttont, amelyet aztán a jobb gombbal történt kattintás után formázzunk is meg, ahogy szeretnénk:

Ezután, még mindig Design Modeban, kattintsunk duplán erre a gombra, majd a megnyíló VBA-editorban a CommandButton1 Click eseményéhez rendeljünk hozzá egy UserForm2.Show utasítást (értelemszerűen a UserForm2 helyettesítendő az adott userform nevével), azaz a gombra való kattintással elindítjuk a keresést segítő userformot.

Ha már úgyis a VBA-editorban vagyunk, szépen menjünk az Insert menüpontra, ahonnan szúrjunk egy Userformot. Ha beszúrtuk, akkor a Toolbox segítségével (ha ezt nem látnánk úgy, ahogy a lenti képen, akkor a View menüben találjuk meg az indító parancsát) pakoljunk a formra legalább egy Listboxot és egy Textboxot, plusz célszerű esetleg valamiféle dizájnelemet vagy szöveget is rátenni:

Alapvetően a listboxunkat fogjuk a keresés eredményének megjelenítésére használni, a textboxban pedig gépelni fog a felhasználónk, úgyhogy a listboxot mindenképpen konfigurálnunk egy kicsit. Kattintsunk rá jobb gombbal, majd a Properties alatt formázgassuk meg, de a ColumnCount paramétert mindenképpen állítsuk annyira, ahány oszlopot akarunk kezelni:

Aztán nagyjából már csak a kód van hátra a Textboxunk mögött. Kattintsunk a mezőre jobb gombbal, majd View Code menüpont segítségével menjünk át a Textboxunk Change eseményéhez:

Tehát ez a kód akkor fog lefutni, ha bármi változás történik a szövegdobozunkban (ergó elkezdünk gépelni). Első lépésként töröljük ki a Listboxunk aktuális tartalmát. Az "me" parancsról már írtam itt korábban, ez mindig arra a "szülő" objektumra hivatkozik, amelyikben a kód benne van, jelen esetben tehát az "me" a userformot jelenti - azaz a userformunk Listbox1-éről beszélünk. Ezután definiáljunk egy i nevű változót, amely nagyjából a vizsgálandó sorokat jelenti, ezért lesz a következő sorunk egy For .. To..., ugyanis amennyiben elkezdünk gépelni, akkor a később következő kódrészeket meg fogjuk nézni az A oszlop összes (A:A), nem üres (CountA("A:A")) során, a headert leszámítva (For i = 2).

De egy újabb For ciklussal kell folytatnunk, hiszen soronként mind a három oszlopot meg kell vizsgálnunk, hiszen nem tudhatjuk, hogy a felhasználó az adatbázis három oszlopa közül melyikben keresne éppen. És hogy az így kialakult mátrix elemein milyen vizsgálatot is fogunk elvégezni?

Elsőként a Length névre keresztelt változóval vetessük fel a Textboxunkba írt szöveg karakterszámát. Ezután viszont jön a tényleges vizsgálat, a kód lelke, azaz ha (If) az adott munkalapunk előbb létrehozott mátrixának (minden sor minden oszlopát vizsgáljuk) aktuálisan vizsgált cellája (.Cells(i,f)) értékének (.Value) bal oldaláról levágunk annyi karaktert (Left), amennyit a felhasználó éppen beírt a textboxba és ez a levágott rész pontosan megegyezik a textboxba beírt szöveggel (és hát persze valami van a textboxban, ergó me.textbox1.text <> ""), akkor (Then) jön a képbe a Listbox AddItem metódusa, amelynek segítségével a listboxban megjelenített értékek listájához tudunk egy újabb értéket hozzáadni. Ezzel ugyanis szépen hozzáadjuk annak a sornak az első oszlopában szereplő értéket a listboxhoz, amely sorban a fenti vizsgálat egyezést talált bármelyik oszlopban.

A kód zárórészével pedig igazából már csak az előbb AddItemmel hozzáadott, első oszlopban szereplő érték mellé kell a második és harmadik oszlopban szereplő értékeket is beraknunk a Listboxba, amit pedig egy újabb For segítségével fogunk elérni, méghozzá úgy, hogy amennyiben a fenti vizsgálat talált egy egyezést, akkor még lesz egy, két lépésből álló ciklusunk (For m = 1 to 2), amelyben a listboxunk második és harmadik oszlopába betesszük az adott sor második és harmadik oszlopában szereplő értéket. Alapvetően ez így elég egyértelmű lenne, kivéve azt a mínusz egyest a listbox List tulajdonságában (ez utóbbi egyébként egy bizonyos elemet jelent a listboxban) szereplő listbox.listcount tulajdonság mögött, ami egyébként arra használatos, hogy megtudjuk egy listbox elemeinek számát. Szóval amit tudnunk kell erről az az, a ListCount mindig eggyel kezdi a számlálást az elemeknél, viszont a listbox nullánál kezdődik, ergó ez azt jelenti, hogy

.List(ListBox1.ListCount - 1, 1)

az első sorban és második oszlopban szereplő elemet fogja jelenteni, a -1,2 pedig a harmadikat.

És innentől kezdve már csak a For ciklusok kötelező Next elemeit kell betennünk és lezárhatjuk a szubrutint. Íme az eredmény: