+ Reply to Thread
Results 1 to 11 of 11

Macro Ends Sub After Executing If Statement

  1. #1
    Registered User
    Join Date
    08-18-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    9

    Macro Ends Sub After Executing If Statement

    Hi,

    I could not find the solution to this problem anywhere. I am building a worksheet, and one of the features it needs to do is to hide or unhide certain columns when a certain macro is triggered. I tried using a simple code below (which works in another workbook) but it behaves strangely in the workbook:

    Please Login or Register  to view this content.
    When I use the debugger, and columns are initially hidden, the code will pass through lines 1-4. This results in unhiding the columns. However, the sub ends right then, and line 5 is never highlighted. If I click "F8" again, it goes to line 1. This time, when I keep clicking "F8", it reaches line 8, executes the code (hides the columns) and then exits the sub again. If I click "F8" again, it will go to line 1.

    How can I fix it so that the code does not get cut off after executing a statement and continues with the rest of the macro?

    Thank you!

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    7,996

    Re: Macro Ends Sub After Executing If Statement

    Hi, pranskius,

    maybe just use a shorter code like
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro Ends Sub After Executing If Statement

    Don't use that code
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    7,996

    Re: Macro Ends Sub After Executing If Statement

    Hi, xladept,

    sorry? What´s wrong with my code?

    Ciao,
    Holger

  5. #5
    Registered User
    Join Date
    08-18-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro Ends Sub After Executing If Statement

    HaBoBe,

    Thank you for your help, but turns out that the problem was with Events being turned on. The act of hiding the columns would trigger the event and interrupt the macro.

    Thank you.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro Ends Sub After Executing If Statement

    @Holger - Absolutely nothing That's what I would have told the OP, but I couldn't imagine why that procedure was written

    And - I wasn't talking to you

  7. #7
    Registered User
    Join Date
    08-18-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro Ends Sub After Executing If Statement

    Hey,

    Sorry for back-and-forth, but I noticed that turning off events did not solve the problem. The macros are being interrupted by a function which I wrote in a module, but I have no idea why. Also, for some reason, the following code line causes the Subs to end in all subs that I use:

    Please Login or Register  to view this content.
    Here is the function that I wrote, which forces itself into execution multiple times when other macros are run

    Please Login or Register  to view this content.
    Here is one of the modules that I run, which contains the lines to hide the columns

    Please Login or Register  to view this content.
    So everything seems to execute correctly, even the columns are hidden, but the MsgBox message at the last line does not show up. If I put a MsgBox before "Selection.EntireColumn.Hidden = True" statement, it will show up.


    Thank you for your help!



    PS: xladept, I used the IF statements initially after seeing that the code wasn't executing all the way because I figured that maybe when the columns are hidden, and I tell the code to hide them, there is an error generated and the sub ends, but that wouldn't have been the case with "On Error GoTo Next" anyway...

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Macro Ends Sub After Executing If Statement

    PS: xladept, I used the IF statements initially after seeing that the code wasn't executing all the way because I figured that maybe when the columns are hidden, and I tell the code to hide them, there is an error generated and the sub ends, but that wouldn't have been the case with "On Error GoTo Next" anyway...
    That shouldn't have generated an error anyway

  9. #9
    Registered User
    Join Date
    08-18-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro Ends Sub After Executing If Statement

    Quote Originally Posted by xladept View Post
    That shouldn't have generated an error anyway
    You're right, it shouldn't have.

  10. #10
    Registered User
    Join Date
    08-18-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro Ends Sub After Executing If Statement

    UPDATE:

    When going over it with debugger, I notice that after line:
    Please Login or Register  to view this content.
    the next step for excel is to run the function below 60 times! And this function is not even in the same module, nor is there a call function for it:
    Please Login or Register  to view this content.

    I assume that for some reason the cells in the sheet containing this function are refreshing during the macro? Maybe that's what causes the macros to end?

  11. #11
    Registered User
    Join Date
    08-18-2014
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro Ends Sub After Executing If Statement

    Hey,

    Just wanted to update the issue just in case anyone else might be having this problem.

    It was the fault of the UDF (User Defined Function) that I created. It was forcing excel to perform its calculations when excel is trying to complete other macros, forcing excel to exit sub at a certain point.

    After getting rid of the UDF, everything worked as expected. I only needed to find a workaround without using a UDF.

    Will mark this thread as Solved but would be nice if anyone actually came up with a solution of how to prevent UDF from interfering with running subs.

+ 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. if statement ends with
    By thongtran in forum Excel General
    Replies: 11
    Last Post: 03-24-2015, 11:36 AM
  2. [SOLVED] If OR statement not executing properly
    By irfanparbatani in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-02-2013, 02:46 AM
  3. [SOLVED] Setting a range that ends where data ends?
    By Mgassma in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-02-2013, 09:46 AM
  4. [SOLVED] Nested IF only executing first loop statement
    By kewcumber in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-22-2012, 03:51 PM
  5. [SOLVED] How to force Excel to complete one statement before executing othe
    By vrk1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-21-2005, 03: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