Wie du in VBA die Arbeitsblattfunktionen einsetzt

Die meisten Arbeitsblattfunktionen von Excel können auch in der VBA-Programmierung verwendet werden.

Die Beispiele in diesem Beitrag werden für jede Aktion ein eigenes Makro zeigen, dennoch wird das in der Praxis nicht so sein. Es wäre unsinnig, z.B. eine Summe mit einem Makro errechnen zu lassen. Das kann natürlich einfacher mit der normalen Excel-Funktion SUMME erfolgen.

Es kann in einem umfangreichen Makro aber durchaus sein, dass auch mal eine Summe gebildet werden muss. Welcher Code dann anzusetzen ist, zeige ich im Folgenden.

1. Vorbemerkungen

Bevor Funktionen in einen VBA-Code eingebaut werden, ist es wichtig zu wissen, welchen Namen sie im Englischen haben.

Dazu gibt es im Internet ein paar Sites, die eine Übersicht liefern:

http://excelnova.org/excel-ressourcen/excel-formeln-ubersetzt-englisch-deutsch/

http://de.excel-translator.de/funktionen/

und ganz aktuell

http://blog.soprani.at/2016/02/19/excel-funktionen-deutsch-englisch/

Eine Liste der in VBA verwendbaren Funktionen findest du hier:

http://www.herber.de/mailing/vb/html/xlmsclistofworksheetfunctions.htm

Der Beitrag behandelt zunächst das Vorgehen, wenn eine Arbeitsblattfunktion in eine Zelle eingefügt werden soll.

Dazu gibt es jeweils drei unterschiedliche Formula-Eigenschaften des Range-Objektes, diese werden nacheinander gezeigt.

Fast alle Beispiele zeige ich mit der Summenfunktion. Jede andere Funktion ist genauso zu verwenden.

Wenn du die Makros nachbauen willst, speichere eine neue Excel-Datei mit der Endung „xlm“ ab. Gehe mit Alt + F11 in die VBA-Oberfläche, füge über Einfügen/Modul ein solches ein und schreibe die vorgeschlagenen oder die eigenen Makros. Mit Alt + Q verlässt du die Oberfläche wieder und befindest dich in deinem Arbeitsblatt.

2.Summe über einen zusammenhängenden Bereich

Das Beispiel enthält fünf Zahlenwerte, die summiert werden sollen.

Die Summe soll in F12 errechnet werden. Schreibe also den jeweiligen Code in ein Modul und füge dir im Arbeitsblatt eine Schaltfläche ein, der du das jeweilige Makro zuweist.

Makrorecorder

Den ersten Versuch unternehme ich mit dem Makrorecorder. Wie der zu starten ist, habe ich in vorhergehenden VBA-Beiträgen gezeigt.

Ich starte den Recorder, aktiviere die Zelle F12 und schreibe =SUMME(F7:F11). Dann beende ich die Aufzeichnung und schau mir an, wie der Code aussieht.

Sub Makro1()
‚
‚ Makro1 Makro
‚

‚
Range(„F12“).Select
ActiveCell.FormulaR1C1 = „=SUM(R[-5]C:R[-1]C)“
Range(„F13“).Select
End Sub

Nicht so toll, oder?

Zur Erklärung:

R[-5] aktiviert war F12
davon 5 Zeilen (Rows) zurück
=F7
C gleiche Spalte = F
R[-1] aktiviert war F12
davon 1 Zeilen (Rows) zurück
=F11
C gleiche Spalte = F

heißt also: F7:F11
oder in R1C1: R7C6:R11C6
(7.Zeile/6.Spalte : 11. Zeile/6.Spalte

Das gefällt mir so nicht unbedingt, deshalb schreibe ich den Code in der ersten Variante so:

FormulaR1C1

Sub Summe1()
Range(„F12“).Select
ActiveCell.FormulaR1C1=“=SUM(R7C6:R11C6)“
End Sub

oder

Sub Summe2()
Cells(12,6).FormulaR1C1=“=SUM(R7C6:R11C6)“
End Sub

Verwendest du Range, muss die Zelle zunächst aktiviert werden, errst dann kann die Formel geschrieben werden. Verwendest du Cells, geht das gleich.

Klicke auf die Schaltfläche und in F12 wird das Ergebnis „299“ stehen.

Formula

Mit Formula habe ich eine Schreibweise, die mir besser gefällt. Diese Schreibweise empfehle ich, wenn du möchtest, dass auch im Ausland dein Code verstanden werden soll.

Sub Summe3()
Range(„F12“).Select
ActiveCell.Formula = „=SUM($F$7:$F$11)“
End Sub

oder

Sub Summe4()
Cells(12,6).Formula= „=SUM($F$7:$F$11)“
End Sub

Ergebnis auch hier: 299

FormulaLocal

Wenn der Code nur innerhalb des deutsprachigen Raums verstanden werden soll, kann auch diese Schreibweise verwendet werden. FormulaLocal bedeutet im deutsprachigen Raum, dass die deutsche Schreibweise verwendet wird.

Sub Summe5()
Range(„F12“).Select
ActiveCell.FormulaLocal = „=SUMME($F$7:$F$11)“
End Sub

oder

Sub Summe6()
Cells(12,6).FormulaLocal= „=SUMME($F$7:$F$11)“
End Sub

Ergebnis auch hier: 299

Bei allen bisherigen Varianten steht nach Lauf des Makros in F12 immer zu lesen:

=SUMME($F$7:$F$11)

2. Summe über einzelne Zellen

Die Zahlenwerte sollen so verteilt sein:

Die Zielzelle soll D70 sein.

Ich beginne auch hier mit dem Makrorecorder:

Sub Makro2()
‚
‚ Makro2 Makro
‚

‚
Range(„D70“).Select
ActiveCell.FormulaR1C1 = „=SUM(R[-5]C,R[-4]C[1],R[-3]C[2],R[-3]C,R[-2]C[1])“
Range(„D71“).Select
End Sub

Natürlich gefällt mir auch das nicht, sieht viel zu kompliziert aus. Aber der Recorder hilft immer, wenn es mal irgendwie nicht weiter geht. Deshalb verdamme ich ihn nicht.

FormulaR1C1

Sub Summe7()
Range(„D70“).Select
ActiveCell.FormulaR1C1=“=SUM(R65C4,R66C5,R67C4,R67C6,R68C5)“
End Sub

oder

Sub Summe8()
Cells(70,4).FormulaR1C1=“=SUM(R65C4,R66C5,R67C4,R67C6,R68C5)“
End Sub

In D70 steht nach Lauf eines der Makros das Ergebnis: 299

Formula

Mit der Eigenschaft Formula schreibe ich die Makros so. Beachte, dass zwischen den Werten kein Semikolon, sondern jeweils ein Komma zu schreiben ist. Leerzeichen sind unbedingt zu vermeiden.

Sub Summe9()
Range(„D70“).Select
ActiveCell.Formula = „=SUM(D65,E66,D67,F67,E68)“
End Sub

oder

Sub Summe10()
Cells(70,4).Formula= „=SUM(D65,E66,D67,F67,E68)“
End Sub

Auch diese Makros errechnen die Summe mit 299.

FormulaLocal

Mit FormulaLocal schließlich verwende ich wieder die deutsche Schreibweise.

Sub Summe11()
Range(„D70“).Select
ActiveCell.FormulaLocal = „=SUMME(D65;E66;D67;F67;E68)“
End Sub

oder

Sub Summe12()
Cells(70,4).FormulaLocal= „=SUMME(D65;E66;D67;F67;E68)“
End Sub

Das Rechenergebnis lautet auch mit diesen Makros jeweils 299.

In D70 steht bei allen Varianten =SUMME(D65;E66;D67;F67;E68).

3. Summe von Variablen und einem Einzelwert

Im Arbeitsblatt sollen zwei Zellen eine Wertezuweisung für Variablen enthalten. Beide Variablen und ein zusätzlicher Einzelwert sollen dann per Makro summiert werden.

Hier müssen zusätzlich die Variablen im Makro definiert werden und eine Zuweisung erfolgen. Die Variablen zeigen z.B. Produktionsmengen zweier Werke in Stück.

In Zelle H134 soll das Berechnungsergebnis stehen.

Makrorecorder

Der Einsatz des Makrorecorders entfällt hier, die Variablen müssen erst definiert werden,
eine Aufzeichnung erfolgt nicht.

FormulaR1C1

Sub Summe13()
Dim intWerk1 As Integer
Dim intWerk2 As Integer
intWerk1=Range(„G131“).Value
intWerk2=Range(„I131“).Value
Range(„H134“).Select
ActiveCell.FormulaR1C1=“=SUM(“ & intWerk1 & „,“ & intWerk2 & „,R131C11)“
End Sub

oder

Sub Summe14()
Dim intWerk1 As Integer
Dim intWerk2 As Integer
intWerk1=Range(„G131“).Value
intWerk2=Range(„I131“).Value
Cells(134,8).FormulaR1C1=“=SUM(“ & intWerk1 & „,“ & intWerk2 & „,R131C11)“
End Sub

Die R1C1-Schreibweise kommt nur beim Einzelwert in Betracht.

Die Variablen intWerk1 und intWerk2 wurden mit Dim deklariert, danach wurden ihnen die Zellinhalte von G131 und I131 zugewiesen.

Ergebnis in H134: 5570

Formula

Mit der Eigenschaft Formula müssen die Variablen ebenfalls deklariert werden und eine Zuweisung erhalten.

Sub Summe15()
Dim intWerk1 As Integer
Dim intWerk2 As Integer
intWerk1=Range(„G131“).Value
intWerk2=Range(„I131“).Value
Range(„H134“).Select
ActiveCell.Formula =“=SUM(“ & intWerk1 & „,“ & intWerk2 & „,K131)“
End Sub

oder

Sub Summe16()
Dim intWerk1 As Integer
Dim intWerk2 As Integer
intWerk1=Range(„G131“).Value
intWerk2=Range(„I131“).Value
Cells(134,8).Formula=“=SUM(“ & intWerk1 & „,“ & intWerk2 & „,K131)“
End Sub

Ergebnis nach Makrolauf: 5570

FormulaLocal

In bekannter Art und Weise schreibe ich auch diese beiden Makros nieder.

Sub Summe17()
Dim intWerk1 As Integer
Dim intWerk2 As Integer
intWerk1=Range(„G131“).Value
intWerk2=Range(„I131“).Value
Range(„H134“).Select
ActiveCell.FormulaLocal = „=SUMME(intWerk1;intWerk2;K131)“
End Sub

oder

Sub Summe18()
Dim intWerk1 As Integer
Dim intWerk2 As Integer
intWerk1=Range(„G131“).Value
intWerk2=Range(„I131“).Value
Cells(134,8).FormulaLocal= „=SUMME(intWerk1;intWerk2;K131)“
End Sub

Als Ergebnis erhalte ich wieder 5570.

In H134 ist diese Formel zu lesen: =SUMME(intWerk1;intWerk2;K131).

4. Funktion nicht in Zelle, sondern in Variable

Wenn eine Arbeitsblattfunktion nicht in eine Zelle eingefügt werden soll, sondern das Ergebnis in eine Variable eingeht, dann wird die WorksheetFunction verwendet.

Im ersten Beispiel soll das Minimum aus dem Datenbereich F7:F11 gefunden und über eine Meldebox bekannt gegeben werden. Für Minimum wird im Englischen die Funktion „Min“ verwendet.

Sub Minimum1()
Dim rngWerte As Range
Set rngWerte = Worksheets(„Tabelle1“).Range(„F7:F11“)
Ergebnis = Application.WorksheetFunction.Min(rngWerte)
MsgBox Ergebnis
End Sub

Habe ich das Makro gestartet, kommt auch schon die MsgBox und teilt mir das Ergebnis mit: 37

Im zweiten Beispiel habe ich einen Bereich, der verschiedene Buchstabenfolgen enthält. Ich will mit einem Makro heraus finden, an welcher Position mein Suchbegriff „AE“ in der Liste steht.

Mit Excel würde ich die Funktion VERGLEICH wählen, mit VBA tue ich nichts anderes. Nur hier heißt die Funktion „Match“.

So sieht der Code aus:

Sub Vergleich1()
Dim rngReihe As Range
Set rngReihe = Worksheets(„Tabelle1“).Range(„B228:B235“)
Position = Application.WorksheetFunction.Match(Range(„C228“), rngReihe, 0)
MsgBox Position
End Sub

Wie bin ich vorgegangen?

Zunächst habe ich eine Variable vom Typ Range deklariert. Mit dem Befehl Set weise ich der Variablen den Bereich B228:B235 zu.

Die Variable nenne ich Position, ohne sie vorher zu deklarieren. D.h. sie erhält automatische den Typ Variant zugewiesen.

Die MsgBox schließlich gibt mir die Position der Buchstabenfolge aus C228 bekannt: 5.

Statt das Ergebnis nur über die Box bekannt zu geben, könnte mit diese Zahl natürlich auch weiter operiert werden.

Damit bin ich am Ende dieses Beitrages angekommen. Unzählige weitere Beispiele, auch komplizierter, ließen sich finden.

Probiere doch selbst mal ein paar Funktionen aus und lasse mich wissen, welche Erfahrungen du gemacht hast.