4 fejl, du kan undgå, når du programmerer Excel-makroer med VBA

Microsoft Excel er allerede et meget kapabelt dataanalyseværktøj, men med evnen til at automatisere gentagne opgaver med makroer Sådan sendes e-mails fra et Excel-regneark ved hjælp af VBA-scripts Sådan sendes e-mails fra et Excel-regneark ved hjælp af VBA-scripts Vores kodeskabelon hjælper dig med at indstille op automatiske e-mails fra Excel ved hjælp af Collaboration Data Objects (CDO) og VBA-scripts. ved at skrive enkel kode i (VBA), er det så meget mere kraftfuld. Dog brugt forkert kan VBA forårsage en masse problemer.

Selvom du ikke er en programmør, tilbyder VBA enkle funktioner, der giver dig mulighed for at tilføje nogle virkelig imponerende funktionalitet til dine regneark, så lad ikke være lige nu!

Uanset om du er en VBA-guru, der opretter dashboards i Excel Byg dit eget vejr Dashboard i dette Google Regneark Master Class Byg dit eget vejr Dashboard i dette Google Regneark Master Class Ønsker du at omdanne din Google Drive-konto til et intelligent vejranalysesystem , og leve ud af en Star Trek-fantasi? Okay! , eller en nybegynder, der kun ved, hvordan man skriver enkle manuskripter, der laver grundlæggende celleberegninger, kan du følge lette programmeringsteknikker, som vil hjælpe dig med at forbedre oddsene for at skrive ren og fejlfri kode.

Kom godt i gang med VBA

Hvis du ikke har programmeret i VBA i Excel før, er det faktisk temmelig let at aktivere Developer-værktøjer til at gøre det. Bare gå til Fil > Muligheder og så Tilpas bånd. Flyt bare Udvikler kommandogruppe fra venstre rude over til højre.

Sørg for, at afkrydsningsfeltet er aktiveret, og nu vises fanen Udvikler i din Excel-menu.

Den nemmeste måde at komme ind i vinduet med kodeditor på dette tidspunkt er bare at klikke på Se kode knappen under Controls i menuen Udvikler.

1. Frygtelige variablenavne

Nu når du er i kodevinduet, er det tid til at begynde at skrive VBA-kode Hvordan du kan lave din egen enkle app med VBA Hvordan du kan lave din egen enkle app med VBA For dem af jer der virkelig ville elske at kunne skrive din egen applikation, men har aldrig skrevet en enkelt kodelinie før, jeg går igennem dig ved at gøre dit meget ... Det første vigtige trin i de fleste programmer, hvad enten det er i VBA eller et andet sprog, er at definere dine variabler.

Gennem mine par årtier med kodeskrivning stødte jeg på mange tankeskoler, når det kommer til forskellige navnekonventioner og lærte et par ting på den hårde måde. Her er de hurtige tip til oprettelse af variabelnavne:

• Gør dem så korte som muligt.
• Gør dem så beskrivende som muligt.
• Forord dem med variabel type (boolsk, heltal osv. ...).
• Husk at bruge det rigtige omfang (se nedenfor).

Her er et eksempelskærmbillede fra et program, som jeg ofte bruger til at oprette WMIC Windows-opkald fra Excel for at indsamle pc-oplysninger Sådan kan du se alle dine pc-oplysninger ved hjælp af et simpelt Excel VBA-script Sådan kan du se alle dine pc-oplysninger ved hjælp af et enkelt Excel VBA-script Excel og 10 minutters arbejde vil give dig mere detaljerede oplysninger om din computer, end du nogensinde troede muligt. Lyder det for godt til at være sandt? Det er Windows, hvis du ved, hvordan du bruger det. .

Når du vil bruge variablerne inde i en hvilken som helst funktion inde i modulet eller objektet (jeg vil forklare dette nedenfor), skal du erklære det som en “offentlig” variabel ved at forkaste erklæringen med Offentlig. Ellers erklæres variabler ved at forpasse dem med ordet Svag.

Som du kan se, hvis variablen er et heltal, er den forudindstillet med int. Hvis det er en streng, så str. Dette hjælper senere, mens du programmerer, fordi du altid ved, hvilken type data variablen har, bare ved at kigge på navnet. Du vil også bemærke, at hvis der er noget som en streng, der indeholder et computernavn, kaldes variablen strComputerName.

Undgå at lave meget indviklede eller forvirrende variabelnavne, som kun du forstår. Gør det lettere for en anden programmør at komme bag dig og forstå, hvad det hele betyder!

En anden fejl, folk begår, er at efterlade arknavne som standard “Ark1”, “Ark2”, osv ... Dette tilføjer et program yderligere forvirring. Navngiv i stedet arkene, så de giver mening.

På denne måde, når du henviser til arknavnet i din Excel VBA-kode Brug af VBA til at automatisere Internet Explorer-sessioner fra et Excel-regneark Brug af VBA til at automatisere Internet Explorer-sessioner fra et Excel-regneark Dets integration med Windows giver kontrol over Internet Explorer i et antal overraskende måder at bruge Visual Basic for Applications (VBA) script fra ethvert program, der understøtter det, såsom Word, Outlook eller Excel. , du henviser til et navn, der giver mening. I eksemplet ovenfor har jeg et ark, hvor jeg henter netværksinformation, så jeg kalder arket “Netværk”. Nu i koden, når som helst jeg vil henvise til netværksarket, kan jeg gøre det hurtigt uden at slå op, hvilket arknummer det er.

2. Bryde i stedet for at løbe

Et af de mest almindelige problemer, nyere programmør har 6 livsvaner, som programmeringen kunne lære dig i dag. 6 livsvaner, at programmeringen kunne lære dig i dag. Alt, hvad du har brug for at vide om at leve et vellykket liv, kan du få fra et computerprogram. Tro mig ikke? Læs videre. når de begynder at skrive kode, er det korrekt at håndtere sløjfer. Og da så mange mennesker, der bruger Excel VBA, meget nybegynder kode, er dårlig looping epidemi.

Looping er meget almindeligt i Excel, fordi du ofte behandler dataværdier ned ad en hel række eller en kolonne, så du er nødt til at sløjfe for at behandle dem alle. Nye programmerere ønsker ofte bare at bryde ud af en løkke (enten et For-loop eller et While-look) med det samme, når en bestemt betingelse er sandt.

Du kan ignorere kompleksiteten af ​​koden ovenfor, bare bemærke, at inden i den indre IF-sætning, er der en mulighed for at forlade For-loop, hvis betingelsen er sand. Her er et enklere eksempel:

For x = 1 til 20 Hvis x = 6 Afslut derefter for y = x + intRoomTemp Næste i 

Nye programmerere tager denne tilgang, fordi det er let. Når der opstår en tilstand, som du venter på for at afslutte en løkke, er fristelsen til bare øjeblikkeligt at hoppe ud af den stærk, men gør det ikke.

Oftere end ikke, den kode, der kommer efter det “pause” er vigtig at behandle, også sidste gang gennem løkken, før du afslutter. En meget renere og mere professionel måde at håndtere forhold, hvor du vil forlade en løkke halvvejs igennem, er bare at inkludere denne udgangsbetingelse i noget som en While-erklæring.

Mens (x> = 1 OG x<=20 AND x6) For x = 1 To 20 y = x + intRoomTemp Next i Wend 

Dette giver mulighed for en logisk strøm af din kode, med den sidste gennemløb, når x er 5, og derefter grådigt afslutter, når For-loop tæller op til 6. Ingen grund til at inkludere akavet EXIT eller BREAK-kommandoer mid-loop.

3. Brug ikke arrays

En anden interessant fejl, som nye VBA-programmerere Grundlæggende om computerprogrammering 101 - variabler og datatyper Grundlæggende om computerprogrammering 101 - variabler og datatyper Efter at have introduceret og talt lidt om Objektorienteret programmering før, og hvor dens navnebror kommer fra, troede jeg, det er på tide, at vi gå gennem de absolutte grundlæggende programmering på en ikke-sprogspecifik måde. Dette ... fabrikat forsøger at behandle alt inde i adskillige indlejrede løkker, der filtrerer ned gennem rækker og kolonner under beregningsprocessen.

Selvom dette kan fungere, kan det også føre til store ydelsesproblemer, hvis du konstant skal udføre de samme beregninger på de samme tal i samme kolonne. At løbe igennem denne kolonne og udtrække værdierne hver eneste gang er ikke kun trættende at programmere, det er en morder på din processor. En mere effektiv måde at håndtere lange lister over numre er at bruge en matrix.

Hvis du aldrig har brugt en matrix før, skal du ikke frygte. Forestil dig en matrix som en isterbakke med et vist antal “terninger” du kan lægge oplysninger i. Terningerne er nummereret 1 til 12, og det er sådan du “sætte” data ind i dem.

Du kan nemt definere en matrix bare ved at skrive Dim arrMyArray (12) som heltal.

Dette skaber en “bakke” med 12 slots tilgængelige for dig at udfylde.

Her er, hvordan en række-looping-kode uden en matrix kan se ud:

Subtest1 () Dim x Som heltal intNumRows = Range ("A2", Range ("A2"). End (xldown)). Rows.Count Range ("A2"). Vælg For x = 1 Til intNumRows Hvis Range (" A "& str (x)). Værdi < 100 then intTemp = (Range("A" & str(x)).value) * 32 - 100 End If ActiveCell.Offset(1, 0).Select Next End Sub 

I dette eksempel behandles koden gennem hver eneste celle i området og udfører temperaturberegningen.

Senere i programmet, hvis du nogensinde vil udføre en anden beregning på disse samme værdier, bliver du nødt til at kopiere denne kode, bearbejde gennem alle disse celler og udføre din nye beregning.

Hvis du i stedet bruger en matrix, kan du gemme de 12 værdier i rækken i din praktiske opbevaringsgruppe. Senere, når du vil køre beregninger mod disse tal, er de allerede i hukommelsen og klar til at gå.

Subtest1 () Dim x Som heltal intNumRows = Område ("A2", Område ("A2"). Slut (xldown)) Rækker. Antal række ("A2"). Vælg For x = 1 Til intNumRows arrMyArray (x- 1) = Range ("A" & str (x)). Værdi) ActiveCell.Offset (1, 0) .Vælg Next Next Sub 

Det “x-1” for at pege på array-elementet er kun nødvendigt, fordi For-loop starter ved 1. Array-elementer skal starte ved 0.

Men når du først har lagt dit array sammen med værdierne fra rækken, senere i programmet, kan du bare piske sammen alle de beregninger, du ønsker, ved at bruge arrayet.

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

Dette eksempel går gennem hele række array (UBound giver dig antallet af dataværdier i arrayen), foretager temperaturberegningen og sætter det derefter i en anden matrix kaldet arrMyTemps.

Du kan se, hvor meget enklere den anden beregningskode er. Og fra dette tidspunkt, hver gang du vil udføre flere beregninger på det samme sæt numre, er dit array allerede indlæst og klar til at gå.

4. Brug for mange referencer

Uanset om du programmerer i fulde Visual Basic eller VBA, skal du medtage “referencer” for at få adgang til visse funktioner, f.eks. adgang til en Access-database, eller skrive output til en tekstfil.

Referencer er slags “biblioteker” fyldt med funktionalitet, som du kan trykke på, hvis du aktiverer denne fil. Du kan finde Referencer i udviklervisningen ved at klikke på Værktøj i menuen og derefter klikke på Referencer.

Hvad du finder i dette vindue er alle de aktuelt valgte referencer til dit aktuelle VBA-projekt.

Efter min erfaring kan valgene her ændres fra en Excel-installation til en anden og fra en pc til en anden. Meget afhænger af, hvad andre mennesker har gjort med Excel på denne pc, om visse tilføjelser eller funktioner blev tilføjet eller aktiveret, eller om nogle andre programmerere muligvis har brugt visse referencer i tidligere projekter.

Årsagen til at det er godt at tjekke denne liste er fordi unødvendige referencer til affaldssystemressourcer. Hvis du ikke bruger nogen XML-filmanipulation, hvorfor hvorfor holde Microsoft XML valgt? Hvis du ikke kommunikerer med en database, skal du fjerne Microsoft DAO. Hvis du ikke skriver output til en tekstfil, skal du fjerne det Microsoft scripting kørsel.

Hvis du ikke er sikker på, hvad disse valgte referencer gør, skal du trykke på F2 og du vil se Object Explorer. Øverst i dette vindue kan du vælge det referencebibliotek, du vil gennemse.

Når det er valgt, vil du se alle objekter og tilgængelige funktioner, som du kan klikke på for at lære mere om.

Når jeg for eksempel klikker på DAO-biblioteket, bliver det hurtigt klart, at det hele handler om at oprette forbindelse til og kommunikere med databaser.

At reducere antallet af referencer, du bruger i dit programmeringsprojekt, er bare god mening og vil hjælpe med at gøre din samlede applikation mere effektiv.

Programmering i Excel VBA

Hele ideen om faktisk at skrive kode i Excel Spare tid med tekstoperationer i Excel Spare tid med tekstbetjeninger i Excel Excel kan gøre magi med tal, og det kan håndtere tegn lige så godt. Denne manual viser, hvordan man analyserer, konverterer, udskifter og redigerer tekst i regneark. Disse grundlæggende giver dig mulighed for at udføre komplekse transformationer. skræmmer en masse mennesker, men denne frygt er virkelig ikke nødvendig. Visual Basic til applikationer er et meget simpelt sprog at lære, og hvis du følger de almindelige almindelige fremgangsmåder nævnt ovenfor, vil du sikre dig, at din kode er ren, effektiv og let at forstå..

Koder du i VBA? Hvilken slags lektioner har du lært gennem årene, som du kan dele med andre læsere, der lærer VBA for første gang? Del dine tip i kommentarfeltet nedenfor!

Billedkreditter: Computer af www.BillionPhotos.com via Shutterstock, Farverige isterbakker fra hahauk via Shutterstock.com