Divers Archives - Page 144 sur 180

Votre opinion pour mon prochain CD de formation sur VBA avancé ?

de Gaetan Mourmant | 18/07/2013 - 21:56 |12/09/2013 Divers

Bonjour à tous,

En parallèle au développement soutenu de FormXL Pro - pour les mises à jour, cliquez ici - je vais enregistrer le prochain volume sur VBA avancé. Pour cela, j'ai besoin de votre aide 🙂

Voici cinq idées de formations possibles pour les prochains volumes de VBA avancé :

A - Les contrôles de listbox en profondeur

Les listbox permettent de gérer énormément d'aspects et sont beaucoup plus puissantes qu'on pourrait croire à première vue.
Dans ce cours, nous allons voir comment créer un gestionnaire avancé de vue personnalisée (tri, ordre des colonnes, etc.) ainsi qu'un hyper navigateur pour des fichiers avec beaucoup de feuilles.
Au programme : l'utilisation des multi-colonnes, le déplacement par souris à l'intérieur d'une listbox, l'ajout de boutons de sélection, désélection et d'inversion de sélection, la sélection simple ou multiple, etc.

B - La création d'un graphique dynamique

Ce graphique va s'ajuster automatiquement en fonction de la sélection en cours.
Au programme : le déplacement d'un graphique en fonction de la position de l'userform, les procédures événementielles, le changement du style de graphique, etc.
Réalisation en collaboration avec Dominique Handelsman.

C - La connexion à des tables multiples

Soit en utilisant des liaisons entre tables (par exemple entre une table clients et une table factures), soit via la réunion de tables, par exemple ajouter les unes en dessous des autres, des feuilles contenant des tables mensuelles.
Au programme : les bases du SQL sur Excel, création automatisée de connexions SQL en VBA, ajout d'un formulaire de contrôle de cette connexion avec sélection des fichiers et des tables liées, etc.

D - Optimisation de filtres de recherche et d'extraction

Il s'agit ici de concocter un mini-moteur de recherche pour vos bases de données.
Au programme: extraction des informations suivant des filtres, filtres dynamiques s'ajustant dès qu'on tape une lettre, optimisation du code VBA, comparaison de plusieurs approches, gestion des tableaux -Array(), etc.

E - 77 trucs à faire avec Vba que vous ne pouvez pas faire avec Excel

Il s'agirait ici de répertorier des trucs en VBA qui ne sont pas faisable directement sous Excel. Ca demanderait plus de temps à créer, mais ca pourrait donner une belle collection de codes pour optimiser Excel.

J'aurais donc beaucoup aimé votre retour pour savoir si

- vous vouliez voir d'autres sujets traités et

- dans quel ordre de priorité vous voudriez voir ces sujets traités.

N'hésitez pas à laisser un commentaire ci-dessous.

A bientôt.

Gaetan

Exploser vos neurones: création de listes et noms dynamiques

de Gaetan Mourmant | 11/07/2013 - 06:03 |11/07/2019 Divers, XL-Humour

5 commentaires

Bonjour à tous,

Il s'agit d'une question récurrente, donc voici une réponse approfondie.

Tout d'abord quelques explications techniques, puis des vidéos récapitulatives plus bas.

Quelques définitions

Par liste dynamique, on entend la création d'un nom faisant référence à une plage de manière dynamique, c'est à dire que lorsqu'on ajoute un nouvel élément en dessous de la liste, ce nom va automatiquement prendre en compte ce nouvel élément.

Par exemple, si un nom fait référence à une liste de villes, en ajoutant une nouvelle ville en dessous de cette liste, le nom va automatiquement inclure cette nouvelle liste.

Ceci est très pratique pour créer des sources de données dynamiques pour des tableaux croisés dynamiques, ou encore en lien avec une liste déroulante via Données - Validation - Liste (voir vidéo).

Il y a pour cela deux techniques.

Première technique (Excel 2007 et suivantes) : avec un tableau

Cette solution est la plus simple, il suffit de mettre la liste en format de tableau (onglet Accueil), puis de lui attribuer un nom.

Au passage, vous verrez que le nom ne sera visible que via le gestionnaire de noms (onglet Formules)

Deuxième technique (toutes les versions d'Excel) : avec la fonction DECALER

Vous avez une présentation détaillée de cette fonction dans ce fichier et ici.

La fonction DECALER permet de définir une plage en fonction d'une référence de départ, d'un décalage de ligne et de colonne par rapport à cette référence, ainsi que d'une définition de la hauteur et de la largeur de cette plage. Si la hauteur et la largeur ne sont pas définies, il y aura un simple décalage de la cellule.

Par exemple, =DECALER(Feuil1!$A$1;2;3;4;4) renverra la plage allant

- de la cellule A1+2 lignes vers le bas et 3 colonnes vers la droite, soit donc la cellule D3

- à la cellule D3 + 4 lignes vers le bas et 4 colonnes vers la droite, soit donc la cellule G6

Au final donc, la plage D4:G6 comme présenté ci-dessous.

On peut ensuite améliorer ce nom pour prendre en compte une liste dynamique disponible dans une colonne.

On utilise pour cela la fonction NBVAL qui permet de retourner le nombre de valeurs dans une cellule.

Ça nous donne donc ceci : =DECALER(Feuil1!$A$1;1;0;NBVAL(Feuil1!$A:$A)-1;1)
qui se lit comme :

Partir de A1,
décaler d'une cellule vers le bas (donc A2),
de 0 cellule vers la droite (donc A2),
puis établir la hauteur comme étant le nombre de valeur de la colonne A (ici 4) moins 1 pour ne pas prendre en compte la ligne de titre.
Finalement, on définit la largeur de la plage à 1, pour ne renvoyer qu'une colonne.

On a donc au final la plage A2:A4.

Si on ajoute une nouvelle ligne, la plage sera modifié automatiquement pour renvoyer A2:A5.

Utilisation des noms dynamiques

Une fois le nom dynamique créé, vous pouvez l'utiliser de multiples manières :

- soit comme source d'une liste de validation, via Données - Validation - Listes et taper =Nom_Dynamique

- Pour 2003, soit comme source d'un tableau croisé dynamique, dans ce cas, on définira aussi la largeur de la plage comme étant dynamique. On a donc la formula suivante (en considérant que la plage commence en A1 et que la feuille ne contient uniquement que la table de données) :
=DECALER(Feuil1!$A$1;0;0;NBVAL(Feuil1!$A:$A);NBVAL(Feuil1!$1:$1))