Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1848to1852
1848to1852
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA Rank-Function

Forumthread: VBA Rank-Function

VBA Rank-Function
06.10.2021 11:00:25
BuddyHoli
Hallo Leute,
entweder bin ich blind oder ich begreife nicht, warum VBA beim Durchlauf des Programms mit einem Anwendungs- oder objektorientierten Fehler abbricht.
Das ist die Codezeile:

CurRank = Application.WorksheetFunction.Rank(Worksheets("Ausgabe").Cells(plRow, SortCol), Worksheets("Ausgabe").Range(Cells(2, SortCol), Cells(lastERow, SortCol)))
Erläuterung: curRank ist eine Variable vom Typ Integer
plRow ist die Variable aus der For-Schleife "For plRow = 2 to LastERow", in der die CurRank-Zeile sitzt. Natürlich mit next plRow abgeschlossen.
SortCol wird in der Schleife der Wert 14 zugewiesen.
VBA bricht an dieser Stelle ab und markiert diese Zeile.
Wenn ich mit der Maus über plRow fahre, bekomme ich 2 angezeigt, wenn ich über SortCol fahre, bekomme ich 14 angezeigt, wenn ich über LastERow fahre, bekomme ich 59 angezeigt.
Auch Cells(plRow, SortCol) wird korrekt ermittelt und der Bereich am Ende soll N2 bis N59 sein, was ja auch stimmt.
Setze ich das Programm fort, läuft es einfach bis zum Ende weiter und setzt auch die Ranks korrekt.
Aber wieso bricht es an dieser Stelle ab?
LG
BuddyHoli
Anzeige

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA Rank-Function
06.10.2021 11:19:07
{Boris}
Hi,
An der Funktion liegt es nicht. Check mal die Zelle mit dem Suchbegriff. Passt der?
VG Boris
Suchwert statt Suchbegriff natürlich…oT
06.10.2021 11:21:32
{Boris}
…
AW: VBA Rank-Function
06.10.2021 11:22:09
BuddyHoli
Ja, der Suchbegriff steht in Zeile 2, Spalte 14 (bzw. N)
Anzeige
AW: VBA Rank-Function
06.10.2021 11:30:28
Daniel
weil du den Fehler machst, den wohl jeder VBA-Anfänger macht:
bei Range(Cells(), Cells()) mit Worksheetangabe (WS) muss das Worksheet vor den beiden Cells stehen.
Cells und Range ohne Tabellenblattangabe davor referenzieren immer* auf das aktive Tabellenblatt auch in dieser Konstellation.
Der Fehler kommt dann, wenn die Cells, die die Range definieren, auf einem anderen Blatt liegen als die Range selbst.
Da die Cells die Range definieren, darf man hier sogar die Tabellenblattangabe vor der Range sogar weglassen.*
also nicht WS.Range(Cells(), Cells()) sondern WS.Range(WS.Cells(), WS.Cells()) oder Range(WS.Cells(), WS.Cells())
für dein Beispiel:
CurRank = Application.WorksheetFunction.Rank(Worksheets("Ausgabe").Cells(plRow, SortCol), Range(Worksheets("Ausgabe").Cells(2, SortCol), Worksheets("Ausgabe").Cells(lastERow, SortCol)))
etwas einfacher wirds, wenn du den Zellbereich mit Resize definierst:
CurRank = Application.WorksheetFunction.Rank(Worksheets("Ausgabe").Cells(plRow, SortCol), Worksheets("Ausgabe").Cells(2, SortCol).Resize(lastERow - 2 + 1, 1))
Gruß Daniel
* das oben genannte gilt für Code in einem allgemeinen Modul
steht der Code in einem Tabellenblattmodul referenzieren Cells und Rows ohne Tabellenblattangabe davor auf das Tabellenblatt des Moduls.
dann muss man das Tabellenblatt nicht nur vor den beiden Cells, sondern auch vor der Range angeben: WS.Range(WS.Cells(), WS.Cells())
Anzeige
Das hatte ich übersehen...oT
06.10.2021 11:40:17
{Boris}
VG, Boris
AW: Das hatte ich übersehen...oT
06.10.2021 12:25:55
BuddyHoli
kein Ding, danke
AW: VBA Rank-Function
06.10.2021 12:25:36
BuddyHoli
Also einfach gesagt, muss ich VBA immer das Arbeitsblatt der Cells mitteilen, die eine Range definieren...?
Naja, Du nennst es Anfängerfehler. Besonders logisch finde ich das nicht, denn eigentlich sollte VBA doch wissen, dass man keine Range über Cells auf unterschiedlichen Arbeitsblättern definieren kann oder?
Anzeige
AW: VBA Rank-Function
06.10.2021 12:45:26
Daniel
naja, Logisch und Logisch sind zwei verschiedene Dinge.
Vorallem wenn man irgendwie stringent bzw durchgängig vorgehen will.
Alle Zellbezübe in VBA (Range, Cells, Columns und Rows) referenzieren auf das aktive Tabellenblatt, wenn nicht anders angegeben.
Cells kann ja auch solitär verwendet werden außerhalb der Range-Funktion. Warum sollte Cells zwei unterschiedliche Verhaltensweisen haben, abhängig davon wo und in welcher Funktion es eingesetzt wird? Die Prüfung müsste ja auch programmiert werden, so kann man ein einheitliches Cells verwenden.
du kannst natürlich auch die beiden Eckzellen nicht als Rangeobjekt (auch Cells ist ein Rangeobjekt) angeben, sondern als Textstring mit der Zelladresse.
dann gilt wieder das Tabellenblatt vor der Range, weil die Textstrings ja nicht zugeordnet sind.
also im Prinzip so: Sheets("Tabelle1").Range("A1", "J10")
wenn man die Zeilen- und Spaltennummer in Variablen hat, kann man das dann so verwenden:
Sheets("Tabelle1").Range(Cells(x, y).Address, Cells(a, b).Address)
also entweder das Tabellenblatt vor die beiden Cells schreiben, oder das Tabellenblatt vor die Range schreiben, vor den Cells weglassen und an die cells das .Address anhängen.
Gruß Daniel
Anzeige
AW: VBA Rank-Function
06.10.2021 13:40:00
BuddyHoli
Ok, verstehe ich.
Seltsam finde ich es dennoch, da ich ja schon mit Worksheets("MySheet").Range(Cells(1,2),Cells(1,2)) dem Range-Objekt bereits mitteilt, auf welchem Sheet die Range zu finden ist.
Sei es drum, habe ich wieder was gelernt.
Ich danke dir für deine tollen und kompetenten Erklärungen und die wirklich große Hilfe.
Buddy
Anzeige
AW: VBA Rank-Function
08.10.2021 20:29:05
Yal
Moin Z'samma,
ich wollte eigentlich nur den "noch offen"-Leuchtturm wieder runtersetzen, aber hier stehe ich und ich kann nicht anders
<Klugscheissermodus an>
Du weisst, dass in ein solche Fall die hässliche Wiederholung von Worksheets(... mit einer einbuchstabigen Variable oder mit With:

With Worksheets("Ausgabe")
CurRank = Application.WorksheetFunction.Rank(.Cells(plRow, SortCol), .Range(.Cells(2, SortCol), .Cells(lastERow, SortCol)))
End With
<Klugscheissermodus aus>
VG
Yal
Anzeige
AW: VBA Rank-Function
08.10.2021 20:34:32
BuddyHoli
Ja, es sieht schöner aus.
Aber ich kann es in der langen Variante später einfach besser lesen
Gewonnheitssache
08.10.2021 22:25:35
Yal
Ich würde unter Umstande sogar folgendes ablegen:

With Worksheets("Ausgabe")
Z = .Cells(plRow, SortCol)
R = .Range(.Cells(2, SortCol), .Cells(lastERow, SortCol))
CurRank = Application.WorksheetFunction.Rank(Z, R)
End With
Lesbarkeit ist ein endloses Thema.
VG
Yal
Anzeige
;
Anzeige

Infobox / Tutorial

VBA Rank-Funktion: Anwendung und Fehlerbehebung

Schritt-für-Schritt-Anleitung

Um die VBA Rank-Funktion richtig anzuwenden, folge diesen Schritten:

  1. Öffne den VBA-Editor in Excel (ALT + F11).

  2. Erstelle ein neues Modul:

    • Rechtsklick auf "VBAProject (DeineDatei.xlsx)" > Einfügen > Modul.

  3. Füge den folgenden Code ein:

    Dim CurRank As Integer
Dim plRow As Integer
Dim SortCol As Integer
Dim lastERow As Integer

lastERow = 59 ' Beispiel für die letzte Zeile
SortCol = 14 ' Entspricht Spalte N

For plRow = 2 To lastERow
   CurRank = Application.WorksheetFunction.Rank(Worksheets("Ausgabe").Cells(plRow, SortCol), _
               Worksheets("Ausgabe").Range(Worksheets("Ausgabe").Cells(2, SortCol), _
               Worksheets("Ausgabe").Cells(lastERow, SortCol)))
   ' Weitere Verarbeitung...
Next plRow

  4. Teste den Code und beobachte, ob Fehler auftreten.

Häufige Fehler und Lösungen

  • Anwendungs- oder objektorientierter Fehler: Dies passiert häufig, wenn die Cells()-Funktion ohne Tabellenblattangabe verwendet wird. Stelle sicher, dass Du die Cells()-Funktion immer mit dem entsprechenden Arbeitsblatt kombinierst, wie hier gezeigt:

    CurRank = Application.WorksheetFunction.Rank(Worksheets("Ausgabe").Cells(plRow, SortCol), _
           Worksheets("Ausgabe").Range(Worksheets("Ausgabe").Cells(2, SortCol), _
           Worksheets("Ausgabe").Cells(lastERow, SortCol)))

  • Falsche Zellreferenzierung: Wenn Du Range(Cells(), Cells()) ohne das Arbeitsblatt angibst, wird auf das aktive Blatt verwiesen. Das kann zu Fehlern führen, wenn die Zellen auf unterschiedlichen Blättern liegen.

Alternative Methoden

Um deinen Code lesbarer zu machen, kannst Du die With-Anweisung verwenden:

With Worksheets("Ausgabe")
    CurRank = Application.WorksheetFunction.Rank(.Cells(plRow, SortCol), _
                .Range(.Cells(2, SortCol), .Cells(lastERow, SortCol)))
End With

Diese Methode reduziert die Wiederholung des Arbeitsblattnamens und verbessert die Lesbarkeit.

Praktische Beispiele

Hier ist ein praktisches Beispiel für den Einsatz der Rank-Funktion:

Sub RankingBeispiel()
    Dim CurRank As Integer
    Dim plRow As Integer
    Dim SortCol As Integer
    Dim lastERow As Integer

    lastERow = 59 ' Beispiel für die letzte Zeile
    SortCol = 14 ' Entspricht Spalte N

    For plRow = 2 To lastERow
        CurRank = Application.WorksheetFunction.Rank(Worksheets("Ausgabe").Cells(plRow, SortCol), _
                    Worksheets("Ausgabe").Range(Worksheets("Ausgabe").Cells(2, SortCol), _
                    Worksheets("Ausgabe").Cells(lastERow, SortCol)))
        Debug.Print "Rank für Zeile " & plRow & ": " & CurRank
    Next plRow
End Sub

Dieses Skript gibt die Ränge der Werte in Spalte N aus.

Tipps für Profis

  • Vermeide redundante Codezeilen, indem Du die With-Anweisung nutzt.

  • Nutze Resize, um den Bereich dynamisch zu definieren:

    CurRank = Application.WorksheetFunction.Rank(Worksheets("Ausgabe").Cells(plRow, SortCol), _
           Worksheets("Ausgabe").Cells(2, SortCol).Resize(lastERow - 2 + 1, 1))

  • Halte Deine Variablen gut dokumentiert, um die Wartbarkeit des Codes zu verbessern.

FAQ: Häufige Fragen

1. Warum bekomme ich einen Fehler bei der Verwendung von Rank? Der Fehler kann auftreten, wenn Du Cells() ohne Arbeitsblattangabe verwendest. Stelle sicher, dass Du das Arbeitsblatt korrekt referenzierst.

2. Was ist der Unterschied zwischen Rank und Rank.EQ in Excel? Rank.EQ gibt den Rang eines Wertes innerhalb einer Liste zurück und behandelt gleiche Werte gleich. Die Rank-Funktion kann unterschiedlich implementiert sein, je nach VBA-Version.

3. Wie kann ich sicherstellen, dass mein Code auf verschiedenen Arbeitsblättern funktioniert? Verwende immer die Arbeitsblattreferenz, wenn Du Cells oder Range verwendest, um sicherzustellen, dass Du auf die richtigen Zellen zugreifst.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige