Un guide VBA avancé pour MS Excel
Si vous ne faites que commencer avec VBA, vous voudrez commencer à étudier notre guide VBA pour les débutants. 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 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
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 d’écrire les résultats dans la feuille de calcul.
Voici les utilisations les plus courantes de VBA dans Excel.
- Importez des données et effectuez des calculs
- Calculer les résultats d’un utilisateur appuyant sur un bouton
- Envoyer les résultats du calcul par e-mail à quelqu’un
Avec ces trois exemples, vous devriez être en mesure d’écrire une variété de votre propre code VBA Excel avancé.
Importation de données et exécution de calculs
L’une des choses les plus courantes pour lesquelles les gens utilisent Excel est d’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 rapport.
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 pour la première fois le mardi 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:temppurchases.csv”
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End WithOuvrez l’outil d’édition Excel VBA et sélectionnez l’objet Sheet1. Dans les listes déroulantes d’objets et de méthodes, choisissez Feuille de travail et Activer. Cela exécutera le code chaque fois que vous ouvrirez la feuille de calcul.
Cela créera un Sous Worksheet_Activate () fonction. Collez le code ci-dessus dans cette fonction.
Cela définit la feuille de calcul active sur Feuille1, efface la feuille, se connecte au fichier en utilisant le chemin de fichier que vous avez défini avec le strFile variable, puis le Avec loop 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 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, disons que vous souhaitez calculer les 5% de taxes payées sur la vente de chaque article.
L’ordre des actions que votre code doit entreprendre est:
- Créer une nouvelle colonne de résultats appelée impôts.
- Parcourez le unités vendues colonne et calculez la taxe de vente.
- Écrivez les résultats du calcul sur 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 DoubleSet 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 recherche 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 ouvrez votre feuille de calcul Excel, elle sort automatiquement et obtient 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 un bouton
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 le même cas que ci-dessus, que se passe-t-il 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 l’exécution automatique du même code d’importation CSV, 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 le Développeur onglet et sélectionnez Insérer du Les contrôles groupe dans le ruban. Sélectionnez le bouton poussoir Contrôle ActiveX 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 vont aller.
Cliquez avec le bouton droit sur le bouton poussoir et sélectionnez Propriétés. Dans la fenêtre Propriétés, modifiez la légende en ce que vous souhaitez afficher à l’utilisateur. Dans ce cas, cela pourrait être Calculer la taxe de 5%.
Vous verrez ce texte reflété sur le bouton poussoir lui-même. Fermer la Propriétés et double-cliquez sur le bouton poussoir lui-même. Cela ouvrira la fenêtre de l’éditeur de code et votre curseur sera à 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 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 StringSet ws = ActiveWorkbook.Sheets("Sheet1")
Maintenant, répétez le processus en créant un deuxième bouton-poussoir. Faire la légende Calculer la taxe de 7%.
Double-cliquez sur ce bouton et collez le même code, mais définissez le multiplicateur de taxe sur 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 un SMS, sélectionnez le Développeur menu et sélectionnez Mode de conception former le groupe Contrôles dans le ruban pour désactiver Mode de conception. Cela activera les boutons poussoirs.
Essayez de sélectionner chaque bouton-poussoir pour voir comment la colonne de résultats «taxes» change.
Envoyer les résultats du calcul par e-mail à quelqu’un
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é Envoyer la feuille au patron en utilisant la même procédure ci-dessus. Le code de ce bouton impliquera l’utilisation de l’objet CDO Excel pour configurer les paramètres de messagerie SMTP et l’envoi des résultats par courrier électronique 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-le et sélectionnez D’accord.
Le code que vous devez créer comporte trois sections principales 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 de destination et de départ 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 = "MyEmail@gmail.com"
strTo = "BossEmail@gmail.com"
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 de la feuille, vous devrez donc ajouter ici une boucle qui traverse 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 cellLa section suivante concerne 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 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@website.com"
.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 WithRemplacez email@website.com et mot de passe par vos propres détails de 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.SendError_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 bloque l’exécution des “applications moins sécurisées”. Vous devrez visiter le page des paramètres des applications moins sécurisées et activez cette fonction.
Une fois 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 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.
