Excel – Protegendo planilhas de maneira eficiente

Republicação de artigo

Como este artigo ainda é muito requisitado, resolvi recolocá-lo aqui no blog, pois anda perdido num HTML antigo do site antigo.

Introdução

O Microsoft Excel permite diversas formas de proteção de dados, incluindo ocultar planilhas e fórmulas a fim de proteger a inteligência, a lógica, da sua planilha.

É importante proteger, conservar a inteligência/lógica de nossas fórmulas, seja por proteção da propriedade intelectual ou por qualquer outro motivo. Alguns recursos são bastante conhecidos. Citarei aqui todas as formas que conheço e que creio serem suficientes para proteger nossas planilhas.

Ocultando planilhas

O método mais básico de proteger planilhas é ocultando-as através do comando: Formatar -> Planilha -> Ocultar, conforme indicado na figura a seguir:

O Excel ocultará a planilha que estiver ativa naquele momento, conforme indicado na figura a seguir:

Esta é uma maneira simples de ocultar as planilhas. Para voltar a exibira as planilhas ocultas, basta usar o comando: Formatar -> Planilha -> Reexibir, conforme indicado na figura a seguir:

Ao executar o comando Formatar -> Planilha -> Reexibir , será aberta a janela Reexibir, na qual são listadas as planilhas ocultas, conforme indicado na figura a seguir. Na listagem estão todas as planilhas ocultas, as quais poderão ser reexibidas.

Por exemplo, para reexibir a planilha Plan1, basta clicar na planilha, para seleciona-la e clicar em OK. Com isso a planilha “Plan1” voltará a ser exibida.

Protegendo e ocultando células

Podemos contornar este problema protegendo a planilha pelo modo clássico. Acionando o comando: Ferramentas -> Proteger -> Proteger planilha, teremos acesso a diferente níveis de proteção à planilha conforme mostra as duas figuras a seguir:

Dentre as opções disponíveis, as mais interessantes são:

Selecionar células bloqueadas à Desmarcando esta opção, o usuário só poderá navegar nas células que tiverem nas suas propriedades, a opção Travada desmarcada (Veja esta opção em Formatar -> Células, guia Proteção).

Selecionar células desbloqueadas à Desmarcando esta opção, o usuário não conseguirá navegar pela planilha.

Todas as outras opções trabalham nesse mesmo padrão. Deixando a caixa de seleção desmarcada, a opção corrente não será liberada ao usuário. Dessa forma você pode conduzir o usuário na utilização da planilha. Outro recurso interessante, é que marcando a opção Oculta na caixa de formatação de células, seu conteúdo (fórmulas) não serão visíveis para o usuário, mesmo que este possa navegar pela célula, conforme ilustrado pela seqüência de figuras a seguir:

Protegendo a planilha conforme citado anteriormente, o conteúdo da fórmula não estará visível na barra de fórmulas:

Outra opção bastante utilizada, porém sem muita proteção é a de ocultar as guias de planilhas no menu Ferramentas -> Opções, guia Exibir, desmarcando a opção Guias de planilha, conforme destacado na figura a seguir:

Dessa forma, planilhas exibidas ou ocultas não estarão visíveis conforme segue:

Até aí tudo bem. Mas não precisamos ser muito veteranos no Excel a ponto de saber alguns atalhos básico como Ctrl+C para copiar, Ctrl+V para colar ou até mesmo Ctrl+1 para abrir a caixa de formatação de células. Por isso, sabemos que existe um atalho também para navegação de planilhas, que é o Ctrl+PageDown para ir para a planilha à direita da planilha atual e Ctrl+PageUp para a esquerda. Ocultar as guias de planilha não impede o usuário de navegar pelas planilhas. Faça o teste e comprove. Abra uma nova pasta, oculta as guias de planilha e tente navegar com os atalhos citados acima.

Algumas combinações desses recursos podem resultar em resultados mais eficientes, porém, existe uma maneira de fazê-lo de forma simples e segura (ou quase).

Ocultando definitivamente as planilhas

As planilhas tem 3 modos de exibição, dois muito conhecidos e 1 mais restrito a usuários avançados, mas que detalharei de forma que possa ser acessível a qualquer nível de usuário.

  • Visível
  • Oculta
  • Muito oculta <- é neste que devemos dar atenção.

Nota: A tradução do recurso (Muito oculta) é por minha conta e risco.

Para acessar este último nível de visibilidade da planilha, teremos que ir ao Editor do Visual Basic disponível no Excel.

Atenção! Dependendo do processo de instalação do Office, o Visual Basic pode não estar disponível em seu Microsoft Excel.

Para acessar o Editor do Visual Basic, vá em Ferramentas, Macro, Editor do Visual Basic, ou simplesmente no Excel, tecle Alt+F11. A janela a seguir será exibida:

Nota: Caso não consiga ver os painéis visíveis à esquerda da tela, acione-os no menu Exibir, Project Explorer (VBA Project) e Janela de propriedades. No Project Explorer, podemos ver todos os objetos de nossa pasta de trabalho hierarquizados. O primeiro é a própria pasta de trabalho, seguida de todas as planilhas identificadas pelo seu respectivo nome.

Selecionando uma das planilhas, suas propriedades serão exibidas na janela de propriedades conforme indicado na figura a seguir:

Obs : as janelas foram re-dispostas na tela para facilitar a visualização.

Detalhar todas as propriedades das planilhas é assunto para outro tutorial. Neste caso, nos atentaremos para a propriedade Visible:

Vou detalhar as opções disponíveis:

Visível -> -1 – xlSheetVisible
Oculta -> 0 – xlSheetHidden
Muito oculta -> 2 – xlSheetVeryHidden

As duas primeiras já são conhecidas e podem ser acessadas pelo Excel como mostrado anteriormente. A propriedade Visible setada para “-1 – xlSheetVisible” é a planilha visível normalmente.

Setada para “0 – xlSheetHidden” , deixa planilha oculta, mas podendo ser exibida através do menu Formatar, planilha, Reexibir.

Já a última opção, coloca a planilha em modo oculto, de forma que nem mesmo através da opção Reexibir , poderá ser acessada! Faça o teste. Coloque a planilha Plan1 com a propriedade Visible setada para “2 – xlSheetVeryHidden”:

Retorne ao Excel, bastando fechar o Visual Basic ou clicando no botão Exibir Microsoft Excel:

Você verá que a planilha Plan1 está oculta como esperávamos. Agora tente ir ao menu Formatar -> Planilha, a opção Reexibir estará desabilitada (conforme indicado na próxima figura). Mesmo que você oculte manualmente a planilha Plan2 ou Plan3 e acionar o menu Reexibir, a planilha Plan1 não aparecerá! Porém, ela continua existindo e pode conter valores referenciados por fórmulas nas outras planilhas. Faça o teste e comprove.

Você pode estar se perguntando: Mas o que eu faço, alguém pode desfazer? A resposta é sim. Mas o nosso trabalho é dificultar ao máximo o trabalho de quem quer fazê-lo, já que não existe sistema 100% seguro, nem mesmo nos poderosos e sofisticados laboratórios na NASA.

A solução é proteger seu projeto VBA. Na mesma tela em que você alterou as configurações da planilha, vá até o Project Explorer e clique com o botão direito no título do Projeto e seleciona Propriedade do VBA Project:

Nesta tela, selecione a opção bloquear projeto para exibição e coloque uma senha para proteção do projeto. Salve e fecha a planilha. Abra-a novamente e vá ao Editor do Visual Basic (Alt+F11). Você verá que o Project Explorer será exibido da seguinte forma:

Se alguém tentar expandir o nó do projeto, verá a seguinte mensagem:

Pronto! Sua planilha está protegida! (desde que você não tenha definido uma senha que seja facilmente descoberta por programas de quebra de senhas. Uma boa senha deve ter letras, números e caracteres especiais. Se tiver somente letras e números, os programas de quebra de senha, como o Passware Kit, irão descobrir a senha em menos de um minuto).

É claro que esta não é uma proteção infalível, até mesmo porque nós mesmos temos o péssimo costume de não anotar senhas e acabamos virando nossa defesa contra nós mesmos. Programas para quebrar senhas como o Advanced Office 2000 Password Recovery podem recuperar a senha de projetos VBA ou sobreescrevê-las para que possam ser acessados.

Meu conselho para este tipo de ferramenta é você adotar uma metodologia segura de definição de senhas, por exemplo, não colocar informações pessoais, não colocar senhas do tipo “1234”. Sempre procure colocar senhas que não se relacionem em nada com você e que contenham letras e números intercalados e, o mais importante, sempre tenha senhas com mais de 6 ou 7 dígitos.

Creio que seguindo estas metodologias, é possível ter um nível de segurança razoável em nossos trabalhos com o Microsoft Excel.

Forte abraço!

VBA – Transferindo os dados de um ListBox para uma Planilha

Por falta de muitos controles no VBA, o ListBox é quase uma unanimidade para fazer a apresentação dos dados de forma tabular. Ele funciona bem, permite adicionar múltiplas colunas, adicionar valores de cabeçalho, mudar o formato da seleção, enfim.

O fato é que, códigos de exemplo para alimentar um listBox com os dados de uma planilha há de monte, mas o contrário nem tanto. Bom, atendendo a pedidos, coloco aqui uma alternativa de código que faz exatamente o trabalho de copiar os dados de um ListBox para uma planilha.

Para não começar totalmente do zero, tomei como base o arquivo do site Ozgrid, disponível neste link:

http://www.ozgrid.com/FreeDownloads/ListBoxTransfer.zip

O código está bem bacana, porém, muito amarrado a planilha que foi produzida, sendo totalmente dependente da sua implementação. Abaixo segue o código com algumas mudanças de tal forma que, possa ser facilmente adaptado para qualquer planilha:

Sub Copiar_ListBox_Para_Planilha(ByVal NomePlanilhaDestino As String, ByRef listBoxControl As MSForms.ListBox)
    Dim iListCount As Integer, iColCount As Integer, iColListCount As Integer
    Dim iRow As Integer
    Dim linhaInicial As Integer
    Dim planilhaDestino As Worksheet
 
    Set planilhaDestino = ThisWorkbook.Worksheets(NomePlanilhaDestino)
    linhaInicial = planilhaDestino.Cells(65536, 2).End(xlUp).Row + 1
 
    If listBoxControl.RowSource <> "" Then
        iColListCount = Range(listBoxControl.RowSource).Columns.Count
    Else
        iColListCount = listBoxControl.ColumnCount - 1
    End If
 
    If listBoxControl.ColumnHeads Then
        iRow = 1
    End If
 
    'faz o loop para todos os itens do ListBox
    For iListCount = 0 To listBoxControl.ListCount - 1
        If listBoxControl.Selected(iListCount) = True Then  'testa se o item está selecionado
            listBoxControl.Selected(iListCount) = False
            iRow = iRow + 1
            'faz o loop pelas colunas
            For iColCount = 0 To iColListCount - 1
                'copia os dados do controle para a planilha
                planilhaDestino.Cells(iRow, iColCount + 1).Value = listBoxControl.List(iListCount, iColCount)
            Next iColCount
        End If
    Next iListCount
 
End Sub

Para fazer a chamada a esse método na mesma planilha disponibilizada, bastaria colocar o seguinte código:

Call Copiar_ListBox_Para_Planilha("Data Transfer Sheet", Me.ListBox1)

Ou seja, ele permite você definir o nome da planilha para qual será copiado o conteúdo e qual o controle ListBox será a fonte dos dados. O código faz várias considerações, prevendo que seus dados sejam baseados em adição manual ou através de RowSource. Copie o código abaixo e faça o teste em um ListBox existente!

Bom proveito!

Excel – Somando apenas as células visíveis no AutoFiltro

Como complemento do último post, que dava a possibilidade de efetuar operações matemáticas em células filtradas através do VBA, adiciono uma alternativa disponibilizada por um colega do fórum INFO.

Existe uma forma mais simples de fazer operações matemáticas em dados filtrados fazendo uso da função SUBTOTAL.

A descrição da Ajuda do Excel fala por si:

A função SUBTOTAL retorna um subtotal em uma lista ou em um banco de dados. É geralmente mais fácil criar uma lista com subtotais usando o comando Subtotais, grupo Contorno, na guia Dados. Assim que a lista de subtotais for criada, você poderá modificá-la editando a função SUBTOTAL.

Sintaxe

SUBTOTAL(núm_função, ref1, ref2, …)

Núm_função é o número de 1 a 11 (incluindo valores ocultos) ou 101 a 111 (ignorando valores ocultos) que especifica qual função usar no cálculo de subtotais dentro de uma lista.

Núm_função
(incluindo valores ocultos)
Núm_função
(ignorando valores ocultos)
Função
1 101 MÉDIA
2 102 CONTA
3 103 CONT.VALORES
4 104 MÁX
5 105 MÍN
6 106 MULT
7 107 DESVPAD
8 108 DESVPADP
9 109 SOMA
10 110 VAR
11 111 VARP

Ref1, ref2 são de 1 a 254 intervalos ou referências cujo subtotal você deseja.

Comentários

  • Se existirem outros subtotais dentro de ref1; ref2,… (ou subtotais aninhados), esses subtotais aninhados serão ignorados para evitar dupla contagem.
  • Para as constantes núm_função de 1 a 11, a função SUBTOTAL inclui os valores de linhas ocultas pelo comando Ocultar Linhas no submenu Ocultar e Exibir do comando Formato no grupo Células, na guia Início). Use essas constantes para subtotalizar números ocultos e não-ocultos em uma lista. Para as constantes núm_função de 101 a 111, a função SUBTOTAL ignora valores de linhas ocultos pelo comando Ocultar Linhas. Use essas constantes para subtotalizar somente números não-ocultos em uma lista.
  • A função SUBTOTAL ignora as linhas não incluídas no resultado de um filtro, independentemente de qual valor de núm_função seja utilizado.
  • A função SUBTOTAL foi projetada para colunas de dados ou intervalos verticais. Ela não foi projetada para linhas de dados nem intervalos horizontais. Por exemplo, quando você subtotaliza um intervalo horizontal usando uma núm_função de 101 ou maior, como SUBTOTAL(109,B2:G2), ocultar uma coluna não afeta o subtotal. Porém, ocultar uma linha em um subtotal de um intervalo vertical afeta o subtotal.
  • Se qualquer uma das referências for uma referência 3D, a função SUBTOTAL retornará o valor de erro #VALOR!.

Exemplo

Portanto, considerando a mesma planilha do post anterior, cuja fórmula mencionada foi:

=Soma_Celulas_Visiveis(C2:C50)

pode ser substituída por:

=SUBTOTAL(9;C2:C50)

A vantagem óbvia é a simplicidade de uso e a dispensa do uso de macros. O post anterior mantém sua utilidade para execução de funções mais complexas em dados não filtrados.

Agradecimentos

Fica meu agradecimento ao usuário Avatar_2010 do fórum INFO de Excel sobre a dica colocada. Valeu garoto!

Bom proveito!

VBA – Somando apenas as células visíveis no AutoFiltro

Dois dos recursos mais poderosos do Excel infelizmente não conseguem trabalhar bem juntos.

O problema reside no fato de que a função SOMA compreende um range de células e inclui em sua soma todo o intervalo. Por exemplo, você possui uma planilha que abrange da linha 2 a linha 50. Aplica uma fórmula SOMA, por exemplo, na coluna C, o que geraria o seguinte:

=SOMA(C2:C50)

Se aplicar um AutoFiltro nesta planilha, perceberá que sua fórmula continua considerando todas as linhas, mesmo as que não estão visíveis. Dependendo da situação, isso pode ser um problema. Não há funções tradicionais para resolvê-lo, porém, uma macro pode ajudar.

Copie e cole esta função personalizada em um módulo VBA.

Function Soma_Celulas_Visiveis(Cells_To_Sum As Object)
    Application.Volatile
    For Each cell In Cells_To_Sum
        If cell.Rows.Hidden = False Then
            If cell.Columns.Hidden = False Then
                Total = Total + cell.Value
            End If
        End If
    Next
    Soma_Celulas_Visiveis = Total
End Function

Agora, substitua a função citada acima pela seguinte:

=Soma_Celulas_Visiveis(C2:C50)

Ou adapte ao range da sua planilha. Agora, aplique o autofiltro de forma que algumas linhas fiquem escondidas. Note que a função considera apenas as células que estão visíveis agora.

Explicando

O segredo da função acima está na seguinte linha:

If cell.Rows.Hidden = False Then

Essa linha verifica se a linha da célula corrente está oculta ou não (propriedade Hidden). Quando o filtro é aplicado, as linhas que não obedecem aos critérios do filtro ficam no estado de Hidden = True.

Espero que a dica seja de grande ajuda.

Bom proveito!

Download da Planilha de exemplo

https://www.tomasvasquez.com.br/downloads/Soma_Celulas_Visiveis.zip

Referências

http://support.microsoft.com/?scid=kb%3Ben-us%3B150363&x=10&y=14