4 Fouten die u kunt vermijden bij het programmeren van Excel-macro's met VBA

Microsoft Excel is een zeer capabele data-analysetool, maar met de mogelijkheid om repetitieve taken met macro's te automatiseren. Hoe e-mails verzenden vanuit een Excel-spreadsheet VBA-scripts gebruiken E-mails verzenden vanuit een Excel-spreadsheet VBA-scripts gebruiken Met onze codesjabloon kunt u instellen up geautomatiseerde e-mails vanuit Excel met behulp van Collaboration Data Objects (CDO) en VBA-scripts. Lees Meer door het schrijven van eenvoudige code in (VBA), het is zoveel krachtiger. Echter, onjuist gebruikt, kan VBA veel problemen veroorzaken.

Zelfs als je geen programmeur bent, biedt VBA eenvoudige functies waarmee je een aantal zeer indrukwekkende functies aan je spreadsheets kunt toevoegen. Ga dus nog niet weg!

Of je nu een VBA-goeroe bent, die dashboards maakt in Excel Bouw je eigen weer-dashboard in deze Google-spreadsheet-hoofdklasse Bouw je eigen weer-dashboard in deze masterclass van Google Spreadsheet Wil je van je Google Drive-account een intelligent weeranalysesysteem maken? , en een Star Trek-fantasie uitleven? OK! Read More, of een newbie, die alleen weet hoe eenvoudige scripts moeten worden geschreven die elementaire celberekeningen uitvoeren, je kunt eenvoudige programmeertechnieken volgen, die je helpen de kansen te verbeteren om schone en foutloze code te schrijven.

Aan de slag met VBA

Als je nog niet eerder in VBA in Excel hebt geprogrammeerd, is het eigenlijk vrij eenvoudig om de hulpprogramma's voor ontwikkelaars in te schakelen. Ga gewoon naar het dossier > opties en dan Lint aanpassen. Verplaats gewoon de Ontwikkelaar commando groep van het linker paneel naar rechts.

Zorg ervoor dat het selectievakje is ingeschakeld en dat nu het tabblad Ontwikkelaar in uw Excel-menu wordt weergegeven.

De eenvoudigste manier om in dit venster van de code-editor te komen, is door gewoon op te klikken Bekijk code knop onder controls in het ontwikkelaarsmenu.

1. Verschrikkelijke variabele namen

Nu je in het codevenster bent, is het tijd om VBA-code te gaan schrijven. Hoe je je eigen eenvoudige app kunt maken met VBA Hoe je je eigen eenvoudige app kunt maken met VBA Voor degenen onder u die echt graag willen kunnen schrijven je eigen toepassing, maar je hebt nog nooit eerder een enkele regel code getypt, ik ga je begeleiden door je hele ... Lees meer. De eerste belangrijke stap in de meeste programma's, of het nu VBA is of een andere taal, is het definiëren van uw variabelen.

Tijdens mijn decennia van coderen, kwam ik veel denkrichtingen tegen als het gaat om variabele naamgevingsconventies en heb ik op de moeilijke manier een paar dingen geleerd. Dit zijn de snelle tips voor het maken van variabelenamen:

• Maak ze zo kort mogelijk.
• Maak ze zo beschrijvend mogelijk.
• Voorafloop ze met variabel type (boolean, integer, etc ...).
• Vergeet niet om de juiste scope te gebruiken (zie hieronder).

Hier is een voorbeeld van een screenshot van een programma dat ik vaak gebruik om WMIC Windows-oproepen vanuit Excel te maken om pc-informatie te verzamelen. Hoe zie ik al uw pc-informatie met een eenvoudig Excel VBA-script Hoe u al uw pc-informatie kunt bekijken met een eenvoudig Excel VBA Script Excel en 10 minuten werk geeft u meer gedetailleerde informatie over uw computer dan u ooit voor mogelijk had gehouden. Klinkt te mooi om waar te zijn? Dat is Windows, als u weet hoe u het moet gebruiken. Lees verder .

Als u de variabelen binnen een functie binnen de module of het object wilt gebruiken (ik zal dit hieronder uitleggen), dan moet u dit als een “openbaar” variabele door de aangifte vooraf te gaan met Openbaar. Anders worden variabelen gedeclareerd door ze vooraf te laten gaan met het woord verduisteren.

Zoals je kunt zien, is de variabele voorafgegaan door een, als de variabele een geheel getal is int. Als het een string is, dan str. Dit helpt later tijdens het programmeren, omdat je altijd weet welk type gegevens de variabele bevat, door alleen maar naar de naam te kijken. Je zult ook opmerken dat als er zoiets als een string is die een computernaam bevat, de variabele wordt gebeld strComputerName.

Vermijd het maken van zeer ingewikkelde of verwarrende variabelenamen die alleen jij begrijpt. Maak het makkelijker voor een andere programmeur om achter je te komen en begrijp wat het allemaal betekent!

Een andere fout die mensen maken, is het achterlaten van bladnamen als standaard “Sheet1”, “Sheet2”, enz ... Dit voegt nog meer verwarring toe aan een programma. Geef de bladen liever een naam zodat ze logisch zijn.

Op deze manier, wanneer u naar de bladnaam in uw Excel VBA-code verwijst VBA gebruiken om sessies van Internet Explorer te automatiseren vanuit een Excel-spreadsheet VBA gebruiken om sessies van Internet Explorer te automatiseren vanuit een Excel-spreadsheet Door de integratie met Windows kan Internet Explorer worden beheerd in een aantal verrassende manieren met behulp van Visual Basic for Applications (VBA) script vanuit elke toepassing die dit ondersteunt, zoals Word, Outlook of Excel. Meer lezen, je doelt op een naam die logisch is. In het bovenstaande voorbeeld heb ik een blad waarop ik netwerkinformatie ophaal, dus ik bel het blad “Netwerk”. Nu in de code, wanneer ik wil verwijzen naar het Netwerkblad, kan ik het snel doen zonder op te zoeken welk werkblad het is.

2. Breken in plaats van herhalen

Een van de meest voorkomende problemen die nieuwere programmeurs hebben 6 levens gewoonten die programmeren kan je vandaag leren 6 Levensgewoonten die programmeren je vandaag kunnen leren Alles wat belangrijk is dat je moet weten over een succesvol leven, kun je krijgen via een computerprogramma. Geloof me niet? Lees verder. Meer lezen als ze beginnen met het schrijven van code heeft op de juiste manier te maken met loops. En omdat zoveel mensen die Excel VBA gebruiken erg veel code-newbies zijn, is slecht lussen epidemisch.

Looping is heel gebruikelijk in Excel, omdat je vaak gegevenswaarden verwerkt in een hele rij of kolom, dus je moet lus maken om ze allemaal te verwerken. Nieuwe programmeurs willen vaak onmiddellijk uit een lus (ofwel een For-lus of een While-look) breken wanneer een bepaalde voorwaarde waar is.

U kunt de complexiteit van de bovenstaande code negeren, maar houd er rekening mee dat er binnen de innerlijke IF-instructie een optie is om de For-lus te verlaten als de voorwaarde waar is. Hier is een eenvoudiger voorbeeld:

Voor x = 1 tot 20 Indien x = 6 Dan Afsluiten Voor y = x + intRoomTemp Volgende i 

Nieuwe programmeurs nemen deze aanpak omdat het eenvoudig is. Wanneer een toestand optreedt waarop je wacht om een ​​lus te verlaten, is de verleiding om er gewoon meteen uit te springen sterk, maar doe het niet.

Vaker wel dan niet, de code die daarna komt “breken” is belangrijk om te verwerken, zelfs de laatste keer door de lus voor het afsluiten. Een veel schonere en professionelere manier om om te gaan met omstandigheden waar je halverwege een lus wilt laten, is gewoon om die exit-voorwaarde op te nemen in zoiets als een While-statement.

Terwijl (x> = 1 AND x<=20 AND x<>6) Voor x = 1 tot 20 y = x + intRoomTemp Volgende i Wend 

Dit zorgt voor een logische stroom van uw code, met de laatste doorloop wanneer x 5 is, en vervolgens gracieus op wanneer de For-lus telt tot 6. Geen noodzaak om lastige EXIT- of BREAK-opdrachten midden in de lus in te lassen.

3. Geen arrays gebruiken

Een andere interessante fout die nieuwe VBA-programmeurs De grondbeginselen van computerprogrammering 101 - Variabelen en datatypes De grondbeginselen van computerprogrammeren 101 - Variabelen en gegevenstypes Nadat ik iets over objectgeoriënteerd programmeren geïntroduceerd en besproken heb voor en waar zijn naamgenoot vandaan komt, vond ik het tijd dat we door de absolute basis van programmeren gaan op een niet-taalspecifieke manier. This ... Read More make is bezig om alles binnen meerdere geneste lussen te verwerken die door rijen en kolommen filteren tijdens het berekeningsproces.

Hoewel dit kan werken, kan dit ook tot grote prestatieproblemen leiden, als u constant dezelfde berekeningen op dezelfde nummers in dezelfde kolom moet uitvoeren. Het doorlopen van die kolom en het elke keer extraheren van de waarden is niet alleen saai om te programmeren, het is een moordenaar op je processor. Een efficiëntere manier om lange lijsten met getallen te verwerken, is om een ​​array te gebruiken.

Als je nog nooit een array hebt gebruikt, wees dan niet bang. Stel je een array voor als een ijsblokjesplateau met een bepaald aantal “kubussen” je kunt informatie invoegen. De kubussen zijn genummerd van 1 tot 12, en dat is hoe jij “leggen” gegevens in hen.

U kunt eenvoudig een array definiëren door gewoon te typen Dim arrMyArray (12) als Integer.

Dit creëert een “dienblad” met 12 slots beschikbaar voor u om te vullen.

Dit is wat een rij-luscode zonder een array er als volgt uitziet:

Subtest1 () Dim x Als geheel getal intNumRows = Bereik ("A2", Bereik ("A2"). Einde (xldown)). Rijen.Countbereik ("A2"). Selecteren voor x = 1 Voor intNumRows If Range (" Een "& str (x)). Waarde < 100 then intTemp = (Range("A" & str(x)).value) * 32 - 100 End If ActiveCell.Offset(1, 0).Select Next End Sub 

In dit voorbeeld wordt de code verwerkt door elke cel in het bereik en voert de temperatuurberekening uit.

Later in het programma, als u ooit een andere berekening op dezelfde waarden wilt uitvoeren, moet u deze code dupliceren, alle cellen verwerken en uw nieuwe berekening uitvoeren..

Als u in plaats daarvan een array gebruikt, kunt u de 12 waarden in de rij opslaan in uw handige opslagarray. Later, wanneer u berekeningen wilt uitvoeren met die getallen, staan ​​ze al in het geheugen en zijn ze klaar voor gebruik.

Subtest1 () Dim x Als geheel getal intNumRows = Bereik ("A2", Bereik ("A2"). Einde (xldown)). Rows.Count Range ("A2"). Selecteren voor x = 1 Voor intNumRows arrMyArray (x- 1) = Bereik ("A" & str (x)). Waarde) ActiveCell.Offset (1, 0). Selecteer Next End Sub 

De “x-1” voor het wijzen naar het array-element is alleen nodig omdat de For-lus begint bij 1. Array-elementen moeten beginnen bij 0.

Maar als u eenmaal uw array hebt geladen met de waarden uit de rij, kunt u later in het programma alle gewenste berekeningen samenvoegen met behulp van de array.

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

Dit voorbeeld doorloopt de volledige rij array (UBound geeft u het aantal gegevenswaarden in de array), voert de temperatuurberekening uit en plaatst deze in een andere array met de naam arrMyTemps.

U kunt zien hoeveel eenvoudiger de tweede berekeningscode is. En vanaf dat moment is uw array al geladen en klaar om te gaan wanneer u meer berekeningen met dezelfde reeks getallen wilt uitvoeren..

4. Te veel verwijzingen gebruiken

Of je nu programmeert in een volwaardige Visual Basic of VBA, je moet het opnemen “referenties” voor toegang tot bepaalde functies, zoals toegang tot een Access-database of het schrijven van uitvoer naar een tekstbestand.

Referenties zijn ongeveer hetzelfde “bibliotheken” vol met functionaliteit waar u gebruik van kunt maken, als u dat bestand inschakelt. U vindt verwijzingen in de ontwikkelaarsweergave door op te klikken Hulpmiddelen in het menu en klik vervolgens op Referenties.

Wat u in dit venster vindt, zijn alle momenteel geselecteerde referenties voor uw huidige VBA-project.

Naar mijn ervaring kunnen de selecties hier veranderen van de ene Excel-installatie naar de andere en van de ene pc naar de andere. Veel hangt af van wat andere mensen met Excel op deze pc hebben gedaan, of bepaalde add-ons of functies zijn toegevoegd of ingeschakeld, of dat sommige andere programmeurs bepaalde referenties in eerdere projecten hebben gebruikt.

De reden dat het goed is om deze lijst te controleren, is omdat onnodige verwijzingen systeembronnen verspillen. Als u geen XML-bestandsmanipulatie gebruikt, waarom zou u dan Microsoft XML geselecteerd houden? als u niet communiceert met een database, verwijdert u Microsoft DAO. Als u geen uitvoer naar een tekstbestand schrijft, verwijder dan Microsoft Scripting Runtime.

Als u niet zeker weet wat deze geselecteerde referenties doen, drukt u op F2 en je ziet de Objectverkenner. Aan de bovenkant van dit venster kunt u de referentiebibliotheek kiezen om te bladeren.

Nadat u deze hebt geselecteerd, ziet u alle objecten en beschikbare functies, waar u op kunt klikken voor meer informatie.

Wanneer ik bijvoorbeeld op de DAO-bibliotheek klik, wordt snel duidelijk dat het hier gaat om het verbinden met en communiceren met databases.

Het is verstandig om het aantal referenties dat u gebruikt in uw programmeerproject te verminderen en uw algemene toepassing efficiënter te laten verlopen.

Programmeren in Excel VBA

Het hele idee van het daadwerkelijk schrijven van code in Excel Saving Time met tekstbewerkingen in Excel Saving Time with Text Operations in Excel Excel kan magie met getallen doen en het kan evengoed met tekens omgaan. Deze handleiding laat zien hoe u tekst in spreadsheets kunt analyseren, converteren, vervangen en bewerken. Met deze basisprincipes kunt u complexe transformaties uitvoeren. Meer lezen maakt veel mensen bang, maar deze angst is echt niet nodig. Visual Basic for Applications is een heel eenvoudige taal om te leren, en als je de gebruikelijke basispraktijken zoals hierboven vermeld volgt, zorg je ervoor dat je code schoon, efficiënt en gemakkelijk te begrijpen is..

Heb je code in VBA? Welke lessen heb je de afgelopen jaren geleerd die je kunt delen met andere lezers die voor het eerst VBA leren? Deel uw tips in de opmerkingen hieronder!

Afbeelding credits: Computer door www.BillionPhotos.com via Shutterstock, Kleurrijke Ice Cube Trays door hahauk via Shutterstock.com

Ontdek meer over: Microsoft Excel, Programmeren, Visual Basic Programming.