Discussion :

[saipas]

Bonjour,


j'ai une feuille Excel sur laquelle sont mélangés calculs normaux et appels à une fonction (qui va chercher une valeur dans une BDD Access à partir d'un champ rempli par une manip de l'utilisateur) et j'aimerais optimiser la façon dont la feuille calcule.

En effet, si l'on laisse la feuille en calcul automatique, la fonction de rappatriement depuis la base (près de 150 occurences dans la feuille) est appelée sans arrêt (presque à chaque calcul, 5/6 secondes par recalcul) alors qu'il faudrait qu'elle ne le soit que lorsque la valeur initiale (celle sur laquelle se base la recherche) est modifiée.

C'est pourquoi j'avais mis la feuille en calcul manuel, avec des appels à Range.Calculate un peu partout, insérés dans le Sheet_change().

Là, deux problèmes se posent :
- lorsque la valeur retournée par la fonction de la BDD change, pas d'appel à Sheet_change, donc je ne peux pas baser mes calculate sur ces cellules
- le grand nombre de calculate ralentit considérablement la feuille, et c'est ça le problème principal.

Je recherche donc, si possible, un moyen de calculer la feuille sans trop de ralentissements, et en gardant la même logique d'appel à base Access.


Merci,
@+

[jfontaine]

tu pourrais appeler cette fonction uniquement a la modification d'une certaine cellule en utilisant le worksheet_change
Evidemment ca change ton principe de formule perso

[saipas]

Désolé de pas avoir répondu plus tôt, mon boss me tenait la patte.

En fait ce que tu me proposes, c'est basiquement ce que je fais ; j'ai un combobox dans lequel sont des références d'articles, lorsqu'il est modifié il change la valeur d'une cellule, et c'est la modification de cette cellule qui appelle le Calculate sur la ligne, qui met enfin la fonction à jour.

L'avantage de ta méthode, c'est qu'elle me permettrait de remettre en route le calcul automatique, et que ça aurait certainement de bonnes répercussions sur la vitesse.
Par contre, comme la fonction n'est pas toujours dans la même colonne sur la feuille, et qu'elle n'a pas pour forcément le même type de paramètre (trois paramètres à définir), cela pourrait fortement contrebalancer le gain...

J'essaye rapidement et je te dis ce qu'il en est.

[saipas]

Encore désolé pour le temps, même problème que tout à l'heure + ça m'a fait trouver des trucs que j'avais oublié et que j'ai du modifier...

Quoi qu'il en soit, je pense que c'est tout simplement l'évènement Change qui est lent, car en modifiant ma méthode de calcul de la façon dont tu l'as préconisé, j'obtiens des temps de chargement identiques à avant.
Par contre, dès que je retire le change, c'est magique...

Mais ce n'est pas spécialement étonnant que cela soit lent, vu le code...

Extrait (pour 25% de la feuille)

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
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 
 
If Sh.Name = "Config" Then
    If Not Intersect(Range("G27:G43"), Target) Is Nothing Then
        Sh.Range("C" & Target.Row).Value = getDB(Range(Target.Address), Range("G22"), Range("C22"))
        Sh.Range("E" & Target.Row).Value = getDB(Range(Target.Address), Range("G22"), Range("E22"))
        Sh.Range("F" & Target.Row).Value = getDB(Range(Target.Address), Range("G22"), Range("F22"))
        Sh.Range("P" & Target.Row).Value = getDB(Range(Target.Address), Range("G22"), Range("P22"))
        Sh.Range("T" & Target.Row).Value = getDB(Range(Target.Address), Range("G22"), Range("T22"))
     End If
End If
 
End Sub

C'est soit ces lignes en VBA, soit un =getDB() dans chaque cellule où j'en ai besoin.

[jfontaine]

Quand tu fais une modification dans l'evenement change d'une feuille, le code est passé 2 fois.

1 - quand tu modifies toi meme
2 - quand une cellule est modifiée par le code.

[saipas]

Je viens d'essayer avec un Application.EnableEvents = False en début de SheetChange() pour éviter que la fonction ne soit parcourue deux fois, et c'est kiff-kiff en temps

[asxasx]

Je ne suis pas sûr d'avoir tout compris mais pourquoi ne ramènerais tu pas toute ta base access dans une feuille excel cachée à l'ouverture de ton fichier Workbook_Open().

Perso c'est ce que je fais avec une base Oracle distante et je ramène plus de 22000 lignes de 16 colonnes en moins de 10s donc tu fais ça à l'ouverture et hop.

[saipas]

En fait je travaille sur un fichier qui doit être à la fois capable d'être mis à jour instantanément mais aussi de fonctionner en stand-alone.

La fonction que j'appelle vérifie donc d'abord l'existence d'une feuille dans le classeur contenant la BDD, et si elle n'existe pas fait une requête vers Access.

Je pensais aussi que le calcul serait plus rapide si la feuille était incluse dans le classeur, mais que dalle, c'est aussi long voire même parfois plus...

Il est fort possible qu'il faille repenser toute la logique des calculs pour que le tout soit à peu près rapide, mais je n'ai aucune idée de quoi faire :/

[asxasx]

Ben effectivement si le fait d'avoir les données sur la feuille d'à coté ne change rien, tout le problème vient de tes calculs...

[saipas]

Voici le code pour getDB (j'ai pas encore eu le temps de le commenter à fond, désolé :/) :

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
 
Function getDB(valeur_cherchée As String, vRef As String, vType As String)
 
If Not FeuilleExiste("Articles") Then
    Dim vRange As String, vWorksheet As String, vWorkbook As String, vPath As String, vSource As String
 
    vRange = LitDansFichierIni("BDD", "Range", Application.ActiveWorkbook.Path & "\config.ini")
    vWorkbook = LitDansFichierIni("BDD", "Workbook", Application.ActiveWorkbook.Path & "\config.ini")
    vWorksheet = LitDansFichierIni("BDD", "Worksheet", Application.ActiveWorkbook.Path & "\config.ini")
    vPath = LitDansFichierIni("BDD", "Path", Application.ActiveWorkbook.Path & "\config.ini")
    vSource = vPath & vWorkbook
 
    getDB = recupDB(vSource, vWorksheet, valeur_cherchée, vType, vRef)
 
Else:
    getDB = recupSheet(valeur_cherchée, vRef, vType)
End If
 
End Function
 
'récupère la donnée sous Access
Function recupDB(vSource As String, vTable As String, vValeur As String, vType As String, vRef As String)
 
Dim vBDD As New ADODB.Connection
Dim vDonnées As New ADODB.Recordset
Dim vSQL As String
 
vBDD.Open "provider=microsoft.jet.oledb.4.0;" & "persist security info=false;" & "data source=" & vSource
vSQL = "select * from " & vTable & " where " & vRef & "='" & vValeur & "'"
vDonnées.Open vSQL, vBDD, adOpenDynamic, adLockReadOnly
recupDB = vDonnées(vType)
vBDD.Close
 
End Function
 
'récupère la donnée dans la feuille du classeur
Function recupSheet(vValeur As String, vRef As String, vType As String)
 
Dim vFind
Dim ref As Integer, typ As Integer
 
With Worksheets("Articles")
    Set vFind = .Range("1:1").Find(What:=vRef, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True)
    ref = vFind.Column
    Set vFind = .Range("1:1").Find(What:=vType, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True)
    typ = vFind.Column
 
    Set vFind = .Columns(ref).Find(What:=vValeur, LookIn:=xlValues, MatchCase:=True)
    If Not vFind Is Nothing Then
        recupSheet = .Cells(vFind.Row, typ)
    End If
End With
 
End Function

[saipas]

Bon, j'ai fait deux trois modifs au niveau du code et ça roule un peu mieux.

En fait, dans recupSheet, au lieu de trouver la ligne avec Find puis de renvoyer la valeur correspondante, je fais un simple worksheetfunction.vlookup. Par ailleurs, les appels à la fonction sont limités par le sheetchange, qui modifie un des paramètres de la dite fonction.

Le sheetchange a aussi été allégé, et celui de la feuille et du classeur ont été regroupés.

Et enfin, la feuille a été coupée en deux, donc ça limite encore les calculs ^^


Merci à ceux qui ont participé au sujet