Contenuti
Anche se non sai come programmare, ci sono molti posti (libri, siti Web, forum) in cui puoi trovare codice macro VBA pronto per un numero enorme di attività tipiche in Excel. Secondo la mia esperienza, la maggior parte degli utenti prima o poi raccoglie la propria raccolta personale di macro per automatizzare i processi di routine, che si tratti di tradurre formule in valori, visualizzare somme in parole o sommare celle per colore. E qui sorge il problema: il codice macro in Visual Basic deve essere archiviato da qualche parte per essere utilizzato in seguito nel lavoro.
L'opzione più semplice consiste nel salvare il codice della macro direttamente nel file di lavoro accedendo all'editor di Visual Basic utilizzando la scorciatoia da tastiera altro+F11 e aggiungendo un nuovo modulo vuoto tramite il menu Inserisci – Modulo:
Tuttavia, ci sono diversi svantaggi con questo metodo:
- Se ci sono molti file di lavoro ed è necessaria una macro ovunque, ad esempio una macro per convertire le formule in valori, dovrai copiare il codice in ogni libro.
- Non deve essere dimenticato salvare il file in formato abilitato alle macro (xlsm) o in formato binario (xlsb).
- Quando si apre un tale file macroprotezione ogni volta emetterà un avviso che deve essere riconosciuto (o disabilita completamente la protezione, il che potrebbe non essere sempre desiderabile).
Una soluzione più elegante sarebbe quella di creare il tuo componente aggiuntivo (componente aggiuntivo di Excel) – un file separato di un formato speciale (xlam) contenente tutte le tue macro “preferite”. I vantaggi di questo approccio:
- Basterà collegare il componente aggiuntivo una volta in Excel – e puoi usare le sue procedure e funzioni VBA in qualsiasi file su questo computer. Non è quindi necessario salvare nuovamente i file di lavoro nei formati xlsm e xlsb, perché. il codice sorgente non verrà archiviato in essi, ma nel file del componente aggiuntivo.
- Marchio non sarai nemmeno disturbato dalle macro. i componenti aggiuntivi sono, per definizione, fonti attendibili.
- Può fare scheda separata sulla barra multifunzione di Excel con dei bei pulsanti per eseguire macro di componenti aggiuntivi.
- Il componente aggiuntivo è un file separato. Il suo facile da trasportare da computer a computer, condividilo con i colleghi o addirittura vendilo 😉
Esaminiamo passo dopo passo l'intero processo di creazione del componente aggiuntivo Microsoft Excel.
Passaggio 1. Crea un file aggiuntivo
Apri Microsoft Excel con una cartella di lavoro vuota e salvala con un nome adatto (ad esempio MyExcelAddin) in formato aggiuntivo con il comando File: salva con nome o chiavi F12, specificando il tipo di file Componente aggiuntivo di Excel:
Si noti che per impostazione predefinita Excel archivia i componenti aggiuntivi nella cartella C:UsersYour_nameAppDataRoamingMicrosoftAddIns, ma, in linea di principio, è possibile specificare qualsiasi altra cartella che sia conveniente per te.
Passaggio 2. Colleghiamo il componente aggiuntivo creato
Ora il componente aggiuntivo che abbiamo creato nell'ultimo passaggio MyExcelAddin deve essere collegato a Excel. Per fare ciò, vai al menu File – Opzioni – Componenti aggiuntivi (File — Opzioni — Componenti aggiuntivi), fare clic sul pulsante Chi siamo (Andare) in fondo alla finestra. Nella finestra che si apre, fai clic sul pulsante Review (Navigare) e specificare la posizione del nostro file aggiuntivo.
Se hai fatto tutto bene, allora il nostro MyExcelAddin dovrebbe apparire nell'elenco dei componenti aggiuntivi disponibili:
Passaggio 3. Aggiungi macro al componente aggiuntivo
Il nostro componente aggiuntivo è connesso a Excel e funziona correttamente, ma non è ancora presente una singola macro. Riempiamolo. Per fare ciò, apri l'editor di Visual Basic con la scorciatoia da tastiera altro+F11 o tramite pulsante Visual Basic linguetta sviluppatore (Sviluppatore). Se le schede sviluppatore non visibile, può essere visualizzato tramite File – Opzioni – Configurazione della barra multifunzione (File — Opzioni — Personalizza barra multifunzione).
Dovrebbe esserci una finestra nell'angolo in alto a sinistra dell'editor Progetto (se non è visibile, attivalo tramite il menu Visualizza: Esplora progetti):
Questa finestra mostra tutte le cartelle di lavoro aperte e i componenti aggiuntivi di Microsoft Excel in esecuzione, incluso il nostro. Progetto VBA (MyExcelAddin.xlam) Selezionalo con il mouse e aggiungi un nuovo modulo tramite il menu Inserisci – Modulo. In questo modulo memorizzeremo il codice VBA delle nostre macro aggiuntive.
Puoi digitare il codice da zero (se sai come programmare) o copiarlo da qualche parte già pronto (che è molto più semplice). Proviamo, a scopo di test, ad inserire il codice di una semplice ma utile macro nel modulo vuoto aggiunto:
Dopo aver inserito il codice, non dimenticare di fare clic sul pulsante di salvataggio (dischetto) nell'angolo in alto a sinistra.
La nostra macro Formule Per Valori, come puoi facilmente immaginare, converte le formule in valori in un intervallo preselezionato. A volte vengono chiamate anche queste macro procedure. Per eseguirlo, devi selezionare le celle con le formule e aprire una finestra di dialogo speciale Macro dalla scheda sviluppatore (Sviluppatore — Macro) o scorciatoia da tastiera altro+F8. Normalmente, questa finestra mostra le macro disponibili da tutte le cartelle di lavoro aperte, ma le macro aggiuntive non sono visibili qui. Nonostante ciò, possiamo inserire nel campo il nome della nostra procedura nome della macro (Nome macro)e quindi fare clic sul pulsante Correre (correre) – e la nostra macro funzionerà:
| |
Qui puoi anche assegnare una scorciatoia da tastiera per avviare rapidamente una macro: il pulsante è responsabile di questo parametri (Opzioni) nella finestra precedente Macro:
Quando si assegnano i tasti, tenere presente che fanno distinzione tra maiuscole e minuscole e il layout della tastiera. Quindi se assegni una combinazione come Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally Shiftper ottenere la lettera maiuscola.
Per comodità, possiamo anche aggiungere un pulsante per la nostra macro alla barra degli strumenti di accesso rapido nell'angolo in alto a sinistra della finestra. Per fare ciò, seleziona File – Opzioni – Barra degli strumenti di accesso rapido (File — Opzioni — Personalizza barra degli strumenti di accesso rapido), quindi nell'elenco a discesa nella parte superiore della finestra l'opzione Macro. Dopo di che la nostra macro Formule Per Valori può essere posizionato sul pannello con il pulsante Aggiungi (Aggiungere) e seleziona un'icona con il pulsante Cambiamento di (Edit):
Passaggio 4. Aggiungi funzioni al componente aggiuntivo
Ma macroprocedure, ci sono anche macro di funzione o come vengono chiamati UDF (Funzione definita dall'utente = funzione definita dall'utente). Creiamo un modulo separato nel nostro componente aggiuntivo (comando di menu Inserisci – Modulo) e incollare lì il codice della seguente funzione:
È facile intuire che questa funzione è necessaria per estrarre l'IVA dall'importo IVA inclusa. Non è il binomio di Newton, ovviamente, ma ci servirà come esempio per mostrare i principi di base.
Si noti che la sintassi di una funzione è diversa da una procedura:
- si usa la costruzione Funzione …. Funzione finale invece Sub … Fine Sub
- dopo il nome della funzione, i suoi argomenti sono indicati tra parentesi
- nel corpo della funzione vengono eseguiti i calcoli necessari e quindi il risultato viene assegnato ad una variabile con il nome della funzione
Si noti inoltre che questa funzione non è necessaria ed è impossibile eseguire come la procedura macro precedente tramite la finestra di dialogo Macro e pulsante Correre. Tale funzione macro dovrebbe essere utilizzata come funzione di foglio di lavoro standard (SOMMA, SE, CERCA.VERT...), cioè basta inserire in una cella qualsiasi, specificando come argomento il valore dell'importo con IVA:
… oppure accedere tramite la finestra di dialogo standard per l'inserimento di una funzione (pulsante fx nella barra della formula), selezionando una categoria Definito dall'utente (Definito dall'utente):
L'unico momento spiacevole qui è l'assenza della consueta descrizione della funzione nella parte inferiore della finestra. Per aggiungerlo dovrai fare quanto segue:
- Apri Visual Basic Editor con una scorciatoia da tastiera altro+F11
- Seleziona il componente aggiuntivo nel pannello Progetto e premi il tasto F2per aprire la finestra Visualizzatore oggetti
- Seleziona il tuo progetto aggiuntivo dall'elenco a discesa nella parte superiore della finestra
- Fare clic con il tasto destro sulla funzione che appare e selezionare il comando Properties.
- Immettere una descrizione della funzione nella finestra Descrizione
- Salva il file del componente aggiuntivo e riavvia excel.
Dopo il riavvio, la funzione dovrebbe visualizzare la descrizione che abbiamo inserito:
Passaggio 5. Crea una scheda aggiuntiva nell'interfaccia
Il tocco finale, anche se non obbligatorio, ma piacevole sarà la creazione di una scheda separata con un pulsante per eseguire la nostra macro, che apparirà nell'interfaccia di Excel dopo aver collegato il nostro componente aggiuntivo.
Le informazioni sulle schede visualizzate per impostazione predefinita sono contenute nel libro e devono essere formattate in un codice XML speciale. Il modo più semplice per scrivere e modificare tale codice è con l'aiuto di programmi speciali: editor XML. Uno dei più convenienti (e gratuiti) è il programma di Maxim Novikov Editor XML della barra multifunzione.
L'algoritmo per lavorarci è il seguente:
- Chiudi tutte le finestre di Excel in modo che non ci siano conflitti di file quando modifichiamo il codice XML del componente aggiuntivo.
- Avvia il programma Ribbon XML Editor e apri il nostro file MyExcelAddin.xlam al suo interno
- Con bottone schede nell'angolo in alto a sinistra, aggiungi lo snippet di codice per la nuova scheda:
- Devi inserire virgolette vuote id la nostra scheda e gruppo (qualsiasi identificatore univoco) e in etichetta – i nomi della nostra scheda e un gruppo di pulsanti su di essa:
- Con bottone pulsante nel pannello di sinistra, aggiungi un codice vuoto per il pulsante e aggiungi i tag:
- etichetta è il testo sul pulsante
— immagine Mso — questo è il nome condizionale dell'immagine sul pulsante. Ho usato un'icona del pulsante rosso chiamata AnimationCustomAddExitDialog. I nomi di tutti i pulsanti disponibili (e ce ne sono diverse centinaia!) possono essere trovati su un gran numero di siti su Internet se si cerca la parola chiave “imageMso”. Per cominciare, puoi andare qui.
- onAzione – questo è il nome della procedura di callback – una breve macro speciale che eseguirà la nostra macro principale Formule Per Valori. Puoi chiamare questa procedura come preferisci. Lo aggiungeremo un po 'più tardi.
- Puoi verificare la correttezza di tutto quanto fatto utilizzando il pulsante con il segno di spunta verde nella parte superiore della barra degli strumenti. Nella stessa posizione, fare clic sul pulsante con un floppy disk per salvare tutte le modifiche.
- Chiudere l'editor XML della barra multifunzione
- Apri Excel, vai nell'editor di Visual Basic e aggiungi una procedura di callback alla nostra macro Kill Formulein modo che esegua la nostra macro principale per sostituire le formule con i valori.
- Salviamo le modifiche e, tornando in Excel, controlliamo il risultato:
Questo è tutto: il componente aggiuntivo è pronto per l'uso. Riempilo con le tue procedure e funzioni, aggiungi bellissimi pulsanti e diventerà molto più facile usare le macro nel tuo lavoro.
- Cosa sono le macro, come usarle nel tuo lavoro, dove ottenere il codice delle macro in Visual Basic.
- Come creare una schermata iniziale quando si apre una cartella di lavoro in Excel
- Che cos'è un Macrolibro personale e come utilizzarlo