Microsoft Excel lets you create your own custom functions, which are generally called user defined functions (UDFs). These are Function procedures inside a module with the VBA code that performs the necessary operations or calculations. They are very similar to the conventional Sub procedures, the main difference being that the Function procedures (and the UDFs) return a value. But they can also return several values. In this post we see how to create a function that returns an array of values.
Arrays are used to store sets of data. A range with data in Excel can be stored in a two-dimensional (2D) array. A column or row are also Range objects and have by default 2 dimensions (one of which has only one element – either the column or the row). In this post we see how to put data from a range into a 2D array, and data from a single column or row into a 1D array.
In a previous post we have seen how to sort text or words inside a single cell in the Excel worksheet using VBA macros. But that approach worked only with a list of unique values. In this post, we see how to sort the elements in the array created from the cell content, regardless whether that content has unique or repeated values. In both cases, the Split VBA function is used to convert the text or values into an array of elements. In this example, the elements are words separated by a space, but the same method can be used for values separated by a comma, semicolon, or a line break, as in the second scenario of the previous post.
