Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel Crashes

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Excel Crashes

Resolved · Low Priority · Version 2003

Reply Mon 1 Dec 2008, 16:29Delegate Harry said...

Harry has attended:
Excel VBA Intro Intermediate course

Excel Crashes

I've got a program that writes an array formula to 24000+ records one column at at time, then hard pastes it before moving to the next column. If I execute the program one line at a time using the F8 key it runs but if I run it in the normal way it crashes.

For upcoming training course dates see: Pricing & availability

Reply Wed 10 Dec 2008, 09:30Trainer Carlos said...

RE: Excel Crashes

Hi Harry

Without seeing the code and observing the crash as it happens I cannot give a reason why it is doing it.

Please send me a copy of the workbook to

forum AT microsofttraining,net

Regards

Carlos

Reply Wed 10 Dec 2008, 14:08Delegate Harry said...

RE: Excel Crashes

Thanks for your response Carlos. I suspect that the problem is that the file is just too big (although I would have thought that it would also crash on debugging).

I've sent the code to the email above for your attention.

The data in the worksheet is part of a large download from a relational database and contains 4 separate tables, pupils on roll, pupils off roll, attendance data on pupils on roll and attendane data of pupils off roll.

The macro takes the field required from the pupils on roll data and puts it in the output sheet and then appends the equivalent columns from the pupils off roll table. It then adds in both the attendance tables to the 'all absence' sheet.

all of the above runs fine. The problem occurs when the macro copies an array formula down a column of data. This takes place in the do while loop contained in the add_absence macro.

Excel articles

· How to Create Better Excel Spreadsheets: Part Three
· How To Create A Simple Currency Convertor In Excel 2010
· Tell AutoFill What To Do And It Will Oblige
· Find Out How To Create That Missing Link With Excel
· Creating Error Alerts In Excel Using Data Validation

Please browse our web site to find out more about
exce+training and other Microsoft training courses.

Excel tip:

Generating Random Numbers

To generate a random number in Excel use the = RAND() function.

The value returned will always be between 0 and 1. To convert this to some other random value, you will need to multiply the result by the highest number you want to consider. For example, if you wanted a random number between 1 and 25, you could use the following code line:
= INT(25 * RAND()+ 1)

Since RAND() will always returns a value between 0 and 1 (but never 1 itself), multiplying what it returns by 25 and then using the Integer function INT on that result will return a whole number between 0 and 24.

Finally, 1 is added to this result, so that x will be equal to a number between 1 and 25, inclusive

View all Excel hints and tips