4

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!

nightTrevors
  • 639
  • 4
  • 11
  • 24
  • 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 Jun 27 '12 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 Jun 27 '12 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 Jun 27 '12 at 23:45

1 Answers1

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)

  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.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • 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) – nightTrevors Jun 27 '12 at 22:51
  • 1
    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. – Tim Williams Jun 27 '12 at 23:03
  • Thanks Tim! I'll be sure to be more specific next time. – nightTrevors Jun 28 '12 at 01:39