Je continue d'avoir beaucoup de plaisir à écouter tous ces experts discuter d'Excel.
Menu dynamique:
- Utiliser la caméra pour créer un menu dynamique en image avec des liens hypertextes.
- Voici le lien vers le fichier.
Audit de classeurs
Excellente liste de points à vérifier lors de l'audit d'une feuille de calcul. Présentation de Patrick O'beirne (http://www.sysmod.com/).
Voici un extrait de sa liste de "bad smells"
- Erreurs sur les références relatives/absolues (le $).
- Vérification des liens externes.
- Calculs manuels.
- Vérification des types de données (par exemple, des dates mal formatées, du texte à la place de nombres).
- Recherchev, Match avec la valeur 0 ou 1 mal appropriée.
- Long code VBA provenant de l'enregistreur de macro.
Quelques trucs pour identifier des erreurs:
- Remplacer des valeurs numériques par des 0 et des 1 pour voir si tout est ok (sur une copie de sauvegarde, bien sur).
- Avoir des données valides de référence pour vérifier que les calculs sont exacts.
- Mise en couleur des formules, avec une couleur différente par formule différente. De fait, si dans une ligne ou une colonne, on a des formules différentes, on va tout de suite les voir. Patrick a fourni gratuitement ce fichier pour faire cela : www.sysmod.com/xltest_colorizer.zip
Par exemple, ca m'a permis de rapidement identifier des erreurs potentielles en colonne AO, AP et AQ :
- Patrick a aussi mentionné ce site pour l'analyse du code VBA (pas encore testé) : https://rubberduckvba.com/
Je rajouterai :
- Plages entrées en dur en VBA, par exemple, range("A1:A10"), ce qui peut générer des problèmes si quelqu'un ajoute ou supprime des lignes ou si d'autres données sont entrées en A11
- VBA: Utilisation du nom de la feuille, au lieu du nom de l'objet feuille, sauf s'il y a un contrôle absolu de l'application.
Il y a même un certificat : http://www.spreadsheetsafe.com/
Excel Dot Net for Application (DNA)
Il y a eu beaucoup de discussions sur les langages de programmation qui peuvent être utilisés autour d'Excel. Parmi ceux-ci, on retrouve .Net, mais aussi Python, Java, etc.
Voici donc quelques limites d'Excel justifiant l'utilisation de .Net:
- Multithreading
- Streaming pour des données en temps réel
- Parfois plus rapide
- Nombreuses librairies
- Etc.
PyXLL pour l'utilisation de Python
Charles Williams - Construire des solutions Excel plus rapide
- Utilisation d'un chronomètre en VBA. Charles utilise un MicroTimer qui vient - sauf erreur de ma part - avec une de ses applications. De mon côté, j'utilise le Timer qui est suffisant pour beaucoup de cas.
- Déclarer des variables tableaux en double peut faire gagner du temps (si les valeurs à charger sont bien toutes de ce type de données).
- Au lieu de comparer des Strings une à une - ce qui prend beaucoup de temps d'après Charles - il propose d'abord de tester si le nombre de caractères est le même avec la fonction LenB, puis de comparer ensuite les chaînes de caractère.
- En plus de ScreenUpdating et autres EnableEvents, Charles a aussi mentionné ceci : Application.EnableAnimations = False.
- Personnellement, j'évite au maximum de jouer avec les calculs, juste au cas où Excel plante et que le calcul n'est pas remis.
Du VBA encore plus rapide :
- On retrouve classiquement l'utilisation des variables tableaux pour lire ou écrire en une seule fois les données. Voir ici.
- Démonstration de l'utilisation de worksheetFunction qui est aussi très très rapide. L'idée générale étant de laisser Excel faire le travail plutôt que de le reprogrammer en VBA.
- Une conclusion très intéressante de la démonstration est que l'utilisation de WorksheetFunction.Match DIRECTEMENT AVEC une plage est 15 fois plus rapide que WorksheetFunction.Match avec un array issu d'une plage!
Il y a encore beaucoup, beaucoup de choses à dire, mais je vais digérer un peu tout ça !
