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!

After answering, and then re-reading your question I'm now unclear on your need. Maybe you could try explaining it in words (not code, since that seems to be where your problem lies). You can't convert a variant array to a Range, if that's what you're trying to do... — Tim Williams
– Tim Williams, Commented Jun 27, 2012 at 22:19
I'm not sure what you are asking, but remember to use "Set" with every range, e.g., Set outputRange = inputArray. — Doug Glancy
– Doug Glancy, Commented Jun 27, 2012 at 22:32
Please add more detail on what you want to do, and what you are working with (ie single row, single column, multi row/column etc) — brettdj
– brettdj, Commented Jun 27, 2012 at 23:45

Tim Williams · Accepted Answer · 2012-06-28 01:00:50Z

6

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)

Select a range which is the same dimensions as the 'inputRange' (same number of rows/columns)
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.

edited Jun 28, 2012 at 1:00

answered Jun 27, 2012 at 22:17

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nightTrevors Over a year ago

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 Over a year ago

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 Over a year ago

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

Collectives™ on Stack Overflow

Switching from Range to Array and Back in a VBA Function

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related