I'm not entirely sure how well-known this problem is; however, I was trying to construct a 'data model' of the structure of a given Workbook. So when I'm manipulating and interacting with the data within the workbook, it doesn't require insane numbers of variables to track things.
I'm making something that registers people for a certain event, in doing so, it keeps track of state using named ranges, and tables. Which upon finalizing the registration, it moves the data into a transaction table.
I didn't want to go to the effort of using Access because the transaction quantity is quite low (guaranteed to be below 500) and I figured automating the ability to construct a series of classes that represents the data in an excel workbook could be handy in the future.
The end goal was taking something like this:
And turning it into this:
I tried initially making 'DataModel' a public property on a module, where it would just set it to the name of the workbook. The workbook would implement the type for the DataModel, the worksheets would implement the data model represented by each sheet.
Simple, right?
Worked great until I saved and reloaded excel, found that nothing worked. Kept getting ambiguous application error 32809. Noticed the xlsm was really bloated, and decided to see what would happen if I removed the implements from the worksheet and workbook. Size dropped over 150 KB and it stopped giving error messages! I realized this was indeed the fix when I removed those portions and only encountered it on one worksheet. Which I looked at an earlier version and noticed I hadn't removed the Implements TypeName and associated private implementation.
Is this a well known issue in Excel? Version used is Excel 2013. If it's well known they should make it impossible to use Implements within the worksheet.
Also, is what I've done above able to be done already, in a way that's really easy?
The idea is you stabilize your data structure and run a macro which builds the data model framework. Being able to Add to a table with a series of optional values seemed ideal, since it takes care of the dirty work. Pushes a lot of code into the workbook, but the time it saves is worth it. Much cleaner code, IMO...?
Bookmarks