Les procédures Function

7/31/2019 Les procdures Function

1/10

Procdures Function/End Function < Sommaire

Excel dispose de nombreuses fonctions utilisables dans une feuillede calcul. Bien entendu, Excel ne fournit pas toutes les fonctionspossibles et imaginables...

C'est ici que vous intervenez pour enrichir la liste des fonctionsinternes avec vos fonctions personnalises.

Une fonction personnalise est dfinie par le couple Function /End Function.

Une telle fonction peut tre utilise dans une feuille de calculet/ou dans le code VBA.

Pour chaque exemple, vous travaillez dans un Module.

Vous pouvez Copier le code d'une fonction de cette page et leColler directement dans un Module.

Pour rappel, si vous composez CTRL + F (ou F3) partirde cette page, vous pourrez rechercher un mot-cl.

Exemples

:: Cube d'un nombre:: Puissance d'un nombre:: Arer un mot:: Vrifier si chiffre:: Gnrer un mot:: Coder un mot (--> illisible):: Vrifier un n de compte bancaire:: Anne bissextile:: Trimestre/Semestre

:: Palindrome:: Multiplier en additionnant !!:: Voyelles:: Maximum/Minimum de 2 nombres:: Pair/Impair:: Adressage indirect:: Chiffrement par dcalage (ou chiffre de Csar)

:: Exemple 1

Function aucube(nbr As Integer) As Single' ---------------------------' Calculer le cube d'un nombre

' ---------------------------

aucube = nbr * nbr * nbr

End Function

Objectif: obtenir le cube d'un nombre

nbr As Integer : nbr est du type Integer (Entier).

As Single : la fonction renvoie un Single (Rel simple)

aucube = nbr * ... : pour renvoyer LA valeur : c'est le nom de lafonction qui doit recevoir la valeur renvoyer l'appelant.

:: Pour utiliser cette fonction dans une feuille de calcul

Exemple 1 : =aucube(10)

Exemple 2 : =aucube(A1)

:: Exemple 2

Function do_expo(nbr As Integer, expo As Integer) As Single

' --------------------------------

Objectif: calculer la puissance d'un nombre.

On suppose que l'exposant sera positifou nul.

r 10


2/10

' Calculer la puissance d'un nombre' --------------------------------'' Version rcursive

If expo >= 1 Then

do_expo = do_expo(nbr, expo - 1) * nbr

Else

do_expo = 1

End If

End Function

As Single : la fonction renvoie un Single (Rel simple)

do_expo(nbr, expo - 1) : appel rcursif(la fonction s'appelleelle-mme).

Ifexpo >= 1 Then : permet de dfinir le critre d'arrt. Uneerreur ce stade peut provoquer un dpassement de la pile(structure contenant les rsultats intermdiaires).

do_expo=1 est excute une et une seule fois.


Exemple 1 : =do_expo(2;5) (renvoie la valeur 32)

Exemple 2 : =do_expo(A1;B2)

:: Exemple 3

Function aerer(lemot As String, nbsp As Integer) As String' ---------------------------------------------' Ajouter un espace entre chaque lettre d'un mot' (sauf aprs le dernier)' ARGUMENTS :' lemot : chane traiter' nbsp : nombre d'espaces insrer

' ---------------------------------------------espace = String$(nbsp, " ")lg = Len(lemot)

For ind = 1 To lg - 1ret = ret & UCase(Mid$(lemot, ind, 1)) & espace

Next indret = ret & UCase(Mid$(lemot, ind, 1))aerer = retEnd Function

Objectif: ajouter un espace entre les caractres d'une chane decaractres

Cette fonction ncessite deux arguments :

lemot : la chane de caractres traiter.

nbsp : nombre d'espaces insrer entre chaque lettre.

La fonction renvoie une valeur du type String (Chane)

Le traitement proprement dit est ralis par la boucle FOR/NEXT

>>>> Pour obtenir de l'aide propos d'un mot de cette fonction,il suffit de cliquer sur le mot et d'appuyer sur F1 (quand vous tesdans l'diteur VBE d'Excel).


Exemple 1 : =aerer("LUNDI";1)

Exemple 2 : =aerer(A1;2)

:: Exemple 4

Function ifchiffre(ch As Variant) As Boolean' -------------------------------------------------' Vrifier si le paramtre ne contient que des chiffres' Renvoie true si la fct. ne trouve que des chiffres' -------------------------------------------------lg = Len(ch)For ind = 1 To lg

IfAsc(Mid$(ch, ind, 1)) >= 48 And Asc(Mid$(ch, ind, 1))


3/10

----------


Exemple 1 : =ifchiffre(A1)

Exemple 2 : =ifchiffre(lecode)

:: Pour utiliser cette fonction dans du code VBA

Exemple 1 : MsgBox ifchiffre("123") ' Affiche une bote avec le message True

Exemple 2 : resul = ifchiffre(zone) ' resul reoit True ou False en fonction du contenu de zone

:: Exemple 5

Function mot(nb As Integer) As String' -----------------------------------------' Crer un mot de NB lettres (en minuscules)' -----------------------------------------Application.Volatile

Dim ind As IntegerDim tempo As String

tempo = ""' Construire le motFor ind = 1 To nb

tempo = tempo &Chr(97 + Rnd() * 25)Next

mot = tempo

End Function

Version rcursive------------------

Function mot(nb As Integer) As String' -----------------------------------------

' Crer un mot de NB lettres (en minuscules)' -----------------------------------------Application.Volatile

' Construire le motIf nb > 0 Then

nb = nb - 1mot = Chr(97 + Rnd() * 25) &mot(nb)

End IfEnd Function

Objectif: crer un mot de x lettres. Les lettres sont choisies "auhasard" dans l'intervalle [a-z].

Chr : renvoie le caractre dont le code est spcifi commeargument.(ex. : Chr(65) renvoie A et Chr(97) renvoie a).

on ne fait riencas 2 : c'est un espace, on l'ajoute au rsultat (resul)cas 3 : c'est une lettre minuscule, on la traite et on la concatne

r 10


4/10

For ind = 1 To nbcar' Code du car. courantasccar = Asc(Mid(mot, ind, 1))' Pour viter les caractres non validesIf asccar < 97 Or asccar > 96 + 26 Then Exit ForIf asccar = 32 Then ' Si espace

resul = resul & " "Else ' c'est une lettre' Prendre la lettre du code qui correspond' au code ASCII-96 de la lettre initiale

resul = resul & Mid(code, asccar - 96, 1)End IfNext indcharabia = resulEnd Function

au rsultat (resul)

3 Renvoyer le rsultat (charabia = resul)

----------------Infos

Len : calcule le nombre de caractres d'une chane.

Asc : renvoie le code ASCII du caractre.

Mid : renvoie un (ici) caractre d'une chane partir d'un pointdonn.

32 : code ASCII de l'espace simple97 : code ASCII de la lettre a122 : code ASCII de la lettre z

Lcase : transforme son argument en lettres minuscules.

:: Appel de la fonction dans une procdure VBA

Sub demo()

Dim lemot As Stringlemot = LCase(InputBox("Mot coder(lettres & espaces)"))MsgBox "Avant : " & lemot & vbCrLf & "Aprs : " &charabia(lemot)

End Sub

:: Exemple 7

Function valid_compte(compte As String)' ----------------------------------------------' Vrification partielle d'un n de compte bancaire' ----------------------------------------------Dim lg, tiret1, tiret2 As BooleanDim msg0 As String, msg1 As String, msg2 As String, msg3 AsStringConst nv As String = "non valide"

lg = Len(compte)=14 ' True si Len(compte) = 14

tiret1 = InStr(1, compte, "-") = 4 ' True si tiret la bonne place

tiret2 = InStr(5, compte, "-") = 12 ' True si tiret la bonne place

msg0 = "Ncompte....: " & compte & vbCrLfmsg1 = "longueur....: " &IIf(lg , "OK", nv) & vbCrLfmsg2 = "tiret1......: " & IIf(tiret1, "OK", nv) & vbCrLfmsg3 = "tiret2......: " & IIf(tiret2, "OK", nv) & vbCrLf

MsgBox msg0 & msg1 & msg2 & msg3

End Function

Objectif: vrifier certains lments d'un n de compte bancaire.

La fonction vrifie seulement les points suivants :

1 longueur du n de compte (14 caractres)

2 prsence d'un tiret en position 4 et en position 12.

Remarques------------1 Appele partir d'une feuille de calcul, la fonction renvoie 0 etaffiche une bote de message avec le rsultat de l'analyse.

2 Remarquez l'utilisation de la fonction IIf(elle joue le rle d'unSi mais avec une nuance importante : IIf value les deuxexpressions, que la condition soit vrifie ou non).


Sub test()Dim moncompte As Stringmoncompte = "001-2352526-89"mavar = valid_compte(moncompte)

End Sub

:: Appel dans une feuille de calcul

Exemple 1 : =valid_compte("001-9996532-47")

Exemple 2 : =valid_compte(B7)

:: Exemple 8

r 10


5/10

Function bissextile(annee As Integer) As Boolean

Dim div4 As Boolean, div100 As Boolean, div400 As Boolean

' Vrifier la divisibilitdiv4 = annee Mod 4 = 0div100 = annee Mod 100 = 0div400 = annee Mod 400 = 0

' Fixer la valeur de retourIf div4 And Not div100 Then ' 1er cas

bissextile = TrueElseIf div400 Then ' 2me cas

bissextile = TrueElse

bissextile = FalseEnd If

End If

End Function

Objectif: vrifier si une anne est bissextile ou non.

Une anne est bissextile dans un des deux cas suivants :

1 Anne divisible par 4 et non par 100 (donc, une anne nonsculaire).

2 Anne divisible par 400.

Remarques

------------1 Mod renvoie le reste de division entire d'un nombre par unautre (ex. : 100 Mod 25 renvoie 0 car 100 est divisible par 25).

2 L'criture annee Mod 4 = 0 renvoie le rsultat d'unecomparaison ; donc, True ou False.

3 Il est possible de simplifier le code... mais l'objectif est demettre en vidence la logique du problme.


Sub test()Dim annee As Interger, mavar as Boolean

annee = 2000mavar = bissextile(annee)End Sub


Exemple 1 : =bissextile(2000)

Exemple 2 : =bissextile(B7)

:: Exemple 9

Function trimestre(ladate As Date) As Byte' ---------------------------' Renvoie le n du trimestre' ---------------------------

trimestre = Int((Month(ladate) - 1) / 3) + 1

End Function

Objectif: calculer le n du trimestre en fonction d'une date.

Remarques------------

1 Int : fonction quivalente ENT utilise dans une feuille.

2 Month : fonction quivalente MOIS utilise dans une feuille.


Exemple : =trimestre(B7)

Remarque-----------Pour obtenir le n du semestre, il suffit de remplacer le 3 par un 6 : simple n'est-ce pas ?

Function semestre(ladate As Date) As Byte' Renvoie le n du semestre

semestre = Int((Month(ladate) - 1) / 6) + 1

End Function

:: Exemple 10

Function palindrome(mot As String) As Boolean' ---------------------------------------------' Palindrome : mot ou phrase que l'on peut lire

' dans les deux sens, en commenant' par la droite ou par la gauche' Exemple : non, radar,t, php, ...' ---------------------------------------------

Objectif: vrifier si une chane de caractres (mot ou phrase)est un palindrome.

r 10


6/10

Dim nbcar As Integer, pal As Boolean, demi As Integer, ind AsInteger

' La fonction REPLACE recherche toutes les occurrences du caractreespace et le remplace par rien("").mot = LCase(Replace(mot, " ", ""))' Longueur du texte sans les espacesnbcar = Len(mot)

demi = Int(nbcar / 2) ' On "coupe" le texte en deux

pal = True ' On prsume que le texte est un palindrome

For ind = 1 To demiIfMid(mot, ind, 1) Mid(mot, nbcar - ind + 1, 1) Then

pal = False ' Ce ne sera pas un palindrome...Exit For ' ... on s'arrte

End IfNext ind

palindrome = pal

End Function

Remarque-----------1 Replace : telle qu'elle est crite, Replace effectue un parcoursdu texte, du premier au dernier caractre.

2 LCase : remplace les majuscules par des minuscules

Comparer le caractre 1 et le caractre n, puis le caractre 2 et lecaractre n-1, ...


Sub test()Dim texte As StringDim msg1 As String, msg2 As String

' Initialisation des variablesmsg1 = " : est un palindrome"msg2 = " : n'est pas un palindrome"

texte = "radar"MsgBox texte &IIf(palindrome(texte), msg1, msg2)

End Sub

Remarque : IIfrenvoie le premier argument si la condition est vrifie et le second si la condition n'est pas vrifie.


Exemple 1 : =palindrome("Esope reste ici et se repose")

Exemple 2 : =palindrome(B7)

:: Exemple 11

Objectif: effectuer une multiplication avec une srie d'additions et de manire rcursive.

On peut dire qu'une fonction(procdure) est rcursive si elle est dfinie en fonction d'elle-mme.

La rcursivit utilise une structure appele pile : pensez une pile d'assiettes, par exemple. Le fonctionnement est simple : je peux

ajouter une assiette sur l'assiette qui est au sommet de la pile et je peux retirer (sans aucun risque !) l'assiette qui se trouve ausommet de la pile. Pratiquement, c'est VBA qui gre l'volution de la pile interne(dont la taille est limite).(n. b. : vous pouvez remplacer les assiettes par des crpes, miam-miam !)

r 10


7/10


Sub test()Dim nb1 As Integer, nb2 As Integernb1 = 5nb2 = 4MsgBox multiplier(nb1,nb2)

End Sub


Exemple 1 : =mutiplier(5;4)

Exemple 2 : =multiplier(A3;B3)

:: Exemple 12

Function voyelles(chaine As String) As Integer' ------------------------------------------------' Compter le nombre de voyelles contenues dans une ' chane de

caractres'-------------------------------------------------Const voyelle As String = "aeiouy"Dim nbcar As IntegerDim ind As IntegerDim nb As Integer

nbcar = Len(chaine)nb = 0

For ind = 1 To nbcar'' Mid(chaine, ind, 1) : renvoie la lettre en position ind traiter' InStr(1, voyelle, Mid(chaine, ind, 1)) : renvoie la position de lalettre parmi les voyelles' Abs(InStr(1, voyelle, Mid(chaine, ind, 1)) 0) : renvoie 1

(grce ABS) ou 0 (grce ABS)' (Une comparaison en VBA renvoie -1 ou 0 )

nb = nb + Abs(InStr(1, voyelle, Mid(chaine, ind, 1)) 0) ' **

Next ind

Objectif: compter le nombre de voyelles contenues dans unechane de caractres.

Quand un caractre n'est pas une voyelle, Instr renvoie 0. Quandun caractre est une voyelle, Instr renvoie la position ducaractre dans la constante voyelle. Donc, par exemple, si Instrrenvoie 4 alors est-ce que 4 est diffrent de 0 ? Oui (c.--d. -1 en

VBA). En utilisant Abs, -1 est converti en 1 et 0 est converti en 0.Alternative------------Remplacer ** par

nb = nb + (InStr(1, voyelle, Mid(chaine, ind, 1)) 0)

r 10


8/10

voyelles = nb ' ***End Function


Exemple 1 : =voyelles("informatique")

Exemple 2 : =voyelles(B3)

Les parenthses externes sont indispensables pour obtenir -1 etnon vrai.

et remplacer *** par

voyelles = -nb

:: Exemple 13

Function maxi(a As Integer, b As Integer) As Integer' ---------------------------' Maximum de deux nombres' Version avec une alternative' ---------------------------

If a < b Thenmaxi = b

Elsemaxi = a

End If

End Function

Function maxi(a As Integer, b As Integer) As Integer' ---------------------------' Maximum de deux nombres' Version mathmatique' ---------------------------

maxi = (a + b + Abs(a - b)) / 2

End Function

Objectif: renvoyer le maximum de deux nombres.

Pour obtenir la minimum, il suffit de remplacer maxi = b par maxi= a et maxi = a par maxi = b

Pour obtenir le minimum, il suffit de remplacer +Abs(...) par -Abs(...)


Exemple 1 : =maxi(-12;17)

Exemple 2 : =maxi(A1;C3)

:: Exemple 14

Version 1----------

Function pair_impair(nbr As Integer)' ------------------------------------' Vrifier si un nombre est pair ou impair' ------------------------------------

If nbr Mod 2 = 0 Then

pair_impair = True ' Nbr. pairElsepair_impair = False ' Nbr. impair

End IfEnd Function

Version 2----------

Function pair_impair(nbr As Integer)' ------------------------------------' Vrifier si un nombre est pair ou impair' ------------------------------------

pair_impair = IIf(nbr Mod 2 = 0, True, False)

End Function

Objectif: la fonction renvoie True si le nombre est pair et False sile nombre est impair.

Mod : oprateur modulo qui renvoie le reste de la division dedeux entiers.

Ecriture : nbr Mod diviseur

(Si nbr est un rel, il sera arrondi avant d'tre utilis)

Si un nombre est pair, le reste de la division par 2 vaut 0.

Si le nombre est impair, le reste de la division par 2 vaut 1.


Exemples

La formule...

r 10


9/10

=pair_impair(8) ........... renvoie VRAI dans la cellule

=pair_impair(37,2)........ renvoie FAUX car 37, 2 est arrondi 37

En VBA, 37,2 Mod 2 renvoie 1 (car 37,2 est arrondi 37)

Dans une feuille, la formule =MOD(37,2;2) renvoie 1,2

:: Exemple 15

Function cumuler() As DoubleDim total As Double' ------------------------------------------' Cumuler les valeurs d'un ensemble de plages' ------------------------------------------

On Error Resume Next

' Indispensable pour forcer le recalcul quand on' modifie le contenu de la feuilleApplication.Volatile

' Range("C2") : contient le nom d'une plage' Range(Range("C2")) : identifie la plage dont le nom est stocken C2' Donc, on effectue un adressage indirect (voir fonction indirectdans une feuille)' Avantage : la formule fonctionne quel que soit le nom de la plagestocke en C2' Sum : effectue une somme

total =Application.WorksheetFunction.Sum(Range(Range("C2")))

cumuler = total

End Function

Objectif: cumuler les valeurs d'une plage quelconque ou deplusieurs plages sachant que les rfrences sont introduites dansune cellule de saisie fixe (ici, C2).


10/10

Function cesar(letexte As String) As String' --------------------------------------------------------------' Cette fonction applique un code par dcalage ou chiffre de Csar une chane.' Objectif : chiffrement d'un message pour le rendre illisible.' Ici, la translation est de 3.' Exemple : A devient D ; Z devient C ; ...' Version pour les lettres majuscules' (Une espace se transforme en #)' Micdub - 2011' --------------------------------------------------------------

Dim machaine As StringDim code, lecodecarDim decalage As ByteDim ind As IntegerDim lalong As Integer

machaine = "" ' Pour stocker la chane chiffredecalage = 3 ' Pas du dcalage

letexte = UCase(letexte) ' UCase renvoie des majusculeslalong = Len(letexte) ' Longueur du texte traiter

For ind = 1 To lalong ' Parcourir la chane caractre/caractrecode = Asc(Mid(letexte, ind, 1))

' Ajouter 3 au code ASCII du caractrelecodecar = code + decalage

' Pour parcourir l'alphabet cycliquementIf lecodecar > 90 Then

lecodecar = 64 + lecodecar Mod 90 ' 90 = code ASCII de ZEnd If

machaine = machaine + Chr(lecodecar) ' Reconstruire le

message

Nextcesar = machaine ' Renvoyer le message chiffrEnd Function

Pour utiliser la fonction dans une cellule :

=cesar("bienvenue")

=cesar(C4)

Sujet: raliser un chiffrement par dcalage ou chiffre de Csar.

ExempleLe mot bienvenue (en clair) s'crit ELHQYHQXH aprs lechiffrement.

Rle de Mod : parcourir cycliquement l'alphabet (A = 65 et Z =90)

65 Mod 90 = 6566 Mod 90 = 66...90 Mod 90 = 091 Mod 90 = 1 + 64 = 65 --> Chr(65) --> A92 Mod 90 = 2 + 64 = 66 --> Chr(66) --> B...

Principe (avec un dcalage de 3 lettres).

Le schma suivant illustre le parcours cyclique de l'alphabet : aprs Z, on recommence partir de A.

Documents

Les procédures Function