Funzioni VBA di Excel
Abbiamo visto che possiamo usare le funzioni del foglio di lavoro in VBA, cioè le funzioni del foglio di lavoro Excel nella codifica VBA usando il metodo application.worksheet, ma come usiamo una funzione di VBA in Excel, beh, tali funzioni sono chiamate funzioni definite dall'utente, quando un utente crea una funzione in VBA, può essere utilizzata anche nel foglio di lavoro Excel.
Anche se abbiamo molte funzioni in Excel per manipolare i dati, a volte abbiamo bisogno di alcune personalizzazioni negli strumenti in modo da poter risparmiare tempo mentre eseguiamo ripetutamente alcune attività. Abbiamo funzioni predefinite in Excel come SOMMA, CONTA.SE, SOMMA.SE, CONTA.SE, CERCA.VERT, INDICE, CONFRONTA in Excel, ecc. Ma eseguiamo alcune attività su base giornaliera per cui un singolo comando o funzione non è disponibile in Excel, quindi utilizzando VBA, possiamo creare la funzione personalizzata che si chiama Funzioni definite dall'utente (UDF).
Cosa fanno le funzioni VBA?
- Eseguono determinati calcoli; e
- Restituisce un valore
In VBA, durante la definizione della funzione, usiamo la seguente sintassi per specificare i parametri e il loro tipo di dati.
Il tipo di dati qui è il tipo di dati che la variabile conterrà. Può contenere qualsiasi valore (qualsiasi tipo di dati o oggetto di qualsiasi classe).
Possiamo connettere l'oggetto con la sua proprietà o metodo usando il punto o il punto (.).
Come creare funzioni personalizzate utilizzando VBA?
Esempio
Supponiamo di avere i seguenti dati di una scuola in cui dobbiamo trovare il punteggio totale ottenuto dallo studente, il risultato e il voto.
Per riassumere i voti ottenuti da un singolo studente in tutte le materie, abbiamo una funzione incorporata, ovvero SOMMA, ma per scoprire il voto e il risultato in base ai criteri stabiliti dalla scuola non è disponibile di default in Excel .
Questo è il motivo per cui dobbiamo creare funzioni definite dall'utente.
Passaggio 1: trova i voti totali
Innanzitutto, troveremo i voti totali utilizzando la funzione SOMMA in Excel.
Premi Invio per ottenere il risultato.
Trascina la formula sul resto delle celle.
Ora, per scoprire il risultato (superato, non riuscito o ripetizione essenziale), i criteri stabiliti dalla scuola sono questi.
- Se lo studente ha ottenuto un punteggio maggiore o uguale a 200 come punteggio totale su 500 e lo studente non è bocciato in nessuna materia (ha ottenuto più di 32 punti in ciascuna materia), allora uno studente è superato
- Se lo studente ha ottenuto un punteggio maggiore o uguale a 200, ma lo studente ha bocciato in 1 o 2 materie, allora uno studente ha ottenuto "Essential Repeat" in quelle materie,
- Se lo studente ha ottenuto un punteggio inferiore a 200 o fallisce in 3 o più materie, lo studente non riesce.
Passaggio 2: creare la funzione ResultOfStudent
Per creare una funzione denominata "ResultOfStudent", è necessario aprire "Visual Basic Editor" utilizzando uno dei metodi seguenti:
- Utilizzando la scheda Sviluppatore excel.
Se la scheda Sviluppatore non è disponibile in MS Excel, possiamo ottenerla utilizzando i seguenti passaggi:
- Fare clic con il pulsante destro del mouse in un punto qualsiasi della barra multifunzione, quindi scegliere Personalizza barra multifunzione in Excel ' .
Quando scegliamo questo comando, si apre la finestra di dialogo "Opzioni Excel" .
- Dobbiamo selezionare la casella per "Sviluppatore" per ottenere la scheda.
- Utilizzando il tasto di scelta rapida, ovvero Alt + F11.
- Quando apriamo l'editor VBA, dobbiamo inserire il modulo andando nel menu Inserisci e scegliendo un modulo.
- Dobbiamo incollare il seguente codice nel modulo.
Funzione ResultOfStudents (Marks As Range) As String Dim mycell As Range Dim Total As Integer Dim CountOfFailedSubject As Integer For Each mycell In Marks Total = Total + mycell.Value If mycell.Value = 200 And CountOfFailedSubject 0 Then ResultOfStudents = "Essential Repeat" ElseIf Totale> = 200 e CountOfFailedSubject = 0 Quindi ResultOfStudents = "Superato" Else ResultOfStudents = "Non riuscito" End If End Function
La funzione precedente restituisce il risultato per uno studente.
Dobbiamo capire come funziona questo codice.
La prima istruzione, "Funzione ResultOfStudents (Marks As Range) As String", dichiara una funzione denominata "ResultOfStudents" che accetterà un intervallo come input per i contrassegni e restituirà il risultato come stringa.
Dim mycell As Range Dim Total As Integer Dim CountOfFailedSubject As Integer
Queste tre istruzioni dichiarano le variabili, cioè
- "myCell" come intervallo,
- 'Totale' come numero intero (per memorizzare il punteggio totale ottenuto da uno studente),
- 'CountOfFailedSubject' come numero intero (per memorizzare il numero di materie in cui uno studente ha fallito).
Per ogni mycell In Marks Total = Total + mycell.Value If mycell.Value <33 Then CountOfFailedSubject = CountOfFailedSubject + 1 End If Next mycell
Questo codice controlla ogni cella nell'intervallo " Marks" e aggiunge il valore di ogni cella nella variabile " Total" e, se il valore della cella è inferiore a 33, aggiunge 1 alla variabile "CountOfFailedSubject" .
If Total> = 200 And CountOfFailedSubject 0 Then ResultOfStudents = "Essential Repeat" ElseIf Total> = 200 And CountOfFailedSubject = 0 Then ResultOfStudents = "Passed" Else ResultOfStudents = "Failed" End If
Questo codice controlla il valore di "Total" e "CountOfFailedSubject" e passa il " Report essenziale", "Superato" o "Non riuscito" di conseguenza a "ResultOfStudents".
Passaggio 3: applicare la funzione ResultOfStudents per ottenere il risultato
La funzione ResultOfStudents prende i voti, cioè la selezione di 5 voti segnati dallo studente.
Ora seleziona l'intervallo di celle, ad esempio B2: F2.
Trascina la formula nel resto delle celle.
Passaggio 4: creare la funzione "GradeForStudent" per ottenere i voti
Ora per scoprire il voto per lo studente, creeremo un'altra funzione denominata "GradeForStudent".
The code would be:
Function GradeForStudent(TotalMarks As Integer, Result As String) As String If TotalMarks> 440 And TotalMarks 380 And TotalMarks 320 And TotalMarks 260 And TotalMarks = 200 And TotalMarks <= 260 And (Result = "Passed" Or Result = "Essential Repeat") Then GradeForStudent = "E" ElseIf TotalMarks < 200 Or Result = "Failed" Then GradeForStudent = "F" End If End Function
This function assigns a ‘Grade’ to the student based on the ‘Total Marks’ and ‘Result.’
We just need to write the formula and open the brackets in Cell H2 and pressing Ctrl+Shift+A to find out about the arguments.
La funzione GradeForStudent prende il punteggio totale (somma dei voti) e il risultato dello studente come argomento per calcolare il voto.
Ora seleziona le rispettive celle, cioè G2, H2.
Ora dobbiamo solo premere Ctrl + D dopo aver selezionato le celle per copiare le formule.
Possiamo evidenziare i valori inferiori a 33 con il colore di sfondo rosso in modo da scoprire le materie in cui lo studente ha fallito.
