Heiligenschein abgenommen .. Das Ding drückt immer so auf die Hörner …

Moin Renè,

schon mal versucht per VBA eine bedingte Formatierung zu setzen.
Das geht ohne Probleme, solange man keine Formeln eingibt, die eine Funktion enthalten, z.B.

rng.FormatConditions.Add Type:=xlExpression, Formula1:=“=UND(D15=0;E15=1;F15=0)“  


JAAA, das funktioniert … aber schick die Datei mal an jemanden, der englische Sprache eingestellt hat. Das funktioniert das dann nicht mehr!


Im VBA-Code muss ich, wenn als Sprache Deutsch eingestellt habe, die Formel auch mit dem deutschen Funktionsnamen eingeben, ansonsten ignoriert Excel die Bedingung . WTF


Schon vor dem gleichen Problem gestanden/gesessen? Ich habe aktuell das Problem, dass ich für eine Firma arbeite, deren Mitarbeiter unterschiedliche Sprachen in Office eingestellt haben, eigentlich habe ich keine Lust jetzt immer prüfen zu müssen welche Sprache gerade aktuell ist und entsprechend die Formeln zu setzen….

mannmannmann

Viele Grüße

Christian

####

Hi Christian,

Warum schreibst du nicht:

rng.FormatConditions.Add Type:=xlExpression, Formula1:=“=AND(D15=0;E15=1;F15=0)“  

Allerdings: wenn die Amis Komma als Trennzeichen eingeschaltet haben, dann läuft das Teilchen auch an die Wand. Übrigens: ebenso die Formeln in der Datenüberprüfung.

Aber das bekommt man mit

Application.International(xlListSeparator)

heraus. Alles andere auch:

https://learn.microsoft.com/de-de/office/vba/api/excel.xlapplicationinternational

(Gruß aus der internationalen Hölle: deutsches Excel; englische Zahlen: 1,234.56)

Übrigens: früher lief der Makrorekorder an die Wand, wenn ich aufgezeichnet habe:

Datenüberprüfung / benutzerdefiniert / =Heute()

Inzwischen klappt das: aufzeichnen und abspielen lassen.

Ich fürchte, du kommst um eine Prüfung nicht rum …

Oder: schreib die englische (!) Formel in eine Zelle; Excel wandelt sie in die Oberflächensprache um und lies die Formel von dort wieder aus:

    ActiveCell.Formula2 = „=AND(D15=0,E15=1,F15=0)“

    MsgBox ActiveCell.Formula2

####

Hunde, die schellen, beißen nicht.

Guten Tag

Ich habe genau dieses Problem, mit dem Löschen verhindern von einzelnen Tabellenblättern.

Die hier gezeigte Lösung erscheint recht logisch und einfach und wollte es auch in meiner Datei ausprobieren.

Habe den Code so übernommen (im Case-Bereich dann die Tabellenblätter die Benennungen angepasst)

Also:

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
    On Error Resume Next
    Select Case Sh.CodeName
        Case "tbl_Unternehmen", "tbl_Standorte", "tbl_Zuordnung1", "tbl_Organisationseinheit", "tbl_Zuordnung2", "tbl_Geschaeftsprozesse", "tbl_Zuordnung3", "tbl_Uebungstyp", "tbl_Szenario", "tbl_Verantwortlich", "tbl_Uebungsplanung", "tbl_Zuordnung4"
            MsgBox "Bitte löschen Sie nicht das Tabellenblatt """ & Sh.Name & """!", vbCritical
            ThisWorkbook.Protect
    End Select
End Sub

Nur bei Sh.CodeName gibt es mir für CodeName immer „DieseArbeitsmappe“ an, statt den den eigentlichen Tabellenname.

Was mache ich falsch?

Vielen Dank für einen Tipp

Freundliche Grüsse

####

Hallo Herr W.,

Sie sind in „DieseArbeitsmappe“?

Sie sind im Ereignis: SheetBeforeDelete?

Testen Sie mal am besten in einer anderen, leeren Datei):

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
	MsgBox Sh.CodeName
        MsgBox TypeName(Sh)
End Sub

Bei mir wird IMMER der Codename des Blattes angezeigt.

Bei Ihnen?

Liebe Grüße Rene Martin

Schweinefleisch kann man jahrelang frisch halten, indem man die Sau am Leben lässt.

Ich glaube, das hatte ich schon einmal!

Ich suche per VBA eine Kostenstelle in einer anderen Liste und benötige die Zeilennummer, weil ich aus dieser Zeile mehrere Informationen auslesen will:

So steht beispielsweise der Wert von A2 (4711) in Zeile 5.

Sub Kostenstellensuche()
    Dim strKostenstelle As String
    Dim lngZeile As Long
    
    strKostenstelle = ActiveSheet.Range("A2").Value
    
    If Application.WorksheetFunction.CountIf(ActiveSheet.Columns("D"), strKostenstelle) > 0 Then
        lngZeile = Application.WorksheetFunction.Match(strKostenstelle, ActiveSheet.Columns("D"), 0)
        MsgBox lngZeile
    End If

End Sub

Das Programm läuft leider an die Wand:

Die Match-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden.

Der Grund: da ich nicht sicher bin, ob die Kostenstelle immer als Zahl vorliegt, habe ich sie in einer String-Variablen gespeichert.

Für CountIF (ZÄHLENWENN) stellt dies kein Problem dar; allerdings für Match (Vergleich). Also prüfen:

Sub Kostenstellensuche()
    Dim strKostenstelle As String
    Dim lngZeile As Long
    
    strKostenstelle = ActiveSheet.Range("A2").Value
    
    If Application.WorksheetFunction.CountIf(ActiveSheet.Columns("D"), strKostenstelle) > 0 Then
        If IsNumeric(strKostenstelle) Then
            lngZeile = Application.WorksheetFunction.Match(CLng(strKostenstelle), ActiveSheet.Columns("D"), 0)
        Else
            lngZeile = Application.WorksheetFunction.Match(strKostenstelle, ActiveSheet.Columns("D"), 0)
        End If
        MsgBox lngZeile
    End If

End Sub

Und DAS funktioniert!

Men are like mascara, they usually run on the first sign of emotion.

Der Auftrag hörte sich einfach an: Der Kunde wollte ein Add-In, welches alle Dateien aus allen Unterordnern vom firmeneigenen Sharepoint herunterlädt und in bestimmten Zellen Werte einfügt.

Der Knackpunkt war: Sharepoint!

Ich habe lange getüftelt, wie ich „auf den Sharepoint komme“, wie ich die Ordner und Unterordner und die dort befindlichen Dateien auslesen könne. Und herunterladen und bearbeiten.

Irgendwann kam mir die Idee: nicht mit VBA und DIR oder den FileScription-Objekt auf den Ordner losgehen, sondern mit Power Query! Damit kann man leicht alle Dateien aller Unterordner auslesen und auflisten. Der Befehl

SharePoint.Files

macht es möglich. Dieses Power Query-Skript kann leicht mit VBA aufgerufen werden (der Makrorekorder zeigt, wie das funktioniert:

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=qry_Sharepoint_Dateien;Extended Properties=""""", _
            Destination:=ActiveSheet.Range("$B$5")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [qry_Sharepoint_Dateien]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = strTabellenname
        .Refresh BackgroundQuery:=False
    End With

Das Ergebnis sieht dann so aus:

Und nun gestaltet sich der Zugriff auf die Dateien leicht:

Set xlDateiZugriff = Application.Workbooks.Open ...

Ändern, speichern (eigentlich nicht nötig) und schließen.

Geht doch!

Zugegeben: Sharepoint Zickt! Das hat auch Martin Weiß (https://www.tabellenexperte.de/) bestätigt:

  • Manchmal stürzt das Programm ab!
  • Manchmal öffnet er nicht (obwohl die Datei vorhanden)
  • Manchmal schließt er nicht. Macht einfach nicht mehr zu!

Dennoch: Meistens klappt es. Und schließlich: Excel muss halt ab und zu nerven …

Pärchen sind Leute, die als Singles versagt haben.

Erstaunt!

In einer Excelmappe (Prinz) befinden sich mehrere Verknüpfungen auf andere Dateien:

Da diese Datei per VBA weiter verarbeitet werden soll, müssen die Verknüpfungen gelöscht werden. Beispielsweise so:

    Dim arrLinks As Variant
    Dim i As Integer

    arrLinks = ThisWorkbook.LinkSources(xlLinkTypeExcelLinks)
    
    If Not VBA.IsEmpty(arrLinks) Then
        For i = 1 To UBound(arrLinks)
            'MsgBox ThisWorkbook.LinkSources(xlLinkTypeExcelLinks)(i)
            ThisWorkbook.BreakLink Name:=arrLinks(i), Type:=xlLinkTypeExcelLinks
        Next
    End If

Die Verknüpfungen werden gelöscht – bis auf eine !?!

Ich schaue nach: diese Verknüpfung liegt auf einem geschützten Blatt.

Was mich irritiert ist, dass keine Fehlermeldung die Folge war: VBA übergeht einfach die Tatsache, dass die Verknüpfung nicht gelöst werden kann. Also hebt man den Blattschutz auf (und merkt ihn sich vorher):

    Dim blnBlattStatus() As Boolean
    i = ThisWorkbook.Worksheets.Count
    ReDim blnBlattStatus(i)


    For i = 1 To ThisWorkbook.Worksheets.Count
        blnBlattStatus(i) = ThisWorkbook.Worksheets(i).ProtectContents
        If ThisWorkbook.Worksheets(i).ProtectContents = True Then
            ThisWorkbook.Worksheets(i).Unprotect 
        End If
    Next

Und setzt ihn am Ende wieder:

    For i = 1 To ThisWorkbook.Worksheets.Count
        If blnBlattStatus(i) = True Then
            ThisWorkbook.Worksheets(i).Protect
        End If
    Next

Und schon sind alle Verknüpfungen gelöscht!

Bitte keine Beischlafanfragen mehr – mein Kalender ist voll!

Guten Tag Herr Martin

Ich hoffe ihnen geht es gut.

Könne Sie mir nochmals sagen, wo ich die Funktion finde wie gross eine Excel-Datei ist

  • Anzahl Register
  • Benötigte Zellen
  • Anzahl Formeln

Danke

Freundliche Grüsse

####

Hallo Herr S.,

Sie meinen die VBA-Befehle, mit denen man das herausbekommt?

– Anzahl der Tabellenblätter (Register):

ThisWorkbook.Sheets.Count

– Benötigte Zellen:

UsedRange

oder:

Range(„A1“).SpecialCells(xlCellTypeLastCell)

ist die letzte Zeile. Sie hat die Eigenschaften Row und Column – also die Nummer der Zeile und die Nummer der Spalte. Noch weiter rechts, bzw. unten gibt es nichts.

– Anzahl Formeln

dafür gibt es keinen einfachen Befehl. Entweder Sie durchlaufen alle verwendeten Zellen in UsedRange

Option Explicit

Sub AnzFormeln()

    Dim xlBlatt As Worksheet

    Dim xlZelle As Range

    Dim lngAnzahl As Long

    Set xlBlatt = ActiveSheet

    For Each xlZelle In xlBlatt.UsedRange

        If xlZelle.HasFormula Then

            lngAnzahl = lngAnzahl + 1

        End If

    Next

    MsgBox „Anzahl der Zellen mit Formeln: “ & lngAnzahl

End Sub

Hilft das?

Hab auf meinem Briefkasten nun auch so einen Aufkleber versehen: Nur Werbung! Keine Rechnungen!

Manchmal bin ich erstaunt, was Firmen (beziehungsweise Menschen, die dort arbeiten), sich so alles wünschen. Beispielsweise folgender Wunsch:

Firmenweit existiert ein Excel-Add-In:

Der XML-Code des Ribbons sieht folgendermaßen aus:

Nun – so lautete der Wunsch – soll ich ein zweites Add-In erstellen. Allerdings soll es sich in das erste einklinken. Also die gemeinsame Registerkarte „Schneewittchen und die sieben Zwerge“ verwenden.

Wie das?

Ich dachte nicht, dass das funktioniert.

Ein Dankeschön an Markus Hahner (www.hahner.de) – er hat mir die Lösung geliefert.

Das „Haupt-Addin“ muss mit einem Namespace im Element <customUI> versehen werden – hier:xmlns:x=“Zeichentrick“. Der Name (Zeichentrick) ist beliebig. Dieser Namespace (hier: „x“) wird im Element <tab> verwendet: idQ=“x:tabZwerge“. Dabei ist „tabZwerge“ natürlich auch beliebig.

Die andere Datei, also das andere Add-In, sieht dann folgendermaßen aus:

Also: Namespace in customUI und idQ sind identisch. Das Label, also die Beschriftung der Registerkarte, muss natürlich nicht wiederholt werden.

Und schon klinkt es sich ein:

Nachdem ich das Problem gelöst hatte, hatte ich doch glatt im Internet eine weitere Beschreibung gefunden:

http://www.cls-software.de/tipps_accessRibbons.php

Zum Thema Wintersport: Gebt Springern eine Schanze.

Ich habe ein Tabellenblatt mit der Eigenschaft xlSheetVeryHidden ausgeblendet. Und wollte es eigentlich nur schnell einblenden:

Und erhalte die nichtssagende Fehlermeldung

Die Visible-Eigenschaft des Worksheet-Objektes kann nicht festgelegt werden.

Ich grüble, was das bedeutet. Dann dämmert es mir: in der Datei wurde der Arbeitsmappenschutz aktiviert.

Also: raus damit und schon darf ich das Blatt wieder einblenden.

Ich brauche keine Drogen oder Alkohol. In meinem Alter erreicht man denselben Effekt, wenn man zu schnell aufsteht.

Es ist mühsam, fremde Projekte zu übernehmen und weiter zu bearbeiten.

Ein Kunde kam auf mich zu und wollte, dass ich ein kleines VBA-Projekt, das ein Kollege, der nun nicht mehr in der Firma ist, weiterführe.

Eines der Aufgaben war: Das Tabellenblatt „Wiederanlaufzeit Prozess“ wird nicht mehr benötigt.

Da ich unsicher war, ob es nicht irgendwo im Code verwendet wird, blendete ich es einfach aus. Der Effekt: ein Fehler:

Die Ursache:

Irgendwo im Code befand sich die Zeile

Worksheets(1).Select

Hätte der Programmierer

Worksheets(1).Activate

verwendet, wäre dies nicht passiert. Oder noch besser: überprüfen, ob das Blatt, welches markiert werden soll, vorhanden und sichtbar ist!

Wann musst du morgen arbeiten? – Von dunkel bis dunkel.

Manchmal muss man andere Denkwege einschlagen …

Max fragt mich, ob ich ihm helfe könne, per VBA einen PowerBI-Bericht anzuzeigen.

Er möchte in Excel über eine Schaltfläche ein Makro aufrufen, das einen Bericht öffnet, so dass die Anwenderinnen und Anwender den Bericht sehen können:

Er hat den Befehl „Shell“ im Internet gefunden. Richtig: Mit Shell rufe ich Programme auf, die ich nicht direkt über einen eingebundenen Verweis starten kann. Wir machen uns auf die Suche. Wie denn der Pfad auf seinem SharePoint lautet, will ich wissen. Den finden wir heraus. Er hat etwa die Form:

https://contoso.sharepoint.com/sites/contoso/Freigegebene Dokumente/VanArsdel.pbix

Jedoch scheitern sämtliche Versuche, diese Datei mit einem Befehl wie

Shell "https://contoso.sharepoint.com/sites/contoso/Freigegebene Dokumente/VanArsdel.pbix"

zu starten. Nach einer sehr langen Weile dämmert es mir: Man nehme den Originalpfad und rufe ihn mit

ThisWorkbook.FollowHyperlink "https://app.powerbi.com/groups/me/reports/385f2964-2ac8-5f56e1894f8b/ReportSection"

Geht doch! So einfach!

Werbung von der Bank: „Legen Sie Ihr Geld in Immobilien an!“ – Prima Idee! Ich kaufe mir gleich am Montag mit meinem Ersparten ein Eigentumszelt.

Ich erstelle ein Add-In für eine Firma. Es soll eine große Liste per Knopfdruck in Einzelteile zerlegen und diese an bestimmten Stellen auf der Festplatte speichern.

Dazu benötige ich eine eindeutige Liste der Kategorien:

Ich überlege: da die Firma Excel 2016 einsetzt, hat sie noch nicht die Funktion EINDEUTIG. Also erzeuge ich per Programmierung eine Pivottabelle und erhalte so eine (sogar sortierte) Liste der einzelnen Kategorien:

Sub MachePivot()

    Dim xlBlattAktiv As Worksheet
    Dim xlBlattHilf As Worksheet
    Dim xlPivotCache As PivotCache
    Dim xlPivotTabelle As PivotTable
    Dim lngZeilen As Long
    Dim lngSpalten As Long
    
    Set xlBlattAktiv = ActiveSheet
    Set xlBlattHilf = ThisWorkbook.Worksheets.Add

    lngZeilen = xlBlattAktiv.Range("A1").CurrentRegion.Rows.Count
    lngSpalten = xlBlattAktiv.Range("A1").CurrentRegion.Columns.Count

    Set xlPivotCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=xlBlattAktiv.Name & "!R1C1:R" & lngZeilen & "C" & lngSpalten, _
        Version:=8)
    Set xlPivotTabelle = xlPivotCache.CreatePivotTable( _
        TableDestination:=xlBlattHilf.Range("A1"), _
        TableName:="RenesPivot2", _
        DefaultVersion:=8)
        
    xlPivotTabelle.ColumnGrand = False
    xlPivotTabelle.RowGrand = False
    
    With xlPivotTabelle.PivotFields("Kategorie")
        .Orientation = xlRowField
        .Position = 1
    End With

End Sub

Ich teste – und: padautz: Fehler!

Ich brauche eine Weile, bis ich die Ursache finde. In der Liste gab es zwei Mal eine Spalte mit der Bezeichnung „Kommentar“. Excel 2016 schafft es nicht, die Spalten für die Pivottabelle umzubenennen (wie neuere Excelversionen):

Also: dann grenzen wir den Bereich doch ein!

    Set xlPivotCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=xlBlattAktiv.Name & "!R1C1:R" & lngZeilen & "C4" , _
        Version:=8)

Geht doch:

Jedes Jahr pünktlich zu Weihnachten kommt dieser beschissende Coca Cola-Truck vorbei. Jetzt, bei dieser Affenhitze hat ihn noch keiner gesehen!

hallo René,

so wie du es geschrieben hast, habe ich das Makro aufgezeichnet: ändere die Skalierung der y-Achse. Allerdings erhalte ich einen Fehler, wenn ich das Makro laufen lasse:

Hallo Nadine,

wahrscheinlich liegt es daran, dass du das Diagramm nicht markiert hast? (ActiveChart)

Liebe Grüße

Rene

Jawoll! Bewerft den Bastard mit Dreck und Erde! – Oma, bitte! Wir sind hier auf einer Beerdigung!

Amüsiert.

VBA-Schulung. Ich lasse während der Schulung kleine „Lösungen“ programmieren. Ich schaue den Teilnehmern und Teilnehmerinnen über die Schulter. Bei einem der Teilnehmer finde ich die Codezeilen:

    i = 3
    MsgBox ActiveCell(i)

Die ActiveCell ist B1, ActiveCell(3) liefert den Inhalt der Zelle B3, hier: „Peking“:

Das heißt: ActiveCell(1) meint die Zelle selbst, ebenso wie ActiveCell(1, 1). Dies ist also keine Kurzschreibweise für ActiveCell.Offset(1, 1), denn damit wäre C2 gemeint. Auch nicht für Cells(1, 1) – das wäre A1.

Diese Schreibweise gehört dann zur Kategorie ActiveCell.Range(„A1“), womit B1, also die aktive Zelle gemeint ist (A1 ist hier ein relativer Bezug).

Ich würde schreiben:

Cells(3, 2)

oder

Range(„B1“).Offset(2, 0)

wenn ich B3 meine.

Und: die Eigenschaft „Value“ nicht vergessen!

Und: noch angeben, auf welchen Tabellenblatt die Zelle liegt.

Es geht aber auch anders. Wer denn möchte …

Früher durfte man erst anfangen zu essen, wenn alle am Tisch waren. Heute: wenn alle ein Foto davon gemacht haben.

Hallo Rene,

ich hoffe das du bei diesen sommerlichen Temperaturen einen guten und entspannten Tag hast.

Zurzeit beschäftige ich mich mit der Ribbon-Programmierung in MS-Office. In Word gibt es ein Button Schließen/Alles Schließen, mit der MsoID = „FileCloseOrCloseAll“. Klickt man diesen Button an, wird das aktuelle Dokument geschlossen. Wird beim Anklicken die Umschalttaste gedrückt, werden alle offenen Dokumente geschlossen.

Wie bekomme ich eine solle Funktionalität bei einem benutzerdefinierten Button hin. Ich möchte zwei unterschiedliche Makros aufrufen, je nachdem ob beim Anklicken die Feststelltaste gedrückt oder nicht gedrückt ist.

Hast du eine Idee?

Liebe Grüße und

Salü

Ernst

Hallo Ernst,

ich glaube nicht, dass das geht. Die Steuerellemente haben folgende Ereignisse:

EreignisBeispiel
onAction (button)Sub Prozedur(ByRef Control As IRibbonControl)
onAction (checkBox, toggleButton)Sub Prozedur(ByRef Control As IRibbonControl, ByRef Pressed As Boolean)
onAction (dropDown, galley)Sub Prozedur(ByRef Control As IRibbonControl, ByRef SelectedID As String, ByRef SelectedIndex As Integer)
onChange (editBox, comboBox)Sub Prozedur(ByRef Control As IRibbonControl, ByRef Text As String)

Das heißt: für die Buttons gibt es beim Aufruf einer Prozedur kein Parameter, der eine weitere Taste abfangen kann.

Auch bei den Methoden:

MethodeBeschreibung
ExecuteMsoFührt das vom idMso-Parameter angegebene Steuerelement aus.
GetEnabledMsoGibt True zurück, wenn das vom idMso-Parameter angegebene Steuerelement aktiviert ist.
GetImageMsoGibt ein IPictureDisp-Objekt des vom idMso-Parameter angegebenen Steuerelementbilds zurück, wobei die Abmessung von Height und Width angegeben wird.
GetLabelMsoGibt die Beschriftung des vom idMso-Parameter angegebenen Steuerelements als Wert vom Typ String zurück.
GetPressedMsoGibt einen Wert zurück, der angibt, ob das vom idMso-Parameter angegebene Umschaltflächen-Steuerelement gedrückt wird.
GetScreentipMsoGibt die QuickInfo des vom idMso-Parameter angegebenen Steuerelements als Wert vom Typ String zurück.
GetSupertipMsoGibt eine MultiInfo zum vom idMso-Parameter angegebenen Steuerelement als Wert vom Typ String zurück.
GetVisibleMsoGibt True zurück, wenn das vom idMso-Parameter angegebene Steuerelement sichtbar ist.

Kann man mit GetPressedMso nur bei Toggle-Buttons prüfen, ober ein- oder ausgeschaltet wurde, aber nicht wie gedrückt wurde.

Ich kann diese Frage mal nächste Woche auf meinem Blog veröffentlichen – vielleicht weiß jemand eine Antwort …

Liebe Grüße

Rene

Hallo Rene.

Nach deinen Ausführungen bin ich auf die Idee gekommen folgendes in WORD auszuprobieren.

Bei zwei geöffneten Dokumenten habe ich im Direktbereich des VBA-Editors den Befehl

CommandBars.ExecuteMso(„FileCloseOrCloseAll“)

einzugeben und danach nur die Enter-Taste gedrückt.

Es wird die Schließen-Routine aufgerufen.

Wird allerdings die Shift- und die Enter-Taste gleichzeitig gedrückt, wird die AllesSchließen-Routine aufgerufen.

Daraufhin habe ich folgendes versucht:

Ich habe in ein Word-Dokument mit Hilfe des Office RibbonX Editor diese Sequenz eingefügt.

und im VBA-Bereich des gleichen Dokumentes folgendes.

Option Explicit

'API zum feststellen des Keyboard-Status deklarieren.
#If VBA7 Then
    Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
 #Else
    Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#End If

Sub OnActionButton(control As IRibbonControl)
    Select Case control.ID
        Case "SaveAsDocxOrPdf":          SpeicherAlsDocxOderPdf  'Neuer Umbruch
        Case Else
        MsgBox "Fehler in OnAction: '" & control.ID & "' nicht erkannt"
    End Select
End Sub

Sub SpeicherAlsDocxOderPdf()
    If Abs(GetKeyState(&H10) < 0) Then      'Abfrage ob Shifttaste gedrückt ist
        CommandBars.ExecuteMso ("FileSaveAsPdfOrXps")
       Else
        CommandBars.ExecuteMso ("FileSaveAsWordDocx")
    End If
End Sub

Und es klappt. Wird das benutzerdefinierte Icon „Als DOCX oder PDF speichern“ angeklickt,  erscheint das Formular „Speichern als Dokument ohne Makros“. Wird während des Anklicken des Icon die Shift-Taste gedrückt, erscheint das Formular „Als PDF oder XPS veröffentlichen“.

Salü

Ernst

Das ist sehr, sehr clever! DARAUF wäre ich nie gekommen.

LG Rene

Warum warst du gestern nicht zu Hause, obwohl du wusstest, dass ich bei dir vorbeikommen wollte? – Nicht schlecht: Frage und Antwort in einem Satz.

Oh – man muss so aufpassen! Durchläuft man in VBA mit einem Zähler eine Sammlung an Objekten, greift sich Excel das erste, das zweite, das dritte, … Objekt. Aber welches ist das erste Objekt?

Bei Tabellenblättern ist das erste Objekt das linkeste. Dann das zweite von links. Dann das dritte.

Leider gibt es viele Sammlungen mit einer anderen „Reihenfolge“: die Reihenfolge, in der die Objekte erstellt wurden. Beispielsweise bei Diagrammen. Das erste ist nicht das linkeste, sondern das Diagramm, das zuerst erstellt wurde. Okay – das leuchtet ein.

Allerdings – wie sieht es bei Links aus. Wenn in einer Spalte mehrere Links stehen, ist der erste Link dann der oberste? Wird dann von oben nach unten weiter gezählt?

Nein! Auch hier gilt die Regel: die Reihenfolge des Erstellens entscheidet! Das heißt:

Das ist der erste Link:

Und nun wird der zweite und dann der dritte Link erstellt:

Und schließlich die übrigen 40.

Lässt man sie auslesen, erhält man:

Sub Links()
    Dim i As Integer
    Dim xlBlatt As Worksheet
    
    Set xlBlatt = ActiveSheet
    For i = 1 To xlBlatt.Hyperlinks.Count
        xlBlatt.Range("C" & (i + 3)).Value = xlBlatt.Hyperlinks(i).SubAddress
    Next i
End Sub

folgende Liste:

Zum Glück verfügt das Objekt Link über die Eigenschaften Range und damit über Range.Row und Range.Column. Natürlich auch über Range.Address. So kann man die Links lokalisieren.

Ich stand auf der Waage. Sie hat mir bestätigt, dass alle Cookies gespeichert wurden.

Oh, Mann – wie kann man nur zwei Wörterbücher gleich nennen?

Genauer: in Word gibt es ein Wörterbuch. In Excel auch. Das VBA-Objekt heißt in Word „Dictionary“. In Excel „CheckSpelling“.

Zugleich gibt es neben Array und Collections in VBA eine Sammlung „Dictionary“. Sie kann verwendet werden, wenn man die „Microsoft Scripting Runtime“-Bibliothek einbindet. Und dann stellt sich die Frage: „welches Dictionary wird denn nun verwendet?“

Richtig: es tritt ein Fehler auf, wenn beide verwendet werden. Genauer: der Fehler tritt dann auf, wenn man ZUERST einen Verweis auf die Word-Bibliothek setzt und anschließend auf die „Microsoft Scripting Runtime“-Bibliothek :

Die besser Lösung (statt dem Ändern der Reihenfolge): Die Bibliotheken voll qualifizieren. Dann klappt es:

Dim wdApp As Word.Application
Dim wdWordWoerterbuch As Word.Dictionary
Dim wdWordBenutzerWoerterbuecher As Word.Dictionaries

' -- Dictionary
Dim wdListeDict As Scripting.Dictionary

Set wdListeDict = New Scripting.Dictionary
wdListeDict.Add "007", "Bond"

' -- das Word-Wörterbuch
Set wdApp = New Word.Application
Set wdWordBenutzerWoerterbuecher = wdApp.CustomDictionaries
For Each wdWordWoerterbuch In wdWordBenutzerWoerterbuecher
    MsgBox wdWordWoerterbuch.Name
Next

Kennst du das? Montag Morgen voller Freude aus dem Haus stürmen? – Ich kenne das auch nicht!

Reguläre Ausdrücke – sie sind so klasse – warum mag Microsoft sie nicht?

Die Aufgabe: Extrahieren Sie aus zirka 30.000 Zellen einer Exceltabelle die darin befindlichen Datumsinformationen. Es finden sich Texte wie:

Maistraße 17 (St. 29.03.2016), Flurnummer-alt: 47/11

Bahnhofstraße 1 – 17.3.2022 – Flurnummer-alt: 08/15

Hirtenweg 3A 05-2005 – Garagen, Flurnummer-alt: 00/77

Ich überlege: Formeln wären eine Option. Aber sehr umständlich.

VBA wäre gut.

PowerQuery auch – kennt aber keine regulären Ausdrücke.

Ich entscheide mich für VBA.

Dort muss man einen Verweis auf „Microsoft VBScript Regular Expressions“ einbinden. Oder diese Klasse mit

Set regex = CreateObject("vbscript.regexp")

aufrufen. Dann kann man definieren:

    ' -- Muster: ***01.01.2022***
    strMuster1 = ".*\d{1,2}\.\d{1,2}\.\d{2,4}.*"
    strMuster1_Raus = "\d{1,2}\.\d{1,2}\.\d{2,4}"

Und kann nun extrahieren:

    regex.Pattern = strMuster1
    regexRaus.Pattern = strMuster1_Raus
    regexRaus.Global = True
    For i = 1 To ThisWorkbook.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
        If regex.Test(Range("I" & i).Value) = True Then
            Set strTreffer = regexRaus.Execute(Range("I" & i).Value)
            j = 0
            For Each strFund In strTreffer
                strTemp = strFund
                If IsDate(strTemp) Then
                    If Len(Split(strTemp, ".")(2)) = 3 Or Len(Split(strTemp, ".")(2)) = 1 Then
                        Range("Q" & i).Offset(0, j).Value = strTemp
                        Range("Q" & i).Offset(0, j).Interior.Color = vbRed
                    
                    Else
                        Range("Q" & i).Offset(0, j).Value = CDate(strTemp)
                        If Year(CDate(strTemp)) > Year(Date) Then
                            Range("Q" & i).Offset(0, j).Interior.Color = vbRed
                        End If
                    End If
                    
                Else
                    Range("Q" & i).Offset(0, j).Value = strTemp
                    Range("Q" & i).Offset(0, j).Interior.Color = vbRed
                End If
                j = j + 1
            Next
        End If
    Next

Vier andere Varianten werden analog abgearbeitet. Klappt.

Warum hat VBA nicht als Standard Regex eingebunden?

Warum kennt PowerQuery keine regulären Ausdrücke?

Warum kann man keine regulären Ausdrücke beim Autofilter oder Spezialfilter eingeben?

Nachtrag: Ich habe etwas gewühlt. Imke Feldmann beschreibt, wie man über JavaScript einen Zugriff auf RegEx erhält:

Trotzdem: ich ziehe hier VBA vor.

Und: vor einigen Jahren hatte ich eine XML-Schulung, in der ich die regulären Ausdrücke vorgestellt hatte. Die Teilnehmerinnen kannten sie, waren damit vertraut, arbeiteten in „anderen Welten“ damit und waren begeistert. Sie wollten sich sogar T-Shirts mit dem Aufdruck „I ♥ RegEx“ drucken lassen. Haben sie aber doch nicht.

Es hat keinen Sinn, über die Männer zu jammern. Wir müssen mit dem vorhandenen Material arbeiten.

Einfach nicht aufgepasst!

Ich habe eine Arbeitsmappe mit sieben + ein, also acht Tabellenblattern. Sie heißen:

Chef, Brummbär, Schlafmütz, Hatschi, Pimpel, Happy, Seppel und Schneewittchen

Die ersten drei Blätter sind ausgeblendet, ebenso das letzte (Master-)Blatt:

Also auf Excel-Seite sieht das so aus:

Nun soll per VBA das erste Blatt sichtbar gemacht werden, umbenannt werden und an eine bestimmte Position verschoben werden. Das Masterblatt „Schneewittchen“ wird anschließend wieder unsichtbar gemacht.

Schritt für Schritt:

1.) Zugriff auf das (unsichtbare) Blatt „Schneewittchen:

    Dim xlBlatt As Worksheet
    Dim i As Integer
    Dim strBlattname As String
    
    Set xlBlatt = ThisWorkbook.Worksheets("Schneewittchen")

2.) Blatt wird eingeblendet:

    xlBlatt.Visible = xlSheetVisible

3.) Blatt wird vor das erste Blatt geschoben:

    xlBlatt.Copy Before:=ThisWorkbook.Worksheets(1)

4.) Mit Hilfe einer benutzerdefinierten Funktion GibtEsBlattname wird überprüft, welcher Blattname noch nicht existiert, beispielsweise „Schneewittchen 001“:

    i = 0
    Do
        i = i + 1
        strBlattname = "Schneewittchen " & Format(i, "000")
    Loop Until GibtEsBlattname(strBlattname) = False

5.) Das erste Blatt wird umbenannt:

ThisWorkbook.Worksheets(1).Name = strBlattname

6.) Das Masterblatt „Schneewittchen“ wird ausgeblendet:

ThisWorkbook.Worksheets("Schneewittchen").Visible = xlSheetVeryHidden

7.) Das neu erzeugte Blatt wird nach hinten verschoben:

ThisWorkbook.Worksheets(strBlattname).Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

Und: ein Fehler ist die Folge:

Die Move-Methode des Worksheet-Objektes konnte nicht ausgeführt werden.

Der Fehler liegt in folgender Zeile:

xlBlatt.Copy Before:=ThisWorkbook.Worksheets(1)

Welches ist das ERSTE Tabellenblatt? Blatt Nummer 1 oder das erste sichtbare Tabellenblatt. Die Antwort: letzteres: das Blatt wird vor das erste sichtbare Blatt kopiert. Damit ist es anschließend nicht das erste Blatt! Sondern die drei ausgeblendeten Blätter liegen noch weiter links von dem Blatt.

Leider besitzt die Methode Copy kein Objekt, an das das Ergebnis des Kopierens übergeben werden kann.

Also muss man sich „auf die Suche“ nach dem Blatt machen, beispielsweise: durchlaufe alle Blätter und such das Blatt, das „Schneewittchen(2)“ heißt, besser

ThisWorkbook.Worksheets(1).Name Like "Schneewittchen *"

Dann klappt es auch!

Die Krankheit, die alles Essbare in Plastikdosen packt, nennt man Tupperkulose.

Ist das ein Bug? Was passiert denn da gerade?

Ich habe ein mächtiges Werkzeug mit Excel VBA erstellt, in welchem unter anderem Dateien geöffnet werden, Informationen ausgelesen und in die eigene Datei geschrieben werden. So nach dem Motto:

Dim xlDatei As Workbook
Set xlDatei = Application.Workbooks.Open("C:\BIA_contoso_IT.xlsm")
' -- tue etwas
xlDatei.Close SaveChanges:=False
Set xlDatei = Nothing

Ich teste mehrere Male.

Mit Erstaunen stelle ich im Projekt-Explorer fest, dass die Datei mehrmals geöffnet ist:

Ein Blick in Excel unter Ansicht / Fenster wechseln kann dies nicht bestätigen. Und schließlich: eine Datei mit einem bestimmten Namen kann in Excel nur ein Mal geöffnet sein.

Beim sechsten Test erhalte ich folgende wunderliche Meldung:

Ich beende das Programm Excel, öffne – der Spuk ist verschwunden. Ich starte das Programm – die verwunderliche Meldung kommt erneut. Die Ursache ist gefunden: Excel behauptet plötzlich, dass das Tabellenblatt Nummer 3 nicht vorhanden ist!?! In der Datei befinden sich zirka 34 Tabellenblätter, die ich alle „prüfe“.

Automatisierungsfehler: Ungültige Vorreferenz oder Referenz zu unkompiliertem Typ

Ich verstehe es gerade nicht. Habe ich – in meiner aktuellen Version 2201 ein neues Feature entdeckt?

Geschirrspülmaschinen sind super – bis man sie ausräumen muss!

Was habe ich nur angerichtet?

In einer Excelschulung wollte ich zeigen, dass zwei gesetzte Filter einem logischen UND entsprechen und somit eine Schnittmenge darstellen:

Ich visualisiere das mit zwei Ellipsen:

Und wollte die Schnittmenge deutlich hervorheben, indem ich beiden Formen kombinieren. Aber das Zusammenführen der Formen ist in Excel leider nicht möglich! Ich habe darüber geschrieben:

https://www.excel-nervt.de/eines-muss-ich-meiner-muedigkeit-ja-lassen-kondition-hat-sie-ja/

Damit habe ich eine kleine Diskussion ausgelöst.

Ernst hat sich nun die Frage gestellt, ob man so ein Problem nicht mit VBA lösen kann. Konkret: ob man die Formen nicht nach PowerPoint kopieren kann, dort zusammenführen kann und anschließend wieder zurück kopieren kann. Die Antwort lautet: „ja“. Allerdings: einige Dinge gibt es hierbei zu beachten:

Im ersten Schritt wird geprüft, ob mindestens zwei Formen markiert wurden.

Anschließend wird ein Auswahldialog aufgerufen:

Er prüft, ob PowerPoint bereits geöffnet ist:

On Error GoTo Fehler
Set PP_app = GetObject(Class:="PowerPoint.Application")
PP_app.Visible = True                                   'Stellt die Sichtbarkeit auf an.
PPObjektaufrufen = True                                 'und setzt die Funktionsrückmeldung auf True.
Exit Function                                           'Funktion wird verlassen.
Fehler:
PPObjektaufrufen = False 'Die Funktionsrückmeldung wird auf False gesetzt.

Falls nicht, wird es geöffnet:

Set PP_app = CreateObject(Class:="Powerpoint.Application")
PP_app.Visible = True                                       'Stellt die Sichtbarkeit auf an.

Wichtig hierbei ist, dass PowerPoint sichtbar ist!

Die Formen werden kopiert und eingefügt:

Selection.Copy                                                      'Die selektierten Formen werden in die Zwischenablage kopiert.
Application.ActiveWindow.Selection.ShapeRange(1).Name = OriginalName 'Danach wird der Originalname der erstselektierten Form wieder hergestellt
PP_app.Presentations.Add.Slides.Add Index:=1, Layout:=ppLayoutBlank 'Es wird eine neue Präsentation mit einer leeren Folie erstellt.
                                                                    'Die Slide.Add-Methode ist eine verborgene PowerPoint-Methode
PP_app.ActivePresentation.Slides(1).Shapes.Paste                    'Die Formen werden aus der Zwischenablage in die PP-Präsentation eingefügt.
PP_app.ActivePresentation.Slides(1).Shapes.SelectAll                'und selektiert.

Dabei war auch die Zeile

PP_app.Presentations.Add.Slides.Add Index:=1, Layout:=ppLayoutBlank

wichtig – die Methode Add der Sammlung Slides ist dies ein verborgenes Element: Diese werden von Intellisense nur dann angezeigt, wenn die Option „verborgene Elemente anzeigen“ bestätigt wurde. Ich habe einmal darüber geschrieben:

https://www.excel-nervt.de/was-war-das-fuer-ein-krach-heute-nacht-die-schuhe-sind-umgefallen-bitte-ich-stand-noch-drin/

Wichtig ist hierbei herauszufinden, welches die zentrale Form ist, also die Form, von welcher die anderen die Formatierung erben:

'PrimaryShape:=  Die Form, von der die resultierende Form ihre Formatierung erbt.
'Wenn Fehler auftritt, wird dieser abgefangen
On Error GoTo Fehler1
PP_app.ActiveWindow.Selection.ShapeRange.MergeShapes MergeCmd:=Formform, _
    PrimaryShape:=PP_app.ActiveWindow.Selection.ShapeRange("PrimaerForm")
'Fehlerroutine zurückstellen auf Fehler allgemein
On Error GoTo FehlerAll

PP_app.ActivePresentation.Slides(1).Shapes.SelectAll            'die neu erstandene Form wird selektiert.

'Wurde eine Form selektiert, wird diese nach Excel übertragen. Es könnte, beispielsweise bei der Aktion
'Schnittmenge bilden, der Fall auftreten, dass keine Form erzeugt wird.

Das Ergebnis wird zurück nach Excel kopiert:

'Wieviel Formen sind selektiert?
AnzahlFormen = 0
AnzahlFormen = PP_app.ActiveWindow.Selection.ShapeRange.Count   'Anzahl selektierte Formen
If AnzahlFormen > 0 Then                                        'Ist keine Form selektiert erfolgt keine Aktion.
    PP_app.ActiveWindow.Selection.Cut                           'Ansonsten werden die selektierten Formen ausgeschnitten und in die Zwischenablage kopiert.
    If Not MergeFehler Then ActiveSheet.Paste                   'und werden dann aus der Zwischenablage nach Excel kopiert.
End If

PowerPoint wird geschlossen:

If PPschonGestartet Then                'Wurde keine neue PP-Instanz gestartet.
    PP_app.ActivePresentation.Close     'wird nur die neu erzeugte PP-Präsentation geschlossen
   Else
    If NeuePPwiederSchliessen Then
       PP_app.Quit                      'Steht dieser Schalter auf True wird PP wieder geschlossen.
    End If
End If
Set PP_app = Nothing                ' Objektvariable wird auf Nothing gesetzt
Exit Sub

Ein beachtliches Werk mit vielen Fallstricken – vielen Dank an Ernst Börgener.

Wer möchte, kann sich das Beispiel herunterladen:

https://www.excel-nervt.de/wp-content/uploads/2022/01/Formenvereinigen.xlsm

Der Autor freut sich über Lob, Kritik und Anmerkungen!

Ich habe keine Schokoladenseite. Ich sehe von allen Seiten aus wie Vanillepudding.

Sicherlich hätte ich sehr, sehr lange gesucht. Und mich gewundert. Zum Glück hat mir Tanja Kuhn geholfen. Beziehungsweise mich schon vorher aufmerksam gemacht.

Die Aufgabe: in einem Wordformular soll dynamisch, das heißt per VBA, die Kopfzeile (und auch die Fußzeile) ausgetauscht werden:

Ich beginne mit dem Löschen der Kopfzeile.

ActiveDocument.Sections(i).Headers(wdHeaderFooterFirstPage).Range.Delete

Obwohl weder das Dokument noch einer der Abschnitte geschützt ist, erhalte ich eine Fehlermeldung:

Die Antwort: das Bild befindet sich in einem Inhaltssteuerelement und das wurde im Entwurfsmodus in den Eigenschaften geschützt. Folglich kann auch nicht die Kopfzeile gelöscht werden …

Böse! Ganz böse!

Der Balkon ist immer noch voller Wespen, obwohl der Experte, der sich das Nest neulich ansah, meinte, im Oktober sind sie dann alle weg. Ich stelle ihnen mal einen Kalender raus.

Ich benötige den Code einer Farbe einer Zelle von Excel. Also schreibe ich:

MsgBox "Color:" & ActiveCell.Interior.Color & vbCr & "ColorIndex: " & ActiveCell.Interior.ColorIndex

Das Ergebnis:

Ich teste an einer anderen Zelle:

Und schließlich:

Drei Mal die gleichen Werte ???

Es dämmert mir …

Hinter den Zellen liegt eine bedingte Formatierung. Color und ColorIndex liefern die voreingestellten Zellfarben und nicht die durch die Datenüberprüfung angezeigten … Es ist übrigens recht mühsam, das Ergebnis der bedingten Formatierung zu ermitteln.

Ich bin wie Batman. Ich muss oft nachts raus.

Hi Rene,

hoffe, Du hast einen schönen Urlaub ohne großen Regen, aber vielleicht Zeit für eine kleine Knobel-Aufgabe in VBA

Ich habe eine Tabelle, in der alles getan werden darf, d.h. auch gefiltert, aber nur nicht sortiert.
Schutz geht leider nicht, da sich dann leider die Tabelle nicht dynamisch erweitert.

In diesem Artikel steht, dass man mit Hilfe des Events „BeforeSort“ die Sortierroutinen abfangen kann.

https://docs.microsoft.com/en-us/office/vba/api/excel.sortfield

Aber leider kriege ich das nicht hin und im Internet habe ich auch nichts gefunden. Weißt Du, wie man dieses Event in Excel platzieren kann? Würde mich freuen.

Hallo Johannes,

ich habe mal ein bisschen gewühlt und probiert:

1. Das SortObjekt existiert – allerdings besitzt es keine Ereignisse (wie MS behauptet): Das sieht man, wenn man versucht in einem Klassenmodul einzutragen:

Public WithEvents SO As so…

2. Ich habe überlegt, ob man die Symbole wegnehmen kann. Das Problem: Man kann über die Registerkarte Start und Daten den Sortierbefehl aufrufen; über das Kontextmenü oder über die Pfeilchen, die der Filter, die intelligente Tabelle, die Pivottabelle filtern. Das heißt: es wird sehr mühsam, dem Anwender die Symbole wegzunehmen.

3. Ich würde alle Zellen auf „nicht gesperrt“ setzen, das Blatt schützen – außer der Sortieroption. Dann kann der Anwender (fast) alles – was er nicht kann, ist beispielsweise einen AutoFilter einschalten.

Tja!

Hilft das?

Liebe Grüße

Rene

Anders als ihre Artgenossen die Seidenspinner werden die Zahnseidenspinner in kleinen Plastikgefäßen gehalten, wo sie zirka 50 Meter Zahnseide produzieren und dann sterben.

Eine Weile habe ich gebraucht, bis ich es verstanden habe. Excel behauptet, dass auf diesem Tabellenblatt eine Verknüpfung zu einer anderen Datei liege.

„Da liegt nix“, denke ich: auf dem Blatt befindet sich nur ein Button, der ein Makro aufruft:

Nach einer Weile dämmert es mir. Ich kopiere regelmäßig den Bereich von anderen Dateien hier auf dieses Blatt. Und richtig: damit auch die Schaltflächen. Und mit ihnen die Verknüpfungen auf andere Dateien. *gggrrrr*

Wenn ein Fahrrad gestohlen wird, hat keiner etwas gesehen. Aber wehe, jemand stellt die gelben Säcke zwei Tage zu früh auf die Straße – dann ist hier Party!

Ich erstelle für einen Kunden in Excel mit VBA ein mächtiges Eingabeformular. Schnell sind wir uns einig darüber, der der Anwender und die Anwenderin nicht mit dem Befehl Suchen-Ersetzen Texte auf einem Tabellenblatt austauschen sollen. Also nehmen wir in diesem Formular dieses Symbol (genau: die ganze Gruppe) aus dem Menüband:

Dazu sind ein paar Zeilen in der XML-Datei nötig, in der das Menüband beschrieben wird:

Der Code:

<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon startFromScratch="false">
    <tabs>

      <tab idMso="TabHome">
        <group idMso="GroupEditingExcel" visible="false" >
        </group>
      </tab>

Und wie wird ersetzt? Natürlich über ein eigenes Werkzeug:

Ich überlege: dem Anwender und der Anwenderin bleiben immer noch die Möglichkeit mit den Tastenkombinationen [Strg] + [F], beziehungsweise [Strg] + [H] den Suche-Dialog zu öffnen. Also raus damit:

Private Sub Workbook_Open()
    On Error Resume Next
    Application.OnKey "^f", "BitteNicht"  ' suchen
    Application.OnKey "^h", "BitteNicht"  ' ersetzen
End Sub

Beim Öffnen der Datei werden diese beide Tastenkombinationen „verbogen“, indem das Makro „BitteNicht“ aufgerufen wird. Es erscheint ein Meldungsfenster. Diese Prozedur wird auch beim Aktivieren der Mappe gestartet:

Private Sub Workbook_Activate()

Schnell merke ich, dass das Makro nach Schließen der Datei (oder auch Wechseln in eine andere Datei) noch aktiv ist. Also: raus damit:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.OnKey "^f", ""  ' suchen
    Application.OnKey "^h", ""  ' ersetzen
End Sub

Ebenso beim Ereignis Workbook_Deactivate.

Und was passiert? Richtig – wenn ich jetzt [Strg] + [F] drücke, passiert: NICHTS. Warum? Genau – ich muss natürlich schreiben:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.OnKey "^f"  ' suchen
    Application.OnKey "^h"  ' ersetzen
End Sub

DANN ist das Ereignis wiederhergestellt!

Wir haben gestern einen Porno geschaut. Da hat der Mann die Frau mit Honig beträufelt und abgeleckt. Wir wollten das heute nachmachen, hatten aber keinen Honig. Um es kurz zu machen … Mit grober Leberwurst ist es nicht das Gleiche. Und wenn dann noch der Hund im Schlafzimmer liegt …

Im VBA-Editor kann man den (internen) Namen eines Tabellenblattes ändern. So kann man auf dieses Blatt über diesen (Code-)Namen zugreifen, egal an welche Position es der Anwender oder die Anwenderin schiebt; egal ob es in Excel umbenannt wird. Allerdings kann es der Anwender oder die Anwenderin löschen. Kein Problem – ich baue einen Button ein, der ein neues Blatt erzeugt:

Dim xlBlattNeu As Worksheet
Set xlBlattNeu = ThisWorkbook.Worksheets.Add
xlBlattNeu.CodeName = "tblITAnwendungen"

*gggrrrrr* CodeName ist schreibgeschützt.

Zuweisung an schreibgeschützte Eigenschaft nicht möglich.

Kann also nicht in VBA umbenannt werden. Ich kann nur überprüfen, ob das Blatt vorhanden ist. Aber nicht (den Namen) erzeugen. Schade! Ärgerlich! Aber verständlich.

Ich hasse Kettenbriefe. Wenn du sie auch so wie ich hasst, leite diese Nachricht an zehn Freunde weiter.

Wenn man per VBA auf ein Blatt zugreifen möchte, gibt es zwei Varianten. Entweder über den Namen:

Also beispielsweise

ThisWorkbook.Worksheets("Unternehmen")

oder

ActiveWorkbook.Worksheets("Standorte")

Problem: Wird die Arbeitsmappe nicht geschützt, kann der Anwender oder die Anwenderin den Namen ändern!

Die zweite Möglichkeit lautet:

ThisWorkbook.Worksheets(1)
ActiveWorkbook.Worksheets(2)

Problem: der Anwender oder die Anwenderin kann das Blatt verschieben, wenn die Arbeitsmappe nicht geschützt ist. Außerdem ist die Zählung bei ausgeblendeten Blättern schwierig.

Es gibt noch eine dritte Variante. Im VBA-Editor kann man in den Eigenschaften einen Namen festlegen:

Über diesen Namen kann man auf das Blatt zugreifen, beispielsweise:

tbl_Uebungsplanung.Range("F1").Value = _
   tbl_UnternehmenCode.Range("A1").Value ' -- Unternehmen

Erstaunlicherweise versagt:

MsgBox ThisWorkbook.tbl_UnternehmenCode.Range("A1").Value

Auch der Zugriff auf ein Blatt über seinen Codename in einer anderen Datei geht nicht!

    Dim xlDatei As Workbook
    Set xlDatei = Application.Workbooks.Open("D:\Übungspanung.xlsx")
    MsgBox xlDatei.tbl_UnternehmenCode.Range("A1").Value

Schade! Man muss mit einer Schleife über alle Blätter iterieren und abfragen, ob der Codename = „tbl_UnternehmenCode“. Und darauf einen Verweis setzen.

Ich habe mich eben mit meinem Handy auf die Waage gestellt und war total entsetzt. Wusstet ihr, dass ein Smartphone-Speicher so um die fünf Kilo wiegt?

Ich erstelle ein VBA-Projekt in Excel, das auf dem Mac und auf dem PC laufen soll. Da die Trennzeichen zwischen den Ordnern unterschiedlich sind („\“ auf PC, „/“ auf Mac), überprüfe ich, auf welchem System das Programm gerade läuft. Die Funktion

=INFO(„SYSTEM“)

liefert entweder „pcdoc“ oder „mac“. Prima!

Dann kann ich das doch in VBA verwenden. Ich werde eines Besseren belehrt:

Die Funktion INFO (oder Info) findet sich nicht in der Liste der Worksheetfunctions! Objekt unterstützt diese Eigenschaft oder Methode nicht. Lautet die Fehlermeldung. Abhilfe schafft der Befehl die Funktion in eine Zelle zu schreiben, den Wert auszulesen und die Formel wieder zu löschen. Beispielsweise so:

Dim xlBlatt As Worksheet
Dim strSystem As String

Set xlBlatt = ActiveSheet
xlBlatt.Range("A1").FormulaR1C1 = "=INFO(""SYSTEM"")"
strSystem = xlBlatt.Range("A1").Value
MsgBox strSystem
xlBlatt.Range("A1").ClearContents

Machst du eigentlich Sport, Susanne? – Ja, natürlich – ich habe ganz oft Laufmaschen in den Strumpfhosen.

Ich erstelle per VBA in Excel dynamische Formulare, in die Daten eingetragen werden. Dabei greife ich auf Mastertabellen zu, die ich einblende, kopiere und modifiziere. Eine der Tabellen hat ein Kommentar (okay – eigentlich eine Notiz). Unter bestimmten Voraussetzungen muss ich ihn löschen.

Okay – ich hätte ihn auch umgekehrt einfügen können – ich entschließe mich für die Variante des Löschens:

ZelleLinksOben.Offset(i, 0).Comment.Delete

Allerdings erhalte ich einen Fehler, wenn in der Zelle kein Kommentar mehr vorhanden ist:

Wie kann ich überprüfen, ob eine Zelle einen Kommentar hat? Die Zelle hat ja keine Comments-Sammlung, so dass ich über die Eigenschaft Count herausbekäme, ob Kommentar oder nicht.

Ich schreibe eine Funktion, die auf einen Kommentar zugreift. Ist dieser nicht vorhanden, erhalte ich einen Fehler. Diesen kann ich abfangen:

Public Function GibtEsKommentar(Zelle As Range) As Boolean
Dim blnVorhanden As Boolean
Dim s As String

On Error Resume Next
Err.Clear
s = Zelle.Comment.Text
If Err.Number = 0 Then
blnVorhanden = True
Else
blnVorhanden = False
End If
Err.Clear
GibtEsKommentar = blnVorhanden
End Function

Damit kann man (ich) arbeiten:

    If RisikoanalyseTyp <> "Dienstleistung" Then
        If GibtEsKommentar(ZelleLinksOben) Then
            .Comment.Delete ' -- lösche den Kommentar
        End If
    End If

Nach einer Weile entdecke ich, dass es zum Löschen eines Kommentars einen anderen Befehl gibt: ClearComments

ZelleLinksOben.Offset(i, 0).ClearComments

Und er löscht einen vorhandenen Kommentar oder macht nichts, wenn sich in der Zelle kein Kommentar befindet. Warum nicht gleich so?

Was war das für ein Krach heute Nacht? – Die Schuhe sind umgefallen! – Bitte? – Ich stand noch drin.

Manchmal bin ich erstaunt. Und verblüfft. Was ich alles noch nicht weiß.

Ich habe genörgelt, dass VBA nicht alle Objekte, Methoden und Eigenschaften anzeigt:

Peter hat mich darauf hingewiesen, dass einige Objekte im Objektkatalog ausgeblendet sind. Über das Kontextmenü kann man sie einblenden:

Dann sind sie nicht nur im Objektkatalog sichtbar

sondern stehen auch in VBA sichtbar zur Verfügung:

Dennoch bleibt die Frage: Warum macht Microsoft denn so etwas?

Seit wann haben wir eine Alarmanlage? – Du stehst auf der Katze!

„Eine üble Sache“, meint Peter.

Der „neue“ Kommentar wird auch auf gesperrten Arbeitsblättern zugelassen 🙁 Und verhindert das setzen eines alten Kommentares.

D.h. VBA Code welcher „alte“ Kommentare (Comments) setzt und dabei auf einen CommentThreaded in der Zelle trifft, hat große Probleme.

Danke an Peter für diesen Hinweis zu den Kommentaren und Notizen.

Kann mann mit Männergrippe schon Pflegestufe 3 beantragen?

Erstaunlich. Ich programmiere eine Access-Datenbank, die Daten nach Excel exportiert und aufbereitet.

Die Excel-Datei liegt im XLS-Format vor und wird am Ende als XLSX gespeichert. Ich muss die Liste nach vier Kriterien sortieren. Deshlab verwende ich den „neuen“ Sortierbefehl, der seit Excel 2007 vorhanden ist:

i = xlBlatt.Range("I1").CurrentRegion.Rows.Count
xlBlatt.Sort.SortFields.Clear
xlBlatt.Sort.SortFields.Add2 Key:=xlBlatt.Range("I2:I" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
xlBlatt.Sort.SortFields.Add2 Key:=xlBlatt.Range("J2:J" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
xlBlatt.Sort.SortFields.Add2 Key:=xlBlatt.Range("M2:M" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
xlBlatt.Sort.SortFields.Add2 Key:=xlBlatt.Range("K2:K" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With xlBlatt.Sort
        .SetRange xlBlatt.Range("I1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With
    ' -- sortiere, damit man zählen kann

Klappt. Klappt bei meinem Excel in Microsoft 365. Allerdings beim Kunden, der Office 2016 einsetzt – leider nicht:

Schade! Also verwende ich den „alten“ Sortierbefehl. Da er jedoch nur drei Sortierkriterien zulässt muss ich zwei Mal sortieren:

xlBlatt.Range("A1").CurrentRegion.Sort Key1:=xlBlatt.Range("J1"), Order1:=xlAscending, Key2:=xlBlatt.Range("M1"), Order2:=xlAscending, Key3:=xlBlatt.Range("K1"), Order3:=xlAscending, Header:=xlYes

xlBlatt.Range("A1").CurrentRegion.Sort Key1:=xlBlatt.Range("I1"), Order1:=xlAscending, Header:=xlYes

Geht doch!

Weiß jemand von euch, auf welcher Seite in der Bibel man das Rezept findet für das Umwandeln von Wasser in Wein?

Ich erstelle gerade benutzerdefinierte Funktionen (UDF) für Excel mit Visual Studio. Dazu habe ich das Projekt Excel-DNA eingebunden. Ich erstelle eine Klasse und wundere mich, warum ich nicht das aktuelle Framework habe. Excel-DNA verlangt ein Framework ab 4.3:

Es lässt sich auch nicht nachinstallieren.

Ich habe lange gesucht, bis ich es gefunden habe. Ich darf nicht die .NET Standard Klassenbibliothek verwenden:

Dort sind die aktuellen Frameworks nicht vorhanden:

Sondern ich muss auf die korrekte Windows Desktop Klassenbibliothek zurückgreifen:

Dann klappt es:

Oder auch mit dem älteren Visual Studio. Diese nicht:

Sondern diese:

Verwirrend! Und dann funktionieren die UDFs:

Die häufigsten Todesursachen bei Männern: Ikea Besuch | Erkältung | „Sie sind dran mit zurückschreiben.“

Hallo Herr Martin,

ich habe mal wieder ein Excel-Problem, diesmal in Richtung VBA.

Ich möchte gerne in eine dynamische Tabelle eine bedingte Formatierung einfügen, die eine Rahmenlinie-unten in jeder letzten Zeile eines Monats ausführt, wenn der Wert zutrifft.

soweit auch kein Problem, das würde ja auch mit einer normalen bedingten Formatierung funktionieren. Allerdings ist die Tabelle schon mit einem Rahmengitter belegt, um die Zellen besser zu unterscheiden.

Deswegen hätte ich gerne die Rahmenlinie-unten in „fett“, damit man die einzelnen Monate besser hervorhebt.

Da man das „fett“ in Excel bei bedingten Formatierungen nicht auswählen kann, habe ich durch Internetrecherche herausgefunden, dass man per VBA-Programmierung dieses Problem lösen kann.

Meine VBA-Kenntnisse sind allerdings gleich null, was mich nicht weiter bringt J

Anbei eine Beispieltabelle in der nach Belieben rumhantiert werden kann.

Hallo Herr F.,

Stimmt – das ist mir noch gar nciht aufgefallen: in der Bedingten Formatierung fehlt die Rahmenart. Doof!

Den Code hätten Sie doch sicherlich selbst hinbekommen.

Das Makro hängt an der Datei.

Sie können es in der Datei lassen oder in Ihre persönliche Makrodatei kopieren.

Sie können es über Entwicklertools / Makros (alternativ: Ansicht / Makros) starten – das Makro heißt „LinienEinfügen“

Oder Sie fügen ein Symbol in die Symbolleiste für den Schnellzugriff ein und starten es darüber.

Nicht ganz elegant, aber leicht zu ändern – in den ersten drei Zeilen lege ich die Spalte fest:

Sub LinienEinfügen()
    Const BEGINN As String = "A"
    Const ENDE As String = "I"
    Const SUCHSPALTE As String = "B"
    Dim i As Long
    Dim lngLetzteZeile As Long
    
    
    lngLetzteZeile = _
       ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row
    ' -- ermittle die Nummer der letzten Zeile
    
    For i = 1 To lngLetzteZeile
        If ActiveSheet.Range(SUCHSPALTE & i).Value <> "" And _
            ActiveSheet.Range(SUCHSPALTE & (i + 1)).Value <> "" Then
            If IsDate(ActiveSheet.Range(SUCHSPALTE & i).Value) And _
                IsDate(ActiveSheet.Range(SUCHSPALTE & (i + 1)).Value) Then
                If Month(ActiveSheet.Range(SUCHSPALTE & i).Value) <> _
                  Month(ActiveSheet.Range(SUCHSPALTE & (i + 1)).Value) Then
                    With ActiveSheet.Range(BEGINN & i & ":" & _
                        ENDE & i).Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .ColorIndex = xlAutomatic
                        .TintAndShade = 0
                        .Weight = xlThick
                    End With
                End If
            End If
        End If
    Next i


End Sub

Guten Morgen Herr Martin,

vielen herzlichen Dank für die Codes.

Damit komme ich zurecht.

Allerdings kann ich leider noch nicht sagen, dass ich das hinbekommen hätte.

Trotzdem vielen Dank nochmal und schöne heiße Tage!

Was geht ab? – Schwarzer Edding schon mal nicht!

Ich glaube, da muss Microsoft nochmal ran!

Ich zeichne ein Makro auf: ein Bild wird eingefügt:

ActiveSheet.Pictures.Insert("F:\Eigene Bilder\Erdbeertörtchen.JPG").Select

Ich möchte den Code etwas verändern:

Und stutze, weil das Objekt „Picture“ nicht in der Liste der Objektvariablen angeboten wird. Ich versuche es trotzdem:

Das Makro

Sub BildEinfuegen()
     Dim xlBlatt As Worksheet
     Dim xlBild As Picture
     
     Set xlBlatt = ActiveSheet
     Set xlBild = xlBlatt.Pictures.Insert("F:\Eigene Bilder\Erdbeertörtchen.JPG")

End Sub

läuft hervorragend. Okay – und was heißt: Bilder verschieben und verkleinern? Der Makrorekorder liefert:

ActiveSheet.Shapes.Range(Array("Picture 8")).Select
Selection.ShapeRange.ScaleWidth 0.3169191919, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.3169191919, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.IncrementLeft 252
Selection.ShapeRange.IncrementTop 54.75

Merkwürdig – ich hänge den veränderten Code an mein Makro:

Dim xlBlatt As Worksheet
Dim xlBild As Picture

Set xlBlatt = ActiveSheet
Set xlBild = xlBlatt.Pictures.Insert("F:\Eigene Bilder\Erdbeertörtchen.JPG")

With xlBild
    .Width = .Width * 0.3169191919
    .Height = .Height * 0.3169191919
    .Left = 252
    .Top = 54.75
    .Visible = True
End With

HIER wird das Picture-Objekt erkannt. Und: läuft doch!

Du willst wissen, wie ich so im Bett bin? – Ich schlafe auf dem Bauch, sabbere, murmle im Schlaf vor mich hin und sehe niedlich aus!

Merkwürdig. Ich erstelle in Outlook ein Makro, das eine Mail mit Anhang versendet:

Private Sub MailVersenden()
     Dim olApp As Application
     Dim olMail As MailItem

     Set olApp = Application
     Set olMail = olApp.CreateItem(olMailItem) ' olMailItem = 0

With olMail
    .To = "rene.martin@compurem.de"
    .Subject = "Beratungscheckliste Privatkunden"
    .Attachments.Add Source:="D:\Eigene Dateien\Excel\Kundeninformationen.xlsx"
    .Body = "Diese Mail wurde automatisch erstellt."

    .Send

End With

MsgBox "Das Dokument wurde erfolgreich per Mail gesendet.", vbInformation

End Sub

Klappt! Ich versuche es in Excel. Kopiere den Code, ändere ihn ein wenig, weil ich dort mit late binding arbeite:

Private Sub MailVersenden()
     Dim olApp As Application
     Dim olMail As MailItem
     Const olMailItem As Integer = 0

     Set olApp = Application
     Set olMail = olApp.CreateItem(olMailItem) ' olMailItem = 0

With olMail
    .To = "rene.martin@compurem.de"
    .Subject = "Beratungscheckliste Privatkunden"
    .Attachments.Add Source:="D:\Eigene Dateien\Excel\Kundeninformationen.xlsx"
    .Body = "Diese Mail wurde automatisch erstellt."

    .Send

End With

MsgBox "Das Dokument wurde erfolgreich per Mail gesendet.", vbInformation

End Sub

Das Ergebnis:

Erst wenn ich den Namen des Parameters „Source“ lösche, funktioniert es:

Ich vermute, dass „Object“ nicht korrekt in MailItem konvertiert werden kann.

Merkwürdig!

Urlaub 2020. Morgens 7:00 Uhr. Handtuch auf die Couch legen. Nicht, dass mein Platz später belegt ist.

Gegeben sei eine Tabelle die per Programmierung erzeugt wird. Sie hat Zwischenüberschriften.

Der Ausdruck ist etwas unglücklich, da die Zwischenüberschriften irgendwo auf der Seite stehen. Sie sollen immer am oberen Papierrand stehen.

Kein Problem: die Befehle für Seitenumbruch einfügen (die Zeilen werden natürlich „gesucht“ – die Zeilennummer berechnet), ist schnell gefunden:

ThisWorkbook.Worksheets(1).HPageBreaks.Add Before:=ThisWorkbook.Worksheets(1).Cells(5, 1)
ThisWorkbook.Worksheets(1).HPageBreaks.Add Before:=ThisWorkbook.Worksheets(1).Cells(30, 1)
ThisWorkbook.Worksheets(1).HPageBreaks.Add Before:=ThisWorkbook.Worksheets(1).Cells(51, 1)

Klappt. Die ersten vier Zeilen werden wiederholt.

Allerdings sollte die Tabelle auf eine Seite angepasst werden:

With ThisWorkbook.Worksheets(1).PageSetup
         .Zoom = False
         .FitToPagesWide = 1
 End With

Und was jetzt passiert entzieht sich meiner Kenntnis:

Ich schaffe es nicht die Seitenbreite = 1 einzustellen UND Seitenumbrüche einzufügen. Alle Versuche die Reihenfolge der Zeilen zu ändern, Application.PrintCommunication ein- oder auszuschalten, die Eigenschaft PageBreak auf xlPageBreakManual festzulegen … Alles scheitert. Also habe ich nachgesehen, wie „breit“ eine Seite ist. Hier: 60%. Und mit dem Befehl

ThisWorkbook.Worksheets(1).PageSetup.Zoom = 60

klappt es auch, wie man sehen kann:

Hier der ganze Code:

Sub SeiteEinrichten()
ThisWorkbook.Worksheets(1).ResetAllPageBreaks
ThisWorkbook.Worksheets(1).PageSetup.PrintArea = ""

Application.PrintCommunication = False
With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$4"
    .PrintTitleColumns = ""
End With

With ThisWorkbook.Worksheets(1).PageSetup
    .Zoom = False
    .FitToPagesWide = 1
End With

ThisWorkbook.Worksheets(1).HPageBreaks.Add Before:=ThisWorkbook.Worksheets(1).Cells(5, 1)
ThisWorkbook.Worksheets(1).HPageBreaks.Add Before:=ThisWorkbook.Worksheets(1).Cells(30, 1)
ThisWorkbook.Worksheets(1).HPageBreaks.Add Before:=ThisWorkbook.Worksheets(1).Cells(51, 1)


ThisWorkbook.Worksheets(1).PageSetup.Zoom = 60
ThisWorkbook.Worksheets(1).PageSetup.CenterHorizontally = False
Application.PrintCommunication = True

ThisWorkbook.Worksheets(1).PageSetup.LeftFooter = "Lebensmittel"
' -- Firmenname in der Fußzeile
End Sub

Schatz – wann gibt es Essen? – In einer Stunde; wenn du hilfst auch früher! – Eine Stunde ist okay.

Gemein! Der VBA-Befehl

Application.CommandBars(„ply“).Enabled = False

deaktiviert das Kontextmenü der Registerkarte. DAS funktioniert dann nicht mehr:

Da der Befehl zu Application, also zu Excel gehört, bleibt er auch für andere Dateien, die geöffnet werden, aktiv. So lange, bis man Excel schließt und wieder erneut öffnet. DANN steht das Kontextmenü wieder zur Verfügung.

Danke an Mourad Louha für diesen Hinweis.

Nach meinem Tod werde ich wahrscheinlich sehr beschäftigt sein. Die Liste der Menschen, die ich dann heimsuchen werde, wird immer länger.

Vor Kurzem hatte den Fehler schon einmal.

Die Methode ‚Visible‘ für das Objekt ‚_Worksheet‘ ist fehlgeschlagen.

Ich wollte per VBA ein Blatt ausblenden. Damals war die Ursache, dass dieses auszublendende Blatt das einzige war – DAS geht nicht. Eine Exceldatei benötigt immer mindestens ein sichtbares Tabellenblatt.

Nun erhalte ich die Fehlermeldung erneut.

Die Ursache diesmal: Die Arbeitsmappe ist geschützt (Überprüfen / Arbeitsmappe schützen).

Früher ging ich mit einer DM zum Kiosk und kam mit zwei Heften, drei Kaugummis, einer Tüte Chips und einem Eis zurück. Und heute? Überall Überwachungskameras!

Einfach nicht aufgepasst!

Mein VBA-Programm liefert eine „merkwürdige“ Fehlermeldung. Warum kann ein Tabellenblatt nicht ausgeblendet werden?

Die Ursache ist schnell gefunden – es gibt nur ein Tabellenblatt – und dieses ein darf ich natürlich nicht ausblenden … Also schnell überprüft, wie viele Blätter sichtbar (xlSheetVisible) sind.

Mama meinte heute zu mir: „Du bist hier nicht im Hotel!“ Habe erstmal eine schlechte Bewertung auf google geschrieben: „Freches Personal.“

Ich predige es in jeder VBA-Schulung: „sauber programmieren“. Und jetzt bin ich selbst darüber gestolpert: Ein Fehlermeldung an der Stelle

If xlBlattSuch.Range(„B1“) = „“ Then

Klar: das Objekt Range(„B1“) wird verwechselt mit der Eigenschaft Range(„B1“).Value.

Einfach vergessen: bei mir läuft es – beim Kunden gibt es eine Fehlermeldung. *ggrrrrr*

Bei mir ist wieder FKK-Zeit: Früh dunkel, Kalte Hände, Kalte Füße

Auch dieses Problem hat mich eine Stunde Zeit gekostet.

Ich erstelle ein umfangreiches Programm für einen Kunden. Die Registerkarten sind ausgeblendet:

Ich starte das Programm mit dem Ausschalten der Bildschirmaktualisierung:

Application.ScreenUpdating = False

Ich blende die Registerkarten per VBA wieder ein:

ActiveWindow.DisplayWorkbookTabs = True

Was passiert? Nichts!

Man muss vor dem Anzeigen die Bildschirmaktualisierung wieder einschalten! ( Application.ScreenUpdating = True). Dann erst werden die Tabs wieder angezeigt:

Übrigens: Es ist erstaunlich, dass die Eigenschaft „DisplayWorkbookTabs“ eine Eigenschaft von ActiveWindow und nicht von ActiveWorkbook ist!

Ich habe mir eine zweite Schneeschaufel gekauft. Ich paarschippe jetzt.

Habt ihr schon einmal Ribbon selbst mit einer XML-Datei erstellt. Ein validierender XML-Editor ist wichtig (wer Visual Studio hat, ist gut beraten).

Man fragt sich, welcher Praktikant den Befehl checkBox und nicht Checkbox (auch nicht CheckBox) genannt hat. Buttons habe das Attribut size mit den Werten „large“ und „normal“. Hum!

Keinen Humor haben, aber sich einen Account bei facebook zulegen. Man geht doch auch nicht zu Ikea, wenn man keine Teelichter braucht.

Boah ist das widerlich!!! *)

Ich erstelle ein Exceltabellenblatt, bei dem der Anwender zwischen zwei Texten wechseln kann. Je nach Text wird eine andere Liste für die Datenüberprüfung verwendet. Da die Liste dynamisch ist und aus einem anderen System kommt, muss ich ein paar Zeilen VBA-Code verwenden:

If Target.Value = „Für Standorte“ Or Target.Value = „Für Gebäude“ Then […]

Nach einer Weile meldet sich der Anwender und sagt, dass er auf diesem Tabellenblatt eine Fehlermeldung erhält. Ich schaue nach:

Das Gemeine: Der Anwender hat einen Doppelklick auf eine verbundene Zelle ausgeführt. Dadurch greift das Target-Objekt nicht – es nicht nun nicht mehr EINE Zelle, die einen Inhalt hat, sondern ein Zellbereich. Ich erhalte einen Fehler!

Also noch schnell eine Zeile Code außenrum – in der ersten Spalte wurde nichts verbunden.
If Target.Column = 1 Then
Und schon klappt es!

Haben Pferde Vorurteile? Denkt zum Beispiel ein Galopppferd von einem Dressurpferd ‚diese Tunte‘?

Hallo Herr Martin,

dieses Mal ist mir ein etwas seltsames Verhalten von VBA aufgefallen, wahrscheinlich kennen Sie das, mir ist es eben zum ersten Mal begegnet.

Ich habe in diesem Beispiel eine sehr simple Schleife mit der Vlookup-Funktion.

In „Sheets(„Tabelle2“).Range(„A:B“)“ stehen die Daten, die ich in „Sheets(„Tabelle1“).Cells(i, 2) hineinspielen möchte.

Ich weiß, ist nicht elegant, aber mir geht es um die Funktion an sich.

Sub Test()

Dim i As Integer

For i = 2 To 11

If Not IsError(Application.WorksheetFunction.VLookup(Cells(i, 1).Value, Sheets(„Tabelle2“).Range(„A:B“), 2, False)) Then

Sheets(„Tabelle1“).Cells(i, 2).Value = Application.WorksheetFunction.VLookup(Cells(i, 1).Value, Sheets(„Tabelle2“).Range(„A:B“), 2, False)

Else:

Sheets(„Tabelle1“).Cells(i, 2).Value = „Fehler“

End If

Next i

End Sub

Verwende ich für Vlookup die Schreibweise Application.WorksheetFunction.VLookup, dann bleibt die Schleife beim ersten Wert hängen, den er nicht findet und gibt den Laufzeitfehler 1004 aus (Die Vlookup-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden). Der Versuch, mit „If not isError“ den Fehler abzufangen, schlägt fehl.

Verwende ich jedoch die Schreibweise Application.VLookup, dann funktioniert alles perfekt und in „Sheets(„Tabelle1“).Cells(i, 2).Value“ wird „Fehler“ hineingeschrieben.

Ein identisches Verhalten zeigen auch andere Funktionen, wie Application.WorksheetFunction.Match.

Verstehen Sie das?

Danke Ihnen und viele Grüße,

Hallo Herr D.

Der Code sieht korrekt aus. Ich kann dazu nur Folgendes sagen:

Letzte Woche habe ich ein VBA-Add-In für einen Kunden erweitert – ich wollte Daten per Formeln aufbereiten, um darauf ein Diagramm aufzusetzen.

Die Formel sah so aus:

xlBlattDiagramm.Range(„B“ & intZeilenDiagramm + 3).Offset(intZeilenDiagramm – 2)).FormulaR1C1 = _

        „=OFFSET(R1C1,0,“ & (intBereichsSpalten + 1) & „-COUNTIF(R[-“ & (intZeilenDiagramm + 1) & „]C:R[-“ & (intZeilenDiagramm + 1) & „]C[“ & (intBereichsSpalten – 1) & „],MAX(R[-“ & (intZeilenDiagramm + 1) & „]C:R[-“ & (intZeilenDiagramm + 1) & „]C[“ & (intBereichsSpalten – 1) & „])))“

    ‚ — =BEREICH.VERSCHIEBEN($A$1;0;9-ZÄHLENWENN(B2:I2;MAX(B2:I2)))

Bei mir lief es hervorragen – der Kunde erhielt auf mehreren Rechnern eine Fehlermeldung – Laufzeitfehler 1004.

Deutlich: ich habe keine Ahnung warum!

Statt einer programmierten Formel habe ich dann die Daten mit einer Schleife aufbereitet – das geht immer …

Heißt: sorry, ich weiß den Grund nicht!

Manchmal nervt es mich, dass ich auf mein gutes Aussehen, meine hohe Intelligenz und mein vieles Geld reduziert werde. Ich bin auch gut im Bett!

PlotArea – der Zeichnungsbereich eines Diagramms. Die Aufgabe: aus generierten Daten soll ein XY-Diagramm erzeugt werden. Nichts leichter als das:

' -- das Diagramm
     Set xlChart = xlBlattDiagramm.ChartObjects.Add(500, 100, 800, 400)
     Set xlDiagramm = xlChart.Chart
' -- XY-Diagramm
xlDiagramm.ChartType = xlXYScatter

With xlDiagramm
    .SetSourceData Source:=xlBlattDiagramm.Range(xlBlattDiagramm.Range("B" & (intZeilenDiagramm + 2)), _
        xlBlattDiagramm.Range("C" & intBereichsZeilen))  '   Range("'fin. Impact'!$B$8:$C$17") - Datenquelle

    .SetElement msoElementDataLabelLeft ' -- Datenbeschriftung
    .SetElement msoElementLegendNone ' -- keine Legende

    .FullSeriesCollection(1).DataLabels.Format.TextFrame2.TextRange. _
        InsertChartField msoChartFieldRange, "='" & strKategorie & "'!$A$" & (intZeilenDiagramm + 2) & ":$A$" & intBereichsZeilen & "", 0
    ' -- Beschriftung der Datenpunkte

    .FullSeriesCollection(1).DataLabels.ShowValue = False
    .FullSeriesCollection(1).DataLabels.ShowRange = True ' -- Werte anzeigen

    .Axes(xlValue).TickLabelPosition = xlNone
    ' -- y-Achse ausblenden

Das Ergebnis:

Nun möchte ich noch die Zeichnungsfläche verschieben, damit man die Beschriftung der Y-Achse besser sehen kann. Obwohl sie einen Abstand von Links = 7 hat, darf ich diesen Wert nicht auf 100 setzen?!?

Nach vielem Probieren finde ich die Lösung:

.PlotArea.Width = .PlotArea.Width * 0.9
.PlotArea.Left = .PlotArea.Left + 100

Das klappt!

Ich verstehe es nicht.

Wer nur die Hälfte weiß, spart 50 Prozent.

Und schon wieder bin ich reingefallen. Ich möchte in einem Excel-Formular per VBA eine Datenübrprüfung einfügen. Referenzspalte ist Spalte A. Steht dort kein Wert wird eine Datenüberprüfung generiert:

For j = 11 To ThisWorkbook.Worksheets(i).Range(„A1“).SpecialCells(xlCellTypeLastCell).Row
If ThisWorkbook.Worksheets(i).Range(„A“ & j).Value = „“ Then
‚ — Datenüberprüfung
End If
Next

Und natürlich erhalte ich einen Fehler: G36 ist „leer“ (weil verbunden mit G35), aber in G36 kann man keine Datenüberprüfung einschalten ( weil verbunden mit G35). Die Lösung: RAUS MIT DEN VERBUNDENEN ZELLEN:

Dann klappt es hervorragend.

Gym? Ich dachte du meintest Gin!

Ich habe den Fehler nicht einkreisen können. Aber er ist da:

In einem Excelformular werden Daten eingegeben. Dort werden Datenüberprüfungen verwendet, die Daten aus einem anderen Tabellenblatt holen. Die Liste verwendet einen Bezug auf das Blatt =tbl_Basisdaten!…

Ich kopiere über eine Schaltfläche ein Tabellenblatt „Interview BIA“ von einer anderen Datei in die aktuelle Datei.

Dadurch wird der Bezug auf die alte Datei hergestellt =[Alte Datei.xlsm]tbl_Basisdaten!…

Das ist mir leider nicht aufgefallen, weil die Dateien auf meiner Festplatte liegen und es deshalb zu keiner Fehlermeldung kommt.

Das muss raus! Okay – wir ändern das:

Ich generiere die Datenüberprüfungen aufgrund der Basisdaten auf dem Interviewformular. Ich verwende keinen Bezug, schreibe per Programmierung „Auftragsabwicklung;Arbeitsvorbereitung und Einkauf;Produktrealisierung Individual;Produktrealisierung maschinelle Fertigung;Wartung / Instandhaltung;Lager / Logistik;EDV-Systembetreuung:

    For i = 2 To intZeilen
        strZellinhalt = ThisWorkbook.Worksheets(Blatt).Range(strSpalte & i).Value
        strListeDatenüberprüfung = strListeDatenüberprüfung & "," & strZellinhalt
    Next
    If strListeDatenüberprüfung Like "*,*" Then
        strListeDatenüberprüfung = VBA.Mid(strListeDatenüberprüfung, 2)
    End If
    If strListeDatenüberprüfung <> "" Then
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=strListeDatenüberprüfung   ' -- geändert, weil Interviewblatt nun importiert wird
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End If

Das Ergebnis:

Klappt:

Ich speichere die Datei, schließe sie und öffne sie:

„Wir haben ein Problem bei einigen Inhalten erkannt. Sollen wir so viel wie möglich wiederherstellen? Wenn Sie der Quelle dieser Arbeitsmappe vertrauen, klicken Sie auf ‚Ja‘.“ Die Datei ist kaputt! Ich finde den Fehler nicht!

Böses, böses Excel!

Das ist kein Speck! Das ist erotische Nutzfläche!

Ich erstelle ein Add-In für einen Kunden. Mit Makros und mit Symbolen im Menüband. Ein Teil des XML-Codes sieht wie folgt aus:

  <tab id="tabBCM" label="BCM">
    <group id="grpEinstellungen" label="Einstellungen">
      <button id="cmdKonfigurationImpact" imageMso="FieldList" label="Konfiguration Impactbewertung" onAction="cmdKonfigurationImpact" size="large" supertip="Öffnet den Dialog zur Konfiguration der Impactbewertung des Kernprozesses" screentip="Dialog: Konfiguration"></button>
      <button id="cmdKonfiguration" imageMso="ControlLayoutStacked" label="Konfiguration Betrachtungshorizont" onAction="cmdKonfiguration" size="large" supertip="Öffnet den Dialog zur Konfiguration" screentip="Dialog: Konfiguration"></button>
      <button id="cmdBasisdaten" imageMso="ControlLayoutTabular" label="Basisdaten (Interview BIA)" onAction="cmdBasisdaten" size="large" supertip="Öffnet den Dialog zur Eingabe der Basisdaten" screentip="Dialog: Basisdaten"></button>

Das Ergebnis sieht wie folgt aus:

Mit fällt auf, dass auf einem kleinen Bildschirm (beispielsweise Laptop) die Gruppen zusammengepackt werden. Diese Darstellung gefällt mir nicht:

Zum Glück entdecke ich, dass man in die Gruppen auch Bilder (imageMso) einfügen kann. Und nun wird mir der Zweck klar: beim Verkleinern werden diese Symbole angezeigt. Das werde ich nun immer machen:

An der Darstellung des Menübandes hat sich nichts geändert:

Auf meinem Grabstein soll stehen: „Guck nicht so doof, ich läge jetzt auch lieber am Strand!!!“

Nein, nein, nein – so nicht! Bitte verbindet keine Zellen! Das bringt nur Ärger! Einige Ärgernisse habe ich hier schon beschrieben – über ein neues bin ich vor einigen Tagen gestolpert: Wenn A1 und A2 verbunden sind, dann liefert:

MsgBox Range(„A1“).Offset(0, 2).Address

Die Zelladresse C1, dagegen:

MsgBox Range(„A1“).Offset(2, 0).Address

ergibt: A4! UUUAAAAH!

Also: bitte, bitte, bitte! – Nicht verbinden!

SO NICHT!

Home is, where WIFI is.

Dürfen die das? Ich wollte gerade in Excel mit VBA programmieren, öffne den VBA-Editor:

und bin ein bisschen verblüfft. Wo kommt denn DER Code her? Ich überlege. Stimmt: ich habe vor Kurzem das Add-In „Analyse-Funktionen“ installiert. Ich wollte etwas in einem der Assistenten nachschauen … Das hat man nun davon!

Weltmacht mit drei Buchtstaben? ICH!

Ich habe für eine Firma ein kleines Add-In geschrieben: Daten werden von A nach B übertragen und andere Daten zurück von B nach A. Um die korrekten Daten zu ermitteln verwende ich die Formeln – man kann es mit SVERWEIS machen – ich habe mich für die flexiblere Variante INDEX und VERGLEIC entschieden. Diese Formel wird in den Bereich eingefügt, der Bereich wird kopiert und als Werte wieder eingefügt:

On Error Resume Next
[...]
xlBereich.Copy
xlBereich.PasteSpecial Paste:=xlPasteValues

Das Programm läuft. Nach einigen Tagen erhalte ich einen Anruf:ein Fehler ist aufgetreten. Ich schaue es mir an. Sie Anwenderinnen haben auf den Bereich einen Filter gesetzt und gefiltert! Klaro – nun kann mein Makro nicht mehr die Inhalte als Werte einfügen:

Also überprüfe ich, ob ein Filter eingeschaltet ist. Wenn ja – dann wird er ausgeschaltet. Und schon kann das Programm wieder sauber die Daten übertragen …

Wenn mir langweilig ist, gehe ich in einem Bekleidungsgeschäft in eine Umkleidekabine und rufe: „Hey, hier ist kein Toilettenpapier!“

Ich programmiere ein Formular für einen Kunden. Einige Zellen sollen dynamische gesperrt oder entsperrt werden. Ich erhalte eine Fehlermeldung:

Seltsam: Der Befehl:

MsgBox Range(„K158“).Locked liefert False

Okay – noch ein Versuch:

Nutzt nichts! Ich schaue nach:

Ah! Verbundene Zellen. Ich darf nicht eine Zelle aus diesem Zellverbund sperren oder entsperren – dies funktioniert nur bei der ersten (hier: C158). Könnte mir Excel VBA ja auch sagen …

Pornos geben jungen Leuten eine falsche Vorstellung davon, wie schnell man heute bei Handwerkern einen Termin bekommt.

Böses Excel! Ich erstelle ein dynamisches Excel-Formular mit VBA. Ich muss bestimmte Stellen ermitteln – beispielsweise die Position „7.1.“ Die Funktion

=VERGLEICH(„7.1.“;A:A;0)

liefert die Zeilennummer. Ich versuche es mit VBA:

Application.WorksheetFunction.Match(„7.1“, ThisWorkbook.Worksheets(„Interviewfragebogen“).Range(„A:A“), 0)

Eine Fehlermeldung ist die Folge:

Gefühlte 120 Versuche, warum WorksheetFunction.Match nicht funktioniert und wie man diese Funktion richtig schreibt. Die Match-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden. Bis ich dahinterkomme, dass ich nicht „7.1“ suche, sondern „7.1.“ Der letzte Punkt hat gefehlt. Während die Funktion

=VERGLEICH(„7.1“;A:A;0)

den Fehler #NV erzeugen würde, schreibt WorksheetFunction.Match erst gar nichts in die Zelle, beziehungsweise in das Meldungsfenster. VBA für Excel könnte ja wenigstens sagen, dass die FUNKTION okay ist, dass sie allerdings einen fehlerhaften WERT liefert. Aber nicht so etwas!

Du stehst gut gelaunt auf, fühlst dich gut und dann triffst du Menschen …

Ich erstelle für einen Kunden ein Programm. Per VBA greift Visio auf Excel zu. Dabei wird überprüft, ob Excel (beziehungsweise eine bestimmte Datei) schon offen ist:

On Error Resume Next

' -- greife auf das offene Excel zu
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
    ' -- falls Excel nicht offen ist: öffne Excel
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False
End If

On Error GoTo 0

Die Zeile GetObject(, „Excel.Application“) liefert einen Fehler, wenn sich der Cursor in einer Zelle befindet:

Eigentlich sollte es automatisch (automatisiert) gehen …

Ich kaufe mir jetzt ein Fernglas. Dann sehe ich weiter.

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.

Liebes Mathebuch, werd erwachsen und lös deine Probleme allein

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.

Selbstgespräche geben einem die Chance, Recht zu behalten.

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

Auf meinem Grabstein soll später mal stehen „Ich würd‘ jetzt auch lieber am Strand liegen…“

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.

Erzähle nicht, wie Du warst, sondern zeige, wie Du jetzt bist.

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

 

Vorhin war’s noch da

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 …

Being funny is not everyone’s first choice

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

Das Problem ist nicht mein leichter Knall. Das Problem ist jemanden zu finden, der einen möglichst kompatiblen Knall hat.

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!

Wer will schon den aalglatten Prinzen, wenn man schon den Hofnarren mit Dreitagebart haben kann?

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!

Ich bin im Niveau ganz flexibel

Ich versuche per VBA benutzerdefinierte Eigenschaften an eine Datei zu binden. Nichts leichter als das, denke ich:

20170126Benutzereigenschaften01

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:

20170126Benutzereigenschaften02

Typen unverträglich? Okay – dann ohne Objektverweis:

20170126Benutzereigenschaften03

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:

20170126Benutzereigenschaften04

Und richtig: Dort wird noch zwingend der Parameter „LinkToContent“ verlangt.

Nächster Test:

20170126Benutzereigenschaften05

??? 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:

20170126Benutzereigenschaften06

we are not amused

Wirklich erstaunlich. In einer Excel sind mehrere Tabellenblätter ausgeblendet:

ausgeblendete Tabellenblätter

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:

20160628Currentregion

Ich rücke ja schon – aber ich sehe den Schlauch nicht auf dem ich stehe

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?

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.

 

Workbooks – zwei Seelen wohnen ach in meiner Brust

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?

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 …

 

Wo ist der Remote-Server-Computer

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?

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ß“.

Aufzählungslisten

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.

Nicht immer erhält man die Listen der Eigenschaften und Methoden.