VBA – VLOOKUP und XLOOKUP

Dieser Artikel zeigt Ihnen, wie Sie die Funktionen VLOOKUP und XLOOKUP (Deutsch: SVERWEIS und XVERWEIS) in VBA verwenden können.

Die Funktionen VLOOKUP und XLOOKUP sind äußerst nützlich in Excel. Sie können auch in der VBA-Programmierung verwendet werden.

VLOOKUP

Es gibt zwei Möglichkeiten, die VLOOKUP-Funktion in VBA aufzurufen:

• Application.WorksheetFunction.Vlookup
• Application.Vlookup

Die beiden Methoden sind identisch, abgesehen davon, wie sie Fehler behandeln.

Application.WorksheetFunction.VLookup

In diesem Beispiel soll die erste Methode demonstriert werden.

Hier haben wir eine VLOOKUP-Formel, die wir in VBA nachbilden werden, in Excel erstellt:

Sub  PreisNachschlagen()
   ActiveCell = Application.WorksheetFunction.VLookup(Range("E3"), Range("B2:C6"), 2, False)
End Sub

Das Ergebnis ist Folgendes:

Beachten Sie, dass das tatsächliche Ergebnis in die Zelle F3 geschrieben wurde und nicht in die Formel!

Wenn wir eine Formel statt eines Wertes an Excel zurückgeben möchten, schreiben Sie VLOOKUP in eine Formel:

Sub PreisNachschlagen()
   ActiveCell = "=VLOOKUP(E3,B2:C6,2,FALSE)"
End Sub

Sie können die Formel auch in eine Zelle schreiben, indem Sie die R1C1-Schreibweise verwenden, die eine Formel mit relativen Verweisen erstellt, die über einen Bereich von Zellen verwendet werden kann:

Sub PreisNachschlagen() 
  ActiveCell = "=VLOOKUP(RC[-1],RC[-4]:R[3]C[-3],2,FALSE)"
End Sub

Sie können auch Variablen verwenden, um die VLOOKUP-Eingaben zu definieren:

Sub PreisNachschlagen()
 Dim strProdukt As String
 Dim bereich As Range 
 strProdukt = Range("E3") 
 Set bereich = Range("B2:C6") 
 ActiveCell = Application.WorksheetFunction.VLookup(strProdukt, bereich, 2, False)
End Sub

Anstatt die Ergebnisse der Funktion in eine Zelle zu schreiben, können Sie die Ergebnisse natürlich auch in eine Variable schreiben:

Sub PreisNachschlagen() 
  Dim strProdukt As String
  Dim bereich As Range 
  Dim strErgebnis As String
 
  strProdukt = Range("E3") 
  Set bereich = Range("B2:C6") 
  strErgebnis = Application.WorksheetFunction.VLookup(strProdukt, bereich, 2, False) 
End Sub

Application.VLookup im Vergleich zu Application.WokrsheetFunction.VLookup

In den obigen Beispielen können wir beide Methoden verwenden und sie liefern das gleiche Ergebnis. Aber was wäre, wenn wir ein Produkt, das nicht existiert, suchen würden?  Welches Ergebnis würde dann zurückgegeben werden? Jede Funktion würde ein ganz anderes Ergebnis liefern.

Normalerweise wird bei der Verwendung von VLOOKUP in Excel #NV in die Zelle zurückgegeben, wenn die Suchfunktion die richtige Antwort nicht findet. Wenn wir jedoch das obige Beispiel verwenden und nach einem Produkt suchen, das es nicht gibt, erhalten wir einen VBA-Fehler.

Wir können diesen Fehler in unserem Code abfangen und eine benutzerfreundlichere Meldung zurückgeben, wenn das gesuchte Element nicht gefunden wird.

Sub PreisNachschlagen()
  On Error GoTo eh
  ActiveCell = Application.WorksheetFunction.VLookup(Range("E3"), Range("B2:C6"), 2, False)
  Exit Sub
eh:
  MsgBox "Produkt nicht gefunden! Bitte versuchen Sie es mit einem anderen Produkt."
End Sub

Alternativ können wir unseren VBA-Code ändern, indem wir Application.Vlookup anstelle von Application.WorksheetFunction.VLookup verwenden.

Sub PreisNachschlagen()
  ActiveCell = Application.VLookup(Range("E3"), Range("B2:C6"), 2, False)
End Sub

Wenn wir dies tun, wird das von uns erwartete #NV an die Zelle zurückgegeben, wenn ein Nachschlagewert nicht gefunden wird.

 

WorksheetFunction.XLOOKUP

Die XLOOKUP-Funktion wurde entwickelt, um die Funktionen VLOOKUP und HLOOKUP in Excel zu ersetzen. Sie ist nur für Office 365 verfügbar, so dass die Verwendung dieser Funktion recht restriktiv ist, wenn einige Ihrer Benutzer ältere Excel-Versionen verwenden.

Sie funktioniert in VBA auf die gleiche Weise wie die VLOOKUP-Funktion.

Sub PreisNachschlagenV()
   ActiveCell = Application.WorksheetFunction.XLookup(Range("E3"), Range("B2:B6"), Range("C2:C6"))
End Sub

ODER

Sub PreisNachschlagenV() 
   ActiveCell = Application.XLookup(Range("E3"), Range("B2:B6"), Range("C2:C6")) 
End Sub

Wenn wir wiederum Variablen verwenden wollen, können wir den folgenden Code schreiben

Sub PreisNachschlagenV()
   Dim strProdukt As String
   Dim rngProdukt As Range
   Dim rngPreis As Range
   strProdukt = Range("E3")
   Set rngProdukt = Range("B2:B6")
   Set rngPreis = Range("C2:C6")
   ActiveCell = Application.WorksheetFunction.XLookup(strProdukt, rngProdukt, rngPreis)
End Sub

Wenn wir anstelle eines Wertes eine Formel an Excel zurückgeben möchten, müssen wir diese mit dem VBA-Code in Excel eingeben.

Sub PreisNachschlagen()
   ActiveCell = "=XLOOKUP(E3,B3:B6,C3:C6)"
End Sub

Oder Sie können die Formel in eine Zelle schreiben, indem Sie die R1C1-Schreibweise verwenden.

Sub PreisNachschlagen() 
  ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],RC[-4]:R[3]C[-4],RC[-3]:R[3]C[-3])"
End Sub

Einer der größten Vorteile von XLOOKUP gegenüber VLOOKUP ist die Möglichkeit, einen Bereich von Spalten zu durchsuchen und den Wert in jeder Spalte zurückzugeben.

Sehen wir uns das folgende Beispiel an:

Wir haben die Formel in Zelle H3 erstellt, wo sie die Werte im Bereich C2:E6 nachschlägt. Da es sich um mehrere Spalten handelt, werden die Spalten I und J automatisch mit den gefundenen übereinstimmenden Ergebnissen aufgefüllt. Die Formel läuft in die Spalten I und J über, ohne dass wir STRG+SHIFT für eine Array-Formel verwenden müssen. Diese Fähigkeit der Formel, über die Spalten überzulaufen, ist eine der neuen Ergänzungen für Excel 365.

Um dies mit VBA-Code nachzubilden, können wir das Folgende in unser Makro eingeben:

Sub PreisNachschlagen()
   ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],RC[-6]:R[3]C[-6],RC[-5]:R[3]C[-3])"
End Sub

Dabei verwendet die Formel die R1C1-Syntax (Zeilen und Spalten) anstelle der A1-Syntax (Bereich). Dies führt dazu, dass die Formeln in Excel wie in der obigen Grafik dargestellt eingegeben werden.

Sie können nicht mehrere Spalten nachschlagen, wenn Sie die Methode WorksheetFunction verwenden.