Uso de fórmulas de array dentro do VBA

Question

As funções da matriz do Excel em planilhas são úteis. Por exemplo. para calcular a média dos valores de sin de uma matriz A1: A5 eu insiro o seguinte que funciona bem:

{=AVERAGE(SIN(A1:A5))}

Agora eu gostaria de criar uma função no VBA que faça o trabalho, algo como:

Function MyFunction(r As Variant) As Variant     ' r is the range, e.g. A1:A5 as above  
    MyFunction = WorksheetFunction.Average(Sin(r))  
End Function  

Esta função deve resultar em um único valor, mas não funciona (não importa se eu o insiro como uma fórmula normal ou de matriz em uma célula do Excel). Eu pensei que o problema pode ser que a função VBA sen é diferente da função sin da planilha, mas substituindo sin por WorksheetFunction.Sin também não funciona.

É claro que podemos descobrir o número de valores, escrever um loop que armazena sin(r[i=1..n]) valores em uma variável de matriz temporária e, em seguida, calcular a média, mas estou procurando uma solução mais eficiente e eficiente.

    

Answer 1

O problema aqui é que a função Sin espera um único valor como um parâmetro, não uma matriz

Observe que a função Sin do VBA não é igual à função da planilha Sin (e Sin não é membro do objeto WorkSheetFunction , portanto, WorksheetFunction.Average(WorksheetFunction.Sin(r)) também não funcionará)

Se você alterar seu udf para

Function MyFunction(r As Variant) As Variant     ' r is the range, e.g. A1:A5 as above  
    MyFunction = WorksheetFunction.Average(r)  
End Function  

você obterá a média do intervalo especificado, que mostra que o udf pode manipular dados de matriz.

Se você está procurando um método eficiente, meu conselho é ficar com funções nativas onde você pode (matriz ou não), como uma chamada para um udf, qualquer udf , é muito mais lenta do que funções nativas.