VBA – Ordenando Arrays e Listas

Uma funcionalidade não nativa do VBA é a ordenação, seja lá ela qual for.

Ela comumente é necessária em Arrays e controles de lista, como ListBox e ComboBox. Métodos de ordenação existem aos montes. Neste caso, usarei o QuickSort, escrito pelo camarada Frederik do http://users.skynet.be/am044448/Programmeren/. O código abaixo faz a ordenação de um Array passado por parâmetro.

Private Sub QuickSort(strArray() As String, intBottom As Integer, intTop As Integer)
    Dim strPivot As String, strTemp As String
 
    Dim intBottomTemp As Integer, intTopTemp As Integer
    intBottomTemp = intBottom
 
    intTopTemp = intTop
    strPivot = strArray((intBottom + intTop) \ 2)
    While (intBottomTemp <= intTopTemp)
        While (strArray(intBottomTemp) < strPivot And intBottomTemp < intTop)
 
            intBottomTemp = intBottomTemp + 1
 
        Wend
        While (strPivot < strArray(intTopTemp) And intTopTemp > intBottom)
 
            intTopTemp = intTopTemp - 1
 
        Wend
        If intBottomTemp < intTopTemp Then
 
            strTemp = strArray(intBottomTemp)
 
            strArray(intBottomTemp) = strArray(intTopTemp)
 
            strArray(intTopTemp) = strTemp
 
        End If
        If intBottomTemp <= intTopTemp Then
 
            intBottomTemp = intBottomTemp + 1
 
            intTopTemp = intTopTemp - 1
 
        End If
    Wend
    'faz a chamada recursiva a si própria até que lista esteja preenchida
 
    If (intBottom < intTopTemp) Then QuickSort strArray, intBottom, intTopTemp
 
    If (intBottomTemp < intTop) Then QuickSort strArray, intBottomTemp, intTop
End Sub

A função não tem um retorno, mas como um array passado por parâmetro para um função é sempre por referência, após a execução desta seu Array estará ordenado em ordem crescente.

Para testar o funcionamento da função, crie um userForm no VBA. Coloque nele um ListBox e um CommandButton, sem se preocupar com alterar seus nomes. Coloque no código do Form a função QuickSort mostrada acima, mais o código abaixo.

Private Sub CommandButton1_Click()
    Dim MyArray(12) As String
    Dim i As Long
 
    'caso não haja itens na lista, não é necessário fazer ordenação
    If ListBox1.ListCount &lt;= 1 Then Exit Sub
    'alimenta o array
    For i = 0 To ListBox1.ListCount - 1
        MyArray(i) = ListBox1.List(i, 0)
    Next i
 
    'ordena o array
    QuickSort MyArray, LBound(MyArray), UBound(MyArray)
    'limpa o listbox
    ListBox1.Clear
    'usa o array ordenado para preencher o listbox
    For i = 1 To UBound(MyArray)
        ListBox1.AddItem MyArray(i)
    Next i
End Sub
Private Sub UserForm_Initialize()
'preenche o listbox com os meses do ano
    For i = 1 To 12
        ListBox1.AddItem MonthName(i, False)
    Next
End Sub

Ao executar o form da primeira vez, pode-se ver que o ListBox é preenchido com os meses do ano através da função MontName.

ordena_listbox_vba
Listbox Carregado com os meses

O código colocado no evento click do botão cria um array de string de tamanho 12, preenche o array com a lista de valores presente no listbox, ordena o array através da função QuickSort e re-preenche o listbox.

ListBox Ordenado
ListBox Ordenado

Uma forma bem simples de fazer ordenação em listas sempre precisar ficar re-pensando no código sempre que precisar.

<< Download do Arquivo >>

Abraços

Tomás

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 &amp; "," &amp; rngFormula.Address, _
                              Text:="Range " &amp; 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!" &amp; vbNewLine &amp; _
                 "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 &gt; 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 – Remover linhas duplicadas

A macro abaixo exclui na coluna selecionada as linhas que tiverem valores repetidos. Praticamente igual a função de filtro para registros exclusivos, só que em VBA. Copie o código e cole em um novo módulo VBA. Preenche em qualquer planilha uma lista com alguns valores repetidos. Clique Alt+F8 para ativar a macro e veja o resultado.

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

Fonte: http://vbamacros.blogspot.com/

Excel – Planilhas interativas

Da PC World Espanha

Introdução

Eu, você e a maioria das pessoas nos acostumamos a digitar valores em células e tabelas para inserir dados em planilhas do Excel. Mas o software da Microsoft oferece mais do que isso. Ele possibilita a inserção de diversos controles interativos. O uso desses recursos torna o trabalho com planilhas mais rápido, profissional e elegante.

Exemplos desses comandos são as barras de rolagem, capazes de alterar o valor de uma determinada célula, e as caixas de combinação, que obrigam o preenchimento de uma célula com uma das opções de uma lista fixa. Além de simplificar a entrada de dados, o uso desses controles evita erros de digitação e permite a associação entre diferentes categorias de valores.

Como modelo, criaremos uma planilha para o cálculo do preço de estadias em hoteis, cujo valor total varia conforme o número de diárias, o estabelecimento escolhido e o tipo de quarto. Para simplificar, criaremos primeiro uma opção com apenas quartos simples e, posteriormente, acrescentaremos opções para apartamentos duplos e triplos.

Barras de rolagem

O valor da célula B3 na imagem abaixo, que representa a duração da estadia, pode variar conforme deslizamos uma barra de rolagem, como as usadas para percorrer um documento muito longo. As barras de rolagem, como todos os controles ou caixas de diálogo, são obtidas a partir da barra de ferramentas Formulários. Se esta barra não estiver visível, clique em Exibir/Barras de Ferramentas/Formulários.

Planilhas interativas

Uma vez que a barra adequada esteja visível, clique no botão Barra de Rolagem. O cursor do mouse se transformará em uma pequena cruz. Arrastando o mouse, desenhe um retângulo de aproximadamente uma linha de altura por três colunas de largura. Ao soltar o botão, a barra será inserida na planilha.

Planilhas interativas

Agora, para vincular o valor da célula B3 à posição da barra de rolagem, clique com o botão direito sobre a caixa e escolha a opção Formatar Controle. Na caixa de diálogo que aparecerá, digite B3 no campo Vínculo da Célula. Isto significa que a célula associada ao movimento da barra será a B3. A caixa de diálogo mostra também diferentes opções de valores (Valor Mínimo, Valor Máximo, Alteração Incremental etc.), que correspondem aos valores extremos da célula vinculada e da variação do valor dessa célula à medida que o cursor da barra é deslizado. Podemos deixar os valores sugeridos pelo Excel para este exemplo. Depois de clicar em OK, teste o funcionamento da barra, deslizando o cursor ao longo dela e verificando se o número de diárias é alterado simultaneamente.

Caixas de listagem

A escolha do hotel será feita por uma caixa de listagem, aqueles menus que usamos, por exemplo, para escolher o tipo de fonte de em documento no processador de texto. Estas listas são conseguidas com o botão Caixa de Combinação na barra de ferramentas Formulários.

Planilhas interativas

Planilhas interativas

Proceda, inicialmente, da mesma maneira. Clique no botão Caixa de Combinação e desenhe, arrastando o mouse, um retângulo de aproximadamente uma linha de altura e uma coluna e meia de largura. As caixas de listagem têm dois endereços associados a elas: um Intervalo de Entrada e uma Célula Vinculada. O intervalo de entrada é a localização das células onde se encontram as opções que aparecerão na lista. No nosso caso, os nomes dos hotéis disponíveis. Já a célula vinculada não é a B4, onde deve aparecer o preço da noite para o hotel. A célula vinculada é uma célula auxiliar, por exemplo, A16.

A célula vinculada exibirá um número correspondente à posição do hotel escolhido na lista: se selecionarmos o primeiro, na célula vinculada aparecerá 1. Se selecionarmos o segundo hotel, aparecerá 2, e assim sucessivamente. No próximo passo, usaremos uma função adequada para obter, em B4, o preço correspondente ao hotel escolhido. Para designar o intervalo de entrada e a célula vinculada, clique com o botão direito do mouse sobre a caixa de listagem, escolha Formatar Controle e preencha os dois campos.

Planilhas interativas

A função Índice

Para obter, na célula B4, o preço correspondente ao hotel escolhido, é necessário utilizar a função Índice. Na célula B4, escreva a seguinte fórmula: “=INDICE(B11:B14;A16)”, sem as aspas, onde B11:B14 indica a localização dos preços dos hotéis e A16 é a célula vinculada à caixa de listagem, cujo valor determinará qual item da lista será transportado para a célula B4.

Planilhas interativas

Com esta função, já podemos testar o funcionamento da planilha, ajustando a quantidade de dias pela barra de rolagem e escolhendo o hotel pela caixa de listagem. O preço total da estadia aparecerá na célula B5, que terá uma fórmula simples de multiplicação: “=B3*B4”, sem as aspas.

Botões de Opção

Consideremos agora uma variação mais complexa dessa planilha. O preço da diária deve variar não apenas de acordo com o hotel escolhido, mas também com as opções de quarto simples, duplo ou triplo, tal como indica a tabela A11:D14 na imagem abaixo. Realizaremos a escolha do tipo de quarto por meio de Botões de Opção, outro dos controles interativos disponíveis na barra de ferramentas Formulários. Insira um da mesma forma como fez com os controles anteriores. Em seguida, crie outros dois com as opções Copiar e Colar.

Planilhas interativas

Os três botões de opção da planilha formam um grupo. Quando selecionamos um deles, a seleção dos outros dois é cancelada. Por isso são usados para a escolha entre opções excludentes. O grupo tem uma única célula vinculada comum aos três. O valor desta célula é o número da ordem do botão selecionado. Na nossa planilha, estabeleceremos A17 como célula vinculada, clicando com o botão direito do mouse, escolhendo Formatar Controle e preenchendo o campo Vínculo da Célula.

O número de cada botão corresponde à ordem em que foram criados. Se mudarmos a ordem, movendo os botões pela planilha, cada um conservará seu número original. Também podemos trocar o texto do botão por outro mais adequado. Para isso, clique com o botão direito do mouse sobre o botão e escolha Editar Texto.

Função Índice com duas variáveis

Agora, para fazer o valor da célula B4 variar também conforme o botão de opção, será necessário modificar a fórmula da função Índice, acrescentando um segundo critério. A sintaxe deve ser a seguinte: “=INDICE(B11:D14;A16;A17)”, sem as aspas. Neste caso, o primeiro argumento (B11:D14) é a tabela com a matriz de preços, o segundo (A16) indica a linha desta tabela e o terceiro (A17) aponta a coluna.

Planilhas interativas

Pronto! A planilha está concluída. Podemos indicar a duração da estadia pela barra de rolagem, escolher o hotel pela caixa de combinação e selecionar o tipo de quarto pelos botões de opção. Estes mesmos objetos podem ser utilizados para padronizar e automatizar o trabalho com qualquer planilha que envolva células cujos valores variam com freqüência, como a duração da estadia, ou sejam provenientes de uma lista, como preços dos quartos.