Deze tutorial laat je zien hoe je de Excel Sum-functie in VBA gebruikt
De somfunctie is een van de meest gebruikte Excel-functies en waarschijnlijk de eerste die Excel-gebruikers leren gebruiken. VBA heeft eigenlijk geen equivalent - een gebruiker moet de ingebouwde Excel-functie in VBA gebruiken met de Werkbladfunctie object.
Som werkblad Functie
Het object WorksheetFunction kan worden gebruikt om de meeste Excel-functies aan te roepen die beschikbaar zijn in het dialoogvenster Functie invoegen in Excel. De SOM-functie is er een van.
| 123 | SubtestfunctieRange("D33") = Application.WorksheetFunction.Sum("D1:D32")Einde sub |
U kunt maximaal 30 argumenten hebben in de SOM-functie. Elk van de argumenten kan ook verwijzen naar een celbereik.
In dit onderstaande voorbeeld worden de cellen D1 tot en met D9 opgeteld
| 123 | Sub TestSum()Range("D10") = Application.WorksheetFunction.SUM("D1:D9")Einde sub |
In het onderstaande voorbeeld wordt een bereik opgeteld in kolom D en een bereik in kolom F. Als u het toepassingsobject niet typt, wordt ervan uitgegaan.
| 123 | Sub TestSom()Bereik ("D25") = Werkbladfunctie.SUM (Bereik ("D1:D24"), Bereik ("F1:F24"))Einde sub |
Merk op dat u voor een enkel celbereik het woord 'Bereik' niet hoeft op te geven in de formule voor de cellen, dit wordt aangenomen door de code. Als u echter meerdere argumenten gebruikt, moet u dit wel doen.
Een somresultaat toewijzen aan een variabele
Misschien wilt u het resultaat van uw formule ergens anders in de code gebruiken in plaats van het rechtstreeks terug te schrijven naar en Excel-bereik. Als dit het geval is, kunt u het resultaat toewijzen aan een variabele om later in uw code te gebruiken.
| 1234567 | Sub AssignSumVariable()Dim resultaat als Double'Wijs de variabele toe'resultaat = WerkbladFunctie.SUM(Bereik("G2:G7"), Bereik("H2:H7"))'Toon het resultaat'MsgBox "Het totaal van de bereiken is " & resultaatEinde sub |
Een bereikobject optellen
U kunt een groep cellen aan het Range-object toewijzen en dat Range-object vervolgens gebruiken met de WerkbladFunctie object.
| 123456789 | Sub TestSumRange()Dim rng als bereik'het bereik van cellen toewijzen'Stel rng = Bereik ("D2:E10") in'gebruik het bereik in de formule'Bereik ("E11") = Werkbladfunctie.SUM(rng)'laat het bereikobject los'Stel rng = Niets inEinde sub |
Meerdere bereikobjecten optellen
Op dezelfde manier kunt u meerdere bereikobjecten optellen.
| 123456789101112 | SubtestSumMultipleRanges()Dim rngA als bereikDim rngB als bereik'het bereik van cellen toewijzen'Stel rngA = Bereik ("D2:D10") inStel rngB = Bereik ("E2:E10") in'gebruik het bereik in de formule'Bereik ("E11") = Werkbladfunctie.SUM(rngA, rngB)'laat het bereikobject los'Stel rngA = Niets inStel rngB = Niets inEinde sub |
Som hele kolom of rij op
Je kunt de Som-functie ook gebruiken om een hele kolom of een hele rij bij elkaar op te tellen
Met deze procedure hieronder worden alle numerieke cellen in kolom D bij elkaar opgeteld.
| 123 | Sub TestSom()Range("F1") = WerkbladFunctie.SUM(Bereik("D:D")Einde sub |
Terwijl deze procedure hieronder alle numerieke cellen in rij 9 optelt.
| 123 | Sub TestSom()Range("F2") = WerkbladFunctie.SUM(Bereik("9:9")Einde sub |
Som een matrix op
U kunt ook de WorksheetFunction.Sum gebruiken om waarden in een array op te tellen.
| 123456789101112 | Sub TestArray()Dim intA (1 tot 5) als geheel getalDim SumArray als geheel getal'vul de array'intA(1) = 15intA(2) = 20intA(3) = 25intA(4) = 30intA(5) = 40'tel de array op en toon het resultaat'MsgBox-werkbladfunctie.SUM(intA)Einde sub |
De SumIf-functie gebruiken
Een andere werkbladfunctie die kan worden gebruikt, is de SUMIF-functie.
| 123 | Sub TestSumIf()Bereik ("D11") = Werkbladfunctie. SUMIF (Bereik ("C2: C10"), 150, Bereik ("D2: D10"))Einde sub |
De bovenstaande procedure telt alleen de cellen in Bereik (D2:D10) op als de corresponderende cel in kolom C = 150.
Som formule
Wanneer u de WerkbladFunctie.SUM om een som toe te voegen aan een bereik in uw werkblad, wordt een statische som geretourneerd, geen flexibele formule. Dit betekent dat wanneer uw cijfers in Excel veranderen, de waarde die is geretourneerd door de WerkbladFunctie zal niet veranderen.
In het bovenstaande voorbeeld heeft de procedure TestSum Range (D2:D10) opgeteld en is het resultaat in D11 gezet. Zoals u in de formulebalk kunt zien, is dit resultaat een cijfer en geen formule.
Als een van de waarden daarom verandert in het bereik (D2:D10), zal het resultaat in D11 NIET verandering.
In plaats van de WerkbladFunctie.SUM, kunt u VBA gebruiken om een somfunctie op een cel toe te passen met behulp van de Formule of FormuleR1C1 methoden.
Formule Methode:
Met de formulemethode kunt u specifiek naar een celbereik verwijzen, bijvoorbeeld: D2:D10, zoals hieronder weergegeven.
| 123 | Sub TestSomFormuleBereik ("D11").Formule = "=SUM(D2:D10)"Einde sub |
FormuleR1C1-methode
De FromulaR1C1-methode is flexibeler omdat deze u niet beperkt tot een bepaald cellenbereik. Het onderstaande voorbeeld geeft ons hetzelfde antwoord als het bovenstaande.
| 123 | Sub TestSomFormule()Bereik ("D11").FormuleR1C1 = "=SUM(R[-9]C:R[-1]C)"Einde sub |
Om de formule echter flexibeler te maken, kunnen we de code aanpassen om er als volgt uit te zien:
| 123 | Sub TestSomFormule()ActiveCell.FormulaR1C1 = "=SOM(R[-9]C:R[-1]C)"Einde sub |
Waar je ook bent in je werkblad, de formule zal dan de 8 cellen er direct boven optellen en het antwoord in je ActiveCell plaatsen. Het bereik binnen de SOM-functie moet worden verwezen met behulp van de rij (R) en kolom (C) syntaxis.
Met beide methoden kunt u dynamische Excel-formules gebruiken binnen VBA.
Er komt nu een formule in D11 in plaats van een waarde.
