Vba Excel

VBA-Programmer efficacement Excel
Maj 2008
VBA : Programmer efficacement Microsoft Excel

Notions importantes
Les collections
On entend par collection, la runion d'lments connexes permettant d'accder ces lments par leurs
numros d'index (ou parfois par leur nom), mais aussi d'ajouter ou de supprimer des membres.
Le modle objet d'Excel contient ainsi de nombreuses collections hirarchises (on peut dire que
l'application contient une collection de classeurs dont chaque lment contient une collection de feuilles etc)
Une collection contient gnralement au moins trois mthodes et une proprit de base.
Les mthodes Add et Remove (ou Delete) permettent l'ajout / suppression d'lments, la mthode Item
permet d'accder un lment par son index ou son nom, la proprit Count donne le nombre d'lments de la
collection. Attention toutefois, certaines collections sont de base 0, d'autres de base 1. Dans Excel, les collections
sont de Base 1. Cela signifie que le numro d'index 1 est le premier numro d'index utilisable pour pointer sur un
lment travers la collection.
En gnral, la collection a le mme nom que les objets qu'elle contient en y ajoutant un "s" la fin. Par
exemple la collection des objets classeurs WorkBook s'appelle WorkBooks
Quelques remarques encore, les collections cotent relativement cher en mmoire, donc avant d'utiliser un
des membres frquemment, il convient de l'affecter une variable fortement type.
Exemple:
Dim MaPlage as Range
Set MaPlage = ActiveSheet.Cells(1,1)
L'utilisation de "MaPlage" permet un meilleur accs la cellule que l'utilisation d'ActiveSheet.Cells(1) tout
au long du code.
L'appel la mthode Item est implicite,
WorkBooks("NomClasseur").Activate et WorkBooks.Item("NomClasseur").Activate sont quivalents.
L'numration
Cette technique permet de parcourir une collection. Sa syntaxe est For Each.Next
L encore, typez fortement votre variable d'numration.
Dim MaCellule As Range
For Each MaCellule in ActiveSheet.Range("A1:E5")
....
Next
Dans l'exemple ci-dessus, "Dim MaCellule As Range" est mieux que "Dim MaCellule As Object" qui est
mieux que "Dim MaCellule As Variant".
L'Adressage
Excel fonctionne principalement par un systme d'adresse (appel aussi rfrence) pour localiser une cellule
particulire. Le nom complet d'une adresse Excel valide est dans le cas d'une cellule :
'[NomClasseur.xls]NomFeuille'!$PositionCellule. Excel utilise indiffremment deux types de rfrences
appeles A1 o les colonnes sont des lettres et les lignes des nombres, et L1C1 o lignes et colonnes sont des
nombres.
L'exemple ci-dessous donne deux adresses Excel compltes dans les deux styles de rfrences :
'[NomClasseur.xls]NomFeuille'!$A$35 ou '[NomClasseur.xls]NomFeuille'!L1C35
Ces rfrences peuvent tre relatives ou absolues. Une rfrence absolue donne la position d'une cellule
dans la feuille par l'intersection de la ligne et de la colonne, une rfrence relative donne l'adresse de la cellule
par dcalage de l'adresse par rapport une autre cellule. Une rfrence peut tre relative dans une direction (par
Page 1/44
Maj 2008
exemple les colonnes) et absolue dans l'autre. Dans le tableau suivant, nous voyons l'criture de l'adresse "B2"
dans la cellule "A1" dans tous les modes
Rfrences
A1
L1C1
Ligne
Colonne
Absolue
Absolue
=$B$2
L2C2
Absolue
Relative
=B$2
L2C(1)
Relative
Absolue
=$B2
L(1)C2
Relative
Relative
=B2
L(1)C(2)
A l'identique en VBA on peut donner l'adresse d'un objet appartenant au modle objet Excel par :
Application.Workbooks("NomClasseur").Sheets("NomFeuille").Objet
Dans la pratique on ne met jamais Application dans l'adresse puisqu'il est clairement implicite (sauf dans le
cas d'un classeur partag dans un programme utilisant plusieurs instances d'Excel).
ThisWorkBook
L'objet ThisWorkBook reprsente le classeur qui contient la macro qui est en train de s'excuter Ceci sousentend,
qu'il n'est jamais ncessaire de mettre dans une variable le classeur contenant la macro pour y faire
rfrence.
Objet actif
La notion de l'objet actif permet d'accder des raccourcis dans l'adressage VBA. Cette notion, trs
employe par l'enregistreur de macro est certes performante, mais non dnue de risque. A chaque instant, de
l'application, on peut nommer le classeur actif (visible) ActiveWorkbook, la feuille visible de ce classeur
ActiveSheet et la cellule slectionne ActiveCell. (il y en a d'autres mais voil les principaux)
Ceci permet bien des raccourcis d'criture de code mais demande deux choses :
Bien savoir quel est l'objet actif

Manipuler l'activation
Or comme nous allons le voir au cours de cet article, manipuler l'activation est une mthode lourde et assez
lente.
Slection
Cet objet reprsente ce qui est slectionn. Cette simple dfinition devrait suffire pour se mfier de son
utilisation. En effet, on l'utilise en gnral quand on ne sait pas quel est le type de l'objet que l'on cherche
atteindre. A mon avis son utilisation est proscrire. L encore, on le trouve trs souvent dans les macros
enregistres, je vais donc vous donner une petite astuce pour connatre le type d'un objet. En gnral, tout objet
insr par le code, l'est par une mthode Add. On ajoute donc une variable de type variant o l'on affecte l'objet
cre. La fonction TypeName permet alors de connatre le type de l'objet. Ceci peut se faire aussi avec l'espion
express du dbogueur.
Paramtres nomms
Excel accepte une syntaxe particulire pour ses mthodes, celle des paramtres (ou arguments) nomms. En
Visual Basic l'appel d'une procdure paramtre se fait en plaant les paramtres dans le mme ordre que celui
qui se trouve dans la dclaration de la procdure. Cette mthode reste vraie en VBA mais il y a aussi la
possibilit de ne passer que quelques-uns de ces paramtres en les nommant. Regardons par exemple la mthode
Page 2/44
Maj 2008
Find d'Excel.
Sa dclaration est :
expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection,
MatchCase, MatchByte)
Son utilisation normale serait
Set CelluleCible=ActiveSheet.Cells.Find(2,ActiveCell, xlValues, xlWhole,
xlByColumns, xlNext, False)
Mais je pourrais trs bien simplifier en crivant
Set CelluleCible=ActiveSheet.Cells.Find(What:=2, LookIn:= xlValues)
Ceci permet une plus grande clart du code ainsi qu'une simplification la condition expresse de bien
connatre la valeur par dfaut des paramtres.
Les vnements
La gestion des vnements dans Excel se fait via du code soit dans un module d'objet WorkSheet, soit dans
le module de l'objet WorkBook. Pour atteindre le module de code du classeur, on ouvre la fentre VBA et dans
l'explorateur de projet on double click sur "ThisWorkBook". Pour atteindre le module d'une feuille on peut soit
passer par l'explorateur de projet, soit faire un click droit sur l'onglet de la feuille et choisir "Visualiser le code".
Intercepter les vnements Excel en Visual Basic

Dans la feuille VB o l'on souhaite rcuprer l'vnement on dclare une variable globale
Private WithEvents MaFeuille As Excel.Worksheet
Aprs il suffit d'crire une procdure d'vnements identique la procdure Excel correspondante.
Par exemple, l'vnement de feuille SelectionChange s'crit dans Excel
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Et dans Visual Basic :
Private Sub MaFeuille _SelectionChange(ByVal Target As Excel.Range)
L'exemple suivant montre l'interception de l'vnement BeforeClose d'un classeur dans Visual Basic afin
d'empcher la fermeture de celui-ci (et donc de l'application Excel) par l'utilisateur. Pour qu'Excel puisse se
fermer dans cet exemple, il faut remplir la case "A1", ce qui est gr par l'vnement
MonClasseur_SheetChange.
Private WithEvents MonClasseur As Excel.Workbook
Private MonExcel As Excel.Application, MaFeuille As Excel.Worksheet
Private Sub OuvreExcel()
Set MonExcel = New Excel.Application
MonExcel.ReferenceStyle = xlR1C1
Set MonClasseur =
MonExcel.Workbooks.Open("D:\User\jmarc\tutorial\excel\tutor1.xls")
Set MaFeuille = MonClasseur.Worksheets("pilotage")
MonExcel.Visible = True
End Sub
Private Sub MonClasseur_BeforeClose(Cancel As Boolean)
Cancel = True
End Sub
Private Sub MonClasseur_SheetChange(ByVal Sh As Object, ByVal Target As
Excel.Range)
If Not MonExcel.Intersect(Target, MaFeuille.Cells(1, 1)) Is Nothing
Then
Page 3/44
Maj 2008
MonExcel.EnableEvents = False
MonClasseur.Close False
Set MaFeuille = Nothing
Set MonClasseur = Nothing
MonExcel.Quit
Set MonExcel = Nothing
End If
End Sub
La base, l'objet Application

Cet objet reprsente l'application Excel. Il est l'objet parent de tous les autres, et contient des proprits
mthodes et vnements trs intressants, que nous allons regarder maintenant.
vnements
On utilise rarement les vnements au niveau de l'application, pour la raison simple qu'ils ne sont pas
accessibles directement. Pour pouvoir les utiliser, il faut dans le projet crer un module de classe dans lequel on
met le code suivant :
Public WithEvents App As Application
Comme la plupart des vnements grs se retrouvent dans les objets classeur et feuille, nous les utiliseront
plutt ce niveau.
Proprits
Je vais donner maintenant quelques proprits utiles de l'objet application
AskToUpdateLinks : Si sa valeur est False la mise jour des liaisons se fait sans appel d'une boite de
dialogue, qui sinon apparat automatiquement.
Calculation (xlCalculationAutomatic, xlCalculationManual, xlCalculationSemiautomatic): Dfinit le mode
de recalcul d'Excel. Il s'agit d'une proprit trs importante pour l'optimisation du temps d'excution. En effet,
pour les feuilles contenant des formules de calcul, le mode de calcul automatique peut tre trs coteux en
temps, en effet Excel recalcule intgralement une feuille de calcul chaque modification ou suppression. C'est
pourquoi, en gnral on bloque le mode de calcul au dpart en faisant :
Application.Calculation = xlCalculateManual
Puis on excute les calculs lorsqu'on en a besoin avec la mthode Calculate
CutCopyMode (False,xlCopy,xlCut) : Dfinit si on utilise un mode par dfaut copier ou couper. En fait, on
l'utilise souvent sous la forme Application.CutCopyMode=False pour vider le presse papier.
DisplayAlerts : Si sa valeur est False, les messages d'avertissements d'Excel ne seront pas affichs.
Il faut toujours remettre la valeur True en fin d'excution
Interactive : Dsactive toutes les interactions entre Excel et le clavier/souris.
Il faut toujours remettre la valeur True en fin d'excution
International : Renvoie des informations relatives aux paramtres rgionaux et internationaux en cours.
Cette proprit est en lecture seule. On s'en sert gnralement pour connatre les sparateurs.
ReferenceStyle (xlA1 ou xlR1C1): Permet de basculer entre les modes L1C1 et A1. Il est noter que cela
change l'affichage des feuilles mais que cela peut aussi avoir une influence sur le code. En effet certaines
formules comme les rgles de validations doivent tre crites dans le mme systme de rfrence que celui de
l'application.
ScreenUpdating : Permet de dsactiver la mise jour d'cran. Pour des raisons de vitesse d'excution il est
conseill de toujours la dsactiver.
Il faut toujours remettre la valeur True en fin d'excution, sous peine de rcuprer un cran fig.
Page 4/44
Maj 2008
SheetsInNewWorkbook : Permet de dfinir le nombre de feuilles contenues dans un nouveau classeur.

Lorsque l'on sait exactement le nombre de feuilles ncessaires, on peut modifier cette proprit afin de ne pas
faire des worksheets.add. Il convient de restaurer cette valeur en fin d'excution.
Mthodes
Calculate : Permet de forcer le calcul. La syntaxe Application.Calculate est peu utilise. On l'utilise
principalement sous la forme MaFeuille.Calculate. Sachez toutefois que l'on peut restreindre le calcul une
plage des fins de performance. Exemple :
Worksheets(1).Rows(2:3).Calculate ne fait les calculs que sur les lignes 2 et 3.
Evaluate : Permet de convertir une chane en sa valeur ou en l'objet auquel elle fait rfrence. Nous allons
regarder quelques utilisations de cette mthode.
Interprtation de formule de calcul Imaginons que ma cellule A1 contient le texte (12*3)+4, crire
Range("A2").Value=Application.Evaluate(Range("A1").Value)
Renverra 40 en A2. De mme on pourra crire :
Resultat= Application.Evaluate("(12*3)+4")
La mthode permet aussi d'valuer une formule respectant la syntaxe Excel (en anglais) ; on peut crire
Resultat= Application.Evaluate("Sum(A1:E5)")
Interprtation d'une adresse Si ma cellule A1 contient B1:B2 je peux crire
Application.Evaluate(Range("A1").Value).Font.Bold=True
Il est noter que le mot Application est facultatif et on trouve parfois la notation
[A1].Font.Bold=True
Qui est strictement quivalente !
FindFile Permet de lancer une recherche de fichier dans laquelle on spcifie les critres. La collection
FoundFiles contient les fichiers trouvs correspondant.
(cf l'exemple plus loin)GetOpenFileName : Ouvre la boite de dialogue "Ouvrir un fichier" mais n'ouvre
pas le fichier. La mthode renvoie juste le nom complet du fichier slectionn.
GetSaveAsFilename : De mme que prcdemment mais avec la boite "Enregistrer Sous"
Goto : Je cite cette mthode pour vous mettre en garde. L'enregistreur de macro l'utilise lors de l'appel d'une
plage nomme, elle sous-tend un "Activate" et un "Select". Il faut donc faire trs attention lors de son utilisation
dans le code car elle peut facilement changer les objets actifs.
Intersect : Renvoie une plage qui est l'intersection de n plages. Bien que les plages appartiennent des
objets feuilles, la mthode Intersect appartient directement l'objet Application.
OnKey : Permet l'excution d'une macro lors de l'appui sur une touche ou sur une combinaison de touches.
Application.OnKey "^{A}", "MaProc" lance la procdure MaProc sur l'appui de CTRL-A
Pour restaurer la squence de touche on crit :
Application.OnKey "^{A}"
OnTime : Permet l'excution d'une macro une heure fixe ou aprs un dlai prcis.
Application.OnTime Now + TimeValue("00:00:15"), "MaProc" attend 15 secondes avant d'excuter la
procdure MaProc
Run : Lance l'excution de la procdure spcifie. Si celle-ci attend des paramtres, ils ne peuvent pas tre
nomms.
Par exemple imaginons la fonction suivante :
Private Function MaSomme(Oper1 as Double, Oper2 as Double) As Double.
La syntaxe d'appel sera :
Resultat=Application.Run(MonClasseur!MaSomme,1.2,2.3)
Union : Renvoie l'union de n plages. Mme remarque que pour Intersect.
Page 5/44
Maj 2008
Wait : Marque une pause dans l'excution de la macro.
Collections et objets particuliers

Nous allons regarder trois collections (objets) intressantes accessibles depuis l'objet Application.
FileSearch
L'objet FileSearch permet une recherche standard de fichiers. On peut spcifier ses critres de recherche en
valorisant ses proprits. L'appel de la mthode Execute lance la recherche. Le rsultat se trouve alors dans la
collection FoundFiles.
Par exemple pour rechercher l'ensemble des fichiers Res*.xls dans le rpertoire courant, et les ouvrir.
Public Sub OuvreFichier()
Dim ChercheFichier As FileSearch, compteur As Long
Set ChercheFichier = Application.FileSearch
With ChercheFichier
.LookIn = CurDir
.FileName = "Res*"
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = True
.Execute
For compteur = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(compteur)
Next
End With
End Sub
Dialogs
La collection Dialogs contient l'ensemble des boites de dialogues utilises par Excel. Comme il y en a
beaucoup (646 dans Excel 97) je ne vais pas les numrer, vous trouverez cette liste soit dans l'explorateur
d'objets, soit dans l'aide "Listes d'arguments de bote de dialogue intgre", soit dans le fichier ListeVBA.xls
que vous retrouverez dans votre dossier Office (VBAlist.xls depuis Excel 2000). Ces boites agissent le plus
souvent sur la slection en cours donc nous sommes dans un des rares cas o l'emploi de la mthode Select sera
obligatoire.
Voici par exemple l'appel de la boite de dialogue "alignement" pour permettre l'utilisateur de modifier
l'alignement sur la plage B1:B10
Public Sub UtilDial()
Range(Cells(1, 2), Cells(10, 2)).Select
Application.Dialogs(xlDialogAlignment).Show
End Sub
On pourrait de la mme faon donner des valeurs par dfaut cette boite en lui passant des arguments
Par exemple :
Application.Dialogs(xlDialogAlignment).Show 3,False,2
Ouvre la mme boite mais en dfinissant les alignements verticaux et horizontaux sur "centr".
WorksheetFunction
Cet objet contient les fonctions de feuille de calcul intgres dans Excel. Attention ces fonctions sont en
anglais. Il faut faire trs attention aux types des paramtres passs, en effet certaines fonctions acceptent
indiffremment des plages ou des nombres alors que d'autres n'acceptent que des plages. L'exemple suivant
montre le calcul d'une moyenne mlant chiffres et plages
Dim resultat As Double
resultat = Application.WorksheetFunction.Average(Range(Cells(1, 2),
Cells(10, 2)), 100, 200)
L encore je ne donnerai pas la liste complte des fonctions que vous pourrez trouver dans l'aide, dans le
Page 6/44
Maj 2008
fichier listeVBA.xls ou avec l'explorateur d'objets.
Rsum
Plus loin dans cet article nous trouverons des exemples d'utilisation des proprits/mthodes de l'objet
Application, mais il faut bien garder l'esprit qu'une procdure Excel devrait toujours contrler le mode de
calcul et dsactiver la mise jour de l'cran.
L'objet WorkBook (classeur)

Le classeur est en gnral l'objet central de la programmation Excel. Lorsque l'application va utiliser un seul
classeur, on peut utiliser ActiveWorkbook, mais dans le cas d'une application multi-classeurs, il convient de les
mettre dans des variables afin d'en simplifier la manipulation. Pour cela, on fait :
En VBA
Dim MonClasseur as WorkBook
Set MonClasseur=ActiveWorkBook
En VB
Dim MonClasseur as Excel.WorkBook
Set MonClasseur=ActiveWorkBook
On peut bien sr faire l'affectation dans le mme temps que l'ouverture ou l'ajout, par exemple
Set MonClasseur=WorkBooks.Open Filename:="C:\User\Classeur1.xls"
N.B. (en VBA uniquement) : Si on utilise deux classeurs dont celui qui contient la macro, il n'est pas utile de
mettre celui-ci dans une variable puisqu'on le retrouve sous le nom "ThisWorkbook".
La collection WorkBooks
Cette collection contient l'ensemble des classeurs ouverts. L'ordre des classeurs dans la collection (index)
est l'ordre d'ouverture. Elle possde quatre mthodes que nous allons tudier
Add
Permet d'ajouter un nouveau classeur la collection et non pas d'ouvrir un classeur existant. Le classeur
cr devient le classeur actif.
Elle suit la syntaxe Worbooks.Add(Template)
Si Template est un fichier Excel existant, le classeur est cr en suivant le modle dfini. Template peut
aussi tre une constante (xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet,
xlWBATWorksheet) et dans ce cas, le classeur ne contiendra qu'une feuille du type donn. Enfin si Template est
omis un nouveau classeur standard est cr.
Close
Cette mthode ferme tous les classeurs de la collection. Donc attention de ne pas confondre
WorkBooks.Close et WorkBooks(1).Close
Open
Ouvre un classeur Excel. Sa syntaxe est :
WorkBooks Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword,
IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMRU)
Seul FileName est obligatoire.
OpenText
Permet d'ouvrir un fichier texte dlimit comme un classeur. Sa syntaxe est
WorkBooks. .OpenText(Filename, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab,
Semicolon, Comma, Space, Other, OtherChar, FieldInfo)
Quelques mthodes connatre
Page 7/44
Close
Ferme le classeur. La syntaxe est :
MonClasseur.Close(SaveChanges, FileName, RouteWorkbook)
Si SaveChanges est omis, une boite de demande d'enregistrement apparat. Donner une valeur FileName
diffrentes de celle du classeur revient faire un SaveAs.
PrintOut
Lance l'impression du classeur. Sa syntaxe est :
MonClasseur.PrintOut(from, To, Copies, Preview, ActivePrinter, PrintToFile, Collate)
A noter que From et To sont des pages d'imprimante et non les feuilles du classeur, et que ActivePrinter
permet de choisir l'imprimante
Protect / UnProtect
Active ou dsactive la protection du classeur. La syntaxe est :
MonClasseur.Protect(Password, Structure, Windows) pour activer la protection
MonClasseur.UnProtect(Password) pour la retirer.
A ce propos, la protection du classeur et des feuilles est une chose indpendante. La protection du classeur
sert bloquer la structure de celui-ci, celle de la feuille protger les objets qu'elle contient. On peut
parfaitement protger une feuille sans protger le classeur et inversement.
Save / SaveAs
Sauvegarde le classeur. La syntaxe de SaveAs est :
MonClasseur.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended,
CreateBackup, AddToMru, TextCodePage, TextVisualLayout)
Evnements du classeur
L'objet classeur possde beaucoup d'vnements, je ne vais donc dtailler que ceux les plus souvent utiliss.
BeforeClose, BeforePrint, BeforeSave

Private Sub Workbook_Before----(Cancel As Boolean)
Se produit avant l'vnement. Dans le cas de BeforeClose se produit avant la demande d'enregistrement des
modifications. Mettre Cancel = True dans la procdure empche l'vnement d'avoir lieu.
Open
Se produit l'ouverture du classeur. Permet donc de faire des macros excution automatique
SheetBeforeDoubleClick, SheetBeforeRightClick
Private Sub MonClasseur_SheetBefore----Click(ByVal Sh As Object, ByVal Target As Range, ByVal Cancel
As Boolean)
Ces vnements sont des vnements de feuille. On utilise l'vnement au niveau classeur lorsquon veut
centraliser l'vnement pour plusieurs feuilles du classeur. S'il existe aussi une procdure pour l'vnement au
niveau feuille, elle s'excutera avant la procdure du classeur.
Cet vnement ne se produit pas sur les feuilles graphiques.
L'objet "Sh" est un objet WorkSheet qui reprsente la feuille sur laquelle l'vnement se produit. Nous
regarderons l'argument Target dans "les techniques de programmation".
SheetCalculate, SheetChange, SheetSelectionChange

Les mmes remarques sont valables. Nous tudierons en dtail ces vnements dans le chapitre sur les
feuilles.
Les Feuilles (Sheets)

Un classeur Excel est compos d'une ou plusieurs feuilles. Ces feuilles peuvent tre de cinq types :
Feuille de calcul, Feuille graphique, Macro Excel4, Macro Excel4 International, feuilles de botes de
dialogue Excel 5.
La collection Sheets contient toutes les feuilles du classeur quel que soit leur type. En fait, on utilise trs
Page 8/44
Maj 2008
Maj 2008
peu cette collection sauf pour connatre le nombre total de feuille avec la proprit Count ou parfois pour ajouter
une feuille de type macro Excel4.
Feuille de calcul (WorkSheet)

L'objet WorkSheet doit suivre les mmes rgles de programmation que l'objet classeur. On utilise
ActiveSheet que dans le cas d'une application ne mettant en jeu qu'une seule feuille. Sinon on rfrence la feuille
par :
En VBA
Dim MaFeuille as WorkSheet
Set MaFeuille=ActiveWorkSheet
En VB
Dim MaFeuille as Excel.WorkSheet
Set MaFeuille=ActiveWorkSheet
Observons qu'il est rarement ncessaire de rfrencer la fois la feuille et le classeur.
La collection Worksheets
Cette collection contient l'ensemble des feuilles de calcul du classeur. Le numro d'ordre d'un objet
WorkSheet dans cette collection est son ordre dans les onglets du classeur.
Mthodes utiles
Add WorkSheet.Add(Before, After, Count, Type)
Before et/ou After permettent de prciser la position o la feuille doit tre ajoute. L'argument Count
dtermine le nombre de feuilles ajouter. Type revient faire un Add de la collection Sheets.
Comme je l'ai signal au pralable, la position de l'objet WorkSheet dans l'ensemble des Worksheets du
classeur va aussi tre son index dans la collection WorkSheets. C'est pourquoi accder une feuille par
WokSheets(index) peut tre une source d'erreur.
Copy Worksheets(index).Copy(Before, After)
Duplique une feuille dans le classeur ou cre une copie dans un autre classeur.
Attention lors de la copie d'une feuille, la collection "Names" des plages nommes appartient l'objet
WorkBook et l'objet WorkSheet. Lors de la duplication de la feuille, Excel va crer deux noms presque
identiques, ce qui peut engendrer des bugs.
Delete WorkSheets(index).Delete
Supprime la feuille spcifie par index. Un classeur doit toujours contenir au moins une feuille.
FillAcrossSheets WorkSheets(collection). FillAcrossSheets (Range, Type)
Permet de recopier une plage sur plusieurs feuilles en mme temps. Range dtermine la plage copier, Type
dfinit le mode de copie (xlFillWithAll, xlFillWithContents ou xlFillWithFormulas)
Il faut passer une collection ou un tableau d'objets WorkSheet contenant les feuilles concernes par la
recopie la mthode. Cette collection doit toujours contenir la feuille contenant la plage source.
Le code suivant recopie la plage A1:A10 sur toutes les feuilles de calcul du classeur
Dim MaFeuille As Worksheet
Set MaFeuille = ActiveWorkbook.Worksheets(1)
Worksheets.FillAcrossSheets MaFeuille.Range(Cells(1, 1), Cells(10, 1)),
xlFillWithAll
Le code suivant recopie la mme plage dans la feuille "Feuil3"
Dim MaFeuille As Worksheet, TabFeuille As Variant
Set MaFeuille = ActiveWorkbook.Worksheets(1)
TabFeuille = Array("Feuil1", "Feuil3")
Worksheets(TabFeuille).FillAcrossSheets MaFeuille.Range(Cells(1, 1),
Cells(10, 1)), xlFillWithAll
Move Worksheets(index).Move(Before, After)
Similaire copy mais dplace la feuille. Dans ce cas il n'y a pas de problme avec les noms.
Page 9/44
Maj 2008
Evnements de l'objet WorkSheet

BeforeDoubleClick, BeforeRightClick
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Se produit avant que l'effet du double click ou du click droit se produise. Mettre Cancel True annule
l'vnement normalement attendu. Target renvoie un objet Range qui reprsente la cellule ayant reu
l'vnement.
Calculate
Se produit aprs le recalcul de la feuille.
Change
Private Sub Worksheet_Change(ByVal Target As Range)
Se produit lorsque le contenu d'une cellule ou d'une plage change, du fait de l'utilisateur ou d'un lien
externe, ou du programme, mais pas par le recalcul.
Target renvoie la plage ou la cellule modifie. Pour la gestion de ces plages voir plus loin au chapitre
"techniques de programmation".
SelectionChange
Se produit lorsque la slection a chang. Target renvoie la nouvelle plage slectionne.
Attention la programmation de cet vnement. La prsence de mthode Select dans la procdure peut
dclencher un vnement en cascade.
Quelques proprits
Names
Renvoie la collection des noms spcifique la feuille de calcul. Attention les plages nommes appartiennent
l'objet WorkBook. La collection Names renvoye par cette proprit ne contient donc pas les plages nommes
contenues par la feuille sauf si l'on a dfini ce nom comme spcifique.
N.B : Pour votre culture gnrale, lors de la dfinition d'un nom, si on crit NomFeuille!Nom on cre une
plage nomme spcifique.
PageSetup
Renvoie un objet PageSetup qui contient toutes les informations de mise en page de la feuille.
Shapes
Renvoie la collection Shapes de toutes les formes prsentes sur la feuille. Cette collection peut contenir
beaucoup d'objets de types diffrents. Je n'aborderai pas dans cet article la programmation avec Shapes.
Visible
Affiche ou masque la feuille. La proprit peut prendre la valeur xlVeryHidden qui masque la feuille de
telle faon qu'elle ne puisse tre rendue visible que par le code ou par l'explorateur de projet.
Plage et cellule (Range)

Nous allons aborder ici la cl de la programmation. Une plage de cellules (objet Range) reprsente
n'importe quel groupe de cellules d'une feuille. La plage peut tre constitue d'une cellule, d'une plage continue
ou de plusieurs plages. L'objet Range, lorsqu'il reprsente des cellules, appartient toujours un objet WorkSheet.
L'objet Range ne possde pas d'vnement spcifique, nanmoins on peut lui faire utiliser les vnements de
feuille ou de classeur.
Excel fonctionne avec un systme d'adresse. Il existe en fait deux styles de rfrence :
R1C1 Les colonnes et les lignes sont reprsentes par des nombres
A1 Les colonnes sont des lettres et les lignes des nombres.
Le changement de style au cours d'un programme ne pose pas de problme vis--vis des formules dj
existantes dans une feuille. Sachez toutefois que le code doit tre rdig dans le mme style de rfrence que
l'application, sous peine de voir parfois se dclencher des erreurs.
Page 10/44
En mode A1 on utilise un range comme suit :

MaFeuille.Range("A1") : une cellule
MaFeuille.Range("A1:E10") ou Range("A1", "E10") : une plage continue
En mode R1C1
MaFeuille.Cells(1,1) : une cellule
MaFeuille.Range(Cells(1,1),Cells(10,5)) : une plage continue
Quel que soit le style, les plages discontinues s'obtiennent l'aide de la mthode Union que nous verrons
plus en dtail dans la deuxime partie.
En gnral j'utilise la syntaxe R1C1. La notation est Cells(Ligne, Colonne)
Je vais faire maintenant un parcours de ses proprits et mthodes.
Proprits ne renvoyant pas un objet - collection

Address
MonRange.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)
RowAbsolute et ColumnAbsolute dtermine si la rfrence renvoye est relative ou absolue, ReferenceStyle
dtermine le style de rfrence, External ajoute le nom du classeur et le nom de la feuille.
Comme nous l'avons vu, peu importe le style utilis puisque la proprit Address renvoie la rfrence dans
le style dsir.
Column, row
Renvoie le numro de la premire colonne / ligne de la plage
ColumnWidth, RowHeight
Renvoie ou dfinit la largeur / hauteur des colonnes / lignes de la plage.
Formula / FormulaR1C1
Dfinit ou renvoie la formule de la plage, cette formule tant en anglais.
FormulaLocal
Identique Formula mais dans la langue de l'utilisateur.
Hidden
Masque une ou plusieurs ligne(s)/colonne(s). Attention l'ensemble de la ligne ou de la colonne doit tre
slectionne (voir plus loin "plage particulire").
HorizontalAlignment
Dfinit l'alignement dans la plage. Peut permettre un centrage multi-colonnes si la plage contient plusieurs
colonnes.
Locked
Renvoie ou dfinit si les cellules sont verrouilles. Le verrouillage n'a aucun effet tant que la feuille n'est
pas protge.
MergeCells
Renvoie Vrai si la cellule fait partie d'une plage fusionne. A ce propos, vitez si possible de fusionner les
cellules. C'est trs souvent une source de problmes.
Name
Renvoie ou dfinit le nom de la plage. Comme nous le verrons dans les techniques de programmation, les
plages nommes sont trs utiles.
NumberFormat
Renvoie ou dfinit le format des cellules de la plage. Lors de la lecture, renvoie NULL si tous les formats ne
sont pas identiques.
ShrinkToFit
Force le contenu s'adapter aux dimensions de la cellule.
Page 11/44
Maj 2008
WrapText
Force le retour la ligne si le contenu dpasse la largeur de la cellule.
Proprits renvoyant un objet

Areas
Renvoie la collection Areas de l'objet Range. Si la plage est une plage continue, la collection Areas ne
contient qu'un lment qui est l'objet Range. Si cette plage contient plusieurs plages discontinues, il y a autant
d'lments que de plages continues dans l'objet Range.
Par Exemple
Dim MaPlage As Range, NbPlage As Integer, AdrTest As String
Set MaPlage = Union(Range(Cells(1, 1), Cells(5, 1)), Range(Cells(1, 3),
Cells(5, 3)), Range(Cells(1, 5), Cells(5, 5)))
NbPlage = MaPlage.Areas.Count
AdrTest = MaPlage.Areas(2).Address(True, True, xlA1)
Dans ce cas NbPlage renvoie 3 et AdrTest = "$C$1:$C$5"
Borders
Renvoie une collection des objets Border d'une cellule ou d'une plage.
On peut manipuler tous ces objets en mme temps avec un appel Borders ou en spcifier un avec sa
proprit Item.
L'exemple ci-dessous cre un encadrement lger intrieur, avec un contour plus pais :
With Range(Cells(1, 1), Cells(5, 1)).Borders
.LineStyle = xlContinuous
.Item(xlEdgeBottom).Weight = xlMedium
.Item(xlEdgeLeft).Weight = xlMedium
.Item(xlEdgeTop).Weight = xlMedium
.Item(xlEdgeRight).Weight = xlMedium
End With
Cells
Cette proprit renvoie un objet Range (une cellule) avec des coordonnes relatives la premire cellule de
l'objet Range. Il faut faire trs attention cette erreur relativement frquente.
MaFeuille.Cells(3,3) reprsente la cellule "C3", par contre si mon objet range est B1:B3 alors
MaRange.Cells(3,3) reprsente la cellule "D3", c'est dire celle qui est en position (3,3) par rapport la
cellule "B1".
Characters
Renvoie un objet Characters sur le contenu d'une cellule ou sur les objets Shapes (Ne renvoie rien si la
plage contient plusieurs cellules).
Un objet Characters renvoie tout ou partie (comme un Mid) du contenu d'une cellule, condition que cela
soit du texte.
La syntaxe est MaCellule.Characters(Start,Length).
Ainsi le code suivant passe les caractres 2 et 3 de la chane contenue dans la cellule en police "Symbol"
Cells(1, 7).Characters(2, 2).Font.Name = "Symbol"
Il est noter que la modification de la police est la seule utilisation que j'ai rencontr de l'objet Characters
dans une cellule.
Columns / Rows
Renvoie la collection de toutes les colonnes / lignes contenues dans la plage. Cela peut permettre certains
raccourcis de programmation intressant. Par exemple :
MaPlage.Columns(2).Value=""
Efface le contenu de toutes les cellules de la colonne 2 dans la plage.
Page 12/44
Maj 2008
Maj 2008
CurrentArray
Cette proprit est un peu particulire. Si la plage fait partie d'une formule matricielle, CurrentArray renvoie
une plage contenant toutes les cellules de cette formule matricielle.
CurrentRegion
Renvoie la plage en cours dans laquelle est l'objet Range. On entend par plage en cours, l'ensemble des
cellules limites par une combinaison de lignes et de colonnes vides.
Habituellement, on utilise cette proprit avec une cellule. Regardons l'exemple suivant :
Les commandes suivantes renvoient respectivement

ActiveSheet.Cells(2, 2).CurrentRegion.Address(True, True, xlR1C1)
R1C1:R5C5
R1C9:R5C11
R4C7
R1C1:R5C6
Les deux premiers cas sont faciles comprendre.
Pour le troisime comme la cellule (4,7 ou "G4") est entoure de cellules vides, CurrentRegion renvoie la
cellule.
Le dernier cas est le plus intressant. La cellule est vide mais contigu une plage non vide. Dans ce cas
CurrentRegion renvoie un objet Range rectangulaire contenant la cellule cible et la plage contigu. Cette
contigut peut tre diagonale.
Dependents / DirectDependents/ Precedents / DirectPrecedents

Renvoie un objet Range contenant les cellules dpendantes ou antcdentes. Une cellule antcdente est une
cellule qui contient dans sa formule de calcul la rfrence la cellule cible. Une cellule dpendante est une
cellule dont la rfrence est contenue dans la formule de calcul de la cellule cible. L'objet Range renvoy peut
tre une plage multiple. Cette proprit peut tre extrmement intressante pour dterminer quelle plage doit tre
recalcule. Nous verrons des utilisations dans la deuxime partie, mais je vais vous donner un exemple.
regardons le code suivant :

Dim Recup As Range, raTest As Range
Set Recup = ActiveSheet.Cells(10, 7).Precedents
For Each raTest In Recup.Areas
Page 13/44
Debug.Print raTest.Address(True, True, xlR1C1)

Next
Le rsultat dans la fentre dexcution sera
R9C6
R11C6
R8C5:R11C5
Si j'avais utilis DirectPrecedents, seuls les deux premiers rsultats aurait t renvoys.
S'il n'y a pas de cellules correspondantes, la proprit dclenche une erreur rcuprable.
End
Cette proprit renvoie un objet Range dcal dans le sens spcifi l'intrieur de la rgion. Toujours dans
mon exemple prcdent, ActiveSheet.Cells(2, 2).End(xlDown).Address(True, True, xlR1C1) renvoie R5C2 qui
est la dernire cellule remplie vers le bas de la colonne 2.
Si la cellule slectionne est vide, est que la colonne est vide, la cellule renvoye est la dernire cellule de la
feuille, c'est dire :
ActiveSheet.Cells(2, 6).End(xlDown).Address(True, True, xlR1C1) renvoie R65536C6.
Il y a un pige avec cette proprit. Supposons que la colonne 12 contienne une valeur dans la ligne 1 et
que toutes les autres cellules soient vides, alors
ActiveSheet.Cells(1, 12).End(xlDown).Address(True, True, xlR1C1) renverra R65536C12
Font
Renvoie ou dfinit un objet Font pour la plage. Cet objet permet de modifier la police, ainsi que ses
proprits. Attention toutefois, la police renvoye lors de la lecture d'une plage affectera NULL aux proprits
qui ne sont pas les mmes sur toute la plage. Supposons que la cellule (2,2) soit en gras. Si je fais :
Dim MaPlage As Range, LaPolice as Font
Set MaPlage = Range(Cells(1, 2), Cells(3, 2))
Set LaPolice = MaPlage.Font
Dans ce cas LaPolice.Bold sera NULL puisque toutes les cellules ne sont pas en gras.
Interior
Renvoie ou dfinit un objet Interior pour la plage. Cet objet reprsente le motif et la couleur de l'intrieur de
la cellule. Comme pour l'objet Font, il renvoie NULL pour les proprits qui ne sont pas toutes identiques dans
la plage.
OffSet
Renvoie un objet range dcal par rapport l'objet sur lequel on fait l'offset. Sa syntaxe est :
MaPlage.OffSet( Offset de ligne, OffSet de Colonne).
Par Exemple :
Range(Cells(1,1),Cells(5,1)).OffSet(2,2).Adress(True,True,xlR1C1) renvoie
R3C3:R7C3
On peut omettre un des paramtres s'il n'y a pas de dcalage, mais pour la lisibilit du code, il convient de
mettre 0.
Resize
Renvoie un objet range redimensionn par rapport la plage d'origine.
La syntaxe est MaPlage.Resize(NbLigne,NbColonne)
Comme on redimensionne la plage, c'est la cellule en haut gauche de la plage d'origine qui sert de base.
Pour ne pas redimensionner dans une des dimensions, on omet le paramtre. Par exemple
Range(Cells(1,1),Cells(5,1)).Resize(2,2).Adress(True,True,xlR1C1) renvoie
R1C1:R2C2
Range(Cells(1,1),Cells(5,1)).Resize(,2).Adress(True,True,xlR1C1) renvoie
R1C1:R5C2
La combinaison d'Offset et de Resize permet de dfinir n'importe quelle plage.
Quelques mthodes
Nous allons maintenant regarder quelques-unes des mthodes les plus utiles de l'objet Range.
Page 14/44
Maj 2008
Maj 2008
AutoFill
Syntaxe MaPlage.AutoFill(Destination, Type)
Destination est un objet Range qui contient forcment MaPlage
Type peut tre xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues, etc
La mthode excute une recopie incrmente dans les cellules de la plage destination.
AutoFit
Ajuste la largeur de colonne / hauteur de ligne en fonction du contenu.
S'applique sur des colonnes ou des lignes mais pas directement sur la plage.
Range(Cells(1, 2), Cells(3, 2)).AutoFit renvoie une erreur, il faut crire
Range(Cells(1, 2), Cells(3, 2)).Columns.AutoFit
BorderAround
Syntaxe MaPlage.BorderAround(LineStyle, Weight, ColorIndex, Color)
Permet de raliser directement un contour extrieur d'une plage. Ainsi l'exemple que j'ai donn pour la
proprit Borders s'crirait plus simplement :
With Range(Cells(1, 1), Cells(5, 1))
.Borders.LineStyle = xlContinuous
.BorderAround Weight:=xlMedium
End With
Calculate
Bien que cette mthode appartienne l'objet application, on l'utilise lorsqu'on veut minimiser le temps de
calcul sur un objet range restreint. En gnral elle s'utilise en coordination avec un vnement Change et une
plage Dependents.
Nous pourrions imaginer la fonction suivante qui ne calculerait en permanence que les cellules ncessaires.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo EnleveErreur
Target.Dependents.Calculate
Exit Sub
EnleveErreur:
Err.Clear
End Sub
Ce code n'est qu'un exemple puisque c'est ce que fait Excel. Celui ci ne servirait que si on utilise des
fonctions "volatiles", ou si on veut restreindre la plage de calcul.
Clear / ClearContents / ClearFormats

Efface tout ou la partie spcifie de la plage.
ColumnDifferences / RowDifferences
Syntaxe : MaPlage.ColumnDifferences(Comparison)
Renvoie un objet Range contenant toutes les cellules de la plage dont le contenu est diffrent de celui
spcifi dans l'argument Comparison. Quelques limitations toutefois, l'argument comparison doit tre un objet
Range contenant une seule cellule ; elle doit faire partie de la plage. L'objet Range renvoy peut tre discontinu,
alors que la plage d'appel doit tre continue. La comparaison ne se fait pas sur toute la feuille mais sur la plage
UsedRange (voir plus loin)
Ces mthodes sont trs puissantes dans un certains nombres de cas. Supposons que j'ai un tableau contenant
des cellules vides dans ma feuille, le code suivant va me renvoyer un objet Range contenant toutes les cellules
non vides de la feuille.
Dim MaPlage As Range, raEnum As Range
Set MaPlage =
Page 15/44
Maj 2008
ActiveSheet.Columns.ColumnDifferences(ActiveSheet.Cells.SpecialCells(xlCell
TypeLastCell).Offset(1, 1))
For Each raEnum In MaPlage.Areas
Next
Copy
Cette mthode utilise ou non l'argument Destination. Lorsque l'argument est omis, la mthode copie la
plage dans le presse-papiers, sinon le collage lieu dans le mme temps. Sauf cas trs particulier il faut toujours
prciser l'argument Destination. Cet argument doit tre soit un objet Range de la mme dimension que la plage
source, soit une cellule unique qui deviendra le coin suprieur gauche de la plage colle. Pour donner un
exemple, le code gnr par l'enregistrement automatique serait :
Range("A1:E5").Select
Selection.Copy
Sheets("Feuil2").Select
Range("C3").Select
ActiveSheet.Paste
Ce qui revient crire :
Range("A1:E5").Copy Destination:=Worksheets("Feuil2").Range("C3")
Dans certaines versions d'Excel, une erreur se produit si une des plages contient des cellules
fusionnes.
En Excel97 il est impratif que la zone copie soit situe sur la feuille active.
Insert / Delete
Permet d'insrer ou de supprimer une plage de cellules. Utilise l'argument Shift pour dfinir le sens de
dplacement des cellules. Pour insrer une ligne ou une colonne entire utiliser EntireRow / EntireColumn (voir
plus loin)
FillDown, FillLeft, FillRight,FillUp

Recopie la plage avec le contenu de la cellule source. La cellule source doit tre l'extrmit inverse du
sens de la recopie.
Ainsi Range(Cells(1,1),Cells(10,1)).FillDown recopie la cellule A1 dans la plage
Range(Cells(1,1),Cells(10,1)).FillUp recopie la cellule A10 dans la plage
Find & FindNext

La syntaxe de cette mthode est MaPlageFind(What, After, LookIn, LookAt, SearchOrder, SearchDirection,
MatchCase, MatchByte)
Je vais en profiter pour tordre le cou un canard. La mthode Find ne dclenche pas une erreur lorsqu'elle
ne trouve pas de cellules correspondantes, comme je l'ai souvent lu dans des forums. Lors d'un enregistrement
automatique on obtient :
Selection.Find(What:="27", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Ainsi qu'un message d'alerte qui signale qu'il n'y a pas de cellules correspondantes. Lorsqu'on prend ce bout
de code et qu'on cherche le bricoler pour faire une recherche dans son programme, on constate qu'il se produit
une erreur s'il n'y pas de cellule contenant la recherche. Or ce qui est en cause n'est pas la mthode Find, mais
l'appel de la mthode Activate. En effet, Find renvoie un objet Range qui est la premire cellule contenant le
critre ou Nothing s'il n'y en a pas.
Et l'appel de Nothing.Activate fait planter l'excution.
Ce qui en une mthode me permet de montrer qu'il faut se mfier de l'enregistrement automatique, de la
mthode Activate et des rigolos qui font des copier/coller de bout de code.
La mthode Find se programme comme dans l'exemple suivant.
Dim MaFeuille As Worksheet, Reponse As Range, PremAdresse As String
Dim MonCritere As String
Set MaFeuille = ActiveSheet
MonCritere = "27"
Page 16/44
Set Reponse = MaFeuille.Cells.Find(MonCritere, MaFeuille.Cells(1, 1),

xlValue, xlWhole)
If Not Reponse Is Nothing Then
PremAdresse = Reponse.Address
Do
Set Reponse = Selection.FindNext(After:=Reponse)
Loop While Not Reponse Is Nothing And Reponse.Address <>
PremAdresse
End If
PasteSpecial
Bien que l'on puisse souvent se passer de cette mthode, il y a des cas o elle est trs utile. Sa syntaxe est :
MaPlage.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)
Replace
Fonctionne sur le mme schma que Find, la diffrence que cette mthode ne renvoie rien.
Sort
Tri la plage spcifie. Sa syntaxe est :
MaPlage.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase,
Orientation)
Type n'est utilis que pour les objets PivotTable.
Les Arguments Key sont des variants, contenant la cl de tri, les arguments Order sont le sens du tri.
Header dfinit s'il y a une ligne / colonne d'entte, MatchCase si le tri est sensible la casse, Orientation
donne le sens du tri.
Par exemple
Range("I1:K5").Sort Key1:=Range("K1"), Order1:=xlAscending,
Key2:=Range("J1") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom
SpecialCells
MaPlage.SpecialCells(Type, Value)
Renvoie un objet Range contenant les cellules correspondant aux critres donns en argument. Les choix
possibles sont :
XlCellTypeNotes Les cellules contenant des annotations.
xlCellTypeConstants Les cellules contenant des constantes.
xlCellTypeFormulas Les cellules contenant des formules.
XlCellTypeBlanks Les cellules vides.
XlCellTypeLastCell La dernire cellule de la plage utilise.
XlCellTypeVisible Toutes les cellules visibles.
Les Types xlCellTypeConstants et xlCellTypeFormulas acceptent un argument Value qui peut tre xlErrors,
xlLogical, xlNumbers, xlTextValues, xlAllFormatConditions. Celui ci permet d'affiner les critres de slection.
Nous verrons des exemples dans la seconde partie de ce document.
L'exemple suivant renvoie un objet range contenant toutes les cellules vides de la plage :
Dim MaPlage As Range
Set MaPlage = Range(Cells(1, 1), Cells(10,
10)).SpecialCells(xlCellTypeBlanks)
TextToColumns
Permet de redistribuer sur plusieurs cellules une cellule contenant du texte avec des sparateurs, similaire
la mthode OpenText de la collection WorkBooks.
Plages particulires
Page 17/44
Maj 2008
Maj 2008
Ligne ou colonne entire

Il y a plusieurs mthodes pour slectionner des lignes compltes.
On peut utiliser la proprit EntireRow d'un objet Range. Par exemple :
Range(Cells(1,1),Cells(2,1)).EntireRow renvoie les lignes 1 et 2.
Sinon on peut utiliser la collection Rows de l'objet Range.
MaFeuille.Range(Rows(1), Rows(2)) renvoie les mmes lignes.
UsedRange
Renvoie un objet Range qui reprsente l'ensemble de la plage utilise dans la feuille. Attention toutefois,
l'effacement du contenu d'une cellule la laisse comme tant utilise dans la feuille jusqu' un nouvel appel de la
mthode UsedRange. Nous verrons ce point dans la deuxime partie de ce document.
Plage nomme
Toute plage d'une feuille peut avoir un nom (proprit Name). Le fait de nommer une plage permet de
pouvoir faire rfrence celle-ci de faon simple, et de rendre la programmation indpendante de l'adresse relle
de la plage. Ces plages peuvent tre discontinues. Sauf dclaration particulire, une plage nomme appartient
l'objet Workbook. Il y a l un danger lors de la duplication d'une feuille. Au moment de la duplication, toutes les
plages nommes qui rfrent la feuille que l'on va dupliquer deviennent des plages propres cette feuille (c'est
dire dont le nom de la plage se transforme de "NomPlage" en "NomFeuille!NomPlage", puis les noms sont
dupliqus dans la nouvelle feuille. Notons que les noms spcifiques de feuilles apparaissent dans la collection
Names de l'objet classeur, mais que seuls les noms spcifiques apparaissent dans la collection Names de l'objet
feuille. Dans un classeur modle, il est fortement conseill d'utiliser au maximum les plages nommes.
Objets graphiques (Chart & ChartObject)

Les objets graphiques servent tracer des courbes. Il existe des feuilles graphiques (Chart) qui
appartiennent l'objet WorkBook, et des graphiques incorpors (ChartObject) qui appartiennent l'objet
WorkSheet. Bien que ces objets soient similaires, ils prsentent quelques diffrences d'emploi. Je ne vais pas
tudier l'ensemble des proprits et mthodes de mise en forme de ces objets car elles sont assez faciles utiliser.
Par contre, nous allons regarder les objets constituants de l'objet Chart.
Collection Charts & ChartObjects

Pour la collection Charts, la position relative des onglets donne le numro d'index de l'objet, il n'est en
gnral pas le mme que l'index dans la collection Sheets. Pour la collection ChartObjects, le numro d'index
correspond l'ordre de cration.
Les proprits / mthodes sont les mmes que pour la collection WorkSheets. Il y a juste une diffrence
pour la mthode Add de la collection ChartObjects puisquon peut donner la position et les dimensions de l'objet.
ChartObjects.Add(Left, Top, Width, Height)
Les coordonnes sont donnes en point.
Evnements
Gestion d'vnement pour l'objet ChartObject
L'objet ChartObject tant contenu dans l'objet WorkSheet, il ne possde pas de module objet qui lui soit
propre. Pour pouvoir utiliser ces vnements il faut dclarer l'objet "WithEvents".
Par exemple, dans le module de la feuille :
Private WithEvents MonGraphe As Graph
Et ensuite
Private Sub MonGraphe_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal
Arg2 As Long, Cancel As Boolean)
End Sub
Il faudra bien sr faire l'affectation dans le code avec par exemple
Set MaFeuille.MonGraphe=MaFeuille.ChartObjects.Add(10,10,100,100)
Si on doit grer les vnements de nombreux graphiques incorpors, il convient d'crire une classe pour
cette gestion.
Page 18/44
Maj 2008
BeforeDoubleClick / BeforeRightClick
Private Sub MonGraphe_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal
Arg2 As Long, Cancel As Boolean)
ElementID renvoie l'lment du graphique sur lequel le double click a eu une action, le contenu de Arg1 et
Arg2 dpendent de cet lment.
Calculate
Se produit aprs l'ajout ou la modification d'une srie.
MouseDown, MouseUp, MouseMove
Private Sub MonGraphe_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long,
ByVal Y As Long)
Private Sub MonGraphe_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long,
ByVal Y As Long)
Private Sub MonGraphe _MouseMove(ByVal X As Long, ByVal Y As Long)
Evnements de gestion de la souris (classique en Visual Basic).
SeriesChange
Private Sub MonGraphe_SeriesChange(ByVal SeriesIndex As Long, ByVal PointIndex As Long)
Les arguments renvoient la srie slectionne et le point slectionn.
Cet vnement se produit lorsque la valeur d'un point change.
Proprits et mthodes
CopyPicture
MonGraphe.CopyPicture(Appearance, Format, Size)
Gnralement utilise en Visual Basic, cette mthode permet de copier l'image d'un graphique, ce qui
permet de le rendre indpendant de ses donnes. Attention, il n'est pas possible aprs de faire la procdure
inverse.
Export
MonGraphe .Export(FileName, FilterName, Interactive)
Sert exporter le graphique dans un fichier image.
GetChartElement
MonGraphe .GetChartElement(X, Y, ElementID, Arg1, Arg2)
Cette mthode s'utilise avec les vnements souris. En passant cette mthode les arguments X et Y, il
renvoie le type d'lment et des informations complmentaires dans ElementID, arg1 et arg2
Location
Permet de changer un objet Chart en ChartObject ou inversement.
SetSourceData
Permet de dfinir une plage contenant les donnes tracer. C'est une des mthodes permettant de tracer des
sries. Nous y reviendrons en dtail dans la deuxime partie.
DisplayBlanksAs
Cette proprit dfinit comment sont gres les cellules vides de la plage de donnes.
HasAxis, HasLegend, HasTitle

Doivent tre misent "True" pour pouvoir utiliser les objets axes, lgende.
Les objets constituants

La programmation des graphiques se fait surtout en manipulant les objets constituants de celui-ci. Ces
objets sont directement accessibles dans le cas d'un objet Chart, mais il faut passer par la proprit Chart pour y
Page 19/44
Maj 2008
accder depuis un ChartObject (voir dans l'exemple pour "Legend " ci-dessous).
Axis
Collection des axes du graphique. Pour identifier un seul axe, utiliser la mthode Axes (Type, Group) de
l'objet Chart. Les valeurs pour Type sont xlCategory pour les abscisses et xlValue pour les ordonnes. Le modle
objet Axis est le suivant :
ChartAreas, PlotAreas
Dfinissent des zones du graphique. Utilises uniquement pour la mise en forme.
Legend
Dfinit la lgende du graphique. Le modle objet est le suivant :
A chaque srie du graphique correspond normalement un objet LegendEntry. Il est possible de supprimer
un de ces objets, mais pas d'en modifier le texte directement (pour cela il faut modifier la proprit Name de la
srie). Par exemple pour supprimer la lgende de la quatrime srie d'un graphique incorpor :
Dim MonGraphe As ChartObject
Set MonGraphe = ActiveSheet.ChartObjects(1)
MonGraphe.Chart.Legend.LegendEntries(4).Delete
Comme je vous l'ai dit, vous voyez ici que je passe par la proprit Chart pour accder la lgende.
Series, SeriesCollection
C'est donc l'objet principal de la programmation des graphes, aussi allons nous regarder un peu plus en
dtail.
La collection SeriesCollection reprsente donc toutes les sries du graphique. On peut donc ajouter une
srie l'aide de la mthode Add.
MonGraphe.SeriesCollection.Add(Source, Rowcol, SeriesLabels,
CategoryLabels, Replace)
L'argument Source contient l'objet Range contenant les donnes. On peut aussi ajouter une srie vide
l'aide de la mthode NewSerie. Enfin on peut ajouter des donnes une srie a l'aide de la mthode Extend. Je
reviendrai dans la deuxime partie de ce document sur la cration dynamique de sries. On utilise directement
l'objet Series pour sa mise en forme.
Page 20/44
Maj 2008
Point, Points
La collection points reprsente tous les points d'une srie, l'objet point un seul de ces points. Sachez juste
quon ne peut pas ajouter un point la collection Points.
Conseils gnraux
Un peu de dtente
Aprs ce parcours un peu fastidieux du modle objet d'Excel, je vais un peu gloser sur les dfauts habituels
que l'on rencontre souvent en VBA. On trouve cinq grandes familles de "dveloppeurs" VBA-Excel.
L'enregistreur
C'est la famille la mieux reprsente, car la mthode est simple. On lance l'enregistrement automatique pour
gnrer le maximum de code possible, puis on tente d'crire le liant pour que l'ensemble fonctionne. Ce genre de
code est facile identifier car les variables ne sont pas dclares et encore moins types, le code est extrmement
lent, et une fois sur deux, la mise jour de l'cran n'tant pas dsactive, le dplacement rapide de la slection
peut rendre une taupe pileptique.
Le codeur fou
En gnral il possde une bonne connaissance de la syntaxe Excel. Le code est mme souvent trs propre.
Seulement comme il aime bien crire des programmes, il se garde bien d'utiliser un seul raccourci pour allger le
code. Ainsi pour remplir quatre cases d'un formulaire, il en profitera pour gnrer l'ensemble du formulaire
chaque excution au lieu d'utiliser un modle.
Le mathmaticien
Celui l ne pense pas qu'Excel puisse servir autre chose que pour faire de la mise en page de tableau (la
preuve, cela s'appelle un tableur). Aussi ne va-t-il pas confier un calcul un logiciel, alors qu'il peut les faire
dans le code.
Le navigateur
De la famille du grand Surfeur, il parcourt le Web la recherche de bout de code qu'il pourrait copier pour
mettre dans son programme. Cela cre un code un peu disparate, avec des parties bien crites et d'autres moins.
En gnral, ce genre d'application ne fait jamais bien ce qu'on lui demande, mais en change, elle fait des tas de
choses dont on n'a pas besoin.
Le designer
Cette dernire espce est trs frquente en VB. Enthousiasm par la conception de formulaire, on y trouve
des boites de dialogue de toute beaut. Bien que la plupart des contrles n'ait aucune utilit, reconnaissons que la
beaut de l'oeuvre peut laisser rveur.
Les rgles de bases

Poser le problme
L'criture d'une application quel que soit le langage utilis doit toujours commencer par une analyse de ce
que l'on veut faire. En gnral pour de la programmation Excel, on dispose d'une feuille Modle (Template) dans
laquelle on va amener des donnes pour obtenir des rsultats (calculs et/ou graphiques). Ces donnes sont soit
tires d'un fichier, soit demandes l'utilisateur. Dans le premier cas, l'interaction avec l'utilisateur devra tre
minimise voire interdite, dans le deuxime on crira une application vnementielle.
Les variables
Penser toujours dclarer vos variables avant de les utiliser et donnez leur un type le plus prcis possible.
Faite attention prciser le type aprs chaque variable, en VBA
Dim Var1, Var2 As Integer est diffrent de Dim Var1 As Integer, Var2 As Integer.
Minimisez toujours la porte de vos variables, afin qu'elles n'existent que l o elles sont utiles.
Librer la mmoire ds que possible, en utilisant des tableaux dynamiques plutt que fixes, librer aussi les
objets.
Mettez dans des variables les proprits dont vous avez besoin dans les boucles.
Page 21/44
Maj 2008
Les fonctions
N'abusez pas des fonctions, n'appelez pas de fonction dans les boucles. En gnral, passez des paramtres et
n'utilisez pas des variables publiques. N'oubliez pas qu'une fonction peut modifier les paramtres passs ByRef
et donc retourner des valeurs par ses arguments. Demandez-vous toujours ce que vous avez besoin de rcuprer,
ne rcuprer pas un objet lorsqu'une adresse suffirait.
Les objets
Utilisez des blocs With lorsque vous avez besoin d'accder plusieurs proprits / mthodes d'un objet.
Mettez dans des variables les objets que vous appelez frquemment. Faites attention, certaines proprits /
mthodes d'un objet renvoient un objet.
L'application
Pensez dsactiver le mode de calcul et la mise jour d'cran. Remettez toujours l'objet Application dans
l'tat o il tait. Dsactivez les vnements quand vous n'en avez pas besoin.
Slection
Sauf lorsque c'est indispensable, n'utilisez pas les mthodes Select et Activate. Lorsque vous avez plusieurs
objets feuilles ou classeurs, utilisez des variables pour les nommer, n'utilisez pas l'objet "Slection".
Utilisation d'un modle

Pour simplifier la programmation, on utilise en gnral un modle. Il est souvent inutile de programmer
toute la mise en forme d'un document, alors que l'on peut appeler un modle contenant dj :
La mise en page
Celle ci doit tre relativement simple. Evitez le plus souvent de fusionner les cellules, ne surchargez pas vos
feuilles de motifs et de bordures. Lorsqu'il s'agit d'un formulaire avec lequel l'utilisateur doit interagir,
chercher faire un formulaire fonctionnel et non une oeuvre d'art.
Les formules de calculs
Dans vos formules, utilisez des plages nommes si vous tes amen supprimer / insrer des cellules. Faites
attention aux rfrences (relatives / absolues).
Les graphes
Il est possible de placer dj des graphes mis en forme dans le classeur. Une bonne solution consiste
utiliser pour les sries des plages nommes. Ainsi, le fait de nommer la plage lors de l'excution remplira
automatiquement le graphique.
Les plages nommes
Nommez les plages spcifiques de la feuille la cration, sauf dans le cas o vous ne connaissez pas leur
taille. Utilisez des noms clairs et significatifs.
Techniques de programmation
vnements
Utiliser l'argument Target
De nombreux vnements Excel passe un argument Target qui est un objet Range contenant la plage
concerne par l'vnement. Cette plage peut tre discontinue.
Intersection d'une plage avec Target
Dans l'exemple suivant, la fonction met une croix (un X) lorsque l'on clique sur une cellule de la plage
Nomme AjoutX
Page 22/44
Maj 2008
Set MaPlage = Application.Intersect(Range("AjoutX"), Target)

If Not MaPlage Is Nothing Then MaPlage.Value = "X"
End Sub
Action sur une plage dfini par Target
L'exemple suivant dclenche le calcul sur toutes les cellules dpendantes de la plage modifie.
On Error Resume Next
Set MaPlage = Target.Dependents
Err.Clear
If Not MaPlage Is Nothing Then
MaPlage.Calculate
End If
End Sub
Dsactiver les vnements

Cela se fait avec Application.EnableEvents=False. Ceci est particulirement utile pour les vnements
Change et SelectionChange qui peuvent facilement crer un vnement en cascade. Par exemple le code suivant
est un vnement en cascade.
ActiveCell.Offset(1).Value = 12
End Sub
Pour une excution correcte, il faut crire :
Application.EnableEvents = False
ActiveCell.Offset(1).Value = 12
End Sub
Lire les informations d'un graphe

Dans un objet graphique, on peut utiliser les vnements de la souris pour savoir sur quelle partie du
graphique se situe le pointeur. En elle-mme la fonction suivante ne sert rien, mais elle donnera une ide de la
programmation suivre. Cette fonction affiche dans la StatusBar les informations rcupres lors du
dplacement de la souris.
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long,
ByVal X As Long, ByVal Y As Long)
Dim MonElement As Long, Param1 As Long, param2 As Long
ActiveChart.GetChartElement X, Y, MonElement, Param1, param2
Select Case MonElement
Case xlChartArea
Application.StatusBar = "Zone de graphique"
Case xlChartTitle
Application.StatusBar = "Titre"
Case xlPlotArea
Application.StatusBar = "Zone de traage"
Case xlLegend
Application.StatusBar = "Lgende"
Case xlSeries
Application.StatusBar = "Srie n " & Param1 & "point n " & param2
Case xlDataLabel
Application.StatusBar = "label de la srie n " & Param1 & "point n "
& param2
Case xlLegendKey
Application.StatusBar = "lgende de la srie n " & Param1
Page 23/44
Maj 2008
End Select
End Sub
Application
N'oublions pas les fonctions de bases
Mise jour d'cran Application.ScreenUpdating = False / True
Mode de calcul Application.Calculation= xlCalculationAutomatic / xlCalculationManual
Message d'alerte Application.DisplayAlerts= False / True
Vider le presse - papier Application.CutCopyMode= False
Bloquer les interactions souris-clavier Application.Interactive = False / True
Rcuprer les paramtres internationaux

On utilise pour cela la proprit "International". Attention, cette proprit est en lecture seule. Il est
possible de modifier ces paramtres avec l'API Windows, mais pensez toujours alors restaurer ces paramtres
avant l'arrt de votre application.
Modifier les menus

Il est possible de supprimer des menus d'Excel lors de l'excution, on utilise pour cela la collection
CommandBars de l'objet Application. Pour trouver les rfrences d'un menu, il suffit de connatre sa position. La
barre de menu est CommandBars(1) les autres objets tant les barres d'outils. Il convient de faire preuve de
cohrence. Lorsque l'on cherche supprimer l'accs ou la visibilit d'un menu, on supprime de mme son
quivalent dans les barres d'outils et on bloque l'accs au menu "Outils-personnaliser".
Rappel : "Enabled" interdit l'accs, "Visible" masque l'lment
La fonction suivante dsactive le menu "Insertion-Cellules", les boutons correspondant de la barre d'outils
et le menu "Outils-personnaliser".
Public Sub DesacInsertDelCell()
With Application
With .CommandBars(1)
'Menu insertion cellule
.Controls(4).Controls(1).Enabled = False
'Menu outils personnaliser
.Controls(6).Controls(12).Enabled = False
End With
With Application.CommandBars("Formatting")
.Controls(15).Enabled = False
.Controls(16).Enabled = False
End With
End With
End Sub
Classeur
Ouvrir un classeur
Selon un modle
Workbooks.add("MonFichier.xlt")
N'ayant qu'une seule feuille
Workbooks.add(xlWBATWorksheet)
Avec un nombre fixe de feuille
Application. SheetsInNewWorkbook=4
Workbooks.add
Feuille
Page 24/44
Maj 2008
Rendre les plages nommes spcifiques leur feuilles

La macro suivante va affecter chaque feuille les plages qui rfrent elle dans le classeur. Cette fonction
n'a pas de vritable utilit dans un code, mais elle va nous permettre de voir deux concepts intressants.
Public Sub AffectNom()
Dim MaFeuille As Worksheet, LeNom As Name, Recup As Range
For Each LeNom In ActiveWorkbook.Names
If InStr(1, LeNom.RefersTo, "!") > 0 And InStr(1, LeNom.Name, "!") = 0
Then
Set MaFeuille = Worksheets(Mid(LeNom.RefersTo, 2, InStr(1,
LeNom.RefersTo, "!") - 2))
Set Recup = Application.Evaluate(LeNom.RefersTo)
Recup.Name = MaFeuille.Name & "!" & LeNom.Name
LeNom.Delete
End If
Next
End Sub
La premire remarque est de remarquer que l'on ne peut pas renommer une plage pour la rendre spcifique.
Si j'avais utilis la ligne LeNom.Name = MaFeuille.Name & "!" & LeNom.Name cela n'aurait pas eu d'effet. Il
est possible de changer le Nom pour un autre nom, mais pas par un nom spcifique.
La deuxime astuce est l'utilisation d'Evaluate. Cette fonction se retrouve souvent en VBA car elle permet
de combler de nombreuses lacunes de la programmation Excel. Dans le cas prsent, je l'utilise pour convertir une
adresse en un objet Range.
N.B: Dans ce cas, Evaluate n'est pas ncessaire, la commande suivante fonctionne aussi :
Set Recup = Range(LeNom.RefersTo)
Gestion des erreurs

Celle-ci se pratique de la mme faon que dans Visual Basic. Pour une raison que je n'ai toujours pas
comprise, beaucoup de dveloppeurs VB la mprise, comme si son utilisation sous-entendait que le
programmeur va faire des erreurs. Sachez toutefois qu'elle est dans certains cas extrmement rapide, et qu'il est
dommage de ne pas l'utiliser au nom d'une dogmatique tout fait discutable. Prenons l'exemple suivant. Dans
mon classeur je cherche savoir si la feuille "pilotage" existe. Je peux videmment parcourir la collection des
feuilles pour la chercher, mais cela est assez lent. Le mieux est alors de faire :
Public Sub rempliTab()
Dim MaFeuille As Worksheet, Maplage As Range, compteur As Long
On Error Resume Next
Set MaFeuille = ActiveWorkbook.Worksheets("pilotage")
If Err.Number <> 0 Then
ActiveWorkbook.Worksheets.Add
After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
ActiveSheet.Name = "pilotage"
Set MaFeuille = ActiveSheet
Err.Clear
End If
On Error GoTo 0
Set Maplage = Worksheets("pilotage").Range(Cells(1, 1), Cells(20, 5))
For compteur = 1 To Maplage.Cells.Count
Maplage.Cells(compteur).Value = compteur
Next compteur
End Sub
Plage
Rfrences dans les plages
Dans une plage dfinie, le systme de rfrence est le mme que pour une feuille, savoir
Page 25/44
Maj 2008
MaPlage.Cells(Ligne,Colonne). La cellule suprieure gauche au sein de la plage prend l'adresse (1,1) et la cellule
infrieure droite la valeur MaPlage.Cells(MaPlage.Rows.Count,MaPlage.Columns.Count).
Regardons le cas suivant :
Public Sub rempliTab()
Dim Maplage As Range, compteur As Long
Set Maplage = Worksheets("pilotage").Range(Cells(1, 2), Cells(20, 6))
For compteur = 1 To Maplage.Cells.Count
Maplage.Cells(compteur).Value = compteur
Next compteur
MaPlage.Range(Cells(3,3),Cells(4,4)).Interior.ColorIndex=3
Maplage.Cells(1, 0).Interior.ColorIndex=4
End Sub
La rfrence MaPlage.Range(Cells(3,3),Cells(4,4)) renvoie une plage de quatre cellules dans le systme
d'adresse de la plage, c'est dire dans notre exemple gale ActiveSheet. Range(Cells(3,4),Cells(4,5)), puisque
la plage commence la colonne 2. Nous voyons la ligne suivante que nous pouvons donner une adresse
extrieure la plage. Dans notre cas le numro de colonne 0 reprsente la colonne gauche de la plage. Ceci
n'est pas sans inconvnients, car il n'y a pas de contrle sur la prsence d'une cellule dans la plage.
Donc en gnral l'adresse d'une cellule de l'objet Range se donne par Cells(Ligne, Colonne). Nanmoins
dans une plage continue, on peut utiliser juste le numro d'index de la cellule au sein de la collection. Excel
donne ce numro toujours dans l'ordre " droite puis en bas". Ainsi le code ci dessus affichera "1" dans la cellule
B1, "5" dans la cellule F1, "6" dans la cellule B2 etc..
Cette notation est trs peu utilise, mais elle peut permettre de parcourir une plage avec une seule boucle.
Pour passer d'un index une adresse on emploie les formules suivantes :
Index Ligne, colonne
MaPlage.Cells(MonIndex\ MaPlage.Columns.Count+1,MonIndex mod MaPlage.Columns.Count)
Ligne, colonne Index
MonIndex=(Cellule.Row-1)*MaPlage.Columns.Count+Cellule.Column
Tableau contenant les valeurs ou les formules

Deux proprits d'une plage peuvent tre rcupres dans un tableau partir d'une plage Value et Formula.
L'avantage de cette mthode est qu'il est beaucoup plus rapide de parcourir un tel tableau que d'numrer une
grande collection de cellules. Dans le chapitre qui vient, nous allons regarder en dtail le calcul sur les plages.
Pour pouvoir faire quelques tests de performance, je vais utiliser l'API "GetTickCount" qui renvoie le nombre de
millisecondes coules depuis le dmarrage de ma session Windows. Mon code va donc se prsenter ainsi :
Option Explicit
Private Declare Function GetTickCount Lib "kernel32" () As Long
Public Sub TestEff()
Dim MaPlage As Range, Depart As Long
Application.Calculation = xlCalculationManual
Depart = GetTickCount
.. 'code tester
MaPlage.Calculate
MsgBox GetTickCount
Depart
End Sub
N.B : "Option Explicit" oblige la dclaration des variables.
Pour crer ma plage de tests je vais utiliser la formule Excel "=Ligne()*Colonne()" qui dans chaque cellule
multipliera le numro de la ligne par celui de la colonne. Ma ligne de remplissage sera :
MaPlage.FormulaLocal = "=Ligne()*Colonne()"
Dans ce cas j'utilise FormulaLocal pour lui passer une formule appelant des fonctions en franais, ce qui est
strictement quivalent utiliser :
MaPlage.Formula = "=ROW()*COLUMN()"
En supposant par contre que la cellule A1 contienne dj la formule ci-dessus, je pourrais recopier cette
formule avec AutoFill.
MaPlage.Cells(1).AutoFill Destination:=MaPlage
Page 26/44
Cependant le code suivant engendrera une erreur car AutoFill ne peut pas recopier une cellule dans deux
directions simultanment. Je devrais donc utiliser :
MaPlage.Cells(1).AutoFill Destination:=MaPlage.Rows(1)
MaPlage.Rows(1).AutoFill Destination:=MaPlage
Cette mthode est un peu plus lente que de faire :
MaPlage.Formula = MaPlage.Cells(1).Formula
Mais ayez dj l'esprit que le temps de remplissage de la plage reprsente environ 10% du temps de la
fonction (le reste tant le temps de calcul).
A ne jamais faire
Une autre mthode pourrait tre de parcourir la collection des cellules et d'crire :
For Each MaCellule In MaPlage.Cells
MaCellule.FormulaLocal = "=Ligne()*Colonne()"
Next
Seulement dans ce cas, le temps de traitement est multipli par dix.
Utiliser un tableau (variant)
Comme je vous l'ai dit au dbut on peut affecter les valeurs d'un tableau la proprit "Value" ou
"Formula" d'une plage. Je dclare mon tableau comme un Variant car certaines versions d'Excel n'acceptent pas
les tableaux typs du fait que le tableau peut contenir du texte. Regardons les trois codes suivants:
MaPlage.Calculate
MsgBox GetTickCount - Depart
End Sub
Dim MonTab As Variant, comptLig As Long, comptCol As Long
ReDim MonTab(1 To MaPlage.Rows.Count, 1 To MaPlage.Columns.Count)
For comptLig = 1 To MaPlage.Rows.Count
For comptCol = 1 To MaPlage.Columns.Count
MonTab(comptLig, comptCol) = "=Ligne()*Colonne()"
Next comptCol
Next comptLig
MaPlage.FormulaLocal = MonTab
End Sub
MonTab(comptLig, comptCol) = comptLig * comptCol
Next comptCol
Next comptLig
MaPlage.Value = MonTab
End Sub
Page 27/44
Maj 2008
Maj 2008
Le premier et le deuxime code font exactement la mme chose, c'est dire qu'ils mettent la formule de
calcul dans chaque cellule de la plage, mais le premier le fait cent fois plus vite. Il convient donc de ne pas
utiliser de tableau Variant sur les formules mais bien uniquement sur les valeurs.
Le premier et le troisime code donnent les mmes rsultats, mais une plage contient des formules, et l'autre
uniquement des valeurs. Par contre, la vitesse de traitement est strictement identique. Comme cette plage va me
servir de plage de valeur je peux donc utiliser indiffremment une des deux fonctions suivantes (la premire
tant un peu plus rapide) :
MonTab(comptLig, comptCol) = comptLig * comptCol
Next comptCol
Next comptLig
MaPlage.Value = MonTab
End Sub
MaPlage.Value = MaPlage.Value
End Sub
A noter que la ligne MaPlage.Value = MaPlage.Value remplace les formules de la plage par les valeurs
(c'est l'quivalent d'un collage spcial valeur).
Nous allons continuer valuer l'utilisation de ces tableaux en faisant l'exercice suivant. Je pars donc de ma
plage et je veux que chaque cellule contienne la valeur Cellule*2+3. Dans ma feuille, j'ai nomm ma plage
prcdente "PlageSource" (ce qui n'est pas original).
Calcul par la feuille
La premire mthode consiste faire le calcul par la feuille. J'utilise donc le code suivant :
Dim Depart As Long, MaPlage As Range
'mthode1 calcul par feuille
Set MaPlage = Range("PlageSource").Offset(0,
Range("PlageSource").Columns.Count)
MaPlage.FormulaLocal = "=LC(-" & Range("PlageSource").Columns.Count &
")*2+3"
MaPlage.Calculate
Range("PlageSource").Value = MaPlage.Value
MaPlage.Clear
Set MaPlage = Nothing
End Sub
C'est une utilisation standard des feuilles de calcul, mais il faut tre sr d'avoir la place pour crer une plage
intermdiaire.
Calcul par collage spcial
Cette mthode est beaucoup plus originale. Dans mon exemple elle n'est pas logique d'emploi, mais sachez
qu'elle existe et qu'elle est aussi rapide que les autres.
Page 28/44

Dim Depart As Long, MaPlage As Range
Set MaPlage = Range("PlageSource").Offset(0,
Range("PlageSource").Columns.Count)
MaPlage.Value = 2
MaPlage.Copy
Range("PlageSource").PasteSpecial xlPasteValues,
xlPasteSpecialOperationMultiply
MaPlage.Value = 3
MaPlage.Copy
Range("PlageSource").PasteSpecial xlPasteValues,
xlPasteSpecialOperationAdd
MaPlage.Clear
Set MaPlage = Nothing
End Sub
Comme vous le voyez, je colle des plages de taille identique sur ma plage en lui faisant faire une opration
chaque fois. Notez que dans ces deux codes, je libre l'objet Maplage en fin de fonction ce qui n'est pas
ncessaire puisque la variable va tre dtruite, mais ce qu'il ne faut pas oublier de faire dans une fonction plus
longue.
Calcul par tableau variant
Avec ce code je passe par un tableau en mmoire.
Dim Depart As Long, MaPlage As Range, MonTab As Variant, comptLig As Long,
comptCol As Long
MonTab = Range("PlageSource").Value
For comptLig = 1 To UBound(MonTab, 1)
For comptCol = 1 To UBound(MonTab, 2)
MonTab(comptLig, comptCol) = MonTab(comptLig, comptCol) * 2 + 3
Next comptCol
Next comptLig
Range("PlageSource").Value = MonTab
Erase MonTab
End Sub
Calcul par tableau avec Evaluate
Enfin cette dernire mthode utilise la fonction "Evaluate"
Dim Depart As Long, MaPlage As Range, MonTab As Variant
Application.ReferenceStyle = xlA1
MonTab = Application.Evaluate(Range("plagesource").Address(True, True,
xlA1, True) & "* 2 + 3")
Maplage.Value = MonTab
Erase MonTab
End Sub
Pour un bon fonctionnement de Evaluate, il faut que l'environnement Excel soit en mode A1.
Page 29/44
Maj 2008
Maj 2008
Synthse
Je vous donne ces diffrentes syntaxes car elles sont sensiblement quivalentes en vitesse. Selon les cas il
convient d'en utiliser une plutt que l'autre, mais c'est vous de savoir.
UsedRange et "SpecialCells(xlCellTypeLastCell)"
Il y a un pige potentiel lors de la recherche de la dernire cellule de la feuille. En pratique Excel dfinit
comme dernire cellule, la cellule de rang le plus lev de la plage UsedRange. Regardons le code suivant
Range(Cells(1, 1), Cells(100, 10)).Value = "12"
Range(Cells(50, 1), Cells(100, 10)).Clear
MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address
La boite de dialogue va renvoyer l'adresse J100, pourtant la dernire cellule non vide est la cellule J49. Ceci
vient du fait qu'Excel ne remet pas jour automatiquement l'adresse de la dernire cellule lors d'un effacement.
Pour forcer cette mise jour, il faut faire appel explicitement la proprit UsedRange. Ds lors le code correct
est :
MsgBox ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Address
N.B : L'appel de UsedRange dclenche toujours la mise jour de la dernire cellule.
A la recherche des cellules vides
Voil un grand classique de la programmation d'Excel. En fait plusieurs mthodes sont utilisables selon que
l'on souhaite savoir s'il y a des cellules vides ou si on veut pouvoir y accder.
Etudions le cas ci-dessus avec le code ci-dessous :
Public Sub ChercheVide()
Dim MaPlage As Range, NbVide As Integer
NbVide = Application.WorksheetFunction.CountBlank(Range(Cells(1, 1),
Cells(16, 3)))
If NbVide>0 Then Set MaPlage = Range(Cells(1, 1), Cells(16,
Page 30/44
Maj 2008
3)).SpecialCells(xlCellTypeBlanks)
MsgBox NbVide - MaPlage.Cells.Count
End Sub
La boite de dialogue affiche "4". Cela vient du fait que l'ordre SpecialCells(xlCellTypeBlanks) cherche les
cellules effectivement vides (c'est dire qui ne contiennent ni valeur ni formule) alors que la fonction
CountBlank compte les cellules dont la valeur est vide. On obtient donc deux informations diffrentes.
Supposons maintenant que je le but soit d'obtenir un objet Range avec toutes les cellules dont la valeur est vide.
Cette plage ne peut pas tre trouve directement, nous allons donc crire la fonction qui le fait. J'utilise donc ma
fonction ChercheVide, qui appelle une fonction PlageCelluleVide qui renvoie l'objet Range dsir.
Public Sub ChercheVide()
Dim MaPlage As Range, NbVide As Integer, MaFeuille As Worksheet
NbVide = Application.WorksheetFunction.CountBlank(Range(Cells(1, 1),
Cells(16, 3)))
If NbVide>0 Then Set MaPlage = PlageCelluleVide(Range(Cells(1, 1),
Cells(16, 3)))
MsgBox NbVide - MaPlage.Cells.Count
End Sub
Public Function PlageCelluleVide(PlageSource As Range) As Range
Dim MaPlage As Range, PremAdresse As String
Set MaPlage = PlageSource.Cells.Find("", PlageSource.Cells(1, 1),
xlValue, xlWhole)
If Not MaPlage Is Nothing Then
PremAdresse = MaPlage.Address
Set PlageCelluleVide = MaPlage
Do
Set MaPlage = PlageSource.FindNext(After:=MaPlage)
If MaPlage Is Nothing Then Set PlageCelluleVide = MaPlage Else
Set PlageCelluleVide = Union(PlageCelluleVide, MaPlage)
Loop While Not MaPlage Is Nothing And MaPlage.Address <>
PremAdresse
End If
End Function
Bien sr on peut aussi faire une numration de toutes les cellules, mais c'est beaucoup plus long. Vous
noterez que quel que fut le cas, j'ai conditionn l'affectation de l'objet Range au fait qu'il y ait au moins une
cellule vide afin de ne pas dclencher une erreur d'excution.
Recherche de valeur particulire
Ce genre de recherche suit le mme principe que la recherche ci-dessus. La fonction de dcompte s'appelle
"CountIf", et on utilise de prfrence la mthode "Find" une numration.
Il y a nanmoins une astuce trs efficace bien que mconnue et qui consiste utiliser la mthode Autofilter.
Cette mthode ne fonctionne bien que sur une colonne mais rien n'empche de faire une boucle. Je vais prendre
un exemple. Je cherche l'ensemble des cellules dont la valeur vaut 24 dans une plage. Je devrais normalement
utiliser Find avec le code suivant :
Public Sub Recherche1()
Dim MaPlage As Range, PlageCellule24 As Range, PremAdresse As String,
MaCell As Range
Set MaPlage = ActiveSheet.Cells(1, 15).CurrentRegion
Set MaCell = MaPlage.Cells.Find(24, MaPlage.Cells(1, 1), xlValue,
xlWhole)
If Not MaCell Is Nothing Then
PremAdresse = MaCell.Address
Set PlageCellule24 = MaCell
Do
Set MaCell = MaPlage.FindNext(After:=MaCell)
Set PlageCellule24 = Union(PlageCellule24, MaCell)
Page 31/44
Maj 2008
Loop While Not MaCell Is Nothing And MaCell.Address <>

PremAdresse
End If
End Sub
Ce code fonctionne parfaitement bien, mais le code suivant fait la mme chose cent cinquante fois plus
vite sur une plage de 4000 lments.
Public Sub Recherche2()
Dim MaPlage As Range, PlageCellule24 As Range, compteur As Long
Set MaPlage = ActiveSheet.Cells(1, 15).CurrentRegion
For compteur = 1 To MaPlage.Columns.Count
MaPlage.Columns(compteur).AutoFilter field:=1, Criteria1:=24
If PlageCellule24 Is Nothing Then Set PlageCellule24 =
MaPlage.Columns(compteur).SpecialCells(xlCellTypeVisible) Else Set
PlageCellule24 =
Application.Union(MaPlage.Columns(compteur).SpecialCells(xlCellTypeVisible)
, PlageCellule24)
MaPlage.AutoFilter
Next compteur
PlageCellule24.Select
End Sub
Comme vous le voyez sur chaque colonne de la plage, j'applique l'AutoFilter sur la colonne. Celui ci
masque toutes les lignes qui ne correspondent pas au critre, et je rcupre la plage des cellules non masques
avec MaPlage.Columns(compteur).SpecialCells(xlCellTypeVisible)
Constantes et formules
L'autre utilisation frquente de la mthode "SpecialCells" est pour la gestion des tableaux contenant des
formules. En effet dans le cas classique d'utilisation d'un tableur, il y a un certain nombre de donnes fixes
(nommes constantes) qui alimentent les formules de calcul. Si ces plages sont continues, il n'y a pas de
problmes majeurs pour intervenir dessus, mais ce n'est pas toujours le cas. La ligne suivante supprime tous les
nombres constants prsents sur une feuille, sans toucher aux formules:
ActiveSheet.UsedRange.CurrentRegion.SpecialCells(xlCellTypeConstants,
xlNumbers).ClearContents
L'exemple suivant verrouille toutes les cellules contenant une formule.
ActiveSheet.UsedRange.CurrentRegion.SpecialCells(xlCellTypeFormulas).Locked
=True
La ligne suivante renvoie une plage contenant toutes les cellules en erreur :
Set MaPlage = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas,
xlErrors)
Format conditionnel
Je traite ce cas, car il concerne un sujet qui revient trs souvent sur les forums Excel. Trs souvent, on
trouve des questions du style, "comment trouver les cellules dont le fond est rouge?". Comme les cellules d'Excel
ne rougissent pas spontanment, c'est en gnral une recherche sur une cellule ayant un format conditionnel.
Comme les rgles d'un format conditionnel dcoulent du contenu d'une cellule (valeur ou formule) il convient de
faire porter la recherche sur ce contenu, et non sur l'effet visuel qui n'est que le rsultat d'un test de ce contenu.
Nanmoins certains cas peuvent tre plus complexes, nous allons donc regarder cela dans l'exemple suivant.
Toutefois, ne perdons pas de vu que le but est toujours d'viter une numration des cellules. Envisageons le cas
de la recherche de doublons sur une colonne.
L'objet FormatCondition
Chaque cellule peut avoir une collection FormatConditions qui peut contenir jusqu' trois objets
FormatCondition. Cet objet se dcompose globalement en deux parties, les rgles et le format. Pour simplifier,
on dfinit des rgles qui, lorsqu'elles sont remplies, modifient le format de la cellule. Les proprits de format
s'obtiennent en passant par les objets borders, font et interior. Les rgles se dfinissent l'aide de la mthode
Add de la faon suivante.
Page 32/44
Maj 2008
Add(Type, Operator, Formula1, Formula2)

Type est soit xlCellValue (valeur de la cellule) soit xlExpression (formule n'tant pas contenue dans la
cellule)
Operator peut tre xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween ou
xlNotEqual, il est ignor si le type est xlExpression.
Formula1 est la valeur ou l'expression associe au format conditionnel. Il peut s'agir d'une valeur constante,
d'une chane, d'une rfrence de cellule ou d'une formule.
Formula2 est la valeur ou l'expression associe au second lment du format conditionnel lorsque Operator
vaut xlBetween ou xlNotBetween (sinon, l'argument est ignor). Il peut s'agir d'une valeur constante, d'une
chane, d'une rfrence de cellule ou d'une formule.
N.B : il existe une mthode Modify qui permet de changer une rgle existante.
Pour reprendre le cas qui nous intresse, je veux que le fond des cellules doublonnes soit rouge. J'utilise
alors le code suivant :
With ActiveSheet.Columns(11).FormatConditions
.Add Type:=xlExpression, Formula1:="=NB.SI(C11;LC)>1"
.Item(.Count).Interior.ColorIndex = 3
End With
Dans ce cas, toutes les cellules doublonnes de la colonne 11 auront un fond rouge. C'est l que l'utilisateur
se demande comment rcuprer les cellules ayant un fond rouge et qu'une numration de la plage lui parat
invitable. Pourtant, il ne faut pas poser le problme ainsi. Le format conditionnel sert donner une indication
"visuelle" l'utilisateur. Notons d'ailleurs que le format conditionnel est volatile, il n'est donc pas dtecter
comme un format diffrent lors d'une numration de cellule. Lorsque l'on veut accder la plage des doublons
on doit passer par un tableau, et utiliser le tri. Je vous donne le code ci-dessous
Sub PlageDoublon()
Dim Depart As Long, maplage As Range, TabRes() As Long, MaChaine As
String
Dim montab As Variant, comptX As Long
Application.ScreenUpdating = False
Set maplage = ActiveSheet.Range(Cells(1, 11), Cells(1, 11).End(xlDown))
With maplage.Offset(, -1)
.FormulaLocal = "=ligne()"
.Value = .Value
End With
Set maplage = maplage.Offset(, -1).Resize(, 2)
maplage.Sort maplage.Cells(1, 2), xlAscending
montab = maplage.Value
ReDim TabRes(1 To 1)
For comptX = 2 To UBound(montab, 1) - 1
If montab(comptX, 2) = montab(comptX + 1, 2) Or montab(comptX, 2) =
montab(comptX - 1, 2) Then
TabRes(UBound(TabRes)) = montab(comptX, 1)
ReDim Preserve TabRes(1 To UBound(TabRes) + 1)
End If
Next comptX
Erase montab
maplage.Sort maplage.Cells(1, 1), xlAscending
maplage.Columns(1).ClearContents
Set maplage = maplage.Offset(, 1).Resize(, 1)
maplage.Cells.EntireRow.Hidden = True
If UBound(TabRes) > 1 Then
For comptX = 1 To UBound(TabRes) - 1
maplage.Cells(TabRes(comptX), 1).EntireRow.Hidden = False
Next comptX
Page 33/44
Maj 2008
End If
Set maplage = maplage.SpecialCells(xlCellTypeVisible)
maplage.Interior.ColorIndex = 3
maplage.Cells.EntireRow.Hidden = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Cette fonction est extrmement rapide sur une trs grande plage contenant de nombreux doublons (pour
65000 lments contenant 4700 doublons elle prend environ 9 secondes contre plus de dix minutes pour une
mthode "standard").
AdvancedFilter
Si le but est simplement de rcuprer une plage sans doublon, ce qui est diffrent de ce qu'on a cherch
faire au dessus on utilise alors un filtre particulier AdvancedFilter
Sa syntaxe est la suivante
expression.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)
Action est soit xlFilterInPlace, soit xlFilterCopy. Dans le premier cas le filtre est dit "en place", il
fonctionne alors comme les filtres classiques par masquage des colonnes. Dans l'autre cas, le rsultat apparat sur
une plage diffrente et continue.
CriteriaRange est la plage contenant les critres du filtre, pour plus de renseignements sur son
fonctionnement consultez dans l'aide la rubrique " Exemples de critres pour le filtre labor".
CopyToRange est la plage de destination si le filtre n'est pas en place.
Unique permet justement d'enlever les doublons.
Le code suivant cre donc une plage sans doublon droite de la plage source.
Sub PlageSansDoublons()
Dim maplage As Range, Depart As Long
Application.ScreenUpdating = False
Set maplage = ActiveSheet.Range(Cells(1, 11), Cells(1, 11).End(xlDown))
maplage.AdvancedFilter xlFilterCopy, , maplage.Offset(, 1).Resize(1,
1), True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Notons que pour une plage de 65536 cellules contenant 4700 doublons le traitement prends tout de mme
environ 7 minutes. Un filtre en place serait d'ailleurs plus long. Il peut tre rentable ds lors de crer sa propre
fonction, drive de celle vu auparavant, afin d'acclrer notablement la vitesse de traitement.
Validation
Notons que nous pouvons, dans le cas dun formulaire, limiter les saisies l'aide de l'objet validation. Dans
notre cas le code suivant interdira la possibilit de saisir un doublon.
Public Sub SaisieDoublon()
Dim maplage As Range
Set maplage = ActiveSheet.Columns(11)
With maplage.Validation
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Formula1:="=NB.SI(C11;LC)=1"
.ErrorTitle = "valeur dj existante"
End With
End Sub
Graphique
La mise en forme des graphiques par programme n'est pas trs complique. Nous verrons un exemple de
Page 34/44
Maj 2008
mise en forme un peu complexe la fin de ce chapitre. Le problme majeur de la manipulation des graphiques
est la manipulation des sries.
Cration / ajout de sries

Il y a plusieurs mthodes pour ajouter des sries. La plus simple, mais aussi la moins souple et l'ajout
implicite la cration. En effet si une plage de cellules est slectionne lors de la cration du graphe, Excel trace
les sries automatiquement. Sauf dans certains cas triviaux, cette mthode est viter. Pour regarder l'ajout de
sries je vais prendre le cas du trac nuage de points suivant :
Soit trois colonnes de tempratures qui seront mes ordonnes, et Pression qui sera ma colonne d'abscisses.
Si j'utilise le code suivant, je vais obtenir un trac de quatre sries au lieu des trois sries que je dsire.
Public Sub TestGraphe()
Page 35/44
Maj 2008
Dim MonGraphe As Chart

Range(Cells(1, 1), Cells(99, 4)).Select
ThisWorkbook.Charts.Add
End Sub
Bien entendu, je pourrais rcuprer les valeurs de la premire srie et les mettre comme abscisse de chaque
srie, mais ce ne serait ni clair, ni efficace.
Attention, cela induit qu'Excel essaie toujours d'interprter la slection de la feuille active comme une
plage de donnes. Il faut donc, si on a utilis des slections dans le code antrieur la cration du graphe, veiller
ramener la slection une seule cellule.
Cration par la proprit DataSource
Lorsque la plage est bien positionne, c'est dire avec les abscisses en premire ligne ou colonne, on peut
utiliser cette mthode. Elle prsente l'avantage d'implmenter un grand nombre de sries simultanment.
Dim MonGraphe As Chart, MaPlage As Range
Set MaPlage = Worksheets("donnees").Range(Cells(1, 1), Cells(99, 4))
Set MonGraphe = ThisWorkbook.Charts.Add
MonGraphe.ChartType = xlXYScatter
MonGraphe.SetSourceData MaPlage, xlColumns
End Sub
Cration par ajout
Cette mthode est similaire la prcdente, seulement on ajoute les donnes la collection des sries.
MonGraphe.SeriesCollection.Add MaPlage, xlColumns, True, True
End Sub
Cration par copier / coller
Cette mthode est particulire. Elle consiste crer l'objet graphe au pralable puis de copier la plage de
donnes afin de raliser un collage de sries.
MaPlage.Copy
MonGraphe.SeriesCollection.Paste xlColumns, True, True, True, True
End Sub
Ces deux mthodes sont efficaces, mais utilisables uniquement dans certains cas. Nous allons voir
maintenant des mthodes standards beaucoup plus universelles. Ces mthodes reposent sur un principe diffrent.
On cre d'abord l'objet srie, puis on lui affecte ses valeurs. Cela permet de travailler sur des plages discontinues,
et la position de la colonne contenant les abscisses n'a plus d'importance
Cration par valeur
Dim MonGraphe As Chart, MaPlage As Range, MaSerie As Series, compteur
As Long
Set MaSerie = MonGraphe.SeriesCollection.NewSeries
Page 36/44
Maj 2008
MaSerie.Values = "=" & MaPlage.Columns(compteur).Address(True,

True, xlR1C1, True)
MaSerie.XValues = "=" & MaPlage.Columns(1).Address(True, True,
xlR1C1, True)
MaSerie.Name = "=" & MaPlage.Cells(1).Offset(-1, compteur 1).Address(True, True, xlR1C1, True)
Next compteur
End Sub
Comme nous le voyons, dans ce cas je passe par une cration srie par srie de mon graphe.
Cration par formule
Similaire la mthode prcdente mais un peu moins lisible.
Dim MonGraphe As Chart, MaPlage As Range, MaSerie As Series, compteur
As Long, toto
MaSerie.Formula = "=SERIES(" & MaPlage.Cells(1).Offset(-1, compteur
- 1).Address(True, True, xlR1C1, True) & "," &
MaPlage.Columns(1).Address(True, True, xlR1C1, True) & "," &
MaPlage.Columns(compteur).Address(True, True, xlR1C1, True) & "," &
compteur - 1 & ")"
Next compteur
End Sub
Pour mmoire une formule dune srie suit la syntaxe suivante :
SERIES(Nom,PlageX,PlageY,Ordre)
Modification de sries
La modification est en gnral de deux types : la modification d'une plage ou l'extension de la srie. La
modification de la plage se fait en attaquant une des proprits Values, Xvalues ou Formula. L'extension se fait
avec la mthode Extend. Je ne donnerai pas d'exemple dans ce cas puisque la mthode est rarement utilise.
Srie de plages discontinues

Nous allons voir un cas particulier, qui peut tre utile. Tout d'abord, il faut savoir qu'il n'y a pas obligation
utiliser des plages continues, ni mme des plages de dimensions identiques pour les abscisses et les ordonnes.
Nanmoins, crer par le code une srie de plages discontinues n'est pas vident. Pour tudier ce cas je reprends
mon exemple prcdent, mais en cherchant tracer un point sur 10. Le code suivant fait cela :
Public Sub TestGraphe1()
Dim MonGraphe As Chart, MaPlage As Range, compteur As Long, toto
Set MaPlage = Worksheets("Donnees").Cells(2, 1).Resize(, 4)
For compteur = 1 To Worksheets("Donnees").Cells(2,
1).Resize(Worksheets("Donnees").Cells(2, 1).End(xlDown).Row - 1).Rows.Count
\ 10
Set MaPlage = Application.Union(MaPlage, MaPlage.Offset(compteur *
10 + 1))
Next compteur
End Sub
Pourtant ce code n'est pas utilisable volont, puisque pass un certain nombre de points, Excel ne peut
Page 37/44
Maj 2008
plus crire la formule srie cause d'un trop grand nombre de caractres.
Exemple 1 : cration d'un graphique personnalis

Dans ce premier exemple nous allons crer un graphique compos partir d'un tableau pour obtenir par le
code un graphe personnalis (voir ci-dessous)
Dans ce cas, le tableau est correctement ordonn, nous pouvons donc procder une cration groupe des
sries. Il s'agit d'un graphique superpos, c'est dire possdant deux types de courbes, avec des histogrammes
cumuls et une srie en nuage de points. Plusieurs mthodes de cration seraient possibles, mais dans ce cas, la
plus simple consiste crer un graphique "histogramme cumul" avec toutes les sries, puis agir sur la dernire
srie pour la transformer en nuage de points.
Comme nous l'avons vu prcdemment la fonction commencera ainsi :
Public Sub CreationGraphe1()
MonGraphe.ChartType = xlColumnStacked100
A ce point j'ai cre un graphe de base contenant toutes les sries. Je vais maintenant modifier la srie "total"
afin de pouvoir la mettre en nuage de points et l'affecter l'axe secondaire des ordonnes.
With MonGraphe.SeriesCollection(5)
.ChartType = xlXYScatterSmoothNoMarkers
.AxisGroup = 2
With .Border
.Weight = xlMedium
.LineStyle = xlAutomatic
.ColorIndex = 4
End With
End With
Je vais maintenant mettre les libells des axes et du titre
With MonGraphe
.HasTitle = True
With .ChartTitle
.Characters.Text = "ANNEE 2001"
.Shadow = True
.Border.Weight = xlHairline
End With
With .Axes(xlValue, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = "Proportion"
Page 38/44
Maj 2008
End With
With .Axes(xlValue, xlSecondary)
.HasTitle = True
.AxisTitle.Characters.Text = "Total (hrs)"
End With
End With
Et j'ai obtenu avec une fonction trs courte le graphe dsir.
Exemple 2 : cration d'un graphique complexe

Dans cet exemple nous allons trs largement compliquer la tche en crant un graphique l'aide du tableau
ci-dessous.
Ce style de graphe est assez souvent utilis pour des essais scientifiques. Dans le cas prsent on trace des
rsultats moyens pour une exprience avec leur cart type de reproductibilit. On trace ensuite, les rsultats de la
nouvelle srie d'exprience, (les ronds rouges) ce qui permet de visualiser rapidement une srie de rsultats
anormaux (ce qui est le cas dans cet exemple).
Dans ce cas je vais crer les sries les unes aprs les autres. En effet, pour faire ce graphique de type
StockHLC il faut que les trois premires sries soit dans l'ordre suivant (maximum, minimum, moyenne) ce qui
n'est pas le cas de mon tableau. Ma fonction commencera ainsi :
Public Sub CreationGraphe2()
Dim MonGraphe As Chart, maplage As Range, MaSerie As Series, compteur
As Long
Dim Mini As Single, Maxi As Single
Set maplage = Worksheets("donnees").Range(Cells(20, 7), Cells(25, 13))
Mini = Application.WorksheetFunction.Min(maplage.Columns(1),
maplage.Columns(2))
Mini = Int(Mini / (Int(Log(Mini) / Log(10)) * 10)) * Int(Log(Mini) /
Log(10)) * 10
Maxi = Application.WorksheetFunction.Max(maplage.Columns(1),
maplage.Columns(2))
Maxi = (Int(Maxi / (Int(Log(Maxi) / Log(10)) * 10)) + 1) *
Int(Log(Maxi) / Log(10)) * 10
For compteur = 1 To 3
MaSerie.Values = "=" & maplage.Columns(Choose(compteur, 5, 6,
3)).Address(True, True, xlR1C1, True)
MaSerie.XValues = "=" & maplage.Columns(1).Address(True, True,
xlR1C1, True)
MaSerie.Name = Choose(compteur, "max", "min", "moyenne")
Next compteur
MonGraphe.ChartType = xlStockHLC
Page 39/44
Maj 2008
For Each MaSerie In MonGraphe.SeriesCollection

With MaSerie.Border
.LineStyle = xlContinuous
.Weight = xlThin
If MaSerie.PlotOrder = 3 Then .ColorIndex = 1 Else .ColorIndex
= 5
End With
Next
J'ajoute ensuite la srie de rsultats, que je mets en forme :
With MaSerie
.ChartType = xlXYScatter
.Values = "=" & maplage.Columns(2).Address(True, True, xlR1C1,
True)
.XValues = "=" & maplage.Columns(1).Address(True, True, xlR1C1,
True)
.Name = "resultat"
.MarkerStyle = xlMarkerStyleCircle
.MarkerForegroundColorIndex = 3
.MarkerSize = 10
.ApplyDataLabels xlDataLabelsShowValue, False, True
With .DataLabels
.Position = xlLabelPositionLeft
.Font.ColorIndex = 3
End With
End With
Ensuite je vais faire une opration particulire, qui consiste afficher les labels de la srie "max" puis
modifier leur texte afin d'afficher une chane de la forme "Moyenne Ecart".
Set MaSerie = MonGraphe.SeriesCollection(1)
MaSerie.ApplyDataLabels xlDataLabelsShowLabel
For compteur = 1 To MaSerie.Points.Count
MaSerie.Points(compteur).DataLabel.Text = maplage(compteur,
7).Value
Next compteur
Enfin je supprime les deux entres de la lgende (min et max) et j'ajuste l'axe.
With ActiveChart.Axes(xlValue)
.MinimumScale = Mini
.MaximumScale = Maxi
End With
With MonGraphe.Legend
.LegendEntries(1).Delete
.LegendEntries(1).Delete
End With
End Sub
De voir apparatre deux fois LegendEntries(1).Delete peut sembler tre une erreur, mais cela vient du fait
que la suppression de la premire entre donne le rang un la deuxime. Ceci est une source d'erreur, il convient
donc de faire attention lors de l'utilisation de Delete sur les collections Excel.
Piloter Excel avec Visual Basic 6

Comme nous l'avons vu le pilotage est extrmement simple puisque la programmation est la mme. Il suffit
juste d'ajouter Excel lors du typage des variables, et de dclarer tous les objets Excel utiliss comme variable.
Notons toutefois que dans certains cas on peut s'affranchir de certains objets s'ils sont uniques ou inutiles dans la
procdure. Par exemple, si mon traitement porte sur une seule feuille de calcul je pourrais utiliser la fonction
suivante :
Page 40/44
Maj 2008
Private Sub TraiteExcel()

Dim MonExcel As Excel.Application, MaFeuille As Excel.Worksheet
MonExcel.Workbooks.Add xlWBATWorksheet
Set MaFeuille = MonExcel.ActiveSheet
Comme nous le voyons, je n'ai pas utilis d'objet Workbook, puisque ma cible sera uniquement une feuille
de calcul.
Rgles gnrales
Cohrence des rfrences
En gnral, on programme le pilotage en utilisant un seul style de rfrence (soit A1 soit L1C1). Il convient
de n'utiliser qu'un seul style tout au long du programme et de mettre l'option sur ce style au dmarrage. Ainsi si
je travaille en mode L1C1, je devrais trouver au dbut de ma procdure :
MonExcel. ReferenceStyle = xlR1C1
N'oubliez pas que pour transformer les valeurs de colonnes, de lettres en nombres ou inversement, il suffit
d'utiliser les proprits columns ou address.
NumCol = MaFeuille.Columns("CE").Column
LetCol = MaFeuille.Columns(83).Address(True, False)
Quitter proprement
En fin de procdure, et avant de fermer Excel, pensez toujours dtruire vos variables d'objets Excel. Ne
pas le faire peut engendrer une erreur. Dans mon premier exemple une sortie correcte serait :
MonExcel.Quit
End Sub
De mme mfiez-vous de la dsactivation des messages d'alertes.
Communiquer avec Excel

Le mode de communication le plus utilis est la communication directe, c'est dire la possibilit de lire ou
d'crire dans des objets Excel partir du programme VB. Cette communication, se fait uniquement par
l'intermdiaire des objets que le composant Excel fournit.
Il faut tout d'abord savoir qu'Excel est un serveur Out-of-process, c'est dire qui ne s'excute pas dans le
mme espace mmoire que votre application. Ceci fait que vous pouvez trs bien le grer de faon asynchrone,
et lui faire excuter des tches pendant que votre propre application travaille. Pour pouvoir utiliser cette facult,
on utilise les notifications asynchrones, autrement dit l'interception d'vnements. Un autre mode de
communication possible est l'utilisation du presse-papiers.
Communication directe
Celle ci reprend tous les exemples que nous avons vus prcdemment. Elle consiste en quelques sortes
crire une macro dans le code Visual Basic. Nous regarderons plus loin, l'utilisation d'objets fournis par Excel
pour faire de la programmation de VB. Voici un exemple classique de rcupration de donnes Excel :
Dim MonExcel As Excel.Application, MonClasseur As Excel.Workbook, MaFeuille
As Excel.Worksheet
Dim MonTab As Variant, MaPlage As Excel.Range
MonExcel.ReferenceStyle = xlR1C1
Set MonClasseur =
MonExcel.Workbooks.Open("D:\User\jmarc\tutorial\excel\tutor1.xls")
Set MaFeuille = MonClasseur.Worksheets("pilotage")
Set MaPlage = MaFeuille.UsedRange
Page 41/44
Maj 2008

MonTab = MaPlage.Value
MonExcel.Quit
End Sub
Vous noterez que la variable "montab" est dclare comme un variant et nom comme un tableau typ, sinon
il y a le risque d'avoir une erreur lors de l'affectation. Notez que la ligne de redimensionnement est facultative.
Utiliser le presse-papiers
Vous pouvez faire communiquer votre application avec Excel par l'intermdiaire du presse-papiers. Si je
reprends le cas prcdent avec :
MaPlage.Copy
MonTab = Clipboard.GetText
Je rcupre une chane qui reprsente le tableau Excel. Ces chanes sont toujours spares avec des
tabulations pour les colonnes et un retour chariot pour les lignes. La seule diffrence notable dans les deux cas et
donc l'utilisation d'une chane ou d'un tableau.
Interception des vnements
Nous avons vu au dbut de cet article le codage de l'interception des vnements. Sachez toutefois que pour
que celui ci fonctionne correctement il faut utiliser des DoEvents dans votre code Visual Basic. Certains
vnements Post-opration (comme calculate) se prte particulirement bien la communication VB Excel.
WorksheetFunction
Un des avantages lors du pilotage d'Excel est de pouvoir accder l'objet WorksheetFunction dans le code
Visual Basic. Certaines de ces fonctions acceptent comme arguments des variables n'tant pas de type Range et
sont donc directement utilisables. Prenons l'exemple suivant :
Dim MonExcel As Excel.Application, TabNombre(1 To 100) As Integer
Dim compteur As Long, Moyenne As Single, Maxi As Integer, Mini As Integer
TabNombre(compteur) = compteur
Next compteur
Moyenne = MonExcel.WorksheetFunction.Average(TabNombre)
Maxi = MonExcel.WorksheetFunction.Max(TabNombre)
Mini = MonExcel.WorksheetFunction.Min(TabNombre)
MonExcel.Quit
End Sub
J'utilise ainsi Excel comme une bibliothque de fonctions supplmentaires pour Visual Basic..
Fonction renvoyant un tableau
Certaines fonctions renvoient un tableau, comme la fonction Frquence que nous allons voir. Celle-ci attend
comme arguments un tableau d'origine et un tableau d'intervalles. Elle renvoie le tableau des distributions. On
dclare le tableau de destination comme Variant.
Dim MonExcel As Excel.Application, MonTab As Variant, MesInter(2) As
Integer
Dim TabNombre(1 To 100) As Integer, compteur As Long
If compteur Mod 2 = 0 Then TabNombre(compteur) = compteur Else
TabNombre(compteur) = 5
Next compteur
MesInter(0) = 10
Page 42/44
Maj 2008
MesInter(1) = 100
MonTab = MonExcel.WorksheetFunction.Frequency(TabNombre, MesInter)
MonExcel.Quit
End Sub
Attention, quelques fonctions attendent imprativement une plage, si on a besoin de les utiliser, il convient
alors de transfrer les donnes sur une feuille Excel. Comme ceci est assez lourd, essayez toujours de chercher
une mthode de contournement.
Correction orthographique
Nous allons voir un exemple simple d'utilisation dans VB du correcteur orthographique d'Excel.
Private Function TrouveFaute(PhraseTest As String) As Boolean
Dim MonExcel As Excel.Application
TrouveFaute = Not MonExcel.CheckSpelling(PhraseTest)
MonExcel.Quit
End Function
Astuces diverses
Envoyer un classeur par mail
A condition d'avoir un systme de messagerie install par dfaut, on peut envoyer directement un classeur
par Mail. Le code VBA correspondant est :
Public Sub EnvoiClasseur()
If IsNull(Application.MailSession) Then Application.MailLogon "username",
"motdepasse"
ThisWorkbook.SendMail Recipients:="[email protected]"
End Sub
Bloquer les actions clavier & souris

On peut, pendant l'excution d'un code assez long, bloquer le clavier et la souris afin que l'utilisateur ne
puisse pas agir sur Excel pendant que celui ci travaille. Nanmoins, comme la mthode prsente un risque il faut
imprativement avoir un contrle d'erreurs afin de pouvoir ractiver le clavier et la souris en cas de problme.
Public Sub LongTraitement()
On Error GoTo restauration
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.Interactive = False
End With
'dbut du traitement long
restauration:
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.Interactive = True
End With
End Sub
On utilise trs souvent le mode Interactive False en pilotage VB. Le blocage du clavier n'empche pas la
saisie dans les UserForms.
Page 43/44
Maj 2008
Ajouter du code l'excution

Pour finir cet article nous allons voir une mthode permettant d'ajouter un bouton l'excution et d'crire
l'vnement correspondant pendant l'excution. Cette mthode extrmement puissante, puisqu'elle permet
d'ajouter du code la vole n'est pas sans risque, donc faite attention avant de l'utiliser.
Tout d'abord il faut ajouter au projet la rfrence MS Visual Basic for application "x.x" (Vbeext1.olb). Je
vous met x.x car la version dpend de la version d'Excel.
Dans l'exemple qui suit, je vais ajouter un bouton de commande la feuille et lui crer sa procdure
d'vnement click.
Public Sub AjoutBouton()
Dim MaFeuille As Worksheet, MonBouton As Shape, PosLigne As Integer
Set MaFeuille = ThisWorkbook.Worksheets("pilotage")
Set MonBouton =
MaFeuille.Shapes.AddOLEObject(ClassType:="Forms.CommandButton.1",
Left:=100, Top:=100, Width:=100, Height:=200)
MonBouton.Name = "CommandButton1"
With ThisWorkbook.VBProject.VBComponents("Feuil4").CodeModule
.CreateEventProc "Click", "CommandButton1"
PosLigne = .ProcStartLine("CommandButton1_Click", vbext_pk_Proc)
.InsertLines PosLigne + 3, "msgbox " & Chr(34) & "nouveau bouton" &
Chr(34)
End With
End Sub
Conclusion
Comme nous l'avons vu la programmation d'Excel n'est pas trs complique, encore faut il tre rigoureux.
Gardez toujours l'esprit qu'il faut toujours privilgier la vitesse d'excution celle-ci tant le problme majeur du
VBA Excel. Dans de nombreux cas, il y a plusieurs moyens pour arriver au mme rsultat, n'hsitez pas tester
les diverses mthodes que vous imaginez.
Bonne programmation.
Page 44/44

Vba Excel

Transféré par

Droits d'auteur :

Formats disponibles

Vba Excel

Transféré par

Informations du document

Description originale:

Titre original

Copyright

Formats disponibles

Partager ce document

Partager ou intégrer le document

Options de partage

Avez-vous trouvé ce document utile ?

Ce contenu est-il inapproprié ?

Droits d'auteur :

Formats disponibles

Vba Excel

Transféré par

Droits d'auteur :

Formats disponibles

VBA-Programmer efficacement Excel

VBA : Programmer efficacement Microsoft Excel

VBA-Programmer efficacement Excel

Bien savoir quel est l'objet actif

VBA-Programmer efficacement Excel

Intercepter les vnements Excel en Visual Basic

VBA-Programmer efficacement Excel

La base, l'objet Application

VBA-Programmer efficacement Excel

SheetsInNewWorkbook : Permet de dfinir le nombre de feuilles contenues dans un nouveau classeur.

VBA-Programmer efficacement Excel

Wait : Marque une pause dans l'excution de la macro.

Collections et objets particuliers

VBA-Programmer efficacement Excel

fichier listeVBA.xls ou avec l'explorateur d'objets.

L'objet WorkBook (classeur)

Quelques mthodes connatre

VBA-Programmer efficacement Excel

BeforeClose, BeforePrint, BeforeSave

SheetCalculate, SheetChange, SheetSelectionChange

Les Feuilles (Sheets)

VBA-Programmer efficacement Excel

Feuille de calcul (WorkSheet)

VBA-Programmer efficacement Excel

Evnements de l'objet WorkSheet

Plage et cellule (Range)

VBA-Programmer efficacement Excel

En mode A1 on utilise un range comme suit :

Proprits ne renvoyant pas un objet - collection

VBA-Programmer efficacement Excel

Proprits renvoyant un objet

VBA-Programmer efficacement Excel

Les commandes suivantes renvoient respectivement

Dependents / DirectDependents/ Precedents / DirectPrecedents

regardons le code suivant :

VBA-Programmer efficacement Excel

Debug.Print raTest.Address(True, True, xlR1C1)

VBA-Programmer efficacement Excel

Clear / ClearContents / ClearFormats

VBA-Programmer efficacement Excel

FillDown, FillLeft, FillRight,FillUp

Find & FindNext

VBA-Programmer efficacement Excel

Set Reponse = MaFeuille.Cells.Find(MonCritere, MaFeuille.Cells(1, 1),

VBA-Programmer efficacement Excel

Ligne ou colonne entire

Objets graphiques (Chart & ChartObject)

Collection Charts & ChartObjects

VBA-Programmer efficacement Excel

HasAxis, HasLegend, HasTitle

Les objets constituants

VBA-Programmer efficacement Excel

VBA-Programmer efficacement Excel

Les rgles de bases

VBA-Programmer efficacement Excel

Utilisation d'un modle

VBA-Programmer efficacement Excel

Set MaPlage = Application.Intersect(Range("AjoutX"), Target)