Assigning an array value to a variable in VBA makes my UDF exit

Question

I cannot understand why this UDF goes to currentInput = inputArray(i) . Here is the relevant code:

 Function OrderRange(inputRange As Range) As Variant Dim length As Integer inputHeight = inputRange.Count Dim inputArray As Variant inputArray = inputRange Dim strippedArray() As Variant ReDim strippedArray(0 To (inputHeight - 1)) Dim currentInput As String Dim i As Integer For i = 0 To (inputHeight - 1) currentInput = inputArray(i) '...computations on currentInput...' strippedArray(i) = currentInput Next i OrderRange = strippedArray End Function 

The debugger reaches currentInput = inputArray(i) , but as soon as I go to the next line, the function ends and the error #VALUE! is introduced into the cell with which I call the function #VALUE! . I know this is a specific question, but I'm sure this is a common problem, and I will edit this original post to reflect the general problem.

Edit: This is a range assignment problem for an array variant.

Answer 1

Arrays of options created by setting them to equal ranges have two dimensions, even if they are only one column or row wide. Therefore, if you call a function with A1: A10, you will get an array of 10 * 1. Also, the lower limit of the sizes will be equal to one, not zero. So you will need to do something like:

 For i = 1 To (inputHeight) currentInput = inputArray(i, 1) 

You should also use Option Explicit to be reminded of the declaration of all variables. InputHeight is never declared.