Eine Liebeserklärung an MS Excel
Was ist denn das? Ich erhalte eine Datei, wechsle in den VBA-Editor, wo ich sämtliche Module und Formulare sehe:
Allerdings möchte ich einen Verweis ändern. Und dann das – ein Kennwirt wird verlangt:
Der Grund: man kann in den Projekteigenschaften nur ein Kennwort vergeben, ohne die Anzeige zu sperren. Wer macht denn so etwas? Entweder alles oder gar nichts!
Hallo Rene,
die Excel die wir überarbeitet haben laufen super, und die Kollegen machen jetzt sehr viel auch selbst.
Ich habe jetzt hier das Problem, dass hier ein Protokoll über den Button „PDF mailen“ versendet werden soll, allerdings erkennt Office365 / Excel kein PDF-Add in.
Hast du da einen Tipp welches Add in wir verwenden sollen?
Früher hatten wir das Tool PDF Gotomaxx in Einsatz nur das hat dieses ADD-In nicht mehr in der neusten Version enthalten.
LG
Mario
Hallo Mario,
schön von dir zu hören. Und: schön, das ihr sehr viel selbst macht und hinbekommt.
Ich verwende seit einer Weil die interne PDF-Funktion von Office – du kannst ja die Datei speichern als PDF. Erstaunlicherweise stellt Excel VBA die PDF-Exportfunktion nur für das Blatt zur Verfügung. Also:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
„D:\Eigene Dateien\xy.pdf“, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
Ich habe früher auch mit Add-Ins gearbeitet … ist aber mühsam …
Hilft das?
schöne Grüße aus München und ein schönes Wochenende
Rene
Eine hübsche Frage letzte Woche auf dem Excelstammtisch: In einer Arbeitsmappe befinden sich mehrere Tabellenblätter. Ich arbeite auf einem Blatt, wechsel zu einem anderen, arbeite dort und möchte nun zum ersten Blatt zurückspringen, auf dem ich zuvor gearbeitet habe. In Word gibt es dafür eine Taste: [Umschalt] + [F5]. Und Excel? Leider nichts. Also ein kleines Makro:
In einem Modul deklariere ich eine globale Stringvariable und verwende sie, um zu dem Blatt zu springen:
Public strBlattname As String
Sub GeheZuLetztemBlatt()
On Error Resume Next
ActiveWorkbook.Sheets(strBlattname).Activate
End Sub
Und nun in „DieseArbeitsmappe“. Im Ereignis
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
strBlattname = Sh.Name
End Sub
„merkt“ sind VBA nun in der Variablen strBlattnamen den Namen des Blattes, von welchem du aus weggesprungen bist. Beim Öffnen der Datei kann dieses Makro noch auf eine Taste gelegt werden, beispielsweise Umschalt + F5:
Private Sub Workbook_Open()
Application.OnKey „+{F5}“, „GeheZuLetztemBlatt“
End Sub
In dieser Datei funktioniert es. Man kann es auch als Add-In speichern, damit es in jeder Datei funktioniert. Dazu muss man ein Public WithEvents … deklarieren.
Perfide!
gestern war ein Freund von mir bei mir – wir haben ein paar Excel-Probleme diskutiert. Er wollte „nochmal“ wissen, wie man in Excel Tabellenblätter „verstecken“ kann. „Nochmal“, weil ich es ihm bereits gezeigt hatte.
Kein Problem: Wir wechseln in den Visual Basic-Editor und schalten in den Eigenschaften die Sichtbarkeit des Blattes von xlSheetVisible auf xlSheetVeryHidden. Ich erkläre ihm den Unterschied zwischen xlSheetHidden und xlSheetVeryHidden – xlSheetHidden kann vom Anwender in Excel wieder eingeblendet werden; xlSheetVeryHidden dagegen nicht.
Und dann zeigt ich Axel, wie man einen Kennwortschutz auf das VBA-Projekt legen kann: über Kontextmenü in den Eigenschaften:
Gespeichert, geschlossen, geöffnet – der Kennwortschutz war weg! Noch ein Versuch: speichern, schließen, öffnen – alles sichtbar in VBA! Unglaublich! Ich wurde rot und blass, fing an zu zittern und stammelte wirre Dinge … Wir probierten ein paar Mal – ältere Dateien, die ich für Firmen erstellt hatte …
Nach ein paar versuchen war klar: eine XLSX-Datei erlaubt keinen Kennwortschutz. Eine XLSM-Datei, die mindestens ein (leeres) Modul enthält dagegen schon. Also: Einfügen / Modul und Speichern unter XLSM (mit Makros – auch wenn keine Makros im Projekt / in der Arbeitsmappe sind). Das klappt. Und ich wischte mir den Schweiß von der Stirn.
Danke an Axel für die „nochmalige“ Frage.
Hallo Rene,
[…]
Wenn ich Dir schon mal schreibe.. Ich hätte da eine kleine Excell-Anwenderfrage an dich? Ist es möglich wenn man bei Excell z.B. ein Objekt einfügt und dieses dann verschiebt die Koordinaten dieses Objektes auslesen kann?
Liebe Grüße aus Hessen
Nils
#####
Hallo Nils,
Klar – mit den Eigenschaften Left und Top. Bspw. so:
Dim s As Worksheet
Dim o As OLEObject
Set s = ActiveSheet
Set o = s.OLEObjects(1)
MsgBox o.Left & “ x “ & o.Top
Du musst natürlich die Objekte „sauber“ adressieren.
Das ist mir noch nie aufgefallen. Heute in der VBA-Schulung. Ich gebe als Übung auf zwei Makros zu erstellen: eines soll sämtliche Tabellenblätter schützen, eines soll den Blattschutz aufheben. Ein Teilnehmer testet und bemerkt ein Ruckeln:
Und hier der Code – falls jemand selbst testen möchte. Das Ruckeln erscheint beim Schutzaufheben:
Sub AlleBlätterSchützen()
Dim i As Integer
For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Protect
Next
End Sub
Sub Blattschutzaufheben()
Dim i As Integer
For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Unprotect
Next
End Sub
Heute in der VBA-Schulung in einem chemischen Konzern. Wir üben Makrorekorder: ein benutzerdefiniertes Zahlenformat wird aufgezeichnet:
Eine Teilnehmerin fragte mich, warum Excel „Wasser“ in die Zelle einfügt. Ich habe zwei Mal hinschauen müssen, bis ich entdeckt hatte, dass Sie versehentlich auf die Zelle H20 geklickt hat und dies per Makrorekorder aufgezeichnet hat …
Haben die den Cosinus vergessen?
Nicht, dass ich sie unbedingt benötige – aber ich wollte in VBA etwas bei den Worksheetfunctions nachsehen. Dort kann man sämtliche Excel-Funktionen verwenden. Sämtliche? Nun: Sinus, Cosinus und Tanges finde ich nicht – nur ACos (ArcCos), ASin (ArcSin), Sinh, Cosh, … Auch der Objektkatalog zeigt diese trigonometrischen Funktionen nicht an.
Heute in der VBA-Schulung haben die Teilnehmer über folgende Fehlermeldung gelacht:
Argument ist nicht optional.
Argument ist nicht optimal?
Hätte man vielleicht etwas besser formulieren können.
Excel-VBA-Schulung: Fünf Minuten nach Beginn. Wir schreiben unser erstes Hello-World-Programm. Ein Teilnehmer drückt aus Versehen die Enter-Taste:
Und fragt erstaunt: „muss ich das Programm kompilieren, bevor es läuft?“
Er hat recht: der Begriff „Kompilieren“ ist schlecht gewählt für die Titelzeile des Meldungsfensters.
Sofort schalten wir die „automatische Syntaxüberprüfung“ aus, deren Begriff ebenfalls schlecht gewählt ist.
Ich habe heute mit einem Freund ein paar Excel-VBA-Lösungen programmiert.
In einer Tabelle sollen für den Ausdruck unter jeder Ergebniszeilen (Zeile mit den Zwischensummen) ein Seitenumbruch eingefügt werden (und noch ein paar weitere Dinge eingeschaltet und formatiert werden). Der Hintergrund: jedes dieser Blöcke sollte ausgedruckt an jeweils einen Kunden verschickt werden.
Der erste Test erstaunt:
Oha – man darf nur 1.026 manuelle Seitenumbrüche einfügen – mehr erlaubt Excel nicht.
Schon doof. Wenn ich in VBA eine Funktion mit dem Namen „Fehler“ erstelle, liefert Excel einen Fehler. Aber nicht meinen:
Function Fehler() As String
Fehler = „Excel nervt!“
End Function
Vielleicht kollidiert diese Funktion mit der Excelfunktion FEHLER.TYP. Vielleicht mag Excel einfach keine Fehler. So wie ich auch.
Haben Sie ein Tablett? Verwenden Sie den Tablettmodus? Haben Sie damit schon VBA programmiert? Zum Beispiel folgende Schleife:
Sub Schleife()
Dim i as Integer
MesBox „Los geht’s“
For i = 1 to 10
MsgBox i
Next
Msgbox „fertig – uff!“
End Sub
Das Ergebnis: Manchmal (!?!) flackert der Bildschirm unangenehm beim Testen (wie hier bei der 7):
Vielleicht ist der Name der VBA-Funktion etwas unglücklich gewählt:
Function Autor()
Autor = ActiveWorkbook.BuiltinDocumentProperties(„Author“)
End Function
Dennoch: Dass Excel so eine starke Abneigung gegen diesen Namen verspürt und ihn noch nicht einmal in Groß-/Kleinschreibung anzeigt, finde ich schon frech:
„Benutzer“ oder „Benutzername“ darf ich.
Wie oft muss ich es noch sagen: Programmiert sauber!
In einer Firma lief unter Excel 2007 ein Programm mit folgendem Code:
Dim BlattName
Sheets(„Diagramme“).Visible = True
Sheets(„nocheins“).Visible = True
BlattName = ActiveSheet.Name
Abgesehen davon dass die Variable „BlattName“ nicht sauber von Typ As String deklariert wurde, dass die Eigenschaft Visible eigentlich den Wert der Konstanten xlSheetVisible erhalten sollte und nicht True (True ist 1; xlSheetVisible ist -1) läuft es unter Excel 2016 an die Wand. Der Grund:
Nach Sheets(„Diagramme“).Visible = True ist der Fokus noch auf dem Blatt von dem aus der Code gestartet wurde. Werden jedoch zwei Blätter eingeblendet, wechselt Excel in der Version 2016 nun auf eines der eingeblendeten Blätter. Der Blattname lautet nun nicht mehr wird das ursprünglich aktive Blatt, sondern wie eines der Blätter, die zuvor ausgeblendet waren.
Das kann man gut mit einem Meldungsfenster verifizieren:
Sheets(„Diagramme“).Visible = True
Sheets(„nocheins“).Visible = True
BlattName = ActiveSheet.Name
MsgBox BlattName
Sauber programmieren heißt beispielsweise:
Dim BlattName As String
Dim xlBlattDiagramme As Worksheet
Dim xlBlattNochEins As Worksheet
Dim xlBlattAktual As Worksheet
Set xlBlattAktual = ActiveSheet
Set xlBlattDiagramme = Sheets(„Diagramme“)
Set xlBlattNochEins = Sheets(„nocheins“)
xlBlattDiagramme.Visible = xlSheetVisible
xlBlattNochEins.Visible = xlSheetVisible
BlattName = xlBlattAktual.Name
‚ wird eigentlich nicht mehr benötigt
MsgBox BlattName
Ich schaue mir eine fremde Datei an. Auf einem Tabellenblatt befindet sich ein Diagramm. Ich versuche herauszufinden, woher es die Daten bezieht. Die Quelle liegt auf einem anderen Blatt. Ich will zurück zu dem Blatt „Diagramm“ – doch das ist verschwunden.
Ich brauche schon eine Weile, bis ich dahiner komme:
In VBA befindet sich im Objekt Worksheet beim Ergebnis Deactivate folgender Befehl:
Private Sub Worksheet_Deactivate()
Sheets(„Diagramm“).Visible = xlSheetHidden
End Sub
Interessanter Mechanismus: Lieber Anwender, sobald du das Blatt verlässt, bekommst du es nicht mehr zu Gesicht. Natürlich kann man es sich über eine Schaltfläche wieder einblenden lassen …
Heute in der Excel-VBA-Schulung.
Wir erstellen eine Dialog, in dem die Eingaben überprüft werden. Ich schaue mir die Lösungen der Teilnehmer an. Das Ergebnis funktioniert:
Die Lösung der Teilnehmerin:
[…]
ElseIf Me.txtGeld.Value >= 1 = False Then
[…]
Ich stutze über die Folge. Warum funktioniert sie trotzdem?
Die Antwort: der erste Teil wird ausgewertet: Ist 0,75 >= 1 -> nein.
Dann der zweite Teil: ist WAHR = FALSE? – nein.
Schon wieder bin ich erstaunt. Ein Kunde möchte, dass seine Pivottabelle, die ich mit VBA erzeuge, kursiv formatiert wird. Da ich keine Ahnung habe, wie der Befehl lautet ein Pivottabellenformat für das Stripset zu ändern, greife ich auf den Makrorekorder zurück. Er zeichnet auf:
Sub Kursiv()
ActiveWorkbook.TableStyles(„contoso-Stil“).TableStyleElements(xlColumnStripe1). _
Clear
ActiveWorkbook.TableStyles(„contoso-Stil“).TableStyleElements(xlColumnStripe1). _
Font.FontStyle = „Kursiv“
With ActiveWorkbook.TableStyles(„contoso-Stil“).TableStyleElements( _
xlColumnStripe1).Interior
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.799981688894314
End With
[…]
Ich bin verblüfft: der Befehl
.Font.FontStyle = „Kursiv“
sieht nicht sehr sprach- und länderunabhängig aus. Ich teste – es läuft. Ich ändere die Zeile in die mir bekannte Eigenschaft Italic:
ActiveWorkbook.TableStyles(„contoso-Stil“).TableStyleElements(xlColumnStripe1).Font.Italic = True
Und: es läuft noch immer! Dieser Befehl gefällt mir besser. Makrorekorder – warum kannst du das nicht selbst?
Excel kann schon ganz schön nerven!
Gestern in der Excel-VBA-Schulung. Wir erstellen ein (komplexes) Makro, das eine andere Datei öffnet und dann weitere Befehle ausführt. Beispielsweise so:
Sub TastenkombiTest()
Workbooks.Open „D:\Eigene Dateien\Excel\Asterix.xlsx“
MsgBox „Excel kann schon ganz schön nerven!“
End Sub
In Excel weisen wir dem Makro über Entwicklertools / Code / Makros / Optionen eine Tastenkombination zu. Erster Versuch: das Makro wird auf [Strg] + [Shift] + [i] gelegt. Das Ergebnis: Das Makro bricht nach dem Öffnen der Datei ab.
Zweiter Versuch: das Makro wird auf [Strg] + [i] gelegt. Es klappt!
Muss ich das verstehen?
Heute in der Excel-VBA-Schulung. Ein Teilnehmer sagt mir, dass er ein Programm nicht starten kann. Er erhält eine Meldung, dass er „Else ohne If“ geschrieben hätte.
Ich erwiderte: nun – er habe wohl If vergessen. Oder falsch geschrieben. Er verneinte. Ich schaute mit den Code an:
Für With fehlt das Gegenstück End With. Nicht ganz korrekt die Meldung … sie taucht so an mehreren Stellen auf. Auch in folgenden Beispiel:
Dim i As Integer
For i = 1 To 10
If i > 9 Then
MsgBox „ziemlich groß“
Next
Hier lautet die Fehlermeldung: „Next ohne For“.
Heute in der Excel-VBA-Schulung. Wir erstellen eine UserForm und starten diese.
Ein Teilnehmer hat sich im Code vertippt und erhält eine Fehlermeldung.
Er wechselt nach Excel und will dort die UserForm schließen. Keine Chance!
Die einzige Möglichkeit: er muss in VBA das Makro „zurücksetzen“. dann kann er seinen Fehler in Ruhe korrigieren.
Eigentlich hätte ich es wissen können.
Vor einigen Jahren rief mich ein Teilnehmer in einer VBA-Schulung, weil sein Code nicht funktionierte:
Ich habe lange gesucht, bis ich verstanden hatte, dass er die Variable intSpaltenAnzahl deklariert hat, dagegen der Variablen intSpaltenAnzah1 einen Wert zuweist. Zugegeben: bei einer anderen Schrift als der Courier sieht man das besser.
Gestern beim Programmieren zeichnet der Makrorekorder folgenden Code auf:
ActiveWorkbook.TableStyles.Add („RenesVorlage“)
With ActiveWorkbook.TableStyles(„RenesVorlage“)
.ShowAsAvailablePivotTableStyle = True
.ShowAsAvailableTableStyle = False
.ShowAsAvailableSlicerStyle = False
.ShowAsAvailableTimelineStyle = False
End With
With ActiveWorkbook.TableStyles(„RenesVorlage“).TableStyleElements(xlColumnStripe1).Interior
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399945066682943
End With
ActiveSheet.PivotTables(„PivotTable1“).ShowTableStyleColumnStripes = True
Und ich bin ja wieder reingefallen – es heißt xlColumnStripe1 und nicht xlColumnStripel.
Obwohl?! – Stripel klingt auch lustig.
Auf nichts ist mehr Verlass!
Für einen Kunde habe ich ein Add-In für Excel erstellt. Am Ende werden daten in einer Pivottabelle zusammengefasst. Der Kunde möchte noch ein bisschen Farbe ins Spiel bringen. Kein Problem, denke ich – in Pivottabellen lustige Formatierungen einschalten kann man mittels der Pivottable-Formate erreichen. Doch wie heißen sie in Excel? Der Makrorekorder hilft. Ich zeichne die Farbe auf:
ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1). _
Clear
With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Interior
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.399914548173467
End With
Wunderbar!
Und Linien? Ich zeichne auf:
With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Borders(xlEdgeTop)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
.LineStyle = xlNone
End With
With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Borders(xlEdgeBottom)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
.LineStyle = xlNone
End With
With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Borders(xlEdgeLeft)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
.LineStyle = xlNone
End With
With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Borders(xlEdgeRight)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
.LineStyle = xlNone
End With
[…]
Ich teste – lasse den Code abspielen. Was passiert? Nichts! Teste erneut, …
Ich habe eine Weile benötigt, bis ich gesehen hat, dass der Makrorekorder eine dünne schwarze Linie mit der Eigenschaft LineStyle = xlNone aufzeichnet hat ?!? Also: raus damit. Code noch „putzen“ – und schon läuft es!
xlDateiNeu.TableStyles.Add „Rene-Stil“
With xlDateiNeu.TableStyles(„Rene-Stil“)
.ShowAsAvailablePivotTableStyle = True
.ShowAsAvailableTableStyle = False
.ShowAsAvailableSlicerStyle = False
.ShowAsAvailableTimelineStyle = False
End With
xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).StripeSize = 2
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Interior
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.399945066682943
End With
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Borders(xlEdgeTop)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
‚.LineStyle = xlNone
End With
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Borders(xlEdgeBottom)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
‚.LineStyle = xlNone
End With
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Borders(xlEdgeLeft)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
‚.LineStyle = xlNone
End With
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Borders(xlEdgeRight)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
‚.LineStyle = xlNone
End With
Hübsche Fehlermeldung: „Zu wenige Zeilenfortsetzungen“.
Die Ursache: Mit dem Makrorekorder wird der Befehl Datei / Öffnen (eine Textdatei) aufgezeichnet. Da die Textdatei zirka 200 Spalten hat, kann dies nicht in einem Array gespeichert werden, der in diesem Makro intern verwendet wird:
Noch ein hübscher Fehler aus der heutigen VBA-Schulung.
Warum liefert folgende Codezeile einen Fehler:
Set xlBlatt = xlDatei.Worksheets(„Filme“)
Nun – die Teilnehmerin hat das Blatt nicht „Filme“ genannt, sondern “ Filme“ – also vor den Buchstaben „F“ ein Leerzeichen eingegeben …
Schnell eine Funktion mit VBA erstellt. Sie heißt ReCo und wird im Funktionsassistenten angezeigt, wenn die Datei, in der sie sich befindet, offen ist.
Statt des Umwegs über ein Add-In beschließe ich diese Funktion in der Datei Personal.xlsb zu speichern. Was passiert? Nichts! Die Funktion wird nicht angezeigt!
Also doch ein Add-In!
Heute in der VBA-Schulung.
Da Excel keine Funktion besitzt, um einen Text „rumzudrehen“, verwenden wir die VBA-Funktion StrReverse:
Function RC(Text As String) As String
RC = StrReverse(Text)
End Function
Sie wird in Excel angezeigt und kann verwendent werden:
Padautz – vielleicht doch nicht. Möglicherweise ist der Name RC schon vergeben – RowColumn? Ich weiß es nicht. Ein Umbenennen in beispielsweise ReCo hilft.
Lustiger Fehler in der VBA-Schulung. Finden Sie ihn?
Sub Eingabe()
Dim Geschlecht As String
Dim Benutzername As String
Geschlecht = InputBox(„Bitte Geschlecht angeben – bitte m oder w!“)
If Geschlecht = „m“ Or Geschlecht = „M“ Then
Benutzername = InputBox(„Bitte sag deinen Namen!“, „TF“)
MsgBox „Hallo lieber “ = Benutzername
ElseIf LCase(Geschlecht) = „w“ Then
Benutzername = InputBox(„Bitte sag deinen Namen!“, „TF“)
MsgBox „Hallo liebe “ = Benutzername
Else
MsgBox „Falsche Eingabe!“
End If
End Sub
Das Ergebnis:
Hallo Rene,
bitte wieder Tipparbeit bei der schon bekannten Tabelle.
Durch die Hinweise auf VBA in manchen Vorträgen habe ich mir einige der Schulungen angesehen.
Ach oh je 🙁
Es ist toll, dass man sich nachdem das Programm feriggestellt ist arbeit erspart. Die Suche im Internet nach geeigneten Code hat natürlich nicht zu einem Ergebnis geführt.
Dazu braucht man ein erweitertes Grundwissen das ich nicht habe.
Meine Bitte und Anliegen:
Kannst Du mir bitte VBA Code für die Tabelle mit der Datumskorrektur schreiben?
Das alte Datum soll ausgeblendet werden.
Das einfügen der Spalte für das neu formatierte Datum muss variable sein. Es kommt neuerdings auch eine Tabelle mit Point vor dem Datum.
Bitte ein alphabethisches Inhaltsverzeichnis mit Link auf die aktuellen Tabellen.
Zu VBA
Kannst Du mir ein Buch empfehlen?
Kann man in den Büchern, ähnlich wie in denen von Excel, bestimmte Code für Funktionen (kopieren, einfügen, Formeln in VBA) finden?
Im Internet habe ich für das Einfügen einer Spalte Code entdeckt. Wollte aber eine variable Möglichkeit, die ich nicht gefunden habe. Mitbekommen habe ich, dass man Variable definieren muss. Wie diese jedoch bezeichnet werden habe ich nicht herausgefunden. Die Zellen werden in Ziffern aufgeteilt. Buchstaben und Zahlen für die Bezeichnung der Zellen habe ich in anderen Code entdeckt. Erwähnt wurde auch der Unterschied zwischen USA und dem localen Code.
Mit besten Grüßen
Peter
#####
Hallo Peter,
hier der Code:
Sub MonateErsetzen()
Dim strSpalte As String
Dim i As Integer
Dim strMonateDeutsch
Dim strMonateEnglisch
strMonateDeutsch = Array(„Jänner“, „Februar“, „März“, „April“, „Mai“, „Juni“, „Juli“, „August“, „September“, „Oktober“, „November“, „Dezember“)
strMonateEnglisch = Array(„January“, „February“, „March“, „April“,
„May“, „June“, „July“, „August“, „September“, „October“, „November“,
„December“)
strSpalte = InputBox(„In welcher Spalte sollen die Datumswerte ersetzt werden?“)
For i = 0 To UBound(strMonateDeutsch)
ActiveSheet.Columns(strSpalte & „:“ & strSpalte).Replace What:=strMonateEnglisch(i), Replacement:=strMonateDeutsch(i), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i
End Sub
Wie kommt man dazu? Zeichen mit dem Makrorekorder auf – beispielsweise ersetze „January“ durch „Januar“. Dann erhältst du folgenden Code:
Sub Makro1()
‚
‚ Makro1 Makro
‚
‚
Selection.Replace What:=“February“, Replacement:=“Februar“, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Nun – und da bauen wir ein bisschen Makro außenrum.
Ich empfehle keine Bücher. Jeder hat eine andere Art zu lesen und zu lernen.
Geh in eine große Buchhandlung, hole einen Meter VBA-Literatur raus, blättere sie durch und bestelle dann das Buch, das dir am besten gefallen hat, in einer kleinen Buchhandlung.
Tipp: ich würde einen Kurs besuchen (Volkshochschule oder privater Anbieter). Dort bekommst du in kurzer Zeit am schnellsten die wichtigste Informationen. Dozent zeigt auch, was man falsch machen kann und hilft.
Liebe Grüße
Rene
#######
Hallo Rene,
Danke.
Leider hilft mir dieser Code nicht weiter nachdem nur der Monatsname in deutscher Schreibweise umsetzt wird. Benötigt wird ein ‚echtes‘ Datum.
In der Spalte C steht einmal ein echtes Datum und dann das Datum als Text.
In der neuen Spalte D die eingefügt wurde, hast du mit der untenstehenden Formel ein ‚echtes‘ Datum erstellt.
=WENN(ISTZAHL(C2);C2;DATUM(RECHTS(C2;4);VERGLEICH(TEIL(C2;SUCHEN(“ „;C2)+1;LÄNGE(C2)-8);{„January“;“February“;“March“;“April“;“May“;“June“;“July“;“August“;“September“;“October“;“November“;“December“};0);LINKS(C2;2)))
erstellt.
Bitte im VBA Code umsetzen, dass eine neue Spalte erzeugt wird, das Datum nach deiner Formel eingesetzt wird und die Spalte mit den nicht brauchbaren Angaben ausgeblendet wird.
Deine Vorschläge werde ich befolgen. Schau mal in die Buchhandlung was dort aufliegt. Ein Kurs ist auch eine gute Idee.
Besten Dank,
Peter
#####
Hallo Peter,
probiere es aus!
Durch das Ersetzen „erkennt“ Excel ein Datum und wandelt in eine Datumszahl um
LG :: Rene
Das erste Add-In ist installiert. Es folgt eine zweite Version. Die zweite Datei.xlam wird an einem anderen Speicherort abgelegt.
Wird nun das Add-In erneut installiert, erhält man eine Meldung:
Klar soll sie ersetzt werden – was für eine Frage!
Und was passiert? NICHTS!
Heißt: Man muss erst das alte Add-In löschen und dann das neue installieren. Besser: das neue an den gleichen Ort speichern, in dem sich das alte Add-In befand – das alte also durch das neue ersetzen. Schon perfide!
Ein Kunde möchte per Programmierung Bilder nach Excel eingelesen habe. Das ist kein Problem, ebenso wenig wie das Verschieben und Verkleinern der Bilder. Jedoch: er möchte sie auch komprimiert haben. Weder im Objektkatalog noch in einer Suchmaschine finde ich die Möglichkeit per VBA auf den Assistenten „Bilder verkleinern“ zu gelangen. Auch der Makrorekorder hilft nicht weiter.
Also doch sendkeys. Ich drück die Alt-Taste und sehe, dass die Registerkarte „Format“ mit „JV“ erreicht wird. Dann „l“ für „Bilder komprimieren“ und anschließend „e“ für – „96 ppi“.
Also:
SendKeys „%jvle“
SendKeys „{Enter}“
Klappt nicht. Auch nicht:
SendKeys „%j“
SendKeys „%v“
SendKeys „%l“
SendKeys „%e“
Nach einigem Probieren komme ich dahinter, dass ich nur „j“ senden darf – nicht „jv“. Warum sagen die das nicht gleich?
Ich versuche mittels VBA ein Bild auf 10 cm zu verkleinern. Beim ersten teil hilft der Makrorekorder. Jedoch: wenn das Bild nicht im Querformat, sondern im Hochformat vorliegt, muss ich die Height und nicht die Width verändern. Also gehe ich auf die Suche nach dem Befehl „Winkel“. In Visio heißt er Angle. Jedoch in Excel VBA?
Ein Blick in den Eigenschaften-Dialog (Größe und Position) zeigt: auf diesem Dialog heißt er „Drehung“. Und richtig: unter „Rotation“ werde ich fündig.
Kann Microsoft die Objekte, Eigenschaften und Methoden in den einzelnen Applikationen nicht gleich benennen? Es nervt!
Och nö – Leute – so nicht!
Vor ein paar Tagen habe ich eine Anfrage geschickt bekommen, warum das Makro, das unter Excel 2007 funktionierte, nicht mehr in Excel 2016 läuft. Ein Blick auf den Code – und ich schlage die Hände über dem Kopf zusammen:
Also: „sauber“ programmieren – so wie ihr es in meinen Schulungen lernt. Dann läuft das Programm auch noch in den neueren Excel-Versionen.
Damit Excel nicht mehr nervt!
Ist Ihnen das schon aufgefallen:
In einer Excelmappe gibt es zwei Tabellenblätter: Tabelle1 und Tabelle2. Tabelle1 liegt links; Tabelle2 rechts. Tabelle2 wird ausgeblendet.
Wenn man nun Tabelle1 kopiert: Kopie erstellen (ans Ende stellen) und anschließend Tabelle2 wieder einblendet: Liegt die Kopie nun links oder rechts von Tabelle2?
Die Lösung: sie liegt links von der ehemals ausgeblendeten Tabelle. „Ans Ende stellen“ heißt also: „Ans Ende der sichtbaren Tabellen stellen“. Ist das schlimm? Man sieht doch, wo die Tabellen liegen?
Die Antwort:
Wenn Sie per Programmierung ein Blatt in eine andere Datei kopieren, beispielsweise so:
Dim xlBlatt As Worksheet
Dim xlDatei As Workbook
Set xlDatei = Application.Workbooks.Open(„D:\Excel\Testdatei.xlsx“)
Set xlBlatt = ThisWorkbook.Worksheets(„Tabelle1“)
xlBlatt.Copy After:=xlDatei.Worksheets(xlDatei.Worksheets.Count)
MsgBox xlDatei.Worksheets(xlDatei.Worksheets.Count).Name
Nun liefert das Meldungsfenster nicht den Namen des kopierten Blattes, sondern den Namen des letzten Blattes (wenn es ausgeblendet war). Und: leider liefert die Methode Copy kein Objekt, also kein Verweis auf ein Tabellenblatt zurück.
Heißt: gut aufpassen! Sonst nervt das Ergebnis!
Ein Add-In in Excel zu erstellen ist nicht schwierig – man muss nur eine Excelmappe mit Makros als Add-In speichern.
Ein Add-In einzubinden ist auch nicht schwierig. Unter Datei / Optionen / Add-Ins / Excel-AddIns [Los] findet man die Schaltfläche „Hinzufügen“. Damit kann ein Add-IN eingebunden werden.
Man kann es deaktivieren – aber wie bekommt man es eigentlich weg?
Die einzige Möglichkeit ist das Add-In umzubenennen, es dann erneut zu aktivieren und/oder deaktivieren. Dann wird erkannt, dass es nicht mehr vorhanden ist:
Es ist schön, wenn Excel Assistenten zur Verfügung stellt. Beispielsweise einen zum Duplikate entfernen:
Da ich diese Funktionalität in einem umfangreichen Programm benötige, zeichne ich ihn mit dem Makrorekorder auf:
ActiveSheet.Range(„$A$1:$J$78“).RemoveDuplicates _
Columns:=Array(1, 2, 3, 4, 5, 6, 7 _
, 8, 9, 10), Header:=xlYes
Der Parameter Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) gefällt mir nicht.
In der Hilfe steht, dass man ihn weglassen kann – dann würden alle Spalten verwendet werden. Ein Test zeigt: Das ist falsch. Lässt man den Parameter weg, passiert: GAR NICHTS!
Also programmieren wir den Parameter:
Dim intSpalten() As Integer
Dim i As Integer
ReDim intSpalten(0)
intSpalten(0) = 1
For i = 2 To ActiveSheet.Range(„A1“).CurrentRegion.Columns.Count
ReDim Preserve intSpalten(UBound(intSpalten) + 1)
intSpalten(UBound(intSpalten)) = i
Next
ActiveSheet.Range(„A1“).CurrentRegion.RemoveDuplicates _
Columns:=intSpalten, Header:=xlYes
Das Ergebnis ist eine Fehlermeldung:
Verwundert reibe ich mir die Augen. Probieren und eine lange Suche liefert das Ergebnis: Man muss das Array vom Typ Variant deklarieren. Und: der Parameter Columns verlangt den Wert in Klammern !?! Dann klappt es: die Spaltenanzahl des Assistenten „Duplikate entfernen“ wird dynamisch:
Dim intSpalten
Dim i As Integer
ReDim intSpalten(0)
intSpalten(0) = 1
For i = 2 To ActiveSheet.Range(„A1“).CurrentRegion.Columns.Count
ReDim Preserve intSpalten(UBound(intSpalten) + 1)
intSpalten(UBound(intSpalten)) = i
Next
ActiveSheet.Range(„A1“).CurrentRegion.RemoveDuplicates _
Columns:=(intSpalten), Header:=xlYes
PS: Ein Dankeschön an Dominik Petri für den Hinweis!
Ich kannte den Fehler schon.
Gestern in der Excel-VBA-Schulung fragte eine Teilnehmerin, wo der Fehler liege:
Man muss schon zwei Mal hinschauen. Bei der voreingestellten Schrift Courier sieht die Ziffer „1“ fast genauso aus wie der Buchstabe „l“. Also xlZelleAl und nicht xlZelleA1.
Wisst ihr wie ich das gemacht habe? Nein – das Bild ist nicht bearbeitet! Heute beim Programmieren habe ich erstaunt festgestellt, dass in einer Zelle anderer Text steht als in der Bearbeitungsleiste.
Nun – ein paar Zeilen Code:
Range(„E1“).Value = „Nervt Excel?“
Application.ScreenUpdating = True
[Hier muss Code stehen, der einige Sekunden benötigt, um ausgeführt zu werden]
Application.ScreenUpdating = False
Range(„E1“).Value = „Excel nervt!“
Die Zeile
Application.ScreenUpdating = True
bewirkt die Anzeige der „Sanduhr“ und bewirkt, dass nur nur die Tabelle aktualisiert wird – nicht jedoch die Bearbeitungsleiste.
Achtung: Code muss zwei Mal ausgeführt werden, damit ich „Excel nervt!“ und „Nervt Excel?“ sehe.
Letzte Woche in der Excel-VBA-Schulung. Um die Objekte näher zu bringen, beginne ich mit mit dem Makrorekorder. Wir zeichnen eine Reihe Befehle auf. Beispielsweise:
Füge in die Kopfzeile ein Bild ein:
ActiveSheet.PageSetup.RightHeaderPicture.Filename = _
„F:\Eigene Bilder\Bali\PIC00020.jpg“
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = „“
.PrintTitleColumns = „“
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = „“
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = „“
.CenterHeader = „“
.RightHeader = „&G“
.LeftFooter = „“
.CenterFooter = „“
.RightFooter = „“
[…]
Das Ergebnis:
Wir löschen das Bild und führen das Makro erneut aus. Das Ergebnis: nichts! Ein Blick in den Dialog „Seite einrichten“ zeigt jedoch, dass etwas in der Kopfzeile ist. Ein Bild?
Der Grund ist schnell gefunden: Die Zeile:
Application.PrintCommunication = False
„Gibt an, ob die Kommunikation mit dem Drucker aktiviert ist.“ Deshalb wird zwar das Bild eingefügt aber nicht angezeigt. Also: Zeile löschen – und schon funktioniert es. Manchmal (oft!?!) liefert der Makrorekorder eben doch nicht den besten Code …
Übrigens: auf der Microsoft-Seite findet sich folgende Erklärung:
„Legen Sie die PrintCommunication-Eigenschaft auf False fest, um die Ausführung von Code zu beschleunigen, der PageSetup-Eigenschaften festlegt. Legen Sie die PrintCommunication-Eigenschaft auf True fest, nachdem Sie Eigenschaften zum Ausführen eines Commits aller zwischengespeicherten PageSetup-Befehle festgelegt haben.“
Aha!
Erinnern Sie sich noch? Windows 7? Die Beispielbilder: Wüste, Tulpe, Qualle, Koala, …
Ein Teilneer der Excel-VBA-Schulung möchte Bilder per VBA in seine Exceldatei einfügen. Die Dateinamen stehen dabei bereits in einer Exceltabelle.
„Kein Problem – schreiben Sie einfach ein paar Dateinamen in eine Tabelle.“ Alle Teilnehmer verwenden den Ordner „Beispielbilder“, kopieren den Pfad, tippen die Dateinamen mit der Endung ab. Ich verwende meinen eigen Ordner, in dem ich ein paar Urlaubsbilder habe.
Wir lassen das Makro laufen:
Dim strDateiname As String
Dim i As Integer
For i = 1 To ActiveSheet.Range(„A1“).CurrentRegion.wors.Count – 1
strDateiname = ActiveSheet.Range(„A1“).Offset(i, 0).Value
ActiveSheet.Pictures.Insert strDateiname
Next
Während es bei mir funktioniert, erhalten die Teilnehmer eine Fehlermeldung. Die Dateinamen sind doch korrekt geschrieben, oder:
Ein Blick in die Eigenschaften – Registerkarte „Sicherheit“ belehrt mich eines Besseren. Die Datei Wüste heißt „desert.jpg“, die Qualle „jellyfish.jpg, „penguins .jpg“, „lighthouse.jpg“, …
Ich gestehe – das ist mir noch nie aufgefallen, dass die Dateien einen Alias tragen, also dass die Beispielbilder eigentlich einen anderen Dateinamen aufweisen.
Amüsant: Ich darf eine Zelle „mfg“ nennen, also ihr den Namen „mfg“ geben.
Aber „mfg2“ darf ich sie nicht nennen:
Der Grund ist einleuchtend: da man Namen über das Namensfeld (links neben der Bearbeitungsleiste) vergeben kann, würde ein dort eingegebener Name zur Zelle MFG2 springen. Deshalb dürfen auch nicht die Namen „MF2“ oder „M2“ vergeben werden. Nur „mfg_2“.
Nicht mehr verständlich ist es jedoch, wenn Sie ein Makro mit dem Makrorekorder aufzeichnen, das sie „mfg2“ nennen. DAS ist nicht erlaubt.
Ganz unverständlich wird die Sache jedoch, wenn Sie im Visual Basic-Editor ein Makro erstellen, das den Namen „mfg2“ trägt. DORT ist der Name erlaubt und bereitet keine Probleme ?!?
Heute in der Excel-VBA-Schulung. Wir erstellen ein Beispiel: Zähle solange eine Nummer hoch, bis es eine Datei mit der entsprechenden Nummer nicht mehr gibt. Speichere dann die Datei unter dieser Nummer.
Bei mir funktioniert das Beispiel. Ein Teilnehmer hatte einen Fehler und rief mich. Ich war erstaunt über die Fehlermeldung:
Dateiname oder -nummer falsch
Noch erstaunter war ich, dass die Datei gespeichert war in:
D:\D:\Eigene Dateien\Excel\…
Zwei Mal D:\D:\ …?!?
Zuerst wollte ich Excel wüst beschimpfen, dann schaute ich mir den Code genauer an. Der Teilnehmer hatte die Datei gespeichert in.
„D:\“ & strPfad & strDatei
Erstaunlich: die Datei ist im Explorer nicht auffindbar. Excel (und VBA) behaupte jedoch, dass der FullName lautet:
D:\D:\Eigene Dateien\Excel\…
Hallo Herr Martin,
ich benötige eine Funktion, die das Zeichen π (pi) einfügt. Allerdings zeichnet der Makrorekorder auf:
ActiveCell.FormulaR1C1 = „?“
oder:
ActiveCell.FormulaR1C1 = „P“
Die Antwort: Sie müssen das Makro nachbearbeiten. Beispielsweise:
ActiveCell.Value = „P“
ActiveCell.Font.Name = „Symbol“
Liebe VBA-User: Ist euch das schon aufgefallen:
Ich erstelle in Excel 2016 eine UserForm. Auf der UserForm befindet sich eine Befehlsschaltfläche mit folgenden zwei Codezeilen:
Workbooks.Add
Unload Me
In dem Projekt befindet sich ein Makro:
Sub MaskeStart()
UserForm1.Show
End Sub
Dieses Makro wird an eine Schaltfläche auf dem Zeichenblatt gebunden (dabei ist es egal, ob es sich um ein Formularsteuerelement oder ein Active-X-Steuerelement handelt.
Ich „mache das Steuerelement scharf“, klicke darauf, die Maske startet, eine neue Datei wird geöffnet, in der ich allerdings keine Registerkarte aktivieren kann. Das war doch in älteren Excelversionen nicht der Fall, oder irre ich mich?
Manchmal sind es nicht die Fehlermeldungen, die mich amüsieren:
sondern die Kommentare dazu:
„Hallo Rene,
ich verstehe etwas nicht … wenn ich diesen Code: […]
dann bekomme eine Fehlermeldung. Es fehlt ein with block und so.
Danke für deine Hilfe.“
Natürlich konnte ich helfen: „an Objektvariablen musst du etwas mit dem Schlüsselwort SET übergeben, also:
Set xlRange = ActiveSheet.UsedRange
Eine Kollegin hat mir letzte Woche eine Excelmappe mit einem Makro geschickt. Das Makro kopiert von mehreren Tabellenblättern Bereiche und fügt sie in eine bestehende Präsentation ein.
Die Kollegin hatte ein paar Fragen zum Makro. Da ich die PowerPoint-Präsentation nicht hatte, änderte ich einige wenige Befehle. Beispielsweise „greife auf Folie 1, 2, 3 zu“ wurde geändert in: „füge eine neue Folie ein“. Der Befehl AddSlides war schnell gefunden:
Allerdings erhielt ich hier einen Fehler. Nochmal nachschauen:
Doch: Sildes.AddSlide. Korrekt!
Ebenso die Parameter: an welcher Stelle soll eine neue Folie erzeugt werden? Welches Layout soll verwendet werden? Ebenfalls korrekt!
Mir dämmerte es. Vor gefühlten 100.000 Jahre habe ich mal PowerPoint programmiert. Und dort herausgefunden, dass die Methode nicht AddSlides heißt, sondern Add. Geändert. Tatsächlich: IntelliSense listet nun sogar die korrekten Layout-Konstanten auf.
Hum – Microsoft hätte wahrlich in den letzten 15 Jahren einen so wichtigen Befehl wie „füge neue Folie ein“ korrigieren können. Oder bin ich der einzige, dem dies aufgefallen ist?
Was ist denn nun schon wieder los? Ich möchte eigentlich nur mit dem Befehl Copy ein Blatt in eine andere Datei kopieren:
Die Antwort finden Sie, wenn Sie genau hinschauen. Das zu kopierende Blatt („YTD“) ist xlSheetVeryHidden. Sie müssen die Eigenschaft Visible entweder auf xlSheetVisible oder auf xlSheetHidden stellen. Danach können Sie ihn ja wieder „sehr gut verstecken“.
Die Ursache
Ich versuche per VBA benutzerdefinierte Eigenschaften an eine Datei zu binden. Nichts leichter als das, denke ich:
Man definiert eine Variable vom Typ CustomDocumentProperties (oder Property) und fügt zu der Sammlung ein weiteres Element mit der Methode Add hinzu. Sie möchte Name und Value. Klingt vernünftig. Ich werde jedoch eines Besseren belehrt:
Typen unverträglich? Okay – dann ohne Objektverweis:
Falsch Anzahl an Argumenten? Aber IntelliSense hat mir doch … Ein Blick in die Hilfe verrät, dass ich die CustomDocumentProperties vom Typ DocumentProperties deklarieren muss. Aha:
Und richtig: Dort wird noch zwingend der Parameter „LinkToContent“ verlangt.
Nächster Test:
??? Etwas probieren und schon habe ich die Lösung: Obwohl die Eigenschaft „Type“ in eckigen Klammern, also optional, angegeben wurde, ist dieser Wert zwingend erforderlich.
Kaum probiert man eine halbe Stunde – schon klappt es auch. Flexibilität braucht man schon – nicht nur im Niveau:
Amüsant. Heute in der VBA-Schulung. Wir basteln eine dynamische UserForm. Bei der Auswahl „ein Verkäufer“ kann ein Verkäufer ausgewählt werden, bei der Auswahl „alle Verkäufer“ wird das Listenfeld inaktiv.
Der Code:
Private Sub optAlleVerkäufer_Click()
Me.lstVerkäufer.BackColor = &H8000000F
Me.lstVerkäufer.Enabled = False
Me.lstVerkäufer.BorderStyle = fmBorderStyleSingle
End Sub
Private Sub optEinVerkäufer_Click()
Me.lstVerkäufer.BackColor = &H8000000E
Me.lstVerkäufer.Enabled = True
Me.lstVerkäufer.BorderStyle = fmBorderStyleNone
End Sub
So sieht es nach der Initialisierung aus:
Neun Mal wechseln zwischen alle und ein Verkäufer:
Nach 17 Mal wechseln:
Nach 22 Mal:
Amüsant ?!? Das Listenfeld wird immer kleiner. Man muss gar nicht das Listenfeld inaktiv (Enabled = False) setzen. Es genügt die Eigenschaft BorderStyle zu ändern. Ein paar Mal. Nach zehn bis 20 Klicks schrumpft das Listenfeld bedenklich:
Private Sub optAlleVerkäufer_Click()
Me.lstVerkäufer.BorderStyle = fmBorderStyleSingle
End Sub
Private Sub optEinVerkäufer_Click()
Me.lstVerkäufer.BorderStyle = fmBorderStyleNone
End Sub
Geschmunzelt habe ich schon:
Gestern in der VBA-Schulung haben wir uns Eigenschaften und Methoden von Objekten in Excel VBA angesehen. Beispielsweise von ActiveWorkbook: die Eigenschaften Name, Path, FullName, die Methoden Save, Close und eben auch PrintOut. Mit dem Parameter Copies kann man eine Datei mehrmals ausdrucken lassen, erklärte ich. Ein Teilnehmer wollte wissen, was denn passiere, wenn man die Datei beispielsweise 100.000 Mal ausdrucken würde. Da mein Laptop nicht mit einem Drucker verbunden war, habe ich es getestet: Das Ergebnis:
Maximal 32.767 (215-1) Mal darf man ausdrucken
Ist ja auch logisch: mehr darf man auch nicht in Excel:
32.767 genügt doch auch, oder?
Amüsante Sache heute in der VBA-Schulung:
Ein Teilnehmer fragte mich, warum sein Markorekorder nicht funktioniert. Tatsächlich: Beim Versuch ein Makro aufzuzeichnen kam eine erstaunliche Fehlermeldung:
Komisch?!
Zuerst schaute ich nach, ob noch ein Makro läuft. Nein. Alles war okay – das Stoppen brachte nicht den gewünschten Erfolg. Dann sah ich es: in einem Modul war ein fehlerhaftes Makro – wahrscheinlich hatte der Teilnehmer beim Kopieren das Sub – End Sub vergessen.
Übrigens: Trotz Fehlermeldung konnte das Makro aufgezeichnet werden …
… und ich finde die Ursache …
Heute in der VBA-Schulung. Ein Teilnehmer fragt mich, wo der Fehler in der folgenden Codezeile ist:
ActiveWorkbook.Worksheets(„Filme“).Activate
warum?
Nein – ActiveWorkbook ist richtig – es war keine andere Datei offen. Jedoch hatte der Teilnehmer das Tabellenblatt „Filme “ benannt – mit einem Leerzeichen Ende …
Böses Leerzeichen!
Heute in der VBA-Schulung fragte mich eine Teilnehmerin, warum sie eine UserForm nicht löschen dürfe. Im ersten Moment war ich verblüfft.
Wo bitte ist „entfernen“?
Dann fiel mein Blick auf die Titelzeile. Aha – der Fehler war schnell gefunden: an einer anderen Stelle lief noch ein Makro, das gerade unterbrochen war:
Ah!
Hallo Herr Martin,
doch, doch ich bin ganz sicher. Ich habe der Zelle G16 einen Namen („Betreuer“) gegeben. Aber nach einer Weile verschwindet er – er ist weder links oben im Namensfeld noch im Namensmanager zu sehen. Haben Sie eine Ahnung warum?
Namen verschwinden
Wahrscheinlich wird ihre Datei durch Code (VBA oder VS.NET) gesteuert. Und wahrscheinlich hat dort jemand ein Makro geschrieben, das einen Namen „Betreuer“ anlegt – allerdings unsichtbar.
Man kann herausfinden, welche Namen verwendet wurden, beispielsweise so:
Sub Namenstest()
Dim i As Integer
Dim strListe As String
For i = 1 To ActiveWorkbook.Names.Count
strListe = strListe & vbCr & ActiveWorkbook.Names(i).Name & „:“ & ActiveWorkbook.Names(i).Value & “ sichtbar: “ & ActiveWorkbook.Names(i).Visible
Next
MsgBox strListe
End Sub
Mehrere unsichtbare Namen
Denn: Man kann unsichtbare Namen generieren, die mit der Datei gespeichert werden:
ActiveWorkbook.Names.Add Name:=“Betreuer“, RefersTo:=“$G$35:$K$35″, Visible:=False
Die Idee, endlich in Excel eine gute (!) Funktion zu implementieren, mit deren Hilfe man zwei Dateien vergleichen kann, war dringend nötig. Und so findet sich seit Excel 2013 (wenn Sie die richtige Version gekauft haben) hinter der Registerkarte „Inquire“ eine Schaltfläche „Dateien vergleichen“:
Dateien vergleichen
Das Ergebnis von zwei „größeren“ Dateien begeistert:
Tabellen vergleichen
Jedoch weniger das Design, wenn es darum geht, VBA-Code zu vergleichen. Zugegeben – die Option ist praktisch – das Ergebnis schwer zu interpretieren.
VBA1 und VBA2
Heute in der VBA-Schulung ist uns folgende amüsante Sache aufgefallen:
In einer Datei – egal ob in der Personal.xlsb oder einer anderen beliebigen Makrodatei wird ein Makros geschrieben:
Sub Hinkelstein()
Application.Workbooks.Open „E:\Eigene Dateien\Comics\Asterix.xlsx“
MsgBox „Wo ist Obelix?“
End Sub
Legt man in den Optionen das Makro auf einen Shortcut – beispielsweise [Strg] + [q] gibt es keine Probleme.
Verwendet man jedoch [Strg] + [Shift] + [A], wird nur die erste Zeile ausgeführt – nach Öffnen der Datei bricht das Makro ab (auch bei längeren Makros). Muss ich das verstehen?
Nach Asterix ist Schluss
Warum darf ich plötzlich nicht in die Fußzeile schreiben?
Warum?
Die Antwort: Der Text ist zu lang. Die Fußzeile in Excel erlaubt eine Textlänge von maximal 255 Zeichen. Nicht nur in VBA, sondern auch in Excel:
Die häufigste Frage in der letzten VBA-Schulung war: Warum funktioniert mein Makrorekorder nicht:
Das kann verschiedene Ursachen haben (und ich glaube, meine Teilnehmer haben alle gefunden):
In VBA läuft noch ein Programm: Ausschalten!
Der Cursor befindet sich in einer Zelle: Raus!
Die Arbeitsmappe wurde freigegeben: Aufheben!
Der Änderungsmodus wurde eingeschaltet: Aufheben!
Das Projekt wurde mit einem Kennwort geschützt: Aufheben!
Ein hübscher Fehler im VBA-Kurs:
Variable? Welche Variable?
Ich habe zwei Mal hinschauen müssen:
Natürlich muss es heißen:
For intZähler = 10 To 1 Step -1
Wird der Zielwert weggelassen, interpretiert VBA das Schlüsselwort „Step“ als Variable.
Wirklich erstaunlich. In einer Excel sind mehrere Tabellenblätter ausgeblendet:
ausgeblendete Tabellenblätter
Die Codezeile:
ActiveWorkbook.Worksheets(„Tabelle4“).Activate
selektiert das letzte Blatt und liefert keinen Fehler.
Auch folgender Sachverhalt ist amüsant und erstaunlich:
Der Teilnehmer der VBA-Schulung wollte die Anzahl der Zeilen eines Bereiches mit:
MsgBox ActiveSheet.Range(„A1“).CurrentRegion.Rows.Count
ermitteln. Statt dessen vertippte er sich und schrieb:
MsgBox ActiveSheet.Range(„A1“).CurrentRegion.Count
CurrentRegion.Count liefert die Anzahl der Zellen des Bereichs. Ich hätte etwas anderes erwartet:
Heute in der VBA-Schulung hat ein Teilnehmer etwas Interessante ausprobiert und festgestellt:
Eine InputBox gibt einen String zurück. Wandelt man ihn mit CInt in eine Integerzahl um oder übergibt ihn an eine vom Typ Integer* deklarierte Variable, so wird der eingegebene Wert 1.5 als 15 zurückgegeben. Ich hätte gedacht, dass 1.5 als Datum (01.05.2016) interpretiert wird. Nein! – Auch jeder andere Wert mit einem Punkt bekommt diesen gelöscht: Punkt ist weder Dezimaltrennzeichen, Tausendertrennzeichen noch Datumstrennzeichen. Erstaunlich, nicht?
Punkte verschwinden
*) Übrigens auch bei allen anderen Zahlenformaten: Byte, Long, Single, Double und Currency
Hätten Sie es gewusst? Oder verstanden? Wie lautet die VBA-Eigenschaft (oder -Methode) für klassisches Design einer Pivottabelle?
Klassisches Design
Die Antwort: Der Makrorekorder leistet hier gute Dinge:
With ActiveSheet.PivotTables(„xyz“)
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
Ich gestehe: Alleine wäre ich nie darauf gekommen.
Der Makrorekorder zeichnet manchmal erstaunlich Dinge auf. Vor allem ist unverständlich, warum er für den gleichen Befehl völlig unterschiedlichen Code aufzeichnet. In der letzten VBA-Schulung stellten wir fest, dass Einfügen / PivotTable in Excel 2010 entweder folgendes Makro aufzeichnet:
Sub Pivot01()
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
„Datenbank!R1C1:R76C6″, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=“Tabelle1!R3C1″, TableName:=“PivotTable1“, _
DefaultVersion:=xlPivotTableVersion14
End Sub
Oder folgendes:
Sub Pivot2()
Sheets.Add
ActiveWorkbook.Worksheets(„Tabelle1“).PivotTables(„PivotTable1″).PivotCache. _
CreatePivotTable TableDestination:=“Tabelle2!R3C1“, TableName:= _
„PivotTable2“, DefaultVersion:=xlPivotTableVersion14
End Sub
Je nachdem, ob man auf das obere Symbol oder das untere klickt …
Übrigens: In Excel 2013/2016 gibt es nur noch ein Symbol für Pivottabelle.
Hallo Herr Martin,
irgendwie sind meine Eigenschaften verschwunden. Können Sie mir bitte helfen:
Die Antwort: Klicken Sie auf das [+]-Symbol – schon öffnet sich die Liste der Eigenschaften:
Ich habe drei mal hinschauen müssen, bis ich den Fehler in folgender VBA-Codezeile gesehen habe:
Was mich stutzig gemacht hat, war, dass der Kommentar nicht grün formatiert war. Ein Nachzählen der Gänsefüßchen hat dann ergeben, dass beim benutzerdefinierten Zahlenformat (Kilometer) nur zwei und nicht drei Anführungszeichen nach dem Text stehen. Das Textende wird durch das Ende der Zeile definiert …
Und schon geht es:
Ich liebe VBA. Vielleicht weil ich diese Sprache so gut kann und deshalb schnell programmieren kann.
Ich hasse VBA. Manchmal. Vor allem, wenn ich nicht verstehe warum Dinge passieren:
Ein Listenfeld wird mit Werten gefüllt. Per Programmierung selektiere ich einen der Einträge. Dabei ist es gleichgültig, ob ich die Eigenschaft Value verwende oder von der Sammlung Selected den Wert k auf True setze. In der nächsten Zeile zeigt die Überprüfung, dass der Value der Listbox = „“.
Dann hasse ich VBA sehr! Vor allem: bei den übrigen Listenfeldern funktioniert es – lediglich bei einem nicht.
Trotzdem: in der Liste ist er ausgewählt. Verstehe ich das? Nein!
Ich gestehe – ich mache es nicht sehr oft. Aber manchmal muss es sein. Code per Makro erzeugen. Oder auf vorhandenen Code zugreifen. Ich wollte alle Makros löschen – nein nicht indem ich die xlsm-Datei als xlsx speichere – die Datei war noch im Format xls und sollte es auch bleiben.
Also: erster Test – und schon eine erstaunliche Fehlermeldung:
Eine Zeile Code und schon ein Fehler
Was bitte heißt das: „der programmatische Zugriff auf das Visual Basic-Projekt ist nicht sicher.“? Kurz überlegt – klar: Ich muss in den Optionen einstellen, dass ich dem Zugriff auf das VBA-Objektmodell vertraue – und schon klappt es …
24 VBComponents findet er.
Hallo zusammen, liebe VBA-Gemeinde,
Ist das schon einmal jemandem aufgefallen? Auf einer UserForm wird dynamisch ein Bild angezeigt:
Me.imgBild.Picture = LoadPicture(strPfad & strBilder(i))
Damit kann man eine kleine Bilderschau erzeugen.
Jedoch: Klickt man auf das Bild, funktioniert der LoadPicture-Befehl nicht mehr … Okay, okay … DAMIT kann ich leben!
Gestern hat mich eine Teilnehmerin in der VBA-Schulung darauf aufmerksam gemacht, dass man auf dem Mac unter Excel 2016 nicht so programmieren kann, wie unter Windows. Und tatsächlich – die Suche ergibt beispielsweise (http://peltiertech.com/office-2016-for-mac-is-here/):
Ein Kommentar zu Excel 2016 auf Mac.
Hi. Was heißt hier „Objekt erforderlich“. Ich finde den Fehler in der Zeile
lngZeilen = xlZielZelle.CurrentRegion.Rows.Count
nicht. Einige Zeilen zuvor habe ich doch gesetzt:
Set xlZielZelle = xlZielBlatt.Range(„A1“)
Warum mag VBA das Objekt nicht?
Wo ist das fehlende Objekt?
Die Antwort: Dahinter liegt ein hübscher Denkfehler: Es ist richtig: Sie setzen
Set xlZielZelle = xlZielBlatt.Range(„A1“)
Allerdings mit
xlZielBlatt.Rows(„1:3“).Delete Shift:=xlUp
löschen Sie drei Zeilen, damit auch die Zelle A1 und damit wiederum den Verweis auf diese Zelle. Also – einfach noch einmal setzen nach dem Löschen:
Set xlZielZelle = xlZielBlatt.Range(„A1“)
Dann klappt es.
Hallo zusammen. Ich habe ein paar Zeilen VBA-Code abgetippt, den ich in einer Zeitschrift gefunden habe. Aber es läuft nicht. Und ich weiß nicht warum. Kann mir einer helfen?
geht nicht!?!
Die Antwort ist simpel: Wenn Sie genau hinschauen, sehen Sie, dass Sie die Funktion DateSerial nicht richtig geschrieben haben. Sie haben die Ziffer „1“ als letztes Zeichen geschrieben und nicht den Buchstaben „l“ („L“). Ein Tipp zum Codeerstellen: Schreiben Sie immer in Kleinbuchstaben. Wenn Sie die Zeile verlassen, werden sämtliche Schlüsselwörter in Groß- und Kleinschreibung konvertiert. Wenn Sie sich vertippt haben, fällt das sofort auf, weil dann der Text nicht geändert wird.
Und ich rede! – Wer kann mir helfen?
Manchmal darf ich keine Makros aufzeichnen? Warum? Nur der Makrorekorder ist deaktiviert. Seltsam …
Makrorekorder deaktiviert
Die Antwort: Mit Sicherheit „läuft“ noch ein Makro. Das heißt: Es wurde angehalten. Man muss es erst stoppen. Dann darf man ein neues aufzeichnen.
Stopp – dann geht es weiter.
Hübsche Frage heute in der Excel-VBA-Schulung: Man kann zwar den „Bezeichnertext“ in Extras / Optionen farblich kennzeichnen – aber kann man auch zwei verschiedene Farben für VBA-Objekte (beziehungsweise Methoden und Eigenschaften) einerseits und einen Namen (zum Beispiel Variablen, Prozedurnamen, …) andererseits unterscheiden. Leider nein – es geht nur eine Farbe. Zugegeben – mich hat das noch nie gestört – ich lasse immer die voreingestellte Farbe Schwarz. So etwas wie unten sichtbar würde ich nicht machen.
Lustige (?!?) Farben
Hallo Herr Martin,
ich habe Ende letzten Jahres an einem Ihrer Seminare (Makros für Anwender) teilgenommen. Es war ein super Seminar. Vielen Dank für diese tolle Erfahrung!
Ich habe mich nun an das erste Makro gesetzt. Leider treten hier jedoch einige Fehler auf. Jedoch ist es mir bisher nicht möglich gewesen, diese Fehler durch Internetrecherche zu beheben.
Da Sie bei dem Seminar auch das Angebot geäußert haben, dass man sich bei Fragen auch nach dem Seminar an Sie wenden kann, würde ich hiermit auf dieses Angebot zurück kommen.
Könnten Sie mir hier vielleicht helfen? Mir ist bewusst das es ein langer Code ist, deshalb bitte einfach sagen, wenn Ihnen dass zu viel ist (da es mir nicht möglich ist, hierfür etwas zu bezahlen).
Ich habe bei diesem Code auch bereits jeden einzelnen Schritt mit einer Bemerkung beschrieben. Anbei der Code:
Sub CM()
‚beginnt das Makro
Workbooks.Open Filename:= _
„I:\Daten\PERSONAL\DATEN\Pers_Entwicklung\Dokumentation\sonstige Schulungen\sonstige_Schulungen.XLS“
‚öffnet Datei sonstige_Schulungen.XLS
Workbooks.Open Filename:= _
„I:\Daten\PERSONAL\DATEN\Pers_Entwicklung\Dokumentation\sonstige Schulungen\Makro\sonst._Schulungen_für_Makros.xlsx“
‚öffnet Datei sonst._Schulungen_für_Makros.xlsx
Workbooks(„sonstige_Schulungen.XLS“).Sheets(„alle“).Range(„B7“).AutoFilter Field:=2, Operator:=xlFilterNoFill
‚filtert in Datei sonstige_Schulungen.XLS, Registerblatt „alle“ in B7 alle Zellen nach Zellen ohne Füllung -> blendet alle Zellen, die als LZK, Austritt oder EZ/MU markiert sind, aus
Workbooks(„sonstige_Schulungen.XLS“).Sheets(„alle“).Range((„A1“), ActiveCell.SpecialCells(xlLastCell)).Copy
‚kopiert in Datei sonstige_Schulungen.XLS, Registerblatt „alle“ die Zellen A1 bis zur letzten gefüllten Zelle
Workbooks(„sonst._Schulungen_für_Makros.xlsx“).Sheets(„sonstige Schulungen“).Range(„A1“).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transponse:=True
‚fügt den kopierten Bereich aus Datei sonstige Schulungen.XLS in Datei sonst._Schulungen_für_Makros.xlsx, Registerblatt „sonstige Schulungen“ (Zellen & Spalten vertauscht) in Zelle A1 ein
Workbooks(„sonst._Schulungen_für_Makros.xlsx“).Sheets(„sonstige Schulungen“).Range(„G11“).AutoFilter Field:=7, Criteria1:=Array(„CM1-I“, „CM1-S“, „CM2-I“, „CM2-S“), Operator:=xlFilterValues
‚filtert in Datei sonst._Schulungen_für_Makros.xlsx, Registerblatt „sonstige Schulungen“ in G11 in allen Zellen nach den Zellen mit den genannten „Kürzeln“
Workbooks(„sonst._Schulungen_für_Makros.xlsx“).Sheets(„sonstige Schulungen“).Range(„A1“, ActiveCell.SpecialCells(xlLastCell)).Copy
‚kopiert in Datei sonst._Schulungen_für_Makros.xlsx, Registerblatt „sonstige Schulungen“ die Zellen A1 bis zur letzten gefüllten Zelle
Workbooks(„sonst._Schulungen_für_Makros.xlsx“).Sheets(„CM“).Range(„A1“).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transponse:=True
‚fügt den kopierten Bereich aus sonst._Schulungen_für_Makros.xlsx, Registerblatt „sonstige Schulungen“ in Registerblatt „Kürzel“ (Zellen & Spalten vertauscht
‚-> somit wieder ursprüngliche Form, wie in Datei sonstige_Schulungen.XLS) in Zelle A1 ein
Workbooks(„sonstige_Schulungen.XLS“).Close
’schließt Datei sonstige_Schulungen.XLS
Workbooks(„sonst._Schulungen_für_Makros.xlsx“).Sheets(„sonstige Schulungen“).Range(„G11“).AutoFilter Field:=7
‚löscht in Datei sonst._Schulungen_für_Makros.xlsx, Registerblatt „sonstige Schulungen“, in Zelle G11 den Filter
Workbooks.Open Filename:= _
„I:\Daten\PERSONAL\DATEN\Pers_Entwicklung\Dokumentation\sonstige Schulungen\Abteilungen\ISCH-CM.xlsx“
‚öffnet Datei ISCH-CM.xlsx
‚Beginn für Schulung CM1
[…]
#####
Hallo Frau R. (haben wir nicht du gesagt im letzten Jahr?),
ich habe Ihr Makro mal „angetestet“ – beim Befehl:
‚fügt den kopierten Bereich aus Datei sonstige Schulungen.XLS in Datei sonst._Schulungen_für_Makros.xlsx, Registerblatt „sonstige Schulungen“ (Zellen & Spalten vertauscht) in Zelle A1 ein
muss der Parameter Transpose und nicht TranspoNse heißen. Dann klappt es.
Ansonsten:
1. Wenn Sie ein Problemchen haben, verwenden Sie den Debugger – also: [F8], [F8], [F8], … Dann bleibt er in der Zeile stehen, in der der Fehler auftritt. Ich weiß – die Meldung die hier erscheint, ist nicht aussagekräftig – ich habe auch zwei Mal hinschauen und „rumspielen“ müssen
2. Ich helfe Ihnen gerne – einfach schreiben!
schöne Grüße aus München
Rene Martin
######
Hallo Rene,
leider habe ich beim Verwenden des Debuggers bereits bei diesem Befehl eine Fehlermeldung, aus der ich nicht schlau werde.
Workbooks(„sonstige_Schulungen.XLS“).Sheets(„alle“).Range((„A1“), ActiveCell.SpecialCells(xlLastCell)).Copy
‚kopiert in Datei sonstige_Schulungen.XLS, Registerblatt „alle“ die Zellen A1 bis zur letzten gefüllten Zelle
Hast du hier auch eine Idee, wo der Fehler liegt?
#####
ja, Melanie,
wenn Du „A1“ schreibst, dann sitzt vielleicht der Cursor auf der Zelle eines anderen Blattes (ActiveCell) und nicht auf dem Blatt „alle“. Versuche mal Folgendes:
Workbooks(„sonstige_Schulungen.XLS“).Sheets(„alle“).Range(Workbooks(„sonstige_Schulungen.XLS“).Sheets(„alle“). („A1“), Workbooks(„sonstige_Schulungen.XLS“).Sheets(„alle“).Range(„A1“).SpecialCells(xlLastCell)).Copy
Du weißt, was ich dazu sagen werde – kein schöner Code – aber für den Anfang okay 😉
klappt das?
Ja so funktioniert es 🙂
Jedoch kommt jetzt hier die Fehlermeldung „Objekt unterstützt diese Eigenschaft oder Methode nicht“.
Workbooks(„ISCH-CM.xlsx“).Sheets(„CM1“).Columns(„A:B“).Paste
‚fügt die kopierten Spalten aus Datei sonst._Schulungen_für_Makros.xlsx, Registerblatt „CM“ in Datei ISCH-CM.xlsx, Registerblatt „CM1“, in die Spalten A und B ein
Hallo Melanie,
die Methode Paste darf nicht auf die Spalten, also den Bereich angewendet werden.
Wenn Du den Makrorekorder verwendest, dann zeichnet er etwas auf wie:
Selection.Copy
Range(„B10“).Select
ActiveSheet.Paste
Das heißt: der Befehl muss lauten:
Workbooks(„ISCH-CM.xlsx“).Sheets(„CM1“).Columns(„A:B“).Select
Workbooks(„ISCH-CM.xlsx“).Sheets(„CM1“).Paste
oder:
Workbooks(„ISCH-CM.xlsx“).Sheets(„CM1“).Activate
ActiveSheet.Range(“A1”).Select
ActiveSheet.Paste
klappt das?
#####
Hallo Rene,
super jetzt funktioniert es :-)! Vielen Dank!
Liebe Grüße
In VBA bedeutet die Collection Workbooks einmal die Sammlung aller schon offenen Dateien, einmal die Sammlung der noch nicht offenen Dateien. Ist das nicht unlogisch?
Workbooks – wat is dat?
Das ist richtig – das ist nicht ganz glücklich formuliert. Gemeint ist: Workbooks.Count, beziehungsweise Workbooks(1) aus der Sammlung der Dateien wird die Dateianzahl, beziehungsweise das Element mit der Nummer 1 herausgegriffen. Workbooks.Open, beziehungsweise Workbooks.Add bedeutet: Zu der Sammlung wird ein neues Element hinzugefügt (eine neue Instanz eingefügt). Sie haben recht: vielleicht hätte man zwei verschiedene Begriffe wählen sollen – Programmieranfänger wundern sich immer ein wenig …
Ich gebe zu: so ganz konsequent ist das nicht in VBA. Für Methoden gibt es drei Schreibweisen: Nach einer Methode folgt nichts. Nach einer Methode steht ein Parameter, dann muss ein Leerzeichen geschrieben werden. Nach einer Methode steht ein Parameter und die Methode gibt einen Wert (oder ein Objekt) zurück – dann muss eine Klammer geschrieben werden. Dass es sich bei den drei Befehlen um eine Methode handelt, sieht man, wenn man den Namen der Klasse VBA angibt, also VBA.Beep, VBA.MsgBox, …
Drei Methoden
So weit so gut. Erstaunlicherweise kann man auch bei Methoden, die nichts zurückgeben eine Klammer schreiben:
Das funktioniert.
Noch erstaunlicher wird es jedoch, wenn eine Methode mehrere Parameter hat – dann ist die Klammer verboten:
Klammer nicht bei mehreren Parametern!
Noch erstaunlicher ist es, dass einige Methoden kategorisch die Klammer verbieten:
So nicht!
Aber eben nicht alle Methoden.
Zusammenfassung: Meine Empfehlung: Wenn eine Methode einen Wert zurück gibt, schreibe ich IMMER eine Klammer, wenn sie nichts zurück gibt, schreibe ich NIE eine Klammer. DAS funktioniert immer – ohne Ausnahme. Ehrenwort!
Eigentlich schade, dass Microsoft es in VBA 7.1 – also nach Excel 97 – 2000 – 2002 – 2003 – 2007 – 2010 – 2013 noch nicht geschafft hat einen kurzen Erklärungstext im Objektkatalog von VBA zu liefern, was diese Funktion denn bedeutet.
Leider kein Hilfetext
Man muss die Funktionstaste F1 drücken und gelangt dann zur Onlinehilfe von Microsoft, wo die Funktion erklärt wird.
Die Onlinehilfe
In einem Programm, das ich in VBA geschrieben habe, erscheint manchmal die Fehlermeldung: Der Remote-Server-Computer existiert nicht oder ist nicht verfügbar. Wo bitte steht denn mein Remote-Server-Computer?
Wo ist der Remote-Server-Computer?
Diese Meldung kann zwei Ursachen haben. Entweder Sie greifen mit einer Objektvariablen auf ein anderes Programm (beispielsweise Word oder Access) zu und schließen per Hand (oder per Programmierung) dieses Programm.
Oder Sie greifen auf ein anderes Programm zu und löschen nicht „sauber“ die Objektvariablen. Auch wenn Microsoft behauptet, dass eine Garbage-Collection die Variablen „sauber“ putzen würde, stelle ich ab und zu (nicht immer!) fest, dass dies nicht der Fall ist. Also, wenn Sie beispielsweise per Programmierung Word öffnen (Sie müssen natürlich einen Verweis auf die Word-Bibliothek setzen):
Dim wdApp As Word.Application
Dim wdDatei As Word.Document
Set wdApp = New Word.Application
wdApp.Visible = True
Set wdDatei = wdApp.Documents.Add
Dann sollten Sie am Ende sämtliche Objektvaribalen leeren. Und zwar so.
Set wdDatei = Nothing
Set wdApp = Nothing
Und bitte auch in der richtigen Reihenfolge – von „klein“ nach „groß“.
Wenn Sie in VBA programmiert haben und wenn Sie dort Listenfelder und Kombinationsfelder erstellt habe, haben Sie sicherlich schon entdeckt, dass beide über die Sammlung Column und Row verfügen, die Listenfelder auch über die Sammlung Selected. Das erstaunt, weil Sammlungen eigentlich immer ein Plural-s besitzen: Rows, Worksheets, Workbooks, Cells, Charts, …
Ebenso erstaunt, dass die Zählung der Elemente bei 0 beginnt und nicht bei 1 wie bei allen anderen Sammlungen. Auch das Hinzufügen eines neuen Elementes heißt AddItem und nicht Add.
Ich vermute, dass diese beiden Steuerlemente von einem Drittanbieter hinzugekauft wurden, der etwas andere Konventionen verwendet hat.
Ein Kombinatonsfeld
Kennen Sie das? Sie verwenden ein Objekt in VBA, setzen den Punkt dahinter, aber VBA verweigert die Anzeige der Auflistung.
Während Range(„A1“). funktioniert, geht es beispielsweise bei Cells(1, 1) nicht.
Während es bei ActiveWorkbook klappt, funktioniert es bei ActiveSheet nicht.
Ich weiß nicht, warum es bei den meisten Objekten funktioniert, bei einigen jedoch nicht. Aber ich weiß, wie man immer die Aufzählungsliste angezeigt bekommt:
Verwenden Sie eine Objektvariable. Also beispielsweise so:
Dim xlBlatt As Worksheet
Set xlBlatt = ActiveSheet
xlBlatt. <- Hier funktioniert es
Dim xlZelle As Range
Set xlZelle = Cells(1, 1)
xlZelle <- Hier funktioniert es
Nicht immer erhält man die Listen der Eigenschaften und Methoden.
Kennen Sie das? Wenn Sie einen Befehl – einen einzigen Befehl mit dem Makrorekorder aufzeichnen, zeichnet Excel sehr, sehr viel Code auf. Warum?
Wenn Sie beispielsweise den Befehl aufzeichnen – Schrift ändern, dann erhalten Sie:
With Selection.Font
.Name = „Arial“
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Wenn Sie aufzeichnen „schreibe in die Kopfzeile den Firmennamen“ erhalten Sie über 50 Zeilen Code für einen Befehl (ActiveSheet.PageSetup.RightHeader = „xyz“ hätte genügt):
… zu viel Code …
Der Grund: Excel zeichnet an vielen Stellen nicht den einen Befehl, sondern sämtliche Befehle des Dialogfeldes auf, also beispielsweise „Zellen formatieren“ oder „Seite einrichten“. Nun sind Sie gefragt, um die nicht nötigen Zeilen zu löschen.
Was passiert denn hier? Ich zeichne mit dem Makrorekorder etwas auf, rufe den Befehl auf – und erhalte eine Fehlermeldung.
Die Antwort: Das ist richtig. An einigen (zugegeben, sehr wenigen) Stellen zeichnet Excel falsch auf. Wenn Sie beispielsweise die Datenüberprüfung mit einer Formel aufzeichnen:
Makrorekorder
zeichnet Excel folgenden Code auf:
With Selection.Validation
.Delete
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlLessEqual, Formula1:=“=TODAY(“
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = „“
.ErrorTitle = „“
.InputMessage = „“
.ErrorMessage = „“
.ShowInput = True
.ShowError = True
End With
Erstaunlicherweise fehlt nach dem TODAY die schließende Klammer.
Oder Sie zeichnen die Formel auf:
=rechts(F2;1)<>“ “
also – verbiete am Ende des Textes ein Leerzeichen.
Der Makrorekorder
Dann zeichnet der Makrorekorder auf:
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=“=rechts(F2;1)<>““ „““
Der korrekte Befehl müsste jedoch lauten:
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=“=right(F2,1)<>““ „““
Also in der US-amerikanischen Schreibweise. Der Makrorekorder schafft die Übersetzung an dieser Stelle leider nicht.
Das ist richtig: Eine Dinge werden nicht aufgezeichnet. Beispielweise die Eingabe der Eigenschaften. Wenn Sie diesen Befehl aufzeichnen, erhalten Sie ein leeres Makro:
Der Makrorekorder zeichnet nicht auf!
Sie müssen dann in der Hilfe, im Objektkatalog, im Internet oder in einem guten Buch nachschlagen wie der zugehörige VBA-Befehl lautet.
