Come scrivere macro per Excel. Macro in Excel - Istruzioni per l'uso. Aggiunta di pulsanti macro alle schede

Argomento 2.3. Software di presentazione e nozioni di base sulla programmazione per ufficio

Argomento 2.4. Sistemi di gestione di basi di dati e sistemi esperti

2.4.11. Database di formazione con il pulsante principale del modulo "Training_students" - Download

Programmazione VBA e macro

2.3. Software di presentazione e nozioni di base sulla programmazione per ufficio

2.3.7. Nozioni di base sulla programmazione per ufficio

Linguaggio di programmazione visiva BASIC per applicazioni: Visual Basic for Applications (VBA)

VBA è un sottoinsieme del linguaggio di programmazione visuale Visual Basic (VB) che comprende quasi tutti gli strumenti per la creazione di applicazioni VB.

VBA differisce dal linguaggio di programmazione VB in quanto VBA è progettato per funzionare direttamente con gli oggetti di Office e non può essere utilizzato per creare un progetto indipendente dalle applicazioni di Office. In VBA il linguaggio di programmazione è quindi VB e l'ambiente di programmazione è realizzato sotto forma di un editor VB che può essere attivato da qualsiasi applicazione MS Office.

Ad esempio, per aprire l'editor VBA da PowerPoint, è necessario eseguire il comando Strumenti / Macro / Editor VBA. Puoi tornare dall'editor all'applicazione selezionando il comando Microsoft PowerPoint nel menu Visualizza o premendo la combinazione di tasti Alt + F11.

Utilizzando il set di controlli e l'editor di moduli dell'editor VBA integrato, l'utente può creare interfaccia utente per il progetto in fase di sviluppo con forma dello schermo. I controlli sono oggetti e ciascun oggetto ha un insieme definito di possibili eventi (ad esempio, fare clic o fare doppio clic su un mouse, premere un tasto, trascinare un oggetto, ecc.).

Ogni evento si manifesta in determinate azioni del programma (risposte, reazioni). Un modulo utente consente di creare finestre di dialogo dell'applicazione. Il linguaggio di programmazione VBA viene utilizzato per scrivere il codice del programma, ad esempio la creazione di funzioni utente in Excel.

Il fatto che il sistema di programmazione VBA sia progettato per funzionare con oggetti Office ne consente un utilizzo efficace per automatizzare le attività di sviluppo vari tipi documenti.

Diamo un'occhiata all'algoritmo per la creazione di funzioni utente in VBA:

1. Aprire la finestra dell'editor del codice VBA eseguendo il comando Strumenti / Macro / Editor di Visual Basic o premendo Alt+F11.

2. Selezionare la voce del menu dell'editor Inserisci / Modulo.

3. Successivamente, eseguire Inserisci/Procedura. Nella finestra di dialogo Aggiungi procedura che si apre, inserisci il nome della funzione (ad esempio, SUM5) e imposta i selettori: Tipo – sulla posizione Funzione; Ambito (Ambito) - nella posizione Pubblico (Generale) e fare clic su OK.

Riso. 1.

4. Nella finestra dell'editor per la programmazione VBA, apparirà un modello di funzione: titolo - Funzione pubblica SUM5() e fine - Funzione finale, tra i quali è necessario inserire il codice del corpo della funzione.

Riso. 2.

5. Successivamente, inseriamo un elenco di parametri della funzione, ad esempio, tra parentesi indichiamo (x, y, z, i, j), il tipo di dati (per calcoli accurati) e il tipo di valore restituito dalla funzione (noi non lo inserirò in questo esempio). Inoltre, introduciamo il corpo della funzione, ad esempio SOMMA5 = x + y + z + i + j. Di conseguenza, otteniamo il seguente testo di programma:

Funzione pubblica SUM5(x, y, z, i, j)
SOMMA5 = x + y + z + i + j
Fine funzione

6. Ritorno alla finestra Applicazioni Excel, in cui, ad esempio, dobbiamo sommare cinque valori.

7. Eseguire "Inserisci/Funzione" e nella finestra della procedura guidata della funzione che si apre, selezionare la categoria "Definito dall'utente" e nella finestra "Seleziona una funzione", selezionare SUM5 e fare clic su OK.

Riso. 3.

Riso. 4.

Macro

Creazione di una macro

Creiamo una macro per sommare automaticamente due numeri nelle celle A1, B1 e inseriamo il risultato nella cella C1, oltre a riempire la cella C1 con il colore turchese.

Algoritmo per la creazione di una macro per l'attività:

1. Selezionare Strumenti/Macro, Avvia registrazione.

2. Nel campo Nome macro, immettere un nome per la macro. Il primo carattere del nome della macro deve essere una lettera. Non sono consentiti spazi nel nome della macro; È possibile utilizzare i caratteri di sottolineatura come separatori di parole.

3. Per eseguire una macro utilizzando una scorciatoia da tastiera, immettere una lettera nel campo Scorciatoia da tastiera. È possibile utilizzare le combinazioni CTRL+lettera (per lettere minuscole) o CTRL+MAIUSC+lettera (per lettere maiuscole), dove lettera è qualsiasi tasto della tastiera. Non selezionare una scorciatoia da tastiera standard perché la scorciatoia da tastiera selezionata sovrascrive le scorciatoie da tastiera standard Microsoft Excel in quel momento con questo libro.

4. Nel campo "Salva" seleziona la cartella di lavoro in cui desideri salvare la macro. Salva la macro in "Questa cartella di lavoro". Per creare breve descrizione macro, inserire il testo richiesto nel campo Descrizione. Lo screenshot mostra un esempio di compilazione della finestra di dialogo "Registra macro".

Riso. 5.

5. Fare clic su OK.

6. Esegui i macro comandi che desideri registrare.

Riso. 6.

7. Fare clic sul pulsante Interrompi registrazione sulla barra degli strumenti mobile del sistema operativo (Interrompi registrazione) o su Strumenti / Macro / Interrompi macro.

Al termine della registrazione, una macro viene visualizzata nell'elenco delle macro sotto il suo nome.

L'editor VBA registra automaticamente una sequenza di macro comandi o un programma in base alle azioni eseguite (Fig. 7.).

Riso. 7.

Per richiamare una macro è necessario eseguire il comando Strumenti/Macro/Macro. Successivamente, nella finestra di dialogo con l'elenco delle macro, puoi trovarlo per nome e fare clic sul pulsante Esegui.

Assegnazione di un pulsante della barra degli strumenti per eseguire una macro

La macro può essere lanciata utilizzando il pulsante presente sulla barra degli strumenti integrata; per fare ciò è necessario fare:

1. Seleziona Impostazioni dal menu Strumenti.
2. Nella finestra di dialogo Impostazioni, seleziona la scheda Comandi e seleziona l'opzione Macro nell'elenco Categorie, quindi seleziona "Pulsante personalizzato" nell'elenco Comandi.
3. Dall'elenco Comandi, utilizza il mouse per trascinare il pulsante personalizzato sulla barra degli strumenti.
4. Fare clic con il pulsante destro del mouse su questo pulsante e selezionare Assegna macro a menù contestuale.
5. Immettere un nome per la macro nel campo Nome macro.

Assegnazione di un'area di un oggetto grafico per eseguire una macro:

1. Creare un oggetto grafico.
2. Applica il menu contestuale all'oggetto grafico selezionato.
3. Selezionare il comando Assegna macro dal menu contestuale.
4. Nella finestra di dialogo Assegna macro all'oggetto visualizzata, inserisci il nome della macro nel campo Nome macro, quindi fai clic su OK.

La modifica di una macro si effettua utilizzando l'editor VBA; per fare ciò è necessario effettuare le seguenti operazioni:

1. Selezionare il comando Strumenti / Macro / Macro.
2. Seleziona il nome della macro che desideri modificare dall'elenco Nome.
3. Fare clic sul pulsante Modifica, si aprirà una finestra di Visual Basic in cui è possibile modificare i comandi della macro selezionata scritti in Visual Basic.

Rimozione di una macro:

1. Dal menu Strumenti, selezionare Macro, quindi selezionare Macro.
2. Nell'elenco delle macro nella cartella di lavoro corrente, è necessario selezionare la macro che si desidera eliminare e fare clic sul pulsante Elimina.

Rinominare una macro

Per rinominare una macro, è necessario accedere alla modalità di modifica della macro e modificare il titolo nel testo del programma. Il nuovo nome sostituirà automaticamente quello vecchio negli elenchi delle macro e i tasti di scelta rapida chiameranno la macro con il nuovo nome.

Innanzitutto, un po’ di terminologia.

Macro- questo è il codice scritto nel linguaggio integrato di Excel VBA(Visual Basic per l'applicazione). Le macro possono essere create manualmente o registrate automaticamente utilizzando un cosiddetto registratore di macro.

Registratore di macroè uno strumento in Excel che registra tutto ciò che fai in Excel passo dopo passo e lo converte in codice VBA. Il registratore di macro produce un codice molto dettagliato (come vedremo in seguito) che potrai modificare in seguito, se necessario.

La macro registrata può essere eseguita un numero illimitato di volte ed Excel ripeterà tutti i passaggi registrati. Ciò significa che anche se non sai nulla di VBA, puoi automatizzare alcune attività semplicemente registrando i tuoi passi e riutilizzandoli in seguito.

Ora tuffiamoci e vediamo come registrare una macro in Excel.

Mostra la scheda Sviluppatore nella barra multifunzione del menu

Prima di registrare una macro, è necessario aggiungere una scheda Sviluppatore alla barra multifunzione del menu di Excel. Per fare ciò, attenersi alla seguente procedura:

Di conseguenza, la scheda "Sviluppatore" verrà visualizzata sulla barra multifunzione del menu.

Registrazione di una macro in Excel

Ora scriviamo una macro molto semplice che selezioni una cella e vi inserisca del testo, come "Excel".

Ecco i passaggi per registrare una macro di questo tipo:

Congratulazioni! Hai appena registrato la tua prima macro in Excel. Sebbene la macro non faccia nulla di utile, ci aiuterà a capire come funziona il registratore di macro in Excel.

Ora diamo un'occhiata al codice registrato dal registratore macro. Seguire questi passaggi per aprire l'editor di codice:

Vedrai che dopo aver fatto clic sul pulsante Esegui, il testo "Excel" verrà inserito nella cella A2 e la cella A3 verrà selezionata. Questo avviene in millisecondi. Ma in realtà la macro ha eseguito le azioni registrate in sequenza.

Nota. Puoi anche eseguire una macro utilizzando la scorciatoia da tastiera Ctrl + Maiusc + N (tieni premuti i tasti Ctrl e Maiusc, quindi premi il tasto N). Questa è la stessa etichetta che abbiamo assegnato alla macro quando l'abbiamo registrata.

Cosa registra una macro?

Ora andiamo all'editor del codice e vediamo cosa abbiamo ottenuto.

Ecco i passaggi per aprire l'editor VB in Excel:

1. Nel gruppo Codice fare clic sul pulsante Visual Basic.

Puoi anche utilizzare la combinazione di tasti Alt + F11 e andare all'editor del codice VBA.

• Barra dei menu: contiene i comandi che è possibile utilizzare mentre si lavora con l'editor VB.
• Barra degli strumenti- sembra un pannello accesso veloce in Excel. Puoi aggiungere ulteriori strumenti che usi frequentemente.
• Esploratore di progetti- Qui Excel elenca tutte le cartelle di lavoro e tutti gli oggetti in ciascuna cartella di lavoro. Ad esempio, se abbiamo una cartella di lavoro con 3 fogli di lavoro, verrà visualizzata in Project Explorer. Ci sono diversi oggetti aggiuntivi qui, come moduli, moduli utente e moduli di classe.
• Finestra del codice- il codice VBA stesso si trova in questa finestra. Per ogni oggetto elencato nell'esploratore progetto, esiste una finestra di codice, come fogli di lavoro, cartelle di lavoro, moduli, ecc. In questo tutorial, vedremo che la macro registrata si trova nella finestra di codice del modulo.
• Finestra delle proprietà- puoi vedere le proprietà di ciascun oggetto in questa finestra. Utilizzo spesso questa finestra per etichettare gli oggetti o modificarne le proprietà.
• Finestra immediata(finestra di anteprima) - Nella fase iniziale non ne avrai bisogno. È utile quando si desidera testare i passaggi o durante il debug. Non viene visualizzato per impostazione predefinita ed è possibile visualizzarlo facendo clic sulla scheda Visualizza e selezionando l'opzione Finestra immediata.

Quando abbiamo registrato la macro "EnterText", nell'editor VB sono accadute le seguenti cose:

• È stato aggiunto un nuovo modulo.
• La macro è stata registrata con il nome specificato: "InputText"
• Una nuova procedura è stata aggiunta alla finestra del codice.

Quindi se fai doppio clic su un modulo (nel nostro caso il modulo 1), apparirà una finestra di codice come mostrata di seguito.

Ecco il codice registrato dal registratore macro:

SubTextEnter() " "TextEnter Macro " " Range("A2").Seleziona ActiveCell.FormulaR1C1 = "Excel" Range("A3").Seleziona End Sub

In VBA, qualsiasi riga che segue " (l'apostrofo) non viene eseguita. Questo è un commento solo a scopo informativo. Se rimuovi le prime cinque righe di questo codice, la macro funzionerà comunque.

Ora esaminiamo ogni riga di codice e descriviamo cosa e perché.

Il codice inizia con Sub seguito dal nome della macro e da parentesi vuote. Sub è l'abbreviazione di subroutine. Ogni subroutine (chiamata anche procedura) in VBA inizia con Sub e finisce Fine sott.

• Range("A2").Seleziona: questa riga seleziona la cella A2.
• ActiveCell.FormulaR1C1 = "Excel" - Questa riga inserisce il testo "Excel" nella cella attiva. Poiché abbiamo selezionato la cella A2 come primo passaggio, diventa la nostra cella attiva.
• Intervallo ("A3"). Seleziona: seleziona la cella A3. Ciò accade quando premiamo il tasto Invio dopo aver inserito il testo che risulta nella selezione della cella A3.

Spero che tu abbia una conoscenza di base su come registrare una macro in Excel.

Tieni presente che il codice registrato tramite un registratore di macro generalmente non è un codice efficiente o ottimizzato. Il registratore di macro spesso aggiunge passaggi aggiuntivi non necessari. Ma questo non significa che non sia necessario utilizzare un registratore di macro. Per coloro che stanno appena imparando VBA, un registratore di macro può essere un ottimo modo per analizzare e comprendere come funziona tutto in VBA.

Registrazione macro assoluta e relativa

Conosci già i collegamenti assoluti e relativi in ​​Excel? Se utilizzi un riferimento assoluto per registrare una macro, il codice VBA farà sempre riferimento alle stesse celle che hai utilizzato. Ad esempio, se selezioni la cella A2 e inserisci il testo "Excel", ogni volta, indipendentemente da dove ti trovi nel foglio di lavoro e indipendentemente dalla cella selezionata, il tuo codice inserirà il testo "Excel" nella cella A2.

Se utilizzi l'opzione di riferimento relativo per registrare una macro, VBA non si collegherà a un indirizzo di cella specifico. In questo caso, il programma si “sposterà” rispetto alla cella attiva. Ad esempio, supponiamo che tu abbia già selezionato la cella A1 e inizi a registrare la tua macro in modalità collegamento relativo. Ora selezioni la cella A2, inserisci Testo Excel e premere Invio. Ora se esegui questa macro non tornerà alla cella A2, ma si sposterà rispetto alla cella attiva. Ad esempio, se è selezionata la cella B3, si sposterà su B4, scriverà il testo "Excel" e poi si sposterà sulla cella K5.

Ora registriamo la macro in modalità collegamento relativo:

La macro in modalità collegamento relativo verrà salvata.

Ora fai quanto segue.

1. Seleziona una cella qualsiasi (tranne A1).
2. Vai alla scheda "Sviluppatore".
3. Nel gruppo Codice, fare clic sul pulsante Macro.
4. Nella finestra di dialogo Macro, fare clic sulla macro Riferimenti relativi salvata.
5. Fare clic sul pulsante "Esegui".

Come noterai, la macro non ha scritto il testo "Excel" nelle celle A2. Ciò è accaduto perché hai registrato la macro in modalità di riferimento relativo. Pertanto, il cursore si sposta rispetto alla cella attiva. Ad esempio, se lo fai mentre è selezionata la cella B3, andrà nel testo di Excel - cella B4 e finirà per selezionare la cella B5.

Ecco il codice registrato dal registratore macro:

Tieni presente che il codice non fa riferimento alle celle B3 o B4. La macro utilizza Activecell per fare riferimento alla cella corrente e all'offset relativo a quella cella.

Ignorare la parte Range("A1") del codice. Questo è uno di quei casi in cui il registratore di macro aggiunge codice non necessario che non serve a nulla e può essere rimosso. Senza di esso il codice funzionerà correttamente.

Cosa non puoi fare con un registratore di macro?

Il registratore di macro è ottimo per te in Excel e registra i tuoi passaggi esatti, ma potrebbe non essere la scelta giusta per te quando devi fare di più.

• Non è possibile eseguire il codice senza selezionare un oggetto. Ad esempio, se desideri che la macro passi al foglio di lavoro successivo ed evidenzi tutte le celle riempite nella colonna A senza uscire dal foglio di lavoro corrente, il registratore di macro non sarà in grado di farlo. In questi casi, è necessario modificare manualmente il codice.
• Non è possibile creare una funzione personalizzata utilizzando un registratore di macro. Con VBA è possibile creare funzioni personalizzate che possono essere utilizzate in un foglio di lavoro come normali funzioni.
• Non è possibile creare loop utilizzando il registratore di macro. Ma puoi registrare un'azione e aggiungere un loop manualmente nell'editor di codice.
• Non puoi analizzare le condizioni: puoi controllare le condizioni nel codice utilizzando un registratore di macro. Se scrivi manualmente il codice VBA, puoi utilizzare le istruzioni IF Then Else per valutare la condizione ed eseguire il codice se vero (o altro codice se falso).

Estensione di file Excel che contengono macro

Quando registri una macro o scrivi manualmente codice VBA in Excel, devi salvare il file con un'estensione di file abilitata per le macro (.xlsm).

Prima di Excel 2007 era sufficiente un formato file: .xls. Ma dal 2007, .xlsx è stata introdotta come estensione di file standard. I file salvati come .xlsx non possono contenere una macro. Quindi, se hai un file con estensione .xlsx e scrivi/registra una macro e la salvi, ti avviserà di salvarla in un formato abilitato per le macro e ti mostrerà la seguente finestra di dialogo:

Se si seleziona No, Excel salva il file in un formato abilitato per le macro. Ma se fai clic su Sì, Excel rimuoverà automaticamente tutto il codice dalla cartella di lavoro e salverà il file come cartella di lavoro in formato .xlsx. Pertanto, se nella cartella di lavoro è presente una macro, è necessario salvarla in formato .xlsm per salvarla.

Cos'è una macro? Macroè una sequenza salvata di azioni o un programma creato in VBA (Visual Basic for Application). Domande frequenti come scrivere una macro in Excel? Appena.

Quelli. se dobbiamo eseguire le stesse azioni più volte, possiamo ricordarle ed eseguirle con un solo pulsante. Non ti costringerò a imparare VBA né a suggerirti molte macro standard in questo articolo. In realtà, chiunque può creare/scrivere una macro in Excel. A questo scopo esiste la caratteristica più interessante e insolita di Excel: Registratore di macro(registrando le tue azioni sotto forma di codice). Quelli. puoi registrare le tue azioni, come in un video, e tradurle in codice (sequenza).

In breve, se esegui le stesse azioni ogni giorno, vale la pena capire come automatizzare questo processo. Continua a leggere per imparare come scrivere una macro in Excel?

1.Consenti macro

Menu (pulsante rotondo in alto a sinistra) - Opzioni di Excel- Centro protezione - Impostazioni Centro protezione - Impostazioni macro. Seleziona la casella di controllo "Abilita tutte le macro".

Oppure fai lo stesso nella scheda Sviluppatore

2. Abilita il menu Sviluppatore per lavoro veloce con le macro

Menu (pulsante rotondo in alto a sinistra) - Opzioni Excel - Generale - Mostra scheda Sviluppatore.

Affinché la macro creata venga salvata nella cartella di lavoro, è necessario salvare il file in .xlsm o .xlsb. Fare clic su Salva con nome - Cartella di lavoro Excel abilitata per macro o Cartella di lavoro binaria.

Quindi, come scrivere una macro in Excel?

È semplice. Andiamo a Excel. In fondo alla pagina, sotto le etichette dei fogli, è presente il pulsante “Registra macro”.

Fare clic sul pulsante e si aprirà la finestra "Registra macro". Diamo alla macro il nome che preferiamo nel campo Nome macro:. Impostiamo la combinazione di tasti che utilizzeremo in seguito per richiamarlo (facoltativo).

Fare clic su OK. Il registratore macro ha iniziato a registrare.

Tutto. Ora eseguiamo le azioni che dobbiamo ricordare come macro. Ad esempio, dobbiamo eliminare una riga e colorare la cella A1 di giallo.

Durante queste manipolazioni, al posto del pulsante “Registra macro” sotto i fogli, si illuminerà un quadrato, cliccando sul quale si interromperà l'esecuzione della macro.

Come lanciare quello che è successo? Fare clic sul pulsante, verrà visualizzata la finestra Seleziona macro, selezionare la macro desiderata, quindi fare clic sul pulsante Esegui.

Come posso vedere cosa è successo? Fare clic sul pulsante. Seleziona la macro desiderata e fai clic su Modifica. Si aprirà la finestra Registra macro (finestra VBA).

Il codice macro dovrebbe assomigliare a questo.

Sub Esempio1() " Esempio1 Righe macro("2:2").Seleziona selezione.Elimina Maiusc:=xlUp Range("A1").Seleziona con selezione.Interno .Pattern = xlSolid .PatternColorIndex = xlAutomatico.Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 Fine con End Sub

‘ è un simbolo di commento, ad es. questa riga non è inclusa nel codice. Una macro deve avere un nome (Sub Esempio1()) e un finale (End Sub).

Puoi leggere come creare un pulsante per una macro.

Quindi, puoi registrare qualsiasi tua azione in modo da poterla ripetere tutte le volte che vuoi!

Puoi leggere come salvare una macro per tutti i libri sul tuo computer.

Per fare ciò, utilizzare il Personal.

Puoi leggere come eseguire una macro in base alla condizione

Buona fortuna, sono sicuro che lo troverai molto utile!

Condividi il nostro articolo sui tuoi social network:

Con le tabelle o altri oggetti di Office si verificano spesso situazioni in cui è necessario eseguire le stesse azioni. Questo non solo richiede tempo extra, ma è anche molto fastidioso. Fortunatamente, ci sono delle macro per risolvere questo problema.

L'ambiente VBA è progettato per creare macro, ma non è necessario essere un programmatore o studiare Visual Basic for Application per imparare a scriverle. A questo scopo esistono strumenti appositamente sviluppati che creano codice VBA su tuo comando, senza richiedere ulteriori conoscenze da parte tua. Tuttavia, padroneggiare questa lingua non è così difficile.

Le macro vengono create nelle applicazioni utilizzando strumenti di registrazione. In questo caso, a una determinata combinazione di tasti viene assegnata la sequenza di azioni desiderata. Apri un'applicazione di Office. Seleziona il frammento che formatterai.

Selezionare la voce di menu “Strumenti” -> “Macro” -> “Avvia registrazione” (in Office 2007 – “Visualizza” -> “Macro” -> “Registra macro”). Nella finestra “Registra macro” che appare, imposta il nome della nuova macro; di default è “Macro1”, ma è meglio dargli un nome, soprattutto se ci saranno più macro. Taglia massima campi nome – 255 caratteri, punto e spazio non sono consentiti.

Decidi la scelta del pulsante o della combinazione di tasti che la tua macro utilizzerà in futuro. Prova a scegliere l'opzione più conveniente, soprattutto se è destinata ad un uso frequente. Selezionare la voce opportuna nel campo “Assegna macro”: “pulsante” o “tasti”.

Se selezioni "pulsante", si aprirà la finestra "Impostazioni rapide". Quando selezioni "tasti", devi solo inserire la combinazione sulla tastiera. Rivedi le "Combinazioni attuali" per evitare ripetizioni. Fare clic su "Assegna".

La macro creata in Word e PowerPoint sarà valida per tutti i documenti futuri. Per rendere disponibile una macro di Excel per tutti i documenti, salvala nel file personal.xls, che viene eseguito automaticamente all'apertura dell'applicazione. Eseguire il comando “Finestra” -> “Visualizza” e selezionare nella finestra che appare la riga con il nome del file personal.xls.

Immettere una descrizione della macro nel campo Descrizione. Fai clic su OK e tornerai al documento, ma ora puoi vedere un'icona di registrazione sul cursore del mouse. Formatta il testo con la sequenza di azioni che desideri automatizzare. Fai molta attenzione e non eseguire azioni non necessarie, poiché la macro le registrerà tutte e ciò influirà sul tempo di esecuzione futuro.

Eseguire il comando “Strumenti” -> “Macro” -> “Interrompi registrazione”. Hai creato un oggetto VBA senza scrivere tu stesso una sola riga di codice. Se però hai comunque bisogno di apportare modifiche manualmente, inserisci l'oggetto tramite la sezione “Macro”, il comando “Cambia” o utilizzando la combinazione di tasti Alt+F8.

Pochi sanno che la prima versione del popolare prodotto Microsoft Excel è apparsa nel 1985. Da allora, ha subito diverse modifiche ed è richiesto da milioni di utenti in tutto il mondo. Allo stesso tempo, molte persone lavorano solo con una piccola parte delle capacità di questo processore di fogli di calcolo e non si rendono nemmeno conto di come la capacità di programmare in Excel potrebbe semplificargli la vita.

Cos'è VBA

La programmazione in Excel viene eseguita utilizzando il linguaggio di programmazione Visual Basic for Application, originariamente integrato nel più famoso processore di fogli di calcolo di Microsoft.

Gli esperti attribuiscono ai suoi vantaggi la relativa facilità di apprendimento. Come dimostra la pratica, anche gli utenti che non hanno competenze di programmazione professionali possono padroneggiare le basi di VBA. Le funzionalità di VBA includono l'esecuzione di script in un ambiente applicativo per ufficio.

Lo svantaggio del programma sono i problemi associati alla compatibilità di diverse versioni. Sono causati dal fatto che accede il codice del programma VBA funzionalità, che sono presenti in nuova versione prodotto, ma mancante in quello vecchio. Inoltre, gli svantaggi includono l'eccessiva apertura del codice alle modifiche da parte di estranei. Tuttavia Microsoft Office, così come IBM Lotus Symphony, consentono all'utente di crittografare il codice iniziale e impostare una password per visualizzarlo.

Oggetti, collezioni, proprietà e metodi

Questi sono i concetti che devono essere compresi da chi si appresta a lavorare nell'ambiente VBA. Prima di tutto, devi capire cos'è un oggetto. In Excel queste funzioni sono foglio, cartella di lavoro, cella e intervallo. Questi oggetti hanno una gerarchia speciale, ad es. obbedire a vicenda.

Il principale è Application, che corrisponde a Programma Excel. Poi arrivano cartelle di lavoro, fogli di lavoro e intervallo. Ad esempio, per accedere alla cella A1 di un foglio di lavoro specifico, è necessario specificare un percorso che tenga conto della gerarchia.

Per quanto riguarda il concetto di “collezione”, si tratta di un gruppo di oggetti della stessa classe, che nella voce ha la forma ChartObjects. Anche i suoi singoli elementi sono oggetti.

Il concetto successivo sono le proprietà. Sono una caratteristica necessaria di qualsiasi oggetto. Ad esempio, per Intervallo è Valore o Formula.

I metodi sono comandi che indicano cosa è necessario fare. Quando si scrive codice in VBA, devono essere separati dall'oggetto da un punto. Ad esempio, come verrà mostrato in seguito, molto spesso quando si programma in Excel viene utilizzato il comando Cells(1,1).Seleziona. Significa che devi selezionare una cella con le coordinate

Selection.ClearContents viene spesso utilizzato insieme ad esso. Eseguire ciò significa cancellare il contenuto della cella selezionata.

Come iniziare

Quindi devi andare all'applicazione VB, per la quale devi solo utilizzare la combinazione di tasti "Alt" e "F11". Ulteriore:

• nella barra dei menu situata nella parte superiore della finestra, fare clic sull'icona accanto all'icona di Excel;
• seleziona il comando Mudule;
• salva cliccando sull'icona con l'immagine;
• scrivono, diciamo, una bozza del codice.

Sembra questo:

Sottoprogramma()

"Il nostro codice

Tieni presente che la riga "Il nostro codice" verrà evidenziata con un colore diverso (verde), il motivo è l'apostrofo posto all'inizio della riga, che indica che segue un commento.

Ora puoi scrivere qualsiasi codice e creare un nuovo strumento per te stesso VBA Excel(vedi esempi di programmi di seguito). Naturalmente sarà molto più semplice per chi ha familiarità con le basi di Visual Basic. Tuttavia, anche chi non li possiede, se lo desidera, può abituarsi abbastanza velocemente.

Macro in Excel

Questo nome nasconde i programmi scritti in Visual Basic for Application. Pertanto, la programmazione in Excel crea macro con il codice richiesto. Grazie a questa funzionalità, il processore dei fogli di calcolo Microsoft si sviluppa, adattandosi alle esigenze di un utente specifico. Avendo capito come creare moduli per scrivere macro, puoi iniziare a pensare esempi specifici Programmi Excel VBA. È meglio iniziare con i codici più elementari.

Esempio 1

Compito: scrivere un programma che copierà il valore del contenuto di una cella e poi lo scriverà in un'altra.

Per questo:

• aprire la scheda “Visualizza”;
• vai sull'icona “Macro”;
• cliccare su “Registra macro”;
• compila il modulo che si apre.

Per semplicità, lascia “Macro1” nel campo “Nome macro”, e inserisci, ad esempio, hh nel campo “Scorciatoia da tastiera” (questo significa che puoi avviare il programma con il comando rapido “Ctrl+h”). Premere Invio.

Ora che la registrazione della macro è già iniziata, il contenuto di una cella viene copiato in un'altra. Ritorna all'icona originale. Fare clic su "Registra macro". Questa azione significa la fine del programma.

• tornare alla riga “Macro”;
• selezionare “Macro 1” dall'elenco;
• fare clic su “Esegui” (la stessa azione viene avviata avviando la combinazione di tasti “Ctrl+hh”).

Di conseguenza, si verifica l'azione eseguita durante la registrazione della macro.

Ha senso vedere come appare il codice. Per fare ciò, torna alla riga “Macro” e fai clic su “Cambia” o “Accedi”. Di conseguenza, si trovano nell'ambiente VBA. In realtà, il codice macro stesso si trova tra le righe Sub Macro1() e End Sub.

Se la copia è stata eseguita, ad esempio, dalla cella A1 alla cella C1, una delle righe di codice sarà simile a Range("C1").Seleziona. Tradotto appare come “Intervallo(“C1”).Seleziona”, ovvero va in VBA Excel, nella cella C1.

La parte attiva del codice viene completata dal comando ActiveSheet.Paste. Significa scrivere il contenuto della cella selezionata (in questo caso A1) nella cella selezionata C1.

Esempio 2

I loop VBA ti aiutano a creare varie macro in Excel.

I loop VBA ti aiutano a creare varie macro. Supponiamo di avere una funzione y=x + x 2 + 3x 3 - cos(x). È necessario creare una macro per ottenere il suo grafico. Questo può essere fatto solo utilizzando i loop VBA.

I valori iniziale e finale dell'argomento della funzione sono x1=0 e x2=10. Inoltre, è necessario inserire una costante: il valore per la fase di modifica dell'argomento e il valore iniziale per il contatore.

Tutti gli esempi di macro VBA Excel vengono creati utilizzando la stessa procedura presentata sopra. In questo caso particolare, il codice è simile a:

Sottoprogramma()

passo = 0,1

Fai mentre x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)

y=x1 + x1^2 + 3*x1^3 - Cos(x1)

Cells(i, 1).Valore = x1 (il valore x1 viene scritto nella cella con coordinate (i,1))

Cells(i, 2).Valore = y (il valore y viene scritto nella cella con coordinate (i,2))

i = i + 1 (il contatore è attivo);

x1 = x1 + shag (l'argomento cambia in base al valore del passo);

Fine sott.

Come risultato dell'esecuzione di questa macro in Excel, otteniamo due colonne, la prima delle quali contiene i valori per x e la seconda per y.

Quindi viene creato un grafico da essi in modo standard per Excel.

Esempio 3

Per implementare i loop in VBA Excel 2010, come in altre versioni, insieme alla costruzione Do While già fornita, viene utilizzato For.

Considera un programma che creerà una colonna. In ciascuna delle sue celle verranno scritti i quadrati del numero di riga corrispondente. L'utilizzo del costrutto For ti consentirà di scriverlo molto brevemente, senza utilizzare un contatore.

Per prima cosa devi creare una macro come descritto sopra. Successivamente scriviamo il codice stesso. Supponiamo che siamo interessati ai valori per 10 celle. Il codice è simile a questo.

Per i = da 1 a 10 Avanti

Il comando è tradotto nel linguaggio “umano” come “Ripeti da 1 a 10 in passi di uno”.

Se il compito è ottenere una colonna con quadrati, ad esempio, di tutti i numeri dispari compresi tra 1 e 11, allora scriviamo:

Per i = da 1 a 10 passo 1 Successivo.

Qui il passo è un passo. In questo caso è pari a due. Di default l'assenza di questa parola nel ciclo significa che il passo è unico.

I risultati ottenuti devono essere salvati nelle celle numerate (i,1). Quindi, ogni volta che viene avviato il ciclo, con i che aumenta di un passo, il numero di riga aumenterà automaticamente. In questo modo il codice sarà ottimizzato.

Nel complesso il codice sarà simile a:

Sottoprogramma()

For i = 1 To 10 Passo 1 (puoi semplicemente scrivere For i = 1 To 10)

Cells(i, 1).Valore = i ^ 2 (ovvero il valore del quadrato i viene scritto nella cella (i,1)

Successivo (in un certo senso svolge il ruolo di contatore e significa un altro inizio del ciclo)

Fine sott.

Se tutto è stato eseguito correttamente, inclusa la registrazione e l'esecuzione della macro (vedere le istruzioni sopra), quando la chiami, ogni volta otterrai una colonna della dimensione specificata (in questo caso, composta da 10 celle).

Esempio 4

IN Vita di ogni giorno Molto spesso sorge la necessità di prendere una decisione o l'altra a seconda di alcune condizioni. Non puoi farne a meno in VBA Excel. Esempi di programmi in cui l'ulteriore corso di esecuzione dell'algoritmo viene scelto e non predeterminato inizialmente, molto spesso utilizzano la costruzione If ...Then (per casi complessi) If ...Then ...END If.

Consideriamo un caso specifico. Supponiamo di dover creare una macro per Excel in modo che nella cella con le coordinate (1,1) venga scritto quanto segue:

1 se l'argomento è positivo;

0 se l'argomento è nullo;

-1 se l'argomento è negativo.

Inizia la creazione di tale macro per Excel in modo standard, attraverso l'uso dei tasti di scelta rapida Alt e F11. Successivamente viene scritto il seguente codice:

Sottoprogramma()

x= Celle(1, 1).Valore (questo comando assegna a x il valore del contenuto della cella alle coordinate (1, 1))

Se x>0 Allora Celle (1, 1). Valore = 1

Se x=0 allora Celle (1, 1). Valore = 0

Se x<0 Then Cells(1, 1).Value = -1

Fine sott.

Non resta che eseguire la macro e ottenere il valore richiesto per l'argomento in Excel.

Funzioni VBA

Come avrai già notato, programmare nel più famoso processore per fogli di calcolo di Microsoft non è così difficile. Soprattutto se impari a usare le funzioni VBA. In totale, questo linguaggio di programmazione, creato appositamente per scrivere applicazioni in Excel e Word, dispone di circa 160 funzioni. Possono essere divisi in diversi grandi gruppi. Questo:

• Funzioni matematiche. Applicandoli all'argomento si ottiene il valore del coseno, del logaritmo naturale, della parte intera, ecc.
• Funzioni finanziarie. Grazie alla loro disponibilità e all'utilizzo della programmazione Excel è possibile ottenere efficaci strumenti per i calcoli contabili e finanziari.
• Funzioni di elaborazione degli array. Questi includono Array, IsArray; Llegato; UBound.
• Funzioni VBA di Excel per stringhe. Questo è un gruppo abbastanza numeroso. Ciò include, ad esempio, le funzioni Spazio per creare una stringa con il numero di spazi pari all'argomento intero, o Asc per convertire i caratteri in codice ANSI. Tutti sono ampiamente utilizzati e consentono di lavorare con le righe in Excel, creando applicazioni che facilitano notevolmente il lavoro con queste tabelle.
• Funzioni di conversione del tipo di dati. Ad esempio, CVar restituisce il valore dell'argomento Expression convertendolo nel tipo di dati Variant.
• Funzioni per lavorare con le date. Essi espandono notevolmente quelli standard, pertanto la funzione WeekdayName restituisce il nome (completo o parziale) del giorno della settimana tramite il suo numero. Ancora più utile è il timer. Fornisce il numero di secondi che sono trascorsi dalla mezzanotte ad un punto specifico della giornata.
• Funzioni per convertire un argomento numerico in diversi sistemi numerici. Ad esempio, Oct restituisce i numeri in ottale.
• Funzioni di formattazione. Il più importante di questi è Formato. Restituisce un valore Variant con un'espressione formattata secondo le istruzioni specificate nella dichiarazione di formato.
• eccetera.

Lo studio delle proprietà di queste funzioni e della loro applicazione amplierà significativamente la portata di Excel.

Esempio 5

Proviamo a passare alla risoluzione di problemi più complessi. Per esempio:

Viene fornito un documento cartaceo che riporta il livello effettivo dei costi dell'impresa. Necessario:

• sviluppare la sua parte modello utilizzando il foglio di calcolo Excel;
• creare un programma VBA che richiederà i dati iniziali per compilarlo, eseguire i calcoli necessari e compilare con essi le celle del modello corrispondenti.

Consideriamo una delle opzioni di soluzione.

Crea un modello

Tutte le azioni vengono eseguite su un foglio standard in Excel. Le celle libere sono riservate per l'inserimento dei dati relativi al nome dell'azienda consumatrice, all'importo dei costi, al loro livello e al fatturato. Poiché il numero di aziende (società) per le quali viene compilato il rapporto non è fisso, le celle per l'inserimento dei valori in base ai risultati e al nome dello specialista non sono riservate in anticipo. Al foglio di lavoro viene assegnato un nuovo nome. Ad esempio, "rapporti".

Variabili

Per scrivere un programma per compilare automaticamente un modello, è necessario selezionare le notazioni. Questi verranno utilizzati per le variabili:

• NN - numero della riga della tabella corrente;
• TP e TF: fatturato commerciale pianificato ed effettivo;
• SF e SP - importo dei costi effettivi e pianificati;
• IP e IF: livello dei costi pianificati ed effettivi.

Indichiamo l'accumulo del totale per questa colonna utilizzando le stesse lettere, ma con il “prefisso” Itog. Ad esempio, ItogTP - si riferisce alla colonna della tabella intitolata "fatturato pianificato".

Risolvere un problema utilizzando la programmazione VBA

Utilizzando le notazioni introdotte, otteniamo formule per le deviazioni. Se devi calcolare in%, abbiamo (F - P) / P * 100 e in totale - (F - P).

È meglio inserire i risultati di questi calcoli direttamente nelle celle appropriate in un foglio di calcolo Excel.

I totali effettivi e previsti si ottengono utilizzando le formule ItogP=ItogP + P e ItogF=ItogF+ F.

Per le deviazioni utilizzare = (ItogF - ItogP) / ItogP * 100 se il calcolo viene eseguito in percentuale e nel caso di un valore totale - (ItogF - ItogP).

I risultati vengono nuovamente scritti immediatamente nelle celle appropriate, quindi non è necessario assegnarli a variabili.

Prima di eseguire il programma creato, è necessario salvare la cartella di lavoro, ad esempio, con il nome "Report1.xls".

Il pulsante "Crea tabella di reporting" deve essere premuto solo una volta dopo aver inserito le informazioni sull'intestazione. Ci sono altre regole che dovresti conoscere. In particolare, il pulsante “Aggiungi riga” dovrà essere cliccato ogni volta dopo aver inserito nella tabella i valori per ciascuna tipologia di attività. Dopo aver inserito tutti i dati, è necessario fare clic sul pulsante “Fine” e quindi passare alla finestra di Excel.

Ora sai come risolvere i problemi di Excel utilizzando le macro. La capacità di utilizzare vba excel (vedi esempi di programmi sopra) potrebbe essere necessaria anche per lavorare nell'editor di testo più popolare al momento, Word. In particolare, è possibile creare i pulsanti del menu scrivendo, come mostrato all'inizio dell'articolo, oppure scrivendo codice, grazie al quale si possono effettuare molte operazioni sul testo premendo i tasti di servizio o tramite la scheda “Visualizza” e l'icona "Macro".