4 feil du kan unngå når du programmerer Excel-makroer med VBA

Microsoft Excel er et svært kompetent dataanalyseværktøy allerede, men med muligheten til å automatisere repeterende oppgaver med makroer. Slik sender du e-post fra et Excel-regneark ved hjelp av VBA-skript. Slik sender du e-post fra et Excel-regneark ved hjelp av VBA-skript. Vår kodemal vil hjelpe deg med å angi opp automatiserte e-poster fra Excel ved hjelp av samarbeidsdataobjekter (CDO) og VBA-skript. Les mer ved å skrive enkel kode i (VBA), det er så mye kraftigere. Imidlertid brukes feil, kan VBA forårsake mange problemer.

Selv om du ikke er programmerer, tilbyr VBA enkle funksjoner som gir deg mulighet til å legge til noen virkelig imponerende funksjonalitet i regnearkene dine, så ikke gå igjen enda!

Enten du er en VBA-guru som lager dashboards i Excel. Bygg ditt eget værdashboard i denne klassen for Google Regneark. Bygg ditt eget værpanel i denne klassen for Google Regneark. Vil du slå Google Drive-kontoen din til et intelligent væranalysesystem , og leve ut en Star Trek fantasi? Ok! Les mer, eller en nybegynner, som bare vet hvordan man skriver enkle skript som gjør grunnleggende celleberegninger, kan du følge enkle programmeringsteknikker, som vil hjelpe deg med å forbedre oddsen ved å skrive ren og feilfri kode.

Komme i gang med VBA

Hvis du ikke har programmert i VBA i Excel før, er det faktisk ganske enkelt å aktivere utviklerverktøyene. Bare gå til Fil > alternativer og så Tilpass båndet. Bare flytt Utvikler kommandogruppe fra venstre rute over til høyre.

Kontroller at avkrysningsruten er aktivert, og nå vises fanen Utvikler i Excel-menyen.

Den enkleste måten å komme inn i kodeditorvinduet på dette punktet er bare å klikke på Vis kode knapp under kontroller i Utvikler-menyen.

1. Horrible Variable Names

Nå som du er i kodevinduet, er det på tide å begynne å skrive VBA-kode Hvordan du kan lage din egen enkle app med VBA Hvordan du kan lage din egen enkle app med VBA For de av dere som virkelig vil elske å kunne skrive din egen applikasjon, men har aldri skrevet en enkelt kodekode før, jeg skal gå gjennom deg og gjøre deg veldig ... Les mer. Det første viktige trinnet i de fleste programmer, enten det er i VBA eller et annet språk, er å definere variablene dine.

Gjennom mine tiår med kodeskriving, kom jeg over mange tankegang når det gjelder variabel navngivningskonvensjoner og lærte noen ting på den harde måten. Her er de raske tipsene for å lage variable navn:

• Gjør dem så korte som mulig.
• Gjør dem så beskrivende som mulig.
• Forord dem med variabel type (boolean, heltall, etc ...).
• Husk å bruke riktig omfang (se nedenfor).

Her er et eksempel skjermbilde fra et program som jeg ofte bruker til å lage WMIC Windows-anrop fra Excel for å samle PC-informasjon Slik ser du all din PC-informasjon ved hjelp av et enkelt Excel VBA-script Slik ser du all din PC-informasjon ved hjelp av en enkel Excel VBA Script Excel og 10 minutter med arbeid gir deg mer detaljert informasjon om datamaskinen din enn du trodde var mulig. Høres for godt ut til å være sant? Det er Windows, hvis du vet hvordan du bruker det. Les mer .

Når du vil bruke variablene inne i en hvilken som helst funksjon inne i modulen eller objektet (jeg vil forklare dette under), må du deklarere det som en “offentlig” variabel ved prefacing erklæringen med Offentlig. Ellers blir variabler deklarert ved å prefacing dem med ordet dim.

Som du kan se, hvis variabelen er et heltall, er det prefaced med int. Hvis det er en streng, så str. Dette hjelper senere mens du programmerer, fordi du alltid vet hvilken type data variabelen holder, bare ved å blinke på navnet. Du vil også legge merke til at hvis det er noe som en streng som inneholder et datamaskinnavn, blir variabelen kalt strComputerName.

Unngå å gjøre veldig innviklede eller forvirrende variabelnavn som bare du forstår. Gjør det lettere for en annen programmerer å komme bak deg og forstå hva det betyr!

En annen feil folk gjør er å forlate arknavn som standard “Sheet1”, “Ark2”, etc ... Dette gir ytterligere forvirring til et program. I stedet oppgir arkene slik at de gir mening.

På denne måten når du refererer til arknavnet i Excel VBA-koden, bruker du VBA til å automatisere Internet Explorer-økter fra et Excel-regneark ved hjelp av VBA for å automatisere Internet Explorer-økter fra et Excel-regneark. Integrasjonen med Windows tillater kontroll over Internet Explorer i en rekke overraskende måter å bruke Visual Basic for Applications (VBA) skript fra ethvert program som støtter det, for eksempel Word, Outlook eller Excel. Les mer, du henviser til et navn som gir mening. I eksemplet ovenfor har jeg et ark hvor jeg drar inn Nettverksinformasjon, så jeg ringer arket “Network”. Nå i koden, når som helst jeg vil referere til nettverksarket, kan jeg gjøre det raskt uten å se på hvilket arknummer det er.

2. Bryte i stedet for å løkke

En av de vanligste problemene som nyere programmer har, har 6 livsvaner som programmering kan lære deg i dag. 6 Livsvaner at programmering kan lære deg i dag Alt viktig som du trenger å vite om å leve et vellykket liv, kan du få fra et dataprogram. Tro ikke på meg? Les videre. Les mer når de begynner å skrive kode, håndterer løkkene riktig. Og siden så mange som bruker Excel VBA, er veldig mye kode nybegynnere, er dårlig looping epidemi.

Looping er svært vanlig i Excel, fordi du ofte behandler dataverdier ned en hel rad eller en kolonne, så du må sløyfe for å behandle dem alle. Nye programmører vil ofte bare bryte ut av en loop (enten en For-loop eller et Mens-blikk) umiddelbart når en viss tilstand er sant.

Du kan ignorere kompleksiteten til koden ovenfor, bare vær oppmerksom på at i den indre IF-setningen er det et alternativ å gå ut av For-løkken hvis tilstanden er sant. Her er et enklere eksempel:

For x = 1 til 20 Hvis x = 6 Deretter Avslutt For y = x + intRoomTemp Neste jeg 

Nye programmerere tar denne tilnærmingen fordi det er enkelt. Når en tilstand oppstår som du venter på for å avslutte en sløyfe, er fristelsen til å bare hoppe ut av den sterk, men gjør det ikke.

Oftere enn ikke, koden som kommer etter det “gå i stykker” er viktig å behandle, selv sist gang gjennom løkken før du avslutter. En mye renere og mer profesjonell måte å håndtere forhold der du ønsker å legge en løkke halvveis gjennom, er bare å inkludere den avslutte tilstanden i noe som en While statement.

Mens (x> = 1 og x<=20 AND x<>6) For x = 1 til 20 y = x + intRoomTemp Neste jeg venter 

Dette gjør det mulig for en logisk strøm av koden din, med den siste gjennomløp når x er 5, og deretter gledelig utgang når For-løkken teller opp til 6. Ikke nødvendig å inkludere klossete EXIT- eller BREAK-kommandoer midtveis.

3. Ikke bruk arrays

En annen interessant feil som nye VBA-programmerere Grunnleggende om dataprogrammering 101 - Variabler og datatyper Grunnleggende om dataprogrammering 101 - Variabler og datatyper Etter å ha introdusert og snakket litt om Objektorientert Programmering før og hvor dets navnebringer kommer fra, trodde jeg det var på tide vi gå gjennom det absolutt grunnleggende for programmering på en ikke-språkspesifikk måte. Dette ... Les mer gjør forsøke å behandle alt inne i en rekke nestede løkker som filtrerer ned gjennom rader og kolonner under beregningsprosessen.

Selv om dette kan fungere, kan det også føre til store ytelsesproblemer, hvis du hele tiden må utføre de samme beregningene på de samme tallene i samme kolonne. Looping gjennom denne kolonnen og utvinning verdiene hver gang er ikke bare kjedelig å programmere, det er en morder på prosessoren din. En mer effektiv måte å håndtere lange lister over tall på er å benytte en matrise.

Hvis du aldri har brukt en matrise før, ikke vær redd. Tenk deg en matrise som en isbitbakke med et visst antall “kuber” Du kan legge inn informasjon i. Kubene er nummerert 1 til 12, og det er slik du “sette” data inn i dem.

Du kan enkelt definere en matrise bare ved å skrive Dim arrMyArray (12) som helhet.

Dette skaper en “brett” med 12 spor tilgjengelig for deg å fylle opp.

Her ser du en radsløyfekode uten en matrise:

Undertest1 () Dim x Som helhet intNumRows = Range ("A2", Range ("A2"). End (xldown)) .Rows.Count Range ("A2"). Velg For x = 1 Til IntNumRows Hvis Range En "& str (x)). Verdi < 100 then intTemp = (Range("A" & str(x)).value) * 32 - 100 End If ActiveCell.Offset(1, 0).Select Next End Sub 

I dette eksemplet behandles koden ned gjennom hver enkelt celle i området og utfører temperaturberegningen.

Senere i programmet, hvis du noen gang vil utføre en annen beregning på disse samme verdiene, må du duplisere denne koden, prosessere gjennom alle disse cellene, og utføre den nye beregningen.

Nå, hvis du i stedet bruker en matrise, kan du lagre de 12 verdiene i raden i ditt praktiske lagringsarray. Så senere, når du vil kjøre beregninger mot disse tallene, er de allerede i minne og klar til å gå.

Undertest1 () Dim x Som helhet intNumRows = Range ("A2", Range ("A2"). End (xldown)) .Rows.Count Range ("A2"). Velg For x = 1 Til intNumRows arrMyArray (x- 1) = Range ("A" & str (x)). Verdi) ActiveCell.Offset (1, 0) .Velg Next End Sub 

De “x-1” for å peke på arrayelementet er bare nødvendig fordi For-loop starter på 1. Array-elementer må starte ved 0.

Men når du har din matrise alt lastet opp med verdiene fra raden, senere i programmet kan du bare piske sammen alle beregninger du vil ha ved å bruke matrisen.

Sub TempCalc () For x = 0 Til UBound (arrMyArray) arrMyTemps (y) = arrMyArray (x) * 32 - 100 Neste End Sub 

Dette eksempelet går gjennom hele raden (UBound gir deg antall dataværdier i arrayet), gjør temperaturberegningen, og legger den deretter inn i en annen array kalt arrMyTemps.

Du kan se hvor mye enklere den andre beregningskoden er. Og fra dette tidspunktet, når du vil utføre flere beregninger på samme sett med tall, er arrayet allerede forhåndsinstallert og klar til å gå.

4. Bruk av for mange referanser

Enten du programmerer i fullverdig Visual Basic eller VBA, må du inkludere “referanser” for å få tilgang til bestemte funksjoner, for eksempel å få tilgang til en Access-database, eller skrive utdata til en tekstfil.

Referanser er som om “biblioteker” fylt med funksjonalitet som du kan tappe inn hvis du aktiverer den filen. Du kan finne Referanser i utviklervisning ved å klikke på Verktøy i menyen, og klikk deretter på referanser.

Hva du finner i dette vinduet er alle de aktuelt valgte referansene for ditt nåværende VBA-prosjekt.

I min erfaring kan valgene her skifte fra en Excel-installasjon til en annen, og fra en PC til en annen. Mye avhenger av hva andre har gjort med Excel på denne PCen, om enkelte tilleggsprogrammer eller funksjoner ble lagt til eller aktivert, eller om noen andre programmerere kanskje har brukt visse referanser i tidligere prosjekter.

Grunnen til at det er bra å sjekke denne listen, er fordi unødvendige referanser kaster bort systemressurser. Hvis du ikke bruker XML-filmanipulering, hvorfor behold deretter Microsoft XML valgt? Hvis du ikke kommuniserer med en database, fjerner du Microsoft DAO. Hvis du ikke skriver utdata til en tekstfil, fjerner du deretter Microsoft Scripting Runtime.

Hvis du ikke er sikker på hva disse valgte referansene gjør, trykker du på F2 og du vil se Objekt Explorer. Øverst i dette vinduet kan du velge referansebiblioteket for å bla gjennom.

Når du har valgt, ser du alle objekter og tilgjengelige funksjoner, som du kan klikke på for å lære mer om.

Når jeg for eksempel klikker på DAO-biblioteket, blir det raskt klart at dette handler om å koble til og kommunisere med databaser.

Å redusere antall referanser du bruker i programmeringsprosjektet, er bare god fornuft, og vil bidra til at din samlede applikasjon løper mer effektivt.

Programmering i Excel VBA

Hele ideen om faktisk å skrive kode i Excel Lagringstid med tekstoperasjoner i Excel Lagringstid med tekstoperasjoner i Excel Excel kan gjøre magi med tall og det kan håndtere tegn like bra. Denne håndboken viser hvordan du analyserer, konverterer, erstatter og redigerer tekst i regneark. Disse grunnleggende vil tillate deg å utføre komplekse transformasjoner. Les mer skremmer mange mennesker, men denne frykten er egentlig ikke nødvendig. Visual Basic for Applications er et veldig enkelt språk å lære, og hvis du følger de grunnleggende vanlige praksisene som er nevnt ovenfor, vil du sørge for at koden din er ren, effektiv og lett å forstå.

Kode du i VBA? Hva slags leksjoner har du lært gjennom årene som du kan dele med andre lesere som lærer VBA for første gang? Del tipsene dine i kommentarfeltet nedenfor!

Bildekreditter: Computer ved www.BillionPhotos.com via Shutterstock, Colorful Ice Cube Trays av hahauk via Shutterstock.com

Utforsk mer om: Microsoft Excel, Programmering, Visual Basic Programmering.