CODEWITHSUNDEEP
excelvba
Lucas Kappel
Lucas Kappel

Reputation: 3

Excel VBA, Range return of User-defined function not working with OFFSET in formula

I'm working with ranges of data with variable length. Therefore to get the entire range, I made a short excel function:

Function UntilBlank(StartCell As Range)
Dim lastCell As Range
Dim currentCell As Range

Set lastCell = StartCell.Cells(1, 1)
Set currentCell = StartCell.Offset(1, 0)

While Not (IsEmpty(currentCell.Cells(1, 1)))
    Set lastCell = lastCell.Offset(1, 0)
    Set currentCell = lastCell.Offset(1, 0)
Wend

UntilBlank = Range(StartCell.Cells(1, 1), lastCell)

End Function

This works great for most cases. However, when I try to use this function within an OFFSET as its first parameter, OFFSET just returns a single #VALUE cell.

My expectation is the return of my function works like any other range. The .address of the return looks perfect, I just don't understand why offset is having trouble taking that range reference and offsetting it normally.

Upvotes: 0

Views: 80

Answers (2)

CLR
CLR

Reputation: 12279

Could you not replace the entire function with something like this? Neater, tidier and no loops to slow things down.

Function UntilBlank(StartCell As Range) As Range
    If Len(StartCell.Offset(1, 0)) = 0 Then
        Set UntilBlank = StartCell
    Else
        Set UntilBlank = Range(StartCell.Cells(1, 1), StartCell.End(xlDown))
    End If
End Function

Upvotes: 0

rotabor
rotabor

Reputation: 4653

Three letters more will resolve your problem

Set UntilBlank = Range(StartCell.Cells(1, 1), lastCell)

Now the function returns the Range instead of an array of values.

Upvotes: 0

Related Questions