[UDF] ÉVISÍVEL: Somar somente intervalos em planilhas visíveis

Olá Excelmaníacos!

Estou escrevendo mais uma vez um post para responder a uma dúvida curiosa que me deparei em um fórum. O usuário relatou que no Excel existe a função SUBTOTAL que possibilita somar somente células em linhas que estão visíveis, e sua dúvida é se existe alguma forma de somar células que estão apenas nas planilhas visíveis da pasta de trabalho, ou seja, se ocultarmos uma planilha, as células referentes àquela planilha não devem ser somadas na fórmula.

Eu desconheço uma alternativa nativa de resolver esta questão, porém o legal do Excel é que podemos desenvolver nossas próprias soluções. E neste caso, eu vou propor uma solução através do desenvolvimento de uma função personalizada pelo usuário (UDF) que testará se o intervalo está numa planilha visível e, caso verdadeiro, retorne o intervalo para a fórmula processar o cálculo, e caso falso, devolva zero.

O código é bastante simples, veja abaixo com comentário nas linhas:

Public Function ÉVÍSIVEL(rng As Excel.Range) As Variant
'Comando que a função seja volátil, ou seja, efetue o recálculo a cada alteração no arquivo
Application.Volatile
'Declaração da variável da planilha a ser testada
Dim ws As Worksheet
'Atribuir a planilha à variável ws usando a propriedade Parent.Name do range que retornar o nome da planilha
Set ws = Sheets(rng.Parent.Name)
'Teste lógico para saber se á planilha é visível (-1 é visível, 0 é oculta e 2 é "muito oculta" (very hidden)
If ws.Visible = -1 Then
'Se visível a função retorna o próprio intervalo
Set ÉVÍSIVEL = rng
Else
'Caso esteja oculta retorna zero
ÉVÍSIVEL = 0
'Fim do teste lógico
End If
'Atribuição de Nothing a variável para limpar a memória
Set ws = Nothing
End Function

Este código deve ser inserido num módulo da sua pasta de trabalho no VBE (ALT+F11), conforme imagem exemplo abaixo:

Somar Plan Visível

Após inserir o código, pode fechar o VBE, e realizar os cálculos na planilha.

Por exemplo, vamos somar os intervalos A1:A3 da plan2 e da plan3 numa célula da plan1. A fórmula ficaria:

=SOMA(ÉVÍSIVEL(Plan2!A1:A3);ÉVÍSIVEL(Plan3!A1:A3))

Note que para cada intervalo é necessário aplicar a função para fazer a validação de visibilidade.

As observações que deixo é:

1º) Você terá que salvar o arquivo como .xlsm (pasta de trabalho habilitada para macros do excel).

2º) A ação de ocultar uma planilha não dispara o recálculo da planilha, portanto após ocultar uma planilha, é necessário pressionar F9 para forçar o recálculo ou fazer qualquer atualização numa célula para a planilha recalcular e atualizar a fórmula.

Baixe um modelo com esta UDF aqui: Download do Modelo

Anúncios

2 comentários em “[UDF] ÉVISÍVEL: Somar somente intervalos em planilhas visíveis

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s