just learning

[JP Ronse]

Hi All,

Finding some interesting ways to use functions in VBA, I was trying ...

The last statement gives: Compile error: type mismatch.

Why is Large, Match working on an array and Rank not?

Wkr,

JP

Sub test_JP()
Dim arr(1 To 1000) As Double
Dim i, v As Range

For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.WorksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub

 

[Dana DeLouis]

For i = 1 To 10

arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

Hi. In general, you are looking for the number 1 within this array to
find its rank. The number '1 is most likely not there.

Having said that, I too am getting errors. I've tried all variations
that I can think of with no luck. Hopefully, someone will jump in.

= = = = =
Dana DeLouis
(using Excel 2007)

 

[Per Jessen]

Hi

In the Rank function, Arg2 is required to be a Range variable, whereas the
other functions require a Arg2 as Double.

Regards,

 

[Joel]

Array start at zero when using worksheet functions in excel. See if these
changes help

Sub test_JP()
Dim arr(0 To 999) As Double
Dim i, v As Range

For i = 0 To 9
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.WorksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub

 

[JP Ronse]

Hi Dana,

Tnx for your reply. The statement itself generates already the error
(compile error) and hitting <F5> highlights 'arr'?



Wkr,

JP

 

[arjen van der wal]

I'm not sure about the second error, but the first Rank example works when
I change it to the following:

Debug.Print Application.WorksheetFunction.Rank _
(Sheets("Sheet3").Range("B2"), v, 1)

 

[JP Ronse]

Hi Joel,

Thank you very much for spending your time to help me out of this.

The issue is that the statement

Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

does not allow to run the code.

See also the reply of Per.

Wkr,

JP

 

[JP Ronse]

Hi Per,

You are right. I retyped the statement and saw that arg2 must be a range .

Thank you for your time. I should better read what the instructions are
saying instead wishful thinking.

Wkr,

JP

 

[JP Ronse]

Hi Arjen,

Tnx for your time.

The error comes from the statement

Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

Per Jessen explained that arg2 (arr) must be a range.



Wkr,

JP

 

[Rick Rothstein]

If you look at the Intelli-help popup as you type the command in the code
window, it shows this for the syntax...

Rank(Arg1 As Double, Arg2 As Range, [Arg3] As Double)

I know the help for this function says the second argument can be an array
or a range, but it appears that VB imposes a "range only" requirement on
this second argument.

 

[JP Ronse]

Hi Rick,

Tnx, Per Jessen said the same, and testing proved it.

Wkr,

JP

If you look at the Intelli-help popup as you type the command in the code
window, it shows this for the syntax...

Rank(Arg1 As Double, Arg2 As Range, [Arg3] As Double)

I know the help for this function says the second argument can be an array
or a range, but it appears that VB imposes a "range only" requirement on
this second argument.

--
Rick (MVP - Excel)

Hi Dana,

Tnx for your reply. The statement itself generates already the error
(compile error) and hitting <F5> highlights 'arr'?



Wkr,

JP

 

[Per Jessen]

Hi JP

Thanks for your reply. I am glad to help.

Sure, we can all benefit from the instructions/help which are provided.

Best regards,
Per