vba

EXCEL-VBA

EXCEL-VBA

Première partie : Introduction

1 On parle beaucoup de macros sans trop savoir ce que cela recouvre :

 C’est un petit programme écrit en Visual Basic : c’est une sorte de fichier « batch » ou « script » qui va

être exécuté dans un logiciel donné. Ce langage appartient au groupe POO (Prog. Orientée Objet)

 Visual Basic Application (VBA)° est une variante de VB6 qui est déjà compilée et directement

interprétée par une application OFFICE

 Excel, Word ou PowerPoint seulement permettent de créer des macros en VBA. À l’aide d’un

enregistreur de macros.

2 Utilisations potentielles des macros :

 Combiner des commandes variées qui existent pour en faire une seule commande.

 Créer de nouvelles commandes et de nouvelles fonctions supplémentaires qui iront rejoindre les

quelques 300 déjà disponibles.

 Automatiser des actions trop répétitives

 Améliorer des commandes existantes.

 Créer des interfaces personnalisées comme par exemple des boîtes de dialogue qui permettront la

saisie d’informations à l’aide de champs.

3 Notre projet est de partir du plus simple (utilisation) pour aller au plus compliqué (création) selon le plan

suivant :

1. Utilisation de macros déjà créées et présentes sur l’ordinateur.

2. Création-enregistrement-utilisation-modification : d’une macro par reproduction de

« manipulations » dans un classeur.

3. Création d’un bouton de commande- D’une barre d’outils « macros personnelles » pour faciliter

l’utilisation des macros, personnalisation de boutons.

4. Création de nouvelles fonctions qui s’intégreront dans la boîte de dialogue « fonctions »

5. Intégration de macros trouvées sur internet

6. Adaptation à vos besoins avec l’éditeur VBEditor.

7. Création de macros avec l’éditeur VBA de Excel et l’utilisation du langage Visual Basic.



Deuxième partie : Importation et Utilisation d’une macro

4

 Il est important, avant de les utiliser, de savoir à quoi elles servent et à quel(s) objet(s) d’une feuille elles

s’appliquent : allons voir sur un exemple de mise en forme d’une cellule.

 Commençons par importer une macro préparée à l’avance

5 Ouvrir Visual Basic-Importer : (Ouvrir d’abord un classeur)

 Faire Alt + F11 pour passer dans Visual Basic Editor

 Aller dans le menu Fichier > Importer puis allez dans

DossierPLC1 > Macros pour ouvrir FormatCell.bas

6 Exécuter la macro : Commencez par revenir au classeur par Alt + Q

 Saisissez un mot dans une cellule puis sélectionnez cette cellule.

 Allez dans le menu Outils > Macro > Macros pour sélectionnez FormatCell

 Finissez par

 Mieux : Aller dans Affichage > Barre d’outils > Visual Basic.



maurice.ocquidant@free.fr Page 1 04/12/2011

 Cliquez sur () pour ouvrir la boîte des macros disponibles.









7

Visualiser-Modifier la macro :



 Dans la barre d’outils Visual Basic, cliquez sur () et

sélectionnez FormatCell

 Cliquez sur le bouton Modifier

 VBEditor est activé et affiche le code de la macro.

 On découvre les propriétés du format de la cellule

 Modifiez quelques paramètres:

remplacez False par True dans Shadow =

12 par 16 dans Size = ......

 Fermez VBE ou bien Alt + Q

 Testez votre macro sur une cellule.







Troisième partie : Création-Enregistrement d’une macro

8  Nous allons créer une macro directement par reproduction de manipulations dans une feuille.

 On entre d’abord une formule dans A1. La macro vise à transmettre cette formule aux cellules de la

plage A1 :J10 (on veut transmettre la formule =ALEA())









maurice.ocquidant@free.fr Page 2 04/12/2011

9 Lancer-Enregistrer : Sélectionnez A1 dans la feuille (sans

le point d’insertion à l’intérieur)

 Dans la barre d’outils Visual Basic, cliquez sur ()

pour ouvrir cette boîte d’enregistrement.

 Saisissez le Nom de la macro (essai ici)

 Enregistrer la macro dans :Deux options à retenir :

 Ce Classeur : La macro ne sera disponible que dans le

classeur actuellement ouvert.

 Classeur de macros personnelles : la macro sera disponible dans tous vos classeurs.

 Choisir ici la 1ère option et finissez par OK.



Saisie de la macro : Cette petite barre d’outils apparaît (en principe)

 Tout ce que vous allez faire va constituer la macro.

 Faites la sélection de la plage A1 :J10 comme si vous transmettiez une formule (A1 :A10 puis J1:J10)

 Cliquez sur () pour arrêter l’enregistrement : c’est fini !!!

 Sinon retourner dans Outils > Macro pour l’arrêt.





Utiliser :

 Dans la cellule A1, entrez la formule =alea()

 Cliquez sur  dans la barre d’outils VB.

 Sélectionnez la macro PERSO.XLS !essai

 Cliquez sur Exécuter…..C’est fini

 Allez voir le code généré (voir §7) dans la

rubrique « Modules » de la fenêtre des projets.

10 Relatif-Absolu

 La macro précédente se réfère à la cellule A1 de façon absolue et transmet seulement dans la plage

A1:J10 une formule saisie dans A1. (option par défaut)

 Il suffit de cliquer sur le bouton à droite de () pour passer en Référence

relative.

 On refait tout ce qui précède (voir §9) avec ces 2 différences

 Ne pas oublier de sélectionner la référence relative

 L'utilisation se fera à partir d'une cellule quelconque pour agir sur une plage 10x10.

 Allez voir le code généré comme indiqué en §7.





Quatrième partie : Création d’un bouton de commande

11  Nous allons créer un bouton qui va nous permettre d’activer plus directement la macro

 Méthode1 : on se sert d’une image et le bouton sera dans la feuille

 Méthode 2 : On va créer un bouton et le mettre dans une barre d’outils que nous allons également

créer pour y mettre nos boutons de macros (utile si ces macros servent dans différents classeurs)



12 Bouton de commande (Méthode simple)



 Il est possible d’utiliser une image pour lancer une macro

maurice.ocquidant@free.fr Page 3 04/12/2011

 On commence par insérer une image (DossierPLC1 > Images >Boutons .. )

 Faire un ClicD sur l’image pour choisir Affecter une macro dans le menu contextuel

 Sélectionner alors la macro « Essai » dans la boîte de dialogue.



13 Barre d’outils :

 Dans le menu Affichage>Barre d’outils allez

cliquer sur Personnaliser.

 Dans l’onglet Barres d’outils, cliquez sur Nouvelle

 Donnez un nom à cette barre ( « Mes Macros »

par exemple)



 Cette petite barre s’affiche dans votre feuille, il suffit de la mettre en place

dans vos barres d’outils par un « glisser-déplacer ».



14 Bouton de commande

 Dans le menu Affichage>Barre d’outils allez cliquer sur Personnaliser

 Dans l’onglet Commandes sélectionnez la Catégorie Macros



 Transportez l’icône « Smile » qui se trouve dans la partie droite de la

fenêtre dans la petite barre de Macros créée ci-dessus.



15 Personnaliser le bouton-Associer la macro (laisser la boîte ouverte)

 Faites alors un ClicD sur « Smile » dans votre barre de

macros pour ouvrir ce menu à droite.

 Donnez un nom à votre macro dans &Bouton perso

 Cliquez sur Modifier l’image du

bouton pour changer votre image.

 Dans ce même menu cliquez sur

Affecter une macro puis choisir alors la macro

que vous voulez activer avec le bouton.

16 Créer son icône :

 Allez dans Éditeur de boutons et créez-modifiez une image.

 Il suffit de cliquer sur une couleur puis sur l’un des 16x16 points pour

faire sa petite icône personnelle.







Cinquième partie : Création de fonctions

17 Macros complémentaires

 On peut commencer par utiliser ce qui est disponible : allez dans le menu

Outils > Macros complémentaires

 Cochez les catégories qui vous intéressent (Utilitaire d’analyse en Maths)

 Ces nouvelles fonctions viendront compléter celles qui existent déjà.

 On peut ainsi essayer une fonction supplémentaire : =ALEA.ENTRE.BORNES(x ;y) qui retourne une

valeur aléatoire entière de l’intervalle [x, y].



maurice.ocquidant@free.fr Page 4 04/12/2011

 Par exemple, entrez dans A1 [=ALEA.ENTRE.BORNES(1,6)] puis appliquer la macro essai

 Faites ensuite un comptage des 1 dans A11 avec =NB.SI(A1:J10;1)...etc









18 Création de fonctions nouvelles



 Il faut d’abord ouvrir VBE : ClicDroit

sur Feuil1 (en bas) et choisir

Visualiser le code ou bien Alt

+F11.ou bien par le menu Outils >

Macro > VBEditor

 3 fenêtres doivent être ouvertes :

 la fenêtre d’édition du code à droite

 La fenêtre des projets à gauche

 La fenêtre des propriétés

 Sinon allez dans le menu Affichage

pour les ouvrir.





 Dans le paragraphe suivant nous allons essayer de définir une

nouvelle fonction : « cube de ».

19  Pour reprendre les options d’enregistrement (§9) on retrouve dans

VBAProject les deux types de Projets

 Classeur1 : pour les macros affectées à ce classeur seul.

 PERSO.XLS : pour les macros disponibles partout.

 Il faudra donc, en général, sélectionner

VBAProject (PERSO.XLS) pour disposer

partout de ces fonctions personnelles

 Dans le menu Insertion cliquez sur Module pour mettre en place

Module1 (nom du module par défaut)

 Entrez ce code pour définir votre

fonction.

 On pourra aller dans les propriétés et la fenêtre

(Name) pour changer le nom de la fonction.

 Revenons dans un classeur en fermant VBEditor (Alt + Q)







 Cliquez sur l’icône puis puis

 On entre dans la procédure classique d’utilisation d’une fonction.



Sixième partie : Exemples de fonctions







maurice.ocquidant@free.fr Page 5 04/12/2011

20

 Ouvrez cette barre d’outils : Affichage > Barre d’outils >

Visual Basic

Conversion en euros

 Cliquez sur pour ouvrir Visual Basic (ou Alt + F11)

 Allez dans le menu Insertion > Module pour mettre en place un nouveau module.

 Public Function EURO(F) ‘F est la variable de la fonction EURO

EURO = Round(F / 6.55957, 2) & « € » ‘EURO est le résultat arrondi avec 2 décimales

End Function ‘On peut tester la fonction EURO (on la trouvera dans la catégorie « Personnalisées »)



21 Function VolSph(rayon)

VolSph=rayon^3*4*3.14159/4

Volume de la sphère

End Function



22 Public Function nomjour(dat$) As String ‘Fonction nomjour (pour une date donnée)

nomjour = WeekdayName(Weekday(dat$, vbMonday))

Nom du jour d’une date donnée

End Function ‘soit on entre la date 22/08/2001 dans une cellule pour cliquer dedans ensuite soit directement

dans la boîte de dialogue mais sous la forme « 22/08/2001 » (ne pas oublier les guillemets)



23  Pour rechercher l’adresse du plus grand nombre d’une plage donnée



Function MaxAdr(plage as Range)

valrech = Application.WorksheetFunction.Max(plage)

Set cellmax = plage.Find(valrech) Renvoie l’adresse du plus grand

MaxAdr= cellmax.Address nombre d’une plage donnée

End Function

 Dans une cellule la formule =MaxAdr($A$1 :$B$30) donnera la référence de la cellule contenant le

nombre le plus grand.

24 Function Hypot(a As Single, b As Single) As Single

Hypot = Sqr(a^2+b^2) Calcul de l’hypoténuse

End Function



25  Ecrire l’heure au format « 14 h 25 mn 17 s » . Soit on entre l’heure dans la boîte sous la forme

14 :05 :25 soit dans une cellule sous la forme 08-55-00 Changer l’heure de format

Public Function lheure$(T$)

lheure$ = Left$(T$, 2) + " h " + Mid$(T$, 4, 2) + " mn " + Mid$(T$, 7, 2) + " sec"

End Function



26  Ecrire dans une cellule le nom de la feuille :

Nom de la feuille

Function Nomfeuille() As String

Application .Volatile ‘doit permettre de réajuster le nom en cas de modification

Nomfeuille=Application.Caller.Worksheet.Name

End Function









maurice.ocquidant@free.fr Page 6 04/12/2011

27  Faire la somme des cellules ayant une couleur donnée



Function CouleurCell(Range, Optional Couleur)

Dim Cell As Object

For Each Cell In Range

If Cell.Interior.ColorIndex = Couleur Then CouleurCell = CouleurCell +1

Next Cell

End Function Nombre des cellules ayant une

couleur donnée

 Pour appeler cette fonction dans la feuille de calcul

Taper, par exemple, dans une cellule l'instruction:

(3 représente la couleur rouge) =CouleurCell(A1:A9;3)

 A noter que =CouleurCell(A1 :G500 ;-4142) doit donner le nombre des cellules de la plage vide.



28  Couleur d’une cellule : connaître son code VBA



Function Couleur(cellule As Range)

n = cellule.Interior.ColorIndex

Couleur = n

End Function



 Il suffit d’écrire dans une cellule =Couleur($A$1)

pour obtenir le code de la couleur de A1



29  Pour trouver l’adresse d’un mot donné dans MaPos = InStr(Cherchaine, mot)

une plage donnée If MaPos > 0 Then

Cherchmot = cell.Address

Function Cherchmot(mot As String, plage As Range) End If

Dim Cherchaine, MaPos Next



For Each cell In plage End Function

Cherchaine = cell.Text









maurice.ocquidant@free.fr Page 7 04/12/2011

Exemples orientés mathématiques

30 Function PGCD(P As Long, q As Long) As Long Do While r > 0 ‘Boucle de l’algorithme

Dim r As Long P=q

P = Abs(P) ‘p et q > 0 q=r Calcul du PGCD de 2 nombres p et q

q = Abs(q) r = P Mod q

Avec l’algorithme d’Euclide

r = P Mod q Loop

PGCD = q

End Function

31 Option Explicit For Temp_1 = 3 To Sqr(Nombre) Step 2

Dim Nombre As Long If Nombre Mod Temp_1 = 0 Then GoTo Non

Dim Temp_1 As Long Next Temp_1

Premier = True

Function Premier(ByRef Nombre As Long) As Boolean Exit Function

If Nombre Mod 2 = 0 And Nombre 2 Then GoTo Non: Nombre est-il premier ?

Non Premier = False (moins de 10 chiffres)

If Right(Str(Nombre), 1) = "0" Or Right(Str(Nombre), End Function

1)_ ="5" And Nombre 5 Then GoTo Non:





32 Public Function Base10X(ByVal Base As Byte, End If

ByVal Nombre As Long) As String Result = Chiffre & Result

Dim Result As String Nombre = (Nombre - ChifNomb) / Base

Dim Chiffre As String Loop While Not Nombre = 0

Dim ChifNomb As Byte Base10X = Result

If Base > 1 And Base 9 Then En Base quelconque (de 2 à 37)

Chiffre = Chr(55 + Chiffre)

33 Private Const DICTIONNAIRE = _ Nombre10 = Nombre10 + (Chiffre * FromBase ^ Puiss)

"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ" Puiss = Puiss - 1

Public Function BaseX(ByVal Nombre As String, Next

ByVal _ FromBase As Integer) As String Else

Dim Nombre10 As Long ' --- Nombre en base 10 ----------- Nombre10 = Nombre

Dim Puiss As Integer ' --- Puissance -------------------------- End If

Dim i As Integer ' --- Index ---------------------------------- BaseX = Nombre10

Dim Chiffre As Integer ' --- Chiffre traité ------------------- End Function

If FromBase 10 Then

Puiss = Len(Nombre) – 1‘Puissance base= Longueur(N)–1 TransformeNombre écrit en Base X

For i = 1 To Len(Nombre) En Base 10 (de 2 à 37)

…. Chiffre = InStr(1, DICTIONNAIRE,

Mid$(Nombre, i, 1), 1) - 1

‘Mid$ renvoie le caractère de « Nombre » se trouvant à la position i

‘LnStr renvoie la valeur du caractère en fonction de sa position dans

la chaîne DICTIONNAIRE en comparaant avec Mid$



34  Traitement d’une erreur de saisie If MaValeur > 100 Then Error 5000 ' Erreur d'exécution

Range("A1").Value = MaValeur

(Code qui sera lié à une TextBox)

Exit Sub

Sub EvalueValeur()

ValErreur:

On Error GoTo ValErreur

MsgBox "La valeur est incorrecte.Tapez une valeur comprise

GetInput:

entre 0 et 100"

MaValeur = InputBox("Tapez votre valeur (Entre 0

Resume GetInput

et 100)", "Valeur")

End Sub

If MaValeur = "" Then Exit Sub ' Sort si aucune valeur

Traitement d’une erreur de saisie

ou Annuler







maurice.ocquidant@free.fr Page 8 04/12/2011

Septième partie : Intégration de macros existantes.

35 Adresses (pour obtenir des codes)

 Un site qui contient dans les 1500 codes : www.vbfrance.com (orienté VisualBasic 6)

 http://dj.joss.free.fr (orienté VBAXL, c’est pour nous)

 http://www.mindspring.com/~tflynn/excelvba3.html#Printing : Nombreux exemples



Méthode 1 : importer le texte de la macro (format html, doc, …)

 J’en ai sauvegardé quelques-unes dans DossierPLC1 > Macros > ExcelVBA Examples(2)

 Copiez (Ctrl C) le code source de ShowStars après l’avoir sélectionné de Sub ShowStars() à End Sub.

 Revenez dans VBEditeur et sélectionnez VBAProject(Classeur1)

 Ouvrez un nouveau module (par Insertion > Module)

 Collez (Ctrl V) le code source dans la fenêtre de code. C’est fini, la macro est intégrée.Testez



Méthode 2 : importer le code de la macro (format *.bas)

 Dans le classeur passez dans le VBEditor.

 Fichier > Importer depuis DossierPLC1>macros>LignFeuil.bas par exemple

 A noter qu’il est tout aussi simple de faire la manœuvre inverse : exporter une macro.



Huitième partie : écrire une macro.

36  On en arrive au coeur du problème : l'écriture de macros en Visual Basic 6

 Un conseil : automatisez le plus possible la génération du code à l'aide des outils précédents puis

modifiez, améliorez ce qui existe.....

 Notre projet n'est pas de faire un cours de programmation mais de mettre en place quelques notions.

37 Les objets

 Les objets sont le fondement de VB dont la fonction essentielle sera de modifier, d'agir sur ces objets

 Tout objet conceptualisé est caractérisé par des propriétés et des méthodes agissantes (exemple :

l'objet feuille de calcul est une classe d'objets "Sheet") à la différence d'une collection qui sera

formée des feuilles d'un classeur : cette collection sera nommée "Sheets"

 Un objet peut contenir d'autres objets (un classeur contient des feuilles)

 Pour accéder à un objet et lui appliquer une méthode, on indique le chemin d'accès :

Workbooks("MonClasseur").Sheets("MaFeuille").Activate (on active la feuille "MaFeuille" de la collection

du classeur "MonClasseur" de la collection des classeurs ouverts).

 Lorsque un objet est actif, il est inutile de le référencer : ainsi Sheets("MaFeuille").Activate suffit pour

activer cette feuille dans le classeur actif.

38 Les propriétés = état d'un objet

 Ce sont des attributs de l'objet ou des aspects de son comportement : nombre des feuilles d'un

classeur, classeur actif ou non...

 Les propriétés prennent des valeurs spécifiques qui distinguent les objets d'une même collection :

couleur, taille...de la collection des polices

 Ces propriétés peuvent être modifiables ou pas (date de création par exemple).

 Les valeurs sont de 3 types :

 Chaîne de caractères (jusqu'à 2 milliards de caractères, toujours entre guillemets)

 Valeur numérique du style (3; 87706; -123,4071; 3,76E5 ...)

maurice.ocquidant@free.fr Page 9 04/12/2011

 Valeur booléenne (True-False)

 Pour modifier une propriété d'objet : la syntaxe sera AccèsObjet.Propriété = valeur

Exemple : Selection.Font.Size = 14 pour mettre la police de la plage active en taille 14



39 Les méthodes = comportement d'un objet

 Ce sont les actions q'un objet (ou une collection d'objets) peut exécuter

 Pour appliquer une méthode la syntaxe sera :Objet.Méthode ou Collection.Méthode :

Exemple : WorkBooks("Classeur1").Sheets("Feuil3").Actvate active la feuille3 de Classeur1.

Worksbooks.Close ferme tous les classeurs ouverts



40 Saisie d'une macro depuis un classeur

 Outils > Macro > Nouvelle macro et complétez la boîte

 Choisissez "Classeur de macros personnelles" ...OK



 Fermez aussitôt l'enregistrement ()

 Faites Alt + F11 pour aller dans VBE

 Un embryon de macro est présent dans le dossier

Modules de VBAProject (PERSO.XLS) sous le

nom Module1 ou Module2..

 Dans un premier temps, inutile de modifier ce nom.



41 Saisie d'une macro directement (module)

 Depuis un classeur Excel, ouvrez VBEditor (Alt + F11 ou par le menu Outils >

Macro).

 Sélectionnez : soit le classeur ouvert (ici Classeur1) si on veut que la

macro soit uniquement disponible dans ce classeur

soit PERSO.XLS (comme ici) pour qu'elle soit disponible partout.

 Dans le menu Insertion choisir Module pour ouvrir une fenêtre de

code vide.

 On entrera alors le code de la macro entre Sub NomMacro( ) et End Sub.





Exemples

42 Exemple :

 Boîte de dialogue « MsgBox : (En gras les mots imposés par VBA)

Sub dialogue() ‘Une macro est intitulée dialogue

Message= MsgBox(« Etre ou ne pas être ? »,vbYesNoCancel) ‘Message recevra votre réponse

If Message = vbYes Then ‘Vous avez appuyé sur le bouton OUI

MsgBox “Vous avez répondu OUI” ‘Cela ouvre une boîte avec un message

ElseIf Message = vbNo Then ‘Examen des autres possibilités

MsgBox “Vous avez répondu NON”

ElseIf Message =vbCancel Then

MsgBox “Vous ne savez pas”

End If ‘Fin de l’analyse des réponses

End Sub ‘Fin de la macro

 Revenez dans la feuille Excel (Alt + Q) puis cliquez sur () pour ouvrir une boîte et y lancer la macro

nommée « dialogue »





maurice.ocquidant@free.fr Page 10 04/12/2011

43  Ombrer une ligne sur deux dans une feuille

 Application :dans une feuille déjà remplie



Sub LigneFeuil() ‘déclaration de la procédure LignFeuil

Dim compteur As Integer ‘déclaration de la variable compteur

Dim Ligne As Variant ‘déclaration de la variable Ligne

Cells.Interior.ColorIndex = 2 ‘On met en blanc toutes les cellules de la feuille

Ligne = Range("A1").End(xlDown).Address ‘On déterminel'adresse de la dernière ligne saisie

Ligne = Range(Ligne).Row ‘Ligne reçoit pour valeur le numéro de cette ligne

If Not Ligne / 2 = Int(Ligne / 2) Then

Ligne = Ligne + 1 Macro n°2 LignFeuil

End If ‘On vérifie la parité de Ligne

For compteur = 2 To Ligne Step 2

Range(compteur & ":" & compteur).Select ‘Sélection de la ligne "compteur"

Selection.Interior.ColorIndex = 15 ‘Ombrage des cellules de cette ligne = 15

Next compteur

End Sub

44  Les différentes couleurs des cellules avec les codes





Sub NbCouleur() ‘Il n’y a que 56 couleurs disponibles

For i = 1 To 56

Sheets("Feuil1").Activate

Range("A" & i).Select

With Selection.Interior

.ColorIndex = i

.Pattern = xlSolid

End With

Range("B" & i).Value = n

Next i

End Sub

45  Pour Compter dans une plage le nombre des occurrences d’une valeur donnée



Sub compter()

Compteur = 0

Set plageCherche = Application.InputBox(Prompt:="Sélectionner la plage de recherche", Type:=8)

ValCherchée = Application.InputBox(Prompt:="Quelle valeur cherchez-vous?", Type:=1) ‘Type = 2 pour des caractères

For Each Item In plageCherche

If Item.Value = ValCherchée Then Compteur = Compteur + 1

Next Item

MsgBox "Il y a " & CStr(Compteur) & " valeur(s) identique(s)" End Sub

46  Pour Trouver les numéros de ligne et colonne de la cellule active



Sub plage()

Dim plage As Range

Set plage = Selection

MsgBox "Ligne:" & plage.Row & ",Colonne:" & plage.Column End Sub

47  Exporter un graphique d’une feuille sous forme d’image (format *.jpg)



Sub GraphJPG()

Dim MonGraph As Chart

Set MonGraph = ActiveSheet.ChartObjects(1).Chart

MonGraph.Export Filename:="C:\Mes Documents\graph1.jpg", filtername:="JPG"

End Sub INDISPENSABLE





maurice.ocquidant@free.fr Page 11 04/12/2011

48  Affichage aléatoire de n entiers différents entre p et q (p P - 1 Then

MsgBox "DEBUT :" & Time GoTo Fin

j=2 End If

Bouclen: GoTo Bouclen

i=2 Fin:

k = 2 + Range("A" & j).Value Call tableau ‘Macro qui permet de ranger en tableau (§43)

Bouclei: MsgBox "FIN :" & Time

n = Range("A" & i).Value End Sub

If n * n > k Then

GoTo Vrai On peut chronométrer avec la variable « Time » les temps

End If d’affichage. Avec un PentiumIII 600 :

i=i+1

10 000 premiers en 1mn ; 20 000 en 2mn22 (224737)

40 000 en 5mn36 et 65 000 en 11mn10 (814 279)



54  Pour transformer la colonne A de valeurs en un tableau de 10 colonnes.

 La première ligne vide de la colonne A est détectée automatiquement.

 Attention : la macro précédente y fait appel.



Sub tableau() ‘Les valeurs sont dans la colonne A l = 1 + (i - c) / 10

Dim i As Long, k As Variant, c As Long c=c+1

Ligne = Range("A1").End(xlDown).Address Cells(l, c).Formula = k

Ligne = Range(Ligne).Row ‘Dernière valeur dans A Next i

For i = 0 To Ligne For j = l + 1 To Ligne

k = Range("A" & i + 1).Value Cells(j, 1).Formula = ""

c = i Mod 10 ‘Calcul du numéro des 10 colonnes Next j

End Sub

55  Distribuer 5 cartes aléatoirement (avec remise)



Sub cartes()

Randomize ‘Activation de la fonction Randomize

For i = 1 To 5

k = 1 + Int(Rnd * 52) ‘tirage aléatoire d’un numéro de carte (de 1 à 52)

Cells(5, i).Select ‘ Sélection d’une cellule pour la position de la carte tirée

ActiveSheet.Pictures.Insert("cartesJouer\" & k & ".gif").Select ‘Insertion de l’image qui se trouve dans le dossier “cartesJouer”

Next i ‘Ce dossier est dans le même dossier que le classeur et les cartes ont pour nom 1.gif ; 2.gif…..

End Sub

56  Chercher un mot dans les cellules d’une feuille









maurice.ocquidant@free.fr Page 13 04/12/2011

Sub ChercheMot() ‘Le mot sera mis dans M Do

Dim premcellu As String, cellu As Range, M As String MsgBox cellu.Address ‘Affichage de l’adresse-cellule de M

M = Application.InputBox(Prompt:="Mot cherché", Set cellu = Cells.FindNext(cellu) ‘On continue …….

Type:=2) Loop Until cellu.Address = premcellu jusqu’à retrouver la

Set cellu = Cells.Find(M) première occurence

If Not cellu Is Nothing Then‘on a trouvé au moins une fois M End If ‘Trouvé sur :

premcellu = cellu.Address on sauvegarde l’adresse de la End Sub ‘(http://touareg.citeweb.net/aideinformatique)

première occurence de M





Neuvième partie : Les feuilles ou formulaires.

57  Cette partie vise à mettre en place une interface graphique : utilisateur – programme.

 L'utilisateur entre des informations sur une feuille d'un classeur puis valide ces données (OK).

 Ces informations sont alors passées à un programme(macro) qui les exploite.

 Deux phases pour parvenir à ce résultat :

 Création des contrôles (boutons, cases, boîtes...) dans une feuille UserFom

 Ecriture du code permettant l'exploitation des données fournies par l'utilisateur.



58 Créer une feuille

 Allez dans VBEditor (F11) pour sélectionner le projet (ici le classeur Projet1.xls)

 Cliquez ensuite sur l'icône Ajouter UserForm (ou allez dans le menu Insertion).

 Aussitôt la feuille "UserForm1" et la boîte d'outils "Contrôles" s'affichent









 Allez dans le menu Outils > Options pour régler la Grille (points).

 On peut modifier le nom (Name) et l'intitulé (Caption) dans la boîte des propriétés (ouvrez par F4)









maurice.ocquidant@free.fr Page 14 04/12/2011

59 Les Outils de Contrôle (formulaires)



 Outil Sélection (pour sélectionner des contrôles sur une feuille)

 Contrôle Label : permet de placer un intitulé dans la feuille.

 Contrôle TextBox : l'utilisateur y introduit du texte (question par exemple)

 Contrôle ComboBox : permet de choisir dans une liste déroulante (ajout possible)

 Contrôle ListBox : liste déroulante permettant la sélection de un ou plusieurs éléments.

 Contrôle CheckBox : case à cocher pour valider ou non une option.

 Contrôle OptionButton : un seul choix possible parmi plusieurs boutons de ce type.

 Contrôle ToggleButton : oui/non, bouton à bascule (comme une case à cocher).

 Contrôle Frame : permet d'entourer une zone avec un intitulé ou d'associer des OptionButton

 Contrôle CommandButton : bouton associé à une action (OK, Annuler...)

 Contrôle TabStrip : Permet de créer une boîte à onglets mais sans contôles

 Contrôle MultiPage : idem mais avec contrôles

 Contrôle ScrollBar : barre de défilement qui permet de déplacer une zone de texte.

 Contrôle SpinButton : permet de faire défiler et sélectionner une valeur

60  Voilà ce qu'il est possible de faire.

 Pour visualiser cela dans une feuille Excel, il faut

écrire une macro :



Sub AffichageFeuille()

Dim RepAffichage As Integer

RepAffichage = MsgBox("Afficher le formulaire ?", _

vbYesNo + vbQuestion, "Affichage de feuille")

If RepAffichage = vbYes Then

Formulaire.Show

End If

End Sub





 Retournez dans Excel (Alt + Q) et ouvrez le classeur Projet1

 Lancez la macro (Outils > Macro > Macros ) puis AffichageFeuille. Répondez Oui.

61

Sub AffichageSimple() Méthode plus simple (affichage du formulaire)

Formulaire.Show Dans VBE saisissez cette macro que vous nommerez "AffichageSimple".

End Sub

 Placez-vous dans la Feuil1 du classeur Projet1 et ouvrez Affichage > Barres d'outils >

Formulaires

 Sélectionnez "Bouton de commande" et, bouton gauche enfoncé,

tracez le contour du bouton.

 La boîte "Affecter une macro" s'ouvre : choisissez la macro "AffichageSimple".

 Changez directement le titre du bouton puis faites un ClicD sur ce bouton pour le modifier.





Dixième partie : Traitement des formulaires.







maurice.ocquidant@free.fr Page 15 04/12/2011

62 Les propriétés d'un contrôle

 Revenez dans la feuille Formulaire dans VBE (Alt + F11).

 Sélectionnez ComboBox par exemple et

affichez les propriétés attachées.

 On peut paramétrer directement une

multitude de propriétés du contrôle (taille,

couleur, police, aspect du pointeur,

comportement...)

 Si on ne sélectionne rien, ce sont les propriétés de la feuille qui sont affichées.

 ShowModal : Très importante, cette propriété qui offre 2 options : en mode True lorsque le

formulaire est ouvert on ne peut accéder aux cellules de la feuille, par contre en mode False on a

accès aux cellules avec le formulaire actif.

63  On peut faire un ClicD sur le formulaire

« TextBox »pour ouvrir ce menu puis



cliquer sur Code.

 Le code du formulaire associé à l’événement Change est déjà déclaré, il suffit de le

compléter (voir ci-dessus) pour créer une procédure.

 D’autres événements, associés au TextBox, sont disponibles.







64 Exemple 1 détaillé

(Saisie d’informations par des cases à cocher)

Etape 1 : Création du UserForm (Formulaire)



 Dans le classeur Project1, on se place dans Feuil2

 Ouvrez VBE (Alt + F11)

 Dans le menu Insertion cliquez sur UserForm.

 Utilisez la Boîte à outils et les boîtes de propriétés attachées à

chaque type de Contrôle.

 Les Contrôles utilisés :

 Des CheckBoxs (1 à 10)

 Des Labels (1 à 11)

 2 Frames

 2 CommandButtons (OK et Annuler)





65 Etape 2 : Codage d’ouverture de la boîte de dialogue Sub AfficheBoîte()

 Allez dans VBE > Insertion > Modules et entrez ce code qui est une Range("A3:J3").Select

macro que l’on va affecter à un bouton de commande. Selection.ClearContents

UserForm1.Show

 Dans la feuille « Feuil2 » du classeur Project1 créez un bouton. End Sub

 Affectez la macro AfficheBoîte à ce bouton (voir $39)









maurice.ocquidant@free.fr Page 16 04/12/2011

66 Etape 3 : Codage du UserForm Private Sub

 Faites un ClicD dessus pour ouvrir le menu attaché et cliquez sur Code. UserForm_Activate()

CheckBox1 = False

 Choisir la propriété Activate et entrez ce code.

CheckBox10 = False

End Sub



67 Etape 4 : Codage du bouton OK

Private Sub BoutonOK_Click() UserForm1.Hide

Dim n As Byte GoTo Fin

n=0 Else

Dim Module1 As Byte UserForm1.Hide

If CheckBox1.Value = False Then Range("A3").Value = Module1

Module1 = 0 ……………………………….

Else Range("J3").Value = Module10

Module1 = 1 Range("A1").Select

End If End If

n = n + Module1 Fin:

……………………… End Sub

If CheckBox10.Value = False Then

Module10 = 0  Dans VBE > UserForm faites un ClicD sur le

Else bouton OK

Module10 = 1

End If

 Cliquez sur Code pour entrer ces lignes.

n = n + Module10  n sert à compter le nombre des modules

If n 4 Then sélectionnés

MsgBox "Il faut choisir 4 modules, recommencez!", _

vbInformation  Modulei prendra la valeur 0 ou 1

 Lorsque n4 on recommence

68 Etape 5 : Codage du bouton Annuler Private Sub CommandButton2_Click()

 Même technique que pour OK UserForm1.Hide

End Sub

 Entrez ce code, c’est fini !!

Exemple 2 détaillé

(Saisie d’informations par des TextBox)

69

Etape 1 : Faite le UserForm (Formulaire)



 3 « TextBox »

 5 « OptionButton »

 1 Frame qui contient les 5 OptionButtons

 2 « CommandButton » (OK et Annuler)



70 Etape 2 : Codage d’ouverture du Formulaire

 Allez dans VBE > Insertion > Modules et entrez ce code qui est une macro que l’on va affecter à un

bouton de commande(dans la feuille de calcul) Sub AfficheDialogue()

 Dans la feuille « Feuil3 » du classeur Projet1 créez un bouton d’appel UserForm2.Show

End Sub

 Affectez la macro AffichageDialogue à ce bouton (voir $36)

71 Etape 3 : Codage du UserForm Private Sub UserForm2_Activate()

 Faites ClicD dessus pour ouvrir un menu et cliquez sur Code. TextBox1.Value = “”

OptionButton1 = False

 Choisir la propriété Activate et entrez ce Code. ……………………..

OptionButton5 = False

End Sub









maurice.ocquidant@free.fr Page 17 04/12/2011

72 Etape 4 : Codage du bouton OK. If OptionButton1 = True Then

Range("D" & Ligne).Value = "PE1"

 Dans VBE > UserForm faites ClicD sur ce n = 1

bouton OK. End If

If OptionButton2 = True Then

 Cliquez sur Code pour entrer ces lignes : Range("D" & Ligne).Value = "PLC1"

Private Sub CommandButton1_Click() n=1

Dim Ligne As Variant End If

Dim Nom As String If OptionButton3 = True Then

Dim n As Byte, Q As Byte Range("D" & Ligne).Value = "PE2"

Nom = TextBox1.Text n=1

Prenom = TextBox2.Text End If

Disci = TextBox3.Text If OptionButton4 = True Then

Sheets("Feuil3").Activate Range("D" & Ligne).Value = "PLC2"

Range("A2").Value = Nom n=1

Range("B2").Value = Prenom End If

Range("C2").Value = Disci If OptionButton5 = True Then

If OptionButton1 = True Then Range("D" & Ligne).Value = "CAPLP"

Range("D2").Value = "PE1" n=1

End If ‘idem pour 2-3-4-5 End If

Sheets("Base").Activate If (n = 0 Or Nom = "" Or Prenom = "" Or Disci = "") Then

UserForm2.Show Range("A" & Ligne).Value = "" ‘idem pour B-C-D

GoTo Fin Sheets("Feuil3").Activate

End If MsgBox "Formulaire incomplet, recommencez.", vbInformation

Sheets("Feuil3").Activate UserForm2.Show

Q = MsgBox("Votre saisie est correcte?", vbYesNo + GoTo Fin

vbQuestion, "CONFIRMATION") Else

If Q = 7 Then n=0

Sheets("Base").Activate UserForm2.Hide

Range("A" & Ligne).Value = "" ‘idem pour B-C-D TextBox1.Text = ""

Sheets("Feuil3").Activate TextBox2.Text = ""

Ligne = Range("A1").End(xlDown).Address TextBox3.Text = ""

Ligne = 1 + Range(Ligne).Row OptionButton1 = False ‘idem pour 2-3-4-5

Range("A" & Ligne).Value = Nom Range("A2").Value = "" ‘idem pour B2-C2-D2

Range("B" & Ligne).Value = Prenom End If

Range("C" & Ligne).Value = Disci Fin:

End Sub



73 Etape 5 : Codage du bouton Annuler (voir §43) n=0

TextBox1.Text = “” ‘idem pour 2 et 3

Private Sub CommandButton2_Click() OptionButton1 = False ‘idem pour 2-3-4-5

UserForm2.Hide End Sub





Exemple 3 : Utilisation de « ScrollBar »

74  Il est question de choisir n nombres différents compris

entre des entiers p et q (p p. Recommencez.", , End If

"ATTENTION" Next Item

GoTo Fin Next i

End If alea.Hide Macro attachée au bouton de

n = ScrollBar3.Value Fin: commande OK

If n >= q - p Then End Sub

MsgBox "n doit être un entier Barres d’outils.

 Permet d’ouvrir la barre des différents

contrôles ci-contre. On ne peut agir sur eux qu’en MODE CREATION

 Il suffit de sélectionner l’un d’eux pour construire l’objet.

79 Premier exemple

 On va construire un premier contrôle : un « intitulé » (Label 1)







 Après avoir cliqué sur A , dessiner le contour du contrôle, bouton gauche



enfoncé.

 A chaque contrôle des propriétés sont attachées .Cliquer sur cette

icône pour faire apparaître cette « boîte de propriétés.



 On peut ainsi modifier le texte (Caption) ; la police, les couleurs, le cadre

 Nous avons ainsi créer un objet sur lequel on ne peut plus agir (si on sort du mode création).



80 Deuxième exemple





 Construisons une liste déroulante dans Feuil1

 Dans Feuil2 et la plage $A$1 :$A$10 entrons des informations (PE1-PE2-PLC1-PLC2….)

 Ouvrons la boîte des propriétés pour y entrer



et mettre à son goût l’aspect du

contrôle. La liste déroulante contient les rubriques de la plage

Feuil2 !$A$1 :$A$10 et la sélection va apparaître dans $I$23

 Mais on peut affecter une macro à chaque contrôle, par exemple :

 Nous allons créer une « TextBox1» dans laquelle nous allons placer notre

sélection à l’aide d’une petite macro.





 Sélectionner la liste déroulante ScrollBar1 puis cliquer sur

 Aussitôt nous sommes en position pour saisir :

Private Sub ComboBox1_Change()

TextBox1.Value = ComboBox1.Text

End Sub

 Sortir du mode création pour tester tout cela.









maurice.ocquidant@free.fr Page 20 04/12/2011

Table d’Eratosthène (n lignes et p colonnes)

81

 Construction d’une table d’Eratosthène n*p









Const ALPHABET = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" With Selection.Borders(xlEdgeLeft)

Private Sub CommandButton1_Click() .LineStyle = xlDouble

Dim Ligne As Long, P As Long .Weight = xlThick

Dim Colonne As Byte .ColorIndex = xlAutomatic

Dim Chiffre As Variant End With

Dim Col1 As String With Selection.Borders(xlEdgeTop)

Dim i As Byte, j As Byte .LineStyle = xlDouble

Dim Temp_1 As Long .Weight = xlThick

Sheets(« Feuil2 »).Activate ‘Feuille 2 activée .ColorIndex = xlAutomatic

Cells.Select End With

Selection.Clear ‘Feuille 2 nettoyée With Selection.Borders(xlEdgeBottom)

Ligne = TextBox1.Value ‘On recueille les données .LineStyle = xlDouble

Colonne = TextBox2.Value .Weight = xlThick

For i = 1 To Ligne .ColorIndex = xlAutomatic

For j = 1 To Colonne End With

If j 2 Then GoTo Non Tablenp.Hide

If Right(Str(Nombre), 1) = "0" Or Right(Str(Nombre), 1) = "5" And End Sub

Nombre 5 Then GoTo Non: Code du bouton Annuler

For Temp_1 = 3 To Sqr(Nombre) Step 2

If Nombre Mod Temp_1 = 0 Then GoTo Non

Next Temp_1 Sub Eratosthène()

Premier = True Sheets(“Feuil1”).Activate

Exit Function Tablenp.Show

Non: End Sub

Premier = False Code de la fonction Premier

End Function « p est-il premier ? » Code de la macro de lancement

Eratosthène



83 Private Sub CommandButton2_Click()

Plage.Hide Codage du bouton Annuler

End Sub





maurice.ocquidant@free.fr Page 21 04/12/2011

Les décimales de 

84 

2(k)!

 La théorie : On va utiliser la formule :   2  

k 1 (2k 1)!!

Une factorisation « à l’envers » va nous permettre de créer l’algorithme de calcul.

La somme partielle S4 peut se factoriser sous la forme S4= {[(8/9+2)3/7+2]2/5+2}1/3+2

 Nombre de termes à calculer :

Si on souhaite p décimales exactes il suffira de calculer Sn (avec n=2+Ent(p/Log10(2))

Ainsi pour 1000 décimales exactes il faudra que le calcul porte sur 3323 termes.

 Dans EXCEL (précision 15 décimales)

 Dans la colonne A mettre les entiers de 50 à 1.

 Mettre ces formules dans B1 :B2 et C1 :C2 puis transmettre pour

obtenir 3,14159265358979 (Format > Nombre de 15 décimales)



85  Calcul en base 100 000 et avec la précision demandée

 On commence par demander le nombre « ndec » des décimales à calculer (99999 Call tableau Code du bouton OK

Cells(i, 1).Formula = q DecPI.Hide

Cells(i + 1, 1).Formula = r * Base + Celj * k MsgBox "DEBUT :" & T & " FIN :" & Time

Next i End Sub ‘Voir (§56) pour la macro tableau





86 Private Sub CommandButton2_Click()

DecPI.Hide

End Sub



Code du bouton Annuler









maurice.ocquidant@free.fr Page 22 04/12/2011

Macro pour animer des graphiques

87 Différents tracés de courbes (Utilisation de macros)

 Ouvrir le classeur foncanimMaths.xls dans le dossier

ClasseursMath

 Lancer la Macro en cliquant

sur le boutons ci-contre

 Il reste à compléter le

« Formulaire » et cliquer sur OK.

 ATTENTION : les fonctions seront saisies avec la variable

x (on peut oublier le signe =)

 On peut mettre une virgule ou un point dans les

nombres décimaux

88  Calculs et point animé sur la courbe dans la même feuille.

 Le « UserForm » s’appelle « Animfonction »

 La macro de lancement se nomme « AnimFonct ».



Private Sub TextBox6_Change()

k$ = TextBox6.Value ‘k$ reçoit la fonction Macro associée

If k$ "" And Mid(k$, 1, 1) "=" Then à la zone de saisie de la

TextBox6.Value = "=" & k$ ‘En cas d’oubli on met = devant fonction

End If

End Sub ‘On contrôle chaque entrée.

Private Sub TextBox1_Change() TextBox1.Value = k$ Macro associée

k$ = TextBox1.Value ‘ k$ reçoit la saisie c$ = Mid$(k$, Len(k$), 1) à la zone de saisie de a

If k$ = "" Then GoTo fin d = Asc(c$)

c$ = Mid$(k$, Len(k$), 1) ‘Dernière saisie End If

d = Asc(c$) ‘Code ASCII de la dernière saisie test: ‘La saisie est-elle un chiffre ?

If Len(k$) = 1 And c$ = "-" Then GoTo fin If (d > 47 And d < 58) Or c$ = "," Then GoTo fin

If Len(k$) = 1 Then GoTo test TextBox1.Value = ""

r$ = Mid(k$, 1, Len(k$) - 1) MsgBox "Il faut saisir un nombre décimal avec virgule."

If c$ = "." Then ‘On remplace le point par virgule fin:

dans le nombre décimal (en cas d’erreur) End Sub ‘Cette macro est activée à chaque frappe

k$ = r$ & ","

Sub AnimFonct() ‘Macro de lancement Err.Clear ‘Traitement de l’erreur de saisie de la

AnimFonction.Show ‘Ouverture du formulaire fonction dans la cellule “B2” avec annonce dans “C2”

Range("C2").Select ‘Mise en forme de « C2 » ActiveCell.FormulaR1C1 =

Selection.Font.ColorIndex = 3 "=IF(ISERROR((R2C2))=TRUE,""Erreur de saisie dans

Selection.Font.Bold = True la fonction ???"","""")"

With Selection.Font End Sub

.Name = "Arial" Macro qui ouvre

.Size = 14 le formulaire

.ColorIndex = 3

End With









maurice.ocquidant@free.fr Page 23 04/12/2011