Function, compile error.

[stramvaier]

Hi.
i get this error when i try to run this function. Compile error: procedure too large.

Im probably over the 64k limit(?), but i need to run this code. It inserts components into a list based on selections from a userform. the code in the attachment is only 1/20 of the total code but it is this spesific function that chrashes.

I need some help on how to make it run. if i can split it some how, or can i use some procedure inside the function to make it run?

i tried to split it at case 19 with a new function, but then it would not work past case 19.

im new to VBA and im lost.

[Jan Karel Pieterse]

That seems like something that really screams for a re-design.

In my humble opinion, you have too much hard-coded data in your code.

I think it would be better to move out all the hard-coded data to e.g. tables on worksheets and then use logic to decide what piece of data you need to copy from those tables, based on the choices made on the form.

It will make your code a lot more compact and at the same time makes maintaining the information a lot easier.

[stramvaier]

i appresiate the input but i need to run this code. it is to big of a job for me to rewrite with my limited experience. if there is no other option i must hire a pro, but if it is possible to do something(fairly easy) to it that will let it run,i would rather do that.

[Bob Phillips]

We can offer you advice and direction on how to reduce it, but it is too big a job to expect us to recut the function for you, it would take a long time.

You say function, you don't hope to call this from a worksheet as a UDF do you?

[stramvaier]

it is combobox2 in my userform that lists several options for the user. as they select i have a function that makes cases based on the value in the combobox. That again puts the info that is hardcoded into an inventory list.

Maybe i can devide the function in two? case 13 to 19 etc and then make some code that looks at the value the user selects in the combobox and from this say something like value greater than case 19 go to function "part two"(case 19 to 28).

That is my idea....... its poorly explained........think i will go job hunting now.

[Bob Phillips]

It needs a more fundamental change than that in my view. Every option is basically doing the same, injecting some row headings and a matrix of headings, but differing values depending upon the selection.

As such, my advice would be to start by building a generic function to populate the cells, then call that function with your different data. There is a ton of redundancy in your code, that looping looking for an empty cell is totally unnecessary, all the selecting, and so on, so my suggestion would cut it down dramatically.

Then you should consider Jan Karel's suggestion, put all of the values on a worksheet, and load the actual sheets from there. This would ease maintenance and reduce the code to some tens of lines.