Discussion :

[xenondeele]

Bonjour,

Je suis face à un comportement étrange de VBA que je ne comprends pas. Je crée une fonction Stock dans laquelle j'utilise une variable tableau ( initialisé à partir des cellules d'une feuilles )

Code :

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

liste_catégorie = Sheets("Stock minimum").Range("A1").CurrentRegion

Quand j'appelle ma fonction via une SUB, il n'y a aucun problème.

Mais quand je teste ma fonction dans Excel, ce code ne fonctionne plus

Mais si je remplace par un tableau classique : liste_catégorie (0,0) = "Céréales", liste_catégorie (1,0) = "laitages", la fonction marche dans la feuille

C'est à en perdre la boule

Quelqu'un aurait-il une idée ?

D'avance merci !

[ARTURO83]

Bonjour,

Quel est le but atteindre? si j'ai bien compris, il semblerait que vous voulez rapatrier la valeur du stock mini en M1 pour la sous-catégorie sélectionnée, si c'est bien de cela qu'il s'agit, alors pourquoi passer par une fonction personnalisée? Une RECHERCHEV ne ferait-elle pas l'affaire?

Cdlt

[xenondeele]

bonjour,

merci de la réponse. Je connais bien évidemment la rechercheV(), mais c'est un exercice purement formel pour comprendre le VBA.

Je ne comprends pas cette bizarrerie ... le tableau est correct quand c'est une une macro qui appelle la fonction Stock() mais pas à partir d'excel quand on met la fonction Stock dans une cellule de la feuille.

:-)

[ARTURO83]

Bonjour,

Alors ceci:

Code :

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

1
2
3
4
5
6
 
Function stock(Catégorie As String) As Long
    Dim x As Object
    Set x = Sheets("Stock minimum").Range("StockMinimum").Find(Catégorie)
    If Not x Is Nothing Then stock = Sheets("Stock minimum").Cells(x.Row, 2)
End Function

Cdlt

Edit: ne pas valider la formule "=stock(L2)" avec CTRL + SHIFT + ENTREE comme vous l'avait fait auparavant.

[xenondeele]

Bonjour,

ça marche ! merci :-)

mais du coup pourriez-vous m'expliquer ceci :

Set x = Sheets("Stock minimum").Range("StockMinimum").Find(Catégorie)

d'où sort cette Range("StockMinimum) ... il n' y a pas de plage nommée StockMinimum dans le classeur, ni de colonne StockMinimum dans la feuille ?

et d'autre part, mon but était d'utiliser un tableau, car finalement vous ne l'utilisez pas :

Sheets("Stock minimum").Cells(x.Row, 2)

Mon but est vraiment de comprendre pourquoi la création de tableau en utilisant directement une plage de cellules dans ce cas ne fonctionne pas ( alors que ça fonctionne quand je passe un appel via la sub test())

C'est un intérêt purement théorique ...

merci d'avance :-)

[ARTURO83]

"StockMinimum " est le nom du tableau structuré, je fais une recherche sur l'ensemble de la plage entière du tableau.

********************************************

Dans votre cas, avec la fonction personnalisée, "liste_catégorie" renvoie un nom, alors qu'avec une sub, cela renvoie un nombre de lignes, c'est pour cela que ça ne marche pas avec la fonction.

sinon, autre écriture qui se rapproche votre code:

Code :

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

1
2
3
4
5
6
7
8
9
10
Function stock(Catégorie As String)
    Dim i As Integer, liste_catégorie As Variant
    liste_catégorie = Sheets("Stock minimum").ListObjects("StockMinimum").DataBodyRange.Rows.Count
    For i = 2 To liste_catégorie + 1
        If Catégorie = Sheets("Stock minimum").Cells(i, 1) Then
            stock = Sheets("Stock minimum").Cells(i, 2)
            Exit For
        End If
    Next
End Function

[xenondeele]

Merci de votre réponse :-)

mais j'ai déjà crée ce type de code :-)

ce que je veux c'est créer un tableau en posant l'égalité liste_catégories = range("A1:C500") par exemple .... encore une fois ça fonctionne quand j'appelle la fonction via une sub mais pas par une fonction dans la feuille ...

je voudrais comprendre pourquoi :-)

Bonne journée :-)

[Qwazerty]

Salut

On est bien d'accord que cette fonction n'a de but que de manipuler VBA?

A mon avis, il vaudrait mieux ajouter un paramètre "Plage as range" à ta fonction. Comme ça tu lui passes ton tableau ( Stock minimum) dans plage et le critère (cellule L2). Tu peux même ajouter un paramètre pour identifier à quelle colonne doit correspondre le paramètre.

Lors de la création d'une formule perso, il faut rendre tous les éléments paramétrables, le but étant de pouvoir réutiliser cette formule dans d'autre cas de figure. Dans cette optique, faire référence à un tableau spécifique d'une feuille n'a aucun sens (ça n'est pas une critique).

Pour tenter de répondre plus particulièrement à ta question de départ.
En premier lieu, sache qu'il n'est pas possible de tout faire dans une fonction perso, si tu essais de modifier le contenu d'une cellule par exemple, ça plante. Je n'ai pas d'autre trucs qui me viennent mais il y à d'autres cas. Seconde chose, quand ça plante, ça sort manu militari et sans message d'erreur....
Si tu places un point d'arrêt dans le code de ta fonction (touche F9 pour basculer le point d'arrêt).
Lorsque tu mets à jour ta formule sur ta feuille, Excel va faire un arrêt sur la ligne contenant le point, tu pourras ensuite utiliser F8 pour exécuter ligne par ligne.
en observant le contenu de tes variables (regarde la notion d'espion en VBA) tu verras que liste_catégorie ne contient pas ton tableau complet mais uniquement "ss Catégorie" (dans la valeur de la cellule A1).
Du coup, vu que liste_catégorie est un variant, au lieu de prendre comme sous type un Array, il prend un String (tu peux le voir en plaçant un espion sur ta variable). Et logiquement "UBound(liste_catégorie)" fait planter ta fonction -> Sortie de code sans message d'erreur.

Pourquoi ça ne le fait pas en passant hors formule tableau... aucune idée mais comme dit plus haut, le comportement en version formule perso est un peu altéré et visiblement, il n'a pas accès à CurrentRegion qui d'ailleurs n'a aucun intérêt dans ton cas puisque tu utilises (àet tu as bien raison) un tableau structuré!

Code :

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

liste_catégorie = Sheets("Stock minimum").Range("A1").ListObject.Range

Et ton problème est résolu

Un conseil général (qui n'est pas en lien avec ton problème) :évite les accents sur les noms de variables et préfères y ajouter au moins une majuscule pour voir si tu l’orthographies correctement dans l'éditeur (VBA ajoutera la majuscule au passage à la ligne).
Dans le même but, en début de Module, ajoute "Option Explicit", ça oblige à tout déclaré et du coup si une variable est mal orthographiée, tu as une alerte.

++
Qwaz

[xenondeele]

Merci de la réponse.

Effectivement, si je mets un paramètre supplémentaire ( liste_categorie as range ), ça fonctionne.

ça me conforte dans l'idée qu'on ne pouvait pas faire ça dans une fonction mais je voulais être sur que je n'étais pas passé à coté d'un détail qui m'aurait échappé.

Bon week-end

[Qwazerty]

Salut

ça me conforte dans l'idée qu'on ne pouvait pas faire ça dans une fonction mais je voulais être sur que je n'étais pas passé à coté d'un détail qui m'aurait échappé.

Je ne sais pas ce que tu entends par "ne pouvait pas faire ça" mais il est possible de le faire mais pas en utilisant CurrentRegion. En utilisant directement la structure du tableau structuré ça fonctionne (cf mon message précédent).

++
Qwaz

[xenondeele]

re :-)

je voulais juste savoir si et pourquoi non la fonction ne pouvait pas se servir dans une feuille comme n'importe quelle sub ... habituellement j'utilise les variables objet et je fais des boucles for - each directement sur les objets ... pas vraiment besoin de tableau VBA :-)

Bonne fin de journée

[Qwazerty]

je voulais juste savoir si et pourquoi non la fonction ne pouvait pas se servir dans une feuille comme n'importe quelle sub ... habituellement j'utilise les variables objet et je fais des boucles for - each directement sur les objets ... pas vraiment besoin de tableau VBA :-)

Il est possible de le faire dans une fonction perso, il faut juste trouver un autre moyen que CurrentRegion pour cibler tes données (il y en a plein). Même si le paramètre reste bien sûr la meilleur option .

++
Qwaz

[xenondeele]

Effectivement le problème vient de l'utilisation de currentregion ... pour le reste sinon ça fonctionne 😊
Merci 😊