Vba why modules

That however does not mean the code will run correctly, just that you made no syntax errors. Blue code indicates a reserved keyword has been typed. Keywords cannot be used to name anything in your code remember that. Green code is a comment which is basically a note to the person that is reading your code. These lines start with an apostrophe or the word Rem. When typing Excel and VBA commands i. This however does not indicate if it will run correctly. A common mistake also encountered when correcting code is that you want to press Enter when done.

Just click off the line when you are done editing. When typing your computer code, most of your code is typed within a procedure's boundaries. These statements are typed at the very top of a module in the declaration section before any procedures are typed and only if they are needed. Sub, Function, and Property procedures may all be typed in the same module, just make sure to name them uniquely.

A procedure itself is limited to 64K in size, which means how much text is typed within its boundaries. It is a very large amount of code and you will not bump up against it when first starting out. A common misperception is that VBA code must be stored in the same workbook as it is commanding.

Actually your modules containing your VBA code can be stored in any desired workbook. The author generally stores them in a project workbook that a user clicks buttons in to run their procedures. VBA code can search through Excel for the workbooks it needs or open them as needed, command them, then make new workbooks to store reports, charts, tables Keeping code in a single project workbook that is not attached to your data, charts, reports and so forth prevents you from making copies of your VBA code thus making version control impossible.

A note here, when creating models in workbooks, you would have one model workbook that could be upgraded as needed and issued and you would upload and download the model parameters to the model using VBA storing them separately in workbooks and text files.

This strategy is great for batch processing and trade studies. How VBA commands Excel is through object expressions i. Command commonly referred to as "paths". If you do not assume things are active in your VBA code and create the proper object expressions to track the Excel elements your are commanding, the code may be housed anywhere as discussed above. Relying on things being active is what causes most Excel VBA code to run slow and work intermittently.

While using the Macro Recorder is a very good research tool for figuring out specific Excel commands and their syntax, it writes horrible code. Drag and Drop that module to the project where you want to copy it. When you insert a new module, you can see an option to insert a class module.

As you have understood all about the standard modules, class modules are special modules that can help you create your custom objects. You can also define methods, properties, and events for those objects. And when you create a new object from the class module, you can refer to it from the standard module as well.

The following are the disadvantages of using class module to create objects. However, in the long run it will save you a huge amount of time. Your code will be easier to manage, update and reuse.

If you would like to see working examples of this code you can download the source code from the top of this post. To create a class module we right-click in the Project window and then select Insert and Class Module. Our new class is called Class1. We can change the name in the Properties window as the following screenshot shows:.

Then we will add a variable to the class module like this:. We can use now use this class module in any module standard or class in our workbook. For example. People who are new to using classes and VBA class modules, often get confused between what is a class and what is an object.

Think of a mass produced item like a coffee mug. A design of the mug is created first. Then, thousands of coffee mugs are created from this design. The New keyword in VBA is what we use to create an object from a class module.

For example:. See When New is not required for more information. Writing code in a class module is almost the same as writing code in a normal module. We can use the same code we use in normal modules. These often cause confusion among new users. For example, imagine we have two identical PrintCustomer subs. One is in a class module and one is in a normal module…. To use the PrintCustomer sub from the class module, you must first create an object of that type. When you create a variable in a normal module there is only one copy of it.

For a class module, there is one copy of the variable for each object you create. For example, imagine we create a variable StudentName in both a class and normal module..

For the class module, a new copy of the variable StudentName is created each time a new object is created. If we run the code we will get the following: Starting balance is: Balance after deposit is: Balance after withdrawl is: Methods refer to the procedures of the class.

In VBA procedures are subs and functions. Like member variables they can be Public or Private. The member variable is very similar to the normal variable we use in VBA.

The difference is we use Public or Private instead of Dim. The Public keyword means the variable can be accessed from outside the class module. In the above example, we cannot access Balance because it is declared as Private. We can only use a Private variable within the class module. It is considered poor practice to have public member variables. This is because you are allowing code outside the object to interfere with how the class works.

The purpose of the using classes is so that we hide what is happening from the caller. We have seen already that the Property is simply a type of sub. The purpose of the Property is to allow the caller to get and set values. Imagine we have a class that maintains a list of Countries.

We could store the list as an array. This code solves the two problems we listed above. We can change our Array to a Collection and the caller code will still work e. The caller is oblivious to how the countries are stored.