Formule Excel avec des fonctions sur Module Imbriquées

Bonjour,

J'ai crée certaines User Defined Functions sur VBA pour Excel que j'aimerais utiliser sur la feuille de calcul et pouvoir les imbriquer.

Pour simplifier disons que j'ai deux fonctions FctA et FctB, FctA passe la valeur telle qu'elle est et FctB transforme le Variant en un tableau de Double

Function FctA(A() As Double) As Double()
    FctA = A
End Function
Function FctB(A As Variant) As Double()
    Dim C() As Double
    Dim i As Integer, j As Integer, nRowsA As Integer, nColumnsA As Integer

    nRowsA = UBound(A.Value, 1)
    nColumnsA = UBound(A.Value, 2)

    ReDim C(1 To nRowsA, 1 To nColumnsA)
    For i = 1 To nRowsA
        For j = 1 To nColumnsA
            C(i, j) = CDbl(A(i, j).Value)
        Next j
    Next i
    FctB = C
End Function

FctB marche sur les feuilles excel mais FctA non car elle prend un Double et pas un Variant.

Mais ce qui m’étonne c'est que FctA(FctB(A1:B2)) par exemple ne marche pas en tant que formule. Pourquoi?

Finalement j'ai crée une troisième fonction FctC qui imbrique FctB et FctA:

Function FctC(A As Variant) As Double()
    FctC = FctA(FctB(A))
End Function

FctC(A1:B2) Marche, Pourquoi?

Comment je pourrais modifier le code pour que FctA(FctB(A1:B2)) ?

Merci d'avance

bonjour,

ma compréhension.

excel n'utilise pas les mêmes types de variables que VBA.

Il y a des cas où l'interface excel/vba/excel fait des conversions, notamment pour tout ce qui concerne les tableaux (ils sont convertis en variant)

donc pour le cas =fcta(fctb(A1:b4))

fctB(a as variant) as double() en vba cela va te renvoyer un tableau de double mais la réponse sera envoyée à excel qui la convertira en variant,puis passera ce tableau à FctA, tu auras une erreur car Fcta attend un tableau de double

par contre si tu fais ces opérations en VBA, tu n'as pas d'erreur. c'est ce que tu fais avec ta fonction C

=fctc(A1:B4)

fctc(a as variant) as double()

FctC = FctA(FctB(A))

la fonction fctC prend un variant, puis dans l'environnement VBA appelle la fctB qui convertit le tableau en double et puis qui appelle la fctA avec ce tableau de double qui renvoie à FctC un tableau de Double, la fonction renvoie à excel ce tableau de double en variant.

donc pour contourner le problème, faire comme tu as fait, utiliser une fonction qui règle ces problèmes d'interface entre VBA et Excel (ta fonction FctC)

Merci pour la réponse,

C'est ce que j'ai pensé au début, mais alors pourquoi FctC(FctC(A1:B2)) ne marche pas?

Suivant cette logique, ça devrait marcher:

Variant (A1:B2) -> FctC -> Double() -> Excel -> Variant -> FctC -> Double()

Edit:

Ok, j'ai changé FctB et ça marche FctC(FctC()) marche

Function FctB2(ByVal A As Variant) As Double()
    Dim C() As Double
    Dim A2 As Variant
    A2 = A
    Dim i As Integer, j As Integer, nRowsA As Integer, nColumnsA As Integer

    nRowsA = UBound(A2, 1)
    nColumnsA = UBound(A2, 2)

    ReDim C(1 To nRowsA, 1 To nColumnsA)
    For i = 1 To nRowsA
        For j = 1 To nColumnsA
            C(i, j) = CDbl(A(i, j))
        Next j
    Next i
    FctB2 = C
End Function

Les seuls changement c'est que j'ai virée .Value des Ubound et j'ai fait une copie de A

Mais ça me pose d'autres questions: pourquoi j'ai du crée une copie de la matrice passée à la fonction (A) que j'ai appelée A2?

J'ai fait quelques test avec FctB et FctB2 et j'arrive à la conclusion suivante:

J'ai l'impression que quand le A As Variant que je passe est un Range et pas un Variant quand je fait la selection A1:B2 sur excel. C'est pour ça que j'ai du mettre .Value dans le code et que quand je passe le Variant crée par l'excel le code ne marche pas. C'est aussi pourquoi je dois copier A (qui est un range) dans A2 (qui es un Variant) pour que ça marche dans le deux cas.

Comment palier à ça?