Die Funktionen wie SVERWEIS, ZÄHLENWENN, SUMIF werden als Arbeitsblattfunktionen bezeichnet. Im Allgemeinen sind die Funktionen, die in Excel vordefiniert und auf einem Arbeitsblatt verwendet werden können, Arbeitsblattfunktionen. Sie können den Code hinter diesen Funktionen in VBA nicht ändern oder sehen.
Andererseits sind die benutzerdefinierten Funktionen und VBA-spezifischen Funktionen wie MsgBox oder InputBox VBA-Funktionen.
Wir alle wissen, wie man VBA-Funktionen in VBA verwendet. Aber was ist, wenn wir SVERWEIS in einer VBA verwenden möchten. Wie machen wir das? In diesem Artikel werden wir genau das untersuchen.
Verwenden von Arbeitsblattfunktionen in VBA
Um auf die Arbeitsblattfunktion zuzugreifen, verwenden wir eine Application-Klasse. Fast alle Arbeitsblattfunktionen sind in der Application.WorksheetFunction-Klasse aufgeführt. Und mit dem Punktoperator können Sie auf alle zugreifen.
Schreiben Sie in einem beliebigen Unterordner Application.WorksheetFunction. Und beginnen Sie mit dem Schreiben des Namens der Funktion. VBAs Intellisense zeigt den Namen der verfügbaren Funktionen an. Sobald Sie den Funktionsnamen gewählt haben, werden die Variablen wie bei jeder Excel-Funktion abgefragt. Sie müssen jedoch Variablen in einem für VBA verständlichen Format übergeben. Wenn Sie beispielsweise einen Bereich A1:A10 übergeben möchten, müssen Sie ihn als Bereichsobjekt wie Range("A1:A10") übergeben.
Lassen Sie uns also einige Arbeitsblattfunktionen verwenden, um es besser zu verstehen.
So verwenden Sie die SVERWEIS-Funktion in VBA
Um zu demonstrieren, wie Sie eine SVERWEIS-Funktion in VBA verwenden können, habe ich hier Beispieldaten. Ich muss den Namen und die Stadt 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" 'Verwenden der SVERWEIS-Funktion, um den Namen der angegebenen ID im Tabellennamen abzurufen = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26" ), 2, 0) 'Verwenden der SVERWEIS-Funktion zum Abrufen der Stadt der angegebenen ID in der Tabelle city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0) MsgBox ("Name: " & name & vbLf & "Stadt: " & Stadt) 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.
LoginID als String dimmen
Dim name, city As String
Zuerst haben wir zwei Variablen vom Typ String deklariert, um das von der SVERWEIS-Funktion zurückgegebene Ergebnis zu speichern. Ich habe Variablen vom Typ String verwendet, weil ich sicher bin, dass das von SVERWEIS zurückgegebene Ergebnis ein String-Wert ist. Wenn von Ihrer Arbeitsblattfunktion erwartet wird, dass sie eine Zahl, ein Datum, einen Bereich usw. eines Werttyps zurückgibt, verwenden Sie diese Art von Variable, um das Ergebnis zu speichern. Wenn Sie sich nicht sicher sind, welche Art von Wert von der Arbeitsblattfunktion zurückgegeben wird, verwenden Sie Variablen vom Typ Variant.
2.
loginID = "AHKJ_1-3357042451"
Als Nächstes haben wir die Variable loginID verwendet, um den Nachschlagewert zu speichern. Hier haben wir einen hartcodierten Wert verwendet. Sie können auch Referenzen verwenden. Zum Beispiel. Sie können Range("A2").Value verwenden, um den Nachschlagewert aus dem Bereich A2 dynamisch zu aktualisieren.
3.
name = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 2, 0)
Hier verwenden wir die SVERWEIS-Funktion, um zu erhalten. Wenn Sie jetzt die Funktion rechts und die Klammer öffnen, werden die erforderlichen Argumente angezeigt, aber nicht so beschreibend 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 beschreibenden Variablendetails anzuzeigen.
Hier ist der Nachschlagewert 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 Lookup-Typ ist Arg4. Wir haben 0 als Arg4 verwendet.
city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0)
Ebenso erhalten wir den Stadtnamen.
4.
MsgBox ("Name: " & Name & vbLf & "Stadt: " & Stadt)
Schließlich drucken wir Name und Stadt mit Messagebox.
Warum die Arbeitsblattfunktion in VBA verwenden?
Die Arbeitsblattfunktionen besitzen immense Rechenleistung und es ist nicht schlau, die Leistungsfähigkeit der Arbeitsblattfunktionen zu ignorieren. Wenn wir beispielsweise die Standardabweichung eines Datensatzes benötigen und Sie dafür den gesamten Code schreiben möchten, kann dies Stunden dauern. Aber wenn Sie wissen, wie Sie die Arbeitsblattfunktion STDEV.P in VBA verwenden, um die Berechnung in einem Rutsch zu erhalten.
Sub GetStdDev() std = Application.WorksheetFunction.StDev_P(Range("A1:K26")) End Sub
Verwenden mehrerer Arbeitsblattfunktionen VBA
Nehmen wir an, wir müssen eine Indexübereinstimmung verwenden, um einige Werte abzurufen. Wie würden Sie nun die Formel in VBA schreiben? Das wirst du wohl 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 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, habe ich den 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, SVERWEIS und STDEV.P zuzugreifen. Sobald wir die End With-Anweisung verwenden, können wir nicht auf Funktionen zugreifen, ohne vollständig qualifizierte Funktionsnamen zu verwenden.
Wenn Sie also mehrere Arbeitsblattfunktionen in VBA verwenden müssen, verwenden Sie with block.
Nicht alle Arbeitsblattfunktionen sind über Application.WorksheetFunction verfügbar
Einige Arbeitsblattfunktionen stehen direkt zur Verwendung in VBA zur Verfügung. Sie müssen das Application.WorksheetFunction-Objekt nicht verwenden.
Zum Beispiel Funktionen wie Len(), die verwendet werden, um die Anzahl der Zeichen in einem String zu erhalten, links, rechts, Mitte, Trimmen, Offset usw. Diese Funktionen können direkt in VBA verwendet werden. Hier ist ein Beispiel.
Sub GetLen() Strng = "Hallo" Debug.Print (Len(strng)) End Sub
Sehen Sie, hier haben wir die LEN-Funktion verwendet, ohne das Application.WorksheetFunction-Objekt zu verwenden.
Ebenso können Sie andere Funktionen wie links, rechts, Mitte, Zeichen usw. verwenden.
Sub GetLen() Strng = "Hallo" Debug.Print (Len(strng)) Debug.Print (left(strng,2)) Debug.Print (right(strng,1)) Debug.Print (Mid(strng, 3, 2)) Ende Sub
Wenn Sie das obige Untermenü ausführen, wird Folgendes zurückgegeben:
5 Er 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 dir geholfen. Wenn Sie Fragen zu diesem Artikel oder zu anderen VBA-bezogenen Themen haben, stellen Sie diese im Kommentarbereich unten. Bis dahin können Sie weiter unten über andere verwandte Themen lesen.
Was ist die CSng-Funktion in Excel VBA? | Die SCng-Funktion ist eine VBA-Funktion, die jeden Datentyp in eine Gleitkommazahl mit einfacher Genauigkeit umwandelt ("sofern es sich um eine Zahl handelt"). Ich verwende hauptsächlich die CSng-Funktion, um textformatierte Zahlen in tatsächliche Zahlen umzuwandeln.
So erhalten Sie Text und Zahlen in umgekehrter Richtung über VBA in Microsoft Excel | Um Zahlen und Text umzukehren, verwenden wir Schleifen und die Mid-Funktion in VBA. 1234 wird in 4321 umgewandelt, "du" wird in "uoy" umgewandelt. Hier ist der Ausschnitt.
Formatieren Sie Daten mit benutzerdefinierten Zahlenformaten mit VBA in Microsoft Excel | Um das Zahlenformat bestimmter Spalten in Excel zu ändern, verwenden Sie dieses VBA-Snippet. Es wandelt das Zahlenformat vom angegebenen in das angegebene Format mit einem Klick um.
Verwenden des Arbeitsblattänderungsereignisses zum Ausführen eines Makros, wenn eine Änderung vorgenommen wird | Um Ihr Makro bei jeder Blattaktualisierung auszuführen, verwenden wir die Arbeitsblattereignisse von VBA.
Makro ausführen, wenn eine Änderung am Blatt im angegebenen Bereich vorgenommen wurde | Verwenden Sie diesen VBA-Code, um Ihren Makrocode auszuführen, wenn sich der Wert in einem angegebenen Bereich ändert. Es erkennt jede Änderung im angegebenen Bereich und löst das Ereignis aus.
Einfachster VBA-Code zum Hervorheben der aktuellen Zeile und Spalte mit | Verwenden Sie dieses kleine VBA-Snippet, um die aktuelle Zeile und Spalte des Blatts hervorzuheben.
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
Die SVERWEIS-Funktion in Excel | Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
ZÄHLENWENN in Excel 2016 | Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel | Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.