Si vous débutez avec VBA, commencez par consulter le Guide du débutant en VBA . En revanche, si vous êtes un utilisateur VBA expérimenté et que vous recherchez des fonctionnalités plus avancées pour Excel, poursuivez votre lecture.
L'utilisation du langage VBA dans Excel ouvre un univers d'automatisation. Vous pouvez automatiser les calculs, les boutons et même l'envoi d'e-mails. Les possibilités d'automatisation de vos tâches quotidiennes grâce à VBA sont bien plus nombreuses que vous ne l'imaginez.
Guide VBA avancé pour Microsoft Excel
L'objectif principal de l'écriture de code VBA dans Excel est de vous permettre d'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.
- Importation des données et exécution des calculs
- Calcul des résultats en fonction des clics de l'utilisateur sur les boutons
- Envoyer les résultats des calculs par courriel à quelqu'un
À partir de ces trois exemples, vous devriez être capable d'écrire vous-même divers codes VBA Excel avancés.
Importation des données et exécution des calculs
L'une des utilisations les plus courantes d'Excel est le traitement de données provenant de sources externes. Sans VBA, il faut importer manuellement les données, effectuer les calculs et exporter les résultats vers une autre feuille ou un autre rapport.
Grâce à VBA, vous pouvez automatiser l'intégralité du processus. Par exemple, si un nouveau fichier CSV est téléchargé chaque lundi dans un répertoire de votre ordinateur, vous pouvez configurer votre code VBA pour qu'il s'exécute dès l'ouverture de votre feuille de calcul le mardi matin.
Le code d'importation suivant sera exécuté et le fichier CSV sera importé dans votre feuille de calcul Excel.
Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = "c:temppurchases.csv" With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")).TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Ouvrez l'éditeur VBA d'Excel et sélectionnez l'objet Sheet1. Dans les menus déroulants Objet et Méthode, choisissez Worksheet , puis Activer . Le code s'exécutera alors à chaque ouverture de 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 , analyse la feuille, se connecte au fichier en utilisant le chemin d'accès au fichier que vous avez spécifié avec la variable strFile , puis la boucle With parcourt chaque ligne du fichier et place les données dans la feuille en commençant par 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 vide, dans Sheet1.
L'importation n'est que la première étape. Ensuite, vous devrez créer un nouvel en-tête de colonne qui contiendra les résultats de vos calculs. Dans cet exemple, supposons que vous souhaitiez calculer la taxe de 5 % appliquée à la vente de chaque article.
L'ordre des actions que votre code doit effectuer est le suivant :
- Créez une nouvelle colonne de résultats appelée impôts.
- Répétez sur une colonne unités vendues Et calculer la taxe de vente.
- Inscrivez les résultats des calculs sur la ligne appropriée de la feuille.
Le code suivant permettra d'accomplir 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") 'Trouver la dernière ligne et la dernière colonne 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 repère la dernière ligne de votre feuille de données, puis définit la plage de cellules (la colonne contenant les prix de vente) en fonction des première et dernière lignes de données. Il parcourt ensuite chaque cellule, calcule la taxe et inscrit le résultat dans votre nouvelle colonne (colonne 5).
Collez le code VBA ci-dessus sous le code précédent et exécutez le script. Les résultats s'afficheront dans la colonne E.
Désormais, à chaque ouverture de votre feuille de calcul Excel, le programme récupérera automatiquement les données les plus récentes du fichier CSV. Il effectuera ensuite les calculs et enregistrera les résultats dans la feuille de calcul. Vous n'aurez plus rien à faire manuellement !
Calculez les résultats en appuyant sur le bouton
Si vous préférez contrôler directement le moment où les calculs s'exécutent, plutôt que de les exécuter automatiquement à l'ouverture de la feuille de calcul, vous pouvez utiliser un bouton de contrôle.
Les boutons de contrôle sont utiles si vous souhaitez maîtriser les calculs utilisés. Par exemple, dans le même scénario évoqué précédemment, que se passerait-il si vous vouliez appliquer un taux de taxe de 5 % à une région et de 7 % à une autre ?
Vous pouvez autoriser l'exécution automatique du même code d'importation CSV, mais laisser le code de calcul des taxes s'exécuter uniquement lorsque le bouton approprié est enfoncé.
Dans la même feuille de calcul mentionnée précédemment, sélectionnez l' onglet Développeur , puis Insérer dans le groupe Contrôles du ruban. Sélectionnez ensuite le contrôle Bouton-poussoir ActiveX dans le menu déroulant.
Dessinez le bouton-poussoir n'importe où sur la feuille, loin de l'endroit où les données seraient enregistrées.
Faites un clic droit sur le bouton et sélectionnez Propriétés . Dans la fenêtre Propriétés, modifiez la légende pour afficher le texte souhaité à l'utilisateur. Dans cet exemple, il pourrait s'agir de « Calculer la taxe de 5 % ».
Ce texte s'affichera directement sur le bouton. Fermez la fenêtre Propriétés , puis double-cliquez sur le bouton. Une fenêtre d'éditeur de code s'ouvrira et le curseur se positionnera dans la fonction qui sera exécutée lorsque l'utilisateur cliquera sur le bouton.
Collez le code de calcul de la taxe de la section précédente dans cette fonction, en conservant le multiplicateur du taux de taxe à 0.05. N'oubliez pas d'inclure les deux lignes suivantes pour identifier la feuille active.
Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1")
Répétez maintenant le processus et créez un deuxième bouton. Nommez-le « Calcul de la taxe de 7 % ».
Double-cliquez sur ce bouton et collez le même code, mais définissez le multiplicateur de taxe à 0.07.
Désormais, en fonction du bouton sur lequel vous appuyez, la colonne des taxes sera calculée en conséquence.
Une fois terminé, vous trouverez les deux boutons sur votre feuille. Chacun lancera un calcul d'impôt différent et affichera des résultats différents dans la colonne des résultats.
Pour tester cela, sélectionnez le menu Développeur , puis sélectionnez Mode Création dans le groupe de commandes du ruban pour désactiver le mode Création . Cela activera les boutons.
Essayez de sélectionner chaque bouton pour voir comment la colonne des résultats « Taxes » change.
Envoyer les résultats du compte par e-mail à quelqu'un
Et si vous vouliez envoyer les résultats de la feuille de calcul à quelqu'un par e-mail ?
Vous pouvez créer un autre bouton, intitulé « Envoyer la feuille par e-mail au responsable », en suivant la même procédure. Le code de ce bouton utilisera un objet CDO Excel pour configurer les paramètres SMTP et envoyer les résultats par e-mail dans un format convivial.
Pour activer cette fonctionnalité, sélectionnez Outils et références . Faites défiler jusqu'à Bibliothèque Microsoft CDO pour Windows 2000 , activez-la, puis sélectionnez OK.
Vous devez créer trois sections de code principales pour envoyer un e-mail et y inclure les résultats du tableur.
La première étape consiste à définir des variables pour conserver l'objet, les adresses « à » et « de », ainsi que le texte du courriel.
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 payées ce trimestre" strFrom = "MyEmail@gmail.com" strTo = "BossEmail@gmail.com" strCc = "" strBcc = "" strBody = "Voici le détail des taxes payées sur les ventes ce trimestre."
Bien entendu, le texte doit être dynamique en fonction des résultats de l'article ; vous devrez donc ajouter une boucle qui parcourt la plage de valeurs, extrait les données et écrit une ligne à chaque fois dans le texte.
Définir StartCell = Range("A1"). vbCrLf Pour chaque cellule dans rng strBody = strBody & vbCrLf strBody = strBody & "Nous avons vendu " & Cells(rowCounter, 3).Value & " de " & Cells(rowCounter, 1).Value _ & " pour " & Cells(rowCounter, 4).Value & " et payé des taxes de " & Cells(rowCounter, 5).Value & "." rowCounter = rowCounter + 1 Cellule suivante
La section suivante explique comment configurer le serveur SMTP pour pouvoir envoyer des e-mails. Si vous utilisez Gmail, il s'agit généralement de votre adresse e-mail, de votre mot de passe et de l'adresse de votre serveur SMTP (smtp.gmail.com).
Définir CDO_Mail = CreateObject("CDO.Message") En cas d'erreur, aller à Gestion des erreurs Définir CDO_Config = CreateObject("CDO.Configuration") CDO_Config.Load -1 Définir SMTP_Config = CDO_Config.Fields Avec 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@website.com" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "mot de passe" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True Set .Configuration = CDO_Config End With
Remplacez email@website.com et password par les informations de votre compte.
Enfin, pour commencer l'envoi de l'e-mail, saisissez 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 Gestion des erreurs : Si Err.Description <> "" Alors MsgBox Err.Description
Remarque : Si vous rencontrez une erreur de transfert lors de l’exécution de ce code, il est probable que votre compte Google bloque l’option « Applications moins sécurisées ». Vous devrez alors vous rendre sur la page des paramètres des applications moins sécurisées et activer cette option.
Une fois cette option activée, votre e-mail sera envoyé. Voici à quoi il ressemblera pour le destinataire.
Comme vous pouvez le constater, Excel VBA permet d'automatiser de nombreuses tâches. N'hésitez pas à expérimenter avec les extraits de code présentés dans cet article et à créer vos propres automatisations VBA.
Les commentaires sont fermés.