VBA - Code structure

[CuriousGeorge]

Hi,

Im learning vb in order to be able to add some functionality to my db and forms.

I got experience from java and c and im trying to figure out how you structure and organize ur code in vba and what the similarities are.

When programming in e.g. java you have ur classes and methods where u add ur functions and whatever u wanna do. And then u have ur main function from where u call all the methods etc.

In VB, what ive understood, u got models and all these commands which been added to various buttons on forms reports etc.

Can these models be seen as the corresponding classes in java where u store all ur functions etc?

When looking in VB from some already made database u can see all the Class Objects where commands have been added to forms etc. In addition u also have ur modules below. How do these work together? When do i need a module?

If i have a form performing different tasks i.e a collection of class objects - why would i for example need a module?

Cheers

 

[Galaxiom]

It is quite similar.

Objects like forms and reports have their Class Modules. Other Class Modules can also be made for your own objects.

The subs and functions in these are declared either Private or Public. Private are only available within the modules. Public become Methods of the Class. Variables declared Private in the declarations section of the module are available to the module. Private in within the Procedure are only for the procedure.

Public functions in Standard Modules are available directly to the whole project and database for queries etc. Variables declared Public in the Standard module are availiable to the whole VBA project.

 

[CuriousGeorge]

OKey thank u.

So is there any general way of structuring the code for a project?

Ive been looking at a few and it seems like sometimes code is within forms while other code is put in the class module.

Is there something like: procedures goes into the class objects i.e forms whereas functions goes somewhere else and functions are called within the module? Or can u just put everything within the module? is there any praxis for this?

Cheers

 

[Galaxiom]

The custom Class Module is a special thing. Everything in it is part of a Class object definition. Multiple instances of the class can be created with different names allowing the properties and methods to be refered to specific to the instance.

This is a clear, simple explanation of Class. It says Excel but the concept is universal to VBA.
http://www.cpearson.com/excel/classes.aspx

The Class Modules attached to Forms and Reports are simply predefined classes associated with these Access (for want of a better term) stock objects.

Code that is not connected to a particular object is placed in the Standard Module.

 

[vbaInet]

Just to add:

Events (such as the Click event of a control) are like event listeners in Java. Using Java swing you create a control and attach event listeners to it for certain actions. In Access it is created for you automatically when an object is instantiated.

Code you put in a Form Module is accessible only within that form. However, if you declare a function or variable as Public in a form's module it becomes "accessible" outside that module by directly referencing the form's object to get to that function. A form module also allows you to easily manage code that pertains to controls within the form.

Code put in a Standard Module and set as Public is accessible anywhere within the whole Project (like GalaxiomAtHome mentioned). So if you have a function that you want to make accessible in all forms and reports you write it in a Standard Module.

In the end it's all about accessibility and manageability.

 

[gemma-the-husky]

individual functions and subs also have arguments, which can be ether byref, or byval. (I think) unusually, the default is to pass byref - so changes to a passed argument within a sub can produce unexpected effects. Depends what you are used to.

You can also create classes of object, which are more object oriented, than normal functions and subs.

I think many users, like me, are used to procedural languages, and are comfortable creating subs and functions. (a function being a sub that returns a value, for purposes of clarification) [I think these are voids and methods in C?]

We are less used to creating classes, and objects, and probably because of that we reinvent the wheel and duplicate functionality from time to time, I guess.

 

[CuriousGeorge]

Thanks for ur responses.

One of the reasons im asking this is for example when adding some code in a module (standard) and performing some kind of function or procedure related to a form.. and presses the green 'play' button in vb-window it always asks me to save as a new macro...which in turn creates a new procedure which im not interested in.

Why is that and how do i get around that?

cheers

 

[vbaInet]

I've never needed to use the "Play" button (I call it ) and I think that's for running macros.

If you want to test run a function you created do it in the Immediate Window (i.e. View > Immediate Window) or run it in the click event of a button (for example).

To test a Sub in the Immediate Window called MySub you write:

MySub

To test a Function in the Immediate Window called MyFunction you write:

[COLOR=Red]?[/COLOR]MyFunction

If you want to learn more about the VBA editor I would advice you get some books.