none
Utilizar o comando Application.WorksheetFunction.Index com um vetor criado no gerenciador de nomes do Excel RRS feed

  • Pergunta

  • É possível aplicar o comando Application.WorksheetFunction.Index em um vetor montando a partir de fórmula pelo gerenciador de nomes do Excel?

    Ao utilizar o comando com um nome do gerenciador de nomes que se refira a um intervalo de células, o resultado é retornado normalmente, mas ao utilizar o mesmo comando com um nome do gerenciador de nomes que é formado a partir de um cálculo, dá erro de tempo de execução '1004'.

    Por exemplo, criei um nome que gera um vetor com 0 ou 1 baseado em critérios de filtro de um BD, chamado "Filtro_BD" então o vetor fica neste padrão: {0,1,0,0,0,0,1,0,1,0}

    Utilizando a função ÍNDICE na planilha, consigo retornar qualquer valor (Ex: =ÍNDICE(Filtro_BD;2) -> retorna o resultado 1). Mas tentando obter este resultado com o comando Application.WorksheetFunction.Index dá erro

    Application.WorksheetFunction.Index(Range("Filtro_BD"), 2)
    
    
    Testei de várias maneiras, mas acho que este comando só aceita um range de células.


    Rafael Kamimura

    quarta-feira, 22 de outubro de 2014 11:13

Respostas

  • "uma das formas de deixar um código mais rápido é buscar calcular funções direto no código via comando WorksheetFunction ao invés de mandar o código escrever a função na planilha."

    Não fiz benchmark aqui, mas não sei se essa é a melhor forma de ganhar tempo. Eu preferiria utilizar o método Evaluate nesse caso, como por exemplo:

    Range("A1") = Evaluate("=SUM(B1:B3)")

    De qualquer forma, já que o método Worksheet.Index não é exatamente igual à função ÍNDICE, sugiro que faça algo como:

    Debug.Print WorksheetFunction.Index(Evaluate("Filtro_BD"), 1, 1)

    Em que o 2o. argumento é a linha e o 3o. argumento é a coluna do nome definido.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    • Marcado como Resposta Rafael Kamimura quinta-feira, 30 de outubro de 2014 15:27
    quarta-feira, 29 de outubro de 2014 19:53
    Moderador

Todas as Respostas

  • Rafael, não consegui reproduzir seu problema. Criei um nome definido cuja expressão era =DESLOC(Plan1!$H$7;;;11) e pra mim seus dois exemplos funcionaram normalmente.

    Teria como disponibilizar sua pasta de trabalho para darmos uma olhada?


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    sexta-feira, 24 de outubro de 2014 21:45
    Moderador
  • Rafael,

    faltou somente indicar em qual guia está esse range...

    debug.Print Application.WorksheetFunction.Index(Sheets("Plan1").Range("Filtro_BD"), 2)


    Natan

    sábado, 25 de outubro de 2014 11:54
  • Olá Felipe!

    Na verdade eu estava fazendo diversos testes em um projeto que estou desenvolvendo no trabalho, e li num site americano que uma das formas de deixar um código mais rápido é buscar calcular funções direto no código via comando worksheetFunction ao invés de mandar o código escrever a função na planilha. Nestes testes me deparei com a função índice e neste projeto estou trabalhando muito com o gerenciador de nomes de forma matricial.

    Da forma que você expôs, de fato funciona, pois a função DESLOC retorna um intervalo novo na planilha. O que estou tentando fazer é aplicar o comando worksheetFunction numa matriz gerada. Por exemplo, veja a imagem abaixo:

    Observe que a função ÍNDICE na planilha funciona normalmente para a matriz Filtro_BD. A matriz Filtro_BD pode ser observada na avaliação da fórmula:

    A minha dúvida é aplicar no VBA a função worksheetFunction.Index no Filtro_BD.

    Eu acho que não é possível...


    Rafael Kamimura

    sábado, 25 de outubro de 2014 16:35
  • Rafael,

    tu testou a forma que enviei?


    Natan

    sábado, 25 de outubro de 2014 23:21
  • "uma das formas de deixar um código mais rápido é buscar calcular funções direto no código via comando WorksheetFunction ao invés de mandar o código escrever a função na planilha."

    Não fiz benchmark aqui, mas não sei se essa é a melhor forma de ganhar tempo. Eu preferiria utilizar o método Evaluate nesse caso, como por exemplo:

    Range("A1") = Evaluate("=SUM(B1:B3)")

    De qualquer forma, já que o método Worksheet.Index não é exatamente igual à função ÍNDICE, sugiro que faça algo como:

    Debug.Print WorksheetFunction.Index(Evaluate("Filtro_BD"), 1, 1)

    Em que o 2o. argumento é a linha e o 3o. argumento é a coluna do nome definido.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    • Marcado como Resposta Rafael Kamimura quinta-feira, 30 de outubro de 2014 15:27
    quarta-feira, 29 de outubro de 2014 19:53
    Moderador
  • Valeu a dica Felipe!

    Como eu disse, estava apenas realizando alguns testes para testar o desempenho do cálculo no VBA vs Excel.

    Apenas como informação, segue link do artigo que estava lendo com as dicas para tentar deixar o código mais rápido.

    http://www.databison.com/how-to-speed-up-calculation-and-improve-performance-of-excel-and-vba/

    Abraços!


    Rafael Kamimura

    quinta-feira, 30 de outubro de 2014 15:27
  • Natan, obrigado pela disposição, mas a sua proposta não funciona para este caso do exemplo que apresentei, pois o nome filtro_BD não se refere a um intervalo na planilha, mas é um vetor de resposta a uma fórmula matricial.

    Rafael Kamimura

    quinta-feira, 30 de outubro de 2014 15:29
  • Opa, obrigado Rafael, depois vou dar uma olhada.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    segunda-feira, 10 de novembro de 2014 20:03
    Moderador