Trabalhando com células e intervalos no Excel VBA (selecionar, Copiar, Mover, editar)

seleccionando uma célula / gama no Excel usando VBA

para trabalhar com células e gamas no Excel usando VBA, não é necessário seleccioná-la.

na maioria dos casos, é melhor não selecionar células ou intervalos (como veremos).,apesar disso, é importante que passe por esta secção e compreenda como funciona. Isto será crucial na sua aprendizagem VBA e muitos dos conceitos aqui abordados serão usados ao longo deste tutorial.

então vamos começar com um exemplo muito simples.

Selecionando uma Única Célula Usando o VBA

Se você deseja selecionar uma única célula na planilha ativa (digamos A1) e, em seguida, você pode usar o código abaixo:

Sub SelectCell()Range("A1").SelectEnd Sub

O código acima tem a obrigatoriedade de ” Sub ” e “End Sub “parte”, e uma linha de código que seleciona a célula A1.,

Range(“A1”) diz à VBA o endereço da célula a que queremos referir.

selecionar é um método do objeto Range e seleciona as células / range especificadas no objeto Range. As referências das células precisam ser incluídas entre aspas.

este código mostraria um erro no caso de uma folha de gráfico ser uma folha activa. Uma folha de gráfico contém gráficos e não é amplamente utilizado. Uma vez que não tem células/intervalos nele, o código acima não pode selecioná-lo e acabaria por mostrar um erro.

Note que, dado que deseja seleccionar a célula na folha activa, só precisa de indicar o endereço da célula.,

mas se você quiser selecionar a célula em outra folha( digamos Sheet2), você precisa primeiro ativar Sheet2 e, em seguida, selecionar a célula nela.

Sub SelectCell()Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub

da mesma forma, você também pode ativar uma pasta de trabalho e, em seguida, ativar uma planilha específica e, em seguida, selecione uma célula.

Sub SelectCell()Workbooks("Book2.xlsx").Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub

Note que quando você se refere a workbooks, você precisa usar o nome completo junto com a extensão de arquivo (.xlsx no código acima). Caso o livro de trabalho nunca tenha sido salvo, você não precisa usar a extensão de arquivo.,

agora, estes exemplos não são muito úteis, mas você verá mais tarde 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 uma gama, pode ser uma gama de tamanho fixo ou uma gama de tamanho variável.

em um intervalo de tamanho fixo, você saberia como o intervalo é grande e você pode usar o tamanho exato em seu código VBA. Mas com uma gama de tamanho variável, você não tem idéia de quão grande a gama é e você precisa usar um pouco de magia VBA.vamos ver como se faz.,

seleccionando um intervalo de tamanho fixo

Aqui está o código que irá seleccionar o intervalo A1:D20.

Sub SelectRange()Range("A1:D20").SelectEnd Sub

Outra maneira de fazer isso é usando o código abaixo:

Sub SelectRange()Range("A1", "D20").SelectEnd Sub

O código acima leva a célula superior esquerda endereço (A1) e o inferior direito da célula de endereço (D20) e seleciona o intervalo inteiro. Esta técnica torna-se útil quando você está trabalhando com intervalos de tamanho variavelmente (como veremos quando a propriedade final é coberta mais tarde neste tutorial).,

Se quiser que a selecção ocorra num livro de trabalho diferente ou numa folha de trabalho diferente, então terá de dizer à VBA os nomes exactos destes objectos.

Por exemplo, o código abaixo selecionaria o intervalo A1:D20 na folha de trabalho do Sheet2 no livro de trabalho do Book2.

Sub SelectRange()Workbooks("Book2.xlsx").Worksheets("Sheet1").ActivateRange("A1:D20").SelectEnd Sub

Now, what if you don’t know how many rows are there. E se você quiser selecionar todas as células que têm um valor nela.

nestes casos, você precisa usar os métodos mostrados na próxima seção (Ao selecionar variavelmente o intervalo de tamanho).,

seleccionando um intervalo de tamanho variável

Existem diferentes formas de seleccionar um intervalo de células. O método que você escolher dependeria de como os dados são estruturados.

nesta secção, vou cobrir algumas técnicas úteis que são realmente úteis quando você trabalha com intervalos em VBA.

seleccione a utilização da propriedade CurrentRange

nos casos em que não saiba quantas linhas/colunas têm os dados, poderá usar a propriedade CurrentRange do objecto Range.

A propriedade CurrentRange cobre todas as células contíguas cheias numa gama de dados.,

abaixo está o código que irá seleccionar a região actual que contém a célula A1.

Sub SelectCurrentRegion()Range("A1").CurrentRegion.SelectEnd Sub

o método acima é bom quando você tem todos os dados como uma tabela sem quaisquer linhas/colunas em branco nela.

mas, no caso de ter linhas/colunas em branco nos seus dados, não irá seleccionar as que se seguem às linhas/colunas em branco. Na imagem abaixo, o código atual da região seleciona dados até a linha 10 como a linha 11 está em branco.

nestes casos, você pode querer usar a propriedade UsedRange do objeto da planilha.,

seleccione usando a propriedade UsedRange

UsedRange permite-lhe referir-se a quaisquer células que tenham sido alteradas.

assim o código abaixo selecionaria todas as células usadas na folha ativa.

Sub SelectUsedRegion()ActiveSheet.UsedRange.SelectEnd Sub

Note 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é que essa célula usada seria selecionada.

seleccione usando a propriedade final

Agora, esta parte é realmente útil.

a propriedade Final permite-lhe seleccionar a última célula preenchida., Isto permite-lhe imitar o efeito da tecla de Controlo Para Baixo/Cima ou controlar as teclas direita/esquerda.vamos tentar entender isso usando um exemplo.o problema aqui é que os dados podem mudar e você não sabe quantas células estão preenchidas. Se tiver de o fazer com o teclado, poderá seleccionar a célula A1 e, em seguida, usar a tecla de controlo + seta para baixo, para que possa seleccionar a última célula preenchida da coluna.

Agora vamos ver como fazer isso usando VBA., Esta técnica é útil quando você deseja saltar rapidamente para a última célula preenchida em uma variável de tamanho de coluna

Sub GoToLastFilledCell()Range("A1").End(xlDown).SelectEnd Sub

O código acima, salte para a última célula preenchida na coluna a.

da mesma forma, você pode usar o Final(xlToRight) para pular para a última célula preenchida em uma linha.

Sub GoToLastFilledCell()Range("A1").End(xlToRight).SelectEnd Sub

agora, e se quiser seleccionar a coluna inteira em vez de saltar para a última célula cheia.,

Você pode fazer isso usando o código abaixo:

Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown)).SelectEnd Sub

No código acima, temos a primeira e a última referência da célula que precisamos para selecionar. Não importa quantas células cheias existem, o código acima selecionará todas.

Lembre-se do exemplo acima, onde selecionamos o intervalo A1:D20 utilizando a seguinte linha de código:

Range(“A1″,”D20”)

Aqui A1 foi a célula superior esquerda e D20 foi inferior direito da célula no intervalo. Podemos usar a mesma lógica na seleção de intervalos de tamanho variavelmente., Mas como não sabemos a morada exacta da célula inferior-direita, usámos a propriedade final para a obter.

Na Gama(“A1”, Gama (“A1″).End (xlDown)),” A1″refere-se à primeira célula e intervalo (“A1”).End (xlDown) refere-se à última célula. Uma vez que fornecemos ambas as referências, o método Select selecciona todas as células entre estas duas referências.

da mesma forma, você também pode selecionar um conjunto de dados inteiro que tem várias linhas e Colunas.

o código abaixo iria seleccionar todas as linhas/colunas preenchidas a partir da célula A1.,

Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown).End(xlToRight)).SelectEnd Sub

no código acima indicado, utilizámos o intervalo(“A1”).Fim (xlDown).End (xlToRight) para obter a referência da célula de preenchimento inferior-direito do conjunto de dados.

diferença entre usar a região actual e End

Se está a perguntar-se porque é que usa a propriedade final para seleccionar o intervalo preenchido quando temos a propriedade da região actual, deixe-me dizer-lhe a diferença.

com propriedade Final, poderá indicar a célula inicial., Por exemplo, se você tiver seus dados em A1:D20, mas a primeira linha são cabeçalhos, você pode usar a propriedade final para selecionar os dados sem os cabeçalhos (usando o código abaixo).

Sub SelectFilledCells()Range("A2", Range("A2").End(xlDown).End(xlToRight)).SelectEnd Sub

mas a actual Região seleccionaria automaticamente todo o conjunto de dados, incluindo os cabeçalhos.

até agora, neste tutorial, temos visto como se referir a uma gama de células usando maneiras diferentes.

agora vamos ver algumas maneiras em que podemos realmente usar essas técnicas para obter algum trabalho feito.,

copiar as células / intervalos usando VBA

como mencionei no início deste tutorial, a seleção de uma célula não é necessária para realizar ações sobre ela. Você verá nesta seção Como copiar as células e intervalos sem mesmo selecioná-los.

vamos começar com um exemplo simples.

copiar uma única célula

Se quiser copiar a célula A1 e colá-la na célula D1, o código abaixo fá-lo-á.

Sub CopyCell()Range("A1").Copy Range("D1")End Sub

Note que o método de cópia do objecto range copia a célula (tal como o controlo +C) e cola-a no destino especificado.,

no código de exemplo acima, o destino é especificado na mesma linha onde você usa o método de cópia. Se você quer tornar o seu código mais legível, você pode usar o código abaixo:

Sub CopyCell()Range("A1").Copy Destination:=Range("D1")End Sub

Os códigos acima irá copiar e colar o valor, bem como a formatação/fórmulas nela.

Como já deve ter reparado, o código acima copia a célula sem a seleccionar. Não importa onde você está na planilha, o código irá copiar a célula A1 e colá-la em D1.

também, note que o código acima substituiria qualquer código existente na célula D2., Se você quiser que o Excel lhe diga se já há algo na célula D1 sem sobrepô-lo, você pode usar o código abaixo.

A copiar um intervalo de tamanho fixo

Se quiser copiar A1: D20 em J1: M20, poderá usar o código abaixo:

Sub CopyRange()Range("A1:D20").Copy Range("J1")End Sub

na célula de destino, basta indicar o endereço da célula de topo-esquerda. O código copiaria automaticamente o intervalo exato copiado para o destino.

Você pode usar a mesma construção para copiar dados de uma folha para a outra.

o código abaixo copiaria A1: D20 da folha activa para a Sheet2.,

Sub CopyRange()Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub

the above copies the data from the active sheet. Então certifique-se de que a folha que tem os dados é a folha ativa antes de executar o código. Para ser Seguro, Você também pode especificar o nome da planilha ao copiar os dados.

Sub CopyRange()Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub

a coisa boa sobre o código acima é que não importa qual folha está ativa, ele sempre irá copiar os dados do Sheet1 e colá-lo no Sheet2.

Você também pode copiar um intervalo nomeado usando o seu nome em vez da referência.,

Por exemplo, se você tem um intervalo chamado ‘SalesData’, você pode usar o código abaixo para copiar estes dados para o Sheet2.

Sub CopyRange()Range("SalesData").Copy Worksheets("Sheet2").Range("A1")End Sub

Se o âmbito do intervalo nomeado é todo o livro de trabalho, você não precisa estar na folha que tem o intervalo nomeado para executar este código. Uma vez que o intervalo nomeado é escopado para o livro de trabalho, você pode acessá-lo a partir de qualquer folha usando este código.

Se você tem uma tabela com o nome Table1, você pode usar o código abaixo para copiá-lo para Sheet2.

Sub CopyTable()Range("Table1").Copy Worksheets("Sheet2").Range("A1")End Sub

Você também pode copiar um intervalo para outro livro de trabalho.,

no exemplo seguinte, copio a tabela Excel (Quadro 1), Para O Livro de trabalho do Book2.

Sub CopyCurrentRegion()Range("Table1").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")End Sub

este código só funcionaria se o livro já estivesse aberto.

copiar uma gama de tamanho variável

uma maneira de copiar os intervalos de tamanho variável é converter estes em gamas nomeadas ou tabela Excel e usar os códigos como mostrado na seção anterior.

mas se você não pode fazer isso, você pode usar a atual região ou a propriedade final do objeto range.

o código abaixo copiaria a região actual da folha activa e colá-la-ia no Sheet2.,

Sub CopyCurrentRegion()Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1")End Sub

Se você deseja copiar a primeira coluna de seu conjunto de dados até a última célula preenchida e colá-lo na Planilha2, você pode usar o código abaixo:

Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1")End Sub

Se você deseja copiar as linhas, bem como colunas, você pode usar o código abaixo:

Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1")End Sub

Note que todos esses códigos não selecione as células enquanto ser executado. Em geral, você vai encontrar apenas um punhado de casos em que você realmente precisa selecionar uma célula/intervalo antes de trabalhar nela.,

atribuindo intervalos para variáveis de objeto

até agora, temos usado o endereço completo das células (como livros de trabalho (“Book2.xlsx”).Planilhas (“Sheet1”).Intervalo (“A1”).

para tornar o seu código mais gerenciável, você pode atribuir estes intervalos para variáveis objeto e, em seguida, usar essas variáveis.

Por exemplo, no código abaixo, atribuí a gama de origem e destino para variáveis de objeto e, em seguida, usei essas variáveis para copiar dados de um intervalo para o outro.

começamos por declarar as variáveis como objetos de intervalo., Em seguida, Atribuímos o intervalo para estas variáveis usando a declaração Set. Uma vez que o intervalo foi atribuído à variável, você pode simplesmente usar a variável.

introduza os dados na próxima célula vazia (usando o campo de entrada)

pode usar os campos de entrada para permitir ao Utilizador introduzir os dados.

por exemplo, suponha que você tem o conjunto de dados abaixo e que deseja introduzir o registo de vendas, você pode usar a caixa de entrada em VBA. Usando um código, podemos garantir que ele preenche os dados na próxima linha em branco.,

o código acima usa a caixa de entrada VBA para obter as entradas do Usuário, e então entra as entradas nas células especificadas.

Note que nós não usamos referências celulares exatas. Em vez disso, usamos a propriedade final 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ê digitar uma string de texto quando a caixa de entrada pedir quantidade ou quantidade, você vai 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, permitir em conformidade.,

Looping Through Cells/Ranges

So far we can have seen how to select, copy, and enter the data in cells and ranges.

nesta secção, veremos como fazer um laço através de um conjunto de células/linhas/colunas num intervalo. Isto pode ser útil quando você quer analisar cada célula e realizar alguma ação com base nele.

Por exemplo, se você quiser destacar cada terceira linha na seleção, Então você precisa entrar em ciclo e verificar o número da linha., Da mesma forma, se você quiser destacar todas as células negativas, mudando a cor da fonte para vermelho, você precisa fazer um loop através e analisar o valor de cada célula.

Aqui está o código que irá percorrer as linhas nas células seleccionadas e realçar as linhas alternativas.

Sub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.RowsIf Myrow.Row Mod 2 = 0 ThenMyrow.Interior.Color = vbCyanEnd IfNext MyrowEnd 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 é par, ele fica realçado em cor cyan.

aqui está outro exemplo onde o código passa por cada célula e destaca as células que têm um valor negativo nele.,

Sub HighlightAlternateRows()Dim Myrange As RangeDim Mycell As RangeSet Myrange = SelectionFor Each Mycell In MyrangeIf Mycell < 0 ThenMycell.Interior.Color = vbRedEnd IfNext MycellEnd Sub

Note que você pode fazer a mesma coisa usando formatação condicional (que é dinâmica e uma maneira melhor de fazer isso). Este exemplo é apenas para mostrar como o looping funciona com células e intervalos em VBA.

onde colocar o código VBA

perguntando-se onde o código VBA vai no seu manual do Excel?

Excel tem uma infra-estrutura VBA chamada VBA editor. Você precisa copiar e colar o código na janela de código do módulo de edição VB.

Aqui estão os passos para fazer isto:

1. vá para a página de desenvolvimento.,
2. clique na opção Visual Basic. Isto irá abrir o editor de VB na infra-estrutura.
3. na área do Explorador do projecto no Editor VB, carregue com o botão direito em qualquer objecto para o livro de trabalho no qual deseja inserir o código. Se não vir o Project Explorer, vá à página Ver e clique no Project Explorer.
4. vá para inserir e clique no módulo. Isto irá inserir um objecto de Módulo para o seu livro de trabalho.
5. copie e cole o código na janela do módulo.,

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

• trabalhando com folhas de trabalho usando VBA.
• trabalhar com livros de trabalho usando VBA.
• criando funções definidas pelo usuário no Excel.
• Para O próximo Loop no Excel VBA-um guia de Iniciantes com exemplos.
• Como usar a função Instr do Excel VBA (com exemplos práticos).
• Excel VBA Msgbox.
• Como gravar uma Macro no Excel.
• Como executar uma Macro no Excel.
• Como criar um Add-in No Excel.,
• Excel Macro Workbook pessoal / Save & Use Macros em todos os Workbooks.
• Excel VBA Events – um guia fácil (e completo).
• Excel tratamento de erros VBA.
• Como classificar dados no Excel usando VBA (um guia passo a passo).
• 24 exemplos de Macro Excel úteis para iniciantes de VBA (prontos a usar).