Exécuter une fonction personnalisée sur cellule variable

**gedaes** · 14/12/2017, 14h11

Bonjour à tous,

Tout d'abord je tiens à préciser que l'on va sûrement me conseiller de faire cela sous Excel et non VBA mais je n'ai pas le choix, c'est un projet d'études VBA obligatoirement.

Mon problème:
Je suis sur un userform qui remplit une base de données vendeurs sur ma feuille excel (code unique, nom, prénom, date de naissance etc...) chacun sur une ligne. La colonne G doit correspondre au pourcentage du nombre de transactions réalisées par le vendeur. Ces transactions sont résumées sur une autre feuille où figure évidemment le code du vendeur je me suis donc dit que je pouvais passer par une fonction personnalisée via un CountIf comme suit :

Code :

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

1
2
3
4
5
6
7
Function proportionDesVentes(sellerID As Variant, transacList As Range) As Double
Application.Volatile
Dim nombreapparition As Integer, totaltrans As Integer
nombreapparition = Application.WorksheetFunction.CountIf(transacList, sellerID)
totaltrans = Sheets("Liste des transactions").Cells(Rows.Count, 4).End(xlUp).Row - 1
proportionDesVentes = (nombreapparition) * 100 / (totaltrans)
End Function

Ma fonction fonctionne parfaitement quand je l'utilise directement sur Excel comme une fonction basique. Néanmoins je souhaiterai que en validant mon userform la fonction s'applique automatiquement dans la cellule G pour la ligne que je crée ET que la formule apparaisse (pas juste le résultat). J'ai essayé ceci mais en vain après des heures à passer par des variables ou non je n'y arrive pas surtout que si je fais afficher une Msgbox avec contenu cela m'affiche bien le code unique mais pourquoi la fonction ne s'y applique pas?

Code :

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

1
2
3
4
5
6
With Sheets ("Liste des vendeurs")
'récupère le code unique du vendeur qui est en colonne A et sur la ligne créée par l'userform
contenu = Cells(Derlignevendeur + 1, 1).Value   
 'calcule le pourcentage du nombre total de ventes qu'il ou elle a réalisé
.Cells(Derlignevendeur + 1, 7).FormulaLocal = "=proportionDesVentes(contenu ;'Liste des transactions'!D:D)"
End With

Je vous remercie de m'apporter des éclairages!

**Daranc** · 14/12/2017, 15h06

as tu essayé dans ce style

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

.Cells(Derlignevendeur + 1, 7).FormulaR1C1= "=formule"

**gedaes** · 14/12/2017, 15h14

Oui j'ai essayé toutes les formes de .Formula à .FormulaLocal en passant par .FormulaR1C1

Je ne sais pas si j'ai été claire, mais avec .FormulaLocal ma fonction s'affiche bel et bien dans ma cellule souhaitée mais elle ne récupère pas le contenu de la A correspondant à la ligne donc le résultat affichée est toujours 0 c'est pourquoi je pense que le problème vient de ma variable contenu que ma fonction n'arrive pas à prendre en compte...

**Daranc** · 14/12/2017, 15h22

Envoyé par gedaes

Oui j'ai essayé toutes les formes de .Formula à .FormulaLocal en passant par .FormulaR1C1

Je ne sais pas si j'ai été claire, mais avec .FormulaLocal ma fonction s'affiche bel et bien dans ma cellule souhaitée mais elle ne récupère pas le contenu de la A correspondant à la ligne donc le résultat affichée est toujours 0 c'est pourquoi je pense que le problème vient de ma variable contenu que ma fonction n'arrive pas à prendre en compte...

elle s'affiche en texte
peut être une question de langue anglais/français
si c'est le cas , normalement en te plaçant dans la formule et faisant entrée ou F9 le calcul devrait se faire

**gedaes** · 14/12/2017, 15h25

En me plaçant sur la formule dans la cellule Excel c'est bien cela? Non je fais entrée, le calcul se refait mais me redonne 0

J'ai essayé de ne pas passer par cette variable contenu et de donner directement dans l'application de ma fonction la variable cherchée mais je n'ai pas non plus réussi...

**Daranc** · 14/12/2017, 16h11

et un truc de ce genre

Code :

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

Cells(Derlignevendeur + 1, 7).FormulaLocal = "=proportionDesVentes(R0C-7 ;'Liste des transactions'!D:D)"

**gedaes** · 14/12/2017, 16h16

toujours 0 alors que la formule est bien écrite dans la barre mais que pourtant je devrais avoir 1/2...

cela pourrait-il venir du type que j'ai donné à ma première variable dans ma déclaration de fonction par hasard? Variant convient-il pour stocker les données à l'intérieur d'une cellule?
Function proportionDesVentes(sellerID As Variant, transacList As Range) As Double

**Daranc** · 14/12/2017, 16h25

variant c'est le passe partout, ça prend juste le plus de place en mémoire , c'est Excel qui s'adapte
essayes peut etre de concatener ta formule
"=maformule(" & ma variable & " suite "

**gedaes** · 14/12/2017, 16h57

Sous tes conseils j'ai fait ceci c'est cela? c'est ce que tu entendais par là?

Code :

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

.Cells(Derlignevendeur + 1, 7).FormulaLocal = "=proportionDesVentes(""& contenu &"" ;'Liste des transactions'!D:D)"

j'ai doublé les guillemets pour que ça ne se confonde pas avec la fin de formule mais j'ai un message d'erreur 1004 "Erreur définie par l'application ou par l'objet"

**Daranc** · 14/12/2017, 17h11

le doublement de guillemet peut etre devant

Code :

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

.Cells(Derlignevendeur + 1, 7).FormulaLocal = ""=proportionDesVentes( " & contenu & " ; Liste des transactions!D:D)""

mais c'est sans garantie, le formula je m'en sers tres peu

**gedaes** · 14/12/2017, 18h22

non toujours pas... le problème reste entier je ne sais pas ce qui cloche dans mon appel de fonction, merci de toutes tes propositions en tout cas!

**unparia** · 14/12/2017, 18h36

Bonjour
Sans préjuger (je ne m'intéresse qu'à la syntaxe de la formule) de ce que fait la fonction, la formule devrait être, si contenu est une variable string :

Code :

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

"=proportionDesVentes(""" & contenu & """ ; Liste des transactions!D:D)"

pour formulalocal
et/ou

Code :

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

"=proportionDesVentes(""" & contenu & """ , Liste des transactions!D:D)"

pour formula

**gedaes** · 14/12/2017, 18h45

ta réponse m'a l'air d'avoir cerné en quelque sorte le problème car désormais dans ma cellule s'affiche bel et bien ma formule c'est déjà un progrès merci!

Néanmoins il doit toujours exister un problème car elle se traduit comme ceci :

Code :

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

=proportionDesVentes( ""; Liste des transactions!D:D)

comme tu le vois la première valeur n'est pas récupérée et même fait étrange, excel m'ouvre une fenêtre pour que j'aille la sélectionner dans mes documents sur mon ordinateur!!!

Nom : Capture.JPG
Affichages : 578
Taille : 157,3 Ko

Nom : Capture.JPG
Affichages : 578
Taille : 157,3 Ko

**gedaes** · 14/12/2017, 18h59

le problème semble venir du deuxième paramètre ici en fait Excel m'ouvre cette fenêtre pour que je lui donne la plage où chercher

Moi je souhaiterai qu'il cherche "contenu" dans toutes les cellules de la colonne D de la feuille "Liste des transactions"...

**joe.levrai** · 14/12/2017, 19h15

Bonjour,

tu as oublié l'apostrophe devant et derrière le nom de ta feuille, obligatoire quand il y a des espaces dans le nom
donc excel cherche une référence appelée "le premier mot du nom de ta feuille"

pour le contenu vide fait tourner ta fonction en pas à pas pour voir la raison de ta variable vide

**gedaes** · 14/12/2017, 19h30

on approche je pense car désormais cela marche mais pas à tous les coups!
une fois ça marche il récupère la bonne variable et une fois ça ne marche pas la variable est encore vide je ne comprends pas
et je ne sais pas me servir du mode pas à pas désole je suis une débutante..

**joe.levrai** · 14/12/2017, 20h27

Si ça ne marche pas à tous les coups ... alors c'est que ça marche !

A toi d'isoler les conditions pour lesquelles contenu ne remonte rien ... par exemple s'il interroges une cellule vide

pour le pas à pas, il est fondamental de maîtriser pour être autonome : http://silkyroad.developpez.com/VBA/DebuterMacros/
pareil pour la fenêtre d'inspection des variables

**Daranc** · 15/12/2017, 07h12

Envoyé par unparia

Bonjour
Sans préjuger (je ne m'intéresse qu'à la syntaxe de la formule) de ce que fait la fonction, la formule devrait être, si contenu est une variable string :

Code :

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

"=proportionDesVentes(""" & contenu & """ ; Liste des transactions!D:D)"

pour formulalocal
et/ou

Code :

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

"=proportionDesVentes(""" & contenu & """ , Liste des transactions!D:D)"

pour formula

bon sang! mais c'est bien sûr : le Parkinson sur la touche 3#"

autre chose , peut il mettre au final dan le string ( ça fait bizarre écrit comme ça) le nom de sa feuille qui semble ne pas bouger ?
du style

Code :

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

 "=proportionDesVentes(""" & contenu &  , Liste des transactions!D:D"""  & )"

**gedaes** · 15/12/2017, 17h17

Bonjour à tous, merci pour toutes vos réponses elles m'ont toutes un peu aidé!

Ne réussissant pas à comprendre pourquoi cela marchait une fois sur je ne sais combien j'ai exécuté ma fonction à la main comme une fonction de base sur excel directement et j'ai fait appel à ce bon vieux enregistreur de macro pour comprendre. Grâce à lui j'ai totalement remanié mon code en R1C1 et BINGOOOOOO! Depuis cela fonctionne parfaitement et avec un petit Application Volatile tout ça se met à jour!

Je vous remercierai jamais assez vous m'avez aidé à sauver ma note de programmation merci même si je tiens à le préciser car certains ont dit "il" : je suis bel et bien une fille qui fait de la macro VBA oui oui ça existe les programmeuses non mais oh

Pour ceux que ça intéresse mon code final est tout simplement :

Code :

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

.Cells(Derlignevendeur + 1, 7).FormulaR1C1 = "=proportionDesVentes(RC[-6],'Liste des transactions'!C[-3])"

Exécuter une fonction personnalisée sur cellule variable [XL-2016]

Macros et VBA Excel

Discussions similaires

Partager

Partager