En direct d’Excel Virtually Global (2/2)

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 :

1. On retrouve classiquement l'utilisation des variables tableaux pour lire ou écrire en une seule fois les données. Voir ici.
2. 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.
3. 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 !