General Coding Procedure Question

[CB Hamlyn]

I'm working on a fairly large Excel project.

One of the larger bits is a repair feature, where clicking on the toolbar
button brings up a screen with a list of all the available worksheets in the
workbook. The user checks off which sheets will be effected then can either
clear or repair the selected items. Clear blanks out the user editable
cells, repair restores all the formulas for the selected worksheets.
Currently the code is like this:

The user checks off the sheets they want to effect and hits the clear or
repair button. The code checks to see which sheets were selected and for
every one it finds it runs that sheet's clear/repair sub procedure which is
contained in a general "ClearRepair" module.

Generally speaking is it better to have long bits of code in a form or in a
module. I'm wondering if I'm being smart by doing it the way I'm doing it
or if it will run better/cleaner/smaller to pull all the subs out of the
module and put them into forms code.

Thanks for any advice.
CB Hamlyn

 

[Bob Phillips]

CB,

When it comes to 'better' I don't think there is a clear-cut answer that one
is better than the other, but I like to keep my forms simply to event code,
that is code that handles the events associated with the object, and if
there is any other action required, such as clearing workbooks, if this is
complex, then put it in modules.

The way I logically separate it is that the forms are the presentational/GUI
layer, the VBA modules then become the application layer, and there is then
a data layer, which can be a database, or in your case is Excel . Separation
makes for better maintenance IMO. It also facilitates migration to another
platform should tyhat ever be needed.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[CB Hamlyn]

Thanks, that's how I have it set up now, just wondering if there's logic in
doing it the other way.

Thank you for the input.
CB

 

[Bob Phillips]

The other point about separation is that it can make testing easier. For
instance, you could design your tidy-up code and test it before even
building the form. In doing so, you might find some design improvements that
will make the form building even easier.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)