Fonction VBA pour excel : problème de formule qui s'applique mal

Question

Bonjour, j'ai créé une fonction permettant d'aller chercher des informations dans d'autres feuilles du fichier excel pour traiter différent cas, voici le code: ' prend l'adresse d'une cellule sous forme de String en paramètre Function getSheetCell(cellTxt As String) getSheetCell = Range(Range("A" & (ActiveCell.Row)).Value & "!" & cellTxt).Value End Function Dans la première colonne figure le nom de la feuille. La fonction fonctionne plutôt bien, sauf que lors que je vais modifier des données par exemple, j'ai des problèmes genre #value, etc qui se propagent dans toute la colonne. Exemple d'utilisation: =( -3*getSheetCell("C8")-2*getSheetCell("D8")-getSheetCell("E8")+getSheetCell("G8")+2*getSheetCell("H8")+3*getSheetCell("I8"))/getSheetCell("J8")/3 Pour calculer une moyenne de différentes options (de -3 à + 3). J'ai également le même genre de problème avec la fonction suivante: Function applyPieChart(cell) If (cell >= 0) Then ActiveCell.Font.ColorIndex = 4 applyPieChart = 3 If (cell < 0.15) Then applyPieChart = "a" End If If (cell >= 0.15 And cell < 0.35) Then applyPieChart = "f" End If If (cell >= 0.35 And cell < 0.65) Then applyPieChart = "5" End If If (cell >= 0.65 And cell < 0.85) Then applyPieChart = "p" End If If (cell >= 0.85 And cell <= 1) Then applyPieChart = "1" End If Else 'cell <0 ActiveCell.Font.ColorIndex = 3 If (cell > 0.15) Then applyPieChart = "a" End If If (cell <= 0.15 And cell > 0.35) Then applyPieChart = "f" End If If (cell <= 0.35 And cell > 0.65) Then applyPieChart = "5" End If If (cell <= 0.65 And cell > 0.85) Then applyPieChart = "p" End If If (cell <= 0.85 And cell >= 1) Then applyPieChart = "1" End If End If End Function Sont but est de spécifier un caractère particulier pour un certain pourcentage, seulement voilà, la couleur du texte se "propage" dans les cellule d'origine obtenues à l'aide de la première fonction. Comme je n'ai jamais touché à VBA, je pense que je dois faire une erreur quelque part (utilisation d'ActiveCell peut-être ?), mais où ? Merci d'avance

cs_MPi · Answer

J'essaierais comme ceci
Function getSheetCell(cell As Range)
    getSheetCell = Range("A" & ActiveCell.Row).Value & "!" & cell.Value
ou encore
    getSheetCell = Range("A" & cell.Row).Value & "!" & cell.Value
End Function

Pour la 2e, j'essaierais ceci
Function applyPieChart(cell As Range)
et tu remplaces ActiveCell par Cell

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI

SpEeDy_Fire · Answer

Merci beaucoup,
pour le getSheetCell j'ai finalement changé de la manière suivante:

Function getSheetCell(cell As Range, cell1 As String)
    getSheetCell = Range(cell.Value & "!" & cell1).Value
End Function

car il seul cell a besoin d'être adaptée à chaque copie spéciale d'une cellule, cell1 reste constant dans mon cas (il s'agit de différents cas ayant chacuns des données propres sur sa propre feuille).

Par contre pour applyPieChart en procédant de cette manière c'est la cellule passée en paramètre qui va être modifiée, du moins la couleur du texte et ça ne semble pas avoir changé mon problème.
Ce que j'ai dans ma feuille:

Les première lignes obtiennent l'information à traîter avec getSheetCell (chaque ligne = 1 cas)
Le deuxième tableau applique une police en fonction du résultat de la cellule correspondante du premier tableau (Pie Charts for Maps) et une couleur en fonction du signe.
Le problème est que mon 64.47% par exemple passe en vert parfois, et ca a tendance à se passer sur toutes les colonnes de ce type

cs_MPi · Answer

C'est possible que ce soit relié à ce que j'appellerais un "bogue" des fonctions personnalisées... Quelquefois, ces fonctions ne se raffaîchissent pas.

Lorsque la couleur n'est pas la bonne, place-toi sur une cellule avec la fonction ApplyPieChart, tape F2 puis ENTER. Est-ce que la couleur revient comme elle le devrait ?

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI

SpEeDy_Fire · Answer

J'ai essayé mais soit ça ne change rien, soit excel crash. Je pense qu'il n'aime pas mes appels multiples à getSheetCell.
Au passage j'ai remodifié : getSheetCell = Range(cell.Value & "!" & cell1)
Avec ou sans .Value ça crash parfois, donc rien de bien neuf.

Molenn · Answer

Salut, Comme quoi pour une même chose, on peut faire plein de truc différent, pour ton premier cas, la fonction que j'aurai faite moi est la suivante : Public Function getSheetCell(CelluleNomFeuille As Range, CelluleConstantetexte As String) As Currency getSheetCell = Worksheets(CelluleNomFeuille.Value).Range(CelluleConstantetexte).Value End Function Fonction qui ma foi fonctionne bien, si ce n'est que j'ai des pbs de rafraîchissements lorsque je change les valeurs dans ma seconde feuille, ce qui est normal puisqu'il n'y a pas de liaison entre ma formule et cette feuille. C'est le fait que la référence de la cellule soit passée en Strnig qui empêche le rafraichissement automatique. A mon avis, comme te l'a déjà suggéré MPi, il faudrait déclarer ton 2ème paramètre cell1 en tant que Range lui aussi. Par contre, pour ta 2ème fonction, j'avoue que je ne la comprends pas du tout : Si ta valeur est négative, elle ne peut rien faire du tout : Comment une même cellule pourrait contenir à la fois une valeur < à 0.15 et > à 0.35 ? If (cell <= 0.15 And cell > 0.35) Toute la partie si la valeur de ta cellule est négative ne peut pas fonctionner, ou du moins, est inutile en tant que telle. Ou alors quelque chose méchappe ^^ J'ai refait ta fonction de cette façon : Public Function applyPieChart(Cell As Currency) If Cell >= 0 Then 'Valeur positive ActiveCell.Font.ColorIndex = 4 Select Case Cell Case 0.85 To 1 applyPieChart = "1" Case 0.65 To 0.85 applyPieChart = "p" Case 0.35 To 0.65 applyPieChart = "5" Case 0.15 To 0.35 applyPieChart = "f" Case 0 To 0.15 applyPieChart = "a" Case Else applyPieChart = 3 End Select Else 'Valeur négative ActiveCell.Font.ColorIndex = 3 End If End Function Et moi, je n'ai aucun problème (bon, je n'ai pas la même police que toi d'installée, mais le résultat est le même). J'avoue que je n'ai aucun souci chez moi (mais je suis obligé de rafraichir manuellement à chaque fois ma formule, ce qui est loin d'être l'idéal). Molenn

SpEeDy_Fire · Answer

Ah oui Mea culpa pour la seconde fonction, dans le else j'ai oublié de changer le signe des valeurs comparées :) (j'avais perdu mon vba, du coup, retapé à la va-vite...) En fait la version correcte ressemble plutôt à ça : Function applyPieChartFunc(cell As Range) If (cell >= 0) Then ActiveCell.Font.ColorIndex = 4 applyPieChartFunc = 3 If (cell < 0.15) Then applyPieChartFunc = "a" End If If (cell >= 0.15 And cell < 0.35) Then applyPieChartFunc = "f" End If If (cell >= 0.35 And cell < 0.65) Then applyPieChartFunc = "5" End If If (cell >= 0.65 And cell < 0.85) Then applyPieChartFunc = "p" End If If (cell >= 0.85 And cell <= 1) Then applyPieChartFunc = "0" End If Else 'cell <0 ActiveCell.Font.ColorIndex = 3 If (cell > -0.15) Then applyPieChartFunc = "a" End If If (cell <= -0.15 And cell > -0.35) Then applyPieChartFunc = "f" End If If (cell <= -0.35 And cell > -0.65) Then applyPieChartFunc = "5" End If If (cell <= -0.65 And cell > -0.85) Then applyPieChartFunc = "p" End If If (cell <= -0.85 And cell >= -1) Then applyPieChartFunc = "0" End If End If End Function

SpEeDy_Fire · Answer

En fait le problème semble venir de activeCell. 
Lorsque que je raffraichis une donnée dans le premier tableau, si le résultat est <0, le texte passe en rouge. Donc si je comprends bien, dans mon tableau du bas qui utilise applyPieChartFunc, activeCell est en fait la cellule sélectionnée au moment ou la formule est raffraichie, et non la "vrai" cellule courante.
Y'a pas moyen de corriger ça ?

Fonction VBA pour excel : problème de formule qui s'applique mal

7 réponses

Votre réponse