Code VBA qui marche uniquement avec la touche ECHAP?

**swirzel** · 20/03/2018, 16h13

Bonjour à toutes et à tous,

Voila mon problème: j'ai écrit un petit programme sous VBA pour modifier un tableau assez conséquent (plus de 40 000 lignes).

Le programme marche bien si ce n'est que je doit appuyer sur la touche "échap" quelques secondes après l'avoir lancé pour visualiser les modifications que je veux ET pour que Excel arrête de ramer pour rien (je vois le processeur monter en charge petit à petit.. 10%...12%...14%...etc)

C'est comme si j'avais lancé une boucle infinie .. mais en mettant des stops aux endroits clefs pour voir comment se comporte le programme et où ça pouvait bloquer éventuellement, il se trouve que l'endroit faisant tout "capoter" étant juste avant de changer certaines cellules du tableau (les 4 dernières lignes)

Je précise que le script est sur une feuille et non un module, que certaines valeurs d'entrée sont récupérées depuis des userform et que j'ai eu quelques soucis du genre "erreur 400" et "erreur 1004" avant que ça fonctionne (j'ai dû changer cells(x,j) en application.cells(x,j).value)

Voila mon programme ci-dessous:

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
Sub LancementProgramme2()

Dim RefLM As Long, CapaLin As Integer, DateModif As Date, NomPers As String, BoiteDial, i As Long, Nrow As Long, LigneRef As Long, boitedial2

' Récupération des variables renseignées dans les champs de la feuille CAPA

RefLM = TextBox1.Value
CapaLin = TextBox2.Value
DateModif = TextBox3.Value
NomPers = TextBox4.Value


'Comptage du nombre de lignes

Nrow = Range("A65536").End(xlUp).Row

'Vérification si la référence LM est déjà dans le tableau

LigneRef = 999999999

For i = 2 To Nrow
    If Cells(i, 1) = RefLM Then LigneRef = i
Next

If LigneRef = 999999999 Then boitedial2 = MsgBox("La référence que vous avez renseigné n'est pas dans le tableau, voulez-vous créer une nouvelle référence?", vbYesNo, "Attention")
    If boitedial2 = vbYes Then LigneRef = Nrow + 1
    If boitedial2 = vbNo Then Exit Sub

Application.Cells(LigneRef, 1).Value = RefLM
Application.Cells(LigneRef, 2).Value = CapaLin
Application.Cells(LigneRef, 4).Value = DateModif
Application.Cells(LigneRef, 5).Value = NomPers


End Sub

Merci d'avance pour votre aide!!

**Menhir** · 20/03/2018, 16h34

Juste après ton "Nrow = ...", mets la ligne :

Code :

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

1
2
3
Debug.Print Nrow
Debug.Print ActiveSheet.Name
Debug.Print  Range("A65536").Parent.Name

Lance la macro et regarde dans la fenêtre d'exécution le résultat de ces trois instructions.

**swirzel** · 20/03/2018, 16h45

Ça donne:

41606
CAPA
CAPA

41606 est le nombre de lignes du tableau.
CAPA étant le nom de la feuille dans laquelle j'ai le tableau.

**Menhir** · 20/03/2018, 16h57

"Quelques secondes", c'est peut-être normal pour tester une telle quantités de lignes.

Tu n'aurais pas aussi des macro évènementielles, des calculs complexes comme des formules matricielles, des formules sur chacune de tes lignes ?

**swirzel** · 20/03/2018, 19h10

En fait c'est plus que quelques secondes, ça met en charge le processeur sans fin..

Si je fait "echap" quelques secondes après avoir lancé le programme (quand le processeur commence à monter en charge), ça marche..

Et non je n'ai rien de tout ça, justement, c'est ce qui m'étonne! Le plus étonnant, comme je l'ai mis dans mon premier post, c'est que si je mets un "stop" dans mon programme APRÈS avoir cherché la valeur RefLM dans le gros tableau (ce qui est censé prendre le plus de temps), ça ne rame pas. Ca rame au niveau des dernières lignes du programme, quand je renseigne les cases de la ligne correspondante avec:

Code :

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

1
2
3
4
Application.Cells(LigneRef, 1).Value = RefLM
Application.Cells(LigneRef, 2).Value = CapaLin
Application.Cells(LigneRef, 4).Value = DateModif
Application.Cells(LigneRef, 5).Value = NomPers

C'est ce bloc qui fait tout ramer.. c'est ce que je ne comprends pas!

**Marc-L** · 20/03/2018, 20h17

Bonjour !

Déjà dans le code il y a un gros souci de logique au niveau des lignes n°25 à 27
devant toutes être dans le même bloc d'instructions If !

L'exécution de ce code est déjà ralenti par l'utilisation d'une boucle,
à remplacer par exemple par la méthode Range.Find ou encore comme dans la
démonstration suivante par Match correspondant à la fonction de feuille de calculs EQUIV :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub LancementProgramme2()
   Dim RefLM As Long, V
       RefLM = TextBox1.Value
           V = Application.Match(RefLM, ActiveSheet.UsedRange.Columns(1), 0)
If IsError(V) Then
    If MsgBox("La référence que vous avez renseigné n'est pas dans le tableau, voulez-vous créer une nouvelle référence?", vbYesNo, "Attention") = vbNo Then Exit Sub
    V = Cells(Rows.Count, 1).End(xlUp).Row + 1
End If
    Cells(V, 1).Resize(, 2).Value = Array(RefLM, TextBox2.Value)
    Cells(V, 4).Resize(, 2).Value = Array(TextBox3.Value, TextBox4.Value)
End Sub

___________________________________________________________________________________________________________

Merci de cliquer sur

en bas à droite de chaque message ayant aidé puis sur

pour clore cette discussion …

**swirzel** · 21/03/2018, 08h59

Merci pour votre aide!

J'ai encore beaucoup de choses à apprendre en VBA

En effet, c'est surement moins lourd et rudimentaire que mes boucles if !

Cependant, le problème reste le même... je vous ai mis le fichier excel en question en PJ.

**Menhir** · 21/03/2018, 09h21

(j'ai dû changer cells(x,j) en application.cells(x,j).value)

Déjà, ça c'est totalement anormal.
Il faudrait que tu essayes de comprendre pourquoi un simple Cells... = ... provoque une erreur.

Pour savoir si le problème vient de ton code, essaye de mettre un simple Debug.Print "Fin" juste avant ton End Sub et place un stop (point marron) dessus.
Si ton code arrive jusque là rapidement et commence à pédaler après, c'est que la vérité est ailleurs.

**unparia** · 21/03/2018, 09h26

Bonjour
(je n'ouvre en aucu_n cas un classeur tiers et n'ai donc pas ouvert le tien)

Je n'aime vraiment pas ceci :

Code :

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

1
2
3
4
5
6
7
Dim RefLM As Long, CapaLin As Integer, DateModif As Date, NomPers As String, BoiteDial, i As Long, Nrow As Long, LigneRef As Long, boitedial2

' Récupération des variables renseignées dans les champs de la feuille CAPA

RefLM = TextBox1.Value
CapaLin = TextBox2.Value
DateModif = TextBox3.Value

Un contrôle textbox ne contient que des chaînes de caractères et non ni des numériques, ni des dates.
Si leur contenu représente un numérique ou une date, ce contenu est à concertir (utilisation des fonctions de conversion exposées à la rubrique Fonctions de conversion de types de données de ton aide interne VBA)

**swirzel** · 21/03/2018, 09h39

En réponse à Menhir:

En mettant un debug.print avant la fin puis un stop sur le "end sub", ça rame dans la semoule AVANT que j'appuie sur echap et que le "fin" apparaisse dans la fenêtre d'exécution.
Si j'ai bien compris, ça voudrais dire que mon problème est juste au dessus, au lignes:

Code :

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

1
2
 Cells(V, 1).Resize(, 2).Value = Array(RefLM, TextBox2.Value)
    Cells(V, 4).Resize(, 2).Value = Array(TextBox3.Value, TextBox4.Value)

Et en effet, en mettant un debug.print avant ces lignes, pas de problème, il apparait vite..

En réponse à Unparia:

Je comprends tout à fait pourquoi vous ne voulez pas ouvrir ce classeur, pas de problème!

Je vais voir en modifiant les types de données

Merci à tous les deux!

**swirzel** · 21/03/2018, 11h39

En ayant fait les modifications suggérées, ça ne règle pas mon problème..

Il semble que ça vienne de la partie cells(i,j).value=XXX

Je me demande si le fait que ça soit dans une fenêtre et pas un module puisse gêner. Ca doit être lié au fait qu'il n'accepte pas le cells(i,j)=XXX également.

**Menhir** · 21/03/2018, 12h26

Envoyé par swirzel

Il semble que ça vienne de la partie cells(i,j).value=XXX

A part la valeur des variables contenant ligne et colonne, je ne vois vraiment pas ce qui pourrait contrarier cette instruction.
Surtout après avoir vérifié qu'il y a effectivement une feuille active.

Je me demande si le fait que ça soit dans une fenêtre et pas un module puisse gêner.

Je ne comprends pas ce que tu veux dire...

**swirzel** · 21/03/2018, 12h45

Tu as ouvert le fichier excel? est-ce que tu as le même problème?

Jusqu'à présent, je travaillais dans des modules (mon prof de VBA nous le conseillais vivement), sauf que la, comme j'utilise des userform qui sont sur ma feuille active, je suis passé par une feuille et non pas un module.. c'était une idée comme ça

Je ne vois vraiment pas d'où ça peut venir..

J'ai essayé de mettre une instruction pour attendre 5 secondes puis faire "senkeys {echap} pour simuler ce que je fait manuellement mais ça ne marche quand même pas (même si je ne trouve pas l'origine du problème de "charge" inexpliquée du processeur, mon programme fonctionne malgré tout!)

**Menhir** · 21/03/2018, 14h23

Envoyé par swirzel

Tu as ouvert le fichier excel?

Non.
Lis ceci : https://www.developpez.net/forums/d8...s-discussions/

sauf que la, comme j'utilise des userform qui sont sur ma feuille active, je suis passé par une feuille et non pas un module.. c'était une idée comme ça

Un UserForm ne peut pas être dans une feuille. Il a son propre module.

J'ai essayé de mettre une instruction pour attendre 5 secondes puis faire "senkeys {echap} pour simuler ce que je fait manuellement mais ça ne marche quand même pas

Ca ne peut pas fonctionner : soit ton Sendkey est avant que le code pédale et dans ce cas, tes instructions ne sont pas exécutées, soit il est après et dans ce cas il ne sert à rien puisque le code ne l'atteint pas.

**swirzel** · 21/03/2018, 15h06

Comme je l'ai dit précédemment, je comprends tout à fait le risque que ça représente d'ouvrir un fichier excel+macro du premier venu

pas de soucis!

En effet, ça ne peut pas fonctionner.. le code pédale juste avant! :/

**joe.levrai** · 21/03/2018, 16h36

Bonjour,

Il est rare que j'ouvre moi-même un classeur ... mais une proposition de Marc-L qui ne résolve pas la problématique, ça m'a interpelé

Donc, à quoi te sert la colonne C, qui contient sur 40 000 lignes des NB.SI(A:A;A1) ?
C'est ça qui te plombe l'exécution, le temps de recalcul des 40 000 NB.SI

si tu fais sauter les formules, tu verras que c'est instantané
Il n'y a pas de solution miracle, même en neutralisant le rafraichissement/les évènements/le recalcul automatique .... à la fin de la procédure tu vas de nouveau subir le recalcul extrêmement lourd de toutes ces formules qui ne me semblent pas du tout nécessaires ... au pire fait un TCD qui te présentera sans aucune formule les NB de chaque Ref LM

**Menhir** · 21/03/2018, 17h10

Envoyé par joe.levrai

Donc, à quoi te sert la colonne C, qui contient sur 40 000 lignes des NB.SI(A:A;A1) ?
C'est ça qui te plombe l'exécution, le temps de recalcul des 40 000 NB.SI

Envoyé par swirzel

Envoyé par Menhir

Tu n'aurais pas aussi des macro évènementielles, des calculs complexes comme des formules matricielles, des formules sur chacune de tes lignes ?

Et non je n'ai rien de tout ça

Je crois que ça me met un peu les nerfs de chercher pour rien quand le demandeur ne prend pas la peine de faire les vérifications qu'on propose...

**swirzel** · 22/03/2018, 08h21

Encore une fois, je vous remercie de bien vouloir m'aider, et je prends bien compte de vos remarques!

Le code que j'utilise est celui qui a été proposé un peu plus haut dans la discussion:

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
Sub LancementProgramme2()
 
Dim RefLM As String, CapaLin As String, DateModif As String, NomPers As String, BoiteDial, i As Long, Nrow As Long, LigneRef As Long, boitedial2
 
       RefLM = TextBox1.Value
           V = Application.Match(RefLM, ActiveSheet.UsedRange.Columns(1), 0)
If IsError(V) Then
    If MsgBox("La référence que vous avez renseigné n'est pas dans le tableau, voulez-vous créer une nouvelle référence?", vbYesNo, "Attention") = vbNo Then Exit Sub
    V = Cells(Rows.Count, 1).End(xlUp).Row + 1
End If
Debug.Print "fin"
    Cells(V, 1).Resize(, 2).Value = Array(RefLM, TextBox2.Value)
    Cells(V, 4).Resize(, 2).Value = Array(TextBox3.Value, TextBox4.Value)
Debug.Print "fin2"
 
End Sub

Comme vous pouvez le voir, il n'y a plus de boucle "if" pour plomber le truc... pourtant le problème persiste..

**Menhir** · 22/03/2018, 08h49

Envoyé par swirzel

je prends bien compte de vos remarques!

On dirait pas...

Comme vous pouvez le voir, il n'y a plus de boucle "if" pour plomber le truc... pourtant le problème persiste..

Etant donné que tu ne sembles toujours pas lire les messages, le problème n'est pas prêt de se régler...

**swirzel** · 22/03/2018, 10h37

Je vous assure que je ne mets pas de la mauvaise volonté!

1- Non, il n'y a pas de formules dans mon tableau, ce ne sont que des données qui étaient renseignées jusqu'ici à la main

2- La colonne C est invariable, elle pourrait ne pas être la, ça serait pareil. Le programme ne la fait d'ailleurs pas intervenir, elle n'est ni modifiée, ni "questionnée" par le programme.

Je me répète mais la partie qui pose problème dans ce programme c'est quand il s'agit de modifier les cases des colonnes A, B, D et E. Je ne comprends pas pourquoi ça met autant de temps alors que l'opération semble toute simple. D'autant plus que ça fonctionne sans problème si je stoppe le programme. Si je le laisse tourner, ça ne fait que "réfléchir" à rien.
Voila un exemple de ce qu'il y a dans le tableau:

65976862 | 3 | 1 |13/03/2018 | flora
65976876 | 5 | 1 |20/03/2018 | florian

Code VBA qui marche uniquement avec la touche ECHAP? [XL-2016]

Macros et VBA Excel

Discussions similaires

Partager

Partager