[VBA Excel] Formule contenant des noms de colonnes dans macro
Bonjour tout le monde! Je suis un petit nouveau! 8-)
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:
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:
[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:
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:
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 |
