Erreur aléatoire VBA "La méthode _ de l'objet Range a échoué". Pb mémoire ?

Le 19/08/2021 à 08:38

Bonjour,

J'ai rédigé un code VBA permettant de lire une grande quantité de données d'un fichier codé en binaire pour les inscrire dans mon fichier Excel.

Le code fonctionne bien, cependant si je tente de l'exécuter plusieurs fois, au bout d'un certain nombre (aléatoire) d'exécutions je vais obtenir le message d'erreur "la méthode ___ de l'objet Range a échoué". Cette erreur peut apparaître de manière aléatoire à n'importe quel endroit dans mon code où j'utilise une méthode Range.___. Un exemple qui arrive souvent est "la méthode Value de l'objet Range a échoué"

Pour faire disparaître cette erreur il faut que je ferme tous mes fichiers Excel ouverts et que j'ouvre mon fichier à nouveau pour ré-exécuter le code. Cela me fait penser à un problème de mémoire.

Si l'un de vous a déjà rencontré ce genre d'erreur, ou connaît des méthodes pour inspecter l'espace mémoire disponible pour Excel/VBA je suis preneur.

Merci d'avance !

Quelques infos supplémentaires :

  • Excel 2013
  • Les fichiers Excel peuvent faire jusqu'à 10Mo avec pas mal de liaisons internes, de mises en forme conditionnelles et de validations des données
  • Le code VBA fait plusieurs milliers de lignes
  • Les Range sur lesquelles je fais des opérations font au maximum quelques centaines de lignes et pas plus de 10 colonnes
  • Je n'utilise aucune variable globale
  • L'erreur arrive avec ou sans Application.ScreenUpdating, Application.EnableEvents, Application.Calculation (Manuel ou Automatic)
  • J'ai 32Go de RAM installée, le gestionnaire des tâches m'indique entre 200Mo et 500Mo de mémoire utilisée par Excel
  • Je n'ai malheureusement pas la possibilité de joindre une copie du fichier Excel en question
Le 19/08/2021 à 09:00

J'ai déjà rencontré ce type de pb parce que je n'avais pas le réflexe de libérer mes objets de leur contenu après emploi (un FSO, une plage, une cellule, une feuille UF, un classeur, etc.). A chaque déclaration d'un objet doit correspondre une libération systématique de la mémoire en fin de procédure ou de fonction. Si les objets sont nombreux, il est bon de mettre cette libération dans une fonction. Un truc du genre :

Set monObjet = Nothing
Le 19/08/2021 à 09:10

Bonjour et bienvenue sur le forum

Bonjour à tous

Tu peux aussi essayer de libérer de la mémoire en insérant à l'endroit le plus judicieux l'instruction :

Application.CutCopyMode = false

Elle vide le presse papier

Bye !

Le 20/08/2021 à 13:19

Bonjour, merci de vos réponses Optimix et gmb

Malheureusement aucune des deux n'a fonctionné pour moi

Je me suis rendu compte que même si l'erreur apparaissait, l'opération d'affectation Range.Value = __ se réalisait tout de même sur mon fichier Excel. J'ai donc résolu le problème en ignorant l'erreur :

Dim maVariable() as Double
ReDim maVariable(1 to 100)

'Affectation des valeurs dans maVariable

On Error Resume Next
With Sheets("MaFeuille")
        .Range(.Cells(1,1), .Cells(100,1)).Value = Application.Transpose(maVariable)
End With
On Error GoTo 0

J'aimerais tout de même savoir d'où vient l'erreur si vous avez une idée pour éviter de l'ignorer bêtement

Bonne journée !

Le 20/08/2021 à 13:56

Bonjour,

J'aimerais tout de même savoir d'où vient l'erreur si vous avez une idée pour éviter de l'ignorer bêtement

Oui, effectivement, comme je vous comprends.

Malheureusement, sans le code complet et sans fichier, ça va être extrêmement difficile de vous répondre...

a minima le code...

Le 03/09/2021 à 14:49

Rebonjour,

L'erreur n'est malheureusement toujours pas corrigée, quand je l'ignore quelque part, elle resurgit ailleurs dès que la classe Range est sollicitée. Quand l'erreur est survenue une fois, elle reviendra à chaque procédure où un Range est appelé jusqu'à ce que je relance Excel.

Voici un emplacement simple où l'erreur 1004 survient, sur la méthode ClearContents :

'Reinitialise la feuille "Profils"
Public Sub ReinitialiserProfils()
    With Sheets(nomProfils)
        .Range(.Cells(ligneNomProfil, 2), .Cells(ligneNomProfil, 1000)).ClearContents
        .Range(.Cells(ligne1Profil, 2), .Cells(ligne1Profil + 10000, 1000)).ClearContents
    End With
End Sub

où les constantes publiques de localisation des lignes/colonnes sont définies comme suit :

'Constantes de la feuille "Profils"
Public Const nomProfils As String = "Profils"
Public Const ligneNomProfil As Long = 5
Public Const ligne1Profil As Long = 9

En mode débogage, quand je survole les variables leur valeur apparaissent correctement.

J'ai déjà essayé en activant d'abord le workbook et le worksheet, et en déclarant un range de cette manière, mais toujours la même erreur :

Dim rg as Range
With Sheets(nomProfils)
    set rg = .Range(.Cells(ligneNomProfil, 2), .Cells(ligneNomProfil, 1000))
    rg.ClearContents
End With
set rg = Nothing

Dans la fenêtre variables locales, rg apparait comme ceci :

image

Celui qui trouve d'où vient l'erreur me sauverait la vie ! :)

Merci d'avance

Le 03/09/2021 à 15:23

Bonjour Jacks

Et si au lieu de mettre

 With Sheets(nomProfils)

Vous mettiez 

 With ThisWorkbook.Sheets(nomProfils)

Rassurez nous, vous avez bien une feuille nommée "Profils" ?

@+

Le 03/09/2021 à 17:18

Bonjour à tous,

tout à fait au hasard mais qui ne tente rien n'a rien.
A-priori tu as l'air de manipuler beaucoup de données sur tes feuilles.
Tu peux de temps en temps lui laisser le temps de finir son boulot surtout si tu as des formules lourdes.

Attente fin de calcul :

    Do Until Application.CalculationState = xlDone
        DoEvents
    Loop

Si c'est juste des écritures, tu peux oublier
eric

PS : DoEvents ralentis beaucoup, vas-y avec parcimonie...

Le 06/09/2021 à 08:44

Bonjour BrunoM45 et eriiic, merci pour votre aide

BrunoM45 : Oui je vous rassure BrunoM45 j'ai bien une feuille "Profils". D'ailleurs le code peut fonctionner plusieurs fois avant que le bug apparaisse. J'ai essayé en ajoutant "ThisWorkbook" mais le bug est toujours là.

eriic : Oui il y a en effet pas mal de calcul. J'ai essayé votre astuce, et aussi en passant les calculs à "Manuel" mais rien n'y fait.

Le 07/09/2021 à 00:23

Bonsoir,

et si tu le fermes systématiquement avant de le relancer, ça plante toujours ?
Si c'est suffisant, on pourrait imaginer un fichier principal qui le ferme si présent et l'ouvre à chaque nouvelle demande.
eric

Le 07/09/2021 à 02:15

Bonjour à tous,

Quelque chose que je ferais, c'est de déclarer une variable objet plutôt que de mettre des "With" (A chaque fois que ça bug)

'Reinitialise la feuille "Profils"
Public Sub ReinitialiserProfils()
  Dim Sht As Worksheet
  Set Sht = ThisWorkbook.Sheets(nomProfils)
  Sht.Range(Sht.Cells(ligneNomProfil, 2), Sht.Cells(ligneNomProfil, 1000)).ClearContents
  Sht.Range(Sht.Cells(ligne1Profil, 2), Sht.Cells(ligne1Profil + 10000, 1000)).ClearContents
  Set Sht = Nothing
End Sub

Je l'accorde, ce n'est pas logique ou normal

@+

Le 07/09/2021 à 11:57

Bonjour,

eriic : si je ferme toutes les instances Excel avant de ré-ouvrir le fichier ça fonctionne mieux. Ça va plus loin dans l'exécution de la procédure et parfois ça arrive au bout, parfois le code tourne sans problème plusieurs fois, mais ça ne fonctionne pas systématiquement

BrunoM45 : j'ai essayé mais ça ne fonctionne pas . Je n'ai toujours pas accès à toutes les propriétés/méthodes de l'objet alors qu'il est bien défini.

Quelque chose qui pourrait peut-être être une piste, dans le code VBA cette fonction est exécutée et charge une assez grande quantité de données en mémoire (25Mo environ) d'un fichier binaire vers un type défini par l'utilisateur. Cette fonction marche bien (même si je la suspecte de causer un "Microsoft Excel a cessé de fonctionner" de temps en temps). Mon ressenti est que ça prend peut-être de la place en mémoire qui ne se vide pas correctement. Peut-être est-ce lié au problème d'accès aux propriétés des Range qui semble provenir de la mémoire ?

Function LireBin(chemin As String) As MonUDT
    'Renvoie les données du fichier binaire au format "MonUDT"
    Dim Version As Long
    Dim x As Integer

    On Error GoTo HandleError

    'Chargement du fichier
    x = FreeFile
    Open chemin For Binary Access Read As #x
        Get #x, , Version
        Get #x, , LireBin
    Close #x
    Exit Function

HandleError:
    MsgBox Err.Description
    Close
End Function

Merci pour votre aide

Le 07/09/2021 à 12:27

Bonjour,

en théorie tu devrais pouvoir lire 2 Go, 25 Mo n'est pas tant que ça. Mais ça me donne une idée.
Si tu es sur office 32 bits, peux-tu faire un test en office 64 bits ?

Et une autre : tu as Erase pour nettoyer les gros tableaux lorsqu'ils ne servent plus.
Si tu en as des énormes, ca fera toujours ça de récupéré.

Sinon une fenêtre de VBE peu connue mais parfois utile dans ces cas c'est la 'Pile des appels...' Ctrl+L
Tu pourrais faire des captures de la pile au moments des plantages, et par recoupements voir s'il n'y a pas un point commun dans les appels précédents qui pourraient aiguiller ta recherche.
Je pense plus que c'est une accumulation et que les causes peuvent être très loin dans la pile mais bon, autant jeter un oeil.
eric

PS : 1004 peut recouvrer plusieurs types de causes.
Celles que j'avais repérées des fois que ça te donne une idée. Tu es sans doute hors de ces cas mais tu vois que des fois il faut élargir la recherche:

1) code dans Feuil1 pour un .sort en Feuil2. Mettre le code dans un module

2) 'Close' of object _Workbook failed sur fermeture d'un classeur à partir d'un userform :

Bug excel, décharger le userform avant de fermer le classeur

Unload Me : opened_workbook.Close

3) feuille protégée ou masquée (.Sort)

Et les variables utilisées dans les Cells() de tes .Range peuvent aussi être en cause. Si pas déjà fait contrôle que leur évaluation est correcte et correspondent à un existant.

Rechercher des sujets similaires à "erreur aleatoire vba methode objet range echoue memoire"