Optimisation d'un code VBA

Bonjour,

J'ai fait un programme VBA permettant d'obtenir la valeur moyenne de 1000 valeurs simulées.
Pour cela je simule 1000 fois une feuille Excel en fonction de différents paramètres pour récupérer la valeur d'une cellule puis je fais la moyenne.

Le souci est que le calcul de beaucoup de pages Excel prend beaucoup de temps, j'ai pu améliorer le code avec des astuces trouvées sur internet mais je n'arrive plus à améliorer le temps d'exécution du programme.
J'ai pensé à faire un programme qui permettrais de ne plus à avoir à passer par les feuilles Excel mais le problème c'est que je risque d'apporter souvent des modifications sur la feuille ce qui obligerai à modifier le code, de cela me prendrait beaucoup de temps car la feuille Excel est assez complexe.

C'est pour ça que je viens vous demander des idées ou des conseils pour améliorer mon code actuel.

Merci beaucoup à tous ce qui pourrait m'aider.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

Public Sub simulation()     Dim nbsim As Integer Dim values(1 To 1000) As Long   nbsim = 1000       Sheets("Ctrl_T120_A58").Activate     Dim debut As Date, temps As Date, fin As Date debut = Time     Application.ScreenUpdating = False     'Simulation des feuilles excel puis récupération de la valeur dans un vecteur   For I = 1 To nbsim   Range("sim").Select ActiveCell.FormulaR1C1 = I ActiveSheet.Calculate   values(I) = Range("noinet").Value   Next     Application.ScreenUpdating = True     'Calcul de la moyenne   Dim Sum As Double Sum = 0   For L = 1 To 1000   Sum = Sum + values(L)   Next L   Range("nbvnet").Value = Sum / 1000     fin = Time temps = fin - debut MsgBox ("C'est fini !" & Chr(10) & "temps de traitement " & temps)     End Sub

---

Bonjour Artention,

Tu peux déjà enlever les select et la boucle sur L :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Public Sub simulation() Application.ScreenUpdating = False Dim nbsim&, v&, i&, debut As Date, temps As Date, fin As Date nbsim = 1000 debut = Time With Sheets("Ctrl_T120_A58") 'Simulation des feuilles excel puis récupération de la valeur dans un vecteur For i = 1 To nbsim .Range("sim") = i .Calculate v = v + .Range("noinet") Next .Range("nbvnet") = v / 1000 End With fin: Application.ScreenUpdating = True fin = Time temps = fin - debut MsgBox ("C'est fini !" & Chr(10) & "temps de traitement " & temps) End Sub

---

Apres je ne sais pas ce que calcul ta feuille, à voir le code ce sont les .Calculate qui sont long (le reste y a pas de raison que ça le soit)
Peut-être peux-tu calculer plus simplement tes résultats via une fonction VB et ensuite vers la moyenne des résultats de la fonction.

Bonjour,

As-tu essayé d'enlever les activate et les select ? Comme par exemple :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

With Sheets("Ctrl_T120_A58") 'Simulation des feuilles excel puis récupération de la valeur dans un vecteur   For I = 1 To nbsim   .Range("sim").FormulaR1C1 = I .Calculate values(I) = .Range("noinet").Value   Next I Application.ScreenUpdating = True   'Calcul de la moyenne   Sum = 0   For L = 1 To 1000 Sum = Sum + values(L) Next L   .Range("nbvnet").Value = Sum / 1000   End With

---

Bonjour,

N'importe comment, les fonctions intégrées d'Excel, sont plus rapides que VBA.

Tu mets tes 1000 nombres sur une colonne et tu utilises la moyenne d'Excel:

En plaçant une formule dans une cellule avec Formula ou FormulaLocal

ou

En utilisant application.worksheetfunction(average(plage))

Ou bien tu te fais un complément COM (une dll) pour Excel avec un Visual Studio pas Express. Cela prend au minimum l'édition gratuite Community (2013 ou 215)

Ou bien, tu te fais un exécutable autonome qui prend ton classeur Excel comme source de données.

Merci Antony et Aziz, vos réponses m'ont permis de gagner quelques précieuses seconde pour l'exécution du programme. :D

Je suis d'accord avec toi Antony, je pense aussi que c'est le calcul de la feuille qui est long. Il y a de nombreuses ligne sur la feuille excel qui sont toutes plus ou moins liées faire une fonction de la feuille me semble vraiment compliqué.
Lors ce qu'on change de simulation il y a plusieurs données qui sont changées, ces données sont cherchées sur d'autres feuilles excel. J'ai essayé d'éliminer au maximum les Recherchev et de les remplacer par des fonctions Index.

j'avoue je que j'ai du mal a comprendre pourquoi tu utilise le sheets pendant le calcul

puisque d'après ce que je vois dans ta boucle tu fait référence qu'a 2 cellules le calcul pourrait il pas se faire dans le code lui même dans une variable tableau

pour 1000 itération le résultat serait quasi immédiat

Code:

---

1 2 3 4 5 6 7

For I = 1 To nbsim .Range("sim").FormulaR1C1 = I .Calculate values(I) = .Range("noinet").Value Next I

---

Code:

---

1 2 3 4 5 6 7

tablo=range( xy;az) For I = 1 To ubound(tablo) values(I) = ""'ici ton calcul avec I qui te renvoie sur noinet avec ta méthode Next I

---

Je suis d'accord Patrick, le plus rapide serait de coder directement une fonction permettant d'obtenir la valeur voulue.

Cependant ma feuille Excel est vraiment compliquée, il y a beaucoup de paramètres qui rentre en jeu dans le calcul de la valeur et beaucoup de colonnes et de lignes (reliées par des formules) sont utilisées, ainsi coder une fonction est difficile, de plus le calcul de la valeur dans la feuille Excel est surement appelé à évoluer ce qui obligerait à recréer de nouvelles fonctions.

Grâce aux différents conseils, j'ai pu améliorer mon code actuel pour le rendre plus rapide mais il est bien possible que maintenant la seul solution, si je veux qu'il soit plus rapide, c'est de coder une fonction.