VBA Efficiency

[PSULionRP]

I didn't know the best place to post this so I hope it's ok here.

I am dealing with some real spaghetti VBA code. It goes here...it goes
there...some of it is streamlined...while other pieces of it execute module
by module.

I am new to VBA and was just wondering as I'm going through this code, what
the efficiency standards are as far "best performance". Is it best to have
large modules or small modules that are executed by like a control function
or module???

Like I said, this code I'm looking at seems to do both. From a programmer's
perspective, it is definitely easier to read with small functions and subs
being executed one at a tim out of one main function or sub. And what is the
difference bewteen a "sub" and a "function"??? They both look syntaxtially(Is
that a word???) the same...

I appreciate your review and response.

Thanks.

PSULionRP

 

[Jerry Whittle]

In a nutshell, a sub does something, like open a form. A function returns a
value like the Date() function will return a date value.

If you even call one small sub or function that's in a module, the whole
module is loaded into memory. So in some respects it could be better to put
everthing in their own modules to conserver memory OR put everything in one
mega-module so that it's already loaded when another function inside of it is
called.

I usaully group like functions and subs inside a module for ease of
maintenance more than anything else. I have one module, that I call
modUtilities, full of stuff that I often use. I import this module into new
databases that I'm developing as a matter of routine.

 

[Douglas J. Steele]

In a nutshell, a sub does something, like open a form. A function returns
a
value like the Date() function will return a date value.

<picky>

Functions don't have to return values. Sometimes you need to define a
routine as a function just so you can call it when an event fires.

</picky>

 

[David W. Fenton]

<picky>

Functions don't have to return values. Sometimes you need to
define a routine as a function just so you can call it when an
event fires.

</picky>

It will still return a value, that is, Null, though it doesn't
matter when used in that context.