Procedure too big

Hello

I wrote a program that is copying data from an Excel spreadsheet to a Word document in specific bookmarks but now the procedure is too big :mad:

The program has an inteface screen and it copies only the data selected with checkboxes.

I thought of chopping the code into segments using different sub procedures for e.g. each checkbox but now the program doesnt function because the variables are not registered for every sub procedure.

Is there a way in making sub procedures in one big procedure? Will that exceed the size of the container procedure?

Thank you very much

:)

You had the right idea of breaking it into different subs. Probably two would do (this same things has happened to me before).

As far as the variables are concerned, take their declarations out of the actual sub routine and move them either to the declarations section of your userform (it you are using one) or a module and make them Public so both subs can access them.

It is very rare to get this kind of error, and most often it happens to people who are relatively new to programming - as you have not yet learnt how to organise your code.

Without seeing your code (or part of it), it is hard to give specific advice, but in general you should be making use of loops, and if you have a section of code that is repeated you should move it into a Sub/Function (and pass parameters to it as apt).

You can see an example of a using Sub that way in the article How can I pass a control (textbox/listbox/..) to a sub or function? from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page). While it uses a TextBox, you can pass any data type you like (such as String or Integer) via parameters, and use it the same way as the example.

Hello H!

I tried to place half of the code in one private sub and the other half in another private sub all included in a public sub but I get an error message so I need to make 3 different subs, the public and the two private so the program stops at each sub.
Is there a way to inteconnect the subs somehow?

Thank you very much :)

Call Sub2 from Sub1 and Sub3 from Sub2.

From the users perspective, it will look like one continuous activity.

Hello H!

I have 2 now, one I call part 1 and the othe part 2 and I am calling part 1 from part 2 and so on and that has no problem on starting the scrip but...
On the public sub where I call part1 and then when I call part 2 from part1 there is a problem with the variables.
I have a variable that is a Word Document and when in part1 I have the script
wordoc.Bookmarks ("bookmark1").Select

That provides an error on the script :mad:

Is there another way to have the variables for both part1 and part2 from the public sub?


Thank you very much
:)

It is resolved

I used a function in the beginning of the code so now its fine


Thanks all! :cool: