LeCompagnon.info

VBA pour Excel

VBA pour Excel

VBA pour Excel - Objets et événements Excel

Sur cette page

Définitions
Modèle Objet Excel
Principaux objets Excel
Principaux événements Excel
Autres objets intéressants
Autres objets, autres propriétés
Objet implicite (With)
Exemple 1: Changer la couleur d'arrière-plan des cellules modifiées.
Exemple 2: Une fonction qui affiche le contenu réel d'une cellule.
Exemple 3: Empêcher la modification des cellules dont l'arrière-plan est rouge.
Exemple 4: Empêcher la modification des cellules ne faisant pas partie d'une plage.
Exemple 5: Empêcher le déplacement du curseur Excel tant qu'une condition n'est pas respectée.
Exemple 6: Trouver toutes les cellules d'une feuille contenant le mot Liberté.
Exemple 7: Obtenir le nom et la taille du dossier contenant le classeur Excel actif.

Accueil
Concepts de programmation
VBE l'éditeur VBA
Déclarations, types, références, expressions et assignations
Tests et branchements
Les boucles
Gestion d'erreur
Collections et tableaux
Dialogues et formulaires
Objets et événements Excel
Conseils de programmation
Liste d'instructions
Autres formations

Word 2003
Word 2007
Excel
Excel 2007/2010/2013
PowerPoint 2003
PowerPoint 2007
Access
Access 2010
Internet
Général
Intelligence financière
Vidéos

Navigation
Précédent
Suivant
Page Principale
FAQ
Nouveautés
Plan du site
Références
Motivations
Manuels de formation
Fichiers de démonstration
Contact
Par courriel

Abonnez-vous à
l'infolettre LCI
Partager






Abonnez-vous à l'infolettre LeCompagnon.info

201 fonctions pour les pressés









 

Définitions

Objet: Un objet est une instance d'une classe (!).
En fait, un objet est une "entité" informatique qu'un programme informatique peut manipuler.
Dans le cadre du présent tutoriel, un objet est une entité externe à VBA que votre programme VBA peut (veut?) manipuler.
Le cas le plus évident est une cellule de classeur. Pour la manipuler, VBA utilise une instance de la classe Range.
Classe: Une classe est une catégorie (sorte) d'objets. Votre programme peut manipuler une cellule précise (ou un groupe de cellule), mais pas le concept de cellule.
Propriétés: Chaque objet possède des propriétés qui le décrivent. La liste des propriétés varie d'une classe à l'autre, mais tous les objets d'une même classe ont les mêmes propriétés. Ce qui les distingue, ce sont les valeurs des propriétés.
Par exemple, toutes les feuilles Excel ont la propriété Name, mais la valeur de la propriété Name est différente pour chaque feuille (du moins on le souhaite).
Méthode: Une méthode est une action que peut réaliser un objet. La liste des méthodes varie d'une classe à l'autre, mais tous les objets d'une même classe ont les mêmes méthodes.
Par exemple, la méthode Copy d'une cellule permet de copier le contenu de cette cellule dans le presse-papier (ou dans une autre cellule).
Évidemment, il arrive qu'une méthode change les propriétés d'un objet. Par exemple, la méthode PasteSpecial d'une cellule change une ou plusieurs propriétés de cette cellule.
Événement: Un événement permet d'associer une procédure VBA à un objet. Par exemple, l'événement Change d'une feuille se produit lorsqu'une cellule de cette feuille est modifiée et permet d'associer une procédure nommée Worksheet_Change à cette feuille Excel.
Collection: Ensemble d'objets d'une même classe qui peuvent être adressés avec le nom de la collection et un numéro d'item.
Par exemple, un classeur Excel est une collection Sheets d'objets WorkSheet et/ou Chart

Modèle Objet Excel

VBA peut manipuler le contenu d'un classeur Excel par les objets qu'il contient. Mais comment trouver le nom de l'objet (ou de la classe) qu'il faut utiliser?

Le plus simple est d'utiliser l'enregistreuse de macro-commande.

Par exemple, pour trouver comment changer la couleur de fonds d'une feuille Excel,

-Démarrez l'enregistreuse de macro-commandes, en notant l'endroit où elle sera enregistrée.
-Changez la couleur du fonds (sélectionner la feuille, afficher la boîte de dialogue Format de cellule, choisir l'onglet remplissage puis une couleur).
-Arrêtez l'enregistreuse de macro-commande.
-Affichez-là dans l'éditeur VBA. Elle ressemble à:
 

Sheets("Feuil1").Select
Cells.Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark2
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

Dans cet enregistrement, on reconnait les objets:

-Sheets("Feuil1"): la feuille Feuill1 .
-Cells : collection de toutes les cellules de la feuille.
-Interior: collection de tous les intérieurs des cellules.

et les méthodes:

  • L'objet recherché est donc:
    Sheets("Feuil1").Cells.Interior
  • L'aide VBA Excel nous indique alors que cet objet possède une propriété Color. On peut donc programmer:
    Sheets("Feuil1").Cells.Interior.Color = vbYellow
    Ce qui est beaucoup plus efficace que le code produit par l'enregistreuse.
  • Une façon plus complexe est d'explorer le modèle objet Excel.
    L'aide VBA Excel 2007 permet de l'explorer en accédant à la rubrique:
    Référence du développeur Excel 2007
    Microsoft vous offre ici la liste des principaux objets Excel en ordre alphabétique, ce qui est pratiquement inutilisable.

    Utilisez plutôt l'aide VBA Excel 2003, qui est installée à l'adresse
    C:\Program Files\Microsoft Office\OFFICE11\1036\VBAXL10,CHM
    Vous pouvez alors parcourir la hiérarchie des objets Excel:

    et trouver l'objet qui vous intéresse en cliquant d'abord sur Range, puis en trouvant la propriété ou l'objet Interior, et en devinant qu'il faut adresser l'objet Interior de la collection Cells de la feuille voulue.
     

    Principaux objets Excel

    Les cellules Excel sont des objets de la classe Range.

    La meilleure façon d'accéder aux propriétés d'une plage de cellules Excel depuis VBA est de nommer cette plage en Excel, puis d'utiliser ce nom comme référence de range.

    Une technique beaucoup moins utile (mais hélas trop répandue) est d'utiliser l'adresse de la plage.

    Une variante de cette technique est l'utilisation de la méthode Evaluate (voir plus loin).

    Aussi peu utile.

    Deux autres façons utilisent des numéros de ligne et de colonne, et sont beaucoup plus utiles.

    Attention, l'objet Cells utilise l'adressage Ligne, Colonne, alors que Excel utilise Colonne, Ligne (E5).

    Ces deux dernières techniques peuvent être utilisées avec l'objet Range pour désigner des plages.

    L'utilisation d'un nom de plage au lieu d'une adresse permettra de déplacer la plage dans Excel sans devoir modifier la programmation VBA.
    Il est donc FORTEMENT suggéré de manipuler les lignes d'une liste de données ainsi:

    -Nommez la cellule de gauche de l'entête de liste.
    -Utilisez la notation Range(nom).Offset(ligne,colonne) pour représenter les champs de la liste.

    Cellule ou plage? Le modèle objet Excel ne fait pas la différence entre une plage et une cellule. Une cellule est simplement une plage ne contenant qu'une cellule.
    Range("A1:B9") est un objet de la même classe que Range("A1") et possède donc les mêmes propriétés (dont les valeurs peuvent être différentes) et méthodes.

    Un objet Range est aussi une collection de cellules, Comme une cellule est un Range (d'une cellule), c'est une collection ne comptant qu'un objet.
    Un Range est donc une collection de collections.

    Plages spéciales.  Certaines propriétés du modèle objet Excel contiennent des plages spécifiques:

    Cells Plage qui contient toutes les cellules d'une feuille:
    • ActiveSheet.Cells
    • Sheets("Province").Cells
    UsedRange Plage qui contient TOUTES les cellules occupées d'une feuille:
    • ActiveSheet.UsedRange
    • Sheets("Province").UsedRange
    CurrentRegion Plage continue (limitée par des lignes et des colonnes vides) dont fait partie la cellule:
    • Range("Titre").CurrentRegion
    Rows Collection des lignes d'une plage. Permet d'obtenir une ligne d'une plage:
    • Range("ListeProvinces").Rows(1)
    • Range("Titre").CurrentRegion.Rows(1)
    Columns Collection des colonnes d'une plage. Permet d'obtenir une colonne d'une plage:
    • Range("ListeProvinces").Columns(1)
    • Range("Titre").CurrentRegion.Columns(1)
    SpecialCells Plages spéciales d'une plage Excel:
    • SpecialCells(xlCellTypeBlanks)  Cellules vides
    • SpecialCells(xlCellTypeConstants). Cellules contenant des constantes
    • SpecialCells(xlCellTypeFormulas). Cellules contenant des formules
    • SpecialCells(xlCellTypeLastCell). Dernière cellule dans la plage utilisée
    • SpecialCells(xlCellTypeVisible).Toutes les cellules visibles
    • ...

    Les objets Range ont un grand nombre de propriétés. Voici les plus intéressantes:

    Column Numéro de la colonne de gauche de la plage
    • Range("B4:H6").Column vaut 2
    Columns.Count Nombre de colonnes de la plage
    • Range("B4:H6").Columns.Count vaut 7
    Row Numéro de la ligne du haut de la plage
    Rows.Count Nombre de lignes de la plage
    Count Nombre de cellules de la plage
    CurrentRegion Plage continue (limitée par des lignes et des colonnes vides) dont fait partie la cellule.
    End(xlDown) Cellule de la dernière ligne à la fin de la zone qui contient la plage. Correspond aux touches Fin+Bas.
    End(xlToRight) Cellule de la dernière colonne à la fin de la zone qui contient la plage. Correspond aux touches Fin+Droite.
    Text Valeur AFFICHÉE de la cellule, donc évaluée et formatée.
    Value Valeur ÉVALUÉE de la cellule.
    Worksheet Objet Worksheet qui est la feuille qui contient la plage

    Les feuilles Excel sont des objets de la classe Worksheet.

    Elles sont regroupées dans la collection Worksheets et dans la collection Sheets.

    On peut manipuler une feuille de classeur par son nom:

    -Worksheets("Provinces") est la feuille nommée province.
    -Sheets("Provinces") est aussi la feuille nommée province.

    La différence entre les deux collections est que la collection Sheets contient aussi les objets Chart du classeur.

    Les classeurs Excel sont des objets de la classe Workbook.

    Tous les classeurs ouverts sont regroupés dans la collection Workbooks.

    -Workbook("VBA Exemples.xls") est le classeur VBA Exemples.xls

    La programme Excel lui-même, lorsque démarré, est un objet de la classe Application.

    Le classeur actif est: Application.ActiveWorkbook ou plus simplement ActiveWorkbook.

    Le classeur contenant le code VBA en cours d'exécution est:Application.ThisWorkbook ou plus simplement ThisWorkbook.

    La feuille active du classeur actif est:Application.ActiveWorkbook.ActiveSheet ou plus simplement ActiveSheet.

    La cellule active de la feuille active du classeur actif est:Application.ActiveWorkbook.ActiveSheet.ActiveCell ou plus simplement ActiveCell.

    La cellule active de la feuille nommée Province du classeur actif est:Application.ActiveWorkbook.Worksheets("Province").ActiveCell ou plus simplement Worksheets("Province").ActiveCell.

    etc.

    L'objet Selection est l'objet passe-partout, qui contient ce qui est sélectionné dans le classeur Excel. Ce peut être une plage, un graphique, un tableau croisé ou n'importe quoi. L'utilisation de Selection en VBA est à éviter, puisqu'il risque fort de contenir une sélection inattendue lors de l'exécution du programme.

    La barre d'état de la fenêtre Excel est l'objet StatusBar.

    Méthodes Excel

    La plupart des opérations que l'utilisateur peut faire en Excel peuvent être programmées en VBA grâce aux méthodes des différents objets du modèle objet Excel.

    Voici les plus intéressantes:

    Evaluate Permet d'exécuter en VBA une formule Excel (ce qui est à droite du signe = dans une cellule). La méthode Evaluate peut être remplacée par les par les crochets:
    [2+2] équivaut à Evaluate("2+2") et vaut 4. Attention, ce qui est incorrect dans une cellule est incorrect pour Evaluate.
    • [A1] équivaut à Range("A1").Value
    • [Sum("A2:H6")] exécute la formule Excel Somme("A2:H6")
    Intersect Retourne une plage formée des cellules communes de deux plages.
    • Intersect(Range("A1:C4"),Range("B2:D5")) équivaut à Range("B2:C4")

    Cette méthode permet de savoir si une cellule appartient à une plage:

    • (Intersect(Range("B6"),Range("Provinces") is Nothing)
      est faux si B6 fait partie de la plage Provinces.
    Calculate Recalcule la feuille ou le classeur Excel
    • Calculate
    • ActiveWorksheet.Calculate
    Save Enregistre le classeur
    • ActiveWorkbook.Save
    Quit Ferme Excel.
    Attention, si le classeur a été modifié mais non sauvegardé, la boîte de dialogue de sauvegarde d'Excel sera affichée.
    Voir la méthode Save.
    • Application.Quit
    Clear
    ClearContents
    ClearFormats
    Effacent respectivement les cellules, les contenus ou les formats d'une plage.
    • Range("A1").Clear
    • Worksheet("Tableaux").Clear
    Copy Copie le contenu d'une plage dans une autre ou dans le presse-papier
    • Range("A1").Copy Range("B1")
      copie le contenu de A1 dans B1
    • Range("A1").Copy
      copie le contenu de A1 dans le presse-papier>
    PasteSpecial Colle le contenu d'une plage ou du presse-papier dans une plage. On peut spécifier le type de collage spécial: xlPasteAll (défaut), xlPasteAllExceptBorders,  xlPasteColumnWidths. xlPasteComments, xlPasteFormats, xlPasteFormulas, xlPasteFormulasAndNumberFormats, xlPasteValidation, xlPasteValues ou  xlPasteValuesAndNumberFormats.
    • Range("Total").Copy
      copie la plage Total dans le presse-papier
    • Range("A1").PasteSpecial (xlPasteValues)
      copie la VALEUR du presse-papier dans la cellule A1. li>
    Find
    et
    FindNext
    Ces deux méthodes permettent d'effectuer une recherche dans une plage de cellules. Voir Exemples.
    Select Sélectionne une cellule ou une feuille Excel.
    À éviter la plupart du temps en programmation VBA, car le déplacement du curseur Excel est rarement souhaitable pendant l'exécution d'un programme VBA.
    Sort Tri de plage. Voir l'aide VBA Excel pour en connaître le fonctionnement. Il est aussi utile d'utiliser l'enregistreuse de macro-commandes sur un tri manuel des données pour en analyser les paramètres.

    Principaux événements Excel

    Objet Événement Paramètres Se produit lorsque Utilisé pour
    Workbook Open   Le classeur Excel est ouvert
    • Afficher un écran d'accueil
    • Afficher ou masquer des éléments d'Excel ou du classeur
    • Initialiser des paramètres
    • ...
    Workbook BeforeClose Cancel Lorsqu'une demande de fermeture
    du classeur a été faite
    • Contrôler ou empêcher la sauvegarde du classeur
    • Gérer des copies de sécurité
    • Contrôler ou empêcher la fermeture du classeur
    • Rétablir l'affichage d'éléments Excel
    • ...
    Workbook SheetChange Sh la feuille modifiée
    Target
    la plage modifiée
    Lorsqu'une cellule ou plage du classeur est modifiée
    • Contrôler ou empêcher la modification d'une cellule (voir exemples).
      Utiliser lorsque la même procédure s'applique à toutes les plages du classeur
    Worksheet Change Target la plage modifiée Lorsqu'une cellule ou plage de la feuille qui contient la procédure Sub est modifiée
    • Contrôler ou empêcher la modification d'une cellule (voir exemples).
      Utiliser lorsque la procédure ne s'applique qu'aux plages d'une seule feuille
    Worksheet SelectionChange Target la nouvelle plage sélectionnée Lors de la sélection d'une autre plage dans un classeur
    • Contrôler ou empêcher le déplacement du curseur dans un classeur Excel (voir exemples).

    Remarquez que dans les événements SheetChange, Change et SelectionChange, VBA n'a pas directement accès aux valeurs précédant le changement,
    ni à un paramètre Cancel pour annuler le changement. Il faut utiliser une astuce pour empêcher le changement. Voir les exemples.

    Notez finalement qu'on peut désactiver le traitement des événements, en utilisant la propriété EnableEvents.

    Autres objets intéressants

    Me

    C'est un objet Excel ou VBA contenant le code en cours d'exécution. Si le code appartient à une feuille, c'est un objet Worksheet contenant cette feuille.
    Me est le plus souvent utilisé pour désigner le formulaire VBA (Forms) contenant le code en cours d'exécution. On évite ainsi de nommer le formulaire
    dans le code VBA , ce qui donne des programmes plus faciles à utiliser dans d'autres formulaires.

    -Me.Name contient le nom du formulaire en cours d'exécution.

    Utilisation la plus fréquente:

    -Me.Close ferme le formulaire qui contient cette ligne.

    Err

    C'est un objet VBA contenant la dernière erreur rencontrée. Ses propriétés permettent d'en connaître la nature:

    -Err.Description contient la description de la dernière erreur rencontrées par VBA.

    VBA peut déclencher une erreur avec Err:

    -Err.Raise 18 simule une interruption par l'utilisateur (Ctrl-Break).

    FileSystemObject

    C'est l'objet contenant des instructions permettant à VBA d'accéder à l'arborescence de dossiers et de fichiers de l'ordinateur.

    ATTENTION ce n'est pas un objet VBA ni un objet Excel, mais un objet de la librairie Microsoft Scripting Runtime.

    Vous devez référencer cette librairie dans l'éditeur de code VBA avant de l'utiliser. Voir Exemples.

    Objets intéressants:

    Drives Collection d'objets Drive
    Drive Objet contenant un lecteur
    Folders Collection d'objets Folder
    Folder Objet contenant un dossier
    Files Collection d'objets File
    File Objet contenant un fichier

    Un objet FileSystemObject contient une collection Drives.
    Chaque objet Drive de la collection Drives contient une collection Folders et une collection Files.
    Chaque objet Folder d'une collection Folders contient une collection Folders et une collection Files.
    Chaque objet File d'une collection Files contient un fichier (document Windows).

    Cette structure permet de parcourir l'arborescence de documents en VBA comme avec l'explorateur Windows.

    Autres objets, autres propriétés

    Les environnements Excel, Office et Windows offrent une richesse incroyable d'objets et de propriétés qu'il est impossible de décrire ici
    (ni ailleurs d'ailleurs), ne serait-ce que parce que cette liste change constamment

    Il vous faudra donc développer l'habilité de soupçonner l'existence de l'objet que vous souhaitez utiliser et d'en découvrir le nom et la documentation.
    Bonne chance.

    Objet implicite (With)

    Il arrive fréquemment qu'une série d'instructions utilise le même objet. L'écriture (et la lecture) du code VBA devient alors fastidieuse, avec la répétition incessante de la même référence.

    L'instruction With permet de réduire les répétitions.

    With objet
       
    [instructions]
    End With
     

    Où:

    • objet Nom d'un objet.
    • instructions Une ou plusieurs instructions VBA

    À l'intérieur du bloc With ... End With, toute référence commençant par un . (point) appartient à objet.

    ATTENTION: With N'est PAS une instruction de boucle ou de répétition ou de test. Si le code doit être répété, il doit être encadré d'une instruction de boucle (Do) ou de test (If).

    Dans l'exemple 2 ci-dessous, le code:

        If Target.Interior.Color = Range("CouleurVerrou").Interior.Color Then
            Application.EnableEvents = False 'Empêcher une boucle d'événements
            Application.Undo                 'Annuler le changement
            Application.EnableEvents = True  'Très important de réactiver
        End If

    pourrait être:   

        With Application
            If Target.Interior.Color = Range("CouleurVerrou").Interior.Color Then
                .EnableEvents = False 'Empêcher une boucle d'événements
                .Undo                 'Annuler le changement
                .EnableEvents = True  'Très important de réactiver
            End If
        End With

    Exemples

    Exemple 1: Changer la couleur d'arrière-plan des cellules modifiées.

    • Créez la procédure événementielle
    1. Double-cliquez sur ThisWorkbook pour ouvrir le module VBA associé au classeur
    2. Sélectionnez Workbook dans la liste d'objets
    3. Sélectionnez SheetChange dans la liste d'événements

    La procédure Sub Workbook_SheetChange est créée pour vous.

     

     

     

    • Insérez le code ci-contre
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Auteur: Michel Berthiaume
    'Changer l'arrière-plan d'une cellule modifiée

        'Target est la plage modifiée (voir les paramètres de la procédure, ci-dessus)
        Target.Interior.Color = vbYellow 'Jaune

    End Sub
    • Testez le programme en Excel: toute cellule modifiée devient jaune.
     

    Il est possible de restreindre les modifications à une seule feuille:

    Si les deux techniques fonctionnent, la seconde limite l'éparpillement du code dans plusieurs modules et produit donc un programme plus facile à comprendre et à modifier plus tard.

    Exemple 2: Une fonction qui affiche le contenu réel d'une cellule.

    • Dans un module VBA Excel, insérer le code ci-joint:Ouvrez le module VBA associé au classeur Excel

     

    Function fnValeurCellule(rCellule)
    'Auteur: Michel Berthiaume
    'Retourne le contenu (formule ou valeur) de la cellule en paramètre
        If TypeName(rCellule) <> "Range" Then
            fnValeurCellule = "Erreur de paramètre"
            Exit Function
        End If
        fnValeurCellule = rCellule.Formula
    End Function

     

    Dans une (ou plusieurs) cellules du classeur Excel, utilisez la fonction
    =fnValeurCellule(A2)
    en remplaçant A2 par une référence à une cellule Excel dont vous voulez afficher le contenu réel.

    Exemple 3: Empêcher la modification des cellules dont l'arrière-plan est rouge.

    • Ouvrez le module VBA associé au classeur Excel

     

     

    • Insérez le code ci-contre
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Auteur: Michel Berthiaume
    'Empêcher la modification de cellules dont l'arrière-plan
    ' est d'une couleur choisie par l'utilisateur
    'Note: la feuille doit contenir une cellule nommée CouleurVerrou
    ' de la couleur choisie

    'Target est la plage modifiée (voir les paramètres de la procédure, ci-dessus)
        If Target.Interior.Color = Range("CouleurVerrou").Interior.Color Then
            Application.EnableEvents = False 'Empêcher une boucle d'événements
            Application.Undo                 'Annuler le changement
            Application.EnableEvents = True  'Très important de réactiver
        End If
    End Sub
     
    • Dans Excel, changez l'arrière-plan d'une plage de cellules.
    • Créez une plage nommée CouleurVerrou à partir d'une cellule de la plage ainsi colorée.
    • Testez le programme en Excel: toute changement à une cellule de la plage colorée est annulée par le programme.
     

    Remarquez ici l'utilisation de la propriété EnableEvents pour empêcher que l'instruction suivante (la méthode Undo) provoque l'événement Workbook_SheetChange et entraîne ainsi une succession sans fin d'exécution de la procédure Sub correspondante.
    Workbook_SheetChange appelant Workbook_SheetChange appelant Workbook_SheetChange appelant Workbook_SheetChange ...

    Exemple 4: Empêcher la modification des cellules ne faisant pas partie d'une plage.

    • Ouvrez le module VBA associé au classeur Excel

     

     

    • Insérez le code ci-contre
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Auteur: Michel Berthiaume
    'Empêcher la modification de cellules en dehors
    ' d'une plage nommée ZoneDeSaisie
    'Note: le classeur doit contenir une plage nommée ZoneDeSaisie

        'Target est la plage modifiée (voir les paramètres de la procédure, ci-dessus)
        If Intersect(Target, Range("ZoneDeSaisie")) Is Nothing Then
            Application.EnableEvents = False 'Empêcher une boucle d'événements
            Application.Undo                 'Annuler le changement
            Application.EnableEvents = True  'Très important de réactiver
        End If
    End Sub
     
    • Dans Excel, créez une plage nommée ZoneDeSaisie.
    • Testez le programme en Excel: toute changement à une cellule ne faisant pas partie de la plage ZoneDeSaisie est annulée par le programme.
     

    Exemple 5: Empêcher le déplacement du curseur Excel tant qu'une condition n'est pas respectée.

    L'événement qui nous intéresse est SelectionChange, qui n'a pas de paramètre Cancel, donc qu'on ne peut pas annuler directement.
    La méthode Undo ne fonctionne pas non plus, car elle n'annule pas le déplacement. De plus, elle risquerait d'annuler une modification, alors qu'on ne veut annuler que le déplacement.
    Il faudra donc que le programme garde trace de la dernière cellule sélectionnée.

    Dans le programme ci-dessous, dès que l'utilisateur sélectionne une cellule de la plage ZoneDeSaisie, il ne peut pas déplacer le curseur hors de la plage tant que la somme des cellules de la plage sera différente de 10.

    • Insérez les fonctions ci-contre dans un module VBA:
    Function fnDéplacementValide()
    'Auteur: Michel Berthiaume
    'Retourne Vrai si la somme des cellules de la plage ZoneDeSaisie est égale à 10

        fnDéplacementValide = ([sum(ZoneDeSaisie)] = 10)

    End Function

    Function fnPlageAdansPlageB(plageA, PlageB)
    'Auteur: Michel Berthiaume
    'Retourne Vrai si plageA est complètement dans PlageB ou l'inverse

        If Intersect(plageA, PlageB) Is Nothing Then
            fnPlageAdansPlageB = False
        Else
            fnPlageAdansPlageB = (Intersect(plageA, PlageB).Count = plageA.Count)
        End If

    End Function

    • Au début du module VBA ThisWorkbook sous Option Explicit, insérez
    Public rCellulePrécédente As Range    'Contiendra la dernière cellule sélectionnée
    • Dans la procédure Sub Workbook_Open() insérez:
    Set rCellulePrécédente = ActiveCell   'Initialiser à la sélection initiale (à l'ouverture)
    • Dans la procédure Sub Workbook_SheetSelectionChange() insérez:
     

     

    Exemple 6: Trouver toutes les cellules d'une feuille contenant le mot Liberté.

    Technique 1, utilisant une boucle testant chaque cellule d'une plage. Notez bien que le résultat est une plage de cellules disjointes, chacune contenant le mot cherché.

    Notez aussi qu'on a généralisé la solution, qui permet de chercher n'importe quelle chaîne de caractères dans n'importe quelle plage Excel.

    • Insérez les fonctions ci-contre dans un module VBA:
    Function fnTrouveMotDansPlage(sMot, rPlage)
    'Auteur: Michel Berthiaume
    'Retourne une plage formée des cellules de rPlage contenant sMot

    Dim rTrouvée As Range 'contiendra les cellules trouvées
    Dim rCellule As Range 'contiendra chaque cellule testée, à tour de rôle

        For Each rCellule In rPlage    'Pour chaque cellule de rPlage
            'Si sMot est dans rCellule
            If InStr(UCase(rCellule.Value), UCase(sMot)) > 0 Then
                If rTrouvée Is Nothing Then  'Première cellule trouvée
                    Set rTrouvée = rCellule
                Else                         'Cellules trouvées suivantes
                    Set rTrouvée = Union(rTrouvée, rCellule)
                End If
            End If   
        Next
        Set fnTrouveMotDansPlage = rTrouvée
    End Function

    • Le nombre de cellules contenant le mot Liberté trouvées dans la plage A1:C6 peut maintenant être obtenu avec l'expression VBA:
    fnTrouveMotDansPlage("Liberté", Range("A1:C6")).Count
    • ou avec la formule Excel
    =NBVAL(fnTrouveMotDansPlage("Liberté";A1:C6))
    • On peut mettre ces cellules en rouge avec l'instruction VBA:
    fnTrouveMotDansPlage("Liberté", Range("A1:C6")).interior.color = vbRed

    Remarques sur cette fonction

    Technique 2, utilisant les méthodes Find et Findnext dans une boucle cherchant les cellules d'une plage. Notez bien que le résultat est le même que dans la technique 1.

    • Insérez les fonctions ci-contre dans un module VBA:
    Function fnTrouveMotDansPlage(sMot, rPlage)
    'Auteur: Michel Berthiaume
    'Retourne une plage formée des cellules de rPlage contenant sMot


    Dim rTrouvées As Range
    Dim rCelluleTrouvée As Range
    Dim rPremièreTrouvée As Range

        Set rCelluleTrouvée = rPlage.Find(sMot)
        If rCelluleTrouvée Is Nothing Then
            Exit Function 'Aucune occurrence
        End If

        'Find et FindNext trouveront toutes les occurrences,
        'puis recommenceront à la première
        'Il faut donc chercher jusqu'à ce qu'on trouve la première à nouveau.
        Set rPremièreTrouvée = rCelluleTrouvée
        Set rTrouvées = rCelluleTrouvée
        Set rCelluleTrouvée = rPlage.FindNext(rCelluleTrouvée)
     
        Do While Not rCelluleTrouvée = rPremièreTrouvée
            Set rTrouvées = Union(rTrouvées, rCelluleTrouvée)
            Set rCelluleTrouvée = rPlage.FindNext(rCelluleTrouvée)
        Loop
        Set fnTrouveMotDansPlage2 = rTrouvées
    End Function

    Exemple 7: Obtenir le nom et la taille du dossier contenant le classeur Excel actif.

     

    • Insérez les fonctions ci-contre dans un module VBA:
    Function fnDossier(Optional param = 0)
    'Auteur Michel Berthiaume
    'Retourne le nom du dossier et/ou l'espace qu'il utilise

    'La ligne suivante requiert une référence à Microsoft Scripting Runtime
    Dim fso As New Scripting.FileSystemObject

        If param = 1 Then
            fnDossier = ActiveWorkbook.Path
            Exit Function
        ElseIf param = 2 Then
            fnDossier = (fso.GetFolder(ActiveWorkbook.Path).Size) / 1024
            Exit Function
        Else
            fnDossier = ActiveWorkbook.Path & ": " & _
                Round(fso.GetFolder(ActiveWorkbook.Path).Size / 1024, 2) & _
                " Ko"
        End If

    End Function

    Remarques sur cette fonction

     




     

    Vous avez apprécié cette page?

    Dîtes que vous l'aimez en utilisant le bouton Facebook, Twitter ou l'un des autres services sous le bouton "Share".

    Cliquez sur le bouton de votre choix

    Abonnez-vous à l'infolettre

    Abonnez-vous à l'infolettre du site pour recevoir les dernières nouvelles et aussi des formations à votre courriel. Vous pouvez voir la liste des dernières infolettres ici.


    Cliquez ici pour vous abonner à l'infolettre





    Ce site est hébergé par 1&1.com