Discussion :

[pasterlouis]

Bonjour tout le monde,
Après une demi journée de blocage je me permet encore une fois de solliciter votre aide.
En fait je doit faire une macro qui me permet de supprimer toutes les formules de toutes mes feuilles que j'ai choisit dans mon répertoire. J'ai fait ce code(ci-dessous), il ne m'affiche aucune erreur mais il ne marche pas non plus car dans mes feuilles les formules restent toujours.
Je vous remercie d'avance

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
Private Sub CommandButton2_Click()
 
On Error Resume Next
Dim a As Integer
Dim xlwkb As Excel.Workbook
Dim xlwks As Excel.Worksheet
 
Set xlapp = GetObject(, "Excel.Application")
 
xlapp.DisplayAlerts = False
xlapp.AskToUpdateLinks = False
xlapp.Visible = False
num = 1
 
For a = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(a) = True Then
        Set xlwkb = xlapp.Workbooks.Open(repertoire & "\.xls?" & ListBox1.List(a), UpdateLinks:=True)
 
        For i = 1 To xlwkb.Sheets.Count
            Err = 0
            Set xlwks = xlwkb.Sheets(i)            'Suppression des formules
            xlwks.Select
            xlwks.Activate
            xlwks.Cells.Select
            xlapp.Selection.Copy
            xlapp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            xlapp.CutCopyMode = False
        Next
        End If
Next
xlapp.Visible = True
MsgBox "fin"
 
Set fso = Nothing
End
End Sub

[Invité]

Code :

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

1
2
3
4
5
6
7
8
Sub Test()
Dim MyRange As Range
 
For I = 1 To xlwkb.Sheets.Count
   Set MyRange = xlwkb.Worksheets(I).UsedRange.Cells
   MyRange = MyRange 'Suppression des formules
Next
End Sub

[nibledispo]

bonjour rdurupt,

ton code m'a impressionné par sa simplicité et je me suis empressé de l'esssayer.

il semble qu'il ne fasse pas dans le détail et restitue des feuilles vierges de tout contenu, formules ou autres.

cordialement.

[Invité]

Effectivement il est simple, trop vraisemblablement.
Je l’utilise depuis des années pour les même raison que toi et jamais de problème.
Je vérifie pour voir si pas d’erreur, le simple se tape vite et on n’échappe pas à quelques incohérences !

On est dans ce que je disais le vite pas bien fait !

Code :

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

MyRange = MyRange.Value

[pasterlouis]

Merci beaucoup ndurupt
en fait ça ne marche pas, il me mêt un debogage a ce niveau:

Code :

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

For i = 1 To xlwkb.sheets.Count

en suite est ce que cela peut marcher en sachant que je selectionne le dossier que je veux traiter dans un répertoire à l'aide d'un CommanBouton. Voici le code pour cela pour que tu puisse comprendre:

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
Private Sub CommandButton1_Click()
    Dim fso As Object
    Dim repertoire As String, sfile As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Show
        repertoire = .SelectedItems(1)
    End With
    With Me.ListBox1
        .Clear
        sfile = Dir(repertoire & "\*.xls?")   'Dir(repertoire & "\")
        Do While sfile <> ""
            .AddItem sfile
        sfile = Dir
        Loop
    End With
End Sub

[nibledispo]

Bonjour rdurupt,

Cette fois c'est ok.
Par contre, si je constate que cela marche, je ne comprend ni comment ni pourquoi.

peut-être une petite explication ?

Cordialement.

[Invité]

Bonjour,

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub Test()
Dim MyRange As Range 'Déclare un variable de type plage de cellule Excel.
For I = 1 To xlwkb.Sheets.Count	'Parcoure de la feuille 1 à la dernière du classeur
   Set MyRange = xlwkb.Worksheets(I).UsedRange.Cells 'Affect un lien physique entre Myrange et toutes les cellules contenant une valeur ou une formule.
   MyRange = MyRange .Value  'Je remplie Myrange par les valeurs de lui-même
'Si A1 = "toto" et b1= "= a1"
'B1.formular1c1= "C-1 :R"
'B1.value= "toto"
'B1=b1.value revient  B1.formular1c1= B1. value
Next
End Sub

[Didier Gonard]

Bonjour,

La fait d’écrire MyRange = MyRange est effectivement équivalent à écrire MyRange = MyRange.Value et plus précisément en fait à écrire MyRange.Value = MyRange.Value . En effet la propriété Value est la propriété par défaut d’un objet Range, ce qui fait que si elle n’est pas explicitement précisée, c’est elle que le VBA va appliquer de façon implicite.

Dans une écriture telle que MaVariable = xxx le signe « = » ne signifie pas égal, mais est un opérateur d’affectation et il faut lire le processus de droite à gauche (xxx peut être une constante, une variable, une opération…). La valeur obtenue à droite est affectée à la zone mémoire qui correspond à la valeur de la variable à gauche de l’opérateur =.
Ça rend plus logique que si on lisait X=X+5 de façon phonétique.
C’est d’autant plus troublant pour les débutants qu’en VBA le signe = est aussi utilisé comme opérateur de comparaison et c’est alors une égalité selon le sens de l’écriture qui est testée.
Donc tout est question de contexte !
Pour comprendre, testes le code suivant sur des contextes d’écriture proches, on utilise les deux notions du signe « = » en VBA

Code :

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

1
2
3
4
5
6
7
8
Sub OrmonthPlay()
Dim X As Byte
 
X = 7
MsgBox X = X + 5  'ici en fait on teste si l'égalité est vraie, on retourne un boolean
X = X + 5 'ici on utilise l'opérateur d'affectation
MsgBox X
End Sub

Dans le cas qui nous préoccupe, le code assigne à chaque cellule sa propriété Value, les formules sont alors écrasées au même titre que si tu faisais :

Code :

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

1
2
3
4
5
6
7
8
Sub ChangeCell()
Dim MaVar As Variant 'pas forcément numérique
'on prend l'hypothèse que le contenu de A2 est une formule
 
MaVar = Range("A2").Value 'pas de structure With pour plus de lisibilité ici...
Range("A2").Value = MaVar
 
End Sub

Sinon attention à l’usage de la propriété UsedRange qui prend en compte aussi les formatages de cellules et qui a un effet de rémanence qui peut s’avérer dangereux.

cordialement,

Didier

[nibledispo]

Bonjour pasterlouis et Didier,

pasterlouis,

chez moi cela marche mais il est vrai que par commodité, j'ai remplacé le nom du fichier par thisworkbook. Je ne pense toutefois pas que ce soit la raison de l'échec pour ce qui te concerne.

Didier,

Merci pour les explications, toutefois je demeure circonspect dans le cas qui nous est soumis.

Comme je l'ai écrit plus haut :
- myrange = myrange, qui est selon les explications une affectation de valeur, restitue ici une feuille vierge.
c'est dire que même les cellules contenant des valeurs et non pas des formules sont purgées.

- myrange = myrange.value transforme en revanche bien les formules en valeur et conserve les valeurs des cellules autres .
Ceci revient à dire : mets la valeur de myrange dans la cellule myrange.

Donc, myrange = myrange est peut-être équivalent à myrange.value = myrange.value mais de toute évidence pas à myrange = myrange.value dans ce cas particulier. peut-être l'affectation set de la ligne précédente dans le code?

Cordialement.

[Didier Gonard]

Bonjour,

Mets en PJ un petit classeur exemple du cas où ça t'efface tout

cordialement,

Didier

[nibledispo]

Bonsoir Didier,

Ci-joint la première version expédiée par "rdurupt" au questionneur qui vide les feuilles.

la seconde version remise par "rdurupt" après ma remarque sur le vidage des feuilles du classeur fonctionne. Elle correspond à la ligne en dessous mise en commentaire dans le code.

Les trois feuilles sont renseignées et le code est dans module 1.

cordialement.

[pasterlouis]

merci beaucoup a tout ce qui ont pris leur temps pour répondre à mon problème

[Didier Gonard]

Bonjour,

.../... pas à myrange = myrange.value dans ce cas particulier. peut-être l'affectation set de la ligne précédente dans le code?

Non, le souci ne vient pas de là, le souci est d’utiliser les notations implicites (ce qu’on fait couramment), qui dans certains contextes posent des problèmes d’interprétation à Excel ou au VBA.
Le danger et le piège est que ce souci ne se pose pas si MyRange est une cellule unitaire.
Le souci dans cet exemple est qu’à partir du moment où le Range représente une plage de cellule de plus d’un élément, sa propriété Value devient un Array (ça semble logique, vu qu’il faut stocker plusieurs valeurs) et ce doit être ça qui pose un problème d’interprétation avec la notation implicite (via une variable objet ou une référence de plage directe, peu importe, le comportement est identique).
Donc dans ce cas et dans d’autres, (comme quoi il vaudrait mieux toujours être toujours explicite au niveau des propriétés des Range) le système d’interprétation ne sait pas quoi faire et les résultats peuvent être inattendus ou planter.

On va expliciter ça par du code ce qui sera plus parlant en prenant le pouvoir d’interprétation à son propre jeu et en observant ce qui se passe dans la fenêtre variables locales du VBE en pas à pas.
Pour ce faire, on va déclarer notre MyRange en Variant pour voir comment le VBA interprète la chose…

Code :

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

1
2
3
4
5
6
7
8
9
Sub TestOrmonth3()
Dim MyRange As Variant 'on laisse au pouvoir d'interprétation sa liberté totale,
                        'ce qui n'est pas conseillé sauf obligation VBA
Set MyRange = ThisWorkbook.Worksheets(1).UsedRange  '.Cells
MyRange = MyRange.Value 'Le VBA interprète au mieux,MyRange est devenue un Array, le résultat attendu n'est pas celui visé
                        'ici, on change au total l'affectation de la variable et ça ne touche pas à nos valeurs...
MyRange.Select 'du coup ici = plantage / objet requis. CQFD ligne ci-dessus
 
End Sub

Là le danger de ce genre d’écriture saute aux yeux, on ne fait pas qu’une affectation de valeur, on change aussi la nature de la variable (ce que permet le variant) avec les conséquences citées.

En gardant la même approche, mais en étant plus explicite :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub TestOrmonth4()
Dim MyRange As Variant 'on laisse au pouvoir d'interprétation sa liberté totale,
                        'ce qui n'est pas conseillé sauf obligation VBA
 
Set MyRange = ThisWorkbook.Worksheets(1).UsedRange  '.Cells
MyRange.Value = MyRange.Value 'là on est presque explicite, on remplace l'array des valeurs de notre plage par
                            'l'array des valeurs de la propriété Value de notre plage, ce qui est encore interprété
                            'vue que c'est la propriété formula qu'on veut remplacer au final.
MyRange.Select 'on n'a pas changé l'affectation de notre variable en étant plus explicite, ce qui montre que les 2 écritures
                'sont fondamentalement différentes en traitement sous-jaçant
End Sub

Le VBA n’a pas à se poser de question, pour lui, on affecte clairement à la propriété Value de MyRange un autre Array à la place du précédent, pas d’ambiguïté.

Au final, si on veut être le plus précis et rapide possible, un code encore plus net serait de ce genre, on ne cible que les cellules qui comportent des formules et on vise clairement la propriété formula de MyRange.

Code :

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

1
2
3
4
5
6
7
8
Sub TestOrmonth5()
Dim MyRange As Range
 ' PB si il n'y a pas de formules, le Set va planter....donc traitement d'erreur.
On Error Resume Next
Set MyRange = ThisWorkbook.Worksheets(1).UsedRange.SpecialCells(xlCellTypeFormulas, 23) 'L'emploi du usedrange serait à éviter et à remplacer selon contexte
MyRange.Formula = MyRange.Value
On Error GoTo 0 'le gestionnaire d'erreur est désactivé, normalement, en sortie de procèdure, donc pas utile si rien d'autre.
End Sub

Voilà, en espérant que ces explications t’auront éclairé autant que faire se peu….

cordialement,

Didier