Regular visitors will know that I make frequent use of User Defined Functions that return an array of data, and need to be entered as an array function.

This has the advantages that the functions are very flexible in the layout of input and output data, and brief documentation can be added easily, but they do have a couple of disadvantages:

• There is no simple built-in way to reduce the size of an array functions output range.
• For applications where simplicity and speed of operation is more important than flexibility it is often better to use sub-routines with pre-defined ranges and user-input forms and buttons.

To get the best of both worlds I have modified the RegExpres spreadsheet with the following features:

• Added two sub-routines that will adjust an array function range to the selected range (press Ctrl-Shift-R), or adjust the size to display the full output array (press Ctrl-Shift-S).
• Added a simple demonstration of how a function can be wrapped in a sub-routine, that will read the input data from defined ranges, and write the output array as values to a range of adjustable size.

The spreadsheet can be downloaded from RegExpres.xlsb, including full open-source code.

The array re-sizing functions were taken from the Technicana site, where they are made freely available, along with other VBA utilities.

The screen-shots below show an array output being reduced down to the selected area with Ctrl-Shift-R, then re-sized to display the full array with Ctrl-Shift-S:

The code for the subroutine to return the array function output to a defined range is shown below:

Sub Reg_Expres_Sub()
Dim SubIn As Variant, SubArg As Variant, FuncResA As Variant
Dim Pattern As String, Operation As String, RepString As String, Glob As Boolean, IgnoreCs As Boolean
Dim NumRows As Long, NumCols As Long

' Read data
SubIn = Range("subin").Value2
SubArg = Range("subarg").Value2

' Create function arguments
Pattern = (SubArg(1, 1))
Operation = SubArg(2, 1)
RepString = SubArg(3, 1)
Glob = SubArg(4, 1)
IgnoreCs = SubArg(5, 1)

'Call Function
FuncResA = Reg_Exp(SubIn, Pattern, Operation, RepString, Glob, IgnoreCs)

'Check size
NumRows = UBound(FuncResA)
NumCols = UBound(FuncResA, 2)

' Clear output range and rezize
With Range("subout")
.ClearContents
.Resize(NumRows, NumCols).Name = "subout"
End With
' Write results to spreadsheet
Range("subout").Value2 = FuncResA

End Sub

This code is linked to a button allowing the displayed results to be updated as required: