MS Excel: Error when compiling project or library not found

The error message “Error when compiling project or library not found” I had today, She was at one point, to the a variable not previously with Dim were been initialized. There stopped running when my Excel for MAC with the error message:

Fehler beim Kompilieren Projekt oder Bibliothek nicht gefunden

The cause was actually, that a Windows Library was not available, After the reference was removed, everything went back.

Visual Basic Editor-> Extras-> References

There, remove the tick with “DOES NOT EXIST”:

Bildschirmfoto 2017-09-18 um 14.47.13

 

VBA on dropdown change event

When you want to trigger an action on dropdowns or cell/range change , you can do this by using the Worksheet_Change event and named 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 with like

Regex not possible are native in VBA, but only via an ActiveX library available. If you ffor Mac want to program, It's out of question. Instead, you can the regex Lite command “Like” use, with the similar abegspeckte operations can be.

Bsp.

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

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

VBA Excel key-value store cache with dictionary

To increase the performance of long-running calculations with MS Excel, I have installed a rudimentary key-value cache for a recurrent computation using a Dictionary object.

A dictionary is an object from the object in VBA Microsoft scripting library, that must included in the project on:

  1. Open Visual Basic Editor in Excel
  2. Extras-> References
  3. Microsoft scripting library Use Kreutzchen

A Dictionary object is like an associative array, What is already the greatest feeling for VBA :)

The following code is cached the result of a function call in a public (public, global scope) Variable named “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

I have more tips to optimize the performance of Excel macros in the article Excel Markos speed up Guide summarized.

VBA Excel: Mark mouse pointer over a cell as active

Unfortunately, there is an event to register no way via VBA, If the mouse pointer over a cell is.

The only way to get around this, must be a macro on an image/button in a cell, What is then executed. If a cell is associated with a macro, the mouse pointer automatically activates, alerts the user to, that is an active click option.

Link a macro on a cell

  1. Insert a durchsichttbaren .png files or a visible image/button in the cell
  2. right mouse button on the Insert object
  3. Assign macro
  4. Now the mouse is active, If you go over the image

The Excel Markos speed up Guide

If Excel macros to run slowly, because they must meet ever more complex requirements, can you use the following tricks, to enable a faster runtime:

VBA tips

Build a simple caching

See post VBA Excel key-value store cache with dictionary

Simple profiling to find bottlenecks in the application

The Term of certain calculations You can determine, by placing one time measurements in the VBA code on suitable place and spend on the debug console can be. This can be parsed, which parts of the application behave slowly.

Disable events during the Scriptlaufzeit

Are thus thrown no events such as:

Private Sub Worksheet_Change(ByVal Target As Range)

to prevent the event throwing and prevent unnecessary Excel events:

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

more…

VBA Excel Configurator: Calculation of all possible combinations

To build a Produktkonfigurators with Excel you need most of the time a matrix, they are defined in the, What configuration parameters with which other parameters together are possible and which are in the combination auschließen, e.g.. the color white is available for the iPhone 6, but not for the 16 GB version.

These rules are susceptible to error ser, Therefore you need an issue to testing all possible variants of the Configurator.

The many possible combinations are the biggest problem with larger configurators, for that it ever can take weeks to calculate all, If you're trying to go through all possible combinations, because I doubled the run-time with any other possible configuration option. more…

Excel macros automatically make available in each workbook

If you have written macros, are to be available not only in a workbook available, Excel offers 2010 PCunter for Windows 7 following function to:

You put a file called Personal.xlsb.

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

and there to bring under its macro, to be loaded every time Excel startup. This will open two files at the same time always when opening Excel: the actual file and the Personal.xlsb. (The file may be named differently in Excel 2010, There are all Excel files in that folder opens automatically when you start Excel).

In Personal.xlsb the code should be placed as usual in a macro. When it is desired, that a macro runs automatically when you start Excel, by e.g.. custom menus to provide, , the method

Private Sub Workbook_Open()

be used in this workbook.

Excel 2011 for MAC

Is unfortunately not, only Addins.

Add-in to Excel for Mac and PC

Addins can be installed with custom menus in Excel, which are also available in each workbook then. These must be in at “ThisWorkbook” are saved and the workbook then reopened, so that the code is executed.

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