Cells and Ranges

Objective Grid accepts several basic types of cell entries: text, constant numeric values, dates, time, formulas that calculate a value, and graphs. Calculated values can be single numbers, strings, arrays, or tables of values.

Text entries are useful for:

• Using Objective Grid to manage textual information such as names, addresses, or whatever your application may require.

A text entry is usually used for descriptive information such as a column heading, a row label, or an explanatory comment. You can also make text entries of names, addresses, or other non-numeric information. Objective Grid treats text and numeric entries differently, so you can differentiate between text and numeric values when entering cell contents.

To enter most text into a cell, just type the text string as you want it to appear or programmatically call SetExpressionRowCol(). If the text you wish to enter would normally be considered a formula, then you must precede the actual text with the ' (apostrophe) character.

If you make an entry that is not text but which returns an error when treated as a formula, Objective Grid displays an error message. “Error Messages Reference” contains an alphabetized summary of error messages.

Note that some numeric entries, such as phone numbers, should really be treated as text. For example, if you enter the phone number 555-1212 in a cell, Objective Grid displays the number -657, the difference of 555 and 1,212. However, if you start the string with the apostrophe ('555-1212, for example), Objective Grid treats the phone number as text and does not calculate it.

Special characters can be entered as text by using the back-slash convention (\). (This technique is compatible with ANSI C escape codes.) The most common use of this is to force explicitly a new line in a text cell by entering \n. Other useful ones are \" which allows the double quote character within a string and \\ which allows the back-slash character.

If a cell entry begins with a digit (from 0 to 9), Objective Grid treats the entry as a numeric entry.

Objective Grid also recognizes the following symbols as indicators of numeric entries: ., +, and -. You can format numeric values to be displayed in several ways, including fixed formats, scientific notation, currency, and hexadecimal.

Follow these conventions for entering numeric values:

• To enter a positive number, use the number keys to type the number, with or without a + indicator. If you do not type a plus (+), Objective Grid assumes the number is positive.

• To enter a negative number, type a minus sign (-) and the number. Do not use parentheses to indicate negatives. However, if you change the numeric format to Dollars or Comma, Objective Grid displays negative numbers in parentheses.

• Do not use spaces or commas when entering numbers. You can display commas, if you want to, by changing the format.

• Be careful not to substitute a lower case L for the numeral 1 or the upper case O for the numeral 0.

• You can use scientific notation to enter a number, with the convention that the letter e separates the fraction from the base 10 exponent. 1.23e3 is equivalent to 1230.

You can choose from a variety of formats for displaying numbers. The display formats do not change the number itself, the way the number is stored internally, or the way it is used in calculations. Formatting just changes the way Objective Grid displays numbers.

See “Number Formatting” for information on using the CGXStyle::SetFormat() API. The end-user might change the number formatting with the CGXStyleSheet dialog.

Objective Grid provides special, built-in features for displaying date entries in the format you choose. Date and time formats include: 24-Oct-99, 24-Oct, 10/24, Oct-99, 10/24/99, 24.10.1999, and 1999-10-24 (ISO 8061). Time is displayed as: 12:00:05.

If you entered a date or time as a string of numbers separated by hyphens, (for example, 10-31-99) Objective Grid would interpret this string, store a date value in the cell and then set the cell format to date.

If the year (yy) is omitted when entering a date into a cell, the current year is assumed. For example, entering /7/17 is equivalent to entering /7/17/01 when the current year is 2001.

Objective Grid internally stores date and time information using the same convention as other popular spreadsheet programs: Dates are represented as an integer equal to the number of days since December 31, 1899. Times are represented as fractions of a day, starting at midnight. For example, 6:00 AM is stored as 0.25 (a quarter of a 24-hour day). Using this convention, date and time values may be used together. For example, the date/time value 1.25 corresponds to 6:00:00 AM, January 1, 1900.

You can use date values in calculations. For example, subtracting 05-Oct-99 from 10-Oct-99 yields a value of 5 days.

Formulas establish and calculate mathematical relationships between elements of the spreadsheet.

Objective Grid formulas can calculate with numbers, text, logical values, cell references, and other formulas. For example, you can easily calculate the sum of a series of cells, the total of values in a column, or the absolute value of another cell entry.

Formulas are the heart of the Objective Grid spreadsheet, defining relationships between the values in other cells. For example, formulas can be used to sum a column of numbers, compute an average, determine a minimum or maximum value, or round the results of a calculation.

While constant entries remain the same until you change them, cells defined by formulas are automatically changed to reflect changes in referenced cells— even when there are complex interdependencies among cells.

Once entered in a cell, formulas are hidden behind the scenes, performing their work in the background and displaying only the result of their calculation. To view the formula in a cell, move the cell cursor to the cell and switch the cell into edit mode. You can edit the formula or values within the cell.

The Objective Grid Formula Engine comes with over 240 built-in worksheet functions. Functions can be used alone or in combination with formulas and other functions. Objective Grid was designed for demanding users, so it provides many highly specialized functions for business, engineering, scientific, and statistical applications. Sometimes you might want to provide only a subset of these worksheet functions to the end-user, no worksheet functions at all, or add your own worksheet functions.

The CGXWorksheetFunctions class provides an extensible interface to the formula engine where you specify what worksheet functions should get linked into your application and where you can also add your own custom worksheet functions to the engine.

CGXWorksheetFunctions itself implements no worksheet functions. This makes sure that none of the built-in functions will be linked into your executable and waste disk space unless you really need them.

In order to enable all built-in functions, you should call GXEnableWorksheetFunctions(); from your application’s InitInstance() method. GXEnableWorksheetFunctions() instantiates the CGXDefaultWorksheetFunctions class. This class has a lookup table with all built-in worksheet functions.

If you want to add your custom functions, you should subclass CGXWorksheetFunctions (or CGXDefaultWorksheetFunctions) and override LookupFunction(). In your InitInstance() method you can register your class with:

CMyWorksheetFunctions is the class that you derived from CGXWorksheetFunctions.

NOTE >> Implementing your own custom worksheet functions is only recommended for advanced users. When implementing your own custom worksheet functions you will have to deal directly with the formula engine code, which is not documented at all. Take a look at the existing worksheet functions (e.g. in src\grid\engine\fnmath.cpp) for examples of how to implement worksheet functions.

When writing functions you can get a pointer to the sheet context, to the CGXFormulaSheet, and to the CGXGridCore object using the following methods:

• Based from the sheet context id (typically called si when passed to a C-Function) you can get a pointer to the _gx_sheet_context object with gxSheetContext * context = _gx_get_context_ptr(si);

• Based on this context you can get a pointer to the associated CGXFormulaSheet by calling
CGXFormulaSheet* pSheet = context->m_pSheetContext;

• You can also get a pointer to an associated CGXGridCore object by calling CGXGridCore* pGrid = pSheet()->Grid();

A range is a contiguous, rectangular block of cells that has been referenced as a group in a cell formula or selected to be affected by some editing action, such as Copy Formulas or Move.

A range object can either be initialized as an empty range, initialized with another range, or specified by coordinates. These examples show some alternatives for creating a range object.

Individual cells and cell ranges may be referred to by their standard address coordinates (A5..D25, for example) or by pre-assigned names. The Named Range option lets you assign a name to any cell or cell range.

A named range is a range of cells to which a name has been assigned with the Named Range utility. A named cell is a cell to which a name has been assigned. Both named ranges and named cells can be referenced by their names or by their addresses.

Using names can help clarify the logic of your spreadsheet, making it easier to share among users, and easier to update long after it was originally designed. Once defined, names can be used anywhere standard cell or range references are used, including in cell formulas.

The following guidelines apply to named ranges:

• Objective Grid does not differentiate between uppercase and lowercase letters in range names. For example, Objective Grid would consider "Range1" and "range1" to be the same name.

• Names must begin with an uppercase or lowercase alphabetic character. The rest of the name may include any combination of alphabetic characters, numeric characters, and the following characters: $, ., or _.

• When an area containing a named range or cell is moved, Objective Grid automatically updates the definition of the name to reflect the new location.

• Named cells and ranges can have relative or absolute addresses. When cells containing references to named cells with absolute addresses are moved, the references are not updated.

• When cells containing a reference to a named cell or range are copied, Objective Grid converts the reference to an appropriately translated standard reference.

• If you redefine a name, all instances of that name in the spreadsheet are updated as soon as the spreadsheet is recalculated.

• If you remove a name definition, all references to that name in the spreadsheet are converted to appropriate standard references.

The Copy operations, clipboard copy and OLE Drag-and-Drop, duplicate a cell or range of cells in addition to all the formatting and then place these formulas in a new location, overwriting existing data in the destination range. Objective Grid automatically translates relative cell references in the copied formulas to reflect their new locations. For example, if cell A10 contained the formula =@SUM(A1..A9) and you copied it to cell B10, then B10 would contain the formula =@SUM(B1..B9).

To create an absolute cell reference, which is a value that Objective Grid doesn’t translate if the cell contents are copied or moved, insert a dollar sign ( $ ) before each component of the cell reference (e.g., $A$1). For more information, refer to “Formula Values” and “Cell Referencing in Objective Grid.”

Use the CopyCells() method to perform a copy operation programmatically.

Objective Grid overwrites any existing data in destination ranges, so plan carefully before carrying out Copy and Move operations. If you overwrite data by mistake during a Copy or Move, you can put it back like it was using Undo.

The Move operation moves a cell or range of cells to a new location, along with all formulas. Objective Grid clears the source cells and overwrites any existing data in the destination cells. Like the Copy Formulas operation, all cell references are updated to reflect the new cell/range location.

Use the MoveCells() method to perform a move operation programmatically.

The effects of moving cells include:

• When you move a cell that is referenced by a formula, Objective Grid automatically updates the formula for the new location, even if the cell reference is absolute.

• When you move a cell in a range that is referenced by a formula, the formula still references the original range. However, if you move a corner cell of the range, Objective Grid extends or contracts the range to match the new location.

• If you move a cell range that has been named or referenced in a formula, Objective Grid automatically updates the definition of the range name or the formula reference to reflect the new location.

• If you move part of a cell range that has been named or referenced in a formula, it can cause problems. Objective Grid updates range names and references only when you move one or more corner cells of the range. If you move cells in the interior of the range, Objective Grid does not change the range name or any references to it.

NOTE >> If you make a mistake when copying or moving data, you can use Undo to restore the spreadsheet to its state prior to the copy or move operation.

If you delete a row or column that contains a cell that is referenced by a formula in another cell, the formula reference will not be modified. Range references will be updated when rows or columns are deleted within a range specification.