none
Como Carregar uma Colecao. RRS feed

  • Pergunta

  • Bom dia.

    Gostaria de usar o For Each para fazer um loop para selecionar colunas pre determinadas de uma Tabela com Union, para tal declarei uma variavel Colecao como collection e outra de Area como de string.

    Nao sei se funciona o codigo abaixo,mas antes preciso carregar a Colecao com nomes das colunas da Tabela desejada,como carregar? 

    Depois de selecionados as areas desejada pretendo fazer configuracao de validacao de dados lista conforme o codigo abaixo.Lista1 e uma nome da area dinamica na planilha onde digito os dados da valicacao.

    Fui acrescentar o codigo de validacao e a area do codigo partiu em dois??? Alguem sabe como funciona esse editor? De vez enquanto quando movimenta frases com Ctrl as letras ficam diminuidas e nao sei como voltar atras.Muitas vezes quero deletar a area inteira do codigo e nao sei como fazer.

    Desde ja agradeco a quem possa dar um retorno.

    Tadao

    'Procedimento para selecionar colunas pre determinada de uma Tabela
    Sub Selecao()
    
    Dim Tab1 As ListObject, Col As String, Colecao As Collection, area As Range
    
    'Antes de rodar o For Each, preciso carregar a Colecao com nomes dos cabecarios
    'que desejo selecionar, mas como fazer isso?
    
    
    For Each Col In Colecao
        If area Is Nothing Then
            Set area = Tab1.ListColumns([Col]).Range '<--Dica do Felipe Magno
        Else
            Set area = Union(area, Tab1.ListColumns([Col]).Range)
        End If
    Next Col
    If Not area Is Nothing Then area.Select
    
      With Selection.Cells.Validation
        .Delete
        .Add _
            Type:=xlValidateList, _
            Formula1:="=Lista1"
      End With
    End Sub


    quinta-feira, 26 de janeiro de 2017 15:25

Respostas

  • "Acho que o usar de Select ou Selection, deve ser um vicio de inseguranca"

    Ironicamente, usar Select e Selection torna o código menos seguro.

    ---

    "O que imaginei foi ter uma colecao de string com nomes desejadas das colunas da Tabela"

    A melhor prática é você adicionar o Range da coluna na coleção, e não o nome da coluna. Ao invés de adicionar uma String, você adiciona um Range. O range que você quer, no caso, é Tab1.ListColumns([NomeCol]).DataBodyRange (a diferença entre .Range e .DataBodyRange é que .DataBodyRange desconsidera o cabeçalho).

    ---

    Se você usasse uma String, poderia fazer um laço da forma como deseja através de:

    Sub Main()
        Dim iItem As Variant
        
        For Each iItem In Array("Col 2", "Col 4", "Col 6")
            MsgBox iItem
        Next iItem
    End Sub
    
    No entanto, como eu disse, essa não é uma boa prática. Você deve adicionar os DataBodyRange de cada ListColumn na sua coleção.


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    terça-feira, 31 de janeiro de 2017 20:06
    Moderador

Todas as Respostas

  • Ola Pessoal, depois de varias tentativas achei uma solucao que vou dar como solucao.

    Agradeco a todos que tenham acessado este poster, e se tiver outra alternativa melhor do que exposto abaixo, favor comentar. Caso nao tenha outra opiniao fico no aguardo de um Modelador passar por aqui e marcar como resposta para o poster nao ficar com a interrogacao...........ou vou Excluir para nao ocupar espacos....

    Obrigado

    Tadao

    'Procedimento para selecionar colunas pre determinada de uma Tabela para Validacao de Dados
    Sub Validacao()
    
    Dim Tab1 As ListObject, Col As Range, Colecao As Range, Area As Range, NomeCol As String
    'ColDias e nome de uma area dinamica na planilha de nomes das colunas desejadas
    Set Colecao = Worksheets("Aux").Range("ColDias")
    Set Tab1 = ActiveSheet.ListObjects(1)
    
    For Each Col In Colecao
    NomeCol = Col.Value2
    
        If Area Is Nothing Then
            Set Area = Tab1.ListColumns([NomeCol]).Range
        Else
            Set Area = Union(Area, Tab1.ListColumns([NomeCol]).Range)
        End If
        
    Next Col
    If Not Area Is Nothing Then Area.Select
        'Lista1 e uma area dinamica da planilha onde tem relacao de dados de validacao
        With Selection.Cells.Validation
            .Delete
            .Add _
            Type:=xlValidateList, _
            Formula1:="=Lista1"
        End With
    End Sub



    sábado, 28 de janeiro de 2017 07:14
  • Não tem problema nenhuma marcar a própria resposta como solução da pergunta original.

    ---

    Não use o método Select ou objeto Selection. Faça:

    If Area Is Nothing Then Exit Sub
        'Lista1 e uma area dinamica da planilha onde tem relacao de dados de validacao
        With Area.Cells.Validation
            .Delete
            .Add _
            Type:=xlValidateList, _
            Formula1:="=Lista1"
        End With
    End Sub


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    terça-feira, 31 de janeiro de 2017 14:36
    Moderador
  • Ola Felipe, obrigado pelo retorno.

    Acho que o usar de Select ou Selection, deve ser um vicio de inseguranca,nao?. Vou tentar nao usar mais para a rotina ficar mais rapida, obrigado.

    Aproveitando, ja que a postagem e sobre Colecao, acho que talvez esteja fazendo confusao com essa variavel Collection que imaginei.

    O que imaginei foi ter uma colecao de string com nomes desejadas das colunas da Tabela, e a cada for each Col in Colecao, ir colocando em Col os dados obtidos da colecao no codigo abaixo...????xxxx, acho que falei besteira,nao?

    Tab1.ListColumns([Col]).Range

    Sera que nao daria para usar as chaves ou constantes matriciais {  }, por exemplo se tenho tres colunas que desejo fazer a uniao(Col 2,Col 4,Col 6), escrever como abaixo ou coisa parecida?

    For Each Col in {"Col 2", "Col 4", "Col 6"}
    terça-feira, 31 de janeiro de 2017 16:06
  • "Acho que o usar de Select ou Selection, deve ser um vicio de inseguranca"

    Ironicamente, usar Select e Selection torna o código menos seguro.

    ---

    "O que imaginei foi ter uma colecao de string com nomes desejadas das colunas da Tabela"

    A melhor prática é você adicionar o Range da coluna na coleção, e não o nome da coluna. Ao invés de adicionar uma String, você adiciona um Range. O range que você quer, no caso, é Tab1.ListColumns([NomeCol]).DataBodyRange (a diferença entre .Range e .DataBodyRange é que .DataBodyRange desconsidera o cabeçalho).

    ---

    Se você usasse uma String, poderia fazer um laço da forma como deseja através de:

    Sub Main()
        Dim iItem As Variant
        
        For Each iItem In Array("Col 2", "Col 4", "Col 6")
            MsgBox iItem
        Next iItem
    End Sub
    
    No entanto, como eu disse, essa não é uma boa prática. Você deve adicionar os DataBodyRange de cada ListColumn na sua coleção.


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    terça-feira, 31 de janeiro de 2017 20:06
    Moderador
  • Obrigado mais uma vez pelo retorno, Felipe.

    Nao entendi muito bem quando voce falou A melhor prática é você adicionar o Range da coluna na coleção. E que como esta na fase de desenvolvimento, nao queria intervir na codificacao toda vez que alterasse o LayOut da planilha. Se por exemplo aumentasse mais uma coluna Z por necessidade do usuario para atender determinado fim, precisaria mudar os enderecos das Ranges nos codigos toda vez que tenha mudancas na planilha.

    Agora estou refazendo uma pasta em que nao usei muito as Tabelas, e nessa pasta anterior, para evitar intervencao no codigo toda vez que alterasse a planilha, nomeava as celulas e areas que queriam fazer referencias e usava esses nomes no codigos, com o andamento do projeto, ficou tantos nomes que fiquei sem controle, e no fim ja refenciava a proprio endereco da celula ou area. Hoje tenho ate pavor de alterar a planilha com receio de as rotinas nao funcionarem mais.......rs.

    Sim, eu estou usando o Tab1.ListColumns([NomeCol]).DataBodyRange porque desejo mudar as validacoes nos dados das colunas desejadas, mas vi tambem que a Dica do Magno, Range("Tabela1[[" & [NomeCol] & "]]") tambem funciona usando o nome da Tabela que e Tabela1, sem definir o Set Tab1 = ActiveSheet.ListObjects(1) ,nao?

    Outra coisa que notei, e que no codigo abaixo no for each , em vez de colocar Colecao, pensei em colocar diretamente o Range("ColDias"), mas da erro se omitir o Worksheets("Aux"). Sendo o "ColDias" um nome da regiao, isso deve valer para toda pasta,nao?.Pressinto que e cheio de pequenos detalhes que podem travar a rotina.

    Set Colecao = Worksheets("Aux").Range("ColDias")
    Set Tab1 = ActiveSheet.ListObjects(1)
    
    For Each Col In Colecao

    No exemplo que voce fez com Array, declarou o iItem como Variant, ja que ela vai virar String, tentei colocar ja como String, dai deu erro no For Each. Nesse caso do For Each, ela nao aceita String? so com Variant?

    Tadao

    quarta-feira, 1 de fevereiro de 2017 08:46
  • "Se por exemplo aumentasse mais uma coluna Z por necessidade do usuario para atender determinado fim, precisaria mudar os enderecos das Ranges nos codigos toda vez que tenha mudancas na planilha."

    Ao se referir pelo nome das colunas de um ListObject, você não precisa lidar com endereços. Set rng = ThisWorkbook.Worksheets("Plan1").ListObjects("Tabela1").ListColumns("Coluna1").DataBodyRange sempre se referirá à Coluna1 da Tabela1 de Plan1.

    Antes de eu prosseguir com minha resposta para sugerir uma forma mais eficiente, tenho uma dúvida: nessa linha de código:

    Set Colecao = Worksheets("Aux").Range("ColDias")
    
    O que você tem no intervalo ColDias?


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    quarta-feira, 1 de fevereiro de 2017 17:04
    Moderador
  • Ola Felipe, desculpe pela demora do retorno.

    Acho que por falta de traquejo ou conhecimento, estava fazendo uma confusao danada.

    Pelo que percebi, o.....

    Dim Tab As ListObject

    Set Tab=Activesheet.ListObjects(1)

    Tab.ListColumns("Coluna1").DataBodyRange

    ....e mesma coisa que.....

    Activesheet.ListObjects(1).ListColumns("Coluna1").DataBodyRange

    .......que por sua vez e a mesma coisa que...

    Activesheet.Range("Tabela1[[Coluna1]]")

    certo?,por favor, corrija-me se estou escrevendo errado.

    No intervalo "ColDias" ,que fica numa outra.planilha auxiliar numa coluna CE (por exemplo), tem uma relacao dos nomes dos cabecarios da Tabela em que desejo fazer a validacao. Quando o responsavel quiser mudar a coluna da Tabela que deseja fazer a validacao , altera os dados dessa coluna CE que defini como intervalo dinamico . Depois na mesma planilha defini outro intervalo na coluna CA como "Lista1"(alterei para "ValCal"), onde o responsavel  muda o valor do dados que deseja que apareca na lista de validacao. Apertando um botao de Setting, por exemplo, o responsavel altera os dados da validacao conforme a sua necessidade, ficando a pasta de trabalho, multitarefa ou de multiuso. Depois o responsavel deixa essa planilha oculta com senha e entrega para a digitadora preencher os dados.

    Tadao



    sexta-feira, 3 de fevereiro de 2017 12:11
  • As formas de referenciar os intervalos do ListObject que você escreveu são equivalentes, isso mesmo. A forma que menos gosto é a Activesheet.Range("Tabela1[[Coluna1]]").

    ---

    Você está usando uma tabela de parametrização para flexibilizar a formatação na outra tabela, e isso é bom. Só estou achando esquisito o fato de você estar representando todas as colunas da tabela aí. Por que não cria a validação em todas as células da tabela de uma vez então?


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    sexta-feira, 3 de fevereiro de 2017 17:52
    Moderador
  • Obrigado pelo retorno, Felipe.

    Engracado....o que voce menos gosta, e aque mais gosto....escreve menos....sera que e a Lei do menor esforco da minha parte?....rs.

    Gostei do Codigo, ou seja da Palavra Parametrizacao, desculpe o meu esquecimento das palavras mais objetivas que substituiria longas estorias para explicar. Essa planilha vai ser distribuido em varias fabricas e cada fabrica tem horarios e turno diferentes e precisa ser configurado para cada local de uso. Estava ate pensando em fazer um botao para definir a Linguagem, dai depois de selecionado a linguagem, todos os menus dos formularios, botoes e nomes das abas da planilhas mudassem...mas  talvez  isso seria uma trabalheira,nao?. Por enquanto, onde posso, escrevo em japones e entre parenteses em portugues.

    Voce tem razao, no comeco por nao saber o Codigo certo ou a estrategia mais eficaz, e fiquei dando voltas. Agora com Dicas do Magno, nomeei intervalos dentro da Tabela e fiz a validacao conforme abaixo:

    '===================================================================================================
    'Procedimento validar a Area com lista Validacao utilizando intervalos nomeados dentro da Tabela
    Sub ValDados()
    Dim wCal As Worksheet
    Dim Area As Range
    Dim Validacao As String
    Set wCal = Worksheets("Calendario1")
    
    Application.EnableEvents = False
    wCal.Unprotect
    
    'validar a Area com lista Validacao
    Validacao = "=ValCal" 'nome do intervalo onde tem os dados da validacao lista do calendario
    Set Area = [Cal] 'Range do nome do intervalo de calendario dentro da Tabela
    Call Validar(Validacao, Area)
    
    'validar a Area com lista Validacao
    Validacao = "=ValCad" 'nome do intervalo onde tem os dados da validacao lista do cadastro
    Set Area = [DispTrab] 'Range do nome do intervalo de cadastro dentro da Tabela
    Call Validar(Validacao, Area)
    
    Application.EnableEvents = True
    wCal.Protect , DrawingObjects:=False
    
    End Sub
    

    O fato de usar intervalos nomeados ou formulas nas planilhas, estou deparando com o seguinte problema.

    O usuario preenche as planilhas e depois de salvar, so com os valores e suas formatacoes, enviam via e-mail para terceiros. Muitas vezes tem que fazer alteracoes nas planilhas ja enviadas ou salvas, e nelas as formulas e intervalos nomeados ja nao tem, nem a Tabela, so dados. Dessa forma as rotinas de VBA tambem nao vao funcionar, teria que fazer um botao de reativacao colocando os nomes de intevalos ,Tabelas e colocar formulas nas celulas,nao? Sera que e melhor nao usar muito, apesar de ser mais comodo, formulas direto na planilha e usar tudo via VBA?.

    Detalhes nao definidas direito no comeco, no fim do projeto deve dar um trabalhao,nao?.

    Tadao


    sábado, 4 de fevereiro de 2017 05:24
  • "Engracado....o que voce menos gosta, e aque mais gosto....escreve menos....sera que e a Lei do menor esforco da minha parte?....rs."

    Vou explicar por que é o que menos gosto.

    Se, ao acessar ActiveSheet.ListObjects("Tabela1").ListColumns("Coluna1").DataBodyRange obtenho um erro de "subscrito fora do intervalo", tenho que analisar meu problema e responder a seguinte pergunta: "O que não existe na planilha ativa: um ListObject chamado Tabela1 ou uma coluna em Tabela1 chamado Coluna1?"

    Para tal, em modo de depuração, seleciono apenas expressão ActiveSheet.ListObjects("Tabela1") e pressiono Shift+F9. Se aparecer "Subscrito fora do intervalo", sei que não existe o ListObject Tabela1. Se ficar em branco, existe Tabela1 e significa que o que não existe é Coluna1 em Tabela1.

    Em Activesheet.Range("Tabela1[[Coluna1]]"), não é possível eu selecionar apenas parte dessa expressão e pressionar Shift+F9 porque tudo (ListObject + Coluna) está num mesmo literal, o que não ajuda a dividir a resolução do problema por partes.

    Em outras palavras: quanto mais atômico for a representação dos objetos no seu código, melhor.

    E outra coisa: ao fazer um laço, é bem melhor ler .ListColumns(Variavel).DataBodyRange ao invés de .Range("Tabela1[[" & Variavel & "]]")

    ---

    Sobre sua segunda pergunta, eu teria que gastar um tempo analisando seu problema com mais detlahes e entender todo processo para responder melhor, mas infelizmente me falta tempo.


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    sábado, 4 de fevereiro de 2017 19:54
    Moderador
  • Ola Felipe, obrigado ela explanacao bem didatica, vou procurar usar a sua sugestao e deixar de comodismo.

    Aqui e cheio de atalhos interessantes,nao?. Na hora de codificacao, existe alguns atalhos para saber, ou que apresenta uma lista de intervalos nomeados da pasta?, ou variaveis publicas declaradas em outros modulos?

    Dentro de uma atividade intensa de trabalho, agradeco a sua atencao, mas se puder gostaria de saber como voce normalmente faz, se costuma usar formulas na planilha para obter resultado ou fazer formulas no VBA e colocar resultado na planilha?. Acho que cada caso deve ser analizado, mas o que vi o usuario fazer, e que ele deixava as programacoes de dias posteriores prontas para depois so revisar, mas eu fiz uma rotina que ele possa salvar as planilhas so com valores para serem enviadas para terceiros, entao quando ele queria revisar uma planilha salva so com valores, ficava copiando e colando na planilha com formulas. Fiquei com do o usuario...

    Tadao

    sábado, 4 de fevereiro de 2017 23:43
  • "se costuma usar formulas na planilha para obter resultado ou fazer formulas no VBA e colocar resultado na planilha?"

    Geralmente faço os cálculos através de fórmulas, mas isso pode variar bastante. Depende da natureza do meu projeto.

    Se tem um banco de dados, deixo muitos dos cálculos no banco de dados, por exemplo.

    Deixar fórmulas demais na planilha pode ser complicado às vezes porque, dependendo da quantidade de dados que se tem, pode ficar pesado.

    O lado ruim de se colocar fórmulas no VBA é que é mais difícil fazer a manutenção do código.

    Enfim, varia bastante e com um pouco de experiência identifica-se qual melhor arquitetura para usar.


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    domingo, 5 de fevereiro de 2017 13:41
    Moderador
  • Obrigado pelas Dicas Felipe.

    Com o passar do tempo, espero poder identificar a melhor forma.

    Tadao

    domingo, 5 de fevereiro de 2017 15:12