Fase # 2: Código VBA do Excel (macros)
Lição 4: Código VBA do Excel para Planilha (WorkSheet)
objeto WorkSheet representa uma planilha especificada da coleção WorkSheets.
Por alguma razão obscura quando você clicar num nome de folha na janela VBAProject do Visual Basic Editor (VBE) ela tem duas propriedades chamadas Name. Uma entre parênteses e outra não. Uma planilha tem dois nomes, aquele um que aparece na sua guia no Excel (vamos chamar este de propriedade "caption") e aquele um que tem como objeto VBA na janela VBAProject do Visual Basic Editor (VBE) (por default: Plan1, Plan2....). Assim você pode ativar Plan1 "cujo caption é "Balanço"" com ambas declarações:
Sheets("Balanço").Select Worksheets("Balanço").Select
não esquecer os parênteses e as aspas duplas ou Plan1.Select
Eu prefiro o segundo método por 2 razões. Primeiro há menos para teclar nele e em segundo lugar, se você, ou seu usuário, sempre mudar o caption da folha, não há necessidade para rever e corrigir seu código de acordo. A única desvantagem é que o código VBA não é claro e fácil para ler (O que é "Plan1"?). É por isto que eu renomeio as folhas no Visual Basic Editor (eu seleciono a folha na Janela Project e modifico o seu nome (aquele um com parênteses) na Janela Propriedade). No exemplo abaixo o nome da folha (quando objeto VBA) é "folBalanco" quando para o caption, realmente eu não tomo cuidado:
folBalanco.Select
Propriedades da Worksheet
Quando a folha for selecionada na janela VBAProject você pode ver 11 propriedades da planilha na Janela de propriedades do VBE, propriedades para as quais você pode definir um valor default para começar com ele e que você pode modificar pelo procedimento VBA sempre que você quiser.
Existem 3 propriedades que você usará freqüentemente: a Name (nome dentro de parênteses), a Name (sem parênteses) que é de fato o caption aparecendo na guia da folha no Excel e a propriedade visible.
Propriedades (Name)
Como explicado acima você pode mudar a (Name) se você estiver desenvolvendo uma pasta para outros que possam modificá-la no Excel. Você não pode mudar o (Name) de uma folha programaticamente.
Deve-se tomar cuidado de não se usarem caracteres especiais tais como: “:”, “/”, “?”e “*”.
Para mudar o caption você pode fazê-lo na janela propriedade do VBE, ou no Excel, clicando com o botão direito do mouse na guia e daí selecionando "Renomear". Programaticamente você pode mudar o caption de uma folha com o seguinte código: Sheets("Plan1").Name= "Balanço"
Propriedades Visible
A propriedade "Visible" controla a visibilidade de uma planilha e pode tomar 3 valores diferentes. Os primeiros dois são True ou False significando que uma certa folha está ou não está visível que ela está oculta ou não.
Sheets("Plan1").Visible= True Sheets("Plan1").Visible= False
Lembre-se que fórmulas em células são calculadas mesmo se a folha estiver oculta mas que antes você pode fazer algo programaticamente na folha você ocultá-la:
Sheets("Plan1").Visible= True
Sheets("Plan1").Select Range("A1").Value=6 Sheets("Plan1").Visible= False
O terceiro valor que a propriedade "Visible" pode tomar é muito interessante. Uma folha pode ser very hidden "Sheets("Plan1").Visible= xlVeryHidden". Neste estado não somente a folha está oculta mas você não pode ver seu nome quando no Excel você for para "Formatar/Planilha/Reexibir". O valor xlVeryHidden pode somente ser variado programaticamente. O que significa que somente usuários que tenham acesso ao código VBA podem exibir esta folha. Se seu código estiver protegido por um
password somente usuários com o password pode acessar o código e modificar o valor
"xlVeryHidden". Você pode usar este valor da propriedade "Visible" para ocultar informação confidencial como salários e preços ou ocultar parâmetros que você não quer que sejam modificados pelo usuário.
Lembre-se que fórmulas nas células são calculadas mesmo se a folha está very hidden mas que antes você pode fazer qualquer coisa programaticamente na folha você deve reexibí-la:
Sheets("Plan1").Visible= True Sheets("Plan1").Select
Range("A1").Value=6
Sheets("Plan1").Visible= xlVeryHidden
Lembre-que fórmulas nas outras folhas referindo-se às células de uma folha hidden ou very hidden funciona mesmo se a folha estiver hidden ou very hidden.
Se você quiser ocultar muitas folhas ao mesmo tempo você usará o seguinte código:
Sheets(Array("Plan1", "Plan2")).Select Sheets("Plan1").Activate
Lição #04 – Código VBA do Excel para Planilha (Worksheet) 132
Os valores xlSheetHidden e xlSheetVisible permitem, respectivamente, ocultar ou exibí-la novamente.
Propriedades ScrollArea
Essa propriedade limita a área de rolagem e atuação de uma planilha. Por exemplo: ActiveSheet.ScrollArea = “A1:D20”
Permite-nos trabalhar apenas no intervalo delimitado.
Propriedades CodeName
Retorna o nome de código de uma planilha, que substitui a declaração Worksheets(index). Podemos usar: Plan1.Cells(1,1).Value = 10
Ao invés de :
Worksheets(1).Cells(1,1).Value = 10
Vejamos agora alguns MÉTODOS do objeto Worksheet:
Método Deletar
Você poderá querer deletar folhas. Aqui está o código para fazer isto: Sheets("Balanço").Delete
Ou
Worksheets(worksheets.count).Delete
Método Add
Você poderá também querer adicionar uma folha. Se você usar o seguinte código o VBA adicionará uma nova folha antes da planilha ativa.
Sheets.Add
Se você quiser ser mais preciso como para onde e quantas você usará em cada um dos seguintes procedimentos:
Inserindo uma folha após a folha cujo caption é "Balanço" e cujo nome é shBalance:
Sub proTeste()
Sheets.Add before:=Sheets("Balanço")
End Sub
Sub proTeste()
Sheets.Add before:=folBalanco End Sub
Inserindo três folhas após a folha cujo caption é "Balanço":
Sub proTeste()
Sheets.Add after:=Sheets("Balanço"), Count:=3 End Sub
Inserindo uma folha no começo da pasta. Note a ausência de aspas duplas quando usar a posição (rank) da folha:
Sub proTest()
Sheets.Add after:=Sheets(1)
End Sub
Finalmente se você quiser adicionar uma nova folha no final da pasta você precisa contar as folhas com Sheets.Count e usar este valor quando a posição da folha após a qual você quiser adicionar a nova folha:
Sub proTeste()
Sheets.Add After:=Sheets(Sheets.Count)
End Sub
Método Select
Este método seleciona uma planilha. A vantagem deste método sobre o Activate é que ele serve para selecionar várias planilhas ao mesmo tempo (poderia ser com o propósito de excluí-las).
Método Copy
Serve para criar uma cópia fiel de uma determinada planilha, em uma posição específica na coleção Worksheets. Por exemplo para criar uma cópia fiel da planilha ativa como a última da coleção Worksheets:
Active.Copy After:=Worksheets(Worksheets.count)
Algumas vezes você quer fornecer uma única planilha de uma pasta para alguém mas você não quer que todas as fórmulas vão juntas. Aqui está o código para copiar uma folha de uma pasta numa nova pasta, trocar as fórmulas pelos valores e salve a nova pasta:
Sheets("Plan3").Select Sheets("Plan3").Copy Células.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.SaveAs "novaPasta.xls"
Se você quiser fazer a mesma coisa para muitas folhas, você repete o procedimento ou escreve: Sheets(Array("Plan1", " Plan2")).Select Sheets("Copia2").Activate Sheets(Array("Plan1", " Plan2")).Copy Sheets("Plan1").Select Células.Select Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Lição #04 – Código VBA do Excel para Planilha (Worksheet) 134
Sheets("Plan2").Select Células.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.SaveAs "novaPasta.xls"
Veja a lição sobre pastas para gerenciar o diretório (path) quando usar o método "SaveAs".
ActiveSheet
A ActiveSheet é a planilha que foi selecionada por último. Assim você pode escrever: ActiveSheet.Visible=True
ActiveSheet.Copy
Lembre-se que quando você copiou uma célula ou um grupo de células ou qualquer outro objeto de uma folha você SEMPRE cola-o na ActiveSheet: ActiveSheet.Paste a menos que você esteja fazendo um PasteSpecial no qual caso o objeto é "Selection": Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Se você quiser verificar são os autofilters estão ligados para removê-los você precisa usar o objeto ActiveSheet também como no seguinte procedimento onde eu marco os autofilters para ligados para removê-los ou sair do procedimento:
Range("A2").Select
If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilter Else
Exit Sub End If
Você também pode do alguma coisa em cada uma das folhas numa pasta com o seguinte código. Neste exemplo eu defino o valor da célula A1 a 22 em cada planilha. Note que eu primeiro declaro uma variável do tipo Variant Dim varSheet As Variant eentão o procedimento pode rodar.
Sub proTest()
Dim varSheet As Variant
For Each varSheet In Worksheets Range("A1").Value = 22 Next
End Sub
Vejamos agora alguns EVENTOS do objeto Worksheet. A maioria dos eventos de um Workbook, como foi visto, afetam globalmente uma aplicação. Os eventos relacionados a um objeto Worksheet afetam apenas a planilha onde o evento ocorre.
Ocorre quando uma planilha é ativada. Por exemplo:
Private Sub Worksheet_Activate( )
MsgBox “A planilha “ & Me.Name & _ “ acaba de ser ativada.”,vbExclamation
End Sub
Observe o uso da cláusula Me. Ela representa o objeto que está em análise, nesdse caso, a planilha que sofre o evento. Através da cláusula Me, temos acesso a todas as propriedades e métodos da planilha. Evento BeforeDoubleClick
Acontece quando há um duplo clique na planilha, antes que a ação padrão seja executada. Esse evento possui dois argumentos: O argumento Cancel, já estudado em outros eventos, que proporciona a possibilidade de cancelá-lo. O outro parâmetro é Target, do tipo Range. Através desse argumento, podemos saber qual a célula recebeu o duplo clique e dar os devidos tratamentos ao evento. Vejamos um exemplo:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Resposta As Byte
MsgBox "A célula que recebeu o duplo clique é " & Target.Address Resposta = InputBox("Digite o número da cor para pintar a célula:")
Target.Interior.ColorIndex = Resposta End Sub
No exemplo anterior, trocamos a cor da célula que sofreu o evento de duplo clique e, através de cancel = true, evitamos que a ação padrão ocorra, a qual seria colocar a célula em modo de edição. Evento BeforeRightClick
Esse evento ocorre quando há um clique com o botão direito do mouse na planilha. Assim como o evento BeforeDoubleClick, o BeforeRightClick tem os dois argumentos, Target e Cancel, usados para detectar a célula clicada e cancelar o evento, respectivamente.
Vamos fazer um exemplo que troca o padrão de preenchimento de uma célula que recebe o clique com o botão direito do mouse, de acordo com a vontade do usuário.
Na figura ao lado vemos todos os valores possíveis para esses padrões
Nesta Sub apresentamos um detalhe interessante, o uso de On Error, que faz um tratamento caso algum erro ocorra. Em resposta ao erro, On Error faz um desvio da aplicação para a rotina Trat, que evita o erro e cancela o evento. Evento Calculate Ocorre toda vez que um cálculo é executado em uma planilha, alterando valores. Por exemplo, quando montamos uma fórmula e esta executa um cálculo, alteramos os valores das células que são referências para fórmulas (a planilha não está em módulo de cálculo automático) e pressionamos F9, fazendo o recálculo da planilha, entre outros. A seguir, temos um exemplo do uso do evento Calculate. Antes de implementar o código, crie a planilha que segue. Na coluna D, existe uma fórmula que calcula os valores de entrada, menos os valores de saída. Na célula D14, existe uma fórmula de soma das células que estão acima dela, perfazendo o saldo anual.
Lição #04 – Código VBA do Excel para Planilha (Worksheet) 136
Private Sub Worksheet_Calculate()
Dim Valor As Integer
Valor = Me.Range("d14").Value If Valor < 0 Then
MsgBox "Seu balanço anual está negativo", vbCritical
Else
MsgBox "Seu balanço anual está positivo", vbInformation End If
End Sub
Evento Activate
Este evento ocorre quando uma das células da planilha teve o seu conteúdo alterado pelo usuário ou por um agente externo. Seja o exemplo:
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Trat
MsgBox "A célula que teve o seu conteúdo alterado foi " & _ Target.Address, vbInformation
MsgBox "O novo valor de " & Target.Address & " é " & _ Target.Value, vbInformation
Exit Sub
Trat:
MsgBox "Você deve ter alterado mais de uma célula ao" & _ " mesmo tempo", vbCritical
End Sub
Perceba que, se você selecionar um conjunto de células e apagar o seu conteúdo, Target.Value retorna um erro, pois só funciona dessa forma quando temos apenas uma
célula no Range selecionado. Assim, somos desviados para a rotina de tratamento de erro, que informa o que aconteceu.
Evento Activate
É o evento oposto ao evento Activate. É acionado quando desativamos a planilha.
Evento SelectionChange
Este evento é o evento padrão de uma Worksheet. Ele ocorre toda vez que um Range de células dentro de uma planilha é selecionado. Através do parâmetro de retorno Target, temos acesso a todas as células selecionadas no intervalo e podemos dar o tratamento que desejamos. Seja o exemplo:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) MsgBox "O Range de células selecionado é " & Target.Address
If Target.Count = 1 Then
MsgBox "Nesse Range existe 1 célula" Else
MsgBox "Nesse Range existem " & Target.Count & " células"
End If
End Sub
Esses são os sete eventos da coleção Worksheets, que são os mais usados em uma pasta de trabalho. Lembremos que podemos estendê-los ao objeto Workbook de forma global, pois tal objeto possui os mesmos eventos detectados para qualquer planilha da aplicação.
138
Fase #2: Código VBA do Excel (Macros)
Lição 5: Código VBA do Excel para mover-se entre as Células e Ranges