good VBA practice

[stuck]

I've got a user form that appears when a user clicks a button on the Ribbon. The form is very simple, the user has to enter a number and click OK.

My question is what is the 'best' way to structure the code that runs when the OK button is clicked, should the form code that runs when the OK button is clicked be:

A) simple and just capture the entered number and then pass control over to a module holding a sub (or subs) that does everything that is required
or
B) complex and do everything that is required.

The end result in either case would be the same but is one way better practice than the other?

Ken

[HansV]

Do you want to be able to run the code that processes the number in other situations? If so, it's better to create a separate procedure or function that takes the number as argument, and to call it from the On Click event procedure of the command button on the form.
Otherwise, it doesn't really matter, I think.

[stuck]

Do you want to be able to run the code that processes the number in other situations?

Not sure, probably not but I hadn't thought of that as a factor to take into account.

Thanks,

Ken

[Rudi]

Another thing to consider (and would result in the reverse of Hans's advice) is if you intend to use the form in other workbooks. If all the code is attached to the form module then it makes it much more convenient to export the form and upload it into other workbooks, making it instantly usable there too.

Bottom line: Planning a project is more than just the planning the code and the design of the form, but also planning how, when and where the actual project will be used.

[stuck]

...Planning a project...

Who said anything about planning

My approach tends to be more :visitlounge:

[Rudi]

Well, admittedly, that is a brilliant strategy!

[BenCasey]

I would say, if you intend to use the code elsewhere (perhaps another form) it is best to put it in a module and paramatise it if necessary, otherwise put it with the form so that the modules do not become too large (remember that if you load one procedure from a module then all procedures in that module are also loaded whether you want them or not).

If it is a startup form, put it in the form and keep it as light as possible.

As an example, say you have a module with tons of procedures within it which handles all sorts of dates and times functions and amongst them is one procedure which is called repeatedly from other locations I would remove that single procedure into its own module so that this frequently-used procedure is loaded and the other '00s of procedures are not. Loaded standard modules remain in memory until the DB is closed and you could be using resources unnecessarily.

Consider using light-weight forms and hyperlinks instead of buttons.

Consider moving all/most of your modules into its own separate DB and then linking to them as a Reference Library.

[HansV]

Consider using light-weight forms and hyperlinks instead of buttons.

Consider moving all/most of your modules into its own separate DB and then linking to them as a Reference Library.

I suspect that stuck meant "VBA userform" when he mentioned "form", not a form in an Access database.

[rory]

By the sounds of it, a plain Inputbox call would be sufficient, rather than a form.

[stuck]

By the sounds of it, a plain Inputbox call would be sufficient, rather than a form.

Agreed. In fact I might even be able to do away with the form completely but that's another story.

I started this thread because the person that originally wrote the code is leaving our office soon. It works 'as is' but even with my limited VBA skills I can see it is not structured very well. At the very least it needs loads more comments but I wondered if 'real' programmers had any general principles that would help me reorganise the code into something that was easier (for a novice) to follow.

Yes, I did mean a VBA user form. The code in question is Word VBA but it pulls data from an Excel workbook.

Thanks again,

Ken

[PJ_in_FL]

I started this thread because the person that originally wrote the code is leaving our office soon.

Oooooohhhhh. So sorry to hear you're getting STUCK managing other peoples code (Sorry! I couldn't resist! )

[stuck]

...you're getting STUCK...

I choose my username and avatar for that very reason when I first joined what was then Woody's Lounge, way back in the early 2000s. I'm still stuck now.

I don't mind picking up this code though. It was this person's first venture in to VBA and I'll learn more as I go through it.

Ken