Trabalho com planilhas usando Excel VBA (explicado com exemplos)

Além de células e intervalos, trabalhar com planilhas é outra área que você deve saber para usar o VBA com eficiência no Excel.

Assim como qualquer objeto no VBA, as planilhas têm diferentes propriedades e métodos associados que você pode usar enquanto automatiza seu trabalho com o VBA no Excel.

Neste tutorial, irei cobrir as ‘planilhas’ em detalhes e também mostrar alguns exemplos práticos.

Então vamos começar.

Todos os códigos que menciono neste tutorial precisam ser colocados no Editor VB. Vá para a seção ‘Onde colocar o código VBA’ para saber como funciona.

Se você estiver interessado em aprender VBA da maneira mais fácil, confira meu Treinamento Online Excel VBA.

Diferença entre planilhas e planilhas em VBA

No VBA, você tem duas coleções que podem ser um pouco confusas às vezes.

Em uma pasta de trabalho, você pode ter planilhas e também planilhas de gráfico. O exemplo abaixo possui três planilhas e uma planilha de gráfico.

No Excel VBA:

• A coleção ‘Planilhas’ se refere à coleção de todos os objetos da planilha em uma pasta de trabalho. No exemplo acima, a coleção de planilhas consistiria em três planilhas.
• A coleção de 'Planilhas' se refere a todas as planilhas, bem como planilhas de gráfico na pasta de trabalho. No exemplo acima, ele teria quatro elementos - 3 planilhas + 1 planilha de gráfico.

Se você tiver uma pasta de trabalho que contém apenas planilhas e nenhuma planilha de gráfico, a coleção ‘Planilhas’ e ‘Planilhas’ é a mesma.

Mas quando você tem uma ou mais planilhas de gráfico, a coleção de 'Planilhas' seria maior do que a coleção de 'Planilhas'

Planilhas = Planilhas + Planilhas Gráficas

Agora, com essa distinção, recomendo ser o mais específico possível ao escrever um código VBA.

Portanto, se você tiver que se referir apenas a planilhas, use a coleção ‘Planilhas’, e se tiver que se referir a todas as planilhas (incluindo planilhas de gráfico), use a coleção ‘Planilhas’.

Neste tutorial, usarei apenas a coleção ‘Planilhas’.

Referenciando uma planilha em VBA

Existem muitas maneiras diferentes de se referir a uma planilha no VBA.

Entender como consultar planilhas ajudaria você a escrever um código melhor, especialmente quando você estiver usando loops em seu código VBA.

Usando o nome da planilha

A maneira mais fácil de se referir a uma planilha é usar seu nome.

Por exemplo, suponha que você tenha uma pasta de trabalho com três planilhas - Folha 1, Folha 2, Folha 3.

E você deseja ativar a Planilha 2.

Você pode fazer isso usando o seguinte código: Sub ActivateSheet () Worksheets ("Sheet2"). Activate End Sub

O código acima pede ao VBA para consultar a Planilha2 na coleção Planilhas e ativá-la.

Como estamos usando o nome exato da planilha, você também pode usar a coleção Planilhas aqui. Portanto, o código abaixo também faria a mesma coisa.

Sub ActivateSheet () Sheets ("Sheet2"). Ativar End Sub

Usando o número de índice

Embora usar o nome da planilha seja uma maneira fácil de se referir a uma planilha, à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, você não saberia o nome da nova planilha.

Neste caso, você pode usar o número de índice das planilhas.

Suponha que você tenha as seguintes planilhas em uma pasta de trabalho:

O código a seguir ativaria a Planilha2:

Sub ActivateSheet () Planilhas (2) .Activate End Sub

Observe que usamos o índice número 2 em Planilhas (2). Isso se refere ao segundo objeto na coleção das planilhas.

Agora, o que acontece quando você usa 3 como o número do índice?

Ele selecionará a Planilha3.

Se você está se perguntando por que selecionou a Planilha3, já que é claramente o quarto objeto.

Isso acontece porque uma folha de gráfico não faz parte da coleção de planilhas.

Portanto, quando usamos os números de índice na coleção Planilhas, isso só se refere às planilhas na pasta de trabalho (e ignora as planilhas de gráfico).

Pelo contrário, se você estiver usando planilhas, as planilhas (1) se referem às planilhas 1, as planilhas (2) se referem à planilha 2, as planilhas (3) se referem à planilha 1 e as planilhas (4) se referem à planilha3.

Essa técnica de usar o número de índice é útil quando você 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 posteriormente neste tutorial).

Nota: o número do índice vai da esquerda para a direita. Portanto, se você deslocar a Planilha2 para a esquerda da Planilha1, as Planilhas (1) se referirão à Planilha2.

Usando o nome do código da planilha

Uma das desvantagens de usar o nome da planilha (como vimos na seção acima) é que o usuário pode alterá-lo.

E se o nome da planilha foi alterado, seu código não funcionaria até que você alterasse o nome da planilha no código VBA também.

Para resolver esse problema, você pode usar o nome do código da planilha (em vez do nome normal que temos usado até agora). Um nome de código pode ser atribuído no Editor VB e não muda quando você altera o nome da planilha na área da planilha.

Para dar à sua planilha um codinome, siga as etapas abaixo:

1. Clique na guia Desenvolvedor.
2. Clique no botão Visual Basic. Isso abrirá o Editor VB.
3. Clique na opção View no menu e clique em Project Window. Isso tornará o painel Propriedades visível. Se o painel Propriedades já estiver visível, pule esta etapa.
4. Clique no nome da folha no explorador de projetos que você deseja renomear.
5. No painel Propriedades, altere o nome no campo na frente de (Nome). Observe que você não pode ter espaços no nome.

As etapas acima mudariam o nome de sua planilha no back-end do VBA. Na visualização de planilha do Excel, você pode nomear a planilha como quiser, mas no back-end, ela responderá a ambos os nomes - o nome da planilha e o nome do código.

Na imagem acima, o nome da folha é ‘SheetName’ e o nome do código é ‘CodeName’. Mesmo se você alterar o nome da planilha na planilha, o nome do código ainda permanece o mesmo.

Agora, você pode usar a coleção Worksheets para se referir à planilha ou usar o codinome.

Por exemplo, ambas as linhas irão ativar a planilha.

Planilhas ("Nome da Planilha"). Ative o Nome do Código.Ativar

A diferença entre os dois é que, se você alterar o nome da planilha, o primeiro não funcionará. Mas a segunda linha continuaria funcionando mesmo com o nome alterado. A segunda linha (usando o CodeName) também é mais curta e fácil de usar.

Referindo-se a uma planilha em uma pasta de trabalho diferente

Se quiser fazer referência a uma planilha em uma pasta de trabalho diferente, essa pasta de trabalho precisa ser aberta enquanto o código é executado e você precisa especificar o nome da pasta de trabalho e da planilha que deseja consultar.

Por exemplo, se você tem uma pasta de trabalho com o nome de exemplos e deseja ativar a Planilha1 na pasta de trabalho de exemplo, você precisa usar o código a seguir:

Sub SheetActivate () Workbooks ("Examples.xlsx"). Worksheets ("Sheet1"). Ative End Sub

Observe que, se a pasta de trabalho foi salva, você precisa usar o nome do arquivo junto com a extensão. Se você não tiver certeza de qual nome usar, peça ajuda ao Project Explorer.

Caso a pasta de trabalho não tenha sido salva, você não precisa usar a extensão do arquivo.

Adicionando uma planilha

O código a seguir adicionaria uma planilha (como a primeira planilha - ou seja, como a planilha mais à esquerda na guia da planilha).

Sub AddSheet () Worksheets.Add End Sub

Recebe o nome padrão Folha2 (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 (por exemplo, Planilha 2), você pode usar o código a seguir.

Sub AddSheet () Worksheets.Add Before: = Worksheets ("Sheet2") End Sub

O código acima diz ao VBA para adicionar uma planilha e, em seguida, usa a instrução ‘Antes’ para especificar a planilha antes da qual a nova planilha deve ser inserida.

Da mesma forma, você também pode adicionar uma planilha após uma planilha (digamos, Planilha 2), usando o código a seguir:

Sub AddSheet () Worksheets.Add After: = Worksheets ("Sheet2") End Sub

Se você deseja que a nova folha seja adicionada ao final das folhas, primeiro você precisa saber quantas folhas existem. O código a seguir conta primeiro o número de folhas e adiciona a nova folha após a última folha (à qual nos referimos usando o número do índice).

Sub AddSheet () Dim SheetCount As Integer SheetCount = Worksheets.Count Worksheets.Add After: = Worksheets (SheetCount) End Sub

Excluindo uma planilha

O código a seguir excluiria a planilha ativa da pasta de trabalho.

Sub DeleteSheet () ActiveSheet.Delete End Sub

O código acima mostraria um prompt de aviso antes de excluir a planilha.

Se você não quiser ver o aviso de aviso, use o código a seguir:

Sub DeleteSheet () Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub

Quando Application.DisplayAlerts for definido como False, ele não mostrará o prompt de aviso. Se você usá-lo, lembre-se de defini-lo novamente como True no final do código.

Lembre-se de que você não pode desfazer essa exclusão, então use o código acima quando tiver certeza absoluta.

Se quiser excluir uma planilha específica, você pode fazer isso usando o seguinte código:

Sub DeleteSheet () Worksheets ("Sheet2"). Delete End Sub

Você também pode usar o nome do código da planilha para excluí-la.

Sub DeleteSheet () Sheet5.Delete End Sub

Renomeando as planilhas

Você pode modificar a propriedade do nome da Planilha para alterar seu nome.

O código a seguir mudará o nome da Planilha1 para 'Resumo'.

Sub RenameSheet () Worksheets ("Sheet1"). Name = "Summary" End Sub

Você pode combinar isso com o método de adição de folhas para ter um conjunto de folhas com nomes específicos.

Por exemplo, se você deseja inserir quatro planilhas com o nome 2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 e 2021-2022 Q4, você pode usar o código abaixo.

Sub RenameSheet () Dim Countsheets As Integer Countsheets = Worksheets.Count For i = 1 To 4 Worksheets.Add after: = Worksheets (Countsheets + i - 1) Worksheets (Countsheets + i) .Name = "2018 Q" & 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 folhas no final. Conforme a folha é adicionada, o código também a renomeia.

Atribuindo Objeto de Planilha a uma Variável

Ao trabalhar com planilhas, você pode 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ê deseja adicionar um prefixo de ano a todas as planilhas, em vez de contar as planilhas e executar o loop várias vezes, você pode usar a variável de objeto.

Aqui está o código que adicionará 2021-2022 como um prefixo para todos os nomes da planilha.

Sub RenameSheet () Dim Ws As Worksheet para cada Ws nas planilhas Ws.Name = "2018 -" & Ws.Name Next Ws End Sub

O código acima declara uma variável Ws como o tipo de planilha (usando a linha ‘Dim Ws As Planilha’).

Agora, não precisamos contar o número de folhas para percorrê-los. Em vez disso, podemos usar o loop ‘For each Ws in Worksheets’. Isso nos permitirá percorrer todas as planilhas da coleção de planilhas. Não importa se há 2 ou 20 folhas.

Embora o código acima nos permita percorrer todas as planilhas, você também pode atribuir uma planilha específica a uma variável.

No código a seguir, atribuímos a variável Ws à Planilha2 e a usamos para acessar todas as propriedades da Planilha2.

Sub RenameSheet () Dim Ws As Worksheet Set Ws = Worksheets ("Sheet2") Ws.Name = "Summary" Ws.Protect End Sub

Depois de definir uma referência de planilha para uma variável de objeto (usando a instrução SET), esse objeto pode ser usado em vez da referência de planilha. Isso pode ser útil quando você tem um código longo e complicado e deseja alterar a referência. Em vez de fazer a mudança em todos os lugares, você pode simplesmente fazer a mudança na instrução SET.

Observe que o código declara o objeto Ws como a variável do tipo Worksheet (usando a linha Dim Ws as Worksheet).

Ocultar planilhas usando VBA (Oculto + Muito Oculto)

Ocultar e exibir planilhas no Excel é uma tarefa simples.

Você pode ocultar uma planilha e o usuário não a verá ao abrir a planilha. No entanto, eles podem exibir facilmente a planilha clicando com o botão direito do mouse em qualquer guia da planilha.

Mas e se você não quiser que eles exibam a (s) planilha (s).

Você pode fazer isso usando o VBA.

O código a seguir ocultaria todas as planilhas na pasta de trabalho (exceto a planilha ativa), de forma que você não pudesse exibi-la clicando com o botão direito do mouse no nome da planilha.

Sub HideAllExcetActiveSheet () Dim Ws As Worksheet Para Cada Ws Neste Workbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetVeryHidden Next Ws End Sub

No código acima, a propriedade Ws.Visible foi alterada para xlSheetVeryHidden.

• Quando a propriedade Visible é definida como xlSheetVisible, a planilha fica visível na área da planilha (como guias da planilha).
• Quando a propriedade Visible é definida como xlSheetHidden, a planilha fica oculta, mas o usuário pode exibi-la clicando com o botão direito do mouse em qualquer guia da planilha.
• Quando a propriedade Visible é definida como xlSheetVeryHidden, a planilha fica oculta e não pode ser reexibida da área da planilha. Você precisa usar um código VBA ou a janela de propriedades para exibi-lo.

Se você deseja simplesmente ocultar as planilhas, que podem ser desocultadas facilmente, use o código a seguir:

Sub HideAllExceptActiveSheet () Dim Ws As Worksheet Para Cada Ws Neste Workbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetHidden Next Ws End Sub

O código a seguir mostraria todas as planilhas (ocultas e muito ocultas).

Sub UnhideAllWoksheets () Dim Ws As Planilha Para Cada Ws Neste Caderno.Worksheets Ws.Visible = xlSheetVisible Next Ws End Sub

Artigo Relacionado: Reexibir todas as planilhas no Excel (de uma só vez)

Ocultar folhas com base no texto nelas

Suponha que você tenha várias planilhas com o nome de departamentos ou anos diferentes e deseja ocultar todas as planilhas, exceto aquelas que contêm o ano de 2021-2022.

Você pode fazer isso usando uma função VBA INSTR.

O código a seguir ocultaria todas as folhas, exceto aquelas com o texto 2021-2022 nele.

Sub HideWithMatchingText () Dim Ws como planilha para cada Ws nas planilhas If InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Then Ws.Visible = xlSheetHidden End If Next Ws End Sub

No código acima, a função INSTR retorna a posição do caractere onde encontra a string correspondente. Se não encontrar a string correspondente, ele retornará 0.

O código acima verifica se o nome contém o texto 2021-2022. Se isso acontecer, nada acontecerá, caso contrário, a planilha ficará oculta.

Você pode dar um passo adiante, colocando o texto em uma célula e usando essa célula no código. Isso permitirá que você tenha um valor na célula e quando você executar a macro, todas as páginas, exceto aquela com o texto correspondente, permanecerão visíveis (junto com as páginas onde você está inserindo o valor no célula).

Classificando as planilhas em ordem alfabética

Usando o VBA, você pode classificar rapidamente as planilhas com base em seus nomes.

Por exemplo, se você tiver uma pasta de trabalho com planilhas para departamentos ou anos diferentes, poderá usar o código a seguir para classificar rapidamente essas planilhas em ordem crescente.

Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount 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

Observe que este código funciona bem com nomes de texto e, na maioria dos casos, com anos e números também. Mas pode dar resultados errados caso você tenha os nomes das planilhas como 1,2,11. Ele classificará e fornecerá a sequência 1, 11, 2. Isso ocorre porque ele faz a comparação como texto e considera 2 maior que 11.

Proteger / desproteger todas as planilhas de uma só vez

Se você tem muitas planilhas em uma pasta de trabalho e deseja proteger todas as planilhas, pode usar o código VBA abaixo.

Ele permite que você especifique a senha dentro do código. Você precisará dessa senha para desproteger a planilha.

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'substitua Test123 pela senha desejada Para Cada ws Nas planilhas ws.Protect senha: = senha Próximo ws End Sub

O código a seguir desprotegeria todas as folhas de uma vez.

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'substitua Test123 pela senha que você usou ao proteger For Each ws Em Worksheets senha ws.Unprotect: = senha Next ws End Sub

Criação de um índice de todas as planilhas (com hiperlinks)

Se você tem um conjunto de planilhas na pasta de trabalho e deseja inserir rapidamente uma planilha de resumo que contém os links para todas as planilhas, você pode usar o código a seguir.

Sub AddIndexSheet () Worksheets.Add ActiveSheet.Name = "Index" For i = 2 To Worksheets.Count ActiveSheet.Hyperlinks.Add Anchor: = Cells (i - 1, 1), _ Address: = "", SubAddress: = Worksheets (i) .Name & "! A1", _ TextToDisplay: = Planilhas (i) .Name Next i End Sub

O código acima insere uma nova planilha e a denomina Índice.

Em seguida, ele percorre todas as planilhas e cria um hiperlink para todas as planilhas na planilha de índice.

Onde colocar o código VBA

Quer saber para onde o código VBA vai em sua pasta de trabalho do Excel?

O Excel tem um back-end VBA chamado editor VBA. Você precisa copiar e colar o código na janela de código do módulo VB Editor.

Aqui estão as etapas para fazer isso:

1. Vá para a guia Desenvolvedor.
2. Clique na opção Visual Basic. Isso abrirá o editor VB no backend.
3. No painel Project Explorer no VB Editor, clique com o botão direito em qualquer objeto da pasta de trabalho na qual deseja inserir o código. Se você não vir o Project Explorer, vá para a guia View e clique em Project Explorer.
4. Vá para Inserir e clique em Módulo. Isso irá inserir um objeto de módulo para sua pasta de trabalho.
5. Copie e cole o código na janela do módulo.

Você também pode gostar dos seguintes tutoriais do Excel VBA:

• Trabalhando com pastas de trabalho usando VBA.
• Usando instruções IF Then Else em VBA.
• Para o próximo loop em VBA.
• Criando uma função definida pelo usuário no Excel.
• Como gravar uma macro no Excel.
• Como executar uma macro no Excel.
• Eventos do Excel VBA - um guia fácil (e completo).
• Como criar um suplemento no Excel.
• Como salvar e reutilizar macro usando a pasta de trabalho macro pessoal do Excel.