Code in userform/worksheet vs. in Module

[davegb]

In another thread, I asked about using GoTos. Got some great feedback.
But some of what I was told seems contrary to what I have read
elsewhere. I got the impression somewhere in my reading on VBA coding
that it's better to minimize the code in the userforms/worksheets and
keep as much of it as possible in Modules. Easier to find and access,
something like that. Do others here agree, or do you just put the code
wherever? Is there an advantage to using a Call from the userform to a
module, or just do even long, involved code in the userform/worksheet?

 

[Jim Thomlinson]

Where to put code is more of an art than a science. There are a bunch of
general guidelines to follow though (IMO). In the grander scheme of things
try to put your code where it is most apporpriate. If for instance you have a
procedure that deals only with one sheet then that code should probably
reside in that sheet. If however that same code could be used on any number
of sheets then perhaps a module would be more appropriate. The same applies
to userforms. Once you have determined the scope of the sub or function then
where it goes becomes a fairly straight forward decision.

There is also an issue with code re-use. I have a whole pile of forms,
modules and classes stored in a library that I use in a plug and play manner.
These need to be as stand alone as possible in order that I can just plug
them into a new project and have them work.

There is also an issue in terms of what each can and can not do. Type
declarations can only go in modules so that one is straight forward.

Finally there is an issue in terms of copying sheets or such. If you are
going to copy the same sheet within a book then you probably don't want code
in the sheet otherwise you are duplicating the code each time you copy the
sheet. If you are copying the sheet to a new book then maybe the code has to
be in the sheet to give required functionality to the sheet in the new book.

 

[davegb]

Thanks for your reply, Jim. This helps.
I'm not clear what you mean when you say, "Type declarations can only
go in modules". Do you mean that variables can't be declared in a
userform or worksheet? I have some public declarations in a userform,
but maybe they're working for some other reason. Can you clarify that
for me?