Tabela no Excel

Um dos recursos bem interessantes do Excel é a utilização de tabelas dentro da planilha.
Para converter um intervalo (ou range) em tabela, selecione-a e clique no menu Inserir e na faixa de opções, selecione "Tabela".
Nomes de colunas serão criados automaticamente caso não haja.
A partir daí, surgem as vantagens:
- Ao inserir uma soma no final de alguma coluna, novas opções serão exibidas pelo Excel: média, contagem, máx, mín, etc.;
- Ao rolar a tabela para cima, os cabeçalhos da tabela se fixam no cabeçalho de linhas;
- O autofiltro aparece automaticamente nos cabeçalhos;
- A área da tabela já recebe uma nova formatação que facilita a visualização;
- Fórmulas ficam mais fáceis de interpretar (vou abordar isso em outro post futuro);
- Muitas outras opções a mais.

Caso queira voltar a tabela para um intervalo comum, clicar em alguma célula da tabela, ir no meu Design e na faixa de opções, clicar em Converter em intervalo.

Nas primeiras vezes que se utiliza o recurso, pode parecer um pouco complicado, mas rapidamente você se acostuma e percebe que é um recurso muito útil, fácil de utilizar e que oferece muitas opções interessantes.

;-)

Substituindo o antigo arquivo *.bat

Antigamente usávamos os arquivos do tipo "batch" que tinham vários comandos e funcionavam como se fosse executáveis.
O uso era muito comum nas tarefas de automatização.
Hoje temos os scripts que podem executar uma gama de tarefas muito maior, como por exemplo, abrir um banco de dados Access e executar consultas e macros.
A sintaxe dos comandos é muito parecida com o VBA, quem já programa nessa linguagem, não terá dificuldades com os scripts.
Para criar um script, basta digitar o(s) comando(s) num bloco de notas e salvar com extensão *.vbs.
Para executar, é só dar um duplo clique ou acioná-lo pelo agendador de tarefas (o que é mais usual).
Vejam um exemplo bem simples de script para copiar arquivo:

Const OverwriteExisting = TRUE

Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFile "C:\FSO\ScriptLog.txt" , "D:\Archive\", OverwriteExisting


Indico abaixo 2 sites excelentes para aprender mais sobre os scripts:

http://www.macoratti.net/wsh_1.htm
https://www.juliobattisti.com.br/tutoriais/ricardosilva/wsh001.asp

;-)

Dividindo uma amostra em partes iguais (ou o mais próximo disso)

Numa base de CNPJ's relativamente grande (acima de 100 mil registros), é comum se dividir a base para aplicar alguma estratégia de negócios diferentes para cada grupo e depois efetuar o comparativo para ver qual deles gerou melhor resultado.
Uma das formas práticas e rápidas para dividir a base é utilizar o random digit, utilizando para isso, o 6º e 7º dígito de uma  raiz do cnpj com 9 dígitos para identificar o registro e depois classificá-los conforme a necessidade.
Segue abaixo o código para se obter os dígitos e depois basta separar o público.

Public Function fn_randomDG(cnpj As Double) As Integer

    Dim v_cnpj As String
    Dim d6 As String
    Dim d7 As String

    v_cnpj = cnpj   

    d6 = Mid(v_cnpj, 6, 1)
    d7 = Mid(v_cnpj, 7, 1)   

    fn_randomDG = Val(d6 + d7)

End Function

Neste exemplo, dividi uma base de 200 mil registros em 4 partes com random digit de
0 a 25, 26 a 50, 51 a 75 e 76 a 99.
Veja que o resultado são 4 bases de quantidades de registros bastante próximos:

 50.920
 49.972
 49.739
 49.364

A base usada para testes foi baixada no site da Receita Federal, são CNPJ's válidos e disponibilizados para qualquer pessoa fazer o download:
https://www.fazenda.pr.gov.br/modules/conteudo/conteudo.php?conteudo=109

Não vou entrar no detalhe do random digit pois é um assunto no qual não sou especialista, portanto, sem propriedade nenhuma para escrever aqui como foi desenvolvido e a aplicabilidade em outros tipos de análises.
Se você tiver interesse e paciência, procure no Google ou converse com algum estatístico (essa segunda opção, eu acho mais interessante...)

Até a próxima!
;-)

Principais teclas de atalho no ambiente do VBA

A utilização de teclas de atalho aumenta bastante a agilidade quando se programa em VBA, principalmente na fase de depuração do código.
Quem me conhece, sabe que não sou muito fã do mouse e prefiro atalhos por proporcionar acesso direto a recursos, sem precisar clicar em vários itens até chegar onde se precisa.
Segue abaixo a lista dos atalhos que mais utilizo, experimentem.

;-)


CTRL+R = Exibir o Projetc Explorer
CTRL_G = Exibir a janela de verificação imediata
F2 = Exibir o pesquisador de objetos
F5 = Executar o código inteiro
F8 = Executar o código passo a passo
F9 = Ativar/desativar ponto de interrupção
CTRL+Shift+F9 = Desativar TODOS os pontos de interrupção
CTRL+Break=Pausar a execução do código

Tabela dinâmica para iniciantes

Para quem quiser aprender tabelas dinâmicas no Excel, sugiro um curso muito bom que fiz na Udemy com o Cristian Firmino.
Vocês aprenderão de forma fácil e rápida para que serve, quando e como utilizar uma tabela dinâmica que é um dos recursos mais versáteis do Excel para análise e apresentação de dados.
Segue o link, esse eu recomendo:

https://www.udemy.com/course/excel-analise-dados-tabela-dinamica-cristian-firmino/

;-)

Posicionando o cursor na tela com o VBA

Em vários programas tive esse problema: ao selecionar uma célula pelo VBA, o cursor vai até a célula, porém a tela não rola e as vezes o cursor fica fora da área visível da planilha, apesar de selecionada.

Segue o comando para contornar esse problema e deixar sempre a célula selecionada no topo da tela.

Experimentem o comando na janela de verificação imediata do VBA para ver a diferença.

;-)

range("Z200").Select 

Application.Goto Reference:=Worksheets("plan1").Range("Z200"), scroll:=True

Copiando texto de imagem

Antigamente tínhamos vários softwares do tipo "OCR" - Optical Character Recognizer que extraiam textos de imagens, muitos com índice de acerto baixo (minha opinião) dado que as imagens precisavam ter uma resolução muito boa.

O OneNote que faz pate do pacote Office já tem esse recurso.
Conheço poucas pessoas que utilizam esse programa, mas para quem tiver curiosidade, eu recomendo, pois ele é muito útil para anotações diversas nas quais podem ser inseridos imagens, arquivos, textos, prints de telas, etc.

Um programa semelhante e muito útil que também recomendo, é o Keep do Google, vale a pena dar uma olhada para conhecer.

Bem, para extrair o texto pelo OneNote é bem simples:
Basta inserir a imagem em alguma anotação, clicar com o botão direito e selecionar "Extrair texto da imagem".
Obviamente, o texto não será 100% reconhecido, mas dependendo da imagem, o índice de acerto será muito bom.

;-)

Pendrive Sony ou Multilaser?

Em qual marca você confia mais, Sony ou Multilaser?

;-)

Programa para particionar discos

Segue dica de um programa leve e o que é melhor, gratuito, para particionar discos.
Funcionamento muito parecido com o famoso Partition Magic.
Para quem precisa só para tarefas básicas, resolve muito bem.

https://www.minitool.com/partition-manager/partition-wizard-home.html

No site do desenvolvedor, tem diversas outras ferramentas para recuperação de dados em várias mídias, vale a pena dar uma olhada.

;-)

Formatar rapidamente uma planilha

No meu trabalho, todos os dias exporto dados de outros aplicativos para o Excel, seja para análise de dados ou para envio a outros colegas e quase sempre as colunas estão na largura padrão fazendo com que as informações fiquem sobrepostas ou truncadas.
Nada complicado, basta ajustar a largura das colunas e algumas vezes, a altura da linha e tudo bem, mas como no meu caso é repetitivo, crei uma macro que faz isso rapidamente.
Ganho muito em produtividade e os destinatários das minhas planilhas também.
A macro em si é muito simples, mas fica a dica para ganhar tempo em pequenas tarefas recorrentes, que somados, representam um ganho significativo no fim do dia.

Sub AjustaColunas()
'
' Formata Macro
'
' Atalho do teclado: Ctrl+Shift+F
'
    Cells.Select
    Selection.RowHeight = 15
    Cells.EntireColumn.AutoFit
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    
End Sub

Ler arquivo dentro de uma pasta que está dentro de um arquivo ZIP

O código não é meu, vou deixar o link aqui para futuras consultas.
Utilizei recentemente e funciona sem problemas.

;-)

Original em:

http://www.vbforums.com/showthread.php?815681-List-files-from-zip-file-(vba)

Private Sub CommandButton1_Click()
    zpath
End Sub


Sub zpath()
    Dim sh, n
    Dim PathFilename As Variant
    
    Dim FileName As String
    FileName = "temp.sql"
    
    PathFilename = Application.GetOpenFilename("ZipFile (*.zip), *.zip")
    If PathFilename = "False" Then Exit Sub
     
    TextBoxPath.Value = PathFilename
    
    Set sh = CreateObject("shell.application")
    Set n = sh.Namespace(PathFilename)
    recur sh, n
End Sub

Sub recur(sh, n)
    Dim i, subn
    For Each i In n.items
        If i.isfolder Then
            Set subn = sh.Namespace(i)
            recur sh, subn
            Else
            Debug.Print i.Path
        End If
    Next
End Sub

Separar conteúdo de célula em linhas

Para isto, basta determinar onde está o caracter ASC(10) que é  Line Feed e depois separar em linhas conforme a quantidade de caracteres entre os Line Feeds.

Abaixo um exemplo.

Option Explicit

Sub Separa()

    Dim i               As Integer
    Dim x               As Integer
    Dim inicio          As Integer
    Dim contador        As Integer
    Dim caracteres      As Integer
    
    ReDim posicao(50) As Integer
    ReDim texto(50) As String
    
    'Determinar onde estão as quebras de linha
    For i = 1 To Len(Range("A1"))

        If Asc(Mid(Range("A1").Value, i, 1)) = 10 Then
            posicao(contador) = i
            contador = contador + 1
        End If
               
    Next
    
    'Quebrar o texto
    inicio = 1
    caracteres = posicao(0)
    
    'Primeiro
    Debug.Print Mid(Range("A1").Value, inicio, caracteres - 1)
    inicio = posicao(x)
    caracteres = posicao(x + 1) - posicao(x)


    'Segundo em diante
    caracteres = posicao(1) - posicao(0)
    For x = 1 To contador
    
        'Último texto
        If x = contador Then
            caracteres = Len(Range("A1").Value)
            texto(x) = Mid(Range("A1").Value, inicio + 1, caracteres - posicao(x) - 1)
            Debug.Print texto(x)
            Exit For
        End If
    
        texto(x + 1) = Mid(Range("A1").Value, inicio + 1, caracteres - 1)
        Debug.Print texto(x + 1)
        
        inicio = posicao(x)
            
        caracteres = posicao(x + 1) - inicio
        
    Next
 
End Sub

Facas e canivetes podem te levar para a cadeia! - No fio da navalha Ep.23

Não tem nada a ver com o escopo deste blog, mas isto precisa ser divulgado.

Eliminar a última tela preta da apresentação

Para evitar a tela preta tradicional no final de uma apresentação de slides no Powerpoint, basta entrar em Arquivo > Opões > Avançado > Apresentações de slides e desmarcar a opção "terminar com slide preto".

;-)

Alinhando texto no Word

Esta é uma técnica largamente utilizada na elaboração de contratos no Word.
Trata-se de uma tabela que conterá texto em suas células onde o alinhamento (esquerda, direita, centro, superior, inferior, etc.) funciona igual ao Excel, com a qual a maioria das pessoas já está familiarizada.
Após o alinhamento efetuado, basta deixar as linhas das bordas da tabela invisíveis, vejam o resultado abaixo.

;-)

Preenchendo tabela no Word com VBA

Automatizar tarefas no Word não é algo muito comum, desde que comecei a programar no Office, vi muito pouca coisa nesse sentido.
Para registro, vou postar alguns códigos para que sirva de aprendizado para quem está começando e para consultas aos que já utilizaram e esquecem de vez em quando (como eu...).
Abaixo 3 formas de se referenciar células de uma tabela no Word, lembrando que o uso de tabelas é muito útil principalmente em documentos que devem ser preenchidos como contratos, recibos, etc.


;-)

Option Explicit

Sub PreencheCelula()

    ActiveDocument.Tables(1).Rows(1).Cells(2).Range.Text = "Linha 1, coluna 2"
    
    ActiveDocument.Tables(1).Cell(2, 2).Range.Text = "Linha 2, coluna 2"

    ActiveDocument.Tables(1).Columns(2).Cells(3).Range.Text = "Linha 3, coluna 2"

End Sub

Cálculo de dígito verificador - módulo 10

O código do módulo 11 está perdido em algum lugar no blog, vou deixar aqui o cálculo do módulo 10.

;-)

Public Function modulo_10(sOrigem As String) As String
' Função para cálculo do dígito de auto conferência
    Dim iK          As Integer ' Contador
    Dim iValor      As Integer ' Variável para acumular valor
    Dim iDigito     As Integer ' Variável para o conteúdo de cada posição
 
    iValor = 0
    For iK = Len(sOrigem) To 1 Step -2      ' Inicia o laço começando da direita para a esquerda pulando 2 posições
        iDigito = (Val(sOrigem) \ (10 ^ (Len(sOrigem) - iK))) Mod 10
       
        If (iDigito * 2) >= 10 Then ' Se a multiplicação for maior ou igual a dez, soma-se os dígitos separadamente
            iValor = iValor + (((iDigito * 2) \ 10) + (iDigito * 2) Mod 10)
        Else ' Senão, multiplica-se e soma-se ao total
            iValor = iValor + (iDigito * 2)
        End If
       
        If (iK - 1) <= Len(sOrigem) Then
            ' Se não ultrapassar otamanho da string, soma-se ao total
            iDigito = (Val(sOrigem) \ (10 ^ (Len(sOrigem) - (iK - 1)))) Mod 10
            iValor = iValor + iDigito
        End If
    Next iK
 
    modulo_10 = Trim(Str((10 - IIf((iValor Mod 10) = 0, 10, (iValor Mod 10)))))
    ' Retorna o DAC
End Function

Como deixar o Google Maps mais rápido

As vezes o Google Maps fica irritantemente lento e para isso segue uma dica.
No link do Maps, acrescente uma vírgula e "force=lite" conforme abaixo:

https://www.google.com.br/maps/@-23.5679681,-46.5403448,15z?hl=pt-BR,force=lite

Obviamente que os cuidados básicos devem ser tomados como limpeza de arquivos temporários, cookies, etc.

;-)

Somente números numa caixa de texto

Método para não permitir a digitação de caracteres não numéricos numa caixa de texto:

Private Sub Texto1_KeyPress(KeyAscii As Integer)

    If InStr(1, "0123456789" & vbKeyDelete & Chr(8), Chr(KeyAscii)) = 0 Then
    KeyAscii = 0
    End If

End Sub

;-)

Extraindo só números de uma célula da planilha

Deve haver dezenas de maneiras diferentes de fazer, vou deixar registrada aqui uma delas.

;-)

Public Function fn_SoNumeros(texto As String) As Double

    Dim i As Integer
    Dim valor As String
    
    Application.Volatile
    
    'Loop de 1 a quantidade de caracteres da célula
    For i = 1 To Len(texto)
        
        'Se caracter for numérico, concatena na string
        If IsNumeric(Mid(texto, i, 1)) Then valor = valor & Mid(texto, i, 1)
    
    Next
    
    fn_SoNumeros = valor

End Function