Afficher matrice de VBA dans Excel - faire CTRL+SHIFT+ENTER

Bonjour forum.

J'ai fais une fonction qui fait des calcul matriciel de moindres carrés. A l'affichage dans excel, je ne sais comment reproduire le CTRL+SHIFT+ENTER depuis VBA.

Mon code:

Sub estimation()
    Dim A1 As Range, A2 As Range, F1 As Worksheet, F2 As Worksheet, c, Y1 As Range, Y2 As Range, M As Range 'Y1() As Variant, Y2() As Variant, M() As Variant,

    Set F1 = ThisWorkbook.Worksheets("Estimation1")
    Set F2 = ThisWorkbook.Worksheets("Estimation2")
    Set Y1 = F1.Range(F1.Cells(2, 2), F1.Cells(Range("B" & Rows.Count).End(xlUp).Row, 2))
    Set Y2 = F1.Range(F1.Cells(2, 3), F1.Cells(Range("C" & Rows.Count).End(xlUp).Row, 3))
    Set M = F1.Range(F1.Cells(2, 4), F1.Cells(Range("D" & Rows.Count).End(xlUp).Row, F1.Range("D2").End(xlToRight).Column))

    Set A1 = Range(F2.Cells(2, 2), F2.Cells(F1.Range("A1").End(xlToRight).Column, 2))
    Set A2 = Range(F2.Cells(2, 3), F2.Cells(F1.Range("A1").End(xlToRight).Column, 3))

    F2.Range("A1").FormulaArray = "=MCO(M, Y1)"
    F2.Range("B1").FormulaArray = "=MCO(M, Y2)"
End Sub

Private Function MCO(a As Variant, b As Variant) As Variant
    Dim res As Variant
    With WorksheetFunction
        res = .MMult(.MInverse((.MMult(.Transpose(a), a))), .MMult(.Transpose(a), b))
        MCO = res
    End With
End Function

L'instruction

F2.Range("A1").FormulaArray = "=MCO(M, Y1)"

m'affiche =MCO(M, Y1) dans la cellule A1 de la feuille Estimation2. Or, le résultat attendu est la matrice résultat renvoyée par la fonction MCO.

Si je mets

F2.Range("A1").FormulaArray = MCO(M, Y1)

, la macro affiche uniquement la première valeur de la matrice finale attendue au lieu de toute la matrice. Que mettre à la place de " .FormulaArray " ?

Merci pour vos idées et propositions.

Cordialement,

Y.

Bonjour,

M et Y1 sont des variables. Les plaçant dans une chaîne (entre guillemets) cela devient des caractères... revois donc ta façon de composer la formule...

Par ailleurs il faudrait vérifier que ta fonction MCO fonctionne en feuille de calcul, en l'utilisant manuellement, car si elle ne te renvoie pas le résultat que tu attends en utilisation manuelle, c'est la fonction qu'il faut commencer par déboguer.

Cordialement.

edit : ton dernier exemple semble montrer que ta fonction fonctionne, mais ton utilisation dans ce cas n'insère pas une formule mais le résultat de la fonction (ton FormulaArray peut disparaître...) Ne l'affectant qu'à une seule cellule, il est normal que tu n'aies qu'un résultat. Si la fonction renvoie un tableau, il faut l'affecter à une plage à la dimension du tableau renvoyé pour que toutes les valeurs soient affichées. (Attention, si le tableau n'a qu'une dimension, il sera affecté horizontalement, et il faudra le transposer pour l'avoir en vertical.)

Bonjour MFerrand,

La fonction est opérationnelle en effet. J'ai défini A1 et A2 comme plages résultat dans une autre simulation comme ci dessous

    Set A1 = Range(F2.Cells(2, 2), F2.Cells(F1.Range("A1").End(xlToRight).Column, 2))
    Set A2 = Range(F2.Cells(2, 3), F2.Cells(F1.Range("A1").End(xlToRight).Column, 3))

    A1 = MCO(M, Y1)
    A2=  MCO(M, Y2)

Idem au niveau du résultat. Seule la première valeur attendue est affichée. Peut être en effet comme vous l'avez dit, il faut transposer.

Je vais donc essayer avec l'instruction

    Set A1 = Range(F2.Cells(2, 2), F2.Cells(F1.Range("A1").End(xlToRight).Column, 2))
    Set A2 = Range(F2.Cells(2, 3), F2.Cells(F1.Range("A1").End(xlToRight).Column, 3))

    A1 = application.transpose(MCO(M, Y1))
    A2=  application.transpose(MCO(M, Y2))

et voir ce que cela donne.

Bien à vous,

Y.

Peut être en effet comme vous l'avez dit, il faut transposer.

Peut-être si ton résultat est un tableau unidimensionnel et que tu veuilles l'afficher sur une colonne...

Mais si tu affectes A1 et A2, qui me semblent être des plages horizontales, c'est contradictoire.

Si tu n'obtiens qu'une valeur, tu devrais tester la nature du résultat renvoyé par MCO en affichant au niveau de ta fonction les indices minimal et maximal de la matrice renvoyée (avec LBound et UBound) et vérifier également si ce tableau n'a pas une 2e dimension...

Cordialement.

(doublon involontaire)

Bonjour,

Voici un support illustratif. La modification apportée modifie étrangement le fonctionnement de la fonction MCO. Problème de compatibilité type de données? Je ne comprends plus rien.

Ce que je sais touefois: la fonction MCO est censée renvoyer un tableau à une colonne de valeurs. Les variables A1 et A2 sont des range.

Je pense à retranscrire l'idée: A1.value = MCO(M, Y1) . Je ne sais pas si c'est correct.

Nos réponses se sont croisée.

Je sais que je suis censé avoir une matrice au sens mathématique du terme. Mais MCO(a,b) est une fonction.

Je ne l'ai pas définie comme un tableau au préalable. Ma matrice renvoyée est elle une tableau aussi ou simplement une suite de nombres côtes à côtes ?

J'ai dans l'idée, essayé de suivre le fonctionnement sous excel jusque là.

Bonjour,

ci-dessous code à essayer selon ce que j'ai compris

Sub estimation()
    Dim ligne1 As Range, ligne2 As Range, F1 As Worksheet, F2 As Worksheet, c, Y1 As Range, Y2 As Range, M As Range

    Set F1 = ThisWorkbook.Worksheets("Estimation1")
    Set F2 = ThisWorkbook.Worksheets("Estimation2")
    Set Y1 = Range(F1.Cells(2, "B"), F1.Cells(Rows.Count, "B").End(xlUp))
    Set Y2 = Range(F1.Cells(2, "C"), F1.Cells(Rows.Count, "C").End(xlUp))
    Set M = Range(F1.Cells(2, "D"), F1.Cells(F1.Cells(Rows.Count, "D").End(xlUp).Row, F1.Cells(2, Columns.Count).End(xlToLeft).Column))

    Set ligne1 = F2.Range(Range(F1.Cells(1, "A"), F1.Cells(1, Columns.Count).End(xlToLeft)).Address)
    Set ligne2 = F2.Range(Range(F1.Cells(2, "A"), F1.Cells(2, Columns.Count).End(xlToLeft)).Address)

    ligne1.FormulaArray = "=MCO(" & M.Address & "," & Y1.Address & ")"
    ligne2.FormulaArray = "=MCO(" & M.Address & "," & Y2.Address & ")"
End Sub

Une suite de nombres cela ne peut pas être, il y a soit une seule valeur renvoyée, soit plusieurs et si plusieurs en VBA c'est un tableau...

Mon premier essai bloque sur la fonction qui me dit ne pas pouvoir définir INVERSEMAT (MInverse) ?

Je n'ai pas vraiment envie en ce moment de replonger dans des révisions mathématiques à ce niveau...

Ne pouvant donc déterminer la forme de la sortie de la fonction, j'ai testé les autres éléments :

Y1 est une plage qui couvre : B2:B16 [passée en argument b à ta fonction]

M est une plage qui couvre : D2:E16 (2 colonnes) [passée en argument a à ta fonction]

Ce que tu passes donc ce sont des tableaux à ta fonction, constitués par les valeurs de ces plages (les tableaux issus de plages sont toujours à deux dimensions, l'une d'elle pouvant ne comprendre qu'un seul indice).

A1 et A2 sont par contre définies comme des plages à une seule cellule ! Ton dimensionnement tourne court ! Normal car tu l'opères sur F2 vide !

Si donc tu n'as pas l'erreur que j'ai rencontrée, cela à soi seul explique que tu n'aies qu'un résultat, tes plages de réception étant limitées à une seule cellule.

Cordialement.

Bonjour Thev,

L'idée y est presque. La fonction renvoie quelque chose, c'est déjà un très bon début. Seulement, le nombre d'éléments en sortie de MCO doit être égal au nombre de colonnes de M soit ici 2.

Or la sortie des deux listes nous donne des zéros de partout. J'ai beau changer les valeurs, rien n'y fait. L'idée d'utilisation des adresses est pas mal pour contourner la situation.

Je vais creuser un peu de ce côté là. Merci beaucoup.

Bonjour MFerrand,

Merci beaucoup du retour rapide. Si je me réfère à tes explications, A1 et A2 sont censés être des tableaux aussi du coup puisqu'ils ont été définis comme ceux entrés en arguments. ( ou le fait qu'ils soient vides empêche toute considération comme tel.

PS: J'ai la même erreur en ce qui concerne INVERSEMAT (en anglais Minverse). L'erreur varie selon les modification faites sur la ligne d'instruction de A1 et A2.

Selon les essais, soit la fonction renvoie un réel, soit elle trouve une erreur sur l'instruction PRODUITMAT(MMult) ou INVERSEMAT(Minverse). Je pense en somme que si problème il y a , ce ne sera certainement pas dans la fonction MCO.( cpùùe elle a donné des résultats ( que des zéros en revanche) dans la proposition de Thev.)

Bonsoir,

En y regardant de plus près, comme Y1, Y2 et M font référence à la feuille F1 et que la formule matricielle est sur F2, il faudrait que l'adresse soit plus complète en intégrant la feuille F1.

Sub estimation()
    Dim ligne1 As Range, ligne2 As Range, F1 As Worksheet, F2 As Worksheet, c, Y1 As Range, Y2 As Range, M As Range

    Set F1 = ThisWorkbook.Worksheets("Estimation1")
    Set F2 = ThisWorkbook.Worksheets("Estimation2")
    Set Y1 = Range(F1.Cells(2, "B"), F1.Cells(Rows.Count, "B").End(xlUp))
    Set Y2 = Range(F1.Cells(2, "C"), F1.Cells(Rows.Count, "C").End(xlUp))
    Set M = Range(F1.Cells(2, "D"), F1.Cells(F1.Cells(Rows.Count, "D").End(xlUp).Row, F1.Cells(2, Columns.Count).End(xlToLeft).Column))

    Set ligne1 = F2.Range(Range(F1.Cells(1, "A"), F1.Cells(1, Columns.Count).End(xlToLeft)).Address)
    Set ligne2 = F2.Range(Range(F1.Cells(2, "A"), F1.Cells(2, Columns.Count).End(xlToLeft)).Address)

    ligne1.FormulaArray = "=MCO(" & M.Address(1, 1, 1, 1) & "," & Y1.Address(1, 1, 1, 1) & ")"
    ligne2.FormulaArray = "=MCO(" & M.Address(1, 1, 1, 1) & "," & Y2.Address(1, 1, 1, 1) & ")"
End Sub

Bonsoir Thev

Pouvez vous nous expliquer ce que fait l'instruction adresse(1,1,1,1) ? Je constate que le programme fonctionne très bien cette fois.

Seul problème, je ne suis censé avoir en sortie, qu'un tableau à ligne ici à deux colonnes. J'en ai 5 étrangement. Je ne comprends pas trop pourquoi.Surtout que les données se répètent.

Merci.

Bonsoir Thev,

Encore une question. Je n'ai pas bien compris la structure de la liste en sortie. Comment avez vous construit la plage de sortie?

Merci d'avance.

Bonjour,

Yeshua a écrit :

Pouvez vous nous expliquer ce que fait l'instruction adresse(1,1,1,1) ?

La propriété .Address par défaut ne restitue que la plage d'une feuille, par exemple A1:B4.

Si on précise .Address(1,1,1,1), on obtient le classeur et la feuille dans lesquels se trouve la plage, par exemple : [Classeur1.xlsm]Feuil1!$A$1:$B$4

Yeshua a écrit :

Comment avez vous construit la plage de sortie?

La plage de sortie de la ligne 1 de la feuille Estimation2 correspond à la plage de colonnes utilisées de la ligne 1 de la feuille Estimation1 depuis la colonne A.

Idem pour la plage de sortie de la ligne 2 de la feuille Estimation 2.

C'est ce que j'ai cru comprendre en lisant votre code.

Yeshua a écrit :

Seul problème, je ne suis censé avoir en sortie, qu'un tableau à ligne ici à deux colonnes

Dans quelle plage de la feuille F1, se situent ces 2 colonnes utilisées ??

Le plus simple serait que vous fournissiez un exemple de ce que vous voulez obtenir à partir d'un extrait non confidentiel de votre classeur.

Bonjour,

Merci de votre retour rapide. Je vous enverrai un schéma dès que je pourrai, je suis encore sur mon téléphone.

La matrice de sortie était voulue en colonne. C'est une matrice à une colonne et n lignes. Avec n=nombre de colonnes de la matrice M.

Que l'affichage soit en ligne importe peu finalement tant que cet affichage correspond à la transposée de la matrice colonne attendue et définie comme indiqué.

Merci d'avance.

Pour plus de détail, voici les tailles de matrices:

Y1=(n,1)

M =(n, p)

du coup dans le calcul:

transpose(M) = (p, n) dc transpose(M)*M = (p, n) * (n, p) = (p, p)

dc inversemat( transpose(M)*M ) = (p, p)

dc transpose(M)*Y1 = (p ,n) * (n, 1) = (p, 1)

enfin, inversemat( transpose(M)*M ) * transpose(M)*Y1 = (p, p) * (p, 1) = (p, 1)

Ce sont les opérations que fait la fonction MCO. La sortie est de dimension (p, 1). C'est à dire (p lignes, 1 colonne), avec p=nombre de colonnes de M.

Je n'ai pas trouver comment expliquer mieux.

Yeshua a écrit :

La sortie est de dimension (p, 1). C'est à dire (p lignes, 1 colonne), avec p=nombre de colonnes de M.

Cela signifie-t-il que la plage de la feuille Estimation2 à laquelle il faut appliquer la formule matricielle est A1:Ap où p est le nombre de colonnes de M ? , auquel cas le code serait le suivant :

Sub estimation()
    Dim ligne1 As Range, ligne2 As Range, F1 As Worksheet, F2 As Worksheet, c, Y1 As Range, Y2 As Range, M As Range

    Set F1 = ThisWorkbook.Worksheets("Estimation1")
    Set F2 = ThisWorkbook.Worksheets("Estimation2")
    Set Y1 = Range(F1.Cells(2, "B"), F1.Cells(Rows.Count, "B").End(xlUp))
    Set Y2 = Range(F1.Cells(2, "C"), F1.Cells(Rows.Count, "C").End(xlUp))
    Set M = Range(F1.Cells(2, "D"), F1.Cells(F1.Cells(Rows.Count, "D").End(xlUp).Row, F1.Cells(2, Columns.Count).End(xlToLeft).Column))

    F2.Range("A1").Resize(M.Columns.Count).FormulaArray = "=MCO(" & M.Address(1, 1, 1, 1) & "," & Y1.Address(1, 1, 1, 1) & ")"
End Sub

S'il faut appliquer la formule matricielle sur 2 colonnes, c'est à dire sur la plage A1:Bp, le code serait alors :

Sub estimation()
    Dim ligne1 As Range, ligne2 As Range, F1 As Worksheet, F2 As Worksheet, c, Y1 As Range, Y2 As Range, M As Range

    Set F1 = ThisWorkbook.Worksheets("Estimation1")
    Set F2 = ThisWorkbook.Worksheets("Estimation2")
    Set Y1 = Range(F1.Cells(2, "B"), F1.Cells(Rows.Count, "B").End(xlUp))
    Set Y2 = Range(F1.Cells(2, "C"), F1.Cells(Rows.Count, "C").End(xlUp))
    Set M = Range(F1.Cells(2, "D"), F1.Cells(F1.Cells(Rows.Count, "D").End(xlUp).Row, F1.Cells(2, Columns.Count).End(xlToLeft).Column))

    F2.Range("A1").Resize(M.Columns.Count, 2).FormulaArray = "=MCO(" & M.Address(1, 1, 1, 1) & "," & Y1.Address(1, 1, 1, 1) & ")"
End Sub
 

Bonjour Thev,

Merci du retour. Je vais tester les deux solutions, même si je pense que la première proposition me sied davantage.

Cordialement,

Y.