VBA-Arbeitsblattfunktion | Wie verwende ich WorksheetFunction in VBA?

Excel VBA-Arbeitsblattfunktionen

Die Arbeitsblattfunktion in VBA wird verwendet, wenn auf ein bestimmtes Arbeitsblatt verwiesen werden muss. Normalerweise wird beim Erstellen eines Moduls der Code im aktuell aktiven Arbeitsblatt der Arbeitsmappe ausgeführt. Wenn wir den Code jedoch in dem bestimmten Arbeitsblatt ausführen möchten, verwenden wir die Arbeitsblattfunktion. Diese Funktion hat verschiedene Verwendungszwecke und Anwendungen in VBA.

Das Beste an VBA ist, wie wir Formeln im Arbeitsblatt verwenden, ähnlich hat auch VBA seine eigenen Funktionen. Wenn dies das Beste ist, dann hat es auch eine schöne Sache: "Wir können Arbeitsblattfunktionen auch in VBA verwenden".

Ja!!! Sie haben es richtig gehört, wir können auch in VBA auf Arbeitsblattfunktionen zugreifen. Wir können beim Schreiben des Codes auf einige der Arbeitsblattfunktionen zugreifen und ihn zu einem Teil unseres Codes machen.

Wie verwende ich Arbeitsblattfunktionen in VBA?

Sie können diese VBA WorksheetFunction-Vorlage hier herunterladen - VBA WorksheetFunction-Vorlage

Im Arbeitsblatt beginnen alle Formeln mit dem Gleichheitszeichen (=). Ähnlich wie bei der VBA-Codierung sollten wir für den Zugriff auf Arbeitsblattformeln das Wort „WorksheetFunction“ verwenden.

Bevor Sie eine Arbeitsblattformel eingeben, müssen Sie den Objektnamen „WorksheetFunction“ angeben und einen Punkt (.) Einfügen. Anschließend erhalten Sie eine Liste aller verfügbaren Funktionen unter diesem Objekt.

In diesem Artikel konzentrieren wir uns ausschließlich auf die Verwendung der Arbeitsblattfunktion in der VBA-Codierung, um Ihr Codierungswissen zu verbessern.

# 1 - Einfache SUM-Arbeitsblattfunktionen

Ok, um mit den Arbeitsblattfunktionen zu beginnen, wenden Sie die einfache SUMME-Funktion in Excel an, um Zahlen aus dem Arbeitsblatt hinzuzufügen.

Angenommen, das Arbeitsblatt enthält monatliche Umsatz- und Kostendaten wie die folgende.

In B14 und C14 müssen wir die Summe der oben genannten Zahlen ermitteln. Führen Sie die folgenden Schritte aus, um die Anwendung der Funktion "SUMME" in Excel VBA zu starten.

Schritt 1: Erstellen Sie einen einfachen Excel-Makronamen.

Code:

 Sub Worksheet_Function_Example1 () End Sub

Schritt 2: Da wir das Ergebnis in Zelle B14 benötigen, starten Sie den Code als Bereich („B14“). Wert =

Code:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = End Sub

Schritt 3: In B14 benötigen wir den Wert als Ergebnis der Summe der Zahlen. Um über das Arbeitsblatt auf die SUMME-Funktion zuzugreifen, starten Sie den Code als "WorksheetFunction".

Code:

Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction. End Sub

Schritt 4: Sobald Sie einen Punkt (.) Setzen, werden die verfügbaren Funktionen angezeigt. Wählen Sie also SUMME aus.

Code:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum End Sub

Schritt 5: Geben Sie nun die Referenz der obigen Zahlen an, dh Bereich („B2: B13“).

Code:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum (Range ("B2: B13")) End Sub

Schritt 6: Wenden Sie für die nächste Spalte den ähnlichen Code an, indem Sie die Zellreferenzen ändern.

Code:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum (Range ("B2: B13")) Range ("C14"). Value = WorksheetFunction.Sum (Range ("C2: C13")) End Sub

Schritt 7: Führen Sie diesen Code nun manuell oder mit der Taste F5 aus, um eine Gesamtsumme in B14- und C14-Zellen zu erhalten.

Wow, wir haben unsere Werte. Eine Sache, die Sie hier beachten müssen, ist, dass wir keine Formel im Arbeitsblatt haben, sondern nur das Ergebnis der Funktion „SUM“ in VBA erhalten haben.

# 2 - Verwenden Sie VLOOKUP als Arbeitsblattfunktion

Wir werden sehen, wie man VLOOKUP in VBA verwendet. Angenommen, unten sind die Daten aufgeführt, die Sie in Ihrem Excel-Blatt haben.

In der E2-Zelle haben Sie eine Dropdown-Liste aller Zonen erstellt.

Basierend auf der Auswahl, die Sie in der E2-Zelle getroffen haben, müssen wir den PIN-Code für die jeweilige Zone abrufen. Aber diesmal über VBA VLOOKUP, nicht über das Arbeitsblatt VLOOKUP. Befolgen Sie die folgenden Schritte, um VLOOKUP anzuwenden.

Schritt 1: Erstellen Sie einen einfachen Makronamen in der Unterprozedur.

Code:

 Sub Worksheet_Function_Example2 () End Sub

Schritt 2: Wir brauchen das Ergebnis in der F2-Zelle. Starten Sie den Code also als Bereich ("F2"). Wert =

Code:

 Sub Worksheet_Function_Example2 () Range ("F2"). Value = End Sub

Schritt 3: Um auf die Arbeitsblattfunktion zuzugreifen, startet VLOOKUP den Code als "WorksheetFunction.VLOOKUP".

Code:

 Sub Worksheet_Function_Example2 () Range ("F2"). Value = WorksheetFunction.Vlookup (End Sub

Schritt 4: Eines der Probleme hier ist, dass die Syntax Ihnen keine Anleitung zur Arbeit mit VLOOKUP gibt. Sie müssen absolut sicher sein, an welcher Syntax Sie arbeiten.

Die erste Syntax von VLOOKUP lautet "Lookup Value". In diesem Fall ist unser Suchwert der E2-Zellenwert. Schreiben Sie den Code daher als Bereich („E2“). Wert

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value, End Sub

Step 5: Now the second argument is our table array, in this case, our table array range is from A2 to B6. So the code will be Range (“A2:B6”)

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"), End Sub

Step 6: The Third argument will be from which column we need the data from the table array. Here we need the data from the 2nd column, so the argument will be 2.

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"),2, End Sub

Step 7: The final argument is range lookup, we need an exact match so the argument is zero (0).

Code:

 Sub Worksheet_Function_Example2() Range("F2").Value = WorksheetFunction.VLookup(Range("E2").Value, Range("A2:B6"), 2, 0) End Sub

So, we are done with the coding part. Now go to the worksheet and select any of the range.

Now go to your coding module and run the macro Using F5 key or manually to get the pin code of the selected zone.

We cannot go back and run the macro every time, so let’s assign a macro to shapes. Insert one of the shapes in a worksheet.

Add a text value to inserted shape.

Now right click and assign the macro name to this shape.

Click on ok after selecting the macro name.

Now, this shape holds the code of our VLOOKUP formula. So whenever you change the zone name click on the button, it will update the values.

Things to Remember

To access worksheet functions we need to write the word “WorksheetFunction” or “Application.WorksheetFunction”
We don’t have access to all the functions only a few.
We don’t see the actual syntax of worksheet functions, so we need to be absolutely sure of the function we are using.