TDE Informatica srl

Creare Add-in per VBE in Microsoft Access

di Gionata Aladino Canova (VBJ n° 68)

Uilizzare strumenti che svolgono per noi i compiti ripetitivi, permette di concentrarsi sui concetti della programmazione e di essere più produttivi.

Nell'articolo "Creare Add-in per Microsoft Access" abbiamo visto come realizzare una barra di strumenti che si integra nell'ambiente di Access, per automatizzare certe operazioni. In questo articolo vedremo come aggiungere al menu dell'editor di VBA (VBE Visual Basic Editor) dei comandi personalizzati che ci aiutino nella stesura del codice.

Analizzeremo poi un add-in che utilizzo comunemente per sviluppare, visibile in Figura 1.

Tale strumento è liberamente scaricabile ed il suo utilizzo è gratuito; è fornito completo di sorgenti che possono essere modificati e ridistribuiti a condizione di non rimuovere le indicazioni sull'autore. Per la comprensione dell’articolo serve una discreta padronanza dell’ambiente di Access e la conoscenza del VBA; i concetti esposti sono validi da Microsoft Access 2000 in poi.

Le complessità nascoste

La programmazione in un ambiente visuale è un problema complesso. Chi ha visto cosa ci voleva in C per creare una finestra in Windows, sa a cosa mi riferisco. Tanti sistemi moderni, Access compreso, consentono di fare molte cose in modo semplice, nascondendo le complessità sottostanti. Creare un add-in in Access è sicuramente più facile che creare un add-in per il VBE, poiché quest'ultimo nasconde un po' meno cose. Ad esempio, personalizzare i menu di VBE è più complicato che personalizzare i menu di Access. Questo perché VBE è basato sul modello di Visual Studio e non su quello di Office.

Progettare un add-in

La prima operazione da fare per progettare un add-in è analizzare quali operazioni desideriamo implementare. Da questa lista otterremo una serie di funzioni che potranno logicamente essere raggruppate. Ad esempio, le funzioni che trattano la generazione di codice per la gestione di recordset potranno essere organizzate tutte in un'unica form. Creata la lista delle form e delle eventuali funzioni da chiamare direttamente, sarà facile progettare l'interfaccia utente dell'add-in, ossia stabilire quali menu o barre degli strumenti vadano create o quali siano gli eventi da intercettare. Nel wizard di esempio vedremo la creazione di un menu e l'intercettazione dell'evento di compilazione. La parte finale della progettazione sarà il suddividere quali funzioni vadano create in quali moduli. La creazione dell'add-in si svilupperà nella stesura del codice che realizza le funzioni e nella parte che crea l'interfaccia con l'utente.

Costruire lo scheletro dell'add-in

Iniziamo aprendo Access, cliccando sulla caption e spostandoci nell'editor VBE (tasto rapido ALT+F11). La finestra è desolantemente vuota. Se la finestra Progetto non è visualizzata, dal menu Visualizza selezioniamo Gestione Progetti (CTRL+R). Dal menu File, selezioniamo l'opzione Nuovo Progetto e poi Progetto Aggiunta. Nella finestra che compare, compiliamo le voci Nome aggiunta visualizzato inserendo VBJ Wizard e Descrizione aggiunta con un commento a piacere, poi impostiamo la voce Applicazione a Visual Basic for Application IDE. In futuro, per far ricomparire questa finestra, dalla finestra Gestione Progetti, andremo nella sezione Finestre di progettazione e faremo doppio click sulla voce che, di default, si chiama AddInDesigner1. Salviamo il file con il nome VBJAddIn.

Nei riferimenti (Strumenti – Riferimenti) dovremo aggiungere:

• Microsoft Access 10.0 Object Library (per l'oggetto VBE)
• Microsoft Office 10.0 Object Library (per la gestione delle barre degli strumenti, es. CommandBarButton)
• Microsoft Visual Basic For Applications Extensibility 5.3 (per intercettare gli eventi delle Barre di VBE)

Aggiungiamo il modulo che conterrà le operazioni da implementare. Inseriamo il codice seguente:

Option Explicit

Public oHostApplication As Object
Public oAddIn As Object
Public conNomeApp As String
Public Const conVersione = "1.0 del 12-02-2006"
Public Const conTitoloApp = "VBJ Wizard"

Function wizCompila()
    MsgBox "Stai compilando!", vbInformation, "VBJAddIn"
End Function

Sub wizInfo()
    MsgBox "VBJ Wizard ver." & conVersione, vbInformation, _
           "VBJAddIn"
End Sub

Sub wizNumeroCasuale()
    MsgBox "Il primo numero che mi viene in mente è: " _
           & Int(Rnd() * 1000)
End Sub

 La funzione wizCompila ci avviserà che l'utente sta compilando. In un progetto reale potrebbe incrementare un numero di build (funzione mai gestita in Access!). La funzione wizInfo fornisce informazioni sul wizard, mentre wizNumeroCasuale visualizza una message-box che stampa un numero a caso. Dal riquadro Proprietà, impostiamo il nome del modulo a modMioCodice. Dobbiamo adesso costruire l'interfaccia con l'utente. Facciamo un click destro su AddInDesigner1 e selezioniamo Visualizza codice.

Inseriamo il codice contenuto nel Listato 2. Se proviamo a compilare, non dovremmo ottenere errori.

Option Explicit

Private WithEvents mnuItem1 As Office.CommandBarButton
Private WithEvents mnuItem2 As Office.CommandBarButton

Private WithEvents cCompila As CommandBarEvents

Private Sub AddinInstance_OnConnection _
    (ByVal Application As Object, _
     ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
     ByVal AddInInst As Object, custom() As Variant)
    'Memorizzo le referenze all'avvio
    Set oHostApplication = Application
    Set oAddIn = AddInInst
    
    conNomeApp = AddInInst.ProgId
    
    'Crea le opzioni aggiuntive nel menu di VBE
    CreaMenu (AddInInst.ProgId)
    
    ' Intercetta l'evento di compilazione (Debug / Compila)
    Dim c As CommandBarControl
    Set c = VBE.CommandBars(1).Controls(5).Controls(1)
    Set cCompila = VBE.Events.CommandBarEvents(c)
End Sub

Private Sub AddinInstance_OnDisconnection _
        (ByVal RemoveMode As _
         AddInDesignerObjects.ext_DisconnectMode, _
         custom() As Variant)
    'Rimuove le opzioni dal menu
    RimuoviMenu
    Set oAddIn = Nothing
    Set oHostApplication = Nothing
End Sub

Sub CreaMenu(strProgID As String)
    ' Crea un elemento nel menu e lo collega all'addin.
    Dim cbrMenu As Office.CommandBar
    Dim ctlBtnAddIn As Office.CommandBarButton
    Dim strKey As String
    
    ' Stabilisce un riferimento al menu Add-Ins
    Set cbrMenu = VBE.CommandBars("Add-Ins")
    
    ' Prova a cercare il pulsante "VBJ - Numero casuale"
    strKey = "VBJWizNumCasuale"
    Set mnuItem1 = cbrMenu.FindControl(Tag:=strKey)
    
    If mnuItem1 Is Nothing Then
        ' Aggiunge il pulsante
        Set mnuItem1 = cbrMenu.Controls.Add(msoControlButton _
            , , strKey, , True)
    
        With mnuItem1
            .Caption = "VBJ - Numero casuale"
            .Tag = strKey
            .Style = msoButtonCaption
            .FaceId = 634
            .OnAction = "!<" & strProgID & ">"
            .BeginGroup = True
        End With
    End If

    ' Prova a cercare il pulsante "Informazioni su..."
    strKey = "VBJWizInfo"
    Set mnuItem2 = cbrMenu.FindControl(Tag:=strKey)
    
    If mnuItem2 Is Nothing Then
        ' Aggiunge il pulsante
        Set mnuItem2 = cbrMenu.Controls.Add(msoControlButton _
            , , strKey, , True)
    
        With mnuItem2
            .Caption = "Informazioni su VBJ Developer Wizard"
            .Tag = strKey
            .Style = msoButtonCaption
            .FaceId = 634
            .OnAction = "!<" & strProgID & ">"
            .BeginGroup = True
        End With
    End If

End Sub

Sub RimuoviMenu()
    On Error Resume Next
    VBE.CommandBars("Add-Ins").Controls _
        ("VBJ - Numero casuale").Delete
    VBE.CommandBars("Add-Ins").Controls _
        ("Informazioni su VBJ Developer Wizard").Delete
End Sub


' Qui inizia la sezione che gestisce gli eventi
Private Sub mnuItem1_Click(ByVal Ctrl As Office.CommandBarButton, _ 
                           CancelDefault As Boolean)
    wizNumeroCasuale
End Sub

Private Sub mnuItem2_Click(ByVal Ctrl As Office.CommandBarButton, _
                           CancelDefault As Boolean)
    wizInfo
End Sub

Private Sub cCompila_Click(ByVal CommandBarControl As Object, _
                           Handled As Boolean, _
                           CancelDefault As Boolean)
    ' Viene gestito l'evento di compilazione
    wizCompila
End Sub

Prima di analizzarlo, collaudiamo l'add-in.

Collaudare l'add-in

Dopo essere riusciti a compilare senza errori, la sequenza per collaudare l'add-in è:

• Esegui/Esegui Progetto (c'è anche il corrispondente pulsante sulla barra Standard.
• Aggiunte/Gestione Aggiunte. Fare doppio click sul nostro add-in (nell'esempio, VBJ Wizard), per caricarlo, poi premere Ok.
• Nel menu Aggiunte troveremo le due opzioni da noi create. Per provarle è sufficiente selezionarle. Per provare la compilazione, apriamo un database vuoto, aggiungiamo un modulo ed una routine anch’essa vuota. Selezioniamo poi Debug/Compila. Se tutto funziona, una message-box ci avviserà che stiamo compilando.
• Finito il test, ritorniamo in Aggiunte/Gestione Aggiunte e facciamo doppio click sul nostro add-in (nell'esempio, VBJ Wizard), per scaricarlo. Questa operazione è necessaria per ripulire i menu di VBE dalle voci che vi abbiamo aggiunto. Interrompendo semplicemente il progetto, non viene eseguita la routine di pulizia.
• Selezioniamo Esegui/Interrompi progetto

Se saltiamo lo scaricamento dell'Add-in, oppure abbiamo commesso errori nel codice, il menu potrebbe rimanere sporco. Per rimediare, se non abbiamo altre personalizzazioni manuali, è sufficiente andare in modifica delle barre dei menu, selezionare la Barra dei menu e selezionare la funzione Reimposta.

Come funziona l'add-in

Vediamo adesso in dettaglio il funzionamento dell'add-in. Le operazioni che esso compie sono volutamente banali, per potersi concentrare solo sul codice di base necessario per costruire l'add-in.

Le dichiarazioni

Private WithEvents mnuItem1 As Office.CommandBarButton
Private WithEvents mnuItem2 As Office.CommandBarButton

impostano due riferimenti per le due voci di menu che andremo a creare nella routine CreaMenu. La parola chiave WithEvents indica al compilatore che, di quei due oggetti, vogliamo gestirne gli eventi. Il modo per gestire gli eventi è dichiarare una routine, come Access fa in maniera automatica quando gli chiediamo di gestire un evento di un oggetto, con la sintassi

Private Sub <nomeVariabile>_<NomeEvento>()[parametri])

come si vede nell'esempio seguente, dove viene gestito l'evento Click dell'oggetto mnuItem1.

Private Sub mnuItem1_Click(ByVal Ctrl As Office.CommandBarButton, _
            CancelDefault
As Boolean)

Queste routine devono stare nel modulo di codice associato all'AddInDesigner e, ovviamente, possono contenere chiamate a routine contenute in moduli esterni, come succede in quella appena vista.

La dichiarazione

Private WithEvents cCompila As CommandBarEvents

ci permette invece di gestire l'evento legato all'opzione già esistente Compila. L'associazione tra la variabile cCompila ed il pulsante di compilazione viene effettuata all'interno della routine AddinInstance_OnConnection, dalle righe

' Intercetta l'evento di compilazione (Debug / Compila)
Dim c As CommandBarControl
Set c = VBE.CommandBars(1).Controls(5).Controls(1)
Set cCompila = VBE.Events.CommandBarEvents(c)

All'avvio dell'add-in, viene eseguita la routine AddinInstance_OnConnection, nella quale creiamo i menu ed impostiamo l'intercettazione dell'evento di compilazione, come visto sopra. Alla chiusura dell'add-in, tramite la routine AddinInstance_OnDisconnection provvediamo all'eliminazione delle voci di menu.

La routine CreaMenu è abbastanza semplice, se si ha un'idea di come sia il modello ad oggetti delle barre di menu. Impostato il riferimento in cbrMenu, alla barra Add-Ins di VBE, per evitare duplicazioni di opzioni, proviamo a cercare se esiste già l'opzione che vorremmo creare, altrimenti la aggiungiamo. L'operazione viene ripetuta tante volte quante sono le voci da inserire nel menu. La routine RimuoviMenu è molto semplice, limitandosi solo a tentare di cancellare le voci create in precedenza.

In pratica, avendo la lista delle opzioni che vogliamo creare, è necessario dichiarare n variabili come mnuItemX all'inizio della routine, inserire la creazione e la distruzione delle opzioni in CreaMenu e RimuoviMenu ed infine creare le corrispondenti routine di intercettazione degli eventi.

Il codice del Listato 1 non ha bisogno di commenti, essendo un esempio ridotto all'osso.

Un Add-in reale

Nel codice allegato trovate anche un add-in completo, che uso per sviluppare. Il file si chiama AladinoDevWizard.vba e contiene, oltre a tutta l'infrastruttura vista fino ad ora, una form per generare recordset, visibile in Figura 2.

Analizzando la sub wizGeneraRecordSet troviamo del codice la cui analisi esula da questo articolo, ma che merita di essere studiato. La routine, come primo compito, controlla di essere stata chiamata con il cursore puntato in VBE all'interno di una definizione di sub o di function. Poi, dopo aver visualizzato la form e letto i relativi dati, costruisce una stringa che contiene il codice generato. La stringa viene inserita nella posizione in cui si trova il cursore, dalla riga

VBE.ActiveCodePane.CodeModule.InsertLines lngStartLine, strCode

Utilizzando come base questo codice, possiamo costruire una form che ci chieda l'input che vogliamo, generare il codice corrispondente ed inserirlo nel progetto corrente. Per chi non ha mai lavorato con il modello ad oggetti di VBE, consiglio di cercare esempi in rete e di leggere [1] e [2]. Esistono metodi per aggiungere sub e function, spostarsi all'interno del codice e fare altre operazioni in maniera molto semplice.

Nell'add-in è inclusa una UserForm. A differenza di Word ed Excel, pare che in Access non ci sia un modo immediato per aggiungerla. Probabilmente perché si pensa di utilizzare le form di Access. Per aggirare il problema, ci sono due modi. Possiamo personalizzare la barra dei menu aggiungendo il comando UserForm, nel menu Inserisci. Oppure possiamo inserire in un modulo il codice seguente che, richiamato dalla finestra di debug, crea una UserForm.

Sub Add_Form1()
' Declare a variable to hold the UserForm.
Dim x As Object
    ' Create a new UserForm. You can use this new 
    ' VBComponent object to manipulate the User Form.
    Set x = Application.VBE.ActiveVBProject _
       .VBComponents.Add(vbext_ct_MSForm)
End Sub

Il risultato è visibile in Figura 3.

Installazione e distribuzione

Per distribuire il nostro add-in, dovremo prima creare la DLL tramite l'opzione File/CreaVBJAddIn.DLL, poi selezioniamo Aggiunte/Creazione guidata pacchetti (disponibile con la versione Developer di Office). Seguendo le istruzioni, verrà creato un pacchetto che consentirà la distribuzione della nostra DLL, tramite un normale programma di setup.

Conclusioni

La stesura di un Add-in per VBE è un'operazione non banale, ma fattibile tranquillamente da chi programma tutti i giorni. È importante, nel lavoro quotidiano, capire se scriviamo spesso parti di codice ripetitive, perché in questo caso si può trarre parecchio beneficio creando un add-in personalizzato. Per funzioni standard tipo conteggio di righe, ordinamento di procedure o simili, conviene invece fare ricerche in rete, poiché si trovano add-in già pronti e collaudati, spesso gratuiti, veramente potenti. Un esempio è VBA MZ-Tools 3.0 [3].

Riferimenti

[1] Programming the Microsoft Office Visual Basic Editor

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office07042002.asp

[2] Before You Create a VB Add-In, Learn the IDE's Object Model

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dninvb00/html/IDEObjectModel.asp

[3] MZ-Tools 3.0 for Visual Basic 6.0, 5.0 and VBA