Compreendendo células do Excel versus funções de intervalo em VBA

Usar as funções Intervalo e Células no Excel pode ser muito confuso. Veja como você pode usá-los de maneiras que provavelmente nunca imaginou, com a ajuda do VBA

O Excel é poderoso. Se você o usa muito, provavelmente já conhece muitos truques usando fórmulas ou formatação automática, mas usando a função Intervalos e Células no VBA, você pode levar suas análises do Excel a um nível totalmente novo.

O problema de usar as funções Células e Intervalo em macros 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.

Função de células do Visual Basic

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

A função de células VBA geralmente faz referência a uma única célula por vez, enquanto Range faz referência a um grupo de células de uma vez.

Este é o formato da função de células:

 Cells(row, column)

Isso faz referência a cada célula da planilha inteira. Este é um exemplo em que a função de células macro VBA não faz referência a uma única célula:

 Worksheets("Sheet1").Cells

Pelo contrário, este trecho de código faz referência à terceira célula a partir da esquerda, na linha número um, ou seja, Célula C1:

 Worksheets("Sheet1").Cells(3)

Vamos nos aventurar um pouco, referindo a célula D15 usando a função de células VBA:

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

Se desejar, você também pode referenciar a célula D15 com o seguinte código VBA de função de células:

 Cells(15,"D")"

--- você tem permissão para usar a letra da coluna.

Há muita flexibilidade usando a função de célula VBA, já que você pode referenciar uma célula usando um número para coluna e célula, especialmente com scripts que podem percorrer muitas células (e realizar cálculos nelas) muito rapidamente.

A função de alcance

Em muitos aspectos, 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 células não são números (a menos que você incorpore a função de células VBA Excel dentro dela).

O formato desta função é o seguinte:

 Range(Cell #1,Cell #2)

Cada célula pode ser designada por um número de letra. Vejamos alguns exemplos.

O código a seguir é uma função de intervalo que faz referência à célula A5:

 Worksheets("Sheet1").Range("A5")

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

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

Conforme mencionado acima, você não precisa usar atribuições de células com números e letras. Na verdade, você poderia usar duas funções de célula VBA 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

Este código faz referência ao mesmo intervalo que a função Range("A1:E20") faz. O valor de usá-lo é que ele permitiria escrever código que funciona dinamicamente com intervalos usando loops.

Agora que você entende como formatar as funções Células e Intervalo, vamos ver como você pode fazer uso criativo dessas funções em seu código VBA.

Função de processamento de dados com células

A função de células do Visual Basic é bastante útil quando você tem uma fórmula complexa que deseja usar em vários intervalos de células. Esses intervalos também podem existir em várias planilhas.

Vejamos um exemplo simples. Digamos que você gerencie uma equipe de vendas de 11 pessoas e todo mês queira analisar o desempenho delas.

Você pode ter a Planilha1 que rastreia a contagem e o volume de vendas.

Na Planilha2 você tem dados para monitorar a avaliação dos comentários dos clientes da sua empresa nos últimos 30 dias.

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

Uma alternativa para isso é criar um script VBA que é executado sempre que você abre a planilha ou aciona-o por meio de um botão de comando na planilha, para que você possa controlar quando ele calcula. De qualquer maneira, você pode usar um script VBA para extrair todos os dados de vendas de um arquivo externo.

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

Função de células macro do Excel em ação

Se você nunca escreveu código VBA no Excel antes, precisará ativar o item de menu Desenvolvedor . Para fazer isso, vá em Arquivo > Opções. Clique em Personalizar faixa de opções. Por fim, escolha Desenvolvedor no painel esquerdo, Adicione ao painel direito e certifique-se de que a caixa de seleção esteja marcada.

Agora, ao clicar em OK e voltar 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 Ver código para iniciar a codificação.

Neste exemplo, você configurará o script para ser executado sempre que a pasta de trabalho for aberta. Para fazer isso, basta clicar em Ver código 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á semelhante a esta.

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 de células VBA do Excel, obter as três variáveis necessárias para o cálculo.

Lembre-se de que a função worksheets.cells tem linha e coluna como parâmetros para identificar cada célula individual. Você precisa substituir x pela referência da linha e usar 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 Folha 2 para Pontuação de Feedback.

O cálculo final usa as seguintes porcentagens para somar 100% da pontuação total do 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, um volume de $50.000 e uma pontuação de 10 – eles recebem todo o bônus máximo do mês. No entanto, qualquer coisa abaixo da perfeição 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 executada em um script VBA muito simples e curto:

 Private Sub Workbook_Open()For x = 2 To 12Worksheets("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 xEnd Sub

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

Se você quiser que a coluna Bônus mostre o bônus real em dólares, em vez da porcentagem, você pode multiplicá-lo pelo valor máximo do bônus. Melhor ainda, coloque esse valor em uma célula de outra planilha e referencie-o em seu código. Isso tornaria mais fácil alterar o valor posteriormente, sem precisar editar seu código.

A beleza do método das células no VBA é que você pode construir uma lógica bastante criativa para extrair dados de muitas células em muitas planilhas e realizar alguns cálculos bastante 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, confira a página do Microsoft MSDN para o objeto Cells.

Formatando células com função Range

Para percorrer muitas células, uma de cada vez, a função das 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. Na comparação contínua entre o intervalo do VBA e as células, há muito o que esperar.

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

Por exemplo, digamos que a contagem de todo o volume de vendas de todos os funcionários de vendas ultrapasse 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 extra de equipe.

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

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

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

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

• Aplique 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 um intervalo com o método Find

Certifique-se de ler a página do 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 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.