DONUT PROJECT - Use os add-ins do Excel e dê um salto em sua performance

DONUT PROJECT - Use os add-ins do Excel e dê um salto em sua performance

Ultimamente tenho recebido demandas externas, comprado add-ins e vendendo a solução a quem me solicitou sem a necessidade de desenvolver algo com o VBA. 

O que ganho? Tempo! 

E posso atender a muito mais pessoas. Outro aspecto relevante a considerar é o de que estes complementos permitem-me desenvolver Dashboards, Scorecards mais rapidamente.

Quando desenvolvia em VB, a cerca de mais de 14 anos, fiquei muito contente por descobrir a componentização, ou seja, a possibilidade de reutilizar códigos, componentes de terceiros.

​Não há dúvida sobre como o MS Excel é uma ferramenta poderosa. No entanto, ela tem as suas limitações. E é aí que os add-ins (suplementos) entram em jogo. A Microsoft tem sido gentil o suficiente para abrir a sua base de códigos e permitir que desenvolvedores de terceiros estendam as funcionalidades do Excel. Esses suplementos tornam as análises e as manipulações de dados mais fáceis.

Abaixo disponibilizei uma lista breve, mas útil, de suplementos pagos e (principalmente) gratuitos do Excel que poderão ajudá-lo em qualquer esforço adicional que precise.

ANÁLISE DE DADOS

Apesar deste Blog ser escrito em português, sempre verifico o crescente aumento de visitações vindas de outros países, portanto é importante disponibilizar acesso às suas bases de dados 

Ferramentas de Análises não instaladas.

Usar Ferramentas de Análise para executar análises de dados complexas

O Excel Solver é uma ferramenta clássica que exige um desempacotamento adicional. Carregar o Solver

Business intelligence - Ao ampliar o poder das PowerPivots, o Excel atende a necessidade do uso de ferramentas de inteligência de negócio.

O Data Explorer é a mais nova ferramenta de análise de dados e de visualização disponível para download.

Acessar dados do Federal Reserve com o suplemento  FRED.

Obtenha dados do US Crime Ests em Data.gov.

Use o SimTools e o FormList para adicionar funções e procedimentos estatísticos, realizando simulações de Monte Carlo e Análises de Risco. 

Tem necessidade de fazer algumas previsões? Com este suplemento poderá usar vários métodos, incluindo forecasting média móvel, suavização exponencial, regressão e suavização exponencial dupla com um modelo para experimentar.

Necessidade de gerar alguns números aleatórios? Confira este Random Number Generator.

Se este breve artigo te deixou interessando, verifique outros add-ins pagos e gratuitos, experimente a Microsoft App Store,  Add-Ins.com, e o OzGrids. 

Muitas ferramentas de análise de dados e plataformas de software têm seus próprios add-ins, como Tableau, MATLAB, e o Bloomberg por isso, se estiver trabalhando com uma plataforma semelhante é possível que seu provedor tenha um add-in para você. 

Se gosta de add-ins por favor, compartilhe este artigo com os seus amigos e marque o nosso blog para voltar periodicamente para atualizar a sua lista de coisas novas e interessantes.

André Luiz Bernardes

Professional Profile

DONUT PROJECT - VBA - Criando uma Matriz de Datas MAT - Moving Annual Total

DONUT PROJECT - VBA - Criando uma Matriz de Datas MAT

​

Em diversas ocasiões usamos tabelas com períodos MAT, geralmente estas são extraídas de BIs (Business Information). Os cabeçalhos nem sempre são aquilo que desejaríamos usar.

​

Como podemos conciliar o conteúdo, adequando os títulos?

As funções abaixo lhe permitirão carregar (LoadMonths()) em uma Matriz, 12 meses, sendo o primeiro uma data passada e os próximos 11 meses serão calculados retroativamente. Depois poderão recuperar (ReturnMonth()) estas datas.

Global nMeses(12) As Date

Sub LoadMonths()

    '      Author: André Bernardes

    '        Date: 20/08/14 - 09:53

    '      Action: Cria tabelas de Regionais para análise.

    ' Application: Analysis****Regional®

    '   Test line: nMeses (1), nMeses(2), nMeses(3), nMeses(4), nMeses(5), nMeses(6), nMeses(7), nMeses(8), nMeses(9), nMeses(10), nMeses(11), nMeses(12)

    '   Test line: Debug.Print ReturnMonth(1), ReturnMonth(2), ReturnMonth(3), ReturnMonth(4), ReturnMonth(5), ReturnMonth(6), ReturnMonth(7), ReturnMonth(8), ReturnMonth(9), ReturnMonth(10), ReturnMonth(11), ReturnMonth(12)

    Dim i As Integer

    Dim Flag As Boolean

    Dim LastDate As Date

    Let Flag = True

    For i = 1 To 12

        If Flag Then

            Let nMeses(i) = Sheets("Analise").Range("I5").Value

            Let LastDate = nMeses(i)

            Let Flag = False

        Else

            Let nMeses(i) = DateAdd("m", -1, LastDate)

            Let LastDate = nMeses(i)

        End If

    Next

End Sub

Function ReturnMonth (nMnth As Integer) As String

    '      Author: André Bernardes

    '        Date: 20/08/14 - 09:53

    '      Action: Cria tabelas de Regionais para análise.

    ' Application: AnalysisMDTRRegional®

    '   Test line: nMeses (1), nMeses(2), nMeses(3), nMeses(4), nMeses(5), nMeses(6), nMeses(7), nMeses(8), nMeses(9), nMeses(10), nMeses(11), nMeses(12)

    '   Test line: Debug.Print ReturnMonth(1), ReturnMonth(2), ReturnMonth(3), ReturnMonth(4), ReturnMonth(5), ReturnMonth(6), ReturnMonth(7), ReturnMonth(8), ReturnMonth(9), ReturnMonth(10), ReturnMonth(11), ReturnMonth(12)

    Dim nMonth As String

    Dim nYear As String

    Dim nTitle01 As String

    Let nMonth = Mid(Format(nMeses(nMnth), "DD/MM/YYYY"), 4, 2)

    Let nYear = Year(nMeses(nMnth))

    Let nTitle01 = UCase(Left(Format(Month(nMonth), "mmm"), 3))

    If nMonth = "01" Then

        Let ReturnMonth = "JAN|" & nYear

    ElseIf nMonth = "02" Then

        Let ReturnMonth = "FEV|" & nYear

    ElseIf nMonth = "03" Then

        Let ReturnMonth = "MAR|" & nYear

    ElseIf nMonth = "04" Then

        Let ReturnMonth = "ABR|" & nYear

    ElseIf nMonth = "05" Then

        Let ReturnMonth = "MAI|" & nYear

    ElseIf nMonth = "06" Then

        Let ReturnMonth = "JUN|" & nYear

    ElseIf nMonth = "07" Then

        Let ReturnMonth = "JUL|" & nYear

    ElseIf nMonth = "08" Then

        Let ReturnMonth = "AGO|" & nYear

    ElseIf nMonth = "09" Then

        Let ReturnMonth = "SET|" & nYear

    ElseIf nMonth = "10" Then

        Let ReturnMonth = "OUT|" & nYear

    ElseIf nMonth = "11" Then

        Let ReturnMonth = "NOV|" & nYear

    ElseIf nMonth = "12" Then

        Let ReturnMonth = "DEZ|" & nYear

    End If

End Function

Caso queiram traduzir os meses para outros idiomas basta que alterem os meses.

André Luiz Bernardes

Professional Profile

DONUT PROJECT - VBA - Excel - Atualizando Tabelas Dinâmicas - Refresh Pivot Table via VBA

DONUT PROJECT - VBA - Excel - Atualizando Tabelas Dinâmicas - Refresh Pivot Table via VBA

​

Consultancy in Excel, Access, VBA, Dashboards, Scorecards & PowerPoint Projects

DESENVOLVO APLICAÇÕES VBA

Perguntas, Questões e Esclarecimentos

Baixe o Calendário Compacto para 2014 em Excel

Aqueles que utilizam as Tabelas Dinâmicas em alta escala têm consciência do poder que elas têm e da praticidade que trazem para os nossos projetos.

Nos códigos abaixo olharemos para algumas situações onde poderemos atualizar todas as Pivots, ou apenas Pivots escolhidas.

Atualizando uma tabela Simples

Private Sub Worksheet_Activate()

Run "PivotMacro"

End Sub

Sub PivotMacro()

Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables("MyPivot")

pt.RefreshTable

End Sub

Atualizando todas asTabelas Dinâmicas da Planilha

Sub AllWorksheetPivots()

    Dim pt As PivotTable

    For Each pt In ActiveSheet.PivotTables

        pt.RefreshTable

    Next pt 

End Sub

Atualizando uma Tabelas Dinâmicas específicas

Sub ChosenPivots()

Dim pt As PivotTable

    For Each pt In ActiveSheet.PivotTables    

        Select Case pt.Name

            Case "PivotTable1", "PivotTable4", "PivotTable8"

                pt.RefreshTable

            Case Else

        End Select

    Next pt

End Sub

Atualize todas as Tabelas Dinâmicas Selecionadas

Sub AllWorkbookPivots()

Dim pt As PivotTable

Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets    

        For Each pt In ws.PivotTables

                    pt.RefreshTable

        Next pt

    Next ws

    

End Sub

André Luiz Bernardes

Professional Profile

DONUT PROJECT - VBA - Access - Criando uma Query com Parâmetros

DONUT PROJECT - VBA - Access - Criando uma Query com Parâmetros

​

​

Consultancy in Excel, Access, VBA, Dashboards, Scorecards & PowerPoint Projects

DESENVOLVO APLICAÇÕES VBA

Perguntas, Questões e Esclarecimentos

Baixe o Calendário Compacto para 2014 em Excel

Essa técnica pode ajudá-lo em projetos nos quais precise de certa agilidade para criar bases de dados para análises.

O código abaixo o ajudará a fazer isso.

Sub CrieQueriesComParametros()

    '      Author: André Bernardes

    '        Date: 18/08/14 - 10:38

    '      Action: Cria uma query com Parâmetros.

    ' Application: ****®

    Dim dbs As DAO.Database

    Dim qdf As DAO.QueryDef

    Dim strSQL As String

    Set dbs = CurrentDb

    Set qdf = dbs.CreateQueryDef("nQuery")

    Application.RefreshDatabaseWindow

    Let strSQL = "PARAMETERS Param1 TEXT, Param2 INT; "

    Let strSQL = strSQL & "SELECT * FROM [Table1] "

    Let strSQL = strSQL & "WHERE [Field1] = [Param1] AND [Field2] = [Param2];"

    Let qdf.SQL = strSQL

    qdf.Close

    Set qdf = Nothing

    Set dbs = Nothing

End Sub

| Blog Office VBA | Blog Excel | Blog Access | Blog Infochart | 

DONUT PROJECT - VBA - Access - Atualizando o conteúdo de uma Query

​

Consultancy in Excel, Access, VBA, Dashboards, Scorecards & PowerPoint Projects

DESENVOLVO APLICAÇÕES VBA

Perguntas, Questões e Esclarecimentos

Baixe o Calendário Compacto para 2014 em Excel

É muito mais rápido atualizarmos internamente o conteúdo de uma query do que ficar criando-a todas as vezes que precisarmos. A performance conseguida em grandes volumetrias é incrível.

O código abaixo o ajudará a fazer isso.

Sub UpdateQuery(QueryName, SQL)

    '      Author: André Bernardes

    '        Date: 18/08/14 - 09:53

    '      Action: Atualiza o conteúdo de uma query com um novo script.

    ' Application: ****®

    ' Usando o nome da query e o respectivo script, caso a query não exista.

    If IsNull(DLookup("Name", "MsysObjects", "Name='" & QueryName & "'")) Then

        ' Cria-a, ...

        CurrentDb.CreateQueryDef QueryName, SQL

    Else

        ' Caso contrário, atualizao o script sql.

        Let CurrentDb.QueryDefs(QueryName).SQL = SQL

    End If

End Sub

Para evocar o código basta fazer assim:

' Atualiza a query que é a base de todas as análises.

Dim nSQL2 As String

Let nSQL2 = "SELECT * " & _

                     "FROM lnk_Med " & _

                     "WHERE (((Left([FVD_Sector],2))='" & nParticula & "')) "

Call UpdateQuery("qry_lnk", nSQL2)

Lembre-se que deletar uma query inexistente pode causar um erro:

DoCmd.DeleteObject acQuery, "qry_lnk"

| Blog Office VBA | Blog Excel | Blog Access | Blog Infochart | 

UM BREVE RETROSPECTO...

Bem, a minha primeira linguagem de programação foi o COBOL, passei pelo Pascal, C, e aprofundei-me mesmo no Clipper.

Já trabalhando, desenvolvi inúmeras aplicações com o Clipper para as diversas áreas de trabalho, uma vez que trabalhava em Santos, cidade portuária, com necessidades diversas. A empresa em que trabalhei, era uma softwarehouse, como chamávamos na época.

Desenvolvi sistemas de estocagem de café, venda|corretagem de café, contabilidade, estoque em geral, venda no balcão, etc...De software de Locadora de Videos a Controle de Condomínio, desenvolvi uma infinidade de aplicações e sistemas que foram ampliando-se com o passar do tempo e das necessidades dos Clientes, que já eram inúmeros.

A tecnologia também não parou, então venho conhecendo computadores de CP500 ao atual Ultrabook.

Vim de uma época onde o conceito Cliente/Servidor não existia, e precisávamos emulá-lo.

Enfim, casei-me e vim trabalhar no mercado de São Paulo numa consultoria. Como já tinha desenvolvido experiência para ter contato com diversos clientes, desenvolvendo inúmeras aplicações, pude destacar-me nesta empresa, que a princípio contratara-me apenas para desenvolver soluções internas.

Nesta ocasião, fui apresentado ao Delphi, uma linguagem visual, a minha primeira, apesar de já ter visto o Visual Basic e usado a versão Visual Objects do Clipper.

Em São Paulo pude ampliar meus conhecimentos e técnicas. Atendia variados clientes corporativos, nas áreas mais abrangentes possíveis, o que foi excelente!

Com o passar dos anos, além de programação e análise de sistemas, passei a focar-me em processos, liderar equipes, mas a paixão por desenvolver soluções rápidas e práticas não terminava, sempre dava um jeito para participar no desenvolvimento durante os projetos onde estava envolvido.

Pois bem, acabei percebendo um gap, lá pelos idos de 1997/8. As empresas, na sua totalidade, utilizavam massivamente o MS Office. O qual conhecia desde as primeiras versões, pois fora usuário dos famosos Lotus 123, Visicalc, QuattroPro, Wordstar, etc... Tive o insight de detectar que, apesar de muitos usuários terem acesso a este produto, o MS Office, bem poucos sabiam utilizá-lo plenamente. Não conheciam o conceito de Macros, e depois não captaram o momento onde a Microsoft trouxera o Visual Basic para dentro do MS Office.

Juntando a minha percepção e a necessidade do mercado, tive a ideia de me especializar em criar soluções única e exclusivamente com o MS Office. E isso envolvia usá-lo desde o MS Powerpoint, até o MS Access, e é óbvio que depois as tecnologias e a arquitetura em si, foram tornando-se ainda mais abrangentes.

Criei as aplicações mais diversas, em áreas ainda mais distintas que anteriormente. E poderá verificá-las através das minhas experiências profissionais, através das mais de 43 empresas por onde passei e estão constantes no meu curriculum vitae.

Pois bem, como não poderia deixar de ocorrer, findei por embrenhar-me em novos conceitos. Li muito, comprei muitos livros. Ministrei e assisti muitos cursos. E isso acabou me levando a manter em certo momento, uns 5 Blogs, onde escrevia sobre o desenvolvimento VBA e seus desdobramentos: 

| Blog Office VBA | Blog Excel | Blog Access | Blog Infochart | 

Hoje, focado nos últimos 5 anos, na Indústria Farmacêutica, continuo usando os meus conhecimentos técnicos para desenvolver soluções na minha área de atuação.

| Blog Office VBA | Blog Excel | Blog Access | Blog Infochart | 

DONUT PROJECT - VBA - Excel - Populando um ListBox no seu Formulário

Consultancy in Excel, Access, VBA, Dashboards, Scorecards & PowerPoint Projects

DESENVOLVO APLICAÇÕES VBA

Perguntas, Questões e Esclarecimentos

Baixe o Calendário Compacto para 2014 em Excel

Então, digamos que precise criar um pequeno script que permita que os seus usuários selecionem um número de colunas (letras de A a Z) a partir de um ListBox. E em seguida extraiamos os itens selecionados na caixa de listagem.

1

Crie um formulário com uma ListBox chamado Listbox1 e um botão chamado CommandButton1.

2

Crie uma SUB UserForms no seu formulário.

3

Preencha (popule) a ListBox com letras de A a Z:

Private Sub UserForm_Initialize()

' Crie um array.

Dim AlfabetArray() As String

' Defina o conteúdo do array. Aqui podemos separá-las com "|", mas não poderemos usar ",".

Let AlfabetArray = Split("A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|X|Y|Z", "|")

' Populando o Listbox com o array de letras

Let ListBox1.List = AlfabetArray

End Sub

4

Certifique-se de que a caixa de listagem tenha o seu atributo MultiSelect definido como 1 - fmMultiSelectMulti, se você quiser que os usuários possam selecionar vários itens com um clique do mouse.

Selecione 2 - fmMultiSelectExtended, se quiser que os usuários possam usar um  "Ctrl-clique " para selecionar vários itens muito mais rápido.

5

Extraia os itens selecionados do ListBox e insira-os na matriz para uso posterior.

Private Sub CommandButton1_Click()

Dim lItem As Long

Dim BernardNavne() As String    'Array

Dim blDimensioned As Boolean    'Is the array dimensioned?

Dim lngPosition As Long         'Counting

     

Let blDimensioned = False

' Efetua o Loop através de todos os itens no Listbox

For lItem = 0 To Me.ListBox1.ListCount - 1

         

        If Me.ListBox1.Selected(lItem) Then

        ' Se estiver selecionado adicionna-o ao array

        

            'Checamos se o array está corretamente dimensionado

            If blDimensioned = True Then

            ReDim Preserve BernardNavne(0 To UBound(BernardNavne) + 1) As String

            Else

            ReDim BernardNavne(0 To 0) As String

            blDimensioned = True 'flag

            End If

            

            ' Adicionamos a letra ao array

            BernardNavne(UBound(BernardNavne)) = Me.ListBox1.List(lItem)

        End If

Next lItem

'Loop through array to see which items were selected from the Listbox:

For lngPosition = LBound(BernardNavne) To UBound(BernardNavne)

MsgBox BernardNavne(lngPosition)

Next lngPosition

End Sub

André Luiz Bernardes

Professional Profile