Come utilizzare le funzioni del foglio di lavoro come VLOOKUP in VBA di Excel?
Le funzioni come CERCA.VERT, CONTA.SE, SOMMA.SE 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 è possibile modificare 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 CERCA.VERT 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. L’intellisense di VBA mostrerà il nome delle funzioni disponibili per l’uso. Una volta scelto il nome della funzione, chiederà le variabili, come qualsiasi funzione su Excel. Ma dovrai passare le variabili in un formato comprensibile VBA. Ad esempio, se desideri passare un intervallo A1: A10, dovrai passarlo come oggetto intervallo come Intervallo (“A1: A10”).
Quindi usiamo alcune funzioni del foglio di lavoro per capirlo meglio.
Come utilizzare la funzione CERCA.VERT in VBA
Per dimostrare come utilizzare una funzione CERCA.VERT in VBA, qui ho dati di esempio. Devo mostrare il nome e la città dell’ID di accesso fornito in una finestra di messaggio utilizzando VBA. I dati sono distribuiti nell’intervallo A1: K26.
Premere ALT + F11 per aprire VBE e inserire un modulo. Vedi il codice sottostante.
Sub WsFuncitons()
Dim loginID As String
Dim name, city As String
loginID = "AHKJ_1-3357042451"
'Using VLOOKUP function to get name of given id in table
name = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 2, 0)
'Using VLOOKUP function to get city of given id in table
city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0)
MsgBox ("Name: " & name & vbLf & "City: " & city)
End Sub
Quando esegui questo codice, otterrai questo risultato.
Puoi vedere quanto velocemente il VBA stampa il risultato in una finestra di messaggio. Ora esaminiamo il codice.
Come funziona?
1.
Dim loginID As String
Dim name, city 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 numero, data, intervallo, ecc. Tipo di valore, utilizzare quel tipo di variabile per memorizzare il risultato. In caso di dubbi sul tipo di valore 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.
Puoi utilizzare Intervallo (“A2”). Valore per aggiornare dinamicamente il valore di ricerca dall’intervallo A2.
3.
name = Application.WorksheetFunction.VLookup (loginID, Range (“A1: K26”), 2, 0) Qui usiamo la funzione CERCA.VERT per ottenere. Ora, quando si corregge la funzione e si aprono le parentesi, verranno visualizzati gli argomenti richiesti ma non così descrittivi come mostrano in 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 utilizzato 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.
city = Application.WorksheetFunction.VLookup (loginID, Range (“A1: K26”), 4, 0)
Allo stesso modo otteniamo il nome della città.
4.
MsgBox (“Name:” & name & vbLf & “City:” & city)
Infine stampiamo nome e città utilizzando Messagebox.
Perché utilizzare la funzione foglio di lavoro in VBA? Le funzioni del foglio di lavoro possiedono il potere di calcoli immensi e non sarà intelligente ignorare il potere 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 essere necessarie ore. Ma se sai come utilizzare la funzione del foglio di lavoro DEV.ST.P in VBA per ottenere il calcolo in una volta.
Sub GetStdDev()
std = Application.WorksheetFunction.StDev_P(Range("A1:K26"))
End Sub
Utilizzo di più funzioni del foglio di lavoro VBA
Supponiamo di dover utilizzare 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. Vedi l’esempio sotto:
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 userò 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 che usiamo l’istruzione End With, non siamo in grado di accedere alle funzioni senza utilizzare nomi di funzione completamente qualificati.
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, metà, taglio, 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)) End Sub
Quando esegui il sottotitolo sopra, tornerà:
5 He o ll
Quindi sì ragazzi, è così che puoi utilizzare la funzione del foglio di lavoro di Excel in VBA. Spero di essere stato abbastanza esplicativo e questo articolo ti ha aiutato. Se hai domande riguardanti questo articolo o qualsiasi altra cosa relativa a VBA, chiedi nella sezione commenti qui sotto. Fino ad allora puoi leggere altri argomenti correlati di seguito.
Articoli correlati:
link: / excel-macros-and-vba-what-is-csng-function-in-excel-vba [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.
link: / vba-how-to-get-text-number-in-reverse-through-vba-in-microsoft-excel [How to get Text & Number in Reverse through VBA in Microsoft Excel] | * To reverse number e il testo usiamo i loop e la funzione mid in VBA. 1234 verrà convertito in 4321, “tu” verrà convertito in “uoy”. Ecco lo snippet.
link: / formating-in-vba-format-table-depend-on-number-formati-using-vba-in-microsoft-excel [Formattare i dati con formati numerici personalizzati utilizzando VBA in Microsoft Excel] | Per modificare il formato numerico di colonne specifiche in Excel, utilizzare questo frammento VBA. Converte il formato numerico del formato specificato nel formato specificato con un clic.
link: / tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [Using Worksheet Change Event To Run Macro When any Change is made] | Quindi, per eseguire la macro ogni volta che il foglio viene aggiornato, utilizziamo gli eventi del foglio di lavoro di VBA.
link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Run Macro If Any Change Made on Sheet in Specified Range] | Per eseguire il codice macro quando il valore in un intervallo specificato cambia, utilizzare questo codice VBA. Rileva qualsiasi modifica effettuata nell’intervallo specificato e attiverà l’evento.
link: / events-in-vba-semplice-vba-code-to-highlight-current-row-and-column-using [Codice VBA più semplice per evidenziare riga e colonna correnti utilizzando] | Usa questo piccolo frammento VBA per evidenziare la riga e la colonna correnti del foglio.
Articoli popolari:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-aumentare-la-produttività [50 scorciatoie Excel per aumentare la produttività] | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
link: / formule-e-funzioni-introduzione-di-vlookup-funzione [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.
link: / tips-countif-in-microsoft-excel [COUNTIF in Excel 2016] | Contare i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici.
La funzione Countif è essenziale per preparare la tua dashboard.
link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SUMIF in Excel] | Questa è un’altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.