Wie zu verwenden Arbeitsblatt Funktionen wie SVERWEIS in VBA Excel?
Die Funktionen wie VLOOKUP, COUNTIF, SUMIF werden als Arbeitsblattfunktionen bezeichnet. Im Allgemeinen sind die Funktionen, die in Excel vordefiniert und für die Verwendung in einem Arbeitsblatt bereit sind, Arbeitsblattfunktionen. Sie können den Code hinter diesen Funktionen in VBA nicht ändern oder sehen.
Andererseits sind die benutzerdefinierten Funktionen und Funktionen, die für VBA spezifisch sind, wie MsgBox oder InputBox, VBA-Funktionen. Wir alle wissen, wie man VBA-Funktionen in VBA verwendet. Aber was ist, wenn wir VLOOKUP in einem VBA verwenden möchten? Wie machen wir das? In diesem Artikel werden wir genau das untersuchen.
Verwenden von Arbeitsblattfunktionen in VBA
Für den Zugriff auf die Arbeitsblattfunktion verwenden wir eine Anwendungsklasse. Fast alle Arbeitsblattfunktionen sind in der Application.WorksheetFunction-Klasse aufgeführt. Mit dem Punktoperator können Sie auf alle zugreifen.
Schreiben Sie in ein beliebiges Sub Application.WorksheetFunction. Schreiben Sie den Namen der Funktion. VBAs Intellisense zeigt den Namen der verfügbaren Funktionen an. Sobald Sie den Funktionsnamen ausgewählt haben, werden Sie wie bei jeder Excel-Funktion nach den Variablen gefragt. Sie müssen jedoch Variablen im verständlichen VBA-Format übergeben. Wenn Sie beispielsweise einen Bereich A1: A10 übergeben möchten, müssen Sie ihn als Bereichsobjekt wie Range („A1: A10“) übergeben.
Verwenden wir also einige Arbeitsblattfunktionen, um es besser zu verstehen.
Verwendung der VLOOKUP-Funktion in VBA
Um zu demonstrieren, wie Sie eine VLOOKUP-Funktion in VBA verwenden können, habe ich hier Beispieldaten. Ich muss den Namen und den Ort der angegebenen Anmelde-ID in einem Meldungsfeld mit VBA anzeigen. Die Daten sind im Bereich A1: K26 verteilt.
Drücken Sie ALT + F11, um VBE zu öffnen und ein Modul einzufügen. Siehe den folgenden Code.
Sub WsFuncitons()
Dim loginID As String
Dim name, city As String
loginID = "AHKJ_1-3357042451"
'Using VLOOKUP function to get name of given id in table
name = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 2, 0)
'Using VLOOKUP function to get city of given id in table
city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0)
MsgBox ("Name: " & name & vbLf & "City: " & city)
End Sub
Wenn Sie diesen Code ausführen, erhalten Sie dieses Ergebnis.
Sie können sehen, wie schnell der VBA das Ergebnis in ein Meldungsfeld druckt. Lassen Sie uns nun den Code untersuchen.
Wie funktioniert es?
1.
Dim loginID As String
Dim Name, Stadt As String
Zuerst haben wir zwei Variablen vom Typ String deklariert, um das von der VLOOKUP-Funktion zurückgegebene Ergebnis zu speichern. Ich habe Variablen vom Typ Zeichenfolge verwendet, da ich sicher bin, dass das von VLOOKUP zurückgegebene Ergebnis ein Zeichenfolgenwert ist. Wenn von Ihrer Arbeitsblattfunktion erwartet wird, dass sie den Werttyp Nummer, Datum, Bereich usw. zurückgibt, verwenden Sie diese Art von Variable, um das Ergebnis zu speichern. Wenn Sie nicht sicher sind, welche Art von Wert von der Arbeitsblattfunktion zurückgegeben wird, verwenden Sie Variablentypvariablen.
2.
loginID = „AHKJ_1-3357042451“
Als Nächstes haben wir die Variable loginID verwendet, um den Suchwert zu speichern. Hier haben wir einen fest codierten Wert verwendet. Sie können auch Referenzen verwenden. Zum Beispiel.
Sie können Range („A2“). Value verwenden, um den Suchwert aus Bereich A2 dynamisch zu aktualisieren.
3.
name = Application.WorksheetFunction.VLookup (loginID, Range („A1: K26“), 2, 0) Hier verwenden wir die VLOOKUP-Funktion, um abzurufen. Wenn Sie nun die Funktion korrigieren und die Klammer öffnen, werden die erforderlichen Argumente angezeigt, die jedoch nicht so aussagekräftig sind wie in Excel. Überzeugen Sie sich selbst.
Sie müssen sich merken, wie und welche Variable Sie verwenden müssen. Sie können jederzeit zum Arbeitsblatt zurückkehren, um die Details der beschreibenden Variablen anzuzeigen.
Hier ist der Suchwert Arg1. Für Arg1 verwenden wir loginID. Die Nachschlagetabelle ist Arg2. Für Arg2 haben wir Range („A1: K26“) verwendet. Beachten Sie, dass wir A2: K26 nicht direkt wie in Excel verwendet haben. Der Spaltenindex ist Arg3.
Für Arg3 haben wir 2 verwendet, da der Name in der zweiten Spalte steht. Der Nachschlagetyp ist Arg4. Wir haben 0 als Arg4 verwendet.
city = Application.WorksheetFunction.VLookup (Anmelde-ID, Bereich („A1: K26“), 4, 0)
Ebenso erhalten wir den Städtenamen.
4.
MsgBox („Name:“ & Name & vbLf & „Stadt:“ & Stadt)
Schließlich drucken wir Name und Stadt mit Messagebox.
Warum die Arbeitsblattfunktion in VBA verwenden? Die Arbeitsblattfunktionen verfügen über immense Rechenleistung, und es ist nicht klug, die Leistungsfähigkeit von Arbeitsblattfunktionen zu ignorieren. Wenn wir beispielsweise die Standardabweichung eines Datensatzes möchten und Sie dafür den gesamten Code schreiben möchten, kann dies Stunden dauern. Wenn Sie jedoch wissen, wie Sie die Arbeitsblattfunktion STDEV.P in VBA verwenden, um die Berechnung auf einmal zu erhalten.
Sub GetStdDev()
std = Application.WorksheetFunction.StDev_P(Range("A1:K26"))
End Sub
Verwenden mehrerer Arbeitsblattfunktionen VBA
Angenommen, wir müssen eine Indexübereinstimmung verwenden, um einige Werte abzurufen. Wie würden Sie nun die Formel in VBA schreiben? Ich denke, Sie werden Folgendes schreiben:
Sub IndMtch() Val = Application.WorksheetFunction.Index(result_range, _ Application.WorksheetFunction.Match(lookup_value, _ lookup_range, match_type)) End Sub
Das ist nicht falsch, aber es ist langwierig. Der richtige Weg, mehrere Funktionen zu verwenden, ist die Verwendung eines With-Blocks. Siehe das folgende Beispiel:
Sub IndMtch() With Application.WorksheetFunction Val = .Index(result_range, .Match(lookup_value, lookup_range, match_type)) val2 = .VLookup(arg1, arg2, arg3) val4 = .StDev_P(numbers) End With End Sub
Wie Sie sehen können, habe ich With block verwendet, um VBA mitzuteilen, dass ich die Eigenschaften und Funktionen von Application.WorksheetFunction verwenden werde. Ich muss es also nicht überall definieren. Ich habe gerade den Punktoperator verwendet, um auf die Funktionen INDEX, MATCH, VLOOKUP und STDEV.P zuzugreifen. Sobald wir die End With-Anweisung verwenden, können wir nicht mehr auf Funktionen zugreifen, ohne vollständig qualifizierte Funktionsnamen zu verwenden.
Wenn Sie also mehrere Arbeitsblattfunktionen in VBA verwenden müssen, verwenden Sie mit Block.
Nicht alle Arbeitsblattfunktionen sind über Application.WorksheetFunction verfügbar. Einige Arbeitsblattfunktionen können direkt in VBA verwendet werden. Sie müssen das Application.WorksheetFunction-Objekt nicht verwenden.
Zum Beispiel Funktionen wie Len (), mit denen die Anzahl der Zeichen in einer Zeichenfolge, links, rechts, Mitte, Trimmen, Versatz usw. ermittelt wird. Diese Funktionen können direkt in VBA verwendet werden. Hier ist ein Beispiel.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) End Sub
Hier haben wir die LEN-Funktion verwendet, ohne das Application.WorksheetFunction-Objekt zu verwenden.
Ebenso können Sie andere Funktionen wie left, right, mid, char usw. verwenden.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) Debug.Print (left(strng,2)) Debug.Print (right(strng,1)) Debug.Print (Mid(strng, 3, 2)) End Sub
Wenn Sie das obige Sub ausführen, wird Folgendes zurückgegeben:
5 He o ll
Also ja Leute, so können Sie die Arbeitsblattfunktion von Excel in VBA verwenden. Ich hoffe, ich war erklärend genug und dieser Artikel hat Ihnen geholfen. Wenn Sie Fragen zu diesem Artikel oder zu anderen VBA-Themen haben, wenden Sie sich an den Kommentarbereich unten. Bis dahin können Sie unten weitere verwandte Themen lesen.
Verwandte Artikel:
link: / excel-makros-und-vba-was-ist-csng-funktion-in-excel-vba [Was ist CSng-Funktion in Excel VBA] | Die SCng-Funktion ist eine VBA-Funktion, die jeden Datentyp in eine Gleitkommazahl mit einfacher Genauigkeit konvertiert („vorausgesetzt, es handelt sich um eine Zahl“). Ich benutze meistens die CSng-Funktion, um textformatierte Zahlen in tatsächliche Zahlen umzuwandeln.
link: / vba-wie-man-die-text-nummer-in-umgekehrt-durch-vba-in-microsoft-excel erhält [wie man text & nummer in umgekehrt über VBA in Microsoft Excel erhält] | * Um die Nummer umzukehren und Text verwenden wir Schleifen und Mid-Funktion in VBA. 1234 wird in 4321 konvertiert, „du“ wird in „uoy“ konvertiert. Hier ist der Ausschnitt.
link: / formatieren-in-vba-format-tabelle-abhängig-von-nummer-formaten-mit-vba-in-microsoft-excel [Formatieren von Daten mit benutzerdefinierten Zahlenformaten mit VBA in Microsoft Excel] | Verwenden Sie dieses VBA-Snippet, um das Zahlenformat bestimmter Spalten in Excel zu ändern. Es verdeckt das Zahlenformat von angegebenem zu angegebenem Format mit einem Klick.
link: / tips-using-worksheet-change-event-to-run-macro, wenn eine Änderung vorgenommen wird [Verwenden des Arbeitsblatt-Änderungsereignisses zum Ausführen eines Makros, wenn eine Änderung vorgenommen wird] | Um Ihr Makro bei jeder Aktualisierung des Arbeitsblatts auszuführen, verwenden wir die Arbeitsblattereignisse von VBA.
link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Makro ausführen, wenn Änderungen am Blatt im angegebenen Bereich vorgenommen wurden] | Verwenden Sie diesen VBA-Code, um Ihren Makrocode auszuführen, wenn sich der Wert in einem angegebenen Bereich ändert. Es erkennt alle Änderungen, die im angegebenen Bereich vorgenommen wurden, und löst das Ereignis aus.
link: / events-in-vba-einfachster-vba-code zum hervorheben der aktuellen zeile und spalte mit [Einfachster VBA-Code zum Hervorheben der aktuellen Zeile und Spalte mit] | Verwenden Sie dieses kleine VBA-Snippet, um die aktuelle Zeile und Spalte des Blattes hervorzuheben.
Beliebte Artikel:
link: / Tastatur-Formel-Verknüpfungen-50-Excel-Verknüpfungen-zur-Steigerung-Ihrer-Produktivität [50 Excel-Verknüpfungen zur Steigerung Ihrer Produktivität] | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Verknüpfungen können Sie noch schneller in Excel arbeiten.
link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Die VLOOKUP-Funktion in Excel] | Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, mit der Werte aus verschiedenen Bereichen und Tabellen gesucht werden.
link: / tips-countif-in-microsoft-excel [COUNTIF in Excel 2016] | Zählen Sie Werte mit Bedingungen, die diese erstaunliche Funktion verwenden. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen.
Die Countif-Funktion ist wichtig, um Ihr Dashboard vorzubereiten.
link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel] | Dies ist eine weitere wichtige Funktion des Dashboards. Auf diese Weise können Sie Werte unter bestimmten Bedingungen zusammenfassen.