+ Reply to Thread
Results 1 to 9 of 9

Excel Add-in to Clean Excess Styles

  1. #1
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Excel Add-in to Clean Excess Styles

    I have created an Excel Add-in for the purposes of counting styles in the active workbook and deleting all but the built in styles. This is especially useful in files that have excess style, up to the maximum allowed by Excel, which is roughly 65,000. Excess styles can cause performance issues, errors and corruption if left un-checked.

    Not only do I want to make the addin available to others, I am hosting the add-in and its code in a Github repo so that others may see the VBA code, change/use it how they want and maybe even contribute back to making it better.

    The repo can be found here:
    https://github.com/Zer0CoolX/Excel-CleanStyles

    I would love to hear peoples results with this. If you can share sample copy/copies of workbook(s) with excess styles for me and/or others to test that would be great.

    The Github repo contains more details on the add-in (still working on documenting everything) but make it clear, this add-in clears out excess styles beyond the default ~47 built in styles. It will not correct/delete corrupt styles. I am working on another add-in to handle files with excess styles AND corrupt styles (not yet available).

    Hope this helps others, Thanks.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  2. #2
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Excel Add-in to Clean Excess Styles

    This styles thing is an interesting problem.

    One thing I found with this is approach was my users often put so many styles in a sheet that the macro fell over, so it couldn't handle the worst problems (and even where it could it took ages to run, which is a pain). Maybe i needed more "do events" or something.

    So I just do it with direct (vba) manipulation of the styles.xml.

    Not sure if there are any risks there - so far seems to work perfectly every time and fast.

    And possibly to be more useful to you - you misspelled percentage. Yours works fine on the sample I had, I'll try to test it on something challenging if I find it.
    Last edited by scottiex; 12-05-2018 at 03:48 PM. Reason: xml not xlsm duh! And needed to change tone.
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.

  3. #3
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Excel Add-in to Clean Excess Styles

    Ran it on a nice really big file and it worked, took 6 mins on my computer with about 51k styles.

  4. #4
    Registered User
    Join Date
    12-05-2018
    Location
    Kalamazoo, MI
    MS-Off Ver
    Excel 16
    Posts
    6

    Re: Excel Add-in to Clean Excess Styles

    That would be really nice!

  5. #5
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Excel Add-in to Clean Excess Styles

    Quote Originally Posted by scottiex View Post
    One thing I found with this is approach was my users often put so many styles in a sheet that the macro fell over, so it couldn't handle the worst problems (and even where it could it took ages to run, which is a pain). Maybe i needed more "do events" or something.

    So I just do it with direct (vba) manipulation of the styles.xml.

    Not sure if there are any risks there - so far seems to work perfectly every time and fast.
    Yes as I mentioned I have a version that handles excess AND corrupt styles. I accomplish this by manipulating the underlying xml as well. If you can share your code (in a PM, not in this post please) I would love to compare notes, maybe even combine the 2 into something. The problem with mine is that sometimes the breaking down of the file (a copy) from zip and rebuilding doesnt work properly and it fails, usually a second run works. I wanted to work out some quirks in it before offering it to others.

    Ill have to review for the misspelling.

    EDIT: I added some touches to my current macro/addin to try and make it as stable and complete as possible. First, I loop the collection of styles backwards which seems to have a big impact on speed and stability. Also, I set a property of the style that many other projects online do not account for, the .locked property. Honestly, the Do Events in mine is only for the status bar. Its my experience that Do events should be used sparingly (hence it being only called every x iterations of the loop) and only when nothing else works. I have to still document it but I also have some checks at the beginning to avoid problems with things like shared workbooks and locked sheets. I also have a threshhold amount of styles to test for when complete to determine if it was cleaned properly. This threshold can be adjusted by a constant and can be set really strict (only the 47 built in) or a little less prohibitive (like the 67 its set to by default) allowing for a few stragglers to be acceptable upon completion.

    Thanks for testing it out.
    Last edited by Zer0Cool; 12-06-2018 at 07:58 AM.

  6. #6
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Excel Add-in to Clean Excess Styles

    I haven't had trouble with my file edit version, so hopefully it will be helpful.

    Yes I noticed that lock property as additional, does that cause it to fail (as I had running standard code) or does it just leave stragglers if it is set wrong?

  7. #7
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Excel Add-in to Clean Excess Styles

    I found that without that line, other peoples macros would be unable to delete the style(s) that had the quirk. Depending on how they did their macro it would either skip it (IE: if they used on error resume next) or stop and not process further. What the line does is set the property to false so that it can delete it. The only styles I know this macro cannot handle are corrupt ones, which generally have broken tags in the underlying xml.

  8. #8
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Excel Add-in to Clean Excess Styles

    OK trying to PM the code but it complains about html tags...

  9. #9
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Excel Add-in to Clean Excess Styles

    Quote Originally Posted by Zer0Cool View Post
    EDIT: I added some touches to my current macro/addin to try and make it as stable and complete as possible. First, I loop the collection of styles backwards which seems to have a big impact on speed and stability. Also, I set a property of the style that many other projects online do not account for, the .locked property. Honestly, the Do Events in mine is only for the status bar. Its my experience that Do events should be used sparingly (hence it being only called every x iterations of the loop) and only when nothing else works. I have to still document it but I also have some checks at the beginning to avoid problems with things like shared workbooks and locked sheets. I also have a threshhold amount of styles to test for when complete to determine if it was cleaned properly. This threshold can be adjusted by a constant and can be set really strict (only the 47 built in) or a little less prohibitive (like the 67 its set to by default) allowing for a few stragglers to be acceptable upon completion.

    Thanks for testing it out.
    Yes it is great that you have decided to take this on and do it properly. I'm sure it will be widely appreciated. as per KSN above.

+ 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. How to clean up corrupt styles in excel 2013
    By Dave H in forum Excel General
    Replies: 12
    Last Post: 02-18-2022, 05:25 PM
  2. [SOLVED] open the styles.xml and edit it to have no styles
    By scottiex in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-12-2018, 07:02 PM
  3. Replies: 3
    Last Post: 09-05-2015, 09:18 AM
  4. [SOLVED] Coding Convention Questions #2 (To clean up or not to clean up)
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 03-31-2014, 08:18 PM
  5. Replies: 1
    Last Post: 03-08-2013, 02:05 PM
  6. [SOLVED] VBA Code to optimize and clean data- clean out numerical/ or symbol
    By tracylsr in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-05-2012, 04:21 PM
  7. Clean Function to Clean Entire Sheet
    By gema in forum Excel General
    Replies: 7
    Last Post: 11-05-2009, 10:07 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