Procédure trop grande et With

Version imprimable

Bonjour,

Je suis victime de "Procédure trop grande" :
Et, il se trouve que la quasi totalité de mon code se trouve dans un :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
With Workbooks.Add
 
                    .Worksheets(m + 1).Cells(b, 3).Value = ""
                    .Worksheets(m + 1).Cells(b, 4).Value = ""
                    .Worksheets(m + 1).Cells(b, 5).Value = ""
                    .Worksheets(m + 1).Cells(b, 6).Value = ""
                    .Worksheets(m + 1).Cells(b, 7).Value = ""
                    .Worksheets(m + 1).Cells(b, 8).Value = ""
                    .Worksheets(m + 1).Cells(b, 9).Value = ""
                    .Worksheets(m + 1).Cells(b, 10).Value = ""
                    .Worksheets(m + 1).Cells(b, 11).Value = ""
                    .Worksheets(m + 1).Cells(b, 12).Value = ""
                    .Worksheets(m + 1).Cells(b, 13).Value = ""
 
End With

Du coup en le passant sous une Function et en déclarant mes variables m et b en global, j'ai un problème avec la façon dont j'exprime mon Workbook.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
Sub MonPrgm() 
With Workbooks.Add
 
                    Call coucou
End With
End Sub
 
Function coucou()
                    .Worksheets(m + 1).Cells(b, 3).Value = ""
                    .Worksheets(m + 1).Cells(b, 4).Value = ""
                    .Worksheets(m + 1).Cells(b, 5).Value = ""
                    .Worksheets(m + 1).Cells(b, 6).Value = ""
                    .Worksheets(m + 1).Cells(b, 7).Value = ""
                    .Worksheets(m + 1).Cells(b, 8).Value = ""
                    .Worksheets(m + 1).Cells(b, 9).Value = ""
                    .Worksheets(m + 1).Cells(b, 10).Value = ""
                    .Worksheets(m + 1).Cells(b, 11).Value = ""
                    .Worksheets(m + 1).Cells(b, 12).Value = ""
                    .Worksheets(m + 1).Cells(b, 13).Value = ""
End Function

Il m'affiche le message d'erreur suivant :
Erreur de Compilation
Référence incorrecte ou non qualifiée

En regardant l'aide je vois bien que c'est à cause du . qui est devant que l'on ne peut utiliser que dans un bloc with.
Du coup je ne sais pas comment faire parce que je souhaiterais continuer à réaliser des opérations sur ce document que j'ai add plus haut.

Et vous vous doutez bien que si ma procédure est trop grande, c'est que je recherche un moyen sans changer toute la structure de mon code.

En vous remerciant,

EDIT : Sinon est-ce que la capacité d'une fonction est elle plus "grande" que celle d'un Sub ?
Parce que j'peux tout mettre dans la Fonction coucou au pire..

04/09/2017, 15h59
Invité

Bonjour,

le . sous entant un With or dans Coucou je n'en vois pas!

Workbooks.Add ajoute une page vierge! doc quel intérêt de .Worksheets(m + 1).Cells(b, 3).Value = "" pour un feuille M + 1 qui n'existe peut être pas ?

Merci de ta réponse rapide !

Si tu veux ma procédure marche à merveille, donc ne te fais pas de soucis sur les indices ! On m'a demandé de rajouter quelques lignes pour faire des tests supplémentaires.

Donc selon toi, il faudrait que dans ma Function coucou(), j'arrive à récupérer mon Workbook.Add
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Sub MonPrgm() With Workbooks.Add .SaveAs ("monchemin\Fichiers par service\" & listeservice(i) & ".xls") 'un tas d'action sur mon Workbooks.Add Call coucou End Sub Function coucou() With Workbook("monchemin\Fichiers par service\" & listeservice(i) & ".xls") End With End Function
En essayant ce genre de chose (j'ai bien sûr déclaré ma listeservice et mon i en global), je n'arrive pas à récupérer la feuille...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub MonPrgm()
Dim wb As Workbook
Set wb = Workbooks.Add
With wb
            .SaveAs ("monchemin\Fichiers par service\" & listeservice(i) & ".xls")
 
            'un tas d'action sur mon Workbooks.Add
 
            Call coucou(wb)
 
End Sub
 
Function coucou(ByRef wb As Workbook)
          With wb
 
 
 
          End With
End Function

04/09/2017, 16h36
-Anthime-

Merci beaucoup : dysorthographie

Résolu
04/09/2017, 16h39
eriiic

Bonjour,

au-delà des remarques justifiées de dysorthographie (comment pourrait-on deviner la valeur de m et le nombre de feuilles de tes classeurs créés ?) de toute façon une fonction retourne une valeur et ne peut en aucune façon aller modifier une autre cellule.
Il faut le faire dans un sub.
Un classeur neuf étant par définition vide quel est l'intérêt d'aller en vider des cellules ???
En supposant que ce soit utile pourquoi les vider une par une plutôt que toute la plage en une fois ?
eric
04/09/2017, 18h53
patricktoulon

re

Bonjour je rejoins erric sur ce coup la

d'autant plus que l'argument ne devrait pas etre le WB mais le WB.sheets("xxxxx") pour un ".range)
05/09/2017, 11h06
-Anthime-

Bonjour,

Ma procédure étant trop longue, j'ai mis un peu n'importe quoi dans mon With !
Bien sûr que ça n'a pas beaucoup d'intérêt de supprimer des cases d'une page qui est déjà vide.

La question était surtout portée sur l'utilisation d'une fonction à l'intérieur d'un With !

En ravanche

Citation:

ne peut en aucune façon aller modifier une autre cellule.

Pourtant c'est bien ce que je fais faire à mon programme, et ça marche à merveille..
Comment cela se fait-il ?

En vous remerciant,

Merci
05/09/2017, 11h30
eriiic

Bonjour,

Parce que tu appelles ta fonction à partir d'un sub, elle réagit comme un sub qu'elle devrait être.
Une telle écriture est trompeuse inutilement.
Dans tous les langages une fonction est destinée à retourner une valeur et c'est tout. C'est sont rôle, c'est ce qui la distingue d'un sub.
Tu imagines une fonction sin() qui va te mettre 0.5 en B12 et uniquement là ? Non, tu récupères le résultat de la fonction que tu utilises comme tu veux.
eric
05/09/2017, 14h01
-Anthime-

D'accord,

Donc, si je comprends bien, j'en fait une utilisation un peu étrange visiblement.

Et bien je vous remercie à tous pour les explications fournies.

Merci beaucoup