Noções básicas sobre células do Excel vs. funções de intervalo no VBA

Noções básicas sobre células do Excel vs. funções de intervalo no VBA

O Excel é poderoso. Se você o usa muito, provavelmente já conhece muitos truques usando fórmulas ou autoformatação, mas fazendo uso de Células e Faixa funções no VBA, você pode impulsionar suas análises do Excel para um nível totalmente novo.





O problema de usar as funções Células e Alcance no VBA é que, nos níveis avançados, a maioria das pessoas tem dificuldade em entender como essas funções realmente funcionam. Usá-los pode ser muito confuso. Veja como você pode usá-los de maneiras que provavelmente nunca imaginou.



A função das células

As funções Células e Alcance permitem que você diga seu script VBA exatamente onde você deseja obter ou colocar os dados em sua planilha. A principal diferença entre as duas células é o que elas fazem referência.





Células geralmente fazem referência a uma única célula por vez, enquanto Faixa faz referência a um grupo de células de uma vez. O formato desta função é Células (linha, coluna) .

Isso faz referência a cada célula da planilha inteira. É o único exemplo em que a função Cells não faz referência a uma única célula:



Worksheets('Sheet1').Cells

Isso faz referência à terceira célula da esquerda, da linha superior. Célula C1:

Worksheets('Sheet1').Cells(3)

O código a seguir faz referência à célula D15:



Worksheets('Sheet1').Cells(15,4)

Se desejar, você também pode fazer referência à célula D15 com 'Células (15,' D ')' --- você tem permissão para usar a letra da coluna.

Há muita flexibilidade em poder fazer referência a uma célula usando um número para coluna e célula, especialmente com scripts que podem loop através um grande número de células (e realizar cálculos sobre elas) muito rapidamente. Veremos isso com mais detalhes a seguir.



A Função de Alcance

De muitas maneiras, a função Range é muito mais poderosa do que usar Cells, porque permite fazer referência a uma única célula ou a um intervalo específico de células de uma só vez. Você não vai querer percorrer uma função Range, porque as referências para células não são números (a menos que você incorpore a função Cells dentro dela).

O formato desta função é Intervalo (Célula 1, Célula 2) . Cada célula pode ser designada por uma letra-número.

Vejamos alguns exemplos.

eu apertei um botão no meu teclado e agora não consigo digitar

Aqui, a função de intervalo está referenciando a célula A5:

Worksheets('Sheet1').Range('A5')

Aqui, a função de intervalo faz referência a todas as células entre A1 e E20:

Worksheets('Sheet1').Range('A1:E20')

Como mencionado acima, você não precisa usar atribuições de células numéricas e letras. Na verdade, você poderia usar duas funções Cells dentro de uma função Range para identificar um intervalo na planilha, como este:


With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With

O código acima faz referência ao mesmo intervalo que a função Range ('A1: E20') faz. O valor de usá-lo é permitir que você escreva um código que funcione dinamicamente com intervalos usando loops.

Agora que você entende como formatar as funções Cells e Range, vamos mergulhar em como você pode fazer uso criativo dessas funções em seu código VBA.

Processamento de dados com função de células

A função Células é mais útil quando você tem uma fórmula complexa que deseja executar em vários intervalos de células. Esses intervalos também podem existir em várias páginas.

Vamos dar um exemplo simples. Digamos que você gerencie uma equipe de vendas de 11 pessoas e, a cada mês, queira observar seu desempenho.

Você talvez tenha Folha1 que rastreia sua contagem de vendas e seu volume de vendas.

Sobre Folha2 é onde você acompanha a classificação de feedback dos clientes de sua empresa nos últimos 30 dias.

Se você deseja calcular o bônus na primeira folha usando os valores das duas folhas, existem várias maneiras de fazer isso. Você pode escrever uma fórmula na primeira célula que executa o cálculo usando dados nas duas planilhas e arrastá-la para baixo. Isso vai funcionar.

Uma alternativa a isso é criar um script VBA que você pode disparar para ser executado sempre que abrir a planilha ou disparar por um botão de comando na planilha para que você possa controlar quando ela calcula. Você pode usar um script VBA para extrair todos os dados de vendas de um arquivo externo de qualquer maneira.

Então, por que não simplesmente acionar os cálculos para a coluna de bônus no mesmo script naquele momento?

As células funcionam em ação

Se você nunca escreveu VBA no Excel antes, será necessário habilitar o item de menu Desenvolvedor. Para fazer isso, vá para Arquivo > Opções . Clique em Personalizar fita . Por fim, escolha Desenvolvedor no painel esquerdo, Adicionar no painel direito e certifique-se de que a caixa de seleção esteja marcada.

Agora, quando você clica OK e volte para a planilha principal, você verá a opção de menu Desenvolvedor.

Você pode usar o Inserir menu para inserir um botão de comando ou apenas clique Ver código para começar a codificar.

Neste exemplo, faremos o script ser executado sempre que a pasta de trabalho for aberta. Para fazer isso, basta clicar Ver código no menu do desenvolvedor e cole a nova função a seguir na janela de código.

Private Sub Workbook_Open()
End Sub

Sua janela de código será parecida com isto.

Agora você está pronto para escrever o código para lidar com o cálculo. Usando um único loop, você pode percorrer todos os 11 funcionários e, com a função Cells, obter as três variáveis ​​necessárias para o cálculo.

Lembre-se de que a função Cells tem linha e coluna como parâmetros para identificar cada célula individual. Faremos 'x' a linha, usaremos um número para solicitar os dados de cada coluna. O número de linhas é o número de funcionários, portanto, será de 1 a 11. O identificador da coluna será 2 para Contagem de Vendas, 3 para Volume de Vendas e 2 da Planilha 2 para Pontuação de Feedback.

O cálculo final usa as seguintes porcentagens para somar 100 por cento da pontuação total do bônus. É baseado em uma contagem de vendas ideal de 50, o volume de vendas de $ 50.000 e uma pontuação de feedback de 10.

  • (Contagem de vendas / 50) x 0,4
  • (Volume de vendas / 50.000) x 0,5
  • (Pontuação de Feedback / 10) x 0,1

Essa abordagem simples dá aos funcionários de vendas um bônus ponderado. Para uma contagem de 50, um volume de $ 50.000 e uma pontuação de 10 --- eles recebem o bônus máximo total do mês. No entanto, qualquer coisa abaixo da perfeição em qualquer fator reduz o bônus. Qualquer coisa melhor do que o ideal aumenta o bônus.

Agora vamos ver como toda essa lógica pode ser obtida em um script VBA muito simples e curto:

Private Sub Workbook_Open()
For x = 2 To 12
Worksheets('Sheet1').Cells(x, 4) = (Worksheets('Sheet1').Cells(x, 2).Value / 50) * 0.4 _
+ (Worksheets('Sheet1').Cells(x, 3).Value / 50000) * 0.5 _
+ (Worksheets('Sheet2').Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub

Esta é a aparência da saída deste script.

o que você pode fazer com um pi de framboesa

Se você quiser que a coluna Bônus mostre o bônus em dólares real ao invés da porcentagem, você pode multiplicar pelo valor máximo do bônus. Melhor ainda, coloque essa quantidade em uma célula em outra planilha e faça referência a ela em seu código. Isso tornaria mais fácil alterar o valor posteriormente, sem precisar editar seu código.

A beleza da função Cells é que você pode construir uma lógica bastante criativa para extrair dados de muitas células em muitas folhas diferentes, e realizar alguns cálculos bem complexos com eles.

Você pode realizar todos os tipos de ações nas células usando a função Células - coisas como limpar as células, alterar a formatação da fonte e muito mais.

Para explorar tudo o que você pode fazer mais adiante, confira o Página Microsoft MSDN para o objeto Cells.

Formatando células com função de intervalo

Para percorrer muitas células, uma de cada vez, a função Cells é perfeita. Mas se você quiser aplicar algo a um intervalo inteiro de células de uma vez, a função Range é muito mais eficiente.

Um caso de uso para isso pode ser formatar um intervalo de células usando script, se certas condições forem atendidas.

Por exemplo, digamos que se a contagem de todo o volume de vendas de todos os funcionários de vendas ultrapassar US $ 400.000 no total, você deseja destacar todas as células na coluna de bônus em verde para indicar que a equipe ganhou um bônus de equipe extra.

Vamos dar uma olhada em como você pode fazer isso com uma declaração IF .

Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End Sub

Quando isso for executado, se a célula estiver acima da meta da equipe, todas as células no intervalo serão preenchidas em verde.

Este é apenas um exemplo simples das muitas ações que você pode executar em grupos de células usando a função Range. Outras coisas que você pode fazer incluem:

  • Aplique um esboço em todo o grupo
  • Verifique a ortografia do texto dentro de um intervalo de células
  • Limpar, copiar ou cortar células
  • Pesquise em um intervalo com o método 'Find'
  • Muito mais

Certifique-se de ler o Página Microsoft MSDN para o objeto Range para ver todas as possibilidades.

Leve o Excel ao próximo nível

Agora que você entende as diferenças entre as funções Cells e Range, é hora de levar seu script VBA para o próximo nível. O artigo de Dann sobre o uso de funções de contagem e adição no Excel permitirá que você crie scripts ainda mais avançados que podem acumular valores em todos os seus conjuntos de dados muito rapidamente.

E se você está apenas começando com o VBA no Excel, não se esqueça que temos um fantástico guia introdutório ao Excel VBA para você também.

Compartilhado Compartilhado Tweet O email Canon vs. Nikon: Qual marca de câmera é melhor?

Canon e Nikon são os dois maiores nomes da indústria de câmeras. Mas qual marca oferece a melhor linha de câmeras e lentes?

Leia a seguir Tópicos relacionados
  • Produtividade
  • Programação
  • Programação Visual Basic
  • Microsoft Excel
Sobre o autor Ryan Dube(942 artigos publicados)

Ryan é bacharel em Engenharia Elétrica. Ele trabalhou 13 anos em engenharia de automação, 5 anos em TI e agora é engenheiro de aplicativos. Ex-editor-chefe da MakeUseOf, ele palestrou em conferências nacionais sobre visualização de dados e foi apresentado na TV e no rádio nacionais.

Mais de Ryan Dube

Assine a nossa newsletter

Junte-se ao nosso boletim informativo para dicas de tecnologia, análises, e-books grátis e ofertas exclusivas!

Clique aqui para se inscrever