O Excel cria planilhas inexistentes no VBA Project Explorer

Question

O Excel 2010 Professional Plus (32 bits) parece ter criado um novo conjunto de planilhas e renomeado todas as planilhas originais na tela do VBA, por exemplo, Folha10 torna-se a Folha101, a Folha13 torna-se a Folha131. Isso faz com que o UDF pare de funcionar. O "novo" "sheet10" e "sheet13" não parecem existir em qualquer lugar, mas na janela do projeto VBA. As "novas" folhas têm um ícone azul ao lado delas.

O UDF funciona. Até que essas novas folhas sejam criadas e as folhas antigas sejam renomeadas. Existem apenas 12 planilhas & uma pasta de trabalho no arquivo do Excel. O VBAproject mostra 2 pastas de trabalho & 20 planilhas.

1) O que causa isso?
2) Como posso consertar isso?
3) Como isso pode ser evitado?

Imagensadicionadasmostrandoaspropriedadesdeinexistentede"sheet3" e propriedades da folha real "sheet31". Todas as folhas inexistentes & pasta de trabalho tem uma longa lista de propriedades.

Arquivo XLS aqui Você terá que fazer o download, pois ele será aberto no google visualizador.

ATUALIZAÇÃO: 1/6/2016 Então, a partir de hoje, as fórmulas neste XLS são todas erros (#VALUE) quando abri-lo. O Excel não criou as folhas inexistentes, como visto na minha última atualização. Na semana passada, o XLS & fórmulas estavam funcionando e não fiz alterações. Esta é a nova pasta de trabalho para a qual copiei todas as folhas conforme sugerido nos posts abaixo. A pasta de trabalho original (aquela mostrada no pix com as planilhas não existentes) não possui os erros #VALUE. Ambas as pastas de trabalho estão no mesmo computador e foram atualizadas juntas no último mês + para fins de comparação.

ATUALIZAÇÃO3, 1/6/2016 Eu acidentalmente movi uma célula de texto, cliquei em desfazer e todos os erros #VALUE foram embora e agora tenho todos os cálculos corretos. WTF.

    

Answer 1

A chave para o seu problema é a caixa de mensagem "Erro de automação. Falha catastrófica" .

Não existe um único diagnóstico para ele, mas pode ser ...

• Caso 1

Se uma pasta de trabalho estiver tentando executar uma macro que contenha objetos, ela não será incluída na seção de referências do editor do VBA, esse tipo de erro poderá ocorrer.

Por exemplo, se é necessário que uma DLL específica seja registrada corretamente no PC. Se essas DLLs estiverem no seu computador, a pasta de trabalho estará bem, mas em outro PC sem as DLLs, isso acontecerá.

Qualquer projeto incluirá o seguinte como padrão:

Visual Basic For Applications
Microsoft Excel x.0 Object Library
OLE Automation
Microsoft Office x.0 Object Library
and possibly if a form has ever been added -
Microsoft Forms 2.0 Object Library

Onde x é a versão do Excel.

Você abriu e modificou a macro em outro PC antes de executá-la?

É isso, então as referências foram atualizadas para a versão do outro sistema e não foram encontradas no seu sistema.

• Caso 2

Uma de suas UDFs está causando o problema. Recrie o problema e verifique a função destacada em amarelo.

Em conformidade com as imagens que você postou, o problema está em Sumbytext(rg as Range,ltr as String)as Double . Você precisa:

• Rastreie a lógica do UDF, passo a passo, para descobrir onde ela pode estar falhando.
• No intervalo nomeado "List_HolAbbr" são sempre valores? Qual?
• A UDF recebe um intervalo na variável rg . É sempre um intervalo válido?
• Como eles usam valores inteiros, por que Sumbytest e MidResult são definidos como Double?

Answer 2

Revendo minha solução de este outro segmento .

Aqui está minha solução, ela funciona de maneira consistente e você não precisa copiar manualmente as folhas e o código para uma pasta de trabalho em branco. Eu testei esse método em várias pastas de trabalho corrompidas que estavam me dando o erro "Erro de automação - Falha catastrófica" na inicialização.

NOTA: O arquivo original corrompido foi salvo como .xlsm

1. Abra uma pasta de trabalho do Excel em branco
2. guia Desenvolvedor > Segurança de macros > Desativar todas as macros sem notificação
3. Fechar o Excel
4. Clique duas vezes no arquivo corrompido, por exemplo, MyFile.xlsm
5. Arquivo > Salvar como ... > MyFile.xlsb (não .xlsm), escolhendo o formato .xlsb é o que faz o truque
6. guia Desenvolvedor > Segurança de macros > Ative todas as macros (ou qualquer nível de segurança que você preferir)
7. Fechar o Excel
8. Clique duas vezes em MyFile.xlsb

O arquivo foi corrigido agora! Você pode salvar novamente o arquivo MyFile.xlsb como .xlsm, se necessário. Na minha experiência, arquivos .xlsm são corrompidos facilmente, então vou usar o formato .xlsb.

Espero que alguém ache isto útil:)

    

Answer 3

Minha solução é semelhante à solução .xlsb publicada pelo @Scoox (obrigado!), mas tive que modificá-la porque não consigo alterar as configurações de macro no meu PC de trabalho:

1. Faça cópia de todas as macros em arquivos de texto
2. Salve o arquivo corrompido como .xlsx e feche
3. Abra novamente o arquivo, salve como .xlsb e feche
4. Cole todas as macros no arquivo .xlsb

Não é ideal, mas funcionou para mim!