Le funzioni come VLOOKUP, COUNTIF, SUMIF sono chiamate funzioni del foglio di lavoro. In genere, le funzioni predefinite in Excel e pronte per l'uso in un foglio di lavoro sono funzioni del foglio di lavoro. Non puoi alterare o vedere il codice dietro queste funzioni in VBA.
D'altra parte le funzioni definite dall'utente e le funzioni specifiche di VBA come MsgBox o InputBox sono funzioni VBA.
Sappiamo tutti come utilizzare le funzioni VBA in VBA. Ma cosa succede se vogliamo usare VLOOKUP in un VBA. Come lo facciamo? In questo articolo, esploreremo esattamente questo.
Utilizzo delle funzioni del foglio di lavoro in VBA
Per accedere alla funzione del foglio di lavoro utilizziamo una classe Application. Quasi tutte le funzioni del foglio di lavoro sono elencate nella classe Application.WorksheetFunction. E usando l'operatore punto, puoi accedervi tutti.
In qualsiasi sottotitolo, scrivi Application.WorksheetFunction. E inizia a scrivere il nome della funzione. Intellisense di VBA mostrerà il nome delle funzioni disponibili per l'uso. Una volta scelto il nome della funzione, verranno richieste le variabili, come qualsiasi funzione su Excel. Ma dovrai passare le variabili in un formato comprensibile VBA. Ad esempio, se vuoi passare un intervallo A1:A10, dovrai passarlo come un oggetto intervallo come Range ("A1: A10").
Quindi usiamo alcune funzioni del foglio di lavoro per capirlo meglio.
Come utilizzare la funzione CERCA.VERT in VBA
Per dimostrare come è possibile utilizzare una funzione CERCA.VERT in VBA, qui ho dati di esempio. Devo mostrare il nome e la città dell'ID di accesso specificato in una finestra di messaggio utilizzando VBA. I dati sono distribuiti nell'intervallo A1:K26.
Premi ALT+F11 per aprire VBE e inserire un modulo.
Vedi il codice sotto.
Sub WsFuncitons() Dim loginID As String Dim name, city As String loginID = "AHKJ_1-3357042451" 'Utilizzo della funzione VLOOKUP per ottenere il nome dell'id specificato nel nome della tabella = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26" ), 2, 0) 'Utilizzo della funzione CERCA.VERT per ottenere la città dell'ID specificato nella città della tabella = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0) MsgBox ("Nome: " & nome & vbLf & "Città: " & città) End Sub
Quando esegui questo codice, otterrai questo risultato.
Puoi vedere quanto velocemente VBA stampa il risultato in una finestra di messaggio. Ora esaminiamo il codice.
Come funziona?
1.
Dim loginID come stringa
Nome fioco, città As String
Per prima cosa abbiamo dichiarato due variabili di tipo stringa per memorizzare il risultato restituito dalla funzione CERCA.VERT. Ho usato variabili di tipo stringa perché sono sicuro che il risultato restituito da CERCA.VERT sarà un valore stringa. Se si prevede che la funzione del foglio di lavoro restituisca il tipo di valore numero, data, intervallo, ecc., utilizzare quel tipo di variabile per memorizzare il risultato. Se non si è sicuri, quale tipo di valore verrà restituito dalla funzione del foglio di lavoro, utilizzare le variabili di tipo variante.
2.
loginID = "AHKJ_1-3357042451"
Successivamente abbiamo utilizzato la variabile loginID per memorizzare il valore di ricerca. Qui abbiamo usato un valore hardcoded. Puoi anche usare i riferimenti. Per esempio. È possibile utilizzare Range("A2").Value per aggiornare dinamicamente il valore di ricerca dall'intervallo A2.
3.
nome = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 2, 0)
Qui usiamo la funzione VLOOKUP per ottenere. Ora, quando raddrizzi la funzione e apri la parentesi, ti mostrerà gli argomenti richiesti ma non così descrittivi come mostra su Excel. Guarda tu stesso.
Devi ricordare come e quale variabile devi usare. Puoi sempre tornare al foglio di lavoro per vedere i dettagli della variabile descrittiva.
Qui, il valore di ricerca è Arg1. Per Arg1 usiamo loginID. La tabella di ricerca è Arg2. Per Arg2 abbiamo usato Range ("A1: K26"). Nota che non abbiamo usato direttamente A2: K26 come facciamo su Excel. L'indice delle colonne è Arg3. Per Arg3 abbiamo usato 2, poiché il nome è nella seconda colonna. Il tipo di ricerca è Arg4. Abbiamo usato 0 come Arg4.
città = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0)
Allo stesso modo otteniamo il nome della città.
4.
MsgBox ("Nome: " & nome & vbLf & "Città: " & città)
Infine stampiamo nome e città utilizzando Messagebox.
Perché utilizzare la funzione del foglio di lavoro in VBA?
Le funzioni del foglio di lavoro possiedono il potere di calcoli immensi e non sarà intelligente ignorare la potenza delle funzioni del foglio di lavoro. Ad esempio, se vogliamo la deviazione standard di un set di dati e vuoi scrivere l'intero codice per questo, possono volerci ore. Ma se sai come utilizzare la funzione del foglio di lavoro DEV.ST.P in VBA per ottenere il calcolo in una volta sola.
Sub GetStdDev() std = Application.WorksheetFunction.StDev_P(Range("A1:K26")) End Sub
Utilizzo di più funzioni del foglio di lavoro VBA
Diciamo che abbiamo bisogno di usare una corrispondenza di indice per recuperare alcuni valori. Ora come scriveresti la formula in VBA. Questo è quello che immagino scriverai:
Sub IndMtch() Val = Application.WorksheetFunction.Index(result_range, _ Application.WorksheetFunction.Match(lookup_value, _ lookup_range, match_type)) End Sub
Questo non è sbagliato ma è lungo. Il modo giusto per utilizzare più funzioni è utilizzare un blocco With. Vedere l'esempio seguente:
Sub IndMtch() With Application.WorksheetFunction Val = .Index(result_range, .Match(lookup_value, lookup_range, match_type)) val2 = .VLookup(arg1, arg2, arg3) val4 = .StDev_P(numbers) End With End Sub
Come puoi vedere, ho usato With block per dire a VBA che utilizzerò le proprietà e le funzioni di Application.WorksheetFunction. Quindi non ho bisogno di definirlo ovunque. Ho appena usato l'operatore punto per accedere alle funzioni INDICE, CONFRONTA, CERCA.VERT e DEV.ST.P. Una volta utilizzata l'istruzione End With, non siamo in grado di accedere alle funzioni senza utilizzare nomi di funzione completi.
Quindi, se devi utilizzare più funzioni del foglio di lavoro in VBA, usa con block.
Non tutte le funzioni del foglio di lavoro sono disponibili tramite Application.WorksheetFunction
Alcune funzioni del foglio di lavoro sono direttamente disponibili per l'uso in VBA. Non è necessario utilizzare l'oggetto Application.WorksheetFunction.
Ad esempio, funzioni come Len() che viene utilizzata per ottenere il numero di caratteri in una stringa, sinistra, destra, media, trim, offset ecc. Queste funzioni possono essere utilizzate direttamente in VBA. Ecco un esempio.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) End Sub
Vedi, qui abbiamo usato la funzione LEN senza usare l'oggetto Application.WorksheetFunction.
Allo stesso modo puoi usare altre funzioni come left, right, mid, char ecc.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) Debug.Print (left(strng,2)) Debug.Print (right(strng,1)) Debug.Print (Mid(strng, 3, 2)) Fine sotto
Quando esegui il sub sopra, tornerà:
5 Lui o ll
Quindi sì ragazzi, ecco come potete usare la funzione del foglio di lavoro di Excel in VBA. Spero di essere stato abbastanza esplicativo e che questo articolo ti abbia aiutato. Se hai domande su questo articolo o su qualsiasi altra cosa relativa a VBA, chiedi nella sezione commenti qui sotto. Fino ad allora puoi leggere su altri argomenti correlati di seguito.
Che cos'è la funzione CSng in Excel VBA | La funzione SCng è una funzione VBA che converte qualsiasi tipo di dati in un numero a virgola mobile a precisione singola ("dato che è un numero"). Uso principalmente la funzione CSng per convertire i numeri formattati in testo in numeri reali.
Come ottenere testo e numero al contrario tramite VBA in Microsoft Excel | Per invertire il numero e il testo usiamo i loop e la funzione mid in VBA. 1234 sarà convertito in 4321, "tu" sarà convertito in "uoy". Ecco il frammento.
Formattare i dati con formati numerici personalizzati utilizzando VBA in Microsoft Excel | Per modificare il formato del numero di colonne specifiche in Excel, usa questo frammento VBA. Converte il formato numerico del formato specificato nel formato specificato con un clic.
Utilizzo dell'evento di modifica del foglio di lavoro per eseguire la macro quando viene apportata una modifica | Quindi, per eseguire la tua macro ogni volta che il foglio si aggiorna, utilizziamo gli eventi del foglio di lavoro di VBA.
Esegui macro se vengono apportate modifiche al foglio nell'intervallo specificato | Per eseguire il codice della macro quando cambia il valore in un intervallo specificato, utilizza questo codice VBA. Rileva qualsiasi modifica apportata nell'intervallo specificato e attiverà l'evento.
Il codice VBA più semplice per evidenziare la riga e la colonna correnti utilizzando | Usa questo piccolo frammento VBA per evidenziare la riga e la colonna correnti del foglio.
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
La funzione CERCA.VERT in Excel | Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
CONTA.SE in Excel 2016 | Conta i valori con le condizioni usando questa fantastica funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel | Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.