Existe-t-il un moyen d'évaluer des tableaux d'autres feuilles de différentes tailles avec une fonction simple?

Nov 25 2020

J'ai une fonction qui crée une fonction personnalisée et un tableau en fonction de l'évolution des circonstances dans les données sur une feuille. Si j'essaie de l'évaluer sur ma feuille principale, cela renvoie #value, mais si je l'évalue sur la feuille, le tableau en provient, il renvoie la sortie correcte. Une idée de comment contourner ce problème si la taille du tableau 3-d change?

Le tableau est renvoyé car je travaille avec une fonction sumproduct compliquée. La fonction sumproduct est la seule fonction imbriquée dans la formule d'évaluation.

Mon Evaluate VBA ressemble actuellement à:

Function Makeformula(Ref As String)
Application.Volatile
Makeformula = Evaluate(Ref)
End Function

Salut Ben,

Résumé: (1a) et (2a) ci-dessous tireront le bon résultat dans la feuille 1 si je copie et colle la sortie de (1) et (2) dans de nouvelles cellules et exécute le texte en tant que fonction (en ajoutant le signe égal) . (1) fonctionne avec l'une ou l'autre des solutions présentées et (2) ne l'est pas. (2) est un tableau car le sumproduct tire deux valeurs pour le sumproduct.

J'ai essayé les deux réponses que vous avez données en vain. Je vais joindre du code ci-dessous pour vous montrer et mettre en gras la formule Long pour rendre les choses plus faciles pour les yeux.

De même qu'avant, vos réponses fonctionnent lorsque mon sumproduct finit par générer la valeur de texte vue dans (1), mais pas (2). La seule différence est que (1) n'est pas un tableau multidimensionnel tandis que (2) l'est. Il convient également de noter que lorsque je copie et colle (1) et (2) indépendamment en tant que valeurs de texte moi-même dans des cellules vides et que je mets un signe égal au début, ils tirent la valeur correcte. Cependant, (2) n'est pas évalué correctement avec l'une ou l'autre des fonctions Makeformula que vous avez fournies même s'il peut être évalué comme du texte pur inséré dans une nouvelle cellule après la génération de la formule Sumproduct. (2) conduit à une #Valeur! erreur telle quelle.

Voici comment ils sont entrés dans la cellule avec la fonction MakeFormula. J'ai utilisé "Month End Input Frontend" pour le deuxième argument de votre fonction parce que c'est ce à quoi le sumproduct fait finalement référence s'il est exécuté indépendamment comme vu dans (1a) et (2a). Changer le 2ème argument de votre première solution en "Backend de fin de mois" ne fonctionne pas aussi bien.

(1) = MakeFormula2 ( ("SUMPRODUCT ((" & RECHERCHEV (RECHERCHEV ('Inc Stmnt' ''20 contre' '19'!$C11,'Month End Input Backend'!$B$4:$ré$78,3,FALSE),'Month End Input Backend'!$H$4:$K$27,3,FALSE)&"="&CHAR(34)&U$7 & CHAR (34) & ") (" & GAUCHE (myvlookup ($C11,'Month End Input Backend'!$B$4:$E$78,2,INDIRECT(VLOOKUP(VLOOKUP('Inc Stmnt ''20 vs ''19'!$C11, «Backend d'entrée de fin de mois»!$B$4:$D$78,3, FALSE), 'Backend d'entrée de fin de mois'!$H$4:$K$27,4, FALSE))), LEN (myvlookup ($C11,'Month End Input Backend'!$B$4:$E$78,2,INDIRECT(VLOOKUP(VLOOKUP('Inc Stmnt ''20 vs ''19'!$C11, «Backend d'entrée de fin de mois»!$B$4:$D$78,3, FALSE), 'Backend d'entrée de fin de mois'!$H$4:$K$27,4, FALSE)))) - 1) & ") (" & RECHERCHEV (RECHERCHEV ('Inc Stmnt' ''20 contre' '19'!$C11,'Month End Input Backend'!$B$4:$ré$78,3,FALSE),'Month End Input Backend'!$H$4:$27,2 K $, FALSE) & "))") , "Frontend d'entrée de fin de mois")

^ Celui-ci fonctionne

(2) = MakeFormula2 ( ("= SUMPRODUCT ((" & RECHERCHEV (RECHERCHEV ('Inc Stmnt' ''20 vs' '19'! $ C35, 'Backend d'entrée de fin de mois' '!$B$4:$D$78,3, FALSE), 'Backend d'entrée de fin de mois'!$H$4:$K$27,3, FALSE) & "=" & CHAR (34) & U $ 7 & CHAR (34) & ") (" & LEFT (myvlookup ($ C35, 'Backend d'entrée de fin de mois'!$B$4:$E$78,2, INDIRECT (RECHERCHEV (RECHERCHEV ('Inc Stmnt' '20 contre `` 19' '!$C35,'Month End Input Backend'!$B$4:$ré$78,3,FALSE),'Month End Input Backend'!$H$4:$K$27,4,FALSE))),LEN(myvlookup($C35, «Backend d'entrée de fin de mois»!$B$4:$E$78,2, INDIRECT (RECHERCHEV (RECHERCHEV ('Inc Stmnt' '20 contre `` 19' '!$C35,'Month End Input Backend'!$B$4:$ré$78,3,FALSE),'Month End Input Backend'!$H$4:$27,4 K $, FALSE)))) - 1) & ") (" & RECHERCHEV (RECHERCHEV ('Inc Stmnt' '20 vs' '19'! $ C35, 'Backend d'entrée de fin de mois' '!$B$4:$D$78,3, FALSE), 'Backend d'entrée de fin de mois'!$H$4:$K$27,2, FALSE) & "))") , "Frontend d'entrée de fin de mois")

^^ Ce n'est pas le cas.

Vous trouverez ci-dessous les résultats des parties sumproduct des formules qui peuvent extraire correctement les valeurs dans les deux cas si elles sont copiées et collées en tant que valeurs de texte dans de nouvelles cellules.


(1a) SUMPRODUCT(('Month End Input Frontend'!$C$12:$Q$12="Jan 2020")*(('Month End Input Frontend'!B13:B89="                                Sales of Product Income"))*('Month End Input Frontend'!$C$13:$Q$89))

(2a) SUMPRODUCT(('Month End Input Frontend'!$C$12:$Q$12="Jan 2020")*(('Month End Input Frontend'!B13:B89="                        Billing Fee Expense")+('Month End Input Frontend'!B13:B89="                                Medical Billing and Collection"))*('Month End Input Frontend'!$C$13:$Q$89))

Réponses

BigBen Nov 25 2020 at 22:34

Evaluate(ou Application.Evaluate) opère dans le contexte de la feuille active. Vous souhaitez utiliser Worksheet.Evaluate, vous devez donc transmettre à la fonction une référence à l'objet Worksheeten question. Vos options incluent:

  • Transmettez le nom de la feuille comme String:
Public Function MakeFormula(ByVal Ref As String, ByVal SheetName As String) As Variant
    Application.Volatile
    Makeformula = ThisWorkbook.Worksheets(SheetName).Evaluate(Ref)
End Function
  • Passez une Rangeréférence et utilisez Range.Parent. Votre formule ressemblerait à=MakeFormula("yourlongformula", 'Sheet 2'!A1)
Public Function MakeFormula(ByVal Ref As String, ByVal Cell As Range) As Variant
    Application.Volatile
    Makeformula = Cell.Parent.Evaluate(Ref)
End Function