Shorten UserForm_Initialize code

[PDBartlett]

Hello

I was wondering if this code could be shortened as it does take a tad too long to open up. The code works perfect atm.

Please Login or Register  to view this content.

[romperstomper]

Just making it shorter won't make it faster. You're doing quite a lot of processing there - I suggest you start by working out where the most time is spent (how much time are we talking anyway?) and then seeing if you can optimise that section of the code - perhaps by loading the data into an array rather than reading one cell at a time.

[PDBartlett]

Quick question... do I need all these letters as Integers/Longs? Or can I use one but make the value different each time e.g.

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Personally I'd examine the overall process and see if there's a different way of achieving your aim. Can't really comment unless we know what that is. Must admit though that it's the longest Initialise procedure I've ever seen.

First set Application.ScreenUpdating to False and then back to true at the end of the code
Similarly switch off autocalculation with Application.Calculation = Manual ' switching it back to Automatic at the end

See if Dimming your Integer Variables as Long makes a difference. I believe in the background VBA has to translate an Integer to a Long anyway so you may as well declare as a Long in the first place.

Then to shorten the code (but maybe not the time so much) and untested in the absence of the workbook but something like;

'For all the

Please Login or Register  to view this content.

stuff

Please Login or Register  to view this content.

[PDBartlett]

Thanks Richard, I'll give it a go. But could you tell me the difference between Long and Integer?

[Olly]

Each of these sections:

Please Login or Register  to view this content.

Can be replaced like:

Please Login or Register  to view this content.

But it will be the loops nearer the bottom of the routine which are taking the time to process.

[romperstomper]

An Integer can hold a value between -32768 and 32767. A Long (short for Long Integer) can hold between -2,147,483,648 and 2,147,483,647.

[PDBartlett]

Thanks guys. This is noticeably quicker:

Please Login or Register  to view this content.

[Olly]

Just a quick "caution" on your variable declarations:

Please Login or Register  to view this content.

As you have written this, only s is declared as a Long, and ws8 as a Worksheet. All your other variables are variants, by default.

You need to define the variable type of each variable individually, to apply that variable type - so your declaration lines would become:

Please Login or Register  to view this content.

[PDBartlett]

That's annoying! But thanks for the heads up