Fonctions Excel VBA
Nous avons vu que nous pouvons utiliser les fonctions de feuille de calcul dans VBA, c'est-à-dire les fonctions de feuille de calcul Excel dans le codage VBA en utilisant la méthode application.worksheet, mais comment utiliser une fonction de VBA dans Excel, eh bien ces fonctions sont appelées fonctions définies par l'utilisateur, lorsqu'un utilisateur crée une fonction dans VBA, elle peut également être utilisée dans une feuille de calcul Excel.
Bien que nous ayons de nombreuses fonctions dans Excel pour manipuler les données, nous devons parfois personnaliser les outils afin de pouvoir gagner du temps car nous effectuons certaines tâches à plusieurs reprises. Nous avons des fonctions prédéfinies dans Excel comme SUM, COUNTIF, SUMIF, COUNTIFS, VLOOKUP, INDEX, MATCH dans Excel, etc. mais nous effectuons certaines tâches au quotidien pour lesquelles une seule commande ou fonction n'est pas disponible dans Excel, puis en utilisant VBA, nous pouvons créer la fonction personnalisée qui est appelée Fonctions définies par l'utilisateur (UDF).
Que font les fonctions VBA?
- Ils effectuent certains calculs; et
- Renvoyer une valeur
Dans VBA, lors de la définition de la fonction, nous utilisons la syntaxe suivante pour spécifier les paramètres et leur type de données.
Le type de données ici est le type de données que la variable contiendra. Il peut contenir n'importe quelle valeur (n'importe quel type de données ou objet de n'importe quelle classe).
Nous pouvons connecter l'objet à sa propriété ou à sa méthode en utilisant le symbole point ou point (.).
Comment créer des fonctions personnalisées à l'aide de VBA?
Exemple
Supposons que nous ayons les données suivantes d'une école où nous devons trouver le total des notes attribuées par l'élève, le résultat et la note.
Pour résumer les notes obtenues par un élève individuel dans toutes les matières, nous avons une fonction intégrée, c'est-à-dire SUM, mais pour connaître la note et le résultat en fonction des critères définis par l'école n'est pas disponible dans Excel par défaut .
C'est la raison pour laquelle nous devons créer des fonctions définies par l'utilisateur.
Étape 1: trouver le total des notes
Tout d'abord, nous trouverons le total des notes en utilisant la fonction SOMME dans Excel.
Appuyez sur Entrée pour obtenir le résultat.
Faites glisser la formule vers le reste des cellules.
Maintenant, pour connaître le résultat (succès, échec ou répétition essentielle), les critères fixés par l'école sont les suivants.
- Si l'étudiant a obtenu un score supérieur ou égal à 200 comme notes totales sur 500 et que l'étudiant n'a pas échoué dans aucune matière (a obtenu plus de 32 dans chaque matière), alors un étudiant est réussi,
- Si l'élève a obtenu un score supérieur ou égal à 200, mais qu'il échoue dans 1 ou 2 matières, alors l'élève a obtenu la «Répétition Essentielle» dans ces matières,
- Si l'étudiant a obtenu moins de 200 ou échoue dans 3 matières ou plus, alors l'étudiant échoue.
Étape 2: Créer la fonction ResultOfStudent
Pour créer une fonction nommée «ResultOfStudent», nous devons ouvrir «Visual Basic Editor» en utilisant l'une des méthodes ci-dessous:
- En utilisant l'onglet Développeur Excel.
Si l'onglet Développeur n'est pas disponible dans MS Excel, nous pouvons l'obtenir en suivant les étapes suivantes:
- Cliquez avec le bouton droit n'importe où sur le ruban, puis choisissez la personnalisation du ruban dans Excel ' .
Lorsque nous choisissons cette commande, la boîte de dialogue «Options Excel» s'ouvre.
- Nous devons cocher la case "Développeur" pour obtenir l'onglet.
- En utilisant la touche de raccourci, c'est-à-dire Alt + F11.
- Lorsque nous ouvrons l'éditeur VBA, nous devons insérer le module en allant dans le menu Insertion et en choisissant un module.
- Nous devons coller le code suivant dans le module.
Fonction 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 Total> = 200 And CountOfFailedSubject = 0 Then ResultOfStudents = "Passed" Sinon ResultOfStudents = "Failed" End If End Function
La fonction ci-dessus renvoie le résultat pour un étudiant.
Nous devons comprendre comment ce code fonctionne.
La première instruction, «Function ResultOfStudents (Marks As Range) As String», déclare une fonction nommée «ResultOfStudents» qui acceptera une plage comme entrée pour les marques et retournera le résultat sous forme de chaîne.
Dim mycell As Range Dim Total As Integer Dim CountOfFailedSubject As Integer
These three statements declare variables, i.e.,
- ‘myCell’ as a Range,
- ‘Total’ as Integer (to store total marks scored by a student),
- ‘CountOfFailedSubject’ as integer (to store the number of subjects in which a student has failed).
For Each mycell In Marks Total = Total + mycell.Value If mycell.Value < 33 Then CountOfFailedSubject = CountOfFailedSubject + 1 End If Next mycell
This code checks for every cell in the ‘Marks’ range and adds the value of every cell in the ‘Total’ variable, and if the value of the cell is less than 33, then adds 1 to the ‘CountOfFailedSubject’ variable.
If Total>= 200 And CountOfFailedSubject 0 Then ResultOfStudents = "Essential Repeat" ElseIf Total>= 200 And CountOfFailedSubject = 0 Then ResultOfStudents = "Passed" Else ResultOfStudents = "Failed" End If
This code checks the value of ‘Total’ and ‘CountOfFailedSubject’ and passes the ‘Essential Report,’ ‘Passed,’ or ‘Failed’ accordingly to the ‘ResultOfStudents.’
Step 3: Apply ResultOfStudents Function to Get Result
ResultOfStudents function takes marks, i.e., selection of 5 marks scored by the student.
Now Select the Range of cells, i.e., B2: F2.
Drag the Formula to the rest of the Cells.
Step 4: Create ‘GradeForStudent’ Function to get Grades
Now to find out the grade for the student, we will create one more function named ‘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 fonction GradeForStudent prend Total des notes (somme des notes) et le résultat de l'élève comme argument pour calculer la note.
Sélectionnez maintenant les cellules respectives, c'est-à-dire G2, H2.
Il suffit maintenant d'appuyer sur Ctrl + D après avoir sélectionné les cellules pour copier les formules.
Nous pouvons mettre en évidence les valeurs inférieures à 33 avec la couleur de fond rouge afin de découvrir les matières dans lesquelles l'élève a échoué.
