Arrays instead of ranges in function parameters

[vsoler]

Hi,

The below function count the number of rows of a vertical range. It
works fine.

=test(A1:A5) 'gives 5

However, sometimes I need to call it this way:

=test({1\2\3}) 'does not work

How should I modify my function so that it accepts both inputs?

Thank you
---------------------------------------------------------------

Function test(a)
test = a.Rows.Count
End Function


Vicente Soler

 

[Alan]

"{1\2\3}" does not appear to make sense as a range, as no column is
being specified.

What do you want your function to assume if no Column is provided?

Alan

 

[Chip Pearson]

Given an input array of {1,2,3}, what should the result be? The
number of elements in the array? What do the numbers represent?

Perhaps the following will get you started:

Function Test(V As Variant) As Variant
Dim R As Range

If IsObject(V) Then
If TypeOf V Is Excel.Range Then
Test = V.Rows.Count
Exit Function
Else
Test = CVErr(xlErrValue)
Exit Function
End If
End If
If IsArray(V) Then
Test = UBound(V, 1) - LBound(V, 1) + 1
Exit Function
End If
On Error Resume Next
Err.Clear
Set R = Application.Range(V)
If Err.Number = 0 Then
Test = R.Rows.Count
Exit Function
End If
End Function

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Dave Peterson]

You may have an answer at your previous post.

Hi,

The below function count the number of rows of a vertical range. It
works fine.

=test(A1:A5) 'gives 5

However, sometimes I need to call it this way:

=test({1\2\3}) 'does not work

How should I modify my function so that it accepts both inputs?

Thank you
---------------------------------------------------------------

Function test(a)
test = a.Rows.Count
End Function

Vicente Soler

 

[vsoler]

Given an input array of {1,2,3}, what should the result be?  The
number of elements in the array? What do the numbers represent?

Perhaps the following will get you started:

Function Test(V As Variant) As Variant
    Dim R As Range

    If IsObject(V) Then
        If TypeOf V Is Excel.Range Then
            Test = V.Rows.Count
            Exit Function
        Else
            Test = CVErr(xlErrValue)
            Exit Function
        End If
    End If
    If IsArray(V) Then
        Test = UBound(V, 1) - LBound(V, 1) + 1
        Exit Function
    End If
    On Error Resume Next
    Err.Clear
    Set R = Application.Range(V)
    If Err.Number = 0 Then
        Test = R.Rows.Count
        Exit Function
    End If
End Function

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
    Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

Thank you very much Chip Pearson. By the way, I like your web site a
lot, it is very clear and comprehensive.

Two short questions:

#1 - If the parameter is an object but not a Range, what else could it
be?

#2 - If the parameter is neither a Range nor an array, what else could
it be?

#3 - (probably same as #2) I understand that "Set R = Application.Range
(V)" tries to convert whatever else data type it could be to a Range.
Is it correct?

Thank you again

Vicente Soler

 

[Bernd P]

Hello Vicente,

Two "brute-force" ways are:
a) Transpose the input twice, see http://sulprobil.com/html/pfreq.html
b) Try both .count and UBOUND, see http://sulprobil.com/html/interpolate.html

Regards,
Bernd

 

[Chip Pearson]

#1 - If the parameter is an object but not a Range, what else could it
be?

That depends on how the function was called. If called from a
worksheet cell, Range is the only object type that it might be, but it
is possible that the function might be called from other VBA code that
passes who knows what. Since there is no type checking in the
parameter (since a Variant can be anything), the does type checking.
It is a prudent but possibly unnecessary step.

#2 - If the parameter is neither a Range nor an array, what else could
it be?

It could be a simple String or numeric data type:

=Test("A1") ' String type parameter
=Test(123) ' simple numeric

#3 - (probably same as #2) I understand that "Set R = Application.Range
(V)" tries to convert whatever else data type it could be to a Range.
Is it correct?

Correct. After the code has exhausted all other possibilities, it
makes a final attempt to get something meaningful out of the parameter
V. V might be a String containing an address:
=Test("A1")


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[vsoler]

That depends on how the function was called. If called from a
worksheet cell, Range is the only object type that it might be, but it
is possible that the function might be called from other VBA code that
passes who knows what. Since there is no type checking in the
parameter (since a Variant can be anything), the does type checking.
It is a prudent but possibly unnecessary step.


It could be a simple String or numeric data type:

=Test("A1")  ' String type parameter
=Test(123) ' simple numeric


Correct. After the code has exhausted all other possibilities, it
makes a final attempt to get something meaningful out of the parameter
V.  V might be a String containing an address:
=Test("A1")

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
    Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

Chip,

Your answer is very clear, thank you.