{CHART-ASSIGN-RANGE}

{CHART-ASSIGN-RANGE range;method} assigns all data ranges for the current chart.

Arguments

range is the name or address of a range that will be used to create all the desired data ranges.

method is text that specifies how 1-2-3 assigns the data ranges.

method	1-2-3 does the following

by-row	Assigns the A - W data ranges by row. If the first row of the range contains labels or dates, 1-2-3 assigns it as the X data range. If the first column of the range contains labels, 1-2-3 assigns it as the legends range.

by-column	Assigns the A - W data ranges by column. If the first column of the range contains labels or dates, 1-2-3 assigns it as the X data range. If the first row of the range contains labels, 1-2-3 assigns it as the legends range.

Examples

The following command assigns all data ranges for the current chart, by column, from the range NEW_RANGES. 1-2-3 uses the first row of data in NEW_RANGES as the x-axis labels.

{CHART-ASSIGN-RANGE NEW_RANGES;"by-column"}

See also

Equivalent 1-2-3 command

Chart Ranges

Help

{CHART-RANGE}

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-AXIS-INTERVALS} and {CHART-AXIS-LIMITS}
{CHART-AXIS-INTERVALS}
{CHART-AXIS-LIMITS}

{CHART-AXIS-INTERVALS axis;[major];[minor];[major-interval];[minor-interval]} changes the intervals between X-axis, Y-axis, or 2nd Y-axis tick marks in the current chart.

{CHART-AXIS-LIMITS axis;[upper];[lower];[upper-limit];[lower-limit]} creates, for the current chart, a scale for the X-axis, Y-axis, or 2nd Y-axis that displays only the data that falls between (and includes) upper-limit and lower-limit.

Arguments

axis is text that specifies the axis you want to work with.You can use X, Y, or 2Y.

major is a yes/no argument that specifies whether you specify the major interval. If you choose to specify the major interval, include major-interval. If you choose not to specify the major interval, 1-2-3 automatically calculates it.

minor is a yes/no argument that specifies whether you specify the minor interval. If you choose to specify the minor interval, include minor-interval. If you choose not to specify the minor interval, 1-2-3 automatically calculates it.

major-interval is a value that specifies the major interval.

minor-interval is a value that specifies the minor interval.

upper is a yes/no argument that specifies whether you specify the upper limit. If you choose to specify the upper limit, include upper-limit. If you choose not to specify the upper limit, 1-2-3 automatically calculates it.

lower is a yes/no argument that specifies whether you specify the lower limit. If you choose to specify the lower limit, include lower-limit. If you choose not to specify the lower limit, 1-2-3 automatically calculates it.

upper-limit is a value that specifies the upper limit.

lower-limit is a value that specifies the lower limit.

Notes

If you omit an optional argument, 1-2-3 leaves that setting unchanged.

Examples

The following commands select the chart named QUARTERLY and then set the Y-axis major and minor intervals to 100 and 25, respectively.

{SELECT "quarterly";;"chart"}

{CHART-AXIS-INTERVALS "y";"yes";"yes";100;25}

The following commands select the chart named QUARTERLY and then set the Y-axis upper and lower limits at 250 and 0, respectively.

{SELECT "quarterly";;"chart"}

{CHART-AXIS-LIMITS "y";"yes";"yes";250;0}

See also

Equivalent 1-2-3 commands

Chart Axis X-Axis

Chart Axis Y-Axis

Chart Axis 2nd Y-Axis

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-AXIS-SCALE-TYPE}

{CHART-AXIS-SCALE-TYPE axis;type} specifies the type of scale to use for an axis in the current chart.

Arguments

axis is text that specifies the axis you want to work with.You can use X, Y, or 2Y.

type is text that specifies the type of scale to use for axis.

type	1-2-3 does the following

standard	Increases scale numbers linearly by a fixed number of units

log	Increases scale numbers logarithmically

100%	Displays scale numbers that range from 0 through 100% and represent percentages instead of absolute values; not available for the X-axis.

Examples

The following command uses a logarithmic scale for the current chart's Y-axis.

{CHART-AXIS-SCALE-TYPE "y";"log"}|

See also

Equivalent 1-2-3 commands

Chart Axis X-Axis Options

Chart Axis Y-Axis Options

Chart Axis 2nd Y-Axis Options

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-AXIS-TICKS}

{CHART-AXIS-TICKS axis;[major];[minor];[space]} is used to specify major and minor tick marks for an axis in the specified chart.

Arguments

axis is text that specifies the axis you want to work with.You can use X, Y, or 2Y.

major is a yes/no argument that specifies whether 1-2-3 displays tick marks at major intervals.

minor is a yes/no argument that specifies whether 1-2-3 displays tick marks at minor intervals.

If you omit an axis, major, or minor, 1-2-3 leaves that setting unchanged.

space is an integer that specifies how many ticks appear between labels. If you omit space, 1-2-3 uses 1.

Examples

The following macro selects the chart named QUARTERLY, sets the major interval for the Y-axis at 10, and displays tick marks only at major intervals.

{SELECT "quarterly";;"chart"}

{CHART-AXIS-INTERVALS "y";"yes";"no";10}

{CHART-AXIS-TICKS "y";"yes";"no"}

See also

Equivalent 1-2-3 commands

Chart Axis X-Axis

Chart Axis Y-Axis

Chart Axis 2nd Y-Axis

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-AXIS-TITLE}

{CHART-AXIS-TITLE axis;[title];[title-cell]} changes an axis title in the current chart.

Arguments

axis is text that specifies the axis you want to work with.You can use X, Y, or 2Y.

title is text enclosed in " " (quotation marks) that specifies the axis title.

Note to use the contents of a cell for the axis title, use title-cell.

title-cell is the name or address of a cell that contains a label to use as the axis title. If you include both title and title-cell, 1-2-3 ignores title.

Notes

If you omit an optional argument, 1-2-3 leaves that setting unchanged.

To delete existing an axis title, specify "" for title.

Examples

The following command changes the X-axis title of the current chart to Months.

{CHART-AXIS-TITLE "x";"Months"}

The following commands change the X-axis and Y-axis titles of Chart 1 to the contents of the cells X_TITLE and Y_TITLE, respectively.

{SELECT "Chart 1";;"chart"}

{CHART-AXIS-TITLE "x";;X_TITLE}

{CHART-AXIS-TITLE "y";;Y_TITLE}

See also

Equivalent 1-2-3 commands

Chart Axis X-Axis

Chart Axis Y-Axis

Chart Axis 2nd Y-Axis

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-AXIS-UNITS}

{CHART-AXIS-UNITS axis;[manual-calculate];[manual-title];[exponent];[title];[title-cell]} changes the magnitude of the axis units and the axis-unit titles for the current chart.

Arguments

axis is text that specifies the axis you want to work with.You can use X, Y, or 2Y.

manual-calculate is a yes/no argument that specifies whether you want to specify an order of magnitude for the axis scale. If you choose to specify the order of magnitude, include exponent. If you choose not to specify the order of magnitude, 1-2-3 automatically calculates it.

manual-title is a yes/no argument that specifies whether you want to create the axis units title. If you choose to create the axis units title, include title or title-cell. If you choose not to create the axis units title 1-2-3 automatically generates it.

exponent is an integer from -95 through 95 that specifies the order of magnitude for the axis scale (the power of 10 by which the numbers along the scale must be multiplied to reflect the values you are charting).

title is text enclosed in " " (quotation marks) that specifies the units title.

Note To use the contents of a cell for the units title, use title-cell.

title-cell is the name or address of a cell that contains a label to use as the units title. If you include both title and title-cell, 1-2-3 ignores title.

Notes

If you omit an optional argument, 1-2-3 leaves that setting unchanged.

To delete existing an units title, specify "" for title or specify a blank cell for title-cell.

Examples

The following commands select the chart DRIVE, change the magnitude of the Y-axis units to tens, and change the unit title to Miles-Per-Hour.

{SELECT "drive";;"chart"}

{CHART-AXIS-UNITS "y";"yes";"yes";1;"Miles-Per-Hour"}

See also

Equivalent 1-2-3 commands

Chart Axis X-Axis Options

Chart Axis Y-Axis Options

Chart Axis 2nd Y-Axis Options

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-COLOR-RANGE} and {CHART-PATTERN-RANGE}
{CHART-COLOR-RANGE}
{CHART-PATTERN-RANGE}

{CHART-COLOR-RANGE series;[color-range]} sets the color for each value in a data series in the current chart, using values in color-range.

{CHART-PATTERN-RANGE series;[pattern-range]} sets the pattern for each value in a data series in the current chart, using values in pattern-range.

Arguments

series is text that specifies a single data series in the current chart.

Specify a letter from A through W for series.

color-range and pattern-range are the names or addresses of ranges whose values determine the color or pattern for each value in a data range.

See Setting Up a Colors or Patterns Range for information about the contents of a colors or patterns range.

If you omit color-range or pattern-range, 1-2-3 uses the default color or pattern for series.

Examples

The following command uses the values in the range B_COLORS to set the color for each bar in the B data series of the current chart.

{CHART-COLOR-RANGE "b";B_COLORS}

The following command uses the values in the range C1..C6 to set the pattern for each slice of the current pie chart.

{CHART-PATTERN-RANGE "a";C1..C6}

See also

Equivalent 1-2-3 command

Chart Numeric Color

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-DATA-LABELS}

{CHART-DATA-LABELS series;[label-range];[position]} creates labels for data points or bars in the current chart, using data in label-range as the labels.

Arguments

series is text that specifies a single data series, or all data series, in the current chart.

To assign data labels for a single data series, specify a letter from A through W for series; to assign data labels for all data series, specify the word All for series.

label-range is the name or address of a range that contains the data labels. If you omit label-range, 1-2-3 removes the data label for series from the chart.

position is text that specifies location of the data labels. If you omit position, 1-2-3 leaves the setting unchanged.

position	1-2-3 places the data label

center	Centers the data labels on the data points in a line chart and area chart and above the bars in a bar chart.

right	To the right of the data points in a line chart and area chart and above the bars in a bar chart.

below	Below the data points in a line chart and area chart and below the bars in a bar chart.

left	To the left of the data points in a line chart and area chart and above the bars in a bar chart.

above	Above the data points in a line chart and area chart and above the bars in a bar chart.

Examples

The following command creates data labels for all series in the chart WEST COAST from data in the range LABELS. 1-2-3 places the data labels above the bars in the chart.

{SELECT "west coast";;"chart"}

{CHART-DATA-LABELS "all";LABELS;"above"}

See also

Equivalent 1-2-3 command

Chart Data Labels

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-FOOTNOTE} and {CHART-TITLE}
{CHART-FOOTNOTE}
{CHART-TITLE}

{CHART-FOOTNOTE [line1];[line2];[position];[cell1];[cell2]} adds chart footnotes to the current chart.

{CHART-TITLE [line1];[line2];[position];[cell1];[cell2]} adds chart titles to the current chart.

Arguments

line1 is text enclosed in " " (quotation marks) that specifies the first line of the chart title or footnote.

line2 is text enclosed in " " (quotation marks) that specifies the second line of the chart title or footnote.

Note to use the contents of a cell for the first or second line of a chart title or footnote, use cell1 or cell2.

position is text that specifies location of the title or footnote.

position	1-2-3 does the following

left	Left-aligns the title or footnote in the chart

center	Centers the title or footnote in the chart; default if you omit the argument

right	Right-aligns the title or footnote in the chart

cell1 is the name or address of a range that contains a label to use as the first line of the chart title or footnote. If you include both line1 and cell1, 1-2-3 ignores line1.

cell2 is the name or address of a range that contains a label to use as the second line of the chart title or footnote. If you include both line2 and cell2, 1-2-3 ignores line2.

Notes

If you omit an optional argument, 1-2-3 leaves that setting unchanged.

To delete existing chart title or footnote lines, specify "" for line1 or line2.

Examples

The following commands change the title of the chart PROFITS to July Profits and center the title.

{SELECT "profits";;"chart"}

{CHART-TITLE "July Profits";;"center"}

The following commands change the first line of the footnote of the chart EXPENSES to the contents of the cell NOTE, and delete the second line of the footnote.

{SELECT "expenses";;"chart"}

{CHART-FOOTNOTE ;"";;NOTE}

See also

Equivalent 1-2-3 command

Chart Headings

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-GRID}

{CHART-GRID axis;[major];[minor]} displays or hides grid lines for an axis in the current chart.

Arguments

axis is text that specifies the axis from which the grid lines originate.

axis	1-2-3 displays or hides

x	Vertical grid lines that originate from the X-axis

y	Horizontal grid lines that originate from the Y-axis

2y	Horizontal grid lines that originate from the 2nd Y-axis

major is a yes/no argument that specifies whether to display or hide grid lines that originate from major-interval tick marks.

minor is a yes/no argument that specifies whether to display or hide grid lines that originate from minor-interval tick marks.

If you omit major or minor, 1-2-3 leaves the setting unchanged.

Notes

If the current chart is a pie chart or radar chart, 1-2-3 ignores {CHART-GRID}.

Examples

The following commands select the chart named PROFITS, and then add major-interval grid lines to the x-axis and y-axis.

{SELECT "profits";;"chart"}

{CHART-GRID "x";"yes";"no"}

{CHART-GRID "y";"yes";"no"}

See also

Equivalent 1-2-3 command

Chart Girds

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-LEGEND}

{CHART-LEGEND series;[legend]; [position];[legend-range]} creates legend labels that identify the colors, symbols, or patterns of the current chart's data series.

Arguments

series is text that specifies a single data series, or all data series, in the current chart.

To assign legend labels for a single data series, specify a letter from A through W for series; to assign legend labels for all data series, specify the word All for series.

legend is text enclosed in " " (quotation marks) that specifies the legend label for series.

If series is All, omit legend.

Note To use the contents of a cell for the legend label, use legend-range.

position is text that specifies location of the legend label. If you omit position, 1-2-3 leaves the setting unchanged.

position	1-2-3 places the legend label

right	To the right of the chart

below	Below the chart

manual	Leaves the setting unchanged

legend-range is the name or address of a cell or range.

• 	If series specifies a single data series, legend-range specifies a cell that contains a label.

• 	If series specifies all data series, legend-range specifies a range that contains as many labels as there are data series in the current chart.

If you include both legend and legend-range, 1-2-3 ignores legend.

Examples

The following command creates legend labels for all series in the current chart. 1-2-3 creates the labels from the data in the range LABELS and places the legend labels below the chart.

{CHART-LEGEND "all";;"below";LABELS}

See also

Equivalent 1-2-3 command

Chart Legend

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-NEW}

{CHART-NEW location;[type];[style];[name]} draws a chart at location, using data from the currently selected range.

Arguments

location is the name or address of a range in the current file, on which 1-2-3 draws the new chart.

Note If location is a single cell, 1-2-3 does not automatically place and size the chart; the chart appears only in the single cell.

type is text that specifies a chart type. You can use Line, Area, Bar, Pie, XY, HLCO, Mixed, Radar, 3D-Line, 3D-Area, 3D-Bar, or 3D-Pie.

If you omit type, 1-2-3 uses the default chart type.

For a complete description of chart types, see Chapter 15 of the User's Guide.

style is an offset number that specifies a style in the Chart Type dialog box for the type of chart you specified.

If you omit style, 1-2-3 uses the first style.

Note 1-2-3 displays the styles in two columns and up to three rows. 1-2-3 numbers the styles from left to right and top to bottom.

name is text that specifies a name for the chart. If you omit name, 1-2-3 assigns a default name, such as CHART 1.

Examples

The following commands create a stacked bar chart named JULY SALES in B:A1..B:D15. 1-2-3 creates the chart from data in the range JULY.

{SELECT JULY}

{CHART-NEW B:A1..B:D15;"bar";2;"july sales"}

See also

Equivalent 1-2-3 command

Tools Chart

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-PIE-LABELS} and {CHART-PIE-SLICE-EXPLOSION}
{CHART-PIE-LABELS}
{CHART-PIE-SLICE-EXPLOSION}

{CHART-PIE-LABELS [values];[percentage];[x-range];[c-range]} creates labels for the current pie chart.

{CHART-PIE-SLICE-EXPLOSION explosion-type;[all-by-%]} explodes slices in the current pie chart.

Arguments

values is a yes/no argument that specifies whether to display the values in the A data range as pie-slice labels.

percentages is a yes/no argument that specifies whether to display each value in the A data range, as a percentage of 100.

x-range is a yes/no argument that specifies whether to display the contents of the X data range.

c-range is a yes/no argument that specifies whether 1-2-3 uses values you enter in the C data range to show or hide percentages.

explosion-type is text that specifies how 1-2-3 explodes slices.

explosion-type	1-2-3 explodes

none	No slices

all	All slices by the percent specified by all-by-%

using-b	Slices based on data in the B data range

all-by-% is an integer from 1 through 100 that specifies a percent by which to explode the slices if explosion-type is All.

Notes

If you omit an optional argument, 1-2-3 leaves that setting unchanged.

Examples

The following commands select the pie chart named MARKET SHARE and label the slices only with the contents of the X data range.

{SELECT "market share";;"chart"}

{CHART-PIE-LABELS "no";"no";"yes";"no"}

The following command explodes all slices of the current pie chart by 25%.

{CHART-PIE-SLICE-EXPLOSION "all";25}

See also

Equivalent 1-2-3 command

Chart Data Labels

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-RANGE} and {CHART-RANGE-DELETE}
{CHART-RANGE}
{CHART-RANGE-DELETE}

{CHART-RANGE series;[series-range];[series-type];[2y-axis]} sets the data range, series type and 2nd Y axis flag for a data series in the current chart.

{CHART-RANGE-DELETE series} deletes the series from the current chart.

Arguments

series is a letter from A through Z, excluding Y, entered as text, that specifies a data series in the current chart.

If series is Z, 1-2-3 uses the contents of series-range as the legend range.

series-range is the name or address of the data range for series.

series-type is text that specifies the type of series for mixed charts. You can use Area, Line, or Bar.

2y-axis is a yes/no argument that specifies whether this series should be plotted on the 2nd y-axis.

Notes

If you omit an optional argument, 1-2-3 leaves that setting unchanged.

Examples

The following command uses the values in the range NEW_C for the C data series of the current chart.

{CHART-RANGE "c";NEW_C}

The following command removes the C data series from the current chart.

{CHART-RANGE-DELETE "c"}

See also

Equivalent 1-2-3 command

Chart Ranges

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-RENAME}

{CHART-RENAME old-name;new-name} renames a chart.

Arguments

old-name is text that specifies the current name of the chart.

new-name is text that specifies the new name you want to give the chart.

Examples

The following command renames the chart CHART 1 as4 EXPENSES.

{CHART-RENAME "chart 1";"expenses"}

See also

Equivalent 1-2-3 command

Chart Name

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-SET-PREFERRED} and {CHART-USE-PREFERRED}
{CHART-SET-PREFERRED}
{CHART-USE-PREFERRED}

{CHART-SET-PREFERRED} defines current chart's type as the default chart type and defines the selected chart's grid settings as the default grid settings.

{CHART-USE-PREFERRED} changes the current chart to the default chart type and changes the current chart's grid settings to the default grid settings.

Examples

The following commands select the chart named Sales and change it to the default chart type.

{SELECT "Sales";;"chart"}

{CHART-USE-PREFERRED}

See also

Equivalent 1-2-3 commands

Chart Set Preferred

Chart Use Preferred

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHART-TYPE}

{CHART-TYPE type;[style];[orientation];[value-tables];[auto-position]} sets the type of chart for the current chart.

Arguments

type is text that specifies a chart type. You can use Line, Area, Bar, Pie, XY, HLCO, Mixed, Radar, 3D-Line, 3D-Area, 3D-Bar, or 3D-Pie.

For a complete description of chart types, see Chapter 15 of the User's Guide.

style is an offset number that specifies a style in the Chart Type dialog box for the type of chart you specified.

If you omit style, 1-2-3 uses the first style.

Note 1-2-3 displays the styles in two columns and up to three rows. 1-2-3 numbers the styles from left to right and top to bottom.

orientation is text that specifies the orientation of the chart.

orientation	1-2-3 displays

vertical	The x-axis across the bottom of the chart, the y-axis along the left edge of the chart, and the 2nd y-axis along the right edge of the chart; default if you omit the argument

horizontal	The x-axis along the left edge of the chart, the y-axis across the top of the chart, and the 2nd y-axis along the bottom of the chart.

value-tables is a yes/no argument that specifies whether to controlling whether display data values under the chart. If you omit value-tables, 1-2-3 does not display the values.

If type is Pie, HLCO, Radar, or XY, or, if orientation is Horizontal, 1-2-3 ignores value-tables.

auto-position is a yes/no argument that specifies whether 1-2-3 automatically places the plot within the chart.

Notes

If you omit an optional argument, 1-2-3 leaves that setting unchanged.

Examples

The following command changes the chart named CHART 1 to a horizontal stacked bar chart.

{CHART-TYPE "bar";1;"horizontal"}

See also

Equivalent 1-2-3 command

Chart Type

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{RANGE-VERSION?}

{RANGE-VERSION? [option]} displays the Version Manager window.

Arguments

option is text that specifies whether to close the Version Manager window or open it in either Manager or Index form.

option	1-2-3 does the following

on	Opens and activates the Version Manager window in the last form it was previously displayed in (Manager or Index); default if you omit the argument.

off	Closes the Version Manager window, if it is open; otherwise, has no effect

manager	Opens the Version Manager window and activates the Manager

index	Opens the Version Manager window and activates the Index

Notes

Macro execution continues as soon as 1-2-3 displays the Version Manager window. Use {RANGE-VERSION?} to display Version Manager information during a macro.

Examples

The following command opens and activates the Version Manager window.

{RANGE-VERSION? "on"}

See also

Equivalent 1-2-3 command

Range Version

Help

Macros

Tutorial

Lesson 8, "Using Version Manager"

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{SCENARIO-ADD-VERSION}

{SCENARIO-ADD-VERSION scenario-name;[scenario-creator];version-range;version-name;[version-creator]} adds a version to a scenario.

Arguments

scenario-name is text that specifies the name of the scenario. 1-2-3 is case-sensitive for scenario-name.

scenario-creator is text that specifies the name of the user who created the scenario.

If you omit scenario-creator, 1-2-3 uses the most recently created scenario specified by scenario-name.

version-range is the name of range that contains the version to add. version-range must be an existing named range.

version-name is text that specifies an existing version for version-range. 1-2-3 is case-sensitive for version-name.

version-creator is text that specifies the name of the user who created the version.

If you omit version-creator, 1-2-3 uses the most recently created version for the range version-range specified by version-name.

Examples

The following command adds the version Moderate Growth for the range SALES to the scenario Moderate Growth.

{SCENARIO-ADD-VERSION "Moderate Growth";;SALES;"Moderate Growth"}

See also

Equivalent Version Manager button

Displays the Create Scenario dialog box

Help

Macros

Tutorial

Lesson 8, "Using Version Manager"

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{SCENARIO-CREATE}, {SCENARIO-DELETE}, {SCENARIO-SHOW}
{SCENARIO-CREATE}
{SCENARIO-DELETE}
{SCENARIO-SHOW}

{SCENARIO-CREATE name;[share];[comment]} creates a scenario.

{SCENARIO-DELETE name;[creator]} deletes a scenario.

{SCENARIO-SHOW name;[creator]} displays in the worksheet the selected scenario.

Arguments

name is text that specifies the name of the scenario. If you are creating a scenario and name already exists, 1-2-3 creates a new scenario with the same name and a different date/time stamp. 1-2-3 is case-sensitive for name.

share is text that specifies the sharing option for the version.

share	1-2-3 does the following

unprotected	Applies no protection to the scenario; default if you omit the argument

protected	Prevents changes to the scenario

hidden	Prevents changes to and hides the scenario

comment is text that specifies a comment about the scenario. If you omit comment, 1-2-3 does not include a comment.

creator is text that specifies the name of the user who created the scenario. 1-2-3 uses creator to help determine which scenario to use or delete.

If you omit creator, 1-2-3 uses the most recently created scenario specified by name.

Examples

The following command creates a scenario named Moderate Growth, prevents changes to the scenario, and adds the comment Includes all moderate growth versions.

{SCENARIO-CREATE "Moderate Growth";"protected";"Includes all moderate growth versions"}

The following command deletes the scenario named Slow Growth that was created by Isabella Martinez.

{SCENARIO-DELETE "Slow Growth";"Isabella Martinez"}

The following command displays the scenario Moderate Growth that was created by Isabella Martinez.

{SCENARIO-SHOW "Moderate Growth";"Isabella Martinez"}

See also

Equivalent Version Manager buttons

Displays the Create Scenario dialog box

Deletes a scenario

Shows the currently selected versions or scenarios in the worksheet.

Help

Macros

Tutorial

Lesson 8, "Using Version Manager"

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{VERSION-CREATE}, {VERSION-DELETE}, {VERSION-SHOW}, {VERSION-UPDATE}
{VERSION-CREATE}
{VERSION-DELETE}
{VERSION-SHOW}
{VERSION-UPDATE}

{VERSION-CREATE version-range;name;[share];[retain-styles];[comment]} creates a new version.

{VERSION-DELETE version-range;name;[creator]} deletes the specified version.

{VERSION-SHOW version-range;name;[creator];[goto]} displays in the worksheet the selected version.

{VERSION-UPDATE version-range;name;[creator]} updates an already existing version with new data you enter in its named range.

Arguments

version-range is the name of range that contains the version. version-range must be an existing named range.

name is text that specifies the name of the version. If you are creating a version and name already exists, 1-2-3 creates a new version with the same name and a different date/time stamp. 1-2-3 is case-sensitive for name.

share is text that specifies the sharing option for the version.

share	1-2-3 does the following

unprotected	Applies no protection to the version; default if you omit the argument

protected	Prevents changes to the version

hidden	Prevents changes to and hides the version

retain-styles is a yes/no argument that specifies whether to save style information with the version. If you omit retain-styles, 1-2-3 ignores the style information.

comment is text that specifies a comment about the version. If you omit comment, 1-2-3 does not include a comment.

creator is text that specifies the name of the user who created the version. 1-2-3 uses creator to help determine which version to use or delete.

If you omit creator, 1-2-3 uses the most recently created version for the range version-range specified by name.

goto is a yes/no argument that specifies whether or not to scroll to version-range. If you omit goto, 1-2-3 does not scroll to the range.

Examples

The following command creates a version named Slow Growth for the range COG, saves style information with the version, and adds a comment.

{VERSION-CREATE COG;"Slow Growth";;"yes";"Assumes COG grows 1% each month"}

The following command deletes the version Slow Growth for the range COG. The version was created by Isabella Martinez.

{VERSION-DELETE COG;"Slow Growth";"Isabella Martinez"}

The following command displays the version Moderate Growth in the range SALES.

{VERSION-SHOW SALES;"Moderate Growth"}

Suppose you changed the formulas in the Cost of Goods (COG) range. The following macro updates the Slow Growth version of theCOG range with the new formulas.

{VERSION-UPDATE COG;"Slow Growth"}

See also

Equivalent Version Manager buttons

Displays the Create Version dialog box

Deletes a version

Shows the currently selected versions or scenarios in the worksheet.

Updates data and styles for a version

Help

Macros

Tutorial

Lesson 8, "Using Version Manager"

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{VERSION-INDEX-MERGE}

{VERSION-INDEX-MERGE source-file;[date-filter];[user-filter];[table-location]} copies versions and scenarios from source-file into the current file.

Arguments

source-file is the name of the file that contains the versions and scenarios you want to merge. source-file must be in memory. If source-file is not in the current directory, include a path.

date-filter tells 1-2-3 to merge only versions and scenarios created or modified on or after a particular date. date-filter is a date number or text that specifies the date in day-month-year, day-month, or Long International Date format.

user-filter tells 1-2-3 to merge only versions and scenarios created or last modified by a particular user. user-filter is text that specifies the user's name.

table-location is the name or address of the range where you want 1-2-3 to create the table of merge results. Specify either the entire range or only the first cell.

Caution  The table occupies one column and as many rows as there are merge results, plus one blank row. 1-2-3 writes over any existing data in table-location.

If there are more rows in the table than there are rows remaining in the worksheet, 1-2-3 truncates the table.

If you omit table-location, 1-2-3 does not create a table of merge results.

Notes

1-2-3 does not merge hidden versions and scenarios.

If you do not include date-filter or user-filter, 1-2-3 copies versions from named ranges in source-file to ranges of the same size and with the same names in the active file. If a version or scenario in the active file has the same name, creation date, last modified date, and last user as a version or scenario in source-file, the version or scenario in the source file is not merged.

Examples

The following command merges all versions and scenarios from the file JULY.WK4.

{VERSION-INDEX-MERGE "C:\PROJECT\JULY.WK4"}

The following command merges versions and scenarios from the file CHICAGO.WK4 that were created or modified on or after April 5, 1993.

{VERSION-INDEX-MERGE "CHICAGO.WK4";@DATE(93;4;5)}

The following command merges versions and scenarios from the file CHICAGO.WK4 that were last modified by John Greene.

{VERSION-INDEX-MERGE "CHICAGO.WK4";;"John Greene")

See also

Equivalent Version Manager buttons

Displays the Merge Versions & Scenarios dialog box

Help

Merge Results

Macros

Tutorial

Lesson 8, "Using Version Manager"

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{VERSION-INFO}

{VERSION-INFO version-range;name;[creator];[share];[retain-styles]} lets you modify style retention and sharing options for a version.

Arguments

version-range is the name of range that contains the version. version-range must be an existing named range.

name is text that specifies the name of the version. 1-2-3 is case-sensitive for name.

creator is text that specifies the name of the user who created the version. 1-2-3 uses creator to help determine which version to use.

If you omit creator, 1-2-3 uses the most recently created version for the range version-range specified by name.

share is text that specifies the sharing option for the version. If you omit share, the sharing option for the version remains unchanged.

share	1-2-3 does the following

unprotected	Applies no protection to the version

protected	Prevents changes to the version

hidden	Prevents changes to and hides the version

retain-styles is a yes/no argument that specifies whether to save style information with the version. If you omit retain-styles, 1-2-3 ignores the style information.

Examples

The following command prevents changes to the version Moderate Growth of the range SALES.

{VERSION-INFO SALES;"Moderate Growth";;"protected"}

See also

Equivalent Version Manager button

Displays the  Version Info dialog box

Help

Macros

Tutorial

Lesson 8, "Using Version Manager"

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CROSSTAB}

{CROSSTAB database-table;row-headings;col-headings;summary-field;summary-method} creates a cross-tabulation table.

Arguments

database-table is the name or address of a database table.

row-headings is text that specifies the name of the field whose entries you want to use as row headings.

col-headings is text that specifies the name of the field whose entries you want to use as column headings.

summary-field is text that specifies the name of the field whose values you want to be summarized in the cells of the crosstab table.

summary-method is text that specifies the method used to summarize in the cross tabulation.

summary-method	1-2-3 does the following

sum	Adds the values

avg	Averages the values

count	Counts the values

min	Finds the smallest value

max	Finds the largest value

Notes

1-2-3 creates a cross-tabulation table and places the table on a new worksheet. 1-2-3 inserts the new worksheet after the worksheet that contains the database table.

Examples

The following command creates a cross-tabulation table using the database table RECYCLE. Row headings are entries from the field Region and column headings are entries from the field Type. 1-2-3 totals the values in the field Q1.

{CROSSTAB RECYCLE;"Region";"Type";"Q1";"sum"}

See also

Equivalent 1-2-3 command

Tools Database Crosstab

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{DATABASE-APPEND}, {DATABASE-DELETE}, {DATABASE-FIND}
{DATABASE-APPEND}
{DATABASE-DELETE}
{DATABASE-FIND}

{DATABASE-APPEND source-range;database-table} adds new records to database-table.

{DATABASE-DELETE database-table;criteria} deletes the records from database-table that meet criteria.

{DATABASE-FIND database-table;criteria} locates and selects records in database-table that meet criteria.

Arguments

source-range is the name or address of the range that contains the records to append to database-table. source-range can be a 1-2-3 range or an external database table.

The first row of source-range must contain field names that are the same as those in the table to which you are appending records.

database-table is the name or address of a database table.

criteria is text that specifies a criteria formula.

Enclose text in criteria in "" "" (double quotation marks). For example, to specify all customers who live in Los Angeles, use "City=""Los Angeles""".

Notes

You cannot use {DATABASE-FIND} with external database tables.

Examples

The following command adds the records from the range MARCH to the database table FIRST_QUARTER.

{DATABASE-APPEND MARCH; FIRST_QUARTER}

The following command deletes the records that were entered before January 1, 1990 from the database table RECYCLE.

{DATABASE-DELETE RECYCLE;"Date<@DATE(90;1;1)"}

The following command creates a collection of all records in the database table PERSONNEL of employees who work in the Finance department.

{DATABASE-FIND PERSONNEL;"Department=""Finance"""}

See also

Equivalent 1-2-3 commands

Tools Database Append Records

Tools Database Delete Records

Tools Database Find Records

Help

About Criteria

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{DATABASE-CONNECT}, {DATABASE-CREATE-TABLE}, {DATABASE-DISCONNECT}, {DATABASE-SEND-COMMAND}
{DATABASE-CONNECT}
{DATABASE-CREATE-TABLE}
{DATABASE-DISCONNECT}
{DATABASE-SEND-COMMAND}

{DATABASE-CONNECT driver-name;[driver-user-id];[driver-password];[connection-string];db-name;[db-user-id];[db-password];[owner-name];table-name;[range-name]} establishes a connection to an external database table so you can use the table with other 1-2-3 commands.

{DATABASE-CREATE-TABLE driver-name;[driver-user-id];[driver-password];db-name;[db-user-id];[db-password];[owner-name];table-name;[range-name];[creation-string];model-table} sets up the structure for and connects to a new table in an external database.

{DATABASE-DISCONNECT range-name} disconnects an external table, ending all data exchange between 1-2-3 and the external table.

{DATABASE-SEND-COMMAND driver-name;[driver-user-id];[driver-password];[connection-string];db-name;[db-user-id];[db-password];command} sends a command to an external database.

Arguments

driver-name is text that specifies the name of the database driver associated with an external database. This external database contains the table to which you want to connect.

driver-user-id is text that specifies the driver user ID.

driver-password is text that specifies the driver password.

If you omit driver-user-id or driver-password, and a user ID or password is required, 1-2-3 displays an ID / password dialog box.

connection-string is text that specifies of additional information which may be needed to connect to a driver. You must enclose connection-string in " " (quotation marks).

db-name is text that specifies the name of the external database that contains the table to which you want to connect.

db-user-id is text that specifies the database user ID.

db-password is text that specifies the database password.

If you omit db-user-id or db-password, and a user ID or password is required, 1-2-3 displays an ID / password dialog box.

owner-name is text that specifies the name of the owner of the table, if one is required.

table-name is text that specifies the name of the external table you want to create or connect to.

range-name is text that specifies the range name of the table.

If you omit range-name in {DATABASE-CONNECT}, the range name in 1-2-3 will be the same as table-name.

model-table is the name or address of a 1-2-3 database table or the name of an external table that 1-2-3 models the new external table on.

creation-string is text that specifies additional information about the table. For example, the Paradox driver lets you use a table creation string to specify a sort order for the table. Not all external databases require a table creation string.

command is text that specifies the command that you want to send.

Notes

For complete information on using 1-2-3 with external databases, see DataLens Drivers for 1-2-3.

If you omit driver-user-id, driver-password, db-user-id, or db-password, and an ID or password is necessary, 1-2-3 displays a dialog box. You can enter the ID or password and choose OK, or choose Cancel.

•	If you do not enter the correct ID or password, 1-2-3 returns an error.

•	If you choose Cancel in the dialog box, the macro ends.

After 1-2-3 executes a {DATABASE-DISCONNECT} command, you cannot refer to the range name of the specified table until it is connected again with {DATABASE-CONNECT}. Any data queries, data table commands, database @functions, or other data external commands that refer to the range name of a disconnected table may result in errors.

Examples

The following command connects 1-2-3 to the table Q_1 in the external database named SALES, located on drive D:. The name of the driver associated with this database is dBASE IV, and you specify the full path. The optional passwords, ID's and owner name are not needed to connect to this table. The range name of the table in 1-2-3 is MYSALES.

{DATABASE-CONNECT "dbase_iv";;;"d:\regional_sales";;;;"q_1";MYSALES}

The following command creates a table named Q_2 in the external database named SALES, located on drive D:. The table uses field information in a 1-2-3 range beginning at A:A1 in the current file. dBASE IV is the required driver. The optional passwords, ID's and owner name are not needed to connect to this table.

{DATABASE-CREATE-TABLE "dbase_iv";;;;"d:regional_sales";;;;"q_2";MYSALES;;A:A1}

The following command disconnects 1-2-3 from the external table Inventory.

{DATABASE-DISCONNECT "Inventory"}

The following command encrypts TABLE1 in the external database C:\DATA. The command requires the password LosAngeles.

{DATABASE-SEND-COMMAND "paradox";;;;"c:\data";;;"encrypt=table1;LosAngeles"}

See also

Equivalent 1-2-3 commands

Tools Database Connect to External

Tools Database Create Table

Tools Database Disconnect

Tools Database Send Command

Help

Macros

User's Guide

Chapter 22, "Working with External Databases"

Chapter 24, "Using Macros to Automate Your Work"


{QUERY-ADD-FIELD}, {QUERY-CHOOSE-FIELDS}, {QUERY-REMOVE-FIELD}, {QUERY-SHOW-FIELD}
{QUERY-ADD-FIELD}
{QUERY-CHOOSE-FIELDS}
{QUERY-REMOVE-FIELD}
{QUERY-SHOW-FIELD}

{QUERY-ADD-FIELD field} adds a field to the currently selected query table. The field is displayed as the last field in the query table.

{QUERY-CHOOSE-FIELDS [field1];[field2];...;[field15]} specifies the fields that you want to appear in the currently selected query table.

{QUERY-REMOVE-FIELD field} removes a field from the currently selected query table.

{QUERY-SHOW-FIELD field; field-alias} specifies an alias field name for field to display in the currently selected query table. Doing so does not change the field name in the database table, but only changes the field name in the query table.

Arguments

field is the name of a field from the database table, enclosed in " " (quotation marks).

field1...field15 are field names of the fields to be displayed in the query table, enclosed in " " (quotation marks).

1-2-3 displays fields in the query table in the same left-to-right order as they appear in the {QUERY-CHOOSE-FIELDS} command.

{QUERY-CHOOSE-FIELDS} with no arguments chooses all fields in the source database table.

field-alias is text that specifies an alternate field name to display in the query table.

Notes

1-2-3 automatically refreshes the query table after it executes a {QUERY-ADD-FIELD}, {QUERY-CHOOSE-FIELDS}, or {QUERY-REMOVE-FIELD} command.

Examples

The following commands select the query table Employees and then add the field Salary.

{SELECT "Employees";;"query"}

{QUERY-ADD-FIELD "Salary"}

The following commands select the query table Payroll and then deletes all fields except Salary and EmployeeID.

{SELECT "Payroll";;"query"}

{QUERY-CHOOSE-FIELDS "Salary";"EmployeeID"}

The following commands select the query table Payroll and then delete the field LastName.

{SELECT "Payroll";;"query"}

{QUERY-REMOVE-FIELD "LastName"}

The following commands display the field name EmployeeID as ID in the query table Payroll. 1-2-3 does not change the field name in the original database table.

{SELECT "Payroll";;"query"}

{QUERY-SHOW-FIELD "EmployeeID";"ID"}

See also

Equivalent 1-2-3 commands

Query Choose Fields

Query Show Field As

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{QUERY-AGGREGATE}

{QUERY-AGGREGATE function;field-name} performs calculations on groups of data from a query table. For example, you can calculate sales by salesperson, by month of sale, or by account.

Arguments

function is text that specifies the aggregate function.

function	1-2-3 does the following

sum	Adds the values

avg	Averages the values

count	Counts the values

max	Finds the largest value

min	Finds the smallest value

reset	Resets the values

field-name is text that specifies the name of a field in the selected query table.

Examples

A query table named June_Sales contains a record for each sale made by an employee during the month of June. The query table contains two fields: Employee and Sales. The following command displays the total sales per employee.

{SELECT "June_Sales";;"query"}

{QUERY-AGGREGATE "sum";"Sales"}

See also

Equivalent 1-2-3 commands

Query Aggregate

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{QUERY-COPY-SQL} and {SEND-SQL}
{QUERY-COPY-SQL}
{SEND-SQL}

{QUERY-COPY-SQL} copies to the Clipboard the SQL command equivalent to the current query.

{SEND-SQL range;command;[output-range];[error-code-location]} sends an SQL syntax command string to an external database driver, against the database that contains the specified external database table.

Arguments

range is the range name of an external database table.

command is text that specifies an SQL command.

If command is the name or address of a range that contains labels, 1-2-3 creates the SQL command by concatenating each label in the range. 1-2-3 concatenates the labels in the range from left to right in a row and from top to bottom.

output-range is the name or address of a destination range for any data that might be received from the SQL command.

Caution 1-2-3 writes over any existing data in output-range.

error-code-location is the cell in which 1-2-3 will display the return code from the SQL command.

Notes

For complete information on using 1-2-3 with external databases, see DataLens Drivers for 1-2-3.

If the external database you to which you are sending the command does not support SQL, the database returns an Invalid Command error and the macro contines with the next command.

Use {DATABASE-CONNECT} to connect to an external database before using {SEND-SQL}.

See also

Equivalent 1-2-3 commands

Query Show SQL

Help

Macros

Tools Database Send Command

User's Guide

Chapter 22, "Working with External Databases"

Chapter 24, "Using Macros to Automate Your Work"


{QUERY-CRITERIA}

{QUERY-CRITERIA [criteria]} specifies criteria to determine which records appear in a new or currently selected query table.

Arguments

criteria is text that specifies a criteria formula. If you omit criteria, 1-2-3 includes all records in the query table.

Enclose text in criteria in "" "" (double quotation marks). For example, to specify all customers who live in Los Angeles, use "City=""Los Angeles""".

Examples

The following commands make records of employees who earn at least $35,000 per year and who were hired on or after June 1, 1987, appear in the query table named Employees.

{SELECT "Employees";;"query"}

{QUERY-CRITERIA "DOH>=@DATE(87;6;1)#AND#Salary>=35000"}

The following commands make records of employees who earn at least $35,000 per year and who work in the Finance department appear in the query table named Employees.

{SELECT "Employees";;"query"}

{QUERY-CRITERIA "Department=""Finance""#AND#Salary>=35000"}

See also

Equivalent 1-2-3 command

Query Set Criteria

Help

About Criteria

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{QUERY-DATABASE-TABLE}

{QUERY-DATABASE-TABLE database-table} changes the database table for the currently selected query table.

Arguments

database-table is the name or address of a database table.

Notes

If the new database table contains the same fields as the current table, 1-2-3 does not change the criteria, sort settings, aggregates, or the location of the query table.

If not all the fields in the new database table match those in the current table, the macro returns an error.

Examples

The following command uses the range ENGINEERS as the database table for the query table named Employees.

{SELECT "Employees";;"query"}

{QUERY-DATABASE-TABLE ENGINEERS}

See also

Equivalent 1-2-3 command

Query Set Database Table

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{QUERY-JOIN}

{QUERY-JOIN [join-criteria]} allows you to query multiple database tables that contain a common field.

Arguments

join-criteria is text that specifies a join formula.

If you omit join-criteria, 1-2-3 deletes all joins, leaving you with just the original database table.

About join formulas

A join formula has the format +table1.field1=table2.field2.

table1 and table2 are range names for two database tables you want to query.

field1 and field2 are the names of fields that contain similar entries in both tables.

When you use a join formula

• 	Enter field names exactly as they appear in the database tables.

• 	The field names do not have to match, but the two fields must contain the same type of data.

• 	Entries in one field must match entries in the other field, and one field should not contain duplicate entries.

For example, the join formula +SALES.Item=PRICE.Item_Name tells 1-2-3 that the fields named Item and Item_Name are located in different tables but contain similar data. Each entry in PRICE.Item_Name is listed only once but may be listed many times in SALES.Item.

Examples

You want to query two database tables named SALES and PRICE. Each database has a field that contains the names of items for sale.

The join formula SALES.ITEM=PRICE.ITEM_NAME tells 1-2-3 that the fields named Item and Item_Name in two database tables (SALES and PRICE) contain the same type of data.

{SELECT "Query 1";;"query"}

{QUERY-JOIN "SALES.ITEM=PRICE.ITEM_NAME"}

See also

Equivalent 1-2-3 command

Query Join

Help

About Criteria

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{QUERY-NAME}

{QUERY-NAME new-name} assigns a new name to the currently selected query table.

Arguments

new-name is text that specifies the new name of the query table.

Examples

The following command renames the query table PERSONNEL to EMPLOYEES.

{SELECT "Personnel";;"query"}

{QUERY-NAME EMPLOYEES}

See also

Equivalent 1-2-3 command

Query Name

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{QUERY-NEW}

{QUERY-NEW database-table;output-range;[criteria];[query-name];[record-limit];[field1];[field2];...;[field10]} creates a query table that contains the records you extract from a database table.

Arguments

database-table is the name or address of a database table.

output-range is the name or address of the range where you want to create the query table.

• 	Caution 1-2-3 writes over any existing data in the range.

• 	output-range cannot overlap database-table.

• 	If you specify the top left cell of a range, the query table will contain as many fields and records as will fit in the worksheet.

• 	If you specify a range, 1-2-3 displays only the records and fields that fit in that range.

For example, if you specify a range of five columns by ten rows, 1-2-3 displays the first five fields and the first ten records in the order in which they appear in the database table.

If you subsequently resize the query table to be larger, 1-2-3 displays the additional fields and records.

criteria is text that specifies a criteria formula. If you omit criteria, 1-2-3 chooses all the records in database-table.

Enclose text in criteria in "" "" (double quotation marks). For example, to specify all customers who live in Los Angeles, use "City=""Los Angeles""".

query-name is text that specifies a name for the query table. If you omit query-name, 1-2-3 assigns a default name, such as Query 1.

record-limit is an integer that specifies the maximum number of records to be displayed in the query table. If you omit record-limit, 1-2-3 displays all records that match criteria.

field1...field10 are names of fields from database-table that you want to display in the query table. Enclose the field names in " " (quotation marks). If you omit the field arguments, 1-2-3 chooses all the fields in database-table.

Notes

1-2-3 displays fields in the query table in the same left-to-right order as they appear in the {QUERY-NEW} command.

Examples

The following command creates a new query table starting in cell B:A1 from the database table EMPLOYEES. The new query table is named Finance and contains records for all employees who work in the Finance department.

{QUERY-NEW EMPLOYEES;B:A1;"Department=""finance""";"finance"}

See also

Equivalent 1-2-3 command

Tools Database New Query

Help

About Criteria

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{QUERY-OPTIONS}

{QUERY-OPTIONS option;on-off;[record-limit]} specifies options for manipulating data in the currently selected query table.

Arguments

option is text that specifies an option to set.

option	1-2-3 does the following

allow-updates	Allows you to post any changes made in the query table to the database table

unique-only	Excludes duplicate records from the query table

limit-output	Lets you specify, with record-limit, how many records appear in the query table.

show-samples	Lets you select from a list of unique values when specifying criteria for the query table.

auto-refresh	Changes the query table results when you change any criteria, sort settings, options, choose fields, or aggregate.

on-off is a yes/no argument that specifies whether to turn option on or off.

record-limit is a value from 1 through 8191 that specifies how many records appear in the query table. If you do not turn on the limit-output option, 1-2-3 ignores record-limit.

Notes

Do not turn on both the limit-output and allow-updates options for the same query table.

Examples

The following {QUERY-OPTIONS} command ensures that 1-2-3 refreshes the query table EMPLOYEES after the {QUERY-CRITERIA} command.

{SELECT "Employees";;"query"}

{QUERY-OPTIONS "auto-refresh";"on"}

{QUERY-CRITERIA "Department=""sales"""}

See also

Equivalent 1-2-3 command

Query Set Options

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{QUERY-REFRESH} and {QUERY-UPDATE}
{QUERY-REFRESH}
{QUERY-UPDATE}

{QUERY-REFRESH} updates records in the currently selected query table to reflect changes made to the database table, query options, criteria, or aggregate.

{QUERY-UPDATE} applies any changes you make to records in the currently selected query table to the corresponding database table.

Notes

To ensure that you can update a database table, include the following command before the macro commands that edit data in the query table:

{QUERY-OPTIONS "allow updates";"on"}

Examples

The following commands refresh the query table Inventory.

{SELECT "Inventory";;"query"}

{QUERY-REFRESH}

The following commands update the database table that corresponds to the query table Hats.

{SELECT "Hats";;"query"}

{QUERY-UPDATE}

See also

Equivalent 1-2-3 commands

Query Refresh Now

Query Update Database Table

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{SORT} and {QUERY-SORT}
{QUERY-SORT}
{SORT}

{QUERY-SORT [key1];[order1];[key2];[order2];[key3];[order3]} arranges data in the currently selected query table in the order you specify.

{SORT [range];[key1];[order1];[key2];[order2];[key3];[order3]} arranges data in range in the order you specify.

Arguments

key1, key2, and key3 define the first, second, and third sort keys, respectively.

order1, order2, and order3 specify the sort order for key1, key2, and key3, respectively. You must specify a sort order for each sort key.

order	1-2-3 sorts

ascend	A - Z, and smallest to largest values

descend	Z - A, and largest to smallest values

range is the name or address of a range that contains the data you want to sort.

Notes

If range is a 3D range, 1-2-3 sorts the contents of each worksheet in the range separately; it does not move the entries from one worksheet to another.

Use {QUERY-SORT-KEY-DEFINE} or {SORT-KEY-DEFINE} to sort with more than three keys.

Examples

The following command sorts the data in the query table Finance by LAST_NAME in and DATE_OF_HIRE, both in descending order.

{SELECT "Finance";;"query"}

{QUERY-SORT last_name;"descend";date_of_hire;"descend"}

The following command sorts the data in the range JULY by the sort key NAME, in ascending order.

{SORT JULY;NAME;"ascend"}

See also

Equivalent 1-2-3 commands

Query Sort

Range Sort

Help

Macros

Sort Info Components

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{QUERY-SORT-KEY-DEFINE} and {SORT-KEY-DEFINE}
{QUERY-SORT-KEY-DEFINE}
{SORT-KEY-DEFINE}

{QUERY-SORT-KEY-DEFINE key-number;key-field;key-order} defines a sort key to be used by a subsequent {QUERY-SORT} command.

{SORT-KEY-DEFINE key-number;key-range;key-order} defines a sort key to be used by a subsequent {SORT} command.

Arguments

key-number is an integer from 1 through 255 that specifies a sort key.

key-order is text that specifies the sort order for key-field.

order	1-2-3 sorts

ascend	A - Z, and smallest to largest values

descend	Z - A, and largest to smallest values

key-field is text that specifies the field name to be sorted for this key.

key-range is the name or address of the range to be sorted for this key.

Examples

The following macro selects the range MAIL, defines four sort keys, and then sorts the range.

{SELECT MAIL}

{SORT-KEY-DEFINE 1;"Name";"descend"}

{SORT-KEY-DEFINE 2;"City";"ascend"}

{SORT-KEY-DEFINE 3;"Province";"descend"}

{SORT-KEY-DEFINE 4;"PostalCode";"ascend"}

{SORT}

The following macro selects the query table Mail List, defines four sort keys, and then sorts the query table.

{SELECT "Mail List";;"query"}

{QUERY-SORT-KEY-DEFINE 1;"Name";"descend"}

{QUERY-SORT-KEY-DEFINE 2;"City";"ascend"}

{QUERY-SORT-KEY-DEFINE 3;"Province";"descend"}

{QUERY-SORT-KEY-DEFINE 4;"PostalCode";"ascend"}

{QUERY-SORT}

See also

Help

Macros

Sort Info Components

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{QUERY-SORT-RESET} and {SORT-RESET}
{QUERY-SORT-RESET}
{SORT-RESET}

{QUERY-SORT-RESET} clears all sort keys for the currently selected query table

{SORT-RESET} clears all sort keys for sorting range data.

See also

Equivalent 1-2-3 commands

Query Sort Reset

Range Sort Reset

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{QUERY-UPGRADE}

{QUERY-UPGRADE input-range;output-range;criteria-range;[query-name]} upgrades a query created with /Data commands in a previous version of 1-2-3 so that it works with the Query commands.

Arguments

input-range is the name or address of the input range that you specified for /Data Query Input.

output-range is the name or address of the output range that you specified for /Data Query Output.

The fields in the new query table will be the same as those in output-range.1-2-3 displays the new query table on top of output-range.

Caution  1-2-3 adds a computed column to the query table for each criterion that contains a formula on the left-hand side of the operator. For example, for the criterion @ABS(TEMP_CHANGE)=10, 1-2-3 adds one computed column. 1-2-3 writes over any existing data in the query table location.

criteria-range is the name or address of the criteria range you specified for /Data Query Critera.

query-name is text that specifies a name for the new query table. If you omit query-name, 1-2-3 assigns a default name, such as Query 1, to the query table.

Notes

To upgrade a join query from a previous version of 1-2-3, use a {QUERY-UPGRADE} command whose criteria-range argument contains only the join criteria.

Examples

The following command upgrades a query created with /Data Query to a query table named Finance_qt. The input range is named FINANCE, the output range is named FINANCE_OUT, and the criteria range is named CRIT_RANGE.

{QUERY-UPGRADE FINANCE;FINANCE_OUT;CRIT_RANGE;"finance_qt"}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{DDE-TABLE}

{DDE-TABLE location;[type]} creates a table of conversations associated with all active files that were created with {DDE} commands.

Arguments

location is the address or name of the the top left cell of the range to receive the table.

The table occupies either of the following, depending on type:

•	Three columns and as many rows as there are conversations associated with the active files, plus one row for headings and one blank row

•	Seven columns and as many rows as there are links and conversations associated with the active files, plus one row for headings and one blank row

Caution 1-2-3 writes over any existing data in location.

type is text that specifies what type of information to include in the table.

type	1-2-3 creates a table that includes

long	DDE conversation and link information; default if you omit the argument

short	DDE conversation information only

Notes

The short table contains the following information for each conversation: conversation number, application, and topic. For example:

Conv-Num	App	Topic

12123	AmiPro	SYSTEM

33345	AmiPro	D:\DOCS\LOAN.SAM

The long table contains the same information as the short table for each conversation, plus the following information for each link: item, format, status, data location, and branch location. For example:

Conv-Num	App	Topic	Item	Format	Status	Data Loc	   Branch Loc

12123	AmiPro	System

33345	AmiPro	D:\DOCS\LOAN.SAM

BMARK1	Text	Active	C:B32

BMARK2	Text	Inactive	C:B35

Examples

The following command creates a table of conversations associated with all active files. The table starts in the cell CONV.

{DDE-TABLE CONV;"short"}

See also:

Help

Edit Links Information for information about update mode and link status

{LINK-TABLE}

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{EDIT-OBJECT}, {INSERT-OBJECT}, {UPDATE-OBJECT}
{EDIT-OBJECT}
{INSERT-OBJECT}
{UPDATE-OBJECT}

{EDIT-OBJECT [verb]} executes either the primary or secondary verb for the currently selected OLE embedded object.

{INSERT-OBJECT object-type;[location]} creates and places in the worksheet an OLE embedded object.

{UPDATE-OBJECT} updates a 1-2-3 OLE object embedded in another application file.

Arguments

verb is text that specifies the verb to be executed.

verb	The server application executes the

primary	Primary verb

secondary	Secondary verb; if the object has no secondary verb, the server application executes the primary verb.

If you omit verb, the server application executes the edit verb, regardless of whether it is the primary verb.

object-type is text that specifies the class name of a valid, registered OLE Server object, for example, AmiPro Document.

location is the name or address of the cell where you want to put the upper-left corner of the OLE object. If you omit location, 1-2-3 uses the current selection.

Notes

{INSERT-OBJECT} starts the selected application, or activates it if it is already open.When you choose File Exit or File Exit & Return in the server application, the server application closes if it was not already open, and you return to 1-2-3. The new OLE object appears as a drawn object.

Examples

The following command starts Ami Pro so you can create a memo. When you choose File Exit & Return in Ami Pro, 1-2-3 places the embedded Ami Pro document object in the woksheet, starting at cell A:G15.

{INSERT-OBJECT "AmiPro Document";A:G15}

The following macro selects the embedded Ami Pro document Embedded 1 and lets you edit the document in Ami Pro.

{SELECT "Embedded 1";;"draw"}

{EDIT-OBJECT}

See also

Equivalent 1-2-3 commands

Edit Insert Object

Edit Links Update/Update All

Help

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{DELETE-COLUMNS}, {DELETE-ROWS}, {DELETE-SHEETS}
{DELETE-COLUMNS}
{DELETE-ROWS}
{DELETE-SHEETS}

{DELETE-COLUMNS [range];[delete-selection]} deletes all of each column that includes cells in range; or deletes only the part of the columns covered by range.

{DELETE-ROWS [range];[delete-selection]} deletes all of each row that includes cells in range; or deletes only the part of the rows covered by range.

{DELETE-SHEETS [range]} deletes all of each worksheet that includes cells in range.

Arguments

range is the name or address of a range with at least one cell in each column, row, or worksheet you want to delete. If you omit range, 1-2-3 deletes columns, rows, or worksheets that have cells in the currently selected range or collection.

delete-selection is a yes/no argument that specifies whether to delete only the cells in range and move existing data to the left (for {DELETE-COLUMNS}) or up (for {DELETE-ROWS}). If you omit delete-selection, 1-2-3 deletes entire columns or rows.

Examples

The following command deletes all of columns E, F, and G on worksheet A.

{DELETE-COLUMNS A:E2..A:G2}

The following command deletes only that part of the rows included in the range RATES. In the rows below, only the cells beneath the deleted range move up to take the place of what was deleted.

{DELETE-ROWS RATES;"yes"}

The following command deletes worksheets A, B, and C.

{DELETE-SHEETS A:A1..C:A1}

See also

Equivalent 1-2-3 command

Edit Delete

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{EDIT-COPY-FILL}

{EDIT-COPY-FILL direction;[range]} copies the contents of one row, column, or worksheet in range to all of range, based on a specified direction.

Arguments

direction is text that specifies the direction to copy the data.

direction	1-2-3 copies the

down	Top row of range to all rows in range

right	Leftmost column of range to all columns in range

up	Bottom row of range to all rows in range

left	Rightmost column of range to all rows in range

back	First worksheet of range to all worksheets in range

forward	Last worksheet of range to all worksheets in range

range is the name or address of the range to fill. If you omit range, 1-2-3 uses the current selection.

Examples

The following command copies the formulas in the first row of the range INTEREST to all the rows in that range.

{EDIT-COPY-FILL "down";INTEREST}

See also

Equivalent 1-2-3 commands

Edit Copy Down

Edit Copy Right

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{EDIT-FIND}, {EDIT-FIND?}, {EDIT-REPLACE}, {EDIT-REPLACE-ALL}
{EDIT-FIND}
{EDIT-FIND?}
{EDIT-REPLACE}
{EDIT-REPLACE-ALL}

{EDIT-FIND [search-for];[look-in];[search-through]} finds the first instance of specified characters in labels, formulas, or both.

{EDIT-FIND?} displays the Edit Find & Replace dialog box. After the user leaves the dialog box, 1-2-3 continues the macro.

{EDIT-REPLACE [search-for];[look-in];[replacement];[search-through]} finds the first instance of specified characters in labels, formulas, or both, and replaces them.

{EDIT-REPLACE-ALL [search-for];[look-in];[replacement];[search-through]} finds all instances of specified characters in labels, formulas, or both, and replaces them.

Arguments

search-for is the text for which you want 1-2-3 to search. If you omit search-for 1-2-3 uses the text that you last searched for.

1-2-3 does not distinguish between uppercase and lowercase letters in search-for.

look-in is text that specifies in what types of cell entries 1-2-3 should search. If you omit look-in, 1-2-3 searches the same types of cell entries as the last time you searched for or replaced text.

look-in	1-2-3 looks in

formulas	Cells containing formulas

text	Cells containing labels

both	Both cells containing formulas and cells containing labels.

replacement is the text you want 1-2-3 to replace search-for with. If you omit replacement, 1-2-3 uses the same replacement text as the last time you replaced text.

search-through is the name or address of the range you want 1-2-3 to search. If search-through is a single-cell, 1-2-3 searches the entire current file. If you omit search-through, 1-2-3 searches the current selection, providing it is a multiple-cell range; otherwise 1-2-3 searches the entire file.

Notes

1-2-3 displays a message box if it does not find a match for search-for and the macro ends.

When searching the entire file, 1-2-3 starts at cell A:A1. When searching a range, 1-2-3 starts at the top left cell in the range. Whether searching the entire file or a range, 1-2-3 searches down the leftmost column, then down the next column to the right, and so on; and front to back by sheet.

Examples

The following commands select a range and then display the Edit Find & Replace dialog box.

{SELECT A1..F20}

{EDIT-FIND?}

The following command searches both labels and formulas in the range PRINCIPAL for the first instance of the text Stock Quote.

{EDIT-FIND "Stock Quote";"both";PRINCIPAL}

The following command searches both labels and formulas in the range STOCKS, and replaces the first instance of the text Ibex with the text Willow.

{EDIT-REPLACE "Ibex";"both";"Willow";STOCKS}

The following command searches all labels in the current file and replaces all instances of the text Tax Rate with the text Losses.

{EDIT-REPLACE-ALL "Tax-Rate";"text";"Losses"}

See also

Equivalent 1-2-3 commands

Edit Find & Replace

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{EDIT-PASTE-SPECIAL}

{EDIT-PASTE-SPECIAL [destination];[property]} inserts data on the Clipboard into the worksheet.

Arguments

destination is the name or address of a range where you want to paste the clipboard contents. If you omit destination, 1-2-3 uses the current selection.

property is text that specifies what to paste.

property	1-2-3 pastes

cell-contents	Cell contents, but leaves the styles in destination intact

styles	All formatting done with the Style commands

both	Both cell contents and styles; default if you omit the argument

values	Both cell contents and styles, but converts all formulas to values

query	A query table that is on the Clipboard. Make sure that destination does not overlap any other query tables.

Examples

The following commands copy a column of @SUM formulas from the range TOTALS to the range JUNE_TOTALS, and replace the copied formulas with their current values.

{EDIT-COPY TOTALS}

{EDIT-PASTE-SPECIAL JUNE_TOTALS;"values"}

The following commands copy a query table named Query 1 to the Clipboard then and paste the query table in the range that starts in cell A50.

{SELECT "Query 1";;"query"}

{EDIT-COPY}

{EDIT-PASTE-SPECIAL A25;"query"}

See also

Equivalent 1-2-3 commands

Edit Paste Special

Help

{EDIT-PASTE-LINK}

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{EDIT-QUICK-COPY} and {EDIT-QUICK-MOVE}
{EDIT-QUICK-COPY}
{EDIT-QUICK-MOVE}

{EDIT-QUICK-COPY destination;[source]} copies data and related formatting from the source range to the destination range, without using the Clipboard.

{EDIT-QUICK-MOVE destination;[source]} moves data and related formatting from the source range to the destination range, without using the Clipboard.

Arguments

destination is the name or address of the range to which you are copying.

source is the name or address of the range from which you are copying. If you omit source, 1-2-3 copies the current selection.

Notes

{EDIT-QUICK-COPY} and {EDIT-QUICK-MOVE} are equivalent to copying and moving data with the mouse.

1-2-3 moves but does not copy all drawn objects fastened, at all points, to the destination range.

Examples

The following command copies data and formatting from the range RATE to the range INTEREST.

{EDIT-QUICK-COPY INTEREST;RATE}

The following command moves data and formatting from the range MONTHS to the range EXPENSES.

{EDIT-QUICK-MOVE EXPENSES;MONTHS}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{FILE-UPDATE-LINKS}

{FILE-UPDATE-LINKS} recalculates formulas in the current file that contain links to other files.

See also

Equivalent 1-2-3 commands

Edit Links File Links

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{INSERT-COLUMNS}, {INSERT-ROWS}, {INSERT-SHEETS}
{INSERT-COLUMNS}
{INSERT-ROWS}
{INSERT-SHEETS}

{INSERT-COLUMNS [range];[number];[insert-selection]} inserts one or more blank columns in the current file, or inserts only the part of the columns covered by range.

{INSERT-ROWS [range];[number];[insert-selection]} inserts one or more blank rows in the current file, or inserts only the part of the rows covered by range.

{INSERT-SHEETS [where];[number];[range]} inserts one or more blank worksheets in the current file.

Arguments

range is a name or address or name of a range. 1-2-3 inserts columns immediately to the left of range and rows immediately above range. If you omit range, 1-2-3 uses the current selection.

where specifies where to insert blank worksheets.

where	1-2-3 inserts worksheets

before	Immediately before the first worksheet in range

after	Immediately after the first worksheet in range; default if you omit the argument

number is an integer that specifies how many columns, rows, or worksheets to insert. If you omit number, 1-2-3 inserts the same number of columns, rows, or worksheets as there are in range.

insert-selection is a yes/no argument that specifies whether to insert only the cells in range and move existing data to the right (for {INSERT-COLUMNS}) or down (for {INSERT-ROWS}). If you omit insert-selection, 1-2-3 inserts entire columns or rows.

Examples

Suppose the range A:A1..C:G25 is named OCTOBER. This range occupies part of seven columns in each of the worksheets A, B, and C. The following command inserts seven entire columns immediately to the left of column A in each worksheet.

{INSERT-COLUMNS OCTOBER}

The following command inserts part of four rows immediately above the range A:A5..A:D5.

{INSERT-ROWS A:A5..A:D5;4;"yes"}

The following command inserts three worksheets before the current worksheet.

{INSERT-SHEETS "before";3}

See also

Equivalent 1-2-3 command

Edit Insert

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{FILE-CLOSE}

{FILE-CLOSE [discard]} closes the current file.

Arguments

discard is a yes/no argument that specifies whether to discard any unsaved changes to the current file.

•	If you specify "yes" for discard and there are unsaved changes in the current file, 1-2-3 closes the file without saving unsaved changes and without updating embedded OLE objects.

•	If you specify "no" for discard and there are unsaved changes in the current file, {FILE-CLOSE} has no effect. The file remains open; and no message is displayed.

•	If you specify "no" for discard and there are no unsaved changes in the current file, 1-2-3 closes the file.

•	If you omit discard, and there are no unsaved changes in the current file, 1-2-3 closes the file.

•	If you omit discard, and there are unsaved changes in the current file, 1-2-3 displays the Save Changes and/or Update OLE dialog box. If you choose Cancel in the dialog box, the macro ends.

Examples

If there are unsaved changes in the current file, the following command displays the Save Changes dialog box.

{FILE-CLOSE}

If there are unsaved changes, the following command has no effect; the current file remains open. if there are no unsaved changes, 1-2-3 closes the current file.

{FILE-CLOSE "no"}

The following command closes the current file regardless of whether or not there are unsaved changes.

{FILE-CLOSE "yes"}

See also

Equivalent 1-2-3 command

File Close

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{FILE-COMBINE}

{FILE-COMBINE [how];file-name;[password];[source]} combines data and number formats from a 1-2-3 worksheet (.WK*) file on disk into the current file, starting in the current cell.

Arguments

how specifies how you want 1-2-3 to combine data. You can only include how if you are combining data from 1-2-3 files.

how	1-2-3 combines data this way

add	Adds numbers and the results of numeric formulas in a file on disk to numbers or blank cells in the current file

replace	Replaces data in the current file with data copied from the file on disk; default if you omit the argument

subtract	Subtracts numbers and the results of numeric formulas in a file on disk from numbers or blank cells in the current file

file-name is text that specifies the name of the worksheet file on disk containing data which you want to combine with data in the current file.

password is text that specifies a password for accessing the file.

If you omit password, and a password is necessary, 1-2-3 displays a password dialog box. You can enter the password and choose OK, or choose Cancel.

•	If you do not enter the correct password, 1-2-3 returns an error.

•	If you choose Cancel in the dialog box, the macro ends.

source is the name or address of the range in file-name that contains data that you want to combine with data in the current file. You must enclose source in " " (quotation marks). If you omit source, 1-2-3 combines all the data in file-name with data in the current file.

Notes

You can use {FILE-COMBINE} with 1-2-3 files (.WK*), Symphony files (.WR*), and Graph files (.CGM and .PIC).

1-2-3 does not combine information from format files.

Examples

The following commands add the numeric data from the range ASSETS in the file SALES.WK4 to numeric data in the current file, starting at cell A:A20.

{SELECT A:A20}

{FILE-COMBINE "add";"sales.wk4";;"assets"}

See also

Equivalent 1-2-3 command

Combine 1-2-3 File

Help

{FILE-IMPORT}

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{FILE-EXTRACT}, {FILE-SAVE}, {FILE-SAVE-ALL}, {FILE-SAVE-AS?}
{FILE-EXTRACT}
{FILE-SAVE}
{FILE-SAVE-ALL}
{FILE-SAVE-AS?}

{FILE-EXTRACT file-name;[file-type];[password];[backup];[extract-range];[properties]} saves a range to another file.

{FILE-SAVE [file-name];[file-type];[password];[backup]} saves the current file.

{FILE-SAVE-ALL} saves all active files.

{FILE-SAVE-AS?} displays the File Save As dialog box.

Arguments

file-name is text that specifies the name of the file you want to save.

•	If file-name does not specify an existing file, 1-2-3 creates a file named file-name and saves it.

•	If file-name specifies an existing file, and you omit backup, 1-2-3 displays a Backup, Replace, or Cancel dialog box.

•	If you omit file-name, 1-2-3 saves the file with its existing name, if there is one. If the file has never been saved, 1-2-3 displays the File Save As dialog box.

Note You cannot omit file-name from {FILE-EXTRACT}.

file-type is text that specifies the format of the saved file.

file-type	1-2-3 saves the file as

1-2-3	WK4 format; default if you omit the argument

1-2-3 (wk3)	WK3 format with formatting information in FM3 format

1-2-3 (wk1)	WK1 format with formatting information in FMT format

dbase (dbf)	A dBASE file

excel (xlw)	An Excel workbook file

excel (xls)	An Excel worksheet file

paradox (db)	A Paradox file

text (txt)	A text file (TXT)

shared	A shared file (NS4)

Note You cannot use "shared", "excel (xlw)", or "excel (xls)" for file-type with {FILE-EXTRACT}.

password is text that specifies a password with which to save the file.

If you omit password, and the file specified by file-name has a password, 1-2-3 saves the file with that password.

Note 1-2-3 does not support passwords for text files. If you specify a password for a text file, 1-2-3 ignores the password.

backup is text that specifies whether or not to create a backup file if file-name specifies an existing file.

backup	1-2-3 does the following

backup	Adds a .BAK extension to the name of the file on disk and saves the current file as file-name

replace	Replaces the existing file with the current file

If you omit backup, and file-name specifies an existing file, 1-2-3 displays a Backup, Replace, Cancel dialog box. If you choose Cancel, the macro ends.

Note 1-2-3 does not create backup files for text files.

extract-range is the name or address of a range in the current file from which you want to extract data. If you omit extract-range, 1-2-3 uses the currently selected range.

properties is text that specifies how to save values from extract-range.

properties	1-2-3 does the following when saving extract-range

formulas	Saves formulas without converting them to values; default if you omit the argument.

values	Converts formulas to values

Examples

The following command saves data from the range REVENUES in the current file to a file on disk named SALES.WK4. When saving formulas from REVENUES, 1-2-3 converts them to values.

{FILE-EXTRACT "sales.wk4";"1-2-3 (wk3)";;"backup";REVENUES;"values"}

The following lines from a macro open a file named JULY.WK4 with the password Zeus, edit the file, and then save it with the new name AUGUST.WK4, with no password.

Note that when you omit password from {FILE-SAVE}, the new file does not have the same password as the original file; it has no password.

{FILE-OPEN "july.wk4";"Zeus"}

{SELECT F10}

{CELL-ENTER "9751"}

{SELECT F11}

{CELL-ENTER "805"}

{SELECT F14}

{CELL-ENTER "276}

{FILE-SAVE "August"}

See also

Equivalent 1-2-3 commands

File Save and File Save As

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{FILE-EXIT}

{FILE-EXIT [discard]} ends the 1-2-3 session.

Arguments

discard is a yes/no argument that specifies whether to discard any unsaved changes to any open files.

•	If you specify "yes" for discard and there are unsaved changes in open files, the 1-2-3 session ends without saving unsaved changes and without updating embedded OLE objects.

•	If you specify "no" for discard and there are unsaved changes in open files, {FILE-EXIT} has no effect. The 1-2-3 session does not end; and no message appears.

•	If you omit or specify "no" for discard and there are no unsaved changes in open files, the 1-2-3 session ends.

•	If you omit discard, and there are unsaved changes in open files, 1-2-3 displays the Save Changes and/or Update OLE dialog box. If you choose Cancel in the dialog box, the macro ends.

Examples

If there are unsaved changes in any open files, the following command displays the Save Changes dialog box.

{FILE-EXIT}

If there are unsaved changes, the following command has no effect; the 1-2-3 session does not end. if there are no unsaved changes, the 1-2-3 session ends.

{FILE-EXIT "no"}

The following command ends the 1-2-3 session regardless of whether or not there are unsaved changes.

{FILE-EXIT "yes"}

The following commands save all unsaved changes in all open files and then end the 1-2-3 session.

{FILE-SAVE-ALL}

{FILE-EXIT}

See also

Equivalent 1-2-3 command

File Exit

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{FILE-GET-RESERVATION} and {FILE-RELEASE-RESERVATION}
{FILE-GET-RESERVATION}
{FILE-RELEASE-RESERVATION}

{FILE-GET-RESERVATION} gets the reservation for the current file if it is available and no one saved the file since you read it into memory. When you get the reservation, you are the only person who can save changes to the file.

{FILE-RELEASE-RESERVATION} releases the reservation for the current file.

Examples

The following macro gets the network reservation setting of the current file, runs the subroutine UPDATE, saves the file, and then releases the network reservation setting.

{FILE-GET-RESERVATION}

{DISPATCH UPDATE}

{FILE-SAVE}

{FILE-RELEASE-RESERVATION}

See also

Equivalent 1-2-3 command

File Protect File Reservation

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{FILE-IMPORT}

{FILE-IMPORT [read-text-as];file-name;[character-set]} combines data from a text file with the current file.

Arguments

read-text-as is text that specifies how 1-2-3 should combine data from a text file.

read-text-as can be text from the table below.

read-text-as	1-2-3 imports

text	Text and numbers from a nondelimited text file. 1-2-3 enters each line of data as a label in a separate cell using successive cells in the same column.

If a line of data is longer than 512 characters, the macro ends with an error unless you include an {ONERROR} command.

If there are more than 256 columns of data, 1-2-3 truncates the extra data.

If there are more than 8192 lines of data, 1-2-3 creates a new worksheet for each group of 8192 lines. 1-2-3 also creates a new worksheet when it encounters a form feed (0x0C) character.

numbers	Text and numbers from a delimited text file, or only numbers from a nondelimited text file.

tab	Text and numbers from a tab-delimited text file. Two consecutive tabs result in a blank cell.

comma	Text and numbers from a comma-delimited text file.

space	Text and numbers from a space-delimited text file.

semicolon	Text and numbers from a semicolon-delimited text file.

autoparse	Text and numbers based on the layout of the file. 1-2-3 automatically parses the text file by determining where the breaks are, then breaking the data into separate columns in the worksheet.

For numbers, tab, comma, space, and semicolon, 1-2-3 enters each line of data in a separate successive row. Each numeric or text item in the line is entered one item per cell in successive cells in the row.

For numbers, tab, comma, space, semicolon, and autoparse, if there are more than 512 characters between delimiters, 1-2-3 truncates the data. If there are more than 256 columns of data, the macro ends with an error unless you include an {ONERROR} command.

If you omit read-text-as 1-2-3 uses the same separator you specified the last time you used File Open Text Options, {FILE-IMPORT}, or {FILE-OPEN}. If no separator was previously specified, 1-2-3 uses "autoparse" for read-text-as.

file-name is text that specifies the name of the text file on disk containing the data you want to combine with data in the current file.

character-set is text that specifies the code page you want 1-2-3 to use for interpreting the data in the text file.

Generally, you use the Windows ANSI or DOS code pages. The code page entries in your 123R5.INI file determine which code pages you can use for character-set. To change the list of code pages you can use for character-set, see Modifying Code Page Entries.

character-set	1-2-3 uses this code page

windows	Windows ANSI; default if you omit the argument

dos	DOS or OS/2

cp850	Multilingual

cp932	Japanese

kanji	Kanji

big5	Taiwanese

ks	Korean

gb	Chinese

cp1252	US Windows

cp437	US DOS

cp860	Portuguese

cp863	French Canadian

cp865	Norwegian/Danish

cp1250	Eastern European Windows

cp852	Eastern European DOS

cp1251	Cyrillic Windows

cp866	Cyrillic DOS

cp1253	Greek Windows

cp851	Greek DOS

cp1254	Turkish Windows

cp857	Turkish DOS

cp1255	Hebrew Windows

cp1256	Arabic Windows

Notes

Make sure that numbers in a text file do not contain commas, since these are delimiters. For example, 1-2-3 interprets 12,345 as two values, 12 and 345.

Examples

The following command copies the data from D:\FILES\EXPENSES.TXT, a nondelimited text file on disk, to the current file, starting at the current cell. 1-2-3 enters each line of data as a long label in successive cells in the same column.

{FILE-IMPORT "text";"d:\files\expenses.txt"}

See also

Equivalent 1-2-3 command

File Open Combine

Help

{FILE-COMBINE}

{FILE-OPEN}

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{FILE-NEW}

{FILE-NEW [file-name];[where];[smartmaster]} creates a new file on disk and in memory, places the new file in a window, makes the window current.

Arguments

file-name is text that specifies the name of the file that you want to create. If you omit file-name, 1-2-3 supplies a default file name for the current path. The first default file name is FILE0001.WK4, the next default file name is FILE0002.WK4, and so on.

where is text that specifies the order of the new file in memory. In a macro, the order of files in memory applies only to moving from file to file with macro commands such as {NEXTSHEET} and {PREVSHEET}.

where	1-2-3 opens the new file

before	Immediately before the current file in memory; default if you omit the argument

after	Immediately after the current file in memory

smartmaster is text that specifies the name of the SmartMaster (WT4) file you want to use to create the new file. You do not have to include the path or WT4 extension.

If you omit smartmaster, 1-2-3 creates a new blank file, and displays a worksheet with the cell pointer in cell A1

Examples

The following command creates a new file named DEBITS.WK4 immediately before the current file in memory. A:A1 in DEBITS.WK4 becomes the current cell.

{FILE-NEW "debits.wk4"}

The following command creates a new file named DEBITS.WK4 immediately after the current file in memory, using the Fast Financials SmartMaster (FINANCE.WT4).

{FILE-NEW "debits.wk4";"after";"finance"}

See also

Equivalent 1-2-3 command

File New

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{FILE-OPEN}, {FILE-OPEN?}, {FILE-RETRIEVE}
{FILE-OPEN}
{FILE-OPEN?}
{FILE-RETRIEVE}

{FILE-OPEN file-name;[password];[read-only];[where];[read-text-as];[character-set]} reads a file into memory, makes it the current file, and moves the cell pointer to the cell it was in when you last saved the file.

{FILE-OPEN?} displays the File Open dialog box.

{FILE-RETRIEVE file-name;[password];[read-only];[read-text-as]} replaces the current file in memory with a file from disk, and moves the cell pointer to the cell it was in when you last saved the file.

Caution When you replace the current file with a file retrieved with {FILE-RETRIEVE}, 1-2-3 removes the current file from memory without saving it and without prompting the user.

Arguments

file-name is text that specifies the name of the file you want to open. If file-name specifies a file that is already open, or if no file of that name exists, 1-2-3 returns an error.

password is text that specifies a password for opening the file.

If you omit password, and a password is necessary, 1-2-3 displays a password dialog box. You can enter the password and choose OK, or choose Cancel.

•	If you do not enter the correct password, 1-2-3 returns an error.

•	If you choose Cancel in the dialog box, the macro ends.

read-only is a yes/no argument that specifies whether 1-2-3 should open the file as read only, if another user or another application has the network reservation for the file, or not open the file.

•	If you specify "yes" for read-only and the file is not reserved, 1-2-3 opens the file, and you get the network reservation for the file.

•	If you specify "no" for read-only and the file is reserved, 1-2-3 does not open the file.

•	If you omit read-only, and the file is reserved, 1-2-3 displays a dialog box saying that the file is currently in use and asking whether to open it for viewing only or not to open it.

where is text that specifies the order of the new file in memory. In a macro, the order of files in memory applies only to moving from file to file with macro commands such as {NEXTSHEET} and {PREVSHEET}.

where	1-2-3 opens the new file

before	Immediately before the current file in memory; default if you omit the argument

after	Immediately after the current file in memory

read-text-as specifies how 1-2-3 opens a text file.

read-text-as can be text from the table below.

read-text-as	1-2-3 imports

text	Text and numbers from a nondelimited text file. 1-2-3 enters each line of data as a label in a separate cell using successive cells in the same column.

If a line of data is longer than 512 characters, the macro ends with an error unless you include an {ONERROR} command.

If there are more than 256 columns of data, 1-2-3 truncates the extra data.

If there are more than 8192 lines of data, 1-2-3 creates a new worksheet for each group of 8192 lines. 1-2-3 also creates a new worksheet when it encounters a form feed (0x0C) character.

numbers	Text and numbers from a delimited text file, or only numbers from a nondelimited text file.

tab	Text and numbers from a tab-delimited text file. Two consecutive tabs result in a blank cell.

comma	Text and numbers from a comma-delimited text file.

space	Text and numbers from a space-delimited text file.

semicolon	Text and numbers from a semicolon-delimited text file.

autoparse	Text and numbers based on the layout of the file. 1-2-3 automatically parses the text file by determining where the breaks are, then breaking the data into separate columns in the worksheet.

For numbers, tab, comma, space, and semicolon, 1-2-3 enters each line of data in a separate successive row. Each numeric or text item in the line is entered one item per cell in successive cells in the row.

For numbers, tab, comma, space, semicolon, and autoparse, if there are more than 512 characters between delimiters, 1-2-3 truncates the data. If there are more than 256 columns of data, the macro ends with an error unless you include an {ONERROR} command.

If you omit read-text-as 1-2-3 uses the same separator you specified the last time you used File Open Text Options, {FILE-IMPORT}, or {FILE-OPEN}. If no separator was previously specified, 1-2-3 uses "autoparse" for read-text-as.

character-set is text that specifies the code page you want 1-2-3 to use for interpreting the data in the text file.

Generally, you use the Windows ANSI or DOS code pages. The code page entries in your 123R5.INI file determine which code pages you can use for character-set. To change the list of code pages you can use for character-set, see Modifying Code Page Entries.

character-set	1-2-3 uses this code page

windows	Windows ANSI; default if you omit the argument

dos	DOS or OS/2

cp850	Multilingual

cp932	Japanese

kanji	Kanji

big5	Taiwanese

ks	Korean

gb	Chinese

cp1252	US Windows

cp437	US DOS

cp860	Portuguese

cp863	French Canadian

cp865	Norwegian/Danish

cp1250	Eastern European Windows

cp852	Eastern European DOS

cp1251	Cyrillic Windows

cp866	Cyrillic DOS

cp1253	Greek Windows

cp851	Greek DOS

cp1254	Turkish Windows

cp857	Turkish DOS

cp1255	Hebrew Windows

cp1256	Arabic Windows

Notes

You can use {FILE-OPEN} to open dBASE (.DBF), Excel Worksheet (.XLS), Excel Workbook (.XLW), Paradox (.DB), Text (.TXT, .PRN, .CSV, .DAT, .OUT, .ASC), Shared (.NS4), and SmartMaster template (.WT4) files.

Keep the following in mind when you open dBASE or Paradox files:

• 	1-2-3 ignores password and displays a user ID-and-password dialog box if a password is necessary.

• 	1-2-3 ignores read-only.

• 	1-2-3 ignores where and always opens the file immediately before the current file in memory.

• 	Do not use read-text-only. Opening dBASE or Paradox files as text files may cause unexpected results.

1-2-3 cannot open certain types of files, including .ALL, .FM?, .CGM and .PIC files, with {FILE-OPEN}, {FILE-OPEN?}, or {FILE-RETRIEVE}. If you try to open these types of files with these commands,1-2-3 returns an error.

Use {FILE-COMBINE} to open .CGM and .PIC files.

Examples

The following command opens the file EXPENSES.WK4. If the file is reserved, 1-2-3 will open it for viewing only.

{FILE-OPEN "expenses.wk4";;;"yes"}

The following command replaces the current file with the file FINANCES.WK4.

{FILE-RETRIEVE "finances.wk4"}

See also

Equivalent 1-2-3 command

File Open

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{FILE-SEAL}, {FILE-SEAL-NETWORK-RESERVATION}, {FILE-UNSEAL}
{FILE SEAL}
{FILE-SEAL-NETWORK-RESERVATION}
{FILE-UNSEAL}

{FILE-SEAL [password]} controls the reservation for the current file and seals the file.

{FILE-SEAL-NETWORK-RESERVATION [password]} seals only the network reservation setting of the current file.

{FILE-UNSEAL [password]} unseals the current file and releases its network reservation setting.

Arguments

password is text that specifies a password for sealing or unsealing the current file.

If you omit password for {FILE-SEAL} or {FILE-SEAL-NETWORK-RESERVATION}, 1-2-3 displays a dialog box for entering a password to seal the file. If you choose Cancel in the dialog box, the macro ends.

If you omit password for {FILE-UNSEAL}, 1-2-3 displays a password dialog box. You can enter the password and choose OK, or choose Cancel.

•	If you do not enter the correct password, 1-2-3 returns an error.

•	If you choose Cancel in the dialog box, 1-2-3 does not unseal the file, but continues to the macro command in the next cell.

Examples

The following command seals the current file and prevents changes to its reservation status.  1-2-3 displays a password dialog box for sealing, and later unsealing the file.

{FILE-SEAL}

The following macro seals only the network reservation setting of the current file, runs the subroutine UPDATE, saves the file, and then unseals the network reservation setting. 1-2-3 displays a password dialog box for sealing, and later unsealing the file.

{FILE-SEAL-NETWORK-RESERVATION}

{DISPATCH UPDATE}

{FILE-SAVE}

{FILE-UNSEAL}

See also

Equivalent 1-2-3 command

File Protect

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


Info Components

Info components store information about the current 1-2-3 session.

For example, Worksheet-Background-Color Info component stores the number that identifies the color of the current worksheet.

Using Info Components

• 	In some macro commands, when you omit an optional argument, 1-2-3 uses the current value of an Info component in place of the omitted argument.

For example, if you omit the range argument in {SORT}, 1-2-3 sorts the data in the range specified by the Data-Sort-Range Info component.

• 	You can set an Info component to a particular value by using the {SET} macro command.

For example, {SET "worksheet-format";"currency"} sets the default number format to Currency.

Note Changing an Info component value does not cause 1-2-3 to recalculate, even if you set recalculation to Automatic.

• 	You can find out the value of an Info component by using @INFO.

For example, @INFO("setup-undo") returns YES if Edit Undo is on or NO if Edit Undo is off.

See also

Help

Info Component Categories

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


Info Component Categories

File

Mapping

Print

Printer Setup

Sort

User Setup

Window

Worksheet Defaults

Types of Info components

There are three types of Info component values: Number, Text, and Range.

• 	For Number, use a numeric formula, or the range name or address of a cell that contains a number or numeric formula.

• 	For Text, use any text enclosed in " " (quotation marks), a text formula, or the range name or address of a cell that contains a label or text formula.

For some Text Info components, you can specify any text. In this case, you want 1-2-3 to use the text literally, that is, exactly as you specify it.

For other Text Info components, you must use specific text. For example, the Print-Grid-Lines Info component specifies whether to print grid lines. The value of Print-Grid-Lines can be yes or no.

• 	For Range, use a range name or address, or any formula that evaluates to a range name or address.

See also

Help

Info Components

Macros


File Info Components

Correspond to the options of the File Doc Info command and to the Get reservation automatically check box in the File Protect dialog box.

Component name	Type	Definition
Document-Comments	Text	Comments about the file, up to 256 characters
Document-Keywords	Text	File keywords, up to 256 characters
Document-Revisions	Text	Notes about file revisions, up to 512 characters
Document-Subject	Text	Subject of the file, up to 256 characters
Document-Title	Text	Title of the file, up to 256 characters
File-Reserve-Automatically	manual, automatic	Whether to ask for the file reservation automatically when opening a file, or to ask for it manually


Mapping Info Components

Corresponds to the options of the Tools Map Set Redraw Preference command.

Component name	Type	Definition
Map-Draw	automatic, manual	Whether to redraw maps automatically when data in the data range changes or to redraw them manually


Print Info Components

Correspond to the arguments of the {PRINT} macro command and the options of the File Print and File Page Setup commands.

Component name	Type	Definition
Print-Beginning-Page-Number	Number	Page number with which to start numbering in headers and footer
Print-Centered	horizontal, vertical, both, clear	Centers a print range on the page
Print-Drawn-Objects	no, yes	Whether to print charts and drawn objects
Print-Fit-Page	no, yes	Whether to shrink what you print to fit a single page
Print-Footer-Center-Text	Text	Center text in footer
Print-Footer-Left-Text	Text	Left text in footer
Print-Footer-Right-Text	Text	Right text in footer
Print-Grid-Lines	no, yes	Whether to print grid lines
Print-Header-Center-Text	Text	Center text in header
Print-Header-Left-Text	Text	Left text in header
Print-Header-Right-Text	Text	Right text in header
Print-Margin-Bottom	Number	Bottom margin height, in inches
Print-Margin-Left	Number	Left margin width, in inches
Print-Margin-Right	Number	Right margin width, in inches
Print-Margin-Top	Number	Top margin height, in inches
Print-Orientation	landscape, portrait	Whether to print in portrait or landscape mode
Print-Range	Range or Collection	Range or collection to print
Print-Size	actual, fit-all, fit-columns, fit-rows, fill-page, fill-page-in-proportion	What type of print compression, if any, to use
Print-Size-Manual	Number	Manually specifies a print compression percentage
Print-Titles-Clear	columns, rows	Clears row or column titles
Print-Titles-Column-Range	Range	Range that contains column titles
Print-Titles-Row-Range	Range	Range that contains row titles
Print-Worksheet-Frame	no, yes	Whether to print the worksheet frame


Printer Setup Info Components

Correspond to the options of the File Printer Setup command.

Component name	Type	Definition
Printer-Setup-Bins	Text	Default paper bin from the Paper Source drop-down box in your printer's Setup dialog box
Printer-Setup-Copies	Number	Number of copies to print
Printer-Setup-Name	Text	Name of a printer from the Printer list box
Printer-Setup-Paperlength	Number	The paper length, in either inches or millimeters; overrides Printer-Setup-Papersize
Printer-Setup-Paperwidth	Number	The paper width, in either inches or millimeters; overrides Printer-Setup-Papersize
Printer-Setup-Papersize	Text	Paper size from the Paper Size drop-down box in your printer's Setup dialog box
Printer-Setup-Quality	high, medium, low, draft	Print resolution


Sort Info Components

Correspond to the arguments of the {SORT} and {SORT-KEY-DEFINE} macro commands and to the options of the Range Sort command.

Component name	Type	Definition
Data-Sort-Direction[n]	descend, ascend	Direction in which to sort the data according to Data-Sort-Key[n]
Data-Sort-Key[n]	Range	Contains nth sort key
Data-Sort-Range	Range	Contains data you want to sort

Note n is a value from 1 through 255 that specifies a sort key or sort direction.


User Setup Info Components

Correspond to the options of the Tools User Setup, Tools User Setup Recalculation, and Tools User Setup International commands.

Note To view all the information in this topic, maximize the Help window by clicking the Maximize button.

Component name	Type	Definition
Setup-Autoexec	no, yes	Whether to run autoexecute macros (\0 macros)
Setup-Autoformat	no, yes	Whether to use automatic formatting
Setup-Autosave	no, yes	Whether to save files automatically
Setup-Autosave-Interval	Number	In minutes, time between automatic file saves
Setup-Beep	no, yes	Whether to beep on error
Setup-Drag-And-Drop	no, yes	Whether to use drag-and-drop
Setup-File-Link-Update	manual, automatic	Whether to update file links manually or automatically
Setup-International-Currency-Default	Text	Default currency format; use one of the currency formats from the Format drop-down box in the Style Worksheet Defaults dialog box
Setup-International-Currency-Display	iso, symbol	Whether to display currency symbols as International Standards Organization (ISO) codes or as symbols
Setup-International-Currency-Position	prefix, suffix	Whether the currency symbol appears before or after the number
Setup-International-Currency-Symbol	Text	Text for the currency symbol
Setup-International-Date	mm/dd/yy, dd/mm/yy, dd.mm.yy, yy-mm-dd	Default Date format
Setup-International-Negative-Values	parentheses, minus-sign	Whether negative numbers appear in parentheses or with a minus sign
Setup-International-Separators	pcc, cpp, psc, csp, pcb, cpb,psb, csb	Separators for decimals, arguments, and thousands. c is comma; p is period; s is semicolon; b is blank
Setup-International-Text	country, international	Sets the character translation table that 1-2-3 uses when combining and creating text files
Setup-International-Time	hh:mm:ss, hh.mm.ss, hh,mm,ss, HHhMMmSSs	Default Time format
Setup-International-Wk1	lics, ascii	Default setting for 1-2-3 for DOS Release 2 translation
Setup-Recalculate	manual, automatic	How 1-2-3 recalculates formulas
Setup-Recalculate-Iterations	Number	Number of recalculation iterations (from 1 through 50)
Setup-Recalculate-Order	natural, columns, rows	Formula recalculation order
Setup-Recent-Files-Number	Number	Number of recent files to show in the File menu (from 0 through 5)
Setup-Skip-Smartmasters	no, yes	Whether to display the New File dialog box when starting 1-2-3
Setup-Skip-Welcome	no, yes	Whether to display the Welcome dialog box when starting 1-2-3
Setup-Undo	no, yes	Whether to enable Undo
Setup-Worksheet-Directory	Text	Default directory


Window Info Components

Correspond to options of the View commands and to various mouse actions.

Component name	Type	Definition
Window-Custom-Zoom	Number	Sets the percent for View Zoom Custom for the current file
Window-Display-Drawn-Objects	no, yes	Whether to display charts and drawn objects in the current file
Window-Display-Edit-Line	no, yes	Whether to display the edit line in 1-2-3
Window-Display-Frame	no, yes	Whether to display the worksheet frame in the current file
Window-Display-Frame-Type	standard, characters, inches, metric,points/picas	How to display worksheet frame in the current file
Window-Display-Grid-Lines	no, yes	Whether to display grid lines in the current file
Window-Display-Page-Breaks	no, yes	Whether to display page breaks in the current file
Window-Display-Scroll-Bars	no, yes	Whether to display scroll bars in the current file
Window-Display-SmartIcons	no, yes	Whether to display SmartIcons in 1-2-3
Window-Display-Status-Bar	no, yes	Whether to display the status bar in 1-2-3
Window-Display-Tabs	no, yes	Whether to display worksheet tabs in the current file
Window-Height	Number	Sets the height of the current window, in pixels
Window-Split	clear, horizontal, vertical, perspective	How to split the current file
Window-Split-Height	Number	Sets the height of the current pane, in pixels
Window-Split-Synchronize	no, yes	Whether to synchronize scrolling in panes
Window-Split-Width	Number	Sets the width of the current pane, in pixels
Window-Width	Number	Sets the width of the current window, in pixels
Window-X-Position	Number	Sets the horizontal position, in pixels, measured from the left side of the 1-2-3 window to the left side of the current window
Window-Y-Position	Number	Sets the vertical position, in pixels, measured from the top of the 1-2-3 window to the top of the current window


Worksheet Defaults Info Components

Correspond to the options of the Style Worksheet Defaults command.

Note To view all the information in this topic, maximize the Help window by clicking the Maximize button.

Component name	Type	Definition
Worksheet-Align-Text	left, right, center	How to align data in a cell horizontally
Worksheet-Background-Color	Number	Default cell background color (0 through 255)
Worksheet-Column-Width	Number	Default column width
Worksheet-Display- Windows-Defaults	no, yes	Controls whether 1-2-3 uses Windows default colors for display; this setting has no effect on your print colors, which always come from the worksheet text and background colors
Worksheet-Font	Text	Default typeface
Worksheet-Font-Size	Number	Default point size
Worksheet-Format	automatic, comma, currency, fixed, general, hidden, label, percent, scientific, text, +/-, date-short-international, date-long-international, dd-mmm, dd-mmm-yy, mmm-yy, hh:mm am/pm, hh:mm:ss am/pm, time-long-international, time-short-international	Default number format
Worksheet-Format-Color- Negatives	no, yes	Whether to display negative numbers in red
Worksheet-Format-Decimals	Number	Default number of decimal places
Worksheet-Format-Display-Zeros	show, blank	Whether to display zeros in cells that contain the number 0 or formulas that evaluate to 0
Worksheet-Format-Parentheses	no, yes	Whether to enclose all new values in parentheses
Worksheet-Format-Zero-Text	Text	Label to display in place of zeros in cells that contain the number 0 or formulas that evaluate to 0
Worksheet-Grid-Color	Number	Default grid-line color (0 through 255)
Worksheet-Group-Sheets	no, yes	Whether to turn Group mode on or off for the current file
Worksheet-Row-Height	Number	Default row height
Worksheet-Tab-Color	Number	Color of the tab for the current worksheet
Worksheet-Text-Color	Number	Default text color (0 through 255)

Note Setting the default text or background color for the current worksheet also sets the Worksheet-Display-Windows-Defaults component to "no".

If you want to write a macro that displays the Windows default colors, but prints in another color, set the Worksheet-Format-Text and Worksheet-Format-Background components first, and then set the Worksheet-Display-Windows-Defaults component to yes.


{CELL-ENTER}

{CELL-ENTER data;[target-location]} enters data in target-location.

Arguments

data is the text, number, or formula you want to enter in the worksheet, enclosed in " " (quotation marks).

target-location is the name or address of the cell where you want to enter data. If you specify a range, 1-2-3 enters data in the first cell in the range. If you omit target-location, 1-2-3 enters data in the current cell.

Notes

{CELL-ENTER} is equivalent to entering or editing data in a worksheet.

1-2-3 determines whether data is text, a formula, or a number, just as it would if you were typing it at the keyboard. This means, for example, that if data is a formula, 1-2-3 enters the formula in target-location and displays the result of the formula in the worksheet.

The same messages can occur when you use {CELL-ENTER} as when you enter data by typing; for example, "Out of memory." Also, if 1-2-3 determines that data is an invalid formula, it displays the message "Invalid Expression" and does not enter data in target-location.

Examples

The following command enters the company name Absolute Enterprises, Inc., in the current cell.

{CELL-ENTER "Absolute Enterprises; Inc."}

The following commands enter a series of values in cells C5, C6, and C7 of the current worksheet.

{EDIT-GOTO C5}

{CELL-ENTER "221"}

{DOWN}

{CELL-ENTER "217"}

{DOWN}

{CELL-ENTER "212"}

The following command enters a formula in the first cell of the range ACTION; 1-2-3 displays the result of the formula in the worksheet.

{CELL-ENTER "+A1=1#AND#A2=2";action}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{EDIT-GOTO} and {SELECT}
{EDIT-GOTO}
{SELECT}

{EDIT-GOTO name;[part];[type]} selects all or part of a range, query table, chart, or other drawn object, and then scrolls to it. Any items in the same file that were previously selected become unselected.

{SELECT name;[part];[type]} selects all or part of a range, chart, query table, or other drawn object, without scrolling to it. Any items in the same file that were previously selected become unselected.

Arguments

name is the name of the item you want to select. The item can be in the current file or in another open file.

• 	For a range, name is a range name or address.

• 	For a query table, chart, or other drawn object, name is the name of the item, enclosed in " " (quotation marks).

Note 1-2-3 creates drawn-object names, which you cannot edit. The name of a drawn object is displayed in the selection indicator when the drawn object is selected.

part specifies a part that may be included in the item specified by name. For example, you can select a cell in a range or the title in a chart. If you omit part, 1-2-3 selects the entire item specified by name.

• 	If name specifies a range part must be the name or address of a single cell.

• 	If name specifies a chart, part must be the name of a chart element, enclosed in " " (quotation marks).

• 	If name specifies a query table, part must be a field name, enclosed in " " (quotation marks).

type is text that specifies what type of item name refers to. type can be chart, draw, query, or range. If you omit type, 1-2-3 uses range.

Notes

If name specifies a named chart created in Release 2 or Release 3, 1-2-3 adds the chart to the center of the worksheet area displayed on screen.

Examples

The following command selects and displays a range named EXPENSES in the current file.

{EDIT-GOTO EXPENSES}

The following command selects the range A:C1..A:C5 and makes A:C5 the current cell.

{SELECT A:C1..A:C5;A:C5}

The following command selects and displays the chart Costs.

{EDIT-GOTO "Costs";;"chart"}

The following command selects the title of the chart Costs.

{SELECT "Costs";"title box";"chart"}

The following command selects the query table Employees.

{SELECT "Employees";;"query"}

The following command selects and displays the field Salary in the query table Employees.

{EDIT-GOTO "Employees";"Salary";"query"}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SCROLL-COLUMNS} and {SCROLL-ROWS}
{SCROLL-COLUMNS}
{SCROLL-ROWS}

{SCROLL-COLUMNS [amount]} scrolls horizontally by column in the current worksheet.

{SCROLL-ROWS [amount]} scrolls vertically by row in the current worksheet.

Arguments

amount is an integer that specifies how many columns or rows to scroll.

• 	If amount is positive, 1-2-3 scrolls right by column or down by row.

• 	If amount is negative, 1-2-3 scrolls left by column or up by row.

• 	If amount is 0, 1-2-3 does not scroll.

• 	If you omit amount, 1-2-3 uses 1.

Notes

These commands do not change the current cell. For example, if cell A:A1 is current, it is still the current cell after scrolling.

If you used View Split to divide the worksheet window into two panes, 1-2-3 scrolls in the pane that contains the cell pointer.

If the absolute (positive) value of amount is greater than the number of remaining columns or rows in the worksheet, 1-2-3 scrolls as far as possible.

Examples

The following macro scrolls ten columns left and ten rows down. The current cell does not change.

{SCROLL-COLUMNS -10}

{SCROLL-ROWS 10}

See also

Equivalent 1-2-3 action

Clicking the horizontal or vertical scroll arrows.

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SCROLL-TO-CELL}, {SCROLL-TO-COLUMN}, {SCROLL-TO-ROW}
{SCROLL-TO-CELL}
{SCROLL-TO-COLUMN}
{SCROLL-TO-ROW}

{SCROLL-TO-CELL location} scrolls in the current worksheet so that the first cell of location is in the top left corner of the worksheet window.

{SCROLL-TO-COLUMN location} scrolls left or right in the current worksheet so that the leftmost column of location is the leftmost column of the worksheet window.

{SCROLL-TO-ROW location} scrolls up or down in the current worksheet so that the top row of location is the top row in the worksheet window.

Arguments

location is the name or address of the range to which you want to scroll.

Notes

{SCROLL-TO-COLUMN} is equivalent to moving the scroll box in the horizontal scroll bar. {SCROLL-TO-ROW} is equivalent to moving the scroll box in the vertical scroll bar.

If you used View Split to divide the worksheet window into two panes, 1-2-3 scrolls in the pane that contains the cell pointer.

These commands do not change the current cell. For example, if cell A:A1 is current, it is still the current cell after scrolling.

{SCROLL-TO-CELL} returns an error if location is not in the worksheet that contains the current cell. {SCROLL-TO-COLUMN} and {SCROLL-TO-ROW} ignore sheet letters in location.

Examples

The following command scrolls the current worksheet so cell X32 is the top left cell.

{SCROLL-TO-CELL X32}

The following command scrolls the current worksheet horizontally so that column M is the leftmost column.

{SCROLL-TO-COLUMN M1..P25}

The range A:B60..A:F70 is named EXPENSES. The following command scrolls the current worksheet vertically so that row 60 is the top row.

{SCROLL-TO-ROW EXPENSES}

See also

Help

Macros

Navigation Macro Commands

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SCROLL-TO-OBJECT}

{SCROLL-TO-OBJECT name; [type]} scrolls to but does not select a range, query table, chart or other drawn object in the current worksheet.

Arguments

name is the name of the item you want to scroll to.

• 	For a range, name is a range name or address.

• 	For a query table, chart, or other drawn object, name is the name of the item, enclosed in " " (quotation marks).

Note 1-2-3 creates drawn-object names, which you cannot edit. The name of a drawn object is displayed in the selection indicator when the object is selected.

type is text that specifies what type of item name refers to. type can be chart, draw, query, or range. If you omit type, 1-2-3 uses range.

Notes

{SCROLL-TO-OBJECT} does not change the current cell. For example, if cell A:A1 is current, it is still the current cell after scrolling.

You cannot scroll to a part of another item. For example, you can scroll to a chart, but not to the title of a chart.

{SCROLL-TO-OBJECT} returns an error if the item is not on the worksheet that contains the current cell.

Examples

The following command scrolls to a chart named Chart 1 in the current worksheet.

{SCROLL-TO-OBJECT "Chart 1";"chart"}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SELECT-ALL}

{SELECT-ALL [type]} selects one of the following:

•	The active area of the current worksheet

•	All charts or drawn objects in the current worksheet

•	All worksheets in the current file

Arguments

type is text that specifies what type of item to select.

type	1-2-3 selects

cells	The active area of the current worksheet; default if you omit the argument

charts	All charts in the current worksheet

drawn-objects	All drawn objects in the current worksheet

sheets	All worksheets in the current file

Notes

You cannot select parts of items. For example, you can select all charts, but not the titles of all charts.

Examples

The following macro selects the active area of the current worksheet and then formats all the values as comma with no decimal places.

{SELECT-ALL "cells"}

{STYLE-NUMBER-FORMAT "comma";0}

Suppose the current worksheet contains several text blocks. The following macro selects all the text blocks and then changes their font to Arial 10 point.

{SELECT-ALL "drawn-objects"}

{STYLE-FONT "Arial"}

{STYLE-FONT-SIZE 10}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SELECT-APPEND} and {SELECT-REMOVE}
{SELECT-APPEND}
{SELECT-REMOVE}

{SELECT-APPEND name;part} selects all or part of a range, chart, or other drawn object without deselecting those currently selected.

{SELECT-REMOVE name} removes a range, chart, or other drawn object from the currently selected collection.

Arguments

name is the name of the item to add to or remove from the currently selected collection or group of items. If name is not an existing name for the type of item currently selected, {SELECT-APPEND} returns an error.

•	If ranges are currently selected, name can be the name or address of any range in the current file.

•	If charts or drawn objects are currently selected, name must be the name of a chart or drawn object in the current worksheet, enclosed in " " (quotation marks).

Note 1-2-3 creates drawn-object names, which you cannot edit. The name of a drawn object is displayed in the selection indicator when the object is selected.

part specifies a part that may be included in the item specified by name. For example, you can select a cell in a range or the title in a chart. If you omit part, 1-2-3 selects the entire item specified by name.

• 	If name specifies a range, part must be the name or address of a single cell.

• 	If name specifies a chart, part must be the name of a chart element, enclosed in " " (quotation marks).

Notes

Examples

The following macro selects cells in columns C, E, and G and then sets the widths of the columns to 12.

{SELECT A:C1}

{SELECT-APPEND A:E1}

{SELECT-APPEND A:G1}

{COLUMN-WIDTH 12}

The following command removes the range TOTALS from the currently selected collection.

{SELECT-REMOVE TOTALS}

See also

Equivalent 1-2-3 action

Holding down CTRL and clicking an item

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SELECT-RANGE-RELATIVE}

{SELECT-RANGE-RELATIVE [column];[row];[worksheet];[cp-col-off];[cp-row-off];[cp-sheet-off]} moves the cell pointer and then selects a range whose address is represented by offsets of the current cell (the current cell is 0).

Any items in the same file that were previously selected become unselected.

Arguments

column, row, and worksheet extend the selection from the current cell by the specified number of columns, rows, or worksheets.

cp-col-off, cp-row-off, and cp-sheet-off move the current cell a specified number of columns, rows, or worksheets before selecting the range.

column and cp-col-off are offset numbers from -255 through 255.

row and cp-row-off are offset numbers from -8191 through 8191.

worksheet and cp-sheet-off are offset numbers from -255 through 255.

Positive numbers extend the selection or move the current cell as follows:

By column	Right (toward column IV)

By row	Down (toward row 8192)

By worksheet	Forward (away from A:)

Negative numbers extend the selection or move the current cell as follows:

By column	Left (toward column A)

By row	Up (toward row 1)

By worksheet	Back (toward A:)

If you omit any argument, 1-2-3 uses 0.

Notes

1-2-3 moves the cell pointer first and then selects the range.

{SELECT-RANGE-RELATIVE} does not return an error if the absolute value of any argument is larger than the number columns, rows, or worksheets remaining in the file.

• 	If the absolute value of column, row, or worksheet is larger than the number of remaining columns, rows, or worksheets, the selection extends to the first or last column, row, or worksheet.

For example, suppose there are three worksheets in the current file, and the current cell is cell B:C5. If column is -10, row is -10, and worksheet is 10, 1-2-3 extends the selection to cell C:C5.

• 	If the absolute value of cp-col-off, cp-row-off, or cp-sheet-off is larger than the number of columns, rows, or worksheets remaining in the file, 1-2-3 moves the cell pointer to the first or last column, row, or worksheet.

For example, suppose there are three worksheets in the current file, and the current cell is cell B:C5. If cp-col-off is -10, cp-row-off is -10, and cp-sheet-off is 10, 1-2-3 moves the cell pointer to cell C:A1.

Examples

Suppose that the current cell is cell A:C10. The following command selects the range A:C10..B:E25. Relative to the current cell, this range extends 2 columns right, 15 rows down, and 1 worksheet forward.

{SELECT-RANGE-RELATIVE 2;15;1}

Suppose you want to style every other column of your spreadsheet using the same named style. The following macro selects a single-column range of 13 cells and applies the named style called tpc to that range. It then moves the cell pointer to the right two columns, selects another range of 13 cells, and applies the same named style.

{SELECT-RANGE-RELATIVE 0,12,0}

{NAMED-STYLE-USE "tpc"}

{SELECT-RANGE-RELATIVE 0,12,0,2,0,0}

{NAMED-STYLE-USE "tpc"}

See also

Help

Macros

Transcript Record Absolute

Transcript Record Relative

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SELECT-REPLACE}

{SELECT-REPLACE old-range;new-range} replaces old-range with new-range in a collection.

Arguments

old-range is the name or address of a currently selected range you want to replace with new-range.

new-range is the name or address of the range to replace old-range.

Both old-range and new-range must be in the current file.

Examples

The following command replaces the range Q_1 with the range Q_2 in the currently selected collection.

{SELECT-REPLACE Q_1;Q_2}

See also

Equivalent 1-2-3 action

Holding down CTRL and clicking a range

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


Chart Elements

Chart elements let you select parts of a chart with {EDIT-GOTO}, {SELECT}, {SELECT-APPEND}, and {SELECT-REMOVE}.

? range	(? is a letter from A through W)

? range data labels	(? is a letter from A through W)

A range, slice ?	(? is an integer from 0 through the last slice number)

Plot left wall

Plot back wall

Plot floor

X axis

Y axis

2Y axis

X axis major tick marks

Y axis major tick marks

2Y axis major tick marks

X axis minor tick marks

Y axis minor tick marks

2Y axis minor tick marks

X axis grids

Y axis grids

2Y axis grids

X axis zero line

Y axis zero line

2Y axis zero line

X axis 3D zero line

Y axis 3D zero line

2Y axis 3D zero line

X axis labels

Y axis labels

2Y axis labels

X axis title

Y axis title

2Y axis title

X axis units title

Y axis units title

2Y axis units title

Title line 1

Title line 2

Footnote line 1

Footnote line 2

Legend labels

Title box

Footnote box

Legend box

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{PRINT} and {PRINT?}
{PRINT}
{PRINT?}

{PRINT [what];[from];[to];[start];[copies]} prints the current file according to the current page settings.

{PRINT?} displays the File Print dialog box.

Arguments

what is text that specifies what to print. If you omit what, 1-2-3 prints the range specified by the Print-Range Info component.

what	1-2-3 prints

all	The current file

current	The current worksheet

selection	The current selection

from is a value that specifies the page number of the first page to print.

to is a value that specifies the page number of the last page to print.

start is a value that specifies the page number at which to start numbering pages.

copies is a value that specifies the number of copies to print.

Examples

In the following macro, the {PRINT-NAME-USE} command makes the page settings named Memo the current page settings. Then the {PRINT} command prints the current file, using these settings.

{PRINT-NAME-USE "memo"}

{PRINT "all"}

The following commands select and print a collection.

{SELECT A:A1..D25}

{SELECT-APPEND A:F1..A:I25}

{SELECT-APPEND B:B1..B:B25}

{PRINT "selection"}

See also

Equivalent 1-2-3 command

File Print

Help

Macros

Print Info Components

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{PRINT-NAME-ADD} and {PRINT-NAME-USE}
{PRINT-NAME-ADD}
{PRINT-NAME-USE}

{PRINT-NAME-ADD page-setting-name} saves the current page settings as named page settings in a file on disk.

{PRINT-NAME-USE page-setting-name} makes named page settings the current page settings.

Arguments

page-setting-name is text that specifies the name of the file to save the settings to or to retrieve. 1-2-3 automatically adds the extension .AL3 to a file containing named page settings unless you enter a different extension.

Examples

The following command asaves the current page settings in the file LETTERS.AL3.

{PRINT-NAME-ADD "letters"}

The following commands print the current file, using the page settings in the file LETTERS.AL3.

{PRINT-NAME-USE "letters"}

{PRINT}

See also

Equivalent 1-2-3 command

File Page Setup

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{PRINT-RESET}

{PRINT-RESET} replaces the currently selected Margins, Print titles, Header, Footer, Options, Compression, and Orientation settings with the default page layout for the current file.

Examples

The following commands print the current file using the page settings named Letters, and then restore the default page layout.

{PRINT-NAME-USE "letters"}

{PRINT}

{PRINT-RESET}

See also

Equivalent 1-2-3 command

File Page Setup Restore

Help

Macros

Print Info Components

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{DATA-TABLE-1}, {DATA-TABLE-2}, {DATA-TABLE-3}, {DATA-TABLE-RESET}
{DATA-TABLE-1}
{DATA-TABLE-2}
{DATA-TABLE-3}
{DATA-TABLE-RESET}

{DATA-TABLE-1 [output-range];[input-cell-1]} substitutes values for one variable in one or more formulas and enters the results in output-range.

{DATA-TABLE-2 [output-range];[input-cell-1];[input-cell-2]} substitutes values for two variables in one formula and enters the results in output-range.

{DATA-TABLE-3 [output-range];[input-cell-1];[input-cell-2];[input-cell-3];[formula]} substitutes values for three variables in one formula and enters the results in output-range.

{DATA-TABLE-RESET} clears the ranges and input-cell settings for all what-if tables in the current file.

Arguments

output-range is the name or address of a range that contains the formula, a list of input values that the formula uses in place of the variable, and blank cells where 1-2-3 places the results.

input-cell-1, input-cell-2, and input-cell-3 are the names or addresses of the first, second and third cells in which 1-2-3 temporarily enters values while performing the calculations required to create the table. These cells must be unprotected, and they should be blank or contain unimportant data.

formula is the name or address of a cell containing the formula that has the three variables you want to change.

input cell-1, input-cell-2, input-cell-3, and formula must be outside of output-range.

Notes

If you omit output-range, input-cell-1, input-cell-2, input-cell-3, or formula, 1-2-3 uses the same range as the last time you used a {DATA-TABLE} macro command during the current 1-2-3 session.

Examples

The following command puts a table of results in the range PAYMENTS and uses B1 as the input cell.

{DATA-TABLE-1 PAYMENTS;B1}

The following command puts the table of results in the range PROJECT, which spans four worksheets. A:B4, A:B5, and A:B6 are the input cells, and A:A1 is the formula cell.

{DATA-TABLE-3 PROJECT; A:B4; A:B5;A:B6;A:A1}

See also

Equivalent 1-2-3 command

Range Analyze What-If Table

Help

Macros

User's Guide

Chapter 19, "Solving What-If Problems," to learn how to set up what-if tables

Chapter 24, "Using Macros to Automate Your  Work"


{DISTRIBUTION}

{DISTRIBUTION [values-range];[bin-range]} creates a frequency distribution that counts how many values in values-range fall within each numeric interval specified by bin-range.

Arguments

values-range is the address or name of a range containing the values you want 1-2-3 to analyze. 1-2-3 ignores blank cells and cells that contain labels in values-range.

bin-range is the address or name of a single-column range that contains values. These values are the limits of numeric intervals that define the bins of the frequency distribution.

Each cell in bin-range should contain a unique value, and in ascending order (lowest to highest, reading down the column). 1-2-3 assigns the value 0 to all labels in bin-range and includes them in calculations. 1-2-3 enters the frequency distribution in the adjacent column to the right of bin-range.

Notes

If you omit values-range or bin-range, 1-2-3 uses the same range as the last time you used {DISTRIBUTION} during the current 1-2-3 session.

Examples

{DISTRIBUTION}

See also

Equivalent 1-2-3 command

Range Analyze Distribution

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


Example: {DISTRIBUTION}

The following command determines how many of the values in the range MARCH are less than or equal to 3000; greater than 3000 and less than or equal to 5000; greater than 5000 and less than or equal to 7000; and greater than 7000.

The values range MARCH (A2..B10), the bin range LIMITS (D2..D4) and the resulting frequency distribution (E2..E5) are shown below.

{DISTRIBUTION MARCH;LIMITS}

A ------ A ------ B ----- C ----- D ----- E ---

1          MARCH              LIMITS

2    $2,500   $7,000            3000       7

3     3,520    2,500            5000       5

4     4,230    2,325            7000       5

5       300    2,860                       1

6     5,025    5,005

7     1,069    4,950

8     3,555    6,780

9     7,020    6,505

10    3,030    2,435


{FILL}

{FILL [range];[start];[step];[stop];[units]} enters a sequence of values in a specified range.

Arguments

range is the name or address of the range you want to fill. 1-2-3 writes over any existing data in range.

If you omit range, 1-2-3 uses the currently selected range if it is a multi-cell range.

If you omit range and the current selection is a single cell, 1-2-3 uses the same range as the last time you used Range Fill or {FILL}.

start specifies the first value 1-2-3 enters in range.

step specifies the increment between each of the values in the range.

stop specifies the limit of the sequence. If you specify a negative step value, you must specify a stop value that is less than the start value.

units is text that indicates that the step increment is either a number or a unit of time.

units	1-2-3 defines a Step value of 1 as one

numeric	Integer; default if you omit the argument

day	Day

week	Week (7 days)

month	Month (30 or 31 days)

quarter	Quarter (90 days)

year	Year (365 or 366 days)

hour	Hour

minute	Minute

second	Second

Notes

If you omit start, step, or stop, 1-2-3 uses the same values as the last time you used {FILL} during the current 1-2-3 session.

Examples

The following lines from a macro fill the range TERM with dates starting at the current date and ending as near as it can get to the current date plus 100. The step increment is one month. The {STYLE-NUMBER-FORMAT} command formats TERM as 31-Dec-92.

{SELECT TERM}

{FILL ;@NOW;1;@NOW+100;"month"}

{STYLE-NUMBER-FORMAT "dd-mmm-yy"}

See also

Equivalent 1-2-3 command

Range Fill

Help

{FILL-BY-EXAMPLE}

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{FILL-BY-EXAMPLE}

{FILL-BY-EXAMPLE [range]} fills range with a sequence of data. 1-2-3 creates a pattern for the sequence, based on data you include in the range.

Arguments

range is the name or address of the range you want to fill.

If you omit range, 1-2-3 uses the currently selected range or collection.

Examples

The first cell in the twelve-cell range YEAR contains the label January. The following macro command fills the rest of the range with the labels February, March, April, and so on, through December.

{FILL-BY-EXAMPLE YEAR}

See also

Equivalent 1-2-3 command

Range Fill by Example

Help

{FILL}

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{MATRIX-INVERT} and {MATRIX-MULTIPLY}
{MATRIX-INVERT}
{MATRIX-MULTIPLY}

{MATRIX-INVERT [matrix-to-invert];[output-range]} inverts a square matrix.

{MATRIX-MULTIPLY [matrix1];[matrix2];[output-range]} multiplies the matrix in matrix1 by the matrix in matrix2 to create a matrix in output-range that contains the results.

Arguments

matrix-to-invert is the name or address of a range containing the matrix that you want to invert.

matrix1 is the name or address of a range containing the first matrix that you want to multiply.

matrix2 is the name or address of a range containing the second matrix that you want to multiply.

output-range is the name or address of a range where you want 1-2-3 to put the results of the matrix inversion or multiplication.

Caution 1-2-3 writes over any data in output-range.

Notes

If you omit matrix-to-invert, matrix1, matrix2, or output-range, 1-2-3 uses the same range as the last time you used {MATRIX-INVERT} or {MATRIX-MULTIPLY} during the current 1-2-3 session.

Not every matrix can be inverted. If 1-2-3 cannot create an inverse of the matrix in matrix-to-invert, the macro ends with an error.

Examples

The following command inverts a square matrix in a range named SALES and puts the results in a range named INVERT.

{MATRIX-INVERT SALES;INVERT}

The following command multiplies a matrix in a range named EXPENSES by a matrix in a range named BANK, and puts the results in a range named NOW.

{MATRIX-MULTIPLY EXPENSES;BANK;NOW}

See also

Equivalent 1-2-3 commands

Range Analyze Invert Matrix

Range Analyze Multiply Matrix

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{PARSE}

{PARSE [parse-range];[output-range];[format-line]} Converts long labels from an imported text file into separate columns of data of one or more types (values, dates, times, and labels).

Arguments

parse-range is the name or address of a single-column range that contains the labels that you want to parse.

output-range is the name or address of a range where you want 1-2-3 to put the parsed data. Specify either the entire range or only the first cell.

Caution 1-2-3 writes over any existing data in output-range.

format-line is text that tells 1-2-3 how to parse, or separate, data and enter it in a worksheet.

If you omit format-line, the first cell in parse-range must be a valid format line.

Notes

If you omit parse-range or output-range, 1-2-3 uses the same range as the last time you used {PARSE} during the current 1-2-3 session.

Examples

The following command parses the data in range A10..A60 and puts the parsed data in an output range beginning at B50.

{PARSE A10..A60;B50}

See also

Equivalent 1-2-3 command

Range Parse

Help

{FILE-IMPORT}

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{RANGE-NAME-CREATE}, {RANGE-NAME-DELETE}, {RANGE-NAME-DELETE-ALL}
{RANGE-NAME-CREATE}
{RANGE-NAME-DELETE}
{RANGE-NAME-DELETE-ALL}

{RANGE-NAME-CREATE range-name;[range-location]} assigns a name to a range address.

{RANGE-NAME-DELETE range-name} deletes a range name in the current file.

{RANGE-NAME-DELETE-ALL} deletes all range names in the current file.

Arguments

range-name is text that specifies the name you want to assign to a range.

range-location is the address of a range that you want to name. If you omit range-location, 1-2-3 names the currently selected range.

Examples

The following command assigns the name DATES to the range A:A8..A:F10.

{RANGE-NAME-CREATE "dates"; A:A8..A:F10}

The following command deletes the range name DATES and any associated notes.

{RANGE-NAME-DELETE "dates"}

See also

Equivalent 1-2-3 commands

Range Name

Help

Macros

Range Name Conventions

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{RANGE-NAME-LABEL-CREATE}

{RANGE-NAME-LABEL-CREATE [direction];[label-range]} assigns an existing label as the range name for a single cell immediately above, below, to the right of, or to the left of the label.

Arguments

direction is text that specifies the position of the single-cell range(s) relative to text-range.

direction	1-2-3 names cells

right	To the right of the labels (for example, labels in column A become names for adjacent cells in column B); default if you omit the argument

left	To the left of the labels (for example, labels in column B become names for adjacent cells in column A)

up	Above the labels (for example, labels in row 2 become names for cells in row 1)

down	Below the labels (for example, labels in row 1 become names for cells in row 2)

label-range is the name or address of a range that contains the text you want to assign as a range name(s) to an adjacent cell(s). If you omit label-range, 1-2-3 uses the currently selected range.

Examples

The following command assigns the label in each cell of the range HATS as the range name of its right-adjacent cell.

{RANGE-NAME-LABEL-CREATE "right";HATS}

See also

Equivalent 1-2-3 commands

Range Name

Help

Macros

Range Name Conventions

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{RANGE-NAME-TABLE}

{RANGE-NAME-TABLE [table-location]} creates a two-column table with the names of all defined ranges in the current file listed alphabetically in the left column, and the corresponding range addresses listed in the right column.

Arguments

table-location is the name or address of the range where you want 1-2-3 to create the table of range names and addresses. Specify either the entire range or only the first cell.

If you omit table-location, 1-2-3 creates the table starting at the top left cell of the currently selected range.

Caution The table occupies two columns and as many rows as there are range names, plus one blank row. 1-2-3 writes over any existing data in table-location.

Examples

The following command creates a range name table starting at the top left cell of the range NAMES.

{RANGE-NAME-TABLE "names"}

See also

Equivalent 1-2-3 commands

Range Name

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{RANGE-TRANSPOSE}

{RANGE-TRANSPOSE destination;[transpose];[origin]} copies data from origin to destination, transposing the copied data and replacing any copied formulas with their current values.

Arguments

destination is the name or address of the range to which you are copying. Specify either the entire range or only the first cell.

Caution 1-2-3 writes over any existing data in destination.

transpose is text that specifies how to transpose the data.

transpose	1-2-3 does the following

rows-to-columns	Transposes rows of data in origin to columns of data in destination; default if you omit the argument.

columns-to-sheets	Copies the first column in every worksheet of origin to the first worksheet in destination; the second column in every worksheet of origin to the second worksheet in destination; and so on. This argument works only for multi-sheet ranges.

sheets-to-rows	Copies the first row in every worksheet of origin to the first worksheet in destination; the second row in every worksheet of origin to the second worksheet in destination; and so on. This argument works only for 3D ranges.

origin is the name or address of the range that contains data you want to copy and transpose.

If you omit origin, 1-2-3 uses the currently selected range.

Examples

The following {RANGE-TRANSPOSE} command copies the contents and styles of B3..F7 to B9..G20, transposes the rows of data in B3..F7 to columns of data in B9..G20, and replaces any copied formulas with their current values.

{RANGE-TRANSPOSE B9..G20;"rows-to-columns";B3..F7}

See also

Equivalent 1-2-3 command

Range Transpose

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{RANGE-VALUE}

{RANGE-VALUE destination;[origin]} copies the contents and styles from origin to destination, and replaces all copied formulas with their current values.

Arguments

destination is the name or address of the range to which you are copying. Specify either the entire range or only the first cell.

origin is the name or address of the range that contains data you want to copy.

If you omit origin, 1-2-3 uses the currently selected range.

Examples

The following command copies a column of @SUM formulas from the range TOTALS to the range JUNE_TOTALS, and replaces the copied formulas with their current values.

{RANGE-VALUE TOTALS;JUNE_TOTALS}

See also

Equivalent 1-2-3 command

Edit Copy followed by Edit Paste Special

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{REGRESSION}

{REGRESSION [X-range];[Y-range];[output-range];[intercept]} performs multiple linear regression analysis and also calculates the slope of the line that best illustrates the data.

Arguments

X-range contains the independent variables. X-range is the name or address of a range that can contain up to 75 columns and 8,192 rows.

Y-range contains the set of values for the dependent variable. Y-range is the name or address of a single-column range with the same number of rows as X-range.

output-range is the name or address of a range for the results of the regression analysis. Specify either the entire range or only the first cell.

Caution 1-2-3 writes over any existing data in output-range.

intercept is text that specifies whether 1-2-3 calculates the y-axis intercept or uses 0 as the y-axis intercept.

intercept	1-2-3 does the following

compute	Calculates the y-axis intercept; default if you omit the argument

zero	Uses 0 as the y-axis intercept

Notes

If you omit X-range, Y-range, or output-range, 1-2-3 uses the same range as the last time you used {REGRESSION} during the current 1-2-3 session.

If you specify a single-cell range for output-range, 1-2-3 enters the regression results in a rectangular area nine rows deep and a minimum of four columns wide, with an additional column for each X variable after the second X variable.

If you specify a multiple-cell range for output-range, and it is too small to contain the regression results, the macro ends with an error.

Examples

{REGRESSION}

See also

Equivalent 1-2-3 command

Range Analyze Regression

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


Example: {REGRESSION}

You run an ice cream stand at a tourist location, and you want to predict roughly how many quarts of ice cream you'll sell in the next day.

You think your sales depend on three key factors: the number of hours of sunshine, the midday temperature, and the number of buses in a nearby parking lot.You want to determine the correlation between these factors (X-range, B2..D7) and your sales (Y-range, A2..A7).

A ----- A -- B --- C ----D---

1	Sales	 Sun	Temp	Buses

2	  250	  3	  84	   10

3	  545	  5	  91	    7

4	  550	  5	  89	    8

5	  450	  6	  85	   10

6	  605	  6	  90	   11

7	  615	  7	  88	    9

The following command enters the results of the regression in the worksheet, starting at A11.

{REGRESSION B2..D7;A2..A7;A11}

1-2-3 enters these regression results in the worksheet:

Regression Output

Constant	-2327.90951

Std Err of Y Est	32.67147906

R Squared	0.977225218

No. of Observations	    6

Degrees of Freedom	    2

X Coefficient(s)	61.17698	28.44788087	0.595647194

Std Err of Coef.	12.18849	 6.79391567	11.89658121


{SHEET-NAME} and {SHEET-NAME-DELETE}
{SHEET-NAME}
{SHEET-NAME-DELETE}

{SHEET-NAME new-name;[old-name]} names a 1-2-3 worksheet in the current file.

{SHEET-NAME-DELETE [worksheet-name]} deletes the name of a 1-2-3 worksheet in the current file.

Arguments

new-name is text that specifies a new name for the worksheet.

old-name is text that specifies the current name or letter of the worksheet.

If you omit old-name, 1-2-3 names the current worksheet.

worksheet-name is text that specifies the worksheet name to delete. After you delete worksheet-name, the name reverts to the worksheet letter.

If you omit worksheet-name, 1-2-3 deletes the name of the current worksheet, if it is named.

Examples

The following command assigns the name August to worksheet H.

{SHEET-NAME "August";"H"}

The following command deletes the worksheet name July.

{SHEET-NAME-DELETE "July"}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


Macro Help for 1-2-3 Release 5

Note You are now in Macro Help.

To get Help for an individual macro command, choose Search and specify the name of the macro command, without the { }.

For example, to get Help for {SEND-MAIL}, type send-mail.

To return to main Help for 1-2-3 Release 5, click the Contents button, or choose Back or History to return to another Help topic.

Macros


{subroutine}

{subroutine [arg1];[arg2];...;[argn]} performs a subroutine call.

Arguments

subroutine is the range name of the subroutine that you want the macro to call. The range name can refer to the first cell of the subroutine or to the entire subroutine. If you specify a range for subroutine, 1-2-3 begins executing in the top left corner of the range.

Although you can use a range address for subroutine, Lotus recommends that you use a range name. If you move a subroutine (for example, if you insert some rows above the range), a {subroutine} command that refers to subroutine by name will continue to work correctly, but one that refers to subroutine by address will no longer work correctly.

arg1,arg2,...,argn are optional arguments. You can include up to 31 optional arguments. Arguments can be values or text, including formulas and the names or addresses of cells. {subroutine} passes the arguments to the subroutine, which must begin with a {DEFINE} command if arguments are specified. {DEFINE} evaluates and stores the optional arguments in worksheet cells.

Notes

Use subroutines to divide long macros into smaller, more specific tasks. If these tasks are shared by several macros, using a subroutine means you have to write the shared task only once. You can call it from all macros that use it. 1-2-3 can transfer macro execution from one subroutine to another to perform many different tasks during a macro.

To call a subroutine, use a {subroutine} command in the calling macro where you want 1-2-3 to begin executing the subroutine. Specify the name or address of the subroutine as {subroutine}. 1-2-3 temporarily passes control from the calling macro to the subroutine when it encounters a {subroutine} command. A subroutine ends when 1-2-3 executes a {RETURN} command or encounters a blank cell. A {QUIT} command in the subroutine ends the macro.

One subroutine can call another subroutine. This is known as nesting. Using nested subroutines lets you create large macro applications that are clearly structured, accessible, and easy to revise. The number of subroutines you can nest is limited only by the amount of available memory.

When 1-2-3 encounters a {subroutine} command in a subroutine, it immediately starts executing the new subroutine. When the second subroutine ends, 1-2-3 returns to the subroutine that called it and finishes executing that subroutine, and then returns control to the original calling macro. If there is more than one nested subroutine, 1-2-3 keeps returning to the previous subroutine until it finally returns to the original calling macro.

If you don't want 1-2-3 to return from a particular subroutine, use {RESTART} in that subroutine. {RESTART} cancels the return sequence that 1-2-3 tracks as it executes nested subroutines. When 1-2-3 finishes executing the subroutine that contains {RESTART}, macro execution ends.

Examples

The FORMAT1 macro calls the subroutine CURRSUB four times. Each time, it formats a range (Q_1, Q_2, Q_3, or Q_4) that it selects with {EDIT-GOTO}. After each subroutine call, 1-2-3 returns to the next {EDIT-GOTO} command in FORMAT1.

FORMAT1	{EDIT-GOTO Q_1}

{CURRSUB}

{EDIT-GOTO Q_2}

{CURRSUB}

{EDIT-GOTO Q_3}

{CURRSUB}

{EDIT-GOTO Q_4}

{CURRSUB}

CURRSUB	{STYLE-NUMBER-FORMAT "currency";2}

{RETURN}

The {subroutine} command in macro \B passes three arguments to SUBR1. The {DEFINE} command at the beginning of SUBR1 evaluates the arguments before storing them. Thus, it stores the value of the first argument, today's date, as a number in cell ONE; the second argument, the text Closing Price:, as a label in cell TWO; and the value of the third argument, the contents of the cell named CLOSE, as a number in cell THREE.

The macro then formats the current cell as day-month and enters the number stored in cell ONE; moves right one cell and enters the label stored in cell TWO; moves right one cell again, formats the cell as Currency with two decimal places, and enters the number stored in cell THREE.

\B	{SUBR1 @TODAY;"Closing Price:";CLOSE}

...

SUBR1	{DEFINE ONE:V;TWO;THREE:V}

{STYLE-NUMBER-FORMAT "dd-mmm"}{LET @CELLPOINTER("coord");ONE}{R}

{LET @CELLPOINTER("coord");TWO}{R}

{STYLE-NUMBER-FORMAT "currency";2}{LET @CELLPOINTER("coord");THREE}

The result is a row that reads (depending on the date and closing price)

17-Dec  Closing Price  $9.32.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{?}

{?} suspends macro execution until the user presses ENTER, letting the user type any number of keystrokes.

When the user presses ENTER, 1-2-3 ends the {?} command and continues the macro; it does not enter data or complete a menu command unless the next character in the macro is a ~ (tilde) or a cursor movement key name (for example, {DOWN}).

If the user clicks OK with the mouse to enter data, the {?} command remains in effect, allowing further entry before proceeding with the macro.

Examples

The following example moves the cell pointer to the cell named ERR_MSG, which contains an error message, and pauses to let the user read the message. When the user presses ENTER, the macro continues.

{EDIT-GOTO ERR_MSG}

{?}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{APP-ADJUST} and {WINDOW-ADJUST}
{APP-ADJUST}
{WINDOW-ADJUST}

{APP-ADJUST x;y;width;height} moves the 1-2-3 window so that the top left corner of the window is x pixels from the left and y pixels from the top corner of the screen, and sizes the 1-2-3 window to be height pixels high and width pixels wide.

{WINDOW-ADJUST x;y;width;height} moves the active window so that the top left corner of the window is x pixels from the left and y pixels from the top corner of the 1-2-3 window, and sizes the active window to be height pixels high and width pixels wide.

Arguments

x is a value that specifies the horizontal position, in pixels, measured from the left side of the screen to the left side of the 1-2-3 window (for {APP-ADJUST}) or from the left side of the 1-2-3 window to the left side of the window being moved (for {WINDOW-ADJUST}).

y is a value that specifies the vertical position, in pixels, measured from the top of the screen to the top of the 1-2-3 window (for {APP-ADJUST}) or from the top of the 1-2-3 window to the top of the window being moved (for {WINDOW-ADJUST}).

width is a value that specifies the window width, in pixels, from the left border to the right border.

height is a value that specifies the window height, in pixels, from the top border to the bottom border.

Notes

If you specify too large a value for x or y, the window moves partially or completely out of view. Use the Program Manager Window Tile command to bring the window back into view.

Examples

The following macro places the 1-2-3 window 50 pixels from the left and 30 pixels from the top of the screen, and makes the window 215 by 215 pixels in size:

{APP-ADJUST 50;30;215;215}

The following macro makes the Worksheet window SALES.WK4 active, places it 50 pixels from the left and 30 pixels from the top of the 1-2-3 window, and makes it 215 by 215 pixels in size:

{WINDOW-SELECT "SALES.WK4"}

{WINDOW-ADJUST 50;30;215;215}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{APPENDBELOW} and {APPENDRIGHT}
{APPENDBELOW}
{APPENDRIGHT}

{APPENDBELOW target-location;source-location} copies the contents of source-location to the rows immediately below target-location.

{APPENDRIGHT target-location;source-location} copies the contents of source-location to the columns immediately to the right of target-location.

Arguments

target-location and source-location are ranges of any size. If you use named ranges, the range name definition of target-location expands to include the rows or columns that contain the appended data.

Notes

Use {APPENDBELOW} and {APPENDRIGHT} with {FORM} to transfer records from an entry form to a database table.

In the following situations, {APPENDBELOW} and {APPENDRIGHT} fail and the macro stops due to an error:

•	When the number of rows or columns in source-location exceeds the number of rows below or columns to the right of target-location.

•	When appending source-location to target-location would write over data.

•	When rows below or columns to the right of target-location are protected.

When source-location contains formulas, {APPENDBELOW} and {APPENDRIGHT} copy the current values of the formulas to target-location, not the formulas themselves.

Examples

The following macro lets you enter new customer information in an unprotected range named NEWCUST in an entry form named CUSTFORM. It then appends the information in NEWCUST to the customer database table named CUSTDB and expands CUSTDB to include the new record.

{FORM CUSTFORM}

{APPENDBELOW CUSTDB;NEWCUST}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{APP-STATE} and {WINDOW-STATE}
{APP-STATE}
{WINDOW-STATE}

{APP-STATE state} minimizes, maximizes, or restores the 1-2-3 window.

{WINDOW-STATE state} minimizes, maximizes, or restores the active window.

Arguments

state is one of the words from the table below, entered as text.

state	1-2-3 does the following

maximize	Maximizes the window

minimize	Minimizes the window

restore	Restores a maximized window to its previous state

Examples

The following command minimizes the 1-2-3 window.

{APP-STATE "minimize"}

The following command expands the worksheet window to full-screen size.

{WINDOW-STATE "maximize"}

See also

Equivalent 1-2-3 action

Clicking the Maximize, Minimize, or Restore buttons

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{BEEP}

{BEEP} sounds the Windows beep.

Notes

{BEEP} does not produce a tone when Beep on error is not selected in Tools User Setup or if Sound is turned off with the Windows Control Panel.

See also

Help

Macros

{PLAY}

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{BLANK}

{BLANK location} erases the contents of location. {BLANK} does not change the formatting of the cells in location and does not force recalculation.

Arguments

location is the name or address of a cell or range.

Examples

The following macro erases the contents of the range named DATARANGE.

{BLANK DATARANGE}

The following macro erases the entry in the current cell and then resets the number format to the worksheet default.

{BLANK @CELLPOINTER("coord")}

{STYLE-NUMBER-FORMAT-RESET}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{BRANCH}

{BRANCH location} transfers macro control from the current macro instruction to location and does not return to the calling macro.

Arguments

location is the name or address of a cell or range that contains macro instructions. It is often the name of another macro or subroutine. If you specify a range, 1-2-3 branches to the first cell in the range.

Notes

Use {BRANCH} with {IF} to implement if-then-else processing or to transfer control to another macro.

Use {BRANCH} to create a loop by branching to a cell above the {BRANCH} command in the same macro. This structure is useful for repetitive data entry tasks.

{BRANCH} is not the same as {EDIT-GOTO}. {EDIT-GOTO} moves the cell pointer to another cell. {BRANCH} transfers macro execution to the commands that begin in location.

Examples

The following macro transfers control to either the macro named BIG or the macro named SMALL, depending on the value in the cell named SIZE.

{IF SIZE>100}{BRANCH BIG}

{BRANCH SMALL}

The following macro creates a loop for data entry by entering the data you supply during the {?} command in subsequent cells down a column until you press CTRL+BREAK to end the macro.

\A	{?}{DOWN}

{BRANCH \A}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{BREAK}

{BREAK} clears the edit line when data is being entered or edited, or leaves the current dialog box during selection of a 1-2-3 command, and returns 1-2-3 to Ready mode. In any other situation, {BREAK} has no effect.

Examples

The following macro leaves the current dialog box (if any) and displays a range named HELP_SCREEN.

{BREAK}{EDIT-GOTO HELP_SCREEN}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{BREAKOFF} and {BREAKON}
{BREAKOFF}
{BREAKON}

{BREAKOFF} disables CTRL+BREAK while a macro is running.

{BREAKON} restores the use of CTRL+BREAK, undoing a {BREAKOFF} command.

Notes

{BREAKOFF} stays in effect until 1-2-3 executes a {BREAKON} command or until the macro ends.

Caution Add {BREAKOFF} to a macro only after you have thoroughly tested the macro. If {BREAKOFF} is in effect and the macro goes into an infinite loop, the only way to stop the macro is to turn off and restart the computer. All data entered or changed since the last time the worksheet file was saved is lost.

Examples

The following macro disables CTRL+BREAK before starting the PAYROLL subroutine, preventing the user from gaining access to proprietary information by stopping the macro when the payroll file is open. When the PAYROLL subroutine ends, {BREAKON} restores CTRL+BREAK for the rest of the macro.

{BREAKOFF}

{PAYROLL}

{BREAKON}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CLOSE}

{CLOSE} closes the text file opened with an {OPEN} command and saves any changes made to the file.

Examples

The following macro opens a text file named STOCKS with append access, adds a line to the file to report the day's volume for a stock, and closes the file before ending the macro. Without the {CLOSE} command, STOCKS would remain open at the end of the macro, and you could continue processing STOCKS in a subsequent macro without using an {OPEN} command.

{OPEN STOCKS;A}

{WRITELN VOLUME}

{CLOSE}

{QUIT}

See also

Help

Macros

Working with Text Files

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{COMMIT} and {ROLLBACK}
{COMMIT}
{ROLLBACK}

{COMMIT [driver-name];[database-name]} commits (finalizes) pending external database transactions.

{ROLLBACK [driver-name];[database-name]} cancels pending external database transactions.

Arguments

driver-name is text that specifies the name of the driver.

database-name is text that specifies the name of the external database.

You must use both arguments or no arguments. {COMMIT} or {ROLLBACK} with arguments commits or cancels only the transaction pending for the driver and database you specify. {COMMIT} or {ROLLBACK} with no arguments commits or cancels all pending transactions.

Notes

{COMMIT} and {ROLLBACK} work with the SQL Server driver only.

To learn more about using macros for transaction control, see Using macros for transaction control.

Examples

The following command commits the transaction pending for the driver SQL_SERVER and the database named PAYROLL.

{COMMIT "SQL_SERVER";"PAYROLL"}

The following command cancels the transaction pending for the driver SQL_SERVER and the database named PAYROLL.

{ROLLBACK "SQL_SERVER";"PAYROLL"}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CONTENTS}

{CONTENTS target-location;source-location;[width];[format]} copies the contents of source-location to target-location as a label.

Arguments

target-location and source-location are the addresses or names of cells or ranges. If you specify ranges, 1-2-3 uses the first cells of the ranges.

width is an integer from 1 through 240 that specifies the width of the label 1-2-3 creates.

format is an integer that specifies the format of the label 1-2-3 creates.

format	1-2-3 formats the label as

0 to 15	Fixed, 0 to 15 decimal places

16 to 31	Scientific, 0 to 15 decimal places

32 to 47	Worksheet's default Currency format (specified with Tools User Setup International), 0 to 15 decimal places

48 to 63	Percent, 0 to 15 decimal places

64 to 79	Comma, 0 to 15 decimal places

112	+/-

113	General

114	31-Dec-93

115	31-Dec

116	Dec-90

117	Text

118	Hidden

119	11:59:59 AM

120	11:59 AM

121	12/31/93

122	12/31

123	23:59:59

124	59:59

127	Worksheet's default number format (specified with Style Worksheet Defaults Number Format)

Notes

If you do not include width and format, the label 1-2-3 creates in target-location has the same width and format as source-location.

Although {CONTENTS} changes the contents of cells, 1-2-3 does not automatically recalculate formulas after executing a {CONTENTS} command when worksheet recalculation is set to Automatic. To force recalculation after a {CONTENTS} command, follow the command with {CALC}.

Examples

In the examples below, the cell named INCOME contains the formula +GROSSEXP, which results in $167.24. INCOME is formatted as Currency with two decimal places, and its column width is 9.

{CONTENTS REPORT;INCOME}

+"Today we earned"&REPORT~

Enters the label $167.24 (with a leading and trailing space) in cell REPORT, and then creates the sentence, "Today we earned $167.24" and enters it in the current cell.

{CONTENTS REPORT;INCOME;11;117}

+"The formula we use to calculate earnings is: "&REPORT&"~"

Enters the label +GROSSEXP (with a trailing space) in REPORT (Code 117 is Text format), and then creates the sentence, "The formula we use to calculate earnings is: +GROSSEXP " and enters it in the current cell.

{CONTENTS REPORT;INCOME;3;}

Places the three-character label *** in REPORT, because the specified width is not wide enough to display $167.24.

{CONTENTS REPORT;INCOME;;113}

Places the 167.24 (with two leading spaces and one trailing space) in REPORT. Code 113 formats $167.24 in General format.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{DDE-ADVISE} and {DDE-UNADVISE}
{DDE-ADVISE}
{DDE-UNADVISE}

{DDE-ADVISE [branch-location];item-name;[format];[destination];[acknowledge]} specifies the macro that is executed when data changes in the server application.

{DDE-UNADVISE item-name;[format]} ends a {DDE-ADVISE} command.

Arguments

branch-location is the name or address of a cell or range that contains macro instructions. It is often the name of another macro or subroutine. If you specify a range, 1-2-3 branches to the first cell in the range.

You do not have to include branch-location if you include destination.

item-name is text that specifies the name of the topic item to link to. This is the item in the application file whose data you want transferred through the link.

format is text that specifies one of the Clipboard formats.

{DDE-ADVISE} does not support the Picture/Metafilepict, Bitmap, or DIB Clipboard formats.

If you omit format, 1-2-3 uses the Text Clipboard format.

destination is the name or address of the range where you want the server application to send the data whenever it is updated.

acknowledge is a yes/no argument that specifies whether to acknowledge data messages. If you omit acknowledge, 1-2-3 acknowledges data messages.

Notes

If you include branch-location, 1-2-3 executes the macro at branch-location each time the item specified by item-name is updated by the server application, and then returns to the main macro.

After it successfully executes a {DDE-ADVISE} command, 1-2-3 goes directly to the next cell in the macro, ignoring any macro instructions after {DDE-ADVISE} in the same cell. If the server application could not respond, the macro continues in the same cell as {DDE-ADVISE}.

If the original {DDE-ADVISE} command used a format argument, {DDE-UNADVISE} must use the same format argument.

Examples

See {DDE-ADVISE} and {DDE-UNADVISE} in Example 2 of Sample DDE Macros.

See also

Help

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{DDE-CLOSE}

{DDE-CLOSE [conversation-number]} terminates a conversation with a Windows application.

Arguments

conversation-number specifies which conversation to terminate. conversation-number is the unique identification number Windows assigns to the conversation. If you use the optional location argument in {DDE-OPEN}, 1-2-3 enters the identification number in the worksheet.

If you omit conversation-number, 1-2-3 closes the current conversation.

If you omit conversation-number, and no conversation is current, {DDE-CLOSE} does nothing. To make a conversation current, use {DDE-USE}.

To close all open conversations, specify -1 for conversation-number.

Examples

See {DDE-CLOSE} in Example 1 of Sample DDE Macros.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{DDE-EXECUTE}

{DDE-EXECUTE execute-string} sends a command to an application.

Arguments

execute-string is text that represents any command supported by the server application, including macros.

Notes

{DDE-EXECUTE} returns an error if no conversation is open.

After it successfully executes a {DDE-EXECUTE} command, 1-2-3 goes directly to the next cell in the macro, ignoring any macro instructions after {DDE-EXECUTE} in the same cell. If the server application times out, is busy, or cannot complete the command, the macro continues in the same cell as {DDE-EXECUTE}.

Examples

See {DDE-EXECUTE} in Example 1 of Sample DDE Macros.

See also

Help

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{DDE-OPEN}

{DDE-OPEN app-name;topic-name;[location]} initiates a conversation with a Windows application, making that the current conversation; optionally, enters in location the unique decimal identification number Windows assigns to the conversation.

Arguments

app-name is text that specifies the name of an open Windows application that supports DDE.

topic-name is text that specifies the name of the application file to link to. Use "system" to link to the system topic.

location is a range name or address. If you specify a range, 1-2-3 enters the identification number in the first cell of the range.

Notes

You must start an application before using {DDE-OPEN}. You can start an application either with the Windows Program Manager or with the {LAUNCH} command

You can use more than one {DDE-OPEN} command in a macro; to make a particular conversation current, use {DDE-USE}.

Although location is an optional argument, you should include it if you plan to use more than one {DDE-OPEN} command in a macro.

If 1-2-3 enters 0 in location, the conversation could not be initiated.

After it successfully executes a {DDE-OPEN} command, 1-2-3 goes directly to the next cell in the macro, ignoring any macro instructions after {DDE-OPEN} in the same cell. If {DDE-OPEN} was unsuccessful (for example, if 1-2-3 could not establish the conversation), the macro continues in the same cell as {DDE-OPEN}.

Examples

See {DDE-OPEN} in Example 1 of Sample DDE Macros.

See also

Help

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{DDE-POKE}

{DDE-POKE range;item-name;[format]} sends a range of data to a server application during the current conversation.

Arguments

range is the name or address of the range that contains the data you want to send to the server application.

item-name is text that specifies the name of the item in the server application to link to. This is the item in the server application file to which you want to transfer data through the link.

format is text that specifies one of the Clipboard formats.

If you omit format, 1-2-3 uses the Text Clipboard format.

Notes

Many Windows applications that support DDE as servers do not support {DDE-POKE} functionality. See your application's documentation to find out what level of DDE server support the application provides.

See also

Help

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{DDE-REQUEST}

{DDE-REQUEST range;item-name;[format]} transfers data from a Windows application to 1-2-3.

Arguments

range is the name or address of the range where 1-2-3 enters the requested data.

item-name is text that specifies the name of the item in the server application to link to. This is the item in the server application file whose data you want transferred through the link.

format is text that specifies one of the Clipboard formats.

{DDE-REQUEST} does not support the Picture/Metafilepict, Bitmap, or DIB Clipboard formats.

If you omit the format, 1-2-3 uses the Text Clipboard format.

Notes

{DDE-REQUEST} returns an error if no conversation is open.

After it successfully executes a {DDE-REQUEST} command, 1-2-3 goes directly to the next cell in the macro, ignoring any macro instructions after {DDE-REQUEST} in the same cell. If item-name or format is not supported by the server application, the macro continues in the same cell as {DDE-REQUEST}.

{DDE-REQUEST} does not clear data, or formatting from range before entering data. Use {EDIT-CLEAR} to clear data and formatting from range before using {DDE-REQUEST}.

Examples

See {DDE-REQUEST} in Example 1 of Sample DDE Macros.

See also

Help

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{DDE-USE}

{DDE-USE conversation-number} makes the conversation specified by conversation-number the current conversation.

Arguments

conversation-number is the unique identification number Windows assigns to the conversation. If you use the optional location argument in {DDE-OPEN}, 1-2-3 enters the identification number in the worksheet.

Notes

{DDE-USE} returns an error if conversation-number is invalid or refers to a terminated conversation.

Examples

The following excerpt from a macro opens two DDE conversations with Ami Pro, makes the first conversation current, and transfers data from a bookmark named PAYMENT in LOAN.SAM to the range named LOANPAYMENT in the current 1-2-3 file. The macro then closes both conversations.

{DDE-OPEN "AMIPRO";"D:\DOCS\LOAN.SAM";NUM_1}

{DDE-OPEN "AMIPRO";"C:\DOCS\INTEREST.SAM";NUM_2}

{DDE-USE NUM_1}

{DDE-REQUEST LOANPAYMENT;"PAYMENT"}

{DDE-CLOSE}

{DDE-USE NUM_2}

{DDE-CLOSE}

See also

Help

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{DEFINE}

{DEFINE location1;location2;...;locationn} specifies where to store arguments passed to a subroutine in a {subroutine} command. You must include a {DEFINE} command in any subroutine to which you pass arguments, and the {DEFINE} command must come before the point in the subroutine where the arguments are used.

Arguments

location is the name or address of a cell or range. If location is a range, 1-2-3 uses the first cell of the range as the storage location.

Specify a location argument for each argument in the {subroutine} command. If you do not, the macro terminates with an error when 1-2-3 reaches the {DEFINE} command.

Notes

You can add one of two suffixes to each location argument in a {DEFINE} command.

Although {DEFINE} changes the contents of cells, 1-2-3 does not automatically recalculate formulas after executing a {DEFINE} command when worksheet recalculation is set to Automatic. To force recalculation after a {DEFINE} command, follow the command with {CALC}.

When you use {DEFINE}, it should be the first command in the subroutine.

Examples

The {subroutine} command in macro \A passes three arguments to SUBR1. The {DEFINE} command at the beginning of SUBR1 stores the label @TODAY in cell ONE, the label "Closing Price:" in cell TWO, and the label CLOSE in cell THREE. The {LET} commands then enters the labels in three consecutive cells in a row.

\A	{SUBR1 @TODAY;"Closing Price:";CLOSE}

...

SUBR1	{DEFINE ONE;TWO;THREE}

{LET @CELLPOINTER("coord");ONE}{R}

{LET @CELLPOINTER("coord");TWO}{R}

{LET @CELLPOINTER("coord");THREE}

The result is a row that reads:

@TODAY  Closing Price:  CLOSE

The {subroutine} command in macro \B passes three arguments to SUBR1. The {DEFINE} command at the beginning of SUBR1 evaluates the arguments before storing them. Thus, it stores the value of the first argument, today's date, as a number in cell ONE; the second argument, the text Closing Price:, as a label in cell TWO; and the value of the third argument, the contents of the cell named CLOSE, as a number in cell THREE.

The macro then formats the current cell as day-month and enters the number stored in cell ONE; moves right one cell and enters the label stored in cell TWO; moves right one cell again, formats the cell as Currency with two decimal places, and enters the number stored in cell THREE.

\B	{SUBR1 @TODAY;"Closing Price:";CLOSE}

...

SUBR1	{DEFINE ONE:V;TWO;THREE:V}

{STYLE-NUMBER-FORMAT "dd-mmm"}{LET @CELLPOINTER("coord");ONE}{R}

{LET @CELLPOINTER("coord");TWO}{R}

{STYLE-NUMBER-FORMAT "currency";2}{LET @CELLPOINTER("coord");THREE}

The result is a row that reads (depending on the date and closing price)

17-Dec  Closing Price  $9.32.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{DISPATCH}

{DISPATCH location} performs an indirect branch by transferring macro control to the cell whose name or address is entered in location.

Arguments

location is a single cell containing the name or address of the cell to which macro control is transferred. If location is a multiple-cell range, 1-2-3 branches to location instead of to the cell whose name or address is entered in the first cell of location.

Notes

Use {DISPATCH} to have 1-2-3 branch to one of several possible macros, depending on the contents of location. {DISPATCH} is particularly useful in macros that change depending on conditions in the worksheet.

{DISPATCH} does not return control to the calling macro. If you want to return control to the calling macro, use {BRANCH} or another {DISPATCH} in the called macro.

Examples

The following excerpt from a macro sets the label in the cell named SWITCH and then transfers macro control to the macro whose name is in SWITCH.

{IF BALANCE<0}{LET SWITCH;NEGATIVE:s}

{IF BALANCE=0}{LET SWITCH;ZERO:s}

{IF BALANCE>0}{LET SWITCH;POSITIVE:s}

{DISPATCH SWITCH}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{EDIT-CLEAR}

{EDIT-CLEAR [selection];[property]} deletes data and related formatting from the worksheet without moving it to the Clipboard.

Arguments

selection is the name or address of the range whose contents you want to delete.

If you omit selection, 1-2-3 deletes the contents of the current selection.

property is text that specifies whether to delete cell contents, formatting, or both.

property	1-2-3 deletes

contents	The contents of all cells in the selected range but leaves formatting intact.

styles	All formatting done with the Style commands (except left, center, and right label alignment) for the selected range; returns the font settings to the default font set for the worksheet file with Style Font & Attributes; returns the number format settings to the default set for the worksheet with Style Worksheet Defaults;and returns the color settings to the defaults set for the worksheet window with Window Display Options.

both	Both cell contents and formatting.

Notes

{EDIT-CLEAR} does not clear protected cells or change their format.

Examples

The following {EDIT-CLEAR} command removes the contents of the range LOCAL and returns all formatting to the default.

{EDIT-CLEAR LOCAL;"both"}

See also

Equivalent 1-2-3 command

Edit Clear

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{EDIT-COPY},{EDIT-CUT},{EDIT-PASTE}
{EDIT-COPY}
{EDIT-CUT}
{EDIT-PASTE}

{EDIT-COPY [selection];[format]} copies data and related formatting from the worksheet to the Clipboard.

{EDIT-CUT [selection];[format]} cuts data and related formatting from the worksheet to the Clipboard.

{EDIT-PASTE [selection];[format]} copies data and related formatting from the Clipboard into the active worksheet file.

Arguments

For {EDIT-COPY} or {EDIT-CUT}, selection is the name or address of the range whose contents you want to copy to the Clipboard.

For {EDIT-PASTE}, selection is the name or address of the range where you want to paste the contents of the Clipboard.

If you omit selection, 1-2-3 uses the current selection.

format is text that specifies one of the Clipboard formats.

If you omit format, 1-2-3 uses all appropriate formats.

Examples

The following command copies the contents of the range SALES to the Clipboard:

{EDIT-COPY SALES}

The following commands cut the contents of the range TAXES to the Clipboard and then paste them in the range starting at cell D5.

{EDIT-CUT TAXES}

{EDIT-PASTE D5}

See also

Equivalent 1-2-3 commands

Edit Copy

Edit Cut

Edit Paste

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{EDIT-PASTE-LINK}

{EDIT-PASTE-LINK [destination];[format];[reference]} creates a link between a 1-2-3 for Windows worksheet file and the file referenced on the Clipboard.

Arguments

destination is the name or address of the range to which you want the server application to send the data whenever it is updated.

format is text that specifies one of the Clipboard formats.

If you are creating a link with 1-2-3 cell data, 1-2-3 ignores format.

If you omit format, 1-2-3 uses the Text Clipboard format.

reference is text that specifies whether 1-2-3 creates absolute or relative cell references when linking to 1-2-3 cell data. If you are creating a link with data from another application, 1-2-3 ignores reference.

reference	1-2-3 creates

absolute	Absolute references

relative	Relative references; default if you omit the argument

Notes

You can use {EDIT-PASTE-LINK} only when the Clipboard contains data copied from a valid source file -- that is, from another worksheet file or from a file created with another Windows application that supports OLE or DDE.

When you copy data from a valid source file to the Clipboard, 1-2-3 also stores the link reference (for example, a range address) on the Clipboard. {EDIT-PASTE-LINK} links the active worksheet file to the source file from which you copied the data. Initially, 1-2-3 creates this new link with the update mode set to Automatic (updates the link automatically whenever the source file changes).

See also

Equivalent 1-2-3 command

Edit Paste Link

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{FILESIZE}

{FILESIZE location} counts the number of bytes in an open text file and enters the number in location.

Arguments

location is the name or address of a cell or a range. If you specify a range, 1-2-3 enters the number in the first cell of the range.

Examples

The following macro enters in cell BYTES the number of bytes in the open text file. The {READ} command then copies the contents of the text file into cell FILECONTENTS. If no text file is open, 1-2-3 branches to cell NO_OPEN_FILE for further instructions.

{FILESIZE BYTES}{BRANCH NO_OPEN_FILE}

{READ BYTES;FILECONTENTS}

See also

Help

Macros

Working with Text Files

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{FOR} and {FORBREAK}
{FOR}
{FORBREAK}

{FOR counter;start;stop;step;subroutine} creates a for loop; it repeatedly performs a subroutine call to subroutine.

{FORBREAK} cancels a for loop created by a {FOR} command.

Arguments

counter is the name or address of a cell that keeps track of subroutine execution during the for loop. counter should be a blank cell, since anything in counter is replaced.

start is the initial value for counter.

stop is the value that tells 1-2-3 when to terminate the for loop.

step is the value added to counter each time 1-2-3 executes the subroutine.

subroutine is the range name or address of the subroutine that 1-2-3 executes in the for loop.

Notes about {FOR}

When 1-2-3 encounters a {FOR} command, it does the following:

1.	Enters start in counter.

2.	Compares the number in counter with stop. If the number in counter is less than or equal to stop, 1-2-3 performs a subroutine call to subroutine and goes to step 3.

If the number in counter is greater than stop, 1-2-3 does not perform a subroutine call to subroutine. Instead, 1-2-3 returns to the location of the {FOR} command and continues the macro at the instruction following {FOR}.

3.	Increases the number in counter by step and returns to step 2.

If step is 0, the number in counter can never exceed stop, and the for loop becomes an infinite loop. Press CTRL+BREAK to stop an infinite for loop.

1-2-3 stores start, stop, and step internally. You cannot have subroutine modify these values once it starts.

Although {FOR} changes the contents of cells, 1-2-3 does not automatically recalculate formulas after executing a {FOR} command when worksheet recalculation is set to Automatic. To force recalculation after a {FOR} command, follow the command with {CALC}.

Notes about {FORBREAK}

After executing a {FORBREAK} command, 1-2-3 returns to the calling macro and executes the instruction immediately following the {FOR} command.

Use {FORBREAK} only within a for loop. Using {FORBREAK} anywhere else causes the macro to terminate with an error.

Examples

In macro \A below, 1-2-3 repeats subroutine ENTRY up to ten times, to let you enter names in a roster. If you press ENTER at the {GET-LABEL} command instead of typing a name, the {FORBREAK} command terminates the for loop and 1-2-3 continues immediately to the instructions following the {FOR} command.

\A	{BLANK ROSTER}

{EDIT-GOTO ROSTER}

{FOR J10;1;10;1;ENTRY}

...

ENTRY	{GET-LABEL "Enter name:";@CELLPOINTER("coord")}

{IF @CELLPOINTER("contents")=""}{FORBREAK}

{DOWN}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{FORM} and {FORMBREAK}
{FORM}
{FORMBREAK}

{FORM input-location;[call-table];[include-list];[exclude-list]} suspends macro execution temporarily so you can enter and edit data in the unprotected cells in input-location.

{FORMBREAK} ends a {FORM} command.

Arguments

input-location is a range of any size that contains cells you unprotected with Style Protection. This is the range where you enter data. input-location cannot include any hidden columns or worksheets.

call-table is a two-column range. Each cell in the first column contains the macro name of a key on the keyboard. Each adjacent cell in the second column contains a set of macro instructions that 1-2-3 performs when the user presses the key listed in the first column.

include-list is a range that contains a list of allowable keystrokes.

exclude-list is a range that contains a list of keystrokes to ignore. If you specify an include-list, do not specify an exclude-list, and vice versa. If you specify both an include-list and an exclude-list, 1-2-3 uses the include-list and ignores the exclude-list.

Notes about {FORM}

When 1-2-3 encounters a {FORM} command in a macro, it moves the cell pointer to the first unprotected cell in input-location, suspends macro execution, and waits for you to press a key. What happens when you press a key depends on whether the {FORM} command uses optional arguments.

If the {FORM} command does not use optional arguments, you can press any typewriter key or pointer-movement key, and any of the following keys: ENTER, ESC, F1 (HELP), F2 (EDIT), HOME, END, and, while typing or editing an entry, BACKSPACE and (if the entry is a value) F9 (CALC). If input-location is a 3D range, you can also use CTRL+PG UP and CTRL+PG DN. If you are typing or editing a formula, you can use F4 and F3 (NAME).

When you end the {FORM} command (by pressing either ENTER or ESC when the mode indicator displays READY), 1-2-3 continues the macro, leaving the cell pointer wherever it was when you pressed ENTER or ESC.

If the {FORM} command includes one or more optional arguments, 1-2-3 proceeds as follows:

1.	If the {FORM} command uses a call-table argument, 1-2-3 checks the first column of the call table. If the keystroke is listed, 1-2-3 executes the instructions in the second column as a subroutine, and then returns to the {FORM} command and waits for you to press another key.

Note Including {ESC} or ~ (tilde) in a call-table subroutine, at a point in the subroutine when the mode indicator displays READY, temporarily suspends the {FORM} command, letting you move the cell pointer out of the input-range unprotected area and use all 1-2-3 keys and menus for their standard functions for the rest of the subroutine.

When the call-table subroutine ends, 1-2-3 moves the cell pointer back to wherever it was when the call-table subroutine started (unless the cell pointer is within the input-range unprotected area when the subroutine ends, in which case 1-2-3 leaves the cell pointer where it is) and reinstates use of 1-2-3 keys as defined by the {FORM} command.

To end a macro from within a call-table subroutine, use {RESTART} or {QUIT} in the subroutine. To end a {FORM} command from within a call-table subroutine but continue the macro, use {FORMBREAK} to leave the {FORM} command and continue macro execution at the instruction immediately following the {FORM} command.

2.	If the keystroke is not in call-table and the {FORM} command uses an include-list argument, 1-2-3 checks include-list. If the keystroke appears in include-list, 1-2-3 performs the keystroke. Otherwise, 1-2-3 ignores the keystroke.

3.	If there is no include-list argument and the {FORM} command uses an exclude-list argument, 1-2-3 checks exclude-list. If the keystroke appears in exclude-list, 1-2-3 ignores the keystroke. Otherwise, 1-2-3 performs the keystroke.

call-table, include-list, and exclude-list are case-sensitive for letters typed at the keyboard. For example, if include-list contains an uppercase B but not a lowercase b, 1-2-3 allows only uppercase B's during the {FORM} command; lowercase b's are ignored.

Notes about {FORMBREAK}

After executing a {FORMBREAK} command, 1-2-3 continues macro execution at the instruction immediately following the {FORM} command.

If you use {FORMBREAK} to end a nested {FORM} command, 1-2-3 returns you to the location from which the {FORM} command you are ending was issued and continues the macro there.

Use {FORMBREAK} only within a call-table subroutine or a subroutine to which you transfer control with {BRANCH} or {DISPATCH}. Using {FORMBREAK} anywhere else causes the macro to terminate with an error.

Examples

The {FORM} command in the following example uses a call-table and an exclude-list. This {FORM} command processes inventory orders in a database table using an entry form in a range named ENTRYFORM.

{FORM ENTRYFORM;SIGKEYS;;BADKEYS}

stops the macro temporarily so you can enter an order in the entry form. The call-table, SIGKEYS, includes two key names: {INS} and {END}.

{INS}	{APPENDBELOW ORDERS;INPUT}{BLANK INPUT}

{END}	{FORMBREAK}

•	If you press INS during the {FORM} command, 1-2-3 appends the data in INPUT to the order database table (ORDERS), erases INPUT, and returns to the {FORM} command.

•	If you press END during the {FORM} command, the {FORMBREAK} command ends the {FORM} command and 1-2-3 continues to any instructions that immediately follow the {FORM} command.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{FRAMEOFF} and {FRAMEON}
{FRAMEOFF}
{FRAMEON}

{FRAMEOFF} and {FRAMEON} have no effect in 1-2-3 Release 4 or later.

Notes

To hide the worksheet frame, use the command {SET "window-display-frame";"no"}.

To redisplay the worksheet frame, use the command {SET "window-display-frame";"yes"}.

See also

Help

Macros

{SET}

Window Info Components

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{GET}

{GET location} suspends macro execution until you press a key, and then records the keystroke as a left-aligned label in location.

Arguments

location is the name or address of a cell or range. If you specify a range, 1-2-3 records the keystroke in the first cell of the range.

Notes

After executing a {GET} command, 1-2-3 continues to the cell immediately below the {GET} command, ignoring any instructions in the same cell as the {GET} command.

There is no time limit on a {GET} command; the macro waits indefinitely for a keystroke.

You must use two {GET} commands to record any of the following keystrokes: CTRL+END HOME, END CTRL+HOME, CTRL+END END, CTRL+END CTRL+PG UP, and CTRL+END CTRL+PG DN.

Although {GET} changes the contents of cells, 1-2-3 does not automatically recalculate formulas after executing a {GET} command when worksheet recalculation is set to Automatic. To force recalculation after a {GET} command, follow the command with {CALC}.

Examples

The following macro prompts you to choose Daily or Monthly (by typing D or M) and stores the keystroke in the cell named CHOICE. If the keystroke in CHOICE is D, 1-2-3 branches to DAY; if it is M, 1-2-3 branches to MONTH. If the keystroke is anything else, 1-2-3 beeps and starts the macro again.

\A	{EDIT-GOTO EXPENSES}

{INDICATE "Choose (D)aily or (M)onthly"}

{GET CHOICE}

{IF CHOICE="d"}{BRANCH DAYS}

{IF CHOICE="m"}{BRANCH MONTHS}

{BEEP}{BRANCH \A}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{GETPOS}

{GETPOS location} enters a number in location. This number reports the current byte-pointer position in the open text file.

Arguments

location is the name or address of a cell or a range. If you specify a range, 1-2-3 enters the number in the first cell of the range.

Examples

The following line from a macro records the current position of the byte pointer in cell POINTER. If the {GETPOS} command succeeds, the macro continues in the next cell. If a text file is not open, the macro branches to FAIL, which contains further instructions.

{GETPOS POINTER}{BRANCH FAIL}

See also

Help

Macros

Working with Text Files

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{IF}

{IF condition} evaluates condition as true or false. If condition is true, 1-2-3 continues with the next instruction immediately following the {IF} command in the same cell. If condition is false, 1-2-3 goes immediately to the next cell in the column, skipping any further instructions in the same cell as the {IF} command.

Arguments

condition is usually a logical formula or the name or address of a cell that contains a logical formula. However, you can use any formula, number, text, or cell name or address as condition. 1-2-3 evaluates any condition that does not equal 0 as false. Blank cells, text, and ERR and NA values all equal 0 when used as condition.

Notes

If you use {IF} to implement if-then-else processing in a macro, be sure to include a {BRANCH} or {RETURN} command at the end of the "then" instructions (the instructions that follow the {IF} command in the same cell). This keeps 1-2-3 from continuing to the "else" instructions (the instructions that start in the cell below the {IF} command).

Examples

In the following macro, if the entry in the cell named DATE is between 21002 and 31959 (the date numbers for July 1, 1957, and July 1, 1987, respectively), the macro copies the contents of DATE to the current cell and ends. If not, the macro continues to the {BRANCH} command in the cell below.

{IF DATE>21002}{IF DATE<31959}{EDIT-QUICK-COPY DATE}{QUIT}

{BRANCH INVALID_DATE}

The following macro creates mailing labels from the records in a database table. First, the macro checks to see whether the current cell is blank. (A blank cell indicates the end of the database table.) If so, the macro branches to subroutine PRINT, which contains the macro instructions for printing the mailing labels. If not, it calls subroutine MAKE_A_LABEL, which contains the macro instructions for creating a mailing label, then moves the cell pointer down one cell and repeats macro \A from the beginning.

\A	{IF @CELLPOINTER("type")="b"}{BRANCH PRINT}

{MAKE_A_LABEL}

{DOWN}{BRANCH \A}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{INDICATE}

{INDICATE [text]} displays text in the title bar until 1-2-3 reaches another {INDICATE} command or until you end the 1-2-3 session.

Arguments

text is any text that fits in the title bar.

Notes

Using an empty string as text -- {INDICATE ""} -- displays a blank title bar.

{INDICATE} with no argument restores standard operation of the title bar.

Examples

The following command displays Database Maintenance Macro in the title bar.

{INDICATE "Database Maintenance Macro"}

The following command displays the contents of a cell named MSG in the title bar.

{INDICATE MSG}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{LAUNCH} and {LOTUS-LAUNCH}
{LAUNCH}
{LOTUS-LAUNCH}

{LAUNCH command;[window];[switch-to]} starts and optionally switches to a Windows application.

{LOTUS-LAUNCH name;[window];[switch-to]} starts and optionally switches to a Lotus Windows application.

Arguments

command is text that specifies the command string that starts the Windows application, including the path and any command-line arguments.

name is text that specifies the name of the application to start. Enter name as it appears in the Lotus Applications section of the file LOTUS.INI.

For example, to start Lotus Organizer with {LOTUS-LAUNCH}, name is "ORGANIZE"; to start Ami Pro, name is "AMIPRO".

window is an integer from 0 through 9 that controls the initial state of the application.

The following table shows the possible values of window and their effects on the application you want to start.

Note Not all values for window are supported by all Windows applications.

window	1-2-3 does the following

0	Hides the application window and activates another window.

1	Activates and displays the application window. If the window is minimized or maximized, restores it to its original size and position.

2	Activates and minimizes the application window.

3	Activates and maximizes the application window.

4	Displays the application window in its most recent size and position. 1-2-3 remains the active application.

5	Activates the application window and displays it in its current size and position.

6	Minimizes the application window and activates the top-level window in the window-manager's list.

7	Minimizes the application window. 1-2-3 remains the active application. This is the value 1-2-3 uses if you do not include a window argument.

8	Displays the application window in its current state. 1-2-3 remains the active application.

9	Activates and displays the application window. If the window is minimized or maximized, restores it to its original size and position.

switch-to lets you switch to the application specified in command or to any currently running Windows application.

switch-to is the text that appears in the title bar of an application. switch-to can match the entire title, or just the beginning of it. For example, to switch to Ami Pro, you can specify just "ami" for switch-to.

If you omit switch-to or if you specify text that does not match the title bar of a running application, 1-2-3 launches the application specified in command.

If you specify a different application for switch-to than you specify for command, 1-2-3 switches to the application specified for switch-to, but does not launch the application specified for command.

Examples

The following command starts and maximizes Ami Pro.

{LOTUS-LAUNCH "AMIPRO";3}

The following command switches to the Windows Notepad utility if it is already open, and opens the file LIST.TXT. The command starts Notepad if it is not already open.

{LAUNCH "C:\WINDOWS\NOTEPAD C:\WINDOWS\LIST.TXT";;"Notepad"}

See also

Help

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{LET}

{LET location;entry} enters a number or left-aligned label in location.

Arguments

location is the name or address of a cell or a range. If you specify a range, 1-2-3 enters the number or label in the first cell of the range.

entry is a number, text, a formula, or name or address of a cell that contains a number, a label, or a formula.

Notes

If you use a formula for entry, 1-2-3 evaluates the formula and enters the result in location. {LET} does not enter formulas.

You can add one of two suffixes to entry.

Although {LET} changes the contents of cells, 1-2-3 does not automatically recalculate formulas after executing a {LET} command when worksheet recalculation is set to Automatic. To force recalculation after a {LET} command, follow the command with {CALC}.

Examples

In the following macro, the {LET} command enters the result of 1.5 times the value in QTR_1 in the cell named QTR_2 if QTR_1 is a defined range name. If it is not, the {LET} command enters ERR in the cell named QTR_2.

{LET QTR_2;1.5*QTR_1:v}

The following macro enters 1.5*QTR_1 as a label in the cell named QTR_2:

{LET QTR_2;"1.5*QTR_1"}

The following macro enters the result of the text formula +"Ms." &NAME in the cell named CUSTOMER. If NAME is not a defined range name, the macro enters ERR in the cell named CUSTOMER.

{LET CUSTOMER;+"Ms. "&NAME:v}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{LINK-ASSIGN}

{LINK-ASSIGN link-name;range;[clear-styles]} specifies a range to make an OLE or DDE link to (a destination range).

Arguments

link-name is text that specifies the name for the link specified in {LINK-CREATE}.

If link-name does not refer to an existing link, {LINK-ASSIGN} returns an error.

range is the name or address of the destination range.

Caution When the destination range is not big enough to hold the incoming data, 1-2-3 clips the incoming data that does not fit into the destination range.

clear-styles is a yes/no argument that specifies whether to delete styles in range whenever the client data is updated. If you omit clear-styles, 1-2-3 does not delete styles. If the link is not for range data, 1-2-3 ignores clear-styles.

Notes

If link-name refers to an active link, 1-2-3 updates data from the server application when it reaches a {LINK-ASSIGN} command, even if the link-update mode is manual.

You cannot assign more than one destination range to any one link.

1-2-3 no longer supports property arguments for {LINK-ASSIGN}. If your macros currently contain {LINK-ASSIGN} commands that include property arguments, remove the arguments before you run the macros.

Examples

See {LINK-ASSIGN} in Example 4 of Sample DDE Macros.

See also

Help

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{LINK-CREATE}

{LINK-CREATE link-name;app-name;topic-name;item-name;[format];[mode];[branch-location]}, without using the Clipboard, creates a link between the current worksheet file and another Windows application that supports DDE or OLE as a server.

Arguments

link-name is text that specifies a link name. If the link-name you specify is already in use, {LINK-CREATE} returns an error.

app-name is text that specifies the name of an open Windows application that supports DDE or OLE as a server. Do not include a path and extension in app-name. Doing so may result in an inactive link.

topic-name is text that specifies the name of the application file to link to. Use "system" to link to the system topic.

item-name is text that specifies the name of the item to link to.

format is text that specifies one of the Clipboard formats.

If you omit format, 1-2-3 uses the Text Clipboard format.

mode is text that specifies when data is updated.

mode	1-2-3 updates data in the destination range

automatic	Each time the source item is updated; default if you omit the argument

manual	Only when you use {LINK-UPDATE}.

If the server application does not support the requested mode, data is updated in the mode supported by the server.

branch-location is the name or address of the cell where macro execution will start when the data from the link is updated. If you specify a range, 1-2-3 branches to the first cell in the range.

1-2-3 ignores branch-location for OLE links.

Notes

If the DDE section of your 123W.INI file contains the line

AUTOSTART=1

and the application specified by app-name is not active, {LINK-CREATE} tries to launch the application with a concatenation of app-name and topic-name.

After it successfully executes a {LINK-CREATE} command, 1-2-3 goes directly to the next cell in the macro, ignoring any macro instructions after {LINK-CREATE} in the same cell. If {LINK-CREATE} cannot establish a conversation, the macro continues in the same cell as {LINK-CREATE}.

Generally, 1-2-3 tries to create an OLE link. If 1-2-3 cannot create an OLE link, it creates a DDE link instead. For example, there are certain formats, such as Text, WK1, and WK3, for which 1-2-3 cannot create OLE links.

Examples

See {LINK-CREATE} in Examples 4 and 5 of Sample DDE Macros.

See also

Help

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{LINK-DEACTIVATE}

{LINK-DEACTIVATE [link-name]} deactivates a DDE or OLE link in the current worksheet, but leaves the link intact. When a link is inactive, 1-2-3 does not update values in the destination range.

Arguments

link-name is text that specifies the name for the link specified in {LINK-CREATE}.

If link-name does not refer to an existing link, {LINK-DEACTIVATE} returns an error.

If you omit link-name, 1-2-3 deactivates all existing links.

Notes

Use {LINK-UPDATE} to reactivate a link.

Use {LINK-DELETE} to permanently delete a link.

Examples

See {LINK-DEACTIVATE} in Example 4 of Sample DDE Macros.

See also

Help

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{LINK-DELETE}

{LINK-DELETE link-name} deletes a DDE or OLE link in the current worksheet, but leaves the values obtained through the link in the worksheet.

Arguments

link-name is text that specifies the name for the link specified in {LINK-CREATE}.

If link-name does not refer to an existing link, {LINK-DELETE} returns an error.

Notes

If a destination range was specified for the link you want to delete, 1-2-3 automatically disassociates the destination range from the link, but does not delete the data in the destination range.

When the last link on a conversation is deleted, the conversation is closed.

Examples

The following macro deletes a link between 1-2-3 and Ami Pro named AMILINK2.

{LINK-DELETE "AMILINK2"}

See also

Help

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{LINK-REMOVE}

{LINK-REMOVE link-name} removes the currently used destination range for a DDE or OLE link but does not delete the data in the range.

Arguments

link-name is text that specifies the name for the link specified in {LINK-CREATE}.

If link-name does not refer to an existing link, {LINK-REMOVE} returns an error.

Notes

For OLE links, 1-2-3 leaves in the worksheet a copy of the embedded object (Metafile or Bitmap) which is no longer linked to the Server.

{LINK-REMOVE} does not delete links. Use {LINK-DELETE} to delete links.

If no destination range has been assigned to the link with a {LINK-ASSIGN} command, {LINK-REMOVE} returns an error.

Examples

The following macro changes the current destination range for the link AMILINK2 to MAY_PAYMENT.

{LINK-REMOVE "AMILINK2"}

{LINK-ASSIGN "AMILINK2";"MAY_PAYMENT"}

See also

Help

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{LINK-TABLE}

{LINK-TABLE location} creates a table of all DDE and OLE links associated with the current file.

Arguments

location is the name or address of the top left cell of the range to receive the table. The table will occupy eight columns and as many rows as there are links associated with the file plus one blank row.

Caution 1-2-3 writes over any existing data when it creates the table.

Notes

The table contains the following information for each link: link name, application, topic, item, format, update mode, link status, and destination range. For example:

AMILINK1   Ami Pro   D:\DOCS\LOAN_A.SAM           PAYMENT   TEXT   Automatic   Active   B32

AMILINK2   Ami Pro   D:\DOCS\LOAN_B.SAM           PAYMENT   TEXT   Manual        Active   B35

Examples

The following macro creates a table of links associated with the current file. The table starts in cell LINKS.

{LINK-TABLE "LINKS"}

See also

Help

Edit Links Information for information about update mode and link status

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{LINK-UPDATE}

{LINK-UPDATE [link-name]} updates DDE and OLE links, or activates and updates links deactivated with {LINK-DEACTIVATE}.

Arguments

link-name is text that specifies the name for a link specified in {LINK-CREATE}.

If link-name does not refer to an existing link, {LINK-UPDATE} returns an error.

If you omit link-name, 1-2-3 updates all existing links for that file.

Notes

If you assigned a destination range to the link with {LINK-ASSIGN}, 1-2-3 updates the destination range with current information from the source file.

Examples

See {LINK-UPDATE} in Example 4 of Sample DDE Macros.

See also

Help

Macros

Overview of DDE and OLE in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{LOOK}

{LOOK location} checks the typeahead buffer for keystrokes and records the first keystroke (if any) as a left-aligned label in location. If the buffer is empty, 1-2-3 enters ' (apostrophe label-prefix character) in location.

Arguments

location is the name or address of a cell or range. If you specify a range, 1-2-3 records the keystroke in the first cell in the range.

Notes

The typeahead buffer is the buffer in which 1-2-3 stores keystokes you make during noninteractive parts of a macro.

Use {LOOK} to stop a long macro, break out of an infinite loop, or tell a macro to branch elsewhere. The macro keeps running unless the {LOOK} command records a character that tells the macro to do something else.

1-2-3 does not remove a keystroke it records with {LOOK} from the typeahead buffer, so a subsequent {LOOK} command will record the same keystroke. To remove the first keystroke from the buffer, follow the {LOOK} command with a {GET} command. For example, the sequence {LOOK LOC1}{GET LOC2}{LOOK LOC2} records the first keystroke in the buffer in cell LOC1, removes that keystroke from the buffer, and records the next keystroke in the buffer in cell LOC2.

Although {LOOK} changes the contents of cells, 1-2-3 does not automatically recalculate formulas after executing a {LOOK} command when worksheet recalculation is set to Automatic. To force recalculation after a {LOOK} command, follow the command with {CALC}.

Examples

The TASK macro below is part of a longer macro that requires a user to perform specific tasks. The macro begins by erasing the contents of the cell named KEYCELL. Later, the macro beeps twice and checks whether the user has typed a character. If the typeahead buffer is empty, the macro loops back to the beginning of TASK. If the buffer contains a character, the macro branches to the subroutine NEWTASK.

TASK	{BLANK KEYCELL}

...

{BEEP 4}{BEEP 2}

{LOOK KEYCELL}

{IF KEYCELL=""}{BRANCH TASK}

{BRANCH NEWTASK}

See also.

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{MENUBRANCH} and {MENUCALL}
{MENUBRANCH}
{MENUCALL}

{MENUBRANCH location;[x];[y]} displays a dialog box that contains a list of menu commands; waits for you to select one and then choose OK or Cancel; and then branches to the macro instructions associated with the command you select.

{MENUCALL location;[x];[y]} displays a dialog box that contains a list of menu commands; waits for you to select one and then choose OK or Cancel; and then performs a subroutine call to the macro instructions associated with the command you select.

Arguments

location is the name or address of the first cell of a row that contains the dialog box items. (branch or subroutine names).

x is a value that specifies the horizontal position, in pixels, measured from the left side of the screen to the left side of the dialog box.

y is a value that specifies the vertical position, in pixels, measured from the top of the screen to the top of the dialog box.

If you omit x and y, the dialog box appears in the center of the screen.

Notes

{MENUBRANCH} and {MENUCALL} are included to ensure that macros written with releases of 1-2-3 prior to Release 4 continue to work. When you write new macros, use {MENU-CREATE} to replace the 1-2-3 main menu with a custom menu.

When you highlight an item in the list box, 1-2-3 displays its menu-item description above the list box.

Choosing Cancel or pressing ESC cancels the {MENUBRANCH} or {MENUCALL} command that displayed the dialog box. Macro control returns to the location from which the {MENUBRANCH} or {MENUCALL} command was issued, and the macro continues at the instruction that follows the {MENUBRANCH} or {MENUCALL} command.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{ONERROR}

{ONERROR branch-location;[message-location]} traps and handles errors that occur while a macro is running.

Normally, when an error occurs while a macro is running, 1-2-3 displays an error message and, except for background errors, changes the mode indicator to ERROR and ends the macro. However, if an {ONERROR} command is in effect when the error occurs, 1-2-3 returns to READY mode and branches to branch-location for further macro instructions. If the {ONERROR} command includes the optional message-location argument, 1-2-3 records the error message in message-location.

Arguments

branch-location is the name or address of the cell or range that contains the macro instructions to which 1-2-3 branches after an error occurs. If you specify a range, 1-2-3 branches to the first cell in the range.

message-location is the name or address of a cell or range you specify to store the error message. If you specify a range, 1-2-3 uses the first cell in the range.

Notes

Each {ONERROR} command can handle only one error. An {ONERROR} command remains in effect until an error occurs, until 1-2-3 executes another {ONERROR} command, or until the macro ends.

{ONERROR} traps all types of errors except macro syntax errors (typing errors in macro instructions that prevent 1-2-3 from interpreting the instructions). When 1-2-3 encounters a macro syntax error, it ends the macro and displays an error message that describes the error.

{ONERROR} clears the subroutine stack. This means that if the error occurs in a subroutine, 1-2-3 does not return to the location from which the subroutine call was issued after completing the instructions in branch-location.

When you are using {ONERROR} to trap an error other than the one that results from pressing CTRL+BREAK, you may want to precede the {ONERROR} command with {BREAKOFF}.

Although {ONERROR} changes the contents of cells, 1-2-3 does not automatically recalculate formulas after executing a {ONERROR} command when worksheet recalculation is set to Automatic. To force recalculation after a {ONERROR} command, follow the command with {CALC}.

Examples

The following excerpt from a macro branches to a subroutine named CHOICE2 if an error occurs and stores the error message in a cell named ERR_MSG.

{ONERROR CHOICE2;ERR_MSG}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{OPEN}

{OPEN file-name;access-type} opens a text file for read-only processing or for read-and-write processing, depending on the type of access you specify.

Arguments

file-name is the full name of a text file, including the extension, or the name or address of a cell that contains a text file name. Unless the text file is in the current directory, you must specify the path as part of file-name and enclose the argument in " (quotation marks), for example, "C:\PERSONAL\JOBMEMO.PRN".

access-type is one of the four characters r, w, m, or a (in uppercase or lowercase), or the name or address of a cell that contains one of those characters. The character specifies the type of access you have to the file once it is open:

r	Read access opens an existing file for reading only, placing the byte pointer at the beginning of the file. You can use {READ} and {READLN} but not {WRITE} and {WRITELN} with a file opened with read access.

w	Write access opens a new file for reading and writing. You can use {READ}, {READLN}, {WRITE}, and {WRITELN} with a file opened with write access.

Caution If you open an existing file with write access, 1-2-3 erases the current contents of the file when it opens the file. To open an existing file for writing and retain the existing file contents, use modify or append access.

m	Modify access opens an existing file for reading and writing, placing the byte pointer at the beginning of the file. You can use {READ}, {READLN}, {WRITE}, and {WRITELN} with a file opened with modify access.

a	Append access opens an existing file for reading and writing, placing the byte pointer at the end of the file. You can use {READ}, {READLN}, {WRITE}, and {WRITELN} with a file opened with append access.

Notes

An open text file does not appear on screen. It is open only in the sense that 1-2-3 can use it.

When opening a new file (a file that does not yet exist in the specified directory), you can use write access only. If you try to open a new file with read, modify, or append access, the {OPEN} command will fail.

Examples

The following macro opens a new text file named PASTDUE.PRN in the root directory on drive C, enters the contents of the cell named OVERDUE as the first line of the file, and closes the file; then the macro ends. If unable to open PASTDUE.PRN on drive C, 1-2-3 branches to CONTINUE for further instructions.

{OPEN "C:\PASTDUE.PRN";w}{BRANCH CONTINUE}

{WRITELN OVERDUE}

{CLOSE}

In the following macro, if the working directory contains a file named PASTDUE.PRN, 1-2-3 opens the file with read access, enters the first line of the file in the cell named OVERDUE, closes the file, and ends the macro. If the working directory does not contain a file named PASTDUE.PRN, 1-2-3 branches to CONTINUE.

{OPEN PASTDUE.PRN;r}{BRANCH CONTINUE}

{READLN OVERDUE}

{CLOSE}

See also

Help

Macros

Working with Text Files

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{PANELOFF} and {PANELON}
{PANELOFF}
{PANELON}

{PANELOFF} freezes the control panel until 1-2-3 encounters a {PANELON} command or the macro ends.

{PANELON} unfreezes the control panel and the status line.

Notes

{INDICATE} unfreezes the mode indicator after a {PANELOFF} command.

Examples

The following macro freezes the control panel, status line, and worksheet area so that you don't see the series of prompts and dialog boxes that normally appear during F5 (GOTO) commands.

{PANELOFF}

{EDIT-GOTO DATA_1}

{STYLE-NUMBER-FORMAT "currency";2}

{EDIT-GOTO DATA_2}

{STYLE-NUMBER-FORMAT "currency";2}

{PANELON}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{PUT}

{PUT location;column-offset;row-offset;entry} enters a number or left-aligned label in a cell within location.

Arguments

location is the name or address of a range of any size that contains the cell where you want to enter data.

column-offset and row-offset are offset numbers that identify the column and row position of a cell within location.

entry is a number, text, formula, or name or address of a cell that contains a number, label, or formula. If entry is a text formula, precede it with a + (plus sign).

Notes

If you use a formula for entry, 1-2-3 evaluates the formula and enters the result in location.

You can add one of two suffixes to entry.

Although {PUT} changes the contents of cells, 1-2-3 does not automatically recalculate formulas after executing a {PUT} command when worksheet recalculation is set to Automatic. To force recalculation after a {PUT} command, follow the command with {CALC}.

Examples

The following examples refer to a range named COSTS (A1..D5) in the current worksheet.

{PUT COSTS;3;2;45}

places the number 45 in cell D3.

{PUT COSTS;2;0;MONTH}

copies the contents of the cell named MONTH to cell C1. If MONTH contains a formula, the macro copies the current value of the formula to cell C1.

{PUT COSTS;0;8;500}

results in an error. Range COSTS has only five rows, so a row-offset of 8 is invalid.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{QUIT}

{QUIT} ends a macro immediately, returning keyboard control to the user. 1-2-3 never executes any instructions that follow a {QUIT} command.

Notes

If you use {QUIT} in a subroutine, the command ends the entire macro, not just the subroutine.

Examples

In the following line from a macro, if the cell named YEAR contains the value 1991, the macro ends; otherwise, 1-2-3 continues to the next cell for further macro instructions.

{IF YEAR=1991}{QUIT}

In the following macro, if the value in the cell named YEAR is less than 1991, 1-2-3 branches to OLD; otherwise, the macro ends.

{IF YEAR<1991}{BRANCH OLD}

{QUIT}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{READ} and {READLN}
{READ}
{READLN}

{READ byte-count;location} starts at the current byte-pointer position in the open text file, copies the number of bytes specified by byte-count to location, and advances the byte-pointer byte-count bytes.

{READLN location } starts at the current byte-pointer position in the open text file, copies the remainder of the current line to location, and advances the byte-pointer to the beginning of the next line in the file.

Arguments

byte-count is a value or the name or address of a cell that contains a value from 0 through 511. If the value of byte-count is greater than the number of bytes remaining in the file, 1-2-3 copies all of the remaining bytes to location. Using a negative number or a number greater than 511 as byte-count is equivalent to using 511.

location is the name or address of a cell or range. If you specify a range, 1-2-3 enters the data in the first cell of the range.

Notes

{READ} copies the carriage-return and line-feed characters at the end of text lines.

{READLN} does not copy the carriage-return and line-feed characters at the end of text lines.

Examples

In an open text file, the byte pointer is at the first byte (position 0) of the text:

Total Sales for the Year Ending 1990

The following {READ} command copies the word Total and the space that follows it to the cell named CHARS, and moves the byte pointer forward to the S in Sales.

{READ 6;CHARS}

In an open text file, the byte pointer is at the beginning of the line that contains the word January. Each line ends with a carriage-return.

January

February

The first {READLN} command copies the word January to cell MONTH1. The next {READLN} command copies the word February to the cell MONTH2.

{READLN MONTH1}

{READLN MONTH2}

See also

Help

Macros

Working with Text Files

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{RECALC} and {RECALCCOL}
{RECALC}
{RECALCCOL}

{RECALC location;[condition];[iterations]} recalculates the values in location, proceeding row by row.

{RECALCCOL location;[condition];[iterations]} recalculates the values in location, proceeding column by column.

Arguments

location is the name or address of the cell or range you want to recalculate.

condition tells 1-2-3 to repeat the recalculation until condition is true.

condition is usually a logical formula or the name or address of a cell that contains a logical formula, but it can also be a numeric formula or text formula, a number, or the name or address of a cell.

1-2-3 evaluates any condition that does not equal 0 as true and any condition that does equal 0 as false. A numeric formula or number is considered a true condition unless its value is 0; a text formula is always a true condition; and a reference to a cell that contains the value ERR or NA, a text formula, or a label is always a true condition. A reference to a blank cell is always a false condition.

If condition is the name or address of a cell that contains a formula, and the formula needs to be recalculated for the {RECALC} or {RECALCCOL} command to work correctly, be sure the cell is inside location.

iterations is a value that specifies the number of recalculation passes to perform. This argument overrides the iterations setting in Tools User Setup Recalculation Manual. If iterations is 0, 1-2-3 performs the recalculation once.

If you include both condition and iterations, 1-2-3 repeats the recalculation until condition is true or until it has performed the specified number of iterations, whichever happens first.

Notes

Use {RECALC} or {RECALCCOL} only if recalculation is set to Manual (with Tools User Setup Recalculation).

When 1-2-3 recalculates with {RECALC} or {RECALCCOL}, it does not update formulas outside the range. To ensure that all your formulas are up to date at the end of a macro that uses {RECALC} or {RECALCCOL}, include a {CALC} instruction in the macro, change worksheet recalculation to Automatic, or press F9 (CALC) when the macro ends.

Examples

The following command uses iterations without condition to perform exactly 100 recalculation passes row by row on the range TAXES.

{RECALC TAXES;;100}

The following command recalculates the range named PAYMENT, column by column, until the value in the cell named VAL falls below 100 or the number of recalculations equals 50.

{RECALCCOL PAYMENT;VAL<100;50}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{RESTART}

{RESTART} clears the subroutine stack, ending the macro when the current subroutine ends.

Notes

When 1-2-3 encounters a {RESTART} command, it executes the remaining instructions in the current subroutine, but instead of returning control to the original macro location after it completes the current subroutine, the macro ends. If the instructions that follow {RESTART} in the subroutine transfer macro control elsewhere, however, the macro does not end.

Examples

The following excerpt from a subroutine combines {RESTART} with {IF} to clear the subroutine stack and branch to NEXTPLAN if the cell named STATUS contains the label Not OK. If STATUS contains anything else or is blank, macro control returns to the original macro location after 1-2-3 completes the remainder of the subroutine.

{IF STATUS="Not OK"}{RESTART}{BRANCH NEXTPLAN}

In the following example, the {FOR} command calls the subroutine BALANCE. The subroutine stores the number entered in cell PURCHASE and enters the new balance in cell BAL. If the new balance is 0 or less, {RESTART} branches to BROKE; otherwise, 1-2-3 repeats the subroutine BALANCE ten times, as specified in the {FOR} command. When the for loop is complete, 1-2-3 returns to the instruction that follows the {FOR} command in the original macro.

{FOR COUNT;1;10;1;BALANCE}

BALANCE	{GETNUMBER "Cost of purchase?";PURCHASE}

{LET BAL;BAL-PURCHASE}

{IF BAL<=0}{RESTART}{BRANCH BROKE}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{RETURN}

{RETURN} returns macro control from a subroutine to the calling macro.

Notes

In a subroutine called by {subroutine} or {MENUCALL}, {RETURN} immediately returns macro control from the subroutine to the location in the calling macro from which {subroutine} or {MENUCALL} was issued. In a subroutine called by a {FOR} command, {RETURN} ends the current iteration of the subroutine and immediately starts the next iteration.

If the subroutine ends with a blank cell, {RETURN} is unnecessary; macro control automatically returns to the calling macro.

When used in the main body of macro instructions rather than in a subroutine, {RETURN} is equivalent to {QUIT}: it ends the macro immediately.

Examples

In the SAVE subroutine below, {GET-LABEL} prompts you to type a response. If you type N or n, 1-2-3 returns immediately to the location from which the subroutine call {SAVE} was issued. If you type Y or y, 1-2-3 saves the current version of the file and then returns to the location from which the subroutine call {SAVE} was issued. If you type any other character, 1-2-3 repeats subroutine SAVE from the beginning.

SAVE	{GET-LABEL;"Save file? (Y/N)";INPUT}

{IF INPUT="N"}{RETURN}

{IF INPUT="Y"}{FILE-SAVE}{RETURN}

{BRANCH SAVE}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{SETPOS}

{SETPOS offset-number} moves the byte pointer in an open text file offset-number bytes from the first byte in the file.

Arguments

offset-number is an offset number that specifies the position in the file to which you want to move the byte pointer, relative to the first byte in the file.

Caution 1-2-3 does not prevent you from placing the byte pointer past the end of a file. If necessary, use {FILESIZE} to determine the size of a file before using {SETPOS}.

Examples

If the byte pointer is at the beginning of a text file that consists of 250 bytes and begins with the text

This report contains information based on last year's performance

the command

{SETPOS 10}

moves the byte pointer to the letter t at the end of the word report.

The following command, acting on the same text file, enters the value 250 in the cell named BYTES and then moves the byte pointer to the position after the last character in the file. If no text file is open, the macro branches to NEXT.

{FILESIZE BYTES}{BRANCH NEXT}

{SETPOS BYTES}

See also

Help

Macros

Working with Text Files

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{SYSTEM}

{SYSTEM command} temporarily suspends the 1-2-3 session and executes the specified operating system command.

Arguments

command represents any DOS command, including commands that run batch files or other programs. command is any text enclosed in " " (quotation marks), a formula that results in text, or the name or address of a cell that contains a label or formula that results in a label.

Notes

After the operating system executes command, the 1-2-3 session automatically resumes, and the macro continues.

If command sets an error level, follow {SYSTEM} with @INFO("osreturncode") to test whether command was completed successfully.

Caution Do not use {SYSTEM} to load memory-resident programs. If you do so, you may not be able to resume 1-2-3.

Examples

The following command suspends the 1-2-3 session, executes a batch file called COPYFILE, and returns to 1-2-3. The macro continues after the batch file is finished.

{SYSTEM COPYFILE}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{WAIT}

{WAIT time-number} suspends macro execution and displays WAIT as the mode indicator until the time specified by time-number. When the specified time arrives, 1-2-3 removes the WAIT indicator and continues the macro.

Arguments

time-number is a time number. If time-number represents a nonexistent time or a time that has already passed, 1-2-3 ignores the {WAIT} command and continues to the next macro instruction in the same cell.

Notes

1-2-3 uses your computer's date and time settings to keep track of time. Be sure these settings are correct before you use {WAIT}.

During a {WAIT} command, the only keystroke 1-2-3 responds to is CTRL+BREAK. If you press CTRL+BREAK during a {WAIT} command, 1-2-3 ends the macro immediately, unless you used {BREAKOFF} earlier in the macro.

Examples

The macro READCOL displays the message "Press CTRL+BREAK to stop" as the mode indicator, moves the cell pointer down one row, pauses 5 seconds, and beeps. It repeats this process until you press CTRL+BREAK. This macro is useful to examine a long column of entries or to scroll through a long document while you are reading it.

READCOL	{INDICATE "Press CTRL+BREAK to stop"}

PAUSE	{DOWN}

{WAIT @NOW+@TIME(0;0;5)}

{BEEP}{BRANCH PAUSE}

The following macro suspends macro execution for the amount of time specified by @TIMEVALUE(0.012), about 17.3 minutes:

{WAIT @NOW+@TIMEVALUE(0.012)}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{WINDOWSOFF} and {WINDOWSON}
{WINDOWSOFF}
{WINDOWSON}

{WINDOWSOFF} suppresses screen updates while a macro is running.

{WINDOWSON} cancels {WINDOWSOFF} and restores normal worksheet display.

Notes

{WINDOWSOFF} remains in effect until 1-2-3 executes a {WINDOWSON} command or the macro ends.

Examples

The following macro uses {WINDOWSOFF} before recalculating all the active worksheet files and then uses {WINDOWSON} when recalculation is complete. The macro continues, resuming screen updating.

{WINDOWSOFF}

{CALC}

{WINDOWSON}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{WRITE} and {WRITELN}
{WRITE}
{WRITELN}

{WRITE text} copies text to the open text file, starting at the current byte-pointer position.

{WRITELN text} copies text to the open text file, starting at the current byte-pointer position, and adds a carriage return and line feed.

Arguments

text is the text you want to copy. If text is a text formula, 1-2-3 evaluates the formula and writes the resulting text in the file. If text is a value, the macro terminates with an error.

Notes

1-2-3 evaluates text and converts the result from LMBCS codes to ASCII codes. It then copies the converted result to the file, starting at the current position of the byte pointer, and advances the byte pointer to the position just beyond the last character written. If necessary, 1-2-3 extends the length of the file to accommodate the incoming text.

1-2-3 maps LMBCS characters to Windows ANSI characters (using the closest match available), because Windows can only display and print ANSI characters. 1-2-3 accepts all LMNCS characters, but the actual character displayed or printed is limited by the Windows ANSI character set.

A subsequent {WRITE} or {WRITELN} command begins writing where this command stopped, unless you change the position of the byte pointer with a {SETPOS} command. If the byte pointer is in the middle of the file, the incoming text writes over existing data.

Examples

The following line from a macro copies the text

McGuill's Dairy

to the open text file. If no text file is open, or if the file was opened with read-only access, the macro branches to FAIL.

{WRITE "McGuill's Dairy"}{BRANCH FAIL}

The following macro writes a line to the open text file, adds a carriage return and line feed to start a new line, and writes four more lines that each end with a carriage return and line feed. If no text file is open, or if the text file was opened with read-only access, the macro branches to FAIL.

{WRITE "Musical Instruments in My Band"}{BRANCH FAIL}

{WRITELN " "}

{WRITELN "Keyboard"}

{WRITELN "Saxophone"}

{WRITELN "Drums"}

{WRITELN "Guitar"}

See also

Help

Macros

Working with Text Files in 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


suffixes

You can add one of two suffixes -- :string or :value (or an abbreviation of string or value, as long as the first letter is s or v, respectively) -- to entry. The suffix tells 1-2-3 explicitly whether to treat the argument as a literal string (enter the argument verbatim) or to evaluate the argument before entering it.

The :string suffix tells 1-2-3 to store the argument as a left-aligned label, even if the argument looks like a number, formula, or cell or range address.

The :value suffix tells 1-2-3 to evaluate the argument before storing it. If the argument is a number, 1-2-3 stores it as a number. If the argument is a formula, 1-2-3 evaluates the formula, and stores the result either as a left-aligned label (for a text formula) or a number (for a numeric formula). If the argument is a cell address or range name, 1-2-3 evaluates the contents of the referenced cell and stores the result as a label or number.


{BACKSOLVE}

{BACKSOLVE formula-cell;target-value;adjustable-range} finds values for one or more cells that make the result of a formula equal to a value you specify.

Arguments

formula-cell is the name or address of a cell that contains the formula for which you want to get a specific result. If you specify a multi-cell range for formula-cell, 1-2-3 uses the first cell in the range.

target-value is the specific value you want the formula in formula-cell to result in.

adjustable-range is the name or address of a range that contains values that 1-2-3 can change.

The formula in formula-cell must depend directly or indirectly on the cells in adjustable-range.

Examples

The following command sets the result of the formula in B2 to $1,200 and solves for the principal amount in B1.

{BACKSOLVE B2;1200;B1}

See also

Equivalent 1-2-3 command

Range Analyze Backsolver

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SOLVER-ANSWER}

{SOLVER-ANSWER answer} displays in the worksheet the answers or attempts 1-2-3 finds.

Arguments

answer is text that specifies which answer or attempt to display.

answer	1-2-3 does the following

next	Displays the next answer or attempt

first	Displays the optimal answer (if any), the best answer found, or the first answer or attempt

original	Displays the values that were in the worksheet before you ran Solver

solve	Looks for more solutions to the problem

Notes

1-2-3 changes the values in adjustable cells as you go from one answer or attempt to the next. As a result, cells that contain dependent formulas may also change.

After executing a {SOLVER-ANSWER} command, 1-2-3 returns to Ready mode.

Examples

The following command tells 1-2-3 to display the next answer or attempt.

{SOLVER-ANSWER "next"}

See also

Equivalent 1-2-3 command

Solver Answer

Help

Macros

Range Analyze Solver

{SOLVER-ANSWER-SAVE}

Solver Definition

{SOLVER-REPORT}

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SOLVER-ANSWER-SAVE}

{SOLVER-ANSWER-SAVE scenario;[comment]} saves the current answer or attempt as a scenario.

Arguments

scenario is text that specifies a name for the scenario.

comment is text that specifies a comment for the scenario. If you omit comment, 1-2-3 uses the default comment, which consists of the contents of the information box in the Solver Answer dialog box.

Examples

The following command saves the current answer as a scenario named Best Case. 1-2-3 saves the scenario with the default comment.

{SOLVER-ANSWER-SAVE "Best Case"}

See also

Equivalent 1-2-3 command

Solver Answer Save As Scenario

Help

Macros

Range Analyze Solver

{SOLVER-ANSWER}

Solver Definition

{SOLVER-REPORT}

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SOLVER-DEFINE} and {SOLVER-DEFINE?}
{SOLVER-DEFINE}
{SOLVER-DEFINE?}

{SOLVER-DEFINE [adj-cells];[constraint-cells];[optimize];[opt-cell];[opt-type];[answers]} analyzes data in a worksheet and returns a number of possible answers to a problem you define.

{SOLVER-DEFINE? [adj-cells];[constraint-cells];[optimize];[opt-cell];[opt-type];[answers]} displays the Solver Definition dialog box, optionally displaying defaults you specify.

Arguments

adj-cells are the names or addresses of the adjustable cells, enclosed in " " (quotation marks).

constraint-cells are the names or addresses of the constraint cells, enclosed in " " (quotation marks).

You can use more than one range for adj-cells or constraint-cells. To do so, separate each range name or address with a valid argument separator.

For example, the following command uses three adjustable ranges (TV, RADIO, and PRINT).

{SOLVER-DEFINE "TV;Radio;Print";"Cons-Adv";"On";Adv_Profit;Max;1}

optimize is a yes/no argument that specifies whether Solver should use optimal-cell.

opt-cell is the name or address for which you want Solver to find the highest or lowest value.

opt-cell must depend directly or indirectly on the value of one or more cells in adj-cells.

opt-type is text that specifies whether you want Solver to find the highest or lowest value for opt-cell.

opt-type	1-2-3 finds

max	The highest value for opt-cell

min	The lowest value for opt-cell

answers is a value from 1 through 999 that specifies the approximate number of answers you want. 1-2-3 may find more or fewer answers than you request.

Notes

If you omit adj-cells, constraint-cells, opt-cell, opt-type, or answers, 1-2-3 uses the same information as the last time you solved the current problem.

After executing a {SOLVER-DEFINE} command, 1-2-3 returns to Ready mode and puts your first answer in the worksheet.

After {SOLVER-DEFINE?} displays the Solver Definition dialog box and the user chooses OK, the Solver Progress dialog box appears while 1-2-3 analyzes the problem and looks for answers. When 1-2-3 finishes solving, the Solver Answer dialog box appears.

Examples

Install transfers a sample worksheet file, named SOLVER.WK4, to the subdirectory SAMPLE in your 1-2-3 directory. The file contains 15 examples of varying complexity. Each example uses the {SOLVER-DEFINE?} command to solve the problem. In each example, press PG DN until you see the {SOLVER-DEFINE?} command.

See also

Equivalent 1-2-3 command

Range Analyze Solver

Help

Macros

Solver Definition

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SOLVER-REPORT}

{SOLVER-REPORT type;[comp1];[comp2];[diff-value]} creates a new .WK4 file that contains a report based on the current answer.

Arguments

type is text that specifies the type of report to create.

Note You do not need to include comp1, comp2, or diff-value unless you specify Differences for type.

type	1-2-3 creates a report that

answer	Provides information about all the answers or attempts 1-2-3 found for a problem. 1-2-3 creates the report in a new file with a unique name beginning with ANSWER (for example, ANSWER01.WK4).

cells	Lists the adjustable, constraint, and optimal cells used to solve the problem. 1-2-3 creates the report in a new file with a unique name beginning with CELLS (for example, CELLS001.WK4).

differences	Compares two answers or two attempts and reports cells used whose values differ by at least the amount you specify. 1-2-3 creates the report in a new file with a unique name beginning with DIFFS (for example, DIFFS001.WK4).

how	Lists the information used to find an answer or attempt. 1-2-3 creates the report in a new file with a unique name beginning with HOW (for example, HOW00001.WK4).

inconsistent	Provides information about the constraint cells that were not satisfied (returned a value of 0) for the current attempt. 1-2-3 creates the report in a new file with a unique name beginning with INCONS (for example, INCONS01.WK4).

nonbinding	Provides information about the constraint cells that were not binding for the current answer or attempt. 1-2-3 creates the report in a new file with a unique name beginning with NBIND (for example, NBIND001.WK4).

what-if	Lists the amount the adjustable cells can change and still satisfy all the constraints for the current answer. 1-2-3 creates the report in a new file with a unique name beginning with LIMITS (for example, LIMITS01.WK4).

comp1 is a value that specifies the first answer to compare. If you omit comp1, 1-2-3 uses 1.

comp2 is a value that specifies the second answer to compare. If you omit comp2, 1-2-3 uses 2.

diff-value is a value that specifies a significant difference. If you omit diff-value, 1-2-3 uses 0.

Examples

The following command creates a file named ANSWER01.WK4 that provides information about all the answers or attempts 1-2-3 found for the current problem.

{SOLVER-REPORT "answer"}

See also

Equivalent 1-2-3 command

Range Analyze Solver

Help

Macros

{SOLVER-ANSWER}

{SOLVER-ANSWER-SAVE}

Solver Definition

Solver Reports

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{COLUMN-WIDTH}, {COLUMN-WIDTH-FIT-WIDEST}, {COLUMN-WIDTH-RESET}
{COLUMN-WIDTH}
{COLUMN-WIDTH-FIT-WIDEST}
{COLUMN-WIDTH-RESET}

{COLUMN-WIDTH width;[range]} adjusts each column in range to a specified width in the default font and size.

{COLUMN-WIDTH-FIT-WIDEST [range]} adjusts columns to the width of the widest entries included in range.

{COLUMN-WIDTH-RESET [range]} returns each column in range to the default width defined with Style Worksheet Defaults Column Width.

Arguments

width is an integer that specifies the number of digits to which you want to set the columns.

range is the name or address of the range of columns whose width you want to adjust. If you omit range, 1-2-3 uses the currently selected range, collection, or query table.

Examples

The following command sets the column width to 25 for all columns in the range CITIES.

{COLUMN-WIDTH 25;CITIES}

The following commands formats MONTHS, a range of date numbers, in day-month-year format and adjusts each column in the range to the width of the widest entry.

{SELECT MONTHS}

{STYLE-NUMBER-FORMAT "dd-mmm-yy"}

{COLUMN-WIDTH-FIT-WIDEST}

See also

Equivalent 1-2-3 command

Style Column Width

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{HIDE-COLUMNS} and {HIDE-SHEETS}
{HIDE-COLUMNS}
{HIDE-SHEETS}

{HIDE-COLUMNS [range]} hides all columns in range.

{HIDE-SHEETS [range]} hides all worksheets in range.

Arguments

range is the name or address of a range with at least one cell in each column or sheet you want to hide.

If you omit range, 1-2-3 hides any column or sheet that has cells in the currently selected range.

Examples

The following command hides columns D, E, and F in the current file.

{HIDE-COLUMNS D4..F17}

The following command hides worksheets A, B, and C in the current file.

{HIDE-SHEETS A:A1..C:A1}

See also

Equivalent 1-2-3 command

Style Hide

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{NAMED-STYLE-USE}

{NAMED-STYLE-USE style-name;[range]} applies a named style to a range or query table.

Arguments

style-name is text that specifies the name of the style to apply.

range is the name or address of the range to which you want to apply a named style.

If you omit range, 1-2-3 uses the current selection.

Examples

The following commands apply the style named TITLES to a collection.

{SELECT HEADING_1}

{SELECT-APPEND HEADING_2}

{SELECT-APPEND HEADING_3}

{NAMED-STYLE-USE TITLES}

See also

Equivalent 1-2-3 command

Style Named Style

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{PAGE-BREAK-COLUMN} and {PAGE-BREAK-ROW}
{PAGE-BREAK-COLUMN}
{PAGE-BREAK-ROW}

{PAGE-BREAK-COLUMN on-off} inserts or deletes a vertical page break to the left of the column containing the current cell.

{PAGE-BREAK-ROW on-off} inserts or deletes a horizontal page break above the row containing the current cell.

Arguments

on-off is text that specifies whether to insert or delete a page break.

on-off	1-2-3 does the following

on	Inserts a page break

off	Deletes a page break

Examples

The following commands make cell H40 the current cell and then insert a vertical page break.

{SELECT H40}

{PAGE-BREAK-COLUMN "on"}

The following commands make cell H30 the current cell and then delete the horizontal page break above that cell.

{SELECT H30}

{PAGE-BREAK-ROW "off"}

See also

Equivalent 1-2-3 command

Style Page Break

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{PROTECT} and {UNPROTECT}
{PROTECT}
{UNPROTECT}

{PROTECT [range]} turns protection back on for a range that has been unprotected.

{UNPROTECT [range]} turns protection off for a range.

Arguments

range is the name or address of the range you want to protect or unprotect.

If you omit range, 1-2-3 uses the currently selected range or collection.

Examples

The following data-entry macro removes protection from the range PRICES before sealing the file. When the user finished entering data in PRICES, the macro turns protection for the range back on. The user must provide the password to seal and unseal the file.

{UNPROTECT PRICES}

{FILE-SEAL}

{FORM PRICES;SIGKEYS;;BADKEYS}

{FILE-UNSEAL}

{PROTECT PRICES}

See also

Equivalent 1-2-3 command

Style Protection

Help

{FILE-SEAL}

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{ROW-HEIGHT} and {ROW-HEIGHT-FIT-LARGEST}
{ROW-HEIGHT}
{ROW-HEIGHT-FIT-LARGEST}

{ROW-HEIGHT height;[range]} adjusts each row in range to a specified height in points.

{ROW-HEIGHT-FIT-LARGEST [range]} adjusts each row in range to the height of the largest font in that row.

Arguments

height is an integer from 1 through 255 that specifies the row height, in points.

range is the name or address of the range whose row height you want to adjust.

If you omit range, 1-2-3 uses the currently selected range or query table.

Examples

The following command sets the row height to 24 points for all rows in the range GROSS.

{ROW-HEIGHT 24;GROSS}

The following command adjusts each row in the range MONTHS to the height of the largest font in that row.

{ROW-HEIGHT-FIT-LARGEST MONTHS}

See also

Equivalent 1-2-3 command

Style Row Height

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SHOW-COLUMNS} and {SHOW-SHEETS}
{SHOW-COLUMNS}
{SHOW-SHEETS}

{SHOW-COLUMNS [range]} redisplays all hidden columns in range.

{SHOW-SHEETS [range]} redisplays all hidden worksheets in range.

Arguments

range is the name or address of a range with at least one cell in each column or worksheet you want to redisplay.

If you omit range, 1-2-3 uses the currently selected range.

Examples

Columns D, E, and F of the current worksheet are hidden. The following command redisplays them.

{SHOW-COLUMNS D4..F17}

Sheets A, B, and C of the current file are hidden. The following command redisplays them.

{SHOW-SHEETS A:A1..C:A1}

See also

Equivalent 1-2-3 command

Style Hide

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{STYLE-ALIGN-HORIZONTAL}

{STYLE-ALIGN-HORIZONTAL horizontal;[range];[over-cols];[wrap]} changes the horizontal alignment of labels and values in range.

Arguments

horizontal is text that specifies how to align data in range.

horizontal	1-2-3 aligns data

general	Labels to the left and values to the right

left	To the left

center	In the center

right	To the right

evenly	Stretches text within the cell by expanding the space between words and between the letters in words. Evenly has no effect on labels that end with a . (period), ! (exclamation point), ? (question mark), or : (colon).

over-cols is a yes/no argument that specifies whether align the text in the leftmost cell over the columns in range, according to your horizontal selection.

If you omit over-cols, 1-2-3 does not align over columns.

wrap is a yes/no argument that specifies whether labels should wrap to fit inside a single cell.

If you omit wrap, 1-2-3 does not wrap labels.

Examples

The following command left-aligns data in the range DEBITS.

{STYLE-ALIGN-HORIZONTAL "left";DEBITS}

See also

Equivalent 1-2-3 command

Style Alignment

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{STYLE-ALIGN-VERTICAL}

{STYLE-ALIGN-VERTICAL vertical;[range]} aligns text within a cell whose height is bigger than the largest typeface.

Arguments

vertical is text that specifies how to align data in range.

vertical	1-2-3 aligns data

top	With the top of the cell

center	In the center of the cell

bottom	With the bottom of the cell

range is the name or address of the range or query table where you want to align data.

If you omit range, 1-2-3 aligns data in the currently selected range, collection, or query table.

Examples

The following command top-aligns data in the range DEBITS.

{STYLE-ALIGN-VERTICAL "top";DEBITS}

See also

Equivalent 1-2-3 command

Style Alignment

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{STYLE-ALIGN-ORIENTATION}

{STYLE-ALIGN-ORIENTATION orientation;[angle];[range]} changes the orientation of data in range.

Arguments

orientation is an offset number from 0 through 4 that specifies an orientation style from the "Style Alignment Orientation" drop-down box.

angle is an integer from 1 through 90 that specifies the rotation angle if orientation is 4.

If you omit angle, 1-2-3 uses 45.

range is the name or address of the range or query table where you want to change the orientation of data.

If you omit range, 1-2-3 changes the orientation of data in the currently selected range, collection, or query table.

Examples

The following command rotates data in the range TOP_ROW to a 45-degree angle.

{STYLE-ALIGN-ORIENTATION 4;45;TOP_ROW}

See also

Equivalent 1-2-3 command

Style Alignment

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{STYLE-BORDER}

{STYLE-BORDER border;display;[range];[color];[style]} controls borders for range.

Arguments

border is text that specifies the border you want to work with.

border	1-2-3 manipulates the

outline	Border around the entire range

all	Border around each cell in the range

left	Left border of each cell

right	Right border of each cell

top	Top border of each cell

bottom	Bottom border of each cell

display is a yes/no argument that specifies whether to turn the display of border on or off. If you turn display of border off, 1-2-3 ignores color and style.

range is the name or address of the range where you want to work with borders.

If you omit range, 1-2-3 uses the current selection.

color is an offset number from 0 through 15 that specifies a line color from the "Style Lines & Color Line Color" drop-down box.

style is an offset number from 0 through 7 that specifies a line style from the "Style Lines & Color Line Style" drop-down box.

If you omit color or style, the setting remains unchanged

Examples

The first three commands in the following example select a collection. The {STYLE-BORDER} command draws a bold, red line around the outside edge of each range in the collection.

{SELECT A2..B8}

{SELECT-APPEND D3..E27}

{SELECT-APPEND D32..F35}

{STYLE-BORDER "outside";"on";;2;3}

See also

Equivalent 1-2-3 command

Style Lines & Color

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{STYLE-EDGE} and {STYLE-LINE}
{STYLE-EDGE}
{STYLE-LINE}

{STYLE-EDGE [color];[style];[width];[arrowhead]} changes the color, style, and width of the edge of entire charts, chart elements (plot frames, solid data series, titles, legends, and footnotes), text blocks, enclosed drawn objects, arcs, freehand drawings, polylines, OLE objects, and pictures created in other Windows applications.

{STYLE-LINE [color];[style];[width];[arrowhead];[symbol]} changes the color, style, and width of the selected line for drawn lines and chart lines including line data series, grid lines, and axes.

Arguments

color is an integer from 0 through 255 that specifies a color in the color palette.

style is an offset number from 0 through 7 that specifies a line style from the "Style Lines & Color Line Style" drop-down box.

width is an offset number from 0 through 7 that specifies a line width from the "Style Lines & Color Line Width" drop-down box.

arrowhead is an offset number from 0 through 3 that specifies an arrowhead type from the "Style Lines & Color Arrowhead" drop-down box.

symbol is an offset number from 0 through 23 that specifies a data-point symbol from the "Style Lines & Color Symbol" drop-down box. If the current selection is not a line data series, 1-2-3 ignores symbol.

If you omit color, style, width, arrowhead, or symbol, the setting remains unchanged.

Examples

The first three commands in the following macro select three drawn objects. Then the {STYLE-EDGE} command makes all the rectangle edges bold and red.

{SELECT "Rectangle 1";;"draw"}

{SELECT-APPEND "Rectangle 2"}

{SELECT-APPEND "Rectangle 3"}

{STYLE-EDGE 129;1;3}

The first command in the following macro selects the B line data series in the chart COSTS. Then the {STYLE-LINE} command displays the data points as lime-green, hollow squares.

{SELECT "COSTS";"B Range";"chart"}

{STYLE-LINE 34;1;3;;3}

See also

Equivalent 1-2-3 command

Style Lines & Color

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{STYLE-FONT} and {STYLE-FONT-ALL}
{STYLE-FONT}
{STYLE-FONT-ALL}

{STYLE-FONT typeface;[range];[font-family];[character-set]} assigns a font to range.

{STYLE-FONT-ALL [typeface];[size];[bold];[italic];[underline];[range];[underline-style];[font-family];[character-set]} assigns a font and adds bold, italic, and underlining to range.

Arguments

typeface is text that specifies the name of the font that you want to assign.

range is the name or address of the range where you want to work with fonts.

If you omit range, 1-2-3 uses the current selection.

size is a value that specifies the point size that you want to assign.

bold, italic, and underline are yes/no arguments that add or remove boldface, italics and underlining.

If you omit size, bold, italic, or underline, the setting remains unchanged.

underline-style is an offset number from 0 through 2 that specifies an underline style from the "Style Font & Attributes Underline" drop-down box.

If you omit underline-style, 1-2-3 uses 0. If you specify "off" for underline, 1-2-3 ignores underline-style.

font-family and character-set are useful if you are creating an application for other users and you are not sure what fonts are installed on their computers. These arguments help 1-2-3 map typeface to a similar font installed on the user's computer if typeface is not installed.

font-family is text from the following table.

font-family	1-2-3 maps typeface to

dontcare	A font that most closely matches typeface; default if you omit the argument

decorative	A decorative typeface, such as Zapf Dingbats

modern	A fixed font, such as Courier

roman	A serif font, such as Times Roman

script	A script font, such as Zapf Chancery

swiss	A sans serif font, such as Helvetica

character-set is text that specifies a character set. character-set can be ansi, oem, symbol, or kanji. If you omit character-set, 1-2-3 uses ansi.

Examples

The following command assigns the Helvetica font to the range HEADLINE.

{STYLE-FONT "Helvetica";HEADLINE}

The following command applies the font Times New Roman, in 24 point size, to the range HEADLINE and then adds bold and wide underlining.

{STYLE-FONT-ALL "Times New Roman";24;"on";"off";"on";HEADLINE;2}

See also

Equivalent 1-2-3 command

Style Font & Attributes

Help

Macros

{STYLE-FONT-ATTRIBUTES}

{STYLE-FONT-RESET}

{STYLE-FONT-SIZE}

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{STYLE-FONT-ATTRIBUTES}, {STYLE-FONT-RESET}, {STYLE-FONT-SIZE}
{STYLE-FONT-ATTRIBUTES}
{STYLE-FONT-RESET}
{STYLE-FONT-SIZE}

{STYLE-FONT-ATTRIBUTES attribute;on-off;[range];[underline-style]} adds bold, italic, or underlining to range.

{STYLE-FONT-RESET [range]} restores the worksheet default font, font size, attributes and color to range.

{STYLE-FONT-SIZE size;[range]} assigns a point size to the fonts in range.

Arguments

attribute is text that specifies the attribute that you want to add or remove.

attribute	1-2-3 adds or removes

bold	Boldface

italic	Italics

underline	Underlining

on-off is a yes/no argument that specifies whether to add or remove attribute.

range is the name or address of the range where you want to work with fonts.

If you omit range, 1-2-3 uses the current selection.

underline-style is an offset number from 0 through 2 that specifies an underline style from the "Style Font & Attributes Underline" drop-down box.

If you omit underline-style, 1-2-3 uses 0. If you turn off underlining, 1-2-3 ignores underline-style.

size is a value that specifies the point size that you want to assign.

Examples

The following command adds bold to data in the range HATS.

{STYLE-FONT-ATTRIBUTES "bold";"on";HATS}

The following macro selects the range HEADLINE, and assigns the font Helvetica, in 24 point size, to the range.

{SELECT HEADLINE}

{STYLE-FONT "Helvetica"}

{STYLE-FONT-SIZE 24}

See also

Equivalent 1-2-3 command

Style Font & Attributes

Help

Macros

{STYLE-FONT}

{STYLE-FONT-ALL}

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{STYLE-FRAME}

{STYLE-FRAME display;[color];[style];[range]} adds or removes a frame for range.

Arguments

display is a yes/no argument that specifies whether to turn the display of the frame on or off. If you turn display of the frame off, 1-2-3 ignores color and style.

color is an integer from 0 through 255 that specifies a color in the color palette.

style is an offset number from 0 through 15 that specifies a frame style from the "Style Lines & Color Designer frame" drop-down box.

If you omit color or style, the setting remains unchanged.

range is the name or address of the range where you want to work with a frame.

If you omit range, 1-2-3 uses the current selection.

Notes

1-2-3 ignores color if style is 0 through 7.

Examples

The first three commands in the following example select a collection. Then the {STYLE-FRAME} command adds a red, photo-corner frame around each range in the collection.

{SELECT A2..B8}

{SELECT-APPEND D3..E27}

{SELECT-APPEND D32..F35}

{STYLE-FRAME "on";3;129}

See also

Equivalent 1-2-3 command

Style Lines & Color

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{STYLE-GALLERY}

{STYLE-GALLERY template; [range]} formats range  with one of fourteen style templates available in 1-2-3.

Arguments

template is an offset number from 0 through 13 that specifies a template from the "Style Gallery Templates" drop-down box.

range is the name or address of the range you want to format.

If you omit range, 1-2-3 uses the current selection.

Examples

The following command formats the range EXPENSES with the Chisel2 template.

{STYLE-GALLERY 1;EXPENSES}

See also

Equivalent 1-2-3 command

Style Gallery

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{STYLE-INTERIOR}

{STYLE-INTERIOR [background-color];[pattern];[pattern-color];[text-color];[negatives];[range]} adds colors and patterns to range.

Arguments

background-color, pattern-color, and text-color are integers from 0 through 255 that specify colors in the color palette.

pattern is an offset number from 0 through 63 that specifies a pattern from the "Style Lines & Color Pattern" drop-down box.

If you omit background-color, pattern, pattern-color, or text-color, 1-2-3 uses 0.

negatives is a yes/no argument that specifies whether to display negative values in a range or query table in red.

If the current selection is not a range or query table, 1-2-3 ignores negatives. If you omit negatives, 1-2-3 does not change the setting.

range is the name or address of the range you want to format.

If you omit range, 1-2-3 uses the current selection.

Examples

The following commands selects an ellipse and changes the interior color to light gray.

{SELECT ELLIPSE 1;;"draw"}

{STYLE-INTERIOR 95}

The following commands select the query table EMPLOYEES and then changes the background color to light gray and the text color to dark blue.

{SELECT EMPLOYEES;;"query"}

{STYLE-INTERIOR 95;;;186}

The following command displays negative values in the range MARCH in red.

{STYLE-INTERIOR ;;;;"yes";MARCH}

See also

Equivalent 1-2-3 command

Style Lines & Color

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{STYLE-NUMBER-FORMAT} and {STYLE-NUMBER-FORMAT-RESET}
{STYLE-NUMBER-FORMAT}
{STYLE-NUMBER-FORMAT-RESET}

{STYLE-NUMBER-FORMAT [format];[decimals];[parentheses];[range];[currency]} sets the display of values in range.

{STYLE-NUMBER-FORMAT-RESET [range]} resets the format of range to the current default format specified in Style Worksheet Defaults.

Arguments

format is text that specifies the format that you want to assign to the values in range. If you omit format, the current format of range remains unchanged.

format	1-2-3 displays values in this format

automatic	Automatic

comma	Comma

currency	Currency

fixed	Fixed

general	General

hidden	Hidden

label	Label

percent	Percent

scientific	Scientific

text	Text

+/-	+/-

dd-mmm	31-Dec

dd-mmm-yy	31-Dec-93

mmm-yy	Dec-93

date-long-international	12/31/93

date-short-international	12/31

hh:mm am/pm	11:59:PM

hh:mm:ss am/pm	11:59:59 PM

time-long-international	23:59:59

time-short-international	59:59

decimals is an integer from 0 through 15 that specifies the number of decimal places.

If you omit decimals, 1-2-3 uses 2.

parentheses is a yes/no argument that specifies whether or not to enclose values in parentheses.

If you omit parentheses, the current Parentheses setting of range remains unchanged.

range is the name or address of the range to format.

If you omit range, 1-2-3 uses the current selection.

currency is text that specifies the currency symbol to use if format is "currency". currency is the name of a currency symbol from the Currency list box in the Style Number Format dialog box.

Examples

The following commands format the range EUROPE in the 23:59:59 time format.

{SELECT europe}

{STYLE-NUMBER-FORMAT "time-long-international"}

The following commands format the Y-axis for the chart COG as currency

{SELECT "COG";"Y axis labels";"CHART"}

{STYLE-NUMBER-FORMAT "currency";2}

The following commands format the range APRIL as currency using the Japanese Yen currency symbol.

{SELECT april}

{STYLE-NUMBER-FORMAT "currency";2;;;"japanese yen"}

See also

Equivalent 1-2-3 command

Style Number Format

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{ADDIN-INVOKE}, {ADDIN-LOAD}, {ADDIN-REMOVE}, {ADDIN-REMOVE-ALL}
{ADDIN-INVOKE}
{ADDIN-LOAD}
{ADDIN-REMOVE}
{ADDIN-REMOVE-ALL}

{ADDIN-INVOKE add-in} starts an add-in application.

{ADDIN-LOAD add-in} reads an add-in into memory.

{ADDIN-REMOVE add-in} removes an add-in from memory.

{ADDIN-REMOVE-ALL} removes all add-ins from memory.

Arguments

add-in is text that specifies the name of the add-in you want to work with. For {ADDIN-LOAD}, include the path.

Examples

The following command loads an add-in named Finance into memory.

{ADDIN-LOAD "d:\123r5\addins\finance.adw"}

The following command removes an add-in named Finance from memory.

{ADDIN-REMOVE "finance.adw"}

See also

Equivalent 1-2-3 command

Tools Add-in

Help

Add-in Files

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{AUDIT}

{AUDIT audit;[files];[result];[report-range];[audit-range]} highlights or produces a report of all formulas, or the relationships of values and formulas, in the current file or in all active files; also highlights or produces a report on circular references, file links, or DDE links.

Arguments

audit is text that specifies what to audit.

audit	1-2-3 finds

formulas	All cells that contain formulas

precedents	All cells that are referred to by a formula

dependents	All cells that contain a formula that refers to the cells

circular	The first cell involved in a circular reference; 1-2-3 also automatically uses the default settings for all arguments that follow audit

file-links	All cells that contain a formula that links to other 1-2-3 files

dde-links	All cells that contain a link between the current file and other Windows applications.

files is text that specifies which files to audit

files	1-2-3 audits

current-file	The current file; default if you omit the argument

all-open-files	All open files

result is text that specifies how 1-2-3 shows you the results of the audit

result	1-2-3 does the following

selection	Highlights the cells found in the audit; default if you omit the argument

report	Produces a list of all cells found in the audit, one item per cell from top to bottom, left to right, in report-range

report-range is the name or address of a range where 1-2-3 produces an audit report, if you specify report for result. Specify either the entire range or only the first cell.

1-2-3 produces a list of all cells found in the audit, one item per cell from top to bottom, left to right. Specify a range of blank cells. All cells in report-range must be blank.

audit-range is the name or address of a range to audit if you want to find formula precedents or cell dependents.

If you omit audit-range and you specify precedents or dependents for audit, 1-2-3 audits the current selection

Examples

The following command produces a report, starting in G:A1, of all file links in the current file.

{AUDIT "file-links";"current-file";"report";G:A1}

See also

Equivalent 1-2-3 command

Tools Audit

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SPELLCHECK}
{SPELLCHECK?}

{SPELLCHECK?} displays the Tools Spell Check dialog box and waits for you to choose OK.

Notes

When spell checking is complete, 1-2-3 continues to the macro command in the cell below {SPELLCHECK?}.

If you did not install the Spell Checker, {SPELLCHECK?} returns an error

See also

Equivalent 1-2-3 command

Tools Spell Check

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SEND-MAIL}

{SEND-MAIL [to];[cc];[subject];[body];[clipboard];[file]} sends a mail message, using your mail application, while you are working in 1-2-3. The mail can contain text, the contents of the clipboard and the current file.

Arguments

to and cc specify who to send the mail to and who to copy.

•	If to or cc is text enclosed in " " (quotation marks), you can enter only a single name, for example, "Christine Smith" or "Sales Team."

•	If to or cc is the name or address of a range that contains labels, you must enter the labels in a single row or column. 1-2-3 ignores cells that do not contain labels or text formulas. The range you specify can contain up to 100 cells.

•	If you omit to, 1-2-3 displays a dialog box from your mail application.

subject is text that specifies the subject of the mail message, for example "Information About the Monthly Sales Meeting."

body specifies the body of the message.

•	If body is the name or address of a range that contains labels, you must enter the labels in a single row or column. 1-2-3 ignores cells that do not contain labels or text formulas.

•	If body is a multiple-cell range, each label is followed by a line feed and carriage return in the body of the mail message. 1-2-3 ignores cells that do not contain labels or text formulas. The range you specify can contain up to 100 cells.

clipboard is a yes/no argument that specifies whether to attach the contents of the Clipboard to the mail message.

If you omit clipboard, 1-2-3 does not attach the contents of the Clipboard.

Note Only Lotus Notes users can attach the contents of the Clipboard to the mail message.

file is a yes/no argument that specifies whether to attach the current file to the mail message.

If you omit file, 1-2-3 does not attach the current file.

Examples

The following command sends the current worksheet file to the group East Coast Reps under the subject heading Fourth Quarter Sales Plan. The message contains no text.

{SEND-MAIL "East Coast Reps";;"Fourth Quarter Sales Plan";;"no";"yes"}

See also

Equivalent 1-2-3 command

File Send Mail

Help

Macros

Sending Mail from 1-2-3

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{ -- comment}

{-- comment} puts a comment into a macro. This macro keyword is two - (hyphens) with no spaces between them.

Arguments

comment is the text of the comment that you want to add. comment can contain any characters except } (close brace). You do not have to enclose comment in " " (quotation marks).

Notes

When 1-2-3 encounters a {-- comment}, it ignores this command and proceeds to the next macro command.

Use comment commands to document your macros. Frequent comment commands with long comments, however, slow macro performance. It is best to enter any extended comments as labels in the adjacent column to the right of the commands in the worksheet.

Examples

You might put the following command at the beginning of a subroutine that enters a formatted company name.

{-- Enters the company name in Helvetica bold 24 point}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{PLAY}

{PLAY filename} plays a .WAV file.

Arguments

filename is text that specifies the name of the .WAV file to play, including the path.

Notes

See your Microsoft Windows documentation for information about sound files.

Examples

The following command plays Happy Birthday, which is stored in the file BIRTHDAY.WAV in the directory D:\WINDOWS\SOUNDS.

{PLAY "d:\windows\sounds\birthday.wav"}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SET}

{SET info-id;info-value} sets a specified Info component to a specified value.

Arguments

info-id is text that specifies the name of the Info component whose value you want to set.

info-value specifies the value to which you want to set the Info component. info-value can be a value, text, or a location, depending on the component that you are setting.

Examples

The following commands set the default number format to Currency with no decimal places.

{SET "worksheet-format";"currency"}

{SET "worksheet-format-decimals";0}

See also

Help

Info Components

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{SMARTICONS-USE}

{SMARTICONS-USE set-name} selects a set of SmartIcons to use with 1-2-3.

Arguments

set-name is text that specifies the name of the SmartIcon set to make current.

Examples

The following command selects the set of SmartIcons named Formatting.

{SMARTICONS-USE "Formatting"}

See also

Equivalent 1-2-3 command

Tools SmartIcons

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


Keystroke Equivalents

The table below lists the macro key names that correspond to the 1-2-3 function keys, pointer-movement keys, and a few keyboard keys. For explanations of what the 1-2-3 keys do, see Keyboard Index.

1-2-3 Key	Macro Instruction

~ (tilde)	{~}

{ (open brace)	{{}

} (close brace)	{}}

/ (slash) or < (less than)	/, <, or {MENU}

ALT+F6 (ZOOM)	{ZOOM}

BACKSPACE	{BACKSPACE} or {BS}

CTRL+END	{FILE}

CTRL+END CTRL+PG DN	{PREVFILE}, {PF}, or {FILE}{PS}

CTRL+END CTRL+PG UP	{NEXTFILE}, {NF}, or {FILE}{NS}

CTRL+END END	{LASTFILE}, {LF}, or {FILE}{END}

CTRL+END HOME	{FIRSTFILE},{FF}, or {FILE}{HOME}

CTRL+HOME	{FIRSTCELL} or {FC}

CTRL+LEFT	{BACKTAB} or {BIGLEFT}

CTRL+PG UP	{NEXTSHEET} or {NS}

CTRL+PG DN	{PREVSHEET} or {PS}

CTRL+RIGHT	{BIGRIGHT}

DEL	{DELETE} or {DEL}

DOWN	{DOWN} or {D}

END	{END}

END CTRL+HOME	{LASTCELL} or {LC}

ESC	{ESCAPE} or {ESC}

ESC in 1-2-3 Classic edit line	{CLEARENTRY} or {CE}

F1 (HELP)	{HELP}

F2 (EDIT)	{EDIT}

F3 (NAME)	{NAME}

F4 in Ready mode	{ANCHOR}

F4 (ABS)	{ABS}

F5 (GOTO)	{GOTO}

F6 (PANE)	{WINDOW}

F7 (QUERY)	{QUERY}

F8 (TABLE)	{TABLE}

F9 (CALC)	{CALC}

HOME	{HOME}

INS	{INSERT} or {INS}

LEFT	{LEFT} or {L}

PG DN	{PGDN}

PG UP	{PGUP}

RIGHT	{RIGHT} or {R}

SHIFT+CTRL+LEFT	{SELECT-BIGLEFT}

SHIFT+CTRL+RIGHT	{SELECT-BIGRIGHT}

SHIFT+DOWN	{SELECT-DOWN}

SHIFT+CTRL+HOME	{SELECT-FIRSTCELL}

SHIFT+HOME	{SELECT-HOME}

END SHIFT+CTRL+HOME	{SELECT-LASTCELL}

SHIFT+LEFT	{SELECT-LEFT}

SHIFT+CTRL+PG UP	{SELECT-NEXTSHEET}

SHIFT+PG DN	{SELECT-PGDN}

SHIFT+PG UP	{SELECT-PGUP}

SHIFT+CTRL+PG DN	{SELECT-PREVSHEET}

SHIFT+RIGHT	{SELECT-RIGHT}

SHIFT+UP	{SELECT-UP}

TAB	{TAB}

UP	{UP} or {U}

Note 1-2-3 does not have macro key names for the following keys: ALT+BACKSPACE (UNDO), ALT+F1 (COMPOSE), ALT+F2 (STEP), ALT+F3 (RUN), CAPS LOCK, NUM LOCK, PRINT SCREEN, SCROLL LOCK, and SHIFT. Therefore, you cannot use these keystrokes in a macro.


SmartIcons Macro Commands

SmartIcons macro commands correspond to the functions of commonly-used SmartIcons.

Icon	Corresponding macro command

{SMARTSUM} sums values in the selected or adjacent range, if you include empty cells below or to the right of the range.

{SORT-ASCENDING} sorts a range or database table in ascending order (A - Z and smallest to largest values), using the selected column as the key.

{SORT-DESCENDING} sorts a range or database table in descending order (Z - A and largest to smallest values), using the selected column as the key.

{TOGGLE-OUTLINE} adds or removes a border.

{TOGGLE-SHADOW} draws or removes an outline around a cell or range and adds or removes a drop shadow.


{ALERT}

{ALERT message;[buttons];[icon-type];[results-range];[x];[y]} displays a message box and waits for the user to choose OK or Cancel.

Arguments

message is the text of the message to appear in the box.

buttons is one of the following numbers.

buttons	Message box displays

1	Only the OK button; default if you omit the argument

2	Both the OK and Cancel buttons

icon-type is text that specifies the type of icon to display in the message box.

icon-type	Message box displays

note	Note icon; default if you omit the argument

caution	Caution icon

stop	Stop icon

results-range is the name or address of a cell where 1-2-3 stores the number of the button selected by the user (1 for OK, 0 for Cancel).

The number in the results range before 1-2-3 runs an {ALERT} command determines which button is the default button in the message box. If the results range contains 0, Cancel is the default button; otherwise, OK is the default setting.

If you omit results-range, 1-2-3 does not enter a result in the worksheet.

x is a value that specifies the horizontal position, in pixels, measured from the left side of the screen to the left side of the dialog box.

y is a value that specifies the vertical position, in pixels, measured from the top of the screen to the top of the dialog box.

If you omit x and y, the dialog box appears in the center of the screen.

Examples

Install transfers a sample worksheet file, named UIMACROS.WK4, to your default worksheet directory. This self-documented file contains examples for the macro commands that create dialog boxes and menus.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHOOSE-FILE}

{CHOOSE-FILE [file-type];results-range;[title];[x];[y]} displays a Windows common dialog box that contains a list of files and waits for the user to select one.

Arguments

file-type is text that specifies the type of files displayed in the dialog box. 1-2-3 displays the names of files in the current directory, though the user can change the path.

file-type	1-2-3 lists

worksheet	Only 1-2-3 worksheet files; default if you omit the argument

all	All the files in the directory

text	Only text files with .TXT and .PRN extensions

You can also use the wildcard characters * (asterisk) and ? (question mark) in file-type to display a list of files with similar names or extensions.

•	The * wildcard character represents any number of consecutive characters in a file name or extension. For example, to have {CHOOSE-FILE} list all files with the extension .WK4, make file-type "*.wk4".

•	The ? wildcard character represents any single character in a file name or extension. For example, to have {CHOOSE-FILE} list all files with a three-character extension that begins with .W, make file-type "*.w??".

results-range is the name or address of a cell where, if the user chooses OK, 1-2-3 stores the name of the file that the user selected. If the user chooses Cancel in the dialog box, results-range is blank.

title is text that appears in the title bar of the dialog box.

x is a value that specifies the horizontal position, in pixels, measured from the left side of the screen to the left side of the dialog box.

y is a value that specifies the vertical position, in pixels, measured from the top of the screen to the top of the dialog box.

If you omit x and y, the dialog box appears in the center of the screen.

Notes

If your macros currently contain the SmartPak command {CHOOSEFILE}, this command will continue to work correctly.

File names appear in a list box.

The macro commands that follow {CHOOSE-FILE} can perform an action based on the selected file.

Examples

Install transfers a sample worksheet file, named UIMACROS.WK4, to your default worksheet directory. This self-documented file contains examples for the macro commands that create dialog boxes and menus.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHOOSE-ITEM}

{CHOOSE-ITEM list-range;results-range;[prompt];[title];[x];[y]} displays a dialog box that contains a list of data items; waits for the user to select one and then choose OK or Cancel; and enters the index number for the user's choice in the worksheet.

Arguments

list-range is the name or address of a single-column range that contains the items displayed in the dialog box. The items must appear one per cell in the same order that you want them to appear in the list. End the list of items in list-range with a blank cell or a cell that contains the value ERR or NA.

results-range is the name or address of a cell where 1-2-3 stores the index number of the item selected by the user. The first item in the list is numbered 0, the second item is numbered 1, and so on. If the user chooses Cancel in the dialog box, 1-2-3 leaves results-range blank.

prompt is text that appears at the top of the dialog box. Use this prompt to tell users what happens when they select an item.

title is text that appears in the title bar of the dialog box.

x is a value that specifies the horizontal position, in pixels, measured from the left side of the screen to the left side of the dialog box.

y is a value that specifies the vertical position, in pixels, measured from the top of the screen to the top of the dialog box.

If you omit x and y, the dialog box appears in the center of the screen.

Notes

If your macros currently contain the SmartPak command {CHOOSEITEM}, this command will continue to work correctly.

Items appear in a list box, and the first item in the list box is highlighted.

Use {CHOOSE-ITEM} to let the user select an item other than a file. The macro commands that follow {CHOOSE-ITEM} can perform an action based on the selected item.

Examples

Install transfers a sample worksheet file, named UIMACROS.WK4, to your default worksheet directory. This self-documented file contains examples for the macro commands that create dialog boxes and menus.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHOOSE-MANY}

{CHOOSE-MANY choices-range;results-range;[prompt];[title];[x];[y]} displays a dialog box and waits for the user to select one or more check boxes and then choose OK or Cancel.

Arguments

choices-range is the name or address of a range that contains descriptions of the check boxes. This range requires three rows and a column for each check box. You can create up to eight check boxes.

•	Row 1: Enter the label that you want 1-2-3 to display to the right of the corresponding check box in the dialog box.

•	Row 2: Specify the initial state that you want for the corresponding check box when 1-2-3 displays the dialog box. A value of 0 means off; a value of 1 means on; a value of NA means dimmed.

•	Row 3: 1-2-3 will enter the state of a check box after the user chooses OK. A value of 0 means off; a value of 1 means on.

results-range is the name or address of a cell where 1-2-3 stores a 0 if the user chooses Cancel in the dialog box or 1 if the user chooses OK.

prompt is text that appears at the top of the dialog box. Use this prompt to tell users what happens when they select the check boxes.

title is text that appears in the title bar of the dialog box.

x is a value that specifies the horizontal position, in pixels, measured from the left side of the screen to the left side of the dialog box.

y is a value that specifies the vertical position, in pixels, measured from the top of the screen to the top of the dialog box.

If you omit x and y, the dialog box appears in the center of the screen.

Notes

If your macros currently contain the SmartPak command {CHOOSEMANY}, this command will continue to work correctly.

{CHOOSE-MANY} stores the user's choices in the third row of choices-range. Unlike {CHOOSE-ONE}, however, {CHOOSE-MANY} doesn't perform any action based on what the user chose. Use {CHOOSE-MANY} with subsequent macro commands that test the contents of results-range and the third row of choices-range, and then perform actions based on what the user chooses.

If the user chooses	1-2-3 stores

OK	1 in results-range and 0 or 1 in each cell in the third row in choices-range

Cancel	0 in results-range and doesn't change the third row in choices-range; choosing Cancel removes the {CHOOSE-MANY} dialog box but doesn't stop the macro.

Examples

Install transfers a sample worksheet file, named UIMACROS.WK4, to your default worksheet directory. This self-documented file contains examples for the macro commands that create dialog boxes and menus.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{CHOOSE-ONE}

{CHOOSE-ONE choices-range; results-range; [prompt];[title];[x];[y]} displays a dialog box and waits for the user to select an option and then choose OK or Cancel; then runs the macro associated with the option.

Arguments

choices-range is the name or address of a range that contains descriptions of the option buttons. This range requires at least three rows and a column for each button. You can create up to eight buttons.

•	Row 1: Enter the label that you want 1-2-3 to display to the right of the corresponding button in the dialog box.

•	Row 2: Specify the initial state that you want for the corresponding button when 1-2-3 displays the dialog box. A value of 0 means off; a value of 1 means on; a value of NA means dimmed.

Only one button can be selected when 1-2-3 displays the dialog box, so only one cell in the second row can contain a 1. If no cell contains 1, the first button will be selected. If more than one cell contains 1, only the button corresponding to the first cell that contains 1 will be selected. This row doesn't change after 1-2-3 performs {CHOOSE-ONE}.

•	Row 3: Enter macro commands (up to 511 characters per cell) or the name of the subroutine that you want 1-2-3 to perform when the corresponding button in the dialog box is selected and the user chooses OK. Starting at the third row, you can use as many subsequent rows as you need for macro commands.

results-range is the address or range name of a cell where 1-2-3 enters a 0 if the user chooses Cancel or 1 if the user chooses OK.

prompt is text that appears at the top of the dialog box. Use this prompt to tell users what happens when they select a button.

title is text that appears in the title bar of the dialog box.

x is a value that specifies the horizontal position, in pixels, measured from the left side of the screen to the left side of the dialog box.

y is a value that specifies the vertical position, in pixels, measured from the top of the screen to the top of the dialog box.

If you omit x and y, the dialog box appears in the center of the screen.

Notes

If your macros currently contain the SmartPak command {CHOOSEONE}, this command will continue to work correctly.

Use the {CHOOSE-ONE} command to prompt the user to select a radio button so that 1-2-3 will perform the macro commands associated with the button. Unlike the {CHOOSE-MANY} command, {CHOOSE-ONE} doesn't enter the user's choice in the worksheet, but instead performs an action based on the user's choice.

If the user chooses	1-2-3 stores

OK	1 in results-range and performs the macro commands associated with the selected button.

Cancel	0 in results-range and doesn't perform the macro commands associated with the selected button; choosing Cancel removes the {CHOOSE-ONE} dialog box but doesn't stop the macro.

Examples

Install transfers a sample worksheet file, named UIMACROS.WK4, to your default worksheet directory. This self-documented file contains examples for the macro commands that create dialog boxes and menus.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{DIALOG}

{DIALOG range} displays a custom dialog box created with the Lotus Dialog Editor.

Arguments

range is the name or address of the first cell in the dialog-description table.

Examples

Install transfers a sample worksheet file, named UIMACROS.WK4, to your default worksheet directory. This self-documented file contains examples for the macro commands that create dialog boxes and menus.

See also

Help

Macros

Using a Dialog Box Created with the Lotus Dialog Editor

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{DIALOG?}

{DIALOG? name} displays a 1-2-3 dialog box, and waits for you to choose OK or press ENTER.

Arguments

name is text that specifies the name of the dialog box to display.

The name of a dialog box appears in the title bar when you choose the command that displays the dialog box.

• 	If the dialog-box name contains more than one word, substitute hyphens for spaces between the words. For example, to display the Style Named Style dialog box, use the command

{DIALOG? "named-style"}.

• 	If the dialog-box name contains an & (ampersand), substitute the word and. For example, to display the Style Font & Attributes dialog box, use the command

{DIALOG? "font-and-attributes"}

• 	Range Sort and Query Sort both display dialog boxes with Sort in the title bar.

To display the Range Sort dialog box, use the command {DIALOG? "range-sort"}.

To display the Query Sort dialog box, use the command {DIALOG? "query-sort"}.

• 	Range Name, Chart Name, and Query Name all display dialog boxes with Name in the title bar.

To display the Range Name dialog box, use the command {DIALOG? "range-name"}.

To display the Chart Name dialog box, use the command {DIALOG? "chart-name"}.

To display the Query Name dialog box, use the command {DIALOG? "query-name"}.

Notes

You cannot display a dialog box that does not apply to the current selection.

For example, the following macro causes an error, because a chart is selected, and the Range Fill command applies only to ranges.

{SELECT "Chart 1";;"chart"}

{DIALOG? "fill"}

Examples

The following command displays the File Save As dialog box and waits for the user to enter information and choose OK.

{DIALOG? "save-as"}

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{GET-FORMULA}, {GET-LABEL}, {GET-NUMBER}, {GET-RANGE}
{GET-FORMULA}
{GET-LABEL}
{GET-NUMBER}
{GET-RANGE}

Display a dialog box that contains a text box and wait until the user chooses Cancel or OK. When the user chooses Cancel or OK, 1-2-3 enters the data from the text box in the worksheet and resumes running the macro at the next command.

The user can enter up to 511 characters in the text box.

Arguments

prompt is text that appears at the top of the dialog box. If you omit prompt, 1-2-3 does not display a prompt.

result is the name or address of a range where you want 1-2-3 to store what the user entered. If you specify a multiple-cell range for result, 1-2-3 enters the data in the top left cell of the range.

default is what appears by default in the text box when 1-2-3 displays the dialog box. If you omit default, the text box is blank.

title is text that appears in the title bar of the dialog box. If you omit title, 1-2-3 does not display anything in the title bar.

x is a value that specifies the horizontal position, in pixels, measured from the left side of the screen to the left side of the dialog box.

y is a value that specifies the vertical position, in pixels, measured from the top of the screen to the top of the dialog box.

If you omit x and y, the dialog box appears in the center of the screen.

{GET-FORMULA [prompt];result;[default];[title];[x];[y]}

Lets the user enter a formula. {GET-FORMULA} enters the formula in the worksheet rather than evaluating the formula and entering the result as a number, like {GET-NUMBER}.

•	If the user chooses OK after entering a formula or a reference to a cell that contains a number or formula, 1-2-3 enters the formula in result.

•	If the user enters nothing or something besides a number, formula, or reference to a cell that contains a number or formula, and then chooses OK, 1-2-3 enters the value ERR in result.

•	If the user chooses Cancel, 1-2-3 makes result blank and continues the macro.

{GET-LABEL [prompt];result;[default];[title];[x];[y]}

Lets the user enter anything that you want 1-2-3 to store in the worksheet as a label.

•	If the user types data in the text box and chooses OK, 1-2-3 enters the contents of the text box as a left-aligned label in result.

•	If the user types nothing in the text box and chooses OK when the text box is blank, 1-2-3 enters the ' (apostrophe) label-prefix character in result.

•	If the user chooses Cancel, 1-2-3 makes result blank and continues the macro.

{GET-NUMBER [prompt];result;[default];[title];[x];[y]}

Lets the user enter a number or a numeric formula. {GET-NUMBER} enters the number in the worksheet or evaluates the formula and enters the result as a number.

•	If the user chooses OK after typing a number, numeric formula, or reference to a cell that contains a number or numeric formula, 1-2-3 evaluates the entry and stores the result in result.

•	If the user types nothing and chooses OK when the text box is blank, 1-2-3 enters the value ERR in result.

•	If the user chooses OK after typing something besides a number, numeric formula, or reference to a cell that contains a number or numeric formula, 1-2-3 enters the value ERR in result.

•	If the user chooses Cancel, 1-2-3 makes result blank and continues the macro.

{GET-RANGE [prompt];result;[default];[title];[x];[y]}

Lets the user enter a range name or address. {GET-RANGE} enters the name or address in the worksheet or as a left-aligned label.

•	If the user types a range name or address in the text box and chooses OK, 1-2-3 enters the contents of the text box as a left-aligned label in result.

•	If the user types nothing in the text box and chooses OK when the text box is blank, 1-2-3 enters the ' (apostrophe) label-prefix character in result.

•	If the user chooses Cancel, 1-2-3 makes result blank and continues the macro.

SmartPak commands

If your macros currently contain the SmartPak commands {WGETFORMULA}, {WGETLABEL}, and {WGETNUMBER}, these commands produce the same results as {GET-FORMULA}, {GET-LABEL}, and {GET-NUMBER}.

1-2-3 for DOS commands

If your macros currently contain the 1-2-3 for DOS command {GETLABEL}, this command produces the same results as {GET-LABEL}, except when the user chooses Cancel. Instead of making result blank, 1-2-3 enters the ' (apostrophe) label-prefix character in result.

If your macros currently contain the 1-2-3 for DOS command {GETNUMBER}, this command produces the same results as {GET-NUMBER}, except when the user chooses Cancel. Instead of making result blank, 1-2-3 enters the value ERR in result.

Examples

Install transfers a sample worksheet file, named UIMACROS.WK4, to your default worksheet directory. This self-documented file contains examples for the macro commands that create dialog boxes and menus.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{MENU-COMMAND-ADD} and {MENU-COMMAND-REMOVE}
{MENU-COMMAND-ADD}
{MENU-COMMAND-REMOVE}

{MENU-COMMAND-ADD menu-description-range;menu-index;command-index} adds a command to a pull-down menu.

{MENU-COMMAND-REMOVE menu-index;command-index} removes a command from a pull-down menu.

Arguments

menu-description-range is the name or address of a range that contains a description of the command to add. This range requires five or more rows and one column.

•	Row 1: Enter the name of the command to add.

•	Row 2: Enter a description of the command in the cell above. 1-2-3 displays the description in the title bar when you highlight the command.

•	Row 3: Indicate whether the command two cells above should appear dimmed, checked, or neither dimmed nor checked. A value of NA makes the command appear dimmed; 1 makes it appear checked; a blank cell makes the command appear neither dimmed nor checked.

•	Row 4: Leave it blank.

•	Row 5: Enter macro commands (up to 511 characters per cell) or the range name of a macro that you want 1-2-3 to perform when the user chooses the corresponding command. Starting at the fifth row, you can use as many subsequent rows as you need for macro commands.

menu-index specifies which menu to add the command to or remove the command from. menu-index is an integer that corresponds to the position of a menu in the menu bar. The first menu has a menu-index number of 1, the second 2, and so on.

command-index specifies where in the pull-down menu to add or remove the command. command-index is an integer that corresponds to the position of a command in the pull-down menu. The first command has a menu-index number of 1, the second 2, and so on.

Notes

After using {MENU-COMMAND-ADD} or {MENU-COMMAND-REMOVE}, you can use {MENU-RESET} to restore display of the default menu bar.

Examples

Install transfers a sample worksheet file, named UIMACROS.WK4, to your default worksheet directory. This self-documented file contains examples for the macro commands that create dialog boxes and menus.

See also

Help

Macros

Working with Custom Menus

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{MENU-COMMAND-DISABLE} and {MENU-COMMAND-ENABLE}
{MENU-COMMAND-DISABLE}
{MENU-COMMAND-ENABLE}

{MENU-COMMAND-DISABLE menu-index;command-index} disables a command in a custom menu. Disabled commands appear dimmed.

{MENU-COMMAND-ENABLE menu-index;command-index} enables a command disabled with {MENU-COMMAND-DISABLE}.

Arguments

menu-index specifies which menu to add the command to or remove the command from. menu-index is an integer that corresponds to the position of a menu in the menu bar. The first menu has a menu-index number of 1, the second 2, and so on.

command-index specifies where in the pull-down menu to enable or disable the command. command-index is an integer that corresponds to the position of a command in the pull-down menu. The first command has a menu-index number of 1, the second 2, and so on.

Notes

{MENU-COMMAND-ENABLE} and {MENU-COMMAND-DISABLE} work only with custom menus; you cannot disable commands on the default menu bar.

Examples

Install transfers a sample worksheet file, named UIMACROS.WK4, to your default worksheet directory. This self-documented file contains examples for the macro commands that create dialog boxes and menus.

See also

Help

Macros

Working with Custom Menus

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{MENUCREATE} and {MENUINSERT}
{MENU-CREATE}
{MENU-INSERT}

{MENU-CREATE menu-description-range} replaces the current 1-2-3 menu bar with a customized menu bar.

{MENU-INSERT menu-description-range;[menu-index]} adds a custom pull-down menu to the default 1-2-3 menu bar.

Arguments

For {MENU-CREATE}, menu-description-range is the name or address of a range that contains a description of each command name in the menu bar and points to a description of each command in the corresponding pull-down menu. This range requires four rows and can be up to ten columns wide (nine commands and a blank cell to end the list).

For {MENU-INSERT}, menu-description-range is the name or address of a range that contains a description of the command name to insert in the default 1-2-3 menu bar and points to a description of each command in the corresponding pull-down menu. This range requires four rows and one column.

•	Row 1: For {MENU-CREATE}, enter the names of the commands in the menu bar. The leftmost cell in this row contains the name of the leftmost command in the menu bar, and succeeding cells to the right contain the names of succeeding commands in the menu bar. You can create up to nine commands. End the row of commands with a blank cell or a cell that contains the value ERR or NA.

For {MENU-INSERT}, enter the name of a command to insert.

•	Row 2: Enter a description of the command in the cell above. 1-2-3 displays the description in the title bar when you highlight the command.

•	Row 3: Indicate whether every command in the command two cells above's pull-down menu should appear dimmed or not. A value of NA makes these items appear dimmed; a blank cell makes these items available.

•	Row 4: Enter the range name or address of a pull-down-menu-description range for the corresponding command.

Pull-down-Menu-Description Range

A pull-down-menu-description range is the name or address of a range that contains a description of each command in a pull-down menu. This range requires five or more rows and can be up to 25 columns wide (24 commands and a blank cell to end the list).

•	Row 1: Enter the name of a command in the pull-down menu. The leftmost cell in this row contains the name of the first command in the pull-down, and succeeding cells to the right contain the names of succeeding commands in the menu. End the list of menu commands with a blank cell or a cell that contains the value ERR or NA.

•	Row 2: Enter a description of the command in the cell above. 1-2-3 displays the description in the title bar when you highlight the command.

•	Row 3: Indicate whether the command two cells above should appear dimmed, checked, or neither dimmed nor checked. A value of NA makes the command appear dimmed; 1 makes it appear checked; a blank cell makes the command appear neither dimmed nor checked.

•	Row 4: Leave it blank.

•	Row 5: Enter macro commands (up to 511 characters per cell) or the range name of a macro that you want 1-2-3 to perform when the user chooses the corresponding command. Starting at the fifth row, you can use as many subsequent rows as you need for macro commands.

menu-index specifies where to add the pull-down menu. menu-index is an integer that corresponds to the position of a menu in the menu bar. The first menu has a menu-index number of 1, the second 2, and so on.

If you omit menu index, 1-2-3 inserts the pull-down menu before the last menu in the menu bar. In the default 1-2-3 menu, 1-2-3 inserts the pull-down before the Window menu.

Notes

If your macros currently contain the SmartPak commands {MENUCREATE} and {MENUINSERT}, these commands will continue to work correctly.

After using {MENU-CREATE} or {MENU-INSERT}, you can use {MENU-RESET} to restore display of the default menu bar.

Examples

Install transfers a sample worksheet file, named UIMACROS.WK4, to your default worksheet directory. This self-documented file contains examples for the macro commands that create dialog boxes and menus.

See also

Help

Macros

Working with Custom Menus

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{MENU-RESET}

{MENU-RESET} displays the default 1-2-3 menu bar.

Notes

If your macros currently contain the SmartPak command {MENURESET}, this command will continue to work correctly.

After using {MENU-CREATE}, {MENU-INSERT}, {MENU-COMMAND-ADD}, or {MENU-COMMAND-REMOVE}, you can use {MENU-RESET} to restore display of the default menu bar.

Examples

Install transfers a sample worksheet file, named UIMACROS.WK4, to your default worksheet directory. This self-documented file contains examples for the macro commands that create dialog boxes and menus.

See also

Help

Macros

Working with Custom Menus

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


Working with Custom Menus

These tips will help you create custom menus for 1-2-3.

Creating keyboard shortcuts

When you enter command names, & (ampersand) followed by a character creates a keyboard shortcut for a command. The letter that follows the & (ampersand) appears underlined; the user can choose this command from the keyboard by pressing ALT plus the underlined letter. For example, if you enter First&Quarter, 1-2-3 displays FirstQuarter; the user can press ALT+Q to select the command. To display & (ampersand) in the command name, enter && (two ampersands). For example, to display B&W, enter B&&W.

Dimming command names

To make a command name appear dimmed, follow the command name with ( (open parentheses). For example, if you enter First Quarter(, the menu command First Quarter appears dimmed in the cutom menu.

Restoring the default 1-2-3 menu bar

After executing a command that changes or updates the menu bar, 1-2-3 performs the next command in the macro. The custom or updated menu bar remains displayed until the next {MENU} command changes the menu bar, or until a {MENU-RESET} command restores the 1-2-3 default menu bar. Closing the file that contains the macro does not restore the 1-2-3 default menu bar.

Troubleshooting

While a custom menu bar or custom pull-down menu is displayed, moving or erasing the pull-down-menu-description range or renaming the file that contains the range causes the custom commands to stop working. If your custom menus stop working, follow these steps to restore the 1-2-3 default menu:

1.	Move the cell pointer to a blank cell, type {menu-reset}, and press ENTER.

2.	Press ALT+F3.

The Tools Macro Run dialog box appears.

3.	Press ENTER.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{VIEW-ZOOM}

{VIEW-ZOOM how} decreases or increases the display size of cells, or restores their default display size.

Arguments

how is text that specifies how to zoom.

how	1-2-3 does this to the display size of cells

in	Increases by 10%, to as large as 400% of the normal size

out	Decreases by 10% to as small as 25% of the normal size

custom	Resets to the default display size

Notes

To set the default display size in a macro, use the Window-Custom-Zoom Info component.

Examples

The following commands decrease the display size of cells by a total of 20%.

{VIEW-ZOOM "out"}

{VIEW-ZOOM "out"}

See also

Equivalent 1-2-3 commands

View Zoom In

View Zoom Out

View Custom

Help

Macros

{SET}

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{WORKSHEET-TITLES}

{WORKSHEET-TITLES direction} freezes (or unfreezes) columns along the top of the worksheet, rows along the left edge of the worksheet, or both.

Arguments

direction is text that specifies which titles to freeze.

direction	1-2-3 does the following

horizontal	Freezes all rows above the current cell

vertical	Freezes all columns to the left of the current cell

both	Freezes all rows above and all columns to the left of the current cell

none	Unfreezes all titles

Examples

The following commands move the cell pointer to A:B3 and then freeze column A and rows 1 and 2.

{EDIT-GOTO A:B3}

{WORKSHEET-TITLES "both"}

See also

Equivalent 1-2-3 commands

View Freeze Titles

View Clear Titles

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{WINDOW-ACTIVATE}

{WINDOW-ACTIVATE [window-name];[reserved];[pane]} makes a window the active window.

Arguments

window-name is text that specifies the name of an open window, as it appears in the title bar.

You do not need to include the file extension or path as part of window-name unless files with the same name but different extensions are open (for example, SALES.WK3 and SALES.WK4).

If you specify a window that is not open, {WINDOW-ACTIVATE} returns an error.

reserved is reserved for use with 1-2-3 for Macintosh. Omit reserved by including an extra argument separator.

pane is an offset number that specifies the pane you want to make current. If you omit pane, 1-2-3 uses the current pane. If pane does not exist in the window, {WINDOW-ACTIVATE} returns an error.

Notes

The standard worksheet has one window and one pane, numbered 0. A perspective window can have up to three panes, ordered from front to back, numbered 0, 1, and 2. In a split window, panes are ordered from left to right or top to bottom.

If your macro contains {WINDOW-SELECT} commands, they will continue to work.

Examples

The following command activates the worksheet window EXPENSES.WK4.

{WINDOW-ACTIVATE EXPENSES.WK4}

Suppose that the file FINANCES.WK4 is split into two horizontal panes. The following command activates the file FINANCES.WK4 and makes the lower pane current.

{WINDOW-ACTIVATE FINANCES.WK4;;1}

See also

Equivalent 1-2-3 command

Window (Window Name)

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


{WINDOW-ARRANGE}

{WINDOW-ARRANGE how} sizes open windows (Worksheets and Transcript) and either places them side by side or arranges them one on top of the other, with just the title bars showing.

Arguments

how is text that specifies how you want to arrange the open windows.

how	1-2-3 does this to open windows

stack	Arranges them one on top of the other, with just the title bars showing, and with the active window in front

vertical-tile	Places them side by side, with the active window in the top left corner of the work space.

Examples

The following commands open three files and arrange them in a vertical-tile arrangement.

{FILE-OPEN d:\123w\finance\april.wk4}

{FILE-OPEN d:\123w\finance\may.wk4}

{FILE-OPEN d:\123w\finance\june.wk4}

{WINDOW-ARRANGE "vertical-tile"}

See also

Equivalent 1-2-3 commands

Window Cascade

Window Tile

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your  Work"


Using a Dialog Box Created with the Lotus Dialog Editor

After you create a dialog box in the Dialog Editor and copy it to the Clipboard, you paste it in a worksheet file so that you can use it in a macro.

In the file, the dialog box takes the form of a dialog-description table. This table contains 11 columns and as many rows as there are dialog-box controls, plus two rows. The two rows contain labels that mark the beginning and end of the table.

To see a sample dialog-description table, open the file UIMACROS.WK4 and click the worksheet tab {Dialog}, or press CTRL+PG UP ten times to go to worksheet K.

Columns of the dialog-description table

1-2-3 uses the first nine columns of the table to store information about the contents and layout of the dialog-box. The table requires two more columns:

•	Input column: The tenth column of the table. You enter information about the initial state of a control in the corresponding cell of this column.

•	Output column: The eleventh column of the table. 1-2-3 enters information in this column that is determined by a user's actions in a dialog box. You can use this information in a macro.

Note 1-2-3 requires that the "twelfth" column--that is, the first column to the right of the output column--be blank. This column is not, however, part of the dialog-description table.

For information about valid data for the input column and about what 1-2-3 enters in the output column, select any of the following:

Push Button

Default Push Button

Bitmap Button

Radio Button

Check Box

Edit Box

List Box

Combo Box

Group Box

Static Text

Static Bitmap

Note To enter the value NA in a cell of the input column, type @NA.

Related SmartIcons

Starts the Lotus Dialog Editor

See also

{DIALOG}

Macros


Push Button, Default Push Button, Bitmap Button

Input column

To make the button appear dimmed, enter the value NA.

Output column

Depending on which button the user presses to close the dialog box, 1-2-3 enters one of the following values in the output cell.

When a user chooses	1-2-3 enters

A default push button	1

The Cancel button	0

Any other push button	Button ID number


Radio Button

Input column

Enter a value to specify the initial state of the button when 1-2-3 displays the dialog box.

0	Button is off.

1	Button is on.

NA	Button is dimmed.

Note Only one radio button's input cell can contain 1. If no cell contains 1, the first button is selected. If more than one cell contains 1, only the button corresponding to the first cell that contains 1 is selected.

Output column

When a user selects a radio button, 1-2-3 enters the value 1 in the output cell. If a radio button remains unselected, 1-2-3 enters the value 0.


Check Box

Input column

Enter a value to specify the initial state of the check box when 1-2-3 displays the dialog box.

0	Box is unselected.

1	Box is selected; an X appears in the box.

NA	Box is dimmed.

Output column

When a user selects a check box, 1-2-3 enters the value 1 in the output cell. If a check box remains unselected, 1-2-3 enters the value 0.


Edit Box

Input column

To display an edit box with default text in it, enter the data as a label in the input cell. If the input cell contains the value NA, the box is dimmed.

Output column

When the user closes the dialog box, 1-2-3 enters the text in the edit box as a left-aligned label in the output cell.


List Box

Input column

Enter the address or name of a single-column range that contains the items to appear in the list box. List the items, one per cell, in the order that you want them to appear in the list box. End the list of items with a blank cell or a cell that contains the value ERR or NA. Be sure the cells to the right of the range are blank.

If the input cell contains the value NA, the box is dimmed.

Output column

1-2-3 enters the index number of the item selected by the user in the output cell. The first item in the list is numbered 0, the second item is numbered 1, and so on.

If the user does not choose an item, 1-2-3 enters the value -1 in the output cell.

If multiple selections are possible in the list box, 1-2-3 enters the total number of selected items in the output cell. In the cells to the right of the range containing the list-box items, 1-2-3 enters the value 1 if the item was selected and the value 0 if the item was not selected


Static Text and Static Bitmaps

Input column

To make the static text or bitmap appear dimmed, enter the value NA.

Output column

1-2-3 leaves the static control's output cell blank.


Combo Box

Input column

Enter the address or name of a single-column range that contains the items to appear in the list box. List the items, one per cell, in the order that you want them to appear in the list box. End the list of items with a blank cell or a cell that contains the value ERR or NA.

If the input cell contains the value NA, the box is dimmed.

Output column

The edit portion of a combo box is always blank when the dialog box first appears.

When the user closes the dialog box, 1-2-3 enters the text in the edit portion of the combo box as a left-aligned label in the output cell.


Group Box

Input column

To make a group box and all controls inside it appear dimmed, enter the value NA.

Output column

1-2-3 leaves the output cell blank.


Printing Sections of Macros Help

Scroll through the list below and then, to print a section of Macros Help, just click it.

A book containing detailed descriptions of all 1-2-3 @functions and macro commands is now available from Lotus. Click "@Functions and Macros Book Coupon" to print the coupon for ordering this book.

 @Functions and Macros Book Coupon

 Chart Macro Commands

 Data Manipulation Macro Commands

 Database Macro Commands

 DDE and OLE Macro Commands

 Edit Macro Commands

 File Macro Commands

 Flow of Control Macro Commands

 Navigation Macro Commands

 Range Macro Commands

 SmartIcons Macro Commands

 Solver Macro Commands

 Style Macro Commands

 Tools Macro Commands

 User Environment Macro Commands

 Version Manager Macro Commands

 Window Macro Commands

 Working with Text Files

 Keystroke Equivalents


{REGISTER} and {UNREGISTER}

{REGISTER module-name;procedure-name;alias-name;return-type;arg-count;arg-types} registers a procedure in a Dynamic Link Library (DLL) as an add-in @function, and loads the DLL into memory.

{UNREGISTER alias-name} unregisters the procedure that corresponds to alias-name.

Arguments

module-name is text that specifies the name of the DLL that contains the procedure you want to register.

procedure-name is text that specifies the name of the procedure contained in module-name.

alias-name is text that specifies the @function name you want to give the registered procedure. Do not include the @ symbol in alias-name.

return-type is text that specifies a letter code for the type of value returned from the registered function. return-type is a letter from the table below.

arg-count is an integer that specifies the number of arguments for the @function.

arg-types is text that specifies the type of value accepted for each argument for the @function. arg-types contains letters from the table below.

For example, "AJF" indicates that the values for the first, second, and third @function arguments are Boolean, pointer to a 2-byte integer, and a long LMBCS string pointer.

Note arg-types must contain arg-count number of letters.

return-type or arg-types	C declaration	Type of value
A	Short int	Logical or Boolean
B	Short int	Signed 2-byte integer
C	Long int	Long 4-byte integer
D	Double	IEEE 8-byte floating-point
E	Char*	ANSI NULL terminated character string; maximum string length is 255 characters
F	Char*	Long LMBCS string pointer
G	Unsigned char*	Byte-counted string pointer. The first byte contains the length of the string. The maximum string length is 255 characters
H	Double*	Pointer to IEEE 8-byte floating-point
I	Long*	Pointer to a 4-byte integer
J	Short int*	Pointer to a 2-byte integer
K	Short int*	Pointer to a 2-byte logical
L	Char*	ANSI NULL terminated character string; Maximum string length is 255 characters. Modified in place.
M	Char*	Byte-counted string pointer. The first byte contains the length of the string. The maximum string length is 255 characters. Modified in place.
Notes

Add-in @functions do not appear in the @Functions list box when you click the @function selector.

Examples

The following commands register the add-in @function @GETSTR, which retrieves entries from the LOTUS.INI file. The {LET} commands enter the results of the @GETSTR formulas in various named ranges in a worksheet.

{LET Path;+@INFO("windir")&"system\krnl386.exe"}

{REGISTER Path;"GetPrivateProfileString";"Getstr";"L";6;"EEELBE"}

{LET Def_Name;@GETSTR(IniSect;"Name";"name";"";256;"lotus.ini")}

{LET Def_Title;@GETSTR(IniSect;"Title";"title";"";256;"lotus.ini")}

{LET Def_Company;@GETSTR(IniSect;"Company";"company";"";256;"lotus.ini")}

{UNREGISTER GETSTR}

See also

Help

Macros

Add-in @Functions

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{MODELESS-DISPLAY} and {MODELESS-DISMISS}

{MODELESS-DISPLAY message;[title];[on-top];[x];[y]} displays a modeless dialog box until 1-2-3 reaches another {MODELESS-DISPLAY} command, a {MODELESS-DISMISS} command, or the end of the macro.

{MODELESS-DISMISS} closes the open modeless dialog box. If no modeless dialog box is open, {MODELESS-DISMISS} has no effect.

Arguments

message is text that appears in the dialog box. 1-2-3 can display a message that is up to 512 single-byte characters long.

title is text that appears in the title bar of the dialog box. 1-2-3 can display a title that is up to 64 single-byte characters long. If you omit title, 1-2-3 does not display anything in the title bar.

on-top is a yes/no argument that specifies whether the dialog box remains in the foreground even when it is not active. If you omit on-top, the dialog box does not remain in the foreground.

x is a value that specifies the horizontal position, in pixels, measured from the left side of the screen to the left side of the dialog box.

y is a value that specifies the vertical position, in pixels, measured from the top of the screen to the top of the dialog box.

If you omit x and y, the dialog box appears in the center of the screen.

Examples

Install transfers a sample worksheet file, named UIMACROS.WK4, to your sample files directory. This self-documented file contains examples for the macro commands that create dialog boxes and menus.

See also

Help

Macros

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{SCENARIO-REMOVE-VERSION}

{SCENARIO-REMOVE-VERSION scenario-name;[scenario-creator];version-range} removes a version from a scenario.

Arguments

scenario-name is text that specifies the name of the scenario. 1-2-3 is case-sensitive for scenario-name.

scenario-creator is text that specifies the name of the user who created the scenario. If you omit scenario-creator, 1-2-3 uses the most recently created scenario specified by scenario-name.

version-range is the name of range that contains the version to remove from the scenario. version-range must be an existing named range in the current file.

Notes

The scenario cannot be protected or hidden.

The version in version-range cannot be hidden.

Examples

The following command removes the version in the range CASSETTES from a scenario Regional Sales that was created by Isabella Martinez.

{SCENARIO-REMOVE-VERSION "Regional Sales";"Isabella Martinez";cassettes}

See also

Equivalent Version Manager button

Displays the Scenario Info dialog box, letting you remove versions from a scenario

Help

Macros

Tutorial

Lesson 8, "Using Version Manager"

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{SCENARIO-INFO}

{SCENARIO-INFO scenario-name;[scenario-creator];[share];[comment]} lets you modify the comment and sharing options for a scenario.

Arguments

scenario-name is text that specifies the name of the scenario. 1-2-3 is case-sensitive for scenario-name.

scenario-creator is text that specifies the name of the user who created the scenario. If you omit scenario-creator, 1-2-3 uses the most recently created scenario specified by scenario-name.

share is text that specifies the sharing option for the scenario. If you omit share, the sharing option for the scenario remains unchanged.

share	1-2-3 does the following

unprotected	Applies no protection to the scenario

protected	Prevents changes to the scenario

hidden	Hides and prevents changes to the scenario

comment is text that specifies a comment about the scenario. 1-2-3 replaces the current comment about the scenario with comment. If you omit comment, 1-2-3 leaves the current comment unchanged.

Examples

The following command prevents changes to a scenario named Optimal that was created by Isabella Martinez and changes the comment to read "Final updates incorporated".

{SCENARIO-INFO "Optimal";"Isabella Martinez";"protected";"Final updates incorporated"}

See also

Equivalent Version Manager button

Displays the Scenario Info dialog box

Help

Macros

Tutorial

Lesson 8, "Using Version Manager"

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{VERSION-INDEX-COPY}

{VERSION-INDEX-COPY} copies the information in the Index to the Clipboard.

Notes

Use {EDIT-PASTE} to paste the Index information to a worksheet.

The Version Manager Index must be open before you use {VERSION-INDEX-COPY}. You can open the Version Manager Index with {RANGE-VERSION? "index"}.

Examples

The following commands add two versions to a scenario and copy the updated information in the Version Manager Index to the Clipboard. You could then paste the information into another Windows application.

{SCENARIO-ADD-VERSION "High Net Income";;income;"High Income"}

{SCENARIO-ADD-VERSION "High Net Income";;expenses;"Low Expenses"}

{RANGE-VERSION?}

{VERSION-INDEX-COPY}

See also

Equivalent Version Manager button

Copies the information in the Index to the Clipboard.

Help

Macros

Tutorial

Lesson 8, "Using Version Manager"

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{VERSION-REPORT}

{VERSION-REPORT version-range;[formulas-range];[include-data];[include-audit];[arrange-data];version1;[version2];...;[version10]} creates reports showing selected versions and their effect on the outcome of a formula.

Arguments

version-range is the name of range that contains the versions for which you want a report. version-range must be an existing named range in the current file.

formulas-range lets you include the effect of the selected versions on formulas in the worksheet. formulas-range is the name or address of a range in the current file that contains formulas. If you omit formulas-range, 1-2-3 does not include the effect of the selected versions on formulas.

include-data is a yes/no argument that specifies whether to include the data for the selected versions. If you omit include-data, 1-2-3 includes the data.

include-audit is a yes/no argument that specifies whether to include the names of the users who created and last modified the version and the date and time the version was created and last modified. If you omit include-audit, 1-2-3 includes this information.

arrange-data is text that specifies how 1-2-3 arranges the data in the report.

arrange-data	1-2-3 arranges the data

columns	By column; default if you omit the argument

rows	By row

version1 is text that specifies the name of a version to include in the report.

version2;...;version10 let you specify the names of up to 9 additional versions to include in the report.

1-2-3 is case-sensitive for version names.

Notes

{VERSION-REPORT} creates a version report in a new file. 1-2-3 gives the file a unique name beginning with REPORT (for example, REPORT01.WK4).

Examples

The following command creates a report on the versions named Slow Growth and Moderate Growth for the range COG. The report shows the effects of the two versions on the formula in cell B:A5, and includes the data and audit information for the versions. Data in the report is arranged by row.

{VERSION-REPORT COG;B:A5;"yes";"yes";"rows";"Slow Growth";"Moderate Growth"}

See also

Equivalent Version Manager button

Creates reports showing selected versions and their effect on the outcome of a formula.

Help

Macros

Tutorial

Lesson 8, "Using Version Manager"

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{SEND-RANGE}

{SEND-RANGE range;to;[subject];[body];[return-receipt];[route];[priority];[return-to-originator];[properties]} sends a range of worksheet data to other 1-2-3 Release 5 users who have electronic mail. You can broadcast the range to all recipients at once, or you can route it from one recipient to the next.

Arguments

range is the name or address of the range you want to send.

to specifies who to send the range to and can be any text.

•	If to is text enclosed in " " (quotation marks), you can enter only a single name, for example, "Christine Marini."

•	If to is the name or address of a range that contains labels, you must enter the labels in a single row or column. 1-2-3 ignores cells that do not contain labels or text formulas. The range you specify can contain up to 100 cells.

subject is text that specifies the subject of the mail message, for example "Information About the Monthly Sales Meeting." If you omit subject, the mail message contains no subject.

body is text that specifies the body of the message. If you omit body, 1-2-3 includes no message with the mail.

•	If body is the name or address of a range that contains labels, you must enter the labels in a single row or column. 1-2-3 ignores cells that do not contain labels or text formulas.

•	If body is a multiple-cell range, each label is followed by a line feed and carriage return in the body of the mail message. 1-2-3 ignores cells that do not contain labels or text formulas. The range you specify can contain up to 100 cells.

return-receipt is a yes/no argument that specifies whether to send a delivery confirmation to each sender in a route list when the next person in the list receives the mail.

If you omit return-receipt, 1-2-3 sends no delivery confirmations.

route is a yes/no argument that specifies whether to route the range from one recipient to the next or send it to all recipients at once. If you omit route, 1-2-3 sends the range to all recipients at once.

priority is an integer that specifies a delivery priority.

priority	Delivery priority

1	Low

2	Normal; default if you omit the argument

3	Urgent

return-to-originator is a yes/no argument that specifies whether to add your name to the end of the list of recipients. If you omit return-to-originator, 1-2-3 does not add your name to the end of the list.

properties is text that specifies how to save values from range.

properties	1-2-3 does the following when saving range

formulas	Saves formulas without converting them to values; default if you omit the argument.

values	Converts formulas to values

Notes

If route and return-receipt are both "yes," an updated copy of the file is sent to the originator each time the file is routed to the next person in the to list.

Use {SEND-RANGE-LOGIN} to automatically log in to your mail application.

Examples

The following command sends the range BUDGET, and the message in the cell UPDATE_MSG, to the users listed in the range DEPT_MGRS. The command sends the range to each person, sequentially, sends you a copy of the range that each person receives, sends a confirmation to each sender, and sends you the range after everyone on the list has updated it.

{SEND-RANGE BUDGET;DEPT_MGRS;;UPDATE_MSG;"yes";"yes;;"yes"}

See also

Equivalent Command

File Send Mail

Help

Macros

Sending Mail from 1-2-3

Sending a Range for an overview of range routing

{SEND-MAIL}

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{SEND-RANGE-LOGIN}

{SEND-RANGE-LOGIN [message-container-path];[user-name];[password]} automatically logs in to your mail application.

Arguments

message-container-path is text that specifies the path, including the drive letter, to your cc:Mail post office database. Only use message-container-path if cc:Mail is your mail application.

user-name is text that specifies your user name for your mail application.

If you omit message-container-path or user-name, 1-2-3 looks in the [LOTUSMAIL] or [MAIL] section of your WIN.INI file.

password is text that specifies your password for your mail application.

Notes

You can help ensure the security of your password by entering it in a cell and then using Style Hide to hide either the row or column that contains the cell. Then, enter the name or address of the cell as the password argument.

Examples

The following command automatically logs the user Richard Smith into Lotus Notes, using a password stored in the current file in a cell named SECRET.

{SEND-RANGE-LOGIN ;"richard smith";secret}

See also

Equivalent Command

File Send Mail

Help

Macros

Sending Mail From 1-2-3

{SEND-MAIL}

{SEND-RANGE}

User's Guide

Chapter 24, "Using Macros to Automate Your Work"


{MAP-NEW}

{MAP-NEW location;[map-type]} draws a map at location, using data from the currently selected range.

Arguments

location is the name or address of a cell in the current file. 1-2-3 places the top left corner of the map at location. If location is a range, 1-2-3 places the top left corner of the map at the top left corner of the range and draws the map in the default size, regardless of the size of the range.

map-type is text that specifies the map type. map-type must be the name of the map type listed in the file 123R5.INI, located in your Windows directory. In 123R5.INI, under [MAPS], 1-2-3 lists a section for each map type. In the section for the map type, the entry "name=" identifies the name you must use for map-type. Below is the list of maps that come with 1-2-3. Refer to 123R5.INI for the names of any additional maps you purchase.

map-type	1-2-3 creates this map

World Countries	Countries of the world

USA by State	Continental United States, excluding Alaska and Hawaii

Alaska	Alaska

Hawaii	Hawaii

Canada by Province	Canada by province

European Union by Region	European Union by region

Europe by Country	Europe by country

Japan by Prefecture	Japan by prefecture

Mexico by Estado	Mexico by state

Australia by State	Australia by state

If you omit map-type, 1-2-3 determines the map type by the map codes or region names in the currently selected range.

Examples

The following macro creates a map from data in the range JULY SALES and places the top left corner of the map at B:D15. 1-2-3 displays the data on a map of Canada.

{SELECT JULY SALES}

{MAP-NEW B:D15; "canada by province"}

The following macro creates a map from data in the range JULY SALES and places the top left corner of the map at the cell named SALES MAP. 1-2-3 determines the map type by data in the leftmost column of JULY SALES.

{SELECT JULY SALES}

{MAP-NEW SALES MAP}

See also

Equivalent 1-2-3 command

Tools Map New Map

Help

Macros

Mapping Overview

{SELECT}


{MAP-REDRAW}

{MAP-REDRAW} redraws all maps in the current file.

Notes

You can make 1-2-3 redraw maps automatically or manually during macro execution.

To make 1-2-3 redraw maps automatically when data in the range of map data changes, include this command in your macro:

{SET "map-draw";"automatic"}

To make 1-2-3 redraw maps only when it reaches a {MAP-REDRAW} command, include this command in your macro:

{SET "map-draw";"manual"}

Troubleshooting

Click the icon below to go to the answer to the question. To come back here after you read the answer, select Back.

How can you shorten the time needed to redraw maps?

See also

Equivalent 1-2-3 command

Tools Map Redraw

Help

Macros

Mapping Overview

Info Components to learn more about setting defaults in macros


Online Coupon

Want a One-Stop Shop for All the Information on @Functions and Macro Commands in 1-2-3?

We've developed a comprehensive resource guide that contains detailed information about each @function and macro command that is available in 1-2-3 Release 5 for Windows. The information (which is also available on-line under the 1-2-3 Help menu) is consolidated into a single guide making it easy for you to find the information you need — in a hurry.

Working with  @Functions	Developing Powerful Macros
The @functions section provides detailed information about each @function including arguments, notes, formula type and examples.	The macro language section provides detailed information about each macro command, including arguments, notes, and examples.
Using this section, you will find the descriptions for various formats, learn how to enter @functions and how  to customize the @functions menu.	This section contains information to help you plan, create, name, run, debug, and save your 1-2-3 macros.

Order your 1-2-3 Release 5 for Windows
@Functions and Macro Commands Reference Guide
Call 1-800-872-3387 extension 9635
In Canada: 1-800-GO-LOTUS

Detach this card and place it in a stamped envelope.  Make checks payable to
 Lotus Development Corp.
In Canada, make checks payable to Lotus Development Canada Limited

Please allow 4 - 6 weeks for delivery.

PLEASE PRINT

Company:

Name:

Address:

City:                                                                           State/Province:

Zip Code/Postal Code:

Payment: AMEX    VISA    MC    C heck             Card #:

Signature:                                                                 Exp. Date:

UNITED STATES:  To order the reference guide:  Please call 1-800-872-3387 ext. 9635.  Or simply complete this card, enclose $14.95 plus any applicable sales tax*, and mail to:

Lotus Development Corporation
Box 100, 440 Lincoln Street
Worcester, MA 01653-0100
Attn:  Guide to @Functions and Macros in 1-2-3 Release 5

*Include sales tax for the following states:  AZ, CA, CO, CT, FL, GA, IL, IN, KS, LA, MD, MA, MI, MN, MO, NJ, NY, NC, OH, PA, TN, TX, VA, WA

CANADA:  To order the reference guide:  Please call 1-800-GO-LOTUS.  Or simply complete this card, enclose C$19.95 plus applicable taxes, and mail to:

Lotus Development Canada Limited
P.O Box 679
Scarborough, Ontario  M1K 5C5
Attn: Guide to @Functions and Macros in 1-2-3 Release 5


Marks the start of the @Function section of 123CRTF.H


Marks the end of the @Function section of 123CRTF.H


Marks the start of the Macro section of 123CRTF.H


Marks the end of the Macro section of 123CRTF.H


Marks the start of the Main Help section of 123CRTF.H


Marks the end of the Main Help section of 123CRTF.H


Raptor Help Easter Egg
Brought to you by the 1-2-3 for Windows Doc Team and their friends

Wendy Clarke, Stan Doherty, Maida Eisenberg, David Folk, Gisele Gauthier, Joseph Haungs, Kathy Howard, John Hunt, Mark Jacober, Mike Lowry, Arthur Manzi, Lisa Mosley, Henry Nigro, Peter Rodman, Laura Sohval, Debbie Stolper, Marie Tahir, Bob Voges, Darrell Williams, and of course, Tonya and Nancy.