Fonction excel dans une fonction perso en VBA

Fermé

wlikotae - 20 août 2008 à 15:35
eriiic Messages postés 24570 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 23 avril 2024 - 21 août 2008 à 23:43

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 :)

A voir également:

Fonction excel dans une fonction perso en VBA
Fonction si et - Guide
Liste déroulante excel - Guide
Fonction somme excel - Guide
Fonction moyenne excel - Guide
Fonction trier excel - Guide

3 réponses

Réponse 1 / 3

Utilisateur anonyme
20 août 2008 à 18:55

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

gbinforme Messages postés 14946 Date d'inscription lundi 18 octobre 2004 Statut Contributeur Dernière intervention 24 juin 2020 4 684
21 août 2008 à 00:04

bonjour Lupin.A,

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.

wlikotae
21 août 2008 à 12:09

J'ai toujours le même problème : dans la petite fenêtre qui s'ouvre quand on entre une fonction et qu'on clique sur "=", j'ai toujours #NOMBRE! comme erreur devant C_L.
Et le résultat de ma fonction est #VALEUR!

Merci pour ton aide, en effet Equiv est Match en anglais :)

Réponse 2 / 3

Utilisateur anonyme
21 août 2008 à 15:54

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

Réponse 3 / 3

Utilisateur anonyme
21 août 2008 à 13:44

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é.

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

wlikotae
21 août 2008 à 15:13

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 ?

eriiic Messages postés 24570 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 23 avril 2024 7 213 > wlikotae
21 août 2008 à 23:43

Bonsoir tout le monde,

pour info dans le fichier ...\Microsoft Office\OFFICE11\1036\VBALIST.XLS onglet 'fonctions de feuille de calcul' on a la traduction français<->anglais de toutes les fonctions.
(...\Office12\... pour excel 2007=
eric

Discussions similaires

Remplir une cellule (texte) en fonction d'une autre (texte).

Méthode find dans VBA - Recherche de données sous Excel

Discussions similaires

Newsletters