Fonction de feuille de calcul VBA

Fonctions de feuille de calcul Excel VBA

La fonction de feuille de calcul dans VBA est utilisée lorsque nous devons faire référence à une feuille de calcul spécifique, normalement lorsque nous créons un module, le code s'exécute dans la feuille actuellement active du classeur mais si nous voulons exécuter le code dans la feuille de calcul spécifique, nous utilisons la fonction de feuille de calcul, cette fonction a diverses utilisations et applications dans VBA.

La meilleure chose à propos de VBA est, comme la façon dont nous utilisons les formules dans la feuille de calcul, de même que VBA a ses propres fonctions. Si c'est le meilleur, alors il a aussi une belle chose à savoir «nous pouvons également utiliser des fonctions de feuille de calcul dans VBA».

Oui!!! Vous avez bien entendu, nous pouvons également accéder aux fonctions de feuille de calcul dans VBA. Nous pouvons accéder à certaines des fonctions de la feuille de calcul tout en écrivant le code et en faire une partie de notre code.

Comment utiliser les fonctions de feuille de calcul dans VBA?

Vous pouvez télécharger ce modèle de feuille de travail VBA ici - Modèle de feuille de travail VBA

Dans la feuille de calcul, toutes les formules commencent par le signe égal (=), de la même manière en codage VBA pour accéder aux formules de la feuille de calcul, nous devons utiliser le mot «WorksheetFunction».

Avant de saisir une formule de feuille de calcul, vous devez mentionner le nom de l'objet «WorksheetFunction», puis mettre un point (.), Puis vous obtiendrez une liste de toutes les fonctions disponibles sous cet objet.

Dans cet article, nous nous concentrerons exclusivement sur la façon d'utiliser la fonction de feuille de calcul dans le codage VBA, ce qui ajoutera plus de valeur à vos connaissances en codage.

# 1 - Fonctions simples de la feuille de calcul SUM

Ok, pour commencer avec les fonctions de feuille de calcul, appliquez la fonction SOMME simple dans Excel pour ajouter des nombres à partir de la feuille de calcul.

Supposons que vous ayez des données mensuelles sur les ventes et les coûts dans la feuille de calcul comme ci-dessous.

En B14 et C14, nous devons arriver au total des nombres ci-dessus. Suivez les étapes ci-dessous pour démarrer le processus d'application de la fonction «SOMME» dans Excel VBA.

Étape 1: Créez un nom de macro Excel simple.

Code:

 Sub Worksheet_Function_Example1 () End Sub 

Étape 2: Puisque nous avons besoin du résultat dans la cellule B14, commencez le code comme Range («B14»). Value =

Code:

 Sub Worksheet_Function_Example1 () Range ("B14"). Valeur = End Sub 

Étape 3: Dans B14, nous avons besoin de la valeur résultant de la somme des nombres. Donc, pour accéder à la fonction SOMME à partir de la feuille de calcul, démarrez le code en tant que «WorksheetFunction».

Code:

Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction. End Sub

Étape 4: Au moment où vous mettez un point (.), Il commencera à afficher les fonctions disponibles. Alors sélectionnez SUM à partir de cela.

Code:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum End Sub 

Étape 5: Donnez maintenant la référence des nombres ci-dessus, c'est- à- dire Range («B2: B13»).

Code:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum (Range ("B2: B13")) End Sub 

Étape 6: De même pour la colonne suivante, appliquez le code similaire en modifiant les références de cellule.

Code:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum (Range ("B2: B13")) Range ("C14"). Value = WorksheetFunction.Sum (Range ("C2: C13")) End Sub 

Étape 7: Exécutez maintenant ce code manuellement ou en utilisant la touche F5 pour avoir un total dans les cellules B14 et C14.

Wow, nous avons nos valeurs. Une chose que vous devez noter ici est que nous n'avons aucune formule dans la feuille de calcul, mais nous venons d'obtenir le résultat de la fonction «SOMME» dans VBA.

# 2 - Utilisez RECHERCHEV comme une fonction de feuille de calcul

Nous verrons comment utiliser VLOOKUP dans VBA. Supposons ci-dessous les données que vous avez dans votre feuille Excel.

Dans la cellule E2, vous avez créé une liste déroulante de toutes les zones.

En fonction de la sélection que vous avez effectuée dans la cellule E2, nous devons récupérer le code PIN de la zone respective. Mais cette fois par VBA VLOOKUP, pas par feuille de calcul VLOOKUP. Suivez les étapes ci-dessous pour appliquer VLOOKUP.

Étape 1: Créez un nom de macro simple dans la procédure secondaire.

Code:

 Sub Worksheet_Function_Example2 () End Sub 

Étape 2: Nous avons besoin du résultat dans la cellule F2. Commencez donc le code par Range («F2»). Value =

Code:

 Sub Worksheet_Function_Example2 () Range ("F2"). Value = End Sub 

Étape 3: Pour accéder à la fonction de feuille de calcul VLOOKUP démarre le code comme «WorksheetFunction.VLOOKUP».

Code:

 Sub Worksheet_Function_Example2 () Range ("F2"). Value = WorksheetFunction.Vlookup (End Sub 

Étape 4: L'un des problèmes ici est que la syntaxe ne vous donnera aucune sorte de guide pour travailler avec RECHERCHEV. Vous devez être absolument sûr de la syntaxe sur laquelle vous travaillez.

La première syntaxe de RECHERCHEV est «Valeur de recherche». Dans ce cas, notre valeur de recherche est la valeur de la cellule E2, écrivez donc le code sous la forme Range («E2»).

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value, End Sub 

Step 5: Now the second argument is our table array, in this case, our table array range is from A2 to B6. So the code will be Range (“A2:B6”)

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"), End Sub 

Step 6: The Third argument will be from which column we need the data from the table array. Here we need the data from the 2nd column, so the argument will be 2.

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"),2, End Sub 

Step 7: The final argument is range lookup, we need an exact match so the argument is zero (0).

Code:

 Sub Worksheet_Function_Example2() Range("F2").Value = WorksheetFunction.VLookup(Range("E2").Value, Range("A2:B6"), 2, 0) End Sub 

So, we are done with the coding part. Now go to the worksheet and select any of the range.

Now go to your coding module and run the macro Using F5 key or manually to get the pin code of the selected zone.

We cannot go back and run the macro every time, so let’s assign a macro to shapes. Insert one of the shapes in a worksheet.

Add a text value to inserted shape.

Now right click and assign the macro name to this shape.

Click on ok after selecting the macro name.

Now, this shape holds the code of our VLOOKUP formula. So whenever you change the zone name click on the button, it will update the values.

Things to Remember

  • To access worksheet functions we need to write the word “WorksheetFunction” or “Application.WorksheetFunction”
  • We don’t have access to all the functions only a few.
  • We don’t see the actual syntax of worksheet functions, so we need to be absolutely sure of the function we are using.