Fonction excel dans une fonction perso en VBA
wlikotae
-
eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention -
eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention -
Bonjour,
J'aimerais faire une fonction que j'appele RechercheT (dans le sens où elle fonctionne comme une rechercheV ou H, mais ne va pas chercher le résultat dans une ligne ou une colonne, mais dans un tableau).
Voici la fonction brute que j'utilse dans excel : (DECALER(INDEX(Tableau;EQUIV(Valeur_cherché;Colonne/ligne;0);Ligne-1);Colonne;0;1;1)
j'aimerais en faire une macro, et voici comment j'ai procédé :
Function RECHERCHET(Tableau As Range, C_L As Range, Valeur_cherchée As String, Ligne As Integer, Colonne As Integer) As String
Dim Aux As Integer
Aux = Ligne
Ligne = Aux - 1
Dim L_Val As Integer
L_Val = Application.WorksheetFunction.Equiv(Valeur_cherchée, C_L, 0)
Dim Cell_final As Range
Set Cell_final = Application.WorksheetFunction.Index(Tableau, L_Val, Ligne)
Dim Aux_Cell As Range
Set Aux_Cell = Cell_final
Set Cell_final = Aux_Cell.Offset(0, Colonne)
RECHERCHET = Aux_Cell.Value
End Function
Quand je la rentre dans une cellule, excel tient à ce que je mette un nombre (???) pour la variable C_L :( .
Alors que C_L représente la ligne ou la colonne où la valeur devrait être cherchée. De plus, je ne crois pas qu'il prenne correctement en compte la fonction equiv dans ma macro.
J'ai cherché sur plusieurs sites internets, et impossible de trouver un tutorial/une aide/une question à propos de l'utilisation de fonctions pré-installées (comme equiv ou recherV, ou n'importe) dans une macro.
Merci de votre future aide :)
J'aimerais faire une fonction que j'appele RechercheT (dans le sens où elle fonctionne comme une rechercheV ou H, mais ne va pas chercher le résultat dans une ligne ou une colonne, mais dans un tableau).
Voici la fonction brute que j'utilse dans excel : (DECALER(INDEX(Tableau;EQUIV(Valeur_cherché;Colonne/ligne;0);Ligne-1);Colonne;0;1;1)
j'aimerais en faire une macro, et voici comment j'ai procédé :
Function RECHERCHET(Tableau As Range, C_L As Range, Valeur_cherchée As String, Ligne As Integer, Colonne As Integer) As String
Dim Aux As Integer
Aux = Ligne
Ligne = Aux - 1
Dim L_Val As Integer
L_Val = Application.WorksheetFunction.Equiv(Valeur_cherchée, C_L, 0)
Dim Cell_final As Range
Set Cell_final = Application.WorksheetFunction.Index(Tableau, L_Val, Ligne)
Dim Aux_Cell As Range
Set Aux_Cell = Cell_final
Set Cell_final = Aux_Cell.Offset(0, Colonne)
RECHERCHET = Aux_Cell.Value
End Function
Quand je la rentre dans une cellule, excel tient à ce que je mette un nombre (???) pour la variable C_L :( .
Alors que C_L représente la ligne ou la colonne où la valeur devrait être cherchée. De plus, je ne crois pas qu'il prenne correctement en compte la fonction equiv dans ma macro.
J'ai cherché sur plusieurs sites internets, et impossible de trouver un tutorial/une aide/une question à propos de l'utilisation de fonctions pré-installées (comme equiv ou recherV, ou n'importe) dans une macro.
Merci de votre future aide :)
A voir également:
- Fonction excel dans une fonction perso en VBA
- Fonction si et - Guide
- Fonction moyenne excel - Guide
- Liste déroulante excel - Guide
- Déplacer une colonne excel - Guide
- Fonction somme excel - Guide
3 réponses
Bonjour,
Suggestion :
Décomposer votre fonction globale :
(DECALER(INDEX(Tableau;EQUIV(Valeur_cherché;Colonne/ligne;0);Ligne-1);Colonne;0;1;1)
En VBA les noms de ces fonctions sont en anglais.
1 - ) EQUIV ...
2 - ) INDEX ...
3 - ) DECALER ...
À l'aide de l'enregistreur de macro, créer chacune des fonctions de façon à obtenir la syntaxe exacte :
... Application.WorksheetFunction.Equiv(Valeur_cherchée, C_L, 0)
Éviter les caractères accentué dans les noms de variable [ Valeur_cherché ] ->[ Valeur_cherche ]
Utiliser l'instruction :
Application.Volatile ' pour rendre la function exécutable de façon automatique en début de fonction.
Pour la variable [ C_L As Range ], utiliser le mot réservé [ Optional C_L As Range ]
Je n'ai jamais utilisé le mot clé [ Optional ] dans une fonction personnalisé mais
ça devrait fonctionner comme dans une Sub.
Lupin
Suggestion :
Décomposer votre fonction globale :
(DECALER(INDEX(Tableau;EQUIV(Valeur_cherché;Colonne/ligne;0);Ligne-1);Colonne;0;1;1)
En VBA les noms de ces fonctions sont en anglais.
1 - ) EQUIV ...
2 - ) INDEX ...
3 - ) DECALER ...
À l'aide de l'enregistreur de macro, créer chacune des fonctions de façon à obtenir la syntaxe exacte :
... Application.WorksheetFunction.Equiv(Valeur_cherchée, C_L, 0)
Éviter les caractères accentué dans les noms de variable [ Valeur_cherché ] ->[ Valeur_cherche ]
Utiliser l'instruction :
Application.Volatile ' pour rendre la function exécutable de façon automatique en début de fonction.
Pour la variable [ C_L As Range ], utiliser le mot réservé [ Optional C_L As Range ]
Je n'ai jamais utilisé le mot clé [ Optional ] dans une fonction personnalisé mais
ça devrait fonctionner comme dans une Sub.
Lupin
re :
chez-moi, lorsque j'utilise la fonction, je peux laisser le paramètre [ C_L ] vide ou pointer une cellule !
Mais sans données, j'ai beau tester la fonction, je n'ai pas le résultat souhaité !
Tente l'exécution en mode pas à pas !
Lupin
chez-moi, lorsque j'utilise la fonction, je peux laisser le paramètre [ C_L ] vide ou pointer une cellule !
Mais sans données, j'ai beau tester la fonction, je n'ai pas le résultat souhaité !
Tente l'exécution en mode pas à pas !
Lupin
Bonjour à tous,
Merci gbinforme pour ton introduction toujours zen :-) Mes amitiés à ton monde :-)
Alors wlikotae je ne puis tester la fonction ou la créer pour toi n'ayant pas les données
et la structure des données en main.
Dans un premier temps, as-tu réussi à recréer chacune des parties de ta fonction !
(DECALER(INDEX(Tableau;EQUIV(Valeur_cherché;Colonne/ligne;0);Ligne-1);Colonne;0;1;1)
1.) -> Fonction #1 = Resultat1 = EQUIV(Valeur_cherché;Colonne/ligne;0)
2.) -> Fonction #2 = Resultat2 = INDEX(Tableau;Resultat1;Ligne-1)
3.) -> Fonction #3 = Resultat3 = Decaler(Resultat2;Colonne;0;1;1)
Avec l'enregistreur de macro, assure toi que lors de la création de chacune de ces parties
tu obtienne les bons mots clés VBA réservé à ces fonctions. De plus pour la position de
la déclaration de la variable C_L, celle-ci doit être obligatoirement en fin de déclaration
pour utiliser le mot clé [ Optional ]
Voici une suggestion de code "non-testé" et pas encore complètement structuré.
Lupin
Merci gbinforme pour ton introduction toujours zen :-) Mes amitiés à ton monde :-)
Alors wlikotae je ne puis tester la fonction ou la créer pour toi n'ayant pas les données
et la structure des données en main.
Dans un premier temps, as-tu réussi à recréer chacune des parties de ta fonction !
(DECALER(INDEX(Tableau;EQUIV(Valeur_cherché;Colonne/ligne;0);Ligne-1);Colonne;0;1;1)
1.) -> Fonction #1 = Resultat1 = EQUIV(Valeur_cherché;Colonne/ligne;0)
2.) -> Fonction #2 = Resultat2 = INDEX(Tableau;Resultat1;Ligne-1)
3.) -> Fonction #3 = Resultat3 = Decaler(Resultat2;Colonne;0;1;1)
Avec l'enregistreur de macro, assure toi que lors de la création de chacune de ces parties
tu obtienne les bons mots clés VBA réservé à ces fonctions. De plus pour la position de
la déclaration de la variable C_L, celle-ci doit être obligatoirement en fin de déclaration
pour utiliser le mot clé [ Optional ]
Voici une suggestion de code "non-testé" et pas encore complètement structuré.
Option Explicit
Function RECHERCHET(Tableau As Range, _
Valeur_cherchee As String, _
Ligne As Integer, _
Colonne As Integer, _
Optional C_L As Range) As String
Dim Aux As Integer, L_Val As Integer
Dim Cell_final As Range, Aux_Cell As Range
RECHERCHET = "" : Aux = Ligne: Ligne = Aux - 1
L_Val = Application.WorksheetFunction.Match(Valeur_cherchee, C_L, 0)
Set Cell_final = Application.WorksheetFunction.Index(Tableau, L_Val, Ligne)
Set Aux_Cell = Cell_final
Set Cell_final = Aux_Cell.Offset(0, Colonne)
RECHERCHET = Aux_Cell.Value
Set Aux_Cell = Nothing
Set Cell_final = Nothing
End Function
'
Lupin
Oui j'ai testé l'enregistreur de macro. il me met "ActiveCell.FormulaR1C1 = "=MATCH(RC[-3],resEncheres_ALL_J_2006!C[-4],0)""
C'est comme ça que j'ai appris comment s'appelait Equiv en anglais.
J'ai rangé mes lignes de code comme tu l'as fait, mais j'ai toujours le même problème de nom pour C_L. Il vient d'où ce problème ? vous ne savez pas pourquoi excel tient à ce que ce soit un nombre, alors que dans ma function j'indique que c'est un Range ?
C'est comme ça que j'ai appris comment s'appelait Equiv en anglais.
J'ai rangé mes lignes de code comme tu l'as fait, mais j'ai toujours le même problème de nom pour C_L. Il vient d'où ce problème ? vous ne savez pas pourquoi excel tient à ce que ce soit un nombre, alors que dans ma function j'indique que c'est un Range ?
Très belle analyse du problème posé comme d'habitude et bien le bonjour au Canada.
Il faut en effet que la fonction soit parfaitement structurée pour fonctionner de façon identique à la formule.
Et le résultat de ma fonction est #VALEUR!
Merci pour ton aide, en effet Equiv est Match en anglais :)