Si vous débutez avec VBA, vous voudrez commencer par étudier nos Guide VBA pour les débutants. Mais si vous êtes un expert chevronné de VBA et que vous cherchez des choses plus avancées que vous pouvez faire avec VBA dans Excel, continuez à lire.
La possibilité d'utiliser le codage VBA dans Excel ouvre un monde entier d'automatisation. Vous pouvez automatiser les calculs dans Excel, les boutons-poussoirs et même envoyer des e-mails. Il y a plus de possibilités d'automatiser votre travail quotidien avec VBA que vous ne le pensez.
Guide VBA avancé pour Microsoft Excel
L'objectif principal de l'écriture de code VBA dans Excel est de permettre d'extraire des informations à partir d'une feuille de calcul, effectuez divers calculs dessus, puis réécrivez les résultats dans la feuille de calcul
Voici les utilisations les plus courantes de VBA dans Excel.
Avec ces trois exemples, vous devriez être capable d'écrire une variété de votre propre code VBA Excel avancé.
Importer des données et effectuer des calculs
L'une des choses les plus courantes pour lesquelles Excel utilise effectue 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 sortir ces valeurs dans une autre feuille ou un autre rapport.
In_content_1 all: [300x250] / dfp : [640x360]->Avec VBA, vous pouvez automatiser l'ensemble du processus. Par exemple, si vous avez un nouveau fichier CSV téléchargé dans un répertoire sur votre ordinateur tous les lundis, vous pouvez configurer votre code VBA pour qu'il s'exécute lorsque vous ouvrez votre feuille de calcul le mardi matin.
Le code d'importation suivant sera exécutez et importez le fichier CSV dans votre feuille de calcul Excel.
Dim ws As Worksheet, strFile As StringSet 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 zones de liste déroulante objet et méthode, choisissez Feuille de travailet Activer. 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 le Avec, la boucle 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 est importé dans votre feuille de calcul vierge, dans la feuille1.
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és sur la vente de chaque article.
L'ordre des actions que votre code doit entreprendre est:
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 dans 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 ouvrez votre feuille de calcul Excel, celle-ci s'éteint automatiquement et obtient la copie la plus récente des données du fichier CSV. Ensuite, il effectuera les calculs et inscrira les résultats sur la feuille. Vous n'avez plus rien à faire manuellement!
Calculer les résultats à partir du bouton Appuyez sur
Si vous préférez avoir un contrôle plus direct sur l'exécution des calculs , plutôt que de s'exécuter automatiquement lorsque la feuille s'ouvre, vous pouvez utiliser un bouton de contrôle à la place.
Les boutons de contrôle sont utiles si vous souhaitez contrôler les calculs utilisés. Par exemple, dans ce même cas que ci-dessus, que faire si vous souhaitez utiliser un taux de taxe de 5% pour une région et un taux de taxe de 7% pour une autre?
Vous pouvez autoriser le même code d'importation CSV à exécuter automatiquement, mais laissez le code de calcul de taxe s'exécuter lorsque vous appuyez sur le bouton approprié.
En utilisant la même feuille de calcul que ci-dessus, sélectionnez l'onglet Développeur, puis sélectionnez Insérerdans le groupe Contrôlesdu ruban. Sélectionnez le bouton-poussoirContrôle ActiveX dans le menu déroulant.
Dessinez le bouton-poussoir sur n'importe quelle partie de la feuille à l'écart des données.
Cliquez avec le bouton droit sur le bouton poussoir et sélectionnez Propriétés. Dans la fenêtre Propriétés, remplacez la légende par ce que vous souhaitez afficher pour l'utilisateur. Dans ce cas, il peut s'agir de Calculer la taxe de 5%.
Vous verrez ce texte reflété sur le bouton-poussoir lui-même. Fermez la fenêtre propriétéset double-cliquez sur le bouton-poussoir lui-même. 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 appuiera sur le bouton poussoir.
Collez le code de calcul de taxe de la section ci-dessus dans cette fonction, en gardant le multiplicateur de 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 Calculez la taxe de 7%.
Double-cliquez sur ce bouton et collez le même code, mais faites le multiplicateur de taxe 0,07.
Maintenant, selon le bouton sur lequel vous appuyez, la colonne des taxes être calculé en conséquence.
Une fois que vous avez terminé, vous aurez les deux boutons-poussoirs sur votre feuille. Chacun d'entre eux lancera un calcul de taxe différent et inscrira des résultats différents dans la colonne des résultats.
Pour envoyer un texte, sélectionnez le menu Développeur, puis sélectionnez Mode conceptiondans le groupe Contrôles du ruban pour désactiver le Mode conception. 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
si vous souhaitez envoyer les résultats sur la feuille de calcul à quelqu'un par e-mail?
Vous pouvez créer un autre bouton appelé Envoyer la feuille de courrier électronique au patronen utilisant la même procédure que 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 des résultats par e-mail dans un format lisible par l'utilisateur.
Pour activer cette fonctionnalité, vous devez sélectionner Outils et Références. Faites défiler jusqu'à Bibliothèque Microsoft CDO pour Windows 2000, activez-la et sélectionnez OK.
Il y a trois sections principales dans le code que vous devez créer pour envoyer un e-mail et incorporer les résultats de la feuille de calcul.
La première consiste à configurer les variables à conserver l'objet, les adresses de destination et de provenance 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 devez 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 consiste à configurer les 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 et du serveur SMTP Gmail (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
Remplacer [email protected] et mot de passe avec les détails de votre 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: si vous voyez une erreur de transport lorsque vous essayez d'exécuter ce code, c'est probablement parce que votre compte Google empêche les «applications moins sécurisées» de s'exécuter. Vous devrez visiter les page des paramètres des applications moins sécurisées et activer cette fonctionnalité.
Une fois cela activé, 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, vous pouvez réellement automatiser beaucoup de choses avec Excel VBA. Essayez de jouer avec les extraits de code que vous avez appris dans cet article et créez vos propres automatisations VBA uniques.