[vba] Classe per interrogare in SQL un foglio excel

Obiettivo: creare una classe per poter interrogare tramite query SQL un foglio excel, leggendo e scrivendo i dati come se lavorassimo con tabelle SQL

Questo esercizio riprende il punto direttamente da questo precedente: Interrogare foglio di Excel con SQL in VBA

Dopo aver visto come poter interrogare con query SQL un foglio excel, vediamo come creare una classe ad hoc che ci permetta di leggere e scrivere i dati mediante query SQL. Per farlo anzitutto aggiungiamo una nuova classe:

Premendo ALT+F11 apriamo l’editor Visual Basic, ci posizioniamo su un qualunque punto del progetto a sinistra e clicchiamo col destro (per esempio su Form), poi scegliamo Inserisci ⇒ Modulo classe. A questo punto verrà creata una nuova classe. Per rinominarla è sufficiente selezionarla e poi spostarsi nel riquadro delle proprietà.

A questo punto incolliamo dentro la classe appena creata (tenendo presente poi il nome scelto) il seguente codice:

Faccio notare come nel metodo dbout dobbiamo fare un’operazione un po’ superflua in apparenza, ovvero contare prima le righe e le colonne, per poi ridimensionare il vettore result. Questa operazione è necessaria perché ReDim, in VBA, non permette di ridimensionare l’intera matrice (vettore di vettori), ma solo l’ultima dimensione. Quindi, per esempio, result(4,10) potrebbe diventare result(4,20), ma non result(5,10) oppure result(5,20). Un’alternativa sarebbe quella di creare una funzione che trasponga il vettore e lo modifichi, ritrasponendolo di nuovo, ma dal punto di vista del calcolo credo sarebbe molto più oneroso che fare così.

Fatto questo potremo utilizzare la nostra nuova classe nella maniera seguente.

Una volta creata la classe può essere esportata, oppure importata nel modo seguente:

Per chi volesse è possibile scaricare la classe già pronta cliccando qui: DbSQL.zip

Hash MD5: A2A18A42BB11AEA86DF1AB4F547B4414

La classe è stata testata su Excel 2016.

Vedi articolo

Interrogare foglio di Excel con SQL in VBA

Obiettivo: interrogare e filtrare il contenuto di un foglio excel come se fosse una tabella di un database SQL

Prepariamo anzitutto un file Excel creando un foglio con dei dati come quelli di seguito:

Questo foglio lo chiamiamo DATI. Faccio notare che nella prima riga ho inserito le intestazioni della nostra tabella.

In un secondo foglio andiamo a creare un pulsante che richiamerà un form. L’idea sarebbe quella di creare un metodo di inserimento che guidi l’utente e gli permetta di filtrare una vasta base dati, eseguendo anche dei comandi di ricerca. Il risultato sarà il seguente:

Se proviamo a cercare qualcosa, per esempio “Mar” l’elenco dovrà essere filtrato e otterremo il risultato seguente:

Fatte queste premesse vediamo come realizzare l’interrogazione del foglio mediante VBA (sorvolo sull’elementare creazione del form).

Anzitutto creiamo una Sub che caricherà i valori nella combobox (cmbSeleziona) e nella listbox (lbSeleziona).

Faccio notare che in questo caso la query filtra solamente i valori in base al Nome, inoltre il parametro nome può essere passato in via opzionale, permettendo quindi anche di visualizzare tutti i campi se non c’è nessun nome da filtrare.

Ricordiamoci che per poter eseguire la connessione ADO abbiamo bisogno di importare l’opportuna libreria dai riferimenti.

A questo punto completiamo il nostro codice aggiungendo:

In questo modo ad ogni modifica (change) di txtCerca, che è la textbox di ricerca in cima al form, verrà interrogato il nostro foglio per produrre un risultato.

Il medesimo metodo LoadSelect, questa volta senza argomenti, lo aggiungiamo anche all’avvio del form, per caricare il contenuto completo senza filtri.

Qui è possibile scaricare il file creato con Excel 2016.

Vedi articolo

Calcolare IRR (Internal Rate of Return o TIR.COST) in VBA

Problema: la funzione TIR.COST (Tasso Interno di Rendimento) restituisce un unico valore per l’IRR anche quando ce ne è più di uno

Senza discutere sull’interpretazione dell’IRR stesso, immaginiamo di avere i seguenti due scenari di flusso di cassa.

I Scenario

Questo produce un grafico VAN-IRR come quello seguente:

In questo caso abbiamo due valori dell’IRR rispettivamente 15,47% e 33,33%; la funzione TIR.COST restituisce solamente il primo valore.

II Scenario

A questa serie di flussi di cassa corrisponde il seguente grafico:

In questo caso abbiamo due valori dell’IRR rispettivamente 1,99% e 19,65%; la funzione TIR.COST restituisce solamente il secondo valore.

Costruiamo quindi in VBA le funzioni necessarie per effettuare questo calcolo; come già mostrato anche qui utilizzeremo una funzione che restituisca un vettore di valori sotto forma di Variant, in modo da poterli valutare singolarmente (per esempio utilizzando le funzioni MIN e MAX, quando i valori sono solo due, quindi =MIN(IRRX(A1:A10)) oppure =MAX(IRRX(A1:A10))).

La funzione finale si chiama IRRX, quella che utilizzeremo dentro il foglio excel.

Le altre funzioni servono rispettivamente a:

  • IRRv1 – calcolare in modo approssimativo tutti gli IRR possibili su uno specifico flusso di cassa, con valore massimo per l’IRR al 30.000% (espresso come max = 300), inutile dire come questo valore possa essere impostato anche a molto meno per velocizzare i calcoli; l’IRR qui viene calcolato per ogni punto percentuale con scarti del 1%
  • IRRv2 – qui calcoliamo, per ogni valore di IRR trovato con la precedente funzione, il valore approssimato mediante iterazione su valori che vanno da IRRv2 ± 1%
  • VAN – questa funziona calcola semplicemente il VAN sul dato IRR

 

Testato su Excel 2016.

Vedi articolo