Switching from Range to Array and Back in a VBA Function

Question

There are a lot of questions, and a lot of responses dealing with Range/Array conversion in VBA. I haven't been able to find an answer that works, so I would really apreciate some help.

Below is what I'm trying to do:

    Function RangeToArrayToRange(inputRange As Range) As Range
        Dim inputArray As Variant
        inputArray = inputRange
        'operations on inputArray'
        '...'
        Dim outputRange As Range
        outputRange = inputArray
        Set RangeToArrayToRange = outputRange
    End Function

Thanks in advance for your help!

Solution

If outputRange is the top-left cell of the range to be populated:

outputRange.Resize(Ubound(inputArray,1), _
                   Ubound(inputArray,2)).Value = inputArray

EDIT: I think this is what you want to do

Function RangeToArray(inputRange As Range) As Variant
   Dim inputArray As Variant
   inputArray = inputRange.Value

   'operations on inputArray
   '...'

   RangeToArray = inputArray
End Function

You can use this on a worksheet as a user-defined function (UDF)

1. Select a range which is the same dimensions as the 'inputRange' (same number of rows/columns)
2. Enter "=RangeToArray([yourinputrange])" in the formula bar and press Ctrl+Shift+Enter to enter the formula as an "array formula"

This assumes you're not altering the dimensions (upper/lower bounds) of inputArray in your function.

Comments

[nightTrevors]

Thanks for your quick response, Tim. I implemented your suggestion but I am still getting a #VALUE! error. (EDIT: I'm new to StackOverflow so I couldn't get the code nicely added here)

[Tim Williams]

See my comment to your question: it would improve your question if you added a description of what you're trying to acheive with your code. That you're getting #VALUE error makes me think you're trying to use your function as a UDF on a worksheet. If that's the case, you just need to return inputArray (as a Variant) and enter the formula as an array formula in a range the same dimensions as the input range.

[nightTrevors]

Thanks Tim! I'll be sure to be more specific next time.