Denne vejledning lærer dig, hvordan du opretter celleformler ved hjælp af VBA.
Formler i VBA
Ved hjælp af VBA kan du skrive formler direkte til områder eller celler i Excel. Det ser sådan ud:
| 123456789 | Underformel_eksempel ()'Tildel en hårdkodet formel til en enkelt celleOmråde ("b3"). Formel = "= b1+b2"'Tildel en fleksibel formel til en række cellerOmråde ("d1: d100"). FormulaR1C1 = "= RC2+RC3"Afslut Sub |
Der er to Range -egenskaber, du skal kende:
- .Formel - Opretter en nøjagtig formel (hårdkodede cellereferencer). God til at tilføje en formel til en enkelt celle.
- .FormelR1C1 - Opretter en fleksibel formel. God til at tilføje formler til en række celler, hvor cellereferencer skal ændre sig.
For enkle formler er det fint at bruge egenskaben .Formula. For alt andet anbefaler vi dog at bruge Makrooptager…
Makrooptager og celleformler
Makrooptageren er vores værktøj til at skrive celleformler med VBA. Du kan simpelthen:
- Start optagelse
- Indtast formlen (med relative / absolutte referencer efter behov) i cellen, og tryk på enter
- Stop optagelsen
- Åbn VBA og gennemgå formlen, tilpas efter behov og kopier+indsæt koden, hvor det er nødvendigt.
Jeg finder det meget nemmere at indtaste en formel i en celle end at skrive den tilsvarende formel i VBA.
Læg mærke til et par ting:
- Makrooptageren vil altid bruge egenskaben .FormulaR1C1
- Makrooptageren genkender absolutte vs. relative cellereferencer
VBA FormulaR1C1 ejendom
FormulaR1C1-egenskaben bruger cellehenvisning i R1C1-stil (i modsætning til den standard A1-stil, du er vant til at se i Excel).
Her er nogle eksempler:
| 12345678910111213141516171819 | UnderformelR1C1_Examples ()'Reference D5 (absolut)'= $ D $ 5Område ("a1"). FormulaR1C1 = "= R5C4"'Reference D5 (relativ) fra celle A1'= D5Område ("a1"). FormulaR1C1 = "= R [4] C [3]"'Reference D5 (absolut række, relativ kolonne) fra celle A1'= D $ 5Område ("a1"). FormulaR1C1 = "= R5C [3]"'Reference D5 (relativ række, absolut kolonne) fra celle A1'= $ D5Område ("a1"). FormulaR1C1 = "= R [4] C4"Afslut Sub |
Bemærk, at cellehenvisningen i R1C1-stil giver dig mulighed for at angive absolutte eller relative referencer.
Absolutte referencer
I standard A1 -notation ser en absolut reference sådan ud: “= $ C $ 2”. I R1C1 -notation ser det sådan ud: “= R2C3”.
Sådan opretter du en absolut cellereference ved hjælp af type R1C1:
- R + rækkenummer
- C + kolonnenummer
Eksempel: R2C3 repræsenterer celle $ C $ 2 (C er den tredje kolonne).
| 123 | 'Reference D5 (absolut)'= $ D $ 5Område ("a1"). FormulaR1C1 = "= R5C4" |
Relative referencer
Relative cellereferencer er cellereferencer, der "bevæger sig", når formlen flyttes.
I standard A1 -notation ser de sådan ud: “= C2”. I R1C1 -notation bruger du parenteser [] til at udligne cellereferencen fra den aktuelle celle.
Eksempel: Indtastning af formel “= R [1] C [1]” i celle B3 vil referere til celle D4 (cellen 1 række herunder og 1 kolonne til højre for formelcellen).
Brug negative tal til at henvise til celler over eller til venstre for den aktuelle celle.
| 123 | 'Reference D5 (relativ) fra celle A1'= D5Område ("a1"). FormulaR1C1 = "= R [4] C [3]" |
Blandede referencer
Cellehenvisninger kan være delvist relative og delvist absolutte. Eksempel:
| 123 | 'Reference D5 (relativ række, absolut kolonne) fra celle A1'= $ D5Område ("a1"). FormulaR1C1 = "= R [4] C4" |
VBA Formula Property
Ved indstilling af formler med.Formel ejendom du vil altid bruge A1-stil notation. Du indtaster formlen, ligesom du ville i en Excel -celle, undtagen omgivet af citater:
| 12 | 'Tildel en hårdkodet formel til en enkelt celleOmråde ("b3"). Formel = "= b1+b2" |
VBA Formel Tips
Formel med variabel
Når du arbejder med formler i VBA, er det meget almindeligt at ville bruge variabler inden for celleformlerne. For at bruge variabler skal du bruge & til at kombinere variablerne med resten af formelstrengen. Eksempel:
| 1234567 | Sub Formula_Variable ()Dim colNum As LongcolNum = 4Område ("a1"). FormulaR1C1 = "= R1C" & colNum & "+R2C" & colNumAfslut Sub |
Formel citater
Hvis du skal tilføje et tilbud (“) inden for en formel, skal du indtaste tilbudet to gange (“ ”):
| 123 | Undermakro2 ()Område ("B3"). FormulaR1C1 = "= TEKST (RC [-1]," "mm/dd/åååå" ")"Afslut Sub |
Et enkelt citat (“) betyder for VBA slutningen på en tekststreng. Hvorimod et dobbelt citat (“”) behandles som et citat inden for tekststrengen.
På samme måde skal du bruge 3 anførselstegn (“” ”) til at omgive en streng med et anførselstegn (“)
| 12 | MsgBox "" "Brug 3 til at omgive en streng med citater" ""'Dette udskriver øjeblikkeligt vindue |
Tildel celleformel til strengvariabel
Vi kan læse formlen i en given celle eller et givet område og tildele den til en strengvariabel:
| 123 | 'Tildel celleformel til variabelDim strFormula som strengstrFormula = Range ("B1"). Formel |
Forskellige måder at tilføje formler til en celle
Her er nogle flere eksempler på, hvordan du tildeler en formel til en celle:
- Tildel formel direkte
- Definer en strengvariabel, der indeholder formlen
- Brug variabler til at oprette formel
| 12345678910111213141516171819202122232425 | Sub MoreFormulaExples ()'Alternative måder at tilføje SUM -formel på'til celle B1'Dim strFormula som strengDim celle som områdedæmp fraRække som rækkevidde, tilRække som rækkeviddeIndstil celle = område ("B1")'Direkte tildeling af en strengcell.Formula = "= SUM (A1: A10)"'Lagring af streng til en variabel'og tildeler ejendommen "Formula"strFormula = "= SUM (A1: A10)"cell.Formula = strFormula'Brug af variabler til at bygge en streng'og tildele det til ejendommen "Formula"fraRække = 1toRow = 10strFormula = "= SUM (A" & fromValue & ": A" & toValue & ")cell.Formula = strFormulaAfslut Sub |
Opdater formler
Som en påmindelse, for at opdatere formler, kan du bruge kommandoen Beregn:
| 1 | Beregn |
For at opdatere enkelt formel, område eller hele regneark skal du bruge .Beregn i stedet:
| 1 | Ark ("Ark1"). Område ("a1: a10"). Beregn |
