Procedure Too Large Error VBA

[Rudidw]

Hi,

I hope someone can please help. I have a module with a lot of code that is causing me to get a Procedure too Large Error when I press a button to run the code.

The code used is to transfer data from a selected Row in the Data Base sheet within the workbook to multiple Input sheets in the same workbook.

Below is about half the code used in a module and as I am a newbe to VBA I am not sure how to "lighten" the code or other tricks that can be used in order for it to run properly.

Please Login or Register  to view this content.

[Cheetahke]

Is it possible to post an example of your workbook, with the entire code in it?

[StuCram]

Hi Rudidw,

One suggestion would be to break the sub into a few smaller subs and then make a new sub that calls each of them

Please Login or Register  to view this content.

There are 3 major but quick changes I would also suggest that will reduce the amount of coding and also improve the speed of execution.
a) Use a variable fin place of 'ActiveCell.Row' as it appears to be the same thing on each line.
b) Use the 'With Sheetname' structure on each group of instructions that refer to the same sheet at the start of the lines
c) Use a variable in place of 'Sheets("PropDB") after the = sign on each row
These changes are relatively easy to to using VBA's Find & Replace commands.

Here's my adjusted coded for the part you supplied originally.
(The bold and red colour are mine, for emphasis on the changes)

Please Login or Register  to view this content.

[Rudidw]

Hi Cheetahke,

Thanks for the quick reply. I have attached a sample workbook as requested.

On the ProDB (Database) sheet you just need to click on a cell in column A and then click the button to run the macro (transfer data from this sheet to all the other sheets)

[Rudidw]

Hi StuCram,

Thank you for you quick reply and adjusted code!

Please advise how to break the sub into a few smaller subs and then make a new sub that calls each of them? As I said I am a newbe to VBA and I have no idea how to do what you suggested.

I more or less understand your adjusted code and will give it a try!

[Cheetahke]

Is there any logic behind the numbers of the rows and columns, like a specific value in the cell on the database sheet?

[Rudidw]

Hi Cheetahke yes each row is a different project being analyzed, and each column contains a specific value used during the analysis of a project. All the values are stored on the database sheet for future reference or when a project needs to be re-analyzed due to environmental changes.

[Rudidw]

Hi StuCram,

I was able to sit over the weekend with your suggested code and it works .... sort of.

When I ran your code:

Please Login or Register  to view this content.

I received an error "Compile Error: Method or data member not found"

The way I could get rid of the error message when running the macro was to change the code to:

Please Login or Register  to view this content.

My revised code (with your help) was now able to run the macro I did not get the original "Procedure Too Large" error!

Do you have any idea why I received the Compile Error when I used your code?

[Norie]

sPropDb should be declared as Worksheet not Sheets.

[Rudidw]

Hi Norie,

Thank you! The code now works 100%

[Rudidw]

To optimize the above VBA code for execution speed is which is better to use?

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[Norie]

Don't think it makes a difference.

Mind you, one thing you might want to do is specifically state .Value.

Please Login or Register  to view this content.

[StuCram]

Sorry about that. My carelessness.

My declaration was wrong:

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

I notice that user Norie already spotted that.
Glad you found a solution.

[Rudidw]

Thank you all for the help!