Trabalhando com funções de VBA que retornam uma matriz no Excel 2016

fórmulas de matriz são um dos recursos mais poderosos do Excel. Se você estiver familiarizado com fórmulas de matriz, você vai ficar feliz em saber que você pode criar funções de VBA que retornam um array.

Conteúdo

Retornando uma matriz de nomes mês
Retornando uma lista ordenada

Retornando uma matriz de nomes mês

Vamos começar com um exemplo simples. A função MonthNames retorna uma matriz de 12 elementos de - você adivinhou - os nomes dos meses.

MonthNames function () MonthNames = Array ( “Janeiro”, “Fevereiro”, “March”, _ “Abril”, “pode”, “June”, “Julho”, “August”, _ “September”, “Outubro”, “November”, “dezembro”) End Function

Para usar a função MonthNames em uma planilha, você deve inseri-lo como uma fórmula de matriz de 12 células. Por exemplo, seleccionar gama A2: L2 e introduzir = MonthNames (). Em seguida, pressionar Ctrl + Shift + Enter para introduzir a fórmula de matriz em todas as 12 células seleccionadas. Confira o resultado.

Usando a função MonthNames para retornar uma matriz de 12 elementos.

Se você quiser os nomes dos meses para exibir em uma coluna, selecione 12 células em uma coluna e use esta fórmula de matriz. (Não se esqueça de introduzi-lo pressionando Ctrl + Shift + Enter.)

= Transposição (MonthNames ())

Você também pode escolher um único mês a partir da matriz. Aqui está uma fórmula (não uma fórmula de matriz) que exibe o quarto elemento da matriz: abril.

= Índice (MonthNames (), 4)

Retornando uma lista ordenada

Suponha que você tem uma lista de nomes que deseja mostrar na ordem de classificação em outro intervalo de células. Não seria bom ter uma função de planilha fazer isso para você?

Esta função personalizada faz exatamente isso: É necessário um intervalo de células como seu argumento de coluna única e, em seguida, retorna uma matriz de essas células classificadas. Gama A2: A13 contém alguns nomes. Gama C2: C13 contém esta fórmula de matriz multicelulares. (Lembre-se que você deve inserir a fórmula pressionando Ctrl + Shift + Enter.)

Usando uma função personalizada para retornar um intervalo classificado.

= Classificada (A2: A13)

Aqui está o código para a função Ordenado:

Função ordenada (RNG Como Gama) Dim SortedData () Como VariantDim celular Como RangeDim Temp Como variante, i As Long, j Como LongDim NonEmpty Como os dados de transferência longas` para SortedDataFor cada célula RngIf Não EstáVazio (celular) ThenNonEmpty = NonEmpty + 1ReDim Preservar SortedData (1 Para NonEmpty) SortedData (NonEmpty) = Cell.ValueEnd IfNext celular` Organizar a arrayFor i = 1 Para NonEmptyFor j = i + 1 Para NonEmptyIf SortedData (i) gt; SortedData (j) = ThenTemp SortedData (j) SortedData (j) = SortedData (i) SortedData (i) = TempEnd IfNext jNext i ‘transpor a matriz e retornar itSorted = Application.Transpose (SortedData) End Function

A função Ordenado começa criando uma matriz chamada SortedData. Esta matriz contém todos os valores não vazios na gama de argumento. Em seguida, a matriz SortedData é classificada, usando um algoritmo bubble-sort. Como a matriz é uma matriz horizontal, que deve ser transposta antes de ser devolvido pela função.

A Função Ordenado trabalha com uma gama de qualquer tamanho, desde que seja em uma única coluna ou linha. Se os dados não ordenada está em uma linha, sua fórmula precisa usar a função TRANSPOSE do Excel para exibir os dados ordenados horizontalmente. Por exemplo:

= Transposição (classificada (A16: L16))