transformation des formules excel en macro VB

[jebbayou]

Bonjour à tous,

Voila je vous expose mon problème.
J'utilise depuis un bon moment des formules excel dans le cadre de mon boulot et afin de gagner un peu de temps j'ai pensé a convertir ces formules en code vb.
les formules sont utilisées dans la même feuil (Donnees)

j'utilise 2 feuils ( donnees + gesica):
il faut inserter en meme temps les colonnes necessaires.

=SI(ESTERR(CNUM(G2))=VRAI;CONCATENER(G2;H2);CONCATENER(CNUM(G2);H2))

=SI(AB2="sogetrel";SI(NB.SI(Gesica!A:A;'Donnees FT'!B2)>0;RECHERCHEV(B2;Gesica!A:AM;39;FAUX);"Pas de CRI");AB2)

=SI(AB2="sogetrel";SI(NB.SI(Gesica!A:A;'Donnees FT'!B2)>0;RECHERCHEV(B2;Gesica!A:AL;38;FAUX);"Pas de CRI");AB2)

=SI(I2="non comptab. prod.";C2;SI(I2="post-prod";E1;""))

=SI(H2="non comptab. prod.";C2;SI(H2="post-prod";E1;""))

une explication de ces codes s'impose:
- la 1ere formule concatène un numéro de téléphone avec une date et heure.
- la deuxième formule compare la concaténation entre 2 feuils et renvoi le résultat de la colonne 39 si c vrai.
-la 3eme formule --> même fonction que la 2eme et renvoi la valeur de la colonne 38.

Voila j'attends de vos nouvelles car je seche

Merci d'avance.

 

[Pierrot93]

Re : transformation des formules excel en macro VB

Bonjour,

mets peut être un petit fichier, avec ce que tu as et ce que tu veux... certainement plus facile pour t'aider.....

bonne journée
@+

 

[JNP]

Re : transformation des formules excel en macro VB

Bonjour Jebbayou ,
C'est pas très clair ...
Que veux-tu faire avec VBA, remplir automatiquement les cellules avec les formules (à ce moment-là, il nous manque les références pour savoir où les mettre, et un petit fichier test pour ne pas recommencer plusieurs fois), ou créer des fonctions VBA pour remplacer tes formules (déconseillé car beaucoup plus lent pour les calculs) ?
En attendant, tu peux déjà alléger tes formules ...

=SI(ESTERR(CNUM(G2));G2&H2;CNUM(G2)&H2)

pour la première, par exemple
Bonne journée
Ajout : Salut Pierrot

 

[Jacou]

Re : transformation des formules excel en macro VB

Bonjour Jebbayou, bonjour le forum,

Je ne comprends pas bien ton souci : pourquoi faire un code VB si tu obtiens les résultats que tu attends avec tes formules ?
Une Macro n'a d'intérêt que pour faire exécuter automatiquement des tâches répétitives ou pour faire des affichages avec des userforms.

Jacou

 

[JNP]

Re : transformation des formules excel en macro VB

Re ,
La deuxième devrait pouvoir être écrite comme ceci

=SI(AB2="sogetrel";SIERREUR(INDEX(Gesica!AM:AM;EQUIV(B2;Gesica!A:A;0));"Pas de CRI");AB2)

A +

 

[Misange]

Re : transformation des formules excel en macro VB

Bonjour

Tu peux convertir tes formules facilement en VB en lançant l'enregistreur de macros pour te donner le début. Ca n'a en fait d'intérêt par rapport au fait d'utiliser les formules de la feuille que si tu effectues les calculs par macro et que tu places les résultats de ces calculs dans la feuille. On gagne ainsi énormément sur des grosses feuilles pleines de sommeprod et autres formules gourmandes en ressources.

Bref comme tout le monde (bonjour tout le monde !) l'a souligné, précise dans quel but tu veux faire les choses.

 

[Hicsygrek]

Re : transformation des formules excel en macro VB

Bonjour à tous,

en utilisant FormulaLocal tu n'as pas besoin de modifier tes formules.
exemple : Sheets(Feuil1).Range("A1").FormulaLocal = "Ta Formule"

En espérant que ça puisse t'aider ...
A+

 

[jebbayou]

Re : transformation des formules excel en macro VB

Bonjour et merci a tous pour vos réponses,

Alors je vous explique exactement ce que je veux.
Je veux que le résultat des formules soit donnés par une macro VB afin de gagner du temps.
En effet, les formules sont dans un fichier et ne change jamais par contre ce qui change toute les semaine c'est les données a recalculer et la ça prend beaucoup de temps a chaque fois.
Donc je souhaiterai une macro que me génère le même résultat dans les même cellules.
les formules sont dans les cellules de B a F avec un nombre de ligne variable.

Hicsygrek, ta proposition m’intéresse par contre comment faire pour remplir toutes les lignes (une boucle avec variable???)

En pj un exemple de fichier

Merci d'avance.

 

[Hicsygrek]

Re : transformation des formules excel en macro VB

Re tout l'monde ...

Pour remplir toutes les lignes, une ptite modification de formule + Autofill ...
Mais là sincèrement je ne saisi pas quelles données (dans ton fichier) on doit calculer automatiquement étant donné qu'il y a beaucoup d'infos entrées manuellement et que les formules tu peux également les tirer manuellement ...
Plus de précision peut être ??

A+

 

[Etienne2323]

Re : transformation des formules excel en macro VB

Salut Hic, jebba, les maîtres,
juste à titre informatif comme ça. La solution que propose Hic ne réglera en rien le problème selon moi... À mon sens, si on ne fait qu'ajouter des formules en passant par un

.Formula = ...

ou

.FormulaLocal = ...

, l'opération effectuée est simplement de déposer une formule dans la cellule qui doit ensuite être calculée par Excel. Au net, le résultat est simplement une perte de temps qui provient du temps nécessaire à VBA pour mettre les formules dans la feuille de calcul. On revient donc à la case départ mais en encore plus lent.

Ensuite, un Autofill est l'équivalent de glisser une formule. Encore une fois, cela ne représente qu'une perte de temps puisqu'Excel devra se claquer un méga recalcul qui prendra lui aussi une éternité. Cela représenterait, encore une fois, simplement une opération de plus à un calcul déjà long.

Bref, si c'était juste de moi, j'éviterait complètement une piste de solution comme celle-là.

Je regarderais beaucoup plus du côté de pistes de solutions comme celles-ci :
1) possiblité d'enlever le calcul automatique pendant l'exécution pour le mettre en mode manuel et le remettre par la suite.
2) Intégrer un

Application.Screenupdating = False

en début de code si on passe par VBA
3) Améliorer la structure des formules comme le propose JNP pour augmenter la rapidité de calcul et limiter le poids.

Sinon, l'option de passer le tout par VBA et faire sauter toutes les formules serait aussi envisageable. Des variables tableaux dynamique par exemple dans lequel on pourrait stocker les résultats (roule 30 fois plus vite que les rechechev ...)

Bref, à te relire pour plus,

Étienne

 

[jebbayou]

Re : transformation des formules excel en macro VB

Bonjour HIC et ETIENNE et merci pour vos réponses,

En fait j'aurai préférer passer toutes les formules en code avec un bouton pour exécuter le tout.
Etienne, je ne comprend pas ta proposition sur le calcul automatique????
Si quelqu'un peut me proposer une solution VB sa serai sympa et me sauverai la vie.

A bientôt et merci d'avance

 

[JNP]

Re : transformation des formules excel en macro VB

Re ,
Si tu ne lis pas vraiment les réponses proposées, ça sert à rien que Ducroc se décarcasse ...
Tu postes un fichier sans même avoir uilisé les simplifications que je t'ai proposé (et qui fonctionnent sur ton fichier test) ...

En fait j'aurai préférer passer toutes les formules en code avec un bouton pour exécuter le tout.

Si tu as la traduction, je suis preneur, mais dans l'état, ça ne signifie rien du tout ...
Bonne soirée

 

[jebbayou]

Re : transformation des formules excel en macro VB

Bonsoir JPN,

j'ai bien lu les réponses proposées et je vous remercie tous.
mais moi ce qui m’intéresse c'est de savoir s’il y a une traduction bête et méchante des formules excel en VB.
J'ai testé les modification que tu m'a proposées afin de simplifier les formule mais malheureusement ça ne change pas mon problème (temps de calcul toujours trop lent) il faut savoir que j'utilise 2 feuils qui font entre 5000 et 6000 ligne et a chaque fois que j'ouvre le fichier il recalcule le tout, c'est pour ça que je cherche a me débarrasser des formules.

Voila bonne soirée à tous

 

[kjin]

Re : transformation des formules excel en macro VB

Bonsoir,

...et a chaque fois que j'ouvre le fichier il recalcule le tout

Dans le module de ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
End Sub

Un appui sur F9 provoquera le recalcul de la feuille
A+
kjin

 

[JNP]

Re : transformation des formules excel en macro VB

Re ,

mais moi ce qui m’intéresse c'est de savoir s’il y a une traduction bête et méchante des formules excel en VB.

Si tu entends par là la possibilité d'avoir le résultat figé dans les cellules à la place des formules, sans même passer par VBA, tu peux tout simplement faire un "Copier/Collage spécial : Valeurs" des colonnes concernées, mais évidemment, toutes tes formules vont disparaître ... La solution de Kjin est aussi une bonne possibilité
Si c'est pouvoir demander à VBA de calculer chaque cellule en intégrant les formules dans VBA, c'est largement faisable, MAIS ce ne sera pas un gain de temps, loin de là ...
Après, reste à savoir ce que tu fait sur ton fichier ensuite ... Si tu le modifies, à ce moment là, il peut être intéressant de declencher par VBA juste le calcul de la ligne modifiée
Bon courage