Aide-mémoire, les options d’Excel dont on ne se rappelle jamais !

Bonjour à tous,

Il y a des endroits comme ça dans Excel, on dirait qu'ils disparaissent !

En voici quelques-uns...

Personnellement, je laisse la majorité de ces options par défaut et il est rare que j'ai à les changer...C'est peut être aussi pour cela que j'oublie où elles se trouvent !

Ajouter ou enlever la fonction LireDonnéesTabCroisDynamique
Cette fonction apparaît automatiquement lorsque l'on fait référence à une cellule d'un tableau croisé dynamique. Elle est parfois très utile dans certaines circonstances spécifiques, notamment lors de la gestion de larges bases de données.
Pour la désactiver, aller dans Fichier - Options - Formules et décocher :

Ajouter ou enlever les noms de table dans les formules
Dans une table, par défaut, la formule va faire référence au nom de la table et de ses colonnes. Ceci reste une excellente pratique, mais dans certains cas, on veut revenir à une formule normale.
Pour cela, aller dans Fichier - Options - Formules et décochez :

Ajouter ou enlever la recopie automatique des formules dans les tables
Si vous ajoutez une formule dans une table, par défaut, elle sera automatiquement recopiée vers le bas. Une fois celle-ci recopiée vous pouvez la désactiver en allant sur la dernière cellule et en cliquant sur le bouton en bas à droite de la cellule :

Ici, vous avez aussi accès aux options qui permettent de désactiver cette fonctionnalité de manière permanente, au lieu d'une seule fois. Ceci est aussi accessible via Fichier - Options - Vérification - bouton "Options de correction automatique" et enfin, l'onglet "Mise en forme automatique au cours de la frappe".

Modifier les listes personnalisées
Voici une autre option qui a été planquée dans un recoin sombre et obscur. Elle est parfois bien pratique si vous avez souvent à compléter des listes personnalisées.

Pour y accéder :
Fichier - Options - Options avancées, et tout en bas, cliquer sur le bouton "Listes personnalisées"

Enlever dans VBE le message d'erreur de saisie
Lorsque vous faites une erreur en VBA, un message d'erreur de saisie apparait :

C'est très bien au début, mais ça devient vite lassant. De fait, vous pouvez désactiver ce message en allant dans Outils-Options

En espérant que cet aide-mémoire vous soit utile.

N'hésitez pas si vous en connaissez d'autres !

A bientôt.

Gaëtan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Nouvelle formation, soyez les premiers à l’essayer !

couv excel vba vol3-01Bonjour à tous,

Des heures de travail intense, acharné et parfois quelques moments de schizophrénie en assemblant les vidéos, mais mon nouveau bébé est prêt !
Découvrez en exclusivité cette nouvelle formation : VBA avancé volume 3, les variables tableaux en VBA.

Vous pouvez dès à présent acheter la formation et économisez 10€ sur l'offre de lancement.

Venez vite la découvrir en cliquant ici : http://www.xlerateur.com/vba3/ 🙂

A bientôt.

Gaetan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Des nouvelles de la prochaine formation VBA avancé

Bonjour à tous,

L'enregistrement des vidéos avance, et cette nouvelle formation va vous être super utile et va vous permettre de gagner un temps précieux!

La première série de vidéos est enregistrée.
Elle va vous permettre d'avoir une compréhension globale des arrays - les tableaux dans VBA. Ceux-ci permettent d'accélérer grandement les calculs en VBA.

Actuellement, je suis en train de finaliser l'explication de plus de 50 fonctions, en créant une base détaillée, visuelle et super rapide d’accès pour insérer la bonne fonction au bon endroit.
Une explication détaillée en vidéo de la fonction vous permettra aussi de bien la comprendre !
C'est un petit challenge à réaliser, mais cela vous sera très utile au final 🙂

Voici quelques copies d'écran pour vous faire patienter ! 🙂

VBA-Array-3 1

VBA-Array-2 1 1

vba-array4 1

A bientôt.

Gaëtan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Fonction IsNumeric – Attention aux guillemets

Bonjour à tous,

xl-en-bref 1 1En bref : attention à la fonction VBA "IsNumeric", car cette fonction va retourner Vrai (True), même pour une chaîne de caractère comme "1".

Je suis en train de travailler sur des fonctions très spécifiques pour la prochaine formation VBA avancé sur les arrays !
Il va être très complet et super utile 🙂

Au passage, je suis tombé sur la fonction IsNumeric et une "limite" assez dangereuse (source : Cheap Pearson).

Ce code va ainsi retourner 12, au lieu de 3 !

Sub test_AttentionIsNumeric()
Dim V1 As Variant
Dim V2 As Variant
V1 = "1"
V2 = "2"
If IsNumeric(V1) = True Then
If IsNumeric(V2) = True Then
MsgBox V1 + V2
End If
End If
End Sub

Donc, il faut faire très attention à la fonction IsNumeric, car elle va retourner la valeur Vraie (True), même s'il s'agit d'une string (chaîne de caractères), et le '+' va se comporter comme un '&'.

[EDIT]: suite à un commentaire de Raphaël, vous pouvez faire une conversion des strings avant utilisation, en utilisant ces fonctions : CDbl ou CLng. Il existe d'autres fonctions de conversion pour d'autres cas de figure : CStr, CVar, CDate, etc.

A bientôt.

Gaëtan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

7 règles d’or pour appréhender un fichier Excel complexe !

7 regles images 1Bonjour à tous,

Cela va surement vous arriver : vous démarrez une nouvelle mission et vous devez mettre à jour et/ou perfectionner un fichier méga-complexe. Pas de panique !

De façon à éviter des grosses catastrophes, voici une série de règles d'or pour y arriver au mieux.
Bien sur, si vous voyez d'autres aspects, n'hésitez-pas à commenter ci-dessous :-).

Comme me l'a fait remarquer mon ami Dominique H., ces règles sont itératives et l'ordre dépend du projet : "Pour ma part, je documente plus rapidement qu'il n'est conseillé par cette méthode. Autrement dit, j'alterne des actions visant à une compréhension locale et les actions visant à une compréhension globale. Donc une alternance de top-down et bottom-up.". Merci Dominique !

1. Sauvegardez !

Faites une copie du dossier contenant les fichiers, assurez-vous que les sauvegardes soient faites régulièrement et travaillez sur la copie de sauvegarde dans un premier temps

En effet, si vous ouvrez un fichier complexe, il y a de fortes chances que des liaisons avec d'autres fichiers soient actives.

De fait, sauvegardez non seulement le fichier, mais aussi les fichiers liés. Si les fichiers sont tous dans le même répertoire, faites une copie du répertoire.

copier 1

De manière générale, je tente d'éviter les liaisons entre fichiers, mais cela n'est pas toujours possible.
Pour voir les liaisons : Onglet Données, Modifier les liens.
liaison_fichier_externe 1

Idéalement, travaillez sur le fichier de sauvegarde pour le comprendre en profondeur avant de modifier le fichier original.

2. Discutez avec l'auteur et les utilisateurs du fichier

Ca peut sembler évident, mais si l'auteur du fichier est encore joignable, cela peut vous faire gagner un temps considérable.
Passez au moins deux heures avec l'auteur du fichier pour comprendre la philosophie du fichier, la structure globale et les points délicats du fichier.
Discutez aussi avec les utilisateurs pour comprendre comment le fichier est utilisé.

3. Prenez votre temps

Si le fichier est très complexe, plusieurs jours sont parfois nécessaires pour comprendre toute la structure du fichier.

4. Documentez

C'est aussi un excellent moyen de s'assurer que l'on maîtrise complétement le fichier.
Pour cela :
- créez une feuille ou un document word dédié à la documentation.
- ajoutez des commentaires directement dans les cellules - clic droit, ajouter un commentaire, ou via l'onglet Révision :
novueau-commentaire 1

5. Comprenez parfaitement la structure globale

Il s'agit ici de prendre connaissance de toutes les feuilles qui existent dans le fichier.

- Par exemple, assurez vous que vous connaissez les feuilles masquées, clic droit sur une feuille :
feuilles_masquees 1

- ou encore les feuilles masquées via VBA (xlSheetVeryHidden).
Ouvrez l'éditeur de visual basic et regardez si des feuilles ont la propriétés XLVeryHidden - ALT+F11, sélectionnez la feuille, touche F4 pour afficher les propriétés :
xlSheetVeryHidden 1

6. Affinez la compréhension des formules

Voici quelques outils que l'on peut combiner :
- Sur une cellule qui contient un calcul qui utilise plusieurs cellules de la feuille >> clic dans la barre de formule pour avoir un visuel sur les cellules concernées - leurs bordures se colorent tout comme leurs références dans la formule. Merci à Laurent J. pour la suggestion.
- Utilisez les outils d'audit, pour repérer les antécédents et les dépendants.
- Affichez toutes les formules.
- Évaluez les formules.

audit

- Une alternative plus rapide pour évaluer les formules consiste à sélectionner une partie de la formule et appuyer sur F9 pour calculer cette sélection.
ATTENTION à ne pas valider cette formule, sinon vous allez rendre le fichier inutilisable. Donc utilisez la touche ECHAP au lieu d'entrée pour sortir de la formule sans valider.
Voir aussi ici pour une explication plus détaillée.

toucheF9

- ATTENTION, si vous voyez des { } autour des formules, il s'agit de formules matricielles qui sont validées par CTRL+MAJ+ENTREE, donc si vous devez les éditer, sortez de la formule avec ECHAP, mais ne validez surtout pas avec seulement la touche ENTREE. Si vous devez modifier la formule, validez avec CTRL+MAJ+ENTREE.
formule-matricielle

- Connaissez vos noms. Souvent dans des fichiers complexes, de nombreux noms ont été créés et il convient donc de bien les analyser.
Pour cela, utilisez le Gestionnaire de noms, disponible dans l'onglet Formules :

- Utiliser l'outil espion pour visualiser simultanément des changements dans plusieurs endroits du fichier
fenetre-espion 1

Gestionnaire-de-noms 1
Vous pouvez aussi coller la liste des noms pour faire une analyse plus fine :
coller-liste-noms

7. Comprendre le code VBA

On pourrait refaire une longue liste pour ce point, mais il est essentiel de bien comprendre le code VBA du fichier.
Pour cela, utilisez les outils de débogage :
- Ajout d'un point d'arrêt en cliquant dans la marge
vba-point-arret 1
- Déroulement pas à pas avec les touches de raccourci appropriées
VBA-pas-a-pas
- Utilisation des espions
vba-espion 1

Et il y a encore pleins d'autres en VBA, mais ca sera pour une prochaine fois !

Je vous invite à commenter pour partager votre propres trouvailles :-).

A bientôt.

Gaëtan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !