Macro Ends Sub After Executing If Statement

[pranskius]

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!

[HaHoBe]

Hi, pranskius,

maybe just use a shorter code like

Please Login or Register  to view this content.

Ciao,
Holger

[xladept]

Don't use that code

[HaHoBe]

Hi, xladept,

sorry? What´s wrong with my code?

Ciao,
Holger

[pranskius]

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.

[xladept]

@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

[pranskius]

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...

[xladept]

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

[pranskius]

That shouldn't have generated an error anyway

You're right, it shouldn't have.

[pranskius]

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?

[pranskius]

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.