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 (Exs. B3 ou R3C2).
Um intervalo do Excel se refere 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).
No VBA, você pode se referir a qualquer célula usando o Objeto de alcance.
| 123456789 | 'Consulte a célula B4 na planilha atualmente ativaIntervalo MsgBox ("B4")'Consulte a célula B4 na folha chamada' Dados 'Planilhas MsgBox ("Dados"). Intervalo ("B4")'Consulte a célula B4 na planilha chamada' Dados 'em outra pasta de trabalho ABERTA'chamado' Meus Dados 'Pastas de trabalho MsgBox ("Meus dados"). Planilhas ("Dados"). Faixa ("B4") |
Notação R1C1
Na notação R1C1, uma célula é referida por R seguido pelo número da linha e depois pela letra 'C' seguida pelo número da coluna. por exemplo, B4 na notação R1C1 será referido por R4C2. No VBA você usa o Objeto de Células para usar a notação R1C1:
| 12 | 'Consulte a célula R [6] C [4], ou seja, D6Células (6, 4) = "D6" |
Alcance 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 seguinte se refere a todas as células de A1 a D10:
| 1 | Intervalo ("A1: D10") |
Notação R1C1
Para se referir a mais de uma célula, use “,” entre o endereço da célula inicial e o endereço da última célula. O seguinte se refere a todas as células de A1 a D10:
| 1 | Intervalo (células (1, 1), células (10, 4)) |
Escrevendo para células
Para escrever valores em uma célula ou grupo contíguo de células, basta referir-se ao intervalo, colocar um sinal = e, em seguida, escrever o valor a ser armazenado:
| 12345678910 | 'Armazena F5 na célula com o endereço F6Intervalo ("F6") = "F6"'Armazene E6 na célula com o Endereço R [6] C [5], ou seja, E6Células (6, 5) = "E6"'Armazene A1: D10 no intervalo A1: D10Intervalo ("A1: D10") = "A1: D10"' ouIntervalo (células (1, 1), células (10, 4)) = "A1: D10" |
Leitura de células
Para ler os valores das células, basta referir-se à variável para armazenar os valores, colocar um sinal = e, em seguida, referir-se ao intervalo a ser lido:
| 1234567891011 | Dim val1Dim val2'Lido da célula F6val1 = Intervalo ("F6")'Lido da célula E6val2 = Células (6, 5)MsgBox val1Msgbox val2 |
Nota: Para armazenar valores de um intervalo de células, você precisa usar um Array em vez de uma variável simples.
Células Não Contíguas
Para se referir a células não contíguas, use uma vírgula entre os endereços das células:
| 123456 | 'Armazene 10 nas células A1, A3 e A5Intervalo ("A1, A3, A5") = 10'Armazene 10 nas células A1: A3 e D1: D3)Intervalo ("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:
| 123 | 'Armazene' Col D 'em D1: D10'que é comum entre A1: D10 e D1: F10Intervalo ("A1: D10 D1: G10") = "Col D" |
Offset 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_colunas especificado e número_de_colunas.
Sintaxe de deslocamento
Intervalo.Offset (numero_de_colunas, numero_de_colunas)
Offset de uma célula
| 12345678910111213141516 | 'OFFSET de uma célula A1'Consulte a própria célula'Mova 0 linhas e 0 colunasIntervalo ("A1"). Deslocamento (0, 0) = "A1"'Mova 1 linha e 0 colunasIntervalo ("A1"). Deslocamento (1, 0) = "A2"'Mova 0 linhas e 1 colunasIntervalo ("A1"). Deslocamento (0, 1) = "B1"'Mova 1 linha e 1 colunaIntervalo ("A1"). Deslocamento (1, 1) = "B2"'Mova 10 linhas e 5 colunasIntervalo ("A1"). Deslocamento (10, 5) = "F11" |
Offset from a Range
| 123 | 'Mover referência para intervalo A1: D4 por 4 linhas e 4 colunas'A nova referência é E5: H8Intervalo ("A1: D4"). Deslocamento (4,4) = "E5: H8" |
Definição de referência para 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:
| 12345678 | 'Declare uma variável de intervaloDim myRange as Range'Defina a variável para o intervalo A1: D4Definir myRange = Range ("A1: D4")'Imprime $ A $ 1: $ D $ 4MsgBox myRange.Address |
Redimensionar um intervalo
O método de redimensionamento do objeto Range altera a dimensão do intervalo de referência:
| 1234567 | Dim myRange As Range'Intervalo para redimensionarDefinir myRange = Range ("A1: F4")'Imprime $ A $ 1: $ E $ 10Debug.Print myRange.Resize (10, 5) .Address |
A célula superior esquerda do intervalo redimensionado é igual à célula superior esquerda do intervalo original
Sintaxe de redimensionamento
Intervalo.Resize (número_de_colunas, número_de_colunas)
OFFSET vs Resize
O deslocamento não altera as dimensões do intervalo, mas o move pelo número especificado de linhas e colunas. Redimensionar 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 na folha
O objeto Células se refere a todas as células na planilha (1048576 linhas e 16384 colunas).
| 12 | 'Limpar todas as células nas planilhasCells.Clear |
UsedRange
A propriedade UsedRange fornece o intervalo retangular da célula usada da célula superior esquerda até a célula usada inferior direita da planilha ativa.
| 1234567 | Dim ws como planilhaDefinir ws = ActiveSheet'$ B $ 2: $ L $ 14 se L2 for a primeira célula com qualquer valor'e L14 é a última célula com qualquer valor no'folha ativaDebug.Print ws.UsedRange.Address |
CurrentRegion
A propriedade CurrentRegion fornece o intervalo retangular contíguo da célula superior esquerda até a célula inferior direita usada que contém a célula / intervalo referenciado.
| 1234567891011 | Dim myRange As RangeDefinir myRange = Range ("D4: F6")'Imprime $ B $ 2: $ L $ 14'Se houver um caminho preenchido de D4: F16 a B2 E L14Debug.Print myRange.CurrentRegion.Address'Você pode se referir a uma única célula inicial tambémSet myRange = Range ("D4") 'Imprime $ B $ 2: $ L $ 14 |
Propriedades do intervalo
Você pode obter o endereço, o número da linha / coluna de uma célula e o número de linhas / colunas em um intervalo conforme mostrado abaixo:
| 123456789101112131415161718192021 | Dim myRange As RangeDefinir myRange = Range ("A1: F10")'Imprime $ A $ 1: $ F $ 10Debug.Print myRange.AddressDefinir myRange = Range ("F10")'Imprime 10 para a linha 10Debug.Print myRange.Row'Imprime 6 para a coluna FDebug.Print myRange.ColumnDefinir myRange = Range ("E1: F5")'Imprime 5 para o número de linhas no intervaloDebug.Print myRange.Rows.Count'Imprime 2 para o número de colunas no intervaloDebug.Print myRange.Columns.Count |
Última Célula na Folha
Você pode usar Rows.Count e Columns.Count propriedades com Células objeto para obter a última célula da planilha:
| 1234567891011 | 'Imprime o número da última linha'Imprime 1048576Debug.Print "Linhas na planilha:" & Rows.Count'Imprime o número da última coluna'Prints 16384Debug.Print "Colunas na planilha:" & Columns.Count'Imprime o endereço da última célula'Imprime $ XFD $ 1048576Debug.Print "Endereço da última célula da planilha:" & Células (Rows.Count, Columns.Count) |
Ú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 dessa célula.
| 123 | Dim lastRow enquanto longolastRow = Cells (Rows.Count, "A"). End (xlUp) .Row |
Último número de coluna usado em uma linha
| 123 | Dim lastCol As LonglastCol = Cells (1, Columns.Count) .End (xlToLeft) .Column |
A propriedade END leva você para a última célula no intervalo e End (xlToLeft) leva você da esquerda para a primeira célula usada dessa célula.
Você também pode usar as propriedades xlDown e xlToRight para navegar até a primeira parte inferior ou as células usadas à direita da célula atual.
Propriedades da Célula
Propriedades Comuns
Aqui está o código para exibir as propriedades da célula comumente usadas
| 12345678910111213141516171819202122 | Dim cell como rangeDefinir célula = intervalo ("A1")cell.ActivateDebug.Print cell.Address'Imprimir $ A $ 1Debug.Print cell.Value'Imprime 456' EndereçoDebug.Print cell.Formula'Impressões = SOMA (C2: C3)' ComenteDebug.Print cell.Comment.Text' EstiloDebug.Print cell.Style'Formato da CélulaDebug.Print cell.DisplayFormat.NumberFormat |
Fonte da Célula
O objeto Cell.Font contém propriedades da fonte da célula:
| 1234567891011121314151617181920 | Dim cell como rangeDefinir célula = intervalo ("A1")'Regular, itálico, negrito e negrito itálicocell.Font.FontStyle = "Negrito Itálico"' Igual acell.Font.Bold = Truecell.Font.Italic = True'Definir fonte para Couriercell.Font.FontStyle = "Courier"'Definir cor da fontecell.Font.Color = vbBlue' oucell.Font.Color = RGB (255, 0, 0)'Definir tamanho da fontecell.Font.Size = 20 |
Copiar e colar
Colar tudo
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)
| 1234567 | 'Cópia SimplesIntervalo ("A1: D20"). CopiarPlanilhas ("Folha2"). Intervalo ("B10"). Colar'ou'Copiar da Folha Atual para a folha chamada' Folha2 'Intervalo ("A1: D20"). Destino da cópia: = Planilhas ("Folha2"). Intervalo ("B10") |
Colar especial
As propriedades selecionadas do intervalo de origem podem ser copiadas para o destino usando a opção PASTESPECIAL:
| 123 | 'Cole o intervalo apenas como valoresIntervalo ("A1: D20"). CopiarPlanilhas ("Planilha2"). Intervalo ("B10"). PasteSpecial Paste: = xlPasteValues |
Aqui estão as opções possíveis para a opção Colar:
| 12345678910111213 | 'Colar Tipos EspeciaisxlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats |
Conteúdo de ajuste automático
O tamanho das linhas e colunas pode ser alterado para caber no conteúdo usando o código abaixo:
| 12345 | 'Altere o tamanho das linhas 1 a 5 para caber no conteúdoLinhas ("1: 5"). Ajuste automático'Mude o tamanho das Colunas A para B para caber no conteúdoColunas ("A: B"). Ajuste automático |
Mais exemplos de alcance
É recomendado que você use o Gravador de Macro 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.
Para cada
É mais fácil percorrer um intervalo usando Para cada construir como mostrado abaixo:
| 123 | Para cada célula no intervalo ("A1: B100")'Faça algo com o celularPróxima célula |
A cada iteração do loop, uma célula do intervalo é atribuída à variável ce instruções no loop For são executadas para essa célula. O loop sai 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 para Range.Sort. O código a seguir classificará as colunas A: C com base na chave na célula C2. A ordem de classificação pode ser xlAscending ou xlDescending. Cabeçalho: = xlSim deve ser usado se a primeira linha for a linha do cabeçalho.
| 12 | Colunas ("A: C"). Chave de classificação 1: = Intervalo ("C2"), _pedido1: = xlAscending, Cabeçalho: = xlSim |
Achar
Find também é um método de 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. Voltar Nada se não houver correspondência.
Usar Encontre o próximo método (ou FindPrevious) para localizar a próxima ocorrência (anterior).
O código a seguir mudará a fonte para “Arial Black” para todas as células no intervalo que começam com “John”:
| 12345 | Para cada c no intervalo ("A1: A100")Se c gostar de "John *" entãoc.Font.Name = "Arial Black"Fim sePróximo c |
O código a seguir substituirá todas as ocorrências de "Para teste" para "Aprovado" no intervalo especificado:
| 12345678910 | Com intervalo ("a1: a500")Defina c = .Find ("Para testar", LookIn: = xlValues)Se não c não é nada, entãofirstaddress = c.AddressFazerc.Value = "aprovado"Defina c = .FindNext (c)Loop While Not c Is Nothing And c.Address firstaddressFim seTerminar com |
É 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 aquela célula novamente. Você também deve verificar se nada é encontrado para interromper o loop.
Endereço de intervalo
Use Range.Address para obter o endereço no estilo A1
| 123 | Intervalo de MsgBox ("A1: D10"). Endereço' ouDebug.Print Range ("A1: D10"). Endereço |
Use xlReferenceStyle (o padrão é xlA1) para obter endereços no estilo R1C1
| 123 | Intervalo de MsgBox ("A1: D10"). Endereço (ReferenceStyle: = xlR1C1)' ouDebug.Print Range ("A1: D10"). Endereço (ReferenceStyle: = xlR1C1) |
Isso é útil quando você lida com intervalos armazenados em variáveis e deseja processar apenas para determinados endereços.
Alcance para matriz
É mais rápido e fácil transferir um intervalo para uma matriz e, em seguida, processar os valores. Você deve declarar a matriz como Variant para evitar o cálculo do 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.
| 123456789 | Dim DirArray como variante'Armazene os valores no intervalo para o ArrayDirArray = Range ("a1: a5"). Valor'Loop para processar os valoresPara cada c no DirArrayDebug.Print cPróximo |
Matriz para Alcance
Após o processamento, você pode gravar o Array de volta em um intervalo. Para escrever o Array no exemplo acima em um Range, você deve especificar um Range cujo tamanho corresponda ao número de elementos no Array.
Use o código abaixo para escrever o Array no intervalo D1: D5:
| 123 | Intervalo ("D1: D5"). Valor = DirArrayRange ("D1: H1"). Value = Application.Transpose (DirArray) |
Observe que você deve transpor a matriz se escrever em uma linha.
Intervalo de Soma
| 12 | SumOfRange = Application.WorksheetFunction.Sum (Range ("A1: A10"))Debug.Print SumOfRange |
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.
Intervalo de contagem
| 1234567 | 'Contar o número de células com números no intervaloCountOfCells = Application.WorksheetFunction.Count (Range ("A1: A10"))Debug.Print CountOfCells'Contar o número de células não vazias no intervaloCountOfNonBlankCells = Application.WorksheetFunction.CountA (Range ("A1: A10"))Debug.Print CountOfNonBlankCells |
Escrito por: Vinamra Chandra
