Lavorare con le tabelle di Excel

Una tabella di Excel — ciò che VBA chiama un ListObject — è un intervallo denominato e auto-espandibile con frecce di filtro integrate, righe alternate e riferimenti strutturati alle colonne. Se i tuoi dati non sono già una Tabella, seleziona una qualsiasi cella al suo interno e premi Ctrl+T, oppure lascia che VBA ne crei una utilizzando ListObjects.Add.

Riferimento a un ListObject

Dim ws As Worksheet
Dim tbl As ListObject
 
Set ws = ThisWorkbook.Worksheets("Reports")
Set tbl = ws.ListObjects("tblReports")
 
Debug.Print tbl.Range.Address        ' full table including header
Debug.Print tbl.DataBodyRange.Rows.Count   ' data rows only, no header

Dichiarare tbl As ListObject (anziché solo As Range) è ciò che sblocca tutte le funzionalità specifiche delle Tabelle utilizzate nel resto di questa sezione — ListRows, ListColumns e la Total Row derivano tutte dal fatto che l'oggetto sia tipizzato correttamente. Nota la distinzione tra le due righe di Debug.Print: tbl.Range copre l'intera Tabella inclusa la riga di intestazione, mentre tbl.DataBodyRange copre solo i dati sottostanti. '

Quasi tutte le operazioni — aggiungere una riga, sommare una colonna, scorrere i record — dovrebbero utilizzare DataBodyRange, proprio perché non si vuole che il testo dell'intestazione venga trattato accidentalmente come una riga dati.

Aggiunta di righe

ListRows.Add aggiunge una nuova riga direttamente sotto la tabella — e, cosa fondamentale, tutte le formule con riferimenti strutturati presenti nelle altre colonne si estendono automaticamente anche su di essa, uno dei maggiori vantaggi pratici di una Tabella rispetto a un intervallo semplice.

Dim newRow As ListRow
Set newRow = tbl.ListRows.Add
 
newRow.Range(1, 1).Value = "April"
newRow.Range(1, 2).Value = "North"
newRow.Range(1, 3).Value = 45200
newRow.Range(1, 4).Value = 30750
newRow.Range(1, 5).Value = 14450
newRow.Range(1, 6).Value = 41000

tbl.ListRows.Add crea la riga vuota e la restituisce come oggetto ListRow, motivo per cui le sei righe successive scrivono su newRow invece che su tbl. newRow.Range(1, 1) significa "riga 1 di questa specifica nuova riga, colonna 1" — l'indicizzazione riparte da 1 per la nuova riga stessa, non conta dall'inizio della tabella. Questa è una pratica decisamente migliore rispetto a trovare l'ultima riga del foglio con End(xlUp) e scrivere manualmente una colonna oltre: ListRows.Add si posiziona sempre correttamente all'interno dei confini della Tabella, quindi qualsiasi Riga Totale, formula con riferimento strutturato o regola di formattazione condizionale applicata alla Tabella si estende automaticamente per includerla.

Aggiornamento dei record

Per aggiornare una riga esistente, scorrere DataBodyRange e confrontare una colonna chiave — qui, aggiornando il Target della regione Central di febbraio dopo una revisione del budget:

Dim r As Long
For r = 1 To tbl.DataBodyRange.Rows.Count
    If tbl.DataBodyRange.Cells(r, 1).Value = "February" And _
       tbl.DataBodyRange.Cells(r, 2).Value = "Central" Then
        tbl.DataBodyRange.Cells(r, 6).Value = 52000   ' revised Target
        Exit For
    End If
Next r

Questo è lo stesso schema dall'alto verso il basso, fermandosi alla prima corrispondenza, tipico della logica condizionale, applicato a righe reali invece che a valori hardcoded: il ciclo controlla Mese e Regione insieme con And, e nel momento in cui entrambi corrispondono, aggiorna la colonna Target e chiama Exit For per non continuare a scansionare inutilmente le righe rimanenti. Utilizzare tbl.DataBodyRange.Cells(r, 1) invece di un riferimento Cells a livello di foglio mantiene la numerazione delle righe limitata ai soli dati della Tabella — la riga 1 qui significa la prima riga dati, indipendentemente da quale riga fisica del foglio inizi la Tabella.

Riferimento alle colonne della Tabella

I riferimenti strutturati — ListColumns("Name") — sono più leggibili e più robusti rispetto al conteggio delle colonne per numero, soprattutto quando una tabella viene modificata e le colonne si spostano:

Dim profitCol As Range
Set profitCol = tbl.ListColumns("Profit").DataBodyRange
 
Debug.Print Application.WorksheetFunction.Sum(profitCol)
Debug.Print Application.WorksheetFunction.Average(profitCol)

ListColumns("Profit") trova la colonna tramite il suo testo di intestazione invece che tramite la posizione, quindi il codice continua a funzionare anche se Profit viene successivamente spostata dalla colonna E alla colonna F — contare manualmente Cells(r, 5) si romperebbe silenziosamente in quel caso. Application.WorksheetFunction è il ponte che permette a VBA di richiamare direttamente le normali funzioni di Excel come SOMMA e MEDIA su un oggetto Range, invece di scrivere manualmente un ciclo con un totale progressivo, che richiederebbe più codice e sarebbe più soggetto a errori di conteggio.

Attività

1. Aprire Section_4_Reports.xlsx, salvarlo come Section_4_Reports.xlsm e confermare che i dati del foglio Reports siano una Tabella chiamata tblReports (cliccare su una qualsiasi cella al suo interno — dovrebbe apparire la scheda Progettazione tabella).
2. Scrivere una macro che aggiunga una riga di aprile per ognuna delle cinque regioni utilizzando ListRows.Add (in totale cinque nuove righe, valori inventati vanno bene).
3. Scrivere una seconda macro che utilizzi ListColumns("Sales").DataBodyRange e WorksheetFunction.Sum per stampare il totale delle vendite di tutte le righe nella Finestra Immediata.