Capacité maxi d'une variable de type variant ?

Version imprimable

30/06/2014, 12h46
Denis_67

Capacité maxi d'une variable de type variant ?

Bonjour,

J'utilise avec succès depuis un moment une variable de type variant pour stocker des destinataires de mails et ensuite envoyer la feuille courante à cette liste.
Les noms des destinataires sont lus par une loop dans des cellules où j'ajoute l'extension (toujours la même pour du courrier interne).

Aujourd'hui ma liste étant plus grande que d'habitude, je me retrouve bloqué.

Deux questions:
Quelle est la capacité maximum de la zone variant
Quelle autre type de variable faudrait-il utiliser, j'ai essayé string sans succès.

Lorsque je regarde le contenu de ma variable en mode espion je vois que ma boucle stocke les noms jusqu'à un certain point
après je ne vois pas les noms s'ajouter bien que la boucle tourne sans erreur. Mais c'est peut-être le mode espion qui ne permet pas de voir l'intégralité du contenu de la variable ?

J'ai regardé le tuto de Skyroad sur les variables, mais même si string peut contenir 2 miliards d'info, je plafonne.

Citation:

http://silkyroad.developpez.com/VBA/LesVariables/#LII-A

Merci pour vos suggestions.
Denis
30/06/2014, 13h14
TripleExcel

Pour du texte, tu ne peux utiliser que du string qui, bien que je n'en connais pas sa taille limite, est largement suffisant pour un texte.

Le type Variant autorise tout type de données. C'est son avantage. Mais je te le déconseille car il prend beaucoup de place en mémoire souvent pour rien et c'est Excel qui choisit le format qui lui semble le plus adapté.

On utilise Variant quand on ne connait pas le format (ce qui en soit est absurde) ou justement lorsque la valeur est susceptible de changer... de varier... passant par exemple au cours du traitement d'un état numérique à un état de chaîne de caractère. Ou lorsque tu souhaite utiliser une variable de type 'tableau" dans lequel sera stocké plusieurs type de données.

Et cela me permet de faire une transition avec ce qui pourrait être une solution, utiliser un tableau. A condition que j'ai bien compris le contexte de ta problématique (à ce propos, envoie un fichier c'est toujours plus parlant ;) ).
Si je comprends bien, dans ta variable, il y a plusieurs nom de fichier?

Merci pour ces éclaircissements.

Cette macro est dans mon classeur perso, il permet d'envolyer la feuille courante d'un classeur vers une liste de destinataires fugurant dans ma feuille.
Voici le code c'est un code que j'ai en partie récupéré et réorganisé.

Le problème est dans la variable Mes_destinataires en mode espion, je ne vois pas tous les destinataires quand il y en a de trop
et au moment où j'envoie le message, j'ai une erreur 1004 la méthode show de la classe dialogue a echoué. sur la ligne
Application.Dialogs(xlDialogSendMail).Show Mes_destinataires, Mon_Objet

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
50
 Public Mon_Objet
Public Ma_feuille
Public Mes_destinataires
Public Dest_deb
Sub envoie_feuille_mail()
' Macro1 Macro
' Envoir la feuille courante comme mail
Dim Mes_destinataires As String           'Les destinataires
Dim Mon_Objet As String                   'L'objet du message
Msg = "Indiquer la feuille à expédier"
Title = "Sélection de la feuille"
Ma_feuille = InputBox(Msg, Title, ActiveSheet.Name)
If Ma_feuille = "" Then
Exit Sub
End If
Msg = "Saisir l'objet du mail"
Title = "Objet"
Mon_Objet = InputBox(Msg, Title, ActiveCell.Value)
If Mon_Objet = "" Then
Exit Sub
End If
Msg = "Indiquer le début de la liste de destinataires sur votre feuille exemple A1 laisser une ligne blanche en fin de liste ! OU laisser vide pour renseigner dans Lotus directement"
Title = "DESTINATAIRES"
Dest_deb = InputBox(Msg, Title)
If Dest_deb <> "" Then
' je charge ma liste de destinataires
On Error GoTo erreur_feuille
    Sheets(Ma_feuille).Activate
 
    Range(Dest_deb).Select
Do While Not IsEmpty(ActiveCell)
    Mes_destinataires = Mes_destinataires & ActiveCell.Value & "@MonOrganisation.com" & ", "
    Selection.Offset(1, 0).Select
Loop
End If
' je copie la feuille dans un nouveau classeur
      ActiveWorkbook.Sheets(Ma_feuille).Copy
 
'je l'envoie et je referme sans sauvegarder
    Application.Dialogs(xlDialogSendMail).Show Mes_destinataires, Mon_Objet  
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
Exit Sub
erreur_feuille:
Dim monMsg
monMsg = "Feuille " & Ma_feuille & " inexistante ou mal orthographiée."
MsgBox Prompt:=monMsg
Exit Sub
End Sub

30/06/2014, 14h21
Daniel.C

Bonjour,

Après :

Code:

Loop

ajoute :

Debug.Print Len(Mes_destinataires)

Pour voir la longueur du string.
30/06/2014, 16h24
GreatTeacherOnizuka

Bonjour,

J'ai eu ce problème récemment, c'est parce que ta variable mes_destinataire dépasse les 450 caractères, c'est une limite fixée par excel d'après ce que l'on m'a dit.

Je remplis une variable de 1 000 000 caractères avec une variable (Var) longue de 5000 caractères :
Code:

1 2 3 4 5 6 7 8 9 Sub test() Dim txt As String, Var As String Var = Application.Rept("a", 5000) MsgBox "longueur de ""Var"" : " & Len(Var) Do While Len(txt) < 1000000 txt = txt & Var Loop MsgBox Len(txt) End Sub
Ajoute :

Code:

On Error GoTo 0

après :

Code:

Sheets(Ma_feuille).Activate

et refarde si, maintenant, tu as une erreur.

30/06/2014, 22h16
GreatTeacherOnizuka

Bonjour Daniel C.,

Oui on peut mettre jusqu'à 2 milliards de caractères dans un type String si mes souvenirs sont bons (à vérifier). Mais ce sont les formules Excel qui n'acceptent pas plus de 450 caractères.

J'ai pas regardé en détail mais il me semblait qu'il voulait copier justement le résultat de mes_destinataires dans un fichier Excel.

Mais peut être que je me trompe, peux tu afficher txt? J'ai pas essayé, j'essaierai quand j'ouvrirai un excel.

Bonne soirée.
01/07/2014, 00h58
TripleExcel
Ce qui m'étonne dans ton code c'est que tu déclares tes variables en public puis de nouveau dans ton programme.
Peut être il y a une interaction.
Pourquoi les déclarer publiquement?

Essaye de ne les déclarer que dans la macro pour voir si cela change quelque chose. Sinon, je te ferais un proposition différente.

1 ou 2 petites remarques/questions aussi, qui ne régleront pas ton problème mais qui sont plus pour toi :
ceci
Code:

1 2 3 If Ma_feuille = "" Then Exit Sub End If
écrit le ainsi ;)

Code:

If Ma_feuille = "" Then Exit Sub

aussi, je ne comprends pas l'intérêt de cette ligne

Code:

ActiveWorkbook.Sheets(Ma_feuille).Copy

Enfin, pense à décaler ton code pour qu'il soit plus lisible ;)
01/07/2014, 09h47
Daniel.C

Bonjour à tous,

@ GreatTeacherOnizuka :

La taille maxi d'une formule varie suivant les versions d'Excel (8 000 avec Excel 2013). Une cellule peut contenir 32 767 caractères.
01/07/2014, 12h03
TripleExcel

En fait j'ai tout relu mais c n'est pas ta variable qui pose problème mais ta boite de dialogue générée par ce code

Code:

Application.Dialogs(xlDialogSendMail).Show

C'est elle qui doit t'imposer une limite en nombre de destinataire.
Il faudrait donc arriver à trouver cette limite et faire l'envoie de mail en plusieurs fois au besoin.
01/07/2014, 12h10
illight

Comme tu l'as proposé (je crois que c'est toi), pourquoi ne pas utiliser une variable tableau ? :)

Bonjour,

si tu veux connaitre la taille max d 'un variant!

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
50
51
52
53
54
55
56
57
58
59
60
61
Sub TestVariant()
Dim t As Variant
Dim I As Long
On Error Resume Next
Do While 1 <> 2
    t = t & Space(9999999)
    If Err Then Exit Do
    I = I + 9999999
    DoEvents
Loop
Err.Clear
Do While 1 <> 2
    t = t & Space(999999)
    If Err Then Exit Do
    I = I + 999999
    DoEvents
Loop
Err.Clear
Do While 1 <> 2
    t = t & Space(99999)
    If Err Then Exit Do
    I = I + 99999
    DoEvents
Loop
Err.Clear
Do While 1 <> 2
    t = t & Space(9999)
    If Err Then Exit Do
    I = I + 9999
    DoEvents
Loop
Err.Clear
Do While 1 <> 2
    t = t & Space(999)
    If Err Then Exit Do
    I = I + 999
    DoEvents
Loop
Err.Clear
Do While 1 <> 2
    t = t & Space(99)
    If Err Then Exit Do
    I = I + 99
    DoEvents
Loop
Err.Clear
Do While 1 <> 2
    t = t & Space(9)
    If Err Then Exit Do
    I = I + 9
    DoEvents
Loop
Err.Clear
Do While 1 <> 2
    t = t & Space(1)
    If Err Then Exit Do
    I = I + 1
    DoEvents
Loop
MsgBox I
End Sub

Merci à tous pour votre intéret et vos propositions à mon problème.

Dans l'ordre :

- Je déclare mes variables en public pour que la macro s'exécute dans tous les cas, j'ai supprimé la double déclaration merci pour ce point.
- Merci pour la correction /simplifacation de syntaxe du exit sub.
- Je copie la feuille dans un nouveau classeur pour pouvoir n'envoyer que cette feuille, Je n'ai pas trouvé d'autre solution.

Enfin la réponse à mon problème est dans la réponse de TripleExcel, la longueur maxi de la dialogue box pour le send mail est de 255 caractères (tiens donc !)

J'ai donc contourné la difficulté par un warning si j'ai trop de destinataires, je ne prends que les 255 premiers caractères et je demande à l'utilisateur de rajouter les suivant lui même dans la messagerie.
ça donne:
Code:

1 2 3 4 5 If Len(Mes_destinataires) > 255 Then Mes_destinataires = Left(Mes_destinataires, 255) MsgBox "Trop de destinataires ajouter les destinataires après" & Right(Mes_destinataires, 20) & " à la main" End If

Voici ton code modifié pour l'utilisation d'un tableau (testé avec 400 destinataires :

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
50
51
52
53
Public Mon_Objet
Public Ma_feuille
Public Dest_deb
Sub envoie_feuille_mail()
' Macro1 Macro
' Envoir la feuille courante comme mail
Dim Mes_destinataires() As String            'Les destinataires
Dim Mon_Objet As String                   'L'objet du message
Dim Ctr As String
Ctr = -1
Msg = "Indiquer la feuille à expédier"
Title = "Sélection de la feuille"
Ma_feuille = InputBox(Msg, Title, ActiveSheet.Name)
If Ma_feuille = "" Then
Exit Sub
End If
Msg = "Saisir l'objet du mail"
Title = "Objet"
Mon_Objet = InputBox(Msg, Title, ActiveCell.Value)
If Mon_Objet = "" Then
Exit Sub
End If
Msg = "Indiquer le début de la liste de destinataires sur votre feuille exemple A1 laisser une ligne blanche en fin de liste ! OU laisser vide pour renseigner dans Lotus directement"
Title = "DESTINATAIRES"
Dest_deb = InputBox(Msg, Title)
If Dest_deb <> "" Then
' je charge ma liste de destinataires
On Error GoTo erreur_feuille
    Sheets(Ma_feuille).Activate
 
    Range(Dest_deb).Select
Do While Not IsEmpty(ActiveCell)
    Ctr = Ctr + 1
    ReDim Preserve Mes_destinataires(Ctr)
    Mes_destinataires(Ctr) = ActiveCell.Value & "@MonOrganisation.com"
    Selection.Offset(1, 0).Select
Loop
End If
' je copie la feuille dans un nouveau classeur
      ActiveWorkbook.Sheets(Ma_feuille).Copy
 
'je l'envoie et je referme sans sauvegarder
    Application.Dialogs(xlDialogSendMail).Show Mes_destinataires, Mon_Objet
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
Exit Sub
erreur_feuille:
Dim monMsg
monMsg = "Feuille " & Ma_feuille & " inexistante ou mal orthographiée."
MsgBox Prompt:=monMsg
Exit Sub
End Sub

04/07/2014, 12h17
Denis_67

Heureuse surprise de Daniel,

J'aurais dû suivre sa proposition de tableau, mais comme j'avais pensé à tord que le problème venait de la fenêtre de dialogue qui limitait à 255 caractères, j'avias cloturé le ticket.

Donc cela fonctionne parfaitement.

Merci à tous et n'hésitez pas à utiliser ce petit utilitaire qui permet de n'envoyer qu'une feuille du fichier Excel sans enregistrer un nouveau fichier.
Le petit inconvénient est que comme Excel ne nome un fichier que lors de l'enregistrement le fichier joint s'apelle Classeur1.xls.

Tous mes projets sont suivis dans des fichiers Excel avec une liste de tache, après chaque réunion j'envoie la liste de tache avec les statuts et mon compte rendu est fait. Cchacun sait ce qu'il à faire et pour quand.

Cela évite de charger les messageries avec des gros fichiers excel.
Les fichiers complets restent accessibles sur le serveur de l'entreprise.

Encore une fois merci et bon week end.
Denis8-)