Array Functions

Some of them provide functionality for arrays similar to what built-in functions or VBA commands provide for worksheet ranges. E.g., ArrayCount is essentially the same as the VBA Count Property for ranges; ArrayCountIf is patterned on the worksheet COUNTIF Function (with the added feature of being case-sensitive or not, at the user's option); and ColumnVector(MyArray, 2) returns the second column of the array (or range) as Range("whatever").Columns(2) does for the range.

Others, in addition to providing worksheet range type of functionality for arrays, also expand on the capability of built-in worksheet functions. E.g., VLookups will return an array of sought values corresponding to multiple instances of the lookup value, in contrast with the worksheet VLOOKUP Function, which returns a sought value or values corresponding to only the first occurrence of the lookup value; and VLookupLeft can return a sought value or values that appear to the left of the lefthand column of the lookup array (or range) as well as those that appear to the right, and can, at the user's option, use a different column of the lookup table than the lefthand column for the lookup value.

Others are primarily just supporting functions to facilitate functions that are performing operations. E.g., ArrayDimensions returns the number of dimensions of an array, which is particularly useful when coding for the possibility of both one- and two-dimensional arrays, which most of the functions in the file accommodate; and ConvertBase converts an array to, or insures that it remains, a 0-based or 1-based array, depending on the selection of the user or the base of the input array, which is useful for reconverting an array that has had its base changed during the course of manipulations by other procedures.

A brief description of each of the procedures appears on the single worksheet page of the file; the procedures themselves, fairly well commented, are contained in a single module.

I haven't done any significant testing of execution speed; in particular, I have not examined at all the time consumed in recalculating worksheets containing the results of Functions called from the worksheet that remain on the worksheet. As far as running a single time and producing their single output, however, the functions seem to run essentially instantaneously.

Click on the "Array Functions" link below, and save the file to wherever on your hard drive you select. Incorporating the functions in a Personal.xls type file will make them readily available whenever you are running Excel. Most of the procedures invoke others of the 29, so having the entire suite of procedures always available, assuming the procedures are useful at all, is probably a valuable approach.

And finally, as you would expect, these procedures are provided with no warranty or guarantee of any kind; if they work for you, great-if they don't, write your own! More seriously, I would appreciate any feedback on your use of the procedures, good or bad (so long as it's contructive). I have continued to try to improve them since first announcing this site several years ago, and will consider all suggestions.

Array Functions