Accéder à un classeur caché

Version imprimable

18/09/2015, 22h47
petitchti

1 pièce(s) jointe(s)

Accéder à un classeur caché

Bonjour à tous,

Je suis en train de créer une macro excel qui tourne un certain temps pendant qu'elle réalise de très nombreux calculs. Au lieu de laisser le classeur excel affiché pendant tout le temps de calcul, je cache l'application (Application.Visible = False), et affiche un UserForm.

Cependant, je souhaiterais maintenant améliorer la macro et ne cacher QUE mon classeur lorsque j'exécute ma macro (Workbooks("Nom_du_classeur").Visible=False) de manière à ce que si l'utilisateur avait d'autres fichiers Excel ouverts, ils ne soient pas cachés eux aussi.

Mon problème (qui apparait même lorsque je n'ai pas d'userform) est que lorsque je cache juste mon classeur (et non pas l'application entière) j'ai une erreur. Lorsque j'essaie de tester ou de rentrer une valeur dans une cellulle de ce fameux classeur caché, j'obtiens le message suivant: "La méthode Worksheets de l'objet global a échoué".

La solution d'ajouter " Workbooks("Nom_du_classeur") " devant toutes mes lignes de code de lecture et d'écriture fonctionne à priori sur un exemple (en pièce jointe, on comprend aisément le problème en utilisant F8)... Mais ma macro entière faisant plus de 5000 lignes, j'ai très peu envie de modifier toutes les lignes une à une pour ajouter ce " Workbooks("Nom_du_classeur") ". Et étant plutôt novice sur VBA, je suis toujours motivé pour découvrir de nouvelles structures et méthodes.

Savez-vous pourquoi j'obtiens cette erreur seulement lorsque je cache le classeur et non pas l'application? Y a-t-il un moyen de remédier à ce problème d'une autre façon que celle mentionnée? J'ai par exemple pensé à inclure une ligne au début du code pour définir une sorte de "Workbooks" par défaut pour m'éviter de devoir le mettre à chaque ligne :/

Merci beaucoup pour votre aide.

Citation:

Savez-vous pourquoi j'obtiens cette erreur seulement lorsque je cache le classeur et non pas l'application?

Bonjour,

Le code suivant explique la raison de l'erreur mais ce n'est pas cela qui va solutionner votre problème de fond.

Code:

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
Dim i As Integer
 
Sub test()
Application.Visible = True
Windows(1).Visible = True
For i = 1 To 4
    Worksheets("Feuil1").Cells(i, 1).ClearContents
Next i
Dim S As Window
Set S = Windows(1)
Worksheets("Feuil1").Range("A1").Value = "Ok"
 
Application.Visible = False
 
Worksheets("Feuil1").Range("A2").Value = "Ok"
 
Application.Visible = True
 
Worksheets("Feuil1").Range("A3").Value = "Ok"
 
Windows(1).Visible = False
 
'///ajout/modif
Windows(1).ActiveSheet.Range("A4").Value = "Ok avec Windows(1).Visible = False"
MsgBox "La fenêtre va redevenir visible"
Windows(1).Visible = True
'///
End Sub

20/09/2015, 20h13
petitchti

Bonjour PMO2017,

merci pour votre aide ;)

C'est vrai que cela ne solutionne pas le problème. Au final cela revient à utiliser la méthode que j'ai décrite dans mon premier message: ajouter "Workbooks("Nom_du_classeur")" mais en un peu plus simple :)

Personne n'a la solution (à savoir comment accéder à un classeur caché sans devoir le nommer à chaque ligne de code) ? :)

Merci d'avance.

Bonjour,

Citation:

Personne n'a la solution (à savoir comment accéder à un classeur caché sans devoir le nommer à chaque ligne de code) ?

Tu n'as pas le choix, il te faut "parenter" les "Range" ! Exécute les procédures. "Test1" et ensuite "Test2" et tu verras :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
Sub Test1()
 
    Windows("Test_hidden.xlsm").Visible = False
 
    With Workbooks("Test_hidden.xlsm").Worksheets("Feuil1")
 
        .Range(.Cells(1, 1), .Cells(4, 1)).ClearContents
        .Range("A1").Value = "Ok"
 
        .Range("A2").Value = "Ok"
 
        .Range("A3").Value = "Ok"
 
        .Range("A4").Value = "Ok"
 
    End With
 
    Windows("Test_hidden.xlsm").Visible = True
 
End Sub

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
Sub Test2()
 
    Windows("Test_hidden.xlsm").Visible = False
 
 
        Range(Cells(1, 1), Cells(4, 1)).ClearContents
        Range("A1").Value = "Ok"
 
        Range("A2").Value = "Ok"
 
        Range("A3").Value = "Ok"
 
        Range("A4").Value = "Ok"
 
 
    Windows("Test_hidden.xlsm").Visible = True
 
End Sub

L'instruction "With -End With" est là pour ça, pour ne pas avoir à réécrire la chaîne complète.

21/09/2015, 17h40
Invité
Bonjour,
Quand un code n'est pas bien ficelé, je ne vois pas d'autre solution que de le reprendre,quite ligne par ligne!

C'est la rançon de la gloire!

Il faut apprendre à utiliser la programmation Object!
Code:

1 2 3 set wb= Workbooks("Test_hidden.xlsm") Set wh=wb.Worksheets("Feuil1") With wh
Maintenant c'est toi qui vois, comme l'a dit Theze , je ne suis pas sur que tu ais vraiment le choix, et ça proposition est certainement la moins coûteuse!
21/09/2015, 21h14
clementmarcotte

Bonjour,

Citation:

Envoyé par petitchti

Personne n'a la solution (à savoir comment accéder à un classeur caché sans devoir le nommer à chaque ligne de code) ? :)

Merci d'avance.

Arrêter de prendre les utilisateurs pour des imbéciles patentés et ne rien cacher. Laisser l'utilisateur décider s'il veut cliquer sur la ligne en haut à droite pour que Windows s'en charge lui-même. Au besoin, on se contente d'avertir la victime qu'il peut continuer.

Ou bien, tu exportes ton module en fichier texte. Ensuite tu l'ouvres dans Word et tu fais une opération de recherche et de remplacement. Ensuite tu réenregistres ton fichier au format texte et tu le réimportes dans ton projet.
21/09/2015, 21h48
petitchti

Merci à tous, vos réflexions me permettent d'avancer :)

Oui je programme un peu, mais j'utilise pas vraiment la programmation objet. Mais je pense que je vais m'y mettre :)

Bon en tout cas, je suis contraint à reprendre ligne par ligne mon programme. Il est préférable de le cacher car je crée un "logiciel" à base d'userform, et il est plus esthétique/convivial/user-friendly de cacher la fenêtre et de n'afficher que le userform :)

J'avais pensé à la solution de tout basculer sur word pour faire un rechercher/remplacer. Je vais voir si je peux pas essayer de tourner le problème autrement, et si c'est pas possible, je ferai comme cela :)

Et sinon, question bonus (pour contourner mon problème): Y a-t-il un moyen d'empêcher l'utilisateur d'ouvrir une fenêtre Excel qui est réduite (xlMinimized)? Je n'ai pas trouvé d'occurence sur google :/

Merci encore à vous ;)

Citation:

Y a-t-il un moyen d'empêcher l'utilisateur d'ouvrir une fenêtre Excel qui est réduite (xlMinimized)?

Bonjour,

Peut être avec cette grosse bidouille

1) Copiez le code suivant dans la fenêtre de code de ThisWorkbook du classeur qu'on veut garder minimisé
Code:

1 2 3 4 5 6 7 8 Private Sub Workbook_WindowActivate(ByVal Wn As Window) If GARDER_MINIMISE And Application.Workbooks.Count > 1 Then Application.ScreenUpdating = False ThisWorkbook.Windows(1).WindowState = xlMinimized ActiveWorkbook.Windows(1).WindowState = xlMaximized Application.ScreenUpdating = True End If End Sub
2) Copiez le code suivant (à adapter selon votre traitement) dans un module Standard du classeur qu'on veut garder minimisé
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Public GARDER_MINIMISE As Boolean Sub Debut() GARDER_MINIMISE = True ThisWorkbook.Windows(1).WindowState = xlMinimized ActiveWorkbook.Windows(1).WindowState = xlMaximized '... votre traitement End Sub Sub fin() '... Sub appelée à la fin de votre traitement GARDER_MINIMISE = False ThisWorkbook.Windows(1).WindowState = xlMaximized End Sub
Chaque fois qu'un autre classeur est ouvert, le classeur (qu'on veut garder minimisé) se minimise et le reste tant que la procédure "Sub Debut" est en cours et qu'on n'a pas fait appel à la procédure "Sub fin".

Salut tout le monde,

A la rigueur, tu peux réecrire la propriété Worksheets en spécifiant le ThisWorkbook comme classeur.

Code:

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
 
Option Explicit
Option Private Module
Dim i As Integer
 
 
Sub test()
Application.Visible = True
Windows("test_hidden.xlsm").Visible = True
For i = 1 To 4
    Worksheets("Feuil1").Cells(i, 1).ClearContents
Next i
 
Worksheets("Feuil1").Range("A1").Value = "Ok"
 
Application.Visible = False
 
Worksheets("Feuil1").Range("A2").Value = "Ok"
 
Application.Visible = True
 
Worksheets("Feuil1").Range("A3").Value = "Ok"
 
Windows("test_hidden.xlsm").Visible = False
 
Worksheets("Feuil1").Range("A4").Value = "Ok"
 
End Sub
 
 
Property Get Worksheets(s As String, Optional Wb As Workbook) As Worksheet
    If Wb Is Nothing Then
        Set Worksheets = ThisWorkbook.Worksheets(s)
    Else
        Set Worksheets = Wb.Worksheets(s)
    End If
End Property

22/09/2015, 20h06
petitchti

1 pièce(s) jointe(s)

Merci à vous deux pour votre réponse,

Et un ENORME merci à Nouveau2, ça fonctionne très bien!!! :D :D :D

Et je dirai même plus : on gagne même du temps avec cette méthode!

J'ai fait quelques essais simples (effacer et écrire dans 250 000 cellules) qui sont résumés ci-dessous et dans le fichier ci-joint.

==>MODE1
-On cache uniquement le classeur
-On modifie la propriété de Worksheets
--> Le temps d'execution est de 31 secondes en moyenne

==>MODE2
-On cache uniquement le classeur
-On ajoute "ThisWorkbook." devant les lignes de code
--> Le temps d'execution est de 32 secondes en moyenne

==>MODE3
-On cache l'application
-On ne change rien au code (pas de "ThisWorkbook", ni de modification de la propriété "worksheets")
--> Le temps d'execution est de 32 secondes en moyenne

Il n'y a certes qu'une petite seconde d'écart, mais elle est là quand même, et les résultats sont répétables (j'ai lancé les différents modes 5-6 fois chacun!).

C'est étrange car quand on execute le code pas à pas on voit pourtant bien que la modification de la propriété "Worksheets" est appelée à chaque itération (donc 500 000 fois) et pourtant, on gagne du temps! Il y a surement, en interne (non visible au développeur) la véritable propriété "Worksheets" qui est appelée et qui prend autant de temps que celle de la modification...

Mais pour ma culture personnelle: y-a-t il une explication à ce gain de temps? :)

Pour ce qui est du bidouillage de la fenêtre à laisser réduite, je la garde au chaud de côté, merci à toi PMO2017 ;)

Bonjour,

Vous pouvez aussi travailler en mémoire avec un tableau à 2 dimensions puis inscrire le tableau dans la feuille en une seule passe.

Code:

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
Sub test()
timedebut = Now()
 
'''On efface les anciennes données
''For i = 1 To 25000
''    For j = 1 To 10
''        Worksheets("Feuil1").Cells(i, j).ClearContents  '------------------MODE 1 = 31s
''        'ThisWorkbook.Worksheets("Feuil1").Cells(i, j).ClearContents '-----MODE 2 = 32s
''        'Worksheets("Feuil1").Cells(i, j).ClearContents '------------------MODE 3 = 32s
''    Next j
''Next i
''
'''On teste
''Worksheets("Feuil1").Range("A1").Value = "Ok"
''Application.Visible = False
''Worksheets("Feuil1").Range("A2").Value = "Ok"
''Application.Visible = True
''Worksheets("Feuil1").Range("A3").Value = "Ok"
''
'''On choisit le mode
''Windows("Test_time.xlsm").Visible = False '-----------------------------MODE 1 = 31s
'''Windows("Test_time.xlsm").Visible = False '----------------------------MODE 2 = 32s
'''Application.Visible = False '--------------------------------------------MODE 3 = 32s
''
'''On écrit les données
''For i = 4 To 25000
''    For j = 1 To 10
''        Worksheets("Feuil1").Cells(i, j).Value = "Ok"  '------------------MODE 1 = 31s
''        'ThisWorkbook.Worksheets("Feuil1").Cells(i, j).Value = "Ok" '-----MODE 2 = 32s
''        'Worksheets("Feuil1").Cells(i, j).Value = "Ok" '------------------MODE 3 = 32s
''    Next j
''Next i
''
''Application.Visible = True
''Windows("Test_time.xlsm").Visible = True
 
Dim T()
 
ReDim T(1 To 25000, 1 To 10)
For i = 1 To 25000
  For j = 1 To 10
    T(i, j) = "Ok"
  Next j
Next i
 
Range("a1:j25000") = T
 
MsgBox Format(Now() - timedebut, "ss,0")
End Sub

22/09/2015, 21h15
Invité

Salut,

Citation:

' On efface les anciennes données

Code:

Worksheets("Feuil1").Range("A1", "J25000").ClearContents

C'est pas plus simple et plus rapide ?

Sinon, utilise la méthode de PMO2017 pour lire/écrire sur les cellules d'Excel. C'est beaucoup plus rapide.
22/09/2015, 22h16
petitchti

Oui oui je sais que c'est pas la meilleure façon pour faire ce que j'ai fait ;)

Mais j'ai délibérement écrit des instructions qui prennent du temps pour justement vérifier que la méthode de Nouveau2 ne m'augmente pas significativement mon temps de calcul dans ma véritable macro de 5000lignes. Ce qui ne sera apparemment pas le cas :)

La macro que je vous ai montré est juste à titre d'exemple ;)

Citation:

Envoyé par petitchti

C'est étrange car quand on execute le code pas à pas on voit pourtant bien que la modification de la propriété "Worksheets" est appelée à chaque itération (donc 500 000 fois) et pourtant, on gagne du temps! Il y a surement, en interne (non visible au développeur) la véritable propriété "Worksheets" qui est appelée et qui prend autant de temps que celle de la modification...

Mais pour ma culture personnelle: y-a-t il une explication à ce gain de temps? :)

Je ne sais pas trop d'où vient ton gain, mais sache que quand tu appel ThisWorbook dans ton code, tu appel le module object au sein de ton propre projet (Appelé par défaut VBAProject). Tu appel enfaite VBAProject.ThisWorbook.Worksheets...etc.
ThisWorkbook n'est pas une propriété dans ton cas. Le VBA se base sur la profondeur pour connaitre le niveau de la priorité. Ton Projet est prioritaire sur ceux qui sont externes (Excel, etc...).
Pour appel la propriété, tu peux écrire:
Excel.ThisWorkbook.Worksheets.... au lieu de VBAProject.ThisWorkbook.Worksheets (ou ThisWorkbook.Worksheets)

La propriété que l'on a réécrite tout à l'heure te donne (par défaut):
VBAProject.Module1.Worksheets...etc .

Je ne sais pas si tu y vois plus clair.

-----------------------------------------------------------------------------

Je viens d'essayer, et effectivement, il y a un gain lors des appels.
Je ne sais pas du tout d'où ça vient.

Le code que j'ai utilisé:
Code:

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 Option Explicit Sub Test() Dim Rng As Range Dim i As Long Dim t(0 To 4) As Double Dim s As String Dim Ws As Worksheet Set Ws = Excel.ThisWorkbook.Worksheets("Feuil1") t(0) = Timer For i = 1 To 10000 Set Rng = Excel.ThisWorkbook.Worksheets("Feuil1").Range("A1") Next t(1) = Timer For i = 1 To 10000 Set Rng = VBAProject.ThisWorkbook.Worksheets("Feuil1").Range("A1") Next t(2) = Timer For i = 1 To 10000 Set Rng = VBAProject.Module2.Worksheets("Feuil1").Range("A1") Next t(3) = Timer For i = 1 To 10000 Set Rng = Ws.Range("A1") Next t(4) = Timer For i = 1 To 4 s = s & (t(i) - t(i - 1)) * 1000 & " ms" & IIf(i < 4, vbNewLine, vbNullString) Next Debug.Print s MsgBox s End Sub Private Property Get Worksheets(s As String, Optional Wb As Workbook) As Worksheet If Wb Is Nothing Then Set Worksheets = ThisWorkbook.Worksheets(s) Else Set Worksheets = Wb.Worksheets(s) End If End Property
J'ai ceci comme temps :
223,14453125 ms
213,8671875 ms
126,953125 ms
88,134765625 ms

24/09/2015, 23h11
petitchti

Salut Nouveau2 !

Merci pour ces explications. J'y vois plus clair maintenant, je ne savais pas tout ça, je trouve ça intéressant de savoir comment le tout est ficelé en interne :)

Je mets aussi sous le coup la fonction Timer pour avoir des durées précises (j'utilisais Now() jusqu'à présent, mais on ne pouvait pas avoir une meilleure précision que la seconde!). D'ailleurs sur la fonction Timer il est indiqué qu'on a une précision qu'au centième de seconde, tu as beaucoup plus pourtant! Ici

Le principal, c'est que tu aies résolu mon problème, que ça marche bien, et qu'en plus ça me fasse gagner (un peu) de temps :)

De mon côté j'ai:
179.6875ms
171.875ms
125ms
78.125ms

Mais j'ai pas autant de chiffres après la virgule que toi!

Merci d'avoir passé du temps à t'être occupé de mon problème ;)