Un guide VBA avancé pour MS Excel

Si vous ne faites que commencer avec VBA, alors vous voudrez commencer à étudier notre guide VBA pour les débutants. Mais si vous êtes un expert VBA chevronné et que vous cherchez des choses plus avancées, vous pouvez faire avec VBA dans Excel, alors 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 un e-mail. Il y a plus de possibilités d'automatiser votre travail quotidien avec VBA que vous ne le pensez.

Cela définit la feuille de calcul active pour Feuille, efface la feuille, se connecte au fichier à l'aide du chemin de fichier que vous avez défini avec le strfile variable, puis le Avec faire des cycles de boucle à travers 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 Feuille.

L'importation n'est que la première étape. Ensuite, vous souhaitez créer une nouvelle en-tête pour la colonne qui contiendra vos résultats de calcul. Dans cet exemple, disons que vous souhaitez calculer les impôts de 5% payés sur la vente de chaque article.

L'ordre des actions que votre code devrait prendre est:

1. Créer une nouvelle colonne de résultats appelée taxes.
2. Boucle à travers le unités vendues colonne et calculer la taxe de vente.
3. Écrivez les résultats de calcul sur la ligne appropriée dans la feuille.

Le code suivant accomplira toutes ces étapes.

Dim lastrow aussi longtemps
DIM STARTCELL As Range
Dim Rowcounter comme entier
Rng dim comme gamme, cellule comme gamme
Dim flTTax comme double
Définir StartCell = Range ("A1")
'Trouver la dernière ligne et la colonne
Lastrow = ws.Cellules (ws.Lignes.Compter, startcell.Colonne).Fin (xlup).Ligne
Définir RNG = WS.Gamme (ws.Cellules (2, 4), ws.Cellules (lastrow, 4))
Rowcounter = 2
Cellules (1, 5) = "taxes"
Pour chaque cellule en RNG
flTTax = cellule.Valeur * 0.05
Cellules (Rowcounter, 5) = flTTax
Rowcounter = RowCounter + 1
Cellule suivante

Ce code trouve la dernière ligne de votre feuille de données, puis définit la gamme de cellules (la colonne avec les prix de vente) selon la première et dernière rangée de données. Ensuite, le code fait des boucles à travers chacune de ces cellules, effectue le calcul de l'impôt 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.

Maintenant, chaque fois que vous ouvrez votre feuille de calcul Excel, il va automatiquement sortir et obtenir la copie la plus fraîche des données du fichier CSV. Ensuite, il effectuera les calculs et rédigera les résultats sur la feuille. Tu n'as plus à faire quoi que ce soit manuellement!

Calculer les résultats de la presse sur le bouton

Si vous préférez avoir un contrôle plus direct lorsque les calculs s'exécutent, 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 se passe-t-il si vous souhaitez utiliser un taux d'imposition de 5% pour une région et un taux d'imposition de 7% pour un autre?

Vous pouvez autoriser le même code d'importation CSV à s'exécuter automatiquement, mais laisser le code de calcul de l'impôt pour 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 Contrôles groupe dans le ruban. Sélectionnez le bouton-poussoir Contrôle ActiveX à partir du menu déroulant.

Dessinez le bouton-poussoir sur n'importe quelle partie de la feuille loin de l'endroit où les données iront.

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, ça 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 fenêtre 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 au bouton-poussoir.

Collez le code de calcul de l'impôt de la section ci-dessus dans cette fonction, en gardant le multiplicateur de taux d'imposition à 0.05. N'oubliez pas d'inclure les 2 lignes suivantes pour définir la feuille active.

Dim ws comme feuille de calcul, strfile comme chaîne
Définir WS = ActiveworkBook.Feuilles ("sheet1")

Maintenant, répétez à nouveau le processus, 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 faites le multiplicateur d'impôt 0.07.

Maintenant, selon le bouton que vous appuyez, la colonne des taxes sera calculée en conséquence.

Une fois que vous aurez terminé, vous aurez les deux boutons appuyés sur votre feuille. Chacun d'eux déclenchera un calcul d'impôt différent et écrira différents résultats dans la colonne des résultats.

Pour envoyer un SMS, sélectionnez le Développeur menu et sélectionner Mode de conception former le groupe de commandes 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.

Résultats du calcul de l'e-mail à quelqu'un

Et 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é Feuille électronique à Boss en utilisant la même procédure ci-dessus. Le code de ce bouton impliquera d'utiliser l'objet Excel CDO pour configurer les paramètres de messagerie SMTP et envoyer un e-mail aux résultats dans un format lisible par l'utilisateur.

Pour activer cette fonctionnalité, vous devez sélectionner Outils et références. Faites défiler jusqu'à Microsoft CDO pour la bibliothèque Windows 2000, activer et sélectionner D'ACCORD.

Il y a trois sections principales dans le code que vous devez créer pour envoyer un e-mail et intégrer les résultats de la feuille de calcul.

Le premier consiste à mettre en place des variables pour maintenir le sujet, vers et depuis les adresses, et le corps de messagerie.

Dim cdo_mail comme objet
Dim cdo_config comme objet
Dim smtp_config comme variante
Dim StrSubject en tant que chaîne
Dim Strfrom comme chaîne
Dim strto comme chaîne
DIM STRCC comme chaîne
Dim Strbcc comme chaîne
Dim Strbody comme chaîne
Dim lastrow aussi longtemps
DIM STARTCELL As Range
Dim Rowcounter comme entier
Rng dim comme gamme, cellule comme gamme
Dim flTTax comme double

Définir WS = ActiveworkBook.Feuilles ("sheet1")
strSubject = "taxes payées ce trimestre"
strFrom = "myemail @ gmail.com "
strTo = "bossmail @ gmail.com "
strcc = ""
strbcc = ""
strody = "Ce qui suit est la répartition des impôts payés sur les ventes ce trimestre."

Bien sûr, le corps doit être dynamique en fonction des résultats dans la feuille, donc ici vous devrez ajouter une boucle qui passe par la gamme, extrait les données et écrit une ligne à la fois au corps.

SET STARTCELL = Range ("A1") 'Rechercher la dernière ligne et la colonne lastrow = WS.Cellules (ws.Lignes.Compter, startcell.Colonne).Fin (xlup).Row set rng = ws.Gamme (ws.Cellules (2, 4), ws.Cellules (lastrow, 4)) Rowcounter = 2 Strbody = Strbody & vbcrlf pour chaque cellule dans RNG Strbody = Strbody & VBcrlf Strbody = Strbody & "We Send" & Cells (Rowcounter, 3).Valeur et "de" et cellules (Rowcounter, 1).Valeur _ & "pour" et cellules (Rowcounter, 4).Valeur et "et taxes payantes de" & Celles (Rowcounter, 5).Valeur & "."Rowcounter = Rowcounter + 1 Next Cell

La section suivante consiste à configurer les paramètres SMTP afin que vous puissiez envoyer un e-mail 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).

Définir cdo_mail = createObject ("CDO.Message ") Sur l'erreur Goto Error_Handling set cdo_config = createObject (" CDO.Configuration ") CDO_CONFIG.Charger -1 set smtp_config = cdo_config.Champs avec smtp_config .Article ("http: // schémas.Microsoft.com / CDO / Configuration / Sendusing ") = 2 .Article ("http: // schémas.Microsoft.com / cdo / configuration / smtpServer ") =" smtp.Gmail.com " .Article ("http: // schémas.Microsoft.com / cdo / configuration / smtpauthenticate ") = 1 .Article ("http: // schémas.Microsoft.com / cdo / configuration / sendUsername ") =" e-mail @ site Web.com " .Article ("http: // schémas.Microsoft.com / cdo / configuration / sendpassword ") =" mot de passe " .Article ("http: // schémas.Microsoft.com / cdo / configuration / smtpServerport ") = 465 .Article ("http: // schémas.Microsoft.com / cdo / configuration / smtpusessl ") = true .MISE À JOUR FIN AVEC avec CDO_MAIL SET .Configuration = cdo_config finie avec

Remplacer le site e-mail @.com et mot de passe avec les détails de votre compte.

Enfin, pour initier l'envoi d'e-mail, insérez le code suivant.

Cdo_mail.Sujet = strSubject
Cdo_mail.De = strFrom
Cdo_mail.À = strto
Cdo_mail.Textbody = strody
Cdo_mail.CC = Strcc
Cdo_mail.BCC = strbcc
Cdo_mail.Envoyer
La gestion des erreurs:
Si err.Description "" puis msgbox err.Description

Note: Si vous voyez une erreur de transport lorsque vous essayez d'exécuter ce code, c'est probablement parce que votre compte Google bloque les «applications moins sécurisées» de l'exécution. Vous devrez visiter la page Paramètres des applications les moins sécurisées et activer cette fonctionnalité.

Après cela, votre e-mail sera envoyé. C'est à quoi cela ressemble à 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 avec Excel VBA. Essayez de jouer avec les extraits de code que vous avez appris dans cet article et créez vos propres automations VBA uniques.