XLL+ Class Library (6.3)

Setting formulae

Setting a cell's formula in Excel can be a surprisingly complex process. This topic addresses each of the complexities in turn, and explains how to handle them as easily as possible.

International issues
A1 versus RC addressing
Absolute and relative addresses
Recommendations

International issues

Different regional settings cause Excel to display formulae in a different way. There are several important differences:

Issue Description Affected by
Function names Excel's built-in functions appear with different names in different languages. Excel language version
List separators A function's arguments may be separated by commas, or by semi-colons or by some other punctuation character. User's regional settings
Decimal points A number's integral and fractional parts may be separated by a full stop or a comma or by some other punctuation character. User's regional settings
Array separators An array value expressed as a list may use semi-colons, commas or some other character to separate rows within the list. User's regional settings
RC-style addresses The letters used in RC style addresses vary according to language; thus cell A1 is R1C1 in English and Z1S1 in German. Excel language version

It should be clear from the above that simply setting a cell's formula to be, for example, =SUM(A1,B1,C1) will not work reliably. There will be at least two problems:

  1. Foreign language versions of Excel use a different name for the SUM function.
  2. A user's regional settings may use a character other than a comma to separate the function's arguments.

In addition, if RC notation (see below) is used, the address is different in different languages: thus =R1C1 will fail in German, which expects =Z1S1.

When working with formulae, there are two different sets of methods you can use.

Local International Description
CXlOper::GetFormula CXlOper::GetFormulaIntl For a CXlOper containing a reference, gets a cell's formula, as it appears in Excel (local version), or as it would appear in an English language version of Excel with US settings (international version).
CXlOper::SetFormula CXlOper::SetFormulaIntl Sets a cell's formula, as it appears in Excel (local version), or as it would appear in an English language version of Excel with US settings (international version).
CXlOper::ConvertFormula CXlOper::ConvertFormulaIntl Using a CXlOper containing a reference as a reference point, converts a formula between A1 and RC style addresses and between relative and absolute references.
CXlOper::SetLongFormula CXlOper::SetLongFormulaIntl Sets a cell's formula; the formula may contain up to 1024 characters.
CXlOper::MakeRef CXlOper::MakeRefIntl Creates a range reference from a string.

The Intl variants are recommended. If you use the Intl variants (GetFormulaIntl etc), you should write all your formulae in English, with US settings (i.e. commas to separate list items and arguments, full stops for decimal points and semi-colons to separate rows in array lists). You can be sure that your code will work regardless of the user's Excel language version or personal regional settings.

On the other hand, if you are going to manipulate formulae using the non-international variants (GetFormula etc), you should use the CXllApp::GetInternational method to retrieve the correct punctuation. See the International sample for an example of this technique.

A1 versus RC addressing

Excel addresses can be expressed in two styles, A1 style and RC style. A1 style uses one or more letters for the column, starting at "A" (column 1) and ending at "IV" (column 256); the letters are followed by an integer for the row (1 to 65536). RC style uses "RrCc" where r is the row number and c is the column number, e.g. "R1024C251". Thus "B3" and "R3C2" can refer to the same cell.

The user can choose either style to be displayed in a workbook (the default is A1). Each style supports both absolute and relative addressing (see below).

If you are generating a formula using code, or parsing an existing formula, it is generally much simpler to use RC style. However, the conversion methods in the XLL+ classes, such as CXlRef::ToString and CXlRef::FromString, support both A1 and RC style, so you can use either style as you prefer.

Note that for Excel versions in languages other than English, the letters "R" and "C" in an RC-style address will be in the local language. For example, the English address "R1C1" will be displayed as "Z1S1" in German. See also International issues above.

Absolute and relative addresses

Both styles (A1 and RC) support both relative and absolute addresses. A1 style addresses are relative by default, and are made absolute by prefixing the row, the coloumn or both with a dollar symbol, e.g. "$A$1". RC style addresses are absolute by default and are made relative by replacing the row or column number with a row or column offset within square braces, e.g.: "R[-1]C[-2]".

CXlMacros::FormulaConvert can be used to convert the references in a formula between absolute and relative addressing.

Recommendations

The following practises are advised when manipulating formulae.

You may still choose to use the local versions of formulae when displaying them to users in dialogs etc; it is sensible to avoid local forms otherwise.

Examples

You can find examples of the various ways to set formulae in the SetFormula sample.

Next: Macro helpers >>