Discussion :

[dj_benz]

Bonjour,

J'utilise VBA pour recopier des cellules d'un listing à un autre, et j'y ajoute des formules via la fonction:

Code :

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

feuille.Cells(row, line).FormulaLocal = maFormule

Ceci fonctionne bien, mais c'est assez lent. Le listing comporte environ 20 colonnes, dont 10 formules (recherchev essentiellement), sur 750 lignes. Ca ne traite même pas 5 lignes par seconde (sur un i7 avec 6Go de RAM et un SSD PCI-E).

Comme lu à beaucoup d'endroits, j'ai essayé d'ajouter en début de macro:

Code :

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

Application.Calculation = xlCalculationManual

Pourtant, je vois bien lors de l'exécution que mes cellules de formules se remplissent, et que donc la recherche se fait effectivement à la volée.
L'utilisation de .FormulaLocal (ou .Formula) semble réactiver le calcul auto.

Comment puis-je bloquer définitivement le calcul automatique?

Merci d'avance.

[Invité]

Et si on plus du calcul manuel tu ajoutes ?

Code :

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

Application.ScreenUpdating = False

[NVCfrm]

bonjour,
une lenteur pareille doit dépendre du calcul demandé par ta formule.

[Ashireon]

HEllo,
La macro complete stp Ca aiderai à comprendre.

Merci

[dj_benz]

Bonjour et merci pour vos réponses.

@vcottineau: J'ai essayé de désactiver le ScreenUpdating, mais le temps est identique (et en plus, on a l'impression que c'est planté parce qu'on ne voit pas les cases se remplir).

@NVCfrm: Effectivement, la lenteur vient des formules que j'insère dans mes cases, c'est pourquoi je voudrais qu'Excel colle directement mes formules sans les calculer, et qu'il ne calcule le tout qu'une seule fois, à la fin du traitement.

@Ashireon: Je veux bien copier ici ma macro complète, mais elle fait 600 lignes. Voici donc une version simplifiée, de ce qui pose problème.

Le but de cette macro est de:
1- recopier des cases du listing1 vers listing2
2- créer des formules dans listing2
Les réglages se font dans une feuille de config (wsThis) d'où est lancée la macro.
Les cases à recopier, ou les formules à créer, sont paramétrées dans wsThis (le choix entre recopie ou formule dépend de la longueur du texte: "C" ou "RECHERCHEV...", les formules sont données sans le '=', le caractère '*' permet d'indiquer la ligne courante dans les formules)

Exemple de ce qu'on peut trouver dans une case de config:
C
K
SI(A*=B*;"OK";"NOK")
RECHERCHEV(D*;'[fichier_externe.xlsx]Feuille1'!$A:$C;3;0)

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
 
'[...]
Dim wsThis As Excel.Worksheet
Dim listing1 As Excel.Workbook
Dim feuille1 As Excel.Worksheet
Dim listing2 As Excel.Workbook
Dim feuille2 As Excel.Worksheet
 
Dim firstRow As Integer
Dim lastRow As Integer
Dim currentRow as Integer
Dim fts_first as Integer
Dim fts_last as Integer
Dim fts_cur as Integer
 
' PARAMETRES MACRO    
Set wsThis = ActiveWorkbook.ActiveSheet
Set listing1 = Excel.Application.Workbooks.Open(wsThis.Range("B11").Value, , True) 'readonly
Set feuille1 = listing1.Sheets(wsThis.Range("B12").Value)
Set listing2 = Excel.Application.Workbooks.Open(wsThis.Range("B14").Value, , True) 'readonly
Set feuille2 = listing2.Sheets(wsThis.Range("B15").Value)
firstRow = wsThis.Range("B24")
lastRow = wsThis.Range("B25")
'reglage des colonnes a recopier
fts_first = 26 'premiere ligne
fts_last = 50 'derniere ligne
 
'[...]
Application.Calculation = xlCalculationManual
 
For currentRow = firstRow To lastRow
	'[...]
		' parcours des colonnes demandees
		For fts_cur = fts_first To fts_last
			If (Len(wsThis.Range("B" & fts_cur).Value) <= 2) Then
				' Valeur
				feuille2.Cells(currentRow, fts_cur - fts_first + 1).Value = feuille1.Range(wsThis.Range("B" & fts_cur).Value & currentRow).Value
			Else
				' Formule
				feuille2.Cells(currentRow, fts_cur - fts_first + 1).FormulaLocal = "=" & Replace(wsThis.Range("B" & fts_cur).Value, "*", currentRow)
			End If
		Next
	End If
Next
 
feuille2.Calculate
listing2.Save    
Application.Calculation = xlCalculationAutomatic
listing1.Close
listing2.Close

Ce code est là pour illustrer, mais je maintiens que mon problème vient de l'activation automatique de xlCalculationAutomatic lors de l'utilisation de .FormulaLocal.

Autre piste: peut-on écrire les formules uniquement dans la première ligne, puis les dupliquer à la fin du traitement (l'équivalent d'un double-clic sur le petit carré en bas à droite de la cellule)? Si quelqu'un sait faire ça, je suis preneur!

Merci.

[dj_benz]

Yes!!! C'était la solution.

J'ai donc écrit les formules uniquement dans la première ligne, et lancé un Autofill lors du passage sur la dernière ligne. Puis, un seul Calculate à la fin du traitement, et je tombe mon temps de traitement de 5mn à 35s.

Voici le bout de code modifié:

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
 
'[...]
Application.Calculation = xlCalculationManual
 
For currentRow = firstRow To lastRow
	'[...]
		' parcours des colonnes demandees
		For fts_cur = fts_first To fts_last
			If (Len(wsThis.Range("B" & fts_cur).Value) <= 2) Then
				' Valeur
				feuille2.Cells(currentRow, fts_cur - fts_first + 1).Value = feuille1.Range(wsThis.Range("B" & fts_cur).Value & currentRow).Value
			Else
				' Formule
				' AMELIORATION: ecriture formule uniquement 1ere ligne, puis AUTOFILL à la dernière ligne
				If (currentRow = firstRow) Then
					feuille2.Cells(currentRow, fts_cur - fts_first + 1).FormulaLocal = "=" & Replace(wsThis.range("B" & fts_cur).Value, "*", currentRow)
				ElseIf (currentRow = lastRow) Then
					feuille2.Cells(firstRow, fts_cur - fts_first + 1).Select
					Selection.AutoFill _
					Destination:= _
					feuille2.range( _
						feuille2.Cells(firstRow, fts_cur - fts_first + 1), _
						feuille2.Cells(lastRow, fts_cur - fts_first + 1) _
					)
				End If
			End If
		Next
Next
 
feuille2.Calculate

Merci pour votre aide.