+ Reply to Thread
Results 1 to 15 of 15

Macro freezes... Too many words?

  1. #1
    Registered User
    Join Date
    08-09-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2011
    Posts
    13

    Macro freezes... Too many words?

    Hello!

    I've been using a macro that will find certain words, enlarge them, and make them red so they're easily visible. I've used this for about 30 words and had no problems. I tried using the same macro, but with around 950 words and the editor just freezes and never runs the macro. Is there a workaround for this or are there just too many words?

    Thanks in advance!

    Oh, and here is a sample of the macro:

    Please Login or Register  to view this content.

  2. #2
    Forum Contributor
    Join Date
    11-05-2013
    Location
    Germany
    MS-Off Ver
    Excel 2013
    Posts
    120

    Re: Macro freezes... Too many words?

    Put this in after the redim Findword

    Please Login or Register  to view this content.

    and this before end sub

    Please Login or Register  to view this content.

    basically it stops everything which is not needed. Excel tries to show you every calculation step live on the screen otherwise which is too much for every computer at a certain point of data volume. I always use it in larger macros etc.


    Edit: I copied that from several sources on the internet. I found it when I had a similar problem as you. Probably you only need the screenupdating one though.

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Macro freezes... Too many words?

    Rather than all of that typing, why not just put the list of words on a separate sheet and read it in at the beginning?
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Contributor
    Join Date
    11-05-2013
    Location
    Germany
    MS-Off Ver
    Excel 2013
    Posts
    120

    Re: Macro freezes... Too many words?

    shg. Do you mean it will be faster for his code to run on a seperate sheet or just easier to write the code (because less findword stuff) in general when he uses them on a seperate sheet?

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Macro freezes... Too many words?

    No, I mean that if you put the words on a sheet, the code can read them in. Then you can add or remove words without changing code.

  6. #6
    Registered User
    Join Date
    08-09-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2011
    Posts
    13

    Re: Macro freezes... Too many words?

    can you expand on this please?

  7. #7
    Registered User
    Join Date
    08-28-2013
    Location
    Phoenix, Arizona
    MS-Off Ver
    Excel 2010, 2013
    Posts
    69

    Re: Macro freezes... Too many words?

    Why not use conditional formatting with a reference to an index or lookup?

  8. #8
    Registered User
    Join Date
    11-12-2013
    Location
    NZ
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Macro freezes... Too many words?

    He means....
    1) List all the words you require on a separate excel sheet.
    2) Use a function such as compare, vlookup etc to compare/match words. ( you can write your own macro for this as well)
    3) if it matches then either enlarge the word or maybe have a colour code beside it.

    The reason for doing it this way is that having all the words on a separate sheet makes looking for it easier, editing easier, deleting easier, etc etc etc..

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Macro freezes... Too many words?

    can you expand on this please?
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    08-09-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2011
    Posts
    13

    Re: Macro freezes... Too many words?

    Thank you for clarifying. It makes sense now. However, I believe there might be a small mistake in the code. Excel won't run it.

    Quote Originally Posted by shg View Post
    Please Login or Register  to view this content.

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Macro freezes... Too many words?

    That's not much help. Exactly what happens when you try?

  12. #12
    Registered User
    Join Date
    08-09-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2011
    Posts
    13

    Re: Macro freezes... Too many words?

    A pop-up window appears with an error message "Compile error: End With without With" (referring to the last End With in the code). I tried deleting it and running it again and I get another error message "Compile error: Next without For" (referring to the second to the last line Next wks)

    Quote Originally Posted by shg View Post
    That's not much help. Exactly what happens when you try?

  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Macro freezes... Too many words?

    Ah, sorry:

    Please Login or Register  to view this content.
    Last edited by shg; 12-03-2013 at 06:25 PM.

  14. #14
    Registered User
    Join Date
    08-09-2013
    Location
    Los Angeles
    MS-Off Ver
    Excel 2011
    Posts
    13

    Re: Macro freezes... Too many words?

    Ah so close!! Now I just get an error message that says, "Run-time error '1004': Application-defined or object defined error". I googled why I get that message and it pretty much says that it happens when an Excel chart contains more legend entries than there is space available to display the legend entries on the Excel chart. The problem is that I'm not using a chart. I simply listed all the words on a separate sheet within the same workbook. Any suggestions?


    Quote Originally Posted by shg View Post
    Ah, sorry:

    Please Login or Register  to view this content.

  15. #15
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Macro freezes... Too many words?

    What line fails?

+ 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] Screen Scrolling Macro (VBA) freezes
    By kencoburn in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 03-21-2012, 01:07 PM
  2. [SOLVED] Macro freezes spreadsheet
    By davemel in forum Excel General
    Replies: 0
    Last Post: 08-01-2006, 03:30 AM
  3. [SOLVED] Macro freezes after using Filters.
    By davemel in forum Excel General
    Replies: 2
    Last Post: 07-31-2006, 08:48 AM
  4. [SOLVED] navigation macro freezes
    By cmarch in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-16-2005, 08:05 PM
  5. [SOLVED] Macro freezes up Excel
    By Jon Delano in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-11-2005, 03:05 PM

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