Excel is krachtig. Als je het veel gebruikt, weet je waarschijnlijk al veel trucs met formules of autoformattering, maar door gebruik te maken van de functies Cells en Range in VBA kun je je 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 verwijzen meestal naar een enkele cel tegelijk, terwijl Range naar een groep cellen tegelijk verwijst. 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 u dat wilt, kunt u ook naar cel D15 verwijzen met "Cellen (15, " D ")" - u mag de kolomletter gebruiken.
Er is veel flexibiliteit in het kunnen verwijzen naar een cel met behulp van een nummer voor kolom en cel, met name met scripts die heel snel een groot aantal cellen kunnen doorlopen (en berekeningen kunnen uitvoeren). 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:
With Worksheets("Sheet1") .Range(.Cells(1, 1), _ .Cells(20, 5)) End With De bovenstaande code verwijst naar hetzelfde bereik als de functie Range ("A1: E20"). 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.
Mogelijk heeft u Blad1 dat hun aantal verkopen en hun verkoopvolume bijhoudt.
Op Sheet2 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 zouden we niet gewoon de berekeningen voor de bonuskolom in hetzelfde script in die tijd triggeren?
De cellenfunctie in actie
Als u nog nooit VBA in Excel hebt geschreven, moet u het menu-item Ontwikkelaar inschakelen. Ga hiervoor naar Bestand > Opties . Klik op Lint aanpassen . Kies tot slot Ontwikkelaar in het linkerdeelvenster, voeg het toe aan het rechterdeelvenster en zorg dat het selectievakje is ingeschakeld.
Als u nu op OK klikt en teruggaat naar het hoofdpagina, ziet u de menuoptie Ontwikkelaar.
U kunt het menu Invoegen gebruiken om een opdrachtknop in te voegen, of klik op Code weergeven om het coderen te starten.
In dit voorbeeld laten we het script uitvoeren telkens wanneer de werkmap wordt geopend. Om dit te doen, klikt u gewoon op Code in het ontwikkelaarsmenu en plakt u 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 maken "x" in de rij, gebruiken een nummer om de gegevens van elke kolom aan 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() For x = 2 To 12 Worksheets("Sheet1").Cells(x, 4) = (Worksheets("Sheet1").Cells(x, 2).Value / 50) * 0.4 _ + (Worksheets("Sheet1").Cells(x, 3).Value / 50000) * 0.5 _ + (Worksheets("Sheet2").Cells(x, 2).Value / 10) * 0.1 _ Next 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 het 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 vrij creatieve logica kunt bouwen om gegevens uit vele cellen op verschillende vellen in te voeren en er behoorlijk ingewikkelde berekeningen mee uit te voeren.
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.
Als u wilt verkennen wat u nog meer kunt doen, bekijkt u de Microsoft MSDN-pagina voor het Cells-object.
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 reeks cellen op te maken met behulp van script, als aan bepaalde voorwaarden is voldaan.
Stel dat de totale verkoopomzet van alle verkoopmedewerkers in totaal meer dan $ 400.000 bedraagt, dan wilt u alle cellen in de bonuskolom in het groen markeren om aan te geven dat het team een extra teambonus heeft verdiend.
Laten we eens kijken hoe je dat kunt doen met een IF-verklaring IF-zinnen gebruiken in Microsoft Excel IF-statements gebruiken in Microsoft Excel Of je nu een doorgewinterde expert bent of een beginnende spreadsheet, wil je dit eens bekijken gids voor IF-statements in Excel. Lees verder .
Private Sub Workbook_Open() If Worksheets("Sheet1").Cells(13, 3).Value>400000 Then 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 op groepen cellen kunt uitvoeren 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
- Zoeken door een bereik met de "Find" -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. Dann's artikel over het gebruik van tel- en optelfuncties in Excel stelt u in staat om nog geavanceerdere scripts te bouwen die zeer snel waarden kunnen verzamelen in al uw datasets.
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.