VBA – As diferenças entre Sub e Function

Introdução

O VBA possui dois tipos de estruturas para construção de algoritmos, Sub e Function.

A questão que surge é, quando usar uma ou outra na construção de nossos algoritmos em VBA no Excel? Na prática, Sub e Function trabalham da mesma forma com uma única diferença. A Sub são funções de processamento autônomo enquanto as Functions podem (e devem) retornar valores no final de sua execução.

Veja a estrutura de cada uma:

Sub ExemploSub([parâmetros])
     ' corpo do código
End Sub

onde:

[modificador] são palavras chave que definem o nível de acesso à estrutura de código.

[parâmetros] são variáveis que são passadas na chamada da função e utilizadas dentro. Podem existir vários ou mesmo nenhum parâmetro.

[tipo] é o que informa o tipo de dado a ser retornado pela Function. Pode ser qualquer tipo de dados válido do VBA.

[valor] uma valor que representa o retorno da função. Pode ser uma variável ou um valor constante.

Como saber qual utilizar? Isto depende do contexto da sua aplicação. Quando sua rotina precisar simplesmente executar uma processamento sem emitir qualquer tipo de informação, é aconselhável usar uma Sub. Já quando seu processamento necessitar devolver algum resultado, sem dúvida uma Function é necessária. Supondo uma situação em que você precisa criar funções que executassem operações aritméticas, elas precisariam depois de efetuar os cálculos, devolver o resultado para ser utilizado.

Veja este exemplo:

Public Function SomaSimplesF(x As Long, y As Long) As Long
    SomaSimplesF = x + y
End Function

O código efetua uma operação simples recendo duas variáveis do tipo Long como parâmetro e devolve também um Long (definido na construção da Function) através da chama SomaSimples na linha 2. Para testar as função, basta ir a uma célula da planilha e digitar =SomaSimplesF(1;3). É possível também testar a função através de código VBA, como no código abaixo:

Public Sub TestaSomaSimplesF()
    Dim soma As Long
    'chama a função SomaSimplesF e atribui o resultado à variável soma
    soma = SomaSimplesF(1, 2)
    'mostra o resultado em uma caixa de mensagem
    MsgBox soma
End Sub

Já se este exemplo fosse construído usando uma Sub, não seria possível diretamente retornar o valor como é feito na Function.  A solução então seria mostrá-lo ao usuário, por exemplo em uma caixa de mensagem. Veja este exemplo:

Public Sub SomaSimplesS(x As Long, y As Long)
    MsgBox = x + y
End Sub

Para testar a função, é precisa criar outro bloco de código que faça a chamada a este. Crie a seguinte função para testar o SomaSimplesS:

Public Sub TestaSomaSimplesF()
    'Faz a chamada a Sub SomaSimplesF
    Call SomaSimples(1, 2)
End Sub

Veja que o Sub não tem sequer a opção de mencionar o tipo de dado que pode ser retornado.

No Microsoft Excel, as Functions possuem uma característica especial. Elas podem ser usadas para criação de funções de planilha. A exigência para que isso seja possível, é criá-las com o modificador Public, que é o padrão
caso seja omitido.

Dentro deste entendimento, uma Function sempre poderá assumir o papel de uma Sub, mas não o contrário. Também por isso, o Excel utiliza Functions para criação de funções de planilha personalizadas e Subs para gravação de Macros.

Para superar a limitação do retorno de um único valor ou mesmo conseguir retornar alguns valores em Subs, é necessária a utilização passagem de parâmetros por referência. Este assunto será discutido em outro texto.

Comentários

Alguns podem pensar que por segurança, é melhor sempre criar Functions ao
invés de Subs. Porém, para termos de organização de código e padronização
de suas rotinas em VBA, procure usar a estrutura correta para ter um bom
funcionamento de seu aplicativo.

VBA – Confirmando decisões com o MsgBox (VbMsgBoxResult)

Introdução

O VBA disponibiliza uma forma de emitir mensagens personalizadas para o usuário através da função MsgBox, permitindo incluse alterar uma série de suas configurações.

Desta vez, analisaremos uma das características da MsgBox que é analisar seu retorno, que é informado pelo usuário o clicar em um dos botões disponíveis na caixa de mensagem. este recurso é útil quando se deseja fazer questões aos usuários. A resposta da questão virá da opções oferecidas a ele pelo MsgBox.

Começaremos sabendo como analisar o retorno da MsgBox. Veja o código de exemplo:

1
2
3
4
5
Public Sub TesteMsgBox()
     Dim resultado As VbMsgBoxResult
     resultado = MsgBox("Testando o VbMsgBoxResult!", vbOKOnly, "Testando o MsgBox")
     MsgBox resultado
End Sub

O resultado da execução do código serão duas caixas de mensagem na seguinte sequência (clicando em OK em cada uma delas):

ajkbsmpbw67f_40cmg98f3c

ajkbsmpbw67f_41dh5q39fm

O que valor notar aqui é o seguinte. Pelo fato de termos informado o parâmetro Button como vbOkOnly, o único botão oferecido ao usuário foi o botão OK, o resultado não poderia sido outro que não um. Vamos relembrar a tabela de valores retornados pelo MsgBox:

Valores retornados

Constante Valor Descrição
vbOK 1 OK
vbCancel 2 Cancelar
vbAbort 3 Abortar
vbRetry 4 Repetir
vbIgnore 5 Ignorar
vbYes 6 Sim
vbNo 7 Não

Segundo a tabela, ao acionar o botão OK, o usuário está informando ao VBA um tipo de retorno com o valor 1 que correnpondente à contante vbOK. Vamos mudar o parâmetro Button para vbYesNo e verificar a execução do código:

1
2
3
4
5
Public Sub TesteMsgBox()
     Dim resultado As VbMsgBoxResult
     resultado = MsgBox("Testando o VbMsgBoxResult!", vbYesNo, "Testando o MsgBox")
     MsgBox resultado
End Sub

O resultado:

ajkbsmpbw67f_42dzkmn5cq

Note que mais opções foram oferecidas ao usuário. Agora clique no botão Sim para ver o que acontece com o retorno:

ajkbsmpbw67f_43gs3jr4gm

Na tabela de Valores retornado, podemos conferir que o valor 6 se refere à contante vbYes. Execute a função novamente, só que desta vez, clique na opção Não:

ajkbsmpbw67f_44d37tqtg4

Na tabela de Valores retornado, podemos conferir que o valor 7 se refere à contante vbNo.

Desta forma, conseguimos um meio que questionar o usuário e facilmente oferecer-lhe opções a respeito desta questão e analisar suas resposta. Experimente alterar o parâmetro Button da chamada ao MsgBox e veja o resultado informado.

Tornando o Recurso Útil

Vimos que é possível oferecer ao usuário uma série de alternativas de escolha em conjunto a uma mensagem com o MsgBox. Veremos agora um exemplo simples de como analisar o retorno da função MsgBox para tomar decisões nos aplicativos VBA.

Veja o seguinte trecho de código:

1
2
3
4
5
6
7
8
9
Public Sub TesteVbMsgBoxResult()
     Dim resultado As VbMsgBoxResult
     resultado = MsgBox("Tem certeza que deseja prosseguir com esta ação?", vbYesNo, "Tomando uma decisão")
     If resultado = vbYes Then
          MsgBox "Você acaba de confirmar a ação"
     Else
          MsgBox "Você acaba de recusar a ação"
     End If
End Sub

Veja que não é necessário gravar ou mesmo analisar o valor numérico do retorno do MsgBox. O VBA disponibiliza as constantes nomeadas, como vbYes, vbNo, vbOK para que seja possível fazer uma produção e leitura mais organizada do código.

Na linha 6, é declarada a variável resultado do tipo VbMsgBoxResult que armazenará o resultado da MsgBox. Na linha 4 acontece a chamada à MsgBox. Quando o usuária clicar em uma das opções mostrada, o VBA armazenará o retorno na variável resultado.

Em seguida, usa-se a estrutura If…Then…Else para avaliar o resultado da função. Veja que a comparação é feita com a contante vbYes. Como o código deseja confirmar a ação do usuário, este analisa o resultado de mostra a mensagem pertinente, Veja o resultado da execução do código:

ajkbsmpbw67f_45gzg6g4dv

Ao clicar no botão Sim, o seguinte resultado é mostrado:

ajkbsmpbw67f_46cwz3shhc

Como o resultado do MsgBox ao clicar no boatão sim é o valor de vbYes, o código executa a mensagem da linha 5.

Se executar o código novamente e clicar no botão Não, o resultado será:

ajkbsmpbw67f_47f7gc97fs

Desta vez, a linha executada pelo código foi a de número 7.

Com estes recursos, é possível oferecer o usuário praticamente todos os tipos de opção disponibilizados pelo Microsoft Windows em todas as situações. Você pode oferecer opções como Anular/Repetir/Ignorar, Sim/Não/Cancelar, entre outras.

Comentários

Vimos que o MsgBox oferece mais recurso além da simples exibição de mensagens. Tendo conhecimento de todos as possibilidades da função, você terá em mãos uma poderosa ferramenta para resolver praticamente todas as situações de decisçao que surgirem no desenvolvimento de seus aplicativos.

VBA – Adicionando e removendo sua barra de ferramentas personalizada

Introdução

Um recurso interessante que o Microsoft Excel disponibiliza é a criação de uma barra de ferramentas personalizada que contém os atalhos de maior interesse para um determinado usuário ou planilha. Além disso, ele permite que você anexe essa barra de ferramentas à sua planilha, o que faz que esta acompanhe a planilha, fazendo que esta apareça em todo computador em que for aberta.

O problema

É uma funcionalidade muito interessante. Seria perfeita se não fosse por apenas uma problema. Quando outro usuário abre a planilha que contém a barra de ferramentas anexa, mesmo que este não abra mais esta planilha ou até a exclua do disco, a barra de ferramentas continuará adicionada a seu Microsoft Excel, o que pode até mesmo gerar erros de execução, forçando o usuário a excluí-la manualmente, desde que este tenha conhecimento para isto.

Porém, é possível contornar tal problema através de Macros que são executadas em momentos específicos, mais precisamente ao abrir a planilha que é quando a nova barra de ferramentas é adicionada e ao fechá-la, que é quando esta deve ser excluída.

Este é o processo mais aconselhado, pois toda vez em que o usuário fechar a pasta de trabalho, a barra de ferramentas será excluída.

Para efetuar este processo, vamos assumir que temos uma barra de ferramentas personalizada já criada de nome “Nova Barra” com alguns botões padrão do Microsoft Excel, conforme a figura abaixo:

Nova Barra de Ferramentas

Agora, anexe esta barra de ferramentas à pasta de trabalho, clicando em Ferramentas->Personalizar e nesta tela, selecione na lista da esquerda o item Nova Barra e clique em Anexar, deixando-a desta forma:

ajkbsmpbw67f_95fj5nkhdk

Isso faz com que em qualquer computador em que esta planilha seja aberta, a barra de ferramentas Nova Barra seja instalada. Nestas condições, todos os problemas listados acima transcorrerão.

A solução

Para resolver o problema de forma automatizada, será necessária a utilização de código VBA. Capturaremos os eventos Workbook_Open, que ocorre quando a pasta de trabalho é aberta Workbook_BeforeClose que ocorre um pouco antes desta ser fechada. Abra o VBA em Ferramentas->Macro->Editor do Visual Basic (Alt+F11) e na janela de projeto, clique duas vezes no item EstaPasta_de_trabalho para abrir a janela de código. Nesta janela, insira o seguinte:

1
2
3
4
5
6
7
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars(“Nova Barra”).Delete
End Sub

Private Sub Workbook_Open()
Application.CommandBars(“Nova Barra”).Enabled = True
End Sub

CommandBars é uma coleção que se refere a todas as Barras de ferramentas do Microsoft Excel que existem no contexto atual. Através desta, é possível obter informações e manipular de formar completa as barras de ferramentas, até sua criação completo por meio de código VBA. Para nosso caso, precisaremos apenas trabalhar algumas propriedades.

O código faz com que, ao ser aberta, a pasta de trabalho habilite a barra de ferramentas “Nova Barra” (linha 6), que existe em seu contexto pois ela foi anexada a esta. Na linha 2, dentro o evento Workbook_BeforeClose que ocorre pouco antes da planilha ser fechada, a barra de ferramentas é excluída, deixando de existir pelo menos para aquela instalação do Microsoft Excel.

Feito isto, é possível testar a funcionalidade. Feche a planilha atual. Abra-a (não se esqueça de ativar as Macros) novamente e veja que a barra de ferramentas “Nova Barra” é mostrada. Esse é o comportamento normal, mesmo sem a adição das Macros. Agora, feche e salve a planilha.

Abra o Excel (no o arquivo com a barra criada anteriormente, e sim o programa Microsoft Excel) novamente criando uma nova pasta. Veja que a barra de ferramentas “Nova Barra” já é mais mostrada. Para conferir a não existência desta, vá ao menu Ferramentas->Personalizar e na aba “Barras de ferramentas”, confira que a “Nova Barra” já existe mais.

Para conferir o teste, tente abrir esta planilha em outro computador.

Obs: Se as macros não forem ativadas, a barra de ferramentas será adiciona pois está anexada à pasta de trabalho, mas não será excluída.

Conclusão

Utilizando esta forma de gerenciamento das barras de ferramentas, além de resolver o problema da população desenfreada de barras personalizadas, é possível gerenciar corretamente suas barras de ferramentas, permitindo a construção de planilhas completas e que não deixam resíduos nos computadores de outros usuários.

VBA – Abrindo arquivos informando a senha

Esta foi uma pergunta feita num fórum e como é uma informação interessante, resolvi postar aqui.

Muita gente, aliás, todo mundo que trabalha com planilhas no dia a dia costuma proteger os arquivos mais críticos com senha de abertura do arquivo.

Até aí a coisa é simples meso para usuários básicos. Mas quando se quer automatizar este tipo de arquivo com VBA, por exemplo, abrindo-o através de outro arquivo, fica ou pouco complicado. Mas nem tanto.

Se reparar bem, ao ativar no próprio VBA o método Open da coleção Workbooks, será possível ver que um dos parâmetros da função é a senha de abertura do arquivo. O exemplo abaixo abre um arquivo de Excel via VBA:

1
2
3
Sub AbreArquivo()
     Call Workbooks.Open(FileName:="C:\arquivo.xls")
End Sub

Porém, caso o arquivo contenha senha de abertura, bastaria adicionar o seguinte argumento:

1
2
3
4
Sub AbreArquivoComSenha()
     Call Workbooks.Open(FileName:="C:\arquivo.xls", _
     Password:="1234")
End Sub

Para informação, o método devolve um objeto do tipo Workbook que pode ser manipulado depois de aberto.
Isso resolve o problema. Claro, é preciso tomar os devidos cuidados para proteger o projeto VBA e não expor indevidamente a senha para o usuário. Recomenda-se também adicionar um tratamento de erros para inibir ainda mais as mensagens.

Em tempo, a mesma informação é válida para o Word/VBA, porém a coleção utilizada é a Documents e o nome do parâmetro é PasswordDocument. O código abaixo mostra um exemplo da aplicação.

1
2
3
4
Sub AbreDocumentoComSenha()
     Call Documents.Open(FileName:="C:\Documento.doc", _
     PasswordDocument:="1234")
End Sub

Abraços

Tomás