XVerweis vs. XLookup

Forumthread: XVerweis vs. XLookup

20.01.2021 17:58:08

Kay

Hallo zusammen,
mit der neuen XVerweis Funktion kann ich super auf geschlossene Dateien mit direkter Angabe der Adressbereiche arbeiten.
Leider funktioniert die Indirekt Methode in Kombination nicht, soll heißen, man muss vor genau festlegen, was man wo sucht und das so eingeben.
Nun wollte ich die gleiche Vorgehensweise in VBA anwenden, und zwar mit dem Aufruf Application.WorksheetFunction.XLooup(....) .
Eigentlich bin ich davon ausgegangen, dass wenn XVerweis auf geschlossene Dateien zugreifen kann, dann auch XLookup.
Aber leider bekomme ich nur Werte, wenn ich auch die Datei geöffnet habe, anders als beim XVerweis, bei dem ich bei geschlossener Test.xlsx in der Tabelle selber als Funktion Werte bekomme.
Hier mal ein Testaufruf, den ich in einer function in VBA abgebildet habe:
myfunc = Application.WorksheetFunction.XLookup(
"Suchtext", Workbooks("C:\daten\test.xlsx").Worksheets("Tabelle1").Range("A1:A100"), Workbooks("C:\daten\test.xlsx").Worksheets("Tabelle1").Range("B1:B100"), "no match", 0, 1, 1)
Besteht da grundsätzlich ein Unterscheid oder übergebe ich nur die Werte falsch. Rückgabe ist immer #Wert.
Dank Kay

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: XVerweis vs. XLookup

20.01.2021 18:32:21

Daniel
AW: XVerweis vs. XLookup

20.01.2021 18:43:24

Kay
AW: XVerweis vs. XLookup

20.01.2021 18:55:35

Daniel

AW: XVerweis vs. XLookup

20.01.2021 18:32:21

Daniel

Hi
VBA kann nur mit geöffneten Dateien arbeiten.
Bei WorksheetFunction verwendest du zwar die Execlfunktion, aber alles andere, auch die einzelnen Parameter innerhalb der Funktion, sind VBA und es gelten die Spielregeln von VBA.
Wenn du in VBA auf eine geschlossene Datei zugreifen willst, gibt es nur eine Möglichkeit:
1. erstellen in VBA die Berechnung als Formeltext wie er in einer Zelle stehen würde, am besten in englischer Schreibweise mit A1-Adressen.
2. schreibe diesen Formeltext in eine Zelle und lies das Ergebnis aus
Oder
Werte den Formeltext mit der Funktion Evaluate("Formeltext") aus.
Da du in VBA bist, besteht weiterhin auch die Möglichkeit, dass du die Datei schreibgeschützt öffnest und dann damit arbeitest.
Das kann vorteilhaft sein, weil Zugriffe auf geöffnete Dateien schneller sind als auf geschlossene und weil du die Datei ggf bearbeiten kannst (sortieren, Zellbereiche bestimmen) um effektiveres Formelwerk einsetzen zu können (SVerweis mit 4.Parameter = wahr)
Ob das was bringt, hängt vom Einzelfall ab (Größe der Datei, Anzahl der Werte, Datenstruktur)
Gruß Daniel

AW: XVerweis vs. XLookup

20.01.2021 18:43:24

Kay

Hallo Daniel,
Danke für die klare Antwort, auch wenn sie mich nun wieder zum Umdenken anregt.
Da XVerweis sehr schnell, er löst rd. 300 Formeln auf mit verschiedenen Suchparametern in verschiedenen Dateien in nicht groß wahrnehmbarer Zeit auf.
Wenn ich das per VBA mache, muss ich die Dateien jeweils öffnen, dann suchen und wieder schließen.
Ich werde wahrscheinlich per VBA die XVerweis Funktion in die einzelnen Zellen kopieren (komplexes Blatt und kaum Tabellenstruktur), da sich die Quelldatei und der Suchalgorithmus selten ändern - rein der Suchtext ist dynamisch.
Verstehe nur noch so ganz, warum eine Application.WorksheetFunction Aufruf eben so anders ist, als die Funktion im Tabellenblatt.
Trotzdem Danke
Kay

AW: XVerweis vs. XLookup

20.01.2021 18:55:35

Daniel

Naja, VBA ist nicht der Excelformelinterpreter.
Manche Dinge die ähnlich aussehen, sind in VBA anders programmiert als in Excel.
Das muss man akzeptieren.
Du imporierst dir hier die Funktion von Excel nach VBA, aber eben nur die einzelne Funktion und nicht die komplette Logik der Formelinterpretation.
Dh die Bereitstellung der einzelnen Parameter ist reines VBA und gehorcht dessen Gesetzmäßigkeit.
Man kann beispielsweise über Worksheetfunction auch keine Matrixformelen erstellen und berechnen lassen.
In VBA muss man die Schleifen selber programmieren.
Gruß Daniel

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

XVerweis in einer Excel-Zelle verwenden:
- Du kannst die XVerweis-Funktion direkt in einer Zelle verwenden, um Daten aus geschlossenen Dateien abzurufen. Die Syntax lautet:
```
=XVERWEIS(Suchtext, Suchbereich, Rückgabebereich, "no match", 0, 1, 1)
```

XLookup in VBA verwenden:

Um XLookup in VBA zu verwenden, musst du sicherstellen, dass die Datei geöffnet ist. Der Aufruf sieht folgendermaßen aus:

myfunc = Application.WorksheetFunction.XLookup("Suchtext", _
Workbooks("C:\daten\test.xlsx").Worksheets("Tabelle1").Range("A1:A100"), _
Workbooks("C:\daten\test.xlsx").Worksheets("Tabelle1").Range("B1:B100"), _
"no match", 0, 1, 1)

Formeltext in eine Zelle schreiben:
- Eine alternative Methode, um auf geschlossene Dateien zuzugreifen, besteht darin, den Formeltext zu erstellen und in eine Zelle zu schreiben:
```
Worksheets("Tabelle2").Range("A1").Formula = "=XVERWEIS(Suchtext, [test.xlsx]Tabelle1!A1:A100, [test.xlsx]Tabelle1!B1:B100, ""no match"")"
```
Ergebnis auslesen:
- Sobald die Formel in der Zelle steht, kannst du das Ergebnis einfach auslesen:
```
result = Worksheets("Tabelle2").Range("A1").Value
```

Häufige Fehler und Lösungen

#WERT! Fehler:
- Dies kann passieren, wenn die Datei nicht geöffnet ist. Stelle sicher, dass die Datei geöffnet ist, bevor du XLookup in VBA verwendest.
Falsche Syntax:
- Überprüfe die Syntax der XLookup-Funktion. Die Argumente müssen korrekt übergeben werden, sonst gibt es ebenfalls Fehler.
Zugriffsprobleme:
- Stelle sicher, dass der Pfad zur Datei korrekt ist. Bei falschen Pfaden kann es zu Zugriffsfehlern kommen.

Alternative Methoden

Verwendung der Evaluate-Funktion:

Du kannst die Evaluate-Funktion in VBA nutzen, um den Formeltext auszuwerten:

result = Evaluate("XVERWEIS(Suchtext, [test.xlsx]Tabelle1!A1:A100, [test.xlsx]Tabelle1!B1:B100, ""no match"")")

Datei schreibgeschützt öffnen:
- Du kannst die Datei auch schreibgeschützt öffnen, um schneller auf die Daten zuzugreifen:
```
Workbooks.Open "C:\daten\test.xlsx", ReadOnly:=True
```

Praktische Beispiele

Beispiel 1: XVerweis in einer Zelle:

=XVERWEIS("Produkt1", [Daten.xlsx]Tabelle1!A1:A100, [Daten.xlsx]Tabelle1!B1:B100, "Nicht gefunden")

Beispiel 2: Nutzung von XLookup in VBA:

Dim result As Variant
Workbooks.Open "C:\daten\test.xlsx"
result = Application.WorksheetFunction.XLookup("Suchtext", _
 Workbooks("test.xlsx").Worksheets("Tabelle1").Range("A1:A100"), _
 Workbooks("test.xlsx").Worksheets("Tabelle1").Range("B1:B100"), "no match")

Tipps für Profis

Kombiniere XVerweis mit VBA:
- Du kannst die XVerweis-Funktion in VBA nutzen, um Daten dynamisch zu suchen und zu verarbeiten. Dies kann die Effizienz deiner Skripte erheblich steigern.
Verwende Fehlerbehandlung:
- Stelle sicher, dass du Fehlerbehandlungen in deinem VBA-Code einbaust, um unerwartete Fehler besser zu handhaben:
```
On Error Resume Next
```

FAQ: Häufige Fragen

1. Kann ich XLookup in VBA verwenden, um Daten aus geschlossenen Dateien abzurufen? Ja, XLookup in VBA benötigt, dass die Datei geöffnet ist. Um auf geschlossene Dateien zuzugreifen, musst du die Formel als Text in eine Zelle schreiben.

2. Was ist der Unterschied zwischen XVerweis und SVerweis? XVerweis ist flexibler und kann sowohl in zeilen- als auch in spaltenweise Richtung suchen, während SVerweis nur nach rechts sucht.

Beliebteste Forumthreads (12 Monate)

Forumthread: XVerweis vs. XLookup

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Infobox / Tutorial

XVerweis vs. XLookup in VBA

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread