Learn VBA programming with MS EXCEL


Télécharger Learn VBA programming with MS EXCEL

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


 

Ricco Rakotomalala

R.R. – Université Lyon 2

Généralités sur la programmation VBA sous Excel

 

Points importants. Connaissance de l’algorithmie, langage de programmation Visual Basic. Les instructions sont écrites dans des fonctions (function) et procédures (sub), qui sont regroupées dans des modules. Nous travaillons dans VBE (Visual Basic Editor).

R.R. – Université Lyon 2

Généralités sur la programmation

ALGORITHMIE - PROGRAMMATION

R.R. – Université Lyon 2


Algorithmie vs. Programmation

Algorithmie

•  Solution « informatique » relative à un problème

•  Suite d’actions (instructions) appliquées sur des données

•  3 étapes principales :

1.   saisie (réception) des données

2.   Traitements

3.   restitution (application) des résultats

R.R. – Université Lyon 2

Programme

•     Transcription d’un algorithme avec une syntaxe prédéfinie

•     Visual Basic pour Applications • Même principes fondamentaux que les autres langages objets (Java, C#, etc.) • VBA agit en interaction avec les fonctions prédéfinies disponibles dans la suite Office


Langage interprété : + portabilité application ; - lenteur (R, VBA, Python…)

Langage compilé : + rapidité ; - pas portable

(solution possible : write once, compile anywhere ; ex. Lazarus)

Langage pseudo-compilé : + portabilité plate-forme ; - lenteur (?)

(principe : write once, run anywhere ; ex. Java et le principe JIT)

(Visual Basic pour Applications) est un langage de programmation dédié principalement aux applications Microsoft Office. Il est basé sur le langage Visual Basic, mais ne peut s’exécuter que dans une application hôte Microsoft Office, et non de manière autonome.

R.R. – Université Lyon 2


Etapes de la conception d’un programme (une application)

1.   Déterminer les besoins et fixer les objectifs : que doit faire le logiciel, dans quel cadre vat-il servir, quels seront les utilisateurs types ? On rédige un cahier des charges avec le commanditaire du logiciel (Remarque : commanditaire = maître d’ouvrage ; réalisateur = maître d’œuvre)

2.   Conception et spécifications : quels sont les fonctionnalités du logiciel, avec quelle interface ?

3.   Programmation : modélisation et codage

4.   Tests : obtient-on les résultats attendus, les calculs sont corrects, y a-t-il plantage et dans quelles circonstances ? (tests unitaires, tests d’intégration, etc.)

5.   Déploiement : installer le chez le client (vérification des configurations, installation de l’exécutable et des fichiers annexes, etc.)

6.   Maintenance : corrective, traquer les bugs et les corriger (patches) ; évolutive (ajouter des fonctionnalités nouvelles au logiciel : soit sur l’ergonomie, soit en ajoutant de nouvelles procédures)

Programme : suite d’instructions manipulant des données

LANGAGE VISUAL BASIC

R.R. – Université Lyon 2

Visual Basic possède tous les attributs d’un langage de programmation

Données typées. Visual Basic propose les types usuels de la programmation : entier, réels, booléens, chaîne de caractères.

Structures avancées de données. Gestion des collections de valeurs

(énumérations, tableaux) et des objets structurés (enregistrements, classes)

Séquences d’instructions, c’est la base même de la programmation, pouvoir écrire et exécuter une série de commandes sans avoir à intervenir entre les instructions.

Structures algorithmiques : les branchements conditionnels et les boucles.

Les outils de la programmation structurée : pouvoir regrouper du code dans des procédures et des fonctions. Organisation du code en modules et possibilité de distribuer ces dernières.

Visual Basic n’est pas « case sensitive », il ne différencie pas les termes écrits en minuscule et majuscule.


Type de données

Le type de données définit le type d’opérateurs qu’on peut leur appliquer.

•     Numérique qui peut être réel (double) ou entier (long). Les opérateurs applicables sont : +, -, *, / (division réelle), \ (division entière), mod (modulo)

Exemple : 5 / 2 à 2.5 ; 5 \ 2 à 2 ; 5 mod 2 à 1

•     Booléen (boolean) qui ne prend que deux valeurs possibles : True et False. Les opérateurs sont : not, and, or.

Exemple : True and False à False

•     Chaîne de caractères (suite) qui correspond à une suite de caractères délimitée par des guillemets ‘’ ’’. Les opérateurs possibles sont la concaténation, la suppression d’une souspartie, la copie d’une sous-partie, etc.

Exemple : ‘’toto’’ est une chaîne de caractères, toto on ne sait pas ce que c’est (pour l’instant)

Habituellement, les opérations font intervenir des données de type identique et renvoie un résultat du même type.

Opérateurs de comparaison

Les opérateurs de comparaison confrontent des données de même type, mais le résultat est un booléen

= ; <> ; > ; >= ; < ; <=

égalité

inférieur strict,

différent           Supérieur strict,       inférieur ou égal supérieur ou égal

Exemples

                          5 > 2 à True                                               ‘’toto’’ > ‘’tata’’ à True

 

5 > ‘’toto’’ à illicite

Licite. Comparaison de gauche à droite

basée sur le code ASCII. Arrêt des

                         5 <> 5 à False                   comparaisons dès qu’indécision levée.

Variables et premières instructions

Les correspondent à des identifiants auxquels sont associés des valeurs d’un type donné. Elles matérialisent un espace mémoire avec un contenu que l’on peut lire ou écrire.

Identifiant de la variable, utilisable

Dimensionner. Réserver un espace mémoire .

Indication                  la variable

du type

Affectation. Attribuer

v = 2.5

une valeur à la variable

Opération et

x = v * 2

affectation

= est le symbole d’affectation. A gauche de = on modifie le contenu dans une variable, à droite on lit le contenu d’une variable. C’est pour cette raison que l’instruction v = v + 1 est licite.

La valeur 5 est écrite dans la variable x qui doit être déclarée au préalable.

Déclaration d’une variable


Ecriture et utilisation des fonctions personnalisées dans Excel

FONCTIONS PERSONNALISÉES

R.R. – Université Lyon 2

Programmation des fonctions personnalisées

Une fonction personnalisée est une fonction VBA qui peut appelée dans un classeur Excel. Elle prend en entrée des informations en provenance des feuilles du classeur (principalement) et renvoie une valeur insérée dans une cellule (le plus souvent également).

             Formalisme      Function NomFonction(paramètres) as type de donnée

Est un identifiant qui doit Type de la valeur respecter la syntaxe VBAretournée par la fonction.

Les informations que prend en entrée la fonction, elles prennent la forme nom_parametre as type de paramètre. Il peut y en avoir plusieurs, ils sont séparés par des « , » dans ce cas.

 

Un classeur Excel contenant du code VBA doit être enregistré au format XLSM, prenant en charge les macros. Sinon on perd son code.

 

Programmation dans Visual Basic Editor

 

Utilisation de la fonction dans une feuille Excel

 

Fonction avec plusieurs paramètres


Plus loin avec la programmation…

STRUCTURES ALGORITHMIQUES

R.R. – Université Lyon 2

Branchement conditionnel IF

Permet d’activer une partie du code en fonction de la réalisation d’une condition ou pas.

   

If condition Then bloc d’instructions si la condition est vraie

Else bloc d’instructions si la condition est fausse

End If

(1)   Condition est souvent une opération de comparaison

(2)   La valeur de retour de Condition est de type booléen (True ou False)

(3)   Then doit être sur la même ligne que If

(4)   La partie Else est facultative (ne rien faire si la condition est fausse)



(5)   Il est possible d’imbriquer une autre structure conditionnelle If dans les blocs d’instructions

Branchement conditionnel IF – Un exemple

Entrées : prix HT (réel), catégorie de produit (chaîne)

Sortie : prix TTC (réel)

Public Function MonTTCBis(pht As Double, cat As String) As Double

'déclarer la variable de calcul

Dim pttc As Double

'en fonction de la catégorie de produit

If (cat = "luxe") Then pttc = pht * 1.33

Else

‘la valeur de cat est différente de ‘’luxe’’ pttc = pht * 1.2

End If

'renvoyer le résultat

MonTTCBis = pttc

End Function

Branchement multiple SELECT CASE

Permet d’activer une partie du code en fonction des valeurs prises par une variable de contrôle. Peut se substituer au IF, mais pas toujours, tout dépend de la forme de la condition (condition composée, on doit passer par un IF).

   

Select Case variable

Case valeur 1 bloc d’instructions

Case valeur 2 bloc d’instructions

Case Else bloc d’instructions

End Select

(1)   Variable est la variable de contrôle, elle peut être de n’importe quel type en VBA, y compris un réel ou une chaîne de caractères

(2)   Valeur doit être de type compatible avec variable

(3)   La partie Case Else est facultative

(4)   L’imbrication avec un autre IF ou un autre Select Case (autre variable de contrôle) est possible.


'fonction select case

Public Function MonTTCSelon(pht As Double, cat As String) As Double 'déclarer la variable de calcul

Dim pttc As Double

'en fonction de la catégorie de produit

Select Case cat

Case "luxe" pttc = pht * 1.33

Case Else pttc = pht * 1.2 'toute autre valeur que ‘’luxe’’ End Select

'renvoyer le résultat

MonTTCSelon = pttc

End Function

Un exemple

Entrées : prix HT (réel), catégorie de produit (chaîne)

Sortie : prix TTC (réel)

Plages de valeurs

Select Case variable

Case Is .comparaison valeur bloc d’instructions

Case valeur de départ To valeur de fin bloc d’instructions

Case Else bloc d’instructions

End Select

Il est possible d’introduire des plages de valeurs dans la partie Case de la structure Select Case. La comparaison devient plus sophistiquée.

Variable est un numérique dans ce cas, entier ou même réel.

Plages de valeurs – Un exemple

Entrée : quantité (entier)

'calcul du prix unitaire en fonction de la quantité

Public Function MonPU(quantite As Long) As Double

'variable intermédiaire

Dim pu As Double

'selon les valeurs de quantité

Select Case quantite

Case Is < 100 pu = 0.5

Case 100 To 200 pu = 0.3

Case Is > 200 'Case Else aurait fait l'affaire aussi pu = 0.2

End Select MonPU = pu

End Function

Sortie :     prix unitaire (réel)

Calcul :     quantité < 100 à p.u. = 0.5 100 ? quantité ? 200 à p.u. = 0.3 quantité > 200 à p.u. = 0.2


Boucle POUR (FOR)

est contrôlé par un indice.

   

For indice = val.départ to step pas bloc d’instructions

Next indice

(1)   Indice est un type ordonné, très souvent un numérique

(2)   pas contrôle le passage d’une valeur à l’autre d’indice, si omis, pas = 1 par défaut

(3)   Next entérine le passage à la valeur suivante de indice, si cette prochaine valeur est > à , on sort de la boucle

(4)   doit être superieure à val.départ pour que l’on rentre dans la boucle

(5)   Si pas est négatif, doit être inférieure à val.départ cette fois-ci

(6)   L’instruction Exit For permet de sortir prématurément de la boucle

(7)   On peut imbriquer des boucles (une boucle à l’intérieur d’une autre boucle)

Boucle FOR – Un exemple

Entrée : n (entier)

Sortie :     S (réel)

Calcul :     S = 1² + 2² + … + n²

'calcul de la somme des carrés des valeurs

Public Function MaSommeCarre(n As Long) As Double

'variables de calcul (s pour la somme, i : indice)

Dim s As Double, i As Long

'initialisation s = 0

'boucle avec l'indice i

For i = 1 To n Step 1 s = s + i ^ 2

‘Next joue le rôle de l’incrémentation (i suivant)

Next i

'renvoyer le résultat

MaSommeCarre = s

End Function

Boucle TANT QUE… FAIRE (DO WHILE…LOOP)

est contrôlé par une condition. Attention à la boucle infinie c.-à-d. la condition permettant de sortir de la boucle n’est jamais déclenchée.

   

Do While condition

Bloc d’instructions

Loop

(1)   Condition est un booléen, c’est souvent une opération de comparaison

(2)   On continue l’exécution TANT QUE la condition est vraie ; si la condition est fausse, on sort de la boucle

(3)   Exit Do permet de provoquer la sortie prématurée de la boucle

Si la condition est fausse d’emblée. On peut ne pas rentrer dans la boucle.

Boucle DO WHILE…LOOP (un exemple)

Entrée : n (entier)

Sortie :     S (réel)

Calcul :     S = 1² + 2² + … + n²

'calcul de la somme des carrés des valeurs

Public Function MaSommeCarreWhile(n As Long) As Double

'variables de calcul

Dim s As Double, i As Long

'initialisation s = 0

'il nous revient aussi d'initialiser l'indice i = 1

'boucle TANT QUE

Do While (i <= n) 'sommer s = s + i ^ 2

'pas de next, nous devons incrémenter l'indice i = i + 1

Loop

'renvoyer le résultat MaSommeCarreWhile = s

End Function

Boucle FAIRE…TANT QUE (DO…LOOP WHILE)

est contrôlé par une condition.

   

Do

Bloc d’instructions

Loop While condition

                                                     On est sûr de rentrer au moins une fois dans la boucle.      

Le choix de la bonne structure (Faire.. Tant Que ou Tant Que..

Faire) dépend du problème à traiter

Les variantes des boucles DO

Les boucles DO contrôlées par une condition sont très riches en VBA.

Do { While | Until } condition

[ statements ]

[ Exit Do ]

[ statements ]

Loop -or-

Do

[ statements ]

[ Exit Do ]

[ statements ]

Loop { While | Until } condition

                                                                             Le Répeter… Jusqu’à (Until) existe aussi.


Le type « plage de cellules » spécifique à Excel

LE TYPE RANGE

R.R. – Université Lyon 2

Le type RANGE

Le type RANGE désigne une plage de cellules, c’est un type spécifique à Excel.

 

La fonction MaSommeRange() est censée faire la même chose que la fonction standard SOMME() d’Excel.

Exploiter le type Range en VBA

 

Une boucle adaptée pour les plages de cellules – For Each

La boucle For Eachest adaptée au parcours des collections.

Or une plage de cellules est une collection de cellules.

'Travail sur le type Range avec un For Each

Public Function MaSommeRangeEach(plage As Range) As Double


'variables intermédiaires

Dim s As Double, cellule As Range 'initialisation de la somme s = 0



'parcours de la plage de cellules

For Each cellule In plage s = s + cellule.Value

Next cellule

'renvoyer le résultat MaSommeRangeEach = s

End Function

Une cellule est une plage de cellules avec une seule cellule.

plage fait figure de collection à traiter. Qu’importe le sens du parcours ici (ligne par ligne, ou colonne par colonne).

C’est bien la valeur contenue dans la cellule qui est exploitée pour la somme.


Type spécial qui peut contenir toutes sortes de valeur

LE TYPE VARIANT

R.R. – Université Lyon 2

Le type Variant

Le type de variant peut gérer tout type de valeurs. Il est très souple, particulièrement commode quand on ne connaît pas à l’avance le type à utiliser. Mais attention, il ne faut pas en abuser, il est très lent parce que multiplie les vérifications à chaque accès à la variable correspondante.

 

Le type Variant est vraiment très souple

On peut s’en servir pour renvoyer un tableau. Une fonction peut donc renvoyer plusieurs valeurs d’un coup, à l’instar des fonctions matricielles d’Excel (il faut valider la saisie de la fonction avec la séquence de touches CTRL + MAJ + ENTREE).

 

Programmation des macros – Travailler directement sur les feuilles

LES MACROS (1)

R.R. – Université Lyon 2

Macros ?

Les macros sont également des procédures que l’on crée à l’intérieur d’un module. Mais, à la différence des Function, ce sont des Sub() sans paramètres qui peuvent manipuler (accéder et modifier) directement les objets Excel (classeurs, feuilles, cellules, graphiques, scénarios, tableaux croisés dynamiques…).

Ils ne s’exécutent pas de la même manière. Au lieu de les insérer dans une cellule, ils se lancent globalement via le bouton MACROS dans le ruban DEVELOPPEUR.

 

Enregistreur de macros

Une manière simple de générer une macro est de lancer l’enregistreur de macros.

Du code VBA est automatiquement généré.

 

Enregistreur de macros - Bilan

Avantages :

•     Il n’y a pas plus simple pour produire du code, on peut créer et exécuter une macro sans aucune notion de programmation

•     Il nous donne des indications précieuses sur les commandes associées aux objets Excel

Inconvénients :

•     On travaille à structure fixée, si la configuration de la feuille change, il n’est pas possible de lancer la macro

•     On ne bénéficie de la puissance des structures algorithmiques

En définitive :

•     Il peut nous aider à rédiger notre code en nous donnant des pistes sur la syntaxe des commandes et les objets adéquats à manipuler (ex. imprimer automatiquement des feuilles, on lance l’enregistreur une fois, on intègre son code dans le notre à l’intérieur d’une boucle).

Ecriture des macros – Les trois principaux objets

Ecrire directement des macros est simple une fois assimilé la philosophie de l’approche, et identifié les principaux objets et l’accès à leurs propriétés et méthodes (l’enregistreur peut nous y aider).

Activer (sélectionner) le

               Classeurs         Workbooks(‘’’’).Activate    classeur dont le nom de

fichier est ‘’’’

Dans le classeur courant, activer la feuille de calcul

dont le nom est ‘’Feuil1’’ (visible dans la languette Sheets(‘’Feuil1’’).Activate  au bas de la feuille) Feuilles

Workbooks(‘’’’).Sheets(‘’Feuil1’’).Activate         On peut combiner les écritures.

Dans la feuille courante du classeur courant, insérer

De nouveau, on peut combiner.

                                                              Cells(1,1).Value = 15      la valeur 15 dans la cellule ligne n°1, colonne n°1

c.-à-d. en A1, les coordonnées sont absolues ici.

Cellules

Sheets(‘’Feuil1’’).Cells(1,1).Value = 15

Exemple de macros – Simulation valeurs de TVA

 

Travailler sur les sélections de l’utilisateur

LES MACROS (2)

R.R. – Université Lyon 2

Sélection simple

Comment programmer une macro qui manipule directement une plage de cellules sélectionnée par l’utilisateur ? Attention, nous ne sommes pas dans la même configuration que les fonctions personnalisées ici, nous n’insérons pas un résultat dans une cellule, nous manipulons et modifions directement la plage sélectionnée.

 

Sélection simple – On aurait pu écrire…

 

Sélection simple – Un second exemple

10

15

20

13

36

7

8

28

Identifier la première cellule contenant la valeur minimale dans une plage, mettre sa police en bleu.

Sub MonMinBleu()

‘variables intermédiaires

min va servir de cellule témoin

Dim cellule As Range, min As Range

'initialisation du témoin sur la 1ère cellule

Set min = Selection.Cells(1, 1)

'parcourir

                   For Each cellule In Selection      Range est un objet. Une

'comparer avec le contenu de la cellule témoin affectation pour une variable If (cellule.Value < min.Value) Then objet doit être réalisée à

'màj dela cellule témoin

l’aide de l’instruction Set

Set min = cellule

End If

Next cellule

'mettre la couleur pour la cellule minimale

.ColorIndex = 5

End Sub

Sélections multiples

Une sélection peut être multiple aussi c.-à-d. contenant plusieurs ‘’zones’’

 

                                          Très curieusement, le même mot clé Selection peut être exploité.

                                              Selection.Areas.Count  Nombre de ‘’zones’’ dans la sélection.

Accès à la zone n°k (qui est de type Range).

Selection.Areas(k)

Areas est une collection de zones.

Sélection multiple – Un exemple

 

BOÎTES DE DIALOGUE

 

Boîtes de dialogue standards

Les boîtes de dialogue permettent d’interagir avec l’utilisateur. Nous nous en tenons aux plus simples ici. InputBox() pour la saisie, MsgBox() pour l’affichage.

EXPLOITER LES FONCTIONS NATIVES D’EXCEL

 

Accéder aux fonctions natives d’Excel dans nos programmes Excel dispose de fonctions natives puissantes. Nous pouvons y accéder dans nos programmes VBA.

 

De la documentation à profusion (inutile d’acheter des livres sur VBA)

Site de cours de Microsoft

VBA sous Excel : (v=office.14).aspx

Structures de décision : (v=vs.90).aspx

Structures de boucle : (v=vs.90).aspx

Autres cours et supports

Le Compagnon Info :

Excel Easy : -

Cours VBA Gratuit :

Excel VBA for Complete Beginners : Et d’autres très nombreux encore… faites chauffer les moteurs de recherche.

 



18