Gli eventi si verificano continuamente quando un utente apre una cartella di lavoro di Excel e inizia a eseguire varie azioni come l'immissione di dati nelle celle o lo spostamento tra i fogli
All'interno dell'editor di Visual Basic (ALT+F11), sono già impostate sottoroutine che possono essere attivate quando l'utente fa qualcosa, ad es. immissione di dati in una cella. La subroutine non fornisce alcun codice di azione, solo un'istruzione "Sub" e un'istruzione "End Sub" senza alcun codice tra di loro. Sono effettivamente dormienti, quindi non succede nulla finché non inserisci un codice.
Ecco un esempio basato sull'evento "Cambia" in un foglio di lavoro:
In qualità di programmatore VBA, puoi aggiungere del codice per far accadere determinate cose quando l'utente esegue un'azione specifica. Questo ti dà la possibilità di controllare l'utente e di impedire che intraprenda azioni che non vuoi che facciano e che potrebbero danneggiare la tua cartella di lavoro. Ad esempio, potresti desiderare che salvino la propria copia individuale della cartella di lavoro con un altro nome, in modo che non influiscano sull'originale, che potrebbe essere utilizzato da un numero di utenti.
Se chiudono la cartella di lavoro, verrà automaticamente richiesto di salvare le modifiche. Tuttavia, la cartella di lavoro ha un evento "Prima di chiudere" ed è possibile inserire il codice per impedire che la cartella di lavoro venga chiusa e venga attivato un evento "Salva". Puoi quindi aggiungere un pulsante al foglio di lavoro stesso e inserire la tua routine "Salva" su di esso. Puoi anche disabilitare la routine "Salva" utilizzando l'evento "Prima di salvare"
La comprensione di come funzionano gli eventi è assolutamente essenziale per un programmatore VBA.
Tipi di eventi
cartella di lavoro Eventi - questi eventi vengono attivati in base a ciò che l'utente fa con la cartella di lavoro stessa. Includono azioni dell'utente come l'apertura della cartella di lavoro, la chiusura della cartella di lavoro, il salvataggio della cartella di lavoro, l'aggiunta o l'eliminazione di un foglio
Foglio di lavoro Eventi - questi eventi vengono attivati da un utente che esegue azioni su un foglio di lavoro specifico. Ogni foglio di lavoro all'interno della cartella di lavoro ha un modulo di codice individuale, che contiene vari eventi specifici per quel foglio di lavoro (non per tutti i fogli di lavoro). Questi includono azioni dell'utente come modificare il contenuto di una cella, fare doppio clic su una cella o fare clic con il pulsante destro del mouse su una cella.
Eventi di controllo Active X - I controlli Active X possono essere aggiunti a un foglio di lavoro utilizzando l'icona "Inserisci" nella scheda "Sviluppatore" nella barra multifunzione di Excel. Si tratta spesso di pulsanti per consentire all'utente di eseguire varie azioni sotto il controllo del codice, ma possono anche essere oggetti come menu a discesa. L'utilizzo dei controlli Active X rispetto ai controlli Form nel foglio di lavoro offre un ampio margine di programmabilità. I controlli Active X offrono molta più flessibilità dal punto di vista della programmazione rispetto all'utilizzo dei controlli del modulo in un foglio di lavoro.
Ad esempio, potresti avere due controlli a discesa nel foglio di lavoro. Vuoi che l'elenco disponibile nel secondo menu a discesa sia basato su ciò che l'utente ha scelto nel primo menu a discesa. Utilizzando l'evento "Cambia" nel primo menu a discesa, puoi creare codice per leggere ciò che l'utente ha selezionato e quindi aggiornare il secondo menu a discesa. Puoi anche disattivare il secondo menu a discesa finché l'utente non ha effettuato una selezione nel primo menu a discesa
Eventi Form utente - È possibile inserire e progettare un modulo dall'aspetto professionale da utilizzare come pop-up. Tutti i controlli che inserisci nel modulo sono controlli Active X e hanno gli stessi eventi dei controlli Active X che potresti inserire in un foglio di lavoro
Eventi del grafico - Questi eventi sono correlati solo a un foglio grafico e non a un grafico che appare come parte di un foglio di lavoro. Questi eventi includono il ridimensionamento del grafico o la selezione del grafico.
Eventi applicativi - Questi usano l'oggetto Application in VBA. Gli esempi consentirebbero di attivare il codice quando viene premuto un determinato tasto o quando viene raggiunto un certo tempo. Potresti programmare una situazione in cui la cartella di lavoro viene lasciata aperta 24 ore su 24, 7 giorni su 7 e importa i dati da una fonte esterna durante la notte a un'ora predeterminata.
Pericoli derivanti dall'utilizzo del codice negli eventi
Quando scrivi codice per fare qualcosa quando l'utente esegue una determinata azione, devi tenere presente che il tuo codice potrebbe attivare altri eventi, che potrebbero mettere il tuo codice in un ciclo continuo.
Ad esempio, supponiamo di utilizzare l'evento "Modifica" su un foglio di lavoro in modo che quando l'utente inserisce un valore in una cella, un calcolo basato su quella cella viene inserito nella cella immediatamente a destra di essa.
Il problema qui è che l'inserimento del valore calcolato nella cella attiva un altro evento "Cambia", che a sua volta attiva un altro evento "Cambia", e così via fino a quando il codice non ha esaurito le colonne da utilizzare e genera un messaggio di errore.
Devi riflettere attentamente quando scrivi il codice per l'evento per assicurarti che altri eventi non vengano attivati inavvertitamente
Disattiva eventi
È possibile utilizzare il codice per disabilitare gli eventi per aggirare questo problema. Quello che dovrai fare è incorporare il codice per disabilitare gli eventi mentre il tuo codice evento è in esecuzione e quindi riattivare gli eventi alla fine del codice. Ecco un esempio di come farlo:
| 1234 | Sub DisabilitaEventi()Application.EnableEvents = FalseApplication.EnableEvents = TrueFine sottotitolo |
Tieni presente che questo disabilita tutti gli eventi direttamente nell'applicazione Excel, quindi ciò influenzerebbe anche altre funzioni all'interno di Excel. Se lo usi per qualsiasi motivo, assicurati che gli eventi vengano riattivati in seguito.
Importanza dei parametri negli eventi
Gli eventi di solito hanno parametri che puoi usare per saperne di più su cosa sta facendo l'utente e sulla posizione della cella in cui si trova.
Ad esempio, l'evento di modifica del foglio di lavoro ha il seguente aspetto:
| 1 | Private Sub Worksheet_Change (ByVal Target As Range) |
Utilizzando l'oggetto range, puoi scoprire le coordinate di riga/colonna della cella in cui si trova effettivamente l'utente.
| 1234 | Private Sub Worksheet_Change (ByVal Target As Range)MsgBox Target.ColumnMsgBox Target.RowFine sottotitolo |
Se vuoi che il tuo codice funzioni solo su una determinata colonna o numero di riga, aggiungi una condizione che esce dalla subroutine se la colonna non è quella richiesta.
| 123 | Private Sub Worksheet_Change (ByVal Target As Range)Se Target.Column 2 Quindi esci da SubFine sottotitolo |
Questo aggira il problema del tuo codice che attiva più eventi, poiché funzionerà solo se l'utente ha modificato una cella nella colonna 2 (colonna B)
Esempi di eventi della cartella di lavoro (non esaustivi)
Gli eventi della cartella di lavoro si trovano sotto l'oggetto "ThisWorkbook" in VBE Project Explorer. Dovrai selezionare "Cartella di lavoro" nel primo menu a discesa nella finestra del codice, quindi il secondo menu a discesa ti mostrerà tutti gli eventi disponibili
Evento aperto cartella di lavoro
Questo evento viene generato ogni volta che la cartella di lavoro viene aperta da un utente. Potresti usarlo per inviare un messaggio di benvenuto a un utente catturando il suo nome utente
| 123 | Cartella di lavoro secondaria privata_Apri()MsgBox "Benvenuto " & Application.UserNameFine sottotitolo |
Puoi anche controllare il loro nome utente rispetto a un elenco tenuto su un foglio nascosto per vedere se sono autorizzati ad accedere alla cartella di lavoro. Se non sono utenti autorizzati, puoi visualizzare un messaggio e chiudere la cartella di lavoro in modo che non possano utilizzarla.
Evento nuovo foglio cartella di lavoro
Questo evento viene attivato quando un utente aggiunge un nuovo foglio alla cartella di lavoro
Puoi usare questo codice solo per consentire a te stesso di aggiungere un nuovo foglio, piuttosto che avere utenti diversi che aggiungono tutti fogli e creano confusione nella cartella di lavoro
| 1234567 | Cartella di lavoro secondaria privata_NewSheet (ByVal Sh come oggetto)Application.DisplayAlerts = FalseSe Application.UserName "Richard" ThenSh.EliminaFinisci seApplication.DisplayAlerts = TrueFine sottotitolo |
Tieni presente che è necessario disattivare gli avvisi poiché verrà visualizzato un avviso utente quando il foglio viene eliminato che consente all'utente di aggirare il codice. Assicurati di riattivare gli avvisi in seguito!
Stanco di cercare esempi di codice VBA? Prova AutoMacro!
Cartella di lavoro prima di salvare l'evento
Questo evento viene attivato quando l'utente fa clic sull'icona "Salva", ma prima che il "Salva" abbia effettivamente luogo
Come descritto in precedenza, potresti voler impedire agli utenti di salvare le modifiche nella cartella di lavoro originale e costringerli a creare una nuova versione utilizzando un pulsante nel foglio di lavoro. Tutto ciò che devi fare è modificare il parametro "Annulla" su True e la cartella di lavoro non potrà mai essere salvata con il metodo convenzionale.
| 123 | Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)Annulla = VeroFine sottotitolo |
Cartella di lavoro prima della chiusura dell'evento
È possibile utilizzare questo evento per impedire agli utenti di chiudere la cartella di lavoro e forzarli nuovamente a uscire tramite un pulsante del foglio di lavoro. Ancora una volta, imposti il parametro "Annulla" su "Vero". La X rossa nell'angolo in alto a destra della finestra di Excel non funziona più.
| 123 | Cartella di lavoro secondaria privata_BeforeClose (Annulla come booleano)Annulla = VeroFine sottotitolo |
Esempi di eventi del foglio di lavoro (non esaustivo)
Gli eventi del foglio di lavoro si trovano sotto l'oggetto nome foglio specifico in VBE Project Explorer. Dovrai selezionare "Foglio di lavoro" nel primo menu a discesa nella finestra del codice e quindi il secondo menu a discesa ti mostrerà tutti gli eventi disponibili
Evento di modifica del foglio di lavoro
Questo evento viene attivato quando un utente apporta una modifica a un foglio di lavoro, ad esempio inserendo un nuovo valore in una cella
È possibile utilizzare questo evento per inserire un valore o un commento aggiuntivo accanto alla cella modificata, ma come discusso in precedenza, non si desidera avviare un ciclo di eventi.
| 12345 | Private Sub Worksheet_Change (ByVal Target As Range)Se Target.Column 2 Quindi esci da SubActiveSheet.Cells(Target.Row, Target.Column + 1). Valore = _ActiveSheet.Cells(Target.Row, Target.Column). Valore * 1.1Fine sottotitolo |
In questo esempio, il codice funzionerà solo se il valore viene immesso nella colonna B (colonna 2). Se questo è vero, aggiungerà il 10% al numero e lo posizionerà nella successiva cella disponibile
Foglio di lavoro prima dell'evento doppio clic
Questo evento scatterà il codice se un utente fa doppio clic su una cella. Questo può essere estremamente utile per rapporti finanziari come un bilancio o un conto profitti e perdite in cui è probabile che i numeri vengano contestati dai gestori, specialmente se la linea di fondo è negativa!
Puoi usarlo per fornire una funzione di approfondimento, in modo che quando il manager sfida un determinato numero, tutto ciò che deve fare è fare doppio clic sul numero e la suddivisione appare come parte del rapporto.
Questo è molto impressionante dal punto di vista di un utente e li salva chiedendo costantemente "perché questo numero è così alto?"
Dovresti scrivere il codice per scoprire l'intestazione / i criteri per il numero (usando le proprietà dell'oggetto di destinazione) e quindi filtrare i dati tabulari e quindi copiarli nel report.
Programmazione VBA | Il generatore di codice funziona per te!
Foglio di lavoro Attiva evento
Questo evento si verifica quando l'utente si sposta da un foglio all'altro. Si applica al nuovo foglio in cui l'utente si sta spostando.
Potrebbe essere utilizzato per garantire che il nuovo foglio sia completamente calcolato prima che l'utente inizi a fare qualcosa su di esso. Può anche essere utilizzato per ricalcolare solo quel particolare foglio senza ricalcolare l'intera cartella di lavoro. Se la cartella di lavoro è grande e contiene una formula complicata, il ricalcolo di un foglio consente di risparmiare molto tempo
| 123 | Foglio di lavoro secondario privato_Activate()ActiveSheet.CalculateFine sottotitolo |
Eventi di controllo Active X (non esaustivo)
Come discusso in precedenza, puoi aggiungere i controlli Active X direttamente su un foglio di lavoro. Questi possono essere pulsanti di comando, menu a discesa e caselle di riepilogo
Gli eventi Active X si trovano sotto l'oggetto nome foglio specifico (dove è stato aggiunto il controllo) in Esplora progetti VBE. Dovrai selezionare il nome del controllo Active X nel primo menu a discesa nella finestra del codice e poi il secondo menu a discesa ti mostrerà tutti gli eventi disponibili
Pulsante di comando Fare clic su Evento
Quando hai inserito un pulsante di comando su un foglio di calcolo, vorrai che esegua un'azione. Puoi farlo inserendo il codice sull'evento Click.
Puoi facilmente inserire un messaggio "Sei sicuro?" su questo in modo che venga effettuato un controllo prima dell'esecuzione del codice
| 12345 | Sub CommandButton privato1_Click ()Pulsante DimRet come varianteButtonRet = MsgBox("Sei sicuro di volerlo fare?", vbQuestion Or vbYesNo)Se ButtonRet = vbNo, quindi esci da SubFine sottotitolo |
Evento di modifica a discesa (casella combinata)
Un elenco a discesa Active X ha un evento di modifica, in modo che se un utente seleziona un particolare elemento dall'elenco a discesa, è possibile acquisire la propria scelta utilizzando questo evento e quindi scrivere il codice per adattare di conseguenza altre parti del foglio o della cartella di lavoro.
| 123 | Sub ComboBox1_Change privato ()MsgBox "Hai selezionato " & ComboBox1.TextFine sottotitolo |
Programmazione VBA | Il generatore di codice funziona per te!
Casella di controllo (casella di controllo) Fare clic su Evento
È possibile aggiungere un segno di spunta o una casella di controllo a un foglio di lavoro in modo da fornire scelte di opzioni per l'utente. Puoi utilizzare l'evento click su di esso per vedere se l'utente ha modificato qualcosa al riguardo. I valori restituiti sono True o False a seconda che sia stato selezionato o meno.
| 123 | CheckBox secondario privato1_Click ()MsgBox CheckBox1.ValueFine sottotitolo |
Eventi UserForm (non esaustivi)
Excel ti offre la possibilità di progettare i tuoi moduli. Questi possono essere molto utili da utilizzare come pop-up per raccogliere informazioni o per fornire scelte multiple all'utente. Usano i controlli Active X come descritto in precedenza e hanno esattamente gli stessi eventi, sebbene gli eventi dipendano molto dal tipo di controllo.
Ecco un esempio di un semplice modulo:
Quando viene visualizzato, ecco come appare sullo schermo
Utilizzerai gli eventi nel modulo per eseguire operazioni come inserire un nome di società predefinito quando il modulo viene aperto, per verificare che l'input del nome della società corrisponda a quello già presente nel foglio di calcolo e non sia stato scritto in modo errato e per aggiungere codice al clic eventi sui pulsanti 'OK' e 'Annulla'
Il codice e gli eventi dietro il modulo possono essere visualizzati facendo doppio clic in qualsiasi punto del modulo
Il primo menu a discesa consente di accedere a tutti i controlli del modulo. Il secondo menu a discesa darà accesso agli eventi
Evento di attivazione modulo utente
Questo evento viene attivato quando il modulo viene attivato, normalmente quando viene visualizzato. Questo evento può essere utilizzato per impostare i valori predefiniti, ad es. un nome di società predefinito nella casella di testo del nome della società
| 123 | Modulo utente secondario privato_Activate()TextBox1.Text = "Nome della mia azienda"Fine sottotitolo |
Programmazione VBA | Il generatore di codice funziona per te!
Modifica evento
La maggior parte dei controlli sul modulo ha un evento di modifica, ma in questo esempio, la casella di testo del nome della società può utilizzare l'evento per porre una restrizione sulla lunghezza del nome della società da inserire
| 123456 | Sub TextBox1_Change privato ()Se Len (TextBox1.Text) > 20 AlloraMsgBox "Il nome è limitato a 20 caratteri", vbCriticalTextBox1.Text = ""Finisci seFine sottotitolo |
Fare clic su Evento
Puoi utilizzare questo evento per eseguire un'azione dall'utente facendo clic sui controlli nel modulo o anche sul modulo stesso
In questo modulo è presente un pulsante "OK" e, dopo aver raccolto il nome di un'azienda, vorremmo inserirlo in una cella del foglio di calcolo per riferimento futuro
| 1234 | Sub CommandButton privato1_Click ()ActiveSheet.Range("A1"). Valore = TextBox1.TextIo.NascondiFine sottotitolo |
Questo codice agisce quando l'utente fa clic sul pulsante "OK". Mette il valore nella casella di input del nome della società nella cella A1 sul foglio attivo e quindi nasconde il modulo in modo che il controllo dell'utente venga restituito al foglio di lavoro.
Eventi del grafico
Gli eventi del grafico funzionano solo su grafici che si trovano su un foglio grafico separato e non su un grafico incorporato in un foglio di lavoro standard
Gli eventi del grafico sono in qualche modo limitati e non possono essere utilizzati in un foglio di lavoro in cui potresti avere più grafici. Inoltre, gli utenti non vogliono necessariamente passare da un foglio di lavoro contenente numeri a un foglio grafico: qui non c'è un impatto visivo immediato
L'evento più utile sarebbe scoprire il componente di un grafico su cui un utente ha cliccato, ad es. un segmento in un grafico a torta o una barra in un grafico a barre, ma questo non è un evento disponibile nell'intervallo standard di eventi.
Questo problema può essere risolto utilizzando un modulo di classe per aggiungere un evento "Mouse Down" che restituirà i dettagli del componente del grafico su cui l'utente ha fatto clic. Viene utilizzato su un grafico all'interno di un foglio di lavoro.
Ciò comporta una codifica molto complicata, ma i risultati sono spettacolari. È possibile creare drill down, ad es. l'utente fa clic su un segmento del grafico a torta e immediatamente quel grafico viene nascosto e al suo posto viene visualizzato un secondo grafico che mostra un grafico a torta di dettaglio per il segmento originale, oppure è possibile produrre i dati tabulari che supportano quel segmento del grafico a torta.
Eventi applicativi
È possibile utilizzare l'oggetto Application in VBA per attivare il codice in base a un particolare evento
Programmazione VBA | Il generatore di codice funziona per te!
Applicazione.OnTime
Ciò può consentire di attivare una parte di codice a intervalli regolari finché la cartella di lavoro viene caricata in Excel. Potresti voler salvare automaticamente la cartella di lavoro in una cartella diversa ogni 10 minuti o lasciare il foglio di lavoro in esecuzione durante la notte in modo da importare i dati più recenti da una fonte esterna.
In questo esempio, viene inserita una sottoroutine in un modulo. Visualizza una finestra di messaggio ogni 5 minuti, anche se questa potrebbe essere facilmente un'altra procedura codificata. Allo stesso tempo, reimposta il timer sull'ora corrente più altri 5 minuti.
Ogni volta che viene eseguito, il timer si reimposta per eseguire la stessa sottoroutine in altri 5 minuti.
| 1234 | Sub TestOnTime()MsgBox "Test in tempo"Application.OnTime (Now() + TimeValue("00:05:00")), "TestOnTime"Fine sottotitolo |
Applicazione.OnKey
Questa funzione consente di progettare i propri tasti di scelta rapida. Puoi fare in modo che qualsiasi combinazione di tasti chiami una sottoroutine della tua creazione.
In questo esempio la lettera "a" viene reindirizzata in modo che invece di inserire una "a" in una cella, verrà visualizzata una finestra di messaggio. Questo codice deve essere inserito in un modulo inserito.
| 123456 | Sub TestKeyPress()Application.OnKey "a", "TestKeyPress"Fine sottotitoloSub TestKeyPress()MsgBox "Hai premuto 'a'"Fine sottotitolo |
Esegui prima di tutto la sottoroutine "TestKeyPress". Devi eseguirlo solo una volta. Dice a Excel che ogni volta che viene premuta la lettera "a" chiamerà la sottoroutine "TestKeyPress". La sottoroutine "TestKeyPress" visualizza solo una finestra di messaggio per dirti che hai premuto il tasto "a". Potrebbe ovviamente caricare un modulo o fare ogni sorta di altre cose.
Puoi utilizzare qualsiasi combinazione di tasti che puoi utilizzare con la funzione "SendKeys"
Per annullare questa funzionalità, esegui l'istruzione "OnKey" senza il parametro "Procedura".
| 123 | Sub CancelOnKey()Applicazione.OnKey "a"Fine sottotitolo |
Tutto ora è tornato alla normalità.
