Discussion :

[Tinnou]

Bonjour tout le monde! Je suis un petit nouveau!

J'ai un problème un peu casse-tête à résoudre, et je dois dire que je bloque... Vous pourrez peut-être m'aider! Je détaille un peu car à mon avis le contexte est important.

Dans une macro VBA, je souhaite à un moment donné permettre à l'utilisateur de filtrer une feuille. Cette feuille possède des colonnes nommées, pour pouvoir les appeler sans avoir besoin d'indiquer à chaque fois le numéro exact de la colonne. En annexe la procédure que j'ai créée pour nommer les colonnes, si cela peut être utile.

Via une feuille de paramètrage, l'utilisateur peut choisir de filtrer les données de deux façons:

-La première, qui fonctionne bien, est la fonction "LIKE". L'utilisateur remplit deux champs prévus à cet effet, et y indique d'une part le nom de la colonne et d'autre part le critère de sélection à appliquer à cette colonne. On appelle alors la procédure Filter_Like:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
 
Sub Main()
[...]
Dim Colonne_a_Filtrer As String
Dim Valeur_a_Filtrer As String
 
Colonne_a_Filtrer = ThisWorkbook.Names("Filter_Like_Column_Name").RefersToRange.Value 'Récupère la valeur de la cellule nommée "Filter_Like_Column_Name"
Valeur_a_Filtrer = ThisWorkbook.Names("Filter_Like_Value").RefersToRange.Value	'Récupère la valeur de la cellule nommée "Filter_Like_Value"
Call Filter_Like(Sht, Colonne_a_Filtrer, Valeur_a_Filtrer)
[...]
End Sub
 
Sub Filter_Like(Sht As Worksheet, Filter_Column_Name, Filter_Criteria)
Dim Cell As Range
Dim Column_Number As Integer
Column_Number = Sht.Range(Filter_Column_Name).Column 'Permet de récupérer le numéro de la colonne nommée "Filter_Column_Range"
 
For Each Cell In Sht.Range("A2:A" & Sht.UsedRange.Rows.Count).SpecialCells(xlCellTypeVisible)		'Je boucle sur les cellules qui sont visibles, pour éviter de retester des cellules déjà filtrées précédemment ;)
If Not Sht.Cells(Cell.Row, Column_Number) Like Filter_Criteria Then Cell.Value = "#"			'Je teste et met un # dans la première colonne si la ligne est invalide
Next
 
Sht.Cells.AutoFilter Field:=1, Criteria1:="<>#"				'Je filtre en ne gardant que les lignes sans #
 
End Sub

Cette procédure va mettre des "#" dans la première colonne (qui est vide au départ), pour toutes les lignes dont la cellule de la colonne "Filter_Like_Column_Name" ne répond pas au critère.
Par exemple, si j'indique dans la cellule du critère "*EM*" et dans la cellule du nom de colonne "Pays", j'aurais des # partout sauf pour les lignes de l'"AllEMagne", "LuxEMbourg", "DanEMark", etc...

Donc, jusqu'ici, ça marche, mais le problème vient ensuite:

Je souhaite aussi proposer à l'utilisateur un second critère de filtre, beaucoup plus complexe, afin qu'il puisse vraiment le personnaliser au maximum.
Dans ce critère, je voudrais qu'il puisse simplement rentrer dans la cellule prévue à cet effet une formule du style:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

[PAYS] = "France" and Left(NumToStr([NUMERO_TEL]),2) = "06"

, avec l'emploi de fonctions visual basic standards(la fonction Left(,) par exemple), mais aussi de fonctions que j'aurais pu définir moi même (NumToStr() par exemple, qui prendrait un nombre pour le transformer en chaine de caractères).
Mon gros problème, c'est comment réussir à faire passer cette formule comme test dans la boucle de mon filtre, en remplaçant à chaque fois les noms de colonnes ([PAYS], [NUMERO_TEL], etc...) par la valeur de la cellule de la ligne testée et de la colonne correspondante?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
Sub Filter_Formula(Sht As Worksheet, Formula_Criteria As String)
Dim Cell As Range
 
XXXXXXXXXXXXXXXXXXXXXXXXXXX
 
For Each Cell In Sht.Range("A2:A" & Sht.UsedRange.Rows.Count).SpecialCells(xlCellTypeVisible)
If YYYYYYYYYYYYYYYYY Then Cell.Value = "#"
Next
 
Sht.Cells.AutoFilter Field:=1, Criteria1:="<>#"
 
End Sub

Je pensais prendre la formule Formula_Criteria, et faire une recherche des "[" et "]", peut être avec la fonction InStr(), pour remplacer à chaque fois les "[NOM_COLONNE]" dans la formule par "Sht.Cells(Cell.Row, Sht.Parent.Names("NOM_COLONNE").RefersToRange"... Peut-être en créant une collection et en parcourant Formula_Criteria à la recherche des crochets "[" et "]", en prenant automatiquement les valeurs, puis en concaténant le tout... Mais je dois dire que je n'y arrive pas vraiment... Je bloque car il y a des confusions entre les passages de type "chaînes de caractères" et les passages de type "formule": Excel ne comprend pas que je lui donne un bout de chaîne de caractères qu'il est sensé interprêter par une formule...

Voila mon problème, je dois dire que je trouve ça assez compliqué, et je me demandais si des utilisateurs plus expérimentés que moi avec Visual Basic (je me suis mis à VBA il y a deux semaines seulement!!) pourraient m'aider


Merci d'avance!





Annexe:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
Sub Initialisation_Name_All_Columns(Sht As Worksheet)
Dim i As Integer
 
For i = 1 To Sht.Cells(1, 255).End(xlToLeft).Column 'Pour chaque colonne de la feuille
Name_Column Sht, i
Next
 
End Sub
 
'##################################################################################################
'####       Nomme la colonne numéro "Column_Number" de la feuille "Sht" avec le titre de       ####
'####      cette colonne, qui correspond à la valeur de la cellule située en première ligne.   ####
'####       Retourne le titre de la colonne, ou "0000" si celui-ci est invalide.               ####
'##################################################################################################
 
Function Name_Column(Sht As Worksheet, Column_Number As Integer) As String
Dim Column_Title As String
Column_Title = Sht.Cells(1, Column_Number).Value
 
On Error Resume Next
Sht.Parent.Names.Add Name:=Column_Title, RefersToR1C1:="=" & Sht.Name & "!C" & Column_Number
 
If Err <> 0 Then
    If Column_Title <> "" Then
    Name_Column = "0000"
    Exit Function
    End If
Err.Clear
Name_Column = Column_Title
End If
 
End Function

[tirex28]

Bonjour,

Voila mon problème, je dois dire que je trouve ça assez compliqué, et je me demandais si des utilisateurs plus expérimentés que moi avec Visual Basic (je me suis mis à VBA il y a deux semaines seulement!!) pourraient m'aider

Sans vouloir te decourager ce que tu tente de construire ressemble à une belle usine à gaz. J'ajouterais que faire confiance à un utilisateur lambda pour saisir une formule complexe en respectant une syntaxe normalisé releve du pari insensé.

Concernant la mise en oeuvre de criteres complexes, je te suggere de changer ton approche du probleme et de te tourner vers les filtres élaborés, c'est simple à comprendre, simple à utiliser, et efficace.

Cordialement,

Tirex28/

[Tinnou]

Bonjour,

Sans vouloir te decourager ce que tu tente de construire ressemble à une belle usine à gaz. J'ajouterais que faire confiance à un utilisateur lambda pour saisir une formule complexe en respectant une syntaxe normalisé releve du pari insensé.

Concernant la mise en oeuvre de criteres complexes, je te suggere de changer ton approche du probleme et de te tourner vers les filtres élaborés, c'est simple à comprendre, simple à utiliser, et efficace.

Cordialement,

Tirex28/

Bonjour!

Merci pour ta réponse

En effet c'est assez compliqué au niveau programmation, mais mon but c'est de faire un truc facile à utiliser pour l'utilisateur, et tant pis si le programme derrière est compliqué!!
Je cherche à créer une procédure qui sera ensuite réutilisée des centaines de fois. Donc je préfère demander à l'utilisateur de rentrer une formule du style:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Gauche([PAYS],3)&[Numero_Tel]&If([Type]=1, "01", "02")

plutôt que l'obliger à se référer à chaque colonne en cliquant, ce qui donnerait un truc du style:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=GAUCHE([Table_Pays.xls]Pays!L13C6,3)&[Pays.xls]Pays!L42C8&If([Pays.xls]Pays!L30C12="Inde",1,2)

Une formule de ce style risquerait à mon avis beaucoup plus de prêter à confusion, et d'être source d'erreur! Surtout alors que l'utilisateur devra utiliser cette conversion plusieurs dizaine de fois!

Et pour les problèmes de syntaxe, je pourrais toujours créer une procédure qui vérifie que la syntaxe est bien respectée, et prévienne l'utilisateur si ce n'est pas le cas...




Les filtres élaborés pourraient être une solution, mais j'aurais toujours le même problème:
Dans un filtre élaboré, si je souhaite rentrer comme critère de filtre une formule, je devrais trouver un moyen de convertir une formule donnée par l'utilisateur du style

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Gauche([Pays],3)="FRA"

en une formule où je remplace à chaque fois les noms de la colonne (ici [Pays]) par la position de la cellule en première ligne de cette colonne (disons C2 si la colonne C est la colonne "PAYS").


Je crois qu'au fond, mon problème est de parvenir à donner à excel une chaîne de caractères, en lui faisant comprendre qu'il doit la voir comme une formule. Si j'y arrive, je pourrais à mon avis réussir à faire ce que je veux.

Quelqu'un saurait-il si c'est possible, de prendre une chaîne de caractères Chaine_de_caracteres et de l'utiliser comme condition dans ma macro ainsi:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
If Not Chaine_de_caracteres Then 
'Rajouter un # en début de ligne
End If

A l'identique, lorsque je vais créer ma chaîne de caractère, je souhaiterais par exemple la créer en concaténant à un moment donné dans ma chaîne la formule "Sheet.Cells(i,j)", sans pour autant que cette valeur soit évaluée tout de suite:
Par exemple, si j'utilise:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Chaine_De_Caracteres = "Le_Pays_S'Appelle " & "Sheet.Cells(i,j)" & "."

Et que la valeur de la cellule (i,j) vaut "FRANCE", j'obtiendrais comme chaîne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

"Le_Pays_S'Appelle France."

Alors que je veux avoir dans ma chaîne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

"Le_Pays_S'Appelle" & "Sheet.Cells(i,j)" & "."

En fait je voudrais pouvoir créer une chaîne sans évaluer tout de suite les valeurs, pour ensuite m'en servir dans une boucle IF (et à ce moment là j'évaluerai les valeurs).

Est-ce possible?!?


Merci d'avance

[Tinnou]

J'ai réussi!

J'ai contourné le problème, en passant par une colonne intermédiaire. En effet je n'arrivais pas à passer une chaîne comme argument de mon "If", j'ai donc décidé de copier la chaîne dans une colonne spéciale, et dans ce cas ma chaîne de caractères est reconnue par une formule! Il ne me reste alors plus qu'à filtrer sur cette colonne.

Voici le code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub Filter_Formula(Sht As Worksheet, Formula_Criteria As String)
Dim Cell As Range
 
'Création de la colonne "Local_Formula" qui permettra d'évaluer la formule pour chaque ligne
On Error Resume Next
Sht.Range("Local_Formula").Delete
Err.Clear
Call Create_Column(Sht, "Local_Formula", 2)
 
 
Dim Formule As String
Dim i As Integer, j As Integer
Dim Column_Number As Integer
i = 1: j = 0
Formule = ""
 
 
'MsgBox ("Formule = " & Formule & " : Formula_Criteria = " & Formula_Criteria & " : i = " & i & " : j = " & j)
 
Do While Formula_Criteria <> "" And i <> 0
If j = 0 Then
i = InStr(Formula_Criteria, "[")
If i = 0 Then i = Len(Formula_Criteria) + 1
Formule = Formule & Left(Formula_Criteria, i - 1)
Formula_Criteria = Mid(Formula_Criteria, i + 1)
'MsgBox ("Formule = " & Formule & " : Formula_Criteria = " & Formula_Criteria & " : i = " & i & " : j = " & j)
 
j = 1
Else
i = InStr(Formula_Criteria, "]")
Column_Number = Sht.Range(Left(Formula_Criteria, i - 1)).Column
Formule = Formule & "RC" & Column_Number
Formula_Criteria = Mid(Formula_Criteria, i + 1)
'MsgBox ("Formule = " & Formule & " : Formula_Criteria = " & Formula_Criteria & " : i = " & i & " : j = " & j)
j = 0
End If
Loop
'MsgBox ("Formule = " & Formule & " : Formula_Criteria = " & Formula_Criteria & " : i = " & i & " : j = " & j)
 
Sht.Range(Cells(2, 2), Cells(Sht.UsedRange.Rows.Count, 2)).SpecialCells(xlCellTypeVisible).Formula = "=" & Formule
 
Sht.Cells.AutoFilter Field:=2, Criteria1:="True"
 
 
End Sub