Cette semaine j’ai dû réaliser une interface entre SAP et SAGE pour un client pour la gestion des RH.
Le besoin était le suivant : le client extrait depuis SAP un fichier texte avec les données des salariés, les imputations, les travaux, les congés du mois. Ensuite, il faut réintroduire les données retravaillées dans leur logiciel de paie SAGE sous un format particulier.
Le souhait était d’utiliser une macro EXCEL car le fichier à importer dans SAGE est au format CSV.
Pour mes besoins techniques, j’ai dû avoir recours à un « RECHERCHEV » (VLOOKUP) qui me permettrait en fonction d’un code de retrouver une autre donnée fournie dans une feuille de paramétrage du classeur. Ce paramétrage étant modifiable par l’utilisateur, il fallait pouvoir récupérer la donnée depuis une feuille de paramétrage.
Mais comment faire une RECHERCHEV dans VBA ?
Ce que j’ai trouvé en général sur le WEB est un balayage de chaque ligne jusqu’à trouver la bonne valeur. Cela fonctionne très bien mais lorsque qu’on doit le faire pour plusieurs milliers de lignes, la performance n’était pas au RDV.
Il fallait optimiser.
RECHERCHEV
Je vous propose donc ici ma version pour gagner en performance ce type de recherche. J’imagine que ce n’est pas la seule solution, il doit même en exister de meilleures, donc je suis preneur de vos propositions.
Voici une des grille dont je parlais. Il faut savoir que pour chaque imputation traitée, j’ai dû faire un « RECEHERCHEV » sur 8 grilles comme celle-ci. La performance devait donc forcément être optimisée.
Ma première étape a été de charger toutes ces grilles dans des tableaux VBA, comme ceci :
On peut donc voir pour l’exemple concernant les rubriques que je charge dans le tableau Tab_Rubriques toutes les cellules correspondante : Q8:T18.
’18’ puisque la ligne du bas est calculée en fonction de la hauteur du tableau :
7 (pour les lignes non prises en compte)
+ 11 (nombre de rubriques calculées dans le classeur Excel et dont le champ nommé associé est NBRubriques).
Pour réutiliser facilement ces tableaux, je les ai tous déclaré en variable PUBLIC.
Chaque tableau créé aura donc deux axes (arguments): la ligne et la hauteur. Si nous reprenons l’exemple des rubriques, alors Tab_Rubriques(4, 2) aura la valeur: ‘Taux unitaire panier’. Il faudra donc bien tenir en compte du fait que cela correspond à la cellule $R$11 et non à la cellule $D$2.
Ensuite, j’ai créé une fonction « RECHERCHEV » :
Où sTableau est le tableau de recherche, Column est la colonne que je dois retourner et Valeur, la valeur cherchée. Si je ne trouve aucune valeur, je retourne « NA »
En somme, nous avons une nouvelle fonction RECHERCHEV que nous pourrions écrire ainsi :
RECHERCHEV(Valeur, sTableau, Column)
ou
RechercheTableau(sTableau, Column, Valeur)
PERFORMANCES
J’ai fait des mesures de temps sur l’ensemble de mon classeur en faisant soit le choix 1 de balayer le classeur et le choix 2 avec la méthode ci-dessus. Le gain est assez impressionnant puisque j’ai gagné 40%.
Aussi, j’ai voulu faire des mesures dans un autre classeur pour voir l’effet uniquement sur la différence de méthode sans avoir les autres parties de la macro qui faussent la différence de performance.
L’effet est immédiat : je vais 20 fois plus vite en chargeant mes données dans un tableau par rapport au balayage des cellules.
Si vous avez d’autres propositions, n’hésitez pas.