Kategorie: VBA-Befehle

Ist das hier das Treffen der anonymen Klugscheißer? – Das heißt Meeting!

Excel-Objekte, VBA-Befehle

Gestern kam eine Dame auf mich zu, für die vor Jahren in Excel mit VBA ein kleines Add-In erstellt hatte.

Sie erzählte, dass es seit einigen Tagen am Ende immer abstürze. Ganz überraschend.

Keine Ahnung was los war:

Eigentlich speichere ich nur die Datei und schließe sie und melde, dass alles geklappt hat. Warum sollte der Code abstürzen? Keine Ahnung!

Ich füge vor und nach dem Befehl SaveAs ein

DoEvents

ein – und: erstaunlicherweise – jetzt läuft es ohne Absturz. Seltsam!

Kommentar hinterlassen

Hübsch bist Du ja. Aber kannst Du bitte einen Sack über Deinen Charakter ziehen?

VBA-Befehle

Ich bin begeistert: ich habe seit vorgestern das Symbol (und damit die Programmiersprache) Python in Excel in der Registerkarte Formeln zur Verfügung:

Ich tippe 

=py

Und bestätige es und erhalte einen Fehler:

… das fängt ja schon gut an …

Noch einmal:

ich tippe

=py

und bestätige es mit der [Tab]-Taste – DAS geht:

Der Grund: [Enter] wird verwendet, um weitere Codezeile einzufügen, nicht um die Eingabe zu beenden …

Kommentar hinterlassen

Alle elf Minuten vergeht am Montag eine Minute

VBA-Befehle

Guten Tag Herr Martin

Ich habe seit langen ein Problem, den ich nicht lösen kann.

Ich habe einen Code und der läuft bis zu einem Punkt durch. Danach kommt ein Debugg Fehler

Wenn ich dann auf Debuggen gehe , danach mit F5 weiter gehe, dann läuft der Code durch.

Hallo Herr S.,

drei Bemerkungen zu Ihrer Frage:

1. Das sind zu wenige Informationen.

Mir fehlt:

* wsKore_Import ist eine Variable, die auf eine Datei verweist?

* wie ist die Variable r deklariert?

* wie ist die Variable i2 deklariert?

* wenn Sie mit dem Mauszeiger über strBLATTNAME fahren – welchen Wert hat diese Variable?

* Gibt es das Blatt strBLATTNAME in der Datei wsKORE_IMPORT?

* Mit was ist strVON gefüllt?

2. Mich wundert:

Set r = …

Sie verweisen auf einen Bereich.

Anschließend:

For each r in r.cells

Hier beißt sich die Katze in den Schwanz:

Sie verwenden die Variable r (genauer: alle Zellen dieses Bereichs), die Sie gefüllt haben und weisen ihr ständig neue Zellen zu, verwenden aber immer noch r.cells. Autsch!

Verwenden Sie hier bitte eine andere Variable:

Dim r2 as Range

For each r2 in r.Cells

Der Punkt vor Cells ist sicherlich weiter oben zugewiesen, oder? With irgendetwas …?

3. Ich hatte auch schon Fälle, in denen ein Makros sehr merkwürdige Dinge machte (ich glaube, das hatte auch mal, dass es beim Debuggen funktionierte und beim Durchlauf (bei mir) IMMER abstürzte. Lösung: Ich habe den Code verändert! SO lange, bis es lief. Und dann mit den Schultern gezuckt.

In Ihrem Beispiel:

Arbeiten Sie mit einer „Zwischenvariable“. Beispielsweise

Dim xlBlatt As WorkSheet

Set xlBlatt = wsKORE_IMPORT.WorkSheets(strBlattname)

Set r = xlBlatt.range(strVon)

Vielleicht klappt das …

Klappt das?

Liebe Grüße

Rene Martin

Kommentar hinterlassen

Ich steck die Jogginghose in die Waschmaschine. Dann hat sie mal ein bisschen Bewegung.

VBA-Befehle

Hallo Rene,

Du als Excel Spezialist kannst mir vielleicht einen Tip geben wie ich
folgende Aufgabe löse:

In einem Excel basierten Tool mit VBA Makros soll erkannt werden, wenn
die Entwicklertools / Visual Basic aufgerufen werden.

Gibt es dafür eine Möglichkeit eben den Vorgang in einem Makro abzufangen?

Hallo Bernhard,

ich wüsste nicht, wie man das abfangen kann.

Gegenfrage: warum willst du das abfangen?

Wenn du VBA verhindern willst, kannst es über die Gruppenrichtlinien sperren.

Eigene Projekte schütze ich manchmal mit Kennwort gegen Zugriff/Einsicht (ich weiß – das kann man knacken)

Du könntest den Anwender das Symbol und die Tastenkombination [Alt] + [F11] wegnehmen – aber pfiffige Anwender finden sicherlich einen Weg …

Liebe Grüße

Rene

####

Hallo Rene,

ich habe doch noch ein wenig experimentiert, nachdem mir
ChatGPT doch noch einen brauchbaren Hinweis gegeben hat.

Die Tastenkombination ALTF11 wird hierbei abgefragt.

Ich schließe mal daraus, dass man auch den Klick im Menü erfassen kann,
aber wie ……………. Das habe ich noch nicht gefunden.

####

Hallo Bernhard,

du wirst scheitern!

Das habe ich auch für einen Moment überlegt – die Tastenkombination [Alt] + [F11] zu verbiegen und mitzuprotokollieren. (Stichwort: OnKey)

Und ja: du könntest das Symbol Entwicklertools / Visual Basic durch ein anderes ersetzen oder den Befehl abfangen.

Allerdings gelangt man auch in VBA-Editor über das Kontextmenü des Tabellenblattes:

und über Ansicht / Makros / Makros anzeigen / Bearbeiten.

Oder Entwicklertools / Makros.

Oder du fügst dir das Symbol „Makros anzeigen“ in die Symbolleiste für den Schnellzugriff.

Oder: du öffnest Excel mit einer leeren Arbeitsmappe. Wechselst nach VBA. Dann öffnest du eine Datei mit Makros. Und kannst jetzt mit [Alt] + [Tab] oder dem zweiten Excelsymbol in der Taskleiste nach VBA wechseln.

Das heißt: du müsstest diese Aktionen generell in Excel abfangen. Also: Excel überwachen.

Auch hier wirst du an Grenzen stoßen, weil es Anwender gibt, die Excel über das Kontextmenü auf dem Desktop öffnen: Neu / Microsoft Excel Arbeitsblatt.

Du wirst an Grenzen stoßen … Maximal 90% der Fälle erreichen …

Liebe Grüße

Rene

####

Hallo Rene,

nochmal vielen Dank. Ich habe das Thema at Acta gelegt.
Ich brauche es ja auch nicht, mir kam halt nur so eine Idee.

Wünsche Dir weiterhin alles Gute und viel Spaß mit Excel & Co.

Kommentar hinterlassen ,

Wenn eine Türe geschlossen wird, öffnet sich eine andere. (Boing)

sortieren, VBA-Befehle

Die benutzerdefinierten Listen in Excel sind bekannt:

Umso erstaunlicher gestaltet sich das Objekt, wenn man mit VBA darauf zugreifen möchte:

Die Anzahl der Elemente erhält man mit dem Befehl CustomListCount :

MsgBox Application.CustomListCount

Ein neues Element wird hinzugefügt mit einem Array mit dem Befehl addCustomList:

Dim meineListe As Variant
    meineListe = Array("a", "b", "c")

    Application.AddCustomList meineListe

Sehr angenehm: existiert der Eintrag bereits, ist weder Fehlermeldung noch ein zweiter Listeneintrag die Folge – alles bleibt beim Alten.

Auf ein Element kann per Nummer zugegriffen werden. Das Element wird als Array ausgegeben. Die Elemente können per Schleife ermittelt werden (Achtung: das erste Element trägt die Nummer 1!):

    meineListe = Application.GetCustomListContents(21)
    MsgBox Join(meineListe, "/")

Ein Element kann mit der Methode deleteCustomList gelöscht werden:

Application.DeleteCustomList 21

Wird eine Liste mit einer benutzerdefinierten Liste sortiert, lautet der Befehl:

ActiveWorkbook.Worksheets("Tabelle1").Sort.SortFields.Add2 Key:=Range( _
        "F5:F16"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "Blue,Standard,Silber,Gold,Platinum", DataOption:=xlSortNormal

Wird jedoch in einer Pivottabelle nach dieser benutzerdefinierten Reihenfolge sortiert, lautet der Befehl:

    ActiveSheet.Range("$I$5").Sort Order1:=xlAscending, Type:=xlSortLabels, _
        OrderCustom:=12, Orientation:=xlTopToBottom

Allerdings ist nicht 12 der Eintrag „Blue, Standard, Silber, Gold, Platinum“, sondern die Nummer 11:

Application.GetCustomListContents(11)

Ein bisschen verwirrend!

Kommentar hinterlassen ,

Manche Leute sagen wenn sie Kaffee trinken können Sie nicht schlafen. Bei mir ist es genau umgekehrt: wenn ich schlafe, kann ich keinen Kaffee trinken.

VBA-Befehle

In einer Pivottabelle sollen die Kalenderwochen als Spaltenbeschriftung verwendet werden. Eigentlich kein Problem. Jedoch beim Jahreswechsel soll natürlich gezählt werden: 51, 52, 53, 1, 2, 3, …

Würd man die Standardsortierung der Pivottabelle verwenden, wäre die Zählung: 1, 2, 3, … 52, 53.

Aber man kann doch benutzerdefiniert sortieren. Fügt man eine benutzerdefinierte Liste per Programmierung in Excel ein, beispielsweise so:

Sub BenutzerdefinierteListeEinfuegen()
    Dim customList As Variant
    Dim i As Integer
    
    ' Definiere die benutzerdefinierte Liste von 40 bis 53 und dann 1 bis 13
    customList = Array("40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14")
    
    ' Füge die benutzerdefinierte Liste in Excel ein
    Application.AddCustomList ListArray:=customList
End Sub

wird die Liste immer nach laufender Kalenderwoche sortiert.

Allerdings frage ich mich: Was passiert, wenn dieses Makro zwei Mal aufgerufen wird? Ein Fehler, weil die Liste schon vorhanden ist? Eine weitere Liste?

Die Antwort: gar nichts: Excel übergeht diese Liste – sie ist schon da. Sehr schön – Excel hat heute mal nicht genervt.

Kommentar hinterlassen

Nett kann ich auch – bringt aber nix.

VBA-Befehle

Gestern erreichte mich folgende Mail, über die geschmunzelt habe – meine Hilfe war wohl ein Anschubsen (es ging um Visio-Programmierung):

Hallo Rene,

nein, nicht Du, sondern ich stand auf dem Schlauch!…
Da ich mir von Mittendrin meines Programms ein Stück genommen hatte, gab es keine Zuordnung zur aktiven Page.
Leider war auch die Fehlermeldung wenig hilfreich….

Problem gelöst – auf Deinen Seite hatte ich geschaut – war ja alles klar…., aber eben…

Danke trotzdem ganz toll!
Aber das ist so was. Wenn ich mich an meine Dienstzeit erinnere – so haben wir viele Probleme so gelöst, dass man den Anderen alles erklärt hat. Und das hat oft gereicht, um das Problem zu lösen.
Der Gesprächspartner musste oft gar nichts sagen, oder irgend eine dumme Frage stellen. Das hat die Gehirnwindungen zu anderen Pfaden veranlasst…
Deine Sätze haben mir eben verraten, dass „fast alles richtig ist“ …. Dann ging es ganz schnell.

Wie toll doch Gehirne funktionieren!


Liebe Grüße
Wolfgang

Kommentar hinterlassen

Außerdem schmeckt Spinat wesentlich besser, wenn man ihn kurz vor dem Servieren durch ein Schnitzel ersetzt.

VBA-Befehle

Claus stellt eine Frage zu VBA: In einer Schleife wird ein Haltepunkt gesetzt. Eine Variable oder mehrere Variablen werden dort überwacht.

Bei ihm verschwindet die Anzeige im Überwachungsfenster – er muss immer hineinklicken, damit er die Werte der Variablen sieht.

Ich teste es – seltsam – bei mir klappt es – nichts verschwindet:

Kennt jemand das Problem?

Kommentar hinterlassen ,

Unfall auf der Autobahn. LKW voller Viagra umgekippt. Auf 10 km steht der Verkehr.

VBA-Befehle

Guten Tag Herr Martin

Danke für die Glückwünsche und die Antwort.

Aber mit welchem Zeithorizont muss ich rechnen, dass Excel nur noch als Online-Version ausgeführt werden kann oder VBA ganz aus Excel verschwindet.  Ich denke auch wenn ich mit VB.Net arbeite, wird das bei der Online-Version nicht gehen, da ich eine Lokal-Installation der Exe benötige.

Wenn ich VB.net innerhalb der Excel schreibe erhalte ich wieder ein Excel mit Makro.

Aktuell kann ich bei lokal-Installationen sagen, dass alle Online-Excel lokal geöffnet werden kann. Somit ist es mir egal, ob die Excel-Datei online oder lokal ist.

Die Programmiersprache JavaScript /Type Script, M, Dax kenne ich nicht.

Welche Sprache lässt zu, dass ich eine «chaotische» Excel-Datei habe (keine Strukturierten Daten) und bei Änderungen innerhalb von Zellen oder aktivieren eines Arbeisblattes, oder doppelklick usw. aktiv eingreifen kann.

Ich interpretiere die Aktion eines Users und leite diesen.

Somit schaffe ich immer intelligentere Lösungen innerhalb vom Excel und habe, das Ziel, dass der Anwender in der Schweiz immer weniger Fehler machen kann uns somit seine Daten an das Bundesamt für Statistik besser werden.

Ein Beispiel ist, wenn ein User Sagt, dass ich ein Gebäude besitze, dann werden die Abschreibung und Zinsen geprüft und mit der Grösse des Betriebs in relation gesetzt. Ebenso werden die Mieten in relation der Betriebsgrösse gesetzt und alles interpretiert. Dann wird analysiert in welcher KST die Anlagen erfasst sind und welche übrigen Anlagenutzungskosten diese Kostenstellen tragen und wie die Umlagen gesteuert sind. Wenn irgend was in dieser Konstellation nicht korrekt ist, dann wird der Anwender entsprechend hingeführt, was falsch sein könnte. Das ist zu komplex dies innerhalb einer Formel im Excel durch zu führen. Im VBA ist das ein grosser Code welcher viele logische Prüfungen durchläuft, und dann merkt, wo der Fehler sein könnte. Das ganze wird bei einigen Zellenänderungen angesteuert, damit diese Prozedur nicht immer durchläuft. So muss der User nicht daran denken ein Knopf zu drücken, sondern der Export an das Bundesamt für Statistik wird gesperrt wenn noch Fehler vorhanden sind.

Und ehrlich gesagt, ich habe keine Ahnung wie ich das anders Lösen sollte.

Wenn ich eine saubere Excel-Datei als Quelle nutze, die Daten automatisch systematisch auslesen, interpretieren, umwandeld, darstellen uws möchte sehe ich keine Herausforderung mit den anderen Programmeirsprachen, wenn man sie behersrscht (ist bei mir nicht der Fall) Freundliche Grüsse

###

Hallo Herr S.,

die Frage nach dem “wie lang“ müssen Sie an Microsoft stellen. Aber ich fürchte, dass Sie auch dort keine verlässliche Antwort erhalten.

Ich verlasse auf die Aussage eines Microsoft-Mitarbeiters auf der letzten Excelkonferenz in Sofia, der uns bestätigte, dass Excel NICHT VBA entfernen wird. Ich kann mir auch nicht vorstellen, dass das Desktop-Excel verschwinden wird.

Ein großer Teil meiner Einnahmen bestreite ich mit VBA – ich lebe davon und werde es wohl auch in Zukunft.

Ich habe gerade Ende letzten Jahres dem Gartenbaureferat der Stadt München vorgeschlagen, eine Lösung, die auf VBA basiert, einzuführen, damit sie ihr Bestellwesen vereinheitlichen können. Da die Stadt München in puncto Makros sehr restriktiv ist und prüfen müssen, dauert der Prozess wohl noch ein bisschen. Alle anderen Programmiersprachen würde nicht das Ergebnis erzielen, das sie haben wollen.

Und: so wie mir geht es vielen anderen auch. Ich bin zuversichtlich, dass wir noch sehr, sehr lange Excel als Desktop-Lösung haben werden und dass und VBA erhalten bleibt.

Liebe Grüße und: ein schönes Wochenende  

Rene Martin

Kommentar hinterlassen

Sie: Du, ich war beim Friseur! – Er: Und – was hast du dort gemacht?

PowerPoint nervt auch, VBA-Befehle

VBA-Schulung. Eine Teilnehmerin möchte mehrere Diagramme per VBA nach PowerPoint von Excel kopieren:

Wir beginnen.

Wir setzen einen Verweis auf die PowerPoint-Objektbibliothek.

Wir greifen auf PowerPoint zu und lassen das Programm anzeigen:

Sub PowerPointZugriff()
    Dim ppApp As PowerPoint.Application
    Dim ppPräsentation As PowerPoint.Presentation
    Dim ppFolie As PowerPoint.Slide
    
    Dim xlBlatt As Worksheet
    Dim i As Integer
    
    Set xlBlatt = ActiveSheet
    
    Set ppApp = New PowerPoint.Application
    ppApp.Visible = msoTrue

Klappt.

Wir erstellen eine neue Präsentation:

Set ppPräsentation = ppApp.Presentations.Add

Klappt.

Wir fügen eine neue Folie ein – eine Fehlermeldung ist die Folge:

Ich erinnere mich: VBA schlägt zwar die Methode AddSlide mit zwei Parametern vor:

Korrekt wäre jedoch die Methode Add:

Set ppFolie = ppPräsentation.Slides.Add(1, ppLayoutTitleOnly)

Und damit funktioniert das Programm:

Sub PowerPointZugriff()
    Dim ppApp As PowerPoint.Application
    Dim ppPräsentation As PowerPoint.Presentation
    Dim ppFolie As PowerPoint.Slide
    
    Dim xlBlatt As Worksheet
    Dim i As Integer
    
    Set xlBlatt = ActiveSheet
    
    Set ppApp = New PowerPoint.Application
    ppApp.Visible = msoTrue
    Set ppPräsentation = ppApp.Presentations.Add
    
    For i = 1 To xlBlatt.ChartObjects.Count
        Set ppFolie = ppPräsentation.Slides.Add(i, ppLayoutTitleOnly)
        ppFolie.Shapes(1).TextFrame.TextRange.Text = "Überschrift " & i
        xlBlatt.ChartObjects(i).Copy
        ppFolie.Shapes.Paste
    Next i
    ' Achtung: nicht AddSlides sondern Add

Das Ergebnis:

Kommentar hinterlassen ,

Einmal wie ein kleines Kind auf den Boden werfen und ganz laut losbrüllen. Scheitert heutzutage daran, dass man nicht wieder hochkommt!

VBA-Befehle

Excelstammtisch. Wir diskutieren ChatGPT und copilot. Ich zeige, dass die Lösungen manchmal nicht korrekt sind.

Problem: ich möchte mit VBA ermitteln, ob in einer Zelle eine Datenprüfung liegt. ChatGPT antwortet mir:

Sub CheckDataValidation()
    Dim rng As Range
    Dim validationType As XlDVType
    
    ' Definiere die Zelle, die du überprüfen möchtest
    Set rng = Worksheets("DeinBlatt").Range("A1")
    
    ' Überprüfe, ob die Zelle eine Datenprüfung hat
    If rng.Validation.Type <> xlValidateNone Then
        ' Erhalte den Typ der Datenprüfung
        validationType = rng.Validation.Type
        
        ' Zeige den Typ der Datenprüfung in der Konsole an (kann angepasst werden)
        MsgBox "Die Zelle hat eine Datenprüfung vom Typ: " & validationType
    Else
        ' Wenn keine Datenprüfung vorliegt
        MsgBox "Die Zelle hat keine Datenprüfung."
    End If
End Sub

Ich teste:

Ich beschwere mich:

ChatGPT schlägt

xlValidateCustom

vor. Das Ergebnis, wenn keine Datenprüfung in der Zelle vorhanden ist:

Ich frage copilot. Die Antwort:

If Not Cells(1, 1).Validation Is Nothing Then
    MsgBox "Die Zelle hat eine Datenüberprüfung."
Else
    MsgBox "Die Zelle hat keine Datenüberprüfung."
End If

Das Ergebnis:

Das Makro meldet immer, dass die Zelle eine Datenüberprüfung hat.

Sämtliche Antworten sind falsch oder fehlerhaft.

Alexander lässt nicht locker. Er findet nach mehreren Versuchen eine Lösung bei ChatGPT:

Sub CheckNoDataValidationCriteria2()
    Dim targetCell As Range
    Set targetCell = Selection ' Hier die Zelle angeben, die du überprüfen möchtest
    
    ' Überprüfen, ob die Zelle eine Datenprüfung hat
    If Not targetCell.Validation Is Nothing Then
        ' Überprüfen, ob spezifische Kriterien festgelegt wurden
        Dim validationFormula As String
        On Error Resume Next
        validationFormula = targetCell.Validation.Formula1
        On Error GoTo 0

        If validationFormula = "" Then
            MsgBox "Die Zelle hat keine spezifischen Datenprüfungskriterien (jeder Wert ist zugelassen)."
        Else
            ' Kriterien in Zelle D1 schreiben
            Range("B2").Value = "Datenprüfungskriterien: " & validationFormula
            MsgBox "Die Zelle hat Datenprüfung mit spezifischen Kriterien. Die Kriterien wurden in Zelle D1 geschrieben."
        End If
    Else
        ' Wenn keine Datenprüfung vorhanden ist, Zelle D1 löschen
        Range("B2").ClearContents
        MsgBox "Die Zelle hat keine Datenprüfung."
    End If
End Sub

Sie funktioniert: für Zellen mit und für ohne Datenüberprüfung:

Die Lösung funktioniert; der Denkansatz ist jedoch nicht korrekt:

Der Befehl

On Error Resume Next

übergeht den Fehler. Die Zeile 

validationFormula = targetCell.Validation.Formula1

kann ausgeführt werden oder nicht. Würde sie einen Fehler liefern (also keine Datenüberprüfung ist vorhanden), wird sie übergangen und validationFormula bleibt leer („“). Ich hätte es mit err.number überprüft.

Aber okay – ich gebe zu: man kann mit ChatGPT / copilot eine funktionierende Lösung finden. Wenn man beharrlich ist.

Danke an Alexander Vogelmann für seine Beharrlichkeit.

Kommentar hinterlassen ,

Hunde, die schellen, beißen nicht.

Excel-Objekte, VBA-Befehle

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

Kommentar hinterlassen

Neue Klobürste kaufen, auspacken, in die Spülmaschine stellen, den Partner/die Partnerin öffnen lassen und sagen: „Woah – wie neu!“ Der Blick: unbezahlbar!

VBA-Befehle

Moin Rene,

habe was „Schönes“ in den Excel – Dateieigenschaften gefunden:

Als ich ein Addin an mene Kollegen verteilt habe, dem ich über den Windows – Dialog (Rechtsklick) „Dateieigenschaften“ > „Details“ einige zusätzliche Infos wie Titel, Betreff, Kommentare, etc. verpasst hatte, konnten diese das Addin nicht laden oder aktivieren. Beim direkten Start des Addins per Doppelklick kamen seltsame Fehlermeldungen wie „Excel kann auf die Datei ‚C:\Users\awa\AppData\Roaming\Microsoft\AddIns\Pro….xlam‘ nicht zugreifen. Dies kann mehrere Gründe haben:

– Name/Pfad nicht vorhanden

– Dokument wird von anderem Programm verwendet

– Name der Arbeitsmappe die gespeichert werden soll, ist identisch zu dem Namen eines anderen Dokuments, welches schreibgeschützt ist“

Kann nicht sein – als ich das Addin zuletzt getestet hatte, lief alles noch?!

Also habe ich mal dumm die von mir eingetragenen Dateieigenschaften Stück für Stück wieder rausgelöscht – und kam der Sache auf die Spur:

Bei „Revisionsnummer“ dürfen nur Zahlen eingetragen werden, keine Buchstaben oder Kombinationen mit Buchstaben!

Auch bei „normalen“ .XSLX – Dateien funktioniert diese Blockade (durch Eingabe von Buchstaben in die Eigenschaft „Revisionsnummer“) zuverlässig, nur die Fehlermeldungen sind dann andere:

„Wir haben ein Problem bei einigen Inhalten in ….XLSX erkannt. Sollen wir soviel wie möglich wiederherstellen? Wenn Sie der Quelle dieser Arbeitsmappe vertauen, klicken Sie auf ‚Ja'“

Wenn man das macht und die „Reparatur“ durchführt, hat die Datei anschließend die Revisionsnummer 1. Offensichtlich wird diese Eigenschaft von Excel selbst verwaltet / genutzt.

Nur schade, dass Windows ein Editieren durch den user zulässt, was zu den o.a. irritierenden Meldungen führt…

Liebe Grüße aus dem hohen Norden – andreas

####

Vielen Dank, Andreas, für den wertvollen Hinweis – bei XLAM-Dateien erhalte ich auch die Fehlermeldung – XLSX-Dateien kann ich jedoch problemlos öffnen.
Dennoch: Vorsicht vor der Revisionsnummer.

Kommentar hinterlassen ,

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

Excel-Objekte, Merkwürdige Formeln, VBA-Befehle

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!

Kommentar hinterlassen

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

Excel-Objekte, Power Query, VBA-Befehle

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 …

Kommentar hinterlassen , ,

Wenn noch kochendes Wasser übrig ist – einfrieren, man kann es immer wieder gebrauchen.

VBA-Befehle

Nein – das erfreut mich gar nicht. Den ganzen Tag habe ich VBA in Excel programmiert – getestet, angepasst, verändert, getestet, … Plötzlich funktioniert es nicht mehr. Ein Modul ist nicht mehr erreichbar. Ich versuche es zu retten; zu exportieren:

Modul nicht gefunden.

Keine Chance – kein Zugriff auf das Modul und den Code.

Also noch einmal von vorne … Zum Glück hatte ich Datei vom Vortag und zum Glück waren es nicht sehr viele Funktionen …

Beim zweiten mal geht es immer schneller …

Kommentar hinterlassen ,

Hühner sind das ökonomischste Lebensmittel überhaupt, denn man kann sie vor ihrer Geburt und nach ihrem Tod essen.

Outlook nervt auch, VBA-Befehle

Man muss immer aufpassen! Ganz genau hinschauen! Mal eben schnell – das geht einfach nicht!

Ich wollte nur „mal schnell etwas probieren“. Mehrere Mails über Outlook aus Excelversenden an die Mailadresse, die in einer Liste stehen.

Also mal schnell etwas Code getippt (mit einem Verweis auf die Microsoft Outlook-Bibliothek):

Sub MailVersenden()
    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem
    Dim i As Integer
    
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    
    For i = 2 To Range("A1").CurrentRegion.Rows.Count
        With olMail
            .To = Range("B" & i).Value
            .Subject = "Diese Mail ist völlig überflüssig"
            .Body = "Hallo " & Range("A" & i).Value & "," & vbCr & vbCr & "Nicht wundern - das ist nur eine Testmail" & vbCr & vbCr & "Gruß"
            .Send
        End With
    Next i
    
End Sub

Und der Test – liefert einen Fehler. Bei der ZWEITEN Mail:

Outlook kennt mindestens einen Namen nicht.

Seltsam! Hinschauen – überlegen – stimmt:

Ich muss INNERHALB der Schleife eine neue Mail erzeugen – das heißt: für jede Zeile wird eine neue Mail versenden:

Sub MailVersenden()
    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem
    Dim i As Integer
    
    Set olApp = New Outlook.Application

    For i = 2 To Range("A1").CurrentRegion.Rows.Count
        Set olMail = olApp.CreateItem(olMailItem)
        With olMail
            .To = Range("B" & i).Value
            .Subject = "Diese Mail ist völlig überflüssig"
            .Body = "Hallo " & Range("A" & i).Value & "," & vbCr & vbCr & "Nicht wundern - das ist nur eine Testmail" & vbCr & vbCr & "Gruß"
            .Send
        End With
    Next i
    
End Sub

DANN funktioniert es auch. Beim nächsten Mal – halt nicht so schnell!

Kommentar hinterlassen , , ,

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

Excel-Objekte, VBA-Befehle

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!

Kommentar hinterlassen , , ,

Im Kochbuch stand: „Man reibe 3 Tage alte Brötchen.“ Nach einem halben Tag hatte ich die Badewanne und die Schnauze voll.

Fehlermeldungen, Merkwürdige Formeln, VBA-Befehle

Verblüfft war ich schon.

Der Auftrag: der Kunde möchte in eine Excelliste Informationen eintragen, beispielsweise die Namen der Tabellenblätter, die mit einem Klick auf einen Button erzeugt werden. Auf den Blättern werden Verknüpfungen zu den anderen Zellen hergestellt, auf einem weiteren Blatt wird eine Formel aktualisiert. So weit so gut – ich teste – klappt:

Der Kunde testet und schickt meine Mail mit dem Hinweis, dass „Nicht genügend Speicher“ vorhanden sei.

Seltsam – bei mir nicht.

Doch – wenn er 200 Tabellenblätter erzeuge, meldet Excel diesen Fehler nach Blatt Nummer 117.

Seltsam. Bei mir auch:

Der Fehler trat beim Erstellen der Formel auf. Zuerst dachte ich an Schwierigkeiten des Prozessors beim Erstellen so vieler Formeln. Oder vielleicht hatte ich die Objektvariablen nicht sauber „geputzt“. Oder es gab ein Geschwindigkeitsproblem:

Die Ursache war eine andere: Die Formel war schlicht zu lang. Mit der Funktion SUMMEWENNS sollten Berechnungen für jedes Tabellenblatt ausgeführt werden und diese Werte addiert werden. Ein teil der Formel (bei Blatt Nummer 116) ist hier zu sehen:

Also haben wir eine andere Lösung gesucht.

Und: mit Verlaub: ich bin nicht sicher, ob die fast 100 Monster-SUMMEWENNS auf dem Tabellenblatt die Datei mit den 200 Blättern nicht in die Knie gezwungen hätte …

Kommentar hinterlassen ,

Hab mal ein Zettelchen unters Bett geklebt. Für das Monster. Heute darf das Monster in meinem Bettchen schlafen. Bin einsam.

VBA-Befehle, Word nervt auch

Hallo Hr. Martin. Wie kann ich in VBA prüfen, ob ein Textfeld als Formularsteuerelement das mit einer Textmarke versehen ist (z Bsp.“TMThemaPos16″) leer ist. Laut Lokal-Fenster soll es ein String mit 5 Leerzeichen sein. Diese Prüfung wird aber ignoriert. Auch auf Empty prüfen bringt keinen Erfolg. Ich muss den Inhalt der Textmarken in eine Excel überführen, die stetig erweitert wird. Es können 30 Themen ins Formular eingetragen werden. Ab einer leeren Textmarke der Kategorie „Thema“, soll die Prozedur beendet werden. Vielen Dank für ihre Unterstützung.

###

Hallo Herr W.,

Wenn ich Ihr Problem richtig verstanden habe, würde ich das folgendermaßen lösen: Formularsteuerelemente haben keinen Namen und können deshalb nicht direkt angesprochen werden. Hinterlegen Sie bei der Eigenschaft „Tag“ einen Text. Durchlaufen Sie mit einer Schleife alle ContentControls und überprüfen Sie, ob das ContentControls(i) den Tag = „yxz“ hat. Wenn ja, dann können Sie den Text auslesen: ActiveDocument.ContentControls(1).Range.Text Ist das die Antwort auf Ihre Frage?

LG und ein schönes Wochenende

Rene Martin

###

Hallo Hr. Martin.

Ich hab das jetzt über die ASC-Funktion gelöst. Frage somit ab, welches ASCII Zeichen die Textmarke hat und wenn diese (in meinem) Fall 32 ist, dann ist sie leer.

Kommentar hinterlassen , ,

Übrigens: Sektfabriken weiht man ein, indem man ein Schiff dagegen wirft.

VBA-Befehle

Ich habe ein Add-In für einen Kunden erstellt. Per Knopfdruck sollen Daten (eine Liste der Debitoren) in eine bestehende Datei importiert werden.

Ich entscheide mich für VBA, weil ich so die Datei und die Daten prüfen kann.

Klappt:

Ich erhalte die Rückmeldung:

„Wir haben nun das File beim ersten Kunden getestet und dabei ist uns aufgefallen, dass aus den Debitor Stammdaten leider nur Einträge mit Kürzel exportiert werden und jene ohne nicht.“

Ich wundere mich. Ich importiere doch alle Daten:

xlBlattDebitoren.Range("A1").CurrentRegion.Copy Destination:=ThisWorkbook.Worksheets("Debitoren").Range("A1") ' -- kopiere die Daten

Ich ahne wo das Problem ist.

Wenn die ursprüngliche Liste gefiltert war, werden nur die sichtbaren Daten kopiert.

Also muss ich vorher prüfen, ob ein Filter eingeschaltet war. Falls ja, wird er entfernt:

    ' -- schalte mögliche gesetzten Filter aus
    If xlBlattDebitoren.FilterMode = True Then
        xlBlattDebitoren.Range("A1").AutoFilter
    End If

Klappt!

Kommentar hinterlassen ,

Bitte keine Beischlafanfragen mehr – mein Kalender ist voll!

Excel-Objekte, VBA-Befehle

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?

2 Kommentare ,

Seit wir das Passwort täglich ändern, auf ein Blatt schreiben und dann in einen Laufhaufen werfen, verbringen die Kinder viel mehr Zeit in der Natur als früher.

VBA-Befehle

Für einen SAP-Upload soll eine Textdatei erzeugt werden. Mehrere Zeilen mit unterschiedlichen Informationen sollen zusammengebaut werden und in eine Textdatei geschrieben werden. Jede Zeile wird mit einem Zeilenumbruch beendet. Ich verwende vbCrLf.

Da nun die letzte Zeile auch einen Zeilenumbruch hat, überlege ich ihn zu löschen. Ich überprüfe das letzte Zeichen – falls es nicht um vbCrLf handelt, wird es gelöscht. Jedoch:

    ' -- entferne den letzten Zeilenumbruch
    If Right(strExportString, 1) = vbCrLf Then
        strExportString = Left(strExportString, Len(strExportString) - 1)
    End If

funktioniert nicht! Klar doch: vbCrLf sind ZWEI Zeichen: Wagenrücklauf + Zeilenschaltung. Klar: ich muss überprüfen, ob die letzten BEIDEN Zeichen vbCrLf entsprechen. Und dann löschen.

Folgender Befehl funktioniert dann:

    ' -- entferne den letzten Zeilenumbruch
    If Right(strExportString, 2) = vbCrLf Then
        strExportString = Left(strExportString, Len(strExportString) - 2)
    End If
Kommentar hinterlassen ,

Wenn du dich zu Hause alleine fühlst, kauf dir eine Handcreme. Die zieht sofort bei dir ein.

VBA-Befehle, Zellformate

Bei manchen Fehlermeldungen hätte sich Microsoft wirklich mehr Mühe geben können. Beispielsweise bei folgendem:

Objektvariable oder With-Blockvariable nicht festgelegt.

Die Ursache des Fehlers: Die Zelle enthält keinen Kommentar. Wahrscheinlich ist mit „Objektvariable“ das Objekt „Comment“ gemeint.

Übrigens: dieser Fehler tritt auch auf, wenn Zellen verbunden sind. Die Zeile

Range("C4:D4").Comment.Delete

führt zur gleichen Fehlermeldung:

Die korrekte Anweisung muss lauten:


Range("C4").Comment.Delete
Kommentar hinterlassen , ,

Was ist die Mehrzahl von Bier? – Kasten.

VBA-Befehle

Irre!

Ich erstelle eine VBA-Funktion, bei der eine Zahlenreihe (normalerweise vier Ziffern) als Text gespeichert wird und mit Nullen davor aufgefüllt wird (meistens mit zwei Nullen). Der Funktion füge ich in VBA einen Kommentar hinzu, damit ich weiß, dass es sich um sechs Ziffern handelt:

' _ _ _ _ _ _

Die Funktion sieht folgendermaßen aus:

Ich teste und bin irritiert, dass das Ergebnis falsch ist.

Tatsächlich: Else ist grün! Der Unterstrich bewirkt einen Umbruch! Obwohl er im Kommentar steht!

Also verwende ich ein anderes Zeichen, um diese Leerzeichen zu versinnbildlichen:

' [] [] [] [] [] []

Klappt! Na, also!

Kommentar hinterlassen , , , ,

Auf der rechten Brust der Lidl-Kassiererin hängt ein Namensschild: „Renate“. Werde sie mal fragen, ob die linke keinen Namen hat.

VBA-Befehle

Ich programmiere wirklich gerne. Aber ich habe immer meine liebe Not mit ungenauen Arbeitsanweisungen.

Ich soll per Programmierung ein Tabellenblatt als PDF ablegen.

Kein Problem. Allerdings muss ich nachfragen, wie das PDF-Dokument heißen soll.

BatchRecord-Filename-Datum-Uhrzeit

lautet die Antwort.

Ich vermute, dass er nicht Filename, sondern Blattname meint.

Und: in welcher Form das Datum und die Uhrzeit geschrieben wird, erklärt er mir auch nicht. Nun, er wird es mir sicherlich noch sagen.

Ich probiere:

    strBlattname = "BatchRecord-" & ThisWorkbook.Name & Format(Date, "DD.MM.YYYY") & "-" & Format(Now, "HH:MM") & ".pdf"
    ' -- Export als PDF - in den gleichen Ordner, in dem sich die Vorlagendatei befindet
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        ThisWorkbook.Path & "\" & strBlattname, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

Eine Fehlermeldung ist die Folge:

Das Dokument wurde nicht gespeichert.

Ein bisschen suchen und ich werden fündig: Natürlich darf kein Doppelpunkt im Dateinamen auftauchen. Also raus damit (bei der Uhrzeit) und warten, wie denn der Dateiname korrekt aussehen soll:

strBlattname = "BatchRecord-" & ThisWorkbook.Name & Format(Date, "DD.MM.YYYY") & "-" & Format(Now, "HH.MM") & ".pdf"
Kommentar hinterlassen , ,

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

Excel-Objekte, VBA-Befehle

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!

Kommentar hinterlassen , , ,

Wie nennt man Hausarrest bei Erwachsenen? – „Ihr Paket kommt zwischen 8 und 18 Uhr.“

VBA-Befehle

ChatGPT polarisiert: es begeistert, verblüfft und irritiert. Es irritiert durch falsche Antworten. Ein Beispiel gefällig?

Ich frage CHatGPT:

Mit welchem VBA-Befehl kann ich den Autor der Arbeitsmappe ermitteln?

Und erhalte folgende korrekte Antwort:

Sie können den Autor einer Arbeitsmappe in VBA mit folgendem Befehl ermitteln:

Dim strAuthor As String
strAuthor = ThisWorkbook.BuiltinDocumentProperties(„Author“)

In diesem Beispiel wird die Eigenschaft „Author“ aus den „BuiltinDocumentProperties“ des aktuellen Workbooks abgerufen und in der Variablen „strAuthor“ gespeichert. Die „BuiltinDocumentProperties“ enthält alle standardmäßigen Dokumenteigenschaften wie Autor, Betreff, Schlagworte, etc.

Auch die Frage

Mit welchem VBA-Befehl kann ich ein Bild (beispielsweise C:\Test.jpg) in die rechte Kopfzeile einfügen?

wird korrekt beantwortet:

Sie können ein Bild in die rechte Kopfzeile einer Arbeitsmappe in VBA mit folgendem Befehl einfügen:

ActiveSheet.PageSetup.RightHeaderPicture.Filename = „C:\Test.jpg“

In diesem Beispiel wird die aktive Arbeitsblattseite ausgewählt und die Eigenschaft „RightHeaderPicture.Filename“ verwendet, um das Bild „C:\Test.jpg“ in die rechte Kopfzeile einzufügen. Wenn das Bild bereits eingefügt wurde, wird es durch das neue ersetzt.

Jedoch:

Meine Frage

Mit welchem VBA-Befehl kann ich den Mauszeiger ein- und wieder ausblenden?

wird nicht verstanden:

Sie können den Mauszeiger in VBA ein- und wieder ausblenden, indem Sie folgenden Befehl verwenden:

Application.Cursor = xlWait ‚ Mauszeiger ausblenden
‚ Code ausführen
Application.Cursor = xlDefault ‚ Mauszeiger wieder einblenden

In diesem Beispiel wird die Eigenschaft „Cursor“ des Application-Objekts auf den Wert „xlWait“ gesetzt, um den Mauszeiger auszublenden. Nach der Ausführung des Codes wird die Eigenschaft „Cursor“ auf den Wert „xlDefault“ zurückgesetzt, um den Mauszeiger wieder einzublenden.

Bemerkung: xlWait und xlDefault sind Konstanten in der Microsoft Excel-Object Library, die Teil von VBA sind.

Nein, liebes ChatGPT – das ist nicht richtig. Mit der Eigenschaft Cursor kann man den Mauszeiger verändern, aber nicht ein- und ausblenden.

Kommentar hinterlassen

Das Unwort des Jahres ist „Klimaterrorist“. An Putins Stelle würde ich die Wahl anfechten.

Fehlermeldungen, VBA-Befehle

Einfach nicht aufgepasst. Viele Daten werden verarbeitet. Klappt.

Das Add-In wird dem Kunden ausgeliefert: Fehler:

Die Ursache: in einer Zelle steht kein Text, sondern eine Fehlermeldung:

Und so liefert die Zeile

If ActiveCell.Value Like "*Zuversicht*" Then

einen Fehler. Natürlich: besser wäre:

If ActiveCell.Text Like "*Zuversicht*" Then
Kommentar hinterlassen

Ich kann dein Schmatzen nicht mehr hören! – Blöde: Lauter kann ich leider nicht.

VBA-Befehle

In Word soll per VBA ein Bild in eine Kopfzeile eingefügt werden. Blöde nur, dass es nach dem Einfügen „hinter den Text“ eingefügt werden soll. Ich habe lange gesucht, bis ich eine Lösung dafür gefunden habe:

Man muss das Bild in eine Form verwandeln (ConvertToShape). Dann funktioniert es:

Set wdMyInShape = wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes.AddPicture(Bildpfad, , , wdMyRange)
wdKopf.Range.InlineShapes(1).Width = 71
wdKopf.Range.InlineShapes(1).Height = 71
Set wdMyShape = wdMyInShape.ConvertToShape
wdMyShape.WrapFormat.Type = wdWrapBehind  ' 5
wdMyShape.Top = -17.25
Kommentar hinterlassen

Gerade ein Reh überfahren. Muss das erstmal verarbeiten. (Dieter, 57, Metzger)

VBA-Befehle, Word nervt auch

Boah, wie schrecklich: eine Tabelle in Word (in der Kopfzeile) soll bearbeitet werden: in einer Zelle sitzt ein Bild – das soll gelöscht werden.

Mit dem VBA-Befehl Cells kann man in der Form Cells(1,1) auf die einzelnen Zellen zugreifen. Jedoch: sind Zellen verbunden, dann liefert beispielsweise Cells(2,2) ein Fehler, weil diese Zelle nicht vorhanden ist!

Die Lösung ist nicht elegant, aber sie funktioniert: Man durchläuft einfach alle Zellen – mit On Error Resume Next wird die fehlerhafte Zelle übergangen …
On Error Resume Next

Set wdDatei = wdApp.Documents.Open(Datei)


For i = 1 To wdDatei.Sections.Count


    For j = 1 To wdKopf.Range.Tables.Count
        Set wdTabelle = wdKopf.Range.Tables(j)

        intZeilenAnzahl = wdTabelle.Rows.Count
        intSpaltenAnzahl = wdTabelle.Columns.Count

        For intZeile = 1 To intZeilenAnzahl
            For intSpalte = 1 To intSpaltenAnzahl
                If wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes.Count > 0 Then
                    Set wdMyRange = wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes(1).Range

                    wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes(1).Delete

                End If
            Next intSpalte
        Next intZeile

    Next j
Kommentar hinterlassen

Play gedrückt – Nichts. – Lauter gemacht – Nichts. – Noch lauter gemacht – Leises Rauschen. – Kopfhörer rausgezogen – Ganze Straße wach …

VBA-Befehle, Word nervt auch

Der Kunde will nicht nur, dass in einer Reihe von Excelvorlagen das Logo automatisiert, das heißt: mit VBA, ausgetauscht wird, sondern auch in Wordvorlagen.

Und damit beginnt wieder die Fummelei: liegt das Logo in der Kopfzeile oder in einer Tabelle in der Kopfzeile?

Eine Kopfzeile kann zur ersten Seite gehören, zum Objekt „gerade und ungerade Seiten unterschiedlich“ oder „gerade und ungerade Seiten nicht unterschiedlich“. Und diese wiederum an verschiedenen Abschnitten. Also sind eine Reihe von Schleifen nötig, um das alte Logo zu finden und zu löschen:

    For i = 1 To wdDatei.Sections.Count
        
        Set wdKopf = wdDatei.Sections(i).Headers(wdHeaderFooterPrimary)
        If wdKopf.Range.InlineShapes.Count > 0 Then
            Set wdMyRange = wdKopf.Range.InlineShapes(1).Range
            wdKopf.Range.InlineShapes(1).Delete
            wdKopf.Range.InlineShapes.AddPicture Bildpfad, , , wdMyRange ' Kopf ohne Tabelle
        End If
        
        For j = 1 To wdKopf.Range.Tables.Count
            Set wdTabelle = wdKopf.Range.Tables(j)
        
            intZeilenAnzahl = wdTabelle.Rows.Count
            intSpaltenAnzahl = wdTabelle.Columns.Count
            
            For intZeile = 1 To intZeilenAnzahl
                For intSpalte = 1 To intSpaltenAnzahl
                    If wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes.Count > 0 Then
                        
                        wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes(1).Delete
                        
                        Set wdMyInShape = wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes.AddPicture(Bildpfad, , , wdMyRange)

                    End If
                Next intSpalte
            Next intZeile
        
        Next j
Kommentar hinterlassen

Alexa, woran liegt es, dass ich immer mehr verblöde?

Drucken und Seitenlayout, VBA-Befehle

Der Kunde ist noch immer nicht zufrieden. Die Kopfzeile soll um zwei Zeilenschaltungen nach unten verschoben werden. Also nicht so:

sondern so:

Der Befehl ist schnell gefunden. Allerdings amüsiert mich, dass die Kopfzeile vbCrLf als zwei Zeilenschaltungen interpretiert. Nun ja – ist ja okay so:

        With Datei.Worksheets(j).PageSetup
            .LeftHeader = vbCrLf & .LeftHeader
Kommentar hinterlassen , ,

Gerade gelesen: Das Gehirn räumt im Schlaf auf. Von wegen! Wenn ich abends ins Bett gehe, ist die Bude morgens noch unordentlich.

VBA-Befehle

VBA-Schulung. Wie aufmerksam manchmal Teilnehmerinnen und Teilnehmer sind. Und erstaunlich, was mir manchmal entgangen ist.

Warum ist das „x“ zum Schließen des Meldungsfensters bei den Parametern vbAbortRetryIgnore und vbYesNo ausgegraut, also nicht aktivierbar,

während es bei den anderen vier Parametern aktiv ist? Ich weiß es nicht …

Kommentar hinterlassen , , , , ,

Du weißt, dass du alt wirst, wenn in einem Horrorfilm nervige Teenager umgebracht werden und du den Mörder verstehen kannst.

VBA-Befehle

Erstaunlich. In einem Bereich stehen Datumsangaben. In einer Zelle steht eine andere Datumsangabe. Sie soll „gesucht“ werden. Weder ZÄHLENWENN noch VERGLEICH haben ein Problem damit:

Auch der VBA-Befehl CountIf

datDatum = ThisWorkbook.Worksheets(1).Range("F19").Value
MsgBox Application.WorksheetFunction.CountIf(ThisWorkbook.Worksheets(1).Range("F1:F16"), datDatum)

arbeitet problemlos. Allerdings liefert MATCH einen Fehler:

Der Befehl

Application.WorksheetFunction.Match(datDatum, ThisWorkbook.Worksheets(1).Range("F1:F16"), 0)

klappt nicht.

Auch nicht in:

Application.WorksheetFunction.Match(ThisWorkbook.Worksheets(1).Range("F19").Value, _
   ThisWorkbook.Worksheets(1).Range("F1:F16"), 0)

Wahrscheinlich ist die Ursache folgende: Excel kennt nur die Datentypen Text und Zahl, während VBA verschiedene Datentypen für Zahlen kennt (Integer, Long, Double, …), aber auch für Datum (Date).

Also: Flux den Wert der Variablen datDatum in eine Long-Zahl konvertieren (CLng(datDatum) ) – und schon klappt es wieder:

Application.WorksheetFunction.Match(CLng(datDatum), ThisWorkbook.Worksheets(1).Range("F1:F16"), 0)
Kommentar hinterlassen , , , ,

Hat die Schlammpackung deine Frau schöner gemacht? – nur kurz, das Zeug geht ab!

VBA-Befehle

Klickt man in Excel auf Datei / Speichern unter, wählt einen Dateinamen einer anderen Datei, die bereits existiert, wird man gefragt, ob man die Datei ersetzen möchte. In VBA liefert der Befehl SaveAs die gleiche Meldung:

Jedoch nicht die Methode SaveCopyAs. Dort wird die vorhandene Datei (sofern nicht geöffnet) kommentarlos überschrieben.

Randbemerkung: Klickt man im Meldungsfenster auf die Buttons „Nein“ oder „Abbrechen“ ist eine Fehlermeldung die Folge …

Kommentar hinterlassen ,

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

Excel-Objekte, VBA-Befehle

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

Kommentar hinterlassen , ,

Der Praktikant hat sich geschnitten und möchte ein Pflaster. Ich zeige ihm einen Rambo-Film und reiche ihm den Lötkolben.

VBA-Befehle

Ich darf JavaScript unterrichten.

Ich erkläre, wie man eine Variable um den Wert 1 erhöht. Drei Möglichkeiten stellt diese Sprache zur Verfügung:

x = x + 1;
x +=1;
x++;

Wir machen eine Übung. Ein Teilnehmer sagt, dass sein Programm nicht richtig rechne. Seine Variable würde nicht erhöht werden. Ich schaue es mir an:

x = x++;

Richtig: zuerst geschieht die Übergabe, dann wird der Wert erhöht. Das heißt: die Variable verändert ihren Wert nicht. *ggrrrr*

Kommentar hinterlassen ,

Mein Mann positioniert den Rasensprenger und ruft: „Mach bitte das Wasser an; aber erst, wenn ich weg bin!“ — Ihr hättet es auch getan …

VBA-Befehle

Hallo Rene,

nun, seit langem, wieder mal was Fachliches.

Ganz was Einfaches:

In meinen Programmpaket gibt es unheimlich viele Variable. Und wenn was Neues dazu kommt, kommen neue Variable hinzu.

Ich habe bisher noch keine einfache und schnelle Lösung – wie findet man im Programm „nicht benutzte Variable“ ?

So „Tabula rasa“ ist gesucht. Einfach all diese Variablen ungesehen ganz schnell löschen….

Liebe Grüße Wolfgang

Hallo Wolfgang,

ja ich weiß – aber da hat der Uralt-Popel-Editor von VBA nichts zu bieten.

In Visual Studio werden nicht verwendete Variablen angezeigt

Ich lösche die Variablen in VBA und „kompiliere“. Oder manchmal suche ich auch im Code, ob sie noch verwendet wird.

Ist mühsam, ich weiß … Ich entschuldige mich nicht dafür *lach*

Liebe Grüße

Rene

2 Kommentare , ,

Während Bruce Lee täglich am Kämpfen war, hat sich sein Bruder Müs ein Frühstücksimperium aufgebaut.

VBA-Befehle, Word nervt auch

Hallo Herr Martin,

mittlerweile sehe ich schon die Zielgerade J.

Die Probleme mit dem Titel und dem Vorwort habe ich jetzt erledigt.

Ich hänge aber immer noch am Druckauftrag.

Anbei mal die aktuellen Codes:

[… es folgen einige Dutzend Codezeilen]

Folgende Fehler treten aber bei mir auf: Nachdem ich diese ProtectBefehle eingebaut habe kommt folgende Fehlermeldung:

Hallo Herr R.,

mir fehlt noch eine Info:

* öffnen Sie mal bitte Ihr VBA-Projekt (also die Word-Datei

* Wechseln Sie nach VBA

* Heben Sie den Schutz auf

* Klicken Sie auf den Menüpunkt Debuggen / Kompilieren In welcher Zeile entsteht der Fehler? Könnten Sie mir bitte diese Zeile oder auch die Zeilen „außenrum“ schicken.

Hallo Herr Martin,

Sie sind aber schnell… Meinen Sie das?

*lach*

DAS ist Falsch.

Es muss heißen:

ActiveDocument.Protect NoReset:=True

Zwischen Protect und NoReset muss ein Leerzeichen statt des Punktes stehen

Liebe Grüße

Rene Martin

PS: und dann noch einmal Debuggen, bitte!

####

Aber immer noch J

stimmt, Herr R., da habe ich doch mal einen Artikel darüber geschrieben.

Die Methode Protect verlangt unbedingt den Parameter Type, obwohl IntelliSense dies nicht anzeigt:

ActiveDocument.Protect NoReset:=True, Type:=wdAllowOnlyFormFields

Kommentar hinterlassen , , ,

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

Excel-Objekte, VBA-Befehle

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
Kommentar hinterlassen , ,

Ich ärgere mich ja nicht immer über Excel – manchmal schlafe ich auch.

VBA-Befehle

Die neuen Sicherheitseinstellungen kennen Sie? Auf meinem Rechner erhalte ich die Warnmeldung:

Es wurde im Internet schon viel über Sinn oder Unsinn dieser Sicherheitshürde diskutiert.

Sie entfernen es über die Eigenschaften der Datei:

Die Erläuterung:

Makros aus dem Internet werden in Office standardmäßig blockiert. – Deploy Office | Microsoft Docs

Kommentar hinterlassen , , ,

What’s your address? – 151.194.25.39 – No – your local address? – 127.0.0.1 – I mean your physical address? – 19:08:AF:51:11:08

Datenaustausch, Dateneingabe, VBA-Befehle, Zahlenformate

Ich bin gerade völlig perplex. Ich programmiere für eine Schweizer Firma ein Excel-Tool. Ich lasse alle Tabellenblätter, die mit Monatsnamen beschriftet sind, ausblenden. Nur das Blatt MRZ bleibt stehen. In der Schweiz bleibt es stehen.

Ich frage Tanja Kuhn. Sie hat die Schweizer Oberfläche von Excel:

Und ja: sie bestätigt es: seit einigen Versionen lautet die Abkürzung des dritten Monats in der Schweiz nicht Mrz, wie in der ISO 8601 (EN 28601:1992) festgelegt, sondern Mär. Das erkennt man schnell, wenn man den Text „Jan“ einträgt unter herunterzieht:

Und eben deshalb liefert der VBA-Befehl

Format(DateSerial(2022, 3, 1), "MMM")

in der Schweiz etwas anderes als in Deutschland (oder Österreich).

Perfide!

Kommentar hinterlassen , ,

Wenn ich noch mehr Kaffee trinke, heißt meine Blutgruppe bald Arabica.

VBA-Befehle

Hallo Rene,

komme tatsächlich um VBA nicht herum und benötige Deine Unterstützung.

Ich habe mir mit Hilfe YouTube ein kleines Makro gebastelt, welches nur das Tabellenblatt „Ausdruck“ zum Ausdrucken zulässt.

Wie muss ich vorgehen, wenn ich nun noch ein zweites Tabellenblatt zulassen möchte?

Da hast Du doch sicher eine Idee, oder?

Sub Workbook_BeforePrint(Cancel As Boolean)

Dim WsName As String

WsName = „Ausdruck“

For Each xWs In Application.ActiveWorkbook.Windows(1).SelectedSheets

    If xWs.Name <> WsName Then

    MsgBox („Kein Ausdruck dieses Tabellenblattes möglich!“)

        Cancel = True

    End If

Next

End Sub

Gruß

Christian

###

Hallo Christian,

ich glaube du denkst viel zu kompliziert – ich denke das folgende Makro macht, was du möchtest:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    If ActiveSheet.Name <> „Ausdruck“ And ActiveSheet.Name <> „Ausdruck2“ Then

        MsgBox „Kein Ausdruck des Tabellenblattes “ & ActiveSheet.Name & “ möglich!“

        Cancel = True

        End

    End If

End Sub

Kommentar hinterlassen

Kontostand angeschaut und gedacht: „arm aber sexy“. In den Spiegel geschaut und gedacht: „Mist, nur arm!“

VBA-Befehle

Ein Hi in den Süden,

     hallo Rene,

heute ist Samstag und das halbe Wochenende schon einmal rum. Hm.

Der sechste Arbeitstag läuft bei mir noch bis 0 Uhr, morgen auch, aber eingeschränkt.

Was wurde aus meinem aktuellen EXCEL-VBA-Projekt?

(1) Deinen VBA zum Setzen des Filters habe ich versucht zu erweitern durch Variablen.
     Klappt nicht so dolle.

    s_DatenRange = "$A$1:$I$" & l_ZeileLetzte
    s_DatumStart = s_Start_Jahr & ", " & s_Start_Monat & ", " & s_Start_Tag
    s_DatumEnde = s_Ende_Jahr & ", " & s_Ende_Monat & ", " & s_Ende_Tag

    MsgBox "Jetzt kommt das Problem: DateSerial - Argument ist nicht optional"

' Versuch 1
    ActiveSheet.Range(" & s_DatenRange & ").AutoFilter Field:=6, Criteria1:= _
    ">=" & CDbl(DateSerial(" & s_DatumStart & ")), Operator:=xlAnd, Criteria2:="<=" & CDbl(DateSerial(" & s_DatumEnde  & "))

' Versuch 2

Dim s_teil_1, s_teil_2 As String

s_teil_1 = DateSerial(" & s_DatumStart & ")
s_teil_2 = DateSerial(" & s_DatumEnde & ")
s_teil_1 = DateSerial(s_DatumStart)
s_teil_2 = DateSerial(s_DatumEnde)

    ActiveSheet.Range(" & s_DatenRange & ").AutoFilter Field:=6, Criteria1:= _
    ">=" & CDbl(" & s_teil_1 & "), Operator:=xlAnd, Criteria2:="<=" & CDbl(" & s_teil_2 & ")

Hallo Jürgen,

Zu DateSerial: Kennst du dich Excel-Funktion DATUM nicht? Sie wandelt eine JahresZAHL, eine MonatsZAHL und eine TagesZAHL in ein Datum um. Und eben das macht DateSerial in VBA.

Kommentar hinterlassen ,

Forscher haben Mikroplastik im menschlichen Darm gefunden. Die nächste Evolutionsstufe heißt dann „Lego“.

Makrorekorder, VBA-Befehle

Hallo Rene,

deine Mails mit positiven Informationen und Anhang erreichten mich.

Themenwechsel 3 von 3:

Falls du an weiteren Fragen rund um EXCEL-VBA Interesse hast, informiere mich einfach. Dazu habe ich dir zwei Dateien mitgesendet.
Die Lösung der Probleme ist kein MUSS.

Die Thematiken:

(1) ein Filterproblem ist bestimmt nicht so schwierig zu lösen. Eine Liste:

Der Filter:

Nach Filtersetzen werden keine Daten angezeigt.

Der Code:

    ActiveSheet.Range("$A$3:$F$12").AutoFilter Field:=4, Criteria1:= _
        ">=01.01.2000", Operator:=xlAnd, Criteria2:="<=31.03.2022"

Hallo Jürgen,

der Filter verlangt einen Datumswert, der in eine Zahl konvertiert wurde:

    ActiveSheet.Range("$A$3:$F$12").AutoFilter _
        Field:=4, 
        Criteria1:= ">=" & CDbl(DateSerial(2000, 1, 1)), _
        Operator:=xlAnd, _
        Criteria2:="<=" & CDbl(DateSerial(2022, 3, 31))

Dann klappt es:


Kommentar hinterlassen ,

Jedesmal, wenn ich morgens die Augen öffne, denkt der Teufel: „au weia, es ist wach!“

VBA-Befehle

Perfide. Ich habe eine Excelmappe auf der Festplatte – das VBA-Projekt ist mit Kennwort geschützt. Ich kenne das Kennwort. Dennoch: es lässt sich nicht öffnen:

Ich brauche eine ganze Weile, bis es mir dämmert: ich habe die Sprache der Tastatur geändert:

Und richtig: dort sind Y und Z vertauscht und auch die Sonderzeichen sitzen an anderen Positionen …

Kommentar hinterlassen , , ,

Meine Laune ist gerade im Keller. Ich hoffe, sie bringt Wein mit.

VBA-Befehle

Hallo Rene

Es ist schon eine Weile her, dass wir Kontakt hatten und ich hoffe es geht Dir in Anbetracht der Weltlage einigermassen gut?

Leider kämpfe ich wieder mit einem Problem, wobei ich mich ehrlich gesagt mehr darüber ärgere, dass ich das Problem nicht finde, als über den Fehler selbst.
Es geht wieder um das VBA Script, welches die Excel Daten auswertet und je nach Zuweisung im Layer meine Shapes sichtbar macht, oder eben nicht.

Das Script macht eigentlich was es soll, doch nach dem Durchlaufen der letzten Zeile, kommt eben der genannte Fehler.
Vielleicht hast Du etwas Zeit und bist willig mir zu helfen? Herzlichen Dank im Voraus für eine Reaktion.

Hallo H.,,

schau mal in deine Daten: der letzte Datensatz ist leer:

Du musst die überprüfen/abfangen, beispielsweise mit:


If IsNull(data(idColumn)) …

Liebe Grüße

Rene

Kommentar hinterlassen , , ,

Voll nett. Eine japanische Familie hat mir heute im Park ihre Spiegelreflexkamera geschenkt. Den Rest habe ich nicht verstanden.

VBA-Befehle

Man schickt mir eine Fehlermeldungmeldung.

Das Arbeitsblatt oder die Arbeitsblätter, die in die Zielarbeitsmappe kopiert oder verschoben werden, besitzen möglicherweise angefügte Makros. Makrocode stellt ein potenzielles Sicherheitsrisiko dar. Sie sollten den Vorgang nur fortsetzen, wenn Sie sicher sind, dass der Makrocode von einer vertrauenswürdigen Quelle stammt. Möchten Sie den Vorgang fortsetzen?

Ich habe keine Ahnung, wo man das in Excel einstellen kann. Ich vermute, diese Option wurde in den Gruppenrichtlinien von der IT abgefangen. Oder? Kennt jemand diese Meldung?

2 Kommentare

Das eindeutigste Zeichen dafür, dass es intelligentes Leben im Weltraum gibt ist die Tatsache, dass uns noch niemand kontaktieren wollte.

VBA-Befehle

Solche Meldungen erfreuen immer wieder Sinn und Gemüt:

Das Projekt kann nicht erstellt werden, weil das „Excel Visual Studio-Entwurfszeitadapter-Add-In“ nicht ordnungsgemäß ausgeführt wird. Das Add-In wurde in Excel möglicherweise deaktiviert oder für inaktiv erklärt oder in den Einstellungen im Sicherheitscenter sind alle Add-Ins deaktiviert. Überprüfen Sie den Add-In-Status in den Excel-Optionen. Wenn das Add-In aktiv und aktiviert ist, reparieren Sie Visual Studio Tools for Office, oder führen Sie eine Neuinstallation aus,

Lange, sehr lange sitze ich davor und versuche den Sinn zu begreifen …

Kommentar hinterlassen

Als ich jung war, waren Singles aus Vinyl und nicht aus Verzweiflung.

VBA-Befehle

Excel-VBA-Schulung. Wir programmieren eine eigene Funktion. Als Übung gebe ich auf eine Funktion zu schreiben, welche die Hypotenuse in einem rechtwinkligen Dreieck berechnet.

Also:

Ich tippe die Lösung, die ich mit einem „Quick & Dirty“ kommentiere:

Ich verlasse die Codezeile:

Ein Fehler ist die Folge. Darf man nicht die Funktion Sqr (Wurzel) aufrufen und im Aufruf rechnen?

Okay – dann „sauberer“ in zwei Zeilen:

Schon wieder ist die Zeile
    c = a^2 + b^2

in der Funktion

Function Hypotenuse(a As Double, b As Double) As Double
    Dim c As Double
    c = a^2 + b^2
    Hypotenuse = Sqr(c)
End Function

falsch. Es dämmert mir. Richtig: das Caret-Zeichen (^) – der Zirkumflex – darf nicht direkt hinter die Variable geschrieben werden, da a^ zum Erstellen von Long Long-Datentypen in einer 64-Bit-Umgebung verwendet wird. Man muss ein Leerzeichen zwischen Variable und dem „Dach“ ein Leerzeichen schreiben:

Also:

Function Hypotenuse(a As Double, b As Double) As Double
    Dim c As Double
    c = a ^ 2 + b ^ 2
    Hypotenuse = Sqr(c)
End Function

oder in der Kurzform:

Function Hypotenuse(a As Double, b As Double) As Double
    Hypotenuse = Sqr(a ^ 2 + b ^ 2)
End Function

Das funktioniert!

Kommentar hinterlassen , , , ,

Gestern habe ich alkoholfreies Bier gekauft und mit Karte bezahlt. Heute ruft mich die Bank an und fragt, ob meine Karte gestohlen wurde …

Fehlermeldungen, VBA-Befehle

Excel-VBA-Schulung. Wir üben das Programmieren von eigenen Funktionen (also function), die in Excel verwendet werden sollen. Ich zeige einen Fehler:

Und erkläre, dass man die Ursache gut finden kann, indem man einen Haltepunkt in Excel setzt:

Dann muss man die Funktion editieren (doppelklick oder [F2]) und sie wird erneut aufgerufen und berechnet:

Allerdings: nichts passiert. Ich brauche eine Weile, bis ich verstehe. Die Parameter sind vom Typ Double deklariert. Einer der Eingabewerte ist jedoch keine Zahl:

Und so wird die Funktion schon direkt nach dem Aufruf abgebrochen und liefert die Fehlermeldung #WERT, ohne dass die Zeile mit dem Haltepunkt erreicht wird. Also flugs den Wert der Zelle in eine Zahl ändern und schon wird der Haltepunkt erreicht.

Kommentar hinterlassen , , ,

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

Excel-Objekte, VBA-Befehle

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?

Kommentar hinterlassen ,

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

Excel-Objekte, VBA-Befehle

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!

Kommentar hinterlassen , , ,

Als Kind hatte ich kein Smartphone oder Tablett. Ich habe die Cornflakes-Packung beim Frühstück gelesen.

VBA-Befehle

Hallo Herr Martin

Ich hoffe Sie sind gut ins neue Jahr gekommen.

Wir hatten schon einmal kontakt aufgenommen.

Folgende  Meldungen bekomme:

Nach dem Debuggen bekomme ich diese Meldung:


Der Urheber behauptet dass das korrekt ist und keinen Fehler darstellt.
 
Ich würde mich herzlich freuen, wenn wir  kurz miteinander sprechen können.
 
Schon jetzt herzlichen Dank.
 
Mit freundlichen Grüßen

####

Hallo Herr P.,

welchen Wert hat denn „MenueName“? Wenn Sie mit der Maus über diesen Variablennamen beim Debuggen fahren – was wird denn angezeigt? Und was bei „SubMenueName“?

Umgekehrt: Arbeiten Sie noch mit Menüs? Die sind doch seit Excel 2007 verschwunden? Ich verwende seit vielen Jahres das Ribbon und füge dort Symbole ein.

Liebe Grüße Rene Martin

####

Herr Martin,

erstmal Danke für Ihre Rückmeldung,

was Sie schreiben sind für mich Böhmische Dörfer.

Sorry, dass sind zu hohe Ansprüche für mich.

Ich habe Ihnen die VBA kopiert.

‚~~Begin~~#################################################################################

Sub Menue_Erstellen_ZV()

    Dim MB As Object, MWMMenue As Object, Befehl As Object

    Set MB = CommandBars.ActiveMenuBar

    If (Menüpunkt_vorhanden(MenueName)) Then

        If (Menüeintrag_vorhanden(MenueName, SubMenueName)) Then

            Menueeintrag_loeschen_ZV

        End If

        Set MWMMenue = MB.Controls(MenueName)

        Set Befehl = MWMMenue.Controls.Add(Type:=msoControlButton, ID:=1)

        With Befehl

            .Caption = SubMenueName

            .OnAction = „ZV_ZellenVerbinden“

        End With

    Else

        Set MWMMenue = MB.Controls.Add(Type:=msoControlPopup, Temporary:=True)

        MWMMenue.Caption = MenueName

        Set Befehl = MWMMenue.Controls.Add(Type:=msoControlButton, ID:=1)

        With Befehl

            .Caption = SubMenueName

            .OnAction = „ZV_ZellenVerbinden“

        End With

    End If

End Sub

‚~~~END~~~#################################################################################

‚~~Begin~~#################################################################################

Sub Menue_Loeschen_ZV()

    On Error Resume Next

    CommandBars.ActiveMenuBar.Controls(MenueName).Delete

End Sub

‚~~~END~~~#################################################################################

‚~~Begin~~#################################################################################

Function Menüpunkt_vorhanden(Bezeichnung) As Boolean

    Menüpunkt_vorhanden = False

    Dim MNU As CommandBarControl

    For Each MNU In Application.CommandBars _

      („Worksheet Menu Bar“).Controls

        If UCase(MNU.Caption) = UCase(Bezeichnung) Then

            Menüpunkt_vorhanden = True

            Exit Function

        End If

    Next MNU

End Function

‚~~~END~~~#################################################################################

‚~~Begin~~#################################################################################

Function Menüeintrag_vorhanden(SubMenü, Bezeichnung) As Boolean

    Menüeintrag_vorhanden = False

    Dim MNU As CommandBarControl

    For Each MNU In Application.CommandBars _

      („Worksheet Menu Bar“).Controls(SubMenü).Controls

        If UCase(MNU.Caption) = UCase(Bezeichnung) Then

            Menüeintrag_vorhanden = True

            Exit Function

        End If

    Next MNU

End Function

‚~~~END~~~#################################################################################

‚~~Begin~~#################################################################################

‚Löscht den Eintrag SubMenueName im Menü „MWM“

Sub Menueeintrag_loeschen_ZV()

     CommandBars(„Worksheet Menu Bar“). _

       Controls(MenueName).Controls(SubMenueName).Delete

End Sub

‚~~~END~~~#################################################################################

Schon jetzt herzlichen Dank für Ihre Rückmeldung.

####

Hallo Herr P.,

danke für den Code. Mir fehlt allerdings immer noch ein Befehl: ein anderes Makro ruft dieses Makros auf und löscht das Symbol in der Symbolleiste. Ich weiß nicht welches, weil ich diese Variable nicht „sehe“.

Im Makro

Sub Menue_Loeschen_ZV()

lautet die erste Zeile

On Error Resume Next

also auf Deutsch: sollte ich – das Makro – das Symbol nicht löschen können (Controls(MenueName).Delete) – beispielsweise weil es schon gelöscht wurde … na: dann mach halt gar nichts! Auch keine Fehlermeldung! Ist völlig okay so!

Jedoch im Makro

Sub Menueeintrag_loeschen_ZV()

fehlt diese Zeile.

Tipp: Fügen sie nach der Sub-Zeile einfach die On-Error-Zeile ein (wurde wahrscheinlich vergessen), also so:

Sub Menueeintrag_loeschen_ZV()

On Error Resume Next

CommandBars(„Worksheet Menu Bar“). _

       Controls(MenueName).Controls(SubMenueName).Delete

Probieren Sie es mal.

Klappt das?

Liebe Grüße

Rene Martin

Kommentar hinterlassen , ,

Ich überlege mit dem Trinken aufzuhören, aber ich schwanke noch.

VBA-Befehle

Hallo Rene,

Dir zum Jahresanfang alles Gute – bleib gesund und für die Arbeit (notwendiges Kleingeld) viel Erfolg!!!


…und ich stelle mich im Neuen Jahr ganz schön doof an.
So würde ich gern Deine Hilfe annehmen.

In meinen Programmen will ich etwas Neues probieren und es gelingt mir nicht.

Bisher funktioniert alles prima […] Alles ist gut!

Nun:
Mein Wunsch wäre eine etwas komplexere Auswertung, die ich vorher erstellt habe und der in einer Datei Auswertung.xlsx liegt.
Diese Arbeitsmappe kann ich nun per Programm über
      Application.Workbooks.Open „C:\Pfad\Auswertung.xlsx“
öffnen – das funktioniert.

Aber jetzt:
Jetzt möchte ich diese Mappe für die weitere Benutzung zuweisen (ich will diese Mappe ja per Programm bearbeiten)
       Set xlsDatei(i) = ??????
und da geht es nicht weiter…

Es ist bestimmt nur eine Kleinigkeit, aber ich habe mich irgendwie festgebissen…

Ich danke Dir schon im voraus für Deine Hilfe!

Liebe Grüße
Wolfgang

####

Äh ….

    Set xlsDatei(i) = Application.Workbooks.Open(„C:\Pfad\Auswertung.xlsx“)

Moin Wolfgang,

DAS erschüttert mich! Das weißt du doch selbst: Methoden haben in VBA zwei Schreibweisen: Leerzeichen, wenn etwas ausgeführt wird (zöffne die Datei) und Klammer, wenn etwas an eine Variable übergeben wird (… und speichere es als xlsDatei).

Liebe Grüße und einen guten Jahresanfang!

Rene


Kommentar hinterlassen

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

Excel-Objekte, VBA-Befehle, Word nervt auch

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!

Kommentar hinterlassen , , ,

Schlägerei im Altenheim: Wenn sich zwei streiten, fliegen die Dritten

VBA-Befehle

Hallo Rene, hier Andre :-)!,

Ich hatte eine Frage zu Excel.

Könntest du dir das mal durchlesen, eventuell kennst du eine Lösung.

Ich öffne aus einer Exeltabelle heraus eine weitere Exceldatei. Dies ist ein Bestellformular.
Dort trage ich dann bestimmte Werte ein in die entsprechenden Felder.
Ich öffne diue Datei folgendermassen:

    Workbooks.Open FileName:=AblagepfadBanfVorlage + DateinameBanfVorlage

Jetzt ist es aber so das beim Oeffnen der Datei eine MsgBox aufgeblendet wird die sagt man muss einen Vorgang auswählen.
Das ist auch sinnvoll wenn man die BANF(Bestellanforderung) händisch ausfüllt.
Aber nicht bei einem Automatismus.
Ausgelöst wird die MsgBox durch das Event „Worksheet_Change“

Ich frage mich ob es eine Mögliichkeit gibt, die Tabelle so zu öffnen das alle Makros deaktiviert sind und bleiben.
Ich hatte das was gelesen das es so gehen sollte:

Application.EnableEvents = False

Workbooks.Open FileName:=AblagepfadBanfVorlage + DateinameBanfVorlage

Application.EnableEvents = True

Aber das funktioniert nicht.
hast Du da vielleicht eine schnelle Idee.

####

Hallo André,

ich würde die MELDUNGEN (nicht die Makros!) unterdrücken mit:

Application.DisplayAlerts = False

und danach wieder die Warnmeldungen einschalten (= True)

Liebe Grüße

René

Kommentar hinterlassen

Ich dachte, mir hätte jemand beim Joggen nachgepfiffen. War aber nur meine Lunge.

VBA-Befehle

VBA-Schulung. Eine Teilnehmerin sieht ihren Fehler nicht und bittet mich zu helfen. Ich schaue mir den Code an:

Böse Leerzeichen! Der Fehler ist schnell gefunden: Am Ende des Tabellenblattnamens hatte sie aus Versehen ein Leerzeichen geschrieben, also statt Set xlTabelle = xlDatei.Sheets(„Almodovar“)

Set xlTabelle = xlDatei.Sheets("Almodovar ")

Kommentar hinterlassen

Ich weiß gar nicht, was du beruflich machst. – Ich auch nicht – ich gehe da einfach hin.

VBA-Befehle

Guten Abend René,
ich grüße zur späten Abendstunde und erlaube mir um diese Uhrzeit noch eine Frage mitzusenden:
Ich möchte lediglich in Erfahrung bringen, welche Möglichkeiten es (nicht) gibt, bzgl. des EXCEL-Solvers:
(1) Funktionieren sollte …
der Einsatz „Solver“ über Ribbon „Daten“/“Solver“, bei geschütztem Tabellenblatt, wenn zusätzlich VBA genutzt wird und beim Makrolauf das Passwort (Tabellenblatt) am Anfang aufgehoben und gegen Makroende wieder gesetzt wird das kann man dem www entnehmen
(2) Nicht funktionstüchtig ist:
Aktivierter Tabellenblattschutz, keine VBA-Nutzung. „Solver“-Einsatz über Ribbon „Daten“/“Solver“
meine heutige Erfahrung
Mir geht es vor allem um Position (2). Die Argumentation beim Kunden:
Da (2) nicht funktionstüchtig ist, muss ich (1) realisieren.
Ein ok zu (1) und (2) wäre nett, sicherlich kennst du die Thematiken von deinen Kunden.
Ein Dankeschön für dein ok & Gruß
Jürgen

Moin, Jürgen,
was macht der Solver? Er liest Werte aus einem Tabellenblatt, rechnet und schreibt Werte zurück. Beziehungsweise schreibt Zwischenergebnisse zurück und prüft, ob sich die Ergebnisse dem gewünschten Ziel annähern.
Das kann auf einem geschützten Blatt nicht funktionieren.
Ich mache es (bei anderen) VBA-Programmen immer so, dass ich den Blattschutz aufheben, Werte eintrage und dann den Schutz wieder setze.
Übrigens: auch ohne zu schreiben – es gibt einige VBA-Befehle, die man auf einem geschützten Blatt nicht ausführen kann, beispielsweise CurrentRegion!?!
Liebe Grüße
Rene

Kommentar hinterlassen ,

Als ich klein war haben mir meine Eltern verboten an den Schrank mit den Putzmitteln zu gehen. Es wirkt bis heute!

VBA-Befehle

Hallo René

anbei die beiden Files (Visio und Excel Tabelle)
Bei der Tabelle handelt es sich um eine Copy aus einem sehr umfangreichen Excel Workbook, welches unter anderem eben die Tabelle produziert, welche die enthaltenen Services (Visio Shapes) steuert. Falls ein Kunden einen Service in einer Ausprägung bekommt, soll das entsprechend Shape auf dem Visio erscheinen.

Ein komischen Phänomen oder Verhalten ist mir bei der Fehlersuche aufgefallen. Wenn ich im Excel File in der Spalte Layer die Formel mit fixen Werten ersetze, dann läuft es irgendwie deutlich besser. Kann es sein, dass Visio im trotz dem vorher benötigten Datenabgleich im Hintergrund noch irgendetwas mit den Daten macht?
Ich habe keine andere Erklärung, wieso es ohne Formeln im Excel in Visio besser funktionieren sollte ???

Wenn ich Deine Bücher zu VBA und Visio Programmierung durchgearbeitet habe, bin ich sicher in der Lage den Visio Update direkt von Excel aus zu steuern

Freundliche Grüsse
Herby

Hallo Herby,

wenn du bei bestimmten Fällen die Variable shapeOnLayer auf True setzt:

                If LCase(shp.Layer(iLyr).Name) = LCase(lyrName) Then
                    shapeOnLayer = True
                Else

und dies später abfragst:

            If shapeOnLayer = False Then
                lyr.Add shp, 0
            End If

muss du am Anfang der Schleife die Variable wieder „zurück“ auf False setzen:

    For iRow = 0 To UBound(rowIDs)
        data = drs.GetRowData(rowIDs(iRow))
        shapeId = data(idColumn)
        lyrName = data(lyrColumn)
        shapeOnLayer = False

Guten Abend René

Zuerst vielen Dank

Meine Programmier Skills sind leider zu bescheiden um richtig folgen zu können. 

Ich werde aber versuchen dies im Script anzupassen und hoffe, dass es dann funzt. Auf jeden Fall ist es super, dass Du die Probleme gefunden hast.

Lieber Gruss und einen schönen Abend (soweit man das mit unserem tollen Somner überhaupt noch wünschen kann) Herby

Kommentar hinterlassen ,

Ich bin wie Batman. Ich muss oft nachts raus.

Excel-Objekte, sortieren, VBA-Befehle

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

Kommentar hinterlassen

Kalorien werden beim Überbacken mit Käse – wie jeder weiß – abgetötet.

VBA-Befehle

Hallo Rene,

einen Tag Freizeit vom Bauunternehmen – und ich habe mich mal Deinem Hinweis zum „Solver“ gewidmet.
Prima Sache.

Nun könnte ich das vielleicht auch gebrauchen und habe mal nachgeschaut.
Leider gelang es mir nicht den Solver ins VBA unterzubringen….

Hast Du eine Idee, was ich vergessen haben könnte?

Mein Office ist 2007, mein Visio 2010.
Der Solver läuft im Excel.

Anbei die Meldungen beim Versuch, den Verweis einzustellen.

Was sagt der Profi??

Hallo Wolfgang,

du musst den Solver zu den AddIns hinzufügen und dann über Extras / Verweise einbinden. Dann stehen dir die Befehle zur Verfügung:

Schau mal auf:

https://peltiertech.com/Excel/SolverVBA.html

Liebe Grüße

Rene

Hallo Rene,

danke noch einmal für Deinen Hinweis!,
allerdings gelingt mit solch ein Bild  (siehe Deine EMail  „Verweise VBAProject“ ) nicht.
Da kommt kein „Solver“ vor. Und das Laden der „SOLVER32.DLL funktioniert zwar, aber Visio kann damit nichts anfangen (wie in meiner ersten Mail) schon geschrieben.

Nun habe ich ein 64-Bit -System. Leider ist aus der kargen Fehlermeldung die Ursache, dass Visio die SOLVER32.DLL nicht mag, nicht zu erklären.
Und eine SOLVER64.DLL gibt es wohl nicht (soweit ich meine Suche auch ausdehnte).

Nun werde ich mich wohl mit der nächsten Microsoft-Unzulänglichkeit auch zufrieden geben müssen.
So ein großer „Laden“ und soviel Mangel (es gibt ja auch so viel Versionen…)

Vergangenheit: Hätte ich im Steuerungsbereich meinen Kunden soviel „Nichtfunktion“ zugemutet – ich glaube, da wäre ich arm dran…


Liebe Grüße
(und möglichst stressfreie Spaziergänge…)

Wolfgang

Hallo Wolfgang,

ich habe in Excel den Solver eingebunden. Mit dem Makrorekorder aufgezeichnet. Dann kannte Excel ihn (in den Verweisen)

Von Visio aus habe ich das (noch) ausprobiert …

Liebe Grüße

Rene

Hallo Wolfgang,

ich habe noch einmal nachgeschaut: du verwendest eine andere Visio-Version als Excel. Kann das die Ursache sein?

Zeichne das Teilchen doch einmal in Excel mit dem Makrorekorder auf. Läuft?

Dann wandle die Befehle in Late Binding (Object) um. Läuft?

Und dann von Visio!

Was passiert?

Liebe Grüße Rene

Kommentar hinterlassen

Ich schmecke gar nichts – oh mein Gott – ich habe Corona! – Immer dieselbst Show, wenn es Tofu gibt.

VBA-Befehle

Guten Tag René

Der Kunde hat folgende Fehlermeldung 

Image.jpeg

Hast du eine Vermutung was das sein könnte?

Bei mir ist es gelaufen. 

LG Tanja

##########

ja, Tanja,

der Kunde hat ein sehr „altes“ Excel (Excel 2013? Excel 2016?). Ich verwende die Funktion TEXTKETTE (in VBA: CONCAT). Genauer: mit dem Befehl

    Do Until Len(Application.WorksheetFunction.Concat(xlBlatt.Range(xlTabelle.Range.Cells(1).Offset(lngAnzahl + 1, 0), xlTabelle.Range.Cells(1).Offset(lngAnzahl + 1, 8)))) = 0
        lngAnzahl = lngAnzahl + 1
    Loop ' -- wie viele Zeilen sind gefüllt (wird in der Variable lngAnzahl gespeichert)

überprüfe ich, in wie vielen Zeilen der Liste etwas steht, beziehungsweise, ich suche die Zeile, in der die ersten neun Spalten leer sind oder ob die Formeln, die darin stehen, „“ ergeben.

Ich habe es geändert. Könntest du ihnen bitte diese Version schicken

Liebe Grüße

Rene

Kommentar hinterlassen , ,

Sonnenaufgänge sind ja echt schön, liegen aber zeitlich total ungünstig …

VBA-Befehle

Hallo Herr Martin,

habe noch einen Fehler gefunden.

„Kompilierungsfehler im ausgeblendeten Modul. Dieser Fehler tritt häufig auf, wenn der Code nicht mehr mit der Version, Plattform oder Architektur dieser Anwendung kompatibel ist. Klicken Sie auf „Hilfe“, um Informationen dazu zu erhalten, wie Sie diesen Fehler beheben können.“

Der Fehler entsteht, wenn ich im Code im Modul basKonstanten die Versionsnummer ändern will.

Nach Änderung und Neustart der Tabelle kommt o.g. Fehler. Danach kann kein Dashboard mehr ohne Fehler aufgerufen werden!

Wenn das so wieder wie vorher funktioniert, dann wäre es perfekt.

Mit freundlichen Grüßen

Hallo Herr L.,

der Grund des Fehlers ist Folgender:

Das Datum war als Datum definiert in der Form #Monat/Tag/Jahr#. Ich weiß nicht, was Sie eingetragen haben – aber vielleicht nicht als Datum. Da dieses Datum nur einmal als Text verwendet und angezeigt wird, habe ich einen Text daraus gemacht (somit wird nicht mehr impliziert konvertiert – hier lief wohl etwas schief).

Public Const p_cdatAppStand As String = "08.07.2021"

Kommentar hinterlassen

Die wollen doch tatsächlich, dass ich auch Dienstag bis Freitag komme. In der Stellenanzeige stand ausdrücklich: „Facharbeiter für Montage gesucht.“

Merkwürdige Formeln, VBA-Befehle

Ich muss per Programmierung den Inhalt einer Formel mit einem Bezug auswerten. Ich überlege:

Jede Bezugsformel innerhalb einer Arbeitsmappe hat in Excel die Form:

=Blattname!Zellbezug

Prima! Liegt auf dem Blatt „BMW“ in der Zelle „Z8“ ein Wert, kann man die Formel

=BMW!Z8

gut auswerten, indem man an dem Ausrufezeichen trennt. Vor dem Ausrufezeichen: Blattname; hinter dem Ausrufezeichen: Zellbezug. Gesagt – getan.

Jedoch: mir fällt auf, dass mein Programm manchmal einen Fehler produziert. Beispielsweise beim Bezug auf das Blatt „Alfa Romeo“, „Aston Martin“, „Rolls-Roycs“, und so weiter. Klar, ein Bezug auf das Blatt „Alfa Romeo“ wird dargestellt als:

=’Alfa Romeo‘!P1

Da der Blattname ein Leerzeichen (oder Gedankenstrich) enthält, muss ich den Apostroph aus dem Blattnamen löschen. Wirklich? Sollte ich ihn nicht besser von links und rechts löschen? Also: Wenn das erste Zeichen = ‚, dann entfernen. Wenn das letzte Zeichen = ‚, dann entfernen? Kann ein Blattname ein Apostroph enthalten? Ich probiere aus:

Tatsächlich: Mercedes‘ Benz funktioniert! Excel verbietet bei Namen von Tabellenblättern am Anfang und am Ende ein Apostroph, aber innerhalb des Namens ist es erlaubt.

Und wie sieht der Bezug auf dieses Blatt aus?

='Mercedes'' Benz'!W117

Erstaunlich! Der Apostroph wird entwertet, indem das Zeichen zwei Mal geschrieben wird! Das ist mir noch nie aufgefallen!

Das heißt: ich muss zwei Hochkommata (‚ ‚) durch eines ersetzen und muss den Apostroph am Anfang und am Ende löschen. Perfide!

Übrigens: Bevor Sie jetzt erboste Kommentare schreiben: die in Sindelfingen produzierende Automobilfirma heißt MERCEDES BENZ – ohne Apostroph!

2 Kommentare

Der Straßenbelag: hart aber Teer.

VBA-Befehle

Amüsant.

Ich öffne eine Testdatei, die mir ein Anwender zugeschickt hat, und wundere mich, wo das Menüband (das Ribbon) ist.

Dann fällt es mir ein: es gab da doch … Richtig:

Es gab einen Excel 4.0-Makrobefehl, mit dem man die Symbolleiste ausblenden konnte. Dieser funktioniert im aktuellen Excel noch immer und blendet hier das Menüband aus. Der Befehl lautet:

Application.ExecuteExcel4Macro "Show.Toolbar(""Ribbon"", False)"

Und: nicht vergessen: Nach Beendigung bitte wieder einschalten!

Kommentar hinterlassen ,

En Dios creemos; todos los demás deben mostrar datos.*)

Excel rechnet falsch, Fehlermeldungen, VBA-Befehle

Hallo Herr Martin,

mein Bekannter hat sich die neue Version angeschaut.

Jetzt lässt sich der Datensatz zwar anlegen, aber es treten neue Fehler auf, die bisher noch nicht vorhanden waren.

In der Tabelle erscheint nach dem Löschen des Datensatzes ein Fehler.

Hallo Herr L.,

öffnen Sie mal bitte eine ältere Version und klicken Sie dort auf Datenverwaltung und löschen Sie einen Datensatz.

Klicken Sie anschließend auf den Datensatz direkt über dem Datensatz, den Sie gelöscht haben.

Sie erhalten den Fehler.

Heißt: DIESER Fehler war schon lange drin, bevor Sie mir die Datei geschickt haben.

Woher kommt er?

Beim Löschen einer Zeile liefert die Zeile darüber einen Fehler:

Sie greifen auf den VALUE dieser Zelle zu – das knallt!

Die Ursache des Fehlers:

In der Spalte K (Membership) greift die Formel für den Wert „Silber“ auf die Zeile darunter zu! Die Formeln dieser Spalte sind falsch! Schon bevor Sie mir die Datei geschickt haben!

=WENN(UND(H2="Spain";J2>200);"Black";WENN(UND(H2="Spain";J2>180);"Platinum";WENN(UND(H2="Spain";J2>150);"Gold";WENN(UND(H3="Spain";J2>140);"Silber";WENN(UND(H2="Spain";J2>130);"Standard";WENN(UND(H2="Spain";J2>50);"Blue";"out of order"))))))

Fazit: es sind nicht neue Fehler – in Ihrem Programm sind einige alte Fehler, für die ich mich nicht verantwortlich zeichne.

An dieser Maske dlgKundenverwaltung habe ich nichts geändert.

schöne Grüße

Rene Martin

*) An Gott glauben wir; alle anderen müssen Daten zeigen!

Kommentar hinterlassen , , ,

Ein Pakettransporter verlor gestern eine große Ladung Synonymwörterbücher. Die Augenzeugen waren fassungslos, bestürzt, erschüttert, schockiert, betroffen, aufgewühlt, konsterniert, perplex, entsetzt, erschrocken und entgeistert.

VBA-Befehle

Einfach nicht aufgepasst. Dabei weiß ich es doch:

Ich erstelle eine Userform in Excel VBA. Darin sollen Werte „nach unten“ weitergegeben werden, wenn ein Kontrollkästchen angeklickt wird. Die Kästchen heißen chkFamilie02, chkFamilie03, chkFamilie04, … chkFamilie12

Ich prüfe, ob das letzte ausgewählt wurde oder ob noch weitere unten ausgewählt wurden:

Do Until frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Visible = False Or frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Value = False Or intZeile > 12

Das läuft an die Wand – eine Fehlermeldung ist die Folge. Mein Denkfehler:

wenn intZeile > 12, dann wird das geürft. Beispielsweise: intZeile hat den Wert 13. Allerdings: es wird auch geprüft, ob das Control chkFamilie12 sichtbar ist. Und das gibt es nicht!

OR (und auch AND) in VBA prüft (leider!) immer alle Teile. Und stoppt nicht, wenn einer der beiden Zweige falsch ist. Also anders gelöst – nicht ganz elegant – aber okay:

Do Until frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Visible = False frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Value = False 
       strZeile = Format(intZeile, "00")
       frmAuswahl.Controls("lblInfoZeile" & strZeile).Caption = strText
       intZeile = intZeile + 1
       If intZeile > 12 Then Exit Do
Loop

Geht doch!

Kommentar hinterlassen , , , , ,

Schatz. Guck mal! Kann ich den Bikini noch tragen? – Tragen schon, aber nicht mehr anziehen!

VBA-Befehle

Wenn man mit VBA programmiert und wissen möchte, ob in einem Text ein anderer vorhanden ist, kann man mit den Funktionen Left, Right, Mid oder Instr arbeiten. Oder den Vergleichsoperator Like verwenden. Also beispielsweise

If txtIBAN.Value Like „DE*“ Then …

Die Aufgabe: ich will Namen in Excel prüfen. Ich möchte wissen, ob sie auf intelligente Tabellen verweisen, also einen Aufbau haben, wie beispielsweise:

=tbl_Feiertage![#Alle]

oder:

=tbl_Feiertage[2021]

Also prüfe ich:

    For i = 1 To ThisWorkbook.Names.Count
        If ThisWorkbook.Names(i).RefersToLocal Like "=*[*]" Then
            MsgBox ThisWorkbook.Names(i).Name & " bezieht sich auf: " & ThisWorkbook.Names(i).RefersToLocal
        End If
    Next

Und wundere mich, warum die If-Verzweigung nichts findet.

Okay – noch ein Versuch – ich lasse die letzte eckige Klammer weg und prüfe erneut:

For i = 1 To ThisWorkbook.Names.Count
    If ThisWorkbook.Names(i).RefersToLocal Like "=*[*" Then
        MsgBox ThisWorkbook.Names(i).Name & " bezieht sich auf: " & ThisWorkbook.Names(i).RefersToLocal
    End If
Next

Verwundert reibe ich mir die Augen. Was klappt nicht? Die Prüfung

If ThisWorkbook.Names(i).RefersToLocal Like "=*" Then

funktioniert …

Ein Blick in die Hilfe beantwortet meine Frage:

https://docs.microsoft.com/de-de/office/vba/language/reference/user-interface-help/like-operator?f1url=%3FappId%3DDev11IDEF1%26l%3Dde-DE%26k%3Dk(vblr6.chm1008961);k(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

Die eckige Klammer ist als Zeichen reserviert. Man muss sie entwerten. Weiter unten lese ich:

Ah – so funktioniert es:

If ThisWorkbook.Names(i).RefersToLocal Like "=*[[]*" Then

Oder auch so:

If ThisWorkbook.Names(i).RefersToLocal Like "=*[[]*[]]" Then

Warum nicht gleich?!?

Ein Kommentar , , , ,

Menschen, die mich an der Kasse vorlassen, weil sie sehen, dass ich es eilig habe und nur wenige Dinge in der Hand habe, kommen übrigens in den Himmel.

Merkwürdige Formeln, VBA-Befehle

Bernhard hat mich darauf aufmerksam gemacht. Ist mir bislang nicht aufgefallen.

Die Funktion DATEDIF in Excel und die VBA-Funktion DateDiff rechnen unterschiedlich.

Trägt man in zwei Zellen die Datumsangaben 20.05.2021 und 01.06.2021 ein, so beträgt bei der Excelfunktion DATEDIF mit dem Parameter „M“ das Ergebnis 0, bei der VBA-Funktion dagegen 1.

Ich habe eine kleine Tabelle aufgebaut: im oberen Teil einige Datumsdifferenzen auf Basis des Monats:

Im unteren Teil verwende ich ein kleines VBA-Makro:

Sub BerechneDateDIFF()
     Dim intZeile As Integer
     Dim intSpalte As Integer

     For intZeile = 21 To 30
         For intSpalte = 2 To 20
             ActiveSheet.Cells(intZeile, intSpalte).Value = DateDiff("M", ActiveSheet.Cells(intZeile, 1).Value, ActiveSheet.Cells(20, intSpalte).Value)
         Next intSpalte
     Next intZeile

End Sub

Das Ergebnis:

Die Unterschiede habe ich mit einer bedingten Formatierung farblich hervorgehoben.

Ein Dankeschön für den wertvollen Hinweis an Bernhard Ramroth.

Kommentar hinterlassen , , ,

Liebe klopft nicht an und fragt, ob es gerade passt. Liebe passiert einfach. – Übrigens: genauso verhält es sich mit Durchfall.

VBA-Befehle

Hallo Rene,

Eine Sache, die auch bei mir hier (trotz 6GB Arbeitsspeicher) immer wieder kommt, ist folgendes:

Nicht genügend Systemressourcen.

Debuggen kommt dann das hier.. Beim Ausführen einer ganz normalen SQL Anweisung, (zugegeben in einer Rekursion) bleibt er hier stecken..

Auf was muss ich denn da besonders achten?..
Oder könnte man das mit speziellen Funktion abfangen?

Viele Grüße nach München, Bernd

Hi Bernd,

Ich kenne solche Fehler … Nervig!

Versuch mal die Anweisungen rst!dblAnzWdh und Nz(rst!dblWeightPart, 0) in zwei (Double-)Variablen zu schreiben und dann das Produkt auszuführen (das Ergebnis wieder in eine dritte Variable), ebenso die rst!ID.

Und dann den SQL-Code zusammenzubauen.

Klappt das?

Liebe Grüße

Rene

Kommentar hinterlassen

Ich bin Neptun, Herr des Wassers! Spürt meine Macht, ihr Untertanen! – Alter, hör auf, meine Goldfische mit einer Gabel zu jagen!

VBA-Befehle

Einfach nicht aufgepasst. Dabei weiß ich das eigentlich!

In einem großen, langen VBA-Programm werden Daten aus verschiedenen Dateien des gleichen Ordners, in dem sich die Datei mit dem Code befindet, zusammengefasst. Diese Dateien soll geöffnet werden, Inhalt herauskopiert und danach wieder geschlossen werden.

Ich überprüfe im ersten Schritt, ob es sich um eine XLSM oder XLSB-Datei handelt, denn nur dort liegen die gesuchten Daten:

If Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb" Then

Klappt wunderbar.

Fast.

Denn auch die Datei, in der sich der Code befindet, wird bearbeitet, also geschlossen. Das ist nicht Sinn der Sache. Also schließe ich aus:

If Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb" And strDatei <> ThisWorkbook.Name Then

und wundere mich, warum es nicht funktioniert. Die aktuelle Datei wird immer noch geschlossen. Seltsam.

Ich prüfe:

der Inhalt von strDatei entspricht ThisWorkbook.Name. Warum fließt diese Abfrage in die Bedingung, warum wird sie ausgewertet, oder genauer: warum liefert die IF-Verzweigung den Wert Wahr? Es dauert ein paar Sekunden, dann dämmert es mir:

AND ist stärker als OR. Die IF-Verzeigung überprüft, ob die Datei strDatei die Endung XLSB hat UND die gleiche Datei ist. Nein – ich laufende Datei habe die Endung XLSM. Oder: hast du die Endung XLSM? ja – DAS ist korrekt. Also wird die Bedingung ausgewertet. *ggrrrrr* Also noch einmal – diesmal mit Klammer:

If (Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb") And strDatei <> ThisWorkbook.Name Then

Und das funktioniert! Ich bin glücklich!

Kommentar hinterlassen , , , , , , ,

Die Kondomindustrie hat echt Humor. Die packen zehn Stück in eine Packung und dann sind de nur vier Jahre haltbar.

VBA-Befehle

Andreas Thehos erhält Post:

Hallo Andreas,

Kannst du auch einmal ein Tutorial über diese defekten Tabellen machen und wie man die wegbekommt? Bei meinen Recherchen habe ich bis jetzt nur gefunden das die erstellt werden wenn Excel abstürzt. Die einzige Lösung de in foren geholfen hatte, war eine neue Mappe zu erstellen und alle Daten zu übertragen. Aber das kann doch nicht die Lösung sein oder? Alle Änderungen die vorgenommen werden beziehen sich nur auf „DieseArbeitsmappe“

Andreas weiß keinen Rat. Und ich? Ich hatte mal eine solche korrupte Datei gesehen – ist schon eine Weile her. Und nein – sorry – ich weiß leider auch keinen Rat.

Aber Andreas wird fündig:

https://stackoverflow.com/questions/18273071/excel-vba-project-has-generated-multiple-workbook-objects/49073426

Kommentar hinterlassen ,

Nach zwei Stunden Babysitten glaube ich, dass ich doch keine Kinder, sondern lieber etwas Harmloseres möchte. Krokodile zum Beispiel. Oder Löwen.

Power Query, VBA-Befehle

Hallo Excel-Meister

ich arbeite an einem VBA – Projekt, dass aus Power BI Dateien die Metadaten rauslesen soll.

Das Auslesen geschieht über Power Query (what else….), aber ich muss noch ein paar Prüfungen mit VBA erstellen und insbesondere die Power Query Abfragen on the fly erstellen. Letzteres geht problemlos.

Der Ablauf:

  1. Prüfe, ob User die pbix geöffnet hat.
  2. Falls nicht, bitte freundlich darauf aufmerksam machen
  3. Falls nein, Abbruch – falls ja, pbix öffnen.

Bis dahin klappt alles.

Nun kommt der Punkt, wo der Benutzer sich gegenüber der Power BI Datei authentifizieren muss, nachdem er ja gesagt hat.

falls er aber den Dialog hier abbricht (…..DAU…….), kommt eine „schöne“ Meldung:

Nun meine Frage:

Wie kann ich hier meine eigene Meldung einbauen und vor allem, wie fange ich das ab?

Bin schon voller Zweifel…..

Merci, lieber René für deine Geduld mit mir

Freundliche Grüsse Hans Peter

########################################

die unwissenden erleuchten sich selber

habs gefunden. nach Drücken von „Senden“ fiel es mir wieder ein, da stand was im Buch von René

ich danke dir!

Hier der Code, falls es dich interessiert.

Sub GetData()

‚XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

‚ Code erstellt durch: Pfister BI Consulting GmbH

‚ Zweck: Holt Metadaten aus der Power BI Datei

‚ Erstelldatum: 8.3.2021

‚ Aenderungsdatum:

‚XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

‚Testen, ob pbix Datei geöffnet ist. Falls nicht, Mesagebox und fragen, ob sie geöffnet werden soll. Fall nicht, Abbruch

    If Dateigeoeffnet(Range(„Dateipfad_PBIX_Original“)) = False Then

        If MsgBox(„Die Datei muss geöffnet sein. “ & Chr(10) & “ Soll die Datei geöffnet werden?“, vbYesNo, „Power BI Datei öffnen?“) = vbNo Then

            Exit Sub

            Else: Call Open_PBIX

                Application.Wait (Now + TimeValue(„0:00:10“))

            End If

   End If

’notwendige Abfragen aktualisieren

Abfragen_starten:

On Error GoTo ErrHandler

    ActiveWorkbook.Connections(„Abfrage – Tabellen“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Memory Usage Tabellen“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Tabellenliste“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Liste nicht geladene Queries“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Abfragen – nicht geladen“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

  Call Listen_befuellen

ErrHandler:

    ‚Fehler No. 1004 abfangen

    If Err = 1004 Then

        If MsgBox(„Soll der Prozess abgebrochen werden?“, vbYesNo, „Bitte Identifikation vornehmen“) = vbYes Then

            Exit Sub

        ’sonst Abfrage wieder aufnehmen

            Else: Resume Abfragen_starten

        End If

    End If

End Sub

Gruss Hp

Ein Kommentar , ,

Seirtdem ich mich vom Beinbruch erholt habe, laufe ich besser als früher. Jetzt fehlt mir nur noch eine Gehirnerschütterung.

VBA-Befehle

Hallo Rene,
ich benötige BITTE DRINGEND Deine Hilfe!!!
Ich habe hier irgendetwas abgeschossen……
Kann eine ausgeschnitte Zeile nicht mehr einfügen….
DANKE

es geht es geht……
DANKE DANKE DANK!!!!!!!!!!!
Was war es???

vor der Antwort eine Frage:
klick mal bitte in Excel auf Datei / Konto.
hast du auch Version 2102?

Hallo JA, die habe ich auch.
wobei jetzt gerade ein update läuft…. 🙁
hoffe es geht dann noch alles….
habe jetzt: Version 2102 Build 13801.20294
ABER ES LÄUFT!!!

Eben, Jörg: Microsoft macht Updates. ich vermute stark, dass Microsoft etwas kaputt gemacht hat.

In eurem Makro verwendet ihr ein uraltes Tool, um Masken (Dialoge) anzuzeigen. Aus Excel 4.0. Da lief ungefähr in der Zeit, als die Dinosaurier ausstarben. Ich habe dich ja im Dezember mal gefragt, ob man (ich) nicht mal den ganzen alten Schrott, der in euren Makros steckt, erneuern soll. Und ich fürchte: heute ist es passiert – da wird etwas aus den 90er Jahren nicht mehr unterstützt …

Ich habe dir im Dezember einen Screenshot von der Maske geschickt. Deshalb wusste ich, dass sie damals bei mir noch lief. Heute bei mir auch nicht mehr.

ich habe die Maske mit den aktuellen Bordmitteln nachgebaut – deshalb sieht sie ein klein wenig anders aus … beispielsweise der Titel in der Titelzeile.

Ich werde das mal in Foren posten und fragen, ob jemand eine ältere Version hat – ob es da noch läuft …

Bevor dein Chef dich tötet, flehe um Gnade und sag ihm einen schönen Gruß von mir, dass Microsoft Sachen kaputt macht … nicht nur du …

Liebe Grüße

Rene

####

Jörg war glücklich. Ich habe ihm vorgeschlagen, das Makro zu überarbeiten. Habe ihm ein Angebot gemacht. Er hat sich seitdem nicht mehr gemeldet.

Kommentar hinterlassen , , ,

Ich muss meinem Gesichtsausdruck beibringen, wie man seine Meinung für sich behält.

VBA-Befehle

Lieber René,

nachfolgender Code läuft fehlerfrei unter
Win 7 + Word 2010
Win 7 + Word 2013
Win 10 + Word 2019
Win 10 + Word 365

nur nicht unter Win 10 + Word 2016.
Die rot markierte Zeile ist mein Problemkind.

Vielleicht hast Du noch eine Idee.

Sub TopAktuell_Einfügen()
       Application.ScreenUpdating = False
   DocPath = "O:\Topaktuell\"
   ChDrive ("O:\")
   ChDir ("O:\Topaktuell\")
   Dim datei As String
         Selection.EndKey Unit:=wdStory
      For dokumente = 1 To 30
        If Dir(DocPath & dokumente & "_1.docx") = "" Then Exit For
   For seiten = 1 To 20
     datei = dokumente & "_" & seiten & ".docx"
     If Dir(DocPath + datei) = "" Then Exit For
    Selection.InlineShapes.AddOLEObject ClassType:="Word.Document.12", _
    FileName:=datei

     Next
 Next

   Application.ScreenUpdating = True
End Sub

Danke.

LG Traudl

Hi Traudl,

Seit über 20 Jahren schreibe ich VBA-Code. Dabei ist mir aufgefallen, dass sich einige wenige Objekte verändert haben. Ich habe mal vor vielen Jahren ein Makro geschrieben, das Text in eine Word-Tabelle geschrieben hat und den Text mit einem Hyperlink auf ein Word-Dokument versehen hat. Eine der Parameter beim Befehl Hyperlinks.Add wurde geändert.

Deshalb sage ich nicht, dass das nicht sein kann.

ABER! Bitte, bitte, bitte: adressiere doch sauber.
Ändere seinen Code in:
Dim wdBereich As Range

Set wdBereich = ActiveDocument.Range(Start:=ActiveDocument.Range.End - 1)

(Ende des Dokuments)

Und dort:
wdBereich.InlineShapes.AddOLEObject ClassType:="Word.Document.12", _
FileName:=datei

Es kann natürlich sein, dass es trotzdem nicht unterstützt ist – aber eher unwahrscheinlich als das „Hoppeln“ an Ende:
Selection.EndKey Unit:=wdStory
Raus damit!

Und: wenn du gerade dabei bist:
ChDrive ("O:\")
ChDir ("O:\Topaktuell\")

Raus damit!

Und: deklariere die Variablen:
Dim docpath As String
Dim dokumente As Integer
Dim seiten As Integer
Dim datei as String

Und schließlich: Warum fügst du ein Word-Dokument in ein anderes ein? Ich würde es öffnen, an eine Objektvariable übergeben, den Inhalt (brauchst du die Formate?) entweder in einer Stringvariable speichern oder kopieren und ins Dokument einfügen.

Hilft das?

Liebe Grüße

Rene

Ich danke Dir ganz, ganz herzlich für Deine Vorschläge, lieber René.

Den Code hatte ich 2017 erstellt und war froh, dass er läuft. Er ist eine Sub aus einem unfangreichen Makro. Ich bin allerdings keine Programmiererin. Dazu benutze ich VBA viel zu wenig.

Deine Vorschläge werde ich natürlich umsetzen .

Ich gebe Dir Bescheid, wie es funktioniert.

Liebe Grüße Traudl

Kommentar hinterlassen

Mama, was macht der Storch, nachdem er die Kinder abgeliefert hat? – Liegt auf der Couch und schaut Bundesliga.

VBA-Befehle

Hallo René,

ich komme heute mit einer Frage auf Dich zu.

In einem „unserer“ Excel-Stammtische hast Du uns ein Besispiel gezeigt, wie Du durch ein Klick auf ein Bild „Inhalte aktivieren“ bestätigen kannst (d.h. der Benutzer muss nicht oben auf den orangefarbende Balken klicken , um die Makros der Arbeitsmappe zu aktivieren…).

Leider musste ich feststellen, dass meine Kollegen gerne die Aufforderung „Inhalte aktivieren“ übersehen und so meine Arbeitsmappen mit Makros nicht aktiviert werden. Um das ersichtlich zu machen bzw. zu erzwingen, würde ich gerne Deine Lösung einbauen.

Kannst Du mir dazu bitte Dein Beispiel zukommen lassen?

Das wäre großartig!

Vielen Dank & Grüße

Sebastian

so!

Ich habe ein Bild auf das Blatt gelegt, das ich beim Öffnen per Makro unsichtbar mache.

Werden die Makros nicht aktiviert, bleibt das Bild sichtbar.

Die Erklärung: Manchmal beschweren sich Anwender, dass bestimmte Befehle deaktiviert sind. Es wäre praktisch ein Makro zu haben, das meldet, dass die Makros nicht aktiviert wurden. Dies geht natürlich nicht. Um sicherzustellen, dass die Makros aktiviert wurden, kann in ein Projekt ein Bild eingefügt werden.

Werden die Makros nun aktiviert, wird das Bild gelöscht.

Man kann Shapes einen Namen geben oder mit einer Schleife alle Shapes / Bilder durchlaufen und entweder löschen oder Unsichtbar machen (Visible = False)

Das Bild wird beim Schließen der Arbeitsmappe wieder eingeblendet:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next

ThisWorkbook.Worksheets(„BCM“).Shapes(„Hinweis“).Visible = _

   msoTrue

Hallo René,

super! Klasse! 

Ich danke Dir!

Ich war von Deiner Lösung damals so begeistert, dass die hängen geblieben ist.

Da ich allerdings nicht mit (externen) Kunden arbeite, hatte ich keinen Anwendungsfall – bis ich letzte Woche erfahren durfte, dass meine Kollegen gerne den Balken übersehen und so nicht meine Datei funktioniert.

Ich werde Deine Lösung am Montag einbauen- das Bild wird selbstverständlich geändert.

Tausend Dank nochmal und bis bald.

Liebe Grüße  Sebastian 

Kommentar hinterlassen , ,

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!

Excel-Objekte, VBA-Befehle

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!

Kommentar hinterlassen , , , , , , , , ,

Ich hab mal nachgedacht. Das Sportlichste bei mir zu Hause ist der Läufer im Flur.

VBA-Befehle

Tabellenblätter in Excel in Excel haben nicht nur einen (sichtbaren) Namen, der vom Anwender oder von der Anwenderin geändert werden kann. Im VBA-Editor gibt es auch noch einen Name (Codename), der nur dort und nur per Hand geändert werden kann. Ich kann nun per Programmierung leicht überprüfen, ob alle Tabellen, die ich benötige, noch vorhanden sind. Ich habe eine Funktion GibtEsTabellenblatt geschrieben, die überprüft, ob die aktuelle Datei ein Tabellenblatt mit einem solchen Codenamen hat. Ich möchte die Arbeitsmappe nicht schützen, weil der Anwender oder die Anwenderin neue Blätter hinzufügen, löschen, umbenennen darf. Allerdings: meine Blätter dürfen nicht gelöscht werden. Ich sehe nach:

Jedes der Tabellenblätter hat ein Ereignis BeforeDelete:

Dieses Ereignis hat allerdings kein Abbruchkriterium (Cancel), mit dem man das Löschen verhindern könnte.

Der zweite Blick fällt auf das Objekt Workbook. Gibt es dort ein Abbruchparameter?

Das Ereignis dort heißt SheetBeforeDelete und hat auch keinen solchen Parameter:

Allerdings einen Parameter Sh mit dem ich gezielt überprüfen kann, welches Blatt nicht gelöscht werden darf (und die Liste schnell erweitern kann:

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

End Select

Problem: Die Meldung wird angezeigt und DANN das Blatt gelöscht. Doof!

Nächster Versuch: Und wenn ich DANACH die Arbeitsmappe schütze?

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

Klappt! Hier die drei Meldungen, die erscheinen:

Allerdings: DANN ist die Arbeitsmappe geschützt. Was ich ja eigentlich nicht wollte. Also flux den Arbeitsmappenschutz wider aufheben. Wo? Am besten beim Wechseln auf ein anderes Arbeitsblatt:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    On Error Resume Next
    ThisWorkbook.Unprotect
End Sub

Ein Kommentar , , , , , , , , ,

Schnell wieder die Beine rasieren, bevor man zu den alten Nordmann-Tannen am Straßenrand gestellt wird.

VBA-Befehle

Ich erstelle gerade ein größeres VBA-Projekt für einen Kunden. Und bin mal wieder verblüfft! Die größte Verblüffung hatte ich letzte Woche. Man erstelle in Excel eine XLSM-Datei mit Makros. Ein Makro wird über eine Schaltfläche (oder ein Bild, ein SmartArt, eine Form, ein Diagramm) aufgerufen:

Ich kopiere das Tabellenblatt in eine andere Datei

und speichere die Datei OHNE Makros als XLSX! Ich schließe alles, öffne die XLSX-Datei und werde gefragt, ob ich die Makros aktivieren möchte:

Okay. Ich aktiviere die Datei. Ein Klick auf die Schaltfläche und werde darauf hingewiesen:
Microsoft Office hat ein potenzielles Sicherheitsrisiko erkannt.

Was passiert? Die andere Datei wird im Hintergrund geöffnet, das Makro wird ausgeführt! Denn: hinter der Schaltfläche steht noch immer der Dateiname und Makroname.

Fazit: Mein Glaubenssatz: XLSX-Dateien sind sicher, denn sie können keine Makros enthalten, ist erschüttert! Zwar enthalten XLSX-Datei weiterhin keine Makros, können aber Makros aus anderen Dateien aufrufen!

Kommentar hinterlassen , , , ,

Warum gibt es bei Tastaturen nicht auch so Krümelschalen wie bei Toastern?

VBA-Befehle

Wenn ich in VBA für Excel programmiere, muss ich häufig ermitteln, ob ein Wert in einer Liste vorhanden ist. Die Funktion

Application.WorksheetFunction.CountIf([Spalte],[Wert])

also: ZÄHLENWENN, leistet, was ich möchte: ein Befehl (ohne Schleife) und ich habe die Information (Wert in der Spalte oder nicht vorhanden). Ebenso verwende ich häufig SUMMEWENN:

Application.WorksheetFunction.SumIf

oder – um die Zeilennummer zu ermitteln VERGLEICH:

Application.WorksheetFunction.Match([Wert],[Spalte],0)

So spare ich mir das Schreiben von Schleifen. Nun wollte ich die kumulierten Geldbeträge zu bestimmten Monaten wissen. In Excel lautet die Funktion

=SUMMENPRODUKT((MONAT(A:A)=1)*(B:B))

also: summiere die Werte der Spalte B, wenn eine Datumsangabe in der Spalte A ein Datum des ersten Monats (Januar) im Jahr enthält. Klappt wunderbar. Und in VBA? Dort versagt eine Zeile wie:

Application.WorksheetFunction.SumProduct(Month(ActiveSheet.Columns(1) = 1) * ActiveSheet.Columns(1))

Die Ursache ist schnell gefunden: Colums(1) = 1 kann nicht verarbeitet werden; auch nicht Month(Columns(1)); der Gleichheitsoperator in VBA ist nicht matrixfähig; „=“ kann nur identische Dinge vergleichen.

Schade – also doch eine Schleife …

Kommentar hinterlassen , , , , ,

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?

Excel-Objekte, VBA-Befehle

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

4 Kommentare , , , ,

Wurde der Fernseher repariert? Schneidet der Gärtner die Hecke zu Ende? Gibt es Trinkgeld für die Pizza? – Pornos lassen immer so viele Fragen offen …

VBA-Befehle

Hallo, i ch benötige kurze Hilfe, ich habe hier ein Makro, noch von meinem Vorgänger, das wohl in einer Excel Liste eine Zeile entfernt und wo anders
dann wieder einsetzen kann. Nun ist es aber so, das ich Zeilen habe, die einen Zeilenumbruch in der Zelle haben, und dann immer eine Fehlermeldung 1004 kommt. Können Sie mir hier BITTE helfen????
Danke
Jörg

Hallo Jörg,
so groß und schwierig kann das ja wohl nicht sein – wenn das Makro vorher gelaufen ist …
schick es mir doch mal, bitte, dann schreibe ich dir etwas dazu.
Liebe Grüße Rene

Hallo Rene,

hier mal so….

In einer Excel Zelle habe ich einen Textumbruch, mit sehr viel Text.

Den kann ich zwar über den Makro „löschen“, aber dann nciht

mehr an einer anderen Stelle einfügen…..

BITTE Um HILFE! DANKE 

Sub FahrtAuswaehlenEinfach()
Dim aDlg As DialogSheet
     Dim aRow As Range
     Set aDlg = Sheets("Fahrtauswahl")
     
     i = 1
     aDlg.ListBoxes("Fahrten").RemoveAllItems
     Do While Sheets("Tabelle2").Cells(i, 2).Value <> ""
         astring = Format$(Sheets("Tabelle2").Cells(i, 2).Value, "short time")
         astring = astring + " - " + Format$(Sheets("Tabelle2").Cells(i, 3).Value, "short time")
         astring = astring + "  " + Sheets("Tabelle2").Cells(i, 4).Value
         astring = astring + " --- " + Sheets("Tabelle2").Cells(i, 8).Value
         astring = astring + " / " + Sheets("Tabelle2").Cells(i, 11).Value
         aDlg.ListBoxes("Fahrten").AddItem astring
         i = i + 1
     Loop
     
     If aDlg.Show = True Then
         i = aDlg.ListBoxes("Fahrten").ListIndex
         Sheets("Tabelle2").Rows(i).EntireRow.Cut
         Sheets("Linienpläne - quer").Select
         ActiveCell.EntireRow.Insert Shift:=xlDown
         Sheets("Tabelle2").Rows(i).EntireRow.Delete xlUp
     End If 
End Sub

    Zuerst musst ich schlucken und mich auf die Suche machen. Was bitte ist ein DialogSheet? Ich werde fündig: in Excel 4.0 konnte man ein Dialogblatt einfügen. Und darauf Steuerelemente platzieren. Und die verwenden so etwas immer noch im Jahre 2020?

Egal. Meine Vermutung liegt beim Operator „+“. Wenn in den Zellen Zahlen stehen, hat VBA ein Problem beim Verketten von 

astring = astring + "  " + Sheets("Tabelle2").Cells(i, 4).Value

Das ist aber nicht die Lösung. Ich bitte um die Daten. Der Zeilenumbruch ist nicht das Problem, sondern:

Der Text ist zu lang! Excel lässt mehr als 8.000 Zeichen / Zelle zu, die ListBox leider nur 255

Also schnell den Code geändert:

Sub FahrtAuswaehlen()

    Dim aDlg As DialogSheet
    Dim aRow As Range
    Set aDlg = Sheets("Fahrtauswahl")
    
    i = 1
    aDlg.ListBoxes("Fahrten").RemoveAllItems
    Do While Sheets("Tabelle2").Cells(i, 2).Value <> ""
        astring = Format$(Sheets("Tabelle2").Cells(i, 2).Value, "short time")
        astring = astring & " - " & Format$(Sheets("Tabelle2").Cells(i, 3).Value, "short time")
        astring = astring & "  " & Left(Sheets("Tabelle2").Cells(i, 4).Value, 200)
        astring = astring & " --- " & Sheets("Tabelle2").Cells(i, 11).Value
        aDlg.ListBoxes("Fahrten").AddItem astring
        i = i + 1
    Loop
    
    If aDlg.Show = True Then
        For i = aDlg.ListBoxes("Fahrten").ListCount To 1 Step -1
            If aDlg.ListBoxes("Fahrten").Selected(i) Then
                Sheets("Tabelle2").Rows(i).EntireRow.Cut
                Sheets("Linienpläne - quer").Select
                ActiveCell.EntireRow.Insert Shift:=xlDown
                Sheets("Tabelle2").Rows(i).EntireRow.Delete xlUp
            End If
        Next
    End If
    
End Sub

Die Antwort:

Hallo Rene,

JA, das wars wohl! Klappt SUPER!!!

TAusend DANK!!!

Wie kann ich mich revangieren?

D A N K E ! ! !

Gruß

Jörg

Kommentar hinterlassen , , , , ,

Wenn du meinen Charakter beschreiben sollest, welches Tier wäre ich dann? – Eine Miesmuschel!

VBA-Befehle

Hallo René,

hast du das gelesen? 😉

https://www.golem.de/news/microsoft-excel-bringt-endlich-das-ende-von-vba-2012-152627.html

Ich finde ja diese Formulierungen ja immer so bemerkenswert, wenn in der Presse die Rede von Microsoft Excel ist:

„VBA gehört aber zu jenen Sprachen, die nahezu jedem den Verstand rauben und mit denen fast niemand freiwillig programmiert. Wer es dennoch muss und dafür bezahlt wird, wird die Bezahlung eher als Schmerzensgeld betrachten.“

Weiß nicht, ob du den 2. Satz bestätigen kannst. 😉 Viele Grüße, Dominic

Hi Dominic,

nein – das habe ich nicht gelesen. Ich würde ihm erwidern: „Wer so etwas schreibt hat diese Sprache weder gelernt noch verstanden.“

In Corona-Zeiten, in der meine Schulungen weggebrochen sind, bin ich froh, dass ich VBA-Lösungen entwickeln darf. Ich mache das sehr, sehr gerne!

Liebe Grüße

Rene

Kommentar hinterlassen ,

Mein Schatz sagt oft „Du Hengst“ zu mir. Vorhin zum Beispiel: „du Hengst nur noch auf der Couch rum!“

VBA-Befehle

Einfach nicht aufgepasst!

Programmieren Sie VBA? Programmieren Sie Formeln in VBA? Auf einem Tabellenblatt befinden sich in den Spalten BJ und BL Daten, die vertikal angeordnet sind:

Auf einem anderen Blatt wird Bezug auf diese Daten genommen:

Während ich die erste Spalte nach unten ziehen kann, muss ich die Formeln der ersten Zeile getrennt eingeben – ich möchte weder mit MTRANS, INDIREKT noch mit BEREICH.VERSCHIEBEN arbeiten. Es handelt sich um maximal sechs Werte.

Die Formeln müssen per VBA neu geschrieben werden. Der Makrorekorder ermittelt für die Formel

=WENN(tbl_Basisdaten!BJ2="";"";tbl_Basisdaten!BJ2)

der Zelle A2 den VBA-Code:

ActiveCell.FormulaR1C1 = " =IF(tbl_Basisdaten!RC[61]="""","""",tbl_Basisdaten!RC[61])"

Dabei ist RC[61] ein relativer Bezug: R -> gleiche Zeile; C[61] -> Spalte um 61 Spalten verschoben. Wäre der Bezug absolut ($BJ$2) wäre der Code:

R2C62

also: in Zeile 2 und in Spalte 62 – egal, wo sich die Zielzelle befindet. Der Code wird in einer Schleife verwendet:

For i = 1 To 6
   ThisWorkbook.Worksheets("tbl_Risikomatrix").Range("A1").Offset(i, 0).FormulaR1C1 = _
      "=IF(tbl_Basisdaten!RC[61]="""","""",tbl_Basisdaten!RC[61])" ' -- erste Spalte A
Next i

Klappt famos! Und nun die erste Zeile. Der Makrorekorder zeichnet auf:

ActiveCell.FormulaR1C1 = _
    "=IF(tbl_Basisdaten!R[1]C[62]="""","""",tbl_Basisdaten!R[1]C[62])"

Das kann doch leicht angepasst werden:

For i = 1 To 6
    ThisWorkbook.Worksheets("tbl_Risikomatrix").Range("A1").Offset(0, i).FormulaR1C1 = _
        "=IF(tbl_Basisdaten!R[" & i & "]C[62]="""","""",tbl_Basisdaten!R[" & i & "]C[62])" ' -- erste Zeile 1
        
Next i

Padautz – das funktioniert nicht! In B1 greife ich auf Spalte 63 zu, also 62 Spalten „neben mir“. In C1 dagegen benötige ich die Spalte, die sich 61 Spalten neben mir befindet, in D1 dagegen 60 Spalten. Also noch einmal:

For i = 1 To 6
    ThisWorkbook.Worksheets("tbl_Risikomatrix").Range("A1").Offset(0, i).FormulaR1C1 = _
        "=IF(tbl_Basisdaten!R[" & i & "]C[" & (63 - i) & "]="""","""",tbl_Basisdaten!R[" & i & "]C[" & (63 - i) & "])" ' -- erste Zeile 1
Next i

Und das klappt! Man muss so aufpassen bei den Bezügen! Und beim Umdenken von =BL2 auf =R[1]C[62].

Kommentar hinterlassen , , , , ,

Auf der Waage den Bauch einziehen, macht dich auch nicht leichter! – Das nicht, aber ich kann die Zahlen lesen.

VBA-Befehle

Ich bin erstaunt. Ich erstelle mit Excel-DNA eigene Funktionen für Excel (UDF = userdefined fuctions). Klappt gut, beispielsweise: Quersumme:

Das Ergebnis wird korrekt berechnet:

Jedoch: Wenn ich die Funktion ohne Klammer und ohne Parameter eintrage, erhalte ich:

Eine interne Fehlernummer? Ich weiß es nicht …

Kommentar hinterlassen , ,

Kann mann mit Männergrippe schon Pflegestufe 3 beantragen?

Excel-Objekte, sortieren, VBA-Befehle

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!

Kommentar hinterlassen , ,

Vor Kurzem habe ich im Fitnessstudio einen gesehen, der hat tatsächlich eine Wasserflasche in den Pringles-Halter am Laufband geklemmt!

VBA-Befehle

Im Frühjahr habe ich für einen Kunden ein kleines Projekt erstellt. Es geht darum, bestimmte Informationen in Visio einzusammeln und mit VBA nach Excel zu schreiben. Einige Stunden Programmierarbeit; das Projekt lief. Ich habe getestet, der Kunde hat getestet – kein Fehler.

Letzte Woche – nach einem halben Jahr – kam eine Mail, dass das Programm an einer Stelle nicht mehr korrekt rechnen würde. Ich war erstaunt und schaute es mit an. Tatsächlich: drei Fehler(chen) habe ich gefunden:

Fehler I

For i = 1 To vsSeite.Shapes.Count
If vsSeite.Shapes(i).CellExists("Prop._VisDM_ID", False) = True Then
intTemp = WelchesRack(vsSeite.Shapes(i))
For k = 1 To vsSeite.Shapes(i).Section(visSectionProp).Count - 1
[...]

Der Denkfehler ist Folgender: Das erste Shape hat die Nummer 1. Die erste Section allerdings die Nummer 0. Das heißt: In der Zeile

For k = 1 To …

beginnt die Zählung beim zweiten Element! Diese Liste ist nullbasiert!

Fehler II

Ich sammle Werte in einer Liste ein, die folgendermaßen aufgebaut ist:

Europa|Schweiz|Bern|42|12|33|9|4711|||

Einige Werte werden erhöht:

For k = 1 To vsSeite.Shapes(i).Section(visSectionProp).Count - 1
If vsSeite.Shapes(i).Section(visSectionProp).Row(k).Cell(2).FormulaU = """" & strTeil & """" Then ' 2 = Label
If vsSeite.Shapes(i).Section(visSectionProp).Row(k).Cell(0).FormulaU = "" Then
lngTempWert = 0
ElseIf IsNumeric(Replace(vsSeite.Shapes(i).Section(visSectionProp).Row(k).Cell(0).FormulaU, """", "")) = False Then
lngTempWert = 0
Else
lngTempWert = Split(strBerechnung(intTemp), "|")(j) + CLng(Replace(vsSeite.Shapes(i).Section(visSectionProp).Row(k).Cell(0).FormulaU, """", ""))
End If

Der Denkfehler: wenn der einzufügende Wert nicht korrekt ist, darf ich nicht eine 0 einfügen, sondern den alten Wert drinlassen. Also ich darf nicht schreiben:

lngTempWert = 0

sondern:

lngTempWert = Split(strBerechnung(intTemp), "|")(j)

Fehler III

Ein Codeblock sieht so aus:

strTemp = vsSeite.PageSheet.Cells("User.Berechnung" & (k + 1)).ResultStrU("")
If strTemp Like "*|*|*" Then
strTemp0 = Split(strTemp, "|")(0): strTemp1 = Split(strTemp, "|")(1): strTemp2 = Split(strTemp, "|")(2)
If strTemp0 <> "" And strTemp1 <> "" And IsNumeric(strTemp2) Then
strTempWert = Split(strBerechnung(i), "|")(k + 1)
dblBerechneterWert = Berechnung(CDbl(strTempWert), strTemp0, strTemp1, CDbl(strTemp2))
Call ShapeEinrichten(vsShapeUnten, Format(dblBerechneterWert, "0"))
If blnExcel = True Then
xlBlatt.Cells(intExcelZeile + 1, intAnzahlDaten + k + 1).Value = dblBerechneterWert
End If
End If
End If

Und für den zweiten Block – etwas versetzt:

strTemp = vsSeite.PageSheet.Cells("User.Berechnung" & (k + 5)).ResultStrU("")
If strTemp Like "*|*|*" Then
strTemp0 = Split(strTemp, "|")(0): strTemp1 = Split(strTemp, "|")(1): strTemp2 = Split(strTemp, "|")(2)
If strTemp0 <> "" And strTemp1 <> "" And IsNumeric(strTemp2) Then
strTempWert = Split(strBerechnung(i), "|")(k + 1)
dblBerechneterWert = Berechnung(CDbl(strTempWert), strTemp0, strTemp1, CDbl(strTemp2))
Call ShapeEinrichten(vsShapeUnten, Format(dblBerechneterWert, "0"))
If blnExcel = True Then
xlBlatt.Cells(intExcelZeile + 1, intAnzahlDaten + intGruppe1 + k + 1).Value = dblBerechneterWert
End If
End If
End If

Sehen Sie es? Das ist ein „klassischer“ Kopierfehler. Gleicher Block; muss ein bisschen abgeändert werden – Variable ausgetauscht oder Wert erhöht. Hier wird der Wert erhöht: Aus k + 1 wird k + 5:

strTemp = vsSeite.PageSheet.Cells("User.Berechnung" & (k + 5)).ResultStrU("")

Allerdings: die zweite Korrektur habe ich übersehen:

strTempWert = Split(strBerechnung(i), "|")(k + 1)

muss heißen:

strTempWert = Split(strBerechnung(i), "|")(k + 5)

Und warum ist das lange Zeit nicht aufgefallen? Weil diese Fälle (die Verarbeitung der ersten Sektion oder die Benutzung des zweiten Block) nicht verwendet wurden …

Kommentar hinterlassen , , ,

Jahrelang wurde Kindern beigebracht für schönes Wetter alles aufzuessen. Und was haben wir jetzt? Dicke Kinder und Hitzewelle!

VBA-Befehle

Erstaunlich! Aber erklärbar!

Ich erstelle eine Reihe an Werten, mit denen ich weiterrechne – beispielsweise „+“:

Ich lösche zwei Zeilen. Der Fehler #BEZUG! ist die Folge:

Ich kopiere diese fehlerhafte Zeile an einen andere Stelle und füge sie als Inhalt ein:

Ich lösche mit dem Ersetzen Befehl, indem ich #BEZUG! durch „nichts“ ersetze:

Klappt – alles weg. Sowohl in den Formeln (=#BEZUG!+#BEZUG!+D1) als auch in den Zellen

Ich zeichne diese Aktion mit dem Makrorekorder auf:

Der Code:

    Cells.Replace What:="#BEZUG!", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Das Ergebnis:

Nichts wird gelöscht! Also doch per Hand. Beispielsweise so:

Sub Bezug_Loeschen_02()
     Dim xlZelle As Range
     For Each xlZelle In ActiveSheet.UsedRange
        If xlZelle.Text = "#BEZUG!" Then
           xlZelle.ClearContents
       End If
    Next
End Sub

Der Grund ist verständlich: Der Suchen- und Ersetzen-Befehl ist sehr mächtig. So wird auch in „26.10.2020“ der Wert „2020“ gefunden, obwohl in der Zelle eigentlich der Wert 44.130 steht. Eben: #BEZUG! ist eigentlich ein Fehlerwert (und kein Text). Er steht ja auch in der Mitte der Zelle. Suchen und ersetzen „übergeht“ diesen Datentyp; der VBA-Befehl Replace sucht (und ersetzt) einen Text (was nicht in der Zelle steht).

Und wer nun fragt: „Wer braucht denn so etwas?“ Letzte Woche habe ich eine Datei mit fehlerhaften Bezügen bekommen. Der Anwender hat ein Blatt von einer Datei in eine andere kopiert. Ich wollte diese nichtssagenden Fehler mit einem Makro „rausputzen“ – Klick auf Button sollte die Datei „bereinigen“.

Und so habe ich festgestellt, dass der Replace-Befehle nicht geeignet ist.

Kommentar hinterlassen , , ,

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

Excel-Objekte, VBA-Befehle

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:

Kommentar hinterlassen , , , , ,

Bei meinem Heiligenschein blinkt schon wider die Inspektionsleuchte.

VBA-Befehle

Ach, wie dumm. Eine Fehlermeldung in meinem VBA-Projekt:

Der Grund? Ich lese den Value der Zelle aus. Die Fehlermeldung #NV ist allerdings kein Value. Mit der Eigenschaft „Text“ kann man sie abfangen.

Und wie kommt diese Fehlermeldung nach Excel?

Nun – die Daten wurden aus einer (Access-)Datenbank gezogen. Diese wiederum holt sich die Informationen von Visio. Und dort wurde – durch ungeschicktes Kopieren und Einfügen der Fehler #NA erzeugt. Er wurde in die Datenbank als Text eingetragen – allerdings bei Excel ist ein Fehler die Folge.

2 Kommentare , , ,

Mein Mann macht jetzt Home-Office. Ich stelle fest: erstens: er kann sprechen. Zweitens: er kann nett sein!

VBA-Befehle

Ich habe ja schon mehr als einmal über verbundene Zellen geschimpft. Ich tue es heute mal wieder!

Ich bin gestern beim VBA-Programmieren (mal wieder) über verbundene Zellen gestolpert. Ich muss wissen, wie viele Spalten die Liste hat (weil ich in Zeile 4 den Begriff „Summe“ suche).

Der Befehle
MsgBox ThisWorkbook.ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column
liefert 1!
Allerdings
MsgBox ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Columns.Count
liefert 9! Ebenso:
MsgBox ThisWorkbook.ActiveSheet.UsedRange.Columns.Count

Kommentar hinterlassen , ,

Ein Tag ohne „excel-nervt“ ist zwar möglich – aber wer will das schon?

VBA-Befehle

Frage an den Experten: „Bei einigen der erstellten Exceltools hätte ich gerne den VBA Code geschützt. Aber leider ist  der VBA Codeschutz sehr schwach, so dass ich es unterlassen habe und auch keine externen Programme hierfür erworben habe. Einem Kunden eine Exceldatei als .exe zu übergeben ist wohl nicht gerade das Richtige 😉

Letzthin habe ich aus Neugierde den Exactplanner zu Testzwecken heruntergeladen. Und wie ich bin, schaue ich mir immer zuerst mal an, ob die Datei auch VBA Code hat und wie der aussieht.

Aber diesmal habe ich eine Überraschung erlebt.

«Das Projekt kann nicht angezeigt werden!»

Oder war es «das Modul kann nicht angezeigt werden»?

Leider kann ich keinen Screenshot zeigen, da meine Testzeit abgelaufen ist.

Jedenfalls stand im Modulname ManInWeb. Nach einigem Googlen fand ich zwar heraus, dass du ManInWeb bist, aber nicht, wie man VBA-Projekte narrensicher schützen kann.“

Hallo,

Ja, ich hatte eine Zeit lang meine VBA-Projekte geschützt, wo dann erscheint „Das Projekt kann nicht angezeigt werden.“
Das ist eigentlich ganz einfach: Unviewable+ ist das Stichwort. Findest Du hier:

https://www.spreadsheet1.com/unviewable-vba-project-app-for-excel.html

Kostet allerdings etwas. Ich hatte damals an der Beta und den Übersetzungen mitgewirkt. Mittlerweile kann zwar Unviewable+
auch geknackt werden, der Schutz ist aber immer noch so hoch, dass in geschätzt 99 % der Fälle keiner weiss, wie. Geht auch
nur über Zusatztools und nicht den DPB-Trick. Der Vorteil von Unviewable+ ist, dass es keiner externen Tools für die geschützte
Datei mehr braucht, also z.B. das Erstellen von Exe-Dateien usw. nicht nötig ist.

Jep, ich bin in Foren und generell mit dem Nicknamen „Maninweb“ bekannt. Fun fact, da gibt’s eine Story dahinter: kennst Du
den Film MenInBlack (1)? Damals, hatten meine Frau und ich nach einem Domainnamen gesucht und in Analogie zum Filmtitel
dann Maninweb und Womaninweb ersonnen. Tja, dabei ist’s geblieben – wenn auch nun nur als Nicknames.

Excel & VBA ist so seit Mitte der Neunziger mein Ding. Vorher habe ich in Pascal, C, C++ inkl. OOP DOS und Windows-Apps
geschrieben. Und dann Excel & VBA entdeckt und bin seitdem dabei geblieben – seit 2005 dann auf freiberuflicher Basis.
In der Regel beinhalten meine Excel-Anwendungen VBA, sind meist multilingual verfügbar und haben eine Datenbank-
Anbindung im Hintergrund. Ab und zu mache ich auch Schulungen (Excel), meist Inhouse.

Beste Grüße aus Aachen,
Mourad Louha

3 Kommentare ,

Wie viel Alkohol im Blut muss man haben, damit die nervigen Stechmücken daran sterben? Ich geh jetzt in die Offensive!

VBA-Befehle

Hallo Rene,

Wie lautet denn der Befehl, zu prüfen, ob es in einer Datei Verknüpfungen gibt? Müsste irgendwas mit .LinkSources sein, aber wissen tu ichs nicht…

Viele Grüße, Dominic

Hallo Dominic,

ja LinkSource. Ich habe damals überprüft:

Application.EnableEvents = False ‚ — Falls die zu öffnende Datei Autostart-Makros enthält, sollen diese nicht geöffnet werden.
Err.Clear
Application.DisplayAlerts = False‘ — bei Excel 4.0-Dateien erscheint ein Warnhinweis!
Set xlDateiZumÖffnen = Application.Workbooks.Open(objDatei.Path, UpdateLinks:=0, Password:=“Renes Quatschwort“) ‚ — Datei öffnen
Application.DisplayAlerts = True‘ — bei Excel 4.0-Dateien erscheint ein Warnhinweis!
‚ xlDateiZumÖffnen.UpdateLinks = xlUpdateLinksNever ‚ — schalte die Warnmeldungen aus, wenn Fehler kommen, die darauf hinweisen, dass Links nicht vorhanden sind. -> leider nein!
If Err.Number = 0 Then
If Not VBA.IsEmpty(xlDateiZumÖffnen.LinkSources(xlExcelLinks)) Then
For i = 1 To UBound(xlDateiZumÖffnen.LinkSources(xlExcelLinks))
xlZelleZeiger.Offset(0, 6 + i).Value = xlDateiZumÖffnen.LinkSources(xlExcelLinks)(i)
Next
If UBound(xlDateiZumÖffnen.LinkSources(xlExcelLinks)) > 0 Then
xlZelleZeiger.Offset(0, 6).Value = _
UBound(xlDateiZumÖffnen.LinkSources(xlExcelLinks))
If Me.chkKaputt.Value = True Then
For i = 1 To UBound(xlDateiZumÖffnen.LinkSources(xlExcelLinks))
If VBA.Dir(xlDateiZumÖffnen.LinkSources(xlExcelLinks)(i), vbNormal) = „“ Then
xlZelleZeiger.Offset(0, 5).Interior.Color = vbBlack
End If
Next
End If
End If ‚ — trage die Anzahl der gefundenen Links ein und markiere schwarz
End If

[…]

An anderer Stelle überprüfe ich die Links (sie könnten ja „kaputt“ sein:

blnDateiSchutz = False
strLinkKaputt = „“
intAnzahlVerknuepfungen = 0
blnKaputt = False
If Not IsEmpty(xlDateiZumÖffnen.LinkSources(xlExcelLinks)) Then
intAnzahlVerknuepfungen = UBound(xlDateiZumÖffnen.LinkSources(xlExcelLinks))
For i = 1 To intAnzahlVerknuepfungen
strLink = xlDateiZumÖffnen.LinkSources(xlExcelLinks)(i)
If VBA.Dir(strLink, vbNormal) = „“ Then
strLinkKaputt = strLinkKaputt & „###“ & strLink
If strLink Like „“ & Me.txtQuelle.Value & „“ Then
‚ — alles paletti
Else
blnKaputt = True
End If
End If
Next
End If

Liebe Grüße

Rene

… viele Links …
Kommentar hinterlassen

Nur noch ein paar Stunden blöd gucken und dann ist Feierabend.

Datenaustausch, Fehlermeldungen, VBA-Befehle

Hallo Rene,

vielleicht ist das so ein Ding, was du direkt weißt. Ich öffne via Makro eine Datei. In dieser Datei befinden sich Verknüpfungen zu externen Mappen, die zwar kein Mensch braucht, aber die nun mal drin sind, weil die Dateien immer vom Kunden kommen und der damit wer weiß was macht. 😉

Beim öffnen erscheint immer dieser Hinweis und das Makro läuft natürlich nicht weiter:

Kriegt man das irgendwie weg? Bzw. gibt es einen Befehlt der automatisch „Aktualisieren“ oder „Nicht aktualisieren“ auswählt?

Application.DisplayAlerts = False greift hier nicht.

Danke dir und viele Grüße,

Dominic

####

Hat sich schon erledigt – UpdateLinks:=0 nach dem „Open“-Befehl. Manchmal ist der Makro-Rekorder schon ganz praktisch.

####

Hallo Dominic,

ich muss nur ein bisschen warten – und schon lösen die Leute alleine ihre Probleme.

Ich hätte es trotzdem gewusst.

Hintergrund: Die IT einer großen Behörde beschließt im Frühjahr 2018 das Laufwerk P von allen Anwendern zu löschen. Ab jetzt soll es nur noch Q geben. Jeder Anwender soll seine Dateien von P nach Q kopieren, dabei anschauen, ob er die Dateien noch braucht …

Nach fünf Monaten haben sie festgestellt: ups – einige Tausend Dateien haben ja Verknüpfungen auf andere Dateien auf Laufwerk P. Dumm! Die Verknüpfungen funktionieren nicht mehr.

Ich habe ihnen ein VBA-Tool geschrieben:

* liste alle (Excel-)Dateien auf

* Anwender wählt einen Ordner und legt fest welcher Ordner durch welchen ersetzt werden all. Bspw.: P:\Eigene Dateien\Controlling\Excel\2017 durch Q:\Eigene Dateien\ Controlling\Excel\2017

* öffne alle Excelmappen in diesem Ordner (und Unterordner – kann ausgewählt werden)

* prüfe, ob Verknüpfungen drin sind (in Tabellenblättern, Namen, Bedingten Formatierungen, Datenprüfungen) und putze

* speichere und schließe

Problem beim Öffnen:

* Verknüpfungen (wie du beschreibst)

* AutoOpen-Makros

* geschützte Dateien oder Blätter (mit oder ohne Kennwort)

uff!

Einige Programmierstunden, einige Nachbesserungen, … am Ende habe ich nie mehr etwas gehört … wahrscheinlich konnten sie alle (?) Dateien öffnen und putzen.

Liebe Grüße Rene

Kommentar hinterlassen ,

So viele Spiegel in der Wohnung – und keiner funktioniert richtig!

Makrorekorder, VBA-Befehle

Hallo René

Interessant finde ich, dass man erst googlen muss, um herauszufinden, dass die Inversmatrix in Excel als Funktion MINV heißt und bei VBA als Worksheetfunction MINVERSE (sogar mit E am Schluss, da kommst echt net drauf von allein)…

Hallo Axel,

Zwei Tipps, damit die nicht googln musst, was MINV heißt:

Der Translator von Mourad Louha (excel-translator.de) Oder du verwendest die Formel, schaltest den Makrorekorder ein, editierst die Formel (bei mir: [F2]) und Makrorekorder STOPP. Er „übersetzt“ dann die Funktionen ins Englische.

Übrigens: Schöne Skizze:- da steckt viel Hirnschmalz drin …

Kommentar hinterlassen , , ,

Ein Tag hat 24 Stunden. Eine Palette Bier hat 24 Dosen. Das kann kein Zufall sein!

VBA-Befehle

Hallo René,

du bist ein Ass, danke, nun weiß ich wie man die einzelnen Elemente anspricht, nach Zeile und Spalte und habe meinen Code angepasst.

aber: in meinem Fall markiere ich ja vor Eingabe der UDF wie immer bei Matrixfunktionen 3 Zellen untereinander und möchte, dass 3 Ergebnisse aus einem neuen Array, das mit den gewonnenen Variablen arbeitet erscheint

Hallo Axel,

Ich habe die Array verkleinert … das Überwachungsfenster hat mir verraten, dass Matrix1(0, x) und Matrix2(0,x) nicht belegt sind.

Public Function Test1(S1 As Variant, R1 As Variant, S2 As Variant, R2 As Variant, R3 As Variant)
 
' Funktionsbeschreibung: Berechnet den Schnittpunkt einer Geraden mit einer Ebene,
' Inputselektion:  5 zusammenhŠngende Zellbereiche (S1,R1,S2,R2,R3)

' Variablen deklarieren
 Dim S1x As Double
 Dim S1y As Double
 Dim S1z As Double
 
 Dim R1x As Double
 Dim R1y As Double
 Dim R1z As Double
 
 Dim S2x As Double
 Dim S2y As Double
 Dim S2z As Double
 
 Dim R2x As Double
 Dim R2y As Double
 Dim R2z As Double
 
 Dim R3x As Double
 Dim R3y As Double
 Dim R3z As Double
 
 Dim x1 As Double
 Dim x2 As Double
 Dim x3 As Double
 
 Dim Loesung()
 Dim r As Double
 
 Dim ReturnArray(3)
 Dim DoTranspose As Boolean
 
 Dim Matrix1(2, 2) As Double ' (kann man das so deklarieren (Anzahl Zeilen/Spalten der Matrix)?
 Dim Matrix2(2, 0) As Double
 
 
 ' Bestimmen, ob Inputbereich horizontal oder vertikal ist
 If Application.Caller.Rows.Count > 1 Then
 DoTranspose = True
 Else
 DoTranspose = False
 End If
 
 
' Werte aus Inputselektion (Vektorkoordinaten und neue LŠnge) holen
 S1x = S1.Cells(1).Value
 S1y = S1.Cells(2).Value
 S1z = S1.Cells(3).Value
 
 R1x = R1.Cells(1).Value
 R1y = R1.Cells(2).Value
 R1z = R1.Cells(3).Value
 
 S2x = S2.Cells(1).Value
 S2y = S2.Cells(2).Value
 S2z = S2.Cells(3).Value
 
 R2x = R2.Cells(1).Value
 R2y = R2.Cells(2).Value
 R2z = R2.Cells(3).Value
 
 R3x = R3.Cells(1).Value
 R3y = R3.Cells(2).Value
 R3z = R3.Cells(3).Value
 
 'Matrizes bestimmen
 '3x3 Matrix
 Matrix1(0, 0) = R1.Cells(1).Value
 Matrix1(1, 0) = R1.Cells(2).Value
 Matrix1(2, 0) = R1.Cells(3).Value
 
 Matrix1(0, 1) = R2.Cells(1).Value * (-1)
 Matrix1(1, 1) = R2.Cells(2).Value * (-1)
 Matrix1(2, 1) = R2.Cells(3).Value * (-1)
 
 Matrix1(0, 2) = R3.Cells(1).Value * (-1)
 Matrix1(1, 2) = R3.Cells(2).Value * (-1)
 Matrix1(2, 2) = R3.Cells(3).Value * (-1)
 
 '1x1 Matrix
 Matrix2(0, 0) = S2.Cells(1).Value - S1.Cells(1).Value
 Matrix2(1, 0) = S2.Cells(2).Value - S1.Cells(2).Value
 Matrix2(2, 0) = S2.Cells(3).Value - S1.Cells(3).Value
 
 
 ' Berechnungen
 
 ' r berechnen
 
 Loesung = Application.WorksheetFunction.MMult(Application.WorksheetFunction.MInverse(Matrix1), Matrix2)
 
 r = Loesung(1, 1)
 
 MsgBox r

 ' Ergebnis berechnen (Koordinaten des Schnittpunkts nder Geraden mit der Ebene)
 ReturnArray(0) = S1x + r * R1x
 ReturnArray(1) = S1y + r * R1y
 ReturnArray(2) = S1y + r * R1y


' Output transponieren horizontal zu vertikal oder umgekehrt, falls n_tig
 If DoTranspose Then
 Test1 = Application.WorksheetFunction.Transpose(ReturnArray)
 Else
 Test1 = ReturnArray
 End If

' Ergebnis erscheint in den 3 Output-Zellen

End Function

was habe ich gestern gemacht? Wir haben ja herausgefunden, dass in der „Lösungs“-Zeile der Fehler steckt.

Ich habe das Datenfeld Matrix markiert und das Überwachungsfenster eingeschaltet. Dort habe ich festgestellt, dass der Wert an der Position 0 nicht belegt ist:

Kommentar hinterlassen , ,

Da waren sie wieder – meine drei Probleme: Vergesslichkeit, Dings und das Andere.

VBA-Befehle

Lieber René,

ich hoffe, es geht dir gut. Darf ich dir eine kurze Frage VBA stellen? Folgende Situation:

Ich habe eine geodätische Kuppel gebaut und möchte ein Gleichungssystem mit 3 Gleichungen und 3 Unbekannten zu lösen:

Function GleichungsSystemMatrix3x3und1x3Parameter(Matrix1 As Range, Matrix2 As Range) As Variant
GleichungsSystemMatrix3x3und1x3Parameter = Application.WorksheetFunction.MMult(Application.WorksheetFunction.MInverse(Matrix1), Matrix2)
End Function

Und jetzt kommts: Da kommen also 3 Parameter r, t und w raus (so will ich sie später nennen) und die hätte ich gerne in Variablen geschrieben und eben nicht gleich in die Excelzellen, wie das der obige Code halt macht.

Hättest du eine Idee und Lust zu helfen?

LG, Axel

moin Axel,

du musst die Arrays richtig zusammenbauen, dann klappt es. Das Überwachungsfenster hat mir geholfen.

Sub LösungBerechnen()
Dim Matrix(2, 2) As Double
Dim Lö1(2, 2) As Double
Dim R1 As Double
Dim R2 As Double
Dim R3 As Double
Dim T1 As Double
Dim T2 As Double
Dim T3 As Double
Dim W1 As Double
Dim W2 As Double
Dim W3 As Double
Dim L1 As Double
Dim L2 As Double
Dim L3 As Double
R1 = 5
R2 = 3
R3 = 2
T1 = -1
T2 = 2
T3 = 2
W1 = 7
W2 = 5
W3 = 8

L1 = 3
L2 = 4
L3 = 1

Matrix(0, 0) = R1: Matrix(1, 0) = R2: Matrix(2, 0) = R3
Matrix(0, 1) = T1: Matrix(1, 1) = T2: Matrix(2, 1) = T3
Matrix(0, 2) = W1: Matrix(1, 2) = W2: Matrix(2, 2) = W3

Lö1(0, 0) = L1: Lö1(1, 0) = L2: Lö1(2, 0) = L3

MsgBox GleichungsSystemMatrix3x3und1x3Parameter_L1(Matrix, Lö1)
MsgBox GleichungsSystemMatrix3x3und1x3Parameter_L2(Matrix, Lö1)
MsgBox GleichungsSystemMatrix3x3und1x3Parameter_L3(Matrix, Lö1)

End Sub

Function GleichungsSystemMatrix3x3und1x3Parameter_L1(Matrix1 As Variant, Matrix2 As Variant) As Double
Dim Lösung As Variant
Lösung = Application.WorksheetFunction.mmult(Application.WorksheetFunction.MInverse(Matrix1), Matrix2)
GleichungsSystemMatrix3x3und1x3Parameter_L1 = Lösung(1, 1)
End Function

Function GleichungsSystemMatrix3x3und1x3Parameter_L2(Matrix1 As Variant, Matrix2 As Variant) As Double
Dim Lösung As Variant
Lösung = Application.WorksheetFunction.mmult(Application.WorksheetFunction.MInverse(Matrix1), Matrix2)
GleichungsSystemMatrix3x3und1x3Parameter_L2 = Lösung(2, 1)
End Function

Function GleichungsSystemMatrix3x3und1x3Parameter_L3(Matrix1 As Variant, Matrix2 As Variant) As Double
Dim Lösung As Variant
Lösung = Application.WorksheetFunction.mmult(Application.WorksheetFunction.MInverse(Matrix1), Matrix2)
GleichungsSystemMatrix3x3und1x3Parameter_L3 = Lösung(3, 1)
End Function

Liebe Grüße :: Rene

Kommentar hinterlassen , , , ,

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!

Excel-Objekte, Outlook nervt auch, VBA-Befehle

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!

Kommentar hinterlassen , , , , ,

Ich habe mir vor vier Woche das Buch „reich werden durch Betrug“ online bestellt und bezahlt. Es kam bis heute noch nicht an.

VBA-Befehle

Hallo Herr Martin,

ich wollte gerade Inhalte zwischen zwei Dokumenten kopieren mittels Kopieren / Einfügen von Zellinhalten.

Dies funktioniert jedoch leider nicht. Ich nutze die aktuelle Datei.

Ist dies unterbunden?

Kopieren – und ….
… nichts geht mehr“

Hallo Herr H.,

geht doch!? Ich weiß gar nicht was Sie wollen?

Schauen Sie selbst …

*lach*

Und hier die Auflösung:

Erinnern Sie sich an die „unschöne“ Formel, die in der Bearbeitungsleiste angezeigt wird? Ich habe die Bearbeitungsleiste ausgeschaltet.

Wenn man nun in eine andere Mappe wechselt wäre es doof, wenn DORT auch die Bearbeitungsleiste ausgeblendet ist. Also schalte ich beim Wechseln in eine andere Datei diese Leiste zur Sicherheit ein:

Private Sub Workbook_Activate()
Application.DisplayFormulaBar = False ' -- Bearbeitungleiste
End Sub

Private Sub Workbook_Deactivate()
Application.DisplayFormulaBar = True ' -- Bearbeitungleiste
End Sub

Sie wissen, dass man in Excel nach dem Kopieren sofort einfügen muss. Wenn man etwas anderes dazwischen macht, löscht Excel den Zwischenspeicher.

Eben – und HIER machen wir etwas anderes dazwischen. Ich sollte die Datei ändern … Mache ich gleich.

Kommentar hinterlassen , , ,

Wenn du mich mit deinem Auto beeindrucken willst, sollte es ein Eiswagen sein.

VBA-Befehle

Hallo Herr Martin,

ich hoffe, Sue haben / hatten ein erholsames Wochenende.

Ich habe an meiner Prozesslandkarte weitergebastelt. Hierzu möchte ich den Abonnenten der Prozesslandkarte einen vereinfachten Interviewfragebogen zur Verfügung stellen. Diesen möchte ich gerne mit meinem Namen und / oder Logo versehen. Dieses soll für Nicht-Excel-Profis nicht löschbar sein. Also: Ich möchte ein Copyright Vermerk der nicht einfach herausgelöscht werden kann.

Hallo Herr H.,

Tippen Sie in eine beliebigen Zelle

=copyright

Achtung: OHNE KLAMMER!

Dann erscheint Ihr Name. DAS findet keiner wohin ich das versteckt habe.

Möchten Sie so etwas?

Und: was bekomme ich, wenn ich Ihnen verrate, wie ich das in diese Datei reinbekommen habe (und Sie es wieder rausbekommen)?

Hallo Herr Martin,

das ist ja cool!

Ich weiß schon, warum ich das mit Ihnen mache!

Aber jetzt mal raus mit der Sprache …. Wie geht das?

Hallo Herr Hämmerle,

kennen Sie Namen in Excel? Über den Namensmanager in der Registerkarte Formeln oder über das Namensfeld neben der Bearbeitungsleiste kann man einen Namen sehen und erstellen.

Über ein kleines Makro kann man einen unsichtbaren Namen definieren:

Sub MacheCopyright()

    ThisWorkbook.Names.Add Name:="copyright", RefersTo:="compurem Consulting", Visible:=False

End Sub

Über ein Makro (und nur ein Makro) kann man es wieder löschen.

Beim Speichern der Datei fragt Excel, ob Sie die Makros speichern wollen – die Antwort lautet: NEIN!

Hallo Herr Martin,

ich habe das Copyright-Makro in einem anderen Excel genutzt – klappt prima!

Kommentar hinterlassen , , ,

Ich bin ne Raupe / du bist ein Reh. / Ich werde ein Schmetterling, / und du wirst Filet.

VBA-Befehle

Per Programmierung öffne ich eine andere Datei:

Workbooks.Open "D:\Bilanz.xlsm"

Ich möchte nicht, dass der Anwender dies sieht. Also verwende ich vor dem Öffnen den Befehl 

Application.ScreenUpdating = False

Ich führe einige rechenintensive Operationen durch, die einige Sekunden (Minuten?) in Anspruch nehmen:

For i = 1 To 1000000
    strInhalt = ActiveSheet.Cells(i, 1).Value
Next i

Damit der Anwender sieht, dass etwas passiert, schreibe ich einen Text in die Statuszeile:

Application.StatusBar = "Excel nervt! - Bitte warten Sie ...    "

Was sieht man? Richtig: Gar nichts! Weil ich zuvor die Bildschirmaktualisierung ausgeschaltet habe. Zum Glück gibt es den Befehl

DoEvents

Damit klappt es wieder!

Kommentar hinterlassen , ,

Ich: „Es geht nicht darum, wie häufig du fällst, sondern wie häufig du wieder aufstehst.“ Polizist: „So funktionieren aber Alkoholtests nicht.“

VBA-Befehle

Seltsam. Wenn ich VBA programmiere, verwende ich NIE die Befehle Activate oder Select. Mit zwei Ausnahmen: ich programmiere Spunganweisungen: „wechsle für den Anwender auf ein bestimmtes Blatt oder auf eine bestimmte Zelle“. Oder: am Ende des Programms soll der Cursor auf einem bestimmten Blatt und/oder auf einer bestimmten Zelle sitzen. Ich setze Verweise auf Zellen:

Sub Kopieren_und_Fertig()
     Dim xlZelle As Range
     Dim i As IntegerSet

     Set xlZelle = ActiveSheet.Range("C2")

     For i = 1 To 50
         xlZelle.Copy Destination:=xlZelle.Offset(i, 0)
     Next

     MsgBox "fertig"

Ich starte das Makro von Excel aus:

Was passiert? man siehst nichts:

Auch wenn der Verweis auf ein anderes Tabellenblatt gesetzt wird:

Set xlZelle = ThisWorkbook.Worksheets(2).Range(„C2“)

Für den Befehl Inhalte einfügen sind zwei Zeilen Code nötig:

With xlZelle.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="m,w,d"
End With

For i = 1 To 50
    xlZelle.Copy
    xlZelle.Offset(i, 0).PasteSpecial Paste:=xlPasteValidation
    Application.CutCopyMode = False
Next i

MsgBox "fertig"

Was geschieht hier:

Der Cursor wandert über den Bildschirm.

Ein Zucken ist auch am Bildschirm zu sehen, wenn die Inhalte auf einem anderen Tabellenblatt eingefügt werden:

Set xlZelle = ThisWorkbook.Worksheets(2).Range(„C2“)

Was tun? Klar: Die Bildschirmaktualisierung ausschalten. Dann funktioniert es! nichts zuckt; nichts zeigt sich …

Application.ScreenUpdating = False

Kommentar hinterlassen , , , , , , ,

Menschen, die mich vor 9 Uhr fragen, wie es mir geht, schauen auch mit dem Streichholz nach, ob noch Benzin im Tank ist.

VBA-Befehle

Na, ein bisschen mehr Mühe hätte sich VBA schon geben können:

Anwendungs- oder objektorientierter Fehler. Na toll! Und wo? Und was?

Der Debugger hilft: die Berechnung Row – 8 ergibt einen falschen, nämlich negativen Wert (falsch gerechnet; nicht aufgepasst!) – so kann die Zelle „C-1“ nicht ermittelt werden.

Auch hier: Typen unverträglich!

Ey, VBA: sag mir doch deutlich, dass ich Dumpfbacke bei der Funktion MATCH (VERGLEICH) die beiden Parameter vertauscht habe. Während die Funktion der Zeile darüber (COUNTIF, ZÄHLENWENN) die Parameter ich suche wo wen verlangt, ermittelt MATCH / VERGLEICH: ich suche wen wo. Und keiner hilft mir! *lach*

Kommentar hinterlassen , , , , , , ,

Das Fitnessstudio habe ich bezahlt. Also sollte ich auch hingehen. Andererseits: das Sofa war auch nicht billig.

VBA-Befehle

Wenn ich in VBA eine Zelle oder einen Zellbereich kopieren oder ausschneiden möchte, verwende ich die Methoden Copy, beziehungsweise Cut mit dem optionalen Parameter Destination, also beispielsweise:

ActiveCell.Copy

oder auch

ActiveCell.Copy

ActiveCell.Copy Destination:=ActiveCell.Offset(1, 0)

Wenn ich nun die Methode PasteSpecial (Inhalte einfügen) verwende, muss ich zwei Befehle schreiben:

ActiveCell.Copy
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValidation

beispielsweise um die Datenüberprüfung zu kopieren. Danach läuft um die kopierte Zelle eine „Ameisenlinie“:

Das würde nicht weiter stören – sie verschwindet bei den weiteren Befehlen. Aus ästhetischen Gründen und zur Sicherheit (es kann zu Problemen führen, wenn der Kopiermodus noch aktiv ist), schalte ich den Laufrahmen aus. Hierbei hilft

Application.CutCopyMode = False

Das Erstaunliche:

IntelliSense zeigt die Parameter False und True nicht an. Auf der Seite

https://docs.microsoft.com/de-de/office/vba/api/excel.application.cutcopymode

werden sie genannt:

Ein Kommentar , , , , , , , ,

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

Excel-Objekte, VBA-Befehle

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).

2 Kommentare , , ,

Wir werden die erste Generation sein, die eine Handyhalterung am Rollator hat.

Userforms, VBA-Befehle

Ich erstelle ein großes, komplexes Formular (UserForm) in VBA, starte es zum Testen und:

Unerwartetes Dateiende.

Etwas differenziertes dürfte es schon sein. Vor allem dürfte der Debugger starten, der mir die Zeile kennzeichnet, in der der Fehler erzeugt wurde.

PS: Der Fehler kam dadurch zustande, dass ich ein Objekt adressiert hatte, das nicht in einer Sammlung vorhanden war. Ich habe den Fehler im Einzelschrittmodus gefunden.

Kommentar hinterlassen ,

Rettet die Erde, auf anderen Planeten gibt es keine Schokolade.

VBA-Befehle, Word nervt auch

Anruf eines verzweifelten Anwenders. Die Bank, für die er arbeitet, stellt um auf Office 365. Ich glaube von Office 10. Einige Makros in Word funktionieren nicht mehr. Er zeigt es mir online. Altes System: Läuft. Neues System: Nichts läuft.

Ich schaue es mir an und finde sehr schnell:

Ein Modul AutoNew mit einer Main-Prozedur. Mann – das ist sowas von 1997! Ist mir noch nicht aufgefallen – dass diese uralt Automakros nicht mehr funktionieren. Nein – da verwenden wir doch die Ereignisse „Document_Open()“ und „Document_New()“. Damit klappt es dann auch. Der Anwender war glücklich!

Kommentar hinterlassen , , , ,

Hunde geben einem so viel zurück – beispielsweise Blumenzwiebeln, die man letzte Woche eingepflanzt hat.

VBA-Befehle

Ich erstelle einen Dialog und erstelle eine Schaltfläche, die es ermöglicht, die Werte zu sortieren:

Ich verwende eine Code, den ich im Internet gefunden habe:

Private Sub cmdSort_Click()
     Dim i As Integer
     Dim listMatrix As Object, varDaten As Variant
     Set listMatrix = CreateObject("System.Collections.ArrayList")

For i = 0 To Me.lstAuswahl.ListCount - 1
    listMatrix.Add Me.lstAuswahl.List(i)
Next i

listMatrix.Sort
Me.lstAuswahl.Clear ' -- Auswahlliste leeren

For Each varDaten In listMatrix
    Me.lstAuswahl.AddItem varDaten
Next varDaten

If Me.lstAuswahl.ListCount > 0 Then
    Me.lstAuswahl.ListIndex = 0
End If

Set listMatrix = Nothing
End Sub

Klappt wunderbar – allerdings:

ArrayList ist ein Objekt von .NET-Framework 3.5. Ist dieses nicht auf einem Rechner installiert, funktionieren die Befehle der Klasse auch nicht:

Also – raus damit – und die „klassische“ Variante:

Private Sub cmdSort_Click()
Dim intLast As Integer, intNext As Integer
Dim strTemp As String
With Me.lstAuswahl
For intLast = 0 To .ListCount – 1
For intNext = intLast + 1 To .ListCount – 1
If .List(intLast) > .List(intNext) Then
strTemp = .List(intLast)
.List(intLast) = .List(intNext)
.List(intNext) = strTemp
End If
Next intNext
Next intLast
End With
End Sub

Kommentar hinterlassen , , ,

Ich beurteile Menschen nicht nach Aussehen, Hautfarbe oder Religion. Sondern wie sie sich benehmen, wenn eine zweite Kasse geöffnet wird.

VBA-Befehle

Das ist bösartig! In einem Exceldokument sollen Werte von Eigenschaftsfeldern, die von SAP kommen, in die Kopfzeile geschrieben werden. Also von:

Nach:

Der Befehl für diese Felder ist schnell gefunden:

ContentTypeProperties(„Target group“)

Das Makro:

Dim strTitle As String
Dim strLocation As String
Dim strTarget As String
Dim strType As String

strTitle = ThisWorkbook.BuiltinDocumentProperties("Title").Value
strLocation = ThisWorkbook.ContentTypeProperties("Location").Value
strTarget = ThisWorkbook.ContentTypeProperties("Target group").Value
strType = ThisWorkbook.ContentTypeProperties("Document type").Value

If strTitle <> "" Then strTitle = " " & strTitle
If strLocation <> "" Then strLocation = " " & strLocation
If strTarget <> "" Then strTarget = " " & strTarget
If strType <> "" Then strType = " " & strType

With ActiveSheet.PageSetup
    .LeftHeader = Replace(.LeftHeader, "Title", "Title" & strTitle)
    .LeftHeader = Replace(.LeftHeader, "Location", "Location" & strLocation)
    .LeftHeader = Replace(.LeftHeader, "Target group", "Target group" & strTarget)
    .LeftHeader = Replace(.LeftHeader, "Document type", "Document type" & strType)
End With

Ein Durchlauf mit leeren Feldern – klappt! Ein Durchlauf mit Daten bringt die Fehlermeldung 13: Typen unverträglich. Ich stutze. Ich untersuche die Inhalte. tatsächlich: die Daten, die aus Sharepoint kommen, sind keine Texte, sondern Datenfelder. Bestehend aus zwei Werten: Inhalt und ID. Sieht man aber nicht:

Nun das kann man abprüfen:

Dim strTitle As String
Dim strLocation As String
Dim strTarget As String
Dim strType As String

If TypeName(ThisWorkbook.BuiltinDocumentProperties("Title").Value) = "String()" Then
    If UBound(ThisWorkbook.BuiltinDocumentProperties("Title").Value) >= 0 Then
        strTitle = ThisWorkbook.BuiltinDocumentProperties("Title").Value(0)
    End If
ElseIf TypeName(ThisWorkbook.BuiltinDocumentProperties("Title").Value) = "String" Then
    strTitle = ThisWorkbook.BuiltinDocumentProperties("Title").Value
End If

If TypeName(ThisWorkbook.ContentTypeProperties("Location").Value) = "String()" Then
    If UBound(ThisWorkbook.ContentTypeProperties("Location").Value) >= 0 Then
        strLocation = ThisWorkbook.ContentTypeProperties("Location").Value(0)
    End If
ElseIf TypeName(ThisWorkbook.ContentTypeProperties("Location").Value) = "String" Then
    strLocation = ThisWorkbook.ContentTypeProperties("Location").Value
End If

If TypeName(ThisWorkbook.ContentTypeProperties("Target group").Value) = "String()" Then
    If UBound(ThisWorkbook.ContentTypeProperties("Target group").Value) >= 0 Then
        strTarget = ThisWorkbook.ContentTypeProperties("Target group").Value(0)
    End If
ElseIf TypeName(ThisWorkbook.ContentTypeProperties("Target group").Value) = "String" Then
    strTarget = ThisWorkbook.ContentTypeProperties("Target group").Value
End If

If TypeName(ThisWorkbook.ContentTypeProperties("Document type").Value) = "String()" Then
    If UBound(ThisWorkbook.ContentTypeProperties("Document type").Value) >= 0 Then
        strType = ThisWorkbook.ContentTypeProperties("Document type").Value(0)
    End If
ElseIf TypeName(ThisWorkbook.ContentTypeProperties("Document type").Value) = "String" Then
    strType = ThisWorkbook.ContentTypeProperties("Document type").Value
End If

If strTitle <> "" Then strTitle = " " & strTitle
If strLocation <> "" Then strLocation = " " & strLocation
If strTarget <> "" Then strTarget = " " & strTarget
If strType <> "" Then strType = " " & strType

With ActiveSheet.PageSetup
' -- schreibe nur rein, falls der Text noch nicht drinsteht.
    If InStr(1, .LeftHeader, "Title" & strTitle) = 0 Then
        .LeftHeader = Replace(.LeftHeader, "Title", "Title" & strTitle)
    End If
    If InStr(1, .LeftHeader, "Location" & strLocation) = 0 Then
        .LeftHeader = Replace(.LeftHeader, "Location", "Location" & strLocation)
    End If
    If InStr(1, .LeftHeader, "Target group" & strTarget) = 0 Then
        .LeftHeader = Replace(.LeftHeader, "Target group", "Target group" & strTarget)
    End If
    If InStr(1, .LeftHeader, "Document type" & strType) = 0 Then
        .LeftHeader = Replace(.LeftHeader, "Document type", "Document type" & strType)
    End If
End With

Und dann klappt es auch:

Kommentar hinterlassen , , , , , ,

Die Tatsache, dass Quallen mehr als 500 Millionen Jahre überlebt haben, obwohl sie kein Gehirn haben, gibt vielen Menschen Hoffnung.

VBA-Befehle

Ich weiß nicht genau warum. Eigentlich ist VBA VBA. Aber manchmal passiert es, dass ich ein Programm schreibe, es einem Kunden weitergebe und sämtliche Funktionen der VBA-Bibliothek nicht erkannt werden, also: Left, Right, Len, InStr, …

Die Lösung: ich setze den Bibliotheksnamen VBA davor, also: VBA.Left, VBA.Right, VBA.Len, VBA.InStr, … Dann läuft es.

Kommentar hinterlassen , ,

Ich hab mich gefragt, was meine Eltern früher ohne Internet gemacht haben. Auch meine 14 Geschwister konnten mir keine Antwort geben.

VBA-Befehle

Hallo Herr Martin,

Hier mal ein Problem an Sie als Excel-Guru.

Wir haben in unserem Dokumentenlenkungssystem eine Vorlage für Excel mit spezifischen Eigenschaften (siehe Bild mit den teilweise kundenspezifischen Eigenschaften).

Damit in Excel beim ausdrucken die wesentlichen Informationen in Kopf und Fußzeile erscheinen, müssen wir das alles sehr aufwändig händisch in allen Tabellenblättern pflegen. Was natürlich sehr fehlerträchtig ist…

Ich habe mit meinen sehr bescheidenen Kenntnissen mal versucht, irgendwie mit VBA da an die Info ranzukommen, so:

Sub Test()

rw = 1

Worksheets(1).Activate

For Each p In ActiveWorkbook.CustomDocumentProperties

    Cells(rw, 1).Value = p.Name

    Cells(rw, 2).Value = p.Value

    rw = rw + 1

Next

End Sub

Im Ergebnis allerdings ohne Erfolg, da kaum Werte so rauslesbar sind, vor allem nicht die spezifischen. Das wäre auch nur die halbe Miete gewesen, da ja das Einbringen der Info in die Kopf- und Fußzeile nochmal ein separates Problem darstellt, für die ich aktuell keine Idee zur Lösung habe… Kennen Sie vielleicht ein paar VBA-Kniffe, wie ich hier vielleicht weiterkomme?

Hallo Herr F.,

und so geht es:

mit

Dim i As Integer

    On Error Resume Next

    For i = 1 To ThisWorkbook.ContentTypeProperties.Count

        MsgBox ThisWorkbook.ContentTypeProperties(i).Name & „//“ & ThisWorkbook.ContentTypeProperties(i).Value

    Next

ermittle ich die von SharePoint gesetzten Werte und Namen.

Mit

    MsgBox ThisWorkbook.BuiltinDocumentProperties(„Title“).Value, , „Titel“

    MsgBox ThisWorkbook.ContentTypeProperties(„Location“).Value, , „Location“

    MsgBox ThisWorkbook.ContentTypeProperties(„Target group“).Value, , „Target group“

    MsgBox ThisWorkbook.ContentTypeProperties(„Document type“).Value, , „Document type“

greife ich auf die Inhalte zu.

Und: Dies kann in

Private Sub Workbook_BeforePrint(Cancel As Boolean)

eingebunden werden:

ActiveSheet.PageSetup.LeftHeader = …

2 Kommentare , , , , ,

Wenn im Wein die Wahrheit liegt, liegt dann im Glühwein die Erleuchtung?

VBA-Befehle

Boah – ist das mies! Zwei Stunden lang habe ich gesucht. Und dann gefunden.

Ich habe eine Datei mit zwei Tabellenblättern. Eines enthält eine Datenliste, ein zweites eine Datenüberprüfung mit einer Liste, die diese Daten aus dem anderen Blatt holt:

Per VBA ziehe ich nun diese beiden Blätter (einzeln!) in eine Masterdatei (man kann es auch per Hand machen. Die Verknüpfung verweist nun auf die alte Datei:

Der Code:

Dim xlFremdeDatei As Workbook
Dim xlEigeneDatei As Workbook
Dim xlFremdesBlattDaten As Worksheet
Dim xlFremdesBlattDatenüberprüfung As Worksheet


Set xlEigeneDatei = ThisWorkbook
Set xlFremdeDatei = Application.Workbooks.Open("D:\Eigene Dateien\Excel\Beispieltabellen\3Musketiere.xlsx")
xlFremdeDatei.Worksheets(2).Copy Before:=xlEigeneDatei.Worksheets(1)
Set xlFremdesBlattDatenüberprüfung = xlEigeneDatei.Worksheets(1)
xlFremdeDatei.Worksheets(1).Copy Before:=xlEigeneDatei.Worksheets(1)
Set xlFremdesBlattDaten = xlEigeneDatei.Worksheets(1)
xlFremdeDatei.Close SaveChanges:=False
xlEigeneDatei.Save

Beide Dateien werden geschlossen, die Masterdatei wird geöffnet. Unter Datei / Informationen wird angezeigt, dass sich in dieser Datei eine Verknüpfung (auf eine andere Datei) befindet, die man hier nicht löschen kann. Klar!

Nun setze ich in der Zelle mit der Datenüberprüfung per Hand oder per VBA die Verknüpfung auf die eigene Datei:

Excel zeigt noch immer (unter Datei / Informationen) an, dass sich in der Datei eine Verknüpfung befindet. Diesen Eintrag kann ich nicht löschen! Erst durch das Schließen und wieder Öffnen der Datei ist er verschwunden.

Das Erstaunliche: werden die Tabellenblätter gelöscht, wird die Verknüpfung nicht angezeigt. Werden die Verknüpfungen „nur“ behoben, bleibt der Eintrag noch in den Informationen stehen.

Zwei Stunden habe ich benötigt, um das herauszufinden. Mies!

Kommentar hinterlassen , , , ,

Mach dich erst einmal unbeliebt – dann wirst du auch nicht mehr erst genommen.

VBA-Befehle

Ich möchte gerne per VBA auf einem geschützten Excel-Formular eine Dropdownliste (Datenüberprüfung) ändern. Okay – man hätte die auch per Formeln mit zwei Dropdownlisten erzeugen können – aber mit meiner VBA-Lösung bin ich flexibler.

Der Code lautet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim intZeilen As Integer

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     Dim intZeilen As Integer
If Target.Value = "Für Standorte" Or Target.Value = "Für Gebäude" Then
    ActiveSheet.Unprotect
        If Target.Value = "Für Standorte" Then
            intZeilen = ThisWorkbook.Worksheets("tbl_Basisdaten").Range("B1").CurrentRegion.Rows.Count
            Target.Value = "Für Gebäude"
            With Target.Offset(0, 1).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=tbl_Basisdaten!$B$2:$B$" & intZeilen ' -- Spalte B bei "Standorten"
            End With
        ElseIf Target.Value = "Für Gebäude" Then
            intZeilen = ThisWorkbook.Worksheets("tbl_Basisdaten").Range("D1").CurrentRegion.Rows.Count - 1
            Target.Value = "Für Standorte"
            With Target.Offset(0, 1).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=tbl_Basisdaten!$D$2:$D$" & intZeilen ' -- Spalte D bei "Gebäuden"
            End With
        End If
        With Target.Offset(0, 1).Validation
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        Target.Offset(0, 1).Value = ""

    ActiveSheet.Protect
End If

End Sub

Ich erhalte einen Fehler:

Klaro – ich schützte das Blatt (ActiveSheet.Protect) und anschließend wirkt der Doppelklick, denn ich verwende ja das Ereignis BeforeDoubleClick. Die Lösung ist einfach: ich setze den Cursor auf eine nicht gesperrte Zelle – dann klappt es:

Target.Offset(0, 1).Activate

Witzigerweise befindet sich DANN der Cursor in keiner Zelle.

Kommentar hinterlassen , , , ,

Das Leben ist nicht immer ein Wunschkonzert… Aber man kann eine andere Playlist wählen!

VBA-Befehle

VBA-Schulung. Wir erstellen eine Datei mit mehreren Tabellenblättern:

Wir schreiben ein kleines Makro, das uns bei der Blattsuche hilft:

Option Explicit
Option Compare Text

Sub BlattSuche()
Dim strBlattname As String
Dim i As Integer

strBlattname = InputBox("Bitte geben Sie den gesuchten Blattnamen ein!")

For i = 1 To ActiveWorkbook.Sheets.Count
    If ActiveWorkbook.Sheets(i).Name = strBlattname Then
        ActiveWorkbook.Sheets(i).Activate
        Exit Sub
    End If
Next

MsgBox "Das gesuchte Blatt " & strBlattname & " wurde nicht gefunden."

End Sub

Bei einer Teilnehmerin funktioniert das nicht:

Die Activate-Methode des Worksheet-Objektes kann nicht ausgeführt werden.

Ich werde stutzig, als ich das Menüband aufklappe:

Da entdecke ich, dass sie noch beim Schreiben eines Tabellenblattnamens ist:

Kommentar hinterlassen , , ,

Die Sterne lügen nicht, aber wie sieht’s mit ihren Deutern aus?

VBA-Befehle

Perfide.

VBA-in-Excel-Schulung. Am dritten Tag beginne ich mit einer Aufgabe: ein Makro soll alle Tabellenblätter schützen; ein zweites Makro den Schutz aufheben. Die Teilnehmer beginnen die Lösung zu erstellen. Eine mögliche Lösung wäre:

Sub AlleBlaetterSchuetzen()
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

Ein Teilnehmer erstellt auf dem ersten Tabellenblatt zwei Schaltflächen dafür:

Das Perfide: Wenn die Datei mehr als zwei Tabellenblätter hat, wechselt Excel beim Blattschutzaufheben zu einem anderen Tabellenblatt (dem vorletzten). Ich weiß nicht warum …

Kommentar hinterlassen ,

Entschuldigt bitte meine Fehler, es ist mein erstes Leben .

VBA-Befehle, Zellformate

Inga meint, dass es gefährlich ist mit VBA bedingte Formatierungen zu programmieren. Warum? Ich zeichne mit dem Makrorekorder auf – wie lautet der Befehl: „färbe die aktuelle Zelle blau, wenn in A1 das heutige Datum steht“?

Sub HeuteMachenWirBlau()
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=“=A1=HEUTE()“
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 12611584
.TintAndShade = 0
End With
End Sub

Ich teste – klappt! Ich überlege mir: es wäre besser statt HEUTE TODAY zu schreiben; teste – klappt nicht mehr.

Das heißt: die Formeln laufen nur lokal – man müsste nun die einzelnen Sprachen unterscheiden, damit das Makro mehrsprachig funktioniert. Das ist die Hölle.

Danke an Inga Birk für den Hinweis.


2 Kommentare , , , ,

Wenn ein Mann geputzt hat, lobst du ihn gefälligst, wartest bis er weg ist und machst dann sauber.

PowerPoint nervt auch, VBA-Befehle

P.S.: Wenn Sie eine Idee haben, wie ich in PowerPoint eine eingebettete Excel-Tabelle (OLEObject?) ansprechen kann, so wäre ich Ihnen äußerst dankbar

Und so geht es: Mit folgendem Code können Sie auf die Tabelle zugreifen:

Dim ppApp As Application
Dim ppDatei As Presentation
Dim ppFolie As Slide
Dim ppShape As Shape
Dim ppObjekt As Object

Set ppApp = Application
Set ppDatei = ppApp.ActivePresentation
Set ppFolie = ppDatei.Slides(11)
Set ppShape = ppFolie.Shapes(3)
Set ppObjekt = ppShape.OLEFormat.Object

MsgBox ppObjekt.Sheets(1).Range("A2").Value
Kommentar hinterlassen , , , , , ,

Ich mag Männer, die Gefühle zeigen. Wenn neben dem Bett eine Taschentuchbox steht, schauen sie sicherlich häufig traurige Liebesfilme.

Makrorekorder, PowerPoint nervt auch, VBA-Befehle

Hallo Herr Martin,

Ich melde mich dieses mal mit einer generellen Frage an Sie. Im Moment nervt mich nämlich PowerPoint. Es ist schon gemein, dass es da keinen Makrorekorder gibt.
Ich schätze mal, dass auch Sie ab und zu den Makrorekorder in Excel benutzen, wenn Ihnen mal dieser und jener Befehl nicht einfällt.

Nun, in PowerPoint geht das ja leider nicht. Daher meine Frage, wie gehen Sie eigentlich vor oder wo lesen Sie nach, wenn Sie PowerPoint-Kommandos benötigen und gerade nicht wissen, wie diese lauten?

Momentan beschäftige ich mich mit einer automatisierten Berichtserstellung aus Excel heraus. Das klappt auch alles soweit ganz gut, also neue Präsi aus Vorlage erstellen, Daten aus Excel in Diagramme oder Tabellen einfügen oder auch Diagramme von Excel nach PowerPoint zu exportieren. Schwierig wird es nur dann, wenn ich z.B. Formatierungen ausschließlich in PowerPoint durchführen muss. Ich habe z.B. ewig gebraucht um dahinter zu kommen, wie ich in einer PowerPoint-Tabelle eine Zelle farblich hervorhebe (Präsi.Slides(5).Shapes(„Inhaltsplatzhalter 3“).Table.Cell(a, 1).Shape.Fill.ForeColor.RGB = RGB(255, 0, 0)). Oder einen Pfeil einfügen (ActivePresentation.Slides(1).Shapes.AddShape Type:=msoShapeBentUpArrow, Left:=50, Top:=50, Width:=100, Height:=200), mit Animationen fange ich gar nicht erst an.

Haben Sie da einen Tipp oder ein schlaues Nachschlagewerk? Jemand hat mir mal erzählt (ich glaube, es war Andreas Thehos), dass man mit dem Makrorekorder in Word hier so einiges ableiten kann, aber da bin ich irgendwie noch nicht dahinter gekommen…

Ihnen schon einmal vielen Dank und viele Grüße,

Hallo Herr Dauphin,

das Problem des fehlenden Makrorekorders kenne ich – darüberhinaus gibt es noch weitere Fallstricke:

Nichts verursacht soviel Unruhe wie Menschen, die sich erholen wollen!

Werfen Sie mal einen Blick auf das Kapitel „PowerPoint“ aus meinen VBA-lernen-Buch – ich habe den Aufbau erklärt.

Und: Manchmal hilft es, wenn man die Objekte „sauber“ deklariert – Intellisense hilft oft mit der Liste der Eigenschaften und Methoden.

Bei Diagrammen und Grafiken „spicke“ ich manchmal bei Excel und verwende dort den Makrorekorder … aber der Teufel steckt im Detail:

„Irren ist männlich“, sprach der Igel und stieg von der Drahtbürste

Hilft Ihnen das?

Kommentar hinterlassen , ,

Laut Physikbuch dehnen sich alle heißen Körper aus. Also bin ich nicht dick, sondern heiß.

Datenaustausch, VBA-Befehle

Guten Tag Herr Martin
Vielen Dank für Ihre ausführlichen Mitteilungen in Ihrer letzten Mail
Inzwischen steht das erste Modul (Flächenmanagement).
Allerdings sind mir dabei Probleme begegnet, die ich nicht lösen konnte.

1.) Zugriff auf den richtigen Visio Prozess.
Der Zugriff auf Visio läuft prima, solange nicht mehrere Visio-Anwendungen laufen.
Hier die entsprechenden Code-Zeilen

Public vsoApp As Visio.Application

Public Sub Set_vsoApp()
‚Prüfen ob eine Visio Application läuft
‚Wenn ja, dann die laufende App als vsoApp definieren
‚Wenn nein dann eine neue Application starten

If GetObject("winmgmts:").ExecQuery("select * from win32_process where name='VISIO.EXE'").Count > 0 Then
    Set vsoApp = GetObject(, "Visio.Application")
Else
    Set vsoApp = CreateObject("Visio.Application")
End If

End Sub

Mit diesen Zeilen gelange ich zu einer Objektvariable vsoApp, deren Dokumente ich durchlaufe und das gewünschte Dokument entweder finde oder öffne.
Wenn nun aber mehrere Applikationen laufen und das gewünschte Dokument dummerweise nicht in der ersten App läuft, so funktioniert der Code nicht mehr. Das gewünschte Dokument wird nicht gefunden und kann auch nicht ein zweites Mal geöffnet werden.

Die Frage lautet darum:
Gibt es eine Möglichkeit die ganze Auflistung
„select * from win32_process where name=’VISIO.EXE'“
zu durchlaufen und einzelnen zu durchsuchen?

#

Hallo Herr M.,

und hier meine Antworten:

1.) Es gibt verschiedene Strategien. Ich habe ein Projekt, da arbeite ich folgendermaßen:
On Error GoTo Fehler
Set vsApp = CreateObject(„Visio.Application“)
[…]
Set vsDatei = vsApp.Documents.Open(strDateiName)
[…]
vsDatei.Save
vsDatei.Close
[…]
Call VisioSchliessen(True)

Set vsDatei = Nothing
Set vsApp = Nothing
Exit Sub

Fehler:
MsgBox „Es trat ein Fehler auf:“ & vbCr & Err.Number & „: “ & Err.Description

Das heißt: ich öffne einfach die Datei. Sollte die Datei schon offen sein, wird ein Fehler erzeugt, die Sprungmarke angesprungen und die Meldung ausgegeben, dass die Datei in Benutzung ist. Der Anwender muss sie zumachen und das Programm erneut starten.

Zweite Variante: Sie greifen mit GetObject auf das bereits geöffnete Visio und auf die bereits geöffnete Datei zu. Sollte sie noch nicht geöffnet sein, wird ein Fehler erzeugt, der verarbeitet wird, indem Visio, bzw. die Datei geöffnet wird:
Dim vsApp As Object
Dim vsDatei As Object
Const PFAD As String = „D:\Eigene Dateien\Räuberhauptmann2.vsdx“

On Error Resume Next

Set vsApp = GetObject(, "Visio.Application")
If Err.Number <> 0 Then
    Err.Clear
    Set vsApp = CreateObject("Visio.Application")
End If

vsApp.Visible = True

Set vsDatei = vsApp.Documents("Räuberhauptmann2.vsdx")
If Err.Number <> 0 Then
    Err.Clear
    Set vsDatei = vsApp.Documents.Open(PFAD)
End If

MsgBox vsDatei.Name

Und zur dritten (Ihrer Lösung) – durchlaufen Sie alle Dokumente von Visio:

Dim i As Integer
Dim blnDateiOffen As Boolean
blnDateiOffen = False
For i = 1 To vsoApp.Documents.Count
    If vsoApp.Documents(i).Name = "Räuberhauptmann2.vsdx" Then
        Set vsoDatei = vsoApp.Documents(i)
        blnDateiOffen = True
    End If
Next

If blnDateiOffen = False Then
    Set vsoDatei = vsoApp.Documents.Open(PFAD)
End If
Kommentar hinterlassen , , ,

„Irren ist männlich“, sprach der Igel und stieg von der Drahtbürste

Merkwürdige Diagramme, VBA-Befehle

Warum macht Microsoft das nicht einheitlich?

Ich erhalte eine Mail mit der Frage, wie man in einem ACCESS-Diagramm einen Datenpunkt mit VBA formatiert.

Da Access keinen Makrorekorder hat und da ich nicht genau weiß, wie der Datenpunkt in VBA heißt und mit welchen Eigenschaften man die Farbe ändern kann, erstelle ich in Excel ein Diagramm, verwende den Makrorekorder und baue den Code ein wenig um:

Der neue Code sieht folgendermaßen aus:

Dim s As Worksheet
Dim c As ChartObject
Dim cc As Chart
Dim f As FullSeriesCollection
Dim p As Point

Set s = ActiveSheet
Set c = s.ChartObjects(1)
Set cc = c.Chart
Set f = cc.FullSeriesCollection
Set p = f(1).Points(1)

p.Format.Fill.ForeColor.RGB = RGB(255, 0, 0)

In Access empfiehlt es sich einen Verweis auf die Objektbibliothek „Microsoft Graph“ einzubinden:

Ich kopiere den Excel-VBA-Code nach Access und bin verblüfft, wie viel ich in Access ändern muss, um zu dem gleichen Ergebnis zu gelangen:

Dim rep As Report

Dim c As Graph.Chart
Dim f As Graph.SeriesCollection
Dim p As Graph.Point

Set rep = Reports(„repDiagramm“)
Set c = rep.Controls(„Diagramm1“).Object
Set f = c.SeriesCollection
Set p = f(1).Points(3)

p.Interior.Color = RGB(255, 0, 0)

Hätte man nicht die gleichen Befehle und Eigenschaften verwenden können? Hätte man sicherlich …

Kommentar hinterlassen

Der nachfolgende Text enthält Produktplatzierungen!

VBA-Befehle

Und ich predige es in allen VBA-Schulungen

  • Keine langen Codezeilen!
  • Nicht zu viele verschachtelte Befehle!
  • Lieber ein paar Variablen zu viel als zu wenig!

Also nicht so:

Halte ich mich selbst daran? Nicht unbedingt!

Und was passiert? Ich soll eine Korrektur in einem Programm vornehmen, das ich vor einigen Monaten geschrieben habe. In der Spalte BC sollen nun auch die Werte übertragen werden. Also schnell den alten Code von oben kopieren, ändern, testen und: staunen. Warum? Klar – an einer Stelle habe ich vergessen BG3 in BC3 umzubenennen – deshalb wird der Bereich nun nicht von BC3:BC300 aufgespannt, sondern von BC300:BG3, also von BC3:BG300. Ich musste eine Weile suchen.

Also:

  • Keine langen Codezeilen!
  • Nicht zu viele verschachtelte Befehle!
  • Lieber ein paar Variablen zu viel als zu wenig!



Kommentar hinterlassen , , ,

„Und wo bist Du gerade?“ „In der Bredouille!“ „Hach, Frankreich, wie schön!“

VBA-Befehle

Och, Leute – nö! Wie oft muss ich es sagen! Und ich sehe es immer wieder! Gestern zu Beispiel:

Wird in VBA deklariert:

Dim strDateiImport, strDateiExport As String

dann ist strDateExport vom Datentyp String, strDateiImport dagegen vm Typ Variant. Und dies kann zu Problemen führen. Beispielsweise beim Befehl Dir, der zwar „“ verarbeiten kann, aber nicht Leer (Null):

Also bitte:

Dim strDateiImport As String, strDateiExport As String

oder:

Dim strDateiImport As String
Dim strDateiExport As String

Umgekehrt: Quizfrage: was liefern folgende Meldungsfenster:

Dim i, j, k As String

i = 12
j = 3
k = “ Excel kann nerven“

MsgBox i & j
MsgBox i + j

MsgBox i + j & k
MsgBox i + j + k

Kommentar hinterlassen , , , ,

Wenn man Tiere nicht essen soll, warum sind sie dann aus Fleisch?!

Dateneingabe, VBA-Befehle

Am Montag hat Johannes Curio auf unserem Excelstammtisch SharePoint-Listen vorgestellt. Er hat gezeigt, dass SharePoint zeilenweise speichert und dass man so Pflichtfelder anlegen kann. In Excel kann man so etwas nur per Programmierung. Schade eigentlich!

Der Vorteil von Excel: jeder darf alles überall hinschreiben. Der Nachteil: jeder schreibt alles überall hin!

Kommentar hinterlassen

Wenn mir langweilig ist, erzähle ich den Kindern im Bällebad bei Ikea, dass mich meine Eltern schon vor 12 Jahren abholen wollten.

VBA-Befehle

Erstaunlich. Ich programmiere ein Tool für eine Firma. Dort werden per VBA Daten in ein Formular eingetragen. Da mehrere Personen Zugriff auf das Formular haben, wird überprüft, ob das Formular geöffnet ist: die Eigenschaft ReadOnly liest aus, ob die Datei schreibgeschützt geöffnet wurde. Oder man versucht die Datei zu speichern – wird ein Fehler erzeugt, wurde die Datei bereits von einem anderen Anwender geöffnet.

Allerdings scheint es keine Eigenschaft oder Methode zu geben, mit EINFACHEN Mitteln mit VBA herauszufinden, welcher Anwender das Formular benutzt.

Kommentar hinterlassen

Ich drück‘ die Fernbedienung fester, wenn die Batterien leer sind

VBA-Befehle

Schon doof. Ich erstelle für einen Kunden ein dynamisches Excel-Formular. Die Daten sollen automatisiert ausgelesen werden. Deshalb muss ich wissen wie „groß“ das Formular ist, das heißt: wie viele Zeilen es enthält und wo sich bestimmte Informationen befinden.

Leider funktioniert die beiden Befehle

MsgBox Range(„A1“).CurrentRegion.Rows.Count
MsgBox Range(„A1“).SpecialCells(xlCellTypeLastCell).Row

nicht, wenn das Blatt geschützt ist. Also: Schutz aufheben!

Ja, ja, ich weiß: [Strg] + [A] und [Strg] + [Ende] funktionieren auch nicht in Excel …

2 Kommentare , ,

Wer mich entführt, gibt mich spätestens morgen zurück!

Oberfläche, VBA-Befehle

In Excel kann man die Arbeitsmappe schützen. Damit kann man keine Tabellenblätter einfügen, löschen, umbenennen, …

Was passiert allerdings, wenn man diese Befehle in VBA verwendet. Also:

ThisWorkbook.Worksheets.Add
ThisWorkbook.Worksheets(1).Name = "fehlgeschlagen"
ThisWorkbook.Worksheets(1).Visible = xlSheetHidden
ThisWorkbook.Worksheets(1).Delete
ThisWorkbook.Worksheets(1).Tab.Color = 255
ThisWorkbook.Worksheets(1).Copy Before:=xlDatei.Worksheets(1)

Man erhält Fehlermeldungen:

Ein bisschen mehr Mühe hätten sie sich schon bei den Fehlertexten machen können. Lediglich der Kopieren wird mit einem Verweis auf den Schutz der Arbeitsmappe quittiert …

Kommentar hinterlassen , , , , , , ,

Mir wurde ein Modeljob von einer Agentur angeboten. Ich bin das „Vorher“.

VBA-Befehle

Ich weiß, das sollte man nicht machen. Ich lasse den Anwender über eine Inputbox eine Zahl eingeben, speichere sie in einer String-Variablen und schreibe das Ergebnis nach Excel:

Sub ZahlAlsTextEintragen()

Dim s As String

s = InputBox(„Bitte eine Zahl eintragen.“)

ActiveCell.Value = s

End Sub

Das Ergebnis: 1 bleibt 1 (Excel schafft eine korrekte Typkonvertierung). Aus 1,5 und 1,55 werden die Texte 1,5 und 1,55 (sie stehen linksbündig in der Zelle und werden mit einem Smarttag versehen, das „die Zahl in dieser Zelle als Text formatiert ist oder ein Apostroph vorangestellt wurde“.)

Gibt man allerdings 1,555 ein, wird diese Zahl in 1.555 konvertiert. Ups!

Kommentar hinterlassen , , , ,

Man kann nie genug Leute kennen. Man mag ohnehin die wenigsten.

VBA-Befehle

Wahnsinn! Heute war ich in einer Behörde. Sie zeigten mir eine Liste. In der ersten Spalte steht eine laufende Nummer. Sie verweist auf ein Dokument auf der Festplatte. Die Teilnehmer haben mir erzählt, dass sie diese Formel nach Word kopieren, dort „aufbereiten“ und dann in die zirka 280 Zeilen und in die über 30 Spalten kopieren.

Zitat: „Ja – einen Tag lang sind wir jeden Monat schon damit beschäftigt, die Formeln einzufügen. Und fehleranfällig ist es auch.“

Zuerst habe ich überlegt, die ganze Analyse auf Power Query umzustellen; entschied mich dann aber aus Zeitgründen auf ein kleines VBA-Add-In – draufklicken und in 280 x 32 Zellen werden Formeln geschrieben. Fertig!

Kommentar hinterlassen

Hab die Küche geputzt. Wusstet ihr, dass die Scheibe am Backofen durchsichtig ist?

VBA-Befehle

Lieber Rene,

darf ich Dich um eine fachliche Hilfe zu meinem Microsoft-Problem bitten…

Zur Zeit arbeite ich wieder intensiv an einer Simulation. Da brauche ich stets für die vielen Daten Excel dazu. Nun klappt es nicht immer,  die Excel-Datei zu schließen (Vergesslichkeit). Dann Visio gestartet- und es schief – Error. Alles vorn ist nicht ganz so einfach, da Microsoft hinterrücks Verbindungen anlegt.
So ist dann eine temporäre Excel-Datei vorhanden, die sich nur durch PC-Neustart entfernen ließ.
So weit so gut. Damit konnte ich leben. Aber  irgendwann jedoch konnte ich eine Exceldatei  nicht mehr in gewohnter Weise über  den Explorer oder WinCommander öffnen (siehe unten).  Nun habe ich schon gesucht und gesucht und keine Hilfe gefunden. Habe sogar Office gelöscht und neu installiert – hilft nicht!

Hallo Wolfgang,

Zu deiner Frage. Ich habe nachgeschaut:

du machst Excel mit

Set x1App = Excel.Application

auf. Dadurch bleibt Excel unsichtbar. Ich baue deshalb in die Fehlerroutinen immer ein

x1App.Quit

ein. Am besten:

On Error Resume Next

x1App.Quit

Set x1App = Nothing

Wenn du testest und Excel ist „im Hintergrund offen“, kannst du es nicht in der Taskleiste sehen. Mache den Taskmanager auf [Strg] + [Alt] + [Entf] – dort siehst du Excel nicht in den „Prozessen“, weil es im Hintergrund läuft, sondern in den „Details“. Und dort kannst du „den Task beenden“.

Kommentar hinterlassen , , , , ,

Manchmal habe ich das Gefühl, der Haushalt macht das extra.

VBA-Befehle

Hallo René

Ich hätte eine kurze Frage, sofern ich Dich kurz stören darf.

Leider schaffe ich es nicht ein Steuerungselement in im Cockpit einzubauen, so dass man Makro „harte Werte“ auch so starten kann?

Danke schon mal für Deine Hilfe

Hallo Andi,

kurze Frage – kurze Antwort:

BITTE NICHT!

Ich finde in Tabelle5 (Cockpit) ein Makro „HarteWerte“, ich finde ein Modul „HarteWerte“ und darin ein weiteres Makro „HarteWerte“. Die Namen müssen eindeutig sein – sonst gibt es Kuddelmuddel!

Und: Ich habe die Zeile korrigiert:

Set rng = Tabelle4.Range(Tabelle4.Range(„A1“), Tabelle4.Range(„A1“).SpecialCells(xlLastCell)).SpecialCells(xlCellTypeVisible)

Bitte immer vollständig adressieren. Sonst läuft es an die Wand. Wenn du „A1“ schreibst, dann ist nicht klar welches A1 von welchem Blatt.

Kommentar hinterlassen , , ,

Eines muss ich meiner Morgenmüdigkeit lassen – sie hat Ausdauer!

VBA-Befehle

Hallo Rene

Kaum zu glauben, aber 8 Jahre nach Deinem Kurs hat sich mein Bruder Selbständig gemacht und ich durfte mich wieder an Excel VBA austoben um Angebote, Rechnungen und Lieferscheine zu generieren. Aber ich bekomme es einfach nicht hin verlässlich die Rechnungen automatisiert als pdf abzulegen. Bist Du für diesen Programmierauftrag zu gewinnen? Schöne Grüsse, Josef

Hallo Josef,

wenn ich eine Datei als PDF haben möchte, verwende ich den internen Speichern-Befehl (speichere als PDF). Kannst du mit dem Makrorekorder aufzeichnen.

Hallo Rene

Das hab ich gemacht, der Code läuft so lange, bis eine Seite am Drucker auf Papier gedruckt wird
Dieses Phänomen ist für mich absolut unerklärlich.

anbei mein Excel-Programm aus dem ich die damit generierten Rechnungen als PDF automatisiert ablegen möchte:

Speichername = strZiel & Rechnungsnummer & „-“ & Auftragsnummer & “ “ & Kunde & “ “ & Kommission & „.pdf“

‚als PDF Drucken und ablegen
Sheets(„Rechnung“).Activate

ActiveWorkbook.SaveAs Filename:= _
Speichername, FileFormat:=xlPDF, _
PublishOption:=xlSheet

Hallo Josef,

mir fällt beim Öffnen der Excelmappe auf, dass eine Spalte auf der zweiten Seite steht.

Ich würde zuerst alles auf eine Seite anpassen und anschließend ein PDF erstellen:

‚Application.PrintCommunication = False

With ActiveSheet.PageSetup

.FitToPagesWide = 1

.FitToPagesTall = 1

End With

‚Application.PrintCommunication = True

ActiveSheet.ExportAsFixedFormat Filename:= _

Speichername, Type:=xlTypePDF

Bei mir klappt das.

Versuche es mal, bitte

War das die Antwort auf die Frage?

Liebe Grüße

Rene

Hi, der Code mag immer nur bei der ersten Ausführung und sobald der Drucker angesteuert wird bringt er einen objektorientierten Fehler… vielleicht sollten wir doch auf eine neue Excelversion umsteigen
Was mir aber nach dem ersten Ausführen nach der Programmierung aufgefallen ist, dass er den Schnelldruck über das Druckersymbol verweigert hat. Musste über Datei Drucken… gehen
Kommentar hinterlassen , ,

Das Leben ist kein Picknick – Wir sind ja schließlich nicht zur Gaudi hier …

VBA-Befehle

Hallo René,

vielleicht kannst du mir auf die Sprünge helfen.

Einer meiner Kunden verwendet ein von mir gebasteltes Excel-Tool (ist inzwischen wirklich übel verbastelt). Er verteilt das dann an weitere Leute („Trainer“), die die Ergebnisse dann an weiter („Kunden“) verteilen. Er möchte, dass die Vorlage nicht so einfach zerstört wird, und deshalb sind wir auf die xltm-Dateien gekommen.

Der Kunde liebt (leider) seinen Mac über alles und verwendet auch Mac-Excel (ist der erste und letzte Excel-Mac Kunde von mir!). Jetzt gibt es hier einen Unterschied zwischen Mac und Windows. Nach seiner Beschreibung (also beim Mac) wird beim normalen speichern der xltm-Datei automatisch eine xlsm-Datei gespeichert. Beim Speichern unter Windows passiert das nicht, da wird eine xltm-Datei gespeichert, bzw. die vorhandene überschrieben.

Meine Frage an dich ist erst mal, wie das „normale“ Verhalten der xltm-Dateien sein soll. Ich habe dazu keine wirklich saubere Antwort gefunden.

Die nächste Frage ist, wie wir in diesem Fall das vom Kunden gewünschte Verhalten auch bei Windows beibringen können (hab da was beim googeln gefunden mit VBA). Hast du da andere Vorschläge?

Ich würde mich sehr freuen, wenn du mir da weiterhelfen könntest.

Schöne Grüße

Peter

Hallo Peter,

du warst leider bei unserem Vorlagen-Abend nicht dabei. Da haben wir die verschiedenen Varianten diskutiert: du kannst eine Vorlage öffnen oder mit ihr eine neue Datei erstellen. Unter Windows lautet der Standard, dass ein Doppelklick (im Explorer) aus einer XLTX-Datei eine neue Datei erstellt. Allerdings kannst du nicht verhindern, dass der Anwender die Datei mit rechter Maustaste / öffnen aufmacht. Natürlich kann man die Dateien im Vorlagenordner für die Vorlagen speichern und dem Anwender sagen, er solle sie bitte nur über Datei / Neu herholen. Aber auch hier gibt es einige gewitzte Zeitgenossen …

Ich schlage ein anderes Vorgehen vor: Beim Speichern-Ereignis überprüfst du, ob der Anwender die XLTM oder XLSM-Datei speichern will. Will er die XLTM-Datei speichern, dann „schlage ihm auf die Finger“ und setzte den Parameter Cancel auf True.

Ist nicht ganz so elegant, funktioniert aber. Solche Tricks habe ich auch schon anwenden müssen.

Hilft das?

LG  :: Rene

Hallo René,

das hilft mir ganz gewaltig! Vielen Dank für deine Erklärung!

Ich hoffe, dass ich mich mal revanchieren kann.

Schöne Grüße

Peter

Kommentar hinterlassen

Ich hab schon Dinge über mich gehört, die wusste ich selbst noch nicht

VBA-Befehle

Hallo Rene,

die Excel die wir überarbeitet haben laufen super, und die Kollegen machen jetzt sehr viel auch selbst.

Ich habe jetzt hier das Problem, dass hier ein Protokoll über den Button  „PDF mailen“ versendet werden soll, allerdings erkennt Office365 / Excel kein PDF-Add in.

Hast du da einen Tipp welches Add in wir verwenden sollen?

Früher hatten wir das Tool PDF Gotomaxx in Einsatz nur das hat dieses ADD-In nicht mehr in der neusten Version enthalten.

LG

Mario

Hallo Mario,

schön von dir zu hören. Und: schön, das ihr sehr viel selbst macht und hinbekommt.

Ich verwende seit einer Weil die interne PDF-Funktion von Office – du kannst ja die Datei speichern als PDF. Erstaunlicherweise stellt Excel VBA die PDF-Exportfunktion nur für das Blatt zur Verfügung. Also:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

„D:\Eigene Dateien\xy.pdf“, Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _

False

Ich habe früher auch mit Add-Ins gearbeitet … ist aber mühsam …

Hilft das?

schöne Grüße aus München und ein schönes Wochenende

Rene

Kommentar hinterlassen , ,

Dann komm ich halt in die Hölle…im Himmel kenn ich eh keinen…!

VBA-Befehle

Perfide!

gestern war ein Freund von mir bei mir – wir haben ein paar Excel-Probleme diskutiert. Er wollte „nochmal“ wissen, wie man in Excel Tabellenblätter „verstecken“ kann. „Nochmal“, weil ich es ihm bereits gezeigt hatte.

Kein Problem: Wir wechseln in den Visual Basic-Editor und schalten in den Eigenschaften die Sichtbarkeit des Blattes von xlSheetVisible auf xlSheetVeryHidden. Ich erkläre ihm den Unterschied zwischen xlSheetHidden und xlSheetVeryHidden – xlSheetHidden kann vom Anwender in Excel wieder eingeblendet werden; xlSheetVeryHidden dagegen nicht.

Und dann zeigt ich Axel, wie man einen Kennwortschutz auf das VBA-Projekt legen kann: über Kontextmenü in den Eigenschaften:

Gespeichert, geschlossen, geöffnet – der Kennwortschutz war weg! Noch ein Versuch: speichern, schließen, öffnen – alles sichtbar in VBA! Unglaublich! Ich wurde rot und blass, fing an zu zittern und stammelte wirre Dinge … Wir probierten ein paar Mal – ältere Dateien, die ich für Firmen erstellt hatte …

Nach ein paar versuchen war klar: eine XLSX-Datei erlaubt keinen Kennwortschutz. Eine XLSM-Datei, die mindestens ein (leeres) Modul enthält dagegen schon. Also: Einfügen / Modul und Speichern unter XLSM (mit Makros – auch wenn keine Makros im Projekt / in der Arbeitsmappe sind). Das klappt. Und ich wischte mir den Schweiß von der Stirn.

Danke an Axel für die „nochmalige“ Frage.

Kommentar hinterlassen , , , , , , , , , , , , , ,

Endlich hat mein Leben wieder Gin

VBA-Befehle

Excel-VBA-Schulung: Fünf Minuten nach Beginn. Wir schreiben unser erstes Hello-World-Programm. Ein Teilnehmer drückt aus Versehen die Enter-Taste:

Und fragt erstaunt: „muss ich das Programm kompilieren, bevor es läuft?“

Er hat recht: der Begriff „Kompilieren“ ist schlecht gewählt für die Titelzeile des Meldungsfensters.

Sofort schalten wir die „automatische Syntaxüberprüfung“ aus, deren Begriff ebenfalls schlecht gewählt ist.

Kommentar hinterlassen ,

…ich bin klein, mein Herz ist rein.. alles gelogen sagte der Wolf und frass das Rotkäppchen…

Drucken und Seitenlayout, VBA-Befehle

Ich habe heute mit einem Freund ein paar Excel-VBA-Lösungen programmiert.

In einer Tabelle sollen für den Ausdruck unter jeder Ergebniszeilen (Zeile mit den Zwischensummen) ein Seitenumbruch eingefügt werden (und noch ein paar weitere Dinge eingeschaltet und formatiert werden). Der Hintergrund: jedes dieser Blöcke sollte ausgedruckt an jeweils einen Kunden verschickt werden.

Der erste Test erstaunt:

Oha – man darf nur 1.026 manuelle Seitenumbrüche einfügen – mehr erlaubt Excel nicht.

Kommentar hinterlassen , , , ,

Männer verfahren sich nicht… sie kreisen ihr Ziel ein!!

VBA-Befehle

Haben Sie ein Tablett? Verwenden Sie den Tablettmodus? Haben Sie damit schon VBA programmiert? Zum Beispiel folgende Schleife:

Sub Schleife()

Dim i as Integer

MesBox „Los geht’s“

For i = 1 to 10

MsgBox i

Next

Msgbox „fertig – uff!“

End Sub

Das Ergebnis: Manchmal (!?!) flackert der Bildschirm unangenehm beim Testen (wie hier bei der 7):

Kommentar hinterlassen ,

vorgestern dachte ich noch, es könnte etwas werden …

VBA-Befehle

Vielleicht ist der Name der VBA-Funktion etwas unglücklich gewählt:

Function Autor()
Autor = ActiveWorkbook.BuiltinDocumentProperties(„Author“)
End Function

Dennoch: Dass Excel so eine starke Abneigung gegen diesen Namen verspürt und ihn noch nicht einmal in Groß-/Kleinschreibung anzeigt, finde ich schon frech:

„Benutzer“ oder „Benutzername“ darf ich.

Kommentar hinterlassen , , , , ,

Muss das immer einfach sein?

VBA-Befehle

Heute in der Excel-VBA-Schulung.

Wir erstellen eine Dialog, in dem die Eingaben überprüft werden. Ich schaue mir die Lösungen der Teilnehmer an. Das Ergebnis funktioniert:

Die Lösung der Teilnehmerin:

[…]

ElseIf Me.txtGeld.Value >= 1 = False Then

[…]

Ich stutze über die Folge. Warum funktioniert sie trotzdem?

Die Antwort: der erste Teil wird ausgewertet: Ist 0,75 >= 1 -> nein.

Dann der zweite Teil: ist WAHR = FALSE? – nein.

Kommentar hinterlassen , , , ,

Many people are alive only because it’s illegal to shoot them.

VBA-Befehle

Excel kann schon ganz schön nerven!

Gestern in der Excel-VBA-Schulung. Wir erstellen ein (komplexes) Makro, das eine andere Datei öffnet und dann weitere Befehle ausführt. Beispielsweise so:

Sub TastenkombiTest()
Workbooks.Open „D:\Eigene Dateien\Excel\Asterix.xlsx“
MsgBox „Excel kann schon ganz schön nerven!“
End Sub

In Excel weisen wir dem Makro über Entwicklertools / Code / Makros / Optionen eine Tastenkombination zu. Erster Versuch: das Makro wird auf [Strg] + [Shift] + [i] gelegt. Das Ergebnis: Das Makro bricht nach dem Öffnen der Datei ab.

Zweiter Versuch: das Makro wird auf [Strg] + [i] gelegt. Es klappt!

Muss ich das verstehen?

2 Kommentare ,

Nieder mit der Frühjahrsmüdigkeit! Es lebe der Winterschlaf!

VBA-Befehle

Heute in der Excel-VBA-Schulung. Ein Teilnehmer sagt mir, dass er ein Programm nicht starten kann. Er erhält eine Meldung, dass er „Else ohne If“ geschrieben hätte.

Ich erwiderte: nun – er habe wohl If vergessen. Oder falsch geschrieben. Er verneinte. Ich schaute mit den Code an:

Für With fehlt das Gegenstück End With. Nicht ganz korrekt die Meldung … sie taucht so an mehreren Stellen auf. Auch in folgenden Beispiel:

Dim i As Integer
For i = 1 To 10
If i > 9 Then
MsgBox „ziemlich groß“
Next

Hier lautet die Fehlermeldung: „Next ohne For“.

Kommentar hinterlassen , , , , ,

Wegen Rückrufaktion vorübergehend aus dem Sortiment genommen

VBA-Befehle

Eigentlich hätte ich es wissen können.

Vor einigen Jahren rief mich ein Teilnehmer in einer VBA-Schulung, weil sein Code nicht funktionierte:

Ich habe lange gesucht, bis ich verstanden hatte, dass er die Variable intSpaltenAnzahl deklariert hat, dagegen der Variablen intSpaltenAnzah1 einen Wert zuweist. Zugegeben: bei einer anderen Schrift als der Courier sieht man das besser.

Gestern beim Programmieren zeichnet der Makrorekorder folgenden Code auf:

ActiveWorkbook.TableStyles.Add („RenesVorlage“)
With ActiveWorkbook.TableStyles(„RenesVorlage“)
.ShowAsAvailablePivotTableStyle = True
.ShowAsAvailableTableStyle = False
.ShowAsAvailableSlicerStyle = False
.ShowAsAvailableTimelineStyle = False
End With
With ActiveWorkbook.TableStyles(„RenesVorlage“).TableStyleElements(xlColumnStripe1).Interior
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399945066682943
End With
ActiveSheet.PivotTables(„PivotTable1“).ShowTableStyleColumnStripes = True

Und ich bin ja wieder reingefallen – es heißt xlColumnStripe1 und nicht xlColumnStripel.

Obwohl?! – Stripel klingt auch lustig.

Kommentar hinterlassen , , ,

Und ich habe mich nicht an die Spitze der Nahrungskette hochgearbeitet, um Vegetarier zu werden.

VBA-Befehle

Hübsche Fehlermeldung: „Zu wenige Zeilenfortsetzungen“.

Die Ursache: Mit dem Makrorekorder wird der Befehl Datei / Öffnen (eine Textdatei) aufgezeichnet. Da die Textdatei zirka 200 Spalten hat, kann dies nicht in einem Array gespeichert werden, der in diesem Makro intern verwendet wird:

Kommentar hinterlassen , , , ,

Tötet es bevor es Eier legt

VBA-Befehle

Heute in der VBA-Schulung.

Da Excel keine Funktion besitzt, um einen Text „rumzudrehen“, verwenden wir die VBA-Funktion StrReverse:

Function RC(Text As String) As String
RC = StrReverse(Text)
End Function

Sie wird in Excel angezeigt und kann verwendent werden:

Padautz – vielleicht doch nicht. Möglicherweise ist der Name RC schon vergeben – RowColumn? Ich weiß es nicht. Ein Umbenennen in beispielsweise ReCo hilft.

Kommentar hinterlassen ,

Mein Name ist Hase! – Falsch! – Nein, mein Name ist nicht Falsch

VBA-Befehle

Lustiger Fehler in der VBA-Schulung. Finden Sie ihn?

Sub Eingabe()
Dim Geschlecht As String
Dim Benutzername As String

Geschlecht = InputBox(„Bitte Geschlecht angeben – bitte m oder w!“)
If Geschlecht = „m“ Or Geschlecht = „M“ Then
Benutzername = InputBox(„Bitte sag deinen Namen!“, „TF“)
MsgBox „Hallo lieber “ = Benutzername
ElseIf LCase(Geschlecht) = „w“ Then
Benutzername = InputBox(„Bitte sag deinen Namen!“, „TF“)
MsgBox „Hallo liebe “ = Benutzername
Else
MsgBox „Falsche Eingabe!“
End If

End Sub

Das Ergebnis:

Kommentar hinterlassen ,

Betrunkene sagen die Wahrheit. Blöd, das sie immer so nuscheln.

VBA-Befehle

Hallo Rene,

bitte wieder Tipparbeit bei der schon bekannten Tabelle.

Durch die Hinweise auf VBA in manchen Vorträgen habe ich mir einige der Schulungen angesehen.
Ach oh je 🙁
Es ist toll, dass man sich nachdem das Programm feriggestellt ist arbeit erspart. Die Suche im Internet nach geeigneten Code hat natürlich nicht zu einem Ergebnis geführt.
Dazu braucht man ein erweitertes Grundwissen das ich nicht habe.

Meine Bitte und Anliegen:
Kannst Du mir bitte VBA Code für die Tabelle mit der Datumskorrektur schreiben?

Das alte Datum soll ausgeblendet werden.
Das einfügen der Spalte für das neu formatierte Datum muss variable sein. Es kommt neuerdings auch eine Tabelle mit Point vor dem Datum.

Bitte ein alphabethisches Inhaltsverzeichnis mit Link auf die aktuellen Tabellen.

Zu VBA
Kannst Du mir ein Buch empfehlen?
Kann man in den Büchern, ähnlich wie in denen von Excel, bestimmte Code für Funktionen (kopieren, einfügen, Formeln in VBA) finden?

Im Internet habe ich für das Einfügen einer Spalte Code entdeckt. Wollte aber eine variable Möglichkeit, die ich nicht gefunden habe. Mitbekommen habe ich, dass man Variable definieren muss. Wie diese jedoch bezeichnet werden habe ich nicht herausgefunden. Die Zellen werden in Ziffern aufgeteilt. Buchstaben und Zahlen für die Bezeichnung der Zellen habe ich in anderen Code entdeckt. Erwähnt wurde auch der Unterschied zwischen USA und dem localen Code.

Mit besten Grüßen
Peter

#####

Hallo Peter,

hier der Code:

Sub MonateErsetzen()
Dim strSpalte As String
Dim i As Integer
Dim strMonateDeutsch
Dim strMonateEnglisch

strMonateDeutsch = Array(„Jänner“, „Februar“, „März“, „April“, „Mai“, „Juni“, „Juli“, „August“, „September“, „Oktober“, „November“, „Dezember“)
strMonateEnglisch = Array(„January“, „February“, „March“, „April“,
„May“, „June“, „July“, „August“, „September“, „October“, „November“,
„December“)

strSpalte = InputBox(„In welcher Spalte sollen die Datumswerte ersetzt werden?“)

For i = 0 To UBound(strMonateDeutsch)
ActiveSheet.Columns(strSpalte & „:“ & strSpalte).Replace What:=strMonateEnglisch(i), Replacement:=strMonateDeutsch(i), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i

End Sub

Wie kommt man dazu? Zeichen mit dem Makrorekorder auf – beispielsweise ersetze „January“ durch „Januar“. Dann erhältst du folgenden Code:

Sub Makro1()
‚
‚ Makro1 Makro
‚

‚
Selection.Replace What:=“February“, Replacement:=“Februar“, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Nun – und da bauen wir ein bisschen Makro außenrum.

Ich empfehle keine Bücher. Jeder hat eine andere Art zu lesen und zu lernen.
Geh in eine große Buchhandlung, hole einen Meter VBA-Literatur raus, blättere sie durch und bestelle dann das Buch, das dir am besten gefallen hat, in einer kleinen Buchhandlung.

Tipp: ich würde einen Kurs besuchen (Volkshochschule oder privater Anbieter). Dort bekommst du in kurzer Zeit am schnellsten die wichtigste Informationen. Dozent zeigt auch, was man falsch machen kann und hilft.
Liebe Grüße

Rene

#######

Hallo Rene,

Danke.

Leider hilft mir dieser Code nicht weiter nachdem nur der Monatsname in deutscher Schreibweise umsetzt wird. Benötigt wird ein ‚echtes‘ Datum.

In der Spalte C steht einmal ein echtes Datum und dann das Datum als Text.

In der neuen Spalte D die eingefügt wurde, hast du mit der untenstehenden Formel ein ‚echtes‘ Datum erstellt.

=WENN(ISTZAHL(C2);C2;DATUM(RECHTS(C2;4);VERGLEICH(TEIL(C2;SUCHEN(“ „;C2)+1;LÄNGE(C2)-8);{„January“;“February“;“March“;“April“;“May“;“June“;“July“;“August“;“September“;“October“;“November“;“December“};0);LINKS(C2;2)))

erstellt.

 

Bitte im VBA Code umsetzen, dass eine neue Spalte erzeugt wird, das Datum nach deiner Formel eingesetzt wird und die Spalte mit den nicht brauchbaren Angaben ausgeblendet wird.

Deine Vorschläge werde ich befolgen. Schau mal in die Buchhandlung was dort aufliegt. Ein Kurs ist auch eine gute Idee.

Besten Dank,

Peter

#####

Hallo Peter,

probiere es aus!

Durch das Ersetzen „erkennt“ Excel ein Datum und wandelt in eine Datumszahl um

LG ::  Rene

Kommentar hinterlassen , ,

Niemand hat die Absicht, sich eine Headline auszudenken…

VBA-Befehle

Das erste Add-In ist installiert. Es folgt eine zweite Version. Die zweite Datei.xlam wird an einem anderen Speicherort abgelegt.

Wird nun das Add-In erneut installiert, erhält man eine Meldung:

Klar soll sie ersetzt werden – was für eine Frage!

Und was passiert? NICHTS!

Heißt: Man muss erst das alte Add-In löschen und dann das neue installieren. Besser: das neue an den gleichen Ort speichern, in dem sich das alte Add-In befand – das alte also durch das neue ersetzen. Schon perfide!

Kommentar hinterlassen ,

Für ein Burn out fehlt mir einfach die Zeit

VBA-Befehle

Ein Kunde möchte per Programmierung Bilder nach Excel eingelesen habe. Das ist kein Problem, ebenso wenig wie das Verschieben und Verkleinern der Bilder. Jedoch: er möchte sie auch komprimiert haben. Weder im Objektkatalog noch in einer Suchmaschine finde ich die Möglichkeit per VBA auf den Assistenten „Bilder verkleinern“ zu gelangen. Auch der Makrorekorder hilft nicht weiter.

Also doch sendkeys. Ich drück die Alt-Taste und sehe, dass die Registerkarte „Format“ mit „JV“ erreicht wird. Dann „l“ für „Bilder komprimieren“ und anschließend „e“ für – „96 ppi“.

Also:

SendKeys „%jvle“

SendKeys „{Enter}“

Klappt nicht. Auch nicht:

SendKeys „%j“
SendKeys „%v“
SendKeys „%l“
SendKeys „%e“

Nach einigem Probieren komme ich dahinter, dass ich nur „j“ senden darf – nicht „jv“. Warum sagen die das nicht gleich?

Kommentar hinterlassen , , , , , ,

Ich kann nicht verhindern, dass ich alt werde. Aber ich kann dafür sorgen, dass ich Spaß dabei habe.

VBA-Befehle

Och nö – Leute – so nicht!

Vor ein paar Tagen habe ich eine Anfrage geschickt bekommen, warum das Makro, das unter Excel 2007 funktionierte, nicht mehr in Excel 2016 läuft. Ein Blick auf den Code – und ich schlage die Hände über dem Kopf zusammen:

  • Die Variablen sind nicht „sauber“ deklariert
  • Es werden keine Objekte adressiert, sondern „angesprungen“
  • Es werden die Grenzen zwischen Objekten (Zelle(A1) und ihren Eigenschaften (der Wert der Zelle A1) vermischt
  • Es finden keine Fehlerüberprüfungen statt (beispielsweise was passiert, wenn der Text „Stg“ nicht gefunden wird …)

Also: „sauber“ programmieren – so wie ihr es in meinen Schulungen lernt. Dann läuft das Programm auch noch in den neueren Excel-Versionen.

Damit Excel nicht mehr nervt!

Kommentar hinterlassen

Und wie geht das wieder weg?

VBA-Befehle

Ein Add-In in Excel zu erstellen ist nicht schwierig – man muss nur eine Excelmappe mit Makros als Add-In speichern.

Ein Add-In einzubinden ist auch nicht schwierig. Unter Datei / Optionen / Add-Ins / Excel-AddIns [Los] findet man die Schaltfläche „Hinzufügen“. Damit kann ein Add-IN eingebunden werden.

Man kann es deaktivieren – aber wie bekommt man es eigentlich weg?

Die einzige Möglichkeit ist das Add-In umzubenennen, es dann erneut zu aktivieren und/oder deaktivieren. Dann wird erkannt, dass es nicht mehr vorhanden ist:

Kommentar hinterlassen , , , ,

Himmiherrgotzsakramentzefixallelujaglumpfarregtz

VBA-Befehle

Es ist schön, wenn Excel Assistenten zur Verfügung stellt. Beispielsweise einen zum Duplikate entfernen:

Da ich diese Funktionalität in einem umfangreichen Programm benötige, zeichne ich ihn mit dem Makrorekorder auf:

ActiveSheet.Range(„$A$1:$J$78“).RemoveDuplicates _
Columns:=Array(1, 2, 3, 4, 5, 6, 7 _
, 8, 9, 10), Header:=xlYes

Der Parameter Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) gefällt mir nicht.

In der Hilfe steht, dass man ihn weglassen kann – dann würden alle Spalten verwendet werden. Ein Test zeigt: Das ist falsch. Lässt man den Parameter weg, passiert: GAR NICHTS!

Also programmieren wir den Parameter:

Dim intSpalten() As Integer
Dim i As Integer

ReDim intSpalten(0)
intSpalten(0) = 1
For i = 2 To ActiveSheet.Range(„A1“).CurrentRegion.Columns.Count
ReDim Preserve intSpalten(UBound(intSpalten) + 1)
intSpalten(UBound(intSpalten)) = i
Next

ActiveSheet.Range(„A1“).CurrentRegion.RemoveDuplicates _
Columns:=intSpalten, Header:=xlYes

Das Ergebnis ist eine Fehlermeldung:

Verwundert reibe ich mir die Augen. Probieren und eine lange Suche liefert das Ergebnis: Man muss das Array vom Typ Variant deklarieren. Und: der Parameter Columns verlangt den Wert in Klammern !?! Dann klappt es: die Spaltenanzahl des Assistenten „Duplikate entfernen“ wird dynamisch:

Dim intSpalten
Dim i As Integer

ReDim intSpalten(0)
intSpalten(0) = 1
For i = 2 To ActiveSheet.Range(„A1“).CurrentRegion.Columns.Count
ReDim Preserve intSpalten(UBound(intSpalten) + 1)
intSpalten(UBound(intSpalten)) = i
Next

ActiveSheet.Range(„A1“).CurrentRegion.RemoveDuplicates _
Columns:=(intSpalten), Header:=xlYes

PS: Ein Dankeschön an Dominik Petri für den Hinweis!

Kommentar hinterlassen , , , , , ,

Vorhersagen sind immer schwierig — vor allem über die Zukunft.

VBA-Befehle

Wisst ihr wie ich das gemacht habe? Nein – das Bild ist nicht bearbeitet! Heute beim Programmieren habe ich erstaunt festgestellt, dass in einer Zelle anderer Text steht als in der Bearbeitungsleiste.

Nun – ein paar Zeilen Code:

Range(„E1“).Value = „Nervt Excel?“
Application.ScreenUpdating = True
[Hier muss Code stehen, der einige Sekunden benötigt, um ausgeführt zu werden]
Application.ScreenUpdating = False
Range(„E1“).Value = „Excel nervt!“
Die Zeile

Application.ScreenUpdating = True

bewirkt die Anzeige der „Sanduhr“ und bewirkt, dass nur nur die Tabelle aktualisiert wird – nicht jedoch die Bearbeitungsleiste.

Achtung: Code muss zwei Mal ausgeführt werden, damit ich „Excel nervt!“ und „Nervt Excel?“ sehe.

Kommentar hinterlassen , ,

Viele halten mich für arrogant. Woher wollen die das denn wissen? Ich unterhalte mich ja nicht mit denen!

VBA-Befehle

Erinnern Sie sich noch? Windows 7? Die Beispielbilder: Wüste, Tulpe, Qualle, Koala, …

Ein Teilneer der Excel-VBA-Schulung möchte Bilder per VBA in seine Exceldatei einfügen. Die Dateinamen stehen dabei bereits in einer Exceltabelle.

„Kein Problem – schreiben Sie einfach ein paar Dateinamen in eine Tabelle.“ Alle Teilnehmer verwenden den Ordner „Beispielbilder“, kopieren den Pfad, tippen die Dateinamen mit der Endung ab. Ich verwende meinen eigen Ordner, in dem ich ein paar Urlaubsbilder habe.

Wir lassen das Makro laufen:

Dim strDateiname As String
Dim i As Integer
For i = 1 To ActiveSheet.Range(„A1“).CurrentRegion.wors.Count – 1
strDateiname = ActiveSheet.Range(„A1“).Offset(i, 0).Value
ActiveSheet.Pictures.Insert strDateiname
Next

Während es bei mir funktioniert, erhalten die Teilnehmer eine Fehlermeldung. Die Dateinamen sind doch korrekt geschrieben, oder:

Ein Blick in die Eigenschaften – Registerkarte „Sicherheit“ belehrt mich eines Besseren. Die Datei Wüste heißt „desert.jpg“, die Qualle „jellyfish.jpg, „penguins .jpg“, „lighthouse.jpg“, …

Ich gestehe – das ist mir noch nie aufgefallen, dass die Dateien einen Alias tragen, also dass die Beispielbilder eigentlich einen anderen Dateinamen aufweisen.

Kommentar hinterlassen , , ,

Immer auf dem Teppich bleiben – und keine großen Sprünge machen, wenn das Daruntergekehrte verborgen bleiben soll.

VBA-Befehle

Heute in der Excel-VBA-Schulung. Wir erstellen ein Beispiel: Zähle solange eine Nummer hoch, bis es eine Datei mit der entsprechenden Nummer nicht mehr gibt. Speichere dann die Datei unter dieser Nummer.

Bei mir funktioniert das Beispiel. Ein Teilnehmer hatte einen Fehler und rief mich. Ich war erstaunt über die Fehlermeldung:

Dateiname oder -nummer falsch

Noch erstaunter war ich, dass die Datei gespeichert war in:

D:\D:\Eigene Dateien\Excel\…

Zwei Mal D:\D:\ …?!?

Zuerst wollte ich Excel wüst beschimpfen, dann schaute ich mir den Code genauer an. Der Teilnehmer hatte die Datei gespeichert in.

„D:\“ & strPfad & strDatei

Erstaunlich: die Datei ist im Explorer nicht auffindbar. Excel (und VBA) behaupte jedoch, dass der FullName lautet:

D:\D:\Eigene Dateien\Excel\…

Kommentar hinterlassen , ,

Was sagen Sie als Unbeteiligter eigentlich zum Thema Intelligenz?

VBA-Befehle

Manchmal sind es nicht die Fehlermeldungen, die mich amüsieren:

sondern die Kommentare dazu:

„Hallo Rene,

ich verstehe etwas nicht … wenn ich diesen Code: […]

dann bekomme eine Fehlermeldung. Es fehlt ein with block und so.

Danke für deine Hilfe.“

Natürlich konnte ich helfen: „an Objektvariablen musst du etwas mit dem Schlüsselwort SET übergeben, also:

Set xlRange = ActiveSheet.UsedRange

Kommentar hinterlassen , ,

Nichts verursacht soviel Unruhe wie Menschen, die sich erholen wollen!

PowerPoint nervt auch, VBA-Befehle

Eine Kollegin hat mir letzte Woche eine Excelmappe mit einem Makro geschickt. Das Makro kopiert von mehreren Tabellenblättern Bereiche und fügt sie in eine bestehende Präsentation ein.

Die Kollegin hatte ein paar Fragen zum Makro. Da ich die PowerPoint-Präsentation nicht hatte, änderte ich einige wenige Befehle. Beispielsweise „greife auf Folie 1, 2, 3 zu“ wurde geändert in: „füge eine neue Folie ein“. Der Befehl AddSlides war schnell gefunden:

20170129PP01

Allerdings erhielt ich hier einen Fehler. Nochmal nachschauen:

20170129PP02

Doch: Sildes.AddSlide. Korrekt!

20170129PP03

Ebenso die Parameter: an welcher Stelle soll eine neue Folie erzeugt werden? Welches Layout soll verwendet werden? Ebenfalls korrekt!

Mir dämmerte es. Vor gefühlten 100.000 Jahre habe ich mal PowerPoint programmiert. Und dort herausgefunden, dass die Methode nicht AddSlides heißt, sondern Add. Geändert. Tatsächlich: IntelliSense listet nun sogar die korrekten Layout-Konstanten auf.

20170129PP04

Hum – Microsoft hätte wahrlich in den letzten 15 Jahren einen so wichtigen Befehl wie „füge neue Folie ein“ korrigieren können. Oder bin ich der einzige, dem dies aufgefallen ist?

Kommentar hinterlassen , , , ,

Ich kann allem widerstehen – außer der Versuchung. [Oscar Wilde]

VBA-Befehle

Was ist denn nun schon wieder los? Ich möchte eigentlich nur mit dem Befehl Copy ein Blatt in eine andere Datei kopieren:

20170126Kopieren02

Die Antwort finden Sie, wenn Sie genau hinschauen. Das zu kopierende Blatt („YTD“) ist xlSheetVeryHidden. Sie müssen die Eigenschaft Visible entweder auf xlSheetVisible oder auf xlSheetHidden stellen. Danach können Sie ihn ja wieder „sehr gut verstecken“.

Die Ursache

Die Ursache

Kommentar hinterlassen , , , ,

Was sein muss, muss sein. Und was nicht sein muss? Erst recht. – Heimito von Doderer

VBA-Befehle

Geschmunzelt habe ich schon:

Gestern in der VBA-Schulung haben wir uns Eigenschaften und Methoden von Objekten in Excel VBA angesehen. Beispielsweise von ActiveWorkbook: die Eigenschaften Name, Path, FullName, die Methoden Save, Close und eben auch PrintOut. Mit dem Parameter Copies kann man eine Datei mehrmals ausdrucken lassen, erklärte ich. Ein Teilnehmer wollte wissen, was denn passiere, wenn man die Datei beispielsweise 100.000 Mal ausdrucken würde. Da mein Laptop nicht mit einem Drucker verbunden war, habe ich es getestet: Das Ergebnis:

Maximal 32.767 (215-1) Mal darf man ausdrucken

Maximal 32.767 (215-1) Mal darf man ausdrucken

Ist ja auch logisch: mehr darf man auch nicht in Excel:

32.767 genügt doch auch, oder?

32.767 genügt doch auch, oder?

Kommentar hinterlassen , , , , ,

Jemand der etwas nicht möchte, findet Gründe! – Jemand der etwas möchte, findet Wege!

VBA-Befehle

… und ich finde die Ursache …

Heute in der VBA-Schulung. Ein Teilnehmer fragt mich, wo der Fehler in der folgenden Codezeile ist:

ActiveWorkbook.Worksheets(„Filme“).Activate

warum?

warum?

Nein – ActiveWorkbook ist richtig – es war keine andere Datei offen. Jedoch hatte der Teilnehmer das Tabellenblatt „Filme “ benannt – mit einem Leerzeichen Ende …

Böses Leerzeichen!

Böses Leerzeichen!

Kommentar hinterlassen ,

What’s in a name? that which we call a rose || By any other name would smell as sweet (Shakespeare: Romeo & Julia)

Alles ist weg, VBA-Befehle

Hallo Herr Martin,

doch, doch ich bin ganz sicher. Ich habe der Zelle G16 einen Namen („Betreuer“) gegeben. Aber nach einer Weile verschwindet er – er ist weder links oben im Namensfeld noch im Namensmanager zu sehen. Haben Sie eine Ahnung warum?

Namen verschwinden

Namen verschwinden

Wahrscheinlich wird ihre Datei durch Code (VBA oder VS.NET) gesteuert. Und wahrscheinlich hat dort jemand ein Makro geschrieben, das einen Namen „Betreuer“ anlegt – allerdings unsichtbar.

Man kann herausfinden, welche Namen verwendet wurden, beispielsweise so:

Sub Namenstest()
Dim i As Integer
Dim strListe As String
For i = 1 To ActiveWorkbook.Names.Count
strListe = strListe & vbCr & ActiveWorkbook.Names(i).Name & „:“ & ActiveWorkbook.Names(i).Value & “ sichtbar: “ & ActiveWorkbook.Names(i).Visible
Next

MsgBox strListe

End Sub

Mehrere unsichtbare Namen

Mehrere unsichtbare Namen

Denn: Man kann unsichtbare Namen generieren, die mit der Datei gespeichert werden:

ActiveWorkbook.Names.Add Name:=“Betreuer“, RefersTo:=“$G$35:$K$35″, Visible:=False

Kommentar hinterlassen ,

Natürlich könnte man morgens vor dem dritten Kaffee ein konstruktives Gespräch führen. Theoretisch wäre ja auch Weltfrieden möglich.

VBA-Befehle

Die Idee, endlich in Excel eine gute (!) Funktion zu implementieren, mit deren Hilfe man zwei Dateien vergleichen kann, war dringend nötig. Und so findet sich seit Excel 2013 (wenn Sie die richtige Version gekauft haben) hinter der Registerkarte „Inquire“ eine Schaltfläche „Dateien vergleichen“:

Dateien vergleichen

Dateien vergleichen

Das Ergebnis von zwei „größeren“ Dateien begeistert:

Tabellen vergleichen

Tabellen vergleichen

Jedoch weniger das Design, wenn es darum geht, VBA-Code zu vergleichen. Zugegeben – die Option ist praktisch – das Ergebnis schwer zu interpretieren.

VBA1 und VBA2

VBA1 und VBA2

Ein Kommentar , ,

Wann geht es endlich weiter?

VBA-Befehle

Heute in der VBA-Schulung ist uns folgende amüsante Sache aufgefallen:

In einer Datei – egal ob in der Personal.xlsb oder einer anderen beliebigen Makrodatei wird ein Makros geschrieben:

Sub Hinkelstein()
Application.Workbooks.Open „E:\Eigene Dateien\Comics\Asterix.xlsx“
MsgBox „Wo ist Obelix?“
End Sub

Legt man in den Optionen das Makro auf einen Shortcut – beispielsweise [Strg] + [q] gibt es keine Probleme.

Verwendet man jedoch [Strg] + [Shift] + [A], wird nur die erste Zeile ausgeführt – nach Öffnen der Datei bricht das Makro ab (auch bei längeren Makros). Muss ich das verstehen?

Nach Asterix ist Schluss

Nach Asterix ist Schluss

Kommentar hinterlassen , , ,

we are not amused

Excel-Objekte, VBA-Befehle

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

Kommentar hinterlassen , , ,

Sag mir wo die Punkte sind? – wo sind sie geblieben …

VBA-Befehle

Heute in der VBA-Schulung hat ein Teilnehmer etwas Interessante ausprobiert und festgestellt:

Eine InputBox gibt einen String zurück. Wandelt man ihn mit CInt in eine Integerzahl um oder übergibt ihn an eine vom Typ Integer* deklarierte Variable, so wird der eingegebene Wert 1.5 als 15 zurückgegeben. Ich hätte gedacht, dass 1.5 als Datum (01.05.2016) interpretiert wird. Nein! – Auch jeder andere Wert mit einem Punkt bekommt diesen gelöscht: Punkt ist weder Dezimaltrennzeichen, Tausendertrennzeichen noch Datumstrennzeichen. Erstaunlich, nicht?

Punkte verschwinden

Punkte verschwinden

*) Übrigens auch bei allen anderen Zahlenformaten: Byte, Long, Single, Double und Currency

Kommentar hinterlassen , , , ,

Modern ist, was man selbst trägt. Unmodern ist, was andere tragen (Oscar Wilde)

Makrorekorder, VBA-Befehle

Hätten Sie es gewusst? Oder verstanden? Wie lautet die VBA-Eigenschaft (oder -Methode) für klassisches Design einer Pivottabelle?

Klassisches Design

Klassisches Design

Die Antwort: Der Makrorekorder leistet hier gute Dinge:

With ActiveSheet.PivotTables(„xyz“)
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With

Ich gestehe: Alleine wäre ich nie darauf gekommen.

Kommentar hinterlassen

Wenn zwei das Gleiche tun, ist es noch lange nicht dasselbe

Makrorekorder, Pivottable, VBA-Befehle

Der Makrorekorder zeichnet manchmal erstaunlich Dinge auf. Vor allem ist unverständlich, warum er für den gleichen Befehl völlig unterschiedlichen Code aufzeichnet. In der letzten VBA-Schulung stellten wir fest, dass Einfügen / PivotTable in Excel 2010 entweder folgendes Makro aufzeichnet:
Sub Pivot01()
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
„Datenbank!R1C1:R76C6″, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=“Tabelle1!R3C1″, TableName:=“PivotTable1“, _
DefaultVersion:=xlPivotTableVersion14
End Sub
Oder folgendes:

Sub Pivot2()
Sheets.Add
ActiveWorkbook.Worksheets(„Tabelle1“).PivotTables(„PivotTable1″).PivotCache. _
CreatePivotTable TableDestination:=“Tabelle2!R3C1“, TableName:= _
„PivotTable2“, DefaultVersion:=xlPivotTableVersion14
End Sub

Je nachdem, ob man auf das obere Symbol oder das untere klickt …

20160626Pivot02

20160626Pivot01

Übrigens: In Excel 2013/2016 gibt es nur noch ein Symbol für Pivottabelle.

Kommentar hinterlassen

Auch Gänse haben Füße – Gänsefüße

VBA-Befehle

Ich habe drei mal hinschauen müssen, bis ich den Fehler in folgender VBA-Codezeile gesehen habe:

20160531km01

Was mich stutzig gemacht hat, war, dass der Kommentar nicht grün formatiert war. Ein Nachzählen der Gänsefüßchen hat dann ergeben, dass beim benutzerdefinierten Zahlenformat (Kilometer) nur zwei und nicht drei Anführungszeichen nach dem Text stehen. Das Textende wird durch das Ende der Zeile definiert …

Und schon geht es:

20160531km02

Kommentar hinterlassen , ,

Der programmatische Zugriff auf das Visual Basic-Projekt ist nicht sicher.

VBA-Befehle

Ich gestehe – ich mache es nicht sehr oft. Aber manchmal muss es sein. Code per Makro erzeugen. Oder auf vorhandenen Code zugreifen. Ich wollte alle Makros löschen – nein nicht indem ich die xlsm-Datei als xlsx speichere – die Datei war noch im Format xls und sollte es auch bleiben.

Also: erster Test – und schon eine erstaunliche Fehlermeldung:

Eine Zeile Code und schon ein Fehler

Eine Zeile Code und schon ein Fehler

Was bitte heißt das: „der programmatische Zugriff auf das Visual Basic-Projekt ist nicht sicher.“? Kurz überlegt – klar: Ich muss in den Optionen einstellen, dass ich dem Zugriff auf das VBA-Objektmodell vertraue – und schon klappt es …

20160313VBE02

24 VBComponents findet er.

Ein Kommentar , ,

Wieso nicht definiert? – Natürlich gibt es das!

VBA-Befehle

Hallo zusammen. Ich habe ein paar Zeilen VBA-Code abgetippt, den ich in einer Zeitschrift gefunden habe. Aber es läuft nicht. Und ich weiß nicht warum. Kann mir einer helfen?

geht nicht!?!

geht nicht!?!

Die Antwort ist simpel: Wenn Sie genau hinschauen, sehen Sie, dass Sie die Funktion DateSerial nicht richtig geschrieben haben. Sie haben die Ziffer „1“ als letztes Zeichen geschrieben und nicht den Buchstaben „l“ („L“). Ein Tipp zum Codeerstellen: Schreiben Sie immer in Kleinbuchstaben. Wenn Sie die Zeile verlassen, werden sämtliche Schlüsselwörter in Groß- und Kleinschreibung konvertiert. Wenn Sie sich vertippt haben, fällt das sofort auf, weil dann der Text nicht geändert wird.

Kommentar hinterlassen

VBA Code und Farben

VBA-Befehle

Hübsche Frage heute in der Excel-VBA-Schulung: Man kann zwar den „Bezeichnertext“ in Extras / Optionen farblich kennzeichnen – aber kann man auch zwei verschiedene Farben für VBA-Objekte (beziehungsweise Methoden und Eigenschaften) einerseits und einen Namen (zum Beispiel Variablen, Prozedurnamen, …) andererseits unterscheiden. Leider nein – es geht nur eine Farbe. Zugegeben – mich hat das noch nie gestört – ich lasse immer die voreingestellte Farbe Schwarz. So etwas wie unten sichtbar würde ich nicht machen.

Lustige (?!?) Farben

Lustige (?!?) Farben

VBA20160125_2

 

Kommentar hinterlassen

Trauriger VBA-Code

VBA-Befehle

Hallo Herr Martin,

ich habe Ende letzten Jahres an einem Ihrer Seminare (Makros für Anwender) teilgenommen. Es war ein super Seminar. Vielen Dank für diese tolle Erfahrung!

Ich habe mich nun an das erste Makro gesetzt. Leider treten hier jedoch einige Fehler auf. Jedoch ist es mir bisher nicht möglich gewesen, diese Fehler durch Internetrecherche zu beheben.

Da Sie bei dem Seminar auch das Angebot geäußert haben, dass man sich bei Fragen auch nach dem Seminar an Sie wenden kann, würde ich hiermit auf dieses Angebot zurück kommen.
Könnten Sie mir hier vielleicht helfen? Mir ist bewusst das es ein langer Code ist, deshalb bitte einfach sagen, wenn Ihnen dass zu viel ist (da es mir nicht möglich ist, hierfür etwas zu bezahlen).

Ich habe bei diesem Code auch bereits jeden einzelnen Schritt mit einer Bemerkung beschrieben. Anbei der Code:

Sub CM()
‚beginnt das Makro

Workbooks.Open Filename:= _
„I:\Daten\PERSONAL\DATEN\Pers_Entwicklung\Dokumentation\sonstige Schulungen\sonstige_Schulungen.XLS“
‚öffnet Datei sonstige_Schulungen.XLS

Workbooks.Open Filename:= _
„I:\Daten\PERSONAL\DATEN\Pers_Entwicklung\Dokumentation\sonstige Schulungen\Makro\sonst._Schulungen_für_Makros.xlsx“
‚öffnet Datei sonst._Schulungen_für_Makros.xlsx

Workbooks(„sonstige_Schulungen.XLS“).Sheets(„alle“).Range(„B7“).AutoFilter Field:=2, Operator:=xlFilterNoFill
‚filtert in Datei sonstige_Schulungen.XLS, Registerblatt „alle“ in B7 alle Zellen nach Zellen ohne Füllung -> blendet alle Zellen, die als LZK, Austritt oder EZ/MU markiert sind, aus

Workbooks(„sonstige_Schulungen.XLS“).Sheets(„alle“).Range((„A1“), ActiveCell.SpecialCells(xlLastCell)).Copy
‚kopiert in Datei sonstige_Schulungen.XLS, Registerblatt „alle“ die Zellen A1 bis zur letzten gefüllten Zelle

Workbooks(„sonst._Schulungen_für_Makros.xlsx“).Sheets(„sonstige Schulungen“).Range(„A1“).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transponse:=True
‚fügt den kopierten Bereich aus Datei sonstige Schulungen.XLS in Datei sonst._Schulungen_für_Makros.xlsx, Registerblatt „sonstige Schulungen“ (Zellen & Spalten vertauscht) in Zelle A1 ein

Workbooks(„sonst._Schulungen_für_Makros.xlsx“).Sheets(„sonstige Schulungen“).Range(„G11“).AutoFilter Field:=7, Criteria1:=Array(„CM1-I“, „CM1-S“, „CM2-I“, „CM2-S“), Operator:=xlFilterValues
‚filtert in Datei sonst._Schulungen_für_Makros.xlsx, Registerblatt „sonstige Schulungen“ in G11 in allen Zellen nach den Zellen mit den genannten „Kürzeln“

Workbooks(„sonst._Schulungen_für_Makros.xlsx“).Sheets(„sonstige Schulungen“).Range(„A1“, ActiveCell.SpecialCells(xlLastCell)).Copy
‚kopiert in Datei sonst._Schulungen_für_Makros.xlsx, Registerblatt „sonstige Schulungen“ die Zellen A1 bis zur letzten gefüllten Zelle

Workbooks(„sonst._Schulungen_für_Makros.xlsx“).Sheets(„CM“).Range(„A1“).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transponse:=True
‚fügt den kopierten Bereich aus sonst._Schulungen_für_Makros.xlsx, Registerblatt „sonstige Schulungen“ in Registerblatt „Kürzel“ (Zellen & Spalten vertauscht
‚-> somit wieder ursprüngliche Form, wie in Datei sonstige_Schulungen.XLS) in Zelle A1 ein

Workbooks(„sonstige_Schulungen.XLS“).Close
’schließt Datei sonstige_Schulungen.XLS

Workbooks(„sonst._Schulungen_für_Makros.xlsx“).Sheets(„sonstige Schulungen“).Range(„G11“).AutoFilter Field:=7
‚löscht in Datei sonst._Schulungen_für_Makros.xlsx, Registerblatt „sonstige Schulungen“, in Zelle G11 den Filter

Workbooks.Open Filename:= _
„I:\Daten\PERSONAL\DATEN\Pers_Entwicklung\Dokumentation\sonstige Schulungen\Abteilungen\ISCH-CM.xlsx“
‚öffnet Datei ISCH-CM.xlsx

‚Beginn für Schulung CM1
[…]

#####

Hallo Frau R. (haben wir nicht du gesagt im letzten Jahr?),

ich habe Ihr Makro mal „angetestet“ – beim Befehl:

‚fügt den kopierten Bereich aus Datei sonstige Schulungen.XLS in Datei sonst._Schulungen_für_Makros.xlsx, Registerblatt „sonstige Schulungen“ (Zellen & Spalten vertauscht) in Zelle A1 ein

muss der Parameter Transpose und nicht TranspoNse heißen. Dann klappt es.
Ansonsten:
1. Wenn Sie ein Problemchen haben, verwenden Sie den Debugger – also: [F8], [F8], [F8], … Dann bleibt er in der Zeile stehen, in der der Fehler auftritt. Ich weiß – die Meldung die hier erscheint, ist nicht aussagekräftig – ich habe auch zwei Mal hinschauen und „rumspielen“ müssen
2. Ich helfe Ihnen gerne – einfach schreiben!

schöne Grüße aus München

Rene Martin

######

Hallo Rene,

leider habe ich beim Verwenden des Debuggers bereits bei diesem Befehl eine Fehlermeldung, aus der ich nicht schlau werde.

Workbooks(„sonstige_Schulungen.XLS“).Sheets(„alle“).Range((„A1“), ActiveCell.SpecialCells(xlLastCell)).Copy
‚kopiert in Datei sonstige_Schulungen.XLS, Registerblatt „alle“ die Zellen A1 bis zur letzten gefüllten Zelle

Hast du hier auch eine Idee, wo der Fehler liegt?

#####

ja, Melanie,
wenn Du „A1“ schreibst, dann sitzt vielleicht der Cursor auf der Zelle eines anderen Blattes (ActiveCell) und nicht auf dem Blatt „alle“. Versuche mal Folgendes:

Workbooks(„sonstige_Schulungen.XLS“).Sheets(„alle“).Range(Workbooks(„sonstige_Schulungen.XLS“).Sheets(„alle“). („A1“), Workbooks(„sonstige_Schulungen.XLS“).Sheets(„alle“).Range(„A1“).SpecialCells(xlLastCell)).Copy

Du weißt, was ich dazu sagen werde – kein schöner Code – aber für den Anfang okay 😉

klappt das?

####

Ja so funktioniert es 🙂

Jedoch kommt jetzt hier die Fehlermeldung „Objekt unterstützt diese Eigenschaft oder Methode nicht“.

Workbooks(„ISCH-CM.xlsx“).Sheets(„CM1“).Columns(„A:B“).Paste
‚fügt die kopierten Spalten aus Datei sonst._Schulungen_für_Makros.xlsx, Registerblatt „CM“ in Datei ISCH-CM.xlsx, Registerblatt „CM1“, in die Spalten A und B ein
Hallo Melanie,

die Methode Paste darf nicht auf die Spalten, also den Bereich angewendet werden.
Wenn Du den Makrorekorder verwendest, dann zeichnet er etwas auf wie:

Selection.Copy
Range(„B10“).Select
ActiveSheet.Paste

Das heißt: der Befehl muss lauten:
Workbooks(„ISCH-CM.xlsx“).Sheets(„CM1“).Columns(„A:B“).Select
Workbooks(„ISCH-CM.xlsx“).Sheets(„CM1“).Paste

oder:
Workbooks(„ISCH-CM.xlsx“).Sheets(„CM1“).Activate
ActiveSheet.Range(“A1”).Select
ActiveSheet.Paste

klappt das?

#####

Hallo Rene,

super jetzt funktioniert es :-)! Vielen Dank!

Liebe Grüße

Kommentar hinterlassen

Leerzeichen oder Klammer?

VBA-Befehle

Ich gebe zu: so ganz konsequent ist das nicht in VBA. Für Methoden gibt es drei Schreibweisen: Nach einer Methode folgt nichts. Nach einer Methode steht ein Parameter, dann muss ein Leerzeichen geschrieben werden. Nach einer Methode steht ein Parameter und die Methode gibt einen Wert (oder ein Objekt) zurück – dann muss eine Klammer geschrieben werden. Dass es sich bei den drei Befehlen um eine Methode handelt, sieht man, wenn man den Namen der Klasse VBA angibt, also VBA.Beep, VBA.MsgBox, …

Drei Methoden

Drei Methoden

So weit so gut. Erstaunlicherweise kann man auch bei Methoden, die nichts zurückgeben eine Klammer schreiben:

Das funktioniert.

Das funktioniert.

Noch erstaunlicher wird es jedoch, wenn eine Methode mehrere Parameter hat – dann ist die Klammer verboten:

Klammer nicht bei mehreren Parametern!

Klammer nicht bei mehreren Parametern!

Noch erstaunlicher ist es, dass einige Methoden kategorisch die Klammer verbieten:

So nicht!

So nicht!

Aber eben nicht alle Methoden.

Zusammenfassung: Meine Empfehlung: Wenn eine Methode einen Wert zurück gibt, schreibe ich IMMER eine Klammer, wenn sie nichts zurück gibt, schreibe ich NIE eine Klammer. DAS funktioniert immer – ohne Ausnahme. Ehrenwort!

2 Kommentare

Ein kurzer Hilfetext im Objektkatalog in VBA wäre schön

VBA-Befehle

Eigentlich schade, dass Microsoft es in VBA 7.1 – also nach Excel 97 – 2000 – 2002 – 2003 – 2007 – 2010 – 2013 noch nicht geschafft hat einen kurzen Erklärungstext im Objektkatalog von VBA zu liefern, was diese Funktion denn bedeutet.

Leider kein Hilfetext

Leider kein Hilfetext

Man muss die Funktionstaste F1 drücken und gelangt dann zur Onlinehilfe von Microsoft, wo die Funktion erklärt wird.

Die Onlinehilfe

Die Onlinehilfe

Kommentar hinterlassen , , ,