VBA – Saindo do controle de fluxo (comando Exit)

O que é?

A instrução Exit sai de um bloco de código Do…Loop, For…Next, Function, Sub ou Property.

Estrutura:

Exit Do

Fornece um meio de sair de uma instrução Do…Loop. Pode ser usada somente dentro de uma instrução Do…Loop. Exit Do transfere o controle para a instrução seguinte à instrução Loop. Quando usada dentro de instruções Do…Loop aninhadas, Exit Do transfere o controle para o loop que está aninhado em um nível acima do loop onde ocorre Exit Do.

Exit For

Fornece um meio de sair de um loop For. Pode ser usada somente em um loop For…Next ou For Each…Next. Exit For transfere o controle para a instrução seguinte à instrução Next. Quando usada dentro de loops For aninhados, Exit For transfere o controle para o loop que está aninhado em um nível acima do loop onde ocorre Exit For.

Exit Function

Sai imediatamente do procedimento Function em que aparece. A execução continua com a instrução seguinte à instrução que chamou Function.

Exit Property

Sai imediatamente do procedimento Property em que aparece. A execução continua com a instrução seguinte à instrução que chamou o procedimento Property.

Exit Sub

Sai imediatamente do procedimento Sub em que aparece. A execução continua com a instrução seguinte à instrução que chamou o procedimento Sub.

Exemplo

Um exemplo útil para o uso das instruções é Exit é no caso de um loop For…Next. Supondo que estivéssemos buscando um determinado valor dentro de uma coleção de itens de 10 posíções. A alternativa mais comum é verificar a lista item a item comparando-os até que encontrar o item que se deseja. Caso o item estivesse na 10º posição, não haveria tanto problemas. Mas se ele estivesse na 2º posição, porque continuar o executando o código até a 10º posição se já achamos o item?

Veja o código abaixo:

Function ProcuraNome(nome As String) As Boolean
    Dim nomes(10) As String
    Dim i As Long
    Dim quantInteracoes As Long
    Dim resultado As Boolean
 
    nomes(0) = "João"
    nomes(1) = "Maria"
    nomes(2) = "Júlio"
    nomes(3) = "Fabio"
    nomes(4) = "Guilherme"
    nomes(5) = "Roberto"
    nomes(6) = "Francisco"
    nomes(7) = "Juliana"
    nomes(8) = "Fabiana"
    nomes(9) = "Alex"
 
    For i = 0 To 9 Step 1
        quantInteracoes = i + 1
        If nome = nomes(i) Then
            resultado = True
        End If
    Next
 
    MsgBox quantInteracoes
 
    ProcuraNome = resultado
End Function

O código declara uma matriz de tamanho 10 com nomes de pessoas e todas as suas posições. Passando um nome por parâmetro, a função retorna VERDADEIRO se o nome existir na lista e FALSO caso contrário. A matriz é preenchida entre as linhas 7 e 16 e a busca é feita em uma laço For…Next nas linhas 18 a 23.

A variável quantInteracoes foi criada para verificar quantas vezes o laço For…Next do código faz interações, quantidade esta que é mostrada numa caixa de mensagem na linha 25.

Para testar a função, em qualquer célula de sua planilha e digite =ProcuraNome(“João”). Veja que a função retona o valor VERDADEIRO, o que significa que o nome consta na lista. Se informar qualquer outro nome, por exemplo “Sérgio”, a função retornará FALSO. Note que independente do nome informado, exista ele ou não na lista, uma caixa de mensagem com a quantidade 10 sempre. Isso significa que, mesmo que o laço encontre o nome na primeira posição, ele continua executando o laço até a última posição. Se a lista contivesse 1000 nomes, o código ficaria muito demorado.

Mas como apenas queremos saber se o nome existe na lista, não há necessidade de continuar executando o laço quanto a condição da linha 20 for atendida. Então, veja a modificação feita na código:

    ...
    For i = 0 To 9 Step 1
        quantInteracoes = i + 1
        If nome = nomes(i) Then
            resultado = True
            Exit For
        End If
    Next
    ...

Adicionando a cláusula Exit For como feito no código acima, ao atender a condição da linha 20, o código sairá do laço For…Next e continuára a exeução na linha 24. Depóis de fazer a modificação, em qualquer célula de sua planilha e digite =ProcuraNome(“João”). Veja que ao invés do valor 10, a caixa de mensagem mostrará 1. Se colocar =ProcuraNome(“Maria”), a caixa de mensagem apresentará 2 e assim por diante.

Além de evitarmos trabalho desnecesário, aumentamos a performance da função para a maior parte dos casos em que ela for utilizada.

Comentários

Não confunda instruções Exit com instruções End. Exit não define o fim de uma estrutura.

Como a instrução Exit desvia a execução do código, é aconselhável usá-la somente se o código da função estiver bem estruturado, principalmente para os casos de Function e Sub.

VBA – Passagem de parâmetros por valor e por referência (ByVal, ByRef)

As estruturas para criação de funções do VBA, Sub e Function possibilitam o envio de informações para uso destas. Estas são infomadas em seu cabeçalho, entre parênteses e logo após o nome da função. São normalmente conhecidas como parâmetros ou argumentos da função.

Exemplo:

Public Sub Soma(x As Long, y As Long)
... código
End Sub

No código acima, uma Function poderia ter sido utilizada no lugar da Sub. As variáveis declaradas no topo da função devem ser informadas em sua chamada. A partir disso, a código interno terá acesso aos valores destas variáveis e fazer uso destes para suas operações.

Existem duas formas de passar valores para funções no VBA. São elas ByVal (por valor) e ByRef (por referência).

Caso a forma não seja informada, o VBA considera por padrão que os valores são passados em modo ByVal. Ou seja, o código acima poderia ser escrito desta forma sem alterar suas funcionalidades:

Public Sub Soma(ByVal x As Long, ByVal y As Long)
... código
 End Sub

Mas o que quer dizer por valor ou por referência?

Na passagem de parâmetro por valor, ao iniciar a execução, a função faz uma cópia dos valores passados para serem utilizados nas operações. Supondo que o valor passado para a função fosse uma variável criada por você. Uma cópia deste valor seria efetuada durante a execução da função, não alterando em nada o valor original de sua variável. Para entender melhor, veja o exemplo de código abaixo:

Public Sub Teste()
'cria a sua variável
 Dim var As Long
'atribui um valor a ela
var = 2
'chama a função Dobro passando a variável para esta
Call Dobro(var)
'mostra o valor da variável após a execução do código
MsgBox var
End Sub
Public Sub Dobro(ByVal x As Long)
x = x * 2
End Sub

A função teste cria uma variável do tipo numérica na linha 3 a atribui o valor ‘2’ a ela na linha 5. Em seguida, é chamada a função valor que recebe o valor da variável var e o multiplica por 2. Em seguida o valor da variável é mostrado na linha 9. Veja o resultado da execução do código:

byvalbyref1

Note que por mais que tenhamos passado o valor por parâmetro para a função Dobro, a variável var permaneceu com seu valor original. Isso aconteceu porque como explicado, ao iniciar sua execução, a função cria uma cópia em memória do valor da variável passado por parâmetro, deixando seu valor original intacto.

Esse problema pode ser facilmente resolvido utilizando uma Function ao invés de uma Sub e retornar o valor no final de final da execução. Mas se precisássemos alterar ou mesmo retornar mais de um valor? É isso que queremos contornar e é nesse ponto que entra o ByRef. Veja o código alterado abaixo:

Public Sub Teste()
'cria a sua variável
 Dim var As Long
'atribui um valor a ela
var = 2
'chama a função Dobro passando a variável para esta
Call Dobro(var)
'mostra o valor da variável após a execução do código
MsgBox var
End Sub
Public Sub Dobro(ByRef x As Long)
x = x * 2
End Sub

Note que alteramos a passagem do parâmetro de ByVal para ByRef. Veja o resultado da execução da função Teste:

byvalbyref2

O que aconteceu desta vez? Quando a declração de uma variável passada como parâmetro de uma função é feita bom ByRef, o VBA ao invés de efetuar uma cópia como o ByVal, faz um referência ou ligação à variável que foi passada. No caso do código acima, a mudança da forma de passagem do parâmetro para ByRef faz com que exista um vínculo entre a variável x na função Dobro e a variável var na função Teste.

Ou seja, qualquer alteração feito no valor x dentro da função será automaticamente refletida na variável var.

Como você pode passar mais de uma parâmetro as funções no VBA, esta se torna uma forma de poder retorna vários valores com uma única função. Veja o código alterado abaixo:

Public Sub Teste()
'cria as variáveis
 Dim var1 As Long
 Dim var2 As Long
 Dim var3 As Long
 'atribui valores
var1 = 1
 var2 = 2
 var3 = 3
 'chama a função Dobro
Call Dobro(var1, var2, var3)
'mostra o valor das variáveis após a execução do código
MsgBox var1 & vbNewLine & var2 & vbNewLine & var3
End Sub
 
Public Sub Dobro(ByRef x As Long, ByRef y As Long, ByRef z As Long)
x = x * 2
y = y * 2
z = z * 2
End Sub

Veja que todos as valores das variáveis foram alterados. Apenas para informação, o operador ‘&’ é utilizado para concatenar textos eo ‘vbNewLine’ causa uma quebra de linha, similar a um Enter no Word.

As formas de passagem de parâmetros podem ser utilizadas tanto para Sub com para Function. No caso da Function, o valor de retorno tradicional não sofre nenhuma alteração. Porém, é bom frisar que parâmetros passados em funções de planilha não sofrem alterações mesmo se passados como ByRef.

Conclusão
Vimos que o VBA disponibiliza dois tipos de passagem de parâmetros para funções, ByVal e ByRef. A forma padrão é ByVal e nela, uma cópia do valor passado por parâmetro é utilizada na execução da função. Com ByRef, uma referência à variável é passada para a função, fazendo com que todas as alterações feitas sejam refletidas na variável original.

Utilizando o recurso de passagem de parâmetros para funções e conhecendo suas forma de trabalhar, é possível resolver a grande maioria de nosso problemas na construção de aplicativos em VBA.

VBA – A diferenças entre InputBox e Application.InputBox

Introdução

O VBA disponibiliza além da tradicional função InputBox, um método de nome Application.InputBox. A principal questão é, já que existem duas, qual se deve utilizar?

A principal diferenção entre elas é que, a função InputBox é uma maneira simples de capturar textos introduzidos pelo usuário. Já o método Application.InputBox é capaz de verificar automaticamente o tipo de dados que deve ser inserido na caixa de texto do caixa de diálogo mostrada.

Estrutura do Application.InputBox

Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)

Prompt

String necessário. A mensagem a ser exibida na caixa de diálogo. Pode ser uma seqüência de caracteres, um número, uma data ou um valor Boolean (o Microsoft Excel converte o valor automaticamente em um String antes de exibi-lo).

Title

Variant opcional. O título da caixa de entrada. Se esse argumento for omitido, o título padrão será “Entrada”.

Default

Variant opcional. Especifica um valor que aparecerá na caixa de texto quando a caixa de diálogo for inicialmente exibida. Se esse argumento for omitido, a caixa de texto será deixada vazia. Esse valor pode ser um objeto Range.

Left

Variant opcional. Especifica uma posição x para a caixa de diálogo em relação ao canto superior esquerdo da tela, em pontos.

Top

Variant opcional. Especifica uma posição y para a caixa de diálogo em relação ao canto superior esquerdo da tela, em pontos.

Help File

Variant opcional. O nome do arquivo de ajuda para essa caixa de entrada. Se os argumentos HelpFile e HelpContextID estiverem presentes, um botão Ajuda aparecerá na caixa de diálogo.

Help ContextId

Variant opcional. O número de identificação do contexto do tópico da Ajuda em HelpFile.

Type

Variant opcional. Especifica o tipo de dados retornado. Se esse argumento for omitido, a caixa de diálogo retornará texto. Pode ser um dos valores abaixo ou a soma deles.

clip_a04[1]

Exemplo

Note que a única diferença desta para a função InputBox, é a existência do último parâmetro, o Type. É este que permite fazer validações de tipos simples do VBA para retorno da função. Veja o código de exemplo:

1
2
3
4
5
Public Sub TesteAppInputBox()
Dim num As Long
num = Application.InputBox("Digite um valor numérico", "Application.InputBox", "Valor numérico", , , , , 1)
MsgBox "O valor digitado foi: " & num
End Sub

No código, os parâmetros Left, Top, HelpFile e HelpContextId foram omitidos por não serem necessários para este exemplo. O importante neste código é o parâmetro Type que foi informado. Conforme a tabela, o valor 1 significa que o Application.InputBox espera que um valor numérico seja inserido pelo usuário. Execute a função e veja o resultado:

clip_d76[1]

Informe um valor de texto qualquer na caixa de entrada e clique em OK para ver o resultado:

clip_28a[1]

Veja que não necessário nenhuma verificação via código VBA. O Applcation.InputBox somente precisa da informação sobre o tipo de dado que deve ser esperado pelo usuário. Informe um valor numérico qualquer e clique novamente em OK:

clip_0e6[1]

Você pode usar a soma dos valores permitidos para Type. Por exemplo, para uma caixa de entrada que possa aceitar tanto texto como números, defina o parâmtro Type como 1 + 2.

Comentários

Use InputBox para exibir uma caixa de diálogo simples para que você possa inserir informações a serem usadas em uma macro. A caixa de diálogo tem um botão OK e um botão Cancelar. Quando você escolhe o botão OK, InputBox retorna o valor inserido na caixa de diálogo. Quando você clica no botão Cancelar, InputBox retorna False.

Quando Type é 0, InputBox retorna a fórmula na forma de texto — por exemplo, “=2*PI()/360”. Se houver quaisquer referências na fórmula, elas serão retornadas como referências de estilo A1. (Use ConvertFormula para fazer a conversão entre estilos de referências).

Quando Type é 8, InputBox retorna um objeto Range. Você precisa usar a instrução Set para atribuir o resultado a um objeto Range, como mostrado no exemplo seguinte.

1
Set meuRange = Application.InputBox(prompt := "Exemplo", type := 8)

Se você não usar a instrução Set, a variável será definida com o valor no intervalo em vez do próprio objeto Range.

Se você usar o método InputBox para pedir uma fórmula ao usuário, você terá que usar a propriedade FormulaLocal para atribuir a fórmula a um objeto Range. A fórmula de entrada deverá estar no idioma do usuário.

O método InputBox difere da função InputBox porque permite validação seletiva da entrada do usuário e aceita objetos, valores de erro e fórmulas do Microsoft Excel. Observe que Application.InputBox chama o método InputBox e InputBox sem qualificador de objeto chama a função InputBox.

VBA – Listando as impressoras instaladas no computador

O VBA é uma linguagem de automação poderosa. Porém, apesar de herdar as características no Visua Basic tradicional, não goza de todas as suas vantagens. Muitas coisas acabam precisando ser feitas na não.

O código abaixo permite listas as impressoras listas no computador, fazendo a função da coleção Printers existente no VB tradicional:

Option Explicit
 
Const PRINTER_ENUM_CONNECTIONS = &H4
Const PRINTER_ENUM_LOCAL = &H2
 
Private Declare Function EnumPrinters Lib "winspool.drv" Alias "EnumPrintersA" _
                                      (ByVal flags As Long, ByVal name As String, ByVal Level As Long, _
                                       pPrinterEnum As Long, ByVal cdBuf As Long, pcbNeeded As Long, _
                                       pcReturned As Long) As Long
 
Private Declare Function PtrToStr Lib "kernel32" Alias "lstrcpyA" _
                                  (ByVal RetVal As String, ByVal Ptr As Long) As Long
 
Private Declare Function StrLen Lib "kernel32" Alias "lstrlenA" _
                                (ByVal Ptr As Long) As Long
 
 
Public Function ListPrinters() As Variant
 
    Dim bSuccess As Boolean
    Dim iBufferRequired As Long
    Dim iBufferSize As Long
    Dim iBuffer() As Long
    Dim iEntries As Long
    Dim iIndex As Long
    Dim strPrinterName As String
    Dim iDummy As Long
    Dim iDriverBuffer() As Long
    Dim StrPrinters() As String
 
    iBufferSize = 3072
 
    ReDim iBuffer((iBufferSize \ 4) - 1) As Long
 
    'A função EnumPrinters retornará falso casa a fila de impressão estiver muito cheia
    bSuccess = EnumPrinters(PRINTER_ENUM_CONNECTIONS Or _
                            PRINTER_ENUM_LOCAL, vbNullString, _
                            1, iBuffer(0), iBufferSize, iBufferRequired, iEntries)
 
    If Not bSuccess Then
        If iBufferRequired > iBufferSize Then
            iBufferSize = iBufferRequired
            Debug.Print "iBuffer too small. Trying again with "; _
                        iBufferSize & " bytes."
            ReDim iBuffer(iBufferSize \ 4) As Long
        End If
 
        'Tentar chamar a função novamente
        bSuccess = EnumPrinters(PRINTER_ENUM_CONNECTIONS Or _
                                PRINTER_ENUM_LOCAL, vbNullString, _
                                1, iBuffer(0), iBufferSize, iBufferRequired, iEntries)
    End If
 
    If Not bSuccess Then
        'Mostra mensagem em caso de erro na chamada da EnumPrinters
        MsgBox "Error enumerating printers."
        Exit Function
    Else
        'Caso EnumPrinters retorne True, preenche o array com as impressoras
        ReDim StrPrinters(iEntries - 1)
        For iIndex = 0 To iEntries - 1
            'Pega o nome da impressora
            strPrinterName = Space$(StrLen(iBuffer(iIndex * 4 + 2)))
            iDummy = PtrToStr(strPrinterName, iBuffer(iIndex * 4 + 2))
            StrPrinters(iIndex) = strPrinterName
        Next iIndex
    End If
 
    ListPrinters = StrPrinters
 
End Function
 
Public Function IsBounded(vArray As Variant) As Boolean
    'Se a variável passada é um array, retorna True, do contrário, False
    On Error Resume Next
    IsBounded = IsNumeric(UBound(vArray))
End Function

Créditos

Autor do código: Astrid Zeelenberg
http://word.mvps.org/FAQs/MacrosVBA/AvailablePrinters.htm
Adaptado por Tomás Vásquez

Download do exemplo

ListarImpressoras.zip