Excel VBA – Usando o controle TreeView

Tradução do artigo publicado em: http://www.excelguru.ca/node/85
Autor: Ken Puls

Introdução

Neste tutorial criaremos um UserForm que conterá um controle TreeView no Excel VBA. O objetivo é preencher o controle TreeView com dados que refletirão os nomes das planilhas da pasta de trabalho aberta e as células dentro destas que contém alguma fórmula.

Como este artigo envolve programação VBA e controles UserForm, sugerimos alguma familiaridade com estas duas tecnologias por parte do leitor para um melhor entendimento, além de alguma experiência com o ambiente VBE (Visual Basic Editor).

Para deixar o exemplo simples, os nomes dos controles foram mantidos como padrão, porém, sinta-se a vontade para renomeá-los a seu modo, de forma que facilite o acompanhamento do exemplo. Lembre-se apenas de refletir os nomes dos controles alterados nos devidos blocos de código para proporcionar o correto funcionamento do exemplo.

Criando a estrutura para o exemplo:

Siga os seguintes passos para criar nosso exemplo:

  1. Crie uma nova pasta de trabalho
  2. Adicione algumas fórmulas nas planilhas 1 e 2, não importa qual, mas precisa ser uma fórmula
  3. Entre no VBA (Alt+F11)
  4. Adicione um UserForm ao projeto
  5. Adiciona um novo módulo e adicione o seguinte código para chamar o UserForm:
    Sub ufLaunch()
         'Abre o userform
         UserForm1.Show
    End Sub

Adicionando o controle TreeView à barra de ferramentas:

  1. Clique com o botão direita na barra de ferramentas do VBA e selecione a opção “Adicionar Controles…”, conforme mostra a figura abaixo:
  2. Role a barra até encontrar o item “Microsoft Treeview Control, version x.x” O mais provável é que a versão mostrada seja a 6.0, mas não deve fazer diferença. Selecione o item e clique em OK.
  3. O controle será adicionado a barra de ferramentas conforme mostrado abaixo:
  4. NOTA: O controle TreeView permanecerá na sua barra de ferramentas até que este seja removido

Criando e configurando o userform:

  1. Adicione os seguinte controles no userform:
    1. TreeView
    2. Label
    3. CommandButton

    O userform deverá ser contruído para que fique parecido com o seguinte:

Adicionando o código que faz o trabalho:

As rotinas abaixo devem ser adicionadas ao UserForm:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Private Sub UserForm_Initialize()
'Carrega os dados e configura o form
 
     'Congfigura os controles
     With Me
          .CommandButton1.Caption = "Close"
          .Label1 = vbNullString
          .TreeView1.LineStyle = tvwRootLines
     End With
 
     'Preenche o Treeview
     Call TreeView_Populate
End Sub
 
Private Sub TreeView_Populate()
'Preenche os dados do controle TreeView
 
     Dim ws As Worksheet
     Dim rngFormula As Range
     Dim rngFormulas As Range
 
     With Me.TreeView1.Nodes
          'Limpa os dados do controle
          .Clear
 
          For Each ws In ActiveWorkbook.Worksheets
               'Add worksheet nodes
               .Add Key:=ws.Name, Text:=ws.Name
 
               'Verifica de existe alguma fórmula na planilha
               On Error Resume Next
               Set rngFormulas = ws.Cells.SpecialCells(xlCellTypeFormulas)
               On Error GoTo 0
 
               'Adiciona as células que contém fórmulas
               If Not rngFormulas Is Nothing Then
                    For Each rngFormula In rngFormulas
                         .Add relative:=ws.Name, _
                              relationship:=tvwChild, _
                              Key:=ws.Name & "," & rngFormula.Address, _
                              Text:="Range " & rngFormula.Address
                    Next rngFormula
               End If
 
               'Zera o range para a próxima interação do laço
               Set rngFormulas = Nothing
          Next ws
     End With
End Sub
 
Private Sub Treeview1_NodeClick(ByVal Node As MSComctlLib.Node)
'Descreve o nó selecionado
 
     Me.Label1.Caption = Node.Key
End Sub
 
Private Sub CommandButton1_Click()
'Fecha o form
 
     Unload Me
End Sub

O resultado final:
Salve o projeto e retorne a interface do Excel. Pressione Alt+F8 para ativar a tela de macros e selecione a macro “ufLaunch” para execução. O userForm deverá ser mostrado. Como descrito anteriormente, foi solicitado que algumas fórmulas fossem adicionadas nas planilhas. A TreeView inicialmente lista as planilhas existentes e aquelas que possuem fórmulas possuem itens filhos que refletem quais células as possuem. A figura abaixo demonstra o resultado.

Explicando o código:

O userForm faz uso de quatro rotinas que são descritas abaixo:

Private Sub UserForm_Initialize()

Carrega as configurações necessárias para o funcionamento do userForm. É o local ideal para colocar todas as configurações necessárias dos controles, ou pelo menos aquelas que não podem ser feitas em modo de Desgin.Além disso, faz a chamada a rotina que preenche os dados da TreeView.

De especial, a propriedade LineStyle é setada para tvwRootLines. O padrão é tvwTreeLines, que não mostra os indicadores + e – para expansão dos itens, sendo possível apenas por duplo-clique. Por questões práticas, utilizar os sinalizadores + e – torna a tarefa de navegação mais fácil e intuitiva. Por isso a configuraremos assim.

Private Sub TreeView_Populate()

Preenche os dados da TreeView com os nomes das planilhas como nós principais e os nós filhos sendo o endereço das células que contém fórmulas.

O laço feito para varrer as planilhas e células com fórmulas é auto-explicativo e por isso não vamos entrar em detalhes deste ponto

Enquanto os nós são adicionados na TreeView, é preciso definir seus relacionamentos com os nós existentes, podendo ser pai ou filho. Neste caso, as planilhas serão os nós/pai das células que contém as fórmulas.

Para adicionar um nó no nível raiz, é preciso mencionar tanto a chave/nome do nó (Key) como o texto, dados solicitados como parâmetro no método Add. A chave precisa ser única para que o nó seja colocado no nível mais alto. O exemplo de código abaixo adiciona um novo nó com o nome de uma planilha:

.Add Key:=ws.Name, Text:=ws.Name

i.e. Me.TreeView1.Nodes.Add Key:=”Sheet1″, Text:=”Sheet1″

Toda vez em que for necessário adicionar um nó no nível mais alto, a sintaxe acima deve ser utilizada.

Para adicionar nós filho, o processo é um pouco diferente. O método utilizado ainda é o Add, bem como é preciso informar uma chave única para o nó adicionado. Porém é preciso informar o “grau” de parentesco do nó adicionado e quem é o nó relacionado. No caso de um nó filho, o parâmetro a ser informado deve ter o valor tvwChild). O código abaixo adiciona um nó filho ao TreeView:

.Add relative:=ws.Name, relationship:=tvwChild, _
Key:=ws.Name & "," & rngFormula.Address, Text:="Range " & rngFormula.Address

i.e. Me.TreeView1.Nodes.Add relative:= “Sheet1″, relationship:=tvwChild Key:=”Sheet1,$C$8″, Text:=”Rage $C$8”

Como dito, para “conectar” um nó filho ao nó pai, o parâmetro “relative” deve ser informado, e este deve ser o “key” do nó pai. De qualquer forma, o novo nó também precisa ter seu key e este deve ser único.

Private Sub Treeview1_NodeClick(ByVal Node As MSComctlLib.Node)

Este é o evento disparado quando um nó do TreeView é clicado. A rotina captura o nó que foi clicado e escreve sua identificação no label1. É interessante notar que quando um nó filho é clicado, ele leva também a identificação do nó pai, separado por vírgula. Apesar de parecer um detalhe, este formado auxiliaria o acesso a um determinado nó pelo seu valor usando esta string através da função Split(), que retornaria um array com os valores respectivos.

Private Sub CommandButton1_Click()
Simplesmente fecha o userForm.

Conclusão:

Uma vez seguido o tutorial, percebe-se que o funcionamento do controle TreeView é bastante intuitivo. O exemplo foi aplicado ao Excel, fazendo uso de seus objetos, mas poderia facilmente ser aplicado ao Access, Word ou qualquer aplicação que faça uso do VBA. Na verdade, o controle é disponibilizado no Windows, de forma que qualquer aplicativo que ofereça interface de programação, como Delphi o VB tradicional.

Adicionando imagens ao TreeView:

É comum ver o controle TreeView ser utlizado em várias operações do Microsoft Windows, a começar pelo próprio Windows Explorer. No caso deste último, pode-se ver que há imagens associadas a cada nó, como pode ser visto na figura abaixo:

Como na figura acima, é possível associar imagens aos nós do TreeView. Um exemplo de como adicionar imagens ao TreeView pode ser visto neste link.

Usando a TreeView para navegar pelos Ranges:

A rotina abaixo adiciona a funcionalidade de navegação ao userForm TreeView. Adicione um botão ao userForm e adicione o código abaixo. Isso fará com que a aplicação navegue até o nó selecionado e feche o form. Se for uma planilha, nevegará até a mesma, se for uma célula, o mesmo será feito:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Private Sub CommandButton2_Click()
'Purpose: Go to the selected node
 
     Dim sNodes() As String
 
     'Verifica se existe algum nó selecionado
     If Me.Label1.Caption = vbNullString Then
          MsgBox "You have not selected anything!" & vbNewLine & _
                 "Please select something and try again.", vbCritical + vbOKOnly, _
                 "Nothing selected"
          Exit Sub
     End If
 
     'faz o split do conteúdo do Label
     sNodes = Split(Me.Label1, ",")
 
     With Worksheets(sNodes(0))
          'Ativa a planilha
          .Activate
 
          'Ativa o range
          If UBound(sNodes) + 1 > 1 Then
               'Ativa o range selecionado
               .Range(sNodes(1)).Activate
          Else
               'Quando da seleção de uma planilha, ativa a célula A1
               .Range("A1").Activate
          End If
     End With
 
     'Fecha o userform
     Unload Me
 
End Sub

Excel VBA – Delete duplicate rows

The macro below deletes rows with repeated values based on selected column. It’s almost the same as filter default option in Microsoft Excel, but using VBA Code. Copy and Paste the code in a new VBA Module. Fill a worksheet with some sample data, of course, with repeated values. Now, type Alt+F8 to activate and run the macro to see the result.

Public Sub ExcluirLinhasDuplicadas()
 
    Dim Col As Integer
    Dim r As Long
    Dim C As Range
    Dim N As Long
    Dim V As Variant
    Dim Rng As Range
 
    On Error GoTo EndMacro
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    Col = ActiveCell.Column
 
    If Selection.Rows.Count > 1 Then
        Set Rng = Selection
    Else
        Set Rng = ActiveSheet.UsedRange.Rows
    End If
 
    N = 0
    For r = Rng.Rows.Count To 1 Step -1
        V = Rng.Cells(r, 1).Value
        If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
            Rng.Rows(r).EntireRow.Delete
            N = N + 1
        End If
    Next r
 
EndMacro:
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 
End Sub

Source:http://vbamacros.blogspot.com/

VBA – Verificar conexão com a Internet

Internet Connection

Método simples para verificar se o computador está ou não conectado a Internet por VBA.

Copie e cole o seguinte código em um módulo VBA:

Private Declare Function InternetGetConnectedStateEx Lib "wininet.dll" _
                                                     (ByRef lpdwFlags As Long, _
                                                      ByVal lpszConnectionName As String, _
                                                      ByVal dwNameLen As Integer, _
                                                      ByVal dwReserved As Long) _
                                                      As Long
 
Dim sConnType As String * 255
 
Sub TesteConexaoInternet()
    Dim Ret As Long
    Ret = InternetGetConnectedStateEx(Ret, sConnType, 254, 0)
    If Ret = 1 Then
        MsgBox "Você está conectado a Internet via " & sConnType, vbInformation
    Else
        MsgBox "Você não está conectado a Internet", vbInformation
    End If
End Sub

A chamada retorna uma mensagem que avisa se existe conexão com a Internet e através de qual conexão ela é feita.

Abraços

Tomás

VBA – Criando Macros Simples e Úteis

O Microsoft Excel possibilita a automação de tarefas através de macros. Isso não é novidade.

Quando o assunto é automatizar tarefas repetitivas ou um conjunto de tarefas, as macros resolvem o problema. Por isso, é sempre bom ter em mãos algumas macros prontas que geralmente facilitam nosso trabalho em diversas situações. Neste artigo, a idéia é demonstrar algumas que tentam resolver algumas dessas situações.

Zoom-In, Zoom-Out automatizado.

No Excel 2003, trocar o zoom da planilha nem sempre é a tarefa mais cômoda de se fazer. O único atalho disponível depende do mouse com o botão de scroll com a tecla Ctrl pressionada. Criaremos então duas macros. Uma que incrementa o zoom em 10% e outra que o diminui na mesma quantidade. Vamos aproveitar inclusive uma outra macro para configurar o zoom para seu valor padrão que é 100%.

Veja o código abaixo:

Public Sub ZoomIn()
    Dim maisZoom As Long
    maisZoom = ActiveWindow.Zoom
    maisZoom = maisZoom + 10
 
    If maisZoom <= 400 Then         ActiveWindow.Zoom = maisZoom     End If End Sub Public Sub ZoomOut()     Dim menosZoom As Long     menosZoom = ActiveWindow.Zoom     menosZoom = menosZoom - 10     If menosZoom >= 10 Then
        ActiveWindow.Zoom = menosZoom
    End If
End Sub
 
Public Sub ZoomPadrao()
    ActiveWindow.Zoom = 100
End Sub

Observação: Adicionalmente nas macros ZoomIn e ZoomOut, foram adicionadas verificações no valor que é atribuido à propriedade ActivateWindow.Zoom que reflete o Zoom da planilha, já que esse valor não pode ser menor que 10 ou maior que 400. Atribuir qualquer valor que estrapole estes limites geraria um erro na execução da Macro.

Para efetuar um teste idôneo, retorne ao Microsoft Excel e execute estas macros, clicando em Ferramentas->Macro->Macros ou clicando o atalho Alt+F8. A seguinte tela surgirá:

Selecione a macro ZooIn e clique no botão executar. Veja que o zoom da planilha foi aumentado em 10%. Execute-a novamente para confirmar o resultado.

Tente executar o mesmo procedimento, só que agora selecionando a macro ZoomOut.

Para terminar, termine executando a macro ZoomPadrao e verfique se o Zoom da planilha é colocado par ao valor 100%.
Com certeza toda essa operação ficaria mais fácil se não fosse necessário percorrer tantos passos, ou seja, abrir a caixa de diálogo de Macros, selecionar a Macro e logo em seguida clicar no botão executar. Felizmente, o Excel permite que você associe teclas de atallho para acelerar a execução de suas Macros. Para associar uma Macro a uma atalho, abra a caixa de diálogo de Macros (Alt+F8), selecione a Macro ZoomIn e logo em seguida clique no botão Opções para que a seguinte janela seja mostrada.

Essa tela permite que seja inserida uma combinação de teclas, obrigatóriamente iniciada pela tecla Ctrl para ser associada a execução de suas macros. Isso significa que com apenas uma combinação de teclas será possível invocar a execução de uma determinada Macro. Para efetuarmos um teste, nesta tela, com o cursor do mouse sobre a caixa de texto logo após o “Ctrl+” mantenha a tecla Shift pressionada e sem soltá-la, pressione a tecla M. O resultado deve ser parecido com a figura abaixo:

É aconselhável utilizar atalhos que sejam diferentes daquela já existentes no Excel como Ctrl+P ou Ctrl+A. pois estes serão substituídos por estes novos.

Foi adicionada também uma descrição para fazer referência ao funcionamento da Macro ZoomIn. Clique em OK para voltar à caixa de diálogo de Macros nela, clique no botão fechar.

Com a janela do Microsoft Excel ativa, pressione o atalho Ctrl+Shift+M (para facilitar, mantenha pressionada as teclas Ctrl e Shift e aperte a tecla M sem soltar as duas anteriores) e confira o funcionamento da Macro. Para efetuar o mesmo procedimento para as outras Macros, adicione os atalhos Ctrl+Shift+N para a Macro ZoomOut Ctrl+Shift+P para a Macro ZoomPadrao.

O video para download disponível nesta página apresenta claramente a execução das macros através deste atalho.

Conclusão

Esta é apenas uma amostra do que pode ser feito para tornar algumas tarefas bem úteis bem fáceis de serem acessadas. Alguns exemplos comuns são uma macro para Salvar e Fechar automaticamente uma planilha, ou para importar dados de um arquivo, entre outras.

Com estes recursos em mãos, a criatividade é o limite.

Tomás Vásquez

https://www.tomasvasquez.com.br

Public Sub ZoomIn()
Dim maisZoom As Long
maisZoom = ActiveWindow.Zoom
maisZoom = maisZoom + 10

If maisZoom <= 400 Then
ActiveWindow.Zoom = maisZoom
End If
End Sub


Public Sub ZoomOut()
Dim menosZoom As Long
menosZoom = ActiveWindow.Zoom
menosZoom = menosZoom – 10

If menosZoom >= 10 Then
ActiveWindow.Zoom = menosZoom
End If
End Sub


Public Sub ZoomPadrao()
ActiveWindow.Zoom = 100
End Sub