When to create modules

[AUGuy]

I was just curious as to what everyone's rule was for when to create a new module/function versus entering the full code in the event sub.

 

[pbaldy]

Generally speaking, I create a module/function when the process needs to be run from more than one place. With a function, I only maintain the code in one place, then just call it from the appropriate places.

 

[tehNellie]

I place all shared VBA functions in to a module called, funnily enough Functions.

Functions that I call specifically from queries go into a module called SQL_functions.

If I have a procedure/sub that is called from multiple places I'll create a module for that/them. I try to keep these to a minimum, if I have shared methods I tend to create a class for them and most of my code is class based so it can be re-used or switched where necessary.

I have (non class) modules for snippets of code that I use on an ad-hoc basis or need from time to time but don't necessarily form part of a defined process within my application.

 

[AUGuy]

Cool, that's kind of what I figured. Just wanted to see if there were any dissenting opinions.
Thanks!

 

[tehNellie]

If you're interested in coding practices wider than just "making things work" I'd recommend getting your hands on "Code Complete" From Microsoft Press. It goes way beyond the scope of VBA and is essentially language agnostic (thought it does include samples of VB.net and Java code) but is a great book for getting your head round good coding practices and structure.

 

[pbaldy]

I'll add a word on scope. If a function is only going to be used from multiple places on a form or report, I'll have that function in that form/report's module. If it will be called from different forms/reports or any query, I'll have it in a standard module. Same with variables.

 

[tehNellie]

No argument from me on that point.

There are functions and functions

I have one that creates an ADODB command object, I use that one a lot, it goes into the shared Functions modules.

I have another that checks what version of Excel a user has, that one is in the Excel Export class.

 

[Galaxiom]

I tend to break down complex procedures and functions into block as separate subs or functions. This encapsulates the sub-process and simplifies reading of the main procedure.

I do this even when the function or sub is not going to be used elsewhere in the module. It is remarkable how often this pays off when extending functionality of the database and finding the block of code is useful in other contexts.

 

[tehNellie]

Agree on that as well

If you have a procedure that runs for hundreds of lines, how many times do you repeat the same (or very similar) code?

What about the next procedure down that also has areas of commonality?

It's a judgement call as to whether you should break something out into a separate method rather than embed it into your main body of code, but I like have compact procedures that are, as much as possible "self commenting".

Sometimes it's unavoidable to have a "huge" block of code to do something, if that's largely irrelevant to what the main body of code is supposed to do, that's when I tend to break it out into a separate method.

 

[tehNellie]

I'll also add that I like separation of function in my code. My forms don't talk to the database. They talk to classes that give or take the information from the form. Those classes in turn talk to a communication "layer" that process data and package it to and from the database.

This is a horribly over engineered way to write vba code for an access database but the application, business logic and database communication are all independent of each other. I can keep access as a front end but switch the back end into SQL server, oracle, etc in a very short space of time. Likewise I can move the front end into something else and use the business logic and database comms layer more or less as is.

 

[Galaxiom]

I find it is helpful to always think generally when designing a function or procedure. For example when addressing a particular control, a good developer will consider if there is potential benefit in supplying that control as an argument rather than hardcoding so that the same function can be applied to other controls later.

Then you ask if it has use beyond the original form scope too.

It is much easier to design that kind of generality from the start than impliment it later when you realise you are about to do something similar again.

 

[Simon_MT]

The only sub rountine I use is to enble the Mousewheel only because I can't work out how to create a Function that works.

To me the real beauty of Functions is that, as an example I want to Double Click a subform and the Image will appear in a Dialogue Form. The same function can be used by employing With CodeContextObject.

Simon

 

[Galaxiom]

To me the real beauty of Functions is that, as an example I want to Double Click a subform and the Image will appear in a Dialogue Form. The same function can be used by employing With CodeContextObject.

As far as I can tell the main difference between a sub and a function is function can return a value.

Of course a sub can return a value through its ByRef arguments.

 

[pbaldy]

As far as I can tell the main difference between a sub and a function is function can return a value.

Jason summarizes some other differences here:

http://www.baldyweb.com/SubFunction.htm

 

[Simon_MT]

Jason

That is very intuitive. Does that mean you can put Public Subs as a Function?

Just trying to use a MouseWheel function!

Simon

 

[pbaldy]

Was the "Jason" question directed at me? If so, Jason wrote the FAQ but I host it on my site. Yes, any sub could be a function instead (at least I can't think of an instance where you'd HAVE to make it a sub).

 

[thesavo]

I think this maybe appropriate as it deals with modules in Access.

I maintain a few Access data tools in my work flow of creating users for our info systems. A lot of the code that I write is getting converted to functions and all stored in "Module 1" of the separate ACCDB files . I do this in each of the MS Access tools. However it's getting out of had to keep synchronizing the "Module1's" of all of these data tools. I know it's possible , but i just can;t seem to get the Google foo to tell me how to consolidate all these functions into a DLL that I can maintain in Visual Studio.

These access tools are expected to be used by others in the office. They are stored on a network drive.

 

[gemma-the-husky]

a sub is effectively a specialised version of a function, where you are discarding the result.

you must declare a procedure as a function to call it as an event handler, or, say, as a function for a menu event handler, even if you do not return a value.

eg, I have a large close button on a lot of forms, with the click event =standardclose(). I can copy the button from form to form, as it brings its code with it.

this calls

function standardclose()
with screenactiveform
docmd.close
end with
end function

------
generally speaking, I put anything I might reuse in a module. I have also ended up weith a lot of "standard" modules that I copy from project to project, so a lot of functions actually do not get used in many projects.

I tried putting all these in libraries, but gave up, as it was proving awkward to give each user his own library (due to problems setting reference libraries as relative paths). maybe every user could have shared a standard library, but I was trying to avoid that, and give each user their own dedicated code.

I expect a lot of us even end up duplicating functions, when we can't find exactly where we put them last time!

 

[AUGuy]

I expect a lot of us even end up duplicating functions, when we can't find exactly where we put them last time!

So true....so true...

 

[Simon_MT]

I have manged to minimise duplication by using With CodecontextObject. This allows the same Function to be used irrespective of (mainly) the Form from which it is it is called.

Simon