Leitura e Escrita de Planilhas por Programas

Até agora, a única forma de comunicação vista entre uma planilha e uma função é a passagem de parâmetros. No entanto, podemos querer que uma função leia um dado de uma planilha sem que tenhamos que explicitamente passar o valor para o programa. Por exemplo, se quisermos somar os valores positivos de uma coluna:

A B

1 Produto Quantidade em Estoque

2 Vassoura 125

3 Rodo -2 ...

53 Esfregão 12

Neste caso, vamos pensar que a planilha é uma Matriz de dimensão 2x2. A posição (I,J) da matriz corresponde à linha I e à coluna J. Por exemplo, na planilha acima, a posição (2,1) contém o valor ``Vassoura'' de tipo String e a posição (53,2) possui o valor 12 de tipo inteiro (ou double).

De maneira geral o valor da planilha que fica na posição de interseção da linha I com a coluna J pode ser lido através do comando Valor = Cells( I, J ).

Um programa que lê a soma dos itens em estoque (só os itens positivos, pois os itens negativos indicam falta de produto) pode ser o seguinte:

Function ItensEmEstoque() As Integer

'Este é um programa que conta o número de itens em
'estoque conforme armazenado na segunda coluna da Planilha.

Dim Soma As Integer 'Soma de itens em estoque
Dim Valor As Integer 'Valor da linha atual
Dim I As Integer 'Indice da linha
'Inicializacões
Soma = 0
I = 2 'Começamos na segunda linha
Valor = 0
'Principal
Do While I <= 53 'Terminamos na linha 53
'Lê o valor da Planilha
Valor = Cells( I, 2 ) 'Valor lido na linha I, coluna 2
'Atualiza soma
If Valor > 0
Soma = Soma + valor
End If
'Próxima linha
I = I + 1
Loop
'Retorno
ItensEmEstoque = Soma
End Function

Suponhamos agora que não sabemos a priori qual a última linha da coluna de valores. Há uma forma de saber se uma posição está vazia numa planilha, usando a função IsEmpty() . Por exemplo, a condição IsEmpty( cells( I, J ) ) retorna True se a célula (I,J) está vazia e retorna False se algo estiver escrito nela.

O programa anterior pode ser reescrito, sem limite de linhas, como:

Function ItensEmEstoque1() As Integer
'Este é um programa que conta o número de itens em
'estoque conforme armazenado na segunda coluna da Planilha.
Dim Soma As Integer 'Soma de itens em estoque
Dim I As Integer 'Indice da linha
'Inicializacoes
Soma = 0
I = 2 'Começamos na segunda linha
Valor = 0
'Principal
Do While Not IsEmpty(Cells(I,2)) 'Terminamos na linha vazia
'Atualiza soma
If Cells( I, 2 ) > 0
Soma = Soma + Cells( I, 2 )
End If
'Próxima linha
I = I + 1
Loop
'Retorno
ItensEmEstoque = Soma
End Function

Escrita de dados na planilha não pode ser feita a partir de funções, apenas a partir de subrotinas. Isto porque as funções não possuem autorização para mudar a planilha, apenas para lê-las e retornar um único valor.

Para escrever um valor na posição (I,J) da planilha, é preciso usar o comando cells( I, J ) = <valor>. Se quisermos simplesmente apagar um valor contido numa célula, podemos usar cells( I, J ) = Empty.

Para exemplificar este fato, uma planilha resumindo as movimentações bancárias de diversos clientes:

A B C D

1 Nome Saldo Anterior Crédito Débito Saldo Atual

2 Aarão 132,45 12,12 4321,12

3 Abel -987,65 78,90 56,56 ...

Vamos preencher a coluna de Saldo Atual = Saldo Anterior + Crédito - Débito.

Sub SaldoAtual()
' Saldo Anterior na coluna 2
' Credito na coluna 3
' Débito na coluna 4
' Saldo atual na coluna 5
Dim Anterior As Double
Dim Credito As Double
Dim Debito As Double
Dim Atual As Double
Dim I As Integer 'Contador das linhas
I = 2 'Primeira linha
'Não há necessidade de inicialização pois as
' variáveis internas são lidas no início de cada ciclo
Do While Not IsEmpty( Cells(I,1) )
'Lê dados da planilha
Anterior = Cells( I, 2)
Credito = Cells( I, 3)
Debito = Cells( I, 4)
Atual = Anterior + Credito - Debito
'Escreve dados na Planilha
Cells(I, 5) = Atual
'Próxima Linha
I = I + 1
Loop
End Sub

Note que poderíamos dispensar todas as variáveis internas (exceto I), sendo que as 5 primeiras atribuições do loop ficariam comprimidas em um único (e quase incompreensível) comando:

Cells(I, 5) = Cells( I, 2 ) + Cells( I, 3 ) - Cells( I, 4 )

Exercícios

1.

Considere uma planilha que descreve para cada produto, a quantidade em estoque, o estoque mínimo e o estado do estoque.

A B C D

1 Produto Quantidade em Estoque Estoque Mínimo Status

2 Vassoura 125 100

3 Rodo 2 10 ...

53 Esfregão 12 20

Fazer uma macro que escreve na coluna de Status escreve ``OK'' se o estoque existente for maior que o estoque mínimo, ou escreve ``ATENÇÃO!!!'' se o estoque estiver abaixo do mínimo. Resolver o problema em dois casos:

• Para 52 produtos, como mostra a figura.
• Para um número indefinido de produtos, send que o último produto é sinalizado por uma linha vazia.

Por exemplo, sua macro deve alterar a planilha acima para:

A B C D

1 Produto Quantidade em Estoque Estoque Mínimo Status

2 Vassoura 125 100 OK

3 Rodo 2 10 ATENÇÃO!!! ...

53 Esfregão 12 20 ATENÇÃO!!!

2.

Fazer uma macro que copia uma coluna de um lugar para outro da planilha. Assumir que:

• a coluna origem e a coluna destino são requisitadas ao usuário via InputBox.
• As colunas são descritas por números inteiros, ex: 1=A, 2=B, etc
• Os dados da coluna começam na linha 1
• Os dados da coluna terminam com uma linha vazia.

Fazer uma macro para cada um dos dois casos abaixo:

• a coluna de origem NÃO é apagada (Copiar).
• a coluna de origem é apagada (Mover).

Por exemplo, no primeiro caso, se pedimos a cópia da coluna origem 1 para a coluna de destino 3, devemos transformar a planilha:

A B C

1 10 2

2 3 1

3 -7 4

na planilha

A B C

1 10 2 10

2 3 3

3 -7 -7 4