Dicas – 5 dicas estupidamente boas no Excel

Dicas

Admito que gostei desse negócio de dicas. Então, lá vão mais algumas que ajudam um bocado, só que agora no Excel!

1 – Verificando resultados parciais

Quanto você tem fórmulas muito complexas, fica muito complicado analisar o que está acontecendo quando ocorre um erro. O Excel até oferece um mecanismo de execução passo a passo da fórmula, só que mais simples do que isso é usar o atalho F9 (que já é a tecla padrão é cálculo do Excel) para calcular o resultado de um pedaço desta. Por exemplo, se você tem uma fórmula como esta:

=SE(SOMA(A1:A10)>100;”Muito alto”;”Tá valendo”)

Para saber o resultado de SOMA(A1:A10) sem precisar colocar essa fórmula em outra célula, basta ir na barra de fórmulas, selecionar exatamente essa parte da fórmula com o mouse e clicar em F9. Quando fizer isso, será mostrado o resultado da fórmula direto na barra de fórmula. Isso pode ser feito em qualquer parte da fórmula, facilitando muito a análise de resultados parciais e verificação de erros na fórmula.

Só é preciso um cuidado com esse técnica. Quando se clica F9 na barra de fórmulas, ele não faz só um cálculo temporário. O Excel literalmente substitui o valor na fórmula, portanto, se clicar em Enter ou sair da célula logo após a ação, a fórmula que compunha o resultado será perdida, mantendo apenas o valor do resultado. Basta tomar o cuidado de sair da edição da fórmula com a tecla Esc.

2 – Preenchimento automático

Quando se está preenchendo uma lista de valores, o comando Copiar e Colar ajuda bastante. Mas, ter que ficar selecionando a célula para copiar demora ou um pouco, ou esperar o Excel sugerir o valor já existente na lista. Para isso, use o atalho Ctrl+D. Ele ativa o comando Preencher para baixo, copiando o valor da célula logo acima. Simples, rápido, funcional.

3 – Auto Preenchimento sem dor

Não é raro você tem que preencher uma lista de valores em uma tabela. Normalmente usa-se o arrastar e soltar para auto preencher, mas quando a quantidade de linhas é muito grande, a coisa fica chata. Pois bem, se estamos em uma tabela, ao invés de arrastar e soltar, dê um duplo clique no mesmo local utilizado para arrastar e soltar. Voilá! Sua coluna de valores está preenchida!

4 – Auto Preenchimento sem dor – Parte 2

Além do procedimento descrito acima, existe uma outra técnica para auto-preencher uma quantidade de células com um mesmo dado. Supondo que você queira preencher uma coluna inteira com um único valor até a linha 100. OK, o que vem a cabeça primeiro é digitar o valor na primeira linha e usar o arrastar e soltar para auto preencher até a linha 100. Funciona, mas nem sempre o mouse ajuda. Tem um jeito mais sofisticado de fazer isso:

  • Selecione a primeira célula na coluna A por exemplo
  • Clique Ctrl+G. Na caixa Referência, digite A1:A100. Isso fará com que o Excel selecione as células de A1 a A100.
  • Vá na barra de fórmulas e digite o valor deseja, só que ao invés de pressionar Enter para confirmar, pressiona Ctrl+Enter

Pronto, todas as células foram preenchidas com o dito valor. Nem preciso dizer que isso poderia ter sido feito até a linha 10000 certo? Além disso, funciona para mais de uma coluna. Sem dor!

5 – Navegue entre os extremos

É comum trabalharmos com áreas de dados, principalmente no formato de tabela. Quando elas têm muitas colunas ou linhas, fica chato navegar aos “soquinhos” usando as setas de navegação. Além de poder usar a navegação atrás do Ir para (Ctrl+G), você também pode usar Ctrl + Seta para ser levado a última célula preenchida de seu intervalo. Por exemplo, se tem uma tabela com 1000 linhas e 20 colunas preenchidas, em qualquer lugar da tabela, acionar Ctrl+Seta para baixo, você será levado a linha 1000 da planilha. Se fizer Ctrl+Seta para direita, será levado a coluna 20.

O único porém é que se houver intervalos em branco no meio dos dados, o Excel irá parar nele, já que a consideração feita é, “navegue até a última célula preenchida neste intervalo”. Ajuda muito.

Bom proveito!

VBA – Aumente a segurança de seus projetos VBA

 

Logo VBA

Código de autoria do colega Felipe Dasi, colaborador do blog e do nosso fórum.

O conjunto de código abaixo serve para proteger seu arquivo de projeto VBA. Ele faz com que, caso o projeto VBA esteja desprotegido, todo os conteúdo de código dos módulos e planilha sejam excluídos, dificultando o hackeamento de seu aplicativo.

O código abaixo promove a proeza. Os comentários auxiliam na orientações deste e são do próprio autor.

Colocar em Estapasta_de_trabalho:

Option Explicit
 
Private Sub Workbook_Open()
      If Application.Version > 9 Then
            Dim VisualBasicProject As Object
            On Error Resume Next
            Set VisualBasicProject = ActiveWorkbook.VBProject
            If Not Err.Number = 0 Then
 
      End If
      'DESABILITA ESC E INICIA RELOGIO
      Application.EnableCancelKey = xlDisabled
      Run "IniciaRelogio"
      End If
End Sub
 
'
'TODAS AS POSSIBILIDADES
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
      Run "ParaRelogio"
      'PODE COLOCAR CODIGO PROPRIO AQUI
      Run "IniciaRelogio"
End Sub
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      Run "ParaRelogio"
      'PODE COLOCAR CODIGO PROPRIO AQUI
      Run "IniciaRelogio"
End Sub
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
      Run "ParaRelogio"
      'PODE COLOCAR CODIGO PROPRIO AQUI
      Run "IniciaRelogio"
End Sub
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      Run "ParaRelogio"
      'PODE COLOCAR CODIGO PROPRIO AQUI
      Run "IniciaRelogio"
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
      Run "ParaRelogio"
      'PODE COLOCAR CODIGO PROPRIO AQUI'
End Sub

Colocar em um módulo VBA:

'Se a senha em A1 for igual a 123
'Se a senha em Comentários em propriedades da Planilha = 456
'Se a senha do VBA = 123
'RESULTADO O projeto VBA é aberto
'Resumindo, se uma das senha não bater o VBA é apagado
'Se você tirar a senha de A1 e Salvar, a planilha pode ser usada normalmente, mas
' se o usuário quebrar a senha, ao acessar o modulo o VBA serás apagado.
Option Explicit
 
Public IdleTime As Date
 
Private Sub DeletaVBA()
      With ThisWorkbook
            If Plan1.[A1] = "123" _
               And .BuiltinDocumentProperties("Comments") = "456" Then
                  'COLOCANDO A SENHA CORRETA DO VBA E MANTENDO AS OUTRAS DUAS EM A1 E EM PROPRIEDADES O RELOGIO NÃO É ATIVADO.
               'CASO CONTRARIO O RELOGIO É ATIVADO E EM 5 SEGUNDOS O PROJETO VBA SERÁ APAGADO.
                  Run "ParaRelogio"
                  Exit Sub
            Else
                  If .VBProject.Protection = 0 Then   'unlocked
                        Run "ParaRelogio"
 
                        Dim Component As Object
 
                        ' AQUI DELETA TODOS MODULOS DO VBA
                        For Each Component In .VBProject.VBComponents
                              With Component.CodeModule
                                    .DeleteLines 1, .CountOfLines
                              End With
                        Next
 
                        'SALVA AS ALTERAÇÕES FEITAS NA PLANILHA
                        .Save
                        'SE QUISER COLOCAR UMA MENSAGEM
                          MsgBox "SENHA(S) INCORRETA(S) - " & _
                               "TODOS OS MODULOS SERÃO APAGADOS... :o)"
                        Workbooks.Open (.FullName)
                        .Close
                  Else
                        'INICIA A CONTAGEM DO RELOGIO SE VBA FOR QUEBRADO
                        Run "IniciaRelogio"
                  End If
            End If
      End With
      Exit Sub
 
End Sub
 
Private Sub IniciaRelogio()           
      If Plan1.[A1] = "123" _
         And ThisWorkbook.BuiltinDocumentProperties("Comments") = "456" _
         Then Exit Sub
 
            'AQUI É O TEMPO DE ABERTURA DO VBA
      IdleTime = Now + TimeValue("00:00:05")
 
      Application.OnTime IdleTime, "DeletaVBA"
End Sub
 
Private Sub ParaRelogio()
      On Error Resume Next
      Application.OnTime EarliestTime:=IdleTime, _
                         Procedure:="DeletaVBA", Schedule:=False
End Sub

O código e melhorias estão sendo discutidas em nosso fórum:

https://www.tomasvasquez.com.br/forum/viewtopic.php?f=6&t=249&start=10

Bom proveito! Valeu Felipe!

VBA – Excluindo linhas em branco no Excel

De vez em quando a Microsoft é mãe. Além de fornecer a ferramenta (guardadas as devidas proporçõe$), fornece vários exemplos de como resolver os problemas com elas. Seria esse o valor agregado?

Enfim, mas uma dúvida frequente em emails de fóruns é pelo VBA conseguir excluir as linhas que estão em branco ou vazias em uma planilhas. Outros códigos já disponibilizados aqui no fórum já promovem a façanha com um pequeno ajuste. Mas, para quem quer um código mais focado, a própria Microsoft disponibiliza a seguinte Macro:

' The following code deletes blank rows from the active worksheet.
 
Dim Counter
Dim i As Integer
 
Sub DelRow()
 
' Input box to determine the total number of rows in the worksheet.
    Counter = InputBox("Enter the total number of rows to process")
    ActiveCell.Select
    ' Loops through the desired number of rows.
    For i = 1 To Counter
        ' Checks to see if the active cell is blank.
        If ActiveCell = "" Then
            Selection.EntireRow.Delete
 
            ' Decrements count each time a row is deleted. This ensures
            ' that the macro will not run past the last row.
            Counter = Counter - 1
        Else
            ' Selects the next cell.
            ActiveCell.Offset(1, 0).Select
        End If
 
    Next i
 
End Sub

A parte bacana é que o código, ao ser executado, ele mostra um InputBox solicitando a quantidade de linhas que serão processadas, ou avaliadas. É uma boa, pois você pode não querer varrer todas as linhas de uma planilha certo?

O link original é:

http://support.microsoft.com/kb/110759/pt-br

Bom proveito!

Excel – Crie uma planilha de gastos mensais personalizada

Crie uma planilha de gastos mensais personalizada

Essa saiu no UOL hoje. Como ficou muito bacana, resolvi “sharear”.

Além das planilhas de controle financeiro que já são recomendadas neste blog, o pessoal do UOL tecnologia disponibilizou um passo a passo de como montar uma planilha básica de controle financeiro. O que achei interessante foi o formato, pouco usa em tutoriais na internet, que foi o slideshow. Com printscreens das telas e pouco texto, eles mostram como usar os recursos do Excel para montar um planilha de gastos mensais personalizada.

Abaixo o visual do slideshow. Bem auto explicativo:

Crie uma planilha de gastos mensais personalizada

Vale muito a pena conferir. O link para o passo a passo segue abaixo:

http://tecnologia.uol.com.br/album/planilha_de_gastos_excel_album.jhtm

Bom proveito!