Contents [show]
Selfs as jy nie weet hoe om te programmeer nie, is daar baie plekke (boeke, webwerwe, forums) waar jy klaargemaakte VBA-makrokode vir 'n groot aantal tipiese take in Excel kan vind. Volgens my ervaring versamel die meeste gebruikers vroeër of later hul persoonlike versameling makro's om roetineprosesse te outomatiseer, of dit nou is om formules in waardes te vertaal, somme in woorde te vertoon of selle volgens kleur op te som. En hier ontstaan die probleem – die makro-kode in Visual Basic moet iewers gestoor word om later in die werk gebruik te word.
Die maklikste opsie is om die makro-kode direk in die werklêer te stoor deur na die Visual Basic-redigeerder te gaan met die sleutelbordkortpad alt+F11 en die byvoeging van 'n nuwe leë module via die spyskaart Voeg in – Module:
Daar is egter verskeie nadele met hierdie metode:
- As daar baie werklêers is, en 'n makro is oral nodig, soos 'n makro vir die omskakeling van formules in waardes, dan sal jy die kode moet kopieer in elke boek.
- Moet nie vergeet word nie stoor lêer in makro-geaktiveerde formaat (xlsm) of in binêre boekformaat (xlsb).
- Wanneer so 'n lêer oopgemaak word makro beskerming sal elke keer 'n waarskuwing uitreik wat erken moet word (wel, of deaktiveer beskerming heeltemal, wat dalk nie altyd wenslik is nie).
'n Meer elegante oplossing sou wees om te skep jou eie byvoeging (Excel-byvoeging) – 'n aparte lêer van 'n spesiale formaat (xlam) wat al jou "gunsteling" makro's bevat. Die voordele van hierdie benadering:
- Dit sal genoeg wees koppel byvoeging een keer in Excel – en jy kan sy VBA-prosedures en funksies in enige lêer op hierdie rekenaar gebruik. Dit is dus nie nodig om jou werklêers in xlsm- en xlsb-formate te stoor nie, want. die bronkode sal nie daarin gestoor word nie, maar in die byvoegingslêer.
- Beskerming jy sal ook nie deur makro's gepla word nie. byvoegings is per definisie betroubare bronne.
- Kan doen aparte oortjie op die Excel-lint met mooi knoppies om byvoegingsmakro's uit te voer.
- Die byvoeging is 'n aparte lêer. Syne maklik om te dra van rekenaar tot rekenaar, deel dit met kollegas of verkoop dit selfs 😉
Kom ons loop stap vir stap deur die hele proses om jou eie Microsoft Excel-byvoeging te skep.
Stap 1. Skep 'n byvoegingslêer
Maak Microsoft Excel oop met 'n leë werkboek en stoor dit onder enige geskikte naam (byvoorbeeld MyExcelAddin) in byvoegingsformaat met die opdrag Lêer – Stoor as of sleutels F12, wat die lêertipe spesifiseer Excel-byvoeging:
Neem asseblief kennis dat Excel by verstek byvoegings in die C:UsersYour_nameAppDataRoamingMicrosoftAddIns-lêergids stoor, maar in beginsel kan jy enige ander vouer spesifiseer wat vir jou gerieflik is.
Stap 2. Ons koppel die geskepte byvoeging
Nou die byvoeging wat ons in die laaste stap geskep het MyExcelAddin moet aan Excel gekoppel wees. Om dit te doen, gaan na die spyskaart Lêer – Opsies – Byvoegings (Lêer — Opsies — Byvoegings), klik op die knoppie Oor (Gaan) aan die onderkant van die venster. Klik op die knoppie in die venster wat oopmaak Resensie (Snuffel) en spesifiseer die ligging van ons byvoegingslêer.
As jy alles reg gedoen het, dan is ons MyExcelAddin moet in die lys van beskikbare byvoegings verskyn:
Stap 3. Voeg makro's by die byvoeging
Ons byvoeging is aan Excel gekoppel en werk suksesvol, maar daar is nog nie 'n enkele makro daarin nie. Kom ons vul dit vol. Om dit te doen, maak die Visual Basic-redigeerder oop met die sleutelbordkortpad alt+F11 of per knoppie Visual Basic tab ontwikkelaar (Ontwikkelaar). As oortjies ontwikkelaar nie sigbaar is nie, dit kan deur vertoon word Lêer – Opsies – Lintopstelling (Lêer - Opsies - Pas lint aan).
Daar moet 'n venster in die boonste linkerhoek van die redigeerder wees Project (as dit nie sigbaar is nie, skakel dit dan aan deur die spyskaart Bekyk - Project Explorer):
Hierdie venster vertoon alle oop werkboeke en lopende Microsoft Excel-byvoegings, insluitend ons s'n. VBAProjek (MyExcelAddin.xlam) Kies dit met die muis en voeg 'n nuwe module daarby via die kieslys Voeg in – Module. In hierdie module sal ons die VBA-kode van ons byvoegingsmakro's stoor.
Jy kan óf die kode van nuuts af tik (as jy weet hoe om te programmeer), óf dit van klaargemaakte plek af kopieer (wat baie makliker is). Kom ons, vir toetsing, voer die kode van 'n eenvoudige maar nuttige makro in die bygevoegde leë module in:
Nadat u die kode ingevoer het, moenie vergeet om op die stoorknoppie (diskette) in die linkerbovenhoek te klik nie.
Ons makro FormulesToValues, soos u maklik kan voorstel, skakel formules om na waardes in 'n voorafgeselekteerde reeks. Soms word hierdie makros ook genoem prosedures. Om dit uit te voer, moet jy selle met formules kies en 'n spesiale dialoogkassie oopmaak Macros vanaf die blad ontwikkelaar (Ontwikkelaar - Makro's) of sleutelbordkortpad alt+F8. Normaalweg wys hierdie venster beskikbare makro's van alle oop werkboeke, maar byvoegingsmakro's is nie hier sigbaar nie. Ten spyte hiervan kan ons die naam van ons prosedure in die veld invoer makro naam (Makro naam)en klik dan op die knoppie Run (hardloop) – en ons makro sal werk:
| |
Hier kan jy ook 'n sleutelbordkortpad toewys om vinnig 'n makro te begin – die knoppie is daarvoor verantwoordelik Grense (Opsies) in die vorige venster Makro:
Wanneer u sleutels toewys, moet u in gedagte hou dat hulle hooflettergevoelig en sleutelborduitlegsensitief is. So as jy 'n kombinasie soos Ctrl+Й, dan sal jy in die toekoms in werklikheid moet seker maak dat jy die uitleg aangeskakel het en addisioneel druk Skuifom die hoofletter te kry.
Ons kan gerieflikheidshalwe ook 'n knoppie vir ons makro by die vinnige toegang-nutsbalk in die boonste linkerhoek van die venster voeg. Om dit te doen, kies Lêer – Opsies – Vinnige Toegang-nutsbalk (Lêer - Opsies - Pas Vinnige Toegang-nutsbalk aan), en dan in die aftreklys boaan die venster die opsie Macros. Daarna ons makro FormulesToValues kan met die knoppie op die paneel geplaas word Voeg (Voeg by) en kies 'n ikoon daarvoor met die knoppie verandering (Edit):
Stap 4. Voeg funksies by die byvoeging
maar makro-prosedures, daar is ook funksie makro's of soos hulle genoem word UDF (Gebruikergedefinieerde funksie = gebruikergedefinieerde funksie). Kom ons skep 'n aparte module in ons byvoeging (kieslysopdrag Voeg in – Module) en plak die kode van die volgende funksie daar:
Dit is maklik om te sien dat hierdie funksie nodig is om BTW uit die bedrag insluitend BTW te onttrek. Natuurlik nie Newton se binomiaal nie, maar dit sal vir ons dien as voorbeeld om die basiese beginsels te wys.
Let daarop dat die sintaksis van 'n funksie verskil van 'n prosedure:
- konstruksie gebruik word Funksie …. Eindig funksie plaas Sub … Einde Sub
- na die naam van die funksie word sy argumente tussen hakies aangedui
- in die liggaam van die funksie word die nodige berekeninge uitgevoer en dan word die resultaat aan 'n veranderlike met die naam van die funksie toegeken
Let ook daarop dat hierdie funksie nie nodig is nie, en dit is onmoontlik om soos die vorige makroprosedure deur die dialoogkassie te hardloop Macros en knoppie Run. So 'n makrofunksie moet gebruik word as 'n standaard werkbladfunksie (SOM, IF, VLOOKUP...), maw voer net in enige sel in, en spesifiseer die waarde van die bedrag met BTW as argument:
… of gaan deur die standaard dialoogkassie om 'n funksie in te voeg (knoppie fx in die formulebalk), kies 'n kategorie Gebruiker gedefinieerd (Gebruiker gedefinieerd):
Die enigste onaangename oomblik hier is die afwesigheid van die gewone beskrywing van die funksie onderaan die venster. Om dit by te voeg, sal jy die volgende moet doen:
- Maak die Visual Basic Editor oop met 'n sleutelbordkortpad alt+F11
- Kies die byvoeging in die projekpaneel en druk die sleutel F2om die Object Browser-venster oop te maak
- Kies jou byvoegingsprojek uit die aftreklys boaan die venster
- Regskliek op die funksie wat verskyn en kies opdrag Eiendomme.
- Voer 'n beskrywing van die funksie in die venster in Beskrywing
- Stoor die byvoeginglêer en herbegin Excel.
Na herbegin moet die funksie die beskrywing vertoon wat ons ingevoer het:
Stap 5. Skep 'n byvoegingsoortjie in die koppelvlak
Die laaste, hoewel nie verpligte nie, maar aangename aanraking sal die skepping van 'n aparte oortjie met 'n knoppie wees om ons makro te laat loop, wat in die Excel-koppelvlak sal verskyn nadat ons ons byvoeging gekoppel is.
Inligting oor die oortjies wat by verstek vertoon word, is in die boek vervat en moet in 'n spesiale XML-kode geformateer word. Die maklikste manier om sulke kode te skryf en te redigeer, is met behulp van spesiale programme - XML-redigeerders. Een van die gerieflikste (en gratis) is Maxim Novikov se program Lint XML-redigeerder.
Die algoritme om daarmee te werk is soos volg:
- Maak alle Excel-vensters toe sodat daar geen lêerkonflik is wanneer ons die XML-byvoegingskode wysig nie.
- Begin die Ribbon XML Editor-program en maak ons MyExcelAddin.xlam-lêer daarin oop
- Met knoppie oortjies in die boonste linkerhoek, voeg die kodebrokkie vir die nuwe oortjie by:
- Jy moet leë aanhalingstekens insit id ons oortjie en groep (enige unieke identifiseerders), en in etiket – die name van ons oortjie en 'n groep knoppies daarop:
- Met knoppie knoppie op die linkerpaneel, voeg 'n leë kode vir die knoppie by en voeg etikette daarby:
- etiket is die teks op die knoppie
— beeldMso — dit is die voorwaardelike naam van die prent op die knoppie. Ek het 'n rooi knoppie-ikoon genaamd AnimationCustomAddExitDialog gebruik. Die name van alle beskikbare knoppies (en daar is 'n paar honderd van hulle!) kan op 'n groot aantal webwerwe op die internet gevind word as jy na die sleutelwoorde "imageMso" soek. Om mee te begin, kan jy hierheen gaan.
- op Aksie – dit is die naam van die terugbelprosedure – 'n spesiale kort makro wat ons hoofmakro sal laat loop FormulesToValues. Jy kan hierdie prosedure noem wat jy wil. Ons sal dit 'n bietjie later byvoeg.
- U kan die korrektheid van alles wat gedoen is, kontroleer deur die knoppie met 'n groen regmerkie bo-aan die nutsbalk te gebruik. Klik op dieselfde plek op die knoppie met 'n disket om alle veranderinge te stoor.
- Maak die lint XML-redigeerder toe
- Maak Excel oop, gaan na die Visual Basic-redigeerder en voeg 'n terugbelprosedure by ons makro DoodFormulessodat dit ons hoofmakro laat loop om formules met waardes te vervang.
- Ons stoor die veranderinge en gaan terug na Excel, kyk na die resultaat:
Dit is al – die byvoeging is gereed om te gebruik. Vul dit met jou eie prosedures en funksies, voeg pragtige knoppies by – en dit sal baie makliker word om makro's in jou werk te gebruik.
- Wat is makro's, hoe om dit in jou werk te gebruik, waar om makro-kode in Visual Basic te kry.
- Hoe om 'n spatskerm te maak wanneer 'n werkboek in Excel oopgemaak word
- Wat is 'n persoonlike makroboek en hoe om dit te gebruik