Excel-macro's geschikt maken voor anderen

Pagina's in dit artikel

Taakbalken

Een veelgebruikte methode om toegang te verschaffen tot macro’s is via taak- of werkbalken.

Zo'n werkbalk kan middels VBA code worden aangemaakt (zie listing 3):

Listing 3

Option Explicit

Sub RemoveBar()
    On Error Resume Next
    Application.CommandBars("xlUtilDemo1").Delete
End Sub

Sub CreateBar()
    Dim oBar As CommandBar
    Dim oControl As CommandBarControl
    RemoveBar
    Set oBar = Application.CommandBars.Add
    oBar.Name = "xlUtilDemo1"
    oBar.Visible = True
    Set oControl = oBar.Controls.Add(ID:=1, Before:=1)
    oControl.OnAction = "ButtonClicked"
    oControl.FaceId = 275
    oControl.Caption = "Click me!"
    Set oControl = Nothing
    Set oBar = Nothing
End Sub

Sub ButtonClicked()
    MsgBox "Je knop werkt!!!"
End Sub

De Sub Createbar maakt 1 werkbalk aan met daarop 1 knopje. Klikken op dat knopje zorgt ervoor dat het programmaatje ButtonClicked gestart wordt.

Merk op, dat deze nieuwe werkbalk op de tab Invoegtoepassingen wordt gezet in het lint.

Om ervoor te zorgen dat de werkbalk wordt gemaakt bij openen en weer verdwijnt bij sluiten van het bestand kan de code uit listing 4 opgenomen worden in de "Thisworkbook" module:

Listing 4

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RemoveBar
    RemoveMenu
End Sub

Private Sub Workbook_Open()
    CreateBar
    MakeMenu
End Sub

Deze methode heeft een nadeel. Als de gebruiker Excel gaat sluiten, maar op annuleren klikt zodra Excel vraagt of de bestanden opgeslagen moeten worden, dan is bovenstaande Workbook_BeforeClose subroutine al uitgevoerd en de knoppenbalk (en het menu) dus verdwenen. Dit kan als volgt worden voorkomen:

Definieer in een normale module een publieke variabele:

Public bMeClosing As Boolean

En verander de code in de thisworkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RemoveBar
    RemoveMenu
    If Not bMeClosing Then
        Application.OnTime Now, "Createbar"
        Application.OnTime Now, "MakeMenu"
    End If
End Sub

Hoe werkt dit nu. Als de gebruiker zelf Excel sluit zal de variabele bMeClosing onwaar zijn en wordt een Ontime event ingesteld, welke na afloop van alle event macro’s (in dit geval het Workbook_BeforeClose event) zal starten. Omdat Excel echter sluit gebeurt dit niet en is de werkbalk netjes verwijderd. Indien de gebruiker echter besluit het sluiten te annuleren wordt alsnog de macro "Createbar" gestart en keert de werkbalk terug. Als de utility zelf gesloten moet worden (bijvoorbeeld wanneer de gebruiker besluit de invoegtoepassing uit te schakelen), dan is het natuurlijk gewenst dat de werkbalk niet weer terugkeert. Zet hiertoe eerst de variabele bMeClosing op Waar:

bMeClosing = True

en sluit dan het bestand, bijvoorbeeld via

ThisWorkbook.Close SaveChanges:=False

Overigens kan door deze methode het voorbeeld bestand alleen gesloten worden door op de knop "Bestand sluiten" te klikken op het werkblad Blad1.