Excel not responding on macro run

[chbrandt]

Let me start off by saying I'm extremely new to VBA. With that said, I'm sure that this code could be improved 100 times better than it currently is, but I'm learning. I've made a couple of macro's that, for some reason, are freezing up excel when ran on a computer other than the one they were made on; I can run them with no problem on mine, they work perfectly. Is there some way that the code can like my machine more than others with the same macro security settings?

Any advice would be greatly appreciated. Please see attached.

[6StringJazzer]

But what happens? Does it require you to kill the Excel process and restart? I suggest you look at Task Manager, Performance tab, and see what kind of CPU Excel is burning up. If it is using more than 0%, your code is in an infinite loop (actually it will probably be something like 50-99% if that is the case). You have a loop in Sub Lines that has the potential to go infinite, or at least long. Are you doing exactly the same thing on both computers? Starting on the same worksheet, same data, etc.?

I don't know what is wrong in your specific situation but I'll give you some suggestions.

You have placed your code in Modules. That's often a good idea, but be aware that when you use an expression like

Please Login or Register  to view this content.

you have not specified which worksheet you're referring to. Therefore it will refer to whatever sheet is active. (Code like that in a Sheet module will refer to that sheet.) Your code does not activate a sheet, so you are at the mercy of your user to have the right sheet selected. You should generally be explicit and use spmething like this:

Please Login or Register  to view this content.

It is not necessary to Select a range just to do something with it.
There are several things I would do differently in this blurb of code:

Please Login or Register  to view this content.

rewritten as:

Please Login or Register  to view this content.

In Sub Lines I highly discourage you from using the GoTo. This tends to create convoluted flow through the code. If you were to draw a line from every GoTo to every target, you will get what looks like a bowl of spaghetti, hence the term "spaghetti code."

In the same place, use indentation to reflect your If and loop structures.

Use comments. I can't figure out what you're trying to do. Well, I could if I spent another 20 minutes, but a few comments would have told me immediately.

[chbrandt]

Thanks a lot for the tips. I've updated some of the code to be more clear on which sheet is selected. I had trouble with the line

Please Login or Register  to view this content.

as excel gave me an error, but I still cleaned that line up some. I checked with the CPU usage and it was 25% using either of the run buttons. Here's what I'm trying to do, sorry I left that out in the original post.

On Sheet2 I have every day of the year in one column and a =RAND() in each cell in the column next to it. The point is for a random selection of days to be pulled through the year, as random as Excel can truly be. I want the user to be able to input the number of days, and then excel to copy that number of days from the top after sorting the days high to low to mix them up, then paste them and re-sort them in order based on MM/DD/YYYY on the first sheet.

The other macro, Lines, is to pull a random number of lines (lines being row numbers in excel) from a population with the population being the max. I have fixed some of the confusion using the GoTo, but I didn't know how else to maneuver to get around any better than I did. Every other computer I've tried it on seems to have the exact same setup as my computer and my excel and I'm opening the same file and taking the same actions, except on other computers I have to end task on Excel. I've attached the most recent version and any help would be appreciated.

[chbrandt]

I've been playing with the code since recently learning how to 'Step Through' the macro, and I think I've found what's causing the problem:

Please Login or Register  to view this content.

Any instance I have that is selecting an entire column is causing a problem. Now in the case above, I know that I could fix the line to only select relevant cells, but I'm not sure how to do that. Could I just use the same idea as I did with the Range:

Please Login or Register  to view this content.

Any idea why selecting a column would make excel freeze and have to kill the process? Thanks!

[6StringJazzer]

Any idea why selecting a column would make excel freeze and have to kill the process? Thanks!

I am not seeing that behavior, but merely selecting a column shouldn't cause it. I'm taking a look, and will offer further code cleanup.

Just for the record, Excel isn't "freezing", it's getting so damn busy it is ignoring you Normally I would say a loop is out of control, but if you're getting different behavior on different machines I can't account for it. I'll see what I can find out.

[6StringJazzer]

Here is a reworked version but the logic is still the same.

What numbers are you typically using for population size and sample size when you run it?

I do not see anything that would cause you to get hung. However, I added a safety valve in the loop so that if it iterates more than 1000 times it will abort. It should not iterate more than a few times. The number of iterations will grow larger as your sample size approaches your population size, and as the population size grows. For example, if you have a population of 1000 and a sample of 1000, you might have to do more and more iterations as your get near the 1000th sample, and the number of iterations will average to be 1000 on that last sample.

I got rid of your GoTo's, added some comments, took greater advantage of existing With statements, removed unused Module2.

Edit: I also ran into trouble with

Please Login or Register  to view this content.

I do not know why that causes a runtime error but maybe some other denizen of this board can enlighten us.

[chbrandt]

Wow-- Thanks a lot, I'm such a noob but I'm learning a lot as we go. For the column selections that were causing problems for some reason, I just selected ranges of relevant cells instead of entire columns, as you'll see in the code.

I implemented the changes you made, and also attempted to add a Copy to clipboard button that seems to copy them, but doesn't have the desired effect; I'd like to copy them for use in Outlook or Word, etc. and now have the cells selected after it runs. The only other thing I think that would be nice would be if there was a way to add a checkbox that said "Exclude Weekends" and when checked for the Days macro, weekends would not be counted.

As for the iterations, the 1000 is in relation to the sample size, correct? I guess it wouldn't matter how high that number goes as long as it is more than the most a sample might be?

Thanks a lot for your help.