MS Excel: Fehler beim Kompilieren Projekt oder Bibliothek nicht gefunden

Die Fehlermeldung “Fehler beim Kompilieren Projekt oder Bibliothek nicht gefunden” hatte ich heute, sie war an einer Stelle, an der eine Variable nicht vorher mit Dim initialisiert worden waren. Dort stoppte die Ausführung bei meinem Excel für MAC mit der Fehlermeldung:

Fehler beim Kompilieren Projekt oder Bibliothek nicht gefunden

Die Ursache war tatsächlich, dass eine Windows Bibliothek nicht verfügbar war, nachdem der Verweis entfernt worden war, ging alles wieder.

Visualb Basic Editor -> Extras -> Verweise

dort das Häckchen entfernen mit dem “NICHT VORHANDEN”:

Bildschirmfoto 2017-09-18 um 14.47.13

 

VBA On Dropdown Change Event Aktion ausführen

Wenn man auf das Ändern eines Dropdowns oder einer Zelle/Range reagieren will und eine andere Aktion ausführen will, dann kann man dies mittels des Worksheet_Change Events und am besten mittels eines benamten Bereiches /Ranges:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim checkTarget As Range
    Set checkTarget = Application.Intersect(Target, Range("my_named_range"))
    If checkTarget Is Nothing Then
        Debug.Print "not active range, exit"
    Else
        MsgBox "success"
    End If

End Sub

VBA Regex Lite mit Like

Native in VBA sind Regex leider nicht möglich, sondern nur über eine ActiveX Bibliothek erhätlich. Wenn man auch für Mac programmieren will, kommt dies nicht in Frage. Statt dessen kann man den Regex Lite befehl “Like” benutzen, mit dem ähnliche abegspeckte Operationen machen kann.

Bsp.

success = text Like "T-#########-#"
'matches:
'T-123456789-1

success = text Like "[0-9a-z][0-9a-z]"
'matches:
'1a, 2b

Wozu MS Excel Markos digital signieren lassen?

Man kann Makros nach der Programmierung digital signieren lassen von einer CA-Authority, z.B. thawte. Damit lässt sich der folgende Dialog beim Öffnen der Excel Mappe unter Umständen verhindern:

sicherheitswarnung_markos

Diese Sicherheitsabfrage hat den Zweck den PC vor Angriffen mit bösartigen Excel und Word Dokumenten zu schützen, die gern im Anhang von Spam Mails verschickt werden.

Vorteile von zertifizierten Makros: weiterlesen…

VBA Excel Key-Value Store Cache mit Dictionary

Um die Performance von langlaufenden Berechnungen mit MS Excel zu erhöhen, habe ich für eine immer wiederkehrende Berechnung einen rudimentären Key-Value Cache eingebaut mit Hilfe eines Dictionary Objektes.

Eine Dictionary Objekt in VBA ist ein Object aus der Microsoft Scripting Library, die dafür in das Projekt eingebunden muss über:

  1. Visual Basic Editor in Excel öffnen
  2. Extras -> Verweise
  3. Microsoft Scripting Library Kreutzchen setzen

Ein Dictionary Object ist wie ein assoziatives Array, was für VBA schon das höchste der Gefühle ist :)

Folgender Code cached das Ergebnis eines Funktionsaufrufes in einer öffentliche (public, globaler Scope) Variablen namens “cache”:

'this code does not run, it is pseudo code understand the cache behaviour

'public variable, global scope
Dim cache  As Scripting.Dictionary

sub mySub()
    'initialize cache
    Set cache = = New Scripting.Dictionary 
    For Each myRow In rows
        resultFromCache = myFunction(1 , 2, 3)
    Next myRow
end Sub

Function myFunction(input1, input2, input3)
    'chache this result
    cacheKey = input1 & " " & input2 & " " & input3
    If Not cache.Exists(cacheKey) Then
        result = cacheAbleFunction(input1, input2, input3)
        cache.Add cacheKey, result
    Else
        result = cache.Item(cacheKey)
    End If
    myFunction = result
end Function

Function cacheAbleFunction(input1, input2, input3)
    'do something here very slow or very often
    cacheAbleFunction = input1 * input2 * input3 * input1 * input2 * input3 * input1 * input2 * input3
end Function

Weitere Tipps zur Performance-Optimierung von Excel Makros habe ich in dem Artikel Excel Markos Speed Up Guide zusammengefasst.

VBA Excel: Mouse Pointer über eine Zelle als aktiv markieren

Leider gibt es keine Möglichkeit über VBA sich an einen Event zu registrieren, wenn der Mouse Pointer über einer Zelle ist.

Die einzige Möglichkeit dies zu umgehen, ist ein Makro auf ein Bild/Button in einer Zelle zu legen, was dann ausgeführt wird. Wenn einer Zelle ein Makro zugeordnet ist, wird der Mouse Pointer automatisch aktiv und macht den User darauf aufmerksam, dass eine aktive Klickmöglichkeit vorliegt.

Ein Makro auf eine Zelle verlinken

  1. einfügen eines durchsichttbaren .png Files oder eines sichtbaren Bildes/Buttons in die Zelle
  2. rechte Maustaste auf das eingefügt Objekt
  3. Makro zuweisen
  4. Jetzt ist die Maus aktiv, wenn man über das Bild fährt

Der Excel Markos Speed Up Guide

Wenn Excel Makros langsam laufen, weil sie immer komplexeren Anforderungen genügen müssen, kann man folgende Tricks anwenden, um eine schnellere Laufzeit zu ermöglichen:

VBA Tipps

Einfaches Caching einbauen

siehe Beitrag VBA Excel Key-Value Store Cache mit Dictionary

Einfaches Profiling um Engpässe in der Applikation zu finden

Die Laufzeit bestimmter Berechnungen lässt sich bestimmen, indem man in den VBA Code an passender Stelle Zeitmessungen platziert und auf der Debug Console ausgeben lässt. Damit kann analysiert werden, welche Teile der Applikation sich langsam verhalten.

 Events deaktivieren währen der Scriptlaufzeit

Damit keine Events geworfen werden wie z.B.:

Private Sub Worksheet_Change(ByVal Target As Range)

kann man das Eventwerfen verhindern und so unnötige Excel-Events verhindern:

Application.EnableEvents = False
...
'your macro
...
Application.EnableEvents = True

weiterlesen…

VBA Excel Konfigurator: Berechnung aller möglichen Kombinationen

Zum Bau eines Produktkonfigurators mit Excel benötigt man meistens eine Matrix, in der man definiert, welche Konfigurationsparameter mit welchen anderen Parametern zusammen möglich sind und welche sich in der Kombination auschließen, z.B. die Farbe weiß ist erhältlich für das iPhone 6, aber nicht für die 16GB Variante.

Diese Regeln sind ser anfällig für Fehler, deshalb benötigt man zum Testing eine Ausgabe alle möglichen Varianten des Konfigurators.

Das größte Problem bei größeren Konfiguratoren sind die vielen möglichen Kombinationen, für die es schon mal Wochen dauern kann alle zu berechnen, wenn man versucht einfach alle möglichen Kombinationen durchzugehen, denn die Laufzeit verdoppelt ich mit jeder weiteren möglichen Konfigurationsmöglichkeit. weiterlesen…

Excel Makros automatisch in jeder Arbeitsmappe verfügbar machen

Wenn man Makros geschrieben hat, die nicht nur in einer Arbeitsmappe zur Verfügung stehen sollen, bietet Excel 2010 für PCunter Windows 7 folgenden Funktion an:

Man legt eine Datei namens PERSONAL.XLSB.

C:\Users\IhrWindowsName\AppData\Roaming\Microsoft\Excel\XLSTART

an und bringt dort seine Makros unter, die bei jedem Excel Start geladen werden sollen. Es öffnet sich dann immer gleichzeitig zwei Dateien beim öffnen von Excel: die eigentliche Datei und die PERSONAL.XLSB. (Die Datei kann auch anders genannt werden unter Excel 2010, es werden alle Excel Datein in diesem Ordner automatisch geöffnet beim Start von Excel).

In der PERSONAL.XLSB sollte der Code wie gewohnt in ein Makro gelegt werden. Wenn gewünscht wird, dass ein Makro automatisch beim Start von Excel ausgeführt wird, um z.B. eigene Menüs zur Verfügung zu stellen, kann die Methode

Private Sub Workbook_Open()

genutzt werden unter DieseArbeitsmappe.

Excel 2011 für MAC

Geht leider nicht, nur über Addins.

Addins in Excel für Mac und PC

Mit Addins lassen sich eigene Menüs einbauen in Excel, die dann auch in jeder Arbeitsmappe verfügbar sind. Diese müssen in unter “DieseArbeitsmappe” gespeichert und die Arbeitsmappe danach neu geöffnet werden, damit der Beispiel Code ausgeführt wird.

Private Sub Workbook_Open()

Dim cbMainMenuBar As CommandBar

Dim cbcCutomMenu As CommandBarControl

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&Mein Menupunkt").Delete
On Error GoTo 0

 Set cbMainMenuBar = _
     Application.CommandBars("Worksheet Menu Bar")

 Set cbcCutomMenu = _
     cbMainMenuBar.Controls.Add(Type:=msoControlPopup)

  cbcCutomMenu.Caption = "&Mein Menupunkt"

 With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "Absätze entfernen"
                .OnAction = "AbsaetzeEntfernen"
 End With

 With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "csv Dateien zusammenfügen"
                .OnAction = "csvMerge"
 End With

 With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "Export einzelner .txt Dateien"
                .OnAction = "transponierenUndSpeichern"
 End With

End Sub