Avancée Tutorial VBA Excel

September 30

Avancée Tutorial VBA Excel


Le langage de script de Microsoft Excel, Visual Basic pour Applications, dispose de nombreux outils pour étendre les capacités par défaut d'Excel. Un tel outil est la possibilité d'ajouter des contrôles (tels que les cases à cocher et boutons) directement sur des feuilles de calcul. Utilisation des commandes de Visual Basic et sa capacité à puiser dans les fonctions de base d'Excel fait pour un tutoriel efficace pour améliorer Excel via Visual Basic pour Applications.

Projet: Utiliser Visual Basic pour Compute sous-totaux

Avancée Tutorial VBA Excel


Entreprendre un projet de calcul des sous-totaux pour les colonnes d'une table de feuille de calcul. Commencez par ouvrir Excel et la création d'une table partout dans la feuille de calcul. Le tableau est au début comme la table de l'Illustration 1.

Sélectionnez la table, y compris les en-têtes de colonnes, et de créer le nom "mytab" pour cette table. Créez le nom en entrant dans Nom Box Excel, qui est vers le coin supérieur gauche de l'espace de travail Excel.

Ajouter contrôles Visual Basic

Avancée Tutorial VBA Excel


Ajouter des cases à cocher à la table, chaque case assis au-dessus d'une colonne de table. Vous pouvez trouver les cases à cocher sous Developer> Insérer> Contrôles de formulaire. En outre, ajouter deux boutons à la feuille de calcul. Faites glisser et déposez-les dans la même zone que les cases à cocher, à gauche et à droite de la table mytab. Clic droit sur le bouton gauche et changer son texte à lire "Do sous-totaux." Modifier le texte du bouton droit de "Supprimer les sous-totaux."

Placez les boutons et les cases de sorte que votre tableau ressemble à la table Illustration 2.

Ecrire le code

Maintenant que vous avez conçu la table et ses commandes, vous allez écrire le code qui fait les sous-totaux sur la table. Entrez l'IDE Visual Basic (environnement de développement intégré) et ajouter un module. Cliquez sur Insertion> Module. Dans la fenêtre de code du nouveau module, entrez ce code de programme:

Option Explicit

Public Sub doSubtotal ()

Dim s As String
r Dim comme gamme
c Dim As Object
Dim ar () As Integer
Dim Ifield As Integer
Dim varItems
Dim nChkd As Integer

ReDim ar (0 à 0)

'Éliminer les sous-totaux antérieurs
RemoveSubtotals

«Créer le tableau des champs indices (à partir de 1) sous-total

Ifield = 1
nChkd = 0

'Boucle à travers les cases à cocher. Si l'on est cochée, ajouter son champ d'ensemble
Pour chaque c Dans ActiveSheet.CheckBoxes

Si (c.Value = 1) Ensuite,
nChkd nChkd = + 1
'Ajouter ce champ pour tableau

ar (UBound (ar)) = Ifield
ReDim Preserve ar (0 Pour UBound (ar) + 1) 'ajouter élément pour le prochain champ sélectionné

Fin si

Ifield = Ifield + 1

Prochain

Si (nChkd = 0) Ensuite,

MsgBox ("Please check at least one box.")
Exit Sub

Fin si

ReDim Preserve ar (0 Pour UBound (ar) - 1) "Supprimer vide dernier élément
varItems = ar

«Do subtotale réelle. Tout d'abord, trouver la gamme à faire rapport au montant sur

Set r = Application.Names ( "mytab"). RefersToRange 'récupérer la plage nommée sous-total

r.Subtotal GroupBy: = 1, Fonction: = xlSum, TotalList: = varItems, SummaryBelowData: = xlSummaryBelow

End Sub

Public Sub RemoveSubtotals ()

'Vérifiez subtotale avant, en utilisant le stockage fourni par le champ de commentaire dans l'espace de noms pour mytab (voir Formules> Gestionnaire Nom)
«Nous stockons original, la première colonne de la table. Si la colonne de départ de la table actuelle diffère, nous devons restaurer la table à l'emplacement d'origine.

r Dim comme gamme
Dim s As String
Dim nOrigCol As Integer

Set r = Application.Names ( "mytab"). RefersToRange
s = Application.Names ( "mytab"). Commentaire

'Aucun commentaire signifie pas courir avant, donc pas de retrait du sous-total avant, ou le réglage de la plage d'origine, est nécessaire.

Mais, sauver la colonne de départ de la table pour le prochain appel à cette fonction.
Si (s = "") Ensuite,

Application.Names("myTab").Comment = r.Column
Exit Sub

Fin si

Application.Range ( "a1: xfd65536") RemoveSubtotal.

«Réglage de la plage: supprimer une colonne si l'on a été ajouté
nOrigCol = CInt (s)
Si (nOrigCol <r.Column) Ensuite,
r.Previous.EntireColumn.Delete

Fin si

End Sub

Exécutez le programme

Retour à la feuille de calcul Excel qui a la table mytab. Affecter les boutons pour les macros que vous venez d'écrire. Faites un clic droit sur le bouton "Do sous-totaux" et sélectionnez "Affecter une macro." Dans la boîte de dialogue qui apparaît, sélectionnez la fonction "doSubtotal", qui fait partie du code que vous venez d'écrire. Fermez la boîte de dialogue en cliquant sur "OK" et affecter l'autre fonction macro que vous avez écrit, RemoveSubtotals, à l'autre bouton.

Exécutez votre programme en vérifiant toute combinaison des cases à cocher et en cliquant sur le bouton faire des sous-totaux. Désactivez les sous-totaux en appuyant sur la touche.


          

Post a comment: