Formules in cellen plaatsen met een macro

Het grote voordeel van werken met een macro is dat je vele handmatige handelingen met een druk op de knop kunt uitvoeren. Een nadeel van een macro is dat het voor een leek best een soort van black box kan zijn. Als de gebruiker van de macro namelijk geen kennis van VBA heeft dan kan het onduidelijk zijn wat voor bewerkingen er uitgevoerd worden. Je kan dit oplossen door niet alle berekeningen af te vangen in de macro. Je kunt namelijk ook met behulp van een macro een formule in een cel te plaatsen. Op deze manier is het voor een gebruiker inzichtelijker wat er exact qua bewerking uitgevoerd wordt. In dit artikel laat ik je zien hoe je met een macro een formule kunt plaatsen in een bepaalde cel of in een range.

Hoe schrijf je formules in VBA?

Formules plaats je in een cel of range eigenlijk hetzelfde als een stuk tekst (string). Er zijn 2 notaties die je kunt gebruiken om formules in cellen te plaatsen. Waar je wel rekening mee moet houden dat je met beide methodes de formules in het Engels dient te schrijven. De eerste manier is door ‘.formula’ achter een bepaalde cel of range te gebruiken. Dit schrijf je dan op deze manier:

Blad1.Cells(1, 1).Formula = "=Sum(A1:B1)"
Blad1.Range("C1:D5").Formula = "=Sum(A1:B1)"

Het alternatief is het plaatsen van ‘.formulaR1C1’ achter een bepaalde cel of range. De notatie van de cellen schrijf je bij formulaR1C1 anders. Je geeft nu de cijfers aan van de regels en de kolommen. Je kunt hierbij een relatieve referentie gebruiken (gezien vanaf de plek waar je de formule plaatst) of een absolute referentie. Ik zal hieronder voorbeelden geven van elke variant zodat het helder is hoe je deze kunt toepassen

Blad1.Range("D6:D7").FormulaR1C1 = "=SUM(R[-3]C:R[-2]C)"

Deze bovenstaande regel code plaatst een formule in cel D6 en D7. In cel D6 plaatst hij nu de som van ‘D3:D4’. Dit komt omdat hij vanaf D6 3 rijen naar boven telt. Dit zit je door het getal -3 dat tussen haakjes staat aangegeven. Er staat niks tussen haakjes aangegeven achter de C. Dit betekent dat de kolom gelijk blijft. Bij het einde van de range telt hij 2 rijen omhoog (er staat immers ‘R[-2]’) en de kolom blijft ook hier gelijk.

Blad1.Range("D6:D7").FormulaR1C1 = "=SUM(R3C4:R4C4)"

De bovenstaande regel code plaatst In cel D6 dezelfde formule als bij de relatieve methode. Je geeft nu niet de relatieve referentie mee vanaf de cell waar je de formule plaatst, maar je geeft een absoluut cijfer mee voor de rij en de kolom. kolom D is gelijk aan het cijfer 4 (D is de 4e letter van het alfabet). Het verschil van de bovenstaande regel met de relatieve referentie is dat je bij de absolute referentie in iedere cel (binnen de aangeven range) dezelfde formule komt te staan.

Mocht je het willen dan heb je ook nog de mogelijkheid om een combinatie van een relatieve referentie en een absolute referentie te gebruiken.

Maak gebruik van de macro recorder om een formule te plaatsen in een cel

Ik raad je aan om de bij het plaatsen van formules in cellen of ranges de macro recorder te gebruiken. Korte formules zijn nog wel zelf te schrijven in code maar op het moment dat een formule wat langer is dan wordt het gelijk behoorlijk complex om deze zelf te schrijven. Dan is het makkelijker om de formule eerst in een cel te plaatsen en deze actie op te nemen met de macro recorder. Vervolgens kun je bij de opgenomen code bekijken hoe de formule er uitziet in VBA. Wat ik wel een nadeel vind is dat de opgenomen code altijd de .formulaR1C1 notatie gebruikt in combinatie met de relatieve referenties. Ik vind persoonlijk de .formula notatie prettiger om mee te werken. 

Variabelen toepassen in je formules

Naast het hard meegeven van rijen kolommen of andere onderdelen van formules kun je ook variabelen gebruiken en verwerken in je formules. Dit doe je door de string af te sluiten met het ‘”‘ teken. Vervolgens plaats je het ‘&’ teken gevolgd door de variabele die je wilt gebruiken. Als de variabele niet het einde van de formule betreft dan doe je het volgende. Je plaatst weer een ‘&’ teken gevolgd door de rest van de formule als string geschreven. Ik zal dit weer verduidelijken aan de hand van een voorbeeld. Stel dat we in het bovenstaande voorbeeld in de som formule het cijfer 3 niet hard invullen maar uit een variabele halen dan werkt dit als volgt.

getal = 3
Blad1.Range("D6:D7").FormulaR1C1 = "=SUM(R" & getal & "C4:R4C4)"

Ik hoop dat het na het lezen van dit artikel nu compleet duidelijk voor je is hoe je een formule in een cel of range kunt plaatsen met een macro. Heb je toch nog een vraag over dit onderwerp? Laat het weten in de reacties!