Microsoft 365 Exécution VBA : "Erreur 7: dépassement de capacité de la mémoire"

[Bernard38]

Bonjour le forum,

Ce n'est pas une question, mais je vous fais part d'une expérience pour permettre à ceux qui rencontreraient ce problème de trouver une explication et une solution.
Nota : le message d'erreur "Erreur 7 : dépassement de capacité de mémoire" peut être réellement lié à un dépassement de capacité de la RAM en cas d'utilisation de variables ou manipulation d'objets trop volumineux mais dans mon cas c'est un problème "Excel".

Problème rencontré : lors de l'envoi d'une variable tableau vers une feuille Excel, j'ai un message d'erreur "Erreur 7 : dépassement de capacité de la mémoire". La variable tableau est à 2 dimensions et comprend 5592 lignes sur 39 colonnes. Le code plante à la ligne 255. Je ne comprends pas car j'ai déjà transféré des variables tableau bien plus importante.
Explication du plantage : après lancement en mode débogage, je m'aperçois que l'arrêt du code se situe donc sur la ligne 255 et spécifiquement sur la colonne 18 du tableau. C'est une colonne de dates. Je vais donc voir la cellule 255, 18 dans la base et là je m'aperçois que le fichier comporte un "bug" d'importation : cette cellule comporte un date négative automatiquement remplacée par Excel par des "########################". C'est cette cellule en anomalie qui crée un "dépassement de capacité de la mémoire" et non pas la taille de la variable tableau comme je le croyais initialement. Il a suffit de remplacer cette date erronée et le code n'a plus de plantage.
Enjoy

 

[job75]

Bonjour Bernard38,

Chez moi sur Excel 2019 cette macro :

Sub test()
Dim tablo
Cells(255, 18).NumberFormat = "dd/mm/yyyy"
Cells(255, 18) = -1000000
tablo = [A1].Resize(5592, 39)
End Sub

crée une erreur d'exécution 6 Dépassement de capacité.

Pas question de mémoire.

Notez que VBA accepte les dates négatives jusqu'à -657434 (01/01/100).

A+

 

[Bernard38]

Bonjour job75,

Bizarre, peut être qu'Office365 a rectifié l'erreur ...? Moi j'ai bien un dépassement de mémoire :

Blocage sur une date de naissance ressortant comme ça dans la cellule et affichée -1389 dans la barre de formules :

A+

 

[job75]

-1389 est une date acceptée par VBA, c'est le 11/03/1896 :

Sub a()
MsgBox CDate(-1389)
End Sub

 

[Bernard38]

re,
Je ne pense pas que le pb viennent du VBA mais d'Excel. Lors du téléchargement du fichier, la cellule contenant cette date négative affiche un nombre illimité de #####, j'ai déjà eu ce cas je ne sais plus où.
En fait mon code ne crée pas la date, il l'a récupère de la cellule pour faire un calcul de différence entre 2 dates. Tout fonctionne impecc jusqu'à cette cellule… (de toute façon une date de naissance négative est forcément une erreur dans ce cas)

 

[TooFatBoy]

Bonjour,

Est-ce que ce n'est pas différent pour une MsgBox et pour une cellule ?

Pour une cellule il faut être en calendrier depuis 1904, je crois, ou n'est-ce que pour les heures négatives ???

 

[Bernard38]

Bonjour TooFatBoy,
Je viens de faire un test, je pense que c'est un pb de corruption du format Excel lors de l'import : je tape -1389 dans la cellule D7 d'une feuille, je mets en format date 00/00/0000 et je lance le code de @job45 rectifié comme suit :

Sub a()
MsgBox CDate(Range("d7"))
End Sub

et j'ai bien la msgbox qui m'affiche 11/03/1986

 

[laurent950]

Bonsoir

Sub Test2()
MsgBox CDate(Range("d7"))
' Modifier D7
Range("D7").Value = Format(CStr(CDate(Range("d7"))), "dd/mm/yyyy")
' Copie dans E7 sans modifier D7
Range("E7").Value = Format(CStr(CDate(Range("d7"))), "dd/mm/yyyy")
End Sub

 

[job75]

Bonsoir laurent950,

Bien sûr mais le problème c'est de savoir pourquoi il y a l'erreur chez @Bernard38.

A+

 

[TooFatBoy]

Je viens de faire un test, je pense que c'est un pb de corruption du format Excel lors de l'import : je tape -1389 dans la cellule D7 d'une feuille, je mets en format date 00/00/0000 et je lance le code de @job45 rectifié comme suit :

Sub a()
MsgBox CDate(Range("d7"))
End Sub

et j'ai bien la msgbox qui m'affiche 11/03/1986

OK, tu as 11/03/1986 qui s'affiche dans la MsgBox, mais c'est surtout en D7 qu'il faut regarder ce qui s'affiche.

- As-tu toujours les # ?

- Si tu as toujours les #, as-tu essayé de passer en calendrier depuis 1904 ? Qu'affiche alors la cellule D7 ?

 

[TooFatBoy]

Je viens d'essayer sur mon PC (W10 Pro 64 22H2 et Excel 2016 32), et j'ai bien sûr aussi les #.
En passant en calendrier depuis 1904 j'ai alors -21/10/1907 qui s'affiche.

 

[laurent950]

Bonsoir @TooFatBoy
, @job75 , le forum
Poste #1
Problème rencontré : lors de l'envoi d'une variable tableau vers une feuille Excel
La colonne 18 du tableau contient une colonne de dates.
Cela viens du formatage des cellules Excel lorsque les données de cette colonne 18 est transféré de la variable tableau 2D vers la feuille Excel il y a donc un conflit de formatage avec (la ou les) cellules de colonne qui doit recevoir ces valeurs afin d'être convertie par Excel en format date d'ou le conflit expliqué en Poste #1.

la suites des 3 Codes ci-dessous expliques la logique du Poste #1
Nota : J'ai bien sur corrigé la Première ligne colonne 1 et 2 du tableau T
Test0 = L'erreur.
Test1 = Le résultat obtenue sera en format TEXTE.
Test2 = Le résultat obtenu sera en format DATE. (puis modifiable depuis excel)

Sub test0()
' Erreur d'exécution '1004'
' Erreur définie par l'application ou par l'objet
' ici l'erreur est définie par cela = CDate(-1389)
Dim T() As Variant
ReDim T(1 To 2, 1 To 2)
    T(1, 1) = CDate(-1389)
    T(1, 2) = CDate(-1389)
    T(2, 1) = -1389
    T(2, 2) = -1389
Cells(1, 1).Resize(UBound(T, 1), UBound(T, 2)) = T
End Sub
' --------------------------------------------------------------------------------------
Sub test1()
' Pour la suppression de l'erreur du test0
' Il n'y a plus Erreur d'exécution '1004'
' l'Erreur définie par l'application ou par l'objet a était corrigé ici
' Par la suppression de l'erreur avec / Trim = Trim(CDate(-1389))
' Mais le résultat sera du format Texte dans la cellule excel
Dim T() As Variant
ReDim T(1 To 2, 1 To 2)
    T(1, 1) = Trim(CDate(-1389))
    T(1, 2) = Trim(CDate(-1389))
    T(2, 1) = -1389
    T(2, 2) = -1389
Cells(1, 1).Resize(UBound(T, 1), UBound(T, 2)) = T
End Sub
' --------------------------------------------------------------------------------------
Sub test2()
' Pour substitué le resultat obtenue en formatage texte
' par un du formatage au format date dans la cellule
' Complément autest1
' Substition du texte par formatage = Format(CStr(CDate(-1389)), "dd/mm/yyyy")
' Alors le résultat sera obtenu en format dans la cellule excel
' Possibilité de changer le format de date directement depuis la feuille excel
' a essayer avec le resultat Obtenu en Test2
Dim T() As Variant
ReDim T(1 To 2, 1 To 2)
    T(1, 1) = Format(CStr(CDate(-1389)), "dd/mm/yyyy")  ' Formatage date 1
    T(1, 2) = Format(CStr(CDate(-1389)), "dddd/mmm/yy") ' Formatage Date 2
    T(2, 1) = -1389
    T(2, 2) = -1389
Cells(1, 1).Resize(UBound(T, 1), UBound(T, 2)) = T
End Sub

 

[TooFatBoy]

Bonsoir laurent950 : tu as raison, mes réponses sont totalement hors sujet. Désolé et merci

 

[Bernard38]

Je viens d'essayer sur mon PC (W10 Pro 64 22H2 et Excel 2016 32), et j'ai bien sûr aussi les #.
En passant en calendrier depuis 1904 j'ai alors -21/10/1907 qui s'affiche.

Salut TooFatBoy, salut Laurent950

@TooFatBoy Non je n'ai pas essayé car, j'avoue, quand j'ai repéré d'où venait l'erreur j'ai juste rectifié la date fausse dans le fichier d'extraction (il n'y en avait qu'une seule) et le pb a été réglé et comme je dois avancer sur le sujet pour le boulot... . Mais sinon, oui en 1904 ça m'affiche bien -21/10/1907.

@laurent950 Merci pour l'attention portée et pour le bout de code. par contre, je n'ai pas une erreur 1004 mais une erreur 7 "Mémoire insuffisante" , je ne pense pas qu'on génère le même pb... Et je n'arrive pas à comprendre à quoi sert le Trim, c'est bien une fonction de suppression d'espaces ? Je n'ai peut être pas compris correctement ton code... Et puis le soucis c'est qu'il faudrait que je teste chaque date.

Mais sinon, en vérifiant, je vous confirme que le code VBA accepte bien de charger la donnée dans la variable tableau sous son format date, je n'ai pas de plantage à ce niveau. C'est bien lors du transfert sur Excel qu'il y a pb. J'ai fait un debug.print sur l'adresse (255,18) de mon tableau de variables et j'ai bien la date 11/03/1896 en variable. Lors du transfert, cette date négative est transformée par Excel en un nombre infini de # qui provoque un dépassement de capacité. La macro s'arrête donc sur cette erreur.

A+

 

[job75]

Bonsoir Bernard38, le fil,

On tourne en rond.

Puisque l'erreur se produit lors de l'extraction déposez votre fichier avec le code VBA.

On vous dira si l'on constate la même erreur.

A+