Un beau défi: Concaténer sous Excel formules personnelles et formules classiques

[deosantiago]

Bonjour à tous,

Entre amoureux de vba, je vous soumets un beau défi. Je tire d'avance mon chapeau à celui où celle qui connaît une réponse à ce problème sur lequel j'ai déjà pas mal planché

1) J'ai commencé à coder une fonction personnelle Excel très pratique appelée RECUPADRESSE().
Cette fonction a pour variable un nombre non fixé de cellules ou de plages.
Elle renvoit l'adresse de toutes les celulles qui, parmi celles entrées comme paramètres, vérifient certains critères spécifiés directement dans le code.

2) Je m'explique:si on a mis comme critère dans le code qu'on ne garde que l'adresse des celulles en gras, RECUPADRESSE(A2:B6; $G$8; F4:F7) renvoit $G$8; F4:F7 si ces cellules sont en gras et que les cellules A2:B6 ne sont pas en gras.

3) L'objectif est par exemple de pouvoir faire la somme des valeurs des cellules en gras. Remarque: je suis tout à fait capable de faire une fonction qui additionne les celulles en gras. Ce n'est pas ça que je cherche. L'intérêt c'est de pouvoir utiliser des fonctions plus compliquées comme RECHERCHEV(A6;RECUPADRESSE();...) ou NB.SI(RECUPADRESSE();...) et de changer rapidement les critères de la fonction RECUPADRESSE directement dans le code.


J'ai codé la chose suivante qui me renvoit l'adresse exactement comme je veux mais quand je l'insère dans une fonction Excel classique, celle ci renvoit #VALEUR#

Public Function RECUPADRESSE(ParamArray PlageMultiple()) As String
Dim Adresse As String
Adresse = ""

Select Case UBound(PlageMultiple())
Case 0
Adresse = PlageMultiple(0).Address
Case 1
Adresse = PlageMultiple(0).Address & ";" & PlageMultiple(1).Address
Case Else
Adresse = PlageMultiple(0).Address
For i = LBound(PlageMultiple()) + 1 To UBound(PlageMultiple())
Adresse = Adresse & ";" & PlageMultiple(i).Address
Next i
End Select

RECUPADRESSE = Adresse

End Function


Un grand merci d'avance à l'expert qui arrivera à résoudre ce problème qui me dépasse un peu.


Bonne journée

 

[Gareth]

Re : Un beau défi: Concaténer sous Excel formules personnelles et formules classiques

Bonjour,

Ci-dessous une version simplifiée de ton code :

Public Function RECUPADRESSE(ParamArray PlageMultiple())
Address = ""
For i = LBound(PlageMultiple()) To UBound(PlageMultiple())
    Adresse = Adresse & ";" & PlageMultiple(i).Address
Next i
RECUPADRESSE = Right(Adresse, Len(Adresse) - 1)
End Function

Il faut renvoyer un objet range plutot qu'une string pour faire ce que tu veux faire (ce que j'ai compris).
Cependant, la fontion RECHECHEV ne fontionne pas sur une zone discontinue ... tu veux faire quoi exactement ?

 

[Dranreb]

Re : Un beau défi: Concaténer sous Excel formules personnelles et formules classiques

Bonjour.
Je pense qu'il faut au contraire renvoyer un tableau de variant:

Function test() As Variant()
test = WorksheetFunction.Transpose(Array(1, 2, 3))
End Function

=SOMME(test())

renvoie 6 dans une cellule.


Autre exemple:

Function Gras(Plage As Range) As Variant()
Dim L As Long, TV() As Variant
ReDim TV(1 To Plage.Rows.Count, 1 To 1)
For L = 1 To Plage.Rows.Count
   TV(L, 1) = -Plage(L, 1).Font.Bold
   Next L
Gras = TV
End Function

=SOMMEPROD(B7:B11;Gras(B7:B11))

Avec en B7:B11 1,2,3,4,5 et 2 et 4 en gras, renvoie 6 également
Attention: la fonction n'est pas forcément réévaluée toute seule quand on met une des cellules impliquées en gras.
Cordialement.
À +