Padrões em círculos

É possível criar padrões extremamente intrincados, a partir de construções simples.

No VBA, é possível desenhar um círculo com o seguinte comando.

    ActiveSheet.Shapes.AddShape(msoShapeOval, 25, 25, 15, 15)

(parâmetros: posição x e y, tamanho na direção x e tamanho na direção y)

Para trocar cores da borda, preenchimento do círculo, etc, normalmente uso o “gravar macro” e reaproveito o código.

Utilizando apenas círculos com raios pequenos, é possível criar uma malha formada de pontos.

(Versão em Excel (https://1drv.ms/x/s!Aumr1P3FaK7joCmnhqOUkgQfqrwW), e versão JS D3 em: https://asgunzi.github.io/Padr-es-em-C-rculos/PadroesCirculos.html)

https://ideiasesquecidas.files.wordpress.com/2022/05/circulos01.png?w=1024

Se o raio de cada ponto for aumentado, e com o raio vermelho levemente maior que o azul.

Começa a ficar interessante quando os raios aumentam a ponto de se tangenciar.

https://ideiasesquecidas.files.wordpress.com/2022/05/circulos03.png?w=1024

Aumentando mais ainda.

https://ideiasesquecidas.files.wordpress.com/2022/05/circulos04.png?w=1024

E assim sucessivamente:

https://ideiasesquecidas.files.wordpress.com/2022/05/circulos05.png?w=1024

Padrões diversos formados aumentando mais ainda os raios:

https://ideiasesquecidas.files.wordpress.com/2022/05/circulos06.png?w=1024

https://ideiasesquecidas.files.wordpress.com/2022/05/circulos07.png?w=1024

https://ideiasesquecidas.files.wordpress.com/2022/05/circulos08.png?w=1024

São desenhos de alta complexidade, feitos a partir de um padrão simples de círculos.

https://ideiasesquecidas.files.wordpress.com/2022/05/circulos09.png?w=1024

Vide planilha, para criar estes e outros padrões.

É necessário ativar macros para funcionar.

Veja também:

https://ideiasesquecidas.com/

Como gerar um UUID no Excel?

Num banco de dados, é importante ter um registro único como chave, para evitar que dois registros conflitem.

Como eu posso fazer para ter esse identificador (ID) único para cada registro?

Uma forma é ter um controle de IDs: verificar qual o último número já gravado e gerar um novo número. Talvez sequencial, como no exemplo abaixo.

Porém, para casos onde for difícil ou custoso fazer essa verificação, ou a comunicação central online for complicada, é possível utilizar uma técnica de números aleatórios.

É aí que entra o conceito de UUID, identificador universal exclusivo. É um número de 128 bits representado por 32 dígitos hexadecimais, cada um gerado aleatoriamente.

Algo como:

c3d5d666-4050-4bad-8258-6a0c4f8eb701

Há uma série de protocolos para gerar esse número. Apesar de aleatório, é aleatório dentro desses protocolos.

O site a seguir fornece o serviço que gera um UUID, de forma gratuita.

https://www.uuidgenerator.net/

A tabela ficaria assim, para efeito de comparação:

Há chance de duplicação? Sim, há uma probabilidade maior que zero, porém muito pequena: 1 em 2,7 quintilhões. É mais fácil ganhar na mega sena duas vezes seguidas do que dar um conflito de UUIDs.

Em anexo, uma rotina em Excel – VBA que consulta o site citado acima, gera um UUID aleatório e puxa para uma célula. Foi desenvolvida pelo amigo Bruno Cambria, como um componente de um projeto que tocamos.

Um uso possível pode ser gerar N bancos separados, assíncronos, e juntar depois, com chance quase nula de duplicação. Ciência da computação pura.

Link para saber mais.

https://en.wikipedia.org/wiki/Universally_unique_identifier

Bônus: Review do livro Hacking Digital, com boas dicas e cuidados a serem tomados na transformação digital da companhia.

Acesse em https://www.linkedin.com/posts/arnaldogunzi_hacking-digital-best-practices-to-implement-activity-6924321829675241472-73gh

https://ideiasesquecidas.com/

Dicas de Copiar e colar VBA

Em geral, é melhor carregar tudo na memória, trabalhar e voltar tudo de uma vez só.

A cada manipulação de planilha (como colar dados), perdemos eficiência.

Exemplo simples.

Digamos que eu queira copiar valores de A para coluna B.

Um jeito simples é copiar linha a linha.

    n = Application.WorksheetFunction.CountA(Range(“a2:a100000”))

    For i = 2 To n

        Range(“b” & i) = Range(“a” & i)

    Next i

Uma forma extremamente mais eficiente é copiar tudo numa variant, e posteriormente colar ela.

n = Application.WorksheetFunction.CountA(Range(“a2:a100000”))         

‘Copia a tabela inteira para uma variável variant    

tab1 = Range(“a2:a” & 1 + n)         

‘Cola a tabela duma vez só, começando na célula c2    

Range(“c2”).Resize(UBound(tab1, 1), UBound(tab1, 2)) = tab1      

Neste caso específico, a diferença é de 9s, pouco, porém para manipulações mais complexas, a gente pode ganhar muito tempo, da ordem de dezenas de minutos a cada vez que rodar.    

Baixar a planilha aqui, a fim de comparação.

Ideias técnicas com uma pitada de filosofia
https://ideiasesquecidas.com/

Loja e recomendações
https://ideiasesquecidas.com/loja/

Como deixar a execução do seu script VBA em espera…?

(Enviado pelo amigo Wagner Gurgel do Amaral)

Nos nossos projetos em VBA, de vez em quando precisamos fazer uma chamada a aplicações externas para executar algum outro processo.

Porém, o processo externo pode acabar antes ou depois do resto da aplicação VBA. Como garantir a sincronização?

Pensando nisso, a dica de hoje é como fazer o VBA aguardar esse processo externo ser finalizado.

O código abaixo é bem simples e caso necessite também segue aqui (https://1drv.ms/x/s!Aumr1P3FaK7jn39ra16Dtq8ssIQw). O segredo é utilizar a opção (waitOnReturn) no código.

Link para maiores informações: https://docs.microsoft.com/pt-br/office/vba/api/excel.range.run#syntax

Ideias técnicas com uma pitada de filosofia
https://ideiasesquecidas.com/

Como atualizar todas as tabelas dinâmicas do Excel

Segue uma dica escrita pela Mônica Aoki.

Já mostrou dados desatualizados por esquecer de atualizar a tabela dinâmica? Então fique por dentro dessa dica!

Conseguimos atualizar todas as tabelas dinâmicas da planilha apertando apenas um botão:

Como atualizar todas as tabelas dinâmicas do Excel

(Obs. Dados ilustrativos, gerados aleatoriamente)

Mas você quer disponibilizar uma ferramenta para o usuário final e não deseja que ele tenha que fazer nada para atualizar a tabela dinâmica? Conseguimos resolver isso com os “Eventos” do Excel:

1) Clique em Developer > Visual Basic

2) Na janela do Visual Basic, de um duplo clique no nome da sua aba (1). Em seguida selecione a opção “Worksheet” (2) e “Change” (3)

  • Cole o seguinte comando dentro da sua função. 

A base de dados do exemplo vai até a coluna “D”. Estou chamando o nome da minha tabela dinâmica (Faturamento) e falando para o excel dar um “Refresh” nela sempre que houver mudança de valor nessas quatro primeiras colunas.

Aqui vocês podem modificar o código para o número de colunas que tem a sua base de dados.

Uma forma alternativa é utilizar activeworkbook.refreshAll, que funciona, porém vai deixar o Excel lento – porque vai atualizar toda a planilha sempre que houver alguma alteração.

Segue planilha para vocês testarem (https://1drv.ms/x/s!Aumr1P3FaK7jn2Ti9JmfnH_8tgIP)

Fica a dica!

Veja também:

https://ferramentasexcelvba.wordpress.com/

Remover acento, arrumar strings

Quando temos que fazer match entre tabelas, frequentemente alguns problemas surgem. Algumas funções para ajudar.Remover acento.  O código VBA do arquivo aqui (https://1drv.ms/x/s!Aumr1P3FaK7jn1JLiNq7KKtOcFU3) elimina acentuação.

A função ARRUMAR do Excel elimina espaços no começo e no fim da string (em VBA, função TRIM).

A função MAIUSCULA do Excel transforma string em maiúscula (em VBA, função UCASE).

Analogamente, há a função MINUSCULA (em VBA, função LCASE)

Vide exemplos no link.

Quick sort VBA

Ordenar tabelas pode ser feito jogando numa planilha e usando ordenação por filtros.

Porém, se estivermos trabalhando em VBA na memória, é muito ruim ficar colando dados na planilha só para ordenar.

Uns anos atrás, fiz um quicksort no vba para fazer isso direto em tabelas. Você passa: a tabela, o inicio e o fim, e a coluna a ordenar. Também diz se é crescente ou não. Vide planilha aqui (https://1drv.ms/x/s!Aumr1P3FaK7jn0FQpHettougpiGb)

Sub teste()

Dim tab1 As Variant

Dim i As Integer, j As Integer

ReDim tab1(1 To 1000, 1 To 3)

For i = 1 To 1000

    For j = 1 To 3

        tab1(i, j) = Math.Rnd

    Next j

Next i

‘Ordeno pela coluna 3

QuickSortArray tab1, 1, UBound(tab1, 1), 3, “Crescente”

Range(“a1”).Resize(UBound(tab1, 1), UBound(tab1, 2)) = tab1

End Sub

Colar eficiente – VBA

Um erro comum em VBA é fazer manipulações linha a linha: copiar uma linha da planilha, trabalhar, escrever a linha de volta no Excel.

É extremamente mais eficiente copiar tudo de uma vez, trabalhar e colar tudo.

Para colar a tabela inteira de uma vez, use um comando assim:

Range(“a1”).Resize(UBound(tab1, 1), UBound(tab1, 2)) = tab1

Escolha a célula inicial a colar (no caso, “a1”), depois expanda para as dimensões da tabela (no caso, tab1) e depois colar.

Exemplo aqui (https://1drv.ms/x/s!Aumr1P3FaK7jnz8rsuntJq8VGFce). Veja que é bem eficiente.

Sub colarexemplo()

Dim tab1 As Variant

Dim i  As Integer, j As Integer

ReDim tab1(1 To 1000, 1 To 2333)

For i = 1 To UBound(tab1, 1)

    For j = 1 To UBound(tab1, 2)

        tab1(i, j) = Math.Rnd

    Next j

Next i

Sheets(“Plan1”).Range(“a1”).Resize(UBound(tab1, 1), UBound(tab1, 2)) = tab1

End Sub

Ocultar colunas – VBA

Digamos que eu tenha um dashboard, como o seguinte.

O objetivo é criar uma rotina para mostrar somente as telas que forem clicadas e ocultar as demais.

Ex. Clico no botão “Tela 2”, e a rotina mostra apenas a Tela 2:

Outro exemplo. Clico no botão “Tela 1”, e somente a Tela 1 é mostrada.

1 – Via VBA

Via código, para ocultar as colunas de B a G, é só fazer:

    Columns(“B:G”).EntireColumn.Hidden = True

Para mostrar as colunas de B a G, utilizar False

    Columns(“B:G”).EntireColumn.Hidden = False

E é só adaptar essa lógica para cada caso.

2 – Via agrupar e desagrupar:

Esta é uma funcionalidade 100% Excel, sem código. Selecionar as colunas e via Dados -> Agrupar, vão aparecer uns sinais de + e –, fazendo exatamente a funcionalidade desejada.

Planilha para download: https://1drv.ms/x/s!Aumr1P3FaK7jnzUjn1FYUsDRf6kV

Bônus: O Bingo da AI.

Ideias Analíticas Avançadas

Um mundo mais eficaz através do Analytics

Nova guia (medium.com)

Metaheurística com 2 variáveis

Essa versão é um pouquinho mais elaborada que a versão com uma variável (https://ferramentasexcelvba.wordpress.com/2021/03/27/exemplo-de-metaheuristica-uma-variavel/)

Serve para resolver o problema linear:

Max 10*x +5*y

Sujeito a:

2*x+4*y <= 50

1*x -6*y <= 15

É semelhante ao anterior, mas agora as variáveis são matrizes.

‘Inicializando coeficientes FO

coefFO(1) = 10

coefFO(2) = 5

‘Inicializando coeficientes Matriz

coefMatriz(1, 1) = 2

coefMatriz(1, 2) = 4

coefMatriz(2, 1) = 1

coefMatriz(2, 2) = -6

‘Inicializando rhs

RHS(1) = 50

RHS(2) = 15

Um parâmetro adicional, restringindo as variáveis a assumir valor máximo 50 e mínimo 0.

For i = 1 To nvar

    varmin(i) = 0 ‘Range Mínimo

    varmax(i) = 50 ‘Range Máximo

Next i

Em linhas gerais:

– Escolhe valores aleatórios no range das variáveis

– Pondera o valor aleatório com a melhor solução até agora

– Lambda controla o mix valor aleatório x melhor solução até agora. No início lambda é pequeno, depois vai aumentando

– Verifica se cumpre todas as restrições

 – função objetivo que avalia a solução

– salva o histórico

‘Algoritmo evolutivo

For iter = 1 To Niteracoes ‘Numero de iteracoes

    lambda = iter / Niteracoes

    ‘Sorteia um valor

    sorteia var, varmin, varmax, varbest, lambda

    ‘Verifica restricoes

    isOk = verificaRestricoes(var, coefMatriz, RHS)

    If isOk Then

        ‘Avalia a função atual

        FOatual = FO(var, coefFO)

        ‘Salva a melhor (maximizando)

        If FOatual > FOBest Then

            FOBest = FOatual

            copiaUni var, varbest

        End If

    End If

    FOhist(iter, 1) = FOBest

Next iter

Usando o solver, as variáveis ótimas são 22,5 e 1,25.


A metaheurística chega a valores próximos ao ótimo, o que mostra que funciona para uma solução suficientemente boa.

Provavelmente, com muitas variáveis e muitas restrições, vai ficar bem mais difícil convergir para uma solução boa.

Uma coisa ruim é todas as equações serem hard coded, mas quem domina código consegue fazer isso fácil.

É possível “tunar” esse algoritmo básico com pré processamento, ou algum chute guiado ao invés de ser basicamente aleatório, mas aí vai depender do problema.

Planilha para download no Github: https://github.com/asgunzi/Metaheuristica-2-var