VBA – Auto executando macros ao abrir arquivos do Office

É uma necessidade básica para quem está começando a programar em VBA.

Depois da criação das primeiras macros e formulários, é preciso alguma forma de executá-los sem precisar ir direto ao VBA ou simplesmente acionando o menu de macros.

Os caminhos mais tradicionais são:

  • Associar a execução de uma macro a um botão de comando ou outro componente qualquer
  • Colocar a execução de determinada macro na abertura do arquivo

Vamos tratar aqui da segundo item.

Usando o Auto_Open

Existe uma forma geral de executar esta tarefa no VBA, idenpendente de qual seja o aplicativo hospedeiro. Basta dar ao nome da macro, ou seja, o cabeçalho da função como Auto_Open.

Suponha a macro abaixo:

1
2
3
Public Sub MostraMensagem()
    MsgBox "Seja bem vindo"
End Sub

Clicar em F5 para testar o código. O resultado deve ser uma caixa de mensagem com a mensagem “Seja bem vindo”. Para mostrar esta macro na abrir um arquivo, bastaria que esta fosse renomeada para Auto_Open ao invés do nome original. Para não estragar o código, poderíamos criar uma macro de nome Auto_Open que chama a nossa macro, por exemplo:

1
2
3
Public Sub Auto_Open()
    Call MostraMensagem
End Sub

O único requisito para o funcionamento desta macro é que ela não tenha parâmetros, podendo ser um Function ou Sub e ser chamada após esta execução.

Mapeando o evento de abertura do arquivo

Este é outro método também largamente utilizado. Praticamente todos os aplicativos dotados do VBA possuem um evento que representa a abertura de um arquivo. Abaixo segue alguns exemplos do Microsoft Office:

Aplicativo Evento
Excel Workbook_Open
Word Document_Open

Alguns aplicativos são excessão, como o PowerPoint que suporta somente eventos em nível de Aplicativo, assunto não abordado aqui.

Como exemplo, usarei o Excel para mapear o evento Workbook_Open.

  • Abra o Excel
  • Acione o VBA em Ferramentas->Macro->VBA ou simplesmente Alt+F11
  • Na janela de projeto, procure o item EstaPasta_de_trabalho
  • Clique duas vezes para abrir a tela de código
  • Na caixa de listagem à esquerda superior da tela de código, selecione o item Workbook
  • Caso nada aconteça, selecio a caixa de listagem à direto superior da tela de código e selecione o item Opem
  • Isso fará com que o evento Workbook_Open seja criado automaticamente

O código gerado é parecido com este:

1
2
Public Sub Workbook_Open()
End Sub

Qualquer código colocado dentro deste procedimento será executado na abertura da pasta de trabalho.

Conclusão

Como no VBA não existe um contexto de aplicativo, onde existe um método principal de início de execução, estas rotinas auxiliam bastante quando profissionalizamos programas feitos nesta linguagem.

Bom proveito!

Segurança – Quebrando senhas de projetos VBA do Microsoft Office

Essa é uma informação um pouco perigosa, devo admitir. Ela compromete uma série de desenvolvedores de aplicativos baseados em VBA, principalmente no contexto de produto.

Mas como considero isso mais uma “falha” do que um “macete”, publicarei aqui a informação para registro e possíveis providências.

O lado bom

Os desenvolvedores VBA recorrem geralmente à proteção de seus projetos pelo mecanismo de proteção padrão oferecido pelo editor. Clicando com o botão direito sobre o projeto, selecione o opção VBAProject Properties (Propriedades do Projeto VBA):

Quebrando senha VBA

A seguinte janela será mostrada após a seleção da aba Protection (Proteção):

Quebrando senha VBA

Até aqui é o tradicional. Basta informar a senha nos dois campos e travar o projeto para visualização (lock project for viewing) que teremos nosso projeto VBA bloqueado para visualização e edição na próxima vez em que o arquivo for aberto.

O lado mau

Qualquer veterano na linguagem VBA sabe que existem uma série de programas que “quebram” as senhas destes projetos. Na verdade eles a sobrescrevem de alguma forma. O resultado é que os projetos, mesmo protegidos por senha ficam vulneráveis.

Geralmente estes softwares usam de artimanhas que vão desde a força bruta (tentativa e erro) ou eles entram mesmo no âmago do arquivo mexem no que não devem, substituindo assim a o dado devido.

Esses softwares são pagos na maioria das vezes, o que pelo menos caracteriza um pouco de profissionalismo por parte de seus fabricantes.

Concordo que em alguns casos existe o esquecimento por parte do autor, mas diria que este são a minoria. Geralmente alguém que tem conhecimento em VBA suficiente para construir um projeto que precisa ser protegido por senha, costuma ser organizado com este tipo de coisa. Temos uma outra situação onde empresas geralmente sofrem com funcionários de má fé que vão embora e largam arquivos bloqueados.

Estes são os únicos casos em que vejo necessidade da existência deste tipo de aplicativo, pois trata-se de cuidar da propriedade intectual do proprietário do código fonte.

Tirando isso, o resto pode ser caracterizado como violação de propriedade.

Bom, posto isso, vem aí a má notícia.

Quebrando o projeto VBA

É um pouco assustador, mas vamos lá.

Para ter acesso ao código de um projeto VBA, é fácil e barato. Basta baixar o Open Office neste endereço:

http://www.broffice.org/download

Para quem não sabe, o Open Office é uma suíte de aplicativos de escritório concorrente do Microsoft Office, assim como o Star Office. Não vou discutir as capacidades do Open Office, além da importante característica que ele possui de também ter disponível um suíte de programação muito semelhante ao VBA, também baseada no Visual Basic, capaz de construir aplicações da mesma forma.

Quando você abre um arquivo do Excel ou Word no aplicativos respectivos no Open Office (Calc e Writer), você tem acesso a todo o conteúdo do arquivo da mesma forma, bem como a todo o projeto VBA, incluindo módulos e formulários.

Muito bem, isso tudo é muito bom pois significa uma compatibilidade quase que universal entre arquivos e aplicativos. Com isso não seria necessário conversões na hora de trocar arquivos com outros sistemas.

O problema é que o Open Office abre o projeto em VBA, mesmo que ele esteja protegido por senha.

isso dá a qualquer um acesso completo a todo o código do projeto.

Não há como demonstrar isso aqui, mas para testar:

  • crie um novo arquivo no Excel ou Word
  • abra o VBA
  • adicione alguns módulos e formulários
  • adicione alguns controles a estes formulários e alguns códigos simples
  • depois disso, proteja o projeto com senha e bloqueie-o para visualização (como mostrado acima)
  • salve e feche o arquivo
  • abra-o novamente no Microsoft Office para certificar-se de que o projeto está mesmo bloqueado para visualização
  • após certificar-se, abra o Open Office Calc para abrir o arquivo de Excel ou o Open Office Calc para o abrir o arquivo do Word
  • através do menu ferramentas, abra a tela de macros e selecione um objeto do projeto VBA e clique em editar para visualizar o código

Seguindo estes passos fica provado o fato do acesso extremamente facilitado ao projeto VBA através de um software livre e gratuito.

Conclusões e comentários

Contra a tecnologia não há nada que se possa fazer. Pelo menos com aquilo que veio da tecnologia.

Não dá para saber de quem é a culpa, se é que há algum culpado nesta história.

Da Microsoft por não promover um esquema de segurança mais eficaz?

Da fundação Open Office por não respeitar o esquema de segurança promovido pelo Microsoft Office?

Eu tenho minha opinião. Fica aqui o protesto pelo fato e o agradecimento a blogosfera por permitir descobrir e divulgar a informação.

Abraços

Tomás

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