Calc method

Carries out a calculation. Returns True if successful, otherwise False.

Applies to

TableData	ArrData	RsetData	Xdata	XShared
	✓

Parameters

Name	Type	Description
Field	Variant	Name or number of the field to be calculated.
CalcProc	Variant	Calculation procedure.
Scratch	Range (optional)	Scratch area to be used for calculation.
Params	Variant (optional)	Parameters where calculation is a user-defined function.

In order to carry out a calculation with an Excel formula, specify the formula in the CalcProc parameter. This is a normal Excel formula (starting with a = character) but in place of cell references or data names, use the field name preceded and followed with a ` character. The calculation then replaces this with the actual value of the field in the record being calculated.

There are two methods of calculation:

• To use Application.Evaluate, omit the Scratch parameter. The formula is evaluated as an array formula if necessary, though the length of the formula (after values have been substituted) cannot exceed 255 characters (an Excel limitation).
• To use spare space on a worksheet, set the Scratch parameter to the first cell. The column starting with this cell is used (and cleared after use). With this method there is no restriction on the length of the formula, though it is not evaluated as an array formula. This method is normally faster.

If you precede the field name with a \ character (after the `) this refers to the value of the field in the preceding record; but if the field being referenced is the same field as is being calculated, the behaviour depends on which calculation method has been used:

• Using Application.Evaluate, the value returned is the new value after the calculation. Since calculation proceeds sequentially, this can be used to create cumulative values.
• Using the worksheet method, the value returned is the original value.

If the result of the calculation starts with # and ends with ! this is treated as an error and the text between these delimiters is returned to the user as an error message. If the formula is not a valid Excel formula, the calculation again fails with an error message and the formula which has failed (after substitution of field values) is output to the VBA immediate pane to assist with debugging (if using the worksheet method, the formula returned is the formula for the first record).

Arrays can be used in calculation just like Excel array constants, so any function which would work in an Excel formula with an array constant can be used (such as MATCH). Arrays can be created using Group or certain of the built-in functions (see below).

Do not use Excel functions which produce a result which spills into more than one cell.

There are also some inbuilt calculation functions. To use these, the CalcProc parameter will be the name of the function and the Param parameter will be an array of parameters used by the function. These are:

Name	Purpose	Parameters
RecordNumber	Creates a sequential record number	None.
Expand	Creates an array of all the integer values between the two parameters.	Start value; End value.
Combine	Creates an array combining the parameters (which may be arrays). Parameters must be either field names/numbers or literals. Literals may be preceded by $ to ensure they are not interpreted as field names. Data is combined as is (no extra " characters are added) though ToTextArray can be used first.	Fields/Values to be combined.
ToTextArray	Convert the parameter field to a single element text array.	Field to be converted.
Total	Returns the total of the specified field using the specified group function (Sum, Count, Average, Max Min, First, Last).	Field to be totalled; group function.
ConvertFrac	Converts a field in the format of a fraction to a numeric value.	Field to be converted.
RegExpr	Extracts part of a field using a regular expression. A guide to Microsoft regular expressions can be found here . Every record must match the whole regular expression. If the constant RegexMode is set to True at the top of XShared, it uses Microsoft VBScript Regular Expressions 5.5, otherwise it uses Jscript (Microsoft have indicated that VBScript will be withdrawn at some point). The global parameter is not used in this implementation, only the first match will be used.	Field; Pattern; IgnoreCase; Global; Match number (0 based).

It is also possible to write your own calculation function. The name of the user defined function is supplied as the CalcProc parameter. For more information on writing user defined functions see Transforming Data.