Aller au contenu principal


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 :

 

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

Cliquer pour agrandir l'image

 

à 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

___________________

LO Basic - 3 classes à connaitre pour créer une macro-commande

Gestion des chaînes de caractères

_____________________

Introduction aux macro-commandes