How can I send and return range/array in Excel VBA

Question

I'm very new to VBA in Excel, tried this one, but it did not work with me.

I'm trying to build an inverse of an input array, as shown in the pic below, that convert the rows ro columns, and convert columns into rows.

I followed the below instruction, as a startup, that I want to re-print the same input array as it is before doing the required reverse, but I got an error

Steps followed:

Open the Visual Basic Editor. In Excel, hit Alt+F11 if on Windows, Fn+Option+F11 if on a Mac.
Insert a new module. From the menu: Insert -> Module (Don't skip this!).

Create a Public function. Example:

Public Function transform(inputRange As Range) As Range
    transform = inputRange
End Function

Then use it in any cell like you would any other function: =transform(A1:B2).

I got error #VALUE!

How can I read the range in the VBA, work with its elements, and return new one to be displayed in the excel sheet?

Mathieu Guindon · Accepted Answer · 2019-08-30T11:04:56.850

Let's make the default member calls and keywords explicit:

Public Function transform([ByRef] inputRange As Range) As Range
    [Let] transform.[_Default] = inputRange.[_Default]
End Function

Because of the missing Set keyword, the RHS Range is being Let-coerced into the LHS, which becomes the victim of an implicit default member assignment.

The right-hand side of the assignment is not a Range, it's a Variant that's either a single value or a 2D variant array, depending on what inputRange is: the function is computing to #VALUE! because it runs into a type mismatch error when it attempts to assign that Variant (RHS) to a Range reference (LHS) - you can experience this by typing ?transform([A1]) in the immediate pane (Ctrl+G).

If you mean the function to return a Range object reference, you need a Set keyword there - note that inputRange can be passed by value:

Public Function transform(ByVal inputRange As Range) As Range
    Set transform = inputRange
End Function

Now, with that said, a function that returns a Range can't be used as-is in a worksheet - you have to wrap it with, say, an INDEX function, at least if what you're returning isn't a single value: a UDF cannot modify any cells in the sheet: it returns a value.

You're looking for the TRANSPOSE function.

UPDATE

You can display your results in the excel sheet as below, as explained here:

First select some blank cells. But make sure to select the same number of cells required to be returned by the function, both horizontally and vertically
With those blank cells still selected, type your function, like: =transform(
Now type the range of the cells you want to manipulate, so the formula would be something like =transform(A1:B2) -- but don't press ENTER yet! Just stop typing, and go to the next step.
Finally, press CTRL+SHIFT+ENTER

Your VBA function for doing so, could be:

Public Function transform(inputRange As Range) As Variant()
    x = inputRange.rows.Count
    y = inputRange.Columns.Count

    Dim oldArray() As Variant, newArray() As Variant
    ReDim oldArray(1 To x, 1 To y), newArray(1 To y, 1 To x)

    oldArray = inputRange.Value

    For i = 1 To x
        For j = 1 To y
            newArray(j, i) = oldArray(i, j)
        Next
     Next

    transform = newArray
End Function

Just to complete the valid answer by an alternative function return: an approach in one line could be `transform = Application.Transpose(inputRange.Value)`. *Caveat: AFAIK there is a limitation for counts greater than 65K in Excel versions prior to 2019* — T.M., Aug 30 '19 at 17:24

score 0 · Answer 2 · answered Aug 29 '19 at 21:42

Does it have to a function? How would a subroutine like that work for you?

Sub TransposeRowsToColumns()
    Dim SourceRange As Range
    Dim DestinationRange As Range

    Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
    Set DestinationRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8)

    SourceRange.Copy
    DestinationRange.Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Application.CutCopyMode = False

End Sub

I found it and added an answer, thanks for your answer as will it may help me in the future. — Hasan A Yousef, Aug 30 '19 at 10:16

Hasan A Yousef · Answer 3 · 2019-08-30T15:49:21.837

With help of the @Mathieu answer and this work around, I was able to do it as below:

Function UDF_fn(r As Range)
    Let x = r.Rows.Count
    Let y = r.Columns.Count

    Dim oldArray() As Variant, newArray() As Variant
    ReDim oldArray(1 To x, 1 To y), newArray(1 To y, 1 To x)

    oldArray = r.Value

    For i = 1 To x
        For j = 1 To y
             newArray(j, i) = oldArray(i, j)
        Next
     Next

    For j = 1 To y
        For i = 1 To x
            Evaluate "Adjacent(" & Application.Caller.Offset(j - 1, i).Address(False, False) & ", " & newArray(j, i) & ")"
        Next
     Next

    UDF_fn = "Result"
End Function

Private Sub Adjacent(CellToChange As Range, A As Integer)
    CellToChange = A
End Sub

For standard VBA without a function, you can check this document

How can I send and return range/array in Excel VBA

3 Answers3