5.2. Criando Arrays Bidimensionais

5.2.1. Array padrão (Sem Loop)

                 Agora, vamos criar um código, sendo mais específicos, determinando o número de linhas, o que tornará mais fácil a inserção dos dados na planilha, já que o VBA entenderá que as informações devem entrar nas mesmas linhas que determina o array, sempre na 1ª coluna, dispensando a criação de um loop para isto.

Sub CopaLin2014()

'neste caso, declaramos 3 linhas

Dim PaisCopa(1 To 3, 1 To 1) As String      '1º. declaramos um array bidimensional com 3 posições

Set ShtArray = Sheets("Array")      'Declaramos a plan Array que receberá os valores do array

'Atribuímos valores a cada posição do array

ShtArray.Select

PaisCopa(1,1) = "Brasil"             'Brasil será inserido na linha 1

PaisCopa(2,1) = "Olanda"              'Olanda na linha 2

PaisCopa(3,1) = "Alemanha"            'E Alemanha na linha 3

ShtArray.Range("A1:A3") = PaisCopa  'insere as informações do array na planilha

End Sub

                 Se você rodar este código, os países serão inseridos diretamente na planilha, sem a necessidade de um loop.

Para acessar o curso completo Clique aqui

5. ENTENDENDO OS ARRAYS

Nada me causa tanto fascínio em programação como a compreensão da construção de um “Array”, popularmente conhecido como “Matriz”.

            Para continuarmos com este estudo, precisamos diferenciar os dois tipos conhecidos. Na verdade, nas duas situações temos um Array, porém, o primeiro é chamado de Unidimensional e o segundo, Bidimensional.        

O primeiro refere-se a simples atribuição de valores a um array, que será constituído por uma única lista, uma única linha de dados, recebendo valores de 0 a 2, por exemplo.

Já as Bidimensionais, podem guardas valores como em uma tabela, com várias linhas e várias colunas, o que obviamente é mais complexo.

5.1. Criando Arrays Unidimensionais

           

            A primeira coisa que se deve saber sobre a aplicação de um Array, é como declará-lo. Para declarar uma array, você não precisa usar nenhuma palavra reservada. Apenas use algo que possa identificar o objetivo da sua criação, para melhor compreensão do seu proposito. Como estamos em clima de copa do mundo, vamos supor as três primeiras colocações da competição, para construir o nosso primeiro código.

                 Veja que declaramos o array como PaisCopa(2). Neste caso, teremos 3 posições(0,1 e 2).

Declaramos a variável assim: Dim PaisCopa(2) As String         

Neste caso, podemos inserir 3 itens neste array, quando ficará assim.

PaisCopa(0) = "Brasil"

PaisCopa(1) = "Olanda"

PaisCopa(2) = "Alemanha"

                 Inserimos os 3 itens que queríamos. Agora precisamos jogar estes dados em uma planilha. É muito importante, que você entenda, que neste array, que eu chamaria mais de vetor, não determinamos linhas e colunas como vamos fazer nos próximos. Desta forma, para inserirmos estas informações em uma planilha, precisamos criar um loop, para que as informações fiquem nas posições que determinarmos. Veja como ficaria o código.

Sub CopaUni2014()

Dim PaisCopa(2) As String           '1º. declaramos um array unidimensional com 3 posições

Set ShtArray = Sheets("Array")      'Declaramos a plan Array que receberá os valores do array

'Atribuímos valores a cada posição do array

ShtArray.Select

PaisCopa(0) = "Brasil"

PaisCopa(1) = "Olanda"

PaisCopa(2) = "Alemanha"

i = 0

For Lin = 1 To 3                            'percorrerá da 1ª até a 3ª linha da planilha

ShtArray.Range("A" & Lin) = PaisCopa(i)  'insere as informações do array na planilha

i = i + 1                               'incremanta a variável

Next

End Sub

                 Ao executarmos esta macro, os dados serão inseridos nas células A1, A2 e A3 respectivamente, em uma planilha que você nomerá “Array”.

                 Prontinho, molezinha!!! Até o próximo capítulo.

Para acessar o curso completo Clique aqui

4. TRATAMENTO DE ERROS

Algumas literaturas não dão muita importância ou até menosprezam este assunto. Não consigo entender porque, já que no dia-a-dia é quase impossível não lançar mão deste poderoso recurso do VBA.

            Dependendo do sistema que você está desenvolvendo, erros podem acontecer recorrentemente. Por exemplo, você precisar abrir um arquivo que está em determinado diretório(“pelo menos você pensava que ele estava lá”), porém foi excluído por algum colaborador da sua empresa. Vejamos, se for apenas um arquivo, você pode tratar com a condicional “If”. Por exemplo: If MeuArq.xlsm não for encontrado lá no diretório que coloquei, “Então” “Msgbox” O arquivo não foi encontrado.

            Porém, isto só é possível em condições normais de temperatura e pressão, como vimos acima. Mas considerando o capítulo anterior, quando dissemos que o gerente de filiais precisava gerir 200 arquivos, você acredita que dá pra tratar com o “If”? De jeito maneira.

            Então, vamos tratar as possíveis ocorrências de erros quando fomos rodar o sistema. Vamos fazer algumas alterações no nosso código.

Perceba na imagem, que mudei o nome do arquivo da Filial 1  no diretório. Vou tentar com isto, provocar um erro quando a código tentar abrir o arquivo.

Veja o que vai acontecer ao tentarmos abrir o arquivo da Filial 1.

            Obviamente my friend, não conseguiremos abrir o arquivo se ele tiver com o nome diferente. Então vamos tratar isto para que não haja este retorno de erro! “Prestenção” e veja como o ficou o código!!!

Sub TotalFiliaisArvore()

Dim NomArq                              'declaramos a variável que receberá o nome do arquivo que será aberto

Set ShtArv = Sheets("Árvore")           'declaramos a planilha que receberá os dados

Lin = 2

Do While ShtArv.Cells(Lin, 1) <> ""     'agora percorremos todos os arquivos no diretório à procura do que nos interessa

If ThisWorkbook.Sheets("Árvore").Cells(Lin, 5) = "Abrir" Then

On Error Resume Next            'trata o erro caso não seja possível abrir o arquivo

Workbooks.Open ShtArv.Cells(Lin, 2) 'se acharmos, vamos abri-lo. Perceba que agora estamos abrindo pelo endereço que geramos na árvore

If Err.Number <> 0 Then     'se houver erro ao tentar abrir o arquivo, informe ao usuário

MsgBox "O arquivo " & ShtArv.Cells(Lin, 2) & " não encontra-se no diretório especificado!", vbOKOnly + vbCritical, "ERRO AO TENTAR ABRIR O ARQUIVO"

Exit Sub                'sai da rotina

End If

On Error GoTo 0

NomArq = ActiveWorkbook.Name        'captura o nome do arquivo

'Após abri-lo, precisamos pegar a informação que queremos e tranferí-la para o nosso relatório.

With ThisWorkbook.Sheets("Árvore")

.Cells(Lin, 4) = ActiveWorkbook.ActiveSheet.Range("B2") 'e finalmente, o valor

End With

Workbooks(NomArq).Close             'fechamos o arquivo, pois já pegamos a informação que queriamos

End If

Lin = Lin + 1                       'incremente a linha para abrirmos o próximo arquivo

Loop

End Sub

            Vamos analisar com detalhes a alteração que fizemos no código.

On Error Resume Next            'trata o erro caso não seja possível abrir o arquivo

Workbooks.Open ShtArv.Cells(Lin, 2) 'se acharmos, vamos abri-lo. Perceba que agora estamos abrindo pelo endereço que geramos na árvore

If Err.Number <> 0 Then     'se houver erro ao tentar abrir o arquivo, informe ao usuário

MsgBox "O arquivo " & ShtArv.Cells(Lin, 2) & " não encontra-se no diretório especificado!", vbOKOnly + vbCritical, "ERRO AO TENTAR ABRIR O ARQUIVO"

Exit Sub                'sai da rotina

End If

On Error GoTo 0

            Usamos o “On Error Resume Next” antes de o código tentar abrir o arquivo. Isto vai impedir o erro caso o arquivo não seja encontrado no diretório.

            Logo após, verificamos com “Err.Number”, se aconteceu algum erro e caso positivo, disparamos uma mensagem para o usuário, que saberá que o arquivo não foi encontrado. Logo após, saímos da rotina com “Exit Sub” e finalizando, fechamos o bloco com “On Error GoTo 0”.

            Simples assim, essa medida dará mais profissionalismo ao código que você criou e o usuário poderá identificar com facilidade se algo fugiu o padrão(no nosso caso, o nome do arquivo que foi alterado por um colaborador da empresa, lembra?).

            É isso ai, nos vemos na próxima lição.

Para acessar o curso completo Clique aqui 

3. ABRINDO ARQUIVOS A PARTIR DE UMA ÁRVORE

         Meus queridos! Na última lição, aprendemos a gerar uma árvore com todos os arquivos de um determinado diretório. Talvez você tenha se perguntado: pra que fizemos isto sô? Calma, eu respondo!!!

            Repisando a velha história de precisar manipular muitos arquivos, talvez você não queira abrir todos e especificar quais devem ser abertos.

            Sendo assim, no próximo código, vamos dizer quais queremos, ok?

            Na mesma planilha que geramos a árvore, vamos trazer na coluna “D” o valor total das vendas. Na coluna “E”, vamos colocar a palavra abrir, para todas as que queremos extrair o total.

            A ideia é simples, assim que rodarmos a macro, os arquivos que serão abertos estão determinamos na coluna “E”.

            Veja como o código é simples!

Sub TotalFiliaisArvore()

Dim NomArq                              'declaramos a variável que receberá o nome do arquivo que será aberto

Set ShtArv = Sheets("Árvore")           'declaramos a planilha que receberá os dados

Lin = 2

Do While ShtArv.Cells(Lin, 1) <> ""     'agora percorremos todos os arquivos no diretório à procura do que nos interessa

If ThisWorkbook.Sheets("Árvore").Cells(Lin, 5) = "Abrir" Then

Workbooks.Open ShtArv.Cells(Lin, 2) 'se acharmos, vamos abri-lo. Perceba que agora estamos abrindo pelo endereço que geramos na árvore

NomArq = ActiveWorkbook.Name        'captura o nome do arquivo

'Após abri-lo, precisamos pegar a informação que queremos e tranferí-la para o nosso relatório.

With ThisWorkbook.Sheets("Árvore")

.Cells(Lin, 4) = ActiveWorkbook.ActiveSheet.Range("B2") 'e finalmente, o valor

End With

Workbooks(NomArq).Close             'fechamos o arquivo, pois já pegamos a informação que queriamos

End If

Lin = Lin + 1                       'incremente a linha para abrirmos o próximo arquivo

Loop

End Sub

            Ao rodar o código teremos!

                       

            Veja! Somente os valores requeridos foram trazidos. Mão na roda não é?!!!

            Nos vemos na próxima lição!!!

Para acessar o curso completo Clique aqui 

2. GERANDO UMA ÁRVORE DE ARQUIVOS - INTERMEDIÁRIO

           

           Talvez o seu caso seja outro. De repente, você precisa saber quais arquivos estão nos diretórios, para depois decidir se vai abrir ou não os arquivos, ou simplesmente determinar quais dever ser abertos.

            Podemos então, gerar uma árvore com os arquivos que estão nos diretórios determinados por você.

            Antes de mostrar a você o código, devemos fazer algo muito importante. Temos que habilitar uma biblioteca do VBA.

            Primeiro, vá ao menu “Ferramentas” do editor do VBA e entre em “Referências”.

            Agora, habilite a biblioteca “Microsoft Scripting Runtime” e click em OK.

            Pronto, agora já podemos começar a desenvolver o nosso código.

Dim L As Long                                               'Declaramos a variável que receberá a linha

Sub GerArvore()

Const strCaminho As String = "C:\ExcelECia\"            'Idendificamos o caminho onde os arquivos serão procurados

'Declaração de variáveis para leitura dos arquivos/pastas

Dim fso As Scripting.FileSystemObject

Dim fld As Scripting.Folder

'Aqui vamos determinar o caminho em que os arquivos serão buscados

Set fso = CreateObject("Scripting.FileSystemObject")

Set fld = fso.GetFolder(strCaminho)                     'Permite entrar no diretorio e localizar os arquivos

Sheets("Árvore").Select                                 'selecionamos a planilha onde vamos gerar a árvore

With ActiveSheet

.Cells.Delete                                       'Limpa planilha

'criamos um matriz simples para montar o cabeçalho da planilha

.Range("A1:E1") = Array("Diretório", "Caminho completo", "Nome", "Data de modificação", "Data de criação")

L = 2

LocalizaArquivos fld                                'Chama rotina para localizar os arquivos

.Columns.AutoFit

End With

MsgBox "Consulta realizada com sucesso!", vbInformation, "Macro consulta arquivos"

End Sub

            Esta é a primeira parte do nosso código. Na verdade é apenas o pano de fundo para outra rotina que vamos desenvolver e que localizará os endereços dos arquivos. Não se esqueça de nomear uma planilha como “Árvore”.

            Fique despreocupado, pois no final deste capítulo, vou disponibilizar pra você o arquivo de exemplo.

Antes de desenvolvermos a segunda parte do código, criei uma outra pasta dentro do caminho padrão que declaramos, veja.

            Fiz isto para testarmos no nosso código, já que ele deve percorrer também as subpastas do diretório e trazer o endereço dos arquivos que desejamos buscar.

            Agora vamos à segunda parte do código.

Function LocalizaArquivos(fld As Folder)

Dim subfld As Scripting.Folder                          'Cria script para vasculharmos as subpastas

Dim fl As Scripting.File

For Each fl In fld.Files                        'Para cada arquivos dentro da pasta que vasculhamos

If UCase(Left(fl.Name, 6)) = "FILIAL" Then  'Se o nome do arquivo começar com Filial

Cells(L, "A") = fl.ParentFolder         'retorna o endereço da pasta onde está o arquivo

Cells(L, "B") = fl.Path                 'retorna o endereço completo do arquivo

Cells(L, "C") = fl.Name                 'retorna o nome do arquivo

Cells(L, "D") = fl.DateLastModified     'retorna a data da última modificação

Cells(L, "E") = fl.DateCreated          'retorna a data de criação do arquivo

ActiveSheet.Hyperlinks.Add Anchor:=Cells(L, "A"), Address:=Cells(L, "A") 'cria hinperlink para pasta do arquivo

ActiveSheet.Hyperlinks.Add Anchor:=Cells(L, "B"), Address:=Cells(L, "B") 'cria hinperlink para endereço completo do arquivo

L = L + 1                               'incrementa a linha

Cells(L, "A").Select                    'seleciona a proxima linha que receberá o novo endereço

End If

Next fl

For Each subfld In fld.SubFolders                       'percorre as subpastas para procurar os arquivos desejados

LocalizaArquivos subfld

Next subfld

End Function

Agora é só salvar em um módulo e rodar. O resultado será este.

            Perceba, que o código trouxe até o arquivo que estava na subpasta. Legal demais.

            No próximo capítulo, vamos aprender a abrir arquivos, usando a Árvore que acabamos de gerar. See you later!!!

Para acessar o curso completo Clique aqui