21

Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet

Programmation VBA/Excel
Programmation VBA
Pierre BONNET
22
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
La prora!!ation "B#
$istori%ue!ent& la prora!!ation sous '()el avait )o!!e *on)tion d+auto!atiser
une su))ession d+a)tions *aites dans la *euille , l+aide de la souris.
-+o. la notion de Ma)ro '()el
Le lanae initial /tait sp/)i*i%ue 0versions en #nlais et en 1ran2ais3
# partir d+'()el 45& a6andon du lanae sp/)i*i%ue pour un lanae uni%ue
adoptant la s7nta(e du Basi) 8
"isual Basi) *or #ppli)ation "B#
9+est un enri):isse!ent de "B par des *on)tions sp/)i*i%ues , ):a%ue appli)ation
0'()el& ;ord& #))ess....3.
'()el )o!prend tous les outils d+/)riture et d+e(/)ution de "B& 7 )o!pris les
possi6ilit/s d+e(tension ave) des 6i6liot:<%ues ou =)ontr>les= suppl/!entaires.
La di**/ren)e essentielle est %ue l+a**i):ae se *ait dans les *euilles d+un )lasseur
23
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
#))<s , la prora!!ation "B# #lt 111
2?
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
"B#@"BA
"B# '()el
"B# '()el
RunTime VBA
RunTime VBA DLL, OCX
DLL, OCX
"BA
"BA
RunTime VB6
RunTime VB6
Appel de
fonctions
excutables
Biblioth!ue
de base Office
Biblioth!ue
de fonctions utilisateu"
Biblioth!ue de base VB6
!sv6v!A0.dll
1ra!e;orB .C'T
"B# '()el
"B# '()el
Visual #tudio
Tools fo" Office
Visual #tudio
Tools fo" Office
"isual Studio 200D
"isual Studio 200D
$achine %i"tuelle CLR
Common Lan&a&e RunTime
'"ame(o") *net
'"ame(o") *net
Code inte"mdiai"e
$#+L
,n%i"onnement de
d%eloppement
VB*-,T, C., C/
/*-,T, 0a%a,
0., 12thon***
RunTime VBA
RunTime VBA
Biblioth!ue
de base Office
:ttp8@@*r.EiBipedia.or@[email protected]'T
:ttp8@@!orp:eus.developpeF.)o!@vsto2@
2D
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
"aria6les
Dcla"ation implicite
Par d/*aut& l+usae d+une varia6le dans une a**e)tation tient lieu de d/)laration 0)o!!e en Matla63
Le t7pe a**e)t/ est d/ter!in/ auto!ati%ue!ent par l+/valuation de la partie droite de l+/valuation
-aner de l+a6sen)e de d/)laration 8 toute erreur de *rappe du no! d+une varia6le )r/e une nouvelle
varia6leG
Dcla"ation explicite
HtiliseF la dire)tive en d/6ut de prora!!e Option Explicit
Toute d/)laration a la stru)ture 8 Dim Nom_Variable as Type_Variable
1o"te de la dcla"ation
Hne varia6le n+est visi6le %ue pour la *euille o. elle est d/)lar/e .
Pour une visi6ilit/ des autres *euilles@)lasseur& utiliser Public Nom_Variable as Type_Variable
1a"ticula"it 3 La notion de pointeur n+e(iste pas en "B@"B# d+o. )o!!uni)ation i!possi6le ave) des
*on)tions %ui utilisent )ette appro):e pour le dialoue
2A
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
"aria6les
Boolean
Prend les valeurs True et 1alse. Par )onversion depuis d+autres t7pes nu!/ri%ues& la valeur 0
devient 1alse et toutes les autres deviennent True. -ans le )as inverse& 1alse devient 0 et True
devient -1.
9onversion d+une donn/e nu!/ri%ue en Boolean par la *on)tion 96ool
e(e!ple 8 A = CBool(1! "A pren# la $aleur True%
B2te, +nte&e", Lon&
T7pes de donn/es entiers I& 1A ou 32 6its .
9onversion d+ une donn/e nu!/ri%ue en B7te& utiliseF la *on)tion 9B7te& ide! pour les autres
t7pes .
#ttention 8 l+adressae des lines (ls doit se *aire en t7pe lon 0valeur /ventuelle!ent sup/rieure ,
32 5A53
#in&le, Double
9ara)t/rise les no!6res r/els 0ou *lottants3 si!ple et dou6le pr/)ision
25
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
"aria6les
#t"in& J Les ):aKnes de lonueur *i(e peuvent )ontenir de 1 , 2L1A )ara)t<res.
Dim ma_c&aine as strin'
ma_c&aine = (Bon)our(
*s'Box ma_c&aine
Mu 6ien Dim ma_c&aine+

Il e(iste en "B# toutes les *on)tions de )onversion no!6re N--O ):aKne
voir str et $al
)on)at/nation de ):aKne 8 ma_c&aine = (Bon( , ()our(
ma_c&aine = (Amplitu#e -( . $al(si'nal!
2I
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
Tableaux Tous les t7pes peuvent Ptre /tendus en ta6leau
Dim Tableau1(1! as #ouble
Tableau(/! = /%10112
Dim *atrice34(156! as Boolean "#imension 7 au maximum
*atrice34(85/! = True
L+indi)e de d/6ut est i!pli)ite!ent 0 par d/*aut & 1 en utilisant la d/)laration Option Base 1 ou en
sp/)i*iant les indi)es 8
Dim *onTableau(1 To 15 1 To 8! As 9trin'
Re):er):e des indi)es !in et !a( par les *on)tions :Boun# et ;boun#
NbElements = (;boun#(*atrice3451!<:Boun#(*atrice3451!,1!=(;boun#(*atrice3458!%%%
%%% <:Boun#(*atrice3458!,1!
Ta6leau d7na!i%ue ave) redi!ensionne!ent
Dim >arnau'&(! as boolean
?eDim >arnau'&(8!
attention& les an)iennes valeurs sont e**a)/es 0utiliser ?eDim Preser$e3
le t7pe ne peut pas Ptre !odi*i/ 0sau* dans le )as d+un ta6leau de $ariant3

Le t7pe #rra7 per!et de )r/er une liste de valeurs Qde t7pe ta6leau de variantR
*onTableau = Array((a(5 (b(5 (c(!
24
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
"aria6les
Va"iant
"ariant est le t7pe de donn/es attri6u/ , toutes les varia6les %ui ne sont pas e(pli)ite!ent
d/)lar/es )o!!e /tant d+un autre t7pe 0, l+aide d+instru)tions telles %ue -i!& Private& Pu6li) ou
Stati)3. Le t7pe de donn/es "ariant ne poss<de au)un )ara)t<re de d/)laration de t7pe.
"ariant est un t7pe de donn/es sp/)ial pouvant )ontenir des donn/es de toutes sortes& ,
l+e()eption des donn/es de t7pe Strin de lonueur *i(e.
La valeur '!pt7 d/sine une varia6le de t7pe "ariant %ui n+a pas /t/ initialis/e 0)+est-,-dire ,
la%uelle au)une valeur initiale n+a /t/ a**e)t/e3. Hn variant peut aussi )ontenir la valeur Cull ou
'rror
Lors%u+un variant )ontenant une ):aKne est utilis/ dans une op/ration re%u/rant un t7pe
nu!/ri%ue& le variant est auto!ati%ue!ent )onverti en no!6re.
'(e!ple8
Dim *yVar As Variant
*yVar = (8(
Cells(15 1! = *yVar , 1
La valeur a**i):/e dans la )ellule est 30
30
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
"aria6les
Date 4t"s impo"tant pou" la &estion des donnes indust"ielles ho"odates5
Les dates sont )o!prises entre le 1er Sanvier 1400 et le 31 d/)e!6re 4444& les :eures allant de 0800800
, 238D48D4.
Le t7pe date est sto)B/ dans un dou6le. La partie enti<re )orrespond au no!6re de Sours /)oul/s depuis
le 1er Tanvier 1400U la partie *ra)tionnaire )orrespond , la *ra)tion de Sour /)oul/e 01: V 1@2? de Sour&
1!n V 1@02?(A03 de Sour & 1s V1@02?(A0(A03 de Sour 3.
'(e!ple 8
Dim ma_#ate As Date
Dim mon_&eure5 ma_#ate&eure As Double
ma_#ate = (17@2@8A(
mon_&eure = %1 "une #emi<)ournBe #e plus soit 18C
ma_#ate&eure = ma_#ate , mon_&eure
Cells(/5 1! = ma_#ate&eure
donne )o!!e r/sultat 1A@04@0I 12800 PM 0d/pend du *or!at d+a**i):ae ):oisi3
- la )onversion de t7pe est i!pli)ite depuis le *or!at ):aKne.
- utiliseF la *on)tion Is-ate pour savoir si une valeur variant peut Ptre )onvertie en date ou en :eure.
- "B# propose des *on)tions de )onversion 0-ateSerial & Ti!eSerial3
31
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
"aria6les
T2pe utilisateu"
Hn t7pe utilisateur d/*init une stru)ture )ontenant les t7pes de 6ase.
'(e!ple8
-/)laration du t7pe
Public Type Dic&eEtu#iant
Nom As 9trin'=/ "$ariable type 9trin' contenant le nom%
DateNaissance As Date "$ariable type Date contenant la #ate #e naissance %
9exe As Boolean "$ariable type Boolean #BEinissant le sexe
En# Type (Dalse=EBm5True=masc%!%
Hsae du t7pe 8
Dim NeFEtu#iant as Dic&eEtu#iant
NeFEtu#iant%Nom = (Dupont(
NeFEtu#iant%DateNaissance = (81@1@A8(
NeFEtu#iant%9exe = True
ou 6ien 8
Dim NeFEtu#iant as Dic&eEtu#iant
Git& NeFEtu#iant
%Nom = (Dupont(
%DateNaissance = (81@1@A8(
%9exe = True
En# Git&
32
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
"aria6les
T2pe ob6et
Hn o6Set Qou )lasseR est une varia6le stru)tur/e per!ettant de !anipuler des )on)epts /volu/s. Les
valeurs asso)i/es , un o6Set s+appellent des para!<tres & les *on)tions appli%u/es , un )lasse sont des
!/t:odes
Pour pouvoir utiliser un o6Set& il *aut )o!!en)er par le d/)larer puis l+instan)ier 8
Dim monOb)et As *aClasse
9et monOb)et = NeF *aClasse
monOb)et%param1 = 1
monOb)et%AEEic&er %%%
Mu Dim monOb)et As NeF *aClasse
%%%
Lors%ue lWo6Set nWest pas instan)i/ !ais seule!ent d/)lar/& il est alors /%uivalent , =Cot:in= .

-estru)tion d+un o6Set 9et monOb)et = Not&in'
Il e(iste de no!6reu( o6Sets pr/d/*inis dans '()el 8 GorHBooH5 ?an'e

33
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
"aria6les
7i"a"chie des p"incipaux ob6ets ,xcel
#ppli)ation
;orB6ooBs
;orBBooB
;orBs:eets
;orBS:eet
Rane& 9ells
'(e!ple de r/*/ren)e!ent 8

Excel%GorHbooHs(1!%9&eets(1!%?an'e((A1(!%Value = (salut(
L+o6Set 9ell n+e(iste pas au sens stri)t en '()el
R/*/ren)e 8 :ttp8@@rdorat.*ree.*r@'nseine!ent@"B#@"B#@M6S'()el.:t!l

3?
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
Instru)tions de )ontr>le
If (con#ition! Then
instruction 1
instruction 8
%%%
Else
instruction 1
%%%
End If
If (con#ition! Then
instruction 1
%%%
Elseif (con#ition! Then
instruction 1
%%%
End If

For ($ariable! = (#Bbut! To (Ein!
instruction 1
%%%
Next [nom de la variable d'incrment]
'(pression des )onditions
opBrateur #e comparaison
a = b a I= 1 a IJ /
ma_c&aine = (Bon)our(


opBrateur lo'iKue
(a = b! AND (a J= 1!
( a = b! O? NOT( a I= 1!
( a IJ b ! 3O? (a = c!
3D
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
Instru)tions de )ontr>le
Do LwhileMuntilN (con#ition! ou hile (con#ition!
instruction 1 instruction 1
%%% %%%
!oo" end
Do
instruction 1
%%%
!oo" LwhileMuntilN (con#ition!
#elect $ase ($ariable!
case $aleur1
instruction 1
%%%
case $aleur8
instruction 1
%%%
case $aleur/ To $aleur0
instruction 1
%%%
case Os J= $aleur1
instruction 1
%%%
case else
instruction 1
%%%
End #elect

3A
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
Ma)ros
Hne !a)ro est une pro)/dure dont l+e(/)ution est lan)/e depuis 8
- le !enu Mutils@Ma)ro@'(/)uter
- par un ra))our)i )lavier
- par un 6outon de la 6arre d+outils *or!ulaire
- une i!ae
- un 6outon de la 6arre d+outils
Le )ode "B# d+une !a)ro peut Ptre /n/r/ auto!ati%ue!ent par l+enreistreur de !a)ro
35
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
Pro)/dures
Hne pro)/dure )o!!en)e par 9ub et ter!ine par En# 9ub. 'lle e(/)ute des
a)tions !ais ne renvoie pas de valeurs .
'(e!ple sans aru!ent8 pro)/dure de re)opie la )ellule a)tive 1 line vers le 6as et 0 )olonne
vers la droite& puis s/le)tion de )ette nouvelle )ellule
#ub Copie_Decale(!
Acti$eCell%Copy Acti$eCell%OEEset(15!
Acti$eCell%OEEset(15!%9elect
End #ub
'(e!ple ave) aru!ent d+entr/e8 les r/*/ren)es de la )ellule , d/)aler sont pass/es en
para!<tre de t7pe ):aKne .
#ub Copie_Decale_bis(A#ressePla'e As 9trin'!
Dim *a_Pla'e As ?an'e
9et *a_Pla'e = ?an'e(A#ressePla'e!
*a_Pla'e%Copy Pla'e%OEEset(15!
*a_Pla'e%OEEset(15!%9elect
End #ub
Pour e(/)uter )ette pro)/dure& taper 9opieX-e)aleX6is0=93=3 dans la *enPtre ,xcution
3I
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
1on)tions
Hne *on)tion a))epte un ou plusieurs aru!ents en entr/e et retourne un aru!ent .
'lle )o!!en)e par Function et se ter!ine par End Function
'(e!ple8 *on)tion retournant le )ode )ouleur de la )ellule dont les )oordonn/es sont pass/es en
para!<tre de t7pe ):aKne. La *on)tion retourne une ):aKne .
Public Function Co#e_Couleur(A#ressePla'e As 9trin'! As 9trin'
Dim *a_Pla'e As ?an'e
Dim On#ex as inte'er
9et *a_Pla'e = ?an'e(A#ressePla'e!
On#ex = *a_Pla'e%Onterior%ColorOn#ex
Co#e_Couleur = (Couleur #e la cellule ( . A#ressePla'e . ( - ( .
str(On#ex!
End Function
Pour e(/)uter )ette *on)tion& taper ran'e((B/(! = Co#e_Couleur((A/(! dans la *enPtre
,xcution. Le r/sultat est donn/ dans la )ellule B3 .
34
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
Pro)/dures@1on)tions
Passae de para!<tres
Par valeur 0appel par d/*aut38 9+est la valeur de la varia6le %ui est pass/e dans la *on)tion. Tout
)al)ul sur )ette valeur est local , la *on)tion et n+a**e)te pas la valeur dans la stru)ture appelante.
a = 1
increment(a!
*s'Box a "la $aleur aEEic&Be est 1
%%%
9ub increment (ByVal $ar1 as Onte'er!
$ar1 =$ar1 , 1 " $ar1 pren# la $aleur 1
En# 9ub
Par r/*/ren)e 8 le para!<tre pass/ est l+adresse de la varia6le. Il en r/sulte %ue toute !odi*i)ation
de la valeur dans la *on)tion a**e)te la valeur de la varia6le dans la stru)ture appelante
a = 1
increment(a!
*s'Box a "la $aleur aEEic&Be est 7
%%%
9ub increment (By?eE $ar1 as Onte'er!
$ar1 =$ar1 , 1 " $ar1 pointe sur a
En# 9ub
?0
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
'(er)i)e 3
1aire le )al)ul de la varian)e d+un ta6leau de donn/es '()el et l+a**i):er dans une 6oite de
dialoue 0voir suite transparents3. L+inter*a)e de saisie est une *euille '()el
Le )ontenu du ta6leau est ran/ , partir de la )ellule #D & le n6 de lines en B2 et le no!6re de
)olonnes en B3.
13 1aire un prora!!e 6asi%ue de )al)ul en "B# )o!prenant la r/)up/ration des valeurs& le )al)ul puis
l+a**i):ae
23 Isoler le )al)ul dans une fonction #TD4mon8tableau5 dont le para!<tre d+entr/e est le seul no! de
varia6le du ta6leau 0utiliser un ta6leau d7na!i%ue& re):er):er les di!ensions du ta6leau d+entr/e et
appli%uer le )al)ul3
33 Mn sou:aite %ue le ta6leau soit dire)te!ent s/le)tionn/ , la souris. Htiliser les propri/t/s de
l+o6Set Sele)tion pour r/)up/rer les donn/es.
A5
?1
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
#utres 1on)tionnalit/s
Envoyer un message d'avertissement
Dim *on *essa'e as strin'
*on*essa'e = (Vous n"Ptes pas autorisB
Q mener cette action(,C&r+(1/!,
(OpBration annulBe(
*s'Box *on*essa'e
Envoyer un message avec rponse type choix
Dim *on*essa'e5 Titre As 9trin'
Dim 9aisie As Onte'er

*on*essa'e = (:ancement Ompression R(
Titre = (Ompression ?apport(
9aisie = *s'Box(*on*essa'e5 $bO>Cancel5 Titre!
OE 9aisie = $bO> T&en *s'Box(En cours(

?2
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
#utres a)tions
Saisie d'une valeur
Dim 9aisie as Variant
9aisie = OnputBox((9aisir la #urBe en s(5(test systSme(!
#TT'CTIMC 8 Les *on)tions MsBo( et InputBo( sont 6lo)antes
Sus%u+, r/ponse de l+op/rateur.
?3
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
Htilisation de Hser1or!
per!et de )r/er une *enPtre dans une appli)ation& ind/penda!!ent des *euilles '()el.
'n "B pur & la *enPtre est l+/l/!ent de 6ase de l+I$M
??
Masters SMaRT & GSI - Supervision Industrielle - 2012-2013 P. Bonnet
Programmation VBA/Excel
Htilisation de Hser1or!
les )ontr>les usuels 0)urseur& )ase , )o):er& *enPtre te(te...3 peuvent Ptre ins/r/s
dans un Hser1or!
Par prora!!ation& toutes les propri/t/s des )ontr>les sont a))essi6les& ainsi %ue les
)ellules des *euilles '()el 0la valeur d+un )ontr>le peut Ptre li/ dire)te!ent , )elle
d+une )ellule3
par d/*aut& le Hser*or! n+est pas a**i):/. Pour le *aire passer en pre!ier plan& il
*aut utiliser la !/t:ode 8
*y_;serDorm% 9&oF