denne opplæringen vil lære deg hvordan du oppretter celle formler ved HJELP AV VBA.
Formler I VBA
Ved HJELP AV VBA kan du skrive formler direkte Til Områder eller Celler i Excel. Det ser slik ut:
Sub Formula_Example() 'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2" 'Assign a flexible formula to a range of cells Range("d1:d100").FormulaR1C1 = "=RC2+RC3"End Sub
det er to Rekkeviddeegenskaper du trenger å vite:
- .Formel-Oppretter en eksakt formel (hardkodede cellereferanser). Bra for å legge til en formel i en enkelt celle.
- .FormulaR1C1-Skaper en fleksibel formel. Bra for å legge til formler i en rekke celler der cellereferanser skal endres.
for enkle formler er det greit å bruke .Formel Eiendom. Men for alt annet anbefaler vi at Du bruker Makroopptakeren…
Makroopptaker og Celleformler
Makroopptakeren er vårt go-to verktøy for å skrive celleformler med VBA. Du kan ganske enkelt:
- Start opptak
- Skriv inn formelen (med relative / absolutte referanser etter behov) i cellen & trykk enter
- Stopp opptak
- Åpne vba Og se gjennom formelen, tilpass etter behov og kopier + lim inn koden der det trengs.
jeg synes det er mye lettere å skrive inn en formel i en celle enn å skrive den tilsvarende formelen I VBA.
Legg Merke til et par ting:
- Makroopptakeren vil alltid bruke Den .FormulaR1C1-egenskapen
- Makroopptakeren gjenkjenner Absolutte vs. Relative Cellereferanser
Vba FormulaR1C1-Egenskapen
FormulaR1C1-egenskapen bruker R1C1-stil cellereferanse (i motsetning til standard A1-stilen du er vant Til å se I Excel).
Her er noen eksempler:
Sub FormulaR1C1_Examples() 'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4" 'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC" 'Reference D5 (Absolute Row, Relative Column) from cell A1 '=D$5 Range("a1").FormulaR1C1 = "=R5C" 'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"End Sub
Legg Merke TIL AT R1C1-stil celle referanse lar deg angi absolutte eller relative referanser.
Absolutte Referanser
i standard a1-notasjon ser en absolutt referanse slik ut: «= $C $ 2». I r1c1 notasjon ser det slik ut: «=R2C3».
for å opprette En Absolutt cellereferanse ved HJELP AV R1C1-stiltype:
- R + Radnummer
- C + Kolonnenummer
Eksempel: R2C3 ville representere celle $C$2 (C er 3.kolonne).
'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4"
Relative Referanser
Relative cellereferanser er cellereferanser som «flytter» når formelen flyttes.
i standard a1 notasjon ser de slik ut: «=C2». I r1c1-notasjon bruker du parenteser til å forskyve cellereferansen fra gjeldende celle.
Eksempel: Å skrive inn formel «=RC » i celle B3 vil referere til celle D4(cellen 1 rad under og 1 kolonne til høyre for formelcellen).
bruk negative tall til å referere til celler over eller til venstre for gjeldende celle.
'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC"
Blandede Referanser
Cellereferanser kan være delvis relative og delvis absolutte. Eksempel:
'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"
Vba Formelegenskapen
når du angir formler med .Formula Property du vil alltid bruke a1-stil notasjon. Du skriver inn formelen akkurat som du ville gjort i En Excel-celle, unntatt omgitt av sitater:
'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2"
Vba Formeltips
Formel Med Variabel
når du arbeider Med Formler I VBA, er det veldig vanlig å bruke variabler i celleformlene. For å bruke variabler bruker du & for å kombinere variablene med resten av formelstrengen. Eksempel:
Sub Formula_Variable() Dim colNum As Long colNum = 4 Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumEnd Sub
Formel Sitater
hvis du trenger å legge til et tilbud («) i en formel, skriv inn tilbudet to ganger («»):
Sub Macro2() Range("B3").FormulaR1C1 = "=TEXT(RC,""mm/dd/yyyy"")"End Sub
et enkelt sitat («) betyr Å VBA slutten av en tekststreng. Mens et dobbelt sitat («») behandles som et sitat i tekststrengen.
på samme måte bruker du 3 anførselstegn («»») for å omgjøre en streng med et anførselstegn («)
MsgBox """Use 3 to surround a string with quotes"""' This will print <"Use 3 to surround a string with quotes"> immediate window
Tilordne Celleformel Til Strengvariabel
vi kan lese formelen i en gitt celle eller område og tilordne den til en variabel:
'Assign Cell Formula to VariableDim strFormula as StringstrFormula = Range("B1").Formula
Ulike Måter Å Legge Til Formler i En Celle
her er noen flere eksempler på hvordan du tilordner en formel til en celle:
- Tilordne Formel Direkte
- Definer En Strengvariabel Som Inneholder Formelen
- Bruk Variabler for å opprette formel
Sub MoreFormulaExamples ()' Alternate ways to add SUM formula' to cell B1' Dim strFormula as String Dim cell as Range dim fromRow as Range, toRow as Range Set cell = Range("B1") ' Directly assigning a String cell.Formula = "=SUM(A1:A10)" ' Storing string to a variable ' and assigning to "Formula" property strFormula = "=SUM(A1:A10)" cell.Formula = strFormula ' Using variables to build a string ' and assigning it to "Formula" property fromRow = 1 toRow = 10 strFormula = "=SUM(A" & fromValue & ":A" & toValue & ") cell.Formula = strFormulaEnd Sub
oppdater formler
som en påminnelse, for å oppdatere formler, Kan du Bruke beregn-Kommandoen:
Calculate
for å oppdatere enkelt formel, område eller Hele Regnearkbruk .Beregn i stedet:
Sheets("Sheet1").Range("a1:a10").Calculate