It is possible to use Excel's ready-to-use formulas through VBA programming. These are properties that can be used with Range or Cells.
Formula adds predefined Excel formulas to the worksheet. These formulas should be written in English even if you have a language pack installed.
Range("F2").Formula = "=SUM(B2:C7)"
Range("F3").Formula = "=SUM($B$2:$C$7)"
Do not worry if the language of your Excel is not English because, as in the example, it will do the translation to the spreadsheet automatically.
You can insert multiple formulas at the same time using the Formula property. To do this, simply define a Range object that is larger than a single cell, and the predefined formula will be "dragged" across the range.
"Dragging" manually:
"Dragging" by VBA:
Range("D2:D7").Formula = "=SUM(B2:C2)"
Another way to perform the same action would be using FillDown method.
Range("D2").Formula = "=SUM(B2:C2)"
Range("D2:D7").FillDown
FormulaLocal adds predefined Excel formulas to the worksheet. These formulas, however, should be written in the local language of Excel (in the case of Brazil, in Portuguese).
Range("F2").FormulaLocal = "=SOMA(B2:C7)"
Just as the Formula property, FormulaLocal can be used to make multiple formulas.
FormulaR1C1, as well as Formula and FormulaLocal, also adds pre-defined Excel formulas to the spreadsheet; however, the use of relative and absolute notations have different rules. The formula used must be written in English.
FormulaR1C1 is the way to use Excel's ready-to-use formulas in VBA by easily integrating them into loops and counting variables.
In the notations:
- R refers to rows, in the case of vertical displacement
- C refers to columns, in the case of horizontal displacement
- N symbolizes an integer that indicates how much must be shifted in number of rows and/or columns
The format of the relative formula is: R[N]C[N]:R[N]C[N].
Range("F2").FormulaR1C1 = "=SUM(R[0]C[-4]:R[5]C[-3])" 'Equals the bottom row
Range("F2").FormulaR1C1 = "=SUM(RC[-4]:R[5]C[-3])"
When N is omitted, the value 0 is assumed.
In the example, RC[-4]:R[5]C[-3] results in "B2: C7". These cells are obtained by: receding 4 columns to the left RC[-4] from Range("F2") to obtain "B2"; and 5 lines down and 3 columns to the left R[5]C[-3] from Range("F2") to obtain "C7".
The format of the relative formula is: RNCN:RNCN.
Range("F2").FormulaR1C1 = "=SUM(R2C2:R7C3)" 'Results in "$B$2:$C$7"
N negative can only be used in relative notation.
The two notations (relative and absolute) can be merged.
Range("F2").FormulaR1C1 = "=SUM(RC[-4]:R7C3)" 'Results in "B2:$C$7"
Excel formulas can also be accessed by object WorksheetFunction methods.
Range("F2") = WorksheetFunction.Sum(Range("B2:C7"))
Excel formulas can also be accessed similarly to functions created in VBA.
The formulas present in the WorksheetFunction object are all in English.
One of the great advantages of accessing Excel formulas this way is to be able to use them more easily in the VBA environment.
MsgBox (WorksheetFunction.Sum(3, 4, 5))
Expense=4
MsgBox (WorksheetFunction.Sum(3, 4, 5,-Expense))
To list the available Excel formulas in this format, simply type WorksheetFunction. that automatically an option menu with all formulas will appear:
Suggested Exercise
SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.
Excel ® is a registered trademark of the Microsoft Corporation.
© 2024 SuperExcelVBA | ABOUT
