Suppose you have a crossword (size = A by B) and wish to find words (ie strings of letters) of length L (L < A and L < B) hidden in the grid in a straight line either horizontally, vertically or diagonally. How would you write a function, using VBA, to output all the possible words?
As an example, if you were looking for words of length 8 in a 12 by 12 grid you could input = Search(8, 12, 12) and then the output should be 5 words for each row (letters 1-8, 2-9, 3-10, 4-11 and 5-12 from each row; 60 words), 5 words for each column (letters 1-8, 2-9, 3-10, 4-11 and 5-12 from each column; 60 words) and all the diagonal strings of length 8 (1 + 2 + 3 + 4 + 5 + 4 + 3 + 2 + 1 = 25 in SE direction + 25 in SW direction). In this case the output should be 170 eight-letter strings.
To get the output using a formula (which is implied by your example), you would have to use a formula over an array range. ie. A range that shares the same single formula. This is because a UDF called by a range cannot manipulate the Excel environment; it can only return a result. So your array range would have to be the size of the maximum number of results in any scenario. It would be easier to call the function via another means so that it can simply write the results without this restriction.