Excel (VBA): trovare l’ultima riga avvalorata

22 aprile 2010

Capita spesso di dover creare cicli in VBA che coinvolgono tutte le righe avvalorate in un determinato foglio di lavoro.
Ma se il numero di righe è variabile, a priori non è possibile conoscere il numero l’ultima riga avvalorata.

Una delle possibili soluzioni a questo problema è quella di salire (dal basso verso l’alto) fino alla prima riga vuota disponibile (in Excel 2003, e versioni precedenti, il nostro foglio non può avere più di 65.000 righe). In pratica:

UltimaRiga = Sheets("Foglio1").Range("A65000").End(XlUp).Row

Quindi il nostro ciclo sarà qualcosa del genere:

Sub prova()
UltimaRiga = Sheets("Foglio1").Range("A65000").End(XlUp).Row
For i = 1 to UltimaRiga
   ...
Next
End Sub

N.B.: in Excel 2007 e 2010 il numero massimo di righe è stato aumentato a 1.048.576.


Excel (VBA): velocizzare esecuzione macro

21 aprile 2010

Per velocizzare l’esecuzione del codice VBA è possibile bloccare lo “sfarfallio” dello schermo all’inizio della macro e quindi ripristinarlo alla fine dell’esecuzione.

Sub prova()
Application.ScreenUpdating = False
...
Application.ScreenUpdating = True
End Sub

Excel: convertire il formato data

15 aprile 2010

In questo articolo riporto le formule che possono essere usate con Excel per convertire una data nei due formati più frequenti (AAAAMMGG e GG/MM/AAAA).

Nel primo caso, la data iniziale (cella A9 di tipo testo) è nel formato AAAAMMGG che si può trovare nei log di qualche programma. Alla cella di destinazione deve essere applicato il formato “Data” prima di immettere la formula.

=DESTRA(A9;2)&”/”&SINISTRA(DESTRA(A9;4);2)&”/”&SINISTRA(A9;4)

Nel secondo caso la data iniziale (cella A15 di tipo data) si presenta nel formato italiano standard GG/MM/AAAA.
La funzione TESTO(??;”00″) è necessaria per impostare ai giorni ed ai mesi la lunghezza fissa di 2 caratteri.
Alla cella di destinazione deve essere applicato il formato Generale (o Testo) prima di inserire la formula.

=ANNO(A15)&TESTO(MESE(A15);”00″)&TESTO(GIORNO(A15);”00″)


Excel: creare una funzione personalizzata in VBA

17 luglio 2009

VBA, oltre alle normali procedure (sub), permette di creare un’altra tipologia di programma: le funzioni.

A differenza delle procedure che compiono un’azione, le funzioni restituiscono un singolo valore in base a dei parametri (argomenti) passati ad esse.
Una tipica funzione ha la seguente sintassi:

Function NomeFunzione(elencoParametri)
NomeFunzione = QualcheValore
End Function

All’interno della funzione è necessario comunicare quale valore la funzione deve restituire. Tale valore deve essere assegnato al nome identificativo della funzione. Se dimentichiamo di assegnare il valore al nome funzione, questa non restituirà nulla.

Nel semplice esempio seguente, la funzione “Peso” calcola il peso in Newton partendo dalla massa.

Function Peso(Massa)
'La forza peso si calcola in Newton quindi: Massa(kg) x Accelerazione di gravità(m/s2) = Peso(N)
Const g = 9.80665
Peso = Massa * g
End Function

La funzione può essere richiamata all’interno di una procedura o altra funzione VBA, oppure in una formula del foglio di lavoro (come nell’esempio seguente).

peso

In questo banale caso è forse più conveniente scrivere direttamente la formula nella cella interessata (es. =B2*9,80665), ma in situazioni più complesse le funzioni possono semplificare molto le cose.


Utilizzo della formula matrice con Excel

17 ottobre 2008

Spesso capita di dover estrarre dati specifici da tabelle create con Excel, ma talvolta non è così facile come sembra arrivare ai risultati voluti. Nei casi più complessi si è costretti a ricorrere all’aiuto di funzioni create in VBA (Visual Basic for Applications), ma non sempre è necessario.
Le “Formule Matrice” permettono infatti di ottenere risultati sorprendenti.

  • Matrice: è un intervallo rettangolare di valori (o di formule) che vengono trattati da Excel come unica entità.
  • Formula matrice: è una formula che ha come risultato una matrice ed agisce su uno o più insiemi di valori denominati argomenti matrice.

In generale le formule in forma di matrice vengono create allo stesso modo delle altre formule ma, prima di utilizzarle, dobbiamo ricordare alcune regole fondamentali:

  • Ogni argomento contenuto in una matrice deve avere lo stesso numero di righe e colonne.
  • La matrice deve essere inserita tramite la combinazione Ctrl+Shift+Invio (le parentesi graffe {} che contengono la formula non devono essere inserite manualmente!).

Per capire come funziona una matrice in Excel, possiamo esaminare la seguente tabella che rappresenta un elenco di iscritti ad una palestra.


(Tab. 1)

Calcolo di più risultati
Per calcolare il risultato nella colonna “G” potremmo utilizzare il metodo “tradizionale” scrivendo in G2 il prodotto E2*F2 e poi ricopiando in basso la formula, oppure, sfruttando le matrici:

  • selezionare l’intervallo G2:G9
  • inserire la formula “=E2:E9*F2:F9
  • confermare con Ctrl+Shift+Invio (tutte le celle verranno automaticamente avvalorate con il risultato)

Gli elementi corrispondenti degli intervalli E2:E9 e F2:F9 vengono moltiplicati ed il risultato viene inserito nella corrispondente cella dell’intervallo G2:G9.

E’ obbligatorio ricordae che, essendo l’intervallo matrice un’unica entità (caratterizzato dalle parentesi graffe), non è possibile inserire celle all’interno dell’intervallo, né modificare singolarmente una cella: può essere editato solo l’intervallo matrice completo.

    Nella formula precedente il risultato si presenta sotto forma di matrice estesa in un intervallo
    comprendente più celle, ma è possibile ottenere anche un risultato singolo, elaborando gli
    elementi della matrice stessa, tramite l’utilizzo delle funzioni di Excel.

    Calcolo di un singolo risultato
    Per ottenere, ad esempio, il totale globale dei pagamenti useremo la formula:
    =SOMMA(E2:E9*F2:F9)
    Quando Excel esegue E2:E9*F2:F9 crea una matrice nascosta che contiene il risultato del prodotto. Con la funzione SOMMA vengono poi sommati tutti gli elementi di tale matrice.
    Rifacendoci sempre alla Tab. 1, supponiamo di voler contare quante persone abitano a Pistoia ed hanno più di 20 anni:
    =SOMMA((B2:B9=”Pistoia”)*(C2:C9>20))
    oppure quante persone hanno l’età compresa fra i 10 ed i 30 anni e sono iscritte da più di 3 anni:
    =SOMMA((C2:C9>9)*(C2:C9<31)*(F2:F9>3))
    Ovviamente la funzione SOMMA può essere sostituita con MEDIA, MIN, MAX, ecc. In questo caso però dobbiamo aggiungere nella formula la funzione “SE” in quanto non si tratta più della somma degli elementi della matrice, ma dell’estrapolazione di un singolo elemento della tabella con caratteristiche specifiche.
    Calcoliamo per esempio l’età massima di chi abita a Pistoia:
    =MAX(SE(B2:B9=”Pistoia”;C2:C9;0))
    oppure l’età media delle donne:
    =MEDIA(SE(D2:D9=”F”;C2:C9))
    oppure l’età minima degli uomini che hanno più di 3 mesi di iscrizione:
    =MIN(SE(D2:D9=”M”;SE(F2:F9>3;C2:C9)))
    Come abbiamo visto, una volta capito il procedimento, è possibile sbizzarrirsi ed ottenere il risultato voluto con pochi sacrifici.
    Questo metodo di calcolo ha purtroppo anche qualche svantaggio in quanto troppe formule di matrici rallentano il ricalcolo, l’apertura ed il salvataggio dei fogli di lavoro che le contengono.
    Vanno quindi usate con cura ed occorre sempre valutare se sono strettamente necessarie, oppure se è possibile usare, in alternativa, altre funzioni di Excel.