VBA – Matriz – Criar e Muito Mais

Este tutorial mostrará como criar uma matriz usando uma Array no VBA.

Uma matriz é um conjunto retangular de números com a mesma quantidade de linhas e colunas. Você pode criar uma matriz no VBA preenchendo variáveis de matriz e usando loops do VBA.

Criação de uma Matriz no VBA

Sub CriarMatrizSimples()
 Dim matriz() As Integer
 Dim x, i, j, k As Integer
'redimensionar o tamanho da matriz
 ReDim matriz(1 To 3, 1 To 3) As Integer
 x = 1
 For i = 1 To 3
    For j = 1 To 3
       matriz(i, j) = x
       x = (x + 1)
    Next j
 Next i
' retornar o resultado para a planilha de uma só vez
 Range("A1:C3") = matriz
End Sub

A execução do procedimento acima retornará uma matriz simples para Range(“A1:C3”) em sua planilha.

Converter Vetor de Linha Única em uma Matriz

Considere a coluna de números abaixo. Em algum momento, você poderá querer converter uma coluna de números em uma matriz.

Usando a função abaixo, podemos criar uma matriz a partir da lista de números.

Function Criar_Matriz(Intervalo_Vetor As Range, Nr_Cols_Saida As Integer, Nr_Linhas_Saida As Integer) As Variant
 ReDim Temp_Matriz(1 To Nr_Cols_Saida, 1 To  Nr_Linhas_Saida)
 Dim Nr_Elementos_Vetor As Integer
 Dim Col_Cont As Integer, Linha_Cont As Integer

 Nr_Elementos_Vetor = Intervalo_Vetor.Rows.Count

'Eliminar condições NULAS
 If Intervalo_Vetor Is Nothing Then Exit Function
 If Nr_Cols_Saida = 0 Then Exit Function
 If Nr_Linhas_Saida = 0 Then Exit Function
 If Nr_Elementos_Vetor = 0 Then Exit Function

 For Col_Cont = 1 To Nr_Cols_Saida
    For Linha_Cont = 1 To Nr_Linhas_Saida
       Temp_Matriz(Col_Cont, Linha_Cont) = Intervalo_Vetor.Cells(((Nr_Linhas_Saida) * (Col_Cont - 1) + Linha_Cont), 1)
    Next Linha_Cont
 Next Col_Cont
 Criar_Matriz = Temp_Matriz

End Function

Podemos criar a matriz em nossa planilha do Excel, chamando a função acima.

Sub ConverterParaMatriz()
 Range("C1:H2") = Criar_Matriz(Range("A1:A10"), 2, 6)
End Sub

Converter uma Matriz em um Vetor de Linha Única

Como alternativa, você pode querer converter uma matriz em uma única linha. Considere a matriz abaixo.

Usando a função abaixo, podemos convertê-la em uma matriz de dimensão única.

Function Criar_Vetor(Intervalo_Matriz As Range) As Variant
 Dim Nr_Cols As Integer, Nr_Linhas As Integer
 Dim i As Integer
 Dim j As Integer
'obter as linhas e colunas da matriz
 Nr_Cols = Intervalo_Matriz.Columns.Count
 Nr_Linhas = Intervalo_Matriz.Rows.Count
 ReDim Temp_Matriz(Nr_Cols * Nr_Linhas)
'Eliminar condições NULAS
 If Intervalo_Matriz Is Nothing Then Exit Function
 If Nr_Cols = 0 Then Exit Function
 If Nr_Linhas = 0 Then Exit Function
'fazer um loop pela matriz - o primeiro elemento
 For j = 1 To Nr_Linhas
   'agora fazer um loop pelo segundo elemento
    For i = 0 To Nr_Cols - 1
      'atribua a uma matriz temporária de dimensão única
       Temp_Matriz((i * Nr_Linhas) + j) = Intervalo_Matriz.Cells(j, i + 1)
    Next i
 Next j
 Criar_Vetor = Temp_Matriz
End Function

Agora podemos chamar essa função usando o procedimento abaixo.

Sub GerarVetor()
 Dim Vetor() As Variant
 Dim k As Integer
 Dim Nr_Elementos
'obter a matriz
 Vetor = Criar_Vetor(Sheets("Planilha1").Range("A1:D5"))
'percorrer a matriz e preencher a planilha
 For k = 0 To UBound(Vetor) - 1
    Sheets("Planilha1").Range("G1").Offset(k, 0).Value = Vetor(k + 1)
 Next k
End Sub

Esse procedimento retornará o seguinte resultado.

Uso da WorksheetFunction.MMULT para criar uma Matriz

Considere a seguinte planilha.

Podemos usar o procedimento abaixo para calcular o valor dos juros para a grade acima com base na taxa de juros e no valor que a pessoa deseja emprestar.

Sub UsarMMULT()
 Dim rngJuros As Range
 Dim rngEmprestimo As Range
 Dim Resultado() As Variant
'preencher nossos objetos de intervalo
 Set rngJuros = Range("B4:B9")
 Set rngEmprestimo = Range("C3:H3")
'usar a fórmula MMULT para preencher a matriz de resultados
 Resultado = WorksheetFunction.MMult(rngJuros, rngEmprestimo)
'preencher a planilha
 Range("C4:H9") = Resultado
End Sub

Você notará que, na planilha acima, o procedimento acima preenche as células com valores em vez de fórmulas – veja C4 no quadro acima – ele tem o valor 200, não uma fórmula. O uso do métodoWorksheetFunction sempre retorna um valor estático para a planilha, e não uma fórmula. Isso significa que, se a taxa de juros ou o valor do empréstimo for alterado, os valores correspondentes na matriz preenchida NÃO serão alterados.

Em vez de usar o WorksheetFunction.MMULT, você pode usar o VBA para aplicar a função MMULT a uma célula usando o método FormulaArray .

Sub InserirMMULT()
 Range("C4:H9").FormulaArray = "=MMULT(B4:B9,C3:H3)"
End Sub

Observe que agora, quando a planilha é preenchida, uma fórmula é usada nas células.