Si vous débutez avec VBA , vous voudrez commencer à étudier notre guide VBA pour débutants^{(VBA guide for beginners)} . Mais si vous êtes un expert chevronné de VBA et que vous recherchez des choses plus avancées que vous pouvez faire avec VBA dans Excel , continuez à lire.

La possibilité d'utiliser le codage VBA dans ^(VBA)Excel ouvre tout un monde d'automatisation. Vous pouvez automatiser les calculs dans Excel , les boutons-poussoirs et même envoyer des e-mails. Il existe plus de possibilités d'automatiser votre travail quotidien avec VBA que vous ne le pensez.

Guide VBA avancé pour Microsoft Excel^{(Advanced VBA Guide For Microsoft Excel)}

L'objectif principal de l'écriture de code VBA dans ^(VBA)Excel est de pouvoir extraire des informations d'une feuille de calcul, d'effectuer divers calculs dessus, puis de réécrire les résultats dans la feuille de calcul.

Voici les utilisations les plus courantes de VBA dans Excel .

• Importer^(Import) des données et effectuer des calculs
• Calculer^(Calculate) les résultats d'un utilisateur appuyant sur un bouton
• Envoyer^(Email) les résultats des calculs par e-mail à quelqu'un

Avec ces trois exemples, vous devriez être en mesure d'écrire une variété de votre propre code Excel VBA avancé.^{(Excel VBA)}

Importation de données et réalisation de calculs^{(Importing Data and Performing Calculations)}

L'une des choses les plus courantes pour lesquelles les gens utilisent Excel consiste à effectuer des calculs sur des données qui existent en dehors d' Excel . Si vous n'utilisez pas VBA , cela signifie que vous devez importer manuellement les données, exécuter les calculs et générer ces valeurs dans une autre feuille ou un autre rapport.

Avec VBA , vous pouvez automatiser l'ensemble du processus. Par exemple, si un nouveau fichier CSV est téléchargé dans un répertoire de votre ordinateur tous les lundis^(Monday) , vous pouvez configurer votre code VBA pour qu'il s'exécute lorsque vous ouvrez votre feuille de calcul pour la première fois le ^(VBA)mardi^(Tuesday) matin.

Le code d'importation suivant s'exécutera et importera le fichier CSV dans votre feuille de calcul Excel .

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")
Cells.ClearContents

strFile = “c:\temp\purchases.csv”

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With

Ouvrez l' outil d'édition Excel VBA et sélectionnez l' objet Sheet1 . Dans les listes déroulantes d'objet et de méthode, choisissez Feuille^(Worksheet) de calcul et Activer^(Activate) . Cela exécutera le code chaque fois que vous ouvrirez la feuille de calcul.

Cela créera une fonction Sub Worksheet_Activate() . Collez le code ci-dessus dans cette fonction.

Cela définit la feuille de calcul active sur Sheet1 , efface la feuille, se connecte au fichier en utilisant le chemin de fichier que vous avez défini avec la variable strFile , puis la boucle With parcourt chaque ligne du fichier et place les données dans la feuille à partir de la cellule A1 .

Si vous exécutez ce code, vous verrez que les données du fichier CSV sont importées dans votre feuille de calcul vierge, dans Sheet1 .

L'importation n'est que la première étape. Ensuite, vous souhaitez créer un nouvel en-tête pour la colonne qui contiendra vos résultats de calcul. Dans cet exemple, supposons que vous souhaitiez calculer les 5 % de taxes payées sur la vente de chaque article.

L'ordre des actions que votre code doit effectuer est :

1. Créez une nouvelle colonne de résultats appelée taxes .
2. Parcourez la colonne des unités vendues^{(units sold)} et calculez la taxe de vente.
3. Écrivez les résultats du calcul dans la ligne appropriée de la feuille.

Le code suivant accomplira toutes ces étapes.

Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double

Set StartCell = Range("A1")

'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))

rowCounter = 2
Cells(1, 5) = "taxes"

For Each cell In rng
     fltTax = cell.Value * 0.05
     Cells(rowCounter, 5) = fltTax
     rowCounter = rowCounter + 1
Next cell

Ce code trouve la dernière ligne de votre feuille de données, puis définit la plage de cellules (la colonne avec les prix de vente) en fonction de la première et de la dernière ligne de données. Ensuite, le code parcourt chacune de ces cellules, effectue le calcul de la taxe et écrit les résultats dans votre nouvelle colonne (colonne 5).

Collez le code VBA ci-dessus sous le code précédent et exécutez le script. Vous verrez les résultats apparaître dans la colonne E.

Désormais, chaque fois que vous ouvrirez votre feuille de calcul Excel , elle sortira automatiquement et obtiendra la copie la plus récente des données du fichier CSV . Ensuite, il effectuera les calculs et écrira les résultats sur la feuille. Vous n'avez plus rien à faire manuellement !

Calculer les résultats en appuyant sur le bouton^{(Calculate Results From Button Press)}

Si vous préférez avoir un contrôle plus direct sur le moment où les calculs s'exécutent, plutôt que de s'exécuter automatiquement à l'ouverture de la feuille, vous pouvez utiliser un bouton de contrôle à la place.

Les boutons de contrôle^(Control) sont utiles si vous souhaitez contrôler les calculs utilisés. Par exemple, dans le même cas que ci-dessus, que se passe-t-il si vous souhaitez utiliser un taux d'imposition de 5 % pour une région et un taux d'imposition de 7 % pour une autre ?

Vous pouvez autoriser l'exécution automatique du même code d'importation CSV , mais laisser le code de calcul de la taxe s'exécuter lorsque vous appuyez sur le bouton approprié.

À l'aide de la même feuille de calcul que ci-dessus, sélectionnez l' onglet Développeur , puis sélectionnez ^(Developer)Insérer^(Insert) dans le groupe Contrôles^(Controls) du ruban. Sélectionnez le bouton-poussoir ^{(push button)} Contrôle ActiveX^{(ActiveX Control)} dans le menu déroulant.

Dessinez le bouton-poussoir sur n'importe quelle partie de la feuille loin de l'endroit où les données iront.

Cliquez avec le bouton droit sur le bouton-poussoir et sélectionnez Propriétés^(Properties) . Dans la fenêtre Propriétés^(Properties) , remplacez la légende par ce que vous souhaitez afficher à l'utilisateur. Dans ce cas, il peut s'agir de Calculate 5% Tax .

Vous verrez ce texte reflété sur le bouton-poussoir lui-même. Fermez la fenêtre des propriétés et double-cliquez sur le bouton-poussoir lui-même. ^(properties)Cela ouvrira la fenêtre de l'éditeur de code et votre curseur se trouvera à l'intérieur de la fonction qui s'exécutera lorsque l'utilisateur appuie sur le bouton-poussoir.

Collez le code de calcul de la taxe de la section ci-dessus dans cette fonction, en maintenant le multiplicateur du taux de taxe à 0,05. N'oubliez pas d'inclure les 2 lignes suivantes pour définir la feuille active.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Maintenant, répétez le processus à nouveau, en créant un deuxième bouton-poussoir. Créez la légende Calculate 7% Tax .

Double-cliquez sur^{(Double-click)} ce bouton et collez le même code, mais faites en sorte que le multiplicateur de taxe soit de 0,07.

Maintenant, selon le bouton sur lequel vous appuyez, la colonne des taxes sera calculée en conséquence.

Une fois que vous avez terminé, vous aurez les deux boutons poussoirs sur votre feuille. Chacun d'eux lancera un calcul de taxe différent et écrira des résultats différents dans la colonne des résultats. 

Pour envoyer ce texte, sélectionnez le menu Développeur et sélectionnez ^(Developer)Mode de conception^{(Design Mode)} dans le groupe Contrôles^(Controls) du ruban pour désactiver le mode de conception^{(Design Mode)} . Cela activera les boutons-poussoirs. 

Essayez de sélectionner chaque bouton-poussoir pour voir comment la colonne de résultat "taxes" change.

Envoyer les résultats du calcul par e-mail à quelqu'un^{(Email Calculation Results to Someone)}

Que faire si vous souhaitez envoyer les résultats de la feuille de calcul à quelqu'un par e-mail ?

Vous pouvez créer un autre bouton appelé Email Sheet to Boss en utilisant la même procédure ci-dessus. Le code de ce bouton impliquera l'utilisation de l' objet Excel CDO pour configurer les paramètres de messagerie SMTP et l'envoi par e-mail des résultats dans un format lisible par l'utilisateur.

Pour activer cette fonctionnalité, vous devez sélectionner Outils et références^{(Tools and References)} . Faites défiler vers le bas jusqu'à Microsoft CDO pour Windows 2000 Library^{(Microsoft CDO for Windows 2000 Library)} , activez-le et sélectionnez OK .

Il y a trois sections principales dans le code que vous devez créer pour envoyer un e-mail et intégrer les résultats de la feuille de calcul.

La première consiste à configurer des variables pour contenir le sujet, les adresses To et From et le corps de l'e-mail.

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double

Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."

Bien sûr, le corps doit être dynamique en fonction des résultats dans la feuille, vous devrez donc ajouter ici une boucle qui parcourt la plage, extrait les données et écrit une ligne à la fois dans le corps.

Set StartCell = Range("A1")

'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))

rowCounter = 2
strBody = strBody & vbCrLf

For Each cell In rng
     strBody = strBody & vbCrLf
     strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _
     & " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "."
     rowCounter = rowCounter + 1
Next cell

La section suivante implique la configuration des paramètres SMTP afin que vous puissiez envoyer des e-mails via votre serveur SMTP . Si vous utilisez Gmail , il s'agit généralement de votre adresse e-mail Gmail , de votre mot de passe ^(Gmail)Gmail et du serveur SMTP Gmail^{(Gmail SMTP)} (smtp.gmail.com).

Set CDO_Mail = CreateObject("CDO.Message") 
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields

With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With

With CDO_Mail
     Set .Configuration = CDO_Config
End With

Remplacez [email protected] et mot de passe par les détails de votre propre compte.

Enfin, pour lancer l'envoi de l'e-mail, insérez le code suivant.

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send

Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

Remarque^(Note) : Si vous voyez une erreur de transport lorsque vous essayez d'exécuter ce code, c'est probablement parce que votre compte Google bloque l'exécution des "applications moins sécurisées". Vous devrez visiter la page des paramètres des applications moins sécurisées^{(less secure apps settings page)} et activer cette fonctionnalité.

Une fois cette option activée, votre e-mail sera envoyé. Voici à quoi cela ressemble pour la personne qui reçoit votre e-mail de résultats généré automatiquement.

Comme vous pouvez le voir, il y a beaucoup de choses que vous pouvez réellement automatiser avec Excel VBA . Essayez de jouer avec les extraits de code que vous avez découverts dans cet article et créez vos propres automatisations VBA uniques.^(VBA)

An Advanced VBA Guide For MS Excel

If yoυ are just getting started with VBA, then you’ll want to start out studying our VBA guide for beginners. But if you’re a seasoned VBA expert and you’re looking for more advanced things you can do with VBA in Excel, then keep reading.

The ability to use VBA coding in Excel opens up a whole world of automation. You can automate calculations in Excel, pushbuttons, and even send email. There are more possibilities to automate your daily work with VBA than you may realize.

Advanced VBA Guide For Microsoft Excel

The main goal of writing VBA code in Excel is so that you can extract information from a spreadsheet, perform a variety of calculations on it, and then write the results back to the spreadsheet

The following are the most common uses of VBA in Excel.

• Import data and perform calculations
• Calculate results from a user pressing a button
• Email calculation results to someone

With these three examples, you should be able to write a variety of your own advanced Excel VBA code.

Importing Data and Performing Calculations

One of the most common things people use Excel for is performing calculations on data that exists outside of Excel. If you don’t use VBA, that means you have to manually import the data, run the calculations and output those values to another sheet or report.

With VBA, you can automate the entire process. For example, if you have a new CSV file downloaded into a directory on your computer every Monday, you can configure your VBA code to run when you first open your spreadsheet on Tuesday morning.

The following import code will run and import the CSV file into your Excel spreadsheet.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")
Cells.ClearContents

strFile = “c:\temp\purchases.csv”

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With

Open the Excel VBA editing tool and select the Sheet1 object. From the object and method dropdown boxes, choose Worksheet and Activate. This will run the code every time you open the spreadsheet.

This will create a Sub Worksheet_Activate() function. Paste the code above into that function.

This sets the active worksheet to Sheet1, clears the sheet, connects to the file using the file path you defined with the strFile variable, and then the With loop cycles through every line in the file and places the data into the sheet starting at cell A1.

If you run this code, you’ll see that the CSV file data is imported into your blank spreadsheet, in Sheet1.

Importing is only the first step. Next, you want to create a new header for the column that will contain your calculation results. In this example, let’s say you want to calculate the 5% taxes paid on the sale of each item.

The order of actions your code should take is:

1. Create new results column called taxes.
2. Loop through the units sold column and calculate the sales tax.
3. Write the calculate results to the appropriate row in the sheet.

The following code will accomplish all of these steps.

Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double

Set StartCell = Range("A1")

'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))

rowCounter = 2
Cells(1, 5) = "taxes"

For Each cell In rng
     fltTax = cell.Value * 0.05
     Cells(rowCounter, 5) = fltTax
     rowCounter = rowCounter + 1
Next cell

This code finds the last row in your sheet of data, and then sets the range of cells (the column with the sales prices) according to the first and last row of data. Then the code loops through each of those cells, performs the tax calculation and writes the results into your new column (column 5).

Paste the above VBA code below the previous code, and run the script. You will see the results show up in column E.

Now, every time you open your Excel worksheet, it’ll automatically go out and get the freshest copy of data from the CSV file. Then, it will perform the calculations and write the results to the sheet. You don’t have to do anything manually anymore!

Calculate Results From Button Press

If you’d rather have more direct control over when calculations run, rather than running automatically when the sheet opens, you can use a control button instead.

Control buttons are useful if you want to control which calculations are used. For example, in this same case as above, what if you want to use a 5% tax rate for one region, and a 7% tax rate for another?

You could allow the same CSV import code to run automatically, but leave the tax calculation code to run when you press the appropriate button.

Using the same spreadsheet as above, select the Developer tab, and select Insert from the Controls group in the ribbon. Select the push button ActiveX Control from the dropdown menu.

Draw the pushbutton onto any part of the sheet away from where any data will go.

Right-click the push button, and select Properties. In the Properties window, change the Caption to what you’d like to display to the user. In this case it might be Calculate 5% Tax.

You’ll see this text reflected on the push button itself. Close the properties window, and double-click the pushbutton itself. This will open the code editor window, and your cursor will be inside the function that will run when the user presses the pushbutton.

Paste the tax calculation code from the section above into this function, keeping the tax rate multiplier at 0.05. Remember to include the following 2 lines to define the active sheet.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Now, repeat the process again, creating a second push button. Make the caption Calculate 7% Tax.

Double-click that button and paste the same code, but make the tax multiplier 0.07.

Now, depending which button you press, the taxes column will be calculated accordingly.

Once you’re done, you’ll have both push buttons on your sheet. Each of them will initiate a different tax calculation and will write different results into the result column. 

To text this, select the Developer menu, and select Design Mode form the Controls group in the ribbon to disable Design Mode. This will activate the push buttons. 

Try selecting each push button to see how the “taxes” result column changes.

Email Calculation Results to Someone

What if you want to send the results on the spreadsheet to someone via email?

You could create another button called Email Sheet to Boss using the same procedure above. The code for this button will involve using the Excel CDO object to configure SMTP email settings, and emailing the results in a user-readable format.

To enable this feature, you need to select Tools and References. Scroll down to Microsoft CDO for Windows 2000 Library, enable it, and select OK.

There are three main sections to the code you need to create to send out an email and embed spreadsheet results.

The first is setting up variables to hold the subject, To and From addresses, and the email body.

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double

Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."

Of course, the body needs to be dynamic depending on what results are in the sheet, so here you’ll need to add a loop that goes through the range, extracts the data, and writes a line at a time to the body.

Set StartCell = Range("A1")

'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))

rowCounter = 2
strBody = strBody & vbCrLf

For Each cell In rng
     strBody = strBody & vbCrLf
     strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _
     & " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "."
     rowCounter = rowCounter + 1
Next cell

The next section involves setting up the SMTP settings so that you can send email through your SMTP server. If you use Gmail, this is typically your Gmail email address, your Gmail password, and the Gmail SMTP server (smtp.gmail.com).

Set CDO_Mail = CreateObject("CDO.Message") 
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields

With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With

With CDO_Mail
     Set .Configuration = CDO_Config
End With

Replace [email protected] and password with your own account details.

Finally, to initiate the email send, insert the following code.

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send

Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

Note: If you see a transport error when trying to run this code, it’s likely because your Google account is blocking “less secure apps” from running. You’ll need to visit the less secure apps settings page and turn this feature ON.

After that’s enabled, your email will be sent. This is what it looks like to the person who receives your automatically generated results email.

As you can see there is a lot you can actually automate with Excel VBA. Try playing around with the code snippets you’ve learned about in this article and create your own unique VBA automations.

Related posts

• Le meilleur guide VBA (pour les débutants) dont vous aurez besoin

• Comment créer une macro ou un script VBA dans Excel

• Un guide de toutes les extensions de fichiers Excel et de ce qu'elles signifient

• Comment supprimer des lignes vides dans Excel

• Correction du message d'erreur MS Office "Impossible de vérifier la licence"

• Utilisez le clavier pour modifier la hauteur de ligne et la largeur de colonne dans Excel

• Connecter Excel à MySQL

• 3 façons de diviser une cellule dans Excel

• Épeler rapidement les nombres dans Word et Excel

• Comment insérer une feuille de calcul Excel dans un document Word

• 4 façons de convertir Excel en Google Sheets

• Vérifier tous les hyperliens dans un document MS Word en quelques secondes

• Comment ouvrir Word et Excel en mode sans échec

• Qu'est-ce que Microsoft Publisher ? Un guide du débutant

• Comment supprimer le quadrillage dans Excel

• Comment déplacer des colonnes dans Excel

• Utilisation de l'outil de recherche d'objectifs d'analyse What-If d'Excel

• Comment écrire une formule/instruction IF dans Excel

• Comment utiliser les références absolues dans Excel

• Comment utiliser l'analyse What-If d'Excel