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 |
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:
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.
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.
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.
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.
You can find examples of the various ways to set formulae in the SetFormula sample.