Para praticar o uso de arrays, criaremos nossa própria versão da macro que usamos para demonstrar a vantagem de velocidade dos arrays, passo a passo...
Este é o ponto de partida para este exercício (você verá que o conjunto de dados foi reduzido para 1.000 linhas):
Aqui você pode baixar o arquivo de exemplo Excel: arrays_exercise.xls
Objetivo do exercício: O procedimento deve percorrer os dados do conjunto de dados e contar o número de respostas SIM ou NÃO para cada ano e para cada número de cliente (SIM ou NÃO dependendo da escolha do usuário) e inserir esta quantidade na célula especificada de a planilha.
Complete a macro a seguir para salvar dados da planilha "DS" em um array:
Sub actualize()
Dim last_row As Integer
'A última linha do conjunto de dados
'...
'Salvando um conjunto de dados em uma matriz dinâmica
Dim array_db()
'...
End Sub
Aqui está um exemplo de solução:
Sub actualize()
Dim last_row As Integer
'A última linha do banco de dados
last_row = Sheets("DS").Range("A1").End(xlDown).Row
'Salvando um conjunto de dados em uma matriz dinâmica
Dim array_db()
ReDim array_db(last_row - 2, 2)
For row_number = 2 To last_row
array_db(row_number - 2, 0) = Sheets("DS").Range("A" & row_number)
array_db(row_number - 2, 1) = Sheets("DS").Range("B" & row_number)
array_db(row_number - 2, 2) = Sheets("DS").Range("C" & row_number)
Next
End Sub
Isso basicamente repete o que fizemos na lição anterior…
Mas agora precisamos modificar nossa macro adicionando as seguintes ações:
Aqui está um exemplo de solução:
Sub actualize()
Dim last_row As Integer, search_value As String, insert_row As Integer, value_yes_no As String, rows_number As Integer
'A última linha do banco de dados
last_row = Sheets("DS").Range("A1").End(xlDown).Row
'Valor de pesquisa (YES ou NO)
If Sheets("RES").OptionButton_yes.Value = True Then
search_value = "YES"
Else
search_value = "NO"
End If
'Número de respostas YES ou NO
rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
'Salvando um conjunto de dados em um array
Dim array_db()
ReDim array_db(rows_number - 1, 1)
insert_row = 0
For row_number = 2 To last_row
value_yes_no = Sheets("DS").Range("C" & row_number)
If value_yes_no = search_value Then
array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
insert_row = insert_row + 1
End If
Next
End Sub
A busca pela escolha do usuário é determinada no início do procedimento pelo seguinte código:
'Valor de pesquisa (YES ou NO)
If Sheets("RES").OptionButton_yes.Value = True Then
search_value = "YES"
Else
search_value = "NO"
End If
Usaremos a função CountIF para determinar o número de respostas “SIM” ou “NÃO”:
'Número de respostas YES ou NO
rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
A matriz foi redimensionada para caber no número de respostas SIM ou NÃO e reduzida a duas colunas:
ReDim array_db(rows_number - 1, 1)
Esses dados agora serão armazenados no array quando sua terceira coluna corresponder à seleção do usuário:
'Inserindo um número em um array
insert_row = 0
'Processamento de conjunto de dados
For row_number = 2 To last_row
'Valor da coluna C (YES ou NO)
value_yes_no = Sheets("DS").Range("C" & row_number)
'Se o valor corresponder à seleção do usuário, a string será armazenada no array
If value_yes_no = search_value Then
'Salvando o valor na coluna A
array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
'Salvando o valor na coluna B
array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
'Uma linha foi salva => o número de inserção na matriz é incrementado em 1
insert_row = insert_row + 1
End If
Next
Nosso array contém apenas os dados nos quais estamos interessados.
Tudo o que resta fazer é:
Aqui está um exemplo de solução:
'Número de respostas "YES"/"NO"
For no_years = 2011 To 2026
For no_client = 1 To 30
counter = 0
For i = 0 To UBound(array_db)
If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
counter = counter + 1
End If
Next
Cells(no_years - 2009, no_client + 1) = counter
Next
Next
Problema resolvido com comentários explicando em detalhes:
'Loop para cada linha
For no_years = 2011 To 2026
'Loop para cada coluna
For no_client = 1 To 30
'Reinicialização do contador
counter = 0
'Processamento de matriz
For i = 0 To UBound(array_db)
'Verificando se a linha da tabela corresponde ao ano e ao número do cliente
If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
'Se o ano e o número do cliente corresponderem, o contador aumenta em 1
counter = counter + 1
End If
Next
'Após processar o array, o resultado é inserido na célula correspondente
Cells(no_years - 2009, no_client + 1) = counter
Next
Next
E finalmente, o código de toda a nossa macro:
Sub actualize()
Dim last_row As Integer, search_value As String, insert_row As Integer, value_yes_no As String, rows_number As Integer, counter As Integer
'Excluindo conteúdo
Range("B2:AE17").ClearContents
'A última linha do conjunto de dados
last_row = Sheets("DS").Range("A1").End(xlDown).Row
'Valor de pesquisa (YES ou NO)
If Sheets("RES").OptionButton_yes.Value = True Then
search_value = "YES"
Else
search_value = "NO"
End If
'Número de respostas YES ou NO
rows_number = WorksheetFunction.CountIf(Sheets("DS").Range("C2:C" & last_row), search_value)
'Salvando valores em um array
Dim array_db()
ReDim array_db(rows_number - 1, 1)
insert_row = 0
For row_number = 2 To last_row
value_yes_no = Sheets("DS").Range("C" & row_number)
If value_yes_no = search_value Then
array_db(insert_row, 0) = Sheets("DS").Range("A" & row_number)
array_db(insert_row, 1) = Sheets("DS").Range("B" & row_number)
insert_row = insert_row + 1
End If
Next
'Contando respostas YES ou NO
For no_years = 2011 To 2026
For no_client = 1 To 30
counter = 0
For i = 0 To UBound(array_db)
If Year(array_db(i, 0)) = no_years And array_db(i, 1) = no_client Then
counter = counter + 1
End If
Next
Cells(no_years - 2009, no_client + 1) = counter
Next
Next
End Sub
Aqui você pode baixar o arquivo de exemplo Excel: arrays_exercise_completed.xls
