How to pass used range to a function in a cell

[gmaxey]

In a previous thread I was offered the following function to use to count the number of rows where the combined length of columns A and B were greater than 256:



Public Function fnCountHowManyLongLines(ByVal Rng As Range) As Long
Dim arr As Variant
Dim i As Long, j As Long
Dim s As String
Dim cnt As Long
arr = Rng
For i = 1 To UBound(arr, 1)
s = ""
For j = 1 To UBound(arr, 2)
s = s & arr(i, j) & ""
Next
cnt = cnt + Abs(Len(s) > 256)
Next
fnCountHowManyLongLines = cnt
End Function

The solution indicated to put: =fnCountHowManyLongLines(A2:B3) in any cell to display the result.

That works for A2:B3. The is the sheet is generated as the result of a query and I will not know the exact range (how many records will be in the sheet).

I can run:


Sub Test()
Dim Rng As Range
MsgBox fnCountHowManyLongLines(ActiveSheet.UsedRange)
' or
Set Rng = Range("A1:B" & ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row)
MsgBox fnCountHowManyLongLines(Rng)
End Sub

... and get the correct result, but if I put either in a cell e.g.,:

=fnCountHowManyLongLines(ActiveSheet.UsedRange)

I get #Value or and error.

Is there a way to pass the UsedRange or a calculated range to a cell function in this manner.

Thanks.

[Paul_Hossler]

ActiveSheet.UsedRange

is VBA and doesn't work like that in a WS formula

Example workaround, replace with your own code



Option Explicit

Function aaa() As Long
aaa = ActiveSheet.UsedRange.Count
End Function


and called with =aaa() in a WS cell

[gmaxey]

Thanks Paul,

So used this and it works now.


Function fcnDisplayLongCount() As Long
Dim oRng As Range
Set oRng = Range("A1:B" & ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row)
fcnDisplayLongCount = fnCountHowManyLongLines(oRng)
Application.Volatile
End Function

Public Function fnCountHowManyLongLines(ByVal Rng As Range) As Long
Dim arr As Variant
Dim i As Long, j As Long
Dim s As String
Dim cnt As Long
arr = Rng
For i = 1 To UBound(arr, 1)
s = ""
For j = 1 To UBound(arr, 2)
s = s & arr(i, j) & ""
Next
cnt = cnt + Abs(Len(s) > 256)
Next
fnCountHowManyLongLines = cnt
End Function