VBA-Office: Aprire un file di Excel da VBA disabilitando le Macro

Capita a volte di dover aprire un file Excel da codice e che questo abbia al suo interno delle macro che possono interferire con il corretto funzionamento del nostro programma. I suggerimenti che ho trovato on-line su questo argomento non sono realmente efficaci in quanto tendono a disabilitare gli eventi dell'applicazione Excel e questo può interferire con il nostro stesso programma VBA. Per esempio se il mio codice VBA sta girando su una applicazione Excel e deve aprire un'altro file Excel e mettiamo in atto la disabilitazione degli eventi, questo impatterà anche sul file contenente il mio codice. Oppure: se debbo aprire due files di cui uno con le macro abilitate e l'altro no la soluzione di disabilitare gli eventi non è valida.
Per fortuna Microsoft ci da una soluzione che salva capra e cavoli. E' sufficiente alzare il livello di protezione macro di Excel a "Disattiva tutte le macro senza notifica" prima di aprire il file in questione e poi ripristinare il livello di protezione a quello che era impostato in precedenza. La proprietà da impostare è l'AutomationSecurity che restituisce dei valori elencati nel tipo MsoAutomationSecurity

Ecco un esempio:

    Dim Exc As Excel.Application

    Dim Wb As Excel.Workbook

    Dim secAutomation As MsoAutomationSecurity

    

    'Istanzio l'applicazione Excel e rilevo il livello di sicurezza corrente

    Set Exc = New Excel.Application

    secAutomation = Exc.AutomationSecurity

    'Imposto il livello di sicurezza a "Disattiva tutte le macro senza notifica"

    Exc.AutomationSecurity = msoAutomationSecurityForceDisable

    'Apro il file Excel con le macro disabilitate

    Set Wb = Exc.Workbooks.Open(FileName:="NomeFile.xlsm")

    'Reimposto il livello di sicurezza precedente

    Exc.AutomationSecurity = secAutomation

Alla prossima! :)

VBA-Excel: Come reperire l'ultima riga/colonna di un foglio che contiene dati

A chi programma in VBA per Excel capita di aver bisogno di sapere qual'è l'ultima riga e/o l'ultima colonna di un foglio contenente dati, magari per fare un ciclo di lettura delle celle.
Spesso si usano accrocchi vari per reperire questa informazione che simulano l'esecuzione manuale come per esempio la pressione dei tasti Fine e Freccia giù, oppure improbabili cicli che leggono le celle una dopo l'altra fino a trovarne una vuota. A parte i tempi lunghi di questo approccio resta il problema che alla prima cella vuota trovata ci si ferma dando per assunto che quella sia la fine dei dati, cosa che non è sempre vera.
Il metodo migliore è di gran lunga più semplice e veloce nonché più sicuro di quelli sopracitati.
VBA per Excel offre una proprietà dell'oggetto Worksheet che è molto utile in questo caso e cioè: UsedRange. Tale proprietà restituisce un oggetto di tipo Range che compre l'intera area di celle utilizzata nel Worksheet a cui è associato.
Pertanto per sapere quali sono l'ultima riga e l'ultima colonna usati è sufficiente fare:

Dim UltimaRiga as Long
Dim UltimaColonna as Long

UltimaRiga = Thisworkbook.Worksheets("MioFoglio").UsedRange.Rows.Count
UltimaColonna = Thisworkbook.Worksheets("MioFoglio").UsedRange.Columns.Count

Facile e veloce! :-D

VBA-Excel: Aggiornare le Tabelle Pivot (e relativi Grafici) da codice

A chi scrive codice VBA può capitare l'esigenza di aggiornare le Tabelle Pivot (i Grafici Pivot si appoggiano anch'essi su Tabelle Pivot) dopo aver modificato i dati contenuti nei vari fogli.
Per prima cosa è utile sapere che le Tabelle Pivot si appoggiano su cache di dati e sono quindi queste ultime a dover essere aggiornate. Un modo molto semplice di aggiornare tutte le Pivot del Workbook (file) corrente è un ciclo che passi tutte le cache e ne esegua il Refresh.
Vi riporto di seguito il codice di una semplice Sub che inserita nel codice dell'oggetto ThisWorkbook implementa un metodo richiamabile dal vostro codice per "refreshare" (brutto inglesismo) tutte le Pivot.

Public Sub RefreshPivotTables()

    Dim PT As PivotCache
    For Each PT In ThisWorkbook.PivotCaches
        PT.Refresh
    Next
                
End Sub

Quindi nel vostro codice per richiamare il nuovo metodo sarà sufficiente scrivere:

ThisWorkbook.RefreshPivotTables

Alla prossima :-)

VBA-Excel: La finestra del MsgBox appare nascosta "sotto" la finestra di Excel

Di recente mi è capitato molte volte che la finestra aperta con il comando MsgBox in VBA apparisse "sotto" la finestra di Excel, causando così una sorta di blocco perché l'utente non vedeva il messaggio e l'applicazione restava ferma in attesa di risposta. Unico indizio che c'era un messaggio in attesa era la finestra di Excel "sfarfallante". Per l'utente era sufficiente cliccare il titolo della finestra o premere Alt-Tab per vedere il messaggio e quindi poter rispondere e proseguire, ma certo non è bello.
Ho fatto un po' di ricerche e o trovato una soluzione semplice e che pare efficace. Prima della riga in cui viene eseguito il MsgBox inserite una riga con AppActivate Application.Caption

Esempio:
AppActivate Application.Caption
MsgBox "Mio messaggio"

Spero di essere stato utile :-)

Excel - Media Ponderata in Tabelle Pivot

Come molti sapranno uno dei calcoli statistici più usati è quello della media ponderata, cioè la media che tiene conto del "peso" di ogni valore. Non voglio fare qui una lezione su cosa sia e a cosa serva la media ponderata, se non lo sapete siete nel post sbagliato :-).
Una delle stranezze di Excel è che nel suo strumento statistico per antonomasia, le Tabelle Pivot, Microsoft a pensato che questo calcolo non fosse utile (o forse troppo difficile da implementare?! mah). Fatto sta che se cercate nelle funzioni riepilogative della Tabella Pivot la media ponderata non c'è.
Quindi se la vogliamo calcolare come possiamo fare? Semplice, la calcoliamo noi!
Per prima cosa bisogna aggiungere nella base dati da cui creiamo la Tabella Pivot un campo calcolato contenente la moltiplicazione del valore di cui abbiamo bisogno della media e il suo peso. In seguito la formula dovrà essere copiata per tutte le righe della tabella di origine.
Vi riporto una tabella di esempio dalla quale vogliamo ottenere il prezzo medio unitario degli articoli usando la quantità come peso. Ecco come viene inserita la formula di moltiplicazione usando i riferimenti ai campi della tabella (nominata Tabella1).

Articolo	Quantità	Prezzo un.	Val_X_Media
A	10	1025	=Tabella1[[#Questa riga];[Prezzo un.]]*Tabella1[[#Questa riga];[Quantità]]
A	100	990
C	5	725
B	25	542
C	10	700

Articolo	Quantità	Prezzo un.	Val_X_Media
A	10	1025	10250
A	100	990	99000
C	5	725	3625
B	25	542	13550
C	10	700	7000

Fatto questo creiamo la nostra Tabella Pivot aggiungendo il campo articolo nelle etichette di riga. Aggiungiamo alla tabella un campo calcolato e impostiamo come formula il nostro valore calcolato diviso il peso, per il nostro esempio sarà: = Val_X_Media / Quantità .

Aggiungiamo il nuovo campo calcolato ai Valori della tabella Pivot usando la normale funzione di somma.

Il campo così aggiunto sarà la nostra media ponderata.

Etichette di riga	Media
A	993,18
B	542,00
C	708,33
Totale complessivo	889,50

Il calcolo è giusto, verificate pure :-) .

Se alcuni valori di peso sono 0 e non volete avere nella tabella l'errore #DIV/0! inserite nel campo calcolato della Tabella Pivot una funzione SE per evitare l'errore. Per il nostro esempio sarà così:  = SE(Quantità=0;0;Val_X_Media / Quantità)   e il problema è risolto.

Se vi sono stato utile lasciate un commento, grazie! :-)