Excel - Autom. + Makros / GradTUBS / 24.09.18

Makros

Am 24. September 2018 führen wir für die Graduiertenakademie der TU Braunschweig ein Seminar für die Tabellenkalkulation Excel (Automatisierung + Makros) durch.

Hier unsere Ausschreibung:

Dateitypen für Mappen mit Aktiven Elementen
Nutzung von Arbeitsmappenvorlagen
Makros aufzeichnen (Makrorekorder)
VBA-Editor nutzen (Debugging)
Makros manuell anpassen (Editing)
Eigener VBA-Makrocode (Module)
Eigene Excel-Funktionen per VBA Function
VBA-Beispiele

Hier die Rahmendaten zum Seminar:

Ort: Gauß-IT-Zentrum, Hans-Sommer-Straße 65, HS 65.1 (Achtung: Bautätigkeit!)
Zeiten: Mo., 24.09.18; 09.00 - 16.00 Uhr

Ich werde das Thema - in gewohnter Weise - mit täglichen Infos begleiten.
Ihr Trainer Joe Brandes

Montag, 24.09.2018, 09.00 - 16.00 Uhr

Orientierungsphase, TN-Themen, Zeiten/Pausenregelungen

Aus TN-Kreisen GradTUBS (siehe Feedbackbögen) wird als Verbesserung manchmal eine Vorbereitung/Bereitstellung der Inhalte gewünscht. Dem komme ich mit den hier vorbereiteten inhaltlichen Themen für unsere Seminare nach.
Inhaltliche Strukturierung (Curriculi / LZKs / Idee für 1-Tagesseminare)

Excel Seminare	Inhalte
Automatisierung und Makros	Einstieg in Automatisierungen Makros: der Makrorecorder Eigene Funktionen in Excel Einstieg in den VBA-Editor
VBA Programmierung	VBA-Editor VBA Prozeduren Programmierung: Dateien, Zellen, Blätter UserForms - Formulare, Dialoge

Downloads zu VBA-Snippets (Anm.: temporär!) auf
Onedrive-Laufwerk Trainer J. Brandes (inkl. beispielhaften VBA-PDFs aus dem Netz)
Hinweis: manche Bücher zu diesen Themen heißen "... in 14 Tagen ..."

Hinweis auf Online-Resourcen und Tools für die weiteren Arbeiten mit Excel
Erstellung von Screenshots mit Windows 10 Bordmittel (Tastenkombi: Win + Druck; png-Dateien in Ordner Bilder\Bildschirmfotos, bzw. Pictures\Screenshots)
und Bildschirmlupe mit Win + "+" (bzw. Win + "-"; Lupe deaktivieren mit Win + ESC)
früher: Trainer-Tools (Bildschirmlupe "Virtual Magnifiying Glass" und Screenshots erstellen mit "Lightscreen") über Portal portableapps.com (Nachfrage aus Seminar)
Ansprechpartner an der TU Braunschweig - GradTUBS
Freies WikiBook: https://de.wikibooks.org/wiki/VBA_in_Excel (auch als PDF verfügbar)
Skripte des Herdt-Verlag über das RRZN Uni Hannover zum kleinen Preis über das GITZ: (siehe Bestellform für Software und Skripte)
Link: http://www.rrzn.uni-hannover.de/buecher.html

Arbeitsmappen mit Makros - eigener Dateityp: *.xlsm (bzw. *.xltm für Vorlagen)
beim Öffnen muss außerdem noch die Sicherheitswarnung beachtet werden, die dann überhaupt erst die "Aktivierung" der aktiven Inhalte - sprich unsere Makros - ermöglicht. Ohne diese Aktivierung sind die Makros nicht nutzbar!

Version	Dateiformate
Excel 97-2003	xls - das klassische Excel-Sheet (Arbeitsmappen) xlt - die passenden Vorlagen (engl.: Templates)
Excel 2007 / 2010 / ...	xlsx - XML basiertes Dokumentformat (ohne Makros) xltx - die passenden Vorlagen xlsm - XML basierte Mappen inkl. Makros xltm - die passenden Vorlagen inkl. Makros

Makros I (Erste Gehversuche mit Makros über Aufzeichnung von Makros)
Hinweis auf Menüband Entwicklertools: muss über Menüband Datei - Optionen ... oft erst eingeblendet werden
Ribbon Ansicht - Makro - Makro aufzeichnen: Beispiel mit Transponieren von Tabellenbereichen
Makro-Dialogfenster öffnen mittels Makro - Makros anzeigen (Tastenkombination ALT + F8)
Vergabe von Tastenkombination und Symbol in Schnellzugriffsleiste (oben links) oder auch eigene Gruppen in Menübändern
Tipp: nicht mit Maus Tabellenbereiche markieren, sondern mit Tastatur z.B. Umschalten + Strg + Cursortaste(n) Rechts und dann Runter
Hinweis: wenn bei der Auswahl (mit Alt + F8) der Makros die Makronamen nicht sauber aufgelistet sind, sondern noch zusätzliche Pfade/Infos ersichtlich sind, dann sind wahrscheinlich die Makros in der Mappe nicht aktiviert!
VBA-Editor nutzen (siehe Alt + F11; z.B. für die nachfolgenden Codes)
manuelles Einfügen von Sub Routinen (Makros) oder Function (neue eigene Funktionen; Beispiel: NettoMwst)
Wichtig: falls noch keine Struktur "Module" im Projekt-Explorer vorhanden ist, dann einfach mittels Einfügen - Module diese notwendige Struktur erstellen
über einzelne Module lassen sich die Codes sauber organisieren
Tipp: bei Code mit Vorgestellten Optionen bitte jeweils ein einzelnes Modul nutzen!
Debugging: bei Fehlern lässt sich im Editor der fragliche Quellcode begutachten und korrigieren. Für eine weitere Excel/VBA-Nutzung muss man den Fehler beheben oder die Ausführung des Makros stoppen (Stopptaste im Editor)
Arbeitsmappenvorlagen (*.xltm)
Sammeln von Makros und Funktionen in einer Mappe und bereitstellen als Template
Über neue Mappe - Meine Vorlagen lassen sich dann die Mappen inkl. der aktiven Elemente nutzen
Tipp: Unterordner im Templates-Ordner des Office lassen Registerkarten für Meine Vorlagen entstehen!
Spezial: Excel kennt auch "Persönliche Makroarbeitsmappen"
Makros II (Zusatzinfos - Konfiguration/Sicherheit; Vorlagen nutzen)
Konfiguration über Excel-Optionen - Vertrauensstellungscenter - Einstellungen für Vertrauensstellungscenter
Hier: Einstellungen für Makros (möglichst unverändert lassen); Vertrauenswürdige Speicherorte
alternativ erreichbar über Schaltfläche "Makrosicherheit" im Menüband Entwicklertools
Bei Laufwerkspfaden im Netzwerk (UNC) muss extra ein Kontrollkästchen für diese Nutzung aktiviert werden
Tipp: bei den "Vertrauenswürdigen Speicherorten" finden Sie auch Ihre Verzeichnisse für Benutzer und Templates!
Speicherorte auf Netzwerkressourcen müssen noch extra erlaubt werden
Tastenkombinationen:
ALT+F8 öffnet Makro-Dialogfenster;
ALT+F11 öffnet Visual-Basic-Editor
Auszüge aus Übungen: (ein Makro und eine Funktion){code lang:freebasic showtitle:false lines:true hidden:false}'Mit dieser Prozedur, werden alle markierten Zellen summiert.
'Diese Prozedur einfach in ein Modul einfügen und los gehts.
Sub Selection_Summe()
Dim Zelle As Range
For Each Zelle In Selection
If Zelle.Value <> "" And IsNumeric(Zelle.Value) Then
Summe = Summe + Zelle.Value
End If
Next Zelle
MsgBox "Das Ergebnis lautet: " & Summe, vbCritical
End Sub
Public Function NettoMwst(Betrag, Optional SteuerSatz As Single = 0.19)
'Quelle: Helmut Pirklbauer
Dim Netto As Double
Netto = Betrag / (1 + SteuerSatz)
NettoMwst = Excel.Application.Round(Netto, 4)
End Function
{/code}Komplette Beispiele im Seminar bereitgestellt:
MsgBox, InputBox, Public vs. Private Prozeduren / Variablen, Tabellenblätter sortieren/auflisten, ...
Hier noch ein kleiner Code-Schnippsel: {code lang:freebasic showtitle:false lines:true hidden:false}Option Explicit
Private umfang As Single
Private laenge As Single
Private breite As Single
Private Const faktor = 2

Private Sub BerechneUmfang()
' Umfang eines Rechtecks nach der Formel u = 2*(a+b) bestimmen
umfang = faktor * (laenge + breite)
End Sub

Private Sub EingabeDialog()
' Eingabewerte ermitteln
laenge = InputBox("Bitte geben Sie die Länge des Rechtecks ein: ", "Eingabe", 10)
breite = InputBox("Bitte geben Sie die Breite des Rechtecks ein: ", "Eingabe", 5)
End Sub

Sub Berechnung()
' startet den Eingabedialog und die Berechnung
EingabeDialog
BerechneUmfang
MsgBox "Der Umfang des Rechtecks beträgt " & umfang & " Meter.", vbInformation, "Ausgabe"
End Sub{/code}Für weitere vertiefende Beschäftigungen mit dem Thema "Aktive Inhalte, Makros und Formulare mit Excel" hier eine beispielhafte Anleitung im Internet:
VBA Tutorials (Link Übung Eingabemasken mit Excel)
Nutzung von UserForms: hier wichtig, dass die aktiven Elemente / VBA-Codes den Schaltflächen des Formulars zugeordnet werden.
Das eigentliche Formular wird durch eine einfache Schaltfläche im Arbeitsblatt per aufgerufen
Sicherheitscenter
Konfigurationen für die Nutzung von aktiven Inhalten
Vertrauenswürdige Herausgeber, Vertrauenswürdige Speicherorte, Vertrauenswürdige Dokumente, Add-Ins, ActiveX-Einstellungen, Einstellungen für Makros, Geschützte Ansicht, ...
Darstellung der wichtigsten Optionen und Zusammenhänge; Übung für Vertrauenswürdige Speicherorte (inkl. Netzwerk)
TN-Bescheinigungen, Feedback-Bögen, Letzte Fragen