Introduction à Visual Basic pour Excel

Support de cours introduction à Visual Basic pour Excel, tutoriel & guide de travaux pratiques Excel en pdf.

Insertion de fonctions

Il y aussi une deuxième façon d’effectuer des calculs avec Excel : l’utilisation de fonctions. Une fonction est une formule prédéfinie qui effectue un calcul à l’aide des arguments de la fonction. Pour utiliser une fonction on doit utiliser la syntaxe suivante.
Syntaxe
=NOMFONCTION(argument1 ; argument2 ; argument3 ;…)
On peut aussi accéder à la liste des fonctions ainsi qu’à leur description soit en allant dans le menu Insertion, Fonction ou en appuyant sur la touche fx dans la barre d’outils.
Les arguments d’une fonction représentent ce que la fonction a besoin pour retourner une valeur. Ils peuvent être de différents types : nombre, logique (booléen), etc.
Dans Excel, ces arguments peuvent aussi faire référence à une cellule ou à une plage de cellules. Il est aussi possible d’utiliser les références relatives et absolues.
Exemple
Les notes de 50 étudiants sont entrées dans les cellules A5:A54. On veut obtenir la moyenne et l’écart type.
Pour calculer la moyenne, au lieu d’entrer la formule =(A5+A6+A7+…+A54)/50, nous utiliserons la fonction MOYENNE. =MOYENNE(A5:A54)
Même principe pour calculer l’écart-type des notes de la classe. Nous utiliserons la fonction  =ECARTYPE(A5:A54)
Remarques
• Il est possible d’imbriquer des fonctions à l’intérieur d’une autre fonction.
• Les opérations mathématiques peuvent être utilisées à l’intérieur d’une fonction et avec une fonction. Exemple : Calculer la variance des notes du groupe. On entre la fonction =ECARTYPE(A5:A54)^2.
Voici un exemple combinant les deux remarques.
On désire calculer la probabilité qu’un étudiant pris au hasard ait plus que 70% à son examen. On suppose que la distribution des notes est normale. La fonction à entrer est =1-LOI.NORMALE(0.70 ; MOYENNE(A5:A54) ; ECARTYPE(A5:A54) ; VRAI)
Ici, la fonction LOI.NORMALE retourne la fonction de répartition ou de densité d’une loi normale. Dans cet exemple, il s’agit de la fonction de répartition (VRAI) d’une loi normale, évalué à 0.70, avec paramètres mu = MOYENNE(A5:A54) et sigma = ECARTYPE(A5:A54).

Outils complémentaires d’Excel : le solveur

À l’intérieur de Microsoft Excel, il y a plusieurs outils permettant de calculer et d’analyser des données. Ces outils sont la création de graphiques, de cartes géographiques, outils d’optimisation, d’analyse des données en régression, création d’histogrammes, résolution de problèmes à l’aide de la transformée de Fourier, etc. Nous regarderons ensemble un outil très intéressant en mathématiques et en actuariat dans la résolution de problèmes : le solveur.
Le solveur du logiciel Microsoft Excel est un utilitaire permettant de résoudre efficacement et rapidement certains problèmes d’optimisation en effectuant une approximation de la solution par le biais d’algorithmes numériques. Les problèmes de type linéaires sont traités à l’aide de l’algorithme du simplex et la méthode de Branch-and-Boundmise, tandis que les problèmes de type non linéaires sont résolus à l’aide de la méthode du Generalized Reduced Gradient.
Son fonctionnement est très simple. En termes clairs, il adapte les valeurs des cellules que vous souhaitez modifier (cellules variables), et retourne le type de résultat spécifié (maximum, minimum, etc.) à partir des formules des cellules cibles et des contraintes spécifiées. Le solveur peut notamment être utilisé dans la recherche des extrema d’une fonction complexe, pour résoudre un système d’équation linéaire, pour estimer des paramètres ou pour résoudre un problème algébrique.

Installation du solveur

Si le mot « Solveur » ne figure pas dans la liste déroulante du menu « Outils », il vous faudra installer le solveur. Voici la procédure pour installer le solveur :
(1) Choisir « Macro complémentaire » dans le menu « Outils » ;
(2) Cocher solveur;
(3) Appuyer sur « OK ».

Utiliser le solveur

Pour résoudre un problème avec le solveur, il faut commencer par monter un classeur Excel en reliant certaines cellules par les formules de votre problème. Par exemple, si on cherche la solution du système d’équations suivant :
i )2 x + y + z = 3
ii ) x − y − z = 3
iii )3 x + 2 y + 3 z = 3
Il pourrait être intéressant de construire ce type de feuille Excel :
Remarque
Initialement, on a posé que x = 1, y = 1 et z = 3, . Ce ne sont que des valeurs fictives qui seront par la suite ajustées par le solveur pour satisfaire au système d’équations linéaire.
La seconde étape consiste à décortiquer le problème en déterminant les cellules qui sont :
(1) À optimiser (minimiser, maximiser ou égaliser);
(2) Variables;
(3) Sujettes à une contrainte d’optimisation.
Dans notre exemple, la cellule C4 pourrait être arbitrairement choisie comme une cellule à maximiser, les cellules C4, C5 et C6 seraient les cellules variables (celles que le solveur devra réajuster pour que le système d’équations soit respecté) et les cellules C11, C12 et C13 seront sujettes à des contraintes (ces cellules doivent être égales à 3 pour que le système d’équation soit satisfait). Étant donné les contraintes mises en place, le fait de maximiser arbitrairement la cellule C4 ne modifie en rien le résultat.
Lorsque la feuille Excel est décortiquée, il ne reste plus qu’à entrer les paramètres dans le solveur et à exécuter ce dernier.
Dans notre exemple, voici comment il faudrait entrer les paramètres du solveur :
Voici maintenant l’allure de la feuille Excel après le travail du solveur :
La solution du système d’équations est sans aucun doute x = 2, y = -1,2 et z = 2.
Attention : Lorsque le problème est plus complexe, le solveur peut ne pas converger vers la solution à la première exécution. Dans ce cas, on peut exécuter le solveur une ou plusieurs autres fois ou modifier le nombre d’itérations maximal dans les options du solveur.
Concepts fondamentaux de la programmation orientée objet
Avant de débuter la programmation en Visual Basic, il faut rappeler certains concepts importants de la programmation orientée objet.

Définitions

Objet : Élément de programmation ayant des propriétés, pouvant exécuter certaines actions et pouvant interagir avec d’autres objets. Un objet peut en contenir un autre et peut aussi appartenir à d’autres objets.
Propriété : Caractéristique ou attribut propre à un certain objet.
Méthode : Action que peut exécuter un objet.
Observons ce que pourrait être un objet sans penser à la programmation. Par exemple, une voiture. Effectivement, une voiture peut avoir certaines caractéristiques (couleur, longueur, largeur, puissance, consommation, etc.) La voiture peut aussi exécuter certaines actions (avancer, reculer, etc) et peut aussi interagir avec certains autres éléments (la voiture interagit avec la route, etc.). Une radio, qui est aussi un objet en soi ayant des propriétés, peut être contenu dans une voiture et aussi être contenu dans une maison (objet).
Collection ou classe : Ensemble d’objets ou d’autres collections.
Hiérarchie : Façon d’ordonner les objets et les collections. Exemple : ClasseSuperieure.ClasseInferieure.Objet.Propriete = True. Ici, on a que l’objet appartient à la classe inférieure, qui elle appartient à la classe supérieure. Sa propriété est fixée à True.
Variable : Espace en mémoire réservé pour y stocker de l’information. Une variable peut être de plusieurs types : numérique (entier, réel), booléen (ou logique), texte (ou chaîne de caractères, aussi appelé string).

Étapes de base dans la conception d’un programme

1. Bien comprendre le problème. La première étape de la conception d’un programme ne doit jamais impliquer le contact direct avec un ordinateur. Un important processus de réflexion et de compréhension doit se faire chez le programmeur avant de se lancer dans la conception. Il peut être utile de bien schématiser les étapes.
2. Diviser pour régner
a. Diviser le problème principal en sous-problèmes.
b. À chaque sous-problème est associé une fonction ou sous-programme. De cette façon, il devient beaucoup plus facile de vérifier l’exactitude du programme en entier. Si un bogue se présente, il est ainsi plus simple de localiser la ligne de code fautive car seulement un résultat intermédiaire s’en trouve erroné. Note : Chacune des étapes qui suivent doit être répétée pour chaque sous-programme.
c. Il est alors possible de débuter la programmation.
3. Déclaration des variables qui vont être utilisées dans le programme ou sous-programme. Dans l’utilisation des variables, il y a toujours deux étapes nécessaires.
Introduction à Visual Basic pour Microsoft Excel 21
Premièrement, la déclaration qui alloue ou réserve un espace en mémoire. Deuxièmement, le stockage utilise l’espace qui lui est réservé pour conserver l’information voulue. Il est aussi fortement recommandé de donner un nom intelligent à ses variables. Un principe fondamental : le nom d’une variable devrait refléter son contenu.
4. Code principal.
a. Obtenir les données nécessaires au calcul;
b. Effectuer le calcul;
c. Afficher le résultat.
5. Une fois le programme et les sous-programmes bien rôdés, il est fortement recommandé de bien documenter celui-ci. L’idéal est de bien décrire le rôle de chaque sous-programme ainsi que leurs arguments. Les lignes de code moins intuitives (par exemple, les éclairs de génie) devraient aussi être bien décrites. Une question à se poser lorsqu’on documente ses routines : en lisant le code ainsi que le texte, est-ce que mes collègues seraient en mesure de bien comprendre l’idée principale de ma programmation ainsi que les principales étapes de mon code ? Évidemment, répondre par la négative à cette question devrait vous inciter à continuer la documentation.

Introduction
Introduction à Microsoft Excel
Qu’est-ce que Microsoft Excel ?
Définitions
Interface
Barres d’outils et de menus
Fenêtre de saisie
Feuille de calcul
Barre d’état
Construction d’un tableau simple
Formatage de cellules et de plages de cellules
Trucs pour l’entrée de données
Effectuer des calculs avec Excel
Saisie d’équations ou de formules
Truc pour la saisie de formules répétitives
Références relatives, absolues et mixtes
Un autre truc pour l’entrée d’équations : Nommer des plages
Insertion de fonctions
Outils complémentaires d’Excel : le solveur.
Installation du solveur
Utiliser le solveur
Exemple d’application
Concepts fondamentaux de la programmation orientée objet
Définitions
Étapes de base dans la conception d’un programme
Programmer en Visual Basic pour Excel
Objets propres à Microsoft Excel
Bâtir une macro simple
Gestion des cellules et des plages de cellules : objets et méthodes de base
Range
Remarque
Truc pour la manipulation répétitive du même objet
Gestion des feuilles de calculs et des classeurs : objets et méthodes de base
Workbooks
ActiveWorkbook
Worksheets ou Sheets
ActiveSheet
Ouverture d’un classeur Excel – Méthode Open
Création d’un nouveau classeur Excel – Méthode Add
Introduction à Visual Basic pour Microsoft Excel 3
Sélection (ou activation) d’un classeur ou d’une feuille de calcul – Méthodes Activate et Select
Sauvegarde d’un classeur Excel – Méthodes SaveAs et Save
Fermeture d’un classeur Excel – Méthode Close
Déclaration de variables
Rappel provenant des sections précédentes
Déclaration de variables scalaires
Déclaration de variables matrices
Assigner et obtenir des valeurs d’une variable matrice  Redim
Création de routines et de fonctions personnelles
Enregistrer une macro
Création de routines personnelles
Appel de routines personnelles
Création de fonctions personnelles
Appel de fonctions personnelles
Programmation conditionnelle
Créer une condition
Programmation conditionnelle simple
Programmation conditionnelle par cas
Programmation itérative
Itération simple (For… Next)
Itération conditionnelle (While… Wend)
Gestion des boîtes de dialogue
Boîte de dialogue InputBox
Boîte de dialogue MsgBox
Une vue d’ensemble de l’éditeur Visual Basic
Suite de l’exemple – Construction du tableau de primes
Éléments de programmation avancée en Visual Basic pour Excel
Objet WorksheetFunction
Utiliser une fonction publique Visual Basic créée par le programmeur à l’intérieur d’un classeur
Créer une fonction publique en 3 étapes simples
Exemple complet
Déclaration de variables de type Object
Déclaration de variables de type Object
Assignation dans une variable de type Object
Méthode InputBox