Sisu
Isegi kui te ei tea, kuidas programmeerida, on palju kohti (raamatud, veebisaidid, foorumid), kust leiate valmis VBA makrokoodi suure hulga Exceli tüüpiliste ülesannete jaoks. Minu kogemuse põhjal kogub enamik kasutajaid varem või hiljem oma isikliku makrokogu, et automatiseerida rutiinseid protsesse, olgu selleks siis valemite tõlkimine väärtusteks, summade kuvamine sõnades või lahtrite summeerimine värvide järgi. Ja siit tekibki probleem – Visual Basicu makrokood tuleb kuskile salvestada, et seda hiljem töös kasutada.
Lihtsaim võimalus on salvestada makrokood otse tööfaili, minnes kiirklahvi abil Visual Basicu redaktorisse muu+F11 ja uue tühja mooduli lisamine menüü kaudu Sisesta – moodul:
Sellel meetodil on aga mitmeid puudusi:
- Kui tööfaile on palju ja kõikjal on vaja makrot, näiteks makrot valemite väärtusteks teisendamiseks, peate koodi kopeerima igas raamatus.
- Ei tohi unustada salvestage fail makro-toega vormingus (xlsm) või binaarraamatu vormingus (xlsb).
- Sellise faili avamisel makro kaitse annab iga kord hoiatuse, mida tuleb kinnitada (noh, või keelab kaitse täielikult, mis ei pruugi alati soovitav olla).
Elegantsem lahendus oleks luua teie enda lisandmoodul (Exceli lisandmoodul) – erivormingus (xlam) eraldi fail, mis sisaldab kõiki teie lemmikmakrosid. Selle lähenemisviisi eelised:
- Sellest piisab ühendage lisandmoodul üks kord Excelis – ja saate kasutada selle VBA protseduure ja funktsioone selle arvuti mis tahes failis. Tööfailide uuesti salvestamine xlsm- ja xlsb-vormingus ei ole seega vajalik, kuna. lähtekoodi ei salvestata neisse, vaid lisandmooduli faili.
- Kaitsmed teid ei häiri ka makrod. lisandmoodulid on definitsiooni järgi usaldusväärsed allikad.
- Saab teha eraldi vahekaart Exceli lindil kenade nuppudega lisandmooduli makrode käivitamiseks.
- Lisandmoodul on eraldi fail. Tema lihtne teha arvutist arvutisse, jaga seda kolleegidega või isegi müü maha 😉
Käime samm-sammult läbi kogu oma Microsoft Exceli lisandmooduli loomise protsessi.
Samm 1. Looge lisandmooduli fail
Avage Microsoft Excel tühja töövihikuga ja salvestage see sobiva nime all (näiteks MyExcelAddin) lisandmooduli vormingus käsuga Fail – Salvesta nimega või võtmed F12, määrates failitüübi Exceli lisandmoodul:
Pange tähele, et vaikimisi salvestab Excel lisandmoodulid kausta C:UsersYour_nameAppDataRoamingMicrosoftAddIns, kuid põhimõtteliselt saate määrata mis tahes muu teile sobiva kausta.
Samm 2. Ühendame loodud lisandmooduli
Nüüd lisandmoodul, mille lõime viimases etapis MyExcelAddin peab olema ühendatud Exceliga. Selleks minge menüüsse Fail – Valikud – Lisandmoodulid (Fail — Valikud — Lisandmoodulid), klõpsake nuppu MEIST (Mine) akna allservas. Avanevas aknas klõpsake nuppu Ülevaade (Sirvi) ja täpsustage meie lisandmooduli faili asukoht.
Kui tegite kõik õigesti, siis meie MyExcelAddin peaks ilmuma saadaolevate lisandmoodulite loendis:
3. samm. Lisage lisandmoodulile makrod
Meie lisandmoodul on ühendatud Exceliga ja töötab edukalt, kuid selles pole veel ühtegi makrot. Täidame selle. Selleks avage kiirklahviga Visual Basicu redaktor muu+F11 või nupuga Visual Basic tab arendaja (Arendaja). Kui vahelehed arendaja pole nähtav, seda saab läbi kuvada Fail – Valikud – Lindi seadistamine (Fail — Valikud — Kohanda linti).
Redaktori vasakus ülanurgas peaks olema aken Projekt (kui see pole nähtav, lülitage see menüü kaudu sisse Vaade — Project Explorer):
Selles aknas kuvatakse kõik avatud töövihikud ja töötavad Microsoft Exceli lisandmoodulid, sealhulgas meie oma. VBAProject (MyExcelAddin.xlam) Valige see hiirega ja lisage sellele menüü kaudu uus moodul Sisesta – moodul. Selles moodulis salvestame oma lisandmooduli makrode VBA-koodi.
Koodi võid kas nullist tippida (kui oskad programmeerida) või kopeerida kuskilt valmis (mis on palju lihtsam). Sisestame testimiseks lisatud tühja moodulisse lihtsa, kuid kasuliku makro koodi:
Pärast koodi sisestamist ärge unustage klõpsata vasakus ülanurgas salvestamisnupul (diskett).
Meie makro FormulasToValues, nagu võite kergesti ette kujutada, teisendab valemid väärtusteks eelvalitud vahemikus. Mõnikord nimetatakse neid makrosid ka menetlused. Selle käivitamiseks peate valima valemitega lahtrid ja avama spetsiaalse dialoogiboksi Makrod vahekaardilt arendaja (Arendaja – makrod) või klaviatuuri otsetee muu+F8. Tavaliselt näitab see aken saadaolevaid makrosid kõigist avatud töövihikutest, kuid lisandmooduli makrosid pole siin näha. Sellest hoolimata saame väljale sisestada oma protseduuri nime makro nimi (Makro nimi)ja seejärel klõpsake nuppu jooks (jookse) – ja meie makro töötab:
| |
Siin saab määrata ka kiirklahvi makro kiireks käivitamiseks – selle eest vastutab nupp parameetrid (Valikud) eelmises aknas Makro:
Klahvide määramisel pidage meeles, et need on tõstutundlikud ja klaviatuuripaigutustundlikud. Nii et kui määrate kombinatsiooni nagu Ctrl+Й, siis tegelikult peate tulevikus veenduma, et olete paigutuse sisse lülitanud ja vajutage lisaks nihesuure algustähe saamiseks.
Mugavuse huvides saame lisada oma makro nupu ka kiire juurdepääsu tööriistaribale akna vasakus ülanurgas. Selleks valige Fail – Valikud – kiirjuurdepääsu tööriistariba (Fail — Valikud — kiirjuurdepääsu tööriistariba kohandamine)ja seejärel akna ülaosas olevas ripploendis suvand Makrod. Pärast seda meie makro FormulasToValues saab nupuga paneelile asetada lisama (Lisa) ja valige nupuga sellele ikoon Muutma (Edit):
4. samm. Lisage lisandmoodulile funktsioone
Kuid makroprotseduurid, on ka funktsiooni makrod või kuidas neid nimetatakse UDF (Kasutaja määratud funktsioon = kasutaja määratud funktsioon). Loome oma lisandmoodulis eraldi mooduli (menüükäsk Sisesta – moodul) ja kleepige sinna järgmise funktsiooni kood:
On hästi näha, et seda funktsiooni on vaja käibemaksuga summalt käibemaksu väljavõtmiseks. Muidugi mitte Newtoni binoom, kuid see on meile eeskujuks põhiprintsiipide näitamiseks.
Pange tähele, et funktsiooni süntaks erineb protseduurist:
- kasutatakse ehitust Funktsioon …. Lõpetamisfunktsioon selle asemel Sub … End Sub
- funktsiooni nime järel näidatakse selle argumendid sulgudes
- funktsiooni kehas tehakse vajalikud arvutused ja seejärel omistatakse tulemus funktsiooni nimega muutujale
Samuti pange tähele, et seda funktsiooni pole vaja ja seda ei saa dialoogiboksi kaudu käivitada nagu eelmine makroprotseduur Makrod ja nuppu jooks. Sellist makrofunktsiooni tuleks kasutada standardse töölehe funktsioonina (SUM, IF, VLOOKUP…), st lihtsalt sisestage suvalisesse lahtrisse, määrates argumendiks summa väärtuse koos käibemaksuga:
… või sisestage funktsiooni sisestamiseks standardse dialoogiboksi kaudu (nupp fx valemiribal), valides kategooria Kasutaja määratletud (kasutaja määratud):
Ainus ebameeldiv hetk on siin funktsiooni tavapärase kirjelduse puudumine akna allosas. Selle lisamiseks peate tegema järgmist.
- Avage Visual Basicu redaktor kiirklahviga muu+F11
- Valige projektipaneelil lisandmoodul ja vajutage klahvi F2Objektibrauseri akna avamiseks
- Valige akna ülaosas olevast ripploendist oma lisandmooduli projekt
- Paremklõpsake kuvataval funktsioonil ja valige käsk Kinnisvara.
- Sisestage aknasse funktsiooni kirjeldus Kirjeldus
- Salvestage lisandmooduli fail ja taaskäivitage Excel.
Pärast taaskäivitamist peaks funktsioon kuvama meie sisestatud kirjelduse:
Samm 5. Looge liideses lisandmooduli vahekaart
Viimane, kuigi mitte kohustuslik, kuid meeldiv puudutus on eraldi vahekaardi loomine nupuga meie makro käivitamiseks, mis kuvatakse pärast lisandmooduli ühendamist Exceli liidesesse.
Teave vaikimisi kuvatavate vahekaartide kohta sisaldub raamatus ja see tuleb vormindada spetsiaalses XML-koodis. Lihtsaim viis sellist koodi kirjutada ja redigeerida on spetsiaalsete programmide – XML-redaktorite – abil. Üks mugavamaid (ja tasuta) on Maxim Novikovi programm Lindi XML-redaktor.
Sellega töötamise algoritm on järgmine:
- Sulgege kõik Exceli aknad, et lisandmooduli XML-koodi redigeerimisel ei tekiks failikonflikte.
- Käivitage programm Ribbon XML Editor ja avage selles meie fail MyExcelAddin.xlam
- Nupuga klapid lisage vasakus ülanurgas uue vahekaardi koodilõik:
- Peate sisestama tühjad jutumärgid id meie vahekaart ja rühm (kõik kordumatud identifikaatorid) ja sisse etikett – meie vahekaardi ja sellel olevate nuppude rühma nimed:
- Nupuga nupp lisage vasakul paneelil nupule tühi kood ja lisage sellele sildid:
- etikett on tekst nupul
— piltMso — see on nupul oleva pildi tingimuslik nimi. Kasutasin punase nupu ikooni nimega AnimationCustomAddExitDialog. Kõigi saadaolevate nuppude (ja neid on mitusada!) nimed leiate paljudelt Interneti-saitidelt, kui otsite märksõna "imageMso". Alustuseks võite minna siia.
- onAction – see on tagasihelistamisprotseduuri nimi – spetsiaalne lühike makro, mis käivitab meie peamise makro FormulasToValues. Saate seda protseduuri nimetada nii, nagu soovite. Lisame selle veidi hiljem.
- Kõige tehtu õigsust saate kontrollida tööriistariba ülaosas oleva rohelise linnukesega nupuga. Kõikide muudatuste salvestamiseks klõpsake samas kohas disketiga nuppu.
- Sulgege lindi XML-redaktor
- Avage Excel, minge Visual Basicu redaktorisse ja lisage meie makrole tagasihelistamise protseduur KillFormulaset see käivitaks meie peamise makro valemite väärtustega asendamiseks.
- Salvestame muudatused ja naastes Excelisse kontrollime tulemust:
See on kõik – lisandmoodul on kasutamiseks valmis. Täitke see oma protseduuride ja funktsioonidega, lisage ilusaid nuppe – ja makrode kasutamine oma töös muutub palju lihtsamaks.
- Mis on makrod, kuidas neid oma töös kasutada, kust saada makrokoodi Visual Basicus.
- Kuidas teha töövihiku avamisel Excelis splash screen
- Mis on isiklik makroraamat ja kuidas seda kasutada