Les principaux objets MS Excel

Sources :

Pour écrire une macro-commande, il est indispensable de savoir comment accéder aux différents éléments composant un classeur. Ce billet en décrit donc les principaux et quelques méthodes et propriétés associées dans les paragraphes 1 à 6 suivants.
Il fournit ensuite quelques exemples de code de macro-commande ainsi que la façon de les mettre en oeuvre.

1. Le classeur actif : thisWorkbook

sheetExist() permet de tester l'existence d'une feuille dans le classeur,

2. Les feuilles du classeur : workSheets

L'objet workSheet, instancié au lancement de l'application MS Excel, représente la liste ( collection) des feuilles de calcul du classeur actif.

Lire ici la documentation officielle et la liste des propriétés et méthodes

Voir en bas de page une macro qui crée la copie d'une feuille de calcul.

3. La feuille active : activeSheet

L'objet activeSheet est instancié au chargement du classeur et représente la feuille active

La propriété name d'activeSheet rends le nom de la feuille active.

dim nomFeuilleActive as string
set nomFeuilleActive=activeSheet.name

la méthode range donne accès à une cellule ou une plage de cellule.

dim la plage
dim laCellule

set laCellule = activeSheet.range("E4")
set laPlage = activeSheet.range("B3:C7")

la méthode cells donne accès à une cellule ou une plage de cellule par ses coordonnées.

dim la plage
dim laCellule

'la cellule E4 a pour coordonnées 4, 5
set laCellule = activeSheet.cells (4,5)
set laPlage = ActiveSheet.Range(Cells(3, 2), Cells(7, 3))

4. La cellule :

Les méthodes Range et Cells( d'un objet "feuille de calcul" (par exemple activeSheet) instancie une cellule de cette feuille respectivement à partir de son nom ou de ses coordonnées. L'objet crée est de classe "range", voir la documentation officielle de toutes les propriétés et méthodes de l'interface.

dans MS Excel, les coordonnées débutent à 1 alors que dans LO Calc elle débutent à 0

dim laCelluleA1 as object
set laCelluleA1 = activeSheet.range("A1")

dim laCelluleB1 as object
set laCelluleB1 = activeSheet.cells(1, 2)

Quelques propriétes :

gestion du contenu de la cellule
- formula: le contenu est (sera) une formule ;
- formulaArray : pour les formule matricielle
  Les formules (simples ou matricielles doivent être écrites en anglais (par exemple : VLOOKUP pour RechercheV)
  et uniquement pour MSExcel, les arguments doivent être séparés par des virgules (et non de points virgules)
- hasFormula : rend true si la cellule contient une formule, false sinon ;
- value : le contenu est (sera) un nombre ou une chaîne de caractères.=

    laPlage = "A5:A10"
    LaFormule = "=COUNTA(" + laPlage + ")"
    laCelluleA1.Formula = LaFormule

gestion de la cellule
- select selectionne la cellule
- address rend la référence absolue de la cellule
  - ex :$A$1pour la cellule "A1"
  - $A$1:SC$3 pour la plage "A1:C3"
- column rend le numéro de la colonne
- row rend le numéro de la ligne

    Dim laCellule As Object
    Dim laLigne As Integer
    Dim laColonne As Integer

    Set laCellule = ActiveSheet.Range("A5")

    With laCellule
            laLigne = .Row()
            laColonne = .Column()
    End With
    MsgBox "coordonnées : (" + Str(laLigne) + " , " + Str(laColonne) + ")", 64, "Information :"

décalage ( voir la fonction DECALER)
- offset : le décalage ligne et colonne à appliquer ;
- resize : les dimensions de la plage résultat ;

Dim laCellule As Object

Set laCellule = ActiveSheet.Range("A5")
laCellule.Offset(1, 1).Resize(2, 3).Select

5. La plage de cellules :

La méthode Range ( d'un objet "feuille de calcul" (par exemple activeSheet) instancie une plage de cette feuille.

dim laplageA1B5 as object
set laPlageA1B5 = activeSheet.range("A1:B5")

La plupart des propriétés du paragraphe "les cellules" ci-dessus sont applicable à une plage de cellule.

6. Accès aux fonctions internes

Pour simplifier l'écriture de ce paragraphe, je prends l'exemple de la fonction MOYENNE() mais les indications fournies ici, sont vraies avec toutes les fonctions internes de LO CALC.

Lorsqu'une fonction interne de tableur ( ici MOYENNE ) est donnée, sous sa traduction anglaise (donc AVERAGE), en paramètre de l'attribut "formula" de cette cellule, la valeur de la cellule est la moyenne calculée. Cette valeur peut être récupérée par l'attribut "value" de cette cellule.

Dans une macro-commande il est possible d'obtenir cette valeur sans passer par la cellule grâce à l' objet prédéfini WorksheetFunction ( accès à la documentation officielle). Les fonctions du tableur Excel figurent en méthodes de cet objet.

Exemple :

   Dim result As Double
   Dim laPlage

   Set laPlage = ActiveSheet.Range("$C:$C")

   result = WorksheetFunction.Average(laPlage)
   ' version condensée :
   'result = WorksheetFunction.Average(Range("$C:$C"))

   MsgBox "la moyenne : " & result

La méthode moins "propre" qui utilise une cellule en intermédiaire

   Dim result As Double
   Dim laFormule as String
   Dim laCellule

   Set laCellule =ActiveSheet.Range("A1")
   laFormule = "=AVERAGE($C:$C)"

   laCellule.formula = laFormule

   result = laCellule.value

   MsgBox "la moyenne : " & result

7. Quelques exemples :

copier une feuille

Cliquez sur le bouton pour copier le code
dans le presse papier

 Sub feuilleNotes()

  Dim NbFeuilles As Integer
  Dim nomFeuilleSource As String
  Dim nomFeuilleCible As String
  Dim msg As String

  If SheetExist("Source") Then
    nomFeuilleCible = InputBox("Quel nom pour cette feuille de Notes ?", "Création de la feuille de Notes", "trimestre1")
    If nomFeuilleCible <> "" Then
         If SheetExist(nomFeuilleCible) Then
            msg = "La Feuille " + nomFeuilleCible + " existe déjà : abandon de la procédure"
            MsgBox msg, 64, "Abandon"
         Else
            Worksheets("Source").Copy After:=Worksheets(Worksheets.Count)
            ActiveSheet.Name = nomFeuilleCible
            'info utilisateur
            ActiveSheet.Range("D3").Select
            msg = "La Feuille " + nomFeuilleCible + " vient d' être créée "
            MsgBox msg, 64, "Confirmation de création de la feuille  
         End If
    End If
  Else
    MsgBox "Impossible de copier la feuille car " & nomFeuilleCible & " existe déjà", 64, "Abandon"
  End If

End Sub

8. L'exécution :

Pour tester les portions de code des macro-commandes, figurant dans les onglets ci-dessus, il faut les coller dans l'EDI (Environnement de développement Intégré) de MS Excel. Celui-ci est accessible par l'onglet "développeur", item "visualiser le code" d'un classeur.

Pour exécuter une macro-commande :

- A partir de l'EDI :

Utiliser l'item "exécuter" du menu "Exécution" de la barre de menus ou la touche de fonction F5 ou le bouton de la barre d'outils.

- A partir d'un raccourci clavier

Clique sur l'item "Macros" de l'onglet "développeur" puis suivez les indications de l'image ci-contre.

- A partir d'une cellule de la feuille de calcul :

Le code doit obligatoirement être encadré par function et end function (au lieu de sub end sub)

Taper le nom de cette fonction (avec les parenthèses, même si il n'y a pas de paramètres) comme formule dans une cellule quelconque de la feuille de calcul : ex : = test1()

à partir d' un menu personnalisé
à partir d'un bouton

L'Editeur Visual Basic ou environnement de développement intégré de MS Excel

Quelques objets, méthodes et propriétés à connaitre pour créer une macro-commande
- env. Google Sheet
- env LibreOffice Calc

___________________

_____________________

Introduction aux macro-commandes