O Excel é poderoso. Se você usa muito, provavelmente já conhece muitos truques usando fórmulas ou autoformatação, mas fazendo uso das funções Cells e Range no VBA, você pode impulsionar sua análise do Excel para um nível totalmente novo.
O problema com o uso das funções Células e Intervalo 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 ficar muito confuso. Veja como você pode usá-las de maneiras que você provavelmente nunca imaginou.
A função das células
As funções Cells e Range permitem que você diga ao seu script VBA 4 erros que você pode evitar ao programar macros do Excel com erros do VBA 4 Você pode evitar quando estiver programando macros do Excel com VBA Código simples e macros são as chaves para superpoderes do Microsoft Excel. Mesmo os não-programadores podem facilmente adicionar funcionalidades impressionantes às suas planilhas com Virtual Basics for Applications (VBA). Apenas evite esses erros iniciantes de programação! Leia Mais exatamente onde em sua planilha você deseja obter ou colocar dados. A principal diferença entre as duas células é o que elas referenciam.
As células geralmente fazem referência a uma única célula por vez, enquanto o Range faz referência a um grupo de células de uma só vez. O formato desta função é Cells (linha, coluna) .
Isso faz referência a todas as células da planilha inteira. É o único exemplo em que a função Células 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 você quisesse, também poderia fazer referência à célula D15 com “Células (15, D”) ”- você pode usar a letra da coluna.
Há muita flexibilidade em poder referenciar uma célula usando um número para coluna e célula, especialmente com scripts que podem percorrer um grande número de células (e executar cálculos nelas) muito rapidamente. Nós vamos chegar a isso em mais detalhes abaixo.
A função de faixa
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, tudo de uma vez. Você não vai querer percorrer uma função Range, porque as referências para as células não são números (a menos que você inclua a função Cells dentro dela).
O formato desta função é Range (Cell # 1, Cell # 2) . Cada célula pode ser designada por um número de letra.
Vamos ver alguns exemplos.
Aqui, a função range está referenciando a célula A5:
Worksheets("Sheet1").Range("A5") Aqui, a função range está referenciando 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 de letras numeradas. Você poderia realmente usar duas funções Células dentro de uma função Range para identificar um intervalo na planilha, assim:
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 em usá-lo, é que ele permite que você escreva código que dinamicamente funciona com intervalos usando loops Como Loops Do-While funcionam na programação de computadores Como Loops Do-While funcionam na programação de computadores Os loops são um dos primeiros tipos de controle que você Aprenderá em programação. Você provavelmente sabe sobre while e for loops, mas o que faz um loop do-while? Consulte Mais informação .
Agora que você entende como formatar as funções Células e Intervalo, vamos mergulhar em como você pode fazer uso criativo dessas funções em seu código VBA.
Processando 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 planilhas.
Vamos dar um exemplo simples. Digamos que você gerencie uma equipe de vendas de 11 pessoas e, a cada mês, queira analisar seu desempenho.
Você pode ter Sheet1 que rastreia sua contagem de vendas e seu volume de vendas.
Na Folha 2, você acompanha a avaliação de feedback dos últimos 30 dias dos clientes da sua empresa.
Se você quiser calcular o bônus na primeira folha usando valores das duas folhas, existem várias maneiras de fazer isso. Você pode escrever uma fórmula na primeira célula que realiza o cálculo usando dados nas duas folhas e arrastá-lo para baixo. Isso vai funcionar.
Uma alternativa para isso é criar um script VBA que você aciona para ser executado sempre que abrir a planilha ou acionado por um botão de comando na planilha para que você possa controlar o cálculo. Você pode usar um script VBA para obter todos os dados de vendas de um arquivo externo de qualquer maneira.
Então, por que não apenas acionar os cálculos para a coluna de bônus no mesmo script naquele momento?
A função das células em ação
Se você nunca escreveu o VBA no Excel antes, será necessário ativar o item de menu Desenvolvedor. Para fazer isso, vá para Arquivo > Opções . Clique em Customize Ribbon . Por fim, escolha Desenvolvedor no painel esquerdo, Adicionar ao painel direito e verifique se a caixa de seleção está marcada.
Agora, quando você clica em OK e volta para a planilha principal, você verá a opção do menu Desenvolvedor.
Você pode usar o menu Inserir para inserir um botão de comando ou apenas clicar em Exibir código para iniciar a codificação.
Neste exemplo, faremos o script ser executado toda vez que a pasta de trabalho for aberta. Para fazer isso, basta clicar em View Code no menu do desenvolvedor e colar a nova função a seguir na janela de código.
Private Sub Workbook_Open() End Sub Sua janela de código será algo como 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 Células, extrair as três variáveis necessárias para o cálculo.
Lembre-se de que a função Células 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, isso será de 1 a 11. O identificador da coluna será 2 para Contagem de Vendas, 3 para Volume de Vendas e 2 da Folha 2 para Pontuação de Feedback.
O cálculo final usa as seguintes porcentagens para somar 100% da pontuação total de bônus. É baseado em uma contagem de vendas ideal de 50, volume de vendas de US $ 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 oferece aos funcionários de vendas um bônus ponderado. Para uma contagem de 50, volume de US $ 50.000 e uma pontuação de 10 - eles recebem o bônus máximo total do mês. No entanto, qualquer coisa abaixo do perfeito em qualquer fator reduz o bônus. Qualquer coisa melhor 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 Isto é o que a saída deste script será semelhante.
Se você quiser que a coluna Bonus mostre o bônus em dólar em vez da porcentagem, você pode multiplicá-la 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 ter que editar seu código.
A beleza da função Células é que você pode criar uma lógica bastante criativa para extrair dados de muitas células em várias folhas diferentes e realizar alguns cálculos bastante complexos com elas.
Você pode executar 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, confira a página Microsoft MSDN para o objeto Cells.
Formatando Células Com Função Range
Para percorrer várias células, uma de cada vez, a função Células é perfeita. Mas se você quiser aplicar algo a um intervalo inteiro de células de uma só vez, a função Range é muito mais eficiente.
Um caso de uso para isso pode ser formatar um intervalo de células usando o script, se determinadas condições forem atendidas.
Por exemplo, digamos que se o volume de vendas de todos os funcionários de vendas ultrapassar o total de US $ 400.000, 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 Como usar instruções IF no Microsoft Excel Como usar as instruções IF no Microsoft Excel Se você é um especialista experiente ou um iniciante de planilha, você vai querer verificar este guia para instruções IF no Excel. Consulte Mais informação .
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 do objetivo 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:
- Aplicar um contorno ao redor do grupo
- Verifique a ortografia do texto dentro de um intervalo de células
- Limpar, copiar ou cortar células
- Pesquise por um intervalo com o método "Find"
- Muito mais
Certifique-se de ler a página Microsoft MSDN para o objeto Range para ver todas as possibilidades.
Leve o Excel para o próximo nível
Agora que você entende as diferenças entre as funções Células e Intervalo, é hora de levar seu script do VBA ao próximo nível. O artigo de Dann sobre como usar as funções Contando e Adicionando 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 guia introdutório fantástico para o Excel VBA O Tutorial de Programação do Excel VBA para Iniciantes O Tutorial de Programação VBA do Excel para Iniciantes O VBA é uma ferramenta poderosa do Microsoft Office. Você pode usá-lo para automatizar tarefas com macros, definir acionadores e muito mais. Apresentaremos a programação básica visual do Excel com um projeto simples. Leia mais para você também.