Questo tutorial dimostrerà come lavorare con tabelle e ListObjects in VBA.
Tabelle VBA e ListObjects
Le tabelle sono una delle funzionalità più utili e potenti di Excel, in questo tutorial, esamineremo come utilizzare VBA per creare una tabella, aggiungere un ordinamento semplice a una tabella, filtrare una tabella ed eseguire altre attività relative alla tabella.
Crea una tabella con VBA
Il metodo ListObjects.Add può aggiungere una tabella a un foglio di lavoro, in base a un intervallo in quel foglio di lavoro. Abbiamo l'intervallo mostrato in ($ A $ 1: $ B $ 8) su un foglio di lavoro chiamato Foglio1.
Il codice seguente aggiungerà una tabella, denominata Table1 al foglio di lavoro, in base all'intervallo ($A$1:$B$8) utilizzando lo stile tabella predefinito:
| 123456 | Sub CreateTableInExcel()ActiveWorkbook.Sheets("Sheet1").ListObjects.Add(xlSrcRange, Range("$A$1:$B$8"), , xlYes).Name = _"Tabella 1"Fine sottotitolo |
Il risultato è:
Inserimento di una colonna alla fine della tabella con VBA
Puoi usare il metodo ListColumns.Add per aggiungere una colonna alla fine della tabella. Abbiamo la nostra tabella chiamata Table1 mostrata di seguito.
Puoi aggiungere una colonna alla tua tabella usando il seguente codice, che aggiungerà sempre una colonna alla fine della tabella:
| 12345 | Sub AddColumnToTheEndOfTheTable()ActiveWorkbook.Sheets("Foglio1").ListObjects("Table1").ListColumns.AddFine sottotitolo |
Il risultato è:
Inserimento di una riga in fondo alla tabella con VBA
Puoi usare il metodo ListRows.Add per aggiungere una riga in fondo alla tabella. Abbiamo la nostra tabella chiamata Table1 mostrata di seguito.
Il codice seguente aggiungerà sempre una riga in fondo alla tabella.
| 12345 | Sub AddRowToTheBottomOfTheTable()ActiveSheet.ListObjects("Table1").ListRows.AddFine sottotitolo |
Il risultato è:
Aggiunta di un ordinamento semplice con VBA
Puoi ordinare una tabella con VBA. Abbiamo la nostra tabella chiamata Table1 mostrata di seguito e possiamo usare VBA per ordinare la colonna delle vendite dal più basso al più alto.
Il codice seguente ordinerà la colonna Vendite in ordine crescente.
| 12345678910111213141516171819 | Sub SimpleSortOnTheTable()Intervallo("Tabella1[[#Intestazioni],[Vendite]]").SelezionaActiveWorkbook.Worksheets("Foglio1").ListObjects("Table1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Foglio1").ListObjects("Table1").Sort.SortFields.Add _Key:=Range("Table1[[#All],[Sales]]"), SortOn:=xlSortOnValues, Order:= _xlAscending, DataOption:=xlSortNormalCon ActiveWorkbook.Worksheets("Foglio1").ListObjects("Table1").Ordina.Intestazione = xlSì.MatchCase = False.Orientamento = xlTopToBottom.SortMethod = xlPinYin.ApplicareTermina conFine sottotitolo |
Il risultato è:
Filtra una tabella con VBA
Puoi anche filtrare una tabella Excel usando VBA. Abbiamo la nostra tabella denominata Table1 e vorremmo filtrare la tabella in modo che vengano mostrate solo le vendite superiori a 1500.
Possiamo usare il metodo Autofilter, che ha cinque parametri opzionali. Poiché vorremmo filtrare la colonna Sales, che è la seconda colonna, impostiamo il campo su 2 e utilizziamo il parametro dell'operatore xlAnd, utilizzato per date e numeri.
| 123456 | Sub SimpleFilter()ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _">1500", Operatore:=xlAndFine sottotitolo |
Il risultato è:
Cancella il filtro con il metodo ShowAllData in VBA
È possibile accedere al metodo ShowAllData della classe Worksheet per cancellare il filtro. Se si desidera cancellare i filtri di una tabella, è necessario prima selezionare una cella nella tabella, operazione che è possibile eseguire in VBA.
Il metodo ShowAllData genererà un errore se non si utilizza la logica condizionale per verificare se è stato applicato un filtro nel foglio di lavoro. Il codice seguente mostra come eseguire questa operazione:
| 123456789 | Sub ClearingTheFilter()Intervallo("Tabella1[[#Intestazioni],[Vendite]]").SelezionaSe ActiveWorkbook.Worksheets("Sheet1").FilterMode = True ThenActiveSheet.ShowAllDataFinisci seFine sottotitolo |
Cancella tutti i filtri da una tabella di Excel
È possibile accedere al metodo ShowAllData della classe ListObject senza dover prima selezionare una cella nella tabella. Il codice seguente mostra come eseguire questa operazione:
| 123 | Sub ClearAllTableFilters()ActiveWorkbook.Worksheets("Foglio1").ListObjects("Table1").AutoFilter.ShowAllDataFine sottotitolo |
Eliminazione di una riga con VBA
Puoi eliminare una riga nel corpo dati della tabella utilizzando il metodo ListRows.Delete. Devi specificare quale riga utilizzando il numero di riga. Abbiamo la seguente tabella chiamata Table1.
Supponiamo che tu voglia eliminare la seconda riga nel corpo dati della tua tabella, il seguente codice ti consentirebbe di farlo:
| 12345 | Sub EliminaARow()ActiveWorkbook.Worksheets("Foglio1").ListObjects("Table1").ListRows(2).EliminaFine sottotitolo |
Il risultato è:
Eliminazione di una colonna con VBA
Puoi eliminare una colonna dalla tabella usando il metodo ListColumns.Delete. Abbiamo la seguente tabella chiamata Table1 mostrata di seguito:
Per eliminare la prima colonna, dovresti utilizzare il seguente codice:
| 12345 | Sotto EliminaAColonna()ActiveWorkbook.Worksheets("Foglio1").ListObjects("Table1").ListColumns(1).EliminaFine sottotitolo |
Il risultato è:
Conversione di una tabella in un intervallo in VBA
Puoi riconvertire una tabella in un intervallo normale usando VBA. Il codice seguente mostra come riconvertire una tabella denominata Table1 in un intervallo:
| 12345 | Sub ConvertingATableBackToANormalRange()ActiveWorkbook.Sheets("Foglio1").ListObjects("Table1").UnlistFine sottotitolo |
Aggiunta di colonne a bande e formattazione a tutte le tabelle in un foglio di lavoro utilizzando VBA
Puoi accedere a tutte le tabelle nel tuo foglio di lavoro utilizzando la raccolta ListObjects. Nel foglio sottostante abbiamo due tabelle e vorremmo aggiungere una colonna a bande a entrambe le tabelle contemporaneamente e cambiare il carattere della sezione dati di entrambe le tabelle in grassetto, usando VBA.
| 12345678910111213 | Sub AddingBandedColumns()Dim tbl As ListObjectDim sht come foglio di lavoroImposta sht = ThisWorkbook.ActiveSheetPer ogni tbl In sht.ListObjectstbl.ShowTableStyleColumnStripes = Truetbl.DataBodyRange.Font.Bold = Trueprossimo tblFine sottotitolo |
Il risultato è:
Creazione di una tabella in Access in VBA utilizzando DoCmd.RunSQL
Uno dei modi principali per creare una tabella in Access in VBA consiste nell'utilizzare il metodo DoCmd.RunSQL per eseguire una query di azione con un'istruzione SQL.
Abbiamo un pulsante nel nostro modulo di esempio e quando facciamo clic sul pulsante vorremmo creare una tabella chiamata ProductsTable con due campi o colonne, uno sarebbe il campo della chiave primaria chiamato ProductsID e l'altro sarebbe un campo chiamato Sales.
Per creare questa tabella utilizzeremmo il seguente codice:
| 123456 | Sub privato cmdCreateProductsTable_Click()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"Fine sottotitolo |
Il risultato è:
Filtraggio di una tabella in Access utilizzando VBA
Puoi anche filtrare una tabella in Access usando il metodo DoCmd.ApplyFilter. Abbiamo la nostra semplice tabella mostrata di seguito in Access chiamata ProductsTable.
Vorremmo premere questo pulsante sul nostro modulo e quindi vedere solo le vendite superiori a 1500.
Quindi, useremmo il seguente codice per farlo:
| 1234567 | Sotto privato cmdFilter_Click()DoCmd.OpenTable "ProdottiTable"DoCmd.ApplyFilter , "[Vendite]>1500"Fine sottotitolo |
Il risultato è:
