Fonctions Excel en VBA

Version imprimable

25/11/2014, 13h57
Henri1830

1 pièce(s) jointe(s)

Fonctions Excel en VBA

Bonjour,

Je suis un amateur à peine éclairé en VBA.

Je dois récupérer les données d'une comptabilité et les traiter pour plus de lisibilité.
La comptabilité évoluant sans cesse, il me faut repartir à chaque fois sur base de nouvelles données.
Je l'ai fait manuellement, en mettant les formules qu'il faut dans les cellules et a fonctionne très bien, mais la personne qui va l'utiliser au quotidien n'a pas les connaissances nécessaires pour faire les manipulations.
Je me suis dit que j'allais pouvoir faire appel en VBA pour lui éviter des difficultés.

En VBA, j'ai voulu insérer dans les cellules les formules que j'avais utilisé. Ça a très bien fonctionné pour =P2&Q2&T2&AD2.
J'ai utilisé ActiveSheet.Cells(2, 3).Formula = "=P2&Q2&T2&AD2" et pas de souci, par contre, une simple formule telle ActiveSheet.Cells(2, 5).Formula = [=DROITE("00"&MOIS(W2);2)] place dans la cellule #VALEUR.
Je me suis dit que le souci était la langue et j'ai traduit l'instruction en anglais : ActiveSheet.Cells(2, 5).Formula = =RIGHT("00"&MONTH(W2);2)], ça n'a rien changé, sans compter qu'il est un peu allergique aux différents délimitateurs, guillemets, apostrophes, crochets, etc... et, cerise sur le gâteau, dès qu'une formule dépasse les 255 caractères, je me fais recaler.

Donc, je pense que j'ai dépassé les limites de ma compétence que j'aimerais bien un peu étendre. :mrgreen:
J'ai joins mon code pour être complet.

Merci d'avance pour votre aide.

Henri
25/11/2014, 14h11
Philippe Tulliez

Bonjour,
Voir réponse dans cette discussion dont le sujet est très proche de celle-ci.
En lieu et place de la formule

Code:

=DROITE("00" & MOIS(W2);2)

j'utiliserais celle-ci

Code:

=TEXTE(W2;"mm")

Ce qui donnerait en VBA par exemple (pour la formule placée en C2:C10)

Code:

ActiveSheet.Range("C2:C10").Formula = "=TEXT(W2,""mm"")"
25/11/2014, 15h08
Henri1830

Ok, ça a marché, un grand merci déjà, mais ce n'était qu'un maigre échantillon :?
J'en ai plusieurs autres, dont celle-ci qui présente plusieurs sources de conflit, sa longueur constituant le 1er refus de VBA.

Code:

=SI(ET(SIERREUR(CHERCHE("µ";SUBSTITUE(NOMPROPRE(X2);"M";"µ";NBCAR(X2)-NBCAR(SUBSTITUE(NOMPROPRE(X2);"M";""))));0)>0;T2=Paramètres!$B$1);DROITE(NOMPROPRE(X2);NBCAR(X2)-CHERCHE("µ";SUBSTITUE(NOMPROPRE(X2);"M";"µ";NBCAR(X2)-NBCAR(SUBSTITUE(NOMPROPRE(X2);"M";""))))+1);"")

J'en ai d'autres en stock :oops:

Code:

=SI(T2=Paramètres!$B$1;Q2&" - "&F2&"."&E2&" - "&INDEX(HK_ANT!Camion;EQUIV(A2;HK_ANT!NoDePiece;0));"")

Code:

=SI(T2=Paramètres!$B$1;AA2;0)

Code:

=SI($T2=Paramètres!$B$1;SIERREUR(ABS(INDEX(Montant;EQUIV(SI($P2="VEEXP";$A2&Paramètres!$B$4;$A2&Paramètres!$B$6);Jnl_NoDoc_CptGen_CodeTVA;0)));"Imput. Err.");0)

Voilà, voilà, voilà...

Merci encore, désolé du dérangement.

Henri
25/11/2014, 15h46
Philippe Tulliez

Bonjour,
Il y a toujours une solution.
Lorsque dans une formule, il y a lieu de donner le nom de la feuille et/ou le nom du classeur ainsi qu'une adresse de cellule ou de plage de cellules, j'utilise des balises que je remplace à l'aide de la fonction Replace par l'adresse souhaitée. Voir exemple dans cette discussion.
25/11/2014, 16h21
Henri1830

C'est ici qu'on voit la différence entre "Expert Confirmé Sénior" et "Invité régulier".

Mille mercis pour la réponse, mais j'avoue humblement que je suis largué. :?
Serait-ce trop vous demander d'adapter le système du Replace à une de mes formules ? Celle-ci, par exemple :

Code:

=SI($T2=Paramètres!$B$1;SIERREUR(ABS(INDEX(Montant;EQUIV(SI($P2="VEEXP";$A2&Paramètres!$B$4;$A2&Paramètres!$B$6);Jnl_NoDoc_CptGen_CodeTVA;0)));"Imput. Err.");0)

Je suis confus de faire le difficile mais vous volez dans la stratosphère alors que j'essaie de décoller. ;)

Encore merci pour votre patience.

Henri

Bonjour,

Citation:

Serait-ce trop vous demander d'adapter le système du Replace à une de mes formules ?

Non, je ne peux pas l'adapter mais je vais redonner une explication complète avec un exemple qui j'en suis convaincu te permettra de faire les adaptations.
Pour l'exemple, je pars du scénario suivant, une formule de recherche avec la fonction RECHERCHEV d'une liste de données se trouvant dans une feuille nommée [db] plage et qui renvoie le message "Pas trouvé" en cas d'insuccès.
La formule sera placée en cellule C2 de la feuille active et ensuite sur la plage de cellule C2:C101 de la même feuille
La formule est

Code:

=SIERREUR(RECHERCHEV($B2;db!$A$2:$J$106;EQUIV("Nom";db!$A$1:$J$1;0);FAUX);"Pas trouvé")

1) Pour connaître la syntaxe d'une formule à placer dans une plage de cellules d'excel par une procédure VBA, le plus simple est de l'écrire manuellement dans excel pour vérifier qu'elle fonctionne correctement.
2) Ensuite dans l'éditeur de VBA (VBE), aller dans la fenêtre d'exécution (que l'on active par le raccourci clavier Ctrl+G) et taper cette ligne de code (pour l'exemple la formule se trouve dans la cellule C2 de la feuille active

Code:

? range("C2").Formula

La formule affichée est

Code:

=IFERROR(VLOOKUP($B2,db!$A$2:$J$106,MATCH("Nom",db!$A$1:$J$1,0),FALSE),"Pas trouvé")

3) Cette formule est une chaîne de caractères qui doit être entrée en VBA, placée entre les guillemets et s'il y a des guillemets présents dans la formule, ils doivent être doublé (dans l'exemple "Nom" et "pas trouvé"
Soit

Code:

ActiveSheet.Range("C2").Formula = "=IFERROR(VLOOKUP($B2,db!$A$2:$J$106,MATCH(""Nom"",db!$A$1:$J$1,0),FALSE),""Pas trouvé"")"

Si la formule doit être répétée sur plusieurs cellules (ici dans l'exemple C2:C102)
La ligne d'instruction sera donc

Code:

ActiveSheet.Range("C2:C101").Formula = "=IFERROR(VLOOKUP($B2,db!$A$2:$J$106,MATCH(""Nom"",db!$A$1:$J$1,0),FALSE),""Pas trouvé"")"

Ce qui est expliqué plus haut est un cas simple. On connaît le nombre de lignes et de colonnes de la plage.
Maintenant voici comment je procède si je veux rendre dynamique une formule dont on ne connaît pas la taille de la plage des données

Je place la formule dans une variable ou une constante de type String où l'on placera entre balises les inconnues. (Ici deux inconnues la taille de la plage des données et sa première ligne contenant les étiquettes de colonne.
La procédure
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Sub TestFormula() Dim areaSearch As Range, areaSearch_Label As Range Dim myFormula As String ' Affectation de la variable myFormula avec deux balises <TableauRecherche> et <Label> myFormula = "=IFERROR(VLOOKUP($B2,<TableauRecherche>,MATCH(""Nom"",<Label>,0),FALSE),""Pas trouvé"")" With ThisWorkbook Set areaSearch = .Worksheets("db").Range("A1").CurrentRegion ' Equivalent de Ctrl+"A" Set areaSearch_Label = areaSearch.Resize(1) ' Ligne des étiquettes de colonnes End With ' Remplacement de la première balise par l'adresse complète de la plage des données myFormula = Replace(myFormula, "<TableauRecherche>", areaSearch.Address(external:=True)) ' Remplacement de la deuxième balise par l'adresse complète de la plage des étiquettes de colonnes myFormula = Replace(myFormula, "<Label>", areaSearch_Label.Address(external:=True)) ' ' Ce qui donnera la chaîne suivante : =IFERROR(VLOOKUP($B2,[DataBase1]db!$A$1:$J$106,MATCH("Nom",[DataBase1]db!$A$1:$J$1,0),FALSE),"Pas trouvé") ' ActiveSheet.Range("C2:C101").Formula = myFormula ' Ecriture de la formule sur la plage C2:C101 End Sub
Il est évident que ActiveSheet est utilisé juste pour l'exemple et qu'il faut donner le vrai nom de la feuille ainsi que le classeur à auquel elle est rattachée et idéalement son CodeName

Exemple de la fonction Replace

Code:

MsgBox Replace("Je m'appelle <Prenom>", "<Prenom>", "Philippe")

donnera Je m'appelle Philippe

25/11/2014, 22h44
Henri1830

J'ai fait quelques tests avant d'aller coucher; l'enfant se présente très très bien. :D
Déjà tous mes remerciements, mais je reviendrai certainement avec des demandes de précision.
Bonne nuit !

Henri
04/12/2014, 09h16
Henri1830

Reprise après interruption

Bonjour Philippe,

Je n'avais pas abandonné le développement, j'ai été interrompu par les besoins d'une offre et ensuite la mise en route de la commande qui s'en est suivi, un petit réseau de 50 postes; ça fait toujours plaisir. ;)

Encore mille mercis pour tout le temps que vous avez consacré à m'aider, si je peux vous rendre la pareille un jour, n'hésitez pas.
Ma société (Trade and Training à Bruxelles www.tat.be) a pour domaine de compétences essentiel WinBooks ainsi que l'infrastructure réseau.
Je m'essaie à VBA et j'arrive à certains résultats encourageants.

Je rencontre encore une petite difficulté (avant la suivante :) ) pour laquelle j'ai ouvert une autre discussion. Il n'est indiqué nulle part que vous êtes mon professeur attitré et gratuit, je ne veux pas vous déranger à chaque souci.
Ceci dit, si le coeur vous en dit, je ne refuse certainement pas votre aide.

Je vous souhaite une bonne journée et à bientôt, j'espère.

Henri
04/12/2014, 09h51
Invité

Bonjour,
tu créé ta formule dans excel, tu te place sur la cellule, tu vas dans vba (raccourci clavier [Alt] + [F11]), tu ouvre la fenêtre d'exécution (raccourci clavier [CTRL] + [G]), tu écrits ceci ?ActiveCell.FormulaR1C1 et tu valide avec la touche [Enter]

Code:

ActiveCell.FormulaR1C1= "=IFERROR(VLOOKUP(RC2,db!R2C1:R106C10,MATCH(" & chr(34) & "Nom" & chr(34) & ",db!R1C1:R1C10,0),FALSE)," & chr(34) & "Pas trouvé" & chr(34) & ")"