Existe uma maneira de avaliar matrizes de outras planilhas de tamanhos variados com uma função simples?
Eu tenho uma função que cria uma função e matriz personalizadas com base nas circunstâncias em mudança nos dados em uma folha. Se eu tentar avaliá-lo na minha planilha principal, ele retornará #valor, mas se eu avaliá-lo na planilha, o array vem dele retorna a saída correta. Alguma idéia de como contornar isso se o tamanho do array 3D está mudando?
A matriz é retornada porque estou trabalhando com uma função sumproduct complicada. A função sumproduct é a única função aninhada na fórmula de avaliação.
Meu Avaliar VBA atualmente se parece com:
Function Makeformula(Ref As String)
Application.Volatile
Makeformula = Evaluate(Ref)
End Function
Olá ben,
Resumo: (1a) e (2a) abaixo obterão o resultado correto na Planilha 1 se eu copiar e colar a saída de (1) e (2) em novas células e executar o texto como uma função (adicionando o sinal de igual) . (1) funciona com qualquer solução apresentada e (2) não funciona como está. (2) é um array, pois sumproduct puxa dois valores para sumproduct.
Tentei as duas respostas que você deu, sem sucesso. Vou anexar alguns códigos abaixo para mostrar a você e negrito a fórmula longa para tornar as coisas mais fáceis para os olhos.
Da mesma forma que antes, suas respostas funcionam quando meu produto final acaba gerando o valor de texto visto em (1), mas não em (2). A única diferença é que (1) não é uma matriz multidimensional, enquanto (2) é. Também é importante notar que quando eu copio e colo (1) e (2) independentemente como valores de texto em células em branco e coloco um sinal de igual à esquerda, eles puxam o valor correto. No entanto, (2) não é avaliado corretamente com nenhuma das funções Makeformula fornecidas, embora possa ser avaliado como texto puro inserido em uma nova célula após a fórmula Sumproduct ser gerada. (2) leva a um #Value! erro como está.
Abaixo está como eles são inseridos na célula com a função MakeFormula. Usei "front-end de entrada de fim de mês" para o segundo argumento em sua função porque é a isso que o sumproduct faz referência se executado independentemente, conforme visto em (1a) e (2a). Alterar o segundo argumento de sua primeira solução para "Backend de fim de mês" não funciona tão bem.
(1) = MakeFormula2 ( ("SUMPRODUCT ((" & VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '!$C11,'Month End Input Backend'!$B$4:$D$78,3,FALSE),'Month End Input Backend'!$H$4:$K$27,3,FALSE)&"="&CHAR(34)&U$7 & CHAR (34) & ") (" & LEFT (myvlookup ($C11,'Month End Input Backend'!$B$4:$E$78,2,INDIRECT(VLOOKUP(VLOOKUP('Inc Stmnt ''20 vs ''19'!$C11, 'Backend de entrada de final de mês'!$B$4:$D$78,3, FALSE), 'Backend de entrada de final de mês'!$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 de entrada de final de mês'!$B$4:$D$78,3, FALSE), 'Backend de entrada de final de mês'!$H$4:$K$27,4, FALSE)))) - 1) & ") (" & VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '!$C11,'Month End Input Backend'!$B$4:$D$78,3,FALSE),'Month End Input Backend'!$H$4:$K $ 27,2, FALSE) & "))") , "Frontend de entrada de final de mês")
^ Este funciona
(2) = MakeFormula2 ( ("= SUMPRODUCT ((" & VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '! $ C35,' Mês Final Input Backend '!$B$4:$D$78,3, FALSE), 'Backend de entrada de final de mês'!$H$4:$K$27,3, FALSE) & "=" & CHAR (34) & U $ 7 & CHAR (34) & ") (" & LEFT (myvlookup ($ C35, 'Backend de entrada de final de mês'!$B$4:$E$78,2, INDIRETO (VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '!$C35,'Month End Input Backend'!$B$4:$D$78,3,FALSE),'Month End Input Backend'!$H$4:$K$27,4,FALSE))),LEN(myvlookup($C35, 'Backend de entrada de final de mês'!$B$4:$E$78,2, INDIRETO (VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '!$C35,'Month End Input Backend'!$B$4:$D$78,3,FALSE),'Month End Input Backend'!$H$4:$K $ 27,4, FALSE)))) - 1) & ") (" & VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '! $ C35,' Mês Final Input Backend '!$B$4:$D$78,3, FALSE), 'Backend de entrada de final de mês'!$H$4:$K$27,2, FALSE) & "))") , "Frontend de entrada de final de mês")
^^ Este não.
Abaixo estão os resultados das porções sumproduct das fórmulas que podem extrair os valores corretamente em ambos os casos, se copiados e colados como valores de texto em novas células.
(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))
Respostas
Evaluate(ou Application.Evaluate) opera no contexto da planilha ativa. Você deseja usar Worksheet.Evaluate, portanto, é necessário passar à função uma referência à Worksheetem questão. Suas opções incluem:
- Passe o nome da folha como
String:
Public Function MakeFormula(ByVal Ref As String, ByVal SheetName As String) As Variant
Application.Volatile
Makeformula = ThisWorkbook.Worksheets(SheetName).Evaluate(Ref)
End Function
- Passe uma
Rangereferência e useRange.Parent. Sua fórmula seria semelhante a=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