VBA Excel: Beispielprogramme. Makros in Excel

Nur wenige Menschen wissen, dass die erste Version des beliebten Microsoft Excel Produkts im Jahr 1985 erschien. Seitdem hat es auf der ganzen Welt verschiedene Modifikationen und Nachfrage von Millionen von Nutzern unterzogen. Allerdings nur viele Arbeit mit ein wenig Fähigkeiten dieser Tabelle und wissen nicht einmal, wie sie könnten das Leben leichter für die Fähigkeit, Excel-Programmierung machen.

Was ist VBA

Programmierung in Excel wird von Visual Basic for Application Programmiersprache durchgeführt, die ursprünglich in der bekanntesten Tabelle von Microsoft gebaut wurden.

Zu seinem Verdienst Experten führen die relative Einfachheit der Entwicklung. Wie die Praxis zeigt, kann VBA die Grundlagen beherrschen, können auch Benutzer, die keine professionelle Programmierkenntnisse haben. Für VBA Funktionen gehören Skriptausführung in der Office-Anwendung Umgebung.

Der Nachteil des Programms sind die mit der Kompatibilität der verschiedenen Versionen verbundenen Probleme. Sie zeichnen sich durch die Tatsache, dass der VBA-Code des Programms auf die Funktionalität bezieht, die in der neuen Version des Produkts vorhanden ist, aber nicht in dem alten. Es gibt auch einen großen Nachteil und zu hohe Offenheit der Code das Gesicht eines Fremden zu ändern. Allerdings Microsoft Office und IBM Lotus Symphony ermöglicht es Benutzern, Verschlüsselung Zugangscode und Passwort Einstellungen zu übernehmen um es anzuzeigen.

Objekte, Sammlungen, Eigenschaften und Methoden

Es ist mit diesen Konzepten, die Sie brauchen, um diejenigen zu verstehen, die in der VBA-Umgebung arbeiten werden. Zunächst einmal müssen Sie verstehen, was das Objekt ist. In Excel, in diesem Akt als ein Blatt, ein Buch und Zellbereich. Diese Objekte haben spezielle Hierarchie, d.h. gehorchen einander.

Leiter unter ihnen ist die Anwendung, die entsprechende Excel-Programm selbst. Gefolgten Arbeitsbücher, Arbeitsblätter und Range. Zum Beispiel auf einem bestimmten Blatt zu Zelle A1 verweisen müssen den Weg weisen, unter Berücksichtigung der Hierarchie.

In Bezug auf das Konzept der „Sammlung“, diese Gruppe von Objekten der gleichen Klasse, in der der Aufzeichnung Chartobjects gegeben. Seine Elemente sind auch Objekte.

Das nächste, was – Eigenschaften. Sie sind ein notwendiges Merkmal eines Objekts. Zum Beispiel für die Range – ist ein Wert oder die Formel.

Methoden – ist der Befehl, um zu zeigen, dass Sie machen wollen. Wenn Code in VBA Schreiben sie von dem Objektpunkt getrennt werden. Zum Beispiel, wie später gezeigt werden wird, sehr oft, wenn in dem „Excel“ verwenden, um Zellen Befehl (1,1) .Select Programmierung. Es bedeutet , dass es notwendig ist , eine Zelle mit den Koordinaten zu wählen (1,1), d.h. A1.

Es wird jedoch oft Selection.ClearContents verwendet. Ihre Umsetzung wird das Löschen der Inhalte der ausgewählten Zelle.

Wie man beginnt

Zunächst einmal wollen Sie die Datei und speichern Sie es schaffen, einen Namen zuweisen und die Art der «Buch Excel-Makro-enabled wählen.“

Dann gehen Sie auf die VB-Anwendung, die ausreicht, um eine Kombination aus «Alt» verwenden Taste und «F11». Next:

• in der Menüleiste am oberen Rand des Fensters klicken Sie auf das Symbol neben dem Excel-Symbol;
• Mudule ausgewählten Befehl;
• halten Sie auf das Symbol mit der Diskette klicken;
• schreiben, sagen wir, einen Code zu skizzieren.

Es sieht aus wie folgt:

Sub – Programm ()

‚Unser Code

End Sub

Bitte beachten Sie, dass die Zeile „‚Unser Code“wird in einer anderen Farbe (grün) markiert. Der Grund für diese Apostroph am Anfang des Strings geliefert, das anzeigt, dass das, was folgt ist ein Kommentar.

Jetzt können Sie einen beliebigen Code schreiben und für sich ein neues Werkzeug in VBA Excel (siehe die Beispielprogramme. Etc.) erstellen. Natürlich diejenigen, die mit den Grundlagen von Visual Basic vertraut sind, wird es viel einfacher sein. Aber auch diejenigen, die es nicht haben, wenn man recht schnell, bequem erhalten können, will.

Makros in Excel

Hinter diesem Namen verbirgt sich Programme in Visual Basic for Application Sprache geschrieben. Somit ist die Programmierung in Excel – ist ein Makro auf den gewünschten Code zu erstellen. Mit dieser Fähigkeit, Microsoft-Tabelle Selbst entwickelt, um die Anforderungen eines bestimmten Benutzer anzupassen. Nachdem behandelt wie Module schaffen für das Schreiben von Makros ist es möglich, mit konkreten Beispielen von VBA Excel-Programm fortzufahren. Es ist am besten mit dem grundlegendsten Codes zu starten.

Beispiel 1

Aufgabe: Schreiben Sie ein Programm, das den Wert des Inhalts einer Zelle und dann schreiben Sie an einem anderen kopiert.

Dafür:

• Öffnen Sie die Registerkarte „Ansicht“;
• Verschieben Sie das Symbol „Makros“;
• schütteln, um den „Record Macro“;
• Füllen geöffnet Form.

Aus Gründen der Einfachheit in dem „Makronamen“ verlassen „Makros1“ und in der „Shortcut-Taste“ eingefügt, beispielsweise hh (dies bedeutet, dass Sie das Beispielprogramm wird ausgeführt werden können «Strg + h» blitz-Team sein). Drücken Sie die Eingabetaste.

Nun, da Sie die Makroaufzeichnung begonnen haben, machen Sie den Inhalt einer Zelle in eine andere auf. Rückkehr zum ursprünglichen Symbol. Klicken Sie auf den „Makro aufzeichnen“. Diese Aktion markiert den Abschluss von Applets.

Next:

• wieder in Gang zu der Zeichenfolge „Makros“;
• wird in der Liste „Macro 1“ ausgewählt ist;
• klicken Sie auf „Run“ (die gleiche Aktion startet Shortcut-Tasten gestartet «Strg + hh»).

Als Ergebnis der Aktion, die während der Aufzeichnung des Makros durchgeführt wurde.

Es macht Sinn, um zu sehen, wie der Code aussieht. Um dies zu tun, gehen Sie zurück auf die Zeichenfolge „Makros“ und klicken Sie auf „Bearbeiten“ oder „Enter“. Als Ergebnis finden sie sich in der VBA-Umgebung. Eigentlich ist der Code selbst zwischen den Linien Makro Makros1 Sub () und End Sub befindet.

Wenn das Kopieren durchgeführt worden ist, beispielsweise aus einer Zelle A1 in Zelle C1, eine der Zeilen Code wird wie Bereich aussieht ( „C1“). Wählen Sie. In der Übersetzung, es sieht aus wie "Range (" C1 "). Wählen Sie", mit anderen Worten, einen Übergang zu VBA Excel, in der Zelle C1.

Ein aktiver Teil des Codes abgeschlossen ActiveSheet.Paste Team. Es bedeutet Inhalt ausgewählte Zelle (in diesem Fall A1) in der ausgewählten Zelle C1 aufzuzeichnen.

Beispiel 2

VBA Zyklen helfen verschiedene Makros in Excel zu erstellen.

VBA Zyklen helfen verschiedene Makros zu erstellen. Angenommen, es ist eine Funktion y = x + x ³ + 3x ² – cos (x). Sie wollen einen Makro für seine Grafiken erstellen. Dies kann nur durch die Verwendung von VBA-Zyklen durchgeführt werden.

Für die erste und Endwert Argument Funktionen übernehmen x1 = 0 und x2 = 10. Darüber hinaus ist es notwendig, eine konstante einzuführen – der Wert für Schritt ändert das Argument und ein Anfangswert für den Zähler.

Alle Beispiele von VBA Excel-Makros werden mit dem gleichen Verfahren erstellt, wie oben dargelegt. In diesem speziellen Fall sieht der Code wie:

Sub – Programm ()

x1 = 1

x2 = 10

Noppe = 0,1

i = 1

Sie Während x1 <x2 (Schleife wird bis zum Ausdruck laufen gilt x1 <x2)

y = x1 + x1 ^ 2 + 3 * x1 ^ 3 – Cos (x1)

Zellen (i, 1) = x1 .Wert (x1 – Wert mit den Koordinaten in dem Speicher geschrieben (i, 1))

Zellen (i, 2) .Wert = y (y – Wert wird in der Zelle mit den Koordinaten geschrieben (i, 2))

i = i + 1 (gültig count);

x1 = x1 + Noppe (Argument an die Schrittgröße geändert wird );

Schleife

End Sub.

Als Ergebnis dieses Versuchs Makro „Excel“ erhalten zwei Spalten, von denen der erste die Werte für x aufgezeichnet werden, und die zweite – auf y.

Dann planen Sie können für „Excel“ auf sie, um den Standard zu bauen.

Beispiel 3

Zum Implementierung Zyklus in VBA Excel 2010 sowie in anderen Versionen, zusammen mit dem bereits reduzierten Do While-Design verwendet für.

Betrachten wir ein Programm, das eine Spalte erstellt. In jeder Zelle wird es entsprechende Zeilennummer aufgezeichnet Quadrate wird. Für die Verwendung des Designs ermöglicht es ganz kurz aufzeichnen, ohne die Verwendung eines Zählers.

Zunächst ist es notwendig, einen Makro zu erstellen, wie oben beschrieben. Als nächstes schreiben Sie den Code selbst. Wir glauben, dass wir in den Werten für die 10 Zellen interessiert sind. Der Code ist wie folgt.

Für i = 1 bis 10 Nächsten

Der Befehl wird in die „menschlichen“ Sprache übertragen werden als „von 1 bis 10 in Schritten von einem wiederholt.“

Wenn die Aufgabe der Säule mit Plätzen, zum Beispiel zu erhalten, werden alle ungeraden Zahlen zwischen 1 und 11, schreiben wir:

Für i = 1 bis 10 Schritt 1 Nächste.

Hier Schritt – Schritt. In diesem Fall ist es gleich zwei. Standardmäßig bedeutet das Fehlen des Wortes in der Schleife, dass der ersten Schritt.

Die Ergebnisse müssen in der Zellzahl (i, 1) gespeichert werden. Dann starten Sie jedes Mal wenn Sie den Zyklus mit einem Anstieg des Wertes von i wird automatisch wachsen und die Zeilennummer Schritt. Somit wird es Code-Optimierung sein.

Im Allgemeinen würde aussehen der Code wie:

Sub – Programm ()

Für i = 1 bis 10 Schritt 1 (kann für i nur geschrieben werden = 1 bis 10)

Zellen (i, 1) = .Wert i ^ 2 (d Quadratwert wird in die Zelle geschrieben (i, 1) i)

Als nächstes (in gewisser Hinsicht spielt die Rolle der Zählereinrichtung und einen weiteren Zyklus – Start)

End Sub.

Wenn es richtig gemacht, einschließlich der Aufnahme und Durchführung von Makros (siehe. Die Anweisungen oben), dann wird es jedes Mal aufgerufen eine bestimmte Größe Spalte erhalten wird (in diesem Fall bestehend aus 10 Zellen).

Beispiel 4

Im Alltag sehr häufig ist es notwendig, diese oder jene Entscheidung in Abhängigkeit von bestimmten Bedingungen zu übernehmen. Kann nicht ohne sie in VBA Excel tun. Beispiele für Programme, bei denen der weitere Verlauf des Algorithmus statt ursprünglich vorgegebenen ausgewählt wird, am häufigsten verwendete Design von If … Then (für schwierige Fälle) Wenn … dann … END If.

Betrachten Sie den Fall. Angenommen, Sie möchten ein Makro für „Excel“ in die Zelle mit den Koordinaten erstellen (1,1) aufgezeichnet wurde:

1 , wenn das Argument positiv ist ;

0 , wenn das Argument Null ist ;

1, wenn das Argument negativ ist .

Die Schaffung eines solchen Makro für „Excel“ beginnt in üblicher Weise, durch die Verwendung von „hot“ Tasten Alt und F11. Weitere den folgenden Code geschrieben:

Sub – Programm ()

x = Zellen (1, 1) .Wert (Dieser Befehl weist den Wert von x – Koordinaten des Zellinhaltes (1, 1)) ,

Wenn x> 0 Then Cells (1, 1) .Value = 1

Wenn x = 0 Then Cells (1, 1) .Value = 0

Wenn x <0 Then Cells (1, 1) .Value = -1

End Sub.

Es bleibt ein Makro und bekommen in „Excel“ Soll-Wert für das Argument laufen.

VBA-Funktionen

Wie Sie ist in der bekanntesten Programm Microsoft Tabellenkalkulationsanwendung bemerkt hat nicht allzu schwierig. Vor allem, wenn Sie lernen, wie VBA-Funktionen zu verwenden. Insgesamt diese Programmiersprache zum Schreiben von Anwendungen in „Excel“ und das Wort, etwa 160 Funktionen, die speziell erstellt. Sie lassen sich in mehrere große Gruppen unterteilt werden. Das:

• Mathematische Funktionen. Ihre Anwendung auf das Argument des Kosinus-Wert erhalten wird, den natürlichen Logarithmus, und so den gesamten Teil.
• Finanzfunktionen. Aufgrund ihrer Verfügbarkeit und Nutzung Programmierung in Excel können Sie effektive Werkzeuge für die Buchhaltung und Finanz Siedlungen erhalten.
• Array Verarbeitungsfunktionen. Dazu gehören Array, IsArray; LBound; UBound.
• VBA Excel-Funktion für die Linie. Dies ist eine ziemlich große Gruppe. Es umfasst zum Beispiel Raumfunktion eine Zeichenfolge mit einer Anzahl von Spalten gleich dem ganzzahligen Argument oder Asc Übertragungen Symbole ANSI-Code zu erzeugen. Alle von ihnen sind weit verbreitet und ermöglichen es Ihnen, mit Zeichenfolge in der „Excel“ für die Arbeit mit diesen Tabellen erstellen Anwendungen viel einfacher zu arbeiten.
• Datentyp-Konvertierungsfunktionen. Beispielsweise gibt CVar Expression Argument, es zu Variant-Datentyp zu konvertieren.
• Datumsfunktionen. Sie erweitern erheblich die Standard-Features von „Excel“. So kehrt WeekdayName Funktion die Namen (vollständige oder teilweise) des Tages der Woche durch seine Nummer. Noch nützlicher ist der Timer. Er gibt die Anzahl der Sekunden, die seit Mitternacht zu einem bestimmten Zeitpunkt des Tages verstrichen sind.
• Funktion ein numerisches Argument in den verschiedenen Zahlensystemen zu konvertieren. Beispielsweise gibt Oct oktale Darstellung der Zahl.
• Formatierungsfunktionen. Die wichtigste davon ist das Format. Es gibt eine Variante mit einem Ausdruck formatiert entsprechend den Anweisungen in der Beschreibung des Formats gegeben.
• und so weiter.

Die Untersuchung der Eigenschaften dieser Funktionen und deren Anwendung deutlich den Umfang des „Excel“ erweitern.

Beispiel 5

Lassen Sie uns versuchen, komplexere Probleme zu lösen. Zum Beispiel:

Dan Papierdokument die tatsächliche Höhe der Kosten des Unternehmens Bericht. benötigt:

• entwickelt ihren Musterabschnitt durch Tabelle „Excel“;
• macht ein VBA-Programm, das fragt für Rohdaten, ihn zu füllen, die notwendigen Berechnungen durchzuführen und füllen in der Vorlage der entsprechenden Zelle.

Betrachten Sie eine der folgenden Lösungen.

Erstellen einer Vorlage

Alle Aktionen werden auf einem Standard-Blatt in Excel durchgeführt. Freie Zellen reserviert für Daten über die Eingabe von Monat, Jahr, Titel-Consumer – Unternehmen, die Höhe der Kosten, deren Höhe des Umsatzes. Da die Zahl der Unternehmen (Unternehmen), bei denen der Bericht nicht aufgezeichnet wird, um die Zellen auf der Grundlage von Werten zu machen und professionellen Namen wird nicht im Voraus reserviert. Arbeitsblatt zu einem neuen Namen zugeordnet. Zum Beispiel: „Օ Report“.

Variablen

So schreiben Sie das Programm automatisch in der Vorlage zu füllen, wählen Sie die Schreibweise. Sie werden für die Variablen verwendet werden:

• NN- Nummer der aktuellen Zeile der Tabelle;
• TP und TF – Plan- und Ist-Umsatz;
• SF und SP – tatsächlich und geplant Gesamtkosten;
• IP und IF – Plan- und Ist-Kosten-Ebene.

Wir bezeichnen mit den gleichen Buchstaben, aber mit einer „Präfix» Itog Anhäufung insgesamt für diese Spalte. Zum Beispiel ItogTP – Im Hinblick auf die Spalte mit dem Titel, „der geplanten Umsatz.“

Lösung des Problems mit VBA-Programmierung

Mit dieser Notation, so erhält man die Formel für die Varianz. Wenn Sie die Berechnung in% durchführen wollen wir (F – P) / P * 100, und in Höhe von – (F – P).

Die Ergebnisse dieser Berechnungen lässt sich am besten nur zu „Excel“ Kalkulationstabelle in den entsprechenden Zellen zu machen.

Für das Ergebnis und die Prognose in der Tat durch Formeln ItogP = ItogP + P und ItogF = ItogF + F.

Bei Abweichungen verwenden = (ItogF – ItogP) / ItogP * 100, wenn die Berechnung wird als Prozentsatz ausgeführt, und wenn der Gesamtwert – (ItogF – ItogP).

Die Ergebnisse wieder in den entsprechenden Zellen aufgenommen, so dass es keine Notwendigkeit, sie zu Variablen zugewiesen werden soll.

Vor dem Start eines Programms erstellen möchten Sie die Arbeitsmappe speichern, beispielsweise unter dem Namen „Otchet1.xls“.

Drücken Sie auf „Erstellen einer Berichtstabelle“ Sie müssen nur 1 Mal drücken, nachdem die Header-Informationen eingeben. Sie sollten und andere Regeln kennen. Insbesondere die Schaltfläche „eine Zeile hinzufügen“ muss jedes Mal gedrückt werden, nachdem für jede Aktivität von Werten in die Tabelle eintragen. Nach der Eingabe aller erforderlichen Daten auf die Schaltfläche „Fertig stellen“ zu drücken, und wechseln Sie dann auf „Excel“ in dem Fenster.

Nun, da Sie wissen, wie das Problem für Excel mit Makros zu lösen. Die Fähigkeit, vba Excel zu verwenden (siehe Beispielprogramme. Oben) und Sie können in der Umgebung der beliebtesten im Moment, das Texteditor arbeiten müssen „Wort“. Insbesondere durch die Aufnahme, wie am Anfang dieses Artikels gezeigt wird, oder durch das Schreiben von Code der Menütasten, durch die viele der Operationen auf dem Text erstellen können durch Drücken der Tasten im Dienst oder durch das „Ansicht“ Symbol und „Makros“ Registerkarte erfolgen.