VBA - Editer une fonction via une macro

Version imprimable

4 pièce(s) jointe(s)

Boujour à tous,

J'arrive à la fin de mon stage t je cherche des pistes d'ouverture pour l'amélioration de mon fichier.

Comme si je ne posais pas assez de question ces derniers temps ;)
Du coup, je me suis dit qu'il valait mieux imager mes propos.

Je cherche a éditer une fonction dans un module mais ne dispose pas des connaissances me le permettant.
Je vous expose mes données :

Le fichier permet de sélectionner dans la première feuille les différentes feuilles à exporter dans un autre classeur.
On y choisis la feuille et un groupe d'image associé (ou pas) :
Pièce jointe 212629

La liste des feuilles (qui est aussi la liste dans la collone Vérif) est mise a jour automatiquement avec une macro.

L'association d'une feuille avec un groupe d'image se fait avec une feuille "BDD" et des formule liées à l'espace de sélection (pour savoir quelles images importer). Les images ne sont pas dans le classeur, elles sont appelées depuis un dossier et une fois importées dans la feuille associée.

Pièce jointe 212635Pièce jointe 212632

La fonction à éditer est un bête switch, il permet d'associer à la feuille sélectionnée un nom comprenant les groupes d'images dédiés à cette feuille. Voici le code :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 Function test_switch(ByVal verif As String) As String '' retourne le nom de la liste pour la validation selon la vérif' Pour une question de lisibilité, on mettra les templates avec images en 1er puis les autres sans images test_switch = Switch(verif = "Embouts", "doc_embouts", _ verif = "Soudure", "doc_soudure", _ verif = "Calcul joint", "0", verif = "Thickness Analysis", "0", _ verif = "Draft Analysis", "0", verif = "Calcul joint", "0", verif = "LM Evo Sections Runners", "0", _ verif = "LM Volume Acoustique", "0", verif = "Inserts", "0", verif = "Matage Entretoise", "0", verif = "Feuille Vierge", "0") 'faire un switch pour chaque vérif, on appelle la fonction dans MAJ_doc 'attention a l'orthographe, une erreur et le programme s'interompt 'orthographe exact des noms d'onglets End Function
Je voudrais que cette fonction s'édite en fonction des onglets présents dans le classeur.
Je dispose déjà de la liste de mes onglets via macro mais ne saurais pas, avec mon niveau, utiliser cette liste pour ajouter dans mon switch : , verif = "nom_nouvelle_feuille", "0" (le "0" est une valeur n'associant pas de nom (liste de groupe d'img) à une feuille choisie)

J'espère que je suis clair dans ma demande, j'attend vos pistes et avis sur la question avec impatience.

En vous souhaitant une agréable soirée/journée.

Max

Pièce jointe 212636

13/06/2016, 18h35
Menhir

A mon avis, au lieu de faire une correspondance en utilisant un Switch, tu devrais créer une table de correspondance dans ta feuille Excel et de chercher cette correspondance avec un Range.Find sur la variable verif au lieu de cette fonction Switch.

Ca te permettrait de modifier cette correspondance très facilement.
13/06/2016, 23h11
Gintokuu

2 pièce(s) jointe(s)

Pas bête, tu voudrais dire par là que dans cette feuille (cf ci dessous) je devrais mettre le nom (par nom, j'entend liste de groupe d'image) associé à tel ou tel feuille en face du-dis nom de feuille?
Genre si le nom associé à la feuille soudure (C7) s'appelle doc_soudure, je le met en D7, puis utilise la méthode Range.Find dans mon espace de sélection pour laisser le choix à l'utilisateur?

Pièce jointe 212675
"Table de correspondance?"

Dans l'espace de sélection, on choisis dans la colonne B quelle feuille on exporte (nom_feuille dans la colonne C de la "table de correspondance), dans la colonne C de l'espace de sélection, on choisis l'image (listée a travers un nom). (Juste pour être bien sûr qu'on se comprenne, on est jamais trop prudent à vouloir être clair)

Pièce jointe 212676
"Espace de sélection"

Un petit conseil pour la validation de l'espace de sélection d'image avec la méthode Range.Find ?

Merci d'avoir pris le temps de répondre :)
14/06/2016, 09h58
Menhir
Je parlais de créer une liste pour remplacer ton Switch. Quelque chose comme :
```
Embouts                   doc_embouts
Soudure                   doc_soudure
Calcul joint              0
Thickness Analysis        0
Draft Analysis            0
Calcul joint              0
LM Evo Sections Runners	  0
LM Volume Acoustique	  0
Inserts                   0
Matage Entretoise         0
Feuille Vierge            0
```
Je suppose d'ailleurs que la colonne de gauche existe déjà pour alimenter ta liste déroulante.

Le rapport entre les deux colonnes n'a d'ailleurs pas forcément besoin de macro. Un simple RECHERCHEV() peut suffire à faire la correspondance.
Ce serait beaucoup plus facile à modifier qu'un Switch dans une macro.

Pour répondre rapidement (suis préssé là, mais très content de ton retour)

J'ai changé ma fonction : (bien que son nom n'ai plus aucun sens, elle fonctionne très bien)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 Function test_switch(ByVal verif As String) As String
 
Dim wshtBDD As Worksheet
Dim i As Integer
Dim Cellule As Range
Dim TabBDD() As String
 
 
 
Set wshtBDD = ThisWorkbook.Worksheets("BDD Choix vérifi")
 
For Each Cellule In wshtBDD.Range("TabBDD[[Nom Onglets sans BDD]]")
If Cellule.Value = verif Then
test_switch = wshtBDD.Cells(Cellule.Row, Cellule.Column + 1).Formula
Exit Function
End If
Next Cellule
 
 
End Function

Un grand merci pour ta réflexion, je suis encore un débutant qui tatonne, en prog et en excel ;)

Je met la sub qui l'appelle, si jamais ça peut servir à d'autres

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 Sub MaJ_Doc()
 
' Met a jour les informations choisies dans les feuilles choisies
 
Dim i As Integer
Dim j As Integer
Dim wsht As Worksheet
Dim nom_model As String
Dim doc As String
Dim img As String
 
 
 
 
Set wsht = ThisWorkbook.Worksheets("Choix vérif")
 
'j récupère le nombre de modèles a selectionner
 
j = wsht.Cells(1, 3).Value
 
'''''' réfléchir a possibilité de créer des doublons "Embouts (2)"
 
For i = 1 To j ' itération pour prendre en compte les cellules C11 puis C12...
 
 
 
'nom_model récupère le nom sélectionné
 
    nom_model = wsht.Cells(4 + i, 2).Value
 
 doc = test_switch(nom_model)
If doc <> "" Then
    With wsht.Cells(4 + i, 3).Validation
                        .Delete
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:=doc
                        .IgnoreBlank = True
                        .InCellDropdown = True
                        .ShowInput = True
                        .ShowError = True
    End With
Else: With wsht.Cells(4 + i, 3)
            .Validation.Delete
            .Clear
            End With
End If
 
 
Next i
 
 
End Sub

Plus simple, plus rapide :

Code:

1
2
3
4
5
6
7
8
9
Function test_switch(ByVal verif As String) As String
Dim wshtBDD As Worksheet
Dim Cellule As Range
 
Set wshtBDD = ThisWorkbook.Worksheets("BDD Choix vérifi")
Set Cellule = wshtBDD.Range("TabBDD[[Nom Onglets sans BDD]]").Find verif
test_switch = Cellule.Offset(0,1).Formula
 
End Function

J'ai laissé le Formula mais, à mon avis, il faudrait le remplacer par un Value.

14/06/2016, 13h21
Gintokuu

Wow, je m'attendais pas a ce que ca se résume a 3 lignes :p

Ca devrais marcher du tonnerre, je vérifie de suite après le dej :)

pour le .Formula ou .Value, cela dépends de comment on remplis la case

le .Formula n'est valable que si la cellule contenant le nom en question est de la forme "=doc_nom"

Code:

doc = test_verif

Le .Value serais adapté si la case contient "doc_nom" et qu'en sortie de fonction j'attribue a ma variable

Code:

doc = "=" & test_verif

Corriges moi si je me trompe hein

Un grand merci pour ton aide, tu m'as apporté un regard critique à un endroit crucial :)
15/06/2016, 09h00
Menhir
Petit détail : j'ai oublié de mettre le paramètre de Find entre parenthèses.

Citation:

Envoyé par Gintokuu

le .Formula n'est valable que si la cellule contenant le nom en question est de la forme "=doc_nom"
doc = test_verif
Le .Value serais adapté si la case contient "doc_nom" et qu'en sortie de fonction j'attribue a ma variable
doc = "=" & test_verif
Corriges moi si je me trompe hein

Si tu mets Formula, VBA retournara... la formule. Et non pas le résultat de la formule qui est, si je ne me trompe pas, ce que tu souhaites.
Tu peux vérifier ça en mettant avant le "test_switch =" :
Code:

1 2 Debug.Print Cellule.Offset(0,1).Formula Debug.Print Cellule.Offset(0,1).Value
15/06/2016, 12h14
Gintokuu

Les débug print m'ont bien confirmé que je devais utiliser .Formula

En effet, le .Value m'aurais retourné la première valeur contenue dans le nom (je cherche à récuperer le nom)

le .Formula récupère "=doc_soudure" qui est le nom à récuperer. Une fois mis dans la variabe doc, je peux donc créer une validation de données de type liste avec ce nom.

Si la variable doc = "", il n'y a pas de groupe d'image associé => on enlève toute forme de validation.

Je n'ai plus de questions a ce sujet ;)

Encore merci pour ton aide Menhir !