Coletânea de Funções para o Excel - Suplemento - v2.30g

Pessoal, bom dia!

Depois de um longo período sem atualizar o Blog, estou disponibilizando uma nova versão do nosso Suplemento!

Além da inclusão de novas funções e correção de bugs, agora o suplemento adiciona uma aba no Excel chamada "Add-In Macros", onde temos dois botões. Segue abaixo uma imagem da aba criada.

Ao clicar no botão "Sobre", abre-se uma janela com um índice das funções implementadas no Suplemento e os seus respectivos atalhos. O outro botão corresponde à uma ferramenta que batizei de "Easy PPT", cuja utilidade é automatizar o trabalho de copiar e colar dados como imagem do Excel para apresentações no Power Point. Seu uso é bastante simples e está ilustrado a seguir.

Funcionamento do Easy PPT

A ferramenta funciona da seguinte forma: uma vez executada, ela irá percorrer todas as abas do arquivo aberto (da esquerda para a direita) e buscar as palavras "PPT_START" e "PPT_END" em cada uma. Se a aba contiver estas duas palavras, a ferramenta entenderá que a aba tem alguma informação que deve ser inserida em um slide. Juntamente com estas palavras devem ser indicadas as células inicial e final da área a ser copiada. Por exemplo, para copiar a área da célula A1 até a célula I10, teríamos o seguinte:

PPT_START: A1
PPT_END: I10

Obs.: 1) Após as palavras, inserir um ": " (dois pontos e espaço), e então indicar as células; 2) Usar uma célula para cada instrução; 3) As palavras podem ser inseridas em qualquer célula da aba, uma vez que a busca é feito em todas as células de cada aba.

Ao clicar no botão "PPT me!", uma janela se abre questionando se as informações devem ser inseridas em um novo arquivo ou em um arquivo já existente. Segue abaixo uma imagem da tela de questionamento:

Selecionar "Sim" abrirá uma janela para selecionar uma apresentação existente para incluir a informação. Clicar em "Não" fará com que automaticamente se abra um novo documento do Power Point e as informações serão inseridas nele.

Há outros parâmetros que podem ser especificados e que também são procurados em todas as abas. São eles:

Slide a receber a informação*: PPT_SLIDE: 2
Largura da imagem transferida**: PPT_WIDTH: 300
Altura da imagem transferida**: PPT_HEIGHT: 300
Distância da imagem transferida a partir do topo do slide***: PPT_TOP: 25
Distância da imagem transferida a partir do lado esquerdo do slide***: PPT_LEFT: 25

* Caso o slide não seja informado ou haja no arquivo indicado um número menor de slides do que o slide informado, a imagem será transferida para um novo slide ao final do arquivo;
** Caso a largura e a altura sejam omitidas, a função irá forçar o parâmetro 620 em PPT_WIDTH, visando preencher horizontalmente o slide. Caso ambas estejam especificadas, apenas a WIDTH será usada, uma vez que a proporção entre largura e altura originais serão mantidas (desta forma, para forçar uma altura, não se deve inserir o parâmetro WIDTH);
*** Caso não sejam indicados os parâmetros TOP e LEFT, por padrão eles serão iguais à 50.

Os valores inseridos devem sempre ser inteiros. No slide, cada unidade de medida unitária inserida no Excel equivale a aproximadamente 0,0353 cm.

Exemplo de Uso 1

Segue abaixo um exemplo de utilização. Digamos que os seguintes parâmetros estejam inseridos em um slide:

Marquei em amarelo as células inicial e final selecionadas. Ao clicar em "PPT me!" e depois em "Não", teremos que a área entre as células serão transferidas para o PPT, conforme figura a seguir:

Como você pode perceber, as linhas de grade ficaram aparentes na imagem transferida (a imagem é transferida como um Metarquivo Avançado). Para eliminar este efeito, podemos voltar no Excel, ir na aba "Exibição" e desmarcar a opção "Linhas de Grade". O Excel irá então aparentar como segue:

E no slide não irão mais aparecer as linhas de grade:

Exemplo de Uso 2

Com esta ferramenta é possível deixar 100% da atualização de uma apresentação periódica, por exemplo, implementada através do Excel (inclusive a capa). Por exemplo, podemos ter um relatório cujo modelo de capa (slide 1) seja como segue:

No Excel, preparamos então uma aba com as informações que devem ser inseridas no Slide. A vantagem é que a data atual ou outras informações que necessitem atualização podem ser incluídas com fórmulas facilmente e eliminar o trabalho de atualização manual. Como as informações são transferidas como figuras, também são eliminados os problemas com vínculos entre arquivos. Segue exemplo abaixo:

Agora clicamos em "PPT me!" e depois clicamos em "Sim" para inserir a informação no nosso arquivo modelo. Após selecionar a apresentação, ela automaticamente será aberta e a informação será transferida para o slide indicado. Segue imagem da apresentação após a transferência:

Bom, é isso. Espero que o suplementos seja útil!
Para contato, usar o email: guiamacroexcel@gmail.com.

Seguem os links para download do novo suplemento e do guia de instalação:

Suplemento + EasyPPT - v2.30g

Manual de Funções

Guia de Instalação

Obrigado e até a proxima!

Abrir Janela para Selecionar Arquivo no VBA

Boa noite, pessoal!

Recentemente tive de criar uma ferramenta em Access onde o usuário deveria indicar o caminho de uma planilha para importar no banco de dados. O caminho do arquivo deveria ser flexível e fácil de alterar, e a melhor maneira de fazê-lo pareceu ser então com as já conhecidas telinhas de seleção de arquivos do Windows.

Após alguma leitura na internet, encontrei a codificação necessária. É bastante simples, mas muito útil no desenvolvimento de soluções e basicamente não há alterações para uso em ambas as ferramentas (Excel/Access). Segue o código comentado:



Function AbrirArquivo()

    Dim Caminho As String 'Caminho do arquivo
    Dim fDialog As Office.FileDialog
    
    'Configura caixa de seleção do arquivo
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
        .AllowMultiSelect = False 'Habilita ou desabilita a seleção de múltiplos arquivos
        .Title = "Selecionar arquivo..."
        '.InitialFileName =  'Caminho inicial para seleção, não utilizado no exemplo
        
        'Configura filtros da caixa de seleção
        .Filters.Clear 'Limpa os filtros
        .Filters.Add "Arquivos Excel - .xlsb", "*.xlsb" 'Adiciona filtro para arquivos .xlsb
        .Filters.Add "Arquivos Excel - .xls", "*.xls" 'Adiciona filtro para arquivos .xlsb
        
        If .Show = True Then    'Se o parâmetro .Show for igual à True significa
                                'que algum arquivo foi selecionado
            Caminho = .SelectedItems.Item(1)    'Local + arquivo selecionados são passados para
                                                'a variável chamada de "Caminho"
        Else
            MsgBox "Você clicou em cancelar"
        End If
        
    End With

    AbrirArquivo = Caminho 'Atribui o caminho do arquivo ao retorno da função

End Function

Caso chamada dentro de uma célula do Excel, a função acima irá abrir a janela para seleção do arquivo e retornar o caminho indicado acrescido do nome do arquivo! Por exemplo, ao selecionar o arquivo "Exemplo.xlsb" na raiz do disco rígido "C:\", a função irá retornar a string "C:\Exemplo.xlsb". Ela também pode ser chamada diretamente de outra subrotina.

É isso, pessoal... Qualquer dúvida, sugestões ou consultoria, por favor escrevam para guiamacroexcel@gmail.com!

Obrigado e até a próxima!

Dica: Calcular Range

Pessoal, boa noite!

Hoje vou compartilhar um código simples mas muito poderoso e que tem me auxiliado bastante no desenvolvimento de planilhas que trabalham com grande volume de cálculo.

Mesmo em arquivos que não ocupam muito espaço na memória, um volume elevado de funções pode deixar o cálculo muito lento. Aguardar 40 segundos, por exemplo, quando alguma nova função é implementada no arquivo pode ser extremamente oneroso ao longo de qualquer desenvolvimento.

Lendo o artigo presente neste link (leitura interessante), encontrei algumas dicas para otimização de performance. Também encontrei uma referência ao método "Range.Calculate", que permite calcular apenas uma área selecionada na planilha em uso! Este método pode ser chamado apenas através do VBA, o que pode ser implementado com a inserção da rotina abaixo:


Sub CalculaRange()

'Macro para calcular apenas as células no Range selecionado da planilha.

    Dim rng As Range
    Set rng = ActiveSheet.Range(Selection.Address(False, False)) 'Passa para a variável 'rng' o Range selecionado
        
    On Error Resume Next
    Application.Calculation = xlManual 'Muda modo de cálculo para manual
    rng.Calculate 'Calcula apenas o Range selecionado

End Sub

Uma outra dica: com o cálculo manual ativado, é que é possível calcular apenas as abas selecionadas usando "SHIFT + F9".

Estas funções são poderosas e muito úteis, mas devem ser usadas com cautela. Como nem sem o arquivo inteiro é recalculado, pode ser que o resultado visualizado na tela não seja igual ao do cálculo completo do arquivo.

Aos que se interessarem, recomendo também a leitura deste artigo que tem muita informação interessante.

Bom, é isso... Espero que tenham gostado!

Até a próxima!

Coletânea de Funções para o Excel - Suplemento - v2.17

Pessoal,

Estou disponibilizando no blog uma coletânea de funções, em um suplemento do Excel.

Tenho criado estas funções para facilitar o uso das planilhas no dia a dia. As funções demonstradas no blog estão incluídas, entre muitas outras. São três arquivos: o suplemento em si, um guia com as funções e seus atalhos e um guia de instalação. É tudo bastante simples, são apenas alguns passos para poder utilizar as funções. Seguem os arquivos:

Suplemento - v2.17

Guia com as Funções - v2.17

Guia de Instalação

Até a próxima!

Macro para Preencher Lacunas e Colar Valores/Fórmulas

Boa noite!!

Segue outra macro básica que pode ajudar no dia-a-dia.

Diversas vezes utilizei uma Tabela Dinâmica para resumir dados de grandes tabelas de maneira prática e rápida. Após reorganizar os dados conforme desejado na Tabela Dinâmica, basta copiar a colar valores na aba e teremos uma nova tabela mais resumida.

Entretanto, este método pode gerar tabelas com lacunas por conta da função de resumir, como no caso da tabela abaixo:

No caso de precisarmos utilizar alguma chave nesta nova tabela ou mesmo criar outra Tabela Dinâmica a partir dela, teremos de preencher manualmente os espaços vazios. Há métodos para se fazer isso, que demandariam algum trabalho, mas neste caso vamos usar a seguinte macro para auxiliar no preenchimento:


Sub Preenche()

'
' Macro para preencher as linhas em branco entre linhas preenchidas,
' copiando o valor das células preenchidas imediatamente acima.
' Selecionar a área a ser preenchida e executar a macro.
'
    Dim rng As Range, str As String
    
    Set rng = ActiveSheet.Range(Selection.Address(False, False)) 'Coleta o "Range" selecionado

    On Error Resume Next 'Ignora possíveis erros
    For k = 1 To rng.Areas.Count 'Percorre todas as áreas selecionadas
        For i = 1 To rng.Areas(k).Columns.Count
            str = ""
            For j = 1 To rng.Areas(k).Rows.Count
                
                '"Varre" verticalmente cada célula das áreas
                'selecionadas, e verifica o conteúdo. Se for diferente
                'de vazio, armazena o conteúdo na variável "str" e preenche
                'as células vazias posteriores com o conteúdo desta variável.
                'O conteúdo de "str" é sobrescrito a cada vez que uma célula
                'com conteúdo diferente de vazio é encontrada.
                
                If rng.Areas(k).Cells(j, i).Value <> "" Then
                    str = rng.Areas(k).Cells(j, i).Value
                Else
                    rng.Areas(k).Cells(j, i).Value = str
                End If

            Next
        Next
    Next

End Sub

Agora, selecionando a tabela conforme a figura abaixo:

E aplicando a macro, teremos a tabela preenchida conforme a figura que segue:

E o preenchimento está pronto!

Colando Valores e/ou Fórmulas

Falando em copiar e colar valores, fiz uma macro para colar valores e uma para colar fórmulas que têm sido de grande utilidade. Configurei os atalhos como Ctrl + Shift + V (colar valores) e Ctrl + F (colar fórmulas). Assim fica bem mais rápido colar os valores e/ou fórmulas quando desejado. As macros são bem simples, conforme abaixo:


Sub Colar_Valores()
'
' Cola valores
'
    On Error Resume Next
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub


Sub Colar_Fórmulas()
'
' Cola Fórmulas
'
    On Error Resume Next
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub


Bom, chega ao fim mais um post. Espero que seja útil, leitor!
Dúvidas e/ou sugestões, escrevam para: guiamacroexcel@gmail.com.

Obrigado e até a próxima!

Macro Para Exportar Planilha

Boa noite, pessoal!

Segue uma macro simples que tenho usado no dia-a-dia.

O código serve para exportar a aba que está ativa para um novo arquivo do Excel.
Primeiramente é criada uma cópia da aba selecionada e, depois, são copiados e colados valores nesta nova aba. Por último, esta aba replicada com valores é exportada.
O arquivo sai pronto para ser enviado por e-mail, por exemplo, sem risco de enviar arquivos com vínculos que podem impedir ou dificultar seu uso em outra máquina.


Sub Exportar()

    Application.DisplayAlerts = False 'Desabilita alertas
        
    ActiveSheet.Copy After:=Worksheets(Worksheets.Count) 'Cria cópia da aba selecionada
        
    Cells.Select 'Seleciona todas as células da nova aba
    Selection.Copy 'Copia conteúdo
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False 'Cola valores
    Application.CutCopyMode = False 'Desabilita modo de cópia

    ActiveWindow.SelectedSheets.Move 'Exporta aba selecionada para novo arquivo

    Application.DisplayAlerts = True 'Reabilita alertas

End Sub


Bom, é isso. Espero que gostem.

Para dúvidas e/ou sugestões, por favor, escrevam para guiamacroexcel@gmail.com.

Até a próxima!

Função para Auxiliar Consultas no Access

Pessoal,

Segue função que fiz para auxiliar a criação de consultas no Access com critérios do tipo "[Variável] Ou [Variável] Ou [Variável] Ou ...". Antes, quando eu tinha um conjunto de variáveis a serem filtradas em uma consulta, era necessário executar um procedimento manual para preenchimento dos "Ou"s entre as variáveis. Usando a fórmula, o trabalho fica menor e muito mais rápido. Segue o código:


Function Consulta(ByVal Space As Range)

'
'
'   Função para gerar uma chave com conteúdos das células separados por " Ou ",
' para usá-las em consultas no Access.


    Dim rng As Range, str As String
    
    str = ""
    
    On Error Resume Next
    For Each rng In Space.Areas
        For Each cell In rng
            str = str & cell.Value & " Ou "
        Next
    Next
    
    Consulta = Left(str, Len(str) - 4)
    
End Function

Durante o uso da função, cabe uma observação: Para seleções múltiplas (mais de uma área - segurando Ctrl durante a seleção), usar um parêntesis adicional, indicando que todas as áreas devem ser associadas

à uma única variável do tipo "Range", acessada através de rng.Areas(n), onde n é o índice da área. Por exemplo: =Consulta(Área) ou =Consulta((Múltiplas_Áreas)). Seguem imagens ilustrativas dos dois casos, com uma única área e múltiplas áreas, respectivamente:

O código também serve como base para a criação de outras funções para gerar chaves de consulta automaticamente, por exemplo do tipo "<> [Variável] E <> [Variável] E <> [Variável] E <> ...".

Bom, é isso. Espero que tenham gostado.

Para dúvidas e/ou sugestões, por favor, escrevam!

Até a próxima!