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.
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.
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.
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.
- nothing (aucune sélection) ;
- un objet range si une cellule ou une plage de cellules sont sélectionnées ;
- ...
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. Un exemple :
- la copie d'une feuille de calcul
Function feuilleNote()
Dim nomFeuilleSource As String
Dim nomFeuilleCible As String
Dim msg As String
Dim existe As Boolean
nomFeuilleSource = InputBox("Quel est le nom de la feuille Source ?", "Origine de la copie", "Source")
If SheetExist(nomFeuilleSource) 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(nomFeuilleSource).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 la feuille Source n existe pas", 64, "Abandon"
End If
End Function
Public Function SheetExist(Sheetname As String) As Boolean
SheetExist = False
On Error Resume Next
SheetExist = Worksheets(Sheetname).Index 'toute valeur différente de 0 est équivalente à True
On Error GoTo 0 'réinitialisation des erreurs
End Function
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 :
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()
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 :
___________________
LO Basic - 3 classes à connaitre pour créer une macro-commande
Gestion des chaînes de caractères
_____________________