Formler

Beskrivelse

Formler

Det er ikke specielt kompliceret at indsætte formler i et regneark fra VBA - det gøres igen via Range objektet. Range objektet har en stribe egenskaber der kan bruges til formålet:

Egenskab	Anvendelse
Formula	Range("A1").Formula = "=B2B3" vil indsætte formlen "B2B3" i celle A1
FormulaArray	Range("E1:E3").FormulaArray = "=Sum(R1C1:R3C3)" vil indsætte en matrixformel i E1:E3. Bemærk at man skal bruge RC notation
FormulaHidden	Range("A1:B1").FormulaHidden = True vil skjule formlerne i A1:B1 hvis arkfanen beskyttes
FormulaLabel	ActiveWorkbook.AcceptLabelsInFormulas = True Worksheets(1).Range("a1:e3").FormulaLabel = xlColumnLabels - vil gøre det muligt at bruge kolonne labels i formler
FormulaLocal	Giver mulighed for at angive formler i den danske udgave - dvs. du kan skrive Range("A1").FormulaLocal = "=Middel(B2:B3)" (hvis du arbejder i en dansk Excel)
FormulaR1C1	Virker som Formula, men forudsætter du bruger RC notation
FormulaR1C1Local	Virker som FormulaLocal, men forudsætter du bruger RC notation

Bruger man ikke FormulaLocal skal man huske at alt foregår på engelsk - det gælder både funktionnavne, tal og dato formatering.

Et par eksempler

Range("C1").Formula = "=A1*B1"

Vil altså sætte formlen =A1*B1 ind i celle C1.

Range("D1").Formula = "=C1*1.25"

Lægger moms til tallet i C1 og skriver det med formlen =C1*1,25 i celle D1. Læg mærke til at formlen skrives med . i stedet for , . Når vi arbejder med Formula skal alle formler skrives på "amerikansk".

Man kan også skrive

Range("C1:C10").Formula = "=A1*B1"

Det er en sød lille detalje, der udnytter, at Excel arbejder med relative referencer. Linjen vil indsætte formlen =A1*B1 i celle C1, =A2*B2 i celle C2 osv. (Prøv selv om det passer)

Læg også mærke til at vi kan opbygge formlen dynamisk, hvis vi har behov for det.

AntalRækker = Range("A1").End(xlDown).Row Range("C1").End(xlDown).Offset(1, 0).Formula = "=Sum(C2:C" & AntalRækker & ")"

Øvelse

Download dette regneark, sørg for knapperne virker og send resultatet til mig.

Der gemmer sig et par hyggelige udfordringer i øvelsen her...

Når man skal beregne totalprisen kan det være nyttigt at huske på, at formler i Excel er relative - med mindre man bruger $ tegn - hvilket betyder, at når vi i A2 skriver =B2*C2 mener vi i virkeligheden at vi gerne vil have ganget indholdet i cellen til højre med indholdet af cellen 2 gange til højre.

HUSK, at din kode skal virke selv om der er 83, 103, 1200 eller ... rækker med data.

God kamp

Løsning

Se mit løsningsforslag her.

Min kode ser sådan her ud:

Sub BeregnTotal()     With Range("J2", Range("I2").End(xlDown).Offset(0, 1))         .Formula = "=I2*H2"         .Style = "Comma"     End With End Sub

Sub IndsætSum()     With Range("J2").End(xlDown).Offset(1, 0)         .Formula = "=sum(J2:" & Range("J2").End(xlDown).Address(False, False) & ")"         .Style = "Comma"     End With End Sub

Sub TotalInclMoms()     With Range("K2", Range("J2").End(xlDown).Offset(0, 1))         .Formula = "=J2*1.25"         .Style = "Comma"     End With End Sub

Sub FjernFormler()     Range("J2", Range("I2").End(xlDown).Offset(1, 2)).Clear End Sub

Sub FormaterArk()     FormaterOverskrift     FormaterData     Autotilpas End Sub

Sub Autotilpas()     Range("A1", Range("A1").End(xlToRight)).EntireColumn.AutoFit End Sub
Sub FormaterOverskrift()     With Range("A1", Range("A1").End(xlToRight))         .Interior.ColorIndex = 3         .Font.Bold = True         With .Borders(xlEdgeBottom)             .LineStyle = xlBorderLineStyleContinuous             .Weight = xlThick             .ColorIndex = 0         End With     End With End Sub

Sub FormaterData()      Dim c As Range     For Each c In Range("A2", Range("A2").End(xlDown))         With Range(c, c.End(xlToRight))             If (c.Row Mod 2) = 0 Then                 .Interior.ColorIndex = 15             Else                 .Interior.ColorIndex = 16             End If         End With     Next End Sub

Sub Fjernformatering()     Range("A1", Range("A1").End(xlToRight).End(xlDown)).ClearFormats End Sub

Forrige Formatering

Næste Arbejde med arkfaner

Brug for hjælp til VBA, VSTO eller SQL?

Scient Data tilbyder professionel IT-konsulentbistand

Kontakt Scient Data →