Macros

    From The Document Foundation Wiki
    < Macros‎ | Calc


    Comment trouver la plage utilisée ?

    Les programmes manipulant les classeurs doivent être écrits pour fonctionner quelle que soit l'étendue de la plage des données saisies par les utilisateurs dans chaque feuille.

    Cet exemple a pour objet de montrer comment procéder pour connaître l'étendue de ces plages.

    copie d'écran classeur
    L'étendue utilisée est B3:F12 dans cette feuille

    Nous utiliserons les méthodes gotoStartOfUsedArea et gotoEndOfUsedArea.

    Ces méthodes ne sont pas directement exposées par l'objet Sheet. Il est nécessaire de passer par un niveau intermédiaire : la création d'un curseur. Un curseur est une manière de "se déplacer" dans un tableau.


    Nota : le "déplacement" est ici virtuel, interne au programme ; la sélection active n'est pas modifiée.


    Ces méthodes attendent un paramètre booléen (True/False) 

    • False : à l'issue du "déplacement" la référence obtenue est une cellule.
    • True  : à l'issue du "déplacement" la référence obtenue est une plage de cellules.


    Ceci est en quelque sorte l'équivalent des déplacements dans le tableau via l'interface. A1 étant la cellule active :

    • cliquer sur la cellule F12 affichera F12 dans la zone de nom (cf. la copie d'écran ci-dessus).
    • cliquer sur la cellule F12 en appuyant sur la touche Maj affichera A1:F12 dans la zone de nom.


    Une fois ces 2 instructions exécutées (gotoStartOfUsedArea et gotoEndOfUsedArea), il nous faut maintenant obtenir les coordonnées. L'objet curseur dispose d'une méthode getRangeAddress qui retourne un objet donnant les propriétés colonnes et lignes de début et de fin.

    sub LibOZoneUtilisee
    dim LibOClasseur as Object, LibOFeuille as Object, LibOCellCursor as object, LibOCellRangeAddress as object
    dim LibODebCol as long, LibODebLig as long, LibOFinCol as long, LibOFinLig as long
    
    LibOClasseur = thisComponent
    LibOFeuille = LibOClasseur.Sheets.getByName("Feuille1")
    LibOCellCursor = LibOFeuille.createCursor()
    
    LibOCellCursor.gotoStartOfUsedArea(False)
    LibOCellCursor.gotoEndOfUsedArea(True)
    
    LibOCellRangeAddress = LibOCellCursor.getRangeAddress()
    
    LibODebCol = LibOCellRangeAddress.StartColumn
    LibODebLig = LibOCellRangeAddress.StartRow
    LibOFinCol = LibOCellRangeAddress.EndColumn
    LibOFinLig = LibOCellRangeAddress.EndRow
    
    msgbox LibODebCol  & ", " & LibODebLig & ", " & LibOFinCol & ", " & LibOFinLig
    
    end sub


    Nota :

    • Les collections sont numérotées à partir de zéro. La première colonne (ligne) étant numérotée zéro, dans l'exemple de la copie d'écran le résultat serait 1, 2, 5, 11.
    • Le gotoStartOfUsedArea est indispensable pour initialiser le "déplacement". Dans cet exemple le résultat serait 0, 0, 5, 11 si on l'omettait.


    Dans l'exemple précédent nous recherchons toute l'étendue utilisée. Il est également possible d'initialiser l'objet curseur avec une cellule de "départ" qu'il suffit de passer (en tant qu'objet, pas sous forme de référence) en paramètre lors de la création du curseur.


    sub LibOZoneUtiliseeDepuis
    dim LibOClasseur as Object, LibOFeuille as Object, LibOCellCursor as object, LibOCellRangeAddress as object
    dim LibODebCol as long, LibODebLig as long, LibOFinCol as long, LibOFinLig as long
    
    LibOClasseur = thisComponent
    LibOFeuille=LibOClasseur.Sheets.getByName("Feuille1")
    LibOCellCursor = LibOFeuille.createCursorByRange(LibOFeuille.getCellRangeByName("F3"))
    
    LibOCellCursor.gotoEndOfUsedArea(True)
    LibOCellRangeAddress = LibOCellCursor.getRangeAddress()
    
    LibODebCol = LibOCellRangeAddress.StartColumn
    LibODebLig = LibOCellRangeAddress.StartRow
    LibOFinCol = LibOCellRangeAddress.EndColumn
    LibOFinLig = LibOCellRangeAddress.EndRow
    
    msgbox LibODebCol  & ", " & LibODebLig & ", " & LibOFinCol & ", " & LibOFinLig
    
    end sub


    Nota : les collections sont numérotées à partir de zéro. La première colonne (ligne) étant numérotée zéro, avec cet exemple (curseur initialisé sur F3) le résultat serait 5, 2, 5, 11.


    Un cas de figure courant peut se présenter où les méthodes présentées ci-dessus ne peuvent convenir : nous avons une colonne calculée par formule et désirons trouver la plage affichant des résultats. Dans l'exemple suivant nous avons ajouté une colonne en G et nous souhaitons sélectionner les cellules affichant le mot "loin", c'est-à-dire G4:G8 :

    copie d'écran classeur
    La plage à trouver est G4:G8


    La méthode gotoEndOfUsedArea ne peut convenir qui trouverait G12 comme dernière cellule utilisée puisque les cellules G4:G12 contiennent une formule : elles ne sont pas vides.

    Une solution est de recourir à la fonction de recherche dans la plage.

    • Le programme recherche tout caractère,
    • avec une recherche sur expressions régulières,
    • en ne recherchant que dans les valeurs (pas les formules ni les commentaires).

    Ainsi, les cellules contenant des formules mais pas de valeurs affichées ne sont pas sélectionnées.

    • Les paramètres de recherche sont définis par un service SearchDescriptor[1]
    • Le choix de l'étendue de la recherche (valeurs, formules, commentaires) se fait via la propriété SearchType (non listée dans l'API) qui peut prendre les valeurs : 0 (formules), 1 (valeurs), 2 (commentaires).
    • Les objets Plage disposent d'une interface XSearchable[2] exposant notamment la méthode findAll.

    sub LibOZoneUtiliseeFormule
    
    dim LibOClasseur as Object, LibOFeuille as Object, LibOPlage as Object
    dim LibOCherche as Object, LibOResult as Object
    
    LibOClasseur = thisComponent
    LibOFeuille = LibOClasseur.Sheets.getByName("Feuille1")
    LibOPlage = LibOFeuille.getCellRangeByName("G4:G12")
    
    LibOCherche = LibOPlage.createSearchDescriptor
    
    ' Recherche:
    ' .tout caractère 
    ' .expression régulière coché
    ' .dans les valeurs (et non les formules et commentaires)
    with LibOCherche
    	.SearchString = "."
    	.SearchRegularExpression = true
    	.SearchType = 1
    end with
    
    LibOResult = LibOPlage.findAll(LibOCherche)
    
    if isnull(LibOResult) then
    	msgbox "rien à sélectionner"
    else
    ' On sélectionne la plage concernée
    	thiscomponent.currentController.select(LibOResult)
    end if							
    
    end sub


    Notes :