WorksheetFunction: Quand et pourquoi l’utiliser

En plus des fonctions spécifiques de VBA, il est possible d'utiliser en VBA certaines fonctions d'Excel – c'est-à-dire les fonctions que l'on peut utiliser dans les cellules dans un classeur Excel comme par exemple RECHERCHEV ou encore SOMME et qui n'ont pas leur équivalents en VBA. Deux possibilités s'offrent donc à nous: soit réécrire les fonctions en question en code VBA (ce qui peut être compliqué et fastidieux), soit on se sert directement dans Excel (sans tout de même passer par des cellules!). En VBA, nous pouvons faire appel à ces fonctions en utilisant l'objet WorksheetFunction.

Voici donc un aperçu des avantages & désavantages de ces fonctions, de leur utilisation en pratique (code VBA) et également la liste de toutes les fonctions Excel accessibles en VBA via WorksheetFunction (le tableau se trouve en bas de ce tutoriel).

Avantages et désavantages de l'utilisation des WorksheetFunction's

L'utilisation de WorksheetFunction est plus appropriée dans certaines situations que dans d'autres. En prenant en considération les éléments qui suivent, vous serez en mesure de savoir quand il est opportun de l'utiliser et quand il vaut mieux chercher une autre solution.

Le majeur avantage qu'offrent les fonctions Excel utilisées directement en VBA est le nombre de fonctions disponibles sans devoir écrire du code VBA. L'utilisation de n'importe laquelle de ces fonctions requiert une seule ligne de code VBA (voir plus bas la section "Utilisation de WorksheetFunction en VBA"). Vous pouvez donc profiter de la fonction RECHERCHEV avec une seule ligne de code VBA… Ou bien, comme mentionné plus haut, vous pouvez écrire en VBA votre propre fonction qui simulera la même fonctionnalité – mais n'imaginez pas que cela se fera avec une ligne de code.

Un autre avantage: grâce à des dizaines de fonctions Excel disponibles, toutes les thèmes sont couverts: dates et temps, mathématique, finance, statistiques, conversions,…


Parmi les désavantages, j'en citerais deux. Un pratique et un beaucoup plus essentiel…

1) Contrairement aux fonctions spécifiques de VBA, l'éditeur VBA (le "VBE") ne donne pas de détails concernant les argument des fonctions Excel. Vous aurez droit au nombre des arguments que la fonction utilise et à l'information si l'argument est obligatoire (sans crochets, par ex.: Arg1 ) ou optionnels (avec crochets, par ex.: [Arg2] ). Vous devez donc trouver vous-mêmes quels arguments et dans quel ordre la fonction utilise – pour cela, il vous suffit de regarder cette fonction directement dans Excel (et non dans l'éditeur VBA).

Pour illustration, voici ce que vous indiquera l'éditeur VBA à propos de la WorksheetFunction VLookup (=RechercheV): 4 arguments dont le dernier est optionnel

WorksheetFunction: Arguments en éditeur VBA

WorksheetFunction: Arguments en éditeur VBA

En regardant dans Excel, vous verrez que Arg1 correspond à la valeur recherchée, l'Arg2 représente la plage dans laquelle les données sont recherchés, l'Arg3 est le numéro de colonne qui contient le résultat recherché et dans Arg4, il faut indiquer, le cas échéant, si oui ou non il faut utiliser les valeurs approximatives. Attention: contrairement à la formule RECHERCHEV dans la version francophone d'Excel, les valeurs à indiquer dans VBA sont TRUE ou FALSE et non VRAI ou FAUX !

Ce désavantage est plutôt cosmétique et elle impacte surtout l'écriture du code VBA – mais elle n'influence pas la fonctionnalité de votre code. Ce qui n'est pas le cas pour l'autre désavantage sur lequel j'aimerais attirer votre attention.

2) Comme il s'agit des fonctions Excel, le code VBA contenant un objet WorksheetFonction ne fonctionnera que s'il tourne dans Excel. Cela ne vous posera pas de problème si vous travaillez exclusivement avec la combinaison VBA/Excel. Par contre, si vous prévoyez utiliser votre code dans d'autres applications qu'Excel (par exemple Word ou Outlook), vous ne pourrez pas utiliser cette fonctionnalité – car les fonctions Excel ne sont tout simplement pas accessibles dans, par exemple, MS Outlook.

Utilisation de WorksheetFunction en VBA (syntaxe)

L'utilisation est très simple. Vous faites l'appel à une fonction Excel avec le code suivant:

NomDeFonction est le nom de la fonction que vous voulez utiliser (voir la liste plus bas). Et ArgumentDeFonction1, … , ArgumentDeFonctionX représentent les valeurs des arguments de la fonction Excel utilisée (le nombre des arguments et leur nature obligatoire/facultatif varient selon la fonction).

Remarque: si vous voulez profiter des fonctions Excel dans VBA, vous devez utiliser les formes anglophones de ces fonctions! Par exemple, pour utiliser la formule RECHERCHEV en VBA, il vous faudra utiliser le terme VLOOKUP. Si vous essayez utiliser comme nom de fonction "RECHERCHEV", votre code VBA se générer une erreur…

 

Liste de toutes les WorksheetFunction disponibles en VBA

Et voici la liste de toutes les fonctions Excel que vous pouvez utiliser telles quelles en VBA (en anglais, par ordre alphabétique):

Liste des WorksheetFunction (cliquez pour afficher)
AccrInt
AccrIntM
Acos
Acosh
Aggregate
AmorDegrc
AmorLinc
And
Application
Asc
Asin
Asinh
Atan2
Atanh
AveDev
Average
AverageIf
AverageIfs
BahtText
BesselI
BesselJ
BesselK
BesselY
BetaDist
BetaInv
Bin2Dec
Bin2Hex
Bin2Oct
Binom_Dist
Binom_Inv
BinomDist
Ceiling
Ceiling_Precise
ChiDist
ChiInv
ChiSq_Dist
ChiSq_Dist_RT
ChiSq_Inv
ChiSq_Inv_RT
ChiSq_Test
ChiTest
Choose
Clean
Combin
Complex
Confidence
Confidence_Norm
Confidence_T
Convert
Correl
Cosh
Count
CountA
CountBlank
CountIf
CountIfs
CoupDayBs
CoupDays
CoupDaysNc
CoupNcd
CoupNum
CoupPcd
Covar
Covariance_P
Covariance_S
CritBinom
CumIPmt
CumPrinc
DAverage
Days360
Db
Dbcs
DCount
DCountA
Ddb
Dec2Bin
Dec2Hex
Dec2Oct
Degrees
Delta
DevSq
DGet
Disc
DMax
DMin
Dollar
DollarDe
DollarFr
DProduct
DStDev
DStDevP
DSum
Duration
DVar
DVarP
EDate
Effect
EoMonth
Erf
Erf_Precise
ErfC
Even
Expon_Dist
ExponDist
F_Dist
F_Dist_RT
F_Inv
F_Inv_RT
F_Test
Fact
FactDouble
FDist
Find
FindB
FInv
Fisher
FisherInv
Fixed
Floor
Forecast
Frequency
FTest
Fv
FVSchedule
Gamma_Dist
Gamma_Inv
GammaDist
GammaInv
GammaLn
GammaLn_Precise
Gcd
GeoMean
GeStep
Growth
HarMean
Hex2Bin
Hex2Dec
Hex2Oct
HLookup
HypGeom_Dist
HypGeomDist
IfError
ImAbs
Imaginary
ImArgument
ImConjugate
ImCos
ImDiv
ImExp
ImLn
ImLog10
ImLog2
ImPower
ImProduct
ImReal
ImSin
ImSqrt
ImSub
ImSum
Index
Intercept
IntRate
Ipmt
Irr
IsErr
IsError
IsEven
IsLogical
IsNA
IsNonText
IsNumber
IsOdd
Ispmt
IsText
Kurt
Large
Lcm
LinEst
Ln
Log
Log10
LogEst
LogInv
LogNorm_Dist
LogNorm_Inv
LogNormDist
Lookup
Match
Max
MDeterm
MDuration
Median
Min
MInverse
MIrr
MMult
Mode
Mode_Mult
Mode_Sngl
MRound
MultiNomial
NegBinom_Dist
NegBinomDist
NetworkDays
Nominal
Norm_Dist
Norm_Inv
Norm_S_Dist
Norm_S_Inv
NormDist
NormInv
NormSDist
NormSInv
NPer
Npv
Oct2Bin
Oct2Dec
Oct2Hex
Odd
OddFPrice
OddFYield
OddLPrice
OddLYield
Or
Pearson
Percentile
Percentile_Exc
Percentile_Inc
PercentRank
PercentRank_Exc
PercentRank_Inc
Permut
Phonetic
Pi
Pmt
Poisson
Poisson_Dist
Power
Ppmt
Price
PriceDisc
PriceMat
Prob
Product
Proper
Pv
Quartile
Quartile_Exc
Quartile_Inc
Quotient
Radians
RandBetween
Rank
Rank_Avg
Rank_Eq
Rate
Received
Replace
ReplaceB
Rept
Roman
Round
RoundDown
RoundUp
RSq
RTD
Search
SearchB
SeriesSum
Sinh
Skew
Sln
Slope
Small
SqrtPi
Standardize
StDev
StDev_P
StDev_S
StDevP
StEyx
Substitute
Subtotal
Sum
SumIf
SumIfs
SumProduct
SumSq
SumX2MY2
SumX2PY2
SumXMY2
Syd
T_Dist
T_Dist_2t
T_Dist_RT
T_Inv
T_Inv_2T
T_Test
Tanh
TBillEq
TBillPrice
TBillYield
TDist
Text
TInv
Transpose
Trend
Trim
TrimMean
TTest
USDollar
Var
Var_P
Var_S
VarP
Vdb
VLookup
Weekday
WeekNum
Weibull
Weibull_Dist
WorkDay
WorkDay_Intl
Xirr
Xnpv
YearFrac
YieldDisc
YieldMat
Z_Test
ZTest

Remarque: comme vous pouvez le constater, le point . normalement utilisé dans les noms de fonctions Excel (par exemple NORM.DIST) est remplacé par un souligné _ car le . a un rôle particulier en VBA et ne peut pas être utilisé dans ce contexte. La fonction NORM.DIST se transforme donc en Norm_Dist.

* Cette liste se base sur les fonctions disponibles dans Excel 2010

 

Pour aller plus loin en VBA

Pour terminer, voici quelques articles et tutoriels qui pourraient vous être utiles dans votre travail avec VBA…

 

Laissez un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Commentaire sur “WorksheetFunction: Quand et pourquoi l’utiliser”