Excel-cellen versus bereikfuncties in VBA

Excel is krachtig. Als je het veel gebruikt, weet je waarschijnlijk al veel trucs met formules of autoformattering, maar gebruik je Cellen en reeks functies in VBA, kunt u uw Excel-analyses naar een heel nieuw niveau tillen.

Het probleem met het gebruik van de functies Cellen en Bereik in VBA is dat op de geavanceerde niveaus de meeste mensen moeite hebben om te begrijpen hoe deze functies daadwerkelijk werken. Het gebruik ervan kan zeer verwarrend zijn. Hier leest u hoe u ze kunt gebruiken op manieren die u zich waarschijnlijk nooit hebt voorgesteld.

De cellen-functie

Met de functies Cells en Range kunt u uw VBA-script herkennen 4 Fouten die u kunt vermijden bij het programmeren van Excel-macro's met VBA 4 fouten die u kunt vermijden bij het programmeren van Excel-macro's met VBA Eenvoudige code en macro's zijn de sleutels tot Microsoft Excel-superkrachten. Zelfs niet-programmeurs kunnen gemakkelijk indrukwekkende functionaliteit toevoegen aan hun spreadsheets met Virtual Basics for Applications (VBA). Vermijd gewoon deze programmeerfouten voor beginners! Lees Meer precies waar op uw werkblad u wilt verkrijgen, of plaats gegevens. Het belangrijkste verschil tussen de twee cellen is waarnaar ze verwijzen.

Cellen meestal verwijzen naar een enkele cel tegelijk, terwijl reeks verwijst tegelijkertijd naar een groep cellen. Het formaat voor deze functie is Cellen (rij, kolom).

Dit verwijst naar elke cel in het hele blad. Het is het enige voorbeeld waarbij de functie Cellen niet verwijst naar een enkele cel:

Worksheets ( "Sheet1"). Cells

Dit verwijst naar de derde cel van links, van de bovenste rij. Cel C1:

Worksheets ( "Sheet1"). Cells (3)

De volgende code verwijst naar cel D15:

Worksheets ( "Sheet1"). Cells (15,4)

Als je dat wilt, kun je ook verwijzen naar cel D15 met “Cellen (15,”D”)”-je mag de kolomletter gebruiken.

Er is veel flexibiliteit in het kunnen verwijzen naar een cel met een nummer voor kolom en cel, vooral met scripts die dat wel kunnen doorlussen een groot aantal cellen (en berekeningen uit te voeren) zeer snel. Hieronder zullen we hieronder meer in detail treden.

De bereikfunctie

In veel opzichten is de Range-functie veel krachtiger dan het gebruik van cellen, omdat je hiermee in één keer kunt verwijzen naar een enkele cel of een specifiek cellenbereik. Je zult geen Range-functie willen doorlopen, omdat de referenties voor cellen geen getallen zijn (tenzij je de Cells-functie erin insluit).

Het formaat voor deze functie is Bereik (cel # 1, cel # 2). Elke cel kan worden aangeduid met een letternummer.

Laten we een paar voorbeelden bekijken.

Hier verwijst de bereikfunctie naar cel A5:

Worksheets ( "Sheet1"). Range ( "A5")

Hier verwijst de bereikfunctie naar alle cellen tussen A1 en E20:

Worksheets ( "Sheet1") Range. ( "A1: E20")

Zoals hierboven vermeld, hoeft u geen celtaken met nummer letters te gebruiken. U zou eigenlijk twee Cells-functies in een bereikfunctie kunnen gebruiken om een ​​bereik op het blad te identificeren, zoals dit:

 Met werkbladen ("Blad1") .Range (.Cells (1, 1), _ .Cells (20, 5)) Einde met 

De bovenstaande code verwijst naar hetzelfde bereik als het bereik (“A1: E20”) functie doet. De waarde van het gebruik ervan, is dat het je in staat zou stellen om code te schrijven die dynamisch werkt met ranges met behulp van loops. How Do-while loops werken in computerprogrammering Hoe doen-while loops werken in computerprogrammering Loops zijn een van de eerste soorten besturingselementen die je gebruikt Ik zal leren in programmeren. Je weet waarschijnlijk wel over while en for loops, maar wat bereikt een do-while-lus? Lees verder .

Nu u begrijpt hoe u de functies Cellen en Bereik moet formatteren, laten we u eens kijken hoe u deze functies op een creatieve manier kunt gebruiken in uw VBA-code.

Gegevens verwerken met Cells-functie

De functie Cellen is vooral handig als u een complexe formule hebt die u over meerdere celbereiken wilt uitvoeren. Deze bereiken kunnen ook op meerdere bladen voorkomen.

Laten we een eenvoudig voorbeeld nemen. Stel dat u een verkoopteam van 11 mensen beheert en elke maand wilt u naar hun prestaties kijken.

Misschien heb je dat wel gedaan Sheet1 die hun aantal verkopen en hun verkoopvolume bijhoudt.

Op Sheet2 Hier kunt u hun feedbackscore voor de afgelopen 30 dagen volgen van de klanten van uw bedrijf.

Als u de bonus op het eerste blad wilt berekenen met behulp van waarden van de twee bladen, zijn er meerdere manieren om dit te doen. U kunt een formule schrijven in de eerste cel die de berekening uitvoert met gegevens op de twee bladen en deze naar beneden sleept. Dat zal werken.

Een alternatief hiervoor is het maken van VBA-script dat u activeert om uit te voeren wanneer u het werkblad opent of dat wordt geactiveerd door een opdrachtknop op het werkblad, zodat u kunt bepalen wanneer het wordt berekend. U kunt hoe dan ook een VBA-script gebruiken om alle verkoopgegevens uit een extern bestand op te halen.

Dus waarom zou u in dat geval niet alleen de berekeningen voor de bonuskolom in hetzelfde script activeren??

De cellenfunctie in actie

Als je nog nooit VBA in Excel hebt geschreven, moet je het menu-item Ontwikkelaar inschakelen. Ga hiervoor naar het dossier > opties. Klik op Lint aanpassen. Kies tot slot Ontwikkelaar in het linkerdeelvenster, Toevoegen het naar het rechterdeelvenster en zorg dat het selectievakje is ingeschakeld.

Nu, wanneer u klikt OK en ga terug naar het hoofdblad, je ziet de menu-optie Ontwikkelaar.

U kunt de invoegen om een ​​opdrachtknop in te voegen of klik gewoon op Bekijk code om te beginnen met coderen.

In dit voorbeeld laten we het script uitvoeren telkens wanneer de werkmap wordt geopend. Klik hiertoe om dit te doen Bekijk code in het ontwikkelaarsmenu en plak de volgende nieuwe functie in het codevenster.

Private Sub Workbook_Open () End Sub

Uw codevenster zal er ongeveer zo uitzien.

Nu bent u klaar om de code te schrijven voor de berekening. Met behulp van een enkele lus kunt u alle 11 werknemers doorlopen en met de functie Cellen de drie variabelen invoegen die nodig zijn voor de berekening.

Onthoud dat de functie Cellen rij en kolom heeft als parameters om elke individuele cel te identificeren. We zullen maken “X” gebruik de rij om een ​​nummer te gebruiken om de gegevens van elke kolom op te vragen. Het aantal rijen is het aantal werknemers, dus dit is van 1 tot 11. De kolom-ID is 2 voor Verkooptelling, 3 voor Verkoopvolume en 2 van Blad 2 voor Feedbackscore.

De uiteindelijke berekening gebruikt de volgende percentages om tot 100 procent van de totale bonusscore bij elkaar op te tellen. Het is gebaseerd op een ideale verkoopaantal van 50, een verkoopvolume van $ 50.000 en een feedbackscore van 10.

• (Aantal verkopen / 50) x 0,4
• (Verkoopvolume / 50.000) x 0,5
• (Feedbackscore / 10) x 0,1

Deze eenvoudige aanpak geeft verkoopmedewerkers een gewogen bonus. Voor een telling van 50, een volume van $ 50.000 en een score van 10 krijgen ze de volledige maximumbonus voor de maand. Hoe dan ook, alles onder de perfectie van een factor vermindert de bonus. Alles beter dan ideaal verhoogt de bonus.

Laten we nu kijken hoe al die logica kan worden afgetrokken in een heel eenvoudig, kort VBA-script:

Private Sub Workbook_Open () Voor x = 2 tot 12 werkbladen ("Sheet1"). Cellen (x, 4) = (Werkbladen ("Sheet1"). Cellen (x, 2) .Waarde / 50) * 0.4 _ + (Werkbladen ("Sheet1") Cellen (x, 3) .Waarde / 50000) * 0,5 _ + (Werkbladen ("Sheet2"). Cellen (x, 2) .Waarde / 10) * 0,1 _ Volgende x End Sub 

Dit is hoe de uitvoer van dit script eruit zal zien.

Als u wilt dat de bonuskolom de werkelijke dollarbonus toont in plaats van het percentage, kunt u dit vermenigvuldigen met het maximale bonusbedrag. Beter nog, plaats dat bedrag in een cel op een ander blad en vermeld het in uw code. Dit zou het gemakkelijker maken om de waarde later te wijzigen zonder uw code te hoeven bewerken.

Het mooie van de Cells-functie is dat je een behoorlijk creatieve logica kunt bouwen om gegevens uit te halen veel cellen op veel verschillende vellen, en voer een aantal behoorlijk complexe berekeningen met ze uit.

U kunt allerlei acties uitvoeren op cellen met behulp van de functie Cellen-zaken zoals het leegmaken van de cellen, het wijzigen van de lettertypeopmaak en nog veel meer.

Raadpleeg de Microsoft MSDN-pagina voor het Cells-object voor meer informatie over alles wat u verder kunt doen.

Cellen formatteren met bereikfunctie

Voor het doorlussen van veel cellen één voor één, is de functie Cellen perfect. Maar als u in één keer iets op een heel cellenbereik wilt toepassen, is de bereikfunctie veel efficiënter.

Een use case voor dit kan zijn om een ​​celbereik op te maken met behulp van script, als aan bepaalde voorwaarden is voldaan.

Stel dat het totaal aantal verkoopaantallen van alle verkoopmedewerkers in totaal meer is dan $ 400.000, wilt u bijvoorbeeld alle cellen in de bonuskolom groen oplichten om aan te geven dat het team een ​​extra teambonus heeft verdiend.

Laten we eens kijken hoe u dat kunt doen met een IF-verklaring IF-zinnen gebruiken in Microsoft Excel IF-statements in Microsoft Excel gebruiken Of u nu een ervaren expert of een beginnende spreadsheet bent, u wilt dit bekijken gids voor IF-statements in Excel. Lees verder .

Private Sub Workbook_Open () Als Worksheets ("Sheet1"). Cellen (13, 3). Waarde> 400000 Dan ActiveSheet.Range ("D2: D12"). Interior.ColorIndex = 4 End If End Sub

Als dit gebeurt, als de cel boven het teamdoel staat, worden alle cellen in het bereik groen ingevuld.

Dit is slechts een eenvoudig voorbeeld van de vele acties die u kunt uitvoeren op groepen cellen met behulp van de functie Bereik. Andere dingen die je kunt doen zijn:

• Pas een omtrek toe rond de groep
• Controleer de spelling van tekst binnen een celbereik
• Cellen wissen, kopiëren of knippen
• Zoek door een bereik met de “Vind” methode
• Veel meer

Zorg ervoor dat u de Microsoft MSDN-pagina voor het bereikobject leest om alle mogelijkheden te zien.

Breng Excel naar het volgende niveau

Nu u de verschillen begrijpt tussen de functies Cells en Range, is het tijd om uw VBA-scripting naar een hoger niveau te tillen. Het artikel van Dann over het gebruik van functies Tellen en Toevoegen in Excel stelt u in staat om nog geavanceerdere scripts te maken die zeer snel waarden kunnen verzamelen in al uw gegevenssets.

En als je net begint met VBA in Excel, vergeet dan niet dat we een fantastische inleidende handleiding hebben voor Excel VBA De Excel VBA-programmeerhandleiding voor beginners De Excel VBA-programmeerhandleiding voor beginners VBA is een Microsoft Office-gereedschap. U kunt het gebruiken om taken met macro's te automatiseren, triggers in te stellen en nog veel meer. We zullen u kennis laten maken met Excel visuele basisprogrammering met een eenvoudig project. Lees meer voor jou ook.

.