Top 50 des macros préférées des xldiens

[Odesta]

Re : Top 50 des macros préférées des xldiens

Bonjour

Utilisé sur presque tous mes fichiers 'techniques' :

‘Code pour ouvrir un fichier et lui attribuer un objet (et vérifier si il n'est pas déjà ouvert)

chemin = "D:\Repertoire"
nom_fic = "Traitement.xls"
deja_ouvert = False
For Each fichier In Workbooks 'vérfier dans les classeurs ouverts
    If fichier.Name = nom_fic Then
        deja_ouvert = True 'si il y est
        Set fic_resultat = Workbooks(nom_fic)
    End If
Next

If Not deja_ouvert Then 'si il ne l'est pas, l'ouvrir depuis le répertoire
    Set fso = CreateObject("Scripting.filesystemobject")
    Set dossier = fso.getfolder(chemin)
    For Each fichier In dossier.Files
        If fichier.Name = nom_fic Then
            Set fic_resultat = Workbooks.Open(fichier)
        End If
    Next

End If

 

[MJ13]

Re : Top 50 des macros préférées des xldiens

Bonjour à tous

Et merci JM d'avoir initié cette discussion que je n'avais pas vu à l'époque des Yé-Yé.

Alors moi, celui que je trouve très bien et que j'utilise souvent, c'est ce code qui tient sur une ligne. Il peut déplacer des montagnes .

Sub Ext_BD()
'Ext bD
    Range("_BD").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "_CR"), CopyToRange:=Range("_ZD"), Unique:=False
End Sub

 

[Odesta]

Re : Top 50 des macros préférées des xldiens

Lorsque vous avez plein de TCD sur plusieurs feuilles, après une changement dans les données :

‘Mettre à jour des TCD

For Each feuilleTCD In ActiveWorkbook.Sheets 'pour chaque feuille
    For Each TCD In feuilleTCD.PivotTables 'pour chaque tableau présent
        ActiveSheet.PivotTables(TCD.Name).PivotCache.Refresh 'rafraichir le TCD
    Next TCD
Next feuilleTCD

 

[Odesta]

Re : Top 50 des macros préférées des xldiens

Et enfin : ce code est inspiré d'un code source trouvé sur le net par une de mes collègues mais hélas je n'ai pas la source.
L'idée est d'empécher les déplacer glisser en annulant leur effet. Les "étendres" sont autorisés.
Je me serts de ce code lorsque j'ai une liste de valeur et que je dois remplir un tableau, alors que le tout est soumis à des formules "unitaires" (donc sensible au déplacement)

Dim A As String
Dim B As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address <> A And Target.Count = B Then
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
   End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
   A = T.Address
   B = T.Count
Application.EnableEvents = True
End Sub

 

[Misange]

Re : Top 50 des macros préférées des xldiens

Bonjour à tous,
Ma préférée permet de nettoyer les valeurs qui n'existent plus dans tous les tcd d'un classeur.
Récupérée à partir de liens sur ce site. Evidemment je n'ai pas noté la source et je m'en excuse !

Bonjour

c'est une macro de Debra Dalgheish, MVP excel et webmistress de Contextures.
Debra est sans conteste une référence mondiale sur les TCD.
Note que cette macro est maintenant inutile, cette fonction existant de base avec les TCD 2007 et suivants !

 

[MJ13]

Re : Top 50 des macros préférées des xldiens

Bonjour à tous

Je pense avoir trouvé le code ultime .

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub TCD_Affiche_Valeurs_Distinctes()
'Extrait d'un TCD en page sur le champs Nom en nommant la colonne Nom: "DBNom"

Dim d As Object, Cell As Range

ActiveSheet.PivotTables(1).PivotCache.Refresh

Set d = CreateObject("Scripting.Dictionary")

For Each Cell In Range("BDNom")
  If Cell.Value <> "" Then d(Cell.Value) = Cell.Value
Next

nbval = d.Count: MsgBox (nbval & " valeurs distinctes.")

For Each c In d.keys
     ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Nom"). _
        CurrentPage = d(c)
     Sleep 2000
Next c

End Sub

 

[Staple1600]

Re : Top 50 des macros préférées des xldiens

Bonjour à tous

Bizarre la vie que vivent certains fils sur XLD
Celui-ci je l'avais initié en2006.
Il s'était tu assez rapidement puis soudain 6 ans plus tard, il ressurgit et sort de sa torpeur.

Comme quoi, un fil n'est jamais mort sur XLD

 

[MJ13]

Re : Top 50 des macros préférées des xldiens

Bonjour à tous

Il s'était tu assez rapidement puis soudain 6 ans plus tard, il ressurgit et sort de sa torpeur.

Merci Jean-Marie pour cette discussion qui n'avait pas eu un franc succès à l'époque. Mais bon, c'est comme le bon vin .

Moi, j'aime bien ce genre de discussion . Et n'hésite pas si tu as de bons codes comme tu sais le faire .

 

[Staple1600]

Re : Top 50 des macros préférées des xldiens

Bonjour à tous

MJ13:
Je n'ai pas sous le coude (droit) de macros à proposer pour le moment, mais simplement de la lecture
Êtes-vous d'accord avec tous les points évoqués?

PS: Toujours content de voir la famille des aficionados d'Excel s'agrandir
(et penser ou lire de l'english ça peut toujours servir vu que c'est la seule langue que cause le VBA depuis longtemps maintenant)

EDITION: Bonjour Misange

 

[MJ13]

Re : Top 50 des macros préférées des xldiens

Bonjour Jean-Marie

Merci pour ce lien très, très intéressant .

Êtes-vous d'accord avec tous les points évoqués?

En général, oui. Si il le dit, c'est qu'il a du le tester .

 

[Misange]

Re : Top 50 des macros préférées des xldiens

Hello Jean-Marie (et les autres )

Oui bien sur globalement d'accord. Mais pas forcément avec la hiérarchie des choses.
Éviter de manipuler des range quand c'est possible en passant par un array est une des choses les plus efficaces que je connaisse pour accélérer un code.
Le point 14 est aussi très important pour accélérer.
Il manque aussi le fait d'éviter de lire à chaque passage de boucle la propriété d'un objet si celle-ci ne change pas en cours de route (la stocker dans une variable de type string)

 

[GeoTrouvePas]

Re : Top 50 des macros préférées des xldiens

Bonjour tout le monde,

Êtes-vous d'accord avec tous les points évoqués?

Oui à 99.99%.....
Le 0.01% restant, c'est pour le point n°17 qui personnellement me gène un peu. Je ne conteste pas le fait que ça permette d’accélérer le code mais j'ai toujours préféré utiliser du "vrai code", ne serait - ce que pour simplifier la relecture du code. Mais bon, c'est une habitude strictement personnelle et ça n'arrange pas la vitesse d'exécution.

Le point 14 est aussi très important pour accélérer.

Je pense également que c'est un très bonne habitude à prendre non seulement pour son impact en terme de vitesse mais également pour son impact en terme de fiabilité de la macro.

Il manque aussi le fait d'éviter de lire à chaque passage de boucle la propriété d'un objet si celle-ci ne change pas en cours de route (la stocker dans une variable de type string)

Misange, aurais tu un exemple pour illustrer ton propos ? J'ai du mal à me représenter ce que tu suggères.


Sinon je trouve réellement dommage que l'auteur fasse l'impasse sur les variables tableaux et les dictionnaires......
Ce sont sans doute les meilleurs outils pour retraiter rapidement de gros volumes de données. Sir Boisgontier en fait d'ailleurs la démonstration à de nombreuses reprises sur son site. On constate que les temps d'exécution sont sans commune mesure avec les outils "classiques".

 

[Misange]

Re : Top 50 des macros préférées des xldiens

@Geo
Imagine que dans une boucle tu fasses référence à la valeur de la cellule A1
au lieu d'écrire

For Each c In Range("truc")   
 If range("A1").Value > Machin Then
...
Next c

mieux vaut écrire

Machin = Range("A1").Value   
For Each c In Range("truc")   
 If c.Value > Machin Then
...
Next c

de façon à ne lire qu'une fois la valeur de A1. Idem pour le nom de la feuille, du classeur, bref de toutes les propriétés des objets.

C'est un des conseils que j'ai retenus de Laurent Longre.
Tout à fait d'accord pour les tableaux et dictionnaires, c'est ce que j'ai mis d'ailleurs en faisant référence au fait de passer par les arrays au lieu de manipuler des range, notamment en faisant des bouclettes.

 

[Staple1600]

Re : Top 50 des macros préférées des xldiens

Bonjour à tous

Le point 17

Use Worksheet Functions rather developing own logic:
By using Application.WorkSheetFunction, we tell VBA processor to use native code rather than interpreted code as VBA understands the worksheet functions better than your algorithm. So, for example use
mProduct = Application.WorkSheetFunction.Product(Range("C5:C10"))
rather than defining your own logic like this:
mProduct = 1
For i = 5 to 10
mProduct = mProduct * Cells(3,i)
Next

Geotrouvepas:
En quoi utiliser les fonctions natives d'Excel en VBA n'est pas assimilable à du vrai code?
Il en quand plus simple d'utiliser les fonctions existantes que de les réécrire en VBA, non ?
Et le noms des worksheets.function sont parlantes, non ?

EDITION: Bonjour Misange, Houps collision, je viens juste de voir ton message

 

[GeoTrouvePas]

Re : Top 50 des macros préférées des xldiens

@Misange
Merci pour ton exemple. Je ne sais pas pourquoi mais je m'imaginais un concept beaucoup plus complexe .
Effectivement, rien ne sert de tester la valeur de la cellule à chaque passage dans la boucle.

Désolé j'étais passé à côté de ta mention sur les arrays. Je trouve vraiment dommage que l'auteur soit passé à côté. Peut être est il parti du principe que c'était trop spécifique ou pas suffisamment à la portée "du premier venu".