Excel – Perguntas e Respostas #1

Olá galera Excelente!!!

A partir de hoje vou iniciar um projeto de publicar toda sexta-feira um artigo compilando algumas perguntas que recebo nos comentários aqui do blog.

Os comentários que eu julgar que para respondê-lo seria necessário uma solução mais rebuscada  e detalhada, responderei em forma de artigo. O que me dará mais recursos como a exemplificação através de imagens, arquivos de exemplo para download, enfim, encontrei dessa forma uma maneira de atender melhor e oferecer, também, a possibilidade de deixar mais vísivel a todos os leitores.

Espero que gostem da novidade e que acompanhem sempre as dicas das sextas-feiras…

Vamos, então, as respostas de hoje.

Criar uma Barra de Progresso ao executar uma macro demorada

Comentário feito no artigo Exibindo uma barra de progresso – Parte 2.

Boa tarde!
Eu procurava por um exemplo deste que possa ocorrer enquanto a macro roda, porém minha macro processa diversas planilhas (atuais 35 e que podem ser acrescidas) e em cada planilha desse arquivo executa alterações em 33 colunas e em diversas linhas, cujo número também é variável.
Como posso adaptar essa barra a minha macro?
Abç.

Excel do Seu Jeito responde:

Essa adaptação vai depender de como está estruturado o projeto. Como estão sendo chamadas as rotinas? Como posso identificar qual o tamanho do meu processamento? Onde posso afirmar que determinado processo foi concluído para iniciar outro?

São perguntas que precisamos saber para poder projetar um contador de progresso fiel ao processamento que será executado.

No comentário postado é dito que a macro processa diversas planilhas. Sim, isso pode ocorrer, de diversas formas. Quando as planilhas têm a mesma estrutura, uma macro pode executar a mesma rotina em todas elas. Ou podemos, também, ter várias macros. Uma para cada planilha, ou caso em particular.

No exemplo abaixo, configurei uma macro que faz uma chamada a outras macros. E a cada chamada é adicionado um incremento a barra de progresso Geral. E quando as sub-macros são executadas, elas incrementam um contador individual, apenas para o processamento atual.

Com isso, podemos identificar o andamento geral e o individual do processamento.

Veja o código. (Não se assustem! Estou deixando a planilha pra vocês baixarem, ok.)

 

Sub BarraDeProgresso()
'Esta é a macro Principal, que chama todas as demais.
'Ou seja, esta macro é a que inicia o processo. Geralmente é a macro que está associada a um botão "Iniciar"

    Application.ScreenUpdating = False
    frmBarraDeProgresso.Show False      'Exibe a barra de progresso

    Call MinhaMacro0        'Inicia o processamento

    Unload frmBarraDeProgresso  'Fecha a barra de progresso

    MsgBox "Processo concluído.", vbInformation, "Excel do Seu Jeito"

End Sub

Private Sub MinhaMacro0()
Dim i               As Long
Dim iFinal          As Long
Dim iPercentualConcluido As Double

    iFinal = 5      'Quantidade de processos que vai realizar

    With frmBarraDeProgresso
        'Manda executar a primeira Macro
        iPercentualConcluido = 1 / iFinal       '----- Aqui vai sempre dizendo qual processo está sendo realizado
        Call MinhaMacro1
        Call AtualizaBarra1(iPercentualConcluido)

        'Manda executar a segunda Macro
        iPercentualConcluido = 2 / iFinal       '----- Aqui vai sempre dizendo qual processo está sendo realizado
        Call MinhaMacro2(Plan2.Range("A1:E900"), 41)
        Call AtualizaBarra1(iPercentualConcluido)

        'Manda executar a terceira Macro
        iPercentualConcluido = 3 / iFinal       '----- Aqui vai sempre dizendo qual processo está sendo realizado
        Call MinhaMacro2(Plan3.Range("A1:C500"), 3)
        Call AtualizaBarra1(iPercentualConcluido)

        'Manda executar a quarta Macro
        iPercentualConcluido = 4 / iFinal       '----- Aqui vai sempre dizendo qual processo está sendo realizado
        Call MinhaMacro2(Plan4.Range("B1:C2200"), 14)
        Call AtualizaBarra1(iPercentualConcluido)

        'Manda executar a quinta Macro
        iPercentualConcluido = 5 / iFinal       '----- Aqui vai sempre dizendo qual processo está sendo realizado
        Call MinhaMacro2(Plan5.Range("B10:F850"), 55)
        Call AtualizaBarra1(iPercentualConcluido)

    End With

End Sub

Private Sub MinhaMacro1()
Dim i               As Long
Dim iUltimaLinha    As Long
Dim iPercentualConcluido As Double

    iUltimaLinha = Plan1.Range("A1").End(xlDown).Row
    Call AtualizaBarra2(0)

    For i = 2 To iUltimaLinha
        '---- A barra de progresso é atualiza a cada incremento no loop que percorre as linhas da planilha,
        'ou seja, a cada ação da sua macro, o contador é modificado
        iPercentualConcluido = i / iUltimaLinha
        Call AtualizaBarra2(iPercentualConcluido)

        '----- Execução da sua macro, propriamente dita. As funções que você manda executar
        With ActiveSheet.Cells(i, 1)
            Select Case CInt(Left(.Offset(0, 1).Value, 1))
                Case 7, 8, 9
                    .Offset(0, 2).Value = "Oi, " & .Value & ". Este número de telefone parece ser um celular!"
                Case Else
                    .Offset(0, 2).Value = "Oi, " & .Value
            End Select
        End With
    Next

End Sub

Private Sub MinhaMacro2(ByVal rngIntervalo As Range, ByVal iIndexColor As Integer)
Dim rng             As Range
Dim i               As Long
Dim iFinal          As Long
Dim iPercentualConcluido As Double

    iFinal = rngIntervalo.Cells.Count
    i = 0
    Call AtualizaBarra2(0)

    For Each rng In rngIntervalo.Cells
        '---- A barra de progresso é atualiza a cada incremento no loop que percorre as linhas da planilha,
        'ou seja, a cada ação da sua macro, o contador é modificado
        i = i + 1
        iPercentualConcluido = i / iFinal
        Call AtualizaBarra2(iPercentualConcluido)

        '----- Execução da sua macro, propriamente dita. As funções que você manda executar
        With rng
            .Value = "Célula " & Replace(.AddressLocal, "$", "")
            .Font.ColorIndex = iIndexColor
        End With
    Next rng

End Sub

Private Sub AtualizaBarra1(ByVal iPercentualConcluido As Double)
    With frmBarraDeProgresso
        .framePb.Caption = Format(iPercentualConcluido, "0%") & " Concluído"
        .progressBar.Width = iPercentualConcluido * (.framePb.Width - 10)
    End With
    DoEvents    'Permite que sejam visualizadas as mudanças nos controles do formulário
End Sub

Private Sub AtualizaBarra2(ByVal iPercentualConcluido As Double)
    With frmBarraDeProgresso
        .framePb2.Caption = Format(iPercentualConcluido, "0%") & " Concluído"
        .progressBar2.Width = iPercentualConcluido * (.framePb2.Width - 10)
    End With
    DoEvents    'Permite que sejam visualizadas as mudanças nos controles do formulário
End Sub

 

Exibir os resultados da pesquisa no controle Listview

Comentário feito no artigo Criando uma Pesquisa Personalizada Simples

Em algumas buscas pela internet achei esse código maravilhoso e bem interessante. Só me resta uma dúvida: como posso fazer para o resultado dessa pesquisa aparecer num listview?

Excel do Seu Jeito responde:

Bem, esta é bem simples. Mas, para que ainda não leu os artigos anteriores sobre o assunto pode ficar meio perdido, por isso, leia os artigos abaixo antes de prosseguir:

Criando uma Pesquisa Personalizada Simples – 1a. Parte

Criando uma Pesquisa Personalizada Simples – 2a. Parte

Criando uma Pesquisa Personalizada Simples – 3a. Parte

 

Como a nossa macro de pesquisa grava as informações num array, vamos, então, percorrer todo esse array dentro de um laço for e preencher a listview.

A Listview passará a assumir a função do SpinButton (as setinhas) que era de mover-se entre os registros que correspondiam ao resultado da busca, exibindo-os nas caixas de texto do formulário. Agora, ao clicar em algum item da listagem, os dados serão mostrados na tela.

Não vou reescrever todo o código aqui. Está disponível na planilha de exemplo para vocês fazerem o download no final do artigo. Vou mostrar apenas os métodos para configurar e preencher a listview com os resultados da pesquisa.

Sub ConfigListView()
    With ListView1
        .ColumnHeaders.Add 1, "id", "id", 0
        .ColumnHeaders.Add 2, "nome", "Nome", 70
        .ColumnHeaders.Add 3, "estado", "Estado", 30
        .ColumnHeaders.Add 4, "funcao", "Função", 50
        .ColumnHeaders.Add 5, "status", "Status", 30

        .Gridlines = True
        .FullRowSelect = True
        .HideColumnHeaders = False
        .View = lvwReport
    End With
End Sub

Sub PreencheListView()
Dim i As Long

    ListView1.ListItems.Clear

    If IsArray(MatrizResultadosLinha) Then
        For i = 0 To UBound(MatrizResultadosLinha)
            Set NewItem = ListView1.ListItems.Add(, , i)

            With Sheets(CInt(MatrizResultadosPlanilha(i)))
                NewItem.SubItems(1) = .Cells(MatrizResultadosLinha(i), 1).Value
                NewItem.SubItems(2) = .Cells(MatrizResultadosLinha(i), 2).Value
                NewItem.SubItems(3) = .Cells(MatrizResultadosLinha(i), 3).Value
                NewItem.SubItems(4) = .Cells(MatrizResultadosLinha(i), 4).Value
            End With
        Next i
    End If

End Sub

'##################################################################################
'##########     U P D A T E     (NOV-2012)        #################################
'##################################################################################
'Use este evento do Listview para carregar os dados selecionados nas caixas de texto
'Tem a mesma função que o SpinButton tinha na versão anterior
Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem)
Dim sLinha As Long
Dim iPlanilha As Integer
Dim TotalOcorrencias As Long

    SpinButton1.Value = Item
    
End Sub

 

Então é isso pessoal. Até a próxima!!!

24 comentários em “Excel – Perguntas e Respostas #1”

  1. Quando eu tento executar aparece o seguinte erro:

    É impossivel localizar o projeto ou a biblioteca:

    Essa é a linha que está dando esse erro:

    Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem)

    Responder
    • Diego, o problema está na referência ao controle Listview. Acredito que esteja utilizando o Windows 7. Recentemente houve uma atualização da Microsoft e alguns projeto que utilizam a biblioteca MSCOMCTL.OCX apresentam falhas.

      Faça assim:

      Acesse o formulário no projeto VBA. Selecione o controle Listview, delele e insira um novo.

      Se não estiver localizando este tipo de controle, acesse o menu Ferramentas > Controles adicionais… E selecione o controle Microsoft ListView Control.
      Ele irá aparecer agora na caixa de ferramentas contendo os controles disponíveis.
      Insira no formulário.

      Tente executar o macro novamente.
      Acredito que vá funcionar.

      Abç

      Responder
  2. Olá a todos, gostei muito do arquivo.

    Tenho um arquivo que possui um monte de tabelas dinâmicas e tenho um comando que faço elas se atualizarem.

    Sub Macro_Atualizar()
    ActiveWorkbook.RefreshAll
    End Sub

    Gostaria de saber se há uma possibilidade de ao invés de mostrar o processo individual, mostrar o processo de atualização das tabelas dinâmicas, mostrando o nome da mesma.

    Exemplo:

    Progresso Geral
    64%
    Tabela Dinâmica 15
    28%

    Responder
  3. Olá a todos, gostei muito do arquivo.
    Tenho um arquivo que possui um monte de tabelas dinâmicas e tenho um comando que faço elas se atualizarem.

    Sub Macro_Atualizar()
    ActiveWorkbook.RefreshAll
    End Sub

    Gostaria de saber se há uma possibilidade de ao invés de mostrar o processo individual, mostrar o processo de atualização das tabelas dinâmicas, mostrando o nome da mesma.

    No meu caso, só tenho tabelas dinâmicas, na planilha Cálculos.
    Exemplo:

    Progresso Geral
    64%
    Tabela Dinâmica 64
    99%

    depois

    Progresso Geral
    65%
    Tabela Dinâmica 65
    01%

    Responder
    • Daniel,

      Mostrar o percentual de carregamento de cada tabela não me recorre agora se há como fazer.
      Contudo, acredito que uma possibilidade seria dar um refresh para cada PivotTable fazendo um loop pela coleção de tabelas em sua planilha.

      For Each pvt In Plan1.PivotTables
      pvt.RefreshTable
      Application.StatusBar = pvt.Name
      Next

      Caso queira usar o RefreshAll, pode fazer a chamada da informação de status pelo evento Workbook_SheetPivotTableUpdate da sua pasta de trabalho:

      Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
      Application.StatusBar = “Atualizando ” & Target.Name
      End Sub

      Espero ter ajudado.

      Abç

      Responder
  4. Olá Reinaldo, Parabéns pelo excelente trabalho, gostei muito deste listwiew, funciona quase perfeitamente,porém na hora de editar ele nem sempre salva na plan correta, ex: se buscar a palavra “ma” vai retornar marcelo, maria, etc., tente editar maria da planbase2, ele lançará na primeira linha da plan1;
    É possivel editar usando Listview?

    Att.
    Sampaio

    Responder
    • Olá Sampaio.

      Realmente tem um bugzinho mesmo! Como este exemplo é uma adaptação de um artigo anterior que usava o controle Spinbutton para mover-se pelos registros, e aproveitei a estrutura dele, ficou faltando atualizá-lo.

      Para corrigir, altere o evento ItemClick da Listview para o código a seguir:

      Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem)
      Dim sLinha As Long
      Dim iPlanilha As Integer
      Dim TotalOcorrencias As Long

      SpinButton1.Value = Item

      End Sub

      Grande abraço

      Responder
  5. Reinaldo,

    Eu adoro esse código. Ja usei e adaptei em várias planilhas.
    Mas como faço para ele ir pesquisando a cada letra q eu digito? Em tempo real?
    Eu já encontrei códigos VBA q fazem isso, mas nunca consigo colocar o recurso nesse seu formulário.

    Eu sei que você já fez vários tutoriais sobre a bendita pesquisa (é o 4º ou 5º). Mas se puder fazer MAIS UM incluindo isso, te agradecerei infinitamente.
    ^^

    Abração!
    E parabéns pelo ótimo trabalho!

    Responder
    • ah!

      esse sim é um código q eu nunca acho: clicar no listview, ou num botão, que leve até a respectiva planilha ou célula.

      aqui os workbooks têm várias sheets. com centenas de dados em cada uma.
      quando eu pesquiso, seria conveniente ter um botão que leve até a célula correta, na sheet correta.
      é possível fazer algo assim?

      (acho q compliquei né)

      Responder
      • Complicou não!

        Inclua o código abaixo no formulário de pesquisa, ok.

        
        Private Sub ListView1_DblClick()
        Dim iLinha As Long
        Dim iPlanilha As Integer
        
            If IsArray(MatrizResultadosLinha) Then
                iPlanilha = MatrizResultadosPlanilha(ListView1.SelectedItem)
                iLinha = MatrizResultadosLinha(ListView1.SelectedItem)
                
                'Fecha o form
                Unload Me
                
                'Move o cursor para o item clicado na lista
                With Sheets(iPlanilha)
                    .Select
                    .Rows(iLinha).Select
                End With
                
            End If
        End Sub
        

        Foi? Espero que sim.

        Abç

        Responder
    • Olá Everton,

      Valeu pelo comentário.

      Para que a pesquisa seja realizada a cada letra digitada, inclua as linhas de código a seguir na classe do formulário de pesquisa.

      
      Private Sub txt_Procurar_Change()
          Call btn_Procurar_Click
      End Sub
      

      Abç

      Responder
      • Reinaldo: Obrigado! Deu certo!

        Mas eu to com um terceiro problema, na hora de adaptar seu form.

        Eu coloquei duas checkboxes.

        a intenção era eu pesquisar. Por nome ou estado, etc.
        ticar essa caixa para filtrar toda essa pesquisa apenas com funcionários ativos.
        A outra pra delimitar a pesquisa só com os q estão de férias.
        (talvez eu insira outras checkboxes depois)

        Mas todos os codigos q eu tentei dão erro de sintaxe!

        Responder
        • Everton,

          Manda tua planilha na seção Tira Dúvidas aqui do site.
          Existe uma série de detalhes que não possibilitam simplesmente adaptar a macro de busca. É necessário identificar a lógica e a estrutura do projeto, ok!

          Por exemplo, não acredito que um checkbox resolveria o problema, visto que, os dados que você quer filtrar pertençam ao mesmo campo, entendeu?

          São apenas alguns detalhes, mas que fazem toda a diferença!

          Abç

          Responder
  6. Boa noite.
    Primeiro quero dizer que gosto muito do site, ja me ajudou em varios trabalhos que desenvolvi. parabens.
    preciso de uma ajudinha, quando tento carregar essa listview usando uma planilha apenas como base dah erro no item with
    With Sheets(CInt(MatrizResultadosPlanilha(i)))
    tentei remover a variavel matrizresultadosplanilha trocando por uma referencia a plan1 ( onde vou fazer minha pesquisa ), mas da erro. como poderia resolver isso?

    Responder
    • De acordo com o código da macro, o index da planilha que contêm os resultados da pesquisa fica na variável na MatrizResultadosPlanilha.
      Acredito que o erro não seja aí.

      Se você trocou por Plan1, teria que substituir Sheets(CInt(MatrizResultadosPlanilha(i))) por Plan1, e não apenas a variável, ok!

      Abç

      Responder
  7. Olá Reinaldo, mais uma vez venho aqui pedir sua ajuda, mas tenho observado que anda muito ocupado ultimamente, talvez possa me dar mais essa ajudinha.
    Como faço para quando eu selecionar o item no listview seja marcado a celula ou a linha ativa na planilha, preciso marcar a linha ou a celula ativa para rodar uma macro.

    Abç

    Responder
    • Olá Sampaio, realmente tenho estado muito atarefado… O site acaba sofrendo um pouco com isso, mas, tenho planos de retornar a freqüência com todo o vigor aqui.

      Bom, respondendo sua questão, você pode adicionar as linhas abaixo no evento clicar da listview (ListView1_ItemClick).

      .Select
      .Cells(sLinha, 1).Select

      Faça assim.
      Na planilha que disponibilizei para download, localize dentro do procedimento ListView1_ItemClick a parte do código abaixo:

      
      With Sheets(iPlanilha)
          Label_PlanBase.Caption = "Em " & .Name
          TextBox1.Text = .Cells(sLinha, 1).Value
          TextBox2.Text = .Cells(sLinha, 2).Value
          TextBox3.Text = .Cells(sLinha, 3).Value
          TextBox4.Text = .Cells(sLinha, 4).Value
      End With
      

      E adicione as linhas que falei. Vai ficar assim:

      
      With Sheets(iPlanilha)
          Label_PlanBase.Caption = "Em " & .Name
          TextBox1.Text = .Cells(sLinha, 1).Value
          TextBox2.Text = .Cells(sLinha, 2).Value
          TextBox3.Text = .Cells(sLinha, 3).Value
          TextBox4.Text = .Cells(sLinha, 4).Value
          
          .Select           'Seleciona a planilha
          .Cells(sLinha, 1).Select      'Seleciona uma célula da linha atual
      End With
      

      É isso!

      Responder

Deixe um comentário