User-defined type not defined

[ShogunStealth]

I have recommenced on a path to learn about VBA and remembered how exciting it is coding and when everything is going great but also how frustrating when they don't . I recent begun a project were I would take some raw data in a XLSX format (1000's of lines) and do a number of computations on the columns with VBA formatting, sorting and filtering the data in a particular way. The raw data on USB drive contains several files unfortunately I can not share these files. I had successfully planned to built a XLB to share the code with other people I work with, then about a week ago roughly after blackout and my virtual session with my work blue screen of death cause my laptop to crash when I was working on the project. I begun to receive a peculiar error "User-defined type not defined" seemingly out of the no where.

I conducted a number of tests to isolate the issue. Here what I have done;

• I have removed all modules in the vba editor - after saving them of course
• Removed the Personal.xlb file from XLSTART in my roaming profile
• re booted

This is how the error appears which is really strange, when I open any of the files on the USB even if copy them to my desktop [local copy] there is no issue when I start typing in any of the cells even it is is plain text in either a blank or and existing cell, all good. However I select "Developer" tab then press the button in the ribbon "Visual Basic" then go back the spreadsheet and type just plain text like "cow" not even a formula in another cell I get this error (image attached). It only occurs on the raw data files that either on or from the USB. It doesn't happen with and new empty files or files I have on my laptop. Btw selecting Ok allows the entry until I type in the next cell.

Please note it is NOT a "Compile" error in which 100's or help is available but nothing for this

To be clear there is no more vba code, it s been removed. Is it possible I have some corruption in the application? I had no issue with these files previously but only after BSOD. I would appreciate any thoughts and help

 

[Dermot]

Assuming you saved the file as .XLSM, it is just a zip file
Change the file extension to .zip and you will be able to unzip it normally and see the files inside. All of them can be read in a text editor.
You can google to find out what file names should be inside the zip, and what they should contain
Hopefully you can find what is causing the problem.

PS This is a lesson to create new versions as you work, so if something goes wrong, you can go back to the previous version. I also set Excel so that it creates and saves a backup of the previous file on the disk, every time it saves (google for how to do this)

 

[ShogunStealth]

The raw data is a set of files that is an XLSX extended files generated each month. It made sense that creating an XLB file that either I could share host the code in one place [their own XLSTART location] with other users would mean that they would just run one or more marco's., so saving as XLSM means I have copy the code each month between files and not just run it from the Personnal Macro Workbook.
I believe that I may on not indicated the symptoms correctly. Whilst I have removed all of the modules intentionally I am still getting the error when I type content into any cell in the raw data files see image. When selecting ok it places it in and them the same error for every other celll I write into. I may be missing something but I have no need to zip or unzip any files. To be clear, it appears regardless of code being there the error occurs for these files that didn't have an issue before the BSOD. I cannot download a new set of raw data as i am working from home due to COVID.
Is it possible to accidentally or enter raw data the excel thinks it is user-defined type?

 

[johnnyL]

What do you have checked in the Tools/references section of the Developer/visual Basic tab?

 

[ShogunStealth]

Hi JohnnyL, image attached

 

[johnnyL]

That looks ok. Have you tried copying the data from each of the USB drive files to a new workbook and see if the error continues with the new workbook?

 

[ShogunStealth]

Yes and no, only this morning (au time), I may of come up with a possible solution???? But I don't know why.

When all of the workbooks (original raw data) are open the error remains when paste into new workbook, however after copying the data and if I close Excel and when it asks me to leave or remove everything from the clipboard I say Yes and the application closes. Then I open a new workbook (cannot remember new or existing blank one) and paste it in and close / save the new one and then open it again the error doesn't appear.

 

[ShogunStealth]

Sorry, say Yes to leave on clipboard

 

[ShogunStealth]

Thank you everyone, I've created a workaround without identifying the issue.

Can someone point me in the right direction to close this thread?

 

[johnnyL]

The moderators close threads when they feel it is warranted. Regular members do not have the option to close a thread.