Exemples de fonctions personnalisées
Vous trouverez dans cette section diverses fonctions VBA personnalisées (Excel uniquement donc), généralement faciles à coder, et qui pourraient vous être utiles. Afin d'éviter la saisie d'un code parfois long, celui-ci vous est proposé en téléchargement sur le site www.dunod.com associé au livre, sous forme de fichiers texte rassemblés au sein d'un unique dossier compressé. Nous citerons ci-dessous toujours le nom du fichier correspondant à l'astuce présentée.
Le code correspondant se trouve dans le fichier CouleurMFC.txt. Cette fonction retourne la valeur du format actif d'une mise en forme conditionnelle. Avec la fonction proposée, deux valeurs peuvent être récupérées. La syntaxe de la formule est la suivante :
CouleurMFC(plage, [mode])
où plage est la plage concernée et mode une valeur binaire facultative (0 par défaut) qui renvoie la valeur de Interior.ColorIndex (mode = 0) ou celle de Interior.Color (mode = 1).
Mais avec quelques modifications, toutes les autres propriétés pourraient être renvoyées.
La mise en forme conditionnelle peut dépendre d'une valeur directe (>1 and <10) ou indirecte (>=D10 and <=E10).
Le code correspondant se trouve dans le fichier distri_couleur.txt. Voisine de la fonction précédente, mais cette fois sous forme de procédure et non limitée à une mise en forme conditionnelle, cette macro permet d'obtenir le code d'une couleur, pour traitement ultérieur selon vos besoins.
Le code correspondant se trouve dans le fichier trf_plage.txt.
Seules les formules Somme, Moyenne, Nb, Nbval permettent de faire des calculs sur des plages discontinues ou multi-feuilles (calcul en 3D). Ainsi, =Somme(Feuil1:Feuil3!A1:A10) effectue la somme de la plage A1:A10 des trois feuilles.
Somme.si, Nb.si, Sommeprod, Index, Equiv ainsi que les formules matricielles ne le font pas, même avec des plages nommées.
Cette fonction personnalisée n'a pas pour but de réécrire les différentes formules, mais transforme une plage discontinue ou une plage sur un certain nombre de feuilles en une seule matrice. Sa syntaxe est la suivante :
=Trf_plage(Ma Plage; "Feuil1:Feuil3")
où Ma plage est la plage à traiter (comme A1:A10) et "Feuil1:Feuil3" la plage de feuilles à traiter. Cet argument est facultatif pour les plages discontinues ne nécessitant pas de multi-feuilles.
Attention, les formules Nb.Si et Somme.Si ne fonctionnent toujours pas, mais elles peuvent être facilement transformées en formules matricielles. Index et Equiv fonctionnent très bien.
Souvenez-vous que les formules matricielles doivent être validées en appuyant simultanément sur les touches CTRL+MAJ+ENTREE. Si la validation est correctement effectuée, des accolades {} encadrent automatiquement la formule.
Le code correspondant se trouve dans le fichier nbsi_plus.txt.
La fonction NB.SI d'Excel possède quelques contraintes en termes de nombre d'arguments. La fonction Plus présentée ici permet d'appliquer la fonction NB.SI en s'affranchissant de ces limites.
· automatique en ne sélectionnant que la première cellule Gauche/Haut de la plage de recherche ;
· programmée en sélectionnant la première cellule Gauche/Haut et en étirant sur la ligne souhaitée.
L'emplacement de la plage de critères peut être quelconque.
Le code correspondant se trouve dans le fichier rechercheVmulti.txt.
La fonction RechercheV d'Excel possède de nombreuses limites :
La RechercheV polyvalente proposée ici ne possède pratiquement plus de limite :
Les fonctions doivent se situer dans une même colonne sans cellule vide intercalaire. La recherche recommence sinon à la première occurrence de la liste.
Dans les lignes 3 à 9 de la colonne E se trouve la formule :
=RechercheVmulti($C$3;$B$3;$H$3)
C3 est la colonne où se trouve la liste de recherche. La recherche commence à la ligne 3, mais vous pourriez spécifier une autre ligne de début de la recherche. B3 est la cellule critère, H3 la colonne où la donnée va être saisie : elle peut se situer n'importe où sur la même ligne.
Dans les lignes 3 à 7 de la colonne F se trouve la formule :
=RechercheVmulti($C$3;$B$3;$A$3; 20)
C3 est la colonne où se trouve la liste de recherche. La recherche commence à la ligne 3, mais vous pourriez spécifier une autre ligne de début de la recherche. B3 est la cellule critère, A3 la colonne où la donnée va être saisie : elle peut se situer n'importe où sur la même ligne (le décalage est ici négatif).
La recherche s'arrête à la ligne 20, même s'il y a d'autres concordances plus bas, ce qui permet de délimiter la plage de la colonne - début de recherche dans (C)3 et fin dans (C)20. Ce paramètre est optionnel : s'il n'est pas renseigné, la recherche s'effectue sur toute la colonne.
Collez le code dans un module standard.
Le code correspondant se trouve dans le fichier Worksheet_SelectionChange.txt.
L'événement Change d'une feuille détecte une modification de la cellule active, mais n'apporte pas le moindre renseignement quant au contenu.
Cette procédure événement permet de savoir si la cellule a été modifiée, fonctionne si elle a été initialisée mais aussi si elle a été modifiée.
Lorsque l'événement Change est activé, c'est le contenu de la cellule qui vient d'être sélectionnée qui se trouve dans Target. C'est la cellule qui vient d'être quittée qui est testée.
Le code correspondant se trouve dans le fichier CréerBouton.txt.
C'est un petit code relativement simple qui se borne à créer un bouton de commande et à le doter automatiquement de code. Collez ces deux sous-routines dans un module général (Module1 par exemple).
VBA et les collections d'objets
Le code correspondant se trouve dans le fichier InitOption.txt.
Lorsqu'une feuille ou un formulaire utilisateur (Userform) comporte de très nombreux contrôles, il est parfois fastidieux d'écrire du code dans chaque événement des contrôles.
Voici comment traiter vos contrôles comme s'ils étaient indexés (mode VB6 avec index).
Ouvrez un nouveau classeur, puis incorporez, l'un en dessous de l'autre, dans la colonne C :
Enregistrez le classeur sous le nom Collections.xls, puis collez les deux premières procédures du fichier InitOption.txt dans un module, les deux suivantes dans un module de classe (pour ouvrir un module de classe, choisissez Insertion > Module de classe) et la dernière dans le module du classeur.
À l'ouverture du classeur, les collections sont opérationnelles. Si le code VBA est modifié, les collections seront toutefois désactivées et il faudra appeler la sous-routine ActivationCollect soit en mettant le curseur au milieu de celle-ci et en appuyant sur F5, soit en créant un raccourci.
Le code correspondant se trouve dans le fichier Timer.txt.
VBA reste actuellement dépourvu de composant Timer. Il est très facile d'en créer un.
Collez dans un module de feuille la première procédure (qui n'est qu'un exemple de mise en oeuvre qui démarre/arrête le minuteur à chaque changement de cellule) et dans un module public la seconde procédure.
Renvoyer la couleur active d'une mise en forme conditionnelle
Le code correspondant se trouve dans le fichier CouleurMFC.txt. Cette fonction retourne la valeur du format actif d'une mise en forme conditionnelle. Avec la fonction proposée, deux valeurs peuvent être récupérées. La syntaxe de la formule est la suivante :
CouleurMFC(plage, [mode])
où plage est la plage concernée et mode une valeur binaire facultative (0 par défaut) qui renvoie la valeur de Interior.ColorIndex (mode = 0) ou celle de Interior.Color (mode = 1).
Mais avec quelques modifications, toutes les autres propriétés pourraient être renvoyées.
La mise en forme conditionnelle peut dépendre d'une valeur directe (>1 and <10) ou indirecte (>=D10 and <=E10).
Transformer une couleur en valeur numérique
Le code correspondant se trouve dans le fichier distri_couleur.txt. Voisine de la fonction précédente, mais cette fois sous forme de procédure et non limitée à une mise en forme conditionnelle, cette macro permet d'obtenir le code d'une couleur, pour traitement ultérieur selon vos besoins.
Formules sur matrice multi-feuilles
Le code correspondant se trouve dans le fichier trf_plage.txt.
Seules les formules Somme, Moyenne, Nb, Nbval permettent de faire des calculs sur des plages discontinues ou multi-feuilles (calcul en 3D). Ainsi, =Somme(Feuil1:Feuil3!A1:A10) effectue la somme de la plage A1:A10 des trois feuilles.
Somme.si, Nb.si, Sommeprod, Index, Equiv ainsi que les formules matricielles ne le font pas, même avec des plages nommées.
Cette fonction personnalisée n'a pas pour but de réécrire les différentes formules, mais transforme une plage discontinue ou une plage sur un certain nombre de feuilles en une seule matrice. Sa syntaxe est la suivante :
=Trf_plage(Ma Plage; "Feuil1:Feuil3")
où Ma plage est la plage à traiter (comme A1:A10) et "Feuil1:Feuil3" la plage de feuilles à traiter. Cet argument est facultatif pour les plages discontinues ne nécessitant pas de multi-feuilles.
Attention, les formules Nb.Si et Somme.Si ne fonctionnent toujours pas, mais elles peuvent être facilement transformées en formules matricielles. Index et Equiv fonctionnent très bien.
Souvenez-vous que les formules matricielles doivent être validées en appuyant simultanément sur les touches CTRL+MAJ+ENTREE. Si la validation est correctement effectuée, des accolades {} encadrent automatiquement la formule.
Fonction NB.SI_Plus
Le code correspondant se trouve dans le fichier nbsi_plus.txt.
La fonction NB.SI d'Excel possède quelques contraintes en termes de nombre d'arguments. La fonction Plus présentée ici permet d'appliquer la fonction NB.SI en s'affranchissant de ces limites.
- C'est la plage de critères (sur une seule ligne) qui détermine le nombre de colonnes à balayer.
- La plage de critères peut comporter des blancs.
- Les cellules vides de la plage de critères permettent d'ignorer cette colonne dans le calcul.
- Sélection de la hauteur du bloc (en ligne) :
· automatique en ne sélectionnant que la première cellule Gauche/Haut de la plage de recherche ;
· programmée en sélectionnant la première cellule Gauche/Haut et en étirant sur la ligne souhaitée.
L'emplacement de la plage de critères peut être quelconque.
RechercheV polyvalente
Le code correspondant se trouve dans le fichier rechercheVmulti.txt.
La fonction RechercheV d'Excel possède de nombreuses limites :
- La colonne de la donnée doit être avec un décalage positif.
- La fonction s'arrête à la première occurrence trouvée et ne permet pas de trouver toutes les occurrences d'une colonne de recherche, même si ce n'est pas un doublon tel Dupont Pierre et Dupont Claude.
La RechercheV polyvalente proposée ici ne possède pratiquement plus de limite :
- La liste ne doit pas être triée.
- La colonne de la donnée peut être avec un décalage négatif.
- Le décalage n'a pas de limite du moment qu'il se trouve dans une valeur de colonne valide.
- Les formules peuvent faire une recherche multiple (principal intérêt).
- Il est possible de faire la recherche dans une plage colonne définie.
- La liste peut comporter des cellules vides.
Les fonctions doivent se situer dans une même colonne sans cellule vide intercalaire. La recherche recommence sinon à la première occurrence de la liste.
Dans les lignes 3 à 9 de la colonne E se trouve la formule :
=RechercheVmulti($C$3;$B$3;$H$3)
C3 est la colonne où se trouve la liste de recherche. La recherche commence à la ligne 3, mais vous pourriez spécifier une autre ligne de début de la recherche. B3 est la cellule critère, H3 la colonne où la donnée va être saisie : elle peut se situer n'importe où sur la même ligne.
Dans les lignes 3 à 7 de la colonne F se trouve la formule :
=RechercheVmulti($C$3;$B$3;$A$3; 20)
C3 est la colonne où se trouve la liste de recherche. La recherche commence à la ligne 3, mais vous pourriez spécifier une autre ligne de début de la recherche. B3 est la cellule critère, A3 la colonne où la donnée va être saisie : elle peut se situer n'importe où sur la même ligne (le décalage est ici négatif).
La recherche s'arrête à la ligne 20, même s'il y a d'autres concordances plus bas, ce qui permet de délimiter la plage de la colonne - début de recherche dans (C)3 et fin dans (C)20. Ce paramètre est optionnel : s'il n'est pas renseigné, la recherche s'effectue sur toute la colonne.
Collez le code dans un module standard.
Détection de modification de cellule
Le code correspondant se trouve dans le fichier Worksheet_SelectionChange.txt.
L'événement Change d'une feuille détecte une modification de la cellule active, mais n'apporte pas le moindre renseignement quant au contenu.
Cette procédure événement permet de savoir si la cellule a été modifiée, fonctionne si elle a été initialisée mais aussi si elle a été modifiée.
Lorsque l'événement Change est activé, c'est le contenu de la cellule qui vient d'être sélectionnée qui se trouve dans Target. C'est la cellule qui vient d'être quittée qui est testée.
Ajouter un bouton de commande commandButton et le code qui va avec
Le code correspondant se trouve dans le fichier CréerBouton.txt.
C'est un petit code relativement simple qui se borne à créer un bouton de commande et à le doter automatiquement de code. Collez ces deux sous-routines dans un module général (Module1 par exemple).
VBA et les collections d'objets
Le code correspondant se trouve dans le fichier InitOption.txt.
Lorsqu'une feuille ou un formulaire utilisateur (Userform) comporte de très nombreux contrôles, il est parfois fastidieux d'écrire du code dans chaque événement des contrôles.
Voici comment traiter vos contrôles comme s'ils étaient indexés (mode VB6 avec index).
Ouvrez un nouveau classeur, puis incorporez, l'un en dessous de l'autre, dans la colonne C :
- deux boutons d'option nommés OptionButton1 et OptionButton2, dont la propriété GroupName est fixée à Feuil1A ;
- deux boutons d'option nommés OptionButton3 et OptionButton4 dont la propriété GroupName est fixée à Feuil1B ;
- trois cases à cocher CheckBox1, CheckBox2 et CheckBox3.
Enregistrez le classeur sous le nom Collections.xls, puis collez les deux premières procédures du fichier InitOption.txt dans un module, les deux suivantes dans un module de classe (pour ouvrir un module de classe, choisissez Insertion > Module de classe) et la dernière dans le module du classeur.
À l'ouverture du classeur, les collections sont opérationnelles. Si le code VBA est modifié, les collections seront toutefois désactivées et il faudra appeler la sous-routine ActivationCollect soit en mettant le curseur au milieu de celle-ci et en appuyant sur F5, soit en créant un raccourci.
Un minuteur une seconde tout simple
Le code correspondant se trouve dans le fichier Timer.txt.
VBA reste actuellement dépourvu de composant Timer. Il est très facile d'en créer un.
Collez dans un module de feuille la première procédure (qui n'est qu'un exemple de mise en oeuvre qui démarre/arrête le minuteur à chaque changement de cellule) et dans un module public la seconde procédure.