Tópicos relacionados a códigos VBA, gravação de macros, etc.
Por SandroLima 30 Nov 2018 às 18:59
Membro 3 Estrelas
Mensagens: 276
Reputação: 3
#38932
Boa tarde, pessoal

Preciso de ajuda para escrever o código do Botão "Importar Atividades Recorrentes" (Cor Laranja).

Gostaria que ele fosse baseado na escrita do código do botão "Inserir Novo Registro" (Cor Verde).

A Função da Macro seria importar da Tabela "TB_AtividadesCadastradas" (Planilha CONFIG2) os valores das seguintes colunas:
- Data
- Fluxo
- Periodicidade
- ID
E inserir na tabela "TB_AtividadesDiarias", desde que atendido o critério "Recorrente - data fixa" ou "Recorrente - data variável" da coluna Periodicidade da Planilha CONFIG2.

As colunas DIA DA SEMANA, CATEGORIA e ITEM não haveria necessidade de importar porque são preenchidas por fórmulas (como vcs podem observar na "TB_AtividadesDiarias") e por isso a necessidade de escrever essa macro baseada no código do botão "Inserir Novo Registro", pois a rotina dos filtros e aplicação das fórmulas nos demais campos seria igual.

Estou à disposição se ficou alguma dúvida.

Muito obrigado a quem puder colaborar.
Apenas usuários registrados podem ver ou baixar anexos.
Avatar do usuário
Por DJunqueira 30 Nov 2018 às 21:44
Excel Expert
Mensagens: 1123
Reputação: 437
#38936
Olá SandroLima, a título de expandir o rol de opções eu montei uma planilha com base na sua q realiza o q vc quer utilizando o Power Query. P/ isso criei duas novas abas, uma com uma tabela auxiliar e outra com uma cópia da sua tabela de atividades diárias (Exemplo TbAD).

Clicando na tabela da aba Exemplo TbAD com o botão esquerdo e selecionando 'Atualizar' vc terá a inserção de novos registros desejados e de quebra um dos registros repetidos será excluído.

O Power Query pode fazer uma conexão com sua 'tabela auxiliar' real e realizar o mesmo procedimento sem o uso de fórmulas.
Apenas usuários registrados podem ver ou baixar anexos.
Por SandroLima 30 Nov 2018 às 22:51
Membro 3 Estrelas
Mensagens: 276
Reputação: 3
#38941
Boa noite, DJunqueira... Boa noite, pessoal do fórum.

Muito obrigado pela intenção de ajudar.

Então... eu uso o Excel 2013 e de Power Query ainda não entendo absolutamente nada.
Estou engatinhando ainda no Excel... me aventurando ora no VBA ora em fórmulas e funções.

A planilha que utilizo é mais ampla, apresenta outras tabelas auxiliares (por isso a última linha tem fórmulas com o REF) e as linhas repetidas na verdade foram um erro na hora de exemplificar.
Cada ID tem os valores próprios que através de índice/corresp basicamente preenche as demais colunas.

O motivo da macro que solicitei ajuda é como pode perceber na Coluna Data da Planinha CONFIG 2 as fórmulas são dinâmicas (mudam conforme o mês, permanecendo o dia fixo) e as atividades (entradas e saídas) se repetem mensalmente.

Com a macro a intenção era alimentar a tabela de Atividades Diárias todo início de mês com os novos lançamentos do mês, alterando somente valores ou realizando pequenas modificações (por exemplo no valor de uma conta de energia ou nome de um cliente que realizou consulta , etc).

E a tabela da planilha CONFIG2 serve para cadastrar as atividades que se repetem a cada mês.

Mas muito obrigado mesmo... se puder me ajudar nessa empreitada... fico agradecido a todos que puderem colaborar.
Avatar do usuário
Por DJunqueira 30 Nov 2018 às 23:41
Excel Expert
Mensagens: 1123
Reputação: 437
#38942
P/ o Excel 2013 tem um suplemento gratuito do Power Query é só instalar, vale muito a pena. Como sua atualização é mensal o PwQy seria a ferramenta ideal. A curva de aprendizagem é rápida p/ o básico.

Endereço do suplemento na Microsoft:
https://www.microsoft.com/pt-BR/downloa ... x?id=39379
Por SandroLima 01 Dez 2018 às 12:38
Membro 3 Estrelas
Mensagens: 276
Reputação: 3
#38948
Obrigado, DJunqueira.

Devo estudar em breve... mas por agora é do que eu precisava.

Quando eu falo que a tabela é atualizada mensalmente é pq os lançamentos recorrentes de cada mês são acrescentados à Tabela de Atividades Diárias e não sobrescritos. Como disse ela é bem mais ampla e tem bem mais colunas e mais linhas já estão preenchidas.

Por isso queria essa macro. Todo início de mês eu acionaria a macro e ele incrementaria a tabela com os lançamentos recorrentes... com as fórmulas ( a maioria índice/corresp a partir do ID) ela completaria as demais colunas. Depois eu atualizaria manualmente pequenos ajustes como valores, nome de algum cliente, etc.

Por enquanto vou continuar tentando essa solução... mas vou sim estudar com mais tempo o PowerQuery. Vou pegar o suplemento.

Mas muito obrigado mesmo.
Avatar do usuário
Por DJunqueira 01 Dez 2018 às 14:44
Excel Expert
Mensagens: 1123
Reputação: 437
#38950
Ok Sandro, vale acrescentar q vc tb pode acionar o Power Query com macros.
Inicialmente o Power Query é bem intuitivo e tb é usado no MSPower BI q tb te aconselho a se informar e tb tem download gratuito.
Por SandroLima 02 Dez 2018 às 19:04
Membro 3 Estrelas
Mensagens: 276
Reputação: 3
#38964
Boa tarde...

Vou tentar explicar melhor minha necessidade.

Tenho duas tabelas:
1) TB_AtividadesDiarias
2) TB_AtividadesCadastradas

Na TB_AtividadesCadastradas mantenho cadastradas todas as atividades que se repetem mensalmente.
Como podem observar nessa tabela, a coluna Data apresenta uma fórmula dinâmica, alterando somente o mês porém mantendo os dias (do mês) de vencimento fixos.
Repare que como estamos hoje no mês de Dezembro todas as datas da TB_AtividadesCadastradas mudaram para o mês 12.

Com a macro do botão "Importar Atividades Recorrentes" eu gostaria que fossem acrescentados os lançamentos com as datas do novo mês na TB_AtividadesDiarias (veja bem, eu disse acrescentar e não sobrescrever), ou seja importar os lançamentos (agora do mês 12) e adicionar na TB_AtividadesDiarias.

***IMPORTANTE***
- Obedecer os critérios "Recorrente - data fixa" e "Recorrente - data variável" da coluna Periodicidade da TB_AtividadesCadastradas. Ou seja, importar somente as linhas que cumprem um desses critérios.

- Linhas cujo valor na coluna Periodicidade sejam "Ocasional" não devem sem importados para a TB_AtividadesDiarias.

Não desejo que sejam importados todos os dados de uma linha da TB_AtividadeCadastrada . Mas somente até o campo ID. Isso porque na TB_AtividadesDiarias os demais campos (à direita da coluna ID) são preenchidos com fórmulas. Preciso então que sejam importados somente os valores dos campos DATA / FLUXO / PERIODICIDADE /ID da "TB_AtividadesCadastradas" acrescentando na "TB_AtividadesDiarias". A Coluna DIA DA SEMANA tb não dever ser importada pq é preenchida por fórmula.

Gostaria que fosse construída com base na macro do botão "Inserir Novo Registro" que organiza a tabela após a inserção dos registros.

Muito obrigado a quem puder ajudar.
Por SandroLima 08 Dez 2018 às 12:42
Membro 3 Estrelas
Mensagens: 276
Reputação: 3
#39158
Bom dia, colegas do fórum.

Finalmente com ajuda de um colega em outro fórum consegui o código para a atividade desejada.

Porém agora me deparei com um novo problema.

À medida que a TB_AtividadesDiarias aumenta de tamanho (e no meu caso já possui várias linhas - mais de 1000) (diminui aqui apenas para conseguir enviar o anexo), a rotina se torna extremamente lenta. Podem fazer o teste executando o código diversas vezes em sequencia.

A cada nova execução a rotina vai se tornando mais lenta. Há uma maneira de melhorar isso? Alguma sugestão?

Segue anexo para teste.

Obrigado e tenham um bom dia.
Apenas usuários registrados podem ver ou baixar anexos.
Por babdallas 08 Dez 2018 às 19:51
Membro 5 Estrelas
Mensagens: 1161
Reputação: 542
#39166
Veja se ajuda.
Apenas usuários registrados podem ver ou baixar anexos.
Por SandroLima 10 Dez 2018 às 15:48
Membro 3 Estrelas
Mensagens: 276
Reputação: 3
#39202
Boa tarde, babdallas.

Muito obrigado, amigo... ficou ótimo. Bem mais ágil na execução em relação ao código anterior.

Estou fazendo aqui alguns testes para adequar à minha planilha e melhorar a rotina... mas ficou excelente, Estava precisando demais disso.

Se não for abusar demais poderia em alguns pontos do código fazer um breve comentário para meu aprendizado? Só onde for um pouco mais complexo... o básico eu consigo fazer a leitura/entendimento.

Vou relacionar alguns trechos aqui:

Código: Selecionar todosReDim varDados(1 To lngLinUbound, 1 To lngColBound) As Variant


Código: Selecionar todosReDim datData(1 To lngCrit, 1 To 1) As Date
    ReDim varOutros(1 To lngCrit, 1 To 3) As Variant


Código: Selecionar todosWith tbAtivDiaria
        With .ListRows.Add
            .Range(1, 2).Resize(lngCrit) = datData
            .Range(1, 4).Resize(lngCrit, 3) = varOutros
        End With
    End Withs


As abreviações se puder dizer o que significam, como vc as lê... me ajuda na memorização na hora ler o código ou adaptar para minha escrita. (por exemplo "lng", "Ubound", "var")

Às vezes passo dias travado em um determinado ponto do código e dá uma desanimada, Quando de repente vem alguém com uma solução "mágica" desperta a inspiração de novo e vontade de estudar e aprender mais.

Muito obrigado mesmo, amigo.
Por babdallas 10 Dez 2018 às 20:24
Membro 5 Estrelas
Mensagens: 1161
Reputação: 542
#39210
Pode excluir a Macro1. Acho que fiz algum teste e esqueci de apagar.

Código: Selecionar todosReDim varDados(1 To lngLinUbound, 1 To lngColBound) As Variant


No trecho de código acima, após obter o número de linhas e o número de colunas dos dados, eu redimensiono (Redim) a matriz varDados, sendo a primeira dimensão indo de 1 até lngLinUbound (número de linhas dos dados) e a segunda dimensão de 1 até lngColUbound (número de colunas dos dados).
A nomenclatura de váriáveis é muito pessoal, mas eu costumo usar primeiro uma abreviação do tipo de dados (ex: lng para Long, dbl para Double, str para String, var para Variant, etc). Ubound é o tamanho máximo de uma matriz no VBA e LBound é o tamanho mínimo. Então usei esta função do VBA como nome da minha variável (mas poderia ter usado outro nome).

NO trecho abaixo, eu redimensiono outras duas matrizes. A primeira é uma matriz que usei para armazenar as datas. Por isso o nome que começa com dat (do tipo de dados Date). A outra matriz é uma matriz Variant para armazenar as outras 3 colunas de dados que você pediu. Como estas colunas são adjacentes entre si (uma do lado da outra), então aproveitei para armazenar tudo em uma matriz só.

Código: Selecionar todosReDim datData(1 To lngCrit, 1 To 1) As Date
    ReDim varOutros(1 To lngCrit, 1 To 3) As Variant



No trecho abaixo, eu adiciono uma linha na tabela tbAtivDiaria e coloco na segunda coluna todos os dados contidos na matriz datData, redzimensionanso (através do resize) para o número de linhas que atendiam aos seus critérios (lngCrit). Ao fazer isso, automaticamente as outras linhas da tabela são criadas também.
Depois adiciono os outros dados a partir da coluna 4, porém redimensionando para o número de linhas que atendiam o critério que você pediu (lngCrit) e para o número de colunas da matriz (3, pois são 3 colunas de dados).

Código: Selecionar todosWith tbAtivDiaria
        With .ListRows.Add
            .Range(1, 2).Resize(lngCrit) = datData
            .Range(1, 4).Resize(lngCrit, 3) = varOutros
        End With
    End With


Espero ter esclarecido um pouco.