+ Reply to Thread
Results 1 to 9 of 9

Implements TypeName on Worksheet Causes Corruption

  1. #1
    Registered User
    Join Date
    10-19-2014
    Location
    United States
    MS-Off Ver
    2013
    Posts
    10

    Angry Implements TypeName on Worksheet Causes Corruption

    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:
    Please Login or Register  to view this content.
    And turning it into this:
    Please Login or Register  to view this content.
    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...?

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    Re: Implements TypeName on Worksheet Causes Corruption

    Quote Originally Posted by Allen Copeland View Post
    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.
    Hi,

    You'd be better advised to upload your workbook and manually add the table of results you want to see after you've updated your registration events database.
    If I'm interpreting your requirement correctly this could be achieved with two or three lines of code that run Excel's Advanced Data Filter.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-19-2014
    Location
    United States
    MS-Off Ver
    2013
    Posts
    10

    Question Re: Implements TypeName on Worksheet Causes Corruption

    Um... Not really sure how an advanced filter will move data from one table into another, but perhaps you could explain?

    The general idea is the event is something that happens every ten minutes and the person handling the registration selects the person's preferred times:

    A person can register in multiple transactions, times in red are a previous transaction(s) selections, blue times are new selections. Times which are full are grayed out (not visible above.)

    This data is then presented on the next screen:
    As this is filled out the boxes turn white, if the person had registered before, the 'First Name' and 'Last Name' would be locked.

    Once 'Finalize' is clicked and the person verifies everything, it prints receipts, and moves the selected times (one table) into another 'Registration' table. The code I posted twice shows how I did the movement from the original data sets to the new data set. The second version shows the new version using the 'Model' that I built through VBE within VBA.

    I noticed the issue with error 32809 by this VBE implementing the model within the sheets themselves and subsequently causing the VBA Interpreter to stop functioning. I can't divulge more concrete information due to confidentiality reasons.
    Last edited by Vaibhav; 12-04-2014 at 03:07 AM.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,113

    Re: Implements TypeName on Worksheet Causes Corruption

    So, still no workbook?
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    10-19-2014
    Location
    United States
    MS-Off Ver
    2013
    Posts
    10

    Question Re: Implements TypeName on Worksheet Causes Corruption

    I was going to do a complicated example demonstrating moving data using the automated model, but once I reproduced the error, I stopped.

    The code within is automatically generated.
    Example.xlsm
    Example-Fixed.xlsm

    Example.xlsm on my end Doesn't do anything when 'Process' is clicked', but the fixed version does. Both clearly do the same thing, the only difference is one does not work. The 'Implements' portion of the code is likely the culprit.

    Also, browse the automatically generated code, is it necessary or is there an easier way that I'm not aware of? It's not really intended for simple worksheets but rather ones that requires very controlled data-entry that should always work, without requiring lots of variables to account for table column adjustments.

    So I suppose the question is two fold: Is this error known, and if it is, is there any way around it other than the '-Fixed' version? The idea behind this is: I have a document being used in a live environment that will need to be interacted with and possibly have its data hoisted. Spending a while doing it the hard way by pulling out listcolumns, and iterating the rows using listrows, and so on, isn't something I wanted to do. The Implemented version is intended to be the live version of the data within the active document, where you could pull in another file which mirrors the data format and interop with it, with ease.

    Sort of a way to simplify iterative development in the event that say: you have one document that you created to handle registration. You want to develop a second document that will use the original document's data to perform event-based check-in. Once the original version goes live, you can't touch it until 'Check-in' for said event occurs. So the logical course is develop a version separately that will be able to pull in the original's data. I could do the replication by hand, but the point here is it needs to work very quickly and be guaranteed to get everything. Human error as an element needs eliminated. I'm simply trying to make that process easier, while taking into account that the order of the data might change (but the names will stay the same.)

    Suggestions?

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,144

    Re: Implements TypeName on Worksheet Causes Corruption

    I'm not sure whether anything is documented anywhere, but I've seen issues when trying to make Worksheets Implement an interface.

    FWIW I think you'd have been better off with a database though

  7. #7
    Registered User
    Join Date
    10-19-2014
    Location
    United States
    MS-Off Ver
    2013
    Posts
    10

    Re: Implements TypeName on Worksheet Causes Corruption

    If using a database was an option, I'd be using it.

    It's more a matter of the target audience and their familiarity with certain programs. Something new is scary, it's less scary when it looks familiar.

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,144

    Re: Implements TypeName on Worksheet Causes Corruption

    So use a database on the back end and an excel client?


    Sent from my iPhone using Tapatalk

  9. #9
    Registered User
    Join Date
    10-19-2014
    Location
    United States
    MS-Off Ver
    2013
    Posts
    10

    Re: Implements TypeName on Worksheet Causes Corruption

    Have a good resource for learning the ins and outs of integrating Excel with Access, that can show: setting up the link to the data, performing an insert, delete, and an update?

    Details on how excel handles links to those data sources and how to resolve conflicts and the sort?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] TypeName limitation: How to distinguish Worksheet Variable from Workbook variable
    By bagullo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-02-2013, 07:35 AM
  2. Peculiar TypeName Issue
    By mwynn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-01-2011, 03:47 PM
  3. Add-in corruption
    By Okkitrooi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-24-2011, 08:07 PM
  4. implements statement
    By Hemant_india in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-29-2006, 12:20 PM
  5. VBA "TypeName" international issues
    By johnfromlondon@gmail.com in forum Excel General
    Replies: 2
    Last Post: 08-02-2005, 10:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1