GetArray method

Returns the recordset as an array (1-based) suitable for return to a worksheet.

Applies to

TableData	ArrData	RsetData	Xdata	XShared
		✓

Parameters

Name	Type	Description
MaxCols	Long (optional)	Number of columns to fill. If missing, uses the number of fields in the dataset.
MaxRows	Long (optional)	Number of rows to fill. If missing, uses the number of rows in the dataset (plus 1 for the header).
Default	Variant (optional)	Default value to fill cells where there is no data.

The purpose of this method is to return the results of a query in a format which can be placed directly on a worksheet (including headers). This can be done if required in a user defined function on the worksheet, providing a simple way of returning query results (the SQL can be a formula for maximum flexibility). In a user defined function placed as an array function in a range, the MaxCols parameter can be set to Application.Caller.Columns.Count and the MaxRows parameter can be set to Application.Caller.Rows.Count ensuring that the results returned exactly fill the space occupied by the function.

If row or column counts are greater than maxima specified, the method returns #N/A. If row or column counts are less than maxima specified, the method fills the unused cells with the Default specified. Long text is truncated to 255 characters unless all the fields and Default (if specified) are text (this is an Excel limitation).