[VBA UDF] Determine Range To Return Array

[AiRiFiEd]

Hi, is there any way I can get the UDF to automatically determine the range depending on the size of the array?

I have done this using subroutines through range.resize = application.transpose(arr)

However, would like to check if this can be achieved by UDF using application.caller.resize or something along those lines?

Thank you!

 

[StephenCrump]

You can write a VBA array to Excel using code like this:

Range("A1").Resize(UBound(vArray), UBound(vArray, 2)).Value = vArray

But I'm not clear on what you're doing, and why you think Application.Caller might come in to the picture?

 

[AiRiFiEd]

Hi Stephen thanks for your prompt reply! I'm not too sure but I thought that for UDFs that return arrays, users would need to select an appropriate range and use clt + shift + enter.

However, is there anyway where I can just enter the UDF in 1 cell and it will automatically determine an appropriate range after using clt + shift + enter? Thank you!

You can write a VBA array to Excel using code like this:

Range("A1").Resize(UBound(vArray), UBound(vArray, 2)).Value = vArray

But I'm not clear on what you're doing, and why you think Application.Caller might come in to the picture?

 

[StephenCrump]

Hi Stephen thanks for your prompt reply! I'm not too sure but I thought that for UDFs that return arrays, users would need to select an appropriate range and use clt + shift + enter.

Ahh! Now I understand what you're doing.

However, is there anyway where I can just enter the UDF in 1 cell and it will automatically determine an appropriate range after using clt + shift + enter? Thank you!

You could use mikerickson's technique in Post #9 here: http://www.mrexcel.com/forum/excel-questions/321348-how-write-udf-returns-variable-length-data.html

But perhaps you've seen this already, based on the wording of your original post?

 

[Peter_SSs]

If you detailed a little of what you are actually trying to achieve, that might bring some more responses. If your question is really general in nature, then still one specific example may help somewhat.

 

[mikerickson]

Perhaps you are looking for something like this
Put 2 in A1
Select D1:P1 and enter the array formula =OneToN(A1). Enter it with Ctl-Shift-Enter.

Then change the value in A1

Function OneToN(N As Long) As Variant
    Dim Result() As String
    Dim i As Long
    
    ReDim Result(1 To N)
    For i = 1 To N
        Result(i) = i
    Next i
    
    If TypeName(Application.Caller) = "Range" Then
        ReDim Preserve Result(1 To Application.Caller.Cells.Count)
    End If
    OneToN = Result
End Function

 

[mikerickson]

That version adjusted the array returned to the size of the range that holds the array formula.

This approach adjusts the cells with the array formula to the size of the returned array.
Note that it requires a helper routine in the ThisWorkbook code module.

Put 2 in A1 and 4 in A2
Select D5:G7 and enter the array formula =OneToNExpanding(A1, A2)

As you change the values in A1 and A2, not that which cells have that formula changes.

' in normal module

Function OneToNExpanding(N As Long, M As Long) As Variant
    Dim Result() As String
    Dim i As Long, j As Long
    
    ReDim Result(1 To N, 1 To M)
    For i = 1 To N
        For j = 1 To M
            Result(i, j) = i & ", " & j
        Next j
    Next i
    
    If TypeName(Application.Caller) = "Range" Then
        If Application.EnableEvents Then
            With Application.Caller
                If .Rows.Count <> N Or .Columns.Count <> M Then

                    If ThisWorkbook.RangesToExpand Is Nothing Then 
                        Set ThisWorkbook.RangesToExpand = New Collection
                    End If

                    With .Resize(N, M)
                        ThisWorkbook.RangesToExpand.Add Item:=.Cells, Key:=.Address(, , , True)
                    End With

                End If
            End With
        End If
    End If

    OneToNExpanding = Result
End Function

' in ThisWorkbook code module

Public RangesToExpand As New Collection

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim oneCell As Range
    Dim strFormula
    Application.EnableEvents = False
    For Each oneCell In RangesToExpand
        With oneCell
            strFormula = .Cells(1, 1).FormulaArray
            .CurrentRegion.ClearContents
            .FormulaArray = strFormula
            RangesToExpand.Remove .Address(, , , True)
        End With
    Next oneCell
    Application.EnableEvents = True
End Sub

 

[AiRiFiEd]

sorry all for the late reply and thanks a lot for the responses! I am in the midst of clearing some bugs and will try out your suggestions soon and provide a little more details!