LeCompagnon.info - Joyeuses fêtes

VBA pour Excel

VBA pour Excel

VBA pour Excel - Déclarations, types et références

Sur cette page

Valeurs littérales
Constantes
Variables
Déclarations
Noms
Types
Valeurs spéciales
Conversion
Portée de variable
Exemples

Valeurs littérales

Un programme VBA manipule des valeurs.

Un littéral est une valeur explicitement tapée dans le programme.

Un littéral texte est entre guillemets:

-"Rouge"
-"Maman est chez le coiffeur"

Un littéral date ou heure est entre croisillons #:

-#2009-01-01#
- #14:00:00#
- #2009-01-01 14:00:00#

Un littéral numérique est un nombre:

-12
- 2.5
- 16009829287654

Accueil
Concepts de programmation
VBE l'éditeur VBA
Déclarations, types, références, expressions et assignations
Tests et branchements
Les boucles
Gestion d'erreur
Collections et tableaux
Dialogues et formulaires
Objets et événements Excel
Conseils de programmation
Liste d'instructions
Autres formations

Word 2003
Word 2007
Excel
Excel 2007/2010/2013
PowerPoint 2003
PowerPoint 2007
Access
Access 2010
Internet
Général
Intelligence financière
Vidéos

Navigation
Précédent
Suivant
Page Principale
FAQ
Nouveautés
Plan du site
Références
Motivations
Manuels de formation
Fichiers de démonstration
Contact
Par courriel

Abonnez-vous à
l'infolettre LCI
Partager






Abonnez-vous à l'infolettre LeCompagnon.info

201 fonctions pour les pressés












Constantes

Une constante est un littéral auquel on donne un nom:

Une constante doit être déclarée et initialisée.
Par exemple, dans l'instruction
Const cDouzaine = 12

-Const est l'instruction qui indique à VBA que le texte qui suit est une déclaration de constante.
- cDouzaine est le nom de la constante.
-= est l'instruction d'assignation.
- 12 est une constante littérale qui sera copiée dans cDouzaine.

La valeur d'une constante NE PEUT PAS être modifiée lors de l'exécution du programme. On utilise donc les constantes pour donner un nom à des valeurs qui ne changent pas, comme le nombre d'objets dans une douzaine, la valeur de Pi, le nombre de secondes dans une journée.

VBA contient plusieurs constantes intrinsèques qui nomment des valeurs spécifiques à VBA.

Par exemple, vbRed vaut 255.

Les constantes intrinsèques sont automatiquement déclarées et initialisées par VBA. Pour en consulter la liste, utilisez l'explorateur d'objets:

-Dans l'Éditeur VBA, appuyez sur la touche F2 (ou menu Affichage, choix Explorateur d'objets).
- Dans la 2e liste déroulante (1), tapez constants

1) Critère de recherche dans les bibliothèques d'objets VBA.

2) Liste des bibliothèques d'objets répondant au critère.

3) Classes d'objets dans la bibliothèque choisie en 2).

4) Objets de la classe choisie en 3).

5) Valeur de l'objet choisi en 4).

Vous pouvez aussi consulter l'aide VBA (recherchez Constantes). Pour déclarer et initialiser vos constantes, utilisez l'instruction CONST

[Public | Private] Const nom [As type] = expression

  • nom est un nom respectant les règles VBA
  • type est un type VBA autre qu'objet
  • expression est une expression VBA n'utilisant que des constantes ou des littéraux

Variables

Les littéraux et les constantes ont des valeurs fixes pendant toute l'exécution d'un programme VBA.

Un programme VBA ne peut pas changer la valeur d'une constante.

Les valeurs modifiables par le programme se trouvent dans des variables ou dans des propriétés d'objet.

Les variables sont des unités de stockage internes au programme VBA, alors que les propriétés d'objets sont des unités de stockage externes.

Par exemple, si votre programme doit compter le nombre de cellules jaunes d'une plage, il utilisera une variable.
Mais s'il veut changer la couleur d'une cellule, il utilisera la propriété color de l'objet de la classe Range contenant la cellule.

Lors de l'évaluation d'une expression, le nom de chaque variable est remplacé par son contenu.

Une variable est caractérisée par son nom et son type.

Un nom de variable VBA doit:

-Commencer par une lettre.
- Contenir un maximum de 255 lettres et/ou chiffres.

Un nom de variable peut:

-Commencer par une lettre qui identifie son type (pratique recommandée).
- Décrire son contenu (pratique recommandée).
- Se terminer par un identificateur de type %, &, #, !, @ ou $ (pratique déconseillée).

Un nom de variable ne doit pas:

-Être identique à un mot réservé VBA.
- Contenir des espaces ni des caractères spéciaux (sauf les identificateurs de type à la fin du nom).
- Commencer par un chiffre.
-Être ambigu.

Déclarations

Une variable VBA doit être déclarée pour que VBA lui réserve un espace en mémoire centrale et lui assigne un nom. La quantité et l'organisation de l'espace réservé dépend du type de variable.

Si Option Explicit n'est pas présent au début du module VBA où se trouve une variable, et que la variable n'est pas déclarée, VBA la déclarera automatiquement de type Variant.

Apparemment pratique, cette fonctionnalité est très dangereuse, car elle désactive la détection de fautes de frappe.

En effet VBA ne détectera pas d'erreur dans le code suivant:

Sub Exemple()
Dim cCompteur As Currency

    cComteur = cCompteur + 10
End Sub

Mais la détectera si vous faites plutôt:

Option Explicit

Sub Exemple()
Dim cCompteur As Currency

    cComteur = cCompteur + 10
End Sub

Pour automatiser l'insertion de Option Explicit au début de chaque module:

-Dans le menu Outils, choisissez Options.
- Dans l'onglet Éditeur, assurez-vous que la case à cochée Déclaration des variables obligatoire est cochée.

Une variable est explicitement déclarée dans les instructions Dim, Private, Public, Function et Sub.

Dim nom[([indices])] [As [New] type] [, nom[([indices])] [As [New] type]] . . .
Private nom[([indices])] [As [New] type] [, nom[([indices])] [As [New] type]] . . .
Public nom[([indices])] [As [New] type] [, nom[([indices])] [As [New] type]] . . .

  • nom est un nom respectant les règles VBA.
  • indices déclare les dimensions d'un tableau.
  • NEW valide seulement si type est un objet, déclenche la création de l'objet.
  • Type est un type VBA ou une classe d'objets. Par défaut le type de données est Variant.

Dim peut être utilisé:

  • Au début d'un module (en dehors d'une procédure). Déconseillé.
  • Au début d'une procédure. Recommandé.
  • À l'intérieur d'une procédure. Fortement déconseillé, parce que tous les Dim d'une procédure sont de toute façon exécutés avant les autres instructions du module, et leur présence ailleurs qu'au début donne l'impression du contraire.

Les valeurs numériques sont initialisées à 0, les valeurs texte à "", les dates à #00:00:00#, les variant à Empty et les objets à  Nothing.

Les instructions Private et Public ont la même syntaxe que Dim, mais ils ont une portée différente.

Les instructions Function et Sub permettent aussi de déclarer des variables, qui seront les paramètres de la procédure.

Types

Type de données

Suffixe

Description

Plage

Valeur initiale
Byte   Un octet 0 à 255 0
Boolean   Vrai ou faux, Oui ou Non True ou False False
Integer % Un nombre entier -32 768 à 32 767 0
Long & Un nombre entier -2 147 483 648 à 2 147 483 647 0
Single ! Un nombre pouvant contenir une partie décimale. La valeur peut être une approximation de la valeur assignée. -3,402823E38 à -1,401298E-45 pour les valeurs négatives ; 1,401298E-45 à 3,402823E38 pour les valeurs positives 0
Double # Un nombre pouvant contenir une partie décimale. La valeur peut être une approximation de la valeur assignée.

 

-1,79769313486231E308 à
-4,94065645841247E-324 pour les valeurs négatives ; 4,94065645841247E-324 à 1,79769313486232E308 pour les valeurs positives
0
Currency @ Un nombre pouvant contenir jusqu'à 4 chiffre en partie décimale.

 

-922 337 203 685 477,5808 à 922 337 203 685 477,5807 0
Date   Une date et une heure 1er janvier 100 au 31 décembre 9999 #00:00:00#
Object     Toute référence à des données de type Object Nothing
String $ Du texte 0 à environ 2 milliards de caractères "" ou vbNullString
Variant
 
  Le type indéfini. VBA détermine le type du contenu d'une variable Variant lorsque le programme lui assigne une valeur.   Empty
Type défini par l'utilisateur   Type défini par l'utilisateur. Voir DefType dans l'aide VBA    

VBA offre une grande gamme de types de données, pour être compatible avec des programmes écrits dans des versions antérieures du langage.
En pratique, évitez d'utiliser les types:

-Byte : réservé aux programmeurs chevronnés qui manipulent le contenu des 8 bits d'un octet.
-Integer : utilisez currency, meilleure capacité et plus souple.
-Long: utilisez currency, meilleure capacité et plus souple.
-Single: utilisez currency, qui ne fait pas d'arrondi.
-Double: utilisez currency, qui ne fait pas d'arrondi.
-Variant: désactive la validation de type de VBA.

Donc, pour stocker:

-un nombre, utilisez Currency.
-du texte, utilisez String.
-une date, utilisez Date.
-une heure, utilisez Date.

Vous devrez utiliser les autres types lorsqu'ils sont utilisés dans des programmes existants ou dans les propriétés d'objets.

Valeurs spéciales

En plus des valeurs "normales", comme du texte dans une variable String, un nombre dans une variable Currency, les variables peuvent avoir des valeurs "spéciales" dans des situations "spéciales".

Empty: une variable de type Variant est initialisée à Empty lors de sa création.

-IsNull(nom) est Faux lorsque nom est Empty.
-IsEmpty(nom) est Vrai lorsque nom est Empty.
-nom=0 est Vrai lorsque nom est Empty
-nom="" est Vrai lorsque nom est Empty

"" (vbNullString ou vbNullChar): une variable de type String vide.

-IsNull(nom) est Faux lorsque nom=""
-IsEmpty(nom) est Faux lorsque nom=""
-nom=0 est Faux lorsque nom=""

Null: une variable Null n'existe pas. Aucun espace mémoire ne lui a été assigné. Seul son nom existe. S'applique le plus souvent à des propriétés d'objets.

-IsNull(nom) est Vrai lorsque nom est Null.
-IsEmpty(nom) est Faux lorsque nom est Null.
-nom=0 est Faux lorsque nom est Null.
-nom="" est Faux lorsque nom est Null.

Nothing: lorsqu'une variable objet est déclarée mais pas assignée (avec SET). Ne s'applique qu'aux variables objet. Pas aux propriétés d'objet, pas aux variables VBA.

Conversion

VBA convertit automatiquement les valeurs de types différents au besoin. Il n'est donc pas nécessaire de s'assurer que toutes les variables soient du même type avant de les utiliser dans une expression. Il arrive cependant que VBA convertisse tout en entier, ce qui peut mener à la perte de décimales. Il est donc préférable d'utiliser le type currency pour toutes les variables qui s'y prêtent, soit en pratique toutes les variables numériques de vos programmes.

Le code VBA à droite fonctionne, car VBA convertit S10 en entier avant de l'ajouter à 1 et mettre le résultat dans cCompteur. Sub Exemple()
Dim cCompteur As Currency
Dim s10 As String
    s10 = "10"
    cCompteur = s10 + 1
End Sub
Mais le code à droite ne fonctionnera pas et se terminera par un message d'erreur "Incompatibilité de type" Sub Exemple()
Dim cCompteur As Currency
Dim s10 As String
    s10 = "dix"
    cCompteur = s10 + 1
End Sub

Il n'est pas prudent de se fier à la conversion automatique de types de VBA. Si elle fonctionne PRESQUE tout le temps, il arrive que des données imprévues donnent des résultats inattendus.

VBA fournit donc des Fonctions de conversion: Val(), Str(), Format(), FormatCurrency(), FormatDateTime(),  CBool(), CByte(), CCur(), CDate(), CDbl(), CDec(), CInt(), CLng(), CSng(), CStr(), CVar()

Val(expression)

expression peut être n'importe quelle expression de type string.

Val() tente d'extraire un nombre de l'expression. Val() est l'opposé de Str().

-Si l'expression ne contient que des chiffres, Val(expression) retourne un nombre composé de ces chiffres.
- Si expression ne contient aucun chiffre, Val (expression) retourne 0.
- Si l'expression contient un mélange de chiffres et de lettres, Val(expression) fait de son mieux en arrêtant la récupération des chiffres au premier caractère alphabétique.

Point décimal: Val() ne reconnaît que le point comme séparateur décimal. Si vous voulez permettre l'utilisation d'un autre symbole (comme la virgule) vous devrez utiliser l'instruction Replace() au préalable ou utiliser Ccur() pour faire la conversion.

 

Str(expression)

expression peut être n'importe quelle expression sauf de type string

Str(expression) convertit en string le contenu de expression. Str() est l'opposé de Val().

Le format est choisi par VBA et peut ne pas convenir (séparateur de décimales, format de date ou d'heure...).
Vous pouvez préférer l'utilisation de la fonction Cstr() ou, mieux encore, d'une des fonctions Format(), FormatCurrency(), FormatNumber() ou FormatDateTime()

 

Format(expression[, format[, premierjour[, premièresemaine]]])

  • expression est une expression VBA.

  • format est une expression de type string décrivant le format voulu
    voir les règles ci dessous

  • premierjour expression entre 1 et 7 indiquant le premier jour de la semaine (1 = dimanche).

  • premièresemaine: 1 (semaine du 1er janvier), 2 (première semaine d'au moins 4 jours dans la nouvelle année) ou 3 (première semaine complète).

format peut être

  • un format numérique prédéfini:

    • Nombre général.

    • Monétaire.

    • Fixe.

    • Standard.

    • Pourcentage.

    • Scientifique.

    • Oui/Non: Non si expression vaut 0 sinon Oui.

    • Vrai/Faux: Faux si expression vaut 0 sinon Vrai.

    • Actif/Inactif: Inactif si expression vaut 0 Actif.

  • un format date ou heure prédéfini (suivant les paramètres Windows)
    • Date, général

    • Date, complet

    • Date, réduit

    • Date, abrégé

    • Heure, complet

    • Heure, réduit

    • Heure, abrégé

  • un masque de formatage.
    •  Consultez l'aide VBA (recherchez format) pour connaître les nombreuses possibilités et surtout de nombreux exemples.

 

FormatCurrency(Expression[,NombreDécimales[,ZéroNonSignificatif[,Parenthèses[,Groupes]]]])

FormatNumber(Expression[,NombreDécimales[,ZéroNonSignificatif[,Parenthèses[,Groupes]]]])

Où:

-Expression Expression à formater.
-NombreDécimales Positions à droite de la virgule sont affichées. Par défaut (-1) les paramètres Windows sont employés.
-ZéroNonSignificatif  -1 les affiche, 0 ne les affiche pas, -2 utilise les paramètres Windows.
-Parenthèses -1 les affiche, 0 ne les affiche pas, -2 utilise les paramètres Windows.
-Groupes -1 groupe les milliers, 0 ne les groupe pas, -2 utilise les paramètres Windows.

Ces deux fonctions convertissent Expression en format String en utilisant les paramètres de présentation des paramètres régionaux de Windows.

FormatDateTime(Expression[,Format])

-Expression Expression de type date
-Format une des valeurs suivantes:

  • 0 affiche la partie date sous forme de date abrégée et la partie heure sous forme d'heure complète
  • 1 Affiche la date en format de date complet spécifié dans les paramètres Windows
  • 2 Affiche la date en format de date abrégé spécifié dans les paramètres Windows
  • 3 Affiche l'heure en format d'heure spécifié dans les paramètres Windows
  • 4 Affiche l'heure au format 24 heures (hh:mm).

Cette fonction convertit Expression en format String en utilisant les paramètres de présentation des paramètres régionaux de Windows.

 

CBool(expression)
CByte(
expression)
CCur(
expression)
CDate(
expression)
CDbl(
expression)
CDec(
expression)
CInt(
expression)
CLng(
expression)
CSng(
expression)
CStr(
expression)
CVar(
expression)

-expression peut être de n'importe quel type, mais dont les valeurs acceptables varient selon le type de conversion demandée. En effet, la conversion doit être possible.

Ces fonctions convertissent explicitement expression dans le type indiqué par le nom de la fonction. Si la conversion est impossible, comme dans
CCur("rouge")
l'exécution du programme est interrompue avec le message "Incompatibilité de type".
Pour la fonction CCur, expression doit valoir entre -922 337 203 685 477,5808 et 922 337 203 685 477,5807.

Voir la section Gestion des erreurs pour une technique permettant de contourner le problème potentiel.

Portée de variables

En principe, une variable déclarée dans une
procédure Sub ou une procédure Function
ne peut être utilisée que dans cette procédure:

 

Ici, cMontant est une variable locale
de la procédure Exemple

 

Sub Exemple()
Dim cMontant As Currency
    InputBox cMontant

End Sub

Une variable déclarée au début d'un module
(avant la première procédure) peut être utilisée
dans toutes les procédures du module:

 

Ici, cMontant est une variable locale du module
et publique des procédures du module.

Option explicit

Dim cMontant

Sub Exemple()

    InputBox cMontant

End Sub

Sub AutreSub()

    cMontant = cMontant + 10

End Sub

Pour qu'une variable puisse être utilisée par toutes les procédures de tous les modules, il faut la déclarer en utilisant l'instruction Public au lieu de l'instruction Dim.

Recommandation: n'utilisez que des variables locales de procédure. Utilisez les paramètres de Sub ou de Function ou, mieux encore, des cellules Excel pour partager des valeurs entre les procédures et le modules.

Exemples:

À la fin de l'exécution de la procédure
Sub
Principale ci-contre, la variable
cTaux contiendra 0.15.
Option Explicit
Dim cTaux As Currency
Sub Principale()
    cTaux = 0.05
    Call NouveauTaux
    MsgBox cTaux
End Sub
Sub NouveauTaux()
    cTaux = 0.15
End Sub

 

À la fin de l'exécution de la procédure
Sub
Principale ci-contre, la variable
cTaux contiendra 0.05.
Option Explicit
Sub Principale()
Dim cTaux As Currency
    cTaux = 0.05
    Call NouveauTaux
    MsgBox cTaux
End Sub
Sub NouveauTaux()
Dim cTaux As Currency
    cTaux = 0.15
End Sub

 

La procédure Sub ci-contre affiche
successivement:

2

3.00

3

 

Alors que si t1 et t2 sont déclarés Currency,
elle affichera

3

3.00

3

Sub test()
Dim Taux1 As Double
Dim Taux2 As Double
    Taux1 = 0.15
    Taux2 = 0.05
   

    MsgBox Int(Taux1 / Taux2)
    MsgBox Format(Taux1 / Taux2, "###.00")
    MsgBox Format(Taux1 / Taux2, "###")

End Sub
 




 

Vous avez apprécié cette page?

Dîtes que vous l'aimez en utilisant le bouton Facebook, Twitter ou l'un des autres services sous le bouton "Share".

Cliquez sur le bouton de votre choix

Abonnez-vous à l'infolettre

Abonnez-vous à l'infolettre du site pour recevoir les dernières nouvelles et aussi des formations à votre courriel. Vous pouvez voir la liste des dernières infolettres ici.


Cliquez ici pour vous abonner à l'infolettre





Ce site est hébergé par 1&1.com