Les principaux objets MS Excel

Sources :

https://docs.microsoft.com/fr-fr/dotnet/visual-basic/

https://docs.microsoft.com/fr-fr/visualstudio/vsto/excel-object-model-overview?view=vs-2022

Pour écrire une macro-commande, il est indispensable de savoir comment accéder aux différents éléments composant un classeur. Dans les paragraphes 1 à 6 suivant, ce billet en décrit donc les principaux et quelques méthodes et propriétés qui leurs sont associées.
Il fournit ensuite quelques exemples de code de macro-commande ainsi qu'une façon de les mettre en œuvre.

Billet créé le :

19 jan 2022

1. Le classeur actif : thisWorkbook

La méthode 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. Il 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, elles débutent à 0.

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

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

Quelques propriétés :

gestion du contenu de la cellule :

formula : le contenu est (sera) une formule ;

formulaArray : est à utiliser pour les formules matricielles.

information complémentaire disponible en cliquant ici

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 sélectionne 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 applicables à 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 MS EXCEL.

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 :

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

- 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 et 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()