ExcelAvance7

Chapitre 7 : Fonctions personnalisees



Les fonctions s'ecrivent dans une feuille de type Module au moyen d'un langage de programmation : le langage Visual Basic Edition Applications.
On dit alors que la fonction est ecrite en code VBA.
Ce langage est, par defaut, en anglais. A moins que le changement ait deja ete fait, vous devez donc choisir la langue francaise avant d'ecrire vos fonctions personnelles.

Choisir la langue francaise
-Cliquez Nouveau classeur pour creer un nouveau classeur
-Cliquez le menu Outils Options
-Cliquez l'onglet Module de la boite Options
La zone international affiche les choix courants et notamment le separateur de liste qui est, ici, le point virgule.
Le choix de la langue Langue/Pays est grise car la case cochee ets la case Parametres courants.
Quand cette case est activee, els parametres actifs par defaut sont ceux selectionnes dans les parametres regionaux de Windows 95.
Si le francais n'est pas la langue active :
-Cliquez Parametres par defaut pour cocher cette case
-Cliquez la fleche Langue/Pays pour la derouler puis cliquez Francais/France
-Verifiez que le separateur de liste est bien le point virgule ; puis cliquez OK
-Au besoin, activez le panneau de configuration de Windows 95 et choisissez la langue francaise et le point virgule comme separateur de liste.

Creer une fonction personnalisee

Vous pouvez creer des foncitons personnalisees prenant en compte les calculs que vous utilisez le plus frequemment. Une fois creees, ces fonctions sont disponibles dans tous les classeurs.
-Tapez les donnees suivantes :






Aix

Agen

Apt

Arras


Chiffre d'affaires

145000

125000

135000

115000


Cout production

30000

27000

27000

25000


Cout distribution

7300

6300

6700

6000


Marge brute






Frais generaux






Marge nette




-Cliquez Enregistrer et nommez le classeur Fonctions personnalisees

Fonction personnalisee simple
Les lignes 6 a 8 font appel a des formules que vous utilisez couramment :
Marge Brute = Chiffre d'affaires - Couts (production et distribution)
Marge Nette = Marge brute - Frais generaux
Les frais generaux sont deduits de la marge brute en fonction du volume du chiffre d'affaires : ils representent 10% du montant si les ventes sont superieures a 125000 F et 5% du montant des ventes si les ventes sont inferieures ou egales a 125000 F.
Vous allez creer trois fonctions personnalisees que vous appellerez respectivement : MBrute, Fgeneraux et MNette.
Creer une fonction personnaisee
-Cliquez le menu Insertion Macro Module
Une feuille Module1 est inseree devant la feuille Feuil1 et la barre d'outils Visual Basic ets automatiquement apparue.
-Au besoin, double-cliquez la barre de titre de la barre Visual Basic pour la placer au-dessus de la barre de formule.
Visual Basic Edition Applications (ou VBA) est le langage specifique a Excel qui permet d'ecrire, entre autres, des fonctions personnalisees. La feuille Module1 ne possede pas de quadrillage, ni de lignes et colonnes, car il ne s'agit pas d'une feuille de calcul, mais d'une feuille Visual Basic.
Le point d'insertion clignote en haut de la feuille Module1.
-Tapez Fonction MBrute(CA; Production; Distribution)
Vous avez ainsi defini une fonction de trois arguments selon la syntaxe generale des fonctions d'Excel :
Fonction Nom_fonction(argument1; argument2; ; argumentN)
-La fonction s'appelle MBrute
-Les arguments s'appellent : CA, Production et Distribution
-Tapez ↵
Le terme Fonction apparait en bleu. En effet, Excel identifie ce terme comme un mot-cle VBA specifique a la creation d'une fonction.
Des espaces sont apparus entre les termes Fonction et MBrute ainsi qu'apres les signes ; .
Ces espaces sont necessaires a la syntaxe d'une fonction personnalisee et quand vous oubliez de les taper, ils sont automatiquement generes en validant avec la touche ↵.
[De meme, si vous avez tape un f minuscule a la place du F majuscule, le f minuscule se transforme en F majuscule lorsque vous validez avec ↵.]
-Tapez ⇄ pour vous eloigner du bord gauche de la feuille
-Tapez MBrute=CA - (Production+Distribution) ↵
En tapant ↵ Excel a insere des espaces devant et derriere chaque operateur de la nouvelle fonction.
-Tapez ｩ (backspace) pour revenir vers le bord gauche de la feuille
-Tapez Fin Fonction ↵
Les termes Fin Fonction apparaissent en bleu puisqu'il s'agit d'un code VBA.
Vous avez ainsi defini une fonction personnalisee selon le code Visual Basic :
Fonction Nom(arguments de la fonction)
Operation(s) a executer
Fin fonction

[size=9]
-Tapez SinonSi CA > 130000 Alors [/size][size=9]↵
-Tapez [/size][size=9]⇄ pour creer une tabulation
-Tapez FG = CA * 0,1 [/size][size=9]↵
-Tapez [/size][size=9]ｩ pour supprimer une tabulation
-Tapez Sinon puis [/size][size=9]↵
-Tapez [/size][size=9]⇄ pour creer une tabulation
-Tapez FG = CA * 0,07 puis [/size][size=9]↵
-Tapez [/size][size=9]ｩ pour supprimer une tabulation
-Tapez Fin Si [/size][size=9]↵
-Tapez [/size][size=9]ｩ pour supprimer une tabulation
-Tapez Fin Fonction [/size][size=9]↵
-Cliquez la feuille Feuil1 pour l'activer
-En cellule B15, tapez =FG(B11)
-Recopiez cette formule jusqu'en cellule E15
-Cliquez la cellule E11 et tapez 105000 ↵
Les frais generaux passent a 5% du chiffre d'affaires, soit 5250.
-Cliquez Enregistrer pour sauvegarder les modifications[/size][size=9][/size][size=9]
Selon Cas[/size][size=9][/size][size=9]
La remuneration de vos commerciaux depend de la ville dans laquelle ils travaillent : chaque ville dispose d'un coefficient specifique. Ce coefficient doit etre affecte a la base de remuneration individuelle.
Salaire = Base * (1+Coefficient de la ville)
Vous souhaitez construire la fonction permettant de calculer les salaires et vous voulez, en plus, que votre modele prenne en compte des cas aberrants telle qu'une mauvaise saisie de la ville.
-Le coefficient de la ville d'Aix est : 1,25
-Le coefficient de la ville d'Apt est : 1,05
-Le coefficient de la ville d'Agen est : 1,10
-Le coefficient de la ville d'Arras est : 1,15
-Cliquez la feuille Module1 pour l'activer
-Au besoin, tapez sur Ctrl Fin pour aller a la fin de cette feuille
-Tapez sur ↵ pour creer une ligne vierge
-Tapez Fonction Salaire(Ville; Base) puis ↵
-Tapez [/size][size=9]⇄ pour creer une tabulation
-Tapez Selon Cas Ville puis [/size][size=9]↵
-Tapez [/size][size=9]⇄ pour creer une tabulation
-Tapez Cas "Aix" puis [/size][size=9]↵
-Tapez [/size][size=9]⇄ pour creer une tabulation
-Tapez Salaire = Base * 1,25 puis [/size][size=9]↵
-Tapez [/size][size=9]ｩ pour supprimer une tabulation
-Tapez Cas "Apt" puis [/size][size=9]↵
-Tapez [/size][size=9]⇄ pour creer une tabulation
-Tapez Salaire = Base * 1,05 puis [/size][size=9]↵
-Tapez [/size][size=9]ｩ pour supprimer une tabulation
-Tapez Cas "Agen" puis [/size][size=9]↵
-Tapez [/size][size=9]⇄ pour creer une tabulation
-Tapez Salaire = Base * 1,1 puis [/size][size=9]↵
-Tapez [/size][size=9]ｩ pour supprimer une tabulation
-Tapez Cas "Arras" puis [/size][size=9]↵
-Tapez [/size][size=9]⇄ pour creer une tabulation
-Tapez Salaire = Base * 1,15 puis [/size][size=9]↵
-Tapez [/size][size=9]ｩ pour supprimer une tabulation
-Tapez Cas Sinon puis [/size][size=9]↵
-Tapez [/size][size=9]⇄ pour creer une tabulation
-Tapez Salaire = "erreur de saisie ville" puis [/size][size=9]↵
-Tapez deux fois sur [/size][size=9]ｩ pour supprimer deux tabulations
-Tapez Fin Selon puis [/size][size=9]↵
-Tapez [/size][size=9]ｩ pour supprimer une tabulation
-Tapez Fin Fonction puis [/size][size=9]↵[/size]

[size=9]
-Activez la feuille Feuil2 et saisissez les donnees suivantes :[/size]




[size=9]
Ville[/size]
[size=9]
Base[/size]
[size=9]
Salaire[/size]

[size=9]
Achille[/size]
[size=9]
Arras[/size]
[size=9]
10000[/size]


[size=9]
Alain[/size]
[size=9]
Apt[/size]
[size=9]
11000[/size]


[size=9]
Alex[/size]
[size=9]
Aix[/size]
[size=9]
9000[/size]


[size=9]
Auguste[/size]
[size=9]
Aras[/size]
[size=9]
7000[/size]


[size=9]
Aram[/size]
[size=9]
Aix[/size]
[size=9]
9000[/size]


[size=9]
Agathe[/size]
[size=9]
Apt[/size]
[size=9]
10000[/size]
[size=9]
-Cliquez la cellule D2 et construisez la formule =Salaire(B2;C2)
-Recopiez cette formule jusqu'en cellule D3
-Cliquez Enregistrer pour sauvegarder les modifications[/size][size=9]
[/size][size=9]
Changer la presentation des caracteres[/size][size=9][/size][size=9]
-Cliquez le menu Outils Options
-Cliquez l'onglet Format de module de la boite Options
Cet onglet permet de modifier la presentation du texte dans la feuille Module : comme la police ou la taille des caracteres, la couleur des codes VBA...
Pour changer la couleur d'un element :
-Cliquez l'element a modifier dans la liste Couleur de Code
-Cliquez la fleche Premier Plan et cliquez la couleur souhaitee
-Ici, cliquez Annuler pour fermer la boite Options
-Fermez le classeur.[/size]