Si vous débutez avec VBA, vous devrez commencer par étudier Guide du débutant en VBAMais si vous êtes un expert VBA chevronné et que vous recherchez des fonctionnalités plus avancées que vous pouvez utiliser avec VBA dans 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 listes déroulantes Objet et Méthode, choisissez Feuille et ActiverCela aura pour effet d'exécuter le code à chaque fois que vous ouvrirez la feuille de calcul.
Cela créera une fonction Sous-feuille_Activer()Collez le code ci-dessus dans cette fonction.
Cela définit la feuille de calcul active sur Sheet1Numérisez la feuille et connectez-vous au fichier en utilisant le chemin d'accès que vous avez spécifié dans la variable. strFilePuis la boucle Avec le En parcourant chaque ligne du fichier et en plaçant les données sur la feuille en commençant par la cellule A1.
Si vous exécutez ce code, vous constaterez que les données du fichier CSV sont importées dans votre feuille de calcul vide. 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é.
En utilisant le même tableau de données mentionné ci-dessus, sélectionnez l'onglet Développeur Et sélectionnez insérer D'un groupe Contrôles Dans le ruban, sélectionnez un contrôle. bouton poussoir Sélectionnez 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.
Cliquez avec le bouton droit sur le bouton-poussoir et sélectionnez PropriétésDans la fenêtre Propriétés, modifiez la légende pour qu'elle corresponde au texte que vous souhaitez afficher à l'utilisateur. Dans ce cas, il pourrait s'agir de : Calculer la taxe de 5 %.
Ce texte apparaîtra sur le bouton. Fermer la fenêtre CaractéristiquesDouble-cliquez sur le même bouton. Une fenêtre d'éditeur de code s'ouvrira et le curseur se trouvera à l'intérieur de 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 une liste DéveloppeurEt sélectionnez Mode conception À partir du groupe de commandes du ruban, désactiver Mode conceptionCela 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 appelé Feuille de courriel au patron En utilisant la même procédure que celle décrite ci-dessus, le code de ce bouton impliquera l'utilisation d'un objet CDO Excel pour configurer les paramètres de messagerie SMTP et envoyer les résultats par e-mail dans un format convivial.
Pour activer cette fonctionnalité, vous devez sélectionner Outils et références. Faites défiler jusqu'à Bibliothèque Microsoft CDO pour Windows 2000Activez-le et 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
Remplacer email@siteweb.com Le mot de passe contient les informations relatives à 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
NoteSi vous rencontrez une erreur de transfert lors de l'exécution de ce code, il est probable que votre compte Google bloque l'exécution des applications jugées moins sécurisées. Veuillez consulter [lien/référence]. page des paramètres des applications moins sécurisées Activez 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.