+ Reply to Thread
Results 1 to 17 of 17

Compile error: Procedure too large. Executing macro via hyperlink.

  1. #1
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Compile error: Procedure too large. Executing macro via hyperlink.

    Hi all
    I have a code that allows me to execute macros simply by clicking on a cell. This VBA code must be placed within specific sheet. All this was working really well until I had a need to add a some more cells that would activate certain macros. Now when I added loads more of lines to the macro it prompts me with an error.
    Current code that I have is about 360 lines and it works fine. This code covers one table (4x23 i.e. E7 to I29) where clicking each cell activates certain macro. Now I have a need to do sort of the same thing and have a code that covers some more tables that are within the same sheet. My problem is that I do not know how to make /or split it into separate codes. I only understand how to expand the same code. But to cover each new table it would mean that the current code would expand by around 300 lines. And looks like excel can not cope with too many lines or something. I tried to run macro that covered two tables. The code was 700 lines. It prompted me with compiling error "Procedure too large".


    I will provide example of the code below. I have edited it into about 50 lines to show the idea of the code.

    Please Login or Register  to view this content.

    Does anyone know how to start a new code so I can have a separate code for each table.

    Any ideas very welcome.
    Cheers
    Rain
    Last edited by rain4u; 06-03-2011 at 07:36 PM.

  2. #2
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    I came up with completely new solution to overcome my problem but I still need some help with the code.
    I will place x4 OptionButtons to the sheet. Not to be confused with tickboxes. With OptionButtons I mean the ones that you can click and little dot will appear inside. The ones when only 1 can be clicked i.e. as soon as you click any other box the little dot will move to that clicked box.

    Now I would like to have this code but slightly ammended:
    Please Login or Register  to view this content.


    Now I would like to add this feature. When clicked to any cell listed in the code it would first check which OptionBox is currently ticked to determine which code to launch.

    OptionButton1 = Application.Run "copy_master_to_hypelink_all_sites"
    OptionButton2 = Application.Run "copy_master_to_hypelink_Basildon"
    OptionButton3 = Application.Run "copy_master_to_hypelink_Burnley"
    OptionButton4 = Application.Run "copy_master_to_hypelink_Ilkeston"

    then it would carry on launching macros as per addressed target i.e. if it was E7 that was clicked then as per code it would run Application.Run "copy_master_to_hypelink_all_sites"

    Example:
    Lets say OptionButton2 is currently ticked. If I would now click cell E8
    it would first see that OptionButton2 is ticked and it runs Application.Run "copy_master_to_hypelink_Basildon"
    then as per code it would carry on with Application.Run "hyperlink_backlog_DNB_monday"


    Can anyone advise how to incorporate this to my current code?


    All help is much appreciated.
    Cheers
    Rain
    Last edited by rain4u; 06-03-2011 at 05:34 PM.

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    rain4u,

    Give this a try:
    Please Login or Register  to view this content.


    Hope this helps,
    ~tigeravatar

  4. #4
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    Crickey!
    Thanks Tiger this reduced my code from 360 lines to like less than 50. I barely understand how this works but it works. Simply amazing. How to incorporate a feature like described in my post 2 into this code.
    in other words how to make the code check which OptionBox is ticked to choose the very first step

    OptionButton1 = Application.Run "copy_master_to_hypelink_all_sites"
    OptionButton2 = Application.Run "copy_master_to_hypelink_Basildon"
    OptionButton3 = Application.Run "copy_master_to_hypelink_Burnley"
    OptionButton4 = Application.Run "copy_master_to_hypelink_Ilkeston"


    many thanks!
    Last edited by rain4u; 06-03-2011 at 05:53 PM.

  5. #5
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    One problem I noticed with this awesome code. Even if you click anywhere outside E7 to I29 are it still tries to run macro. Can this be limited to this range only? I have been very structured with my other codes and this fits in very well. Learning every day here!

    Cheers
    Rain

  6. #6
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    rain4u,

    One problem I noticed with this awesome code. Even if you click anywhere outside E7 to I29 are it still tries to run macro. Can this be limited to this range only?
    Anywhere outside? It should only try to run the code in columns 5 (E) through 11 (K). That is in this part here:
    Please Login or Register  to view this content.


    I didn't know if you used saturday and sunday so I included those anyway. It sounds like you don't use them, so you can just delete those lines.

    Hope this helps,
    ~tigeravatar

  7. #7
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    Thank you. Nope. Using till Friday only, so I will delete those lines. When I click on something really random like AF301 it still runs macro. It looks like it executes this line Application.Run "copy_master_to_hyperlink_all_sites"

    Any ideas?
    Rain

  8. #8
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    rain4u,

    Ah, ok. I see what the problem is. I put this line in the wrong spot:
    Please Login or Register  to view this content.


    Instead of being in the Worksheet_SelectionChange event, it should be in the HyperlinkByDay function, at the top:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

  9. #9
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    That problem is now solved.Macros execute only if I click within E7 to I29. Thank you for that.

    Can you help me with this last request. You know this line
    If ThisCell.Row >= 7 And ThisCell.Row <= 29 Then Run "copy_master_to_hypelink_all_sites"

    Can we make here a conditional rule to choose between 4 codes instead launching this "copy_master_to_hypelink_all_sites"

    The 4 codes are following
    Application.Run "copy_master_to_hypelink_all_sites"
    Application.Run "copy_master_to_hypelink_Basildon"
    Application.Run "copy_master_to_hypelink_Burnley"
    Application.Run "copy_master_to_hypelink_Ilkeston"

    I have x4 OptionButtons on the sheet.
    OptionButton1
    OptionButton2
    OptionButton3
    OptionButton4

    Not to be confused with tickboxes. By OptionButtons I mean the ones that you can click and little dot will appear inside. The ones when only 1 can be clicked i.e. as soon as you click any other box the little dot will move to that clicked box.

    Now I would like to add this feature. When clicked to any cell listed in the code it would first check which OptionBox is currently ticked to determine which code to launch.

    OptionButton1 - Application.Run "copy_master_to_hypelink_all_sites"
    OptionButton2 - Application.Run "copy_master_to_hypelink_Basildon"
    OptionButton3 - Application.Run "copy_master_to_hypelink_Burnley"
    OptionButton4 - Application.Run "copy_master_to_hypelink_Ilkeston"

    then it would carry on launching macros as per addressed target

    Please Login or Register  to view this content.
    Example:
    Lets say OptionButton2 is currently ticked. If I would now click cell E8
    it would first see that OptionButton2 is ticked and it runs Application.Run "copy_master_to_hypelink_Basildon"
    then as per target code it would carry on with Application.Run "hyperlink_backlog_DNB_monday"



    Would you be so kind to assist me with this?
    Cheers
    Rain

  10. #10
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    rain4u,

    The answer you're looking for would be rather specific, which is good. However, I need to know what kind of option buttons you're using: ActiveX controls or Form controls. I also need to know if the option button caption (the text for the option button) is the same as the function you want to run, or if there is no caption and the option buttons are just next to cells that contain that text, or if you use a linked cells. It might be easiest to upload a sample workbook so I can take a look at what you're using.

    ~tigeravatar

  11. #11
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    Hi Tiger
    I'm bit newbie in this. Getting better and better though. In order not to give you a wrong answer I just copied the sheet out of the workbook. Attached to this post. option buttons are up there "All sites", "Basildon", "Brunley" and "Ilkeston"

    Thanks for finding time for me. Its very appreciated.

    Cheers
    Rain
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    Oh these form option buttons are so much more flexible and nicer looking. Can you provide code with those ones? I will quickly edit the attached sheet.



    EDIT: oh never mind. it looks like they keep disappearing when switching between the sheets so I'm not fond of them. I will rather keep it simple.
    Last edited by rain4u; 06-03-2011 at 07:09 PM.

  13. #13
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    rain4u,

    Attached is a modified version of the file you provided. You were using ActiveX controls and the option button captions had a partial match. I replaced the old line:
    Please Login or Register  to view this content.


    With this new section:
    Please Login or Register  to view this content.


    Hope that helps,
    ~tigeravatar
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    Thank you very much. Everything works like a charm.
    Got it working! Very impressive. I like the way you have tied everything neatly together even going as far as taking the parts of the macro names from the button names. It will make my life easier when I will try to tweak few things and help my colleagues at work so it will work with their accounts as well. Just when you think you have realized the excels limits, it comes back and surprises you. I think the sky is the limit with excel. I think I will never learn all its functions.
    Thank you Tiger for sharing your time and knowledge. Simply awesome.

    Cheers
    Rain

  15. #15
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    Just a small bug. If I click any random cell between columns E to I it (outside E7 to I29) it executes last used macro (last macro that was used macro when clicked within E7 to I29). It correclty does not execute any macros if clicked between columns A to D and J to IV. I'm not sure if I messed something up because I think we had this correct before.

    Current code:
    Please Login or Register  to view this content.
    Any ideas.

    Cheers
    Rain
    Last edited by rain4u; 06-03-2011 at 08:00 PM.

  16. #16
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    rain4u,

    Add this line in the Worksheet_SelectionChange event:
    Please Login or Register  to view this content.

    So that is looks like this:
    Please Login or Register  to view this content.


    ~tigeravatar

  17. #17
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Compile error: Procedure too large. Executing macro via hyperlink.

    Thank you. This fixed it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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