Como manipular planilhas do Excel com o VBA (com exemplos)
Quer aprender a manipular planilhas do Excel com o VBA? Então você está no lugar certo!
Além saber trabalhar com células e intervalos, trabalhar com planilhas é outra habilidade que você precisa aprender para usar o VBA de maneira eficiente no Excel.
Assim como qualquer objeto no VBA, as planilhas também possuem diferentes propriedades e métodos associados a elas que você pode usar ao automatizar seu trabalho.
Neste guia abordaremos em detalhes os principais pontos que você precisa conhecer para começar a manipular planilhas do Excel com o VBA. Confira!
Como manipular planilhas do Excel com o VBA na prática
Já está interessado(a) no que vem por aí? Então compartilhe com sua rede de amigos para que mais pessoas possam aprender a manipular planilhas do Excel com o VBA.
Diferença entre Sheets e Worksheets no VBA
O primeiro passo para saber manipular planilhas do Excel com o VBA é simplesmente compreender a diferença entre Sheets e Worksheets.
No Excel VBA, temos duas coleções que às vezes podem ser um pouco confusas. Em uma pasta de trabalho (Workbook) podemos ter planilhas (Worksheets) e planilhas de gráficos (Chart Sheets). No exemplo abaixo temos três planilhas e uma folha de gráfico
No Excel VBA:
- A coleção de ‘Worksheets‘ refere-se à coleção de todos os objetos da planilha em uma pasta de trabalho. No exemplo acima, a coleção Worksheets contém três planilhas.
- A coleção “Sheets” refere-se a todas as planilhas, bem como planilhas de gráficos em uma pasta de trabalho. No exemplo acima, a coleção Sheets contém – 3 planilhas + 1 folha de gráfico.
Se você tiver uma pasta de trabalho que tenha apenas planilhas e nenhuma planilha de gráfico, as coleções “Worsheets” e “Sheets” serão as mesmas.
Por outro lado, quando temos uma ou mais planilhas de gráfico, a coleção “Sheets” será maior que a coleção “Worksheets”.
Portanto, se você tiver que consultar apenas planilhas, use a coleção ‘Worksheets’ e, se precisar consultar todas as planilhas (incluindo planilhas de gráfico), utilize a coleção ‘Sheets’.
*Neste tutorial, usaremos apenas a coleção ‘Worksheets’.
Referenciando uma planilha no VBA
Existem diferentes maneiras de se referir a uma planilha no Excel VBA. Neste caso, compreender como se referir a planilhas poderá te ajudar a escrever um código melhor, especialmente quando estiver usando loops.
Veja abaixo 4 maneiras que você pode usar:
1. Usando o nome da planilha
Provavelmente a maneira mais fácil de se referir a uma planilha no Excel VBA é usando o seu nome.
Por exemplo, suponha que você tenha uma pasta de trabalho com três planilhas – Plan1, Plan2 e Plan3. E você quer ativar a Plan3. Neste caso, você pode simplesmente usar o seguinte código:
Sub AtivarPlanilha() Worksheets("Plan3").Activate End Sub
O código acima pede ao VBA para se referir a Plan3 na coleção Worksheets e ativá-la. Como estamos usando o nome exato da planilha, também é possível usar a coleção Sheets aqui. Então, o código abaixo também faria a mesma coisa.
Sub AtivarPlanilha() Sheets("Plan3").Activate End Sub
2. Usando o número do índice
Embora o uso do nome da planilha seja uma maneira fácil de se referir a ela, às vezes, você pode não saber o nome exato da planilha.
Por exemplo, se você estiver usando um código VBA para adicionar uma nova planilha à pasta de trabalho e não souber quantas planilhas já existem, não saberá o nome da nova planilha.
Nesse caso, você pode usar o número de índice das planilhas. Suponha-se, por exemplo, que você tenha as seguintes planilhas em uma pasta de trabalho:
O código abaixo ativaria a Plan2
Sub AtivarPlanilha() Worksheets(2).Activate End Sub
Note que foi usado o número de índice 2 em Worksheets(2), ou seja, o segundo objeto na coleção worksheets.
Mas e se usarmos o número 3 como índice? Ele irá selecionar a Plan3.
Você deve estar se perguntando por que o Excel selecionou a Plan3, já que ela representa o quarto objeto. Isso ocorre porque uma planilha de gráfico não faz parte da coleção Worksheets conforme mostrado anteriormente.
Portanto, quando usamos os números de índice na coleção Worksheets, ele irá se referir apenas às planilhas na pasta de trabalho (e ignorar as planilhas de gráfico).
Por outro lado, se você estiver usando Sheets:
- Sheets(1) irá se referir a Plan1
- Sheets(2) irá se referir a Plan2
- Sheets(3) irá se referir a Gráf1
- Sheets(4) irá se referir à Plan3
Essa técnica de usar o número de índice é útil quando se deseja percorrer todas as planilhas em uma pasta de trabalho. Você pode contar o número de planilhas e, em seguida, percorrê-las usando essa contagem (veremos como fazer isso mais adiante neste tutorial).
Importante: O número do índice vai da esquerda para a direita. Portanto, se você, por exemplo, mudar a Plan2 para a esquerda da Plan1, as Worksheets(1) passará a se referir à Plan2.
3. Criando um codinome para a planilha
Usar o nome da planilha para se referir a ela no VBA pode até ser a opção mais fácil, no entanto, uma desvantagem desse método está na possibilidade do usuário alterar o nome da planilha no Excel. E se o nome da planilha for alterado, seu código não funcionará até que você altere o nome da planilha no código VBA também.
Para solucionar esse problema, podemos usar um codinome para a planilha (em vez do nome comum que usamos anteriormente). Um codinome pode ser atribuído no Editor VBA e não é alterado quando o usuário altera o nome da planilha na sua pasta de trabalho.
Para criar um codinome à planilha, siga as etapas abaixo:
1. Acesse a guia Desenvolvedor.
2. Clique no botão Visual Basic. Isto irá abrir o Editor VB.
3. No menu superior, escolha a opção Exibir> Janela de Propriedades. Isso irá exibir o Painel de Propriedades. Se o painel Propriedades já estiver visível, ignore esta etapa.
4. Clique no nome da planilha que você deseja renomear e altere o nome no campo na frente de (Nome). Note que o nome não poderá ter espaços.
As etapas acima alteraram o nome da planilha no back-end do VBA. Na visualização da planilha do Excel, você pode nomear a planilha como desejar, mas, no back-end, ela responderá a ambos os nomes, ou seja, o nome da planilha e o nome do código.
Na imagem acima, o nome da planilha no Excel é ‘Plan1’ e no VBA nomeamos para ‘PlanilhaA’. Deste modo, mesmo se você alterar o nome da planilha no Excel, o codinome ainda permanecerá o mesmo.
Agora, você pode usar a coleção Worksheets para consultar a planilha ou usar o codinome.
Por exemplo:
'Exemplo 1
Worksheets("Plan1").Activate
'Exemplo 2
PlanilhaA.Activate
A grande diferença nos dois exemplos acima é que, se você alterar o nome da planilha, o primeiro não funcionará. Já no segundo exemplo, a linha continuará funcionando mesmo com o nome alterado. A segunda linha (usando o codinome PlanilhaA) também é mais curta e mais fácil de usar.
4. Referindo-se a uma planilha em uma pasta de trabalho diferente
No VBA, se você precisar se referir a uma planilha em uma pasta de trabalho diferente, essa pasta de trabalho precisa estar aberta enquanto o código é executado, além disso, também é necessário especificar o nome da pasta de trabalho e a planilha que deseja se referir.
Por exemplo, se você tiver uma pasta de trabalho com o nome ‘Vendas.xlsx’ e quiser ativar a Plan1 na pasta de trabalho de exemplo, será necessário usar o código abaixo:
Sub AtivarPlanilha()
Workbooks("Vendas.xlsx").Worksheets("Plan1").Activate
End Sub
Observe que, se a pasta de trabalho tiver sido salva, você precisará usar o nome do arquivo junto com a extensão. Se não tiver certeza de qual nome usar, peça ajuda ao Project Explorer do VBA.
Caso a pasta de trabalho não tenha sido salva, você não precisará usar a extensão de arquivo.
Adicionando uma Planilha com o VBA
Outra habilidade que você precisa dominar para saber manipular planilhas do Excel com o VBA é simplesmente aprender a adicionar uma planilha.
Observe o código abaixo. Ele adiciona uma planilha (posicionando-a como a primeira planilha – ou seja, como a folha mais à esquerda na guia de planilha).
Sub AddPlanilha()
Worksheets.Add
End Sub
Ele pega o nome padrão Plan2 (ou qualquer outro número baseado em quantas folhas já existem).
Se você quiser que uma planilha seja adicionada antes de uma planilha específica (digamos, Plan3), então você pode usar o código abaixo.
Sub AddPlanilha()
Worksheets.Add Before:=Worksheets("Plan3")
End Sub
O código acima informa ao Excel para adicionar uma planilha e, em seguida, usa a instrução ‘Before‘ para especificar a planilha antes da qual a nova planilha deve ser inserida.
Do mesmo modo, também é possível adicionar uma planilha após uma determinada planilha (digamos Plan3), usando o código abaixo:
Sub AddPlanilha()
Worksheets.Add After:=Worksheets("Plan3")
End Sub
Se você pretende adicionar uma nova planilha ao final das planilhas, primeiro você precisa saber quantas planilhas existem. O código a seguir conta primeiro o número de planilhas existentes e adiciona a nova folha após a última folha (à qual nos referimos usando o número do índice).
Sub AddPlanilha() Dim ContarPlanilhas As Integer ContarPlanilhas = Worksheets.Count Worksheets.Add After:=Worksheets(ContarPlanilhas) End Sub
Deletando Planilhas com o VBA
Manipular planilhas do Excel com o VBA também envolve aprender a excluí-las através de linhas de código.
Observe o código abaixo onde a planilha ativa é deletada da nossa pasta de trabalho.
Sub ExcluirPlanilha()
ActiveSheet.Delete
End Sub
O código acima mostrará uma mensagem de aviso antes de excluir a planilha.
Por outro lado, se você não pretende ver o aviso, basta usar o código abaixo:
Sub ExcluirPlanilha()
Application.DisplayAlerts = False
ActiveSheet.Delete
ActiveWindow.SelectedSheets.Delete
End Sub
Ao definir Application.DisplayAlerts como False, o Excel não exibirá a tela de aviso. No entanto, se você usá-lo posteriormente em seu projeto, lembre-se de configurá-lo de volta para True no final do código.
Importante: Você não poderá desfazer essa exclusão, portanto, use o código acima apenas quando tiver certeza.
Caso queira excluir uma planilha específica, então poderá fazer isso usando o seguinte código:
Sub ExcluirPlanilha()
Worksheets("Plan3").Delete
End Sub
Ou simplesmente:
Sub ExcluirPlanilha()
Plan3.Delete
End Sub
Renomeando as planilhas com o VBA
Outra habilidade importante para se manipular planilhas do Excel com o VBA é saber renomear as planilhas. Neste caso, você pode modificar a propriedade Name da planilha para alterar seu nome.
O código a seguir mudará o nome de Plan1 para ‘Vendas’.
Sub RenomearPlanilha()
Worksheets("Plan1").Name = "Vendas"
End Sub
Por outro lado, se você pretende adicionar uma planilha com um nome específico, então basta combinar o código acima com o método mostrado anteriormente para se adicionar planilhas.
Por exemplo, se você pretende inserir quatro planilhas com o nome 2018 Trimestre1, 2018 Trimestre2, 2018 Trimestre3 e 2018 Trimestre4, basta usar o código abaixo:
Sub AddPlanilhas()
Dim ContarPlanilhas As Integer
ContarPlanilhas = Worksheets.Count
For i = 1 To 4
Worksheets.Add after:=Worksheets(ContarPlanilhas + i - 1)
Worksheets(ContarPlanilhas + i).Name = "2018 Trimestre" & i
Next i
End Sub
No código acima, primeiro contamos o número de folhas e, em seguida, usamos um loop For Next para inserir novas planilhas no final. À medida que a planilha é adicionada, o código também a renomeia.
Atribuir Objeto de Planilha a uma Variável
Ao manipular planilhas do Excel com o VBA, é possível atribuir uma planilha a uma variável de objeto e, em seguida, usar a variável em vez das referências da planilha.
Por exemplo, se você pretende adicionar um ano em todas as planilhas, em vez de contar as planilhas e executar o loop com muitos números de tempo, você poderá usar a variável de objeto.
Observe o código abaixo onde é adicionado ‘2018’ como um prefixo em todos os nomes das planilhas
Sub RenomearPlanilha()
Dim Plan As Worksheet
For Each Plan In Worksheets
Plan.Name = "2018 - " & Plan.Name
Next Plan
End Sub
O código acima declara uma variável Plan como o tipo de planilha (usando a linha ‘Dim Plan As Worksheet‘).
Com isso, não é necessário contar o número de planilhas para passar por elas. Em vez disso, podemos usar o loop ‘For each Plan in Worksheets‘. Isso irá permitir percorrer todas as planilhas da Pasta de Trabalho, não importando a quantidade (seja, por exemplo, 2 planilhas ou 20 planilhas).
Enquanto o código acima permitir percorrer todas as planilhas, você também pode atribuir uma planilha específica a uma variável.
No código abaixo, atribuímos a variável Plan à planilha Plan2 e a usamos para acessar todas as propriedades da Plan2.
Sub RenomearPlanilha() Dim Plan As Worksheet Set Plan = Worksheets("Plan2") Plan.Name = "Vendas" Plan.Protect End Sub
Após definir uma referência de planilha para uma variável de objeto (usando a instrução Set), esse objeto poderá ser usado em vez da referência da planilha. Isso pode ser útil quando se tem um código longo e deseja alterar a referência. Em vez de fazer a alteração em todos os lugares, você pode simplesmente fazer a alteração na instrução SET.
Observe que o código declara o objeto Plan como a variável de tipo de planilha (usando a linha Dim Plan as Worksheet).
Ocultando planilhas com o VBA
Se você pretende aprender a manipular planilhas do Excel com o VBA, então deve aprender também a ocultar as planilhas por meio de linhas de código.
No Excel, ocultar e exibir planilhas é uma tarefa simples. Você pode ocultar uma planilha e o usuário não a veria ao abrir a pasta de trabalho. Contudo, é possível exibir facilmente a planilha clicando com o botão direito do mouse em qualquer guia da planilha e escolher a opção ‘Re-exibir…‘.
Mas se a sua intensão é evitar que o usuário seja capaz de re-exibir a(s) planilha(s), então o VBA poderá fazer isso por você.
O código abaixo oculta todas as planilhas na pasta de trabalho (exceto a planilha ativa), de tal forma que não se pode reexibi-las clicando com o botão direito do mouse no nome da planilha.
Sub OcultarTodasPlanilhas()
Dim Plan As Worksheet
For Each Plan In ThisWorkbook.Worksheets
If Plan.Name <> ActiveSheet.Name Then Plan.Visible = xlSheetVeryHidden
Next Plan
End Sub
Observe que no código acima, a propriedade Plan.Visible é alterada para xlSheetVeryHidden .
- Se a propriedade Visible é definida como xlSheetVisible, a planilha fica visível na área da planilha (como guias da planilha).
- Se a propriedade Visible estiver definida como xlSheetHidden, a planilha ficará oculta, mas o usuário poderá mostrá-la clicando com o botão direito do mouse em qualquer guia da planilha.
- Se a propriedade Visible é definida como xlSheetVeryHidden, a planilha fica oculta e não pode ser reexibida na área da planilha. Você precisa usar um código VBA ou a janela de propriedades do VBA para reexibir.
Se você quiser simplesmente ocultar planilhas de modo que possam ser facilmente exibidas novamente, use o código abaixo:
Sub OcultarTodasPlanilhas()
Dim Plan As Worksheet
For Each Plan In ThisWorkbook.Worksheets
If Plan.Name <> ActiveSheet.Name Then Plan.Visible = xlSheetHidden
Next Plan
End Sub
O código abaixo exibirá todas as planilhas (ocultas e super ocultas).
Sub ReexibirPlanilhas()
Dim Plan As Worksheet
For Each Plan In ThisWorkbook.Worksheets
Plan.Visible = xlSheetVisible
Next Plan
End Sub
Classificando as planilhas em ordem alfabética
Outra dica importante para manipular planilhas do Excel com o VBA é saber classifica-las em ordem alfabética.
Por exemplo, se você tiver uma pasta de trabalho com planilhas com nomes ou anos diferentes, poderá usar o código abaixo para classificá-las rapidamente em ordem crescente (A – Z).
Sub OrdenarPlanilhas()
Application.ScreenUpdating = False
Dim ContarPlanilhas As Integer, i As Integer, j As Integer
ContarPlanilhas = Sheets.Count
For i = 1 To ContarPlanilhas - 1
For j = i + 1 To ContarPlanilhas
If Sheets(j).Name < Sheets(i).Name Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub
De maneira geral, o código acima poderá funcionar muito bem com nomes de texto e na maioria dos casos com anos e números. No entanto, pode apresentar resultados incorretos caso você tenha os nomes das planilhas como 1,2,11. Neste caso, ele classificará e dará a seqüência 1, 11, 2. Isso porque faz a comparação como texto e considera 2 maior que 11.
Proteger e desproteger todas as planilhas (de uma só vez)
Manipular planilhas do Excel com o VBA também envolve saber habilitar e desabilitar a proteção delas.
Se você tiver muitas planilhas em uma pasta de trabalho e quiser proteger todas elas de modo a poupar tempo, então poderá usar o código VBA abaixo:
Sub ProtegerPlanilhas()
Dim Plan As Worksheet
Dim senha As String
senha = "123456" 'substitua 123456 pela senha que desejar.
For Each Plan In Worksheets
Plan.Protect password:=senha
Next Plan
End Sub
Observe o código acima que a senha de proteção está sendo especificada dentro dentro do código. Essa senha será necessária para desproteger a planilha posteriormente.
Já o código abaixo pode ser usado para desproteger todas as planilhas de uma só vez.
Sub DesprotegerPlanilhas()
Dim Plan As Worksheet
Dim senha As String
senha = "123456" 'substitua 123456 pela senha usada para proteger as planilhas
For Each Plan In Worksheets
Plan.Unprotect password:=senha
Next Plan
End Sub
Onde inserir o código VBA?
Se você chegou até aqui e não faz ideia de onde inserir os códigos citados nos exemplos acima, então basta acompanhar as etapas abaixo:
1. Acesse a guia Desenvolvedor (caso não tenha essa guia habilitada em seu Excel, confira aqui os passos para habilitá-la).
2. Clique no ícone Visual Basic. Isso irá abrir o editor VB.
3. No painel Project Explorer no VB, clique com o botão direito do mouse em qualquer objeto para a pasta de trabalho na qual você deseja inserir o código. *Caso não consiga visualizar a aba Project Explorer, acesse a guia Exibir e clique em Project Explorer (atalho CTRL + R).
4. Escolha Inserir e clique em Módulo. Isso irá inserir um objeto de módulo em sua pasta de trabalho.
5. Copie e cole o código na janela do módulo e pronto! Basta executá-los.
Leia também: 7 códigos de VBA no Excel que farão você se sentir mais inteligente
E então, o que achou do guia para manipular planilhas do Excel no VBA? Deixe o seu comentário abaixo, gostaríamos muito de conhecer a sua opinião! Para conferir mais dicas de Excel, continue acompanhando o Excel Easy!