Sommario:
- Esempi di funzioni del foglio di lavoro
- Inserimento delle funzioni del foglio di lavoro
- Ulteriori informazioni sull'uso delle funzioni del foglio di lavoro
Video: EXCEL - Tutorial 16: Macro esempi 2025
Sebbene VBA offra un buon assortimento di funzioni integrate, potresti non trovare sempre esattamente ciò di cui hai bisogno. Fortunatamente, puoi anche utilizzare la maggior parte delle funzioni del foglio di lavoro di Excel nelle tue procedure VBA. Le uniche funzioni del foglio di lavoro che non è possibile utilizzare sono quelle con una funzione VBA equivalente. Ad esempio, non è possibile utilizzare la funzione RAND di Excel (che genera un numero casuale) poiché VBA ha una funzione equivalente: Rnd.
VBA rende disponibili le funzioni del foglio di lavoro di Excel tramite l'oggetto WorksheetFunction, contenuto nell'oggetto Application. Ecco un esempio di come è possibile utilizzare la funzione SUM di Excel in un'istruzione VBA:
Totale = Applicazione. WorksheetFunction. SUM (Range ("A1: A12"))
È possibile omettere la parte Application o la parte WorksheetFunction dell'espressione. In entrambi i casi, VBA capisce cosa stai facendo. In altre parole, queste tre espressioni funzionano tutte esattamente allo stesso modo:
Totale = Applicazione. WorksheetFunction. SUM (Range ("A1: A12")) Total = WorksheetFunction. SUM (Range ("A1: A12")) Totale = Applicazione. SUM (Range ("A1: A12"))
La mia preferenza personale è usare la parte WorksheetFunction solo per rendere perfettamente chiaro che il codice sta usando una funzione di Excel.
Esempi di funzioni del foglio di lavoro
Qui si scopre come utilizzare le funzioni del foglio di lavoro nelle espressioni VBA.
Trovare il valore massimo in un intervallo
Ecco un esempio che mostra come utilizzare la funzione MAX del foglio di lavoro di Excel in una procedura VBA. Questa procedura visualizza il valore massimo nella colonna A del foglio di lavoro attivo:
Sub ShowMax () Dim TheMax As Double TheMax = WorksheetFunction. MAX (Range ("A: A")) MsgBox TheMax End Sub
È possibile utilizzare la funzione MIN per ottenere il valore più piccolo in un intervallo. E come ci si potrebbe aspettare, è possibile utilizzare altre funzioni del foglio di lavoro in modo simile. Ad esempio, è possibile utilizzare la funzione LARGE per determinare il k valore più grande in un intervallo. La seguente espressione lo dimostra:
SecondHighest = WorksheetFunction. LARGE (Range ("A: A"), 2)
Si noti che la funzione LARGE utilizza due argomenti. Il secondo argomento rappresenta k th part-2, in questo caso (il secondo valore più grande).
Calcolo di un mutuo
Il prossimo esempio utilizza la funzione del foglio di lavoro PMT per calcolare un pagamento ipotecario. Tre variabili vengono utilizzate per memorizzare i dati passati alla funzione Pmt come argomenti.Una finestra di messaggio visualizza il pagamento calcolato.
Sub PmtCalc () Dim IntRate come Double Dim LoanAmt come Double Dim Periodi a lungo IntRate = 0. 0625/12 Periodi = 30 * 12 LoanAmt = 150000 MsgBox WorksheetFunction. PMT (IntRate, Periodi, -LoanAmt) End Sub
Come illustrato nella seguente dichiarazione, è anche possibile inserire i valori direttamente come argomenti della funzione:
MsgBox WorksheetFunction. PMT (0, 0625/12, 360, -150000)
Tuttavia, l'uso di variabili per memorizzare i parametri rende il codice più facile da leggere e modificare, se necessario.
Utilizzo di una funzione di ricerca
Nell'esempio seguente vengono utilizzate le funzioni InputBox e MsgBox di VBA, oltre alla funzione CERCA.VERT di Excel. Richiede un numero parte e quindi ottiene il prezzo da una tabella di ricerca. Di seguito, l'intervallo A1: B13 è denominato PriceList.
Sottrai GetPrice () Dim PartNum As Variant Dim Prezzo As Double PartNum = InputBox ("Inserisci il numero di parte") Fogli ("Prezzi"). Attiva Price = WorksheetFunction. VLOOKUP (PartNum, Range ("PriceList"), 2, False) MsgBox PartNum e "costi" e prezzo End Sub
Ecco come funziona la procedura GetPrice:
-
La funzione InputBox di VBA chiede all'utente un numero di parte.
-
Il numero di parte immesso dall'utente viene assegnato alla variabile PartNum.
-
La prossima istruzione attiva il foglio di calcolo dei prezzi, nel caso in cui non sia già il foglio attivo.
-
Il codice utilizza la funzione CERCA.VERT per trovare il numero parte nella tabella.
-
Si noti che gli argomenti utilizzati in questa istruzione sono uguali a quelli che si utilizzerebbero con la funzione in una formula del foglio di lavoro. Questa affermazione assegna il risultato della funzione alla variabile Price.
-
Il codice visualizza il prezzo per la parte tramite la funzione MsgBox.
Questa procedura non ha alcuna gestione degli errori e fallisce miseramente se si inserisce un numero di parte inesistente. (Provalo.) Se si trattasse di un'applicazione effettiva utilizzata in un'attività reale, è opportuno aggiungere alcune istruzioni che gestiscono gli errori con maggiore grazia.
Inserimento delle funzioni del foglio di lavoro
Non è possibile utilizzare la finestra di dialogo Funzione Incolla di Excel per inserire una funzione del foglio di lavoro in un modulo VBA. Invece, inserisci tali funzioni alla vecchia maniera: a mano. Tuttavia, può utilizzare la finestra di dialogo Incolla funzione per identificare la funzione che si desidera utilizzare e scoprire i suoi argomenti.
È inoltre possibile usufruire dell'opzione Membri elenco automatico VBE, che visualizza un elenco a discesa di tutte le funzioni del foglio di lavoro. Basta digitare Applicazione. WorksheetFunction , seguito da un punto. Quindi viene visualizzato un elenco delle funzioni che è possibile utilizzare. Se questa funzione non funziona, selezionare il comando Strumenti → Opzioni VBE, fare clic sulla scheda Editor e posizionare un segno di spunta accanto a Membri elenco automatico.
Ulteriori informazioni sull'uso delle funzioni del foglio di lavoro
I nuovi arrivati in VBA spesso confondono le funzioni integrate di VBA e le funzioni della cartella di lavoro di Excel. Una buona regola da ricordare è che VBA non tenta di reinventare la ruota.Per la maggior parte, VBA non duplica le funzioni del foglio di lavoro di Excel.
Per la maggior parte delle funzioni del foglio di lavoro che non sono disponibili come metodi dell'oggetto WorksheetFunction, è possibile utilizzare un operatore o una funzione integrata VBA equivalente. Ad esempio, la funzione MOD del foglio di lavoro non è disponibile nell'oggetto WorksheetFunction poiché VBA ha un equivalente: l'operatore Mod integrato.
Bottom line? Se è necessario utilizzare una funzione, determinare innanzitutto se VBA ha qualcosa che soddisfi le proprie esigenze. In caso contrario, controlla le funzioni del foglio di lavoro. Se tutto il resto fallisce, potresti essere in grado di scrivere una funzione personalizzata usando VBA.
