Denne opplæringen vil lære deg hvordan du lager celleformler ved hjelp av VBA.
Formler i VBA
Ved å bruke VBA kan du skrive formler direkte til områder eller celler i Excel. Det ser slik ut:
| 123456789 | Sub Formula_Example ()'Tilordne en hardkodet formel til en enkelt celleOmråde ("b3"). Formel = "= b1+b2"'Tilordne en fleksibel formel til en rekke cellerOmråde ("d1: d100"). FormulaR1C1 = "= RC2+RC3"Slutt Sub |
Det er to Range -eiendommer du trenger å vite:
- .Formel - Oppretter en eksakt formel (hardkodede cellereferanser). Bra for å legge en formel til en enkelt celle.
- .FormulaR1C1 - Oppretter en fleksibel formel. Bra for å legge til formler i en rekke celler der cellereferanser bør endres.
For enkle formler er det greit å bruke .Formula -egenskapen. For alt annet anbefaler vi imidlertid å bruke Makroopptaker…
Makroopptaker og celleformler
Makroopptakeren er vårt verktøy for skriving av celleformler med VBA. Du kan ganske enkelt:
- Start opptak
- Skriv inn formelen (med relative / absolutte referanser etter behov) i cellen og trykk enter
- Stopp innspillingen
- Åpne VBA og se gjennom formelen, tilpasse etter behov og kopiere+lime inn koden der det er nødvendig.
Jeg finner det mye enklere for å skrive inn en formel i en celle enn å skrive den tilsvarende formelen i VBA.
Legg merke til et par ting:
- Makroopptakeren bruker alltid egenskapen .FormulaR1C1
- Makroopptakeren gjenkjenner Absolutte kontra relative cellereferanser
VBA FormulaR1C1 -eiendom
FormulaR1C1-egenskapen bruker cellehenvisning i R1C1-stil (i motsetning til standard A1-stil du er vant til å se i Excel).
Her er noen eksempler:
| 12345678910111213141516171819 | Sub FormulaR1C1_Examples ()'Referanse D5 (absolutt)'= $ D $ 5Område ("a1"). FormulaR1C1 = "= R5C4"'Referanse D5 (relativ) fra celle A1'= D5Område ("a1"). FormulaR1C1 = "= R [4] C [3]"'Referanse D5 (Absolutt rad, relativ kolonne) fra celle A1'= D $ 5Område ("a1"). FormulaR1C1 = "= R5C [3]"'Referanse D5 (relativ rad, absolutt kolonne) fra celle A1'= $ D5Område ("a1"). FormulaR1C1 = "= R [4] C4"Slutt Sub |
Legg merke til at cellehenvisningen i R1C1-stil 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”.
Slik oppretter du en absolutt cellereferanse ved bruk av type R1C1:
- R + radnummer
- C + kolonnenummer
Eksempel: R2C3 vil representere celle $ C $ 2 (C er den tredje kolonnen).
| 123 | 'Referanse D5 (absolutt)'= $ D $ 5Område ("a1"). FormulaR1C1 = "= R5C4" |
Relative referanser
Relative cellereferanser er cellereferanser som "beveger seg" når formelen flyttes.
I standard A1 -notasjon ser de slik ut: “= C2”. I R1C1 -notasjon bruker du parenteser [] til å forskyve cellereferansen fra den nåværende cellen.
Eksempel: Angi formel “= R [1] C [1]” i celle B3 vil referere til celle D4 (celle 1 rad nedenfor og 1 kolonne til høyre for formelcelle).
Bruk negative tall for å referere til celler over eller til venstre for den gjeldende cellen.
| 123 | 'Referanse D5 (relativ) fra celle A1'= D5Område ("a1"). FormulaR1C1 = "= R [4] C [3]" |
Blandede referanser
Cellehenvisninger kan være delvis relative og delvis absolutte. Eksempel:
| 123 | 'Referanse D5 (relativ rad, absolutt kolonne) fra celle A1'= $ D5Område ("a1"). FormulaR1C1 = "= R [4] C4" |
VBA Formula Property
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, bortsett fra omgitt av sitater:
| 12 | 'Tilordne en hardkodet formel til en enkelt celleOmråde ("b3"). Formel = "= b1+b2" |
VBA Formula Tips
Formel med variabel
Når du arbeider med formler i VBA, er det veldig vanlig å ønske å bruke variabler i celleformlene. For å bruke variabler bruker du & til å kombinere variablene med resten av formelstrengen. Eksempel:
| 1234567 | Sub Formula_Variable ()Dim colNum As Longkolonn = 4Område ("a1"). FormulaR1C1 = "= R1C" & colNum & "+R2C" & colNumSlutt Sub |
Formel sitater
Hvis du trenger å legge til et tilbud (“) i en formel, angir du sitatet to ganger (“ ”):
| 123 | Submakro2 ()Område ("B3"). FormulaR1C1 = "= TEKST (RC [-1]," "mm/dd/åååå" ")"Slutt Sub |
Et enkelt anførsel (“) betyr for VBA slutten av en tekststreng. Mens et dobbelt anførsel (“”) behandles som et sitat i tekststrengen.
På samme måte bruker du 3 anførselstegn (“” ”) for å omringe en streng med anførselstegn (“)
| 12 | MsgBox "" "Bruk 3 for å omringe en streng med anførselstegn" ""'Dette vil skrive ut umiddelbart vindu |
Tilordne celleformel til strengvariabel
Vi kan lese formelen i en gitt celle eller et gitt område og tilordne den til en strengvariabel:
| 123 | 'Tilordne celleformel til variabelDim strFormula som strengstrFormula = Range ("B1"). Formel |
Ulike måter å legge formler til en celle
Her er noen flere eksempler på hvordan du tilordner en formel til en celle:
- Tilordne formelen direkte
- Definer en strengvariabel som inneholder formelen
- Bruk variabler for å lage formel
| 12345678910111213141516171819202122232425 | Sub MoreFormulaExample ()'Alternative måter å legge til SUM -formel'til celle B1'Dim strFormula som strengDim celle som Rangedim fromRow as Range, toRow as RangeAngi celle = område ("B1")'Tilordne en streng direktecell.Formula = "= SUM (A1: A10)"'Lagre streng til en variabel'og tilordner eiendommen "Formula"strFormula = "= SUM (A1: A10)"cell.Formula = strFormula'Bruke variabler for å bygge en streng'og tilordne den eiendommen "Formula"fromRow = 1toRow = 10strFormula = "= SUM (A" & fromValue & ": A" & toValue & ")cell.Formula = strFormulaSlutt Sub |
Oppdater formler
Som en påminnelse, for å oppdatere formler, kan du bruke kommandoen Beregn:
| 1 | Regne ut |
For å oppdatere enkeltformel, område eller hele regnearket, bruk .Beregn i stedet:
| 1 | Ark ("Ark1"). Område ("a1: a10"). Beregn |
