Criar função VBA para retornar array

Como o título sugere, aprenderemos como criar uma função definida pelo usuário no Excel que retorna um array. Já aprendemos como criar uma função definida pelo usuário no VBA. Portanto, sem perder tempo, vamos começar com o tutorial.

O que é uma função Array?

Funções de array são as funções que retornam um array quando usado. Essas funções são usadas com combinações de teclas CTRL + SHIFT + ENTER e é por isso que preferimos chamar a função de matriz ou fórmulas como a função CSE e fórmulas.

A função de matriz do Excel geralmente consiste em fórmulas de matriz de várias células. Um exemplo é a função TRANSPOSE.

Criação de uma função de array UDF no VBA

Portanto, o cenário é que eu só quero retornar os 3 primeiros números pares usando a função ThreeEven ().

O código ficará assim.

Função ThreeEven () As Integer () 'define a matriz Dim números (2) As Integer' Atribui valores aos números da matriz (0) = 0 números (1) = 2 números (2) = 4 'retorna valores ThreeEven = números Função final 

Vamos usar esta função na planilha.

Você pode ver isso, primeiro selecionamos três células (horizontalmente, para vertical, temos que usar uma matriz bidimensional. Abordamos isso abaixo.). Então começamos a escrever nossa fórmula. Em seguida, pressionamos CTRL + SHIFT + ENTER. Isso preenche as células selecionadas com os valores da matriz.

Observação:

• Na prática, você não saberá de quantas células precisará. Nesse caso, sempre selecione mais células do que o comprimento esperado da matriz. A função preencherá as células com matriz e as células extras exibirão o erro # N / A.
• Por padrão, esta função de matriz retorna valores em uma matriz horizontal. Se você tentar selecionar células verticais, todas as células mostrarão apenas o primeiro valor da matriz.

Como funciona?

Para criar uma função de array, você deve seguir esta sintaxe.

Função functionName (variáveis) As returnType () dim resultArray (length) as dataType 'Atribuir valores ao array aqui functionName = resultArray End Function 

A declaração da função deve ser conforme definido acima. Isso declarou que é uma função de matriz.
Ao usá-lo na planilha, você deve usar a combinação de teclas CTRL + SHIFT + ENTER. Caso contrário, ele retornará apenas o primeiro valor da matriz.

Função de array VBA para retornar array vertical

Para fazer com que a função do array UDF funcione verticalmente, você não precisa fazer muito. Apenas declare os arrays como um array bidimensional. Então, na primeira dimensão, adicione os valores e deixe a outra dimensão em branco. É assim que se faz:

Função ThreeEven () As Integer () 'definir números Dim da matriz (2,0) As Integer' Atribuir valores aos números da matriz (0,0) = 0 números (1,0) = 2 números (2,0) = 4 ' valores de retorno TrêsEven = números Função final 

É assim que você o usa na planilha.

Função de matriz UDF com argumentos no Excel

Nos exemplos acima, simplesmente imprimimos os valores estáticos da soma na folha. Digamos que queremos que nossa função aceite um argumento de intervalo, execute algumas operações neles e retorne o array resultante.

Exemplo Adicione "-done" a cada valor no intervalo

Bem, eu sei que isso pode ser feito facilmente, mas apenas para mostrar como você pode usar funções de array VBA definidas pelo usuário para resolver problemas.

Portanto, aqui eu quero uma função de matriz que pegue um intervalo como um argumento e adicione "-done" a cada valor no intervalo. Isso pode ser feito facilmente usando a função de concatenação, mas usaremos uma função de matriz aqui.

Função CONCATDone (rng As Range) As Variant () Dim resulArr () As Variant 'Criar uma coleção Dim col As New Collection' Adicionando valores à coleção On Error Resume Next For each v In rng col.Add v Next On Error GoTo 0 ' completar a operação em cada valor e adicioná-los ao Array ReDim resulArr (col.Count - 1, 0) For i = 0 Para col.Count - 1 resulArr (i, 0) = col (i + 1) & "-done" Next CONCATDone = função final resulArr 

Explicação:

A função acima aceitará um intervalo como um argumento e adicionará "-done" a cada valor no intervalo.

Você pode ver que usamos uma coleção VBA aqui para manter os valores da matriz e, em seguida, fizemos nossa operação em cada valor e os adicionamos de volta em uma matriz bidimensional.

Então sim pessoal, é assim que você pode criar uma função de array VBA personalizada que pode retornar um array. Espero que tenha sido explicativo o suficiente. Se você tiver alguma dúvida sobre este artigo, coloque-o na seção de comentários abaixo.

Clique no link abaixo para baixar o arquivo de trabalho:

Criar função VBA para retornar array

Arrays no Excel Formul | Saiba o que são arrays no Excel.

Como criar uma função definida pelo usuário através do VBA. Aprenda a criar funções definidas pelo usuário no Excel

Usando uma função definida pelo usuário (UDF) de outra pasta de trabalho usando VBA no Microsoft Excel | Use a função definida pelo usuário em outra pasta de trabalho do Excel

Retornar valores de erro de funções definidas pelo usuário usando VBA no Microsoft Excel | Aprenda como você pode retornar valores de erro de uma função definida pelo usuário

Artigos populares:

50 atalhos do Excel para aumentar sua produtividade

A função VLOOKUP no Excel

CONT.SE no Excel 2016

Como usar a função SUMIF no Excel