Un guide VBA avancé pour MS Excel


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.

  • Importez des données et effectuez des calculs
  • Calculez les résultats d'un utilisateur en appuyant sur un bouton
  • Envoyez les résultats des calculs par e-mail à quelqu'un
  • 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 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 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:

    1. Créer nouvelle colonne de résultats intitulée taxes.
    2. Parcourez la colonne unités vendueset calculez la taxe de vente.
    3. Écrivez les résultats du calcul à la ligne appropriée de la feuille.
    4. 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 = "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. 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@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 With

      Remplacer email@website.com 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.

      Débuter avec VBA Excel et la programmation

      Related posts:


      11.02.2020