VBA - Controlando o Excel

Neste artigo vou mostrar alguns conceitos básicos de como podemos controlar o Excel 2007 usando VBA - Visual Basic for Applications.

A figura a seguir mostra o modelo de objetos do Microsoft Excel. Observe que no topo da hierarquia de objetos esta o objeto Application. Este objeto é onde tudo se inicia no Excel.
O objeto Application pode ser considerado o pai de todos os outros objetos.
Você usa o objeto Application para:

1 - Executar ações de nível superior, tais como parar o Excel, mostrar caixas de diálogo,
ou recalcular todos as pastas de trabalho;
2 - Controlar as opções do Excel, como as configurações na caixa de diálogo
Opções de Ferramentas;
3 - Obter referências a outros objetos no Excel;

Vamos abordar algumas das tarefas realizadas pelo objeto Application.

Obs: Para saber como criar o código VBA descrito neste artigo leia os artigos :

1- Encerrar o Excel

Utilize o método Quit para fechar o Excel. Se houver qualquer pastas de trabalho com alterações não salvas, o Excel exibe uma caixa de diálogo perguntando ao usuário se essas alterações devem ser salvas. Existem várias maneiras de mudar esse comportamento:

A seguir temos um exemplo de código que mostra como salvar todos os Workbooks abertos antes de fechar sem avisar o usuário:

Sub EncerraSalvaTudo()
    Dim wb As Workbook
    For Each wb In Workbooks
        wb.Save
    Next
    Application.Quit
End Sub

O código a seguir sai do Excel sem salvar nenhum workbook:

Sub EncerraNaoSalvaNada(  )
    Dim wb As Workbook
    For Each wb In Workbooks
        ' Marca o workbook como salvo
        wb.Saved = True
    Next
    Application.Quit
End Sub

Ao definir a propriedade Saved como True, o Excel 'pensa' que ele não precisa salvar as alterações e elas são perdidas quando o Excel for encerrado.

Existe uma outra maneira de fazer isso usando o método SaveWorkspace. Este método permite que você salve um arquivo .xlw que você pode usar para restaurar os workbooks e janelas atualmente em uso. A seguir o código que usa este método:

Sub SaiComResume()
    Application.SaveWorkspace "Resume.xlw"
    Application.Quit
End Sub

2- Bloquear a ação do usuário

Às vezes você quer impedir que os usuários interrompam o Excel enquanto você executa alguma tarefa demorada via código. O objeto Application fornece formas de limitar a interação do usuário a saber:

Cada uma dessas abordagens deve incluir um código no final do procedimento para alterar as configurações de retorno aos seus padrões, quando o código terminar, caso contrário, você pode bloquear um usuário permanentemente.

O código VBA abaixo mostra como podemos bloquear a ação de um usuário enquanto um processamento esta sendo feito:

Sub BloqueiaUsuario()
    Dim cel As Range
    ' exibe o cursor da ampulheta
    Application.Cursor = xlWait
    ' Desabilita a interação do usuário
    Application.Interactive = False
    Application.ScreenUpdating = False
    ' Simula uma tarafe longa
    For Each cel In [a1:iv999]
        cel.Select
    Next
    ' Restaura as configurações padrão
    Application.Interactive = True
    Application.ScreenUpdating = True
    Application.Cursor = xlDefault
    [a1].Select
End Sub

Um dos benefícios da definição de ScreenUpdating como False é que o código anterior é executado mais rapidamente, pois o Excel não precisa atualizar a tela ou rolar a planilha como células são selecionadas. Apenas certifique-se de retornar às atualizações de tela quando terminar.

3- Abrir e Fechar a janela Excel

O objeto Application fornece uma coleção de objetos Windows que permite que você abra, organize, redimensione e feche as janelas filhas do Excel. O código a seguir abre uma nova janela filha e depois coloca as janelas abertas em cascata e abre as janelas para a pasta de trabalho ativa:

Sub OpenCascadeWindows()
    ActiveWindow.NewWindow
    Application.Windows.Arrange xlArrangeStyleCascade, True
End Sub

Você pode fechar e maximizar janelas filhas usando métodos no objeto Window. Por exemplo, o código a seguir fecha a janela aberta no código anterior e restaura a janela original para um estado maximizado no Excel:

Sub CloseMaximize()
    ActiveWindow.Close
    ActiveWindow.WindowState = xlMaximized
End Sub

Finalmente, você pode controlar a janela pai do Excel usando as propriedades WindowState e DisplayFullScreen do objeto Application. O código abaixo mostra um exemplo:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub ChangeExcelWindowState()
    Application.WindowState = xlMaximized
    Sleep 1000
    Application.WindowState = xlMinimized
    Sleep 1000
    Application.WindowState = xlNormal
    Sleep 1000
    Application.DisplayFullScreen = True
    Sleep 1000
    Application.DisplayFullScreen = False
End Sub

Obs: O código acima usa a rotina sleep da API Kernel32.

4- Exibindo Diálogos

Existem 3 diferentes tipos de caixas de diálogos no Excel :

  1. As que realizam ações
  2. As que retornam informações
  3. E as caixas de diálogos customizadas que você cria a partir de formulários Visual Basic. O objeto Application fornece diversas formas de exibir os dois primeiros tipos de caixas de diálogos a saber:

1- O código a seguir exibe a caixa de diálogo Abrir e abre o arquivo selecionado pelo usuário:

Sub AbrirArquivo()
    On Error Resume Next
    Application.FindFile
    If Err Then Debug.Print "O usuário cancelou a operação."
End Sub

Podemos obter o mesmo resultado usando a coleção Dialogs:

Sub AbrirArquivo2()
    On Error Resume Next
    Application.Dialogs(XlBuiltInDialog.xlDialogOpen).Show
   If Err Then Debug.Print "O usuário cancelou a operação."
End Sub

Os dois exemplos acima exibem a caixa de diálogo Abrir e abre um arquivo no Excel. A coleção Dialogs pode exibir qualquer uma das caixas de diálogo do Excel (aproximadamente umas 250)

Às vezes você não quer que o Excel execute a sua ação padrão após o usuário fechar o diálogo, em vez disso, você prefere obter as informações a partir do diálogo para realizar suas próprias ações no código. O exemplo mais comum disso é quando você deseja obter um nome de arquivo ou pasta. Nesse caso, usamos o método FileDialog.

O método FileDialog exibe uma caixa de diálogo Abrir, mas não abre o arquivo. Você pode alterar a legenda, o filtro de arquivo e outras configurações também. O código a seguir usa o FileDialog para abrir um arquivo da Web no navegador:

Sub AbrirArquivoWeb()
    With Application.FileDialog(msoFileDialogFilePicker)
        ' define as opções da caixa de diálogo
        .Title = "Mostra Arquivo Web"
        .Filters.Add "Web files (*.htm)", "*.htm;*.html;*.xml", 1
        .FilterIndex = 1
        .AllowMultiSelect = False
        ' Se o usuário escolhe um arquivo abre no navegador
        If .Show = True Then _
          ThisWorkbook.FollowHyperlink .SelectedItems(1)
    End With
End Sub

O método InputBox permite que o Excel Obtenha células e fórmulas a partir da seleção do usuário.

A sintaxe para Application.InputBox é :InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)

Prompt - String - A mensagem a ser exibida na caixa de diálogo.
Title - Variant opcional. O título da caixa de entrada.
Default - Variant opcional. Especifica um valor que aparecerá na caixa de texto quando a caixa de diálogo for inicialmente exibida.
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.
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.
O argumento Type do método InputBox determina o tipo de dados que o usuário pode informar, as configurações mais comuns são:

O código a seguir exibe uma caixa de diálogo solicitando o nome do usuário:

Sub GetNomeUsuario()

Dim strNome As String
strNome = InputBox(Prompt:="Informe o seu nome: ", _
                Title:="QUAL O SEU NOME", Default:="seu nome")
       
        'se não informou nada e cancelou então sai
        If strNome = "seu nome" Or strName = vbNullString Then
           Exit Sub
        Else
          Select Case strNome
            Case "Macoratti"
               'codigo
            Case "Administrador"
               'codigo
            Case Else
               'codigo
          End Select
        End If
End Sub

Além do método Application.InputBox existe também a função Inputbox cuja sintaxe é parecida com o método.

Sintaxe de função: InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

Qual a diferença ?

Ao contrário da função InputBox, podemos combinar diferentes tipos (Types) para o método InputBox e tomar uma decisão com base no seu tipo de dados.

Além disso o método Application.InputBox permite validação seletiva da entrada do usuário e aceita objetos, valores de erro e fórmulas do Microsoft Excel.

Veja o exemplo abaixo:

Sub Intervalo_DadosNumero()

Dim vDados
On Error Resume Next
Application.DisplayAlerts = False

vDados = Application.InputBox _
       (Prompt:="Selecione uma única célula, " _
        & "ou informe o numero diretamente.", _
        Title:="Qual a sua idade", Type:=1 + 8)

    On Error GoTo 0
     Application.DisplayAlerts = True

    If IsNumeric(Dados) And vDados <> 0 Then
        MsgBox "Você tem " & vDados & " anos."
    Else
       Exit Sub
    End If
End Sub

No exemplo o usuário poderia clicar na célula para obter sua referência ou informar o valor diretamente.

No código usamos On Error Resume Next e Application.DisplayAlerts=False de forma a não permitir que o Excel trate qualquer entrada inválida ou cancelamento da operação pelo usuário.

Se o usuário informar um dado inválido, como uma letra ou palavra, o foco retornará para a caixa de diálogo e sua entrada será destacada.

Obadias 1:15 Porquanto o dia do Senhor está perto, sobre todas as nações, como tu fizeste, assim se fará contigo; o teu feito tornará sobre a tua cabeça.

Obadias 1:16 Pois como vós bebestes no meu santo monte, assim beberão de contínuo todas as nações; sim, beberão e sorverão, e serão como se nunca tivessem sido.

Obadias 1:17 Mas no monte de Sião haverá livramento, e ele será santo; e os da casa de Jacó possuirão as suas herdades.

Referências:

José Carlos Macoratti