Erreur critique N°28 suite à un code VBA

**Pierre8585** · 07/01/2016, 15h19

Bonjour et meilleur vœux à tous,

Je cherche de l'aide suite à un problème sur un tableur excel 2013 en VBA, je m'explique.

Il y a quelques temps j'ai réalisé avec l'aide de certaines personnes présente sur ce forum et je vous en remercie, un tableau pour une année qui me permet de suivre des interventions de techniciens ainsi que de faire des stats.

Le principe:

Un atelier a besoin d'une intervention, le responsable envois une demande à l'atelier principal qui lui rentre dans un tableau des informations client afin de prévoir l'intervention.

Ce tableau est en VBA pour des questions pratiques, cependant je souhaite réaliser des statistiques d'interventions et là j'ai un problème.

Le problème:

Lorsqu'une intervention est mise dans le tableau celui-ci indique automatiquement la date d'entrée. Mais le code qui me permet de détecter à quel mois elle a été entrée plante. J'ai l'erreur 28 espace pile insuffisant qui apparait puis l'erreur d'exécution '-2147417848 (80010108)' qui suit avec un redémarrage d'excel.

Je sais déjà que le problème viens de mon code de recherche de date car une fois isolé il n'y a plus de problème mais je ne comprends pas pourquoi.

Voilà le code en cause

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
    'AJOUT DE VALEUR "1" DANS LE MOIS SI AJOUT DE BASE (COLONNE: AE à AP)
        Set Plgstatdate1 = Range("L" & Target.Row)              'colonne date de début
        Set janvier = Range("AE" & Target.Row)
        Set fevrier = Range("AF" & Target.Row)
        Set mars = Range("AG" & Target.Row)
        Set avril = Range("AH" & Target.Row)
        Set mai = Range("AI" & Target.Row)
        Set juin = Range("AJ" & Target.Row)
        Set juillet = Range("AK" & Target.Row)
        Set aout = Range("AL" & Target.Row)
        Set septembre = Range("AM" & Target.Row)
        Set octobre = Range("AN" & Target.Row)
        Set novembre = Range("AO" & Target.Row)
        Set decembre = Range("AP" & Target.Row)
 
            If (Plgstatdate1 >= DateValue("1/1/2016")) * (Plgstatdate1 <= DateValue("31/1/2016")) Then
                janvier.Value = 1
            End If

Pouvez-vous m'aider ?

**illight** · 07/01/2016, 17h14

Bonjour,

Code :

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

If (Plgstatdate1 >= DateValue("1/1/2016")) * (Plgstatdate1 <= DateValue("31/1/2016")) Then

Que vient faire cette étoile au milieu ? C'est pas un "AND" que tu devrais mettre plutôt ?

As-tu essayé de mettre un point d'arrêt au tout début de ton code, pour effectuer ensuite du pas à pas et voir exactement à quelle ligne ça coince ?

**Pierre8585** · 08/01/2016, 08h38

Bonjour,

Alors oui l'étoile était un "And" car pour faire cette expression j'ai fait un enregistrement de macro que j'ai un peu modifié lol mais j'ai changé cette ligne par la ligne suivante mais sans résultats

Code :

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

If Plgstatdate1 >= DateValue("1/1/2016") And Plgstatdate1 <= DateValue("31/1/2016") Then

Avec le point d'arrêt j'ai remarqué que la date lu dans la plage "Plgstatdate1" était "08/01/2016" et non 8/1/2016. J'ai donc ajouté des "0" sur le format de date pour donner cela

Code :

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

If Plgstatdate1 >= DateValue("01/01/2016") And Plgstatdate1 <= DateValue("31/01/2016") Then

Mais toujours sans résultats

En revoyant les lignes suite au point d'arrêt, je n'ai pas vu d'erreurs mise à part qu'il m'indique des cases vide lorsque je mets le pointeur de la souris sur

Code :

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

Set Plage = Range ("J:J")

et sur

Code :

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

Set Plgbase = Range ("J:J")

alors qu'il y a une des conditions parmi "AN;HE;CH;VE..." dedans

J'ai également remarqué que si je met une MsgBox à la place de

Code :

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

Janvier.Value =1

il n'y a plus de message d'erreur mais en revanche le message apparait 3 fois et les autres fonction s'applique après avoir validé la 3ème MsgBox

**illight** · 08/01/2016, 10h37

Comment sont définies ta variable Plgstatdate1 ? Est-ce une date ?

je te conseille de rajouter aussi un DateValue sur cette variable, histoire d'être sur que les 2 champs que tu compares sont bien des dates

**Pierre8585** · 08/01/2016, 11h13

oui il s'agit d'une date en colonne "J".

Code :

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

Set Plgstatdate1 = Range("L" & Target.Row)

Enfaite chacune des interventions sont ajouté les unes en dessous des autres dans un tableau et en colonne "J" j'ai la date d'ajout de l'intervention qui se met automatiquement, et le but de ce code c'est qu'il lise à quel mois a été ajouté l'intervention x pour ajouter la valeur "1" plus loin sur la même ligne dans une colonne défini en fonction du mois afin de faire des stats.

Tu me recommande donc d'écrire cela ?

Code :

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

If DateValue (Plgstatdate1) >= DateValue("01/01/2016") And DateValue(Plgstatdate1) <= DateValue("31/01/2016") Then

Car avec cela j'ai toujours l'erreur 23 mais cette fois je peux faire le débogage et il m'indique mon second code erreur d'exécution '-2147417848 (80010108)' la méthode 'value' de l'objet 'range' a échoué en me montrant le code suivant

Code :

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

Set Plage = Range("J:J")

Si je fais débogage, le message revient sur la même ligne et si je fais fin le tableau se relance

**antonysansh** · 08/01/2016, 11h22

Une idée au passage :
remplace

Code :

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

DateValue("31/01/2016")

par

Code :

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

DateValue("01/31/2016")

On utilise jj/mm/aaaa mais en VB je crois qu'il faut utiliser mm/dd/yyyy ça pourrait expliquer l'erreur car le 31ieme n'existe pas.

PS :

Envoyé par Pierre8585

Tu me recommande donc d'écrire cela ?

Code :

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

If DateValue (Plgstatdate1) >= DateValue("01/01/2016") And DateValue(Plgstatdate1) <= DateValue("31/01/2016") Then

Car avec cela j'ai toujours l'erreur 23 mais cette fois je peux faire le débogage et il m'indique mon second code erreur d'exécution '-2147417848 (80010108)' la méthode 'value' de l'objet 'range' a échoué en me montrant le code suivant

Code :

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

Set Plage = Range("J:J")

Si je fais débogage, le message revient sur la même ligne et si je fais fin le tableau se relance

Si la variable Plgstatdate1 est bien une date au sens d'Excel pas besoin de DateValue par contre si c'est un string qui représente une date alors oui il faut mettre DateValue(Plgstatdate1) mais surtout pas ~~DateValue("Plgstatdate1")~~

**Pierre8585** · 08/01/2016, 11h29

Bonjour à toi antonysansh

J'ai fait le test mais pareil, j'ai mon erreur 23 suivie de la -2147417848 (80010108)' la méthode 'value' de l'objet 'range' a échoué en permanence sur la ligne suivante

Code :

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

Set Plage = Range("J:J")

C'est possible que le problème vienne de la ligne du dessous ?

Code :

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

janvier.Value = 1

car lorsque je met

Code :

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

MsgBox "test ok"

cela fonctionne mais la MsgBox s'affiche 3 fois par contre

**antonysansh** · 08/01/2016, 11h31

Comme as tu declaré la variable plage ?
Car un erreur sur Set Plage = Range("J:J") c'est étrange.

Je vois Target dans le code, c'est sur quel evenement ?

Pense a les désactiver sinon ça peut tourner en boucle.

**Pierre8585** · 08/01/2016, 11h37

Pardon pour le 3ème message, erreur de ma part...

pour ce qui est de la date elle est ajouter automatiquement avec le code suivant

Code :

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

1
2
If Target.Column = 10 Then If Not (IsEmpty(Target.Value)) Then Range("L" & Target.Row).Value = Date _
            Else: Range("L" & Target.Row).ClearContents

Lorsque je pointe la souris sur "Plgstatdate1" après avoir mis un point d'arrêt, j'ai la valeur suivante qui s'affiche "08/01/2016"

**illight** · 08/01/2016, 11h37

Envoyé par Pierre8585

oui il s'agit d'une date en colonne "J".

Code :

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

Set Plgstatdate1 = Range("L" & Target.Row)

Bizarre cette déclaration d'une date

Plgstatdate1 c'est une plage ou une valeur ? Sic 'est une valeur, tu n'as pas besoin du Set..

Et comme l'a dit plus haut,c 'est bizarre l'erreur de ta déclaration de la variable Plage..

**Pierre8585** · 08/01/2016, 11h43

Dsl antonysansh je ne connais pas trop trop du coup je ne vois pas ce que tu veux dire, tu peux développer stp

Je viens de remplacer

Code :

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

Set Plgstatdate1 = Range("L" & Target.Row)

par

Code :

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

Plgstatdate1 = Range("L:L")

et cette fois je n'est plus l'erreur 23 mais 13 incompatibilité de type qui me montre la ligne suivante

Code :

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

If DateValue (Plgstatdate1) >= DateValue("01/01/2016") And DateValue (Plgstatdate1) <= DateValue("01/31/2016") Then

**antonysansh** · 08/01/2016, 11h45

illight détail ce que je voulais savoir.

un exemple pour illustrer :
Nom : exemple.PNG
Affichages : 195
Taille : 11,6 Ko

Les deux variables de l'exemple sont totalement differentes.

**antonysansh** · 08/01/2016, 11h48

Si Plgstatdate1 n'est pas un Range, Plgstatdate1 = Range("L:L") ne peut pas fonctionner car tu lui donne pleins de cellules. Par contre Plgstatdate1 = Range("L1") oui.

D'ou ma question, comment as tu déclaré ta variable Plgstatdate1 ?

**Pierre8585** · 08/01/2016, 11h49

ok antony

effectivement sur l’ensemble de mon code j'ai un seul "End Sub"...

**antonysansh** · 08/01/2016, 11h55

ok antony

effectivement sur l’ensemble de mon code j'ai un seul "End Sub"...

Ce n’est pas ce que je voulais mettre en évidence mais c'est déjà bien.

Tu as manifestement un problème dans la déclaration de ta variable Plgstatdate1 et ce que tu lui donne à manger.

Avec

Code :

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

Dim Plgstatdate1 As Range

c'est

Code :

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

Set Plgstatdate1 = Range("L" & Target.Row)

Avec

Code :

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

Dim Plgstatdate As Date

c'est

Code :

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

Plgstatdate1 = Range("L" & Target.Row)

si dans la cellule on a bien une date sinon c'est

Code :

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

Plgstatdate1 = DateVal(Range("L" & Target.Row))

**Pierre8585** · 08/01/2016, 12h01

Tu veux dire mon Private Sub ?

à l'heure actuel mon code ressemble à cela

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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
 Private Sub Worksheet_Change(ByVal Target As Range)
 
    'DESACTIVATION DE LA MAJ D'ECRAN A CHAQUE CALCUL => ok
        Application.ScreenUpdating = False
 
    'DATE D'ACQUISITION => ok
        If Target.Column = 10 Then If Not (IsEmpty(Target.Value)) Then Range("L" & Target.Row).Value = Date _
            Else: Range("L" & Target.Row).ClearContents
 
    'DATE FAIT LE... => ok
        If Target.Column = 15 Then If Not (IsEmpty(Target.Value)) Then Range("P" & Target.Row).Value = Date _
            Else: Range("P" & Target.Row).ClearContents
 
    'CHANGEMENT DE COULEUR EN FONCTION DE LA BASE => ok
        Set Plage = Range("J:J")                                            'Case base
        Set Plg = Range("B" & Target.Row & ":P" & Target.Row)               'Selection du tableau
        Set Plgtt = Range("B" & Target.Row & ":BE" & Target.Row)            'Selection de la totalité de la ligne utiliser, stat compris
 
        If Not Application.Intersect(Target, Plage) Is Nothing And Target.Count = 1 Then
            Select Case Target
                Case Is = "AN": Plg.Interior.Color = RGB(72, 198, 5)        'vert
                Case Is = "HE": Plg.Interior.Color = RGB(225, 206, 154)     'Vanille
                Case Is = "CH": Plg.Interior.Color = RGB(212, 115, 212)     'mauve
                Case Is = "VE": Plg.Interior.Color = RGB(84, 249, 141)      'menthe à l'eau
                Case Is = "FO": Plg.Interior.Color = RGB(255, 0, 0)         'rouge
                Case Is = "NA": Plg.Interior.Color = RGB(44, 117, 255)      'bleu électrique
                Case Is = "FG": Plg.Interior.Color = RGB(255, 255, 0)       'jaune
                Case Is = "MZ": Plg.Interior.Color = RGB(231, 62, 1)        'abricot
                Case Is = "ML": Plg.Interior.Color = RGB(24, 194, 230)      'bleu ciel
                Case Is = "CO": Plg.Interior.Color = RGB(240, 130, 200)     'rose foncé
                Case Is = "DA": Plg.Interior.Color = RGB(255, 165, 90)      'orange pale
                Case Else
                    Plg.Interior.Pattern = xlNone                           'suppr de la couleur si plus de base
                    Plgtt.ClearContents                                       'suppr du contenue de la ligne si plus de base
            End Select
        End If
 
    'CHANGEMENT DE COULEUR SI INTERVENTION FAITE (COLONNE "O") + RETOUR A LA 1ère COULEUR SI SUPPR DE "X" => ok
        Set Plage = Range("O:O")
 
        If Not Application.Intersect(Target, Plage) Is Nothing And Target.Count = 1 Then
            Select Case Target
                Case Is = "X": Plg.Interior.Color = RGB(170, 5, 80)         'magenta foncé
                Case Is = "x": Plg.Interior.Color = RGB(170, 5, 80)         'magenta foncé
            End Select
        If Target.Value = "" Then
            Select Case Cells(Target.Row, 10).Value
                Case Is = "AN": Plg.Interior.Color = RGB(72, 198, 5)        'vert
                Case Is = "HE": Plg.Interior.Color = RGB(225, 206, 154)     'Vanille
                Case Is = "CH": Plg.Interior.Color = RGB(212, 115, 212)     'mauve
                Case Is = "VE": Plg.Interior.Color = RGB(84, 249, 141)      'menthe à l'eau
                Case Is = "FO": Plg.Interior.Color = RGB(255, 0, 0)         'rouge
                Case Is = "NA": Plg.Interior.Color = RGB(44, 117, 255)      'bleu électrique
                Case Is = "FG": Plg.Interior.Color = RGB(255, 255, 0)       'jaune
                Case Is = "MZ": Plg.Interior.Color = RGB(231, 62, 1)        'abricot
                Case Is = "ML": Plg.Interior.Color = RGB(24, 194, 230)      'bleu ciel
                Case Is = "CO": Plg.Interior.Color = RGB(240, 130, 200)     'rose foncé
                Case Is = "DA": Plg.Interior.Color = RGB(255, 165, 90)      'orange pale
            End Select
        End If
        End If
 
    'AJOUT DE VALEURS HORS TABLEAU POUR STAT (COLONNE: R à AB) => ok
        Set Plgbase = Range("J:J")
 
        If Not Application.Intersect(Target, Plgbase) Is Nothing And Target.Count = 1 Then
            Select Case Target
                Case Is = "AN": Range("R" & Target.Row).Value = 1   
                Case Is = "HE": Range("S" & Target.Row).Value = 1   
                Case Is = "CH": Range("T" & Target.Row).Value = 1   
                Case Is = "VE": Range("U" & Target.Row).Value = 1   
                Case Is = "FO": Range("V" & Target.Row).Value = 1   
                Case Is = "NA": Range("W" & Target.Row).Value = 1   
                Case Is = "FG": Range("x" & Target.Row).Value = 1   
                Case Is = "MZ": Range("Y" & Target.Row).Value = 1   
                Case Is = "ML": Range("z" & Target.Row).Value = 1   
                Case Is = "CO": Range("AA" & Target.Row).Value = 1  
                Case Is = "DA": Range("AB" & Target.Row).Value = 1  
            End Select
        End If
 
    'AJOUT DE VALEUR "1" SI FAIT & SUPPR DE LA VALEUR "1" SI PLUS DE BASE (COLONNE: AB) & STAT DELAIS => ok
        Set Plgfait = Range("O:O")                          'colonne fait
        Set Plgstatfait = Range("AC" & Target.Row)          'colonne si intervention faite = 1
        Set Plgstatdelais = Range("BE" & Target.Row)        'colonne pour delais1 = 1
        Set Plgdate1 = Range("L" & Target.Row)              'colonne date de début
        Set Plgdate2 = Range("P" & Target.Row)              'colonne date de fin
 
        If Not Application.Intersect(Target, Plgfait) Is Nothing And Target.Count = 1 Then
            Select Case Target
                Case Is = "X": Plgstatfait.Value = 1    'Si "X" dans fait (MAJ)
                Case Is = "x": Plgstatfait.Value = 1    'Si "x" dans fait (minuscule)
                Case Else: Plgstatfait.ClearContents
            End Select
        If Not Application.Intersect(Target, Plgfait) Is Nothing And Target.Count = 1 Then
            Select Case Target
                Case Is = "X": Plgstatdelais.Value = DateDiff("d", Plgdate1, Plgdate2)  ' Ajout du délais
                Case Is = "x": Plgstatdelais.Value = DateDiff("d", Plgdate1, Plgdate2)  ' Ajout du délais
                Case Else: Plgstatdelais.ClearContents
            End Select
        End If
        End If
 
    'AJOUT DE VALEUR "1" DANS LE MOIS SI AJOUT DE BASE (COLONNE: AE à AP)
        Set Plgstatdate1 = Range("L" & Target.Row)              'colonne date de début
        Set janvier = Range("AE" & Target.Row)
        Set fevrier = Range("AF" & Target.Row)
        Set mars = Range("AG" & Target.Row)
        Set avril = Range("AH" & Target.Row)
        Set mai = Range("AI" & Target.Row)
        Set juin = Range("AJ" & Target.Row)
        Set juillet = Range("AK" & Target.Row)
        Set aout = Range("AL" & Target.Row)
        Set septembre = Range("AM" & Target.Row)
        Set octobre = Range("AN" & Target.Row)
        Set novembre = Range("AO" & Target.Row)
        Set decembre = Range("AP" & Target.Row)
 
            If DateValue (Plgstatdate1) >= DateValue("01/01/2016") And DateValue (Plgstatdate1) <= DateValue("01/31/2016") Then
                janvier.Value = 1
                'MsgBox "test ok"
            End If
 
    'AJOUT DE VALEUR "1" DANS LE MOIS SI INTERVENTION FAITE (COLONNE: AR à BC)
 
		'Idem que précédent mais en colonne "AR" à "BC" en fonction de la date en colonne "P"
 
    'COULEUR DES VALEURS HORS TABLEAU (COLONNE: >=Q) => ok
 
        With Range("Q:BE").Font
                '.Color = RGB(255, 255, 255)     'blanc
                .Color = RGB(32, 32, 32)        'noir
        End With
 
End Sub

Je suis preneur de toutes informations, conseils, critiques car ce code à été fait avec des morceau de code de droite et gauche et aussi avec quelques truc écrit par moi mai j'avoue ne pas avoir suivie de cours sur le VBA, chose qui devrais arriver dans l'année...

Par rapport au début de l'écriture du code il a été largement réduit grâce à des conseil et si je peux l'optimiser et/ou réduire grâce à tous je suis preneur ^^

**illight** · 08/01/2016, 13h52

Personnellement, et de mon point de vue, tu te perds par rapport à ce que l'on dit, car tu n'as pas de déclaration de variables au début.

il faudrait déclarer toutes les variables dans ta procédure (ou ton private Sub si tu préfères). Genre, ta variable Plage, c'est un groupe de cellule donc faudrait la déclarer en :

Code :

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

dim Plage as Range

Et cela, pour toutes tes variables utilisées. ça permettrait d'y voir plus clair

**Pierre8585** · 08/01/2016, 14h27

Ok je vois

donc il faut que je déclare mes variables comme ci dessous pour plus de clarté ?

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
 
Dim Plgstatdate1 as Date 
	Plgstatdate1 = Range("L" & Target.Row)
 
Dim mois as Range
        Set janvier = Range("AE" & Target.Row)
        Set fevrier = Range("AF" & Target.Row)
        Set mars = Range("AG" & Target.Row)
        Set avril = Range("AH" & Target.Row)
        Set mai = Range("AI" & Target.Row)
        Set juin = Range("AJ" & Target.Row)
        Set juillet = Range("AK" & Target.Row)
        Set aout = Range("AL" & Target.Row)
        Set septembre = Range("AM" & Target.Row)
        Set octobre = Range("AN" & Target.Row)
        Set novembre = Range("AO" & Target.Row)
        Set decembre = Range("AP" & Target.Row)
 
            	If DateValue (Plgstatdate1) >= DateValue("01/01/2016") And DateValue (Plgstatdate1) <= DateValue("01/31/2016") Then
                	janvier.Value = 1
                	'MsgBox "test ok"
            	End If

**illight** · 08/01/2016, 15h13

Oui, et tes déclaration de variables, tu les met au début de ta procédure, histoire qu'elles soient toutes regroupées au même endroit. Sinon si tu en cherches une et que ton code est long, tu vas mettre des plombes à la retrouver

Par ailleurs, c'est quoi :

Code :

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

Dim mois as Range

t'es sur que ta variable s'appelle "mois" ? c'est pas plutot janvier (ainsi que tous les autres mois) ?

**Pierre8585** · 08/01/2016, 15h23

oui j'ai vu mon erreur après avoir lu des truc dessus...

Donc j'ai mis toute mes variable avant ma procédure en séparant les "range" de la "date" mais j'ai un petit prob de compilation maintenant sur "J:J" du code suivant...

Code :

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