In questo articolo, ho intenzione di mostrarvi alcuni dei più sorprendenti codici VBA Excel che è possibile utilizzare per ottimizzare il vostro lavoro. VBA è un linguaggio di programmazione, che può essere utilizzato per estendere le funzionalità di MS Excel e altre applicazioni MS Office. E ‘ estremamente utile per gli utenti MS Excel, perché può essere utilizzato per automatizzare il vostro lavoro e migliorare significativamente la vostra efficienza. Questo articolo vi introdurrà a VBA e vi mostrerà alcuni dei più utili, pronto per l’uso codici VBA là fuori. È possibile utilizzare questi esempi di macro per creare i propri script che si adattano alle proprie esigenze.

Non hai bisogno di esperienza di programmazione per sfruttare le informazioni in questo articolo, ma ci si aspetta di avere una conoscenza di base di Excel. Se sei un utente principiante, ti consiglio di leggere l’articolo 20 Formule di Excel che dovresti iniziare a utilizzare ora per saperne di più sulle funzionalità principali di Excel.

Ho preparato per voi una serie di pronti per l’uso VBA Excel Macro esempi con grande funzionalità che è possibile utilizzare per ottimizzare il vostro lavoro. Per usarli, è necessario “installarli” nel file Excel. Il prossimo paragrafo riguarda l’installazione di macro di Excel. Salta questa parte se hai già familiarità con questo.

Come installare una macro

In Excel, premere la combinazione di tasti alt + F11. Questo ti porterà all’editor VBA in MS Excel. Quindi, fare clic destro sulla cartella Oggetti Microsoft Excel a sinistra e selezionare Inserisci = > Modulo. Questo è il luogo in cui sono memorizzate le macro. Per utilizzare la macro, è necessario salvare il documento Excel come abilitato per macro. Dalla scheda file = > salva con nome, scegliere Salva come cartella di lavoro macro-enabled (il .estensione xlsm) Ora, è il momento di scrivere la tua prima macro!

1. Copiare i dati da un file all’altro.

Macro molto utile, in quanto mostra come copiare un intervallo di dati dall’interno di vba e come creare e nominare una nuova cartella di lavoro. Si può facilmente aggiornare per soddisfare le proprie esigenze:

 Sub CopyFiletoAnotherWorkbook() 'Copia le schede tecniche("Esempio 1").Gamma ("B4:C15").Copia ' Creare una nuova cartella di lavoro Cartelle di lavoro.Aggiungi ' Incolla i dati ActiveSheet.Incolla ' Spegnere applicazione avvisi Applicazione.DisplayAlerts = False ' Salva il nuovo file. Modificare il nome della directory. ActiveWorkbook.Nome file SaveAs:="C:\Temp\MyNewBook.xlsx "' Girare avvisi di applicazione di nuovo su applicazione.DisplayAlerts = TrueEnd Sub

2. Mostra righe nascoste

Occasionalmente, file Excel di grandi dimensioni contengono linee nascoste per una maggiore chiarezza. Ecco una macro che mostrerà tutte le righe da un foglio di lavoro attivo:

Sub ShowHiddenRows() Colonne.Intera colonna.Nascosto = False righe.Tutto qui.Hidden = FalseEnd Sub

3. Elimina righe e colonne vuote

Le righe vuote in Excel sono un problema con l’elaborazione dei dati. Ecco come sbarazzarsi di loro:

 Sub DeleteEmptyRowsAndColumns() 'Dichiarare le variabili. Dim MyRange Come Gamma Dim iCounter Come Long ' Definire la gamma di destinazione. Imposta MyRange = ActiveSheet.UsedRange ' Avvia il ciclo inverso attraverso l'intervallo di righe. Per iCounter = MyRange.Fila.Conta fino a 1 Passo -1 ' Se l'intera riga è vuota, eliminala. Se l'applicazione.CountA (Righe (iCounter).EntireRow ) = 0 Poi righe (iCounter).Delete 'Remove comment to See which are the empty rows 'MsgBox" row "& iCounter & "is empty" End If 'Increase the counter down Next iCounter 'Passo 6: Inizia il ciclo inverso attraverso l'intervallo di colonne. Per iCounter = MyRange.Colonna.Conta fino a 1 Passo -1 ' Passo 7: Se l'intera colonna è vuota, eliminala. Se l'applicazione.CountA (Colonne (iCounter).EntireColumn ) = 0 Quindi Colonne (iCounter).Elimina Fine se ' Passaggio 8: Incrementa il contatore verso il basso Successivo iCounter End Sub

4. Trova una cella vuota

Sub FindEmptyCell() ActiveCell.Offset (1, 0).Selezionare Fai mentre non è Vuoto(ActiveCell) ActiveCell.Offset (1, 0).Selezionare LoopEnd Sub

5. Sostituire le celle vuote con un valore.

Come accennato in precedenza, le celle vuote interferiscono con l’elaborazione dei dati e la creazione di tabelle pivot. Ecco un codice che sostituisce tutte le celle vuote con 0. Questa macro ha un’applicazione molto grande perché puoi usarla per trovare e sostituire N/A risultati, così come altri caratteri come punti, virgole o valori duplicati:

Sub FindAndReplace() 'Dichiarare le variabili Dim MyRange Come Intervallo Dim MyCell Come intervallo 'Salvare la cartella di lavoro prima di cambiare le celle? Seleziona Caso MsgBox ("Impossibile annullare questa azione. "& _ " Salva prima la cartella di lavoro?", vbYesNoCancel) Caso è = vbYes ThisWorkbook.Salva caso è = vbCancel Exit Sub End Select ' Definire l'intervallo di destinazione. Imposta MyRange = Selection ' Inizia a scorrere l'intervallo. Per ogni MyCell In MyRange 'Controlla la lunghezza zero quindi aggiungi 0. Se Len(MyCell.Valore) = 0 Poi MyCell = 0 Fine Se ' Ottieni la cella successiva nell'intervallo Next MyCellEnd Sub

6. Ordina numeri

La seguente macro ordina in ordine crescente tutti i numeri dalla colonna della cella attiva. Basta fare doppio clic su qualsiasi cella da una colonna che si desidera ordinare.NB: È necessario inserire il codice nel foglio 1 e non in un modulo per funzionare:

 Sub privato Worksheet_BeforeDoubleClick (ByVal Target as Range, Cancel As Boolean) 'Declare le variabili Dim LastRow As Long 'Find last row LastRow = Cells (Rows.Conte, 1) .Fine (xlUp) .Row ' Sort ascending on double-clicked column Rows ("6:"& LastRow) .Ordina _ Key1: = Celle (6, ActiveCell.Colonna), _ Order1: = xlAscendingEnd Sub

7. Rimuovere gli spazi vuoti

Occasionalmente, i dati nella cartella di lavoro contengono spazi aggiuntivi (spazi) che possono interferire con l’analisi dei dati e corrompere le formule. Ecco una macro che rimuoverà tutti gli spazi da un intervallo di celle preselezionato:

Sub TrimTheSpaces() 'Dichiara le tue variabili Dim MyRange come Intervallo Dim MyCell Come intervallo' Salva la cartella di lavoro prima di cambiare le celle Seleziona Caso MsgBox ("Impossibile annullare questa azione. "& _ " Salva prima la cartella di lavoro?", vbYesNoCancel) Caso è = vbYes ThisWorkbook.Salva caso è = vbCancel Exit Sub End Select ' Definire l'intervallo di destinazione. Imposta MyRange = Selection ' Inizia a scorrere l'intervallo. Per ogni MyCell In MyRange 'Tagliare gli spazi. Se non IsEmpty (MyCell)Allora MyCell = Trim (MyCell) End If ' Ottieni la cella successiva nell'intervallo Next MyCellEnd Sub

8. Evidenziare i valori dublicated

A volte ci sono valori duplicati nelle diverse colonne che vorremmo illuminare. Ecco una macro che fa proprio questo:

Sub HighlightDuplicates() 'Dichiarare le variabili Dim MyRange Come Intervallo Dim MyCell Come intervallo 'Definire l'intervallo di destinazione. Imposta MyRange = Selection ' Inizia a scorrere l'intervallo. Per ogni MyCell In MyRange 'Assicurati che la cella abbia la formattazione del testo. Se WorksheetFunction.CountIf(MyRange, MyCell.Valore) > 1 Poi MyCell.Interno.ColorIndex = 36 End If ' Ottieni la cella successiva nell'intervallo Next MyCellEnd Sub

9. Evidenzia i primi dieci valori

Questo codice evidenzierà i primi dieci valori da una selezione di celle:

Sub TopTen() Selection.FormatConditions.AddTop10 Selezione.FormatConditions (Selezione.FormatConditions.Contare).SetFirstPriority con la selezione.FormatConditions (1) .TopBottom = xlTop10Top ' Cambia il rango qui per evidenziare un diverso numero di valori .Classifica = 10 .Percentuale = Falsa fine con con selezione.FormatConditions (1).Font .Colore = -16752384 .TintAndShade = 0 Fine Con Con selezione.FormatConditions (1).Interno .PatternColorIndex = xlAutomatic .Colore = 13561798 .TintAndShade = 0 Termina con la selezione.FormatConditions (1).StopIfTrue = FalseEnd Sub

Puoi facilmente modificare il codice per evidenziare un numero diverso di valori.

10. Evidenziare maggiore di valori

Quando si esegue questo codice, verrà visualizzata una finestra. Ti chiederà il valore che vuoi confrontare con le celle che hai selezionato.

 Sub HighlightGreaterThanValues() Dim i Come Integer i = InputBox("Enter Greater Than Value", "Enter Value") Selezione.FormatConditions.Elimina ' Cambia l'operatore in xlLower per evidenziare la selezione inferiore ai valori.FormatConditions.Aggiungi tipo: = xlCellValue, Operatore: = xlGreater, Formula1: = i Selezione.FormatConditions (Selezione.FormatConditions.Contare).SetFirstPriority con la selezione.FormatConditions (1) .Font.Colore = RGB(0, 0, 0) .Interno.Color = RGB(31, 218, 154) End WithEnd Sub

Puoi modificare questo codice per evidenziare anche valori più bassi.

Una semplice macro che evidenzia tutte le celle che contengono commenti:

Sub HighlightCommentCells() Selection.SpecialCells (xlCellTypeComments).Seleziona Selezione.Stile = "Nota" Fine Sub

12. Evidenzia le celle con parole errate

Questo è estremamente utile quando lavori con funzioni che prendono stringhe, tuttavia, qualcuno ha inserito la stringa con un errore e le tue formule non funzionano. Ecco come risolvere questo problema:

 Sub ColorMispelledCells() Per ogni cl In ActiveSheet.UsedRange Se non applicazione.CheckSpelling(Parola:= cl.Testo) Poi _ cl.Interno.ColorIndex = 28 Next clEnd Sub

13. Creare una tabella pivot

Ecco come creare una tabella pivot da MS Excel (versione 2007). Particolarmente utile, quando si sta facendo un rapporto personalizzato ogni giorno. È possibile ottimizzare la creazione della tabella pivot nel modo seguente:

Sub PivotTableForExcel2007() Dim SourceRange As Range Set SourceRange = Sheets("Sheet1").Gamma ("A3: N86") ActiveWorkbook.PivotCaches.Crea (_SourceType: = xlDatabase, _ SourceData:=SourceRange, _ Version: = xlPivotTableVersion12).CreatePivotTable _ TableDestination:="", _ TableName:="", _ DefaultVersion:=xlPivotTableVersion12End Sub

14. Allega la cartella di lavoro attiva in un’e-mail

Il mio codice VBA preferito. Ti consente di allegare e inviare il file su cui stai lavorando con un indirizzo email predefinito, il titolo del messaggio e il corpo del messaggio! Per prima cosa è necessario impostare il riferimento a Microsoft Outlook (nel VBA editior, fare clic su strumenti => riferimenti e scegliere Microsoft Outlook).

 Sub SendFIleAsAttachment() 'Dichiarare le variabili 'Impostare il riferimento a Microsoft Outlook Object library Dim OLApp Come Outlook.Applicazione Dim OLMail come oggetto ' Aprire Outlook avviare un nuovo elemento di posta Set OLApp = Nuovo Outlook.Set di applicazioni OLMail = OLApp.CreateItem (0) OLApp.Sessione.Logon 'Crea il tuo elemento di posta e invia Con OLMail .To = "[email protected]; [email protected]". CC="".CCN ="".Subject = "Questa è la riga dell'oggetto".Corpo = "Ciao".Allegato.Aggiungi ActiveWorkbook.FullName .Mostra ' Cambia in .Invia per inviare senza rivedere Termina con ' Memory cleanup Set OLMail = Nothing Set OLApp = NothingEnd Sub

15. Invia tutti i grafici Excel a una presentazione PowerPoint

Una macro molto utile che consente di aggiungere tutti i grafici Excel nella presentazione Powerpoint con un solo clic:

Sub SendExcelFiguresToPowerPoint() 'Imposta riferimento alla libreria di oggetti Microsoft Powerpoint 'Dichiarare le variabili Dim PP come PowerPoint.Applicazione PPPres Dim come PowerPoint.Presentazione Dim PPSlide come PowerPoint.Slide Dim i Come Integer ' Controlla i grafici; uscire se non esistono grafici Fogli ("Dati diapositiva").Selezionare Se ActiveSheet.ChartObjects.Count < 1 Poi MsgBox" Nessun grafico esistente il foglio attivo " Exit Sub End Se 'Aprire PowerPoint e creare nuova presentazione Set PP = Nuovo PowerPoint.Set di applicazioni PPPres = PP.Presentazione.Aggiungi PP.Visible = True ' Avvia il ciclo in base al conteggio del grafico Per i = 1 in ActiveSheet.ChartObjects.Count ' Copia il grafico come immagine ActiveSheet.ChartObjects (i).Grafico.CopyPicture _ Dimensione: = xlScreen, Formato: = xlPicture Application.Attendere (Ora + TimeValue ("0:00:1")) ' Contare diapositive e aggiungere nuova diapositiva come prossimo numero di diapositiva disponibile ppSlideCount = PPPres.Diapositiva.Count Set PPSlide = PPPres.Diapositiva.Aggiungi (SlideCount + 1, ppLayoutBlank) PPSlide.Selezionare ' Incolla l'immagine e regolare la sua posizione; Vai al grafico successivo PPSlide.Forma.Incollare.Seleziona PP.Finestra attiva.Selezione.ShapeRange.Allineare msoAlignCenters, vero PP.Finestra attiva.Selezione.ShapeRange.Allinea msoAlignMiddles, True Next i ' Memory Cleanup Set PPSlide = Nothing Set PPPres = Nothing Set PP = NothingEnd Sub

16. Invia tabella Excel in MS Word

Le tabelle Excel vengono solitamente inserite all’interno di documenti di testo. Ecco un modo automatico di esportare la tabella Excel in MS Word:

 Sub ExcelTableInWord() 'Imposta riferimento alla libreria di oggetti Microsoft Word 'Dichiara le tue variabili Dim MyRange Come Excel.Intervallo Dim wd come parola.Applicazione Dim wdDoc come parola.Documento Dim WdRange come Word.Range ' Copia i fogli intervallo definiti ("Tabella ricavi").Gamma ("B4:F10").Cop ' Apri il set di documenti Word di destinazione wd = Nuova parola.Applicazione Imposta wdDoc = wd.Documento.Apri _ (ThisWorkbook.Percorso && " PasteTable.docx") wd.Visible = True ' Imposta il focus sul segnalibro di destinazione Imposta WdRange = wdDoc.Segnalibri ("DataTableHere").Intervallo ' Elimina la vecchia tabella e incolla nuova in caso di errore Riprendi il prossimo WdRange.Tabelle(1).Elimina WdRange.Incolla 'incolla nella tabella' Regola le larghezze delle colonne WdRange.Tabelle(1).Colonna.SetWidth _ (MyRange.Larghezza / MyRange.Colonna.Count), wdAdjustSameWidth ' Reinserire il segnalibro wdDoc.Segnalibro.Aggiungi "DataTableHere", wdrange ' Memory cleanup Set wd = Nothing Set wdDoc = Nothing Set WdRange = NothingEnd Sub

17. Estrarre una parola specifica da una cella

Possiamo usare formule se vogliamo estrarre un certo numero di simboli. Ma cosa succede se vogliamo estrarre solo la seconda parola da una frase o da un intervallo di parole in una cella? Per fare ciò, possiamo creare una funzione Excel personalizzata con VBA. Questa è una delle funzionalità VBA più importanti, perché consente di creare le proprie funzioni che sono inesistenti in MS Excel. Andiamo avanti e creiamo due funzioni: findword () e findwordrev (). Ecco il codice vba per questo:

 Funzione FindWord (Sorgente come stringa, Posizione come intero) Come Stringa In caso di errore Resume Next FindWord = Split(WorksheetFunction.Trim (Source),"") (Position - 1) On Error GoTo 0End FunctionFunction FindWordRev(Source As String, Position As Integer) As String Dim Arr () As String Arr = VBA.Split (WorksheetFunction.Trim (Fonte),"") In caso di errore Riprendere successivo FindWordRev = Arr(UBound(Arr) - Posizione + 1) In caso di errore GoTo 0END Funzione

Molto bello, abbiamo creato due funzioni cstom Excel. Ora, prova a usarli in Excel. La funzione = FindWordRev (A1,1) prende l’ultima parola dalla cella A1. La funzione = FindWord (A1,3) prende la terza parola dalla cella A1, ecc.

18. Proteggi la tua cartella di lavoro

A volte vogliamo proteggere i dati nel nostro file in modo che solo noi possiamo cambiarlo. Ecco come farlo con VBA:

 Sub ProtectSheets() 'Dichiara le tue variabili Dim w come foglio di lavoro 'Inizia a scorrere tutti i fogli di lavoro Per ogni w In ActiveWorkbook.Protezione dei fogli di lavoro e ciclo al prossimo foglio di lavoro ws.Proteggere la password: = "1234" Prossimo wsEnd Sub

Congratulazioni! Dal momento che stai ancora leggendo questo, sei davvero entusiasta di imparare VBA. Come hai già visto di persona, il linguaggio di programmazione VBA è estremamente utile e può farci risparmiare molto tempo. Spero che hai trovato queste informazioni utili e usarlo per diventare un maestro in MS Excel, VBA e software per computer in generale.