Discussion :

[Artention]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[antonysansh]

Bonjour Artention,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[aziz1015]

Bonjour,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[clementmarcotte]

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.

[Artention]

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

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.

[patricktoulon]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Artention]

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.