The correct position to place msgbox in code

**Jim28** · 10-23-2011, 06:58 AM

Dear all

I am running the below code which searches for combinations of values that add up to a given target. Depending on the number of values it is searching through, the time it takes obviously goes up.

Please Login or Register  to view this content.

When the code is searching through about 30 or more values excel comes up with the message (not responding).

I am not sure whether the code has crash or is still in fact running.

To test this I wish to place a temporary message box to appear, perhaps using the below code. I wish the message box to pop up once every minute for a few seconds to prove the code is still running.

Please Login or Register  to view this content.

However I can not work out where to place it in the original code.

Can anyone help?

Many thanks in advance,

Jim

**TMS** · 10-23-2011, 07:11 AM

I haven't looked at your code but you're better putting Debug.Print in the code at a few strategic points. That won't interrupt the flow of the code and it will give you a "heartbeat" to prove the code is still running.

For example:

Please Login or Register  to view this content.

Either put the code in an "outer loop" so it only gets executed every so often, or put a loop counter in and print every 50 or 100 loops and clear the loop counter

Also, to speed up your code, consider using Application.ScreenUpdating and Application.Calculation to switch these off an on before and after executing code.

Regards

**Jim28** · 10-23-2011, 07:25 AM

Thanks very much TMShucks for your speedy response - apologises I have not come across the debug.print method before and do understand the variable1 value etc part.

Could you explain a bit more about what I need to put? I can do the counter part though...

**6StringJazzer** · 10-23-2011, 10:20 AM

I don't understand what your code is trying to do, but I see that you are using recursion, rather than a loop. I have added a counter to measure the depth of the recursion. It uses Debug.Print to print the depth of recursion every time the recursive Sub is entered and exited. I can't figure out how to get it to enter more than once, though.

To see the results of Debug.Print, go to your code and press CTRL+G to open the Immediate Window, where this output goes. You can clear out the Immediate window manually by hitting CTRL+a then DELETE or CTRL+x.

When running macros and getting "(Not Responding)" it is almost always because your code is in a long (possibly infinite) loop. If your recursion is going very deep, you may not want to print a message for each entry. You could do this to print for every multiple of 10 entries:

Please Login or Register  to view this content.

However, recursive calls allocate additional memory (stack space) on each call. If you are getting very deep calls then you would be likely to get an "out of memory" error eventually.

Are you interrupting the code when it seems to take a long time?

**Jim28** · 10-23-2011, 11:02 AM

Hi

The only thing I can do is do ctrl pause break to get back into the macro when it stops responding.

I really want to stop this happening and thought a message box popping up intermittantly might do this....

**6StringJazzer** · 10-23-2011, 12:20 PM

The message box won't allow you to stop, unless you put an Exit Sub in there based on the response to the box.

If you can tell me exactly what data you use and how you run it, I can try to reproduce it and help diagnose the problem. But I selected all of the data in column B and it ran instantly.

**6StringJazzer** · 10-23-2011, 12:41 PM

OK, I see what you are trying to do but I don't have time to thoroughly analyze your code. I waited until the recursive Sub had been executed 115,000 times before I interrupted it. Your code is not crashing.

It is possible that there is nothing at all wrong with your code. To evaluate all combinations of n numbers, you have to do this many comparisons

sum of n!/r!(n-r)! for r=1 to n

For 30 numbers, that's about a billion combinations. That could take Excel a while.

You may also find this related thread helpful, or at least interesting.

**6StringJazzer** · 10-23-2011, 01:35 PM

In other news, I modified the code to stop after the recursive sub had been called 1,000,000 times. I don't know how many combinations of numbers that represents. But it took 0:02:35, so if it's 1 call = 1 combination, to do 1 billion would take about 42 hours. On the other hand, if it takes more than one call to build one combination, it would take proportionately longer.

It can be surprising to find out how long it takes to evaluate combinations of numbers.

**Jim28** · 10-23-2011, 02:51 PM

Originally Posted by 6StringJazzer

In other news, I modified the code to stop after the recursive sub had been called 1,000,000 times. I don't know how many combinations of numbers that represents. But it took 0:02:35, so if it's 1 call = 1 combination, to do 1 billion would take about 42 hours. On the other hand, if it takes more than one call to build one combination, it would take proportionately longer.

It can be surprising to find out how long it takes to evaluate combinations of numbers.

Thanks for this 6StringJazzer - so at least I know the code is not crashing but still would like something to reassure the person running the code that it has not crashed, while not stop the code running.

In addition, an estimate of how long the code will take to run?

Are we saying about 30 combinations is the limit for reasonable time?

**6StringJazzer** · 10-23-2011, 07:46 PM

I can't estimate the running time without understanding more about how your code works. If the Sub is called once for each combination then analyzing all combinations of 30 elements would take a couple of days. But I'm sure that it must be called more than once for each combination, as it is recursive and that's how it enumerates the combinations. So it might take longer.

By the way, I was wrong above. It is true that VBA is not multithreaded, but I just discovered a way to allow the thread to be interrupted and then return to it. I just happened to stumble on a post by user shg that mentions the built-in function DoEvents, which allows the user to press a button, have your code deal with it, then return to whatever was going on. Attached is a simple example. If you press the Test button, a form appears while a counter increments in A1. If you press Display Message, the process is interrupted while a msgbox pops up. If you press Halt, the whole thing halts.

I also included a grid that calculates the number of combinations of all numbers for a set of n, for 1 to 30. As you can see this number gets big fast.