Macro command functions
[ ] indicates optional parameters
ACTIVATE([window_text], [pane_num])
ACTIVE.CELL()
ALERT(message_text, [type_num], [help_ref])
BEEP([tone_num])
CALCULATE.DOCUMENT()
CALCULATE.NOW()
CALLER()
CLEAR([type_num])
COLUMN.WIDTH([width_num], [reference], [standard], [type_num], [standard_num])
COPY([from_reference], [to_reference])
CUT([from_reference], [to_reference])
DATA.FORM()
DATA.SERIES([rowcol], [type_num], [date_num], [step_value], [stop_value], [trend])
DEREF(reference)
EDIT.DELETE([shift_num])
EVALUATE(formula_text)
FILL.DOWN()
FILL.LEFT()
FILL.RIGHT()
FILL.UP()
FONT.PROPERTIES([font], [font_style], [size], [strikethrough], [superscript], [subscript], [outline], [shadow], [underline], [color], [normal], [background], [start_char], [char_count])
FORMAT.NUMBER(format_text)
FORMULA(formula_text, [reference])
FORMULA.GOTO([reference], [corner])
GET.OBJECT(type_num, [object_id], [start_num], [count_num], [item_index])
INPUT(message_text, [type_num], [title_text], [default], [x_pos], [y_pos], [help_ref])
INSERT([shift_num])
PASTE([to_reference])
PASTE.SPECIAL([paste_num], [operation_num], [skip_blanks], [transpose])
PROTECT.DOCUMENT([contents], [windows], [password], [objects], [scenarios])
REFTEXT(reference, [a1])
ROW.HEIGHT([height_num], [reference], [standard_height], [type_num])
SELECT([selection], [active_cell])
SELECTION()
SORT([orientation], [key1], [order1], [key2], [order2], [key3], [order3], [header], [custom], [case])
TEXTREF(text, [a1])
WORKBOOK.ACTIVATE(sheet_name)
WORKBOOK.DELETE([sheet_name])
WORKBOOK.INSERT([type_num])
WORKBOOK.NAME(old_sheet_name, new_sheet_name)
Macro command functions
ACTIVATE([window_text], [pane_num])
Specifies the workbook, sheet and/or pane that is to be active.
window_text | The workbook and/or sheet name. If this parameter is omitted then the active window is not changed. |
pane_num | For a split window, specifies which pane is to be active. The possible values are: |
|
1 | Upper or upper left. |
2 | Upper right. |
3 | Lower or lower left. |
4 | Lower right. |
|
| If this parameter is omitted it then the active pane is not changed. |
ACTIVE.CELL()
Returns a reference which represents the current position of the cursor on the active worksheet.
ALERT(message_text, [type_num], [help_ref])
Displays a message box containing the specified text. It returns TRUE if the OK button is pressed, otherwise FALSE.
message_text | The text to be displayed in the message box. |
type_num | The type of message box to be displayed. The possible values are: |
|
1 | A box containing a question mark icon and OK and Cancel buttons. |
2 | A box containing an information icon and an OK button. |
3 | A box containing an exclamation icon and an OK button. |
|
| If this parameter is omitted it defaults to 2. |
help_ref | A reference to a Help topic (not currently implemented). |
BEEP([tone_num])
Causes the computer to beep.
tone_num | A number from 1 to 4 indicating which type of beep is to be produced (not currently implemented - all values will produce the same tone). If this parameter is omitted it defaults to 1. |
CALCULATE.DOCUMENT()
Causes the current worksheet to be recalculated.
CALCULATE.NOW()
Causes all worksheets to be recalculated.
CALLER()
Returns information about the caller of the macro.
Macro called from: | CALLER() returns: |
user-defined function | reference of cell containing function call |
user-defined function in array formula | reference of array formula range |
attached to control or drawing object | object identifier |
manually (control key combination) | error value #REF! |
CLEAR([type_num])
Simulates the Edit Clear menu option.
type_num | The type of information to clear from the current selection on the active worksheet. The possible values are: |
|
1 | Clear all |
2 | Clear formats |
3 | Clear contents |
4 | Clear comments |
|
| If this parameter is omitted it defaults to 3. |
COLUMN.WIDTH([width_num], [reference], [standard], [type_num], [standard_num])
Simulates the Format Column menu options.
width_num | The new width of the columns. The width is measured in characters. This parameter is ignored if standard is TRUE or type_num is specified. If this parameter is omitted the width of the specified columns is not changed. |
reference | The columns whose width is to be changed. If this parameter is omitted it defaults to the current selection on the active worksheet. |
standard | Specifies whether the columns are to be set to the standard column width. The possible values are: |
|
TRUE | The columns are set to the standard width. |
FALSE | The columns are not set to the standard width. |
|
| If this parameter is omitted it defaults to FALSE. |
type_num | Specifies whether the columns are to be hidden, unhidden or automatically sized. This parameter is ignored if standard is TRUE. The possible values are: |
|
1 | The columns are hidden. |
2 | The columns are unhidden. |
3 | The columns are set to the width of the longest displayed value. |
|
| If this parameter is omitted the columns will not be hidden, unhidden or auto-sized. |
standard_num | Specifies the value to be used for the standard width. The width is measured in characters. If this parameter is omitted the standard width is not changed. |
COPY([from_reference], [to_reference])
Simulates the Edit Copy menu option.
from_reference | The cells that are to be copied to the clipboard. If this parameter is omitted it defaults to the current selection on the active worksheet. |
to_reference | The cells where the copied information is to be pasted. If this parameter is omitted then the cells are not pasted. |
CUT([from_reference], [to_reference])
Simulates the Edit Cut menu option.
from_reference | The cells that are to be cut to the clipboard. If this parameter is omitted it defaults to the current selection on the active worksheet. |
to_reference | The cells where the cut information is to be pasted. If this parameter is omitted then the cells are not pasted. |
DATA.FORM()
Simulates the Format Data Form menu option.
DATA.SERIES([rowcol], [type_num], [date_num], [step_value], [stop_value], [trend])
Simulates the Edit Fill Series menu option.
rowcol | Indicates whether the series is in rows or columns. The possible values are: |
| |
| If this parameter is omitted it defaults depending on the shape of the current selection. |
type_num | The way in which the series values change. The possible values are: |
|
1 | Linear |
2 | Growth |
3 | Date |
4 | Auto (not currently implemented) |
|
| If this parameter is omitted it defaults to 1. |
date_num | The way in which the date values change. The possible values are: |
|
1 | Day |
2 | Weekday |
3 | Month |
4 | Year |
|
| If this parameter is omitted it defaults to 1. |
step_value | The number used to add to or multiply by the series value. If this parameter is omitted it defaults to 1. |
stop_value | The value at which to stop filling the series. If this parameter is omitted then the entire selection is filled. |
trend | Indicates whether to fill the series using trend values calculated from existing data. The possible values are: |
|
FALSE | Don't use trend values. |
TRUE | Use trend values (not currently implemented). |
|
| If this parameter is omitted it defaults to FALSE. |
DEREF(reference)
Returns the values of cells in a reference. You should not need to use this function, as references are automatically converted to values where necessary when they are used in formulas.
reference | The reference to the cells whose values you want. |
EDIT.DELETE([shift_num])
Simulates the Edit Delete menu option.
shift_num | The way in which the cell deletion is to be processed. The possible values are: |
|
1 | Shift cells left. |
2 | Shift cells up. |
3 | Delete entire rows. |
4 | Delete entire columns. |
|
| If this parameter is omitted it defaults to 2. |
EVALUATE(formula_text)
Returns the result of evaluating a formula.
formula_text | The formula to be evaluated. |
FILL.DOWN()
Simulates the Edit Fill Down menu option.
FILL.LEFT()
Simulates the Edit Fill Left menu option.
FILL.RIGHT()
Simulates the Edit Fill Right menu option.
FILL.UP()
Simulates the Edit Fill Up menu option.
FONT.PROPERTIES([font], [font_style], [size], [strikethrough], [superscript], [subscript], [outline], [shadow], [underline], [color], [normal], [background], [start_char], [char_count])
Formats the cells in the selection on the current worksheet using the specified font attributes.
font | The name of the font to be used. |
font_style | The style to be used. The possible values are: |
|
"Regular" | Removes the bold and italic attributes. |
"Bold" | Sets the font to bold. |
"Italic" | Sets the font to italic. |
"Bold Italic" | Sets the font to bold and italic. |
|
| If this parameter is omitted the style is not changed. |
size | The point size to be used. If this parameter is omitted the size is not changed. |
strikethrough | Sets the strikethrough attribute. The possible values are: |
|
FALSE | The font does not have the strikethrough attribute |
TRUE | The font has the strikethrough attribute |
|
| If this parameter is omitted the strikethrough attribute is not changed. |
superscript | Not currently implemented. |
subscript | Not currently implemented. |
outline | Not currently implemented. |
shadow | Not currently implemented. |
underline | The underline style to be used. The possible values are: |
|
0 | None |
1 | Single |
2 | Double (not currently implemented) |
3 | Single accounting (not currently implemented) |
4 | Double accounting (not currently implemented) |
|
| If this parameter is omitted the underline style is not changed. |
color | The color to be used. The possible values are: |
|
0 | Automatic |
1 to 56 | One of the colors displayed in the Format Cells Font dialog box |
|
| If this parameter is omitted the color is not changed. |
normal | Not currently implemented. |
background | Not currently implemented. |
start_char | Not currently implemented. |
char_count | Not currently implemented. |
FORMAT.NUMBER(format_text)
Formats the cells in the selection on the current worksheet using the specified format.
format_text | The number format to be used. |
FORMULA(formula_text, [reference])
Enters the specified formula in the specified reference.
formula_text | The formula to be inserted. |
reference | The reference where formula to be inserted. If this parameter is omitted it defaults to the cursor position on the active worksheet. |
FORMULA.GOTO([reference], [corner])
Simulates the Edit Goto menu option.
reference | The cells that are to be selected. If the sheet name is omitted then it defaults to the currently active sheet. If this parameter is omitted it defaults to the sheet and cells that were selected before the last goto command. |
corner | Specifies whether the top-left corner of the selection is to be placed in the top-left corner of the window. The possible values are: |
|
FALSE | The sheet is not scrolled to move the selection to the top-left corner of the window. |
TRUE | The sheet is scrolled to move the selection to the top-left corner of the window. |
|
| If this parameter is omitted it defaults to FALSE. |
GET.OBJECT(type_num, [object_id], [start_num], [count_num], [item_index])
Returns information about the specified object.
type_num | The type of information to return. The possible values are: |
|
1 | object type as number: |
| 1 line |
| 2 rectangle |
| 3 oval |
| 6 text box |
| 7 command button |
| 11 check box |
| 12 option button |
| 14 label |
| 16 spinner |
| 17 scroll bar |
| 18 list box |
| 19 group box |
| 20 drop down box |
2 | locked |
3 | z-order |
4 | top-left cell as R1C1 reference |
5 | x-offset of top-left corner in points |
6 | y-offset of top-left corner in points |
7 | bottom-right cell as R1C1 reference |
8 | x-offset of bottom-right corner in points |
9 | y-offset of bottom-right corner in points |
10 | reference of the macro as text |
11 | object positioning as number: |
| 1 move and size with cells |
| 2 move but don't size with cells |
| 3 don't move or size with cells |
12 | object text from start_num for count_num characters |
|
object_id | The object identifier. If this parameter is omitted it defaults to the currently selected object on the active worksheet. |
start_num | The text substring start position for type_num 12. If this parameter is omitted it defaults to 1. |
count_num | The text substring length for type_num 12. If this parameter is omitted it defaults to 255. |
item_index | This parameter is not currently used. |
INPUT(message_text, [type_num], [title_text], [default], [x_pos], [y_pos], [help_ref])
Displays a message in a dialog box with an area for user input and OK and Cancel buttons. If the OK button is pressed then the text entered by the user is returned. If the Cancel button is pressed then this function returns the value FALSE.
message_text | The message that you want displayed in the dialog box. |
type_num | The acceptable data type(s) for the data that you want returned. It can be any combination of the following (to specify more than one value, add the numbers together): |
|
0 | Formula |
1 | Number |
2 | Text |
4 | Logical |
8 | Reference |
16 | Error |
64 | Array |
|
| If this parameter is omitted it defaults to 2. |
title_text | The title to be used for the dialog box. If this parameter is omitted it defaults to "Input". |
default | The text that you want to be initially displayed in the input area of the dialog box. If this parameter is omitted the input area will be blank. |
x_pos | The initial x position on the screen where the dialog box will be displayed. If this parameter is omitted it defaults to the center of the screen. (Not currently supported). |
y_pos | The initial y position on the screen where the dialog box will be displayed. If this parameter is omitted it defaults to the center of the screen. (Not currently supported). |
help_ref | The name of help topic that is to be displayed if the user presses the Help button in the dialog box. If this parameter is omitted then no Help button is displayed. (Not currently supported). |
INSERT([shift_num])
Simulates the Insert Cells menu option.
shift_num | The way in which the cell insertion is to be processed. The possible values are: |
|
1 | Shift cells right. |
2 | Shift cells down. |
3 | Insert entire rows. |
4 | Insert entire columns. |
|
| If this parameter is omitted it defaults to 2. |
PASTE([to_reference])
Simulates the Edit Paste menu option.
to_reference | The cells where the copied information is to be pasted. If this parameter is omitted it defaults to the current selection on the active worksheet. |
PASTE.SPECIAL([paste_num], [operation_num], [skip_blanks], [transpose])
Simulates the Edit Paste Special menu option.
paste_num | The type of information to be pasted. The possible values are: |
|
1 | All |
2 | Formulas |
3 | Values |
4 | Formats |
5 | Comments |
6 | All except borders |
|
| If this parameter is omitted it defaults to 1. |
operation_num | The way in which the data being pasted is merged with existing data. The possible values are: |
|
1 | None |
2 | Add |
3 | Subtract |
4 | Multiply |
5 | Divide |
|
| If this parameter is omitted it defaults to 1. |
skip_blanks | Whether to ignore blank cells in the data being pasted. The possible values are: |
|
FALSE | Blank cells are included. |
TRUE | Blank cells are skipped. |
|
| If this parameter is omitted it defaults to FALSE. |
transpose | Whether to transpose the data being pasted. The possible values are: |
|
FALSE | Data is not transposed. |
TRUE | Data is transposed. (Not currently supported) |
|
| If this parameter is omitted it defaults to FALSE. |
PROTECT.DOCUMENT([contents], [windows], [password], [objects], [scenarios])
Simulates the Format Sheet Protect menu option.
contents | Whether to protect the locked cell contents. The possible values are: |
|
TRUE | The locked cell contents are protected |
FALSE | The locked cell contents are unprotected |
|
| If this parameter is omitted it defaults to TRUE. |
windows | Whether to protect windows from being moved or sized (not currently implemented). The possible values are: |
|
TRUE | The windows are protected |
FALSE | The windows are unprotected |
|
| If this parameter is omitted it defaults to FALSE. |
password | An optional case-sensitive password to protect or unprotect the document (not currently implemented). |
objects | Whether to protect the locked objects (not currently implemented). The possible values are: |
|
TRUE | The locked objects are protected |
FALSE | The locked objects are unprotected |
|
| If this parameter is omitted it defaults to TRUE. |
scenarios | Whether to protect the scenarios (not currently implemented). The possible values are: |
|
TRUE | The scenarios are protected |
FALSE | The scenarios are unprotected |
|
| If this parameter is omitted it defaults to TRUE. |
REFTEXT(reference, [a1])
Returns a string containing the specified reference.
reference | The reference that is to be converted to text. |
a1 | Specifies the style of the reference. The possible values are: |
|
FALSE | R1C1 style |
TRUE | A1 style |
|
| If this parameter is omitted it defaults to FALSE. |
ROW.HEIGHT([height_num], [reference], [standard_height], [type_num])
Simulates the Format Row menu options.
height_num | The new height of the rows. The height is measured in points. This parameter is ignored if standard_height is TRUE or type_num is specified. If this parameter is omitted the height of the specified rows is not changed. |
reference | The rows whose height is to be changed. If this parameter is omitted it defaults to the current selection on the active worksheet. |
standard_height | Specifies whether the rows are to be set to the standard row height. The possible values are: |
|
TRUE | The rows are set to the standard height. |
FALSE | The rows are not set to the standard height. |
|
| If this parameter is omitted it defaults to FALSE. |
type_num | Specifies whether the rows are to be hidden, unhidden or automatically sized. This parameter is ignored if standard_height is TRUE. The possible values are: |
|
1 | The rows are hidden. |
2 | The rows are unhidden. |
3 | The rows are set to the height of the highest displayed value. |
|
| If this parameter is omitted the rows will not be hidden, unhidden or auto-sized. |
SELECT([selection], [active_cell])
Sets the selection and cursor cell on the active worksheet.
selection | The cells that are to be the selection. If this parameter is omitted the current selection is not changed. |
active_cell | The cell that is to be the cursor cell. If this parameter is omitted it defaults to the top left cell in the selection. |
SELECTION()
Returns a reference which represents the current selection on the active worksheet.
SORT([orientation], [key1], [order1], [key2], [order2], [key3], [order3], [header], [custom], [case])
Simulates the Format Data Sort menu option.
orientation | Specifies whether to sort the rows or columns. The possible values are: |
| |
| If this parameter is omitted it defaults to 1. |
key1 | Specifies a reference to the first column or row to sort on. If this parameter is omitted it defaults to the first column or row in the selection. |
order1 | Specifies whether to sort the data in ascending or descending order. The possible values are: |
| |
| If this parameter is omitted it defaults to 1. |
key2 | Specifies a reference to the second column or row to sort on. If this parameter is omitted then the second key is not used. |
order2 | Specifies whether to sort the data in ascending or descending order. The possible values are: |
| |
| If this parameter is omitted it defaults to 1. |
key3 | Specifies a reference to the third column or row to sort on. If this parameter is omitted then the third key is not used. |
order3 | Specifies whether to sort the data in ascending or descending order. The possible values are: |
| |
| If this parameter is omitted it defaults to 1. |
header | Specifies whether the selected cells include a header row or column. The possible values are: |
|
0 | Determine the presence of a header automatically |
1 | There is a header |
2 | There is no header |
|
| If this parameter is omitted it defaults to 2. |
custom | Specifies whether to use a custom list for the first sort key. The possible values are: |
|
1 | Use normal sort sequence |
2 | Day short names |
3 | Day long names |
4 | Month short names |
5 | Month long names |
|
| If this parameter is omitted it defaults to 1. |
case | Specifies whether the sort should be case-sensitive. The possible values are: |
|
TRUE | The sort is case-sensitive |
FALSE | The sort is not case-sensitive |
|
| If this parameter is omitted it defaults to FALSE. |
TEXTREF(text, [a1])
Returns a reference corresponding to the specified text.
text | The string containing the reference. |
a1 | Specifies the style of the reference. The possible values are: |
|
FALSE | R1C1 style |
TRUE | A1 style |
|
| If this parameter is omitted it defaults to FALSE. |
WORKBOOK.ACTIVATE(sheet_name)
Specifies the sheet that is to be the active worksheet.
sheet_name | The sheet that is to be the active worksheet. |
WORKBOOK.DELETE([sheet_name])
Deletes the specified sheet from the workbook.
sheet_name | The sheet that is to be deleted. If this parameter is omitted it defaults to the currently active sheet. |
WORKBOOK.INSERT([type_num])
Adds a new sheet to the workbook.
type_num | The type of sheet that is to be added. The possible values are: |
|
1 | A worksheet |
2 | A chart |
3 | A macro sheet |
|
| If this parameter is omitted it defaults to the type of the currently active sheet. |
WORKBOOK.NAME(old_sheet_name, new_sheet_name)
Renames the specified sheet in the workbook.
old_sheet_name | The sheet that is to be renamed. |
new_sheet_name | The new name for the sheet. |