List of Functions
ABS
Syntax: ABS( number)
PARAMETERS | |||
Name | Required/Optional | Data Type | Description |
Number | Required | Decimal | The number whose absolute value you want to find. |
Example: ABS(-1.24) Returns 1.24.
AND
Returns TRUE (1) if all of the logical expressions supplied are TRUE. If any of the logical expressions are FALSE or 0, the AND function returns FALSE (0).
Syntax
AND(** logical expression1 **, ** logical expression2 **,..., ** logical expressionN ** )
Parameters
PARAMETERS | |||
Name | Required/Optional | Data Type | Description |
logical expression | Required | String | A combination of constants, operators, functions, and references to ShapeSheet cells that results in a value. Any expression that evaluates to a non-zero value is considered to be TRUE. |
Example AND(Height > 1, PinX > 1)
Returns TRUE if both expressions are TRUE.
Returns FALSE if either expression is FALSE.
ARG
Specifies an argument that the calling cell can pass to a custom function, as well as the default value returned by the custom function if the calling cell does not pass in a value for the argument. Returns the value specified by the calling cell and the matching argName parameter.
Syntax
ARG(argName,[ defaultValue ])
Parameters
Name | Required/Optional | Data Type | Description |
argName | Required | String | The name of an argument that the calling cell can pass into the function. |
default Value | Optional | Numeric | The value returned by ARG if the calling cell did not pass in a value for the argName parameter. |
Remarks
As a shape developer, you can create custom functions by placing an expression in one cell and calling that expression from one or more other cells. The expression can include literal strings, ShapeSheet functions, and cell references. The expression can also include specific arguments that are passed in by the calling cell.
The calling cell specifies the cell that contains the custom function as well as any arguments that it wants to pass to the function. The expression cell is evaluated and the result returned to the calling cell.
Example
The following example shows how to use the ARG function in conjunction with the EVALCELL function to find the middle value from a set of three values.
In the expression cell, place the following code that defines the custom function:
In the calling cells, place the following code that calls the custom function:
BOUNDINGBOXDIST
Returns the measurement of the specified part of the shape's bounding box.
Syntax
BOUNDINGBOXDIST(** Index ** )
Parameters
Name | Required/Optional | Data Type | Description |
Index | Required | Number | The part of the shape's bounding box to measure and return. See Remarks for possible values. |
Return value
Number
Remarks
Index can be one of the following values.
Item | Value |
Width | 0 |
Height | 1 |
Left edge to shape pin | 2 |
Shape pin to right edge | 3 |
Shape pin to top edge | 4 |
Bottom edge to shape pin | 5 |
Center of bounding box to PinX | 6 |
Center of bounding box to PinY | 7 |
BOUNDINGBOXRECT
Returns the coordinate of the specified edge of the shape's bounding box.
Syntax
BOUNDINGBOXRECT(** Index ** )
Parameters
Name | Required/Optional | Data Type | Description |
Index | Required | Integer | The edge of the shape's bounding box for which to get the coordinate. See Remarks for possible values. |
Return value
Number
Remarks
Index can be one of the following values.
Item | Value |
Left edge | 0 |
Right edge | 1 |
Top edge | 2 |
Bottom edge | 3 |
If the shape has a parent, the return value is in the coordinate system of that parent.
CALLOUTCOUNT
Returns the total number of callout shapes that are associated with the shape.
Syntax
CALLOUTCOUNT()
Return value
Integer
CATEGORY
Returns the text from the Category field of a document's properties.
Syntax
CATEGORY ( )
CEILING
Rounds a number away from 0 (zero) to the next instance of multiple. If multiple is not specified, the number rounds away from 0 to the next integer.
Syntax
CEILING(** number **, ** multiple ** )
Parameters
Name | Required/Optional | Data Type | Description |
number | Required | Number | The number to round. |
multiple | Required | Number | The multiple to round to. |
Remarks
Number and multiple must have the same signs, or a #NUM! error is returned. If either number or multiple cannot be converted to a value, a #VALUE! error is returned. If either number or multiple is 0, the result is 0.
Example 1 CEILING(3.7)
Returns 4
Example 2 CEILING(-3.7)
Returns -4
Example 3 CEILING(3.7, 0.25)
Returns 3.75
CHAR
Returns the ANSI character for a number.
Syntax
CHAR(** number ** )
Parameters
Name | Required/Optional | Data Type | Description |
number | Required | Number | The number whose ANSI character you want to get. |
Remarks
The resulting string is one character in length. The number parameter must be an integer between 1 and 255 (inclusive), or the function returns an error.
Example CHAR(9)
Returns the tab character.
COMPANY
Returns the text from the Company field of a document's properties.
Syntax
COMPANY ()
CONTAINERCOUNT
Returns the total number of containers that include the shape as a member (including nested relationships).
Syntax
CONTAINERCOUNT()
Return value
Integer
CONTAINERMEMBERCOUNT
Returns the total number of shapes in the container.
Syntax
CONTAINERMEMBERCOUNT()
Return value
Integer
Remarks
If the shape is not a container, CONTAINERMEMBERCOUNT returns -1.
CONTAINERSHEETREF
Returns a sheet reference to the specified container that contains the shape.
Syntax
CONTAINERSHEETREF(** index ** ** [, category] ** )
Parameters
Name | Required/Optional | Data Type | Description |
index | Required | Integer | The 1-based index of the container. See Remarks for more information. |
category | Optional | String | The category of the container. See Remarks for more information. |
Return value
ShapeSheet reference
Remarks
The index of a container is calculated based on the z-order of containers from front to back.
Categories are user-defined strings that you can use to categorize shapes. You can define categories in the User.msvShapeCategories cell in the ShapeSheet for a shape. You can define multiple categories for a shape by separating the categories with semi-colons.
If the shape is not a member of a container, or if there is no container that matches both the specified index number and the category, CONTAINERSHEETREF returns #REF!.
Example
CONTAINERSHEETREF(1)!Height
Returns the value in the Height cell of the container that is most forward on the page to which the shape belongs.
CY
Returns a currency value.
Syntax
CY(** value **, ** cyID ** )
Parameters
Name | Required/Optional | Data Type | Description |
value | Optional | Number or String | A number or a string that includes currency-specific formatting. If not specified, the currency value is formatted according to the currency style in the system's Region and Language settings. |
cyID | Optional | Number | A numeric currency ID or a three-character quoted string for the ISO 4217 abbreviation. |
Remarks
To specify a different currency, you must include a valid cyID. For a list, see About currency constants.
If value is incompatible with the designated currency type, or if an invalid argument such as "not a number" is specified, a #VALUE! error is returned. If value is greater than 922,337,203,685,477.5807 or less than -922,337,203,685,477.5808, a #VALUE! error is returned.
For better precision with very large currency values that include fractions of a unit, such as 3.6 trillion, use string arguments for value.
Specifying an invalid cyID returns an error.
Example 1
If the user's Region and Language settings specify United States dollars:
CY(999998.993)
Returns $999,998.99
Example 2
CY(12345678.993, "USD")
Returns $12,345,678.99
Example 3
CY(12345678.993, "DEM")
Returns 12,345,678.99 DEM
Example 4
CY(12345678.7832, "XXX")
Returns 12,345,678.78
DATA1
Returns the text from the Data 1 box in the Shape Name dialog box for a shape (on the Developer tab, click Shape Name ), as a string.
Syntax
DATA1 ()
Remarks
If the box is empty, the function returns an empty string.
DATA2
Returns the text from the Data 2 box in the Shape Name dialog box for a shape (on the Developer tab, click Shape Name ), as a string.
Syntax
DATA2 ()
Remarks
If the box is empty, the function returns an empty string.
DATA3
Returns the text from the Data 3 box in the Shape Name dialog box for a shape (on the Developer tab, click Shape Name ), as a string.
Syntax
DATA3 ()
Remarks
If the box is empty, the function returns an empty string.
DATETIME
Returns the date and time value represented by datetime or expression.
Syntax
DATETIME(" ** datetime ** "| ** expression ** [, ** lcid ** ])
Parameters
Name | Required/Optional | Data Type | Description |
datetime | Required | String | Any string commonly recognized as a date and time or a reference to a cell containing a date and time. |
expression | Required | String | Any expression that yields a date and time. |
lcid | Optional | Number | Specifies the locale identifier to be used in evaluating a non-local datetime. The locale identifier is a number described in the system header files. |
Return value
Datetime
Remarks
If datetime is missing or cannot be interpreted as a valid date or time, DATETIME returns a #VALUE! error.
The returned value is formatted according to the short date style and time style in the system's current Regional Settings.
The DATETIME function also accepts a single number value for expression where the integer portion of the result represents the number of days since December 30, 1899, and the decimal portion represents the fraction of a day since midnight.
Example 1 DATETIME("May 30, 1997")+5 ed.
Returns the value representing 6/4/1997.
Example 2 FORMAT(DATETIME("5/20/1997 14:30:45"),"C")
Returns the string "Tuesday, May 20, 1997 2:30:45 PM."
Example 3 DATETIME("1:30 PM July 19")
Returns the value representing 7/19/2001 1:30:00 PM (assuming the current year is 2001).
Example 4 DATETIME(35580.6337)
Returns the value representing 5/30/1997 3:12:32 PM.
DATEVALUE
Returns the date value represented by datetime or expression.
Syntax
DATEVALUE(" ** datetime ** "| ** expression ** [, ** lcid ** ])
Parameters
Name | Required/Optional | Data Type | Description |
datetime | Required | String | Any string commonly recognized as a date and time or a reference to a cell containing a date and time. |
expression | Required | String | Any expression that yields a date and time. |
lcid | Optional | Number | Specifies the locale identifier to be used in evaluating a non-local datetime. The locale identifier is a number described in the system header files. |
Return value
Datetime
Remarks
Any time component in datetime or expression is discarded.
If datetime is missing or cannot be converted to a valid result, DATEVALUE returns a #VALUE! error.
The returned value is displayed using the short date style set by the system's current Regional Settings.
The DATEVALUE function also accepts a single number value for expression where the integer portion of the result represents the days since December 30, 1899.
Example 1 DATEVALUE(NOW( ))+5 ed.
Returns the date five days from now.
Example 2 DATEVALUE("7/19/1995 12:30")
Returns the date.
Example 3 DATEVALUE("May 33, 1997")
Returns a #VALUE! error.
Example 4 DATEVALUE(35580.6337)
Returns 5/30/1997.
DATE
Returns the date represented by year, month, and day formatted according to the short date style in the system's Regional Settings. The values for year, month , and day reflect the Gregorian calendar.
Syntax
DATE(** year **, ** month **, ** day ** )
Parameters
Name | Required/Optional | Data Type | Description |
year | Required | Integer | The year. |
month | Required | Integer | The month. |
day | Required | Integer | The day. |
Example 1 DATE(1999,6,7)
Returns the value representing 6/7/1999.
Example 2 DATE(1999,6,7) + 4 ed.
Returns the value representing 6/11/1999.
Example 3 FORMAT(DATE(1999,10,14),"C")
Returns the value representing Tuesday, October 14, 1999.
DAYOFYEAR
Returns an integer, 1 to 366, that represents the sequential day of the year in datetime or expression. The DAYOFYEAR function uses the Gregorian calendar.
Syntax
DAYOFYEAR(" ** datetime ** "| ** expression ** [, ** lcid ** ])
Parameters
Name | Require/Optional | Data Type | Description |
datetime | Required | String | Any string commonly recognized as a date and time or a reference to a cell containing a date and time. |
expression | Required | String | Any expression that yields a date and time. |
lcid | Optional | Number | Specifies the locale identifier to be used in evaluating a non-local datetime. The locale identifier is a number described in the system header files. |
Return value
Integer
Remarks
Any time component in datetime or expression is discarded.
The result corresponds to January 1 to December 31. No rounding is done. If datetime is missing or cannot be interpreted as a valid date or time, the function returns an error.
The DAYOFYEAR function also accepts a single number value for expression where the integer portion of the result represents the number of days since December 30, 1899.
Example 1 DAYOFYEAR("May 30, 1997 13:45:24")
Returns 150.
Example 2 DAYOFYEAR(DATEVALUE("May 30, 1997")+7 ed.)
Returns 157.
Example 3 DAYOFYEAR(35580.6337)
Returns 150.
DAY
Returns an integer, 1 to 31, representing the day in datetime or expression. The DAY function uses the Gregorian calendar.
Syntax
DAY(" ** datetime ** "| ** expression ** [, ** lcid ** ])
Parameters
Name | Required/Optional | Data Type | Description |
datetime | Required | String | Any string commonly recognized as a date and time or a reference to a cell containing a date and time. |
expression | Required | String | Any expression that yields a date and time. |
lcid | Optional | Number | Specifies the locale identifier to be used in evaluating a non-local datetime. The locale identifier is a number described in the system header files. |
Return value
Integer
Remarks
Any time component in datetime or expression is discarded.
No rounding is done. If datetime is missing or cannot be converted to a valid result, the function returns an error.
The DAY function also accepts a single number value for expression where the integer portion of the result represents the number of days since December 30, 1899.
Example 1 DAY("May 30, 1997 15:45:24")
Returns 30.
Example 2 DAY(DATEVALUE("May 30, 1997")+7 ed.)
Returns 6.
Example 3 DAY(35580.6337)
Returns 30.
EVALCELL
Takes a reference to a cell that contains a custom function as well as one or more name-value pairs to pass to the custom function as arguments (optional). Returns the calculated result of the custom function given the specified arguments and values.
Syntax
EVALCELL(** cellRef **,[ ** arg1Name,arg1 ** ],[ ** arg2Name,arg2 ** ],…)
Parameters
Name | Required/Optional | Data Type | Description |
cellRef | Required | String | A reference to the cell that contains the custom function. Cross-sheet references are allowed. |
arg1Name | Optional | String | The name of the first argument to be passed to the custom function. Spaces are allowed. |
arg1 | Optional | Varies | Value of the arg1 parameter. |
arg2Name | Optional | String | The name of the second argument to be passed to the custom function. Spaces are allowed. |
arg2 | Optional | Varies | Value of the arg2 parameter. |
Return value
Number
Remarks
The calling cell does not have to specify every argument used by the custom function.
Example
The following example shows how to use the EVALCELL function in conjunction with the ARG function to find the middle value from a set of three values.
In the expression cell, place the following code that defines the custom function:
In the calling cells, place the following code that calls the custom function:
EVALTEXT
Evaluates the text in shapename as if it were a formula and returns the result.
Syntax
EVALTEXT(** shapename!theText ** )
Parameters
Namr | Required/Optional | Data Type | Description |
shapename!theText | Required | String | A cell that is triggered when the associated shape's text composition changes. |
Return value
String
Remarks
shapename can be used to refer to the text of a shape other than the current shape.
If there is no text, the result is zero. If the text cannot be evaluated, the function returns an error.
Example EVALTEXT(Line.2!theText)
Evaluates the text contained in the shape Line.2. For example, if Line.2 contains "4 ft + 0.5 ft", returns the value 4.5 ft.
Last updated