Formateur excel, access, vba - Philippe Tulliez

VBA Excel – Comment remplacer des formules par leur résultat

21 mai 2016 Auteur:Philippe Tulliez

Aucun commentaire pour le moment

Nous sommes régulièrement amenés à remplacer une plage de cellules contenant des formules par leur résultat. Autrement dit un Copier/Collage spécial – Valeur. En VBA, après avoir éventuellement utiliser l’enregistreur de macros pour nous aider, nous aurions tendance à utiliser ce code. Exemple pour la page A1:H100 de la feuille nommée Feuil1

 With ThisWorkbook.Worksheets("Feuil1").Range("A1:H100")
 .Copy
 .PasteSpecial Paste:=xlPasteValues
 End With
 Application.CutCopyMode = False

1

2

3

4

5

With ThisWorkbook.Worksheets("Feuil1").Range("A1:H100")

.Copy

.PasteSpecial Paste:=xlPasteValues

End With

Application.CutCopyMode = False

L’astuce consiste à utiliser la propriété Value de l’objet Range

 With ThisWorkbook.Worksheets("Feuil1").Range("A1:H100")
 .Value = .Value
 End With

1

2

3

With ThisWorkbook.Worksheets("Feuil1").Range("A1:H100")

.Value = .Value

End With

Philippe Tulliez

[Lire plus...]

Excel – Comment déplacer rapidement des colonnes dans une liste de données

19 avril 2016 Auteur:Philippe Tulliez

Aucun commentaire pour le moment

Pour déplacer rapidement une ou plusieurs colonnes dans une feuille excel, il existe à ma connaissance deux techniques rapides. Dans les deux cas, il faut bien entendu sélectionner la ou les colonnes à déplacer. La première des solutions consiste à se positionner sur l’un des côtés de la sélection (une croix avec des flèches va apparaître) et ensuite appuyer simultanément sur le bouton gauche de la souris et la touche Shift / Maj (Attention pas ShiftLock), une ligne verticale va apparaître et ensuite glisser et se positionner entre les deux colonnes où l’on souhaite déposer les données et lâcher le

[Lire plus...]

Comment incrémenter le nom d’une feuille Excel, lorsqu’il existe déjà ?

16 mars 2016 Auteur:Philippe Tulliez

Aucun commentaire pour le moment

Créer une feuille et la renommer Lorsque l’on crée dynamiquement une feuille dans un classeur Excel à l’aide de l’instruction

Worksheets.Add

1	Worksheets.Add

et que l’on souhaite lui attribuer un nom particulier, par exemple le texte CA suivi de l’année et du numéro du mois en cours

ActiveSheet.Name = "CA " & Format(Date, "yyyy-mm")

1	ActiveSheet.Name = "CA " & Format(Date, "yyyy-mm")

il est possible que ce nom existe déjà et dans ce cas une erreur sera générée Nous pourrions intercepter l’erreur en l’ignorant, comme l’illustre le code ci-dessous

On Error Resume Next ' Va a l'instruction suivante si erreur
ActiveSheet.Name = "CA " & Format(Date, "yyyy-mm")
On Error GoTo 0 ' Rend la main au code VBA

1

2

3

On Error Resume Next ' Va a l'instruction suivante si erreur

ActiveSheet.Name = "CA " & Format(Date, "yyyy-mm")

On Error GoTo 0 ' Rend la main au code VBA

Ce qui aura pour effet d’éviter l’affichage du message d’erreur mais qui nous laissera la feuille récemment créée avec son nom incrémenté comme par exemple Feuil7

[Lire plus...]

Comment mettre en couleur des mots se trouvant dans une plage de cellules ?

10 février 2016 Auteur:Philippe Tulliez

Aucun commentaire pour le moment

Mettez en évidence des mots contenus dans une plage de cellules. Lorsque l’on sélectionne une ou plusieurs cellules, il est possible de mettre la police de la valeur contenue dans ces cellules dans une couleur choisie; Il est possible également de mettre dans une certaine couleur un ou plusieurs mots contenu dans cette cellule. Pour cela, il suffit de sélectionner le mot et choisir sa police. Ce que je vous propose dans ce billet, c’est de le faire à l’aide d’une procédure VBA. Le code de la procédure

Sub CharacterColor(AreaText As Range, TextColoring As String, Optional RGBCode As Long = vbRed, Optional ResetColor As Boolean = True)
 ' Procédure de mise en évidence des mots dans une plage de cellules
 ' Author : http://Philippe.Tulliez.be
 ' Date   : 15/12/2015
 ' Version : 1.1
 ' Arguments
 ' AreaText     (Range)  Plage de cellules à l'intérieure de laquelle les mots seront mis en évidence
 ' TextColoring (String) Liste des mots séparés par un ';' à mettre en évidence
 ' [RGBCode]    (Numérique) Code (RGB) de la couleur qui mettra les mots en évidence (d:vbRed (rouge))
 ' [ResetColor] (Boolean) Efface la couleur de la plage AreaTexte (d:True)
 Dim Cel As Range, nbWord As Integer, tbl() As String, Start As Integer
 If ResetColor Then AreaText.Font.Color = 0 ' Efface la mise en forme précedente
 TextColoring = LCase(TextColoring)
 tbl = Split(TextColoring, ";")
 For Each Cel In AreaText
  For nbWord = 0 To UBound(tbl)
   Start = InStr(LCase(Cel), tbl(nbWord))
    Do While Start
     Cel.Characters(Start, Len(tbl(nbWord))).Font.Color = RGBCode
     Start = InStr(Start + 1, LCase(Cel), tbl(nbWord))
    Loop
  Next
 Next
End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

Sub CharacterColor(AreaText As Range, TextColoring As String, Optional RGBCode As Long = vbRed, Optional ResetColor As Boolean = True)

' Procédure de mise en évidence des mots dans une plage de cellules

' Author : http://Philippe.Tulliez.be

' Date : 15/12/2015

' Version : 1.1

' Arguments

' AreaText (Range) Plage de cellules à l'intérieure de laquelle les mots seront mis en évidence

' TextColoring (String) Liste des mots séparés par un ';' à mettre en évidence

' [RGBCode] (Numérique) Code (RGB) de la couleur qui mettra les mots en évidence (d:vbRed (rouge))

' [ResetColor] (Boolean) Efface la couleur de la plage AreaTexte (d:True)

Dim Cel As Range, nbWord As Integer, tbl() As String, Start As Integer

If ResetColor Then AreaText.Font.Color = 0 ' Efface la mise en forme précedente

TextColoring = LCase(TextColoring)

tbl = Split(TextColoring, ";")

For Each Cel In AreaText

For nbWord = 0 To UBound(tbl)

Start = InStr(LCase(Cel), tbl(nbWord))

Do While Start

Cel.Characters(Start, Len(tbl(nbWord))).Font.Color = RGBCode

Start = InStr(Start + 1, LCase(Cel), tbl(nbWord))

Loop

End Sub

Exemple d’une procédure d’appel

Sub TestCharacterColor()
 Const SheetName As String = "Textes"    ' Nom de la feuille où se trouve les textes à mettre en forme
 Const RangeAddress As String = "A2:A4"  ' Plage de cellules où se trouve les textes à mettre en forme
 Const WordList As String = "raison;trop;le" ' Liste des mots qui doivent être mis en évidence
 Dim rng As Range
 Set rng = ThisWorkbook.Worksheets(SheetName).Range(RangeAddress)
 CharacterColor rng, WordList
End Sub

1

2

3

4

5

6

7

8

Sub TestCharacterColor()

Const SheetName As String = "Textes" ' Nom de la feuille où se trouve les textes à mettre en forme

Const RangeAddress As String = "A2:A4" ' Plage de cellules où se trouve les textes à mettre en forme

Const WordList As String = "raison;trop;le" ' Liste des mots qui doivent être mis en évidence

Dim rng As Range

Set rng = ThisWorkbook.Worksheets(SheetName).Range(RangeAddress)

CharacterColor rng, WordList

End Sub

Liens Je vous conseille la lecture

[Lire plus...]

Comment connaître la date du lundi d’une semaine avec excel ?

8 janvier 2016 Auteur:Philippe Tulliez

1 Commentaire

En ce début d’année, on pense souvent à créer un calendrier, un agenda, etc. Voici une astuce pour connaître la date du lundi pour une année et semaine donnée. Formule avec Excel (en cellule A2, l’année et en B2 le numéro de la semaine)

=7 * B2 + DATE(A2;1;3) - JOURSEM(DATE(A2;1;3)) - 5

1	=7 * B2 + DATE(A2;1;3) - JOURSEM(DATE(A2;1;3)) - 5

Cette formule renverra la date du lundi de la semaine définie en cellule B2, de l’année définie en A2. Si l’on souhaite, obtenir le mardi de la semaine, il faut remplacer la constante 5 par 4 et ainsi de suite. Philippe Tulliez

[Lire plus...]

VBA Excel – Fonction qui renvoie une liste de données résultant d’une requête SQL dans Access

24 décembre 2015 Auteur:Philippe Tulliez

Aucun commentaire pour le moment

Introduction Je vous propose une petite procédure qui importe le résultat d’une requête faite sur une base de données Access dans une feuille Excel. Cette requête est exécutée par une fonction nommée QueryAccess qui renvoie une table contenant le résultat d’une requête faite sur une base de données Access. Les arguments utilisés dans la fonction dbFullName de type String, contient le chemin complet + le nom de la base de données. SqlQuery de type String, contient la requête SQL. [WithLabel] de type booléen, argument facultatif, True ou omis renvoie les étiquettes de colonnes Le code de la procédure

Function QueryAccess(dbFullName As String, SqlQuery As String, Optional WithLabel As Boolean = True)
 ' Philippe Tulliez
 ' v 2.0 - 11/01/14 ' Ajouté argument optionnel WithLabel
 ' Nécessite de référencer Microsoft DAO x.x ObjectLibrary
 ' Arguments
 ' dbFullName ' Chemin +  nom du fichier
 ' SqlQuery ' Chaîne de caractères contenant la requête SQL
 ' [WithLabel] ' True ou Omis renvoie les étiquettes de colonnes
' 
' Variables - Déclaration et affectation des valeurs
 Dim db As DAO.Database, Rs As DAO.Recordset
 Dim myTable(), count As Long, Elem As Integer
 Set db = Workspaces(0).OpenDatabase(dbFullName, ReadOnly:=True)
 Set Rs = db.OpenRecordset(SqlQuery)
 ' Lecture des enregistrements de la requête
 While Not Rs.EOF
  ReDim Preserve myTable(Rs.Fields.count, count)
  If count = 0 And WithLabel Then
   For Elem = 0 To Rs.Fields.count - 1: myTable(Elem, count) = Rs(Elem).SourceField: Next ' Etiquettes de colonnes
   count = count + 1: ReDim Preserve myTable(Rs.Fields.count, count)
  End If
  For Elem = 0 To Rs.Fields.count - 1
    myTable(Elem, count) = IIf(IsNull(Rs(Elem)), "", Rs(Elem))
  Next Elem
  count = count + 1: Rs.MoveNext
 Wend
 QueryAccess = Application.WorksheetFunction.Transpose(myTable)
 Rs.Close: db.Close: Set Rs = Nothing
End Function

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

Function QueryAccess(dbFullName As String, SqlQuery As String, Optional WithLabel As Boolean = True)

' Philippe Tulliez

' v 2.0 - 11/01/14 ' Ajouté argument optionnel WithLabel

' Nécessite de référencer Microsoft DAO x.x ObjectLibrary

' Arguments

' dbFullName ' Chemin + nom du fichier

' SqlQuery ' Chaîne de caractères contenant la requête SQL

' [WithLabel] ' True ou Omis renvoie les étiquettes de colonnes

'

' Variables - Déclaration et affectation des valeurs

Dim db As DAO.Database, Rs As DAO.Recordset

Dim myTable(), count As Long, Elem As Integer

Set db = Workspaces(0).OpenDatabase(dbFullName, ReadOnly:=True)

Set Rs = db.OpenRecordset(SqlQuery)

' Lecture des enregistrements de la requête

While Not Rs.EOF

ReDim Preserve myTable(Rs.Fields.count, count)

If count = 0 And WithLabel Then

For Elem = 0 To Rs.Fields.count - 1: myTable(Elem, count) = Rs(Elem).SourceField: Next ' Etiquettes de colonnes

count = count + 1: ReDim Preserve myTable(Rs.Fields.count, count)

End If

For Elem = 0 To Rs.Fields.count - 1

myTable(Elem, count) = IIf(IsNull(Rs(Elem)), "", Rs(Elem))

Next Elem

count = count + 1: Rs.MoveNext

Wend

QueryAccess = Application.WorksheetFunction.Transpose(myTable)

Rs.Close: db.Close: Set Rs = Nothing

End Function

[Lire plus...]

Excel VBA – Exporter des lignes suivant critères avec la méthode AdvancedFilter

18 décembre 2015 Auteur:Philippe Tulliez

Aucun commentaire pour le moment

Avant-Propos Lorsque l’on doit exporter des données suivant des critères, la solution la plus simple à mettre en place et la plus rapide au niveau exécution est l’utilisation des filtres avancés d’excel soit en VBA la méthode AdvancedFilter de l’objet Range. Scénario : Nous souhaitons exporter les données se trouvant en cellules A1:F261 de la feuille nommée [db] vers la feuille nommée [Export] avec les critères se trouvant en cellules A1:B3 de la feuille nommée [Param] Si vous ne connaissez pas les filtres avancés d’excel, je vous invite à lire ce didacticiel Les filtres avancés ou élaborés dans Excel Code

[Lire plus...]

Excel VBA – Supprimer des lignes suivant critères avec la méthode AdvancedFilter

10 décembre 2015 Auteur:Philippe Tulliez

Aucun commentaire pour le moment

Je vous propose cette semaine, une fonction qui vous permettra de supprimer les lignes d’une liste de données suivant les critères que vous aurez choisi comme par exemple toutes les personnes Célibataires, gagnant plus de 2.500€. Cette fonction est basée sur la méthode AdvancedFilter. La méthode AdvancedFilter d’un objet Range permet d’effectuer énormément d’opération en VBA et ce avec un minimum de lignes de code. Cette méthode est issue de la fonctionnalité des filtres avancés d’excel qui permettent une multitude d’opérations telles que l’exportation de données suivant des critères, l’extraction sans doublons, etc. SI vous ne connaissez pas les filtres

[Lire plus...]

Tester une chaîne de caractères en toute sécurité

5 décembre 2015 Auteur:Philippe Tulliez

Aucun commentaire pour le moment

Qui n’a pas été confronté à des heures de recherches parce-qu’un test sur une chaîne de caractères n’a pas produit le résultat escompté. Scénario Nous avons une liste de données comme l’illustre l’image ci-dessous et vous devez retrouver tous les travaux avec comme statut “Pending” pour les placer en “Invoice“. La liste de données La formule Ces lignes de code qui suivent se trouvent dans une boucle où la variable Row est le numéro de la ligne testée.

   If .Range("D" & Row) = "Pending" Then
    .Range("E" & Row) = "Invoice"
    Else
    .Range("E" & Row) = ""
   End If

1

2

3

4

5

If .Range("D" & Row) = "Pending" Then

.Range("E" & Row) = "Invoice"

Else

.Range("E" & Row) = ""

End If

Le test logique pourrait être également exprimé comme ci-dessous.

   .Range("E" & Row) = IIf(.Range("D" & Row) = "Pending", "Invoice", "")

1	.Range("E" & Row) = IIf(.Range("D" & Row) = "Pending", "Invoice", "")

Le constat Nous pouvons voir que certaines cellules ne sont pas

[Lire plus...]

A l’aide de VBA, comment écrire une formule plus complexe dans une cellule ?

26 novembre 2015 Auteur:Philippe Tulliez

Aucun commentaire pour le moment

Cet article est la suite du billet Comment écrire une formule dans une plage de cellules en VBA ? Nous allons apprendre comment écrire des formules plus complexes. Pour tous les plages de données évoquées plus bas la cellule de départ est la cellule A1 et la première ligne contient les étiquettes de colonnes. Comment écrire une formule plus complexe dans une cellule ? Prenons la formule NB.SI dont la syntaxe est NB.SI(Plage;Critère) et qui a pour but de compter le nombre de cellules répondant au critère. Pour illustrer notre exemple, imaginons une plage de données A1:F31 dont les cellules

[Lire plus...]

Formateur excel, access, vba – Philippe Tulliez

VBA Excel – Comment remplacer des formules par leur résultat

Excel – Comment déplacer rapidement des colonnes dans une liste de données

Comment incrémenter le nom d’une feuille Excel, lorsqu’il existe déjà ?

Comment mettre en couleur des mots se trouvant dans une plage de cellules ?

Comment connaître la date du lundi d’une semaine avec excel ?

VBA Excel – Fonction qui renvoie une liste de données résultant d’une requête SQL dans Access

Excel VBA – Exporter des lignes suivant critères avec la méthode AdvancedFilter

Excel VBA – Supprimer des lignes suivant critères avec la méthode AdvancedFilter

Tester une chaîne de caractères en toute sécurité

A l’aide de VBA, comment écrire une formule plus complexe dans une cellule ?

Articles récents

Commentaires récents