Petite explication - Do While

Salut à tous, j'aurais besoin d'une petite explication à propos de mon code :

Private Sub Workbook_Open()
Dim i As Integer
For i = 2 To 12
If Dir("C:\Nouveau dossier\" & Cells(i, 1) & "*.txt") = "" Then
  Cells(i, 2).Interior.ColorIndex = 3 'ROUGE
Else
  Cells(i, 2).Interior.ColorIndex = 4 'VERT
End If
Next
End Sub

L'idée c'est de colorier une cellule en vert si le fichier est trouvé, et en rouge si ce n'est pas le cas.

Je travail petit à petit, et vérifie que tout marche, jusque là, pas de soucis.

Ensuite, j'ai voulu optimiser mon code, en indiquant, tant que la cellule "i" en colonne A n'est pas vide on continue, du coup j'ai voulu retirer "For" et mettre du "Do While" :

Private Sub Workbook_Open()
Dim i As Integer
Do While Cells(i, 1) <> ""
If Dir("C:\Nouveau dossier\" & Cells(i, 1) & "*.txt") = "" Then
  Cells(i, 2).Interior.ColorIndex = 3 'ROUGE
Else
  Cells(i, 2).Interior.ColorIndex = 4 'VERT
End If
Loop
End Sub

C'est dans le même principe, mais je bride pas le nombre de ligne à 12 et surtout, imaginons que j'ai 200 données et que je mets du For i = 2 To 500, le code va s'effectuer sur 300 lignes pour rien puisque c'est vide, puis même si j'ai 200 lignes aujourd'hui, j'en aurais peut-être 300 demain donc je vais pas modifier le code systématiquement.

Au moins là, dès que la cellule est vide (donc pas plus de donnée) alors on s'arrête.

Le soucis, c'est que j'ai l'erreur "1004 - Erreur définie par l'application ou l'objet.".

Sa marchais en utilisant "For" mais pas "Do While", j'ai l'impression que ça se joue à un détail mais je ne sais lequel.

Et j'ai un doute, avec la boucle "For", l'incrémentation se fait toute seul, avec While non, mais avec Do While ?

Cordialement

Bonjour Fred,

voici ton code VBA optimisé :

Option Explicit

Private Sub Workbook_Open()
  Dim c As Byte, i As Byte
  For i = 2 To 12 'couleur c : 3 = ROUGE ; 4 = VERT
    c = IIf(Dir("C:\Nouveau dossier\" & Cells(i, 1) & "*.txt") = "", 3, 4)
    Cells(i, 2).Interior.ColorIndex = c
  Next i
End Sub

note : avec Do .. While, c'est à toi d'incrémenter i

dhany

Salut, alors tout d'abord merci pour l'optimisation, et pour la réponse concernant l'incrémentation.

Je ne connaissais pas la fonction "Iff", merci pour cette découverte !

En revanche, j'ai du mal m'exprimer pour le reste.

En effet, imaginons que j'ai 12 lignes aujourd'hui, 20 demain, et 100 dans quelques jours.

Avec la fonction "For", c'est problématique car on bride le nombre de ligne, c'est pour ça que j'aimerais passer par la fonction "Do While", et arrêter la boucle dès que "Cells( i , 1 ) = " ".

J'étais donc partie dans l'idée de dire, "Do While Cells( i , 1 ) <> "" " on exécute, mais j'ai l'erreur 1004 qui se présente à moi.

Cordialement

autre version, avec For (et sans bridage de ligne) :

Option Explicit

Private Sub Workbook_Open()
  Dim dlig&, i&, c As Byte: Application.ScreenUpdating = 0
  dlig = Cells(Rows.Count, 1).End(3).Row
  For i = 2 To dlig 'couleur c : 3 = ROUGE ; 4 = VERT
    With Cells(i, 1)
      If .Value <> "" Then
        c = IIf(Dir("C:\Nouveau dossier\" & .Value & "*.txt") = "", 3, 4)
        .Offset(, 1).Interior.ColorIndex = c
      End If
    End With
  Next i
End Sub

dhany

Salut à toi, ça marche merci.

Je vais t'embêter un petit peu, afin de comprendre quelques petites notions dans l'idée de progresser si tu le veux bien !

Dim dlig&, i&, c As Byte: Application.ScreenUpdating = 0

Pourquoi déclarer "dlig" en Long, et "c" en Byte ? Ce sont toutes les deux des variables numériques ? J'ai mon bouquin et je vois la différence entre ces deux variables, mais dans la finalité, qu'est-ce que ça change si je l'ai déclare en Long ou en Byte toutes les deux ?

dlig = Cells(Rows.Count, 1).End(3).Row

Alors j'ai du mal à traduire cette ligne, on compte les lignes en colonne A, jusqu'en bas.

Mais la notion "End(3)" ne me parle pas du tout, surtout le "(3)".

Next i

Pourquoi faut-il indiquer la variable, l'incrémentation se fait toute seul non ? Ou alors c'est pour autre chose ?

Cordialement

Bonjour, Salut Dhany !

Un petite remarque en passant : Cells(i, 1) non qualifiée !

L'incidence habituelle est que VBA cherche la feuille active, donc l'exécution sera moins rapide, mais de plus ici tu ne sais pas quelle sera la feuille active !

Puisque tu exécutes à l'ouverture du classeur, la feuille active pourra être n'importe quelle feuille du classeur.

Sauf s'il n'en a qu'une et jusqu'à ce que tu en ajoutes une autre, ayant oublié la chose...

Il te faut donc au minimum l'activer au démarrage, c'est d'ailleurs une pratique courante à l'ouverture du classeur d'ouvrir sur une feuille d'accueil.

Mais cela ne devrait pas t'empêcher de qualifier ton expression en la référant à sa feuille (qui après tout pourrait être distincte de la feuille dévolue à l'affichage lors de l'ouverture...). Tu auras ainsi un code optimisé sur ce point, et insusceptible de te jouer des tours dans l'avenir quoi que tu fasses (ou presque )

Deuxième remarque mineure : l'utilisation de ColorIndex : rien à redire si on travaille sous 2003 ou version antérieure, la palette prévalait alors car Excel ne pouvait afficher que ces 56 couleurs, mais depuis Excel 2007, il peut en afficher plus de 16 millions (256^3) et il est souhaitable de s'harmoniser avec les possibilités de l'outil.

En outre, comme tu choisis dans les 8 couleurs de base disposant de constantes VBA, tu peux écrire :

...Color = vbRed ou ...Color = vbGreen, à la fois plus court et directement parlant...

Cordialement.

@Fred

réponse à ta question n° 1 :

* pour la couleur c : j'utilise seulement les valeurs 3 et 4 ; ces 2 valeurs tiennent dans un Byte (1 octet) : 0 à 255

* pour un n° de ligne, il faut utiliser un Long (4 octets) car ligne maxi avec Excel 2007 (et ultérieur) = 1 048 576

note que si y'a utilisation de ActiveCell.Row le n° ligne peut être très grand si la cellule active est très en bas !

dans ta boucle du 1er code For i = 2 to 12 : i est utilisé comme n° ligne, mais on est sûr qu'un Byte suffit

l'intérêt de choisir le bon type est d'utiliser celui qui occupe le moins de place mémoire : 1 octet pour Byte au lieu de 4 octets pour Long ; en plus, ça accélère le code ; on pourrait utiliser un seul type : le type Variant mais il occupe encore plus de place (au moins 16 octets, et sa taille est variable selon ce qu'il va contenir)

dhany

Salut MFerrand,

Alors pour déclarer la feuille, c'est pas faux, ou alors mettre le code dans la "Feuil1", comme ça, si le nom de la "Feuil1" change, plus de soucis ? Parce qu'en faisant :

With Sheets("Feuil1")
End With

Sa m'embête, on bride par rapport au nom, et son on fait :

With Sheets(1)
End With

On bride pas par rapport au nom mais par rapport à la position de la feuille, alors quel est le mieux ?

Pour ta deuxième remarque, c'est mon manque de connaissance qui se fait ressentir.

Je passais par la méthode ColorIndex car elle me semblait bien plus simple que celle .Interior.Color = RGB [...], mais tu viens de m'en faire découvrir une troisième qui semble la plus simple, merci.

dhany a écrit :

@Fred

réponse à ta question n° 1 :

* pour la couleur c : j'utilise seulement les valeurs 3 et 4 ; ces 2 valeurs tiennent dans un Byte (1 octet) : 0 à 255

* pour un n° de ligne, il faut utiliser un Long (4 octets) car ligne maxi avec Excel 2007 (et ultérieur) = 1 048 576

note que si y'a utilisation de ActiveCell.Row le n° ligne peut être très grand si la cellule active est très en bas !

dans ta boucle du 1er code For i = 2 to 12 : i est utilisé comme n° ligne, mais on est sûr qu'un Byte suffit

l'intérêt de choisir le bon type est d'utiliser celui qui occupe le moins de place mémoire : 1 octet pour Byte au lieu de 4 octets pour Long ; en plus, ça accélère le code ; on pourrait utiliser un seul type : le type Variant mais il occupe encore plus de place (au moins 16 octets, et sa taille est variable selon ce qu'il va contenir)

dhany

Très bien, merci pour ta réponse, sa me paraît clair ainsi !

Si jamais ta le temps de répondre aux deux autres, à l'occasion, merci d'avance !

@Fred

réponse à ta question n° 2 :

dlig = Cells(Rows.Count, 1).End(3).Row : c'est idem que dlig = Cells(Rows.Count, "A").End(xlUp).Row

ça « calcule » quelle est la dernière ligne selon la colonne A

Rows.Count est le nombre de lignes : 1 048 576 ; donc Cells(Rows.Count, 1) est la cellule A1048576

à partir de cette cellule, si tu fais manuellement Ctrl ↑, ça va monter vers le haut et s'arrêter sur la 1ère cellule non vide rencontrée : c'est donc ça qui permet de connaître quelle est la dernière ligne utilisée d'un tableau ; bien sûr, si toute la colonne A est vide, ça s'arrêtera en A1 même si A1 est vide ; l'équivalent VBA de Ctrl ↑ est .End(xlUp) ou .End(3)

dhany

@Fred

réponse à ta question n° 3 :

effectivement, sans indiquer la variable, l'incrémentation se fait tout seul ; mais pour cela, VBA doit chercher à quel For correspond le Next : c'est particulièrement vrai pour plusieurs boucles For imbriquées, mais même pour une seule boucle For.

indiquer la variable permet d'éviter à VBA cette recherche, donc ça accélère l'exécution du code VBA ; perso, je mets systématiquement la variable du Next.

dhany

Bonjour,

pour t'expliquer ton erreur initiale : tu n'initialisais pas i. Manque avant le Do While :

i=12

eric

Re,

Tes questions sont pertinentes, et je n'ai pas les même choix que Dhany sur un certain nombre de points, que je soumets volontiers à ta réflexion.

Je n'agglutine jamais une instruction exécutable sur une ligne de déclaration, la distinction entre code exécutable ou non est fondamentale et on doit pouvoir à mon avis faire la distinction visuellement sans hésitation.

Traduire les valeurs booléennes par 0 ou -1 est également moins parlant visuellement que False ou True, et oblige souvent à réfléchir si la valeur qu'on a placée est numérique ou booléenne.

Je trouve que les constantes VBA ont du bon à la lecture du code, xlUp est immédiatement plus parlant que 3. Je regrette simplement que certains noms de constantes soient un peu long, mais je fais avec.

Les typages de variables ont une double incidence : sur la mémoire nécessaire à leur stockage d'une part, sur la rapidité d'exécution d'autre part.

Si on ne type pas, le type est Variant, plus lent d'accès qu'une variable typée. On a donc intérêt à typer, autant que possible sachant que divers cas d'utilisation exigent de ne pas typer...

Pour les variables numériques, les types Byte, Integer, Long nécessitent une mémoire croissante (et évidemment Single, Double ou Currency).

On choisit généralement en fonction de l'utilisation pour ne pas risquer un dépassement de pile...

Pour le type Byte, il est tout de même un peu limité (0 à 255, n'admettant pas de nombre négatif). Pour ma part, je ne l'utilise pas car ce type n'était pas admis dans la première version de VBA livrée avec Excel 5... Et les tests faits par Eriiic ont montré une lenteur nette de ce type par rapport aux autres, ce qui n'incite guère à son utilisation.

Entre les types Integer et Long, la rapidité semble dépendre du contexte d'utilisation, avec un net avantage pour le type Long dans un certain nombre de cas. Je n'en suis pas venu à l'utiliser systématiquement, ça me chiffonne toujours un peu au moment de choisir mais dès que le nombre qu'on doit atteindre commence à devenir un peu conséquent je privilégie le type Long, et reste en Integer pour des dimensions limitées et non extensibles.

Il reste possible de tester sur des cas concrets d'utilisation la rapidité induite par le type pour opérer le choix final.

La répétition après un Next de la variable de boucle utilisée. On peut certes s'en abstenir, mais cela a une utilité visuelle pour savoir à quelle boucle appartient ton Next. Voyant un Next, même si ton code est parfaitement indenté (ce qui n'est pas ton cas ! ) tu ne remontes pas toujours immédiatement au For correspondant. Cette indication demeure donc visuellement utile et facilite la recherche d'erreurs.

Note bien que tout ce qui relève d'indications visuelles n'a d'intérêt que pour celui qui intervient sur le code, et surtout lorsqu'il est déjà écrit, pour pouvoir l'interpréter sans délai et travailler plus rapidement. VBA s'en fout, cela ne l'empêche pas d'exécuter tant qu'il peut exécuter...

Cordialement.

J'ai du mal à suivre...

ou alors mettre le code dans la "Feuil1",

D'une part il est préférable de réserver les modules d'objets aux évènements, et éventuellement quelques procédures qui y sont liées, cela facilitera ton travail sur le code.

D'autre part le fait que tu soies sur un module de feuille ne te dispense de qualifier les expressions, VBA ira toujours chercher quelle est la feuille active, il ne déduit pas que c'est la feuille à laquelle le module est dédié, et heureusement car rien n'empêche une autre feuille d'être active.

Ceci dit, dans un module de feuille la qualification est aisée et rapide : Me désigne la feuille sans ambiguïté, il suffit de l'utiliser.

Nom de feuille ou rang de la feuille : le choix dépend de l'élément qui a la plus grande stabilité et c'est à toi d'en décider.

Certains préfèrent travailler avec le nom de code (CodeName), plus stable, même si on peut le changer, car on ne le fait en général pas. Le nom de code s'utilise directement :

    With Feuil1

    End With

Par contre son utilisation ne peut se faire en utilisant une variable String comme pour le nom, et si on doit passer par une variable l'utilisation en devient plus malaisée.

Autre possibilité : accéder à la feuille à partir d'une plage nommée de la feuille. Supposons que tu aies nommé une plage Plage (pour faire simple),

    With [Plage].Worksheet  'ou [Plage].Parent, mais la 1re expression est plus parlante

    End With

Et si tu as un tableau Excel dans une feuille, tu peux utiliser son nom de la même façon.

Note aussi au passage que l'utilisation de noms pour gérer des tableaux ou bases de données permet de s'affranchir de toute nécessité de référence à la feuille.

Cordialement.

@MFerrand

en fait, l'utilisation de Next i au lieu de Next n'a pas qu'une utilité visuelle !

en interne, quand VBA « rencontre » Next i, il note l'adresse de branchement à l'instruction immédiate qui suit le For :

pour la variable i : adresse de branchement pour Next i = tel label en RAM (mémoire vive).

donc ensuite, il n'a plus besoin de chercher cette adresse de branchement : il utilise celle qu'il a noté dans son propre « bloc-note ».

* avec For i = 1 To 1 000 .. Next i : l'adresse de branchement sera calculée une seule fois

* avec For i = 1 To 1 000 .. Next : l'adresse de branchement sera calculée 1 000 fois !

voilà pourquoi ça accélère le code VBA de noter après le Next la variable qui correspond au For.

dhany

@Dhany:

Si tu as raison sur ce point, cela renforce l'utilité de mentionner la variable...

J'avoue que je ne m'étais pas penché sur ce point, pensant qu'exécutant la boucle dont les paramètres sont mémorisés, la rencontre du Next suffisait pour boucler sans recherche particulière...

Cordialement.

Re,

j'ajouterai pour Next i que le compilateur contrôle, et annonce une erreur si on met Next j si la boucle en cours est i.

L'erreur peut tout aussi bien être au niveau du For que du Next.

L'alerte pousse à contrôler ce qui serait passé inaperçu si on indique pas la variable. Ce type d'erreur peut être fastidieux à détecter, le cerveau lira toujours ce qu'il a envie voir.

eric

suite...

pour ce qui est du gain de temps je serais plus circonspect.

Aucune différence sur une boucle de 100 millions...

Sub test()
    Dim i As Long, t As Single
    t = Timer
    For i = 1 To 100000000
    Next i
Debug.Print Timer - t

    t = Timer
    For i = 1 To 100000000
    Next
Debug.Print Timer - t
End Sub

eric

MFerrand a écrit :

Re,

Tes questions sont pertinentes, et je n'ai pas les même choix que Dhany sur un certain nombre de points, que je soumets volontiers à ta réflexion.

Je n'agglutine jamais une instruction exécutable sur une ligne de déclaration, la distinction entre code exécutable ou non est fondamentale et on doit pouvoir à mon avis faire la distinction visuellement sans hésitation.

Traduire les valeurs booléennes par 0 ou -1 est également moins parlant visuellement que False ou True, et oblige souvent à réfléchir si la valeur qu'on a placée est numérique ou booléenne.

Je trouve que les constantes VBA ont du bon à la lecture du code, xlUp est immédiatement plus parlant que 3. Je regrette simplement que certains noms de constantes soient un peu long, mais je fais avec.

Les typages de variables ont une double incidence : sur la mémoire nécessaire à leur stockage d'une part, sur la rapidité d'exécution d'autre part.

Si on ne type pas, le type est Variant, plus lent d'accès qu'une variable typée. On a donc intérêt à typer, autant que possible sachant que divers cas d'utilisation exigent de ne pas typer...

Pour les variables numériques, les types Byte, Integer, Long nécessitent une mémoire croissante (et évidemment Single, Double ou Currency).

On choisit généralement en fonction de l'utilisation pour ne pas risquer un dépassement de pile...

Pour le type Byte, il est tout de même un peu limité (0 à 255, n'admettant pas de nombre négatif). Pour ma part, je ne l'utilise pas car ce type n'était pas admis dans la première version de VBA livrée avec Excel 5... Et les tests faits par Eriiic ont montré une lenteur nette de ce type par rapport aux autres, ce qui n'incite guère à son utilisation.

Entre les types Integer et Long, la rapidité semble dépendre du contexte d'utilisation, avec un net avantage pour le type Long dans un certain nombre de cas. Je n'en suis pas venu à l'utiliser systématiquement, ça me chiffonne toujours un peu au moment de choisir mais dès que le nombre qu'on doit atteindre commence à devenir un peu conséquent je privilégie le type Long, et reste en Integer pour des dimensions limitées et non extensibles.

Il reste possible de tester sur des cas concrets d'utilisation la rapidité induite par le type pour opérer le choix final.

La répétition après un Next de la variable de boucle utilisée. On peut certes s'en abstenir, mais cela a une utilité visuelle pour savoir à quelle boucle appartient ton Next. Voyant un Next, même si ton code est parfaitement indenté (ce qui n'est pas ton cas ! ) tu ne remontes pas toujours immédiatement au For correspondant. Cette indication demeure donc visuellement utile et facilite la recherche d'erreurs.

Note bien que tout ce qui relève d'indications visuelles n'a d'intérêt que pour celui qui intervient sur le code, et surtout lorsqu'il est déjà écrit, pour pouvoir l'interpréter sans délai et travailler plus rapidement. VBA s'en fout, cela ne l'empêche pas d'exécuter tant qu'il peut exécuter...

Cordialement.

Bon alors je te rejoins niveau visibilité sur tout ce qui est True, False, xlUp.

Que ce soit plus long, peut-être, mais au moins c'est plus parlant et pour quelqu'un qui débute, il vaut mieux commencer ainsi et par la suite utiliser ces "raccourcie".

Pour les variantes, vous ditent à peu près la même chose, si on peut dire.

Pour les Next, également.

Merci d'être intervenue, c'est bon à prendre.

Cordialement

MFerrand a écrit :

J'ai du mal à suivre...

ou alors mettre le code dans la "Feuil1",

D'une part il est préférable de réserver les modules d'objets aux évènements, et éventuellement quelques procédures qui y sont liées, cela facilitera ton travail sur le code.

D'autre part le fait que tu soies sur un module de feuille ne te dispense de qualifier les expressions, VBA ira toujours chercher quelle est la feuille active, il ne déduit pas que c'est la feuille à laquelle le module est dédié, et heureusement car rien n'empêche une autre feuille d'être active.

Ceci dit, dans un module de feuille la qualification est aisée et rapide : Me désigne la feuille sans ambiguïté, il suffit de l'utiliser.

Nom de feuille ou rang de la feuille : le choix dépend de l'élément qui a la plus grande stabilité et c'est à toi d'en décider.

Certains préfèrent travailler avec le nom de code (CodeName), plus stable, même si on peut le changer, car on ne le fait en général pas. Le nom de code s'utilise directement :

    With Feuil1

    End With

Par contre son utilisation ne peut se faire en utilisant une variable String comme pour le nom, et si on doit passer par une variable l'utilisation en devient plus malaisée.

Autre possibilité : accéder à la feuille à partir d'une plage nommée de la feuille. Supposons que tu aies nommé une plage Plage (pour faire simple),

    With [Plage].Worksheet  'ou [Plage].Parent, mais la 1re expression est plus parlante

    End With

Et si tu as un tableau Excel dans une feuille, tu peux utiliser son nom de la même façon.

Note aussi au passage que l'utilisation de noms pour gérer des tableaux ou bases de données permet de s'affranchir de toute nécessité de référence à la feuille.

Cordialement.

Bonsoir,

Pour commencer, tu m'en apprend une bonne. Ou du moins, j'étais tellement à fond dans le truc que j'ai jamais prit de recul.

Du coup, pas grand intérêt de mettre des procédures "complexe" dans une feuille. Après, sa doit dépendre du projet, on en revient toujours au même !

Merci pour ces multiples exemples, sa permet de montrer les quelques possibilités d'utilisation.

Après, tout dépend du projet, de toute façon.. mais déjà sa donne une idée.

Dans la finalité, quel est le mieux si on souhaite sélectionner une feuille ? Sheets("...").Select, With... ?

Cordialement