Microsoft Office Tutorials and References
In Depth Information
The ConvertFormula method also lets you convert from the A1 notation to the R1C1 nota
tion. It will also allow you to change a formula’s reference type from absolute to relative or
In the CreateNames routine, the ConvertFormula method converts a simple cell address from
one reference type to another. As the name implies, you can pass a more complex formula for
conversion, such as this example, which computes the daily average for the first two Mondays
in January using the Y2001ByQuarters workbook.
strA1Formula = Application.ConvertFormula( _
Formula:=" =AVERAGE(R5C2:R5C13, _
R12C2:R12C13), fromReferenceStyle:=xlR1C1, _
The ConvertFormula method has the following syntax:
expression .ConvertFormula( Formula, FromReferenceStyle, ToReferenceStyle,
ToAbsolute, RelativeTo )
A required expression that returns an Application object.
A required variant that provides the formula to be converted as a string.
A required integer that matches one of the two xlReferenceStyle
To ReferenceStyle An optional integer that matches one of the two xlReferenceStyle
constants. If not supplied, the reference style is not changed.
To Absolute An optional integer that matches one of the xlReferenceType constants.
If omitted, the reference type is not changed.
RelativeTo An optional variant that returns a range object that points to a single cell.
Relative references relate to this cell.
Table 8-3 lists the available xlReferenceStyle and xlReferenceType constants you can use with
the ConvertFormula method.
Table 8-3. Constants Used with the ConvertFormula Method