Procedure too large

[yikwei]

Hi guys please advice. I attached part of my coding in a userform. Im trying to do a software using only userform control (not using spreadsheet). SO i have to repeat the code below for different component protected;different fluid used ec (Basically all the conditional would gives different parameter which requires user selection from the combo box/option box) Hence, i have to repeat all the equation below for different selection.

For instance, if compoprotectedcombo.value = heatexchanger, then the equation would change a little ( releaserate_small would be 0 this time instead of releaserate_rupture but all the equation used would still be the same) I tried to put the same code in the same procedure and it gives me the error of procedure too large. Could you guys please have a look? Please ask if there is any confusion.Thanks

Please Login or Register  to view this content.

and p/s: I crosspost the same question on the other excel forum. Attached are the link http://www.mrexcel.com/forum/excel-q...too-large.html

[LJMetzger]

Hi yikwei,

Apparently there is a limit of 64 kilobytes of compiled code for a single procedure. This is known to exist for Excel 2003, 2007 and 2010.

You probably have to break the procedure into smaller pieces:

Reference:
http://excelribbon.tips.net/T010449_...r_a_Macro.html
http://stackoverflow.com/questions/1...a-macros-excel

Lewis

[yikwei]

Hi thanks for replying. I understand that the procesure couldnt exceed 64kb. Do you mind help me to take a look how to write the code to perform the similar function without breaking it to different procedure? Cause i wish those updates would make once the user press a singke command button

[HaHoBe]

Hi, yikwei,

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.mrexcel.com/forum/excel-q...too-large.html

Ciao,
Holger

[LJMetzger]

Your code is very readable and understandable. In the spirit of 'do no harm', I think the approach should be to make the smallest amount of changes that will make the code compile.

1. Diagnose the problem usiing 'CONDITIONAL COMPILATION'. Exclude (large sections of) code using 'CONDITIONAL COMPILATION' until the code compiles without error. Before doing this I would put the entire Subroutine or Function in a SEPARATE code module.

Make sure to make a BACKUP copy of your file before starting.

Tip: You know when a section of code is EXCLUDED when using CONDITIONAL COMPILATION, because the debugger WILL NOT let you set a breakpoint in an EXCUCTABLE STATEMENT in that section of code.

Please Login or Register  to view this content.

2. Put excluded code in a separate Sub or function. Variables common to both procedures must be declared as either:
a. Formal parameters to the subroutine OR
b. Global variables

Make sure you use 'Option Explicit' at the top of the module to ensure that all variables are in scope. This example takes the global variable approach.

Please Login or Register  to view this content.

I hope this helps.

Lewis

[yikwei]

Hi lewis, it works perfectly! Thanks. However i still don't understand though. Why is the procedure limited to 64kb only but yet it still could run when you're using "call function" Is it because it is in different procedure?

[LJMetzger]

Hi,

I'm glad it worked out for you.

I don't know anything about Excel internals, but my speculation is that this is a legacy software architecture issue that dates back 15 or more years.

At the time, memory was a SCARCE resource, and addressing more than 64K of memory at a time from a procedure would use a lot more memory, and probably take a lot more time due to the limitations of the 16 bit CPU architecture being used at the time.

Lewis

[Kyle123]

The error message, should really say, re-factor your code