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)

Ideias Analíticas Avançadas

Estou lançando uma publicação na plataforma Medium: a Ideias Analíticas Avançadas (https://medium.com/ideias-anal%C3%ADticas-avan%C3%A7adas).

Os objetivos são:

  • Escrever sobre Analytics Hard: Otimização, Matemática, Python, Computação Quântica, com código e tudo
  • Convidar outros autores a publicar sobre o tema.

Fica já o convite, quem quiser escrever sobre alguns dos temas e divulgar ali.

Macro para salvar Excel em TXT

Segue uma macro que pode ser útil, para salvar dados de Excel para TXT.

Uma aplicação é enviar informação para outro sistema, por exemplo.

O Excel tem nativamente uma opção de salvar como CSV, porém, o uso de macros permite uma flexibilidade enorme, além de todo o poder de processar a informação da forma que quisermos.

Imagine que a planilha seja como a abaixo.

Queremos salvar o arquivo com o nome indicado na célula C2, no mesmo diretório em que o arquivo Excel se encontra.

O código vai contar as linhas, jogar o conteúdo para a memória.

A seguir, vai criar o arquivo texto, escrever nele e fechar:

‘Inicializa funcao de manipulação de arquivos

Set fs = CreateObject(“Scripting.FileSystemObject”)

strPath = ThisWorkbook.Path & “\” & nomeArquivo

Set afs = fs.CreateTextFile(strPath, 1, 0)

‘Escreve conteúdo

For i = 1 To nl

    strAux = “”

    For j = 1 To UBound(edados, 2)

        strAux = strAux & edados(i, j) & “, “

    Next j

    ‘escreve no arquivo

    afs.writeline strAux

Next i

‘Fecha o arquivo

afs.Close

Set afs = Nothing

Set fs = Nothing

Resultado:

Vide arquivo para download: https://1drv.ms/x/s!Aumr1P3FaK7jnyUz0c1mYGji8DgR


Ideias Analíticas Avançadas

https://medium.com/ideias-anal%C3%ADticas-avan%C3%A7adas

Um mundo mais eficaz através do Analytics

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

Exemplo de metaheurística – uma variável

A ideia aqui é escrever uma série de metaheurísticas de dificuldade crescente.

Começando do caso mais simples possível. Variável unidimensional “x”.

Defino um range de valores, no caso, x entre 0 e 100.

varmin = 0 ‘Range Mínimo

varmax = 100 ‘Range Máximo

Seja a função objetivo –x^3 + 20*x^2 + 100, mostrado no gráfico abaixo.

Private Function FO(ByVal var) As Double

    ‘Implementa a função objetivo

    FO = -var ^ 3 + 20 * var ^ 2 + 100

End Function

Este algoritmo vai:

 – escolher um valor aleatório entre 0 e 100

– ponderar 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

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

– salva o histórico

Trecho do código:

For iter = 1 To Niteracoes ‘Numero de iteracoes

    lambda = iter / Niteracoes

    ‘Sorteia um valor

    sorteia var, varmin, varmax, varbest, lambda

    ‘Avalia a função atual

    FOatual = FO(var)

    ‘Salva a melhor (maximizando)

    If FOatual > FOBest Then

        FOBest = FOatual

        varbest = var

    End If

    FOhist(iter, 1) = FOBest

Next iter

Resultado. Em apenas 100 iterações, já chegou num valor excelente: x = 13,3

Você pode mudar a FO, ranges, etc.

Próximo passo é fazer um exemplo multidimensional, etc.

Planilha para download no Github: https://github.com/asgunzi/Metaheuristica-uma-variavel

Metaheurística para o Caixeiro-Viajante

Implementação em VBA de metaheurística para resolução do problema do caixeiro-viajante.

A metaheurística não vai dar o valor ótimo, porém na prática, pode entregar um valor bom o suficiente.

Algumas vantagens: é mais flexível em termos de modelar restrições, é grátis (um solver tem licença que pode custar algumas dezenas de milhares de dólares, no mínimo).

Algumas desvantagens: ter alguém que manje muito de otimização combinatória para adaptar o código corretamente, não garantir a solução ótima.

De qualquer forma, é uma alternativa viável, competitiva e possível de ser utilizada, para problemas de otimização combinatória.

Segue para download no Github.

https://github.com/asgunzi/travelingSalesmanVBA

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

A Conjectura de Collatz

A Conjectura de Collatz é o problema não resolvido de matemática mais simples da história.

Pegue um número qualquer n.

  • Se n for par, divida por 2
  • Se n for ímpar, calcule 3*n+1

E continue fazendo essa conta.

A conjectura diz que a sequência sempre vai convergir para 1.

Exemplo: número inicial 5

5 -> 16 -> 8 -> 4 -> 2 -> 1

Foram 5 passos para convergir para 1.

Exemplo: número inicial 6

6 -> 3 -> 10 -> 5 -> 16 ->  8 -> 4 -> 2 -> 1

Foram 8 passos para convergir para 1.

Para números de 2 a 50, o resultado do número de passos mostra:

Informações interessantes: apesar de extremamente simples de ser formulada, essa conjectura até hoje não foi provada.

É contra intuitivo; parece que vai crescer, mas aí converge.

A sequência é errática: um número pode precisar de 100 passos, o vizinho precisa de 5.

No VBA, a forma mais simples de resolver é com um simples loop while.

Function collatz(n)

Dim count As Long

count = 0
While n > 1

If n Mod 2 = 0 Then
    n = n / 2
Else
    n = 3 * n + 1
End If
count = count + 1

Wend

collatz = count

End Function

É possível pensar numa estrutura da dados mais complexa, porém com melhor performance computacional.

Por exemplo, salvar o número de passos de todos os valores já rodados. Rodar a sequência até chegar a um número menor do que o atual, e aí resgatar da memória o resultado já calculado.

Com esse método, é possível calcular os primeiros 110 mil números, em menos de 1 segundo.

No VBA, o limite é restrição de tamanho do tipo Long. Não há um tipo Big Int, como em Java ou Python, o que torna bem complicado calcular mais do que isso.

Código no Github: https://github.com/asgunzi/CollatzVBA

Veja também:

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

https://www.quantamagazine.org/why-mathematicians-still-cant-solve-the-collatz-conjecture-20200922/

Como criar uma foto pixelada no Excel

A ideia deste tutorial é pegar uma imagem qualquer e pintar as células do Excel de forma correspondente.

Uma imagem é apenas um retângulo dividido em quadriculados (o pixel), e cada pixel é pintado de uma cor.

Imagine que cada célula é pintada de uma combinação de Vermelho, Verde e Azul, as cores primárias.

Cada célula terá um tom de Red, Green e Blue, e essa tonalidade varia de 0 a 255.

Infelizmente, o VBA não tem uma boa biblioteca de manipulação de imagens. Recomendo instalar o OpenCV (open computer vision), no Python.

https://pypi.org/project/opencv-python/

A rotina tem dois passos:

1 – ler a imagem e salvar a matriz de dados (Python)

2 – ler a matriz de dados e colorir o Excel

Passo 1) Rodar o arquivo “OpenCVReadFile.py”, no Python.

Indicar a localização do arquivo de imagem a desenhar

Mudar o local do arquivo CSV de destino.

Essa rotina lê a imagem e salva a matriz de dados em CSV.

Passo 2)  Rodar a macro “Pixeliza” do Excel em anexo. É necessário mudar o endereço do arquivo CSV.

E pronto, temos uma reprodução da imagem na planilha, que pode ser editada normalmente, como qualquer arquivo Excel.

Atenção. Rodando essa macro, descobri que o Excel tem um limite de 4000 células com formatação diferente.

E, pior, chega facilmente nesse limite.

Portanto, o esquema citado só funciona com imagens de resolução muito baixa.

Vide arquivos no Github: https://github.com/asgunzi/FotoPixeladaExcel

Tem o Excel com a macro e o código Python.

Ideias técnicas com uma pitada de filosofia

https://ideiasesquecidas.com/

Escrever por extenso

A macro da planilha (https://github.com/asgunzi/EscreverExtensoVBA) lê um número e escreve por extenso.

O código não é meu. Originalmente é VB6. Peguei num fórum e colei no Excel, e parece que funciona legal.

Não sei se isso pode ser útil de alguma forma.

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

Entre no grupo de estudos de Computação Quântica:
https://www.facebook.com/groups/1013309389112487