excel 2016 · 58 macros et langage vba apprendre à programmer sous excel une variable peut être...

Editions ENI

Excel 2016

CollectionRéférence Bureautique

Extrait

Calculs avancés

© Editions ENI - Toute reproduction interdite 167

Cal cu l s avan cés

Excel 20 16Effectuer des calculs sur des données de type date

Après avoir abordé le principe de calcul sur les dates utilisé par Excel, nous vous pro-posons de découvrir quelques fonctions spécifiques au traitement des dates à traversquelques exemples.

Principe de calcul sur des jours

i Si le calcul porte sur des jours, procédez comme pour les autres calculs car Excelenregistre les dates sous la forme de nombres séquentiels appelés numéros de série.De ce fait, elles peuvent être ajoutées, soustraites et incluses dans d’autres calculs.

i Par défaut, sous Windows, Excel utilise le calendrier depuis 1900 (Excel pourMacintosh, le calendrier depuis 1904). Le 1er janvier 1900 correspond donc (pourExcel sous Windows) au numéro de série 1 et le 1er janvier 2005 correspond au nombre38 353, car 38 353 jours se sont écoulés depuis le 1er janvier 1900.

i Pour utiliser une fonction spécifique de gestion de dates et d'heures, vous pouvezactiver l'onglet Formules et cliquer sur le bouton DateHeure du groupe Bibliothèquede fonctions puis sur la fonction concernée afin d'utiliser l'Assistant.ANNEE(numéro_de_série)Convertir un numéro de série en année.AUJOURDHUI()Renvoyer le numéro de série de la date du jour.DATE(année;mois;jour)Renvoyer le numéro de série d’une date précise.DATEVAL(date_texte)Convertir une date représentée sous forme de texte en numéro de série.FIN.MOIS(date_départ;mois)Renvoyer le numéro séquentiel de la date du 1er jour du mois précédant ou suivant ladate_départ du nombre de mois indiqué.FRACTION.ANNEE(date_début;date_fin;[base])Renvoyer la fraction de l’année représentant le nombre de jours entre la date de dé-but et la date de fin.HEURE(numéro_de_série)Convertir un numéro de série en heure.JOUR(numéro_de_série)Convertir un numéro de série en jour du mois.

Exce l 2016

Les calculs

168

JOURS(date_fin;date_début)Calculer le nombre de jours entre les deux dates.JOURS360(date_début;date_fin;[méthode])Calculer le nombre de jours séparant deux dates sur la base d’une année de 360 jours.JOURSEM(numéro_de_série;[type_retour])Convertir un numéro de série en jour de la semaine.MAINTENANT()Renvoyer le numéro de série de la date et de l’heure du jour.MINUTE(numéro_de_série)Convertir un numéro de série en minute.MOIS(numéro_de_série)Convertir un numéro de série en mois.MOIS.DECALER(date_départ;mois)Renvoyer le numéro séquentiel de la date qui représente une date spécifiée (l’argu-ment date_départ), corrigée en plus ou en moins du nombre de mois indiqué.NB.JOURS.OUVRES(date_début;date_fin;[jours_fériés])Renvoyer le nombre de jours ouvrés entiers compris entre deux dates.NB.JOURS.OUVRES.INTL(date_début;date_fin;[weekend];[jours_fériés])Renvoyer le nombre de jours ouvrés entiers compris entre deux dates à l’aide de pa-ramètres identifiant les jours du week-end et leur nombre.NO.SEMAINE(numéro_de_série;[type_retour])Convertir un numéro de série en numéro de semaine dans l’année.NO.SEMAINE.ISO(date)Renvoyer le numéro ISO de la semaine de l’année correspondant à une date donnée.SECONDE(numéro_de_série)Convertir un numéro de série en seconde.SERIE.JOUR.OUVRE(date_début;nb_jours;[jours_fériés])Renvoyer le numéro de série de la date avant ou après le nombre de jours ouvrésspécifiés.SERIE.JOUR.OUVRE.INTL(date_départ;nb_jours;[nb_jours_week-end];[jours_fériés])Renvoyer le numéro de série de la date avant et après un nombre spécifié de joursouvrés en spécifiant des paramètres qui identifient et dénombrent les jours inclusdans le week-end.

Calculs avancés


TEMPS(heure;minute;seconde)Renvoyer le numéro de série d’une heure précise.TEMPSVAL(heure_texte)Convertir une heure représentée sous forme de texte en numéro de série.

Combiner du texte avec une date

i Pour combiner dans une cellule le texte et la date contenus dans différentes cellules,vous pouvez utiliser la fonction TEXTE dont la syntaxe est : =TEXTE(valeur;format_texte)L’argument valeur représente une valeur numérique ou une formule dont le résultatest une valeur numérique ou bien encore une référence à une cellule contenant unevaleur numérique.L’argument format_texte représente un format de nombre sous forme de texte définidans la zone Catégorie de la boîte de dialogue Format de cellule.

Voici un exemple d’utilisation :

Calculer la différence entre deux dates (fonction DATEDIF)

DATEDIF est l’une des fonctions "masquées" de l’application Excel ; elle n’apparaîtpas dans l’Assistant fonction ni dans l’aide en ligne. Les fonctions masquées ont étéintroduites dans Excel pour des raisons de compatibilité avec d'autres tableurs, ellesfonctionnent parfaitement mais ne font pas partie des fonctions "officielles" d’Excel.

Cette fonction s’avère très pratique dans le cas, par exemple, où vous souhaitezcalculer l’ancienneté d’un employé en années et en mois. La syntaxe de la fonctionDATEDIF est DATEDIF(Date_début;Date_fin;Type).

Exce l 2016

Les calculs

170

L’argument Type représente la durée calculée et peut prendre les valeurs suivantes :

Voici un exemple d’utilisation :

Voici un autre exemple qui permet de calculer l’âge d’une personne en fonction de ladate du jour (fonction=Aujourd’hui()) :

"y" pour calculer la différence absolue en années.

"M" pour calculer la différence absolue en mois.

"d" pour calculer la différence absolue en jour.

"ym" pour calculer la différence en mois si les deux dates sont dans la mêmeannée.

"yd" pour calculer la différence en jours si les deux dates sont dans la mêmeannée.

"md" pour calculer la différence en jours si les deux dates sont dans le mêmemois.

Calculs avancés


Calculer le nombre de jours ouvrés ou non entre deux dates

Excel sait calculer le nombre de jours ouvrés (du lundi au vendredi) qui séparent deuxdates à l’aide de la fonction NB.JOURS.OUVRES dont la syntaxe est : =NB.JOURS.OUVRES(jour_début;jour_fin)Voici un exemple d’utilisation :

Pour que cette fonction puisse tenir compte des jours fériés, vous devez y ajouter untroisième argument faisant référence à un jour férié ou à une plage de dates fériées.La syntaxe de cette fonction est alors : =NB.JOURS.OUVRES(jour_début;jour_fin;jours_fériés)

Sur cet exemple, les jours fériés ont été calculés dans la plage de cellules B3 à B15.

m Pour calculer le nombre de jours entre deux dates (jours fériés, chômés... inclus),vous pouvez utiliser la fonction JOURS dont la syntaxe estJOURS(date_fin;date_début).

Exce l 2016

Les calculs

172

Calculer la date située après un nombre de jours ouvrés donné

La fonction SERIE.JOUR.OUVRE vous permet de calculer une date correspondant àune date (date de début) plus ou moins le nombre de jours ouvrés spécifié. Les joursouvrés excluent les samedi et dimanche ainsi que toutes les dates identifiées commeétant des jours chômés.La syntaxe de cette fonction est la suivante :=SERIE.JOUR.OUVRE(date_début;nb_jours;jours_fériés) :

Voici un exemple d’utilisation : nous souhaitons trouver la date d’échéance d’untravail devant commencer le 01 mai 2015 et qui est d’une durée de 40 jours ouvrés.

Le format Date a été appliqué à la cellule C3 car, par défaut, Excel affiche le résultatsous forme de numéro de série.

Si cette formule vous renvoie un message d’erreur, en voici sa signification :

date_début Représente la date de début.

nb_jours Représente le nombre de jours ouvrés avant ou après la date dedébut. Un nombre de jours positif donne une date future, à l’inverse,un nombre de jours négatif donne une date passée.

jours_fériés Représente une liste de dates à exclure du calendrier des jours detravail (jours fériés, congés, absence...). Cet argument est facultatif.

#VALEUR! Un argument n’est pas une date valide.

#NOMBRE! La date de début plus le nombre de jours ne donne pas une date valide.

Editions ENI

Excel 2016

CollectionRéférence Bureautique

Extrait

Macros et langage VBAApprendre à programmer sous Excel

(4e édition)

CollectionSolutions Business

Extrait

© E

ditio

ns E

NI -

All

right

s res

erve

d7
Chapitre 5 : Les variables 5
Chapi tre 5 : Les v ar i abl es

Mac ros et l an gage V BAA. IntroductionComme nous l'avons vu dans le chapitre précédent, l’Enregistreur de macros convertitchacune des sélections de cellule, de plage de cellules, en références fixes :Range("A3:B5").SelectRange("C8").SelectColumns("D").Select

Écrire un code de cette manière est à proscrire car les références resteront invariable-ment les mêmes. Or, l'intérêt de faire un programme c'est d'appliquer une instruction àun élément (une cellule, une feuille de calcul, un classeur, un graphique...) puis de repro-duire cette instruction à d'autres éléments.

Voilà pourquoi, il ne faut jamais écrire directement les références d’une cellule directe-ment dans le code mais il faut utiliser une variable qui va, comme son nom l'indique,permettre de faire varier les coordonnées de la cellule.

B. Qu'est-ce qu'une variable ?Une variable peut être vue comme une boîte dans laquelle vous allez stocker une infor-mation : un chiffre, une date, du texte...

Alors bien sûr, il ne s’agit pas d’une boîte au sens littéral du terme mais d’un espace ré-servé dans la mémoire de l’ordinateur. Comment l’ordinateur gère cet espace en mé-moire, là n’est pas la question. Tout ce qu’il faut retenir c’est qu’en créant une variable(une boîte) avec un nom qui lui sera propre, le langage VBA saura toujours dans quelleboîte il faudra lire ou récupérer une donnée. En fait, il suffit de déclarer un nom de va-riable et de lui attribuer un type de donnée et Visual Basic s'occupera du reste.

Il n'y a pas vraiment de règles pour le nom que vous donnez à vos variables ; vous pouvezchoisir n'importe quel mot à condition qu'il ne fasse pas partie des mots réservés (mots-clés) de VBA (comme Sheets, Cells, Worksheets, Columns, Rows...).

58
Une variable peut être composée d’un ou plusieurs mots mais ils ne doivent pas êtreséparés par le caractère espace ni par un tiret (-). Le seul caractère de séparation autoriséest l'underscore (_).

C. La méthode MsgBoxLa méthode MsgBox est une fonctionnalité importante dans la compréhension d’un pro-gramme en Visual Basic car elle va permettre d’afficher à tout moment un message per-sonnalisé. Non seulement MsgBox affiche un message personnalisé mais il bloqueégalement l’exécution de la suite du code.

1. Affichage d'un message personnaliséb Par exemple, écrivez le programme suivant :Sub Test_Affichage() MsgBox "Message personnalisé"End Sub

b Et lancez l'exécution de ce programme en appuyant sur la touche 5 ou en cliquant

G Pour vos variables, il est préférable de privilégier des noms compréhensibles(Numero_Ligne, Nom_Classeur, Nom_Feuille...) plutôt que de choisir des nomscomposés d’une seule lettre (i, j, k...) comme c’est souvent malheureusement le cas.

sur l'icône . Le résultat de ce programme affiche la boîte de dialogue suivante :

Comme vous le constatez, l'instruction pour afficher un message est très simple à réali-ser mais ce qu'il faut surtout comprendre c'est que tant que vous n'appuyez pas sur lebouton OK, l'exécution du code reste bloquée sur la ligne MsgBox.

Maintenant, il est possible d'afficher plus qu'un message personnalisé : il est aussi pos-sible de regrouper en même temps du texte personnalisé avec le contenu d'une variable.

© E

ditio

ns E

NI -

All

right

s res

erve

d9
2. Afficher le résultat d'une variableTout texte saisi entre guillemets s'affichera tel quel dans la boîte de dialogue. Mais unevariable ne doit jamais être écrite entre guillemets. Si tel était le cas, la boîte de dialogueafficherait le nom utilisé dans le programme pour qualifier la variable et non pas soncontenu.MsgBox "Ma_Variable" 'Affiche juste le mot Ma_Variable comme message.

Alors que l'instruction suivante affichera la valeur de la variable dans la boîte de dialo-gue.MsgBox "Ma_Variable a pour valeur " & Ma_Variable

Selon le contenu de la variable (texte, nombre, date...), l'instruction précédente afficheraà la fois le texte écrit entre guillemets et le contenu de la variable. Nous pouvons parexemple écrire les messages suivants :y Ma_Variable a pour valeur 35y Ma_Variable a pour valeur Parisy Ma_Variable a pour valeur 25/12/2016

Nous verrons dans la section suivante les différents types de variables que vous pouvezutiliser.

Vous pouvez remarquer le symbole & entre le texte entre guillemets et le nom de la va-riable. Nous verrons plus en détails dans le chapitre Gérer les chaînes de caractères destechniques propres aux chaînes de caractères (et il y en a beaucoup), mais retenez à cestade que le symbole & permet de lier du texte personnalisé avec le contenu d'une va-
riable.
3. Personnaliser le messageVous pouvez personnaliser tous les paramètres de la boîte de dialogue MsgBox, titre dela fenêtre, nombre de boutons, icônes. De cette façon, vos utilisateurs auront la percep-tion que votre message est propre à votre programme et n’est pas un message d'Excel.

La méthode MsgBox, outre le message à faire passer, possède les options suivantes :MsgBox Message, Bouton, Titre

L'option Bouton vous permet de changer le nombre de boutons qui s'affichent (commeAnnuler, Oui, Non...) et aussi de changer l'icône qui s'affiche sur la gauche de la boîte dedialogue. Pour cela, il faut choisir l'une des constantes qui s'affiche après avoir saisi unevirgule après votre message personnalisé.

60
En troisième paramètre, vous pouvez également mettre un titre personnalisé.

Ainsi, en écrivant la ligne suivante :MsgBox "Constante vbCritical", vbCritical, "Alerte GRAAAAVE !"

vous affichez le message suivant :

Maintenant, si vous choisissez la constante vbYesNo, la boîte de dialogue va afficher lesboutons Oui et Non, mais l'écriture de la ligne sera légèrement différente car il faut in-diquer que la réponse de l'utilisateur (Oui ou Non) sera mise dans une variable.
Sub Macro1()Dim LaReponse As Long LaReponse = MsgBox("Etes-vous heureux ?", vbYesNo)End Sub
GNous étudierons plus tard comment traiter la réponse dans le chapitre Les conditionset aussi comment connaître la valeur de la réponse dans le chapitre Débogage.

© E

ditio

ns E

NI -

All

right

s res

erve

d1
D. Déclaration d'une variable

1. Déclaration obligatoire ou pas ?Visual Basic permet de travailler avec des variables sans les déclarer. Cependant, un lan-gage informatique quel qu'il soit, ne peut pas en même temps travailler avec des donnéesnumériques et des données textuelles. En effet, si vous essayez d'additionner deschiffres et du texte, votre programme s'arrêtera net et occasionnera un plantage, c’est-à-dire un arrêt total du programme sans terminer son traitement.

Déclarer vos variables vous prémunit d'erreurs de manipulation des données dans unprogramme.

De plus, quand une variable est déclarée, il est très facile de rappeler le nom de vos va-riables grâce à la combinaison de touches CB.

GDans tous les exemples qui suivent, les variables seront systématiquement déclarées.

G Il suffit de saisir les premiers caractères du nom d'une variable déclarée puis d'utili-ser le raccourci CB pour que le nom complet de la variable soit complété instan-tanément.

Vous pouvez rendre la saisie des variables obligatoire en modifiant une option dans lesparamètres de Visual Basic Editor.

b Ouvrez le menu Outils - Options.

b Dans l'onglet Éditeur, cochez l'option Déclaration des variables obligatoire.

62
b Cliquez sur OK.

Maintenant, l'ajout de tout nouveau module commencera systématiquement par l'ins-truction Option Explicit qui impose la déclaration de toutes vos variables.

2. Vérification des déclarations
Quand vous êtes en mode Explicit, c'est-à-dire que vous travaillez en déclarant toutesvos variables, vous ne pourrez pas lancer votre programme si une seule variable n'a pasété déclarée. En effet le programme sera dans l'incapacité de comprendre comment in-terpréter cette information.
Ceci peut paraître très bloquant mais il existe un moyen simple de vérifier si toutes vosvariables ont bien été déclarées avant de lancer votre programme. Il vous suffit d'activerle menu Débogage, puis le sous-menu Compiler VBAProject.

L'outil va parcourir tout votre projet (toutes les procédures et/ou toutes fonctions) et s'ildétecte qu'une variable est utilisée sans avoir été déclarée, il vous le signale par un mes-sage d'erreur.

excel 2016 · 58 macros et langage vba apprendre à programmer sous excel une variable peut être...

Documents