Excel VBA – Intervalos e Células

Intervalos e Células em VBA

As planilhas do Excel armazenam dados em células. As células são organizadas em linhas e colunas. Cada célula pode ser identificada pelo ponto de interseção de sua linha e coluna (por exemplo, B3 ou R3C2).

Um intervalo do Excel refere-se a uma ou mais células (ex.: A3:B4)

Endereço da Célula

Notação A1

Na notação A1, uma célula é referida por sua letra de coluna (de A a XFD) seguida por seu número de linha (de 1 a 1.048.576). Isso é chamado de endereço da célula.

No VBA, você pode fazer referência a qualquer célula usando o objeto Range.

' Referência à célula B4 na planilha ativa no momento
MsgBox Range("B4")

' Referência à célula B4 na planilha denominada "Data"
MsgBox Worksheets("Data").Range("B4")

' Referência à célula B4 na planilha denominada "Data" em outra pasta de trabalho ABERTA
' Chamada 'Meus Dados'
MsgBox Workbooks("Meus Dados").Worksheets("Data").Range("B4")

Notação R1C1

Na notação R1C1, uma célula é referenciada por R seguido pelo número da linha e, em seguida, pela letra “C” seguida pelo número da coluna. Por exemplo, B4 na notação R1C1 será referenciado por R4C2. No VBA, você usa o objeto Cells para usar a notação R1C1:

' Consultar a célula R[6]C[4], ou seja, D6
Cells(6, 4) = "D6"

Intervalo de Células

Notação A1

Para se referir a mais de uma célula, use um “:” entre o endereço da célula inicial e o endereço da última célula. O exemplo seguinte se refere a todas as células de A1 a D10:

Range("A1:D10")

Notação R1C1

Para fazer referência a mais de uma célula, use um “,” entre o endereço inicial da célula e o endereço da última célula. O exemplo seguinte se refere a todas as células de A1 a D10:

Range(Cells(1, 1), Cells(10, 4))

Gravação em Células

Para gravar valores em uma célula ou em um grupo contíguo de células, basta fazer referência ao intervalo, colocar um sinal = e, em seguida, escrever o valor a ser armazenado:

' Armazenar F5 na célula com o endereço F6
Range("F6") = "F5"

' Armazenar E6 na célula com o endereço R[6]C[5], ou seja, E6
Cells(6, 5) = "E6"

' Armazene A1:D10 no intervalo A1:D10
Range("A1:D10") = "A1:D10"
' ou
Range(Cells(1, 1), Cells(10, 4)) = "A1:D10"

Leitura de Células

Para ler valores de células e atribuir a uma variável, basta fazer referência à variável a armazenar os valores, colocar um sinal = e, em seguida, fazer referência ao intervalo a ser lido:

Dim val1
Dim val2

' Ler da célula F6
val1 = Range("F6")

' Ler da célula F6
val2 = Cells(6, 5)

MsgBox val1
Msgbox val2

Observação: Para armazenar valores de um intervalo de células, é necessário usar uma matriz em vez de uma variável simples.

Células Não Contíguas

Para fazer referência a células não contíguas, use uma vírgula entre os endereços das células:

' Armazenar 10 nas células A1, A3 e A5
Range("A1,A3,A5") = 10


' Armazene 10 nas células A1:A3 e D1:D3)
Range("A1:A3, D1:D3") = 10

Intersecção de Células

Para se referir a células não contíguas, use um espaço entre os endereços das células:

' Armazenar "Col D" em D1:D10
' que é comum entre A1:D10 e D1:F10
Range("A1:D10 D1:G10") = "Col D"

Deslocamento de uma Célula ou Intervalo

Usando a função Offset, você pode mover a referência de um determinado intervalo (célula ou grupo de células) pelo número_de_linhas e número_de_colunas especificados.

Sintaxe de Offset

Range.Offset(número_de_linhas, número_de_colunas)

Deslocamento de uma Célula

' DESLOCAMENTO (OFFSET) de uma célula A1
' Referir-se à própria célula
' Mover 0 linhas e 0 colunas
Range("A1").Offset(0, 0) = "A1"

' Mover 1 linha e 0 colunas
Range("A1").Offset(1, 0) = "A2"

' Mover 0 linhas e 1 coluna
Range("A1").Offset(0, 1) = "B1"

' Mover 1 linha e 1 coluna
Range("A1").Offset(1, 1) = "B2"

' Mover 10 linhas e 5 colunas
Range("A1").Offset(10, 5) = "F11"

Deslocamento de um Intervalo

' Move Reference to Range A1:D4 by 4 rows and 4 columns
' New Reference is E5:H8
Range("A1:D4").Offset(4,4) = "E5:H8"

Definição de referência a um intervalo

Para atribuir um intervalo a uma variável de intervalo: declare uma variável do tipo Range e use o comando Set para defini-la como um intervalo. Observe que você deve usar o comando SET, pois RANGE é um objeto:

' Declarar a variável Range
Dim meuIntervalo as Range

' Defina a variável como o intervalo A1:D4
Set meuIntervalo = Range("A1:D4")

' Imprime $A$1:$D$4
MsgBox meuIntervalo.Address

Redimensionar um Intervalo

O método Resize do objeto Range altera a dimensão do intervalo de referência:

Dim meuIntervalo As Range

' Intervalo para redimensionar
Set meuIntervalo = Range("A1:F4")

' Imprime $A$1:$E$10
Debug.Print meuIntervalo.Resize(10, 5).Address

A célula superior esquerda do intervalo redimensionado é igual à célula superior esquerda do intervalo original

Sintaxe de Redimensionamento

Range.Resize(número_de_linhas, número_de_colunas)

OFFSET vs. Resize

Offset não altera as dimensões do intervalo, mas o move pelo número especificado de linhas e colunas. O redimensionamento não altera a posição do intervalo original, mas altera as dimensões para o número especificado de linhas e colunas.

Todas as Células da Planilha

O objeto Cells refere-se a todas as células da planilha (1048576 linhas e 16384 colunas).

' Limpar todas as células da planilha
Cells.Clear

UsedRange

A propriedade UsedRange fornece o intervalo retangular entre a célula usada no canto superior esquerdo e a célula usada no canto inferior direito da planilha ativa.

Dim ws As Worksheet
Set ws = ActiveSheet

' $B$2:$L$14 se L2 for a primeira célula com algum valor
' e L14 é a última célula com qualquer valor na
' planilha ativa
Debug.Print ws.UsedRange.Address

CurrentRegion

A propriedade CurrentRegion fornece o intervalo retangular contíguo da célula superior esquerda até a célula usada na parte inferior direita que contém a célula/intervalo referenciado.

Dim meuIntervalo As Range

Set meuIntervalo = Range("D4:F6")

' Imprime $B$2:$L$14
' Se houver um caminho preenchido de D4:F16 para B2 E L14
Debug.Print meuIntervalo.CurrentRegion.Address

' Você também pode se referir a uma única célula inicial

Set meuIntervalo = Range("D4") ' Prints $B$2:$L$14

Propriedades do Intervalo

Você pode obter o endereço (Address), o número da linha/coluna de uma célula e o número de linhas/colunas em um intervalo, conforme indicado abaixo:

Dim meuIntervalo As Range

Set meuIntervalo = Range("A1:F10")

' Imprime $A$1:$F$10
Debug.Print meuIntervalo.Address

Set meuIntervalo = Range("F10")

' Imprime 10 for Linha 10
Debug.Print meuIntervalo.Row

' Imprime 6 para coluna F
Debug.Print meuIntervalo.Column

Set meuIntervalo = Range("E1:F5")
' Imprime 5 para o número de linhas no intervalo
Debug.Print meuIntervalo.Rows.Count

' Imprime 2 para o número de colunas no intervalo
Debug.Print meuIntervalo.Columns.Count

Última Célula da Planilha

Você pode usar as propriedades Rows.Count e Columns.Count com o objeto Cells para obter a última célula da planilha:

' Imprimir o último número da linha
' Imprime 1048576
Debug.Print "Linhas na Planilha: " & Rows.Count

' Imprimir o número da última coluna
' Imprime 16384
Debug.Print "Colunas na Planilha: " & Columns.Count

' Imprimir o endereço da última célula
' Imprime $XFD$1048576
Debug.Print "Endereço da última célula da planilha: " & Cells(Rows.Count, Columns.Count).Address

Último Número de Linha Usado em uma Coluna

A propriedade END leva você à última célula do intervalo, e End(xlUp) leva você até a primeira célula usada a partir dessa célula.

Dim UltimaLinha As Long

UltimaLinha = Cells(Rows.Count, "A").End(xlUp).Row

Número da última coluna usada em uma linha

Dim UltimaColuna As Long

UltimaColuna = Cells(1, Columns.Count).End(xlToLeft).Column

A propriedade END leva você à última célula do intervalo, e End(xlToLeft) leva você para a esquerda, para a primeira célula usada a partir dessa célula.

Você também pode usar as propriedades xlDown e xlToRight para navegar até as primeiras células usadas na parte inferior ou direita da célula atual.

Propriedades da Célula

Propriedades Comuns

Aqui está o código para exibir as propriedades de célula comumente usadas

Dim celula As Range
Set celula = Range("A1")

celula.Activate
Debug.Print celula.Address
' Imprime $A$1

Debug.Print celula.Value
' Imprime 456
' Endereço
Debug.Print celula.address
Debug.Print celula.Formula
' Imprime =SUM(C2:C3)

' Comentário
Debug.Print celula.Comment.Text

' Estilo
Debug.Print celula.Style

' Formatação da célula
Debug.Print celula.DisplayFormat.NumberFormat

Fonte da Célula

O objeto Cell.Font contém propriedades da fonte da célula:

Dim celula As Range

Set celula = Range("A1")

' Regular, Itálico, Negrito e Negrito Itálico
celula.Font.FontStyle = "Bold Italic"
' O mesmo que
celula.Font.Bold = True
celula.Font.Italic = True

' Definir a fonte como Courier
celula.Font.Name = "Courier"

' Definir cor da fonte
celula.Font.Color = vbBlue
' ou
celula.Font.Color = RGB(255, 0, 0)

' Definir tamanho da fonte
celula.Font.Size = 20

Copiar e Colar

Colar Tudo

Os intervalos/células podem ser copiados e colados de um local para outro. O código a seguir copia todas as propriedades do intervalo de origem para o intervalo de destino (equivalente a CTRL-C e CTRL-V)

'Cópia Simples
Range("A1:D20").Copy 
Worksheets("Planilha2").Range("B10").Paste

'ou
' Copiar da planilha atual para a planilha denominada "Planilha2
Range("A1:D20").Copy destination:=Worksheets("Planilha2").Range("B10")

Colar Especial

As propriedades selecionadas do intervalo de origem podem ser copiadas para o destino por meio da opção PASTESPECIAL:

' Colar o intervalo apenas como Valores
Range("A1:D20").Copy
Worksheets("Planilha2").Range("B10").PasteSpecial Paste:=xlPasteValues

Aqui estão as opções possíveis para a opção Colar:

' Tipos de Colar Especial
xlPasteAll
xlPasteAllExceptBorders
xlPasteAllMergingConditionalFormats
xlPasteAllUsingSourceTheme
xlPasteColumnWidths
xlPasteComments
xlPasteFormats
xlPasteFormulas
xlPasteFormulasAndNumberFormats
xlPasteValidation
xlPasteValues
xlPasteValuesAndNumberFormats

Ajuste Automático do Conteúdo

O tamanho das linhas e colunas pode ser alterado para se ajustar ao conteúdo usando o AutoFit:

' Alterar o tamanho das linhas 1 a 5 para ajustar o conteúdo
Rows("1:5").AutoFit

' Alterar o tamanho das colunas A e B para ajustar o conteúdo
Columns("A:B").AutoFit

Mais Exemplos de Intervalos

Recomenda-se que você use o Macro Recorder enquanto executa a ação necessária por meio da GUI. Isso o ajudará a entender as várias opções disponíveis e como usá-las.

For Each

É fácil fazer um loop em um intervalo usando For Each conforme mostrado abaixo:

For Each celula In Range("A1:B100")
    ' fazer algo com a célula
Next celula

A cada iteração do loop, uma célula do intervalo é atribuída à célula variável e as instruções no loop For são executadas para essa célula. O loop é encerrado quando todas as células são processadas.

Ordenar

Sort é um método do objeto Range. Você pode classificar um intervalo especificando opções de classificação em Range.Sort. O código abaixo classificará as colunas A:C com base na chave da célula C2. A ordem de classificação pode ser xlAscending ou xlDescending. Header:= xlYes deve ser usado se a primeira linha for a linha de cabeçalho.

   Columns("A:C").Sort key1:=Range("C2"), _
      order1:=xlAscending, Header:=xlYes

Localizar

Find também é um método do Range Object. Ele encontra a primeira célula com conteúdo correspondente aos critérios de pesquisa e retorna a célula como um objeto Range. Ele retorna Nothing se não houver correspondência.

Use o método FindNext (ou FindPrevious) para localizar a próxima ocorrência (anterior).

O código a seguir alterará a fonte para “Arial Black” em todas as células do intervalo que começam com “John”:

For Each c In Range("A1:A100")
    If c Like "John*" Then
        c.Font.Name = "Arial Black"
    End If
Next c

O código a seguir substituirá todas as ocorrências de “Para Testar” por “Aprovado” no intervalo especificado:

With Range("a1:a500")
    Set c = .Find("Para Testar", LookIn:=xlValues)
    If Not c Is Nothing Then
        PrimeiroEndereco = c.Address
        Do
            c.Value = "Aprovado"
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> PrimeiroEndereco 
    End If
End With

É importante observar que você deve especificar um intervalo para usar FindNext. Além disso, você deve fornecer uma condição de parada, caso contrário o loop será executado para sempre. Normalmente, o endereço da primeira célula encontrada é armazenado em uma variável e o loop é interrompido quando você alcança essa célula novamente. Você também deve verificar o caso em que nada é encontrado para interromper o loop.

Endereço do Intervalo

Use Range.Address para obter o endereço no estilo A1

MsgBox Range("A1:D10").Address
' ou
Debug.Print Range("A1:D10").Address

Use xlReferenceStyle (o padrão é xlA1) para obter endereços no estilo R1C1

MsgBox Range("A1:D10").Address(ReferenceStyle:=xlR1C1)
' ou
Debug.Print Range("A1:D10").Address(ReferenceStyle:=xlR1C1) 

Isso é útil quando você lida com intervalos armazenados em variáveis e deseja processar apenas determinados endereços.

Intervalo para Matriz

É mais rápido e fácil transferir um intervalo para uma matriz e depois processar os valores. Você deve declarar a matriz como Variant para evitar calcular o tamanho necessário para preencher o intervalo na matriz. As dimensões da matriz são definidas para corresponder ao número de valores no intervalo.

Dim DirArray As Variant
' Armazene os valores do intervalo na matriz

DirArray = Range("a1:a5").Value

' Loop para processar os valores
For Each c In DirArray
    Debug.Print c
Next

Matriz para Intervalo

Após o processamento, você pode gravar a matriz de volta em um intervalo. Para gravar a matriz do exemplo acima em um intervalo, você deve especificar um intervalo cujo tamanho corresponda ao número de elementos da matriz.

Use o código abaixo para gravar a matriz no intervalo D1:D5:

Range("D1:D5").Value = DirArray 

Range("D1:H1").Value = Application.Transpose(DirArray)

Observe que você deve transpor a matriz se for gravá-la em uma linha.

Soma do Intervalo

SomaDoIntervalo = Application.WorksheetFunction.Sum(Range("A1:A10"))
Debug.Print SomaDoIntervalo

Você pode usar muitas funções disponíveis no Excel em seu código VBA especificando Application.WorkSheetFunction. antes do nome da função, como no exemplo acima.

Contagem do Intervalo

' Contagem do número de células com números no intervalo
ContagemDeCelulas = Application.WorksheetFunction.Count(Range("A1:A10"))
Debug.Print ContagemDeCelulas 

' Contagem do número de células não vazias no intervalo
ContagemNaoVazias = Application.WorksheetFunction.CountA(Range("A1:A10"))
Debug.Print ContagemNaoVazias 

Escrito por: Vinamra Chandra