Thema: RANG-Funktion in VBA auf ARRAY anwenden | Herbers Excel-Forum

Hallo VBA-Spezialisten,
ich suche ein Synonym für die RANG()-Funktion in VBA, die man auf ARRAYS anwenden kann.
Die RANG()-Funktion selbst erhält man in VBA bekanntlich mit Application.WorksheetFunction.Rank()
Diese funktioniert auch, wenn man als Argumente der Funktion Tabellenbereiche angibt !
Ich möchte jedoch keine Tabellenbereiche angeben (auch nicht das ARRAY temp. in eine Tabelle kopieren !), sondern direkt die Werte des ARRAYs auswerten.
Beispiel : Das ARRAY enthält die Werte {30,8,4,90,5,60,2,1,7}
Ich möchte nun das Ranking des ersten Wertes (30) ermitteln (das Ergebnis müsste 7 lauten, da 30 der 7.grösste Wert im Array ist !).
Mit einer UDF (udfRank - siehe unten) kann ich das ermitteln, ich würde jedoch gerne auf die Excel-eigene Funktion RANK() zurückgreifen. Hat jemand eine tolle Idee dazu ?
Hier ein kleines Beispiel zum Testen mit 10 Zufallswerten im ARRAY :

Sub ArrayTest()
Dim ar(10), intZ As Integer
'Zufallswerte in ARRAY schreiben :
For intZ = 0 To 9
ar(intZ) = Rnd()
Next
'funktioniert nicht :
'MsgBox Application.WorksheetFunction.Rank(ar(0), ar)
[A1:J1] = ar 'Werte in Zeile 1 zur Kontrolle ausgeben
MsgBox udfRank(ar(0), ar)
End Sub
Function udfRank(Wert, Wertereihe, Optional aufsteigend = True)
Dim dblW As Double, lngZ As Long, lngE As Long
For lngZ = LBound(Wertereihe) To UBound(Wertereihe)
If aufsteigend Then
If Wert > Wertereihe(lngZ) Then lngE = lngE + 1
Else
If Wert <= Wertereihe(lngZ) Then lngE = lngE + 1
End If
Next
udfRank = lngE
End Function

Vielen Dank für's Mitdenken, Gruß NoNet

Hallo NoNet,
30 ist für mich der 7-kleinste Wert bzw. der 3-größte Wert.
Frage noch offen.
Gruß
Reinhard

_oT

Hallo NoNet,
wenn man das verkürzen könnte...
=(30=KGRÖSSTE({30;8;9;60};1))*1+(30=KGRÖSSTE({30;8;9;60};2))*2+(30=KGRÖSSTE({30;8;9;60};3)) *3+(30=KGRÖSSTE({30;8;9;60};4)) *4...
Gruß
Reinhard

Hallo NoNet,
habe jetzt eine ganze Zeit gedacht und probiert - ohne Erfolg. Daher versuche ich es noch mal mit Logik und erkläre, warum es nicht gehen kann:
1.) Die WorksheetFunction.Rank-Methode verlangt lt. Onlinehilfe "Datentyp: Range, Beschreibung: Bezug – ein Array von oder ein Bezug auf eine Liste von Zahlen. Nichtnumerische Werte in Bezug werden ignoriert. " Auch wenn da eigentlich steht "ein Array von ... Zahlen", heißt das leider noch lange nicht, dass das auch funktioniert.
Debug.Print Application.WorksheetFunction.Rank(2, Array(2, 5, 1, 7, 3))
führt zu einem Fehler, es wird also definitiv ein "Range"-Objekt benötigt.
2.) Onlinehilfe: "Range-Objekt - Stellt eine Zelle, eine Zeile, eine Spalte, eine Auswahl von Zellen aus einem oder mehreren zusammenhängenden Zellblöcken oder einen 3D-Bereich dar."
Es scheint mir also nicht möglich zu sein, ein Range-Objekt zu verwenden, das NICHT auf einen Tabellenbereich verweist.
Ich hatte auch überlegt, ob man irgendwie ein "virtuelles" Range-Objekt neu erstellen kann, das sich nicht auf einen vorhandenen Zellbereich bezieht, aber da sehe ich schwarz.
Es liefe also darauf hinaus, tatsächlich das Array in einen (möglicherweise versteckten) Zellbereich zu kopieren und anschließend die Funktion zu verwenden.
Viele Grüße, Wolfgang

...und ein Argument As Range deklariert, NoNet & Wolfgang,
kann man dem ja auch keinen Variant zuweisen! So ist das halt mit den nachträglich installierten xlFktt (von KrisTech ?); sie lassen die „Genialität des Ursprungs“ vermissen... :->
Gruß Luc :-?

Hallo Leute,
vielen Dank für eure Statements - ich gebe mich nun geschlagen und werde meine eigene UDF (s.o.) verwenden.
Dass die Funktion RANK() als Argumente Bereiche benötigt, ist mir natürlich klar (daher der Hinweis auf "Tabellenbereiche" => RANGE in meiner Fragestellung ;-), ich hatte jedoch gehofft, dass irgendjemand einen genialen Kniff kennt, um das zu umgehen, zumal das bei anderen (Worksheet-)Functions ja auch möglich ist.
Für eine "Quick-and dirty"-Lösung bietet es sich an, das ARRAY temporär in eine Tabelle zu kopieren, dort das Ranking zu ermitteln (klappt bekanntermaßen auch sehr schnell), aber in Mappen, in denen ein Blatt- oder gar Mappenschutz hinterlegt ist, klappt das nicht (da müsste man allenfalls eine neue temp. Mappe erstellen, aber das möchte ich auch nicht).
Reinhards Lösungsvorschlag ist auch ganz nett und trickreich, aber für große Arrays nicht ganz praktikabel (OK, mit einer Schleife wäre das auch kein Hexenwerk, aber da kann ich auch meine UDF verwenden, die ist universeller).
Manchmal darf man eben auch mal auf kleine Wunder hoffen - auch wenn sich das hier leider nicht erfüllt hat ;-)
Danke nochmals, Gruß, NoNet

...Functions ja auch möglich ist..."
Eben, NoNet,
die sind halt anders deklariert...
Nebenbei, dass mit T und N als „Array-Stabilisierer“ bzw -Erzeuger ist auch so ein Irrglaube...
Gruß Luc :-?

Hey Luc,
meine Aussage bezog sich darauf, dass viele WorksheetFunctions sowohl Ranges als auch ARRAYs als Argumente akzeptieren (da diese wohl als Variant deklariert sind) - nicht darauf, ein als RANGE deklariertes Argument "umzubiegen". Es könnte ja sein, dass es eine mir unbekannte Funktion cARRAY2Range (o.ä.) gibt ;-) oder einfach nur eine andere Funktion/Syntax, die das gleiche Ergebnis liefert wie das gewünschte Ergebnis der Application.RANK-Funktion !
Das mit T() und N() liegt Dir wohl quer im Hals oder ;-) - Das hat mit diesem Thread überhaupt nichts zu tun ! Immerhin bewirken N() und T() defakto die Akzeptanz von ARRAYs in anderen Funktionen - auch wenn das nicht die originäre Absicht dieser Funktionen ist :D
Gruß, NoNet

...wie man auch unschwer meiner 1.AW entnehmen kann, NoNet!
2. Dass das OT ist, weiß ich doch, aber weil u.a. auch du ein Anhänger von N/T-MxFmln bist, wollte ich dich mal eben so nebenbei veranlassen, diese mal pur mit vbFkt Evaluate zu testen... ;-)
(Da wird wohl auch etwas anders deklariert worden sein — Ggteil INDEX mit Arg2=0 — quasi als Nachtrag zu deiner alten Diskussion vom Vorjahr...)
Gruß Luc :-?