Discussion :

[max160]

Problème résolu

Bonjour,

J'ai réussi à identifier ce qui posait problème : la méthode suivante utilise la méthode .Find avec tous les paramètres => .Find("*", , , , xlByColumns, xlPrevious), ce qui change les paramètres en cours de la méthode .Find, tout simplement...

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
Sub Calculation()
Dim Cell As Range
Dim i As Long
Dim iOccupiedCount As Long
Dim d As Date
For Each Cell In Selection
    iOccupiedCount = 0
    d = Cells(Cell.Row, Cell.Column - 1).Value
    With Worksheets(3)
        For i = 2 To .Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row
            If d < .Cells(i, 15).Value And d >= .Cells(i, 14) _
            .Value Then iOccupiedCount = iOccupiedCount + 1
        Next i
    End With
    With Worksheets(2)
        For i = 2 To .Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row
            If .Cells(i, 7).Value = "Occupied" And d >= .Cells(i, 11).Value _
            Then iOccupiedCount = iOccupiedCount + 1
        Next i
    End With
    Cell.Value = iOccupiedCount
Next Cell
End Sub

Merci de votre aide


1ère reformulation du problème :

Bonjour,

J'utilise dans mon outil Excel en cours de développement la méthode .Find. Or, il se trouve qu'après l'éxécution de la procédure suivante, la méthode .Find se met à renvoyer Nothing (j'utilise cette procédure sur un onglet à part des cellules recherchées avec .Find). Il suffit alors de sauvegarder, de fermer et de réouvrir le classeur pour qu'elle se remette à fonctionner.

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
Sub Calculation()
Dim Cell As Range
Dim i As Long
Dim iOccupiedCount As Long
Dim d As Date
For Each Cell In Selection
    iOccupiedCount = 0
    d = Cells(Cell.Row, Cell.Column - 1).Value
    With Worksheets(3)
        For i = 2 To .Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row
            If d < .Cells(i, 15).Value And d >= .Cells(i, 14) _
            .Value Then iOccupiedCount = iOccupiedCount + 1
        Next i
    End With
    With Worksheets(2)
        For i = 2 To .Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row
            If .Cells(i, 7).Value = "Occupied" And d >= .Cells(i, 11).Value _
            Then iOccupiedCount = iOccupiedCount + 1
        Next i
    End With
    Cell.Value = iOccupiedCount
Next Cell
End Sub

En vous remerciant par avance


Ancienne formulation du problème :


Bonjour,

Je développe actuellement un outil excel pour gérer des réservations de chambres. J'en suis à 2000 lignes de code environ.

J'ai créé un module "déclarations" dans lequel je déclare toutes les variables et fonctions publiques
J'essaye au maximum de me passer de l'utilisation de variables publiques. J'ai cependant besoin de déclarer des constantes qui sont reprises plusieurs dizaines de fois, et que je peux donc modifier rapidement grâce à ce module. Je préfère éviter de cacher ces valeurs dans une cellule (je le fais une fois avec rRoomsOfCampView).

L'utilisation de fonctions constantes déclarées comme publique me semble plus pratique que les variables globales, avec lesquelles il faudrait affecter une valeur avec ThisWorkbook_Open.

Voici le problème : A différentes occasions, je perds les valeurs des fonctions constantes qui ont été déclarées.

Par exemple, après l'éxecution d'une procédure un peu lourde sur un autre onglet, je me retrouve avec rRoomSideLocation = Nothing, alors que rRoomSideLocation() est défini comme une fonction publique :

Code :

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

1
2
3
4
5
6
7
Public Function rRoomSideLocation() As Range
' Searching "sRoom side" to define where the maintenance information about the room side will
' be displayed. Actually, this zone can be moved if needed, the program will detect
' automatically the new zone, thanks to the "sRoom side" cell, at the top-left corner
' of the zone. You must not write "sRoom side" anywhere else in the "Camp view" sheet.
Set rRoomSideLocation = Worksheets(1).Cells.Find("Room side", LookAt:=xlWhole)
End Function

Il me suffit alors de sauvegarder, de fermer et de réouvrir le fichier pour les retrouver. Mais j'aimerais comprendre ce qui se passe. Quelqu'un aurait une idée ? On pourrait aussi soupçonner la procédure .Find...

Voici la totalité du code dans le module :

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
Public rSelectedRoom As Range
' Room selected when choosing a room at general check in.
Public bSelectARoomFinished As Boolean
' Indicating when the room selection is finished.
Public bIsLoopingSelectARoom As Boolean
' To know if the room selector is waiting for the user.
Public oCurrentInput As Object
' To know if we are writing in a box or in a cell.
Public bIsLoopingSearchWord As Boolean
' To know if the search engine is waiting for the user.
Public bSearchFinished As Boolean
' To know if we are finishing the search.
Public bNextResult As Boolean
' Jumping to the next result in the search engine.
Public Function rNoLock() As Range
' Defining the cells which should not be locked.
Set rNoLock = Union(Worksheets(1).Range( _
"R5:W15,U4:W4,Y5:AD15,AB4:AD4,U22:AD22,U28:AD28,U34:AD34,U40:AD40,U46:AD46,R37:S37,P43:Q43,P44:Q44,P45:Q45,G42:P42,G38:P38,G34:P34,G30:P30,M26:N26,B42:C42,D38:E38,B34:C34,D30:E30,B26:C26,D22:E22,B18:C18,D14:E14,B10:C10,D7:E7,B4:C4,H3:J3,L7:N7,I8:N10" _
), Worksheets(1).Range("I11:K11,I6:N6,I12:N12,I13:N21"))
End Function
Public Function rLinkedInventoryColumns() As Range
' Defining the columns of the database, for which rooms A and B must be linked.
' Ex : Inventory for a junior room is the same for A and B
Set rLinkedInventoryColumns = Worksheets(2).Range("N:AI,D:D")
End Function
Public Function rNotLinkedInventoryCCColumns() As Range
' Defining the room side columns, which must not be linked for crew change rooms.
Set rNotLinkedInventoryCCColumns = Worksheets(2).Range("N:X")
End Function
Public Function rRoomsOfCampView() As Range
' Defining precisely the range of cells which represent the rooms.
' If we want to add a new building, we need to change this.
Set rRoomsOfCampView = Worksheets(1).Range(Worksheets(1).Cells(1, 1).Value)
End Function
Public Function rRoomCodeLocation() As Range
' Searching "sRoom code" to define where the information about the room will
' be displayed. Actually, this zone can be moved if needed, the program will detect
' automatically the new zone, thanks to the "sRoom code" cell, at the top-left corner
' of the zone. You must not write "sRoom code" anywhere else in the "Camp view" sheet.
Set rRoomCodeLocation = Worksheets(1).Cells.Find("Room code", LookAt:=xlWhole)
End Function
Public Function rRoomSideLocation() As Range
' Searching "sRoom side" to define where the maintenance information about the room side will
' be displayed. Actually, this zone can be moved if needed, the program will detect
' automatically the new zone, thanks to the "sRoom side" cell, at the top-left corner
' of the zone. You must not write "sRoom side" anywhere else in the "Camp view" sheet.
Set rRoomSideLocation = Worksheets(1).Cells.Find("Room side", LookAt:=xlWhole)
End Function
Public Function rLavatorySideLocation() As Range
' Searching "Lavatory side" to define where the maintenance information about the room side will
' be displayed. Actually, this zone can be moved if needed, the program will detect
' automatically the new zone, thanks to the "Lavatory side" cell, at the top-left corner
' of the zone. You must not write "Lavatory side" anywhere else in the "Camp view" sheet.
Set rLavatorySideLocation = Worksheets(1).Cells.Find("Lavatory side", LookAt:=xlWhole)
End Function
Public Function lColorGreen() As Long
lColorGreen = Worksheets(1).Cells(43, 16).Interior.Color
' Defining the green color => Free room. It can be changed with Excel tools on the sheet if needed.
End Function
Public Function lColorOrange() As Long
lColorOrange = Worksheets(1).Cells(44, 16).Interior.Color
' Defining the orange color => Occupied room. It can be changed with Excel tools on the sheet if needed.
End Function
Public Function lColorRed() As Long
lColorRed = Worksheets(1).Cells(45, 16).Interior.Color
' Defining the red color => sRoom to repair. It can be changed with Excel tools on the sheet if needed.
End Function
Public Function rSearchInputLocation() As Range
Set rSearchInputLocation = Worksheets(1).Cells(3, 8)
' Defining where the search engine input is located. It can be changed if needed.
End Function

En vous remerciant par avance

[issoram]

Bonjour,

Je ne suis pas sûr de comprendre:

A différentes occasions, je perds les valeurs des fonctions constantes qui ont été déclarées.

Une fonction n'a pas de valeur en tant que telle, elle renvoie une valeur, un objet ou autre chose. En l’occurrence ici ta fonction renvoie un objet Range. Si la méthode Find "échoue" (ne trouve pas la valeur recherchée) elle renvoie Nothing (plage vide) et par extension ta fonction aussi. Rien d'anormal là dedans.

J'espère que ça répond à ta question.

[max160]

Merci pour ta réponse

Effectivement, même si je me sers de ces fonctions pour stocker des valeurs, ce ne sont pas des variables en tant que tel.

J'ai testé une procédure MsgBox Worksheets(1).Cells.Find("Room code", LookAt:=xlWhole).Value après l'éxécution d'une des procédures qui fait que les fonctions renvoient Nothing, et effectivement la méthode .Find renvoie Nothing.

Il s'agit donc d'un problème sur .Find qui ne fonctionne plus.

Peut-être faut-il préciser tous les paramètres du .Find à chaque fois ?

[issoram]

Il s'agit donc d'un problème sur .Find qui ne fonctionne plus.

Euh pas tout à fait, la méthode Find ne trouve pas "Room code" dans les cellules de ta feuille 1.

Peut-être faut-il préciser tous les paramètres du .Find à chaque fois ?

Tout dépend de ce que tu veux faire avec Find, si tu ne mets aucun paramètre, ça sera les valeurs par défaut qui seront utilisées. Dans ton cas, il cherche la chaîne "Room code" comme "contenu total" d'une cellule, sans respect de la casse et je pense qu'il recherche dans les valeurs par défaut (lookin:=xlValues). Voir l'aide pour les paramètres facultatifs.

Autre piste: de mémoire (à vérifier) la fonction find échoue sur les plages masquées.

[max160]

Euh pas tout à fait, la méthode Find ne trouve pas "Room code" dans les cellules de ta feuille 1.

Très juste, c'est une généralisation abusive de ma part.

Tout dépend de ce que tu veux faire avec Find, si tu ne mets aucun paramètre, ça sera les valeurs par défaut qui seront utilisées. Dans ton cas, il cherche la chaîne "Room code" comme "contenu total" d'une cellule, sans respect de la casse et je pense qu'il recherche dans les valeurs par défaut (lookin:=xlValues). Voir l'aide pour les paramètres facultatifs.

Autre piste: de mémoire (à vérifier) la fonction find échoue sur les plages masquées.

Hé bien il me semble qu'en éxécutant la procédure indiquée qui pose problème, les paramètres par défaut de la procédure .Find sont modifiés. En effet, en remplaçant :

Code :

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

Set rRoomCodeLocation = Worksheets(1).Cells.Find("Room code", LookAt:=xlWhole)

par

Code :

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

1
2
Set rRoomCodeLocation = Worksheets(1).Cells.Find("Room code", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)

la procédure .Find parvient à trouver "Room code" (sachant que la cellule de valeur "Room code" est verrouillée avec la feuille protégée, mais pas masquée).

Après je ne vois pas pourquoi les paramètres par défaut sont modifiés. Mais c'est déjà bien de pouvoir palier le problème. Merci de ton aide

edit : C'est bon j'ai trouvé, c'est

Code :

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

.Find("*", , , , xlByColumns, xlPrevious)

qui change les paramètres en cours de la procédure .Find tout simplement...

[patricktoulon]

Bonjour
je n'ai pas regarder toutes les réponses mais ce qui a de sur c'est que le raisonnement n'ai pas bon

en effet
quand tu boucle sur une plage avec find et que tu modifie la cellule trouvée tu ne peut pas utilise le find next
en effet si l'on fait
set celul=range....).find.......

et que tu fait celul.value=ce que tu veux
et au prochain tour celul n'existe plus puisqu'elle ne correspond plus a la recherche car tu l'a modifier et poff !!! erreur
il te faut faire la même chose mais mémoriser dans un array les référence cellule
travailler dessus après