Utilities

External Procedure axll::ConstructRange(startCell, width, height, ResultingRange)

This support function creates a range string given a starting cell and sizes.

Example:

ConstructRange("C2",2,10,myString)

sets myString to “C2:D11”

String Parameter ConstructRange::StartCell
Attributes

  • Property – Input

A string representing the top left cell of the range. For example: “A1” or “D15”.

Parameter ConstructRange::Width
Attributes

  • Property – Input, Integer

The number of columns of the range. It should be an integer value >= 1.

Parameter ConstructRange::Height
Attributes

  • Property – Input, Integer

The number of rows of the range. It should be an integer value >= 1.

String Parameter ConstructRange::ResultingRange
Attributes

  • Property – Output

(Output) The constructed range representation. Example: “C2:D11”

External Procedure axll::GetAllSheetNames(SheetNames)

This function reads all existing sheet names of the active workbook and adds them as elements to the give set.

Set GetAllSheetNames::SheetNames
Attributes

  • Property – Output

(Output) This argument should refer to an (empty) root set. On return the set will contain elements that are named according to all sheets in the workbook.

External Procedure axll::GetNamedRanges(RangeNames, SheetName)

This function reads all the named ranges for the given sheet (both local and global scope). The names of the ranges will be added as elements to the given set.

Set GetNamedRanges::RangeNames
Attributes

  • Property – Output

(Output) This argument should refer to an (empty) root set. On return the set will contain elements that are named according to the named ranges.

String Parameter GetNamedRanges::SheetName
Attributes

  • Property – Optional

(optional) The name of an existing sheet in the active workbook. If not specified the active sheet will be used.

External Procedure axll::ClearActiveSheet

This function clears the entire content of the currently active sheet.

External Procedure axll::ClearRange(RangeToClear)

This function clears all cells in the given range in the currently active sheet.

String Parameter ClearRange::RangeToClear
Attributes

  • Property – Input

The (named) range to be cleared. Examples: “A3:G10”, “MyNamedRange

External Procedure axll::ColumnNumber(colName)
Attributes

  • ReturnType – integer

This utility function will return the sequence number of the column passed in.

For example:

  • ColumnNumber(“A”) will return 1

  • ColumnNumber(“B”) will return 2

  • ColumnNumber(“AB”) will return 28

The name passed in can only contain characters in the range ‘A’ to ‘Z’ (or ‘a’ to ‘z’).

Please note that there are limits on the number of columns in Excel: The maximum column name for an .xlsx file is “XFD” (16,384) and for an .xls file it is “IV” (256).

String Parameter ColumnNumber::colName
Attributes

  • Property – Input

The name of a column. Examples: “A”, “AB

External Procedure axll::ColumnName(colNumber, colName)

This utility function gives you the name that corresponds to the n-th column

For example:

  • ColumnName(1,name) will set name to “A”

  • ColumnName(2,name) will set name to “B”

  • ColumnName(28,name) will set name to “AB”

The column number should be an integer greater or equal to 1.

Please note that there are limits on the number of columns in Excel: The maximum number of columns an .xlsx file is 16,384 (“XFD”) and for an .xls file it is 256 (“IV”).

Parameter ColumnName::colNumber
Attributes

  • Property – Input

The column number (should be >= 1)

String Parameter ColumnName::colName
Attributes

  • Property – Output

(output) The name of the column.

External Procedure axll::CopyRange(DestinationRange, SourceRange, SourceSheet, AllowRangeOverflow)

This function will copy all cells in a range to another range within the same workbook. All cell formatting is copied as well.

If copying within the same sheet, it is not allowed to specify ranges that (partly) overlap.

Example:

CopyRange("B2", "A1:D10", SourceSheet:"OtherSheet", AllowRangeOverflow:1)

This copies all the cells in the range A1:D10 of sheet OtherSheet to the range B2:E11 in the active sheet.

String Parameter CopyRange::DestinationRange
Attributes

  • Property – Input

String Parameter CopyRange::SourceRange
Attributes

  • Property – Input

String Parameter CopyRange::SourceSheet
Attributes

  • Property – Optional

Parameter CopyRange::AllowRangeOverflow
Attributes

  • Default – 1

  • Property – Optional

External Procedure axll::FirstUsedRowNumber
Attributes

  • ReturnType – integer

This function returns the first row in the current sheet that contains a cell with data.

External Procedure axll::LastUsedRowNumber
Attributes

  • ReturnType – integer

This function returns the last row in the current sheet that contains a cell with data.

External Procedure axll::FirstUsedColumnNumber
Attributes

  • ReturnType – integer

This function returns the number of the first column in the current sheet that contains a cell with data. If you need the corresponding column name you can use the function ColumnName.

External Procedure axll::LastUsedColumnNumber
Attributes

  • ReturnType – integer

This function returns the number of the last column in the current sheet that contains a cell with data. If you need the corresponding column name you can use the function ColumnName.

External Procedure axll::SetRangeBackgroundColor(RangeToColor, red, green, blue)

With this function you can specify a background color for the given cell range.

String Parameter SetRangeBackgroundColor::RangeToColor
Attributes

  • Property – Input

The (named) range for which you want to specify the background color. Examples: “A3:G10”, “C1”, “MyNamedRange”

Parameter SetRangeBackgroundColor::red
Attributes

  • Property – Input

The ‘red’ value of an RGB color value [0 .. 255]

Parameter SetRangeBackgroundColor::green
Attributes

  • Property – Input

The ‘green’ value of an RGB color value [0 .. 255]

Parameter SetRangeBackgroundColor::blue
Attributes

  • Property – Input

The ‘blue’ value of an RGB color value [0 .. 255]