Innhold
Selv om du ikke vet hvordan du programmerer, er det mange steder (bøker, nettsider, fora) hvor du kan finne ferdiglaget VBA-makrokode for et stort antall typiske oppgaver i Excel. Etter min erfaring samler de fleste brukere før eller siden sin personlige samling av makroer for å automatisere rutineprosesser, enten det er å oversette formler til verdier, vise summer i ord eller summere celler etter farge. Og her oppstår problemet - makrokoden i Visual Basic må lagres et sted for å kunne brukes senere i arbeidet.
Det enkleste alternativet er å lagre makrokoden direkte i arbeidsfilen ved å gå til Visual Basic-editoren ved å bruke hurtigtasten andre+F11 og legge til en ny tom modul via menyen Sett inn – modul:
Imidlertid er det flere ulemper med denne metoden:
- Hvis det er mange arbeidsfiler, og en makro er nødvendig overalt, for eksempel en makro for å konvertere formler til verdier, må du kopiere koden i hver bok.
- Må ikke glemmes lagre filen i makroaktivert format (xlsm) eller i binært bokformat (xlsb).
- Når du åpner en slik fil makrobeskyttelse vil hver gang gi en advarsel som må bekreftes (vel, eller deaktiver beskyttelsen helt, noe som kanskje ikke alltid er ønskelig).
En mer elegant løsning ville være å lage ditt eget tillegg (Excel-tillegg) – en separat fil i et spesielt format (xlam) som inneholder alle dine "favoritt" makroer. Fordelene med denne tilnærmingen:
- Det blir nok koble til tillegget én gang i Excel – og du kan bruke dens VBA-prosedyrer og funksjoner i hvilken som helst fil på denne datamaskinen. Å lagre arbeidsfilene på nytt i xlsm- og xlsb-formater er derfor ikke nødvendig, fordi. kildekoden vil ikke bli lagret i dem, men i tilleggsfilen.
- beskyttelse du vil heller ikke bli plaget av makroer. tillegg er per definisjon pålitelige kilder.
- Kan gjøre egen fane på Excel-båndet med fine knapper for å kjøre tilleggsmakroer.
- Tillegget er en egen fil. Hans lett å bære fra datamaskin til datamaskin, del den med kolleger eller selg den 😉
La oss gå gjennom hele prosessen med å lage ditt eget Microsoft Excel-tillegg trinn for trinn.
Trinn 1. Opprett en tilleggsfil
Åpne Microsoft Excel med en tom arbeidsbok og lagre den under et passende navn (for eksempel MyExcelAddin) i tilleggsformat med kommandoen Fil – Lagre som eller nøkler F12, som spesifiserer filtypen Excel-tillegg:
Vær oppmerksom på at Excel som standard lagrer tillegg i mappen C:UsersYour_nameAppDataRoamingMicrosoftAddIns, men i prinsippet kan du spesifisere hvilken som helst annen mappe som passer for deg.
Trinn 2. Vi kobler til det opprettede tillegget
Nå er tillegget vi opprettet i det siste trinnet MyExcelAddin må være koblet til Excel. For å gjøre dette, gå til menyen Fil – Alternativer – Tillegg (Fil — Alternativer — Tillegg), klikk på knappen Om oss (Gå) nederst i vinduet. Klikk på knappen i vinduet som åpnes Anmeldelse (Bla gjennom) og spesifiser plasseringen av tilleggsfilen vår.
Hvis du gjorde alt riktig, så vår MyExcelAddin skal vises i listen over tilgjengelige tillegg:
Trinn 3. Legg til makroer i tillegget
Vårt tillegg er koblet til Excel og fungerer vellykket, men det er ikke en eneste makro i det ennå. La oss fylle den opp. For å gjøre dette, åpne Visual Basic-redigeringsprogrammet med hurtigtasten andre+F11 eller med knapp Visual Basic tab utvikler (Utvikler). Hvis faner utvikler ikke synlig, kan den vises gjennom Fil – Alternativer – Båndoppsett (Fil — Alternativer — Tilpass bånd).
Det skal være et vindu i øvre venstre hjørne av editoren Prosjekt (hvis den ikke er synlig, slå den på via menyen Vis — Prosjektutforsker):
Dette vinduet viser alle åpne arbeidsbøker og kjørende Microsoft Excel-tillegg, inkludert våre. VBAProject (MyExcelAddin.xlam) Velg den med musen og legg til en ny modul til den via menyen Sett inn – modul. I denne modulen vil vi lagre VBA-koden til tilleggsmakroene våre.
Du kan enten skrive inn koden fra bunnen av (hvis du vet hvordan du programmerer), eller kopiere den fra et sted ferdig (noe som er mye enklere). La oss, for testing, legge inn koden til en enkel, men nyttig makro i den tomme modulen som er lagt til:
Etter å ha tastet inn koden, ikke glem å klikke på lagre-knappen (diskett) i øvre venstre hjørne.
Makroen vår FormlerTil Verdier, som du lett kan forestille deg, konverterer formler til verdier i et forhåndsvalgt område. Noen ganger kalles disse makroene også prosedyrer. For å kjøre det, må du velge celler med formler og åpne en spesiell dialogboks Makroer fra fanen utvikler (Utvikler – Makroer) eller hurtigtast andre+F8. Normalt viser dette vinduet tilgjengelige makroer fra alle åpne arbeidsbøker, men tilleggsmakroer er ikke synlige her. Til tross for dette kan vi legge inn navnet på prosedyren vår i feltet makronavn (Makronavn)og klikk deretter på knappen Kjør (løpe) – og makroen vår vil fungere:
| |
Her kan du også tilordne en hurtigtast for raskt å starte en makro – knappen er ansvarlig for dette parametere (Alternativer) i forrige vindu Makro:
Når du tilordner taster, husk at de skiller mellom store og små bokstaver og sensitive for tastaturoppsett. Så hvis du tilordner en kombinasjon som Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally Skiftfor å få den store bokstaven.
For enkelhets skyld kan vi også legge til en knapp for makroen vår til hurtigtilgangsverktøylinjen i øvre venstre hjørne av vinduet. For å gjøre dette, velg Fil – Alternativer – Verktøylinje for hurtigtilgang (Fil — Alternativer — Tilpass verktøylinjen for hurtigtilgang), og deretter alternativet i rullegardinlisten øverst i vinduet Makroer. Etter det vår makro FormlerTil Verdier kan plasseres på panelet med knappen Legg til (Legg til) og velg et ikon for det med knappen Endring (Redigere):
Trinn 4. Legg til funksjoner i tillegget
Men makroprosedyrer, det er også funksjonsmakroer eller som de heter UDF (Brukerdefinert funksjon = brukerdefinert funksjon). La oss lage en egen modul i tillegget vårt (menykommando Sett inn – modul) og lim inn koden til følgende funksjon der:
Det er lett å se at denne funksjonen er nødvendig for å trekke ut merverdiavgift fra beløpet inkludert merverdiavgift. Ikke Newtons binomiale, selvfølgelig, men det vil gjøre for oss som eksempel å vise de grunnleggende prinsippene.
Merk at syntaksen til en funksjon er forskjellig fra en prosedyre:
- konstruksjon brukes Funksjon …. Avslutt funksjon i stedet Sub … End Sub
- etter navnet på funksjonen er argumentene angitt i parentes
- i hoveddelen av funksjonen utføres de nødvendige beregningene og deretter blir resultatet tilordnet en variabel med navnet på funksjonen
Merk også at denne funksjonen ikke er nødvendig, og det er umulig å kjøre som forrige makroprosedyre gjennom dialogboksen Makroer og knapp Kjør. En slik makrofunksjon bør brukes som en standard regnearkfunksjon (SUM, IF, VLOOKUP...), dvs. bare skriv inn i en hvilken som helst celle, og spesifiser verdien av beløpet med MVA som argument:
… eller gå inn gjennom standarddialogboksen for å sette inn en funksjon (knapp fx i formellinjen), velge en kategori Brukerdefinert (Brukerdefinert):
Det eneste ubehagelige øyeblikket her er fraværet av den vanlige beskrivelsen av funksjonen nederst i vinduet. For å legge det til må du gjøre følgende:
- Åpne Visual Basic Editor med en hurtigtast andre+F11
- Velg tillegget i prosjektpanelet og trykk på tasten F2for å åpne objektleservinduet
- Velg tilleggsprosjektet ditt fra rullegardinlisten øverst i vinduet
- Høyreklikk på funksjonen som vises og velg kommando Eiendommer.
- Skriv inn en beskrivelse av funksjonen i vinduet Beskrivelse
- Lagre tilleggsfilen og start excel på nytt.
Etter omstart skal funksjonen vise beskrivelsen vi skrev inn:
Trinn 5. Opprett en tilleggsfane i grensesnittet
Den siste, men ikke obligatoriske, men hyggelige berøringen vil være opprettelsen av en egen fane med en knapp for å kjøre makroen vår, som vises i Excel-grensesnittet etter å ha koblet til tillegget vårt.
Informasjon om fanene som vises som standard finnes i boken og må formateres i en spesiell XML-kode. Den enkleste måten å skrive og redigere slik kode på er ved hjelp av spesielle programmer - XML-editorer. En av de mest praktiske (og gratis) er Maxim Novikovs program Ribbon XML Editor.
Algoritmen for å jobbe med den er som følger:
- Lukk alle Excel-vinduer slik at det ikke er noen filkonflikt når vi redigerer XML-tilleggskoden.
- Start Ribbon XML Editor-programmet og åpne filen MyExcelAddin.xlam i den
- Med knapp faner i øvre venstre hjørne legger du til kodebiten for den nye fanen:
- Du må sette inn tomme anførselstegn id fanen og gruppen vår (alle unike identifikatorer), og i etikett – navnene på fanen vår og en gruppe knapper på den:
- Med knapp knapp på venstre panel, legg til en tom kode for knappen og legg til tagger til den:
— merke er teksten på knappen
— imageMso — dette er det betingede navnet på bildet på knappen. Jeg brukte et rødt knappikon kalt AnimationCustomAddExitDialog. Navnene på alle tilgjengelige knapper (og det er flere hundre av dem!) kan finnes på et stort antall nettsteder på Internett hvis du søker etter søkeordene "imageMso". For det første kan du gå hit.
- onAction – dette er navnet på tilbakeringingsprosedyren – en spesiell kort makro som vil kjøre hovedmakroen vår FormlerTil Verdier. Du kan kalle denne prosedyren hva du vil. Vi legger det til litt senere.
- Du kan sjekke riktigheten av alt som er gjort ved å bruke knappen med en grønn hake øverst på verktøylinjen. På samme sted klikker du på knappen med en diskett for å lagre alle endringer.
- Lukk Ribbon XML Editor
- Åpne Excel, gå til Visual Basic-editoren og legg til en tilbakeringingsprosedyre til makroen vår KillFormulasslik at den kjører hovedmakroen vår for å erstatte formler med verdier.
- Vi lagrer endringene, og går tilbake til Excel, kontrollerer resultatet:
Det er alt – tillegget er klart til bruk. Fyll den med dine egne prosedyrer og funksjoner, legg til vakre knapper – og det blir mye enklere å bruke makroer i arbeidet ditt.
- Hva er makroer, hvordan bruker du dem i arbeidet ditt, hvor kan du hente makrokode i Visual Basic.
- Hvordan lage en splash-skjerm når du åpner en arbeidsbok i Excel
- Hva er en personlig makrobok og hvordan du bruker den