Excel UDF

This page is still a work in progress.

The Excel UDF offers functions to control and manipulate Microsoft Excel workbooks.
This page describes the Excel UDF that comes with AutoIt 3.3.10.2 or later.

Features

New versions of Microsoft Office have been released since the last changes were made to the Excel UDF. The new extensions (e.g. xlsx) were not (fully) supported, new functions were missing etc. The current version of the Excel UDF lifts this limitations.

• Works with as many instances of Excel as you like - not just one
• Works with any Workbook - not just the active one
• Works with any Worksheet - not just the active one
• Only does what you tell it to do - no implicit "actions"
• Only one function to read from a cell or a range
• Only one function to write a string, a 1D or 2D array to a cell or a range
• Support for every file format Excel supports
• Speed enhancements when transferring data from/to an Excel sheet (20 - 100 times faster)

Concepts

Range

A Range is a rectangular block made of one or more cells that Excel treats as a unit. The functions of the UDF mainly work with ranges. A range - unlike a selection - is not visible on the screen.

Cell reference

The UDF only supports the A1 form (example: "B7") to reference cells. The R1C1 form (example "R7C2") is not supported. But the UDF provides functions to translate a cell reference between this two forms.
You can reference cells by name as well.

Examples

• Single cell: "B7"
• Multiple cells: "A1:B7"
• Row(s): "2" or "3:5"
• Column(s): "B" or "D:F"
• Name: "TestRange"

Features not covered by the UDF

The UDF only covers basic user needs. Single line functions (like switching to another sheet) or functions with too many parameters (like formatting a cell or range) are not covered by this UDF. You need to use the Excel COM yourself.
I will give a few examples here. The rest can be found on MSDN.

Format a range

Alignment

Horizontal alginment

Enumeration

http://msdn.microsoft.com/en-us/library/ff840772%28v=office.14%29.aspx

$oRange.HorizontalAlignment = $XlHAlign ; Can be xlCenter, xlDistributed, xlJustify, xlLeft or xlRight of the XlHAlign enumeration.

Vertical alignment

Enumeration

http://msdn.microsoft.com/en-us/library/ff835305%28v=office.14%29.aspx

$oRange.VerticalAlignment = $XlVAlign ; Can be xlBottom, xlCenter, xlDistributed, xlJustify or xlTop of the XlVAlign enumeration.

Background

...

Border

...

Color

...

Font

Bold, Color, ColorIndex, Italic, Size, Strikethrough, Subscript, Superscript, ThemeColor, ThemeFont, TintAndShade, Underline

Height/Width

...

Number Format

...

Script breaking changes after AutoIt version 3.3.10.2.

New versions of Microsoft Office have been released since the last changes were made to the Excel UDF. New file types and new functions needed to be supported, hence the UDF was complete rewritten.

Some functions/parameters have been removed or renamed, new functions/parameters have been added. A detailed list of changes can be found here.

General

All function names have been changed from _Excel* to _Excel_*.

@extended no longer contains the number of the invalid parameter. The code returned in @error tells exactly what went wrong.

The following list shows the old/new function/parameter name (a "-" is shown if the function/parameter has been removed) and some example scripts how to mimic the behaviour of the "old" UDF. If there is no entry for a removed function/parameter then there is no need for this functionality.

Function -/_Excel_Open

It's mandatory now to call function _Excel_Open. This function didn't exist in the old UDF. @extended is set if Excel was already running.

Function _ExcelFontSetProperties/-

There are so many formatting functions in Excel that they can't be covered by a few functions. The function only contained a single line of code. So it was removed. Use the code examples above to format a range.

Function _ExcelHorizontalAlignSet/-

There are so many formatting functions in Excel that they can't be covered by a few functions. The function only contained a single line of code. So it was removed. Use the code examples above to format a range.

Function _ExcelSheetActivate/-

The function only contained a single line of code. So it was removed. Replace the function with the following code:

$oWorkbook.Sheets(x).Activate ; x can be the number or name of the sheet to be activated

Function _ExcelSheetNameGet/-

The function only contained a single line of code. So it was removed. Replace the function with the following code:

$sSheetName = $oSheet.Name

Function _ExcelSheetNameSet/-

The function only contained a single line of code. So it was removed. Replace the function with the following code:

$oSheet.Name = "Name of the sheet"