Ao trabalhar com o Excel, a maior parte do tempo é gasto na área da planilha - lidando com células e intervalos.
E se você deseja automatizar seu trabalho no Excel usando VBA, você precisa saber como trabalhar com células e intervalos usando VBA.
Existem muitas coisas diferentes que você pode fazer com intervalos no VBA (como selecionar, copiar, mover, editar, etc.).
Portanto, para cobrir este tópico, dividirei este tutorial em seções e mostrarei como trabalhar com células e intervalos no Excel VBA usando exemplos.
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.
Selecionando uma célula / intervalo no Excel usando VBA
Para trabalhar com células e intervalos no Excel usando VBA, você não precisa selecioná-lo.
Na maioria dos casos, é melhor não selecionar células ou intervalos (como veremos).
Apesar disso, é importante que você leia esta seção e entenda como funciona. Isso será crucial para o aprendizado do VBA e muitos dos conceitos abordados aqui serão usados ao longo deste tutorial.
Então, vamos começar com um exemplo muito simples.
Selecionando uma única célula usando VBA
Se você deseja selecionar uma única célula na planilha ativa (digamos A1), você pode usar o código a seguir:
Intervalo Sub SelectCell () ("A1"). Selecione End Sub
O código acima tem as partes obrigatórias ‘Sub’ e ‘End Sub’, e uma linha de código que seleciona a célula A1.
Intervalo (“A1”) informa ao VBA o endereço da célula à qual queremos nos referir.
Selecione é um método do objeto Range e seleciona as células / intervalo especificado no objeto Range. As referências de células devem ser colocadas entre aspas duplas.
Este código mostraria um erro no caso de uma planilha de gráfico ser uma planilha ativa. Uma folha de gráfico contém gráficos e não é amplamente utilizada. Como não contém células / intervalos, o código acima não pode selecioná-lo e acabaria exibindo um erro.
Observe que, como você deseja selecionar a célula na planilha ativa, você só precisa especificar o endereço da célula.
Mas se você quiser selecionar a célula em outra planilha (digamos, Planilha2), você precisa primeiro ativar a Planilha2 e, em seguida, selecionar a célula nela.
Sub SelectCell () Worksheets ("Sheet2"). Ative Range ("A1"). Select End Sub
Da mesma forma, você também pode ativar uma pasta de trabalho, ativar uma planilha específica nela e selecionar uma célula.
Sub SelectCell () Workbooks ("Book2.xlsx"). Worksheets ("Sheet2"). Ative Range ("A1"). Selecione End Sub
Observe que, ao consultar pastas de trabalho, você precisa usar o nome completo junto com a extensão do arquivo (.xlsx no código acima). Caso a pasta de trabalho nunca tenha sido salva, você não precisa usar a extensão do arquivo.
Agora, esses exemplos não são muito úteis, mas você verá posteriormente neste tutorial como podemos usar os mesmos conceitos para copiar e colar células no Excel (usando VBA).
Assim como selecionamos uma célula, também podemos selecionar um intervalo.
No caso de um intervalo, pode ser um intervalo de tamanho fixo ou um intervalo de tamanho variável.
Em um intervalo de tamanho fixo, você saberia o quão grande é o intervalo e pode usar o tamanho exato em seu código VBA. Mas com um intervalo de tamanho variável, você não tem ideia de quão grande é o intervalo e você precisa usar um pouco da magia do VBA.
Vamos ver como fazer isso.
Selecionando um intervalo de tamanho fixo
Aqui está o código que selecionará o intervalo A1: D20.
Sub SelectRange () Range ("A1: D20"). Selecione End Sub
Outra maneira de fazer isso é usando o código abaixo:
Sub SelectRange () Range ("A1", "D20"). Selecione End Sub
O código acima pega o endereço da célula superior esquerda (A1) e o endereço da célula inferior direita (D20) e seleciona todo o intervalo. Essa técnica se torna útil quando você está trabalhando com intervalos de tamanhos variáveis (como veremos quando a propriedade End for abordada posteriormente neste tutorial).
Se você quiser que a seleção aconteça em uma pasta de trabalho diferente ou em uma planilha diferente, será necessário informar ao VBA os nomes exatos desses objetos.
Por exemplo, o código a seguir selecionaria o intervalo A1: D20 na planilha Sheet2 na pasta de trabalho Book2.
Sub SelectRange () Workbooks ("Book2.xlsx"). Worksheets ("Sheet1"). Ative Range ("A1: D20"). Selecione End Sub
Agora, e se você não souber quantas linhas existem. E se você quiser selecionar todas as células que possuem um valor?
Nesses casos, você precisa usar os métodos mostrados na próxima seção (na seleção de uma faixa de tamanho variável).
Selecionando um intervalo de tamanho variável
Existem diferentes maneiras de selecionar um intervalo de células. O método que você escolher depende de como os dados são estruturados.
Nesta seção, abordarei algumas técnicas úteis que são realmente úteis quando você trabalha com intervalos no VBA.
Selecione usando a propriedade CurrentRange
Nos casos em que você não sabe quantas linhas / colunas contêm os dados, você pode usar a propriedade CurrentRange do objeto Range.
A propriedade CurrentRange cobre todas as células preenchidas contíguas em um intervalo de dados.
Abaixo está o código que selecionará a região atual que contém a célula A1.
Sub SelectCurrentRegion () Intervalo ("A1"). CurrentRegion.Select End Sub
O método acima é bom quando você tem todos os dados como uma tabela sem nenhuma linha / coluna em branco.
Mas no caso de você ter linhas / colunas em branco em seus dados, ele não selecionará aquelas após as linhas / colunas em branco. Na imagem abaixo, o código CurrentRegion seleciona dados até a linha 10, pois a linha 11 está em branco.
Nesses casos, você pode querer usar a propriedade UsedRange do objeto Worksheet.
Selecione usando a propriedade UsedRange
UsedRange permite que você se refira a quaisquer células que foram alteradas.
Portanto, o código abaixo selecionaria todas as células usadas na planilha ativa.
Sub SelectUsedRegion () ActiveSheet.UsedRange.Select End Sub
Observe que no caso de você ter uma célula distante que foi usada, ela seria considerada pelo código acima e todas as células até aquela célula usada seriam selecionadas.
Selecione usando a propriedade final
Agora, esta parte é muito útil.
A propriedade End permite que você selecione a última célula preenchida. Isso permite que você imite o efeito das teclas de seta Control Down / Up ou Control Right / Left.
Vamos tentar entender isso usando um exemplo.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja selecionar rapidamente as últimas células preenchidas na coluna A.
O problema aqui é que os dados podem mudar e você não sabe quantas células são preenchidas. Se você tiver que fazer isso usando o teclado, pode selecionar a célula A1 e, em seguida, usar Control + tecla de seta para baixo, e isso selecionará a última célula preenchida na coluna.
Agora vamos ver como fazer isso usando o VBA. Esta técnica é útil quando você deseja pular rapidamente para a última célula preenchida em uma coluna de tamanho variável
Sub GoToLastFilledCell () Range ("A1"). End (xlDown) .Selecione End Sub
O código acima pularia para a última célula preenchida na coluna A.
Da mesma forma, você pode usar End (xlToRight) para pular para a última célula preenchida em uma linha.
Sub GoToLastFilledCell () Range ("A1"). End (xlToRight) .Select End Sub
Agora, e se você quiser selecionar a coluna inteira em vez de pular para a última célula preenchida.
Você pode fazer isso usando o código abaixo:
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown)). Selecione End Sub
No código acima, usamos a primeira e a última referência da célula que precisamos selecionar. Não importa quantas células preenchidas existam, o código acima selecionará todas.
Lembre-se do exemplo acima, onde selecionamos o intervalo A1: D20 usando a seguinte linha de código:
Intervalo (“A1 ″,” D20 ”)
Aqui A1 era a célula superior esquerda e D20 era a célula inferior direita no intervalo. Podemos usar a mesma lógica ao selecionar intervalos de tamanhos variáveis. Mas como não sabemos o endereço exato da célula inferior direita, usamos a propriedade End para obtê-lo.
Em Intervalo (“A1”, Intervalo (“A1”). Fim (xlDown)), “A1” se refere à primeira célula e Intervalo (“A1”). Fim (xlDown) se refere à última célula. Como fornecemos ambas as referências, o método Select seleciona todas as células entre essas duas referências.
Da mesma forma, você também pode selecionar um conjunto de dados inteiro com várias linhas e colunas.
O código a seguir selecionaria todas as linhas / colunas preenchidas a partir da célula A1.
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown) .End (xlToRight)). Selecione End Sub
No código acima, usamos Range (“A1”). End (xlDown) .End (xlToRight) para obter a referência da célula preenchida no canto inferior direito do conjunto de dados.
Diferença entre usar CurrentRegion e End
Se você está se perguntando por que usar a propriedade End para selecionar o intervalo preenchido quando temos a propriedade CurrentRegion, deixe-me dizer a diferença.
Com a propriedade End, você pode especificar a célula inicial. Por exemplo, se você tem seus dados em A1: D20, mas a primeira linha são cabeçalhos, você pode usar a propriedade End para selecionar os dados sem os cabeçalhos (usando o código abaixo).
Sub SelectFilledCells () Range ("A2", Range ("A2"). End (xlDown) .End (xlToRight)). Selecione End Sub
Mas CurrentRegion selecionaria automaticamente todo o conjunto de dados, incluindo os cabeçalhos.
Até agora neste tutorial, vimos como nos referir a uma gama de células usando diferentes maneiras.
Agora vamos ver algumas maneiras em que podemos realmente usar essas técnicas para realizar algum trabalho.
Copiar células / intervalos usando VBA
Como mencionei no início deste tutorial, selecionar uma célula não é necessário para realizar ações nela. Você verá nesta seção como copiar células e intervalos sem nem mesmo selecioná-los.
Vamos começar com um exemplo simples.
Copiando uma única célula
Se você deseja copiar a célula A1 e colá-la na célula D1, o código abaixo fará isso.
Sub CopyCell () Range ("A1"). Copy Range ("D1") End Sub
Observe que o método de cópia do objeto de intervalo copia a célula (assim como Control + C) e a cola no destino especificado.
No código de exemplo acima, o destino é especificado na mesma linha em que você usa o método Copy. Se quiser tornar seu código ainda mais legível, você pode usar o código a seguir:
Sub CopyCell () Range ("A1"). Destino da cópia: = Range ("D1") End Sub
Os códigos acima irão copiar e colar o valor, bem como a formatação / fórmulas nele.
Como você já deve ter notado, o código acima copia a célula sem selecioná-la. Não importa onde você esteja na planilha, o código irá copiar a célula A1 e colá-la em D1.
Além disso, observe que o código acima sobrescreveria qualquer código existente na célula D2. Se você deseja que o Excel informe se já existe algo na célula D1 sem sobrescrevê-lo, você pode usar o código a seguir.
Sub CopyCell () If Range ("D1") "" Then Response = MsgBox ("Deseja sobrescrever os dados existentes", vbYesNo) End If If Response = vbSim Then Range ("A1"). Copy Range ("D1 ") End If End Sub
Copiando um intervalo de tamanho fixo
Se você deseja copiar A1: D20 em J1: M20, você pode usar o código abaixo:
Sub CopyRange () Range ("A1: D20"). Copy Range ("J1") End Sub
Na célula de destino, você só precisa especificar o endereço da célula superior esquerda. O código copiaria automaticamente o intervalo copiado exato para o destino.
Você pode usar a mesma construção para copiar dados de uma folha para outra.
O código a seguir copiaria A1: D20 da planilha ativa para a Planilha2.
Sub CopyRange () Range ("A1: D20"). Copiar planilhas ("Sheet2"). Range ("A1") End Sub
O acima copia os dados da planilha ativa. Portanto, certifique-se de que a planilha que contém os dados seja a planilha ativa antes de executar o código. Por segurança, você também pode especificar o nome da planilha enquanto copia os dados.
Sub CopyRange () Worksheets ("Sheet1"). Range ("A1: D20"). Copy Worksheets ("Sheet2"). Range ("A1") End Sub
A vantagem do código acima é que, independentemente da planilha ativa, ele sempre copiará os dados da Plan1 e os colará na Plan2.
Você também pode copiar um intervalo nomeado usando seu nome em vez da referência.
Por exemplo, se você tiver um intervalo nomeado chamado ‘SalesData’, você pode usar o código abaixo para copiar esses dados para a Planilha2.
Sub CopyRange () Range ("SalesData"). Copiar planilhas ("Sheet2"). Range ("A1") End Sub
Se o escopo do intervalo nomeado for a pasta de trabalho inteira, você não precisa estar na planilha que contém o intervalo nomeado para executar este código. Como o intervalo nomeado tem como escopo a pasta de trabalho, você pode acessá-lo de qualquer planilha usando este código.
Se você tiver uma tabela com o nome Tabela1, poderá usar o código a seguir para copiá-la para a Planilha2.
Sub CopyTable () Range ("Table1 [#All]"). Copiar planilhas ("Sheet2"). Range ("A1") End Sub
Você também pode copiar um intervalo para outra pasta de trabalho.
No exemplo a seguir, copio a tabela do Excel (Tabela1) para a pasta de trabalho Book2.
Sub CopyCurrentRegion () Range ("Table1 [#All]"). Copiar Workbooks ("Book2.xlsx"). Worksheets ("Sheet1"). Range ("A1") End Sub
Este código funcionaria apenas se a pasta de trabalho já estivesse aberta.
Copiando um intervalo de tamanho variável
Uma maneira de copiar intervalos de tamanhos variáveis é convertê-los em intervalos nomeados ou Tabela do Excel e usar os códigos conforme mostrado na seção anterior.
Mas se você não pode fazer isso, você pode usar a propriedade CurrentRegion ou End do objeto range.
O código abaixo copiaria a região atual na planilha ativa e colaria na Planilha2.
Sub CopyCurrentRegion () Range ("A1"). CurrentRegion.Copy Worksheets ("Sheet2"). Range ("A1") End Sub
Se você deseja copiar a primeira coluna do seu conjunto de dados até a última célula preenchida e colá-la na Planilha 2, você pode usar o código a seguir:
Sub CopyCurrentRegion () Range ("A1", Range ("A1"). End (xlDown)). Copiar planilhas ("Planilha2"). Range ("A1") End Sub
Se você deseja copiar as linhas, bem como as colunas, você pode usar o código a seguir:
Sub CopyCurrentRegion () Range ("A1", Range ("A1"). End (xlDown) .End (xlToRight)). Copiar planilhas ("Sheet2"). Range ("A1") End Sub
Observe que todos esses códigos não selecionam as células enquanto são executados. Em geral, você encontrará apenas alguns casos em que realmente precisa selecionar uma célula / intervalo antes de trabalhar nisso.
Atribuição de intervalos a variáveis de objeto
Até agora, estamos usando o endereço completo das células (como Pastas de Trabalho (“Livro2.xlsx”). Planilhas (“Folha1”). Faixa (“A1”)).
Para tornar seu código mais gerenciável, você pode atribuir esses intervalos a variáveis de objeto e, em seguida, usar essas variáveis.
Por exemplo, no código a seguir, atribuí o intervalo de origem e de destino às variáveis de objeto e, em seguida, usei essas variáveis para copiar dados de um intervalo para o outro.
Sub CopyRange () Dim SourceRange como intervalo Dim DestinationRange como intervalo Definir SourceRange = Worksheets ("Sheet1"). Range ("A1: D20") Set DestinationRange = Worksheets ("Sheet2"). Range ("A1") SourceRange.Copy DestinationRange End Sub
Começamos declarando as variáveis como objetos Range. Em seguida, atribuímos o intervalo a essas variáveis usando a instrução Set. Uma vez que o intervalo foi atribuído à variável, você pode simplesmente usar a variável.
Insira os dados na próxima célula vazia (usando a caixa de entrada)
Você pode usar as caixas de entrada para permitir que o usuário insira os dados.
Por exemplo, suponha que você tenha o conjunto de dados abaixo e deseja inserir o registro de vendas, você pode usar a caixa de entrada no VBA. Usando um código, podemos ter certeza de que ele preenche os dados na próxima linha em branco.
Sub EnterData () Dim RefRange As Range Definir RefRange = Range ("A1"). End (xlDown) .Offset (1, 0) Set ProductCategory = RefRange.Offset (0, 1) Set Quantity = RefRange.Offset (0, 2 ) Defina Amount = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Value + 1 ProductCategory.Value = InputBox ("Product Category") Quantity.Value = InputBox ("Quantity") Amount.Value = InputBox ("Amount") End Sub
O código acima usa a caixa de entrada do VBA para obter as entradas do usuário e, em seguida, insere as entradas nas células especificadas.
Observe que não usamos referências de células exatas. Em vez disso, usamos as propriedades End e Offset para encontrar a última célula vazia e preencher os dados nela.
Este código está longe de ser utilizável. Por exemplo, se você inserir uma sequência de texto quando a caixa de entrada solicitar a quantidade ou o valor, notará que o Excel permite. Você pode usar uma condição If para verificar se o valor é numérico ou não e, em seguida, permiti-lo de acordo.
Looping através de células / intervalos
Até agora, podemos ver como selecionar, copiar e inserir os dados em células e intervalos.
Nesta seção, veremos como percorrer um conjunto de células / linhas / colunas em um intervalo. Isso pode ser útil quando você deseja analisar cada célula e executar alguma ação com base nela.
Por exemplo, se você quiser destacar cada terceira linha na seleção, precisará fazer um loop e verificar o número da linha. Da mesma forma, se você deseja destacar todas as células negativas alterando a cor da fonte para vermelho, é necessário percorrer e analisar o valor de cada célula.
Aqui está o código que percorrerá as linhas nas células selecionadas e destacará as linhas alternativas.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Seleção para cada Myrow em Myrange.Rows If Myrow.Row Mod 2 = 0 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
O código acima usa a função MOD para verificar o número da linha na seleção. Se o número da linha for par, ele será destacado na cor ciano.
Aqui está outro exemplo em que o código passa por cada célula e destaca as células que possuem um valor negativo.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Mycell As Range Set Myrange = Seleção Para Cada Mycell Em Myrange Se Mycell <0 Then Mycell.Interior.Color = vbRed End If Next Mycell End Sub
Observe que você pode fazer a mesma coisa usando a Formatação Condicional (que é dinâmica e é a melhor maneira de fazer isso). Este exemplo serve apenas para mostrar como o loop funciona com células e intervalos no VBA.
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 do Editor VB.
Aqui estão as etapas para fazer isso:
- Vá para a guia Desenvolvedor.
- Clique na opção Visual Basic. Isso abrirá o editor VB no backend.
- 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.
- Vá para Inserir e clique em Módulo. Isso irá inserir um objeto de módulo para sua pasta de trabalho.
- Copie e cole o código na janela do módulo.
