• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
  • Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
  • OverflowAI GenAI features for Teams
  • OverflowAPI Train & fine-tune LLMs
  • Labs The future of collective knowledge sharing
  • About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

Classes vs. Modules in VB.NET

Is it considered an acceptable practice to use Modules instead of Classes with Shared member functions in VB.NET?

I tend to avoid Modules, because they feel like leftover remains from Visual Basic 6.0 and don't really seem to fit in anymore. On the other hand, there doesn't seem to be much difference between using a Module and a Class with only Shared members. It's not that often that I really have much need for either, but sometimes there are situations where they present a simple solution.

I'm curious to hear whether you have any opinion or preferences one way or the other.

Peter Mortensen's user avatar

  • 11 One interesting about modules is that by default the methods and functions declared inside have the protection level of the module, which means you could inadvertently make methods available if you forget to explicitly add the Private qualifier. In a class, the default protection level is private, which can be confusing behaviour unless you are aware about it. –  yu_ominae Commented Dec 18, 2012 at 6:53
  • I AM SO THANKFUL TO HAVE FOUND THIS! I've been wracking my brain to find out how Intellisense can pickup on so many other Methods and such without instantiating the classes explicitly as I'm developing several classes that I though should be global, and having more experience with VBA's Global attribute, I'm ecstatic to find out that Modules are indeed exactly what I am looking for. –  k1dfr0std Commented Jul 17, 2022 at 8:06

8 Answers 8

Module s are VB counterparts to C# static classes. When your class is designed solely for helper functions and extension methods and you don't want to allow inheritance and instantiation , you use a Module .

By the way, using Module is not really subjective and it's not deprecated . Indeed you must use a Module when it's appropriate. .NET Framework itself does it many times ( System.Linq.Enumerable , for instance). To declare an extension method, it's required to use Module s.

Mehrdad Afshari's user avatar

  • 7 Quite right, although I can use a private constructor to prevent instantiation and the NotInheritable modifier. Slightly uglier than a plain old Module, but has the same effect. Thanks for the pointer to the use of Modules in the Framework; I'll look into that. –  Tom Juergens Commented May 19, 2009 at 8:41
  • 8 Under the hood, they are just compiled to classes with [StandardModule] attribute. Also, using Module forces you not to have non-Shared things there which is a good thing. –  Mehrdad Afshari Commented May 19, 2009 at 8:47
  • 20 Modules are not the same as static classes in C#. Methods in a module are effectively global if they are in an imported namespace. –  JaredPar Commented May 19, 2009 at 14:16
  • 2 @JaredPar: My wording is probably bad. I should have said VB counterparts to C# static classes. From that statement, I meant to say using a Module makes sense where you'd write a static class in C#. –  Mehrdad Afshari Commented May 19, 2009 at 14:26
  • 3 @Chiwda See msdn.microsoft.com/en-us/library/bb384936.aspx : "Extension methods can be declared only within modules." –  Mehrdad Afshari Commented Aug 20, 2012 at 4:56

I think it's a good idea to keep avoiding modules unless you stick them into separate namespaces. Because in Intellisense methods in modules will be visible from everywhere in that namespace.

So instead of ModuleName.MyMethod() you end up with MyMethod() popups in anywhere and this kind of invalidates the encapsulation. (at least in the programming level).

That's why I always try to create Class with shared methods, seems so much better.

dr. evil's user avatar

  • 7 Agreed, I am working on an old program at the moment (probably a VB6 to VB.NET 1 port) with classes and modules. There are hundreds of global variables, subs and functions in about ten different modules, and it is hell finding out what originated where and how it got modified. –  yu_ominae Commented Dec 18, 2012 at 6:32
  • ok this is very old, but relevant to my question. I'm not a VB guy, but I have to work with it. I'm trying to break down a big class (>46,000 lines) because IntelliSense/ReSharper just die. I've found using Modules and just ripping out large chunks of functions into their own Module seems to work, but I'm wondering if I should give each module its own namespace as well. Would that be necessary? i.e. would it speed up IntelliSense just with the Modules, or would Namespaces help more? –  codah Commented Jul 13, 2020 at 23:40

Modules are by no means deprecated and are used heavily in the VB language. It's the only way for instance to implement an extension method in VB.Net.

There is one huge difference between Modules and Classes with Static Members. Any method defined on a Module is globally accessible as long as the Module is available in the current namespace. In effect a Module allows you to define global methods. This is something that a class with only shared members cannot do.

Here's a quick example that I use a lot when writing VB code that interops with raw COM interfaces.

JaredPar's user avatar

  • 4 Extension methods... another fine feature I always mean to use, but never really get around to - well, this gives me another good opportunity. I do get the bit about global scope, and globally available methods can certainly be convenient, but I feel a bit queasy about overusing them. At any rate, all of the answers so far tell me that I shouldn't dismiss modules out of hand; there are good reasons to use them in the right circumstances. –  Tom Juergens Commented May 19, 2009 at 16:28

It is acceptable to use Module . Module is not used as a replacement for Class . Module serves its own purpose. The purpose of Module is to use as a container for

  • extension methods,
  • variables that are not specific to any Class , or
  • variables that do not fit properly in any Class .

Module is not like a Class since you cannot

  • inherit from a Module ,
  • implement an Interface with a Module ,
  • nor create an instance of a Module .

Anything inside a Module can be directly accessed within the Module assembly without referring to the Module by its name. By default, the access level for a Module is Friend .

DavidRR's user avatar

  • classes can be instantiated as objects
  • Object data exists separately for each instantiated object.
  • classes can implement interfaces .
  • Members defined within a class are scoped within a specific instance of the class and exist only for the lifetime of the object .
  • To access class members from outside a class, you must use fully qualified names in the format of Object.Member .
  • Modules cannot be instantiated as objects ,Because there is only one copy of a standard module's data, when one part of your program changes a public variable in a standard module, it will be visible to the entire program.
  • Members declared within a module are publicly accessible by default.
  • It can be accessed by any code that can access the module.
  • This means that variables in a standard module are effectively global variables because they are visible from anywhere in your project, and they exist for the life of the program.

Craig's user avatar

When one of my VB.NET classes has all shared members I either convert it to a Module with a matching (or otherwise appropriate) namespace or I make the class not inheritable and not constructable:

JRS's user avatar

  • 3 And here's the syntax for a namespaced module: Imports <whatever> if you have any imports, Namespace MyCoolModule , Public Module MyCoolModule , <members without Shared >, End Module , End Namespace . –  Rory O'Kane Commented Apr 29, 2013 at 15:24

Modules are fine for storing enums and some global variables, constants and shared functions. its very good thing and I often use it. Declared variables are visible acros entire project.

GGSoft's user avatar

You must use a Module (rather than a Class) if you're creating Extension methods. In VB.NET I'm not aware of another option.

Being resistant to Modules myself, I just spent a worthless couple of hours trying to work out how to add some boilerplate code to resolve embedded assemblies in one, only to find out that Sub New() (Module) and Shared Sub New() (Class) are equivalent. (I didn't even know there was a callable Sub New() in a Module!)

So I just threw the EmbeddedAssembly.Load and AddHandler AppDomain.CurrentDomain.AssemblyResolve lines in there and Bob became my uncle.

Addendum : I haven't checked it out 100% yet, but I have an inkling that Sub New() runs in a different order in a Module than a Class, just going by the fact that I had to move some declarations to inside methods from outside to avoid errors.

SteveCinq's user avatar

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .

Not the answer you're looking for? Browse other questions tagged vb.net or ask your own question .

  • The Overflow Blog
  • Community Products Roadmap Update, July 2024
  • Featured on Meta
  • We spent a sprint addressing your requests — here’s how it went
  • Upcoming initiatives on Stack Overflow and across the Stack Exchange network...
  • The [lib] tag is being burninated
  • What makes a homepage useful for logged-in users

Hot Network Questions

  • I forgot to remove all authors' names from the appendix for a double-blind journal submission. What are the potential consequences?
  • Does it make sense to use a skyhook to launch and deorbit mega-satellite constellations now?
  • Why do Electric Aircraft Seem to Eschew Photovoltaics?
  • A check given by castling: is it a discovered check or a special case?
  • Restrict a specific group to access a directory and drive
  • Is there a way to change a cantrip spell type to Necromancy in order to fulfil the requirement of the Death Domain Reaper ability for Clerics?
  • It was the second, but we were told it was the fifth
  • Is there any evidence of reasoning or argument in ancient texts outside Ancient Greece?
  • Is the text of a LLM determined by a random seed?
  • How to solve the intersection truncation problem of multiple \draw[thick, color=xxx] commands by color?
  • How much does a factory reset help in hiding a device's identification details?
  • The meaning of "奪耳" in 《說文解字》
  • Weather on a Flat, Infinite Sea
  • ForeignFunctionLoad / RawMemoryAllocate and c-struct that includes an array
  • Was I wrongfully denied boarding for a flight where the airliner lands to a gate that doesn't directly connect to the international part the airport?
  • Are there dedicated research facilities in the USA?
  • Can player build dungeons in D&D? I thought that was just a job for the DM
  • Why was this a draw? What move I supposed to play to win?
  • Can you be charged with breaking and entering if the door was open, and the owner of the property is deceased?
  • Can a country refuse to deliver a person accused of attempted murder?
  • Segments of a string, doubling in length
  • What makes Python better suited to quant finance than Matlab / Octave, Julia, R and others?
  • Could two moons orbit each other around a planet?
  • What is the correct translation of the ending of 2 Peter 3:17?

visual basic modules tutorial

Organizing code in Visual Basic

last modified October 18, 2023

The basic building blocks of a Visual Basic program are:

Closely related to this topic is the scope and duration of a variable. A scope is the visibility of the declared variable.

ScopeDescription
Block scopeAvailable only within the code block in which it is declared
Procedure scopeAvailable within the procedure in which it is declared
Module scopeAvailable to all code within the module, class, or structure in which it is declared
Namespace scopeAvailable to all code in the namespace in which it is declared

A lifetime of a variable is a period of time during which a variable holds a value. Local variables exists as long as the procedure is executing. After that, they are not available anymore. However, if we declare a variable to be Static, the variable continues to exist after the procedure terminates. Module, Shared and instance variables exist for the lifetime of the application.

The basic example

First, we cover some basics.

The exact example, now without the module. We can put the code inside a class too. The Main procedure must be declared Shared , because the class is not instantiated. The compiler calls the Main method without creating an instance of the class. That is why it must be declared Shared . Java and C# work the same.

Namespaces are used to organize code at the highest logical level. They classify and present programming elements that are exposed to other programs and applications. Within a namespace, we can declare another namespace, a class, an interface, a structure, an enumeration, or a delegate.

We have a ZetCode namespace. In the namespace, we have a module Example1 .

We declare a namespace called ZetCode .

In the second file, we work with the Init method from the previous file.

We work in the same namespace.

The following code example has two distinct namespaces. We use the Imports keyword to import elements from a different namespace.

We import the elements from the MyMath namespace into our namespace.

Note that we do not need the Imports keyword. In the example, it is commented out. We can use elements from other namespaces by using fully qualified names of the elements.

The proc1 procedure creates a local x variable. This variable shadows the one, declared at a module scope.

Block scope

It is important to understand that variables declared within a block of code like If / End If or While / End While have a limited block scope and lifetime. The next example illustrates this.

We have an x variable declared Inside the If / End If block.

  • Network Programming
  • Windows Programming
  • Visual Studio
  • Visual Basic

Logo

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More .

It’s important to establish the fact that VB.NET is truly a first-class citizen when it comes to writing applications based on Microsoft’s .NET runtime. VB.NET has carried the label "object-based" for quite a long time, and many have viewed it as a "toy" language. The fact is VB.NET does support all the main tenets of Object Oriented Programming (OOP) including abstraction, encapsulation, inheritance and polymorphism. Visual Studio 2010 includes the most recent version of VB.NET, also referred to as VB 10.0. It contains many enhancements and features to take full advantage of version 4 of the .NET common language runtime (CLR) . If you’re coming to VB.NET from another language with OOP roots, you should understand most of the concepts here.

One of the main goals in OOP design is to create reusable components so programmers don’t have to keep reinventing the wheel, so to speak. An object is the most basic entity containing executable code, data, or both. VB.NET adds the concept of a Module to the mix mainly because it’s been there for a very long time. In reality, a VB.NET module is something similar to a C# static class. For now, it’s probably sufficient to view a module as a way to group a similar set of functions.

Modules contain subroutines marked by the keywords "Sub" and "End Sub". There is also a special subroutine called Main that is present in every console program. If you open Visual Basic 2010 Express Edition and create a new console application, you’ll have a total of four lines of code generated for you (see Figure 1), which are Module Module1, Sub Main(), End Sub, and End Module. This shows the default hierarchy of subroutines contained within modules.

The default hierarchy of subroutines contained within modules

In VB.NET, a Namespace is the highest container encapsulating classes and modules. We’ll take a much deeper look at .NET namespaces at another time, but for now you should at least know how to reference some of the common .NET namespaces. This is done in Visual Basic with the Imports keyword. Intellisense is your friend here and will help when you’re not sure of the exact spelling of the Namespace (see Figure 2).

Intellisense is your friend

As soon as you type the period after System you will see a list of options available to you. Namespaces use the curly brace "{}"pair as an identifying icon while classes use three boxes connected by lines (see Figure 3). If you scroll through the popup box, you will get tooltip help describing each item.

Namespaces use the curly brace "{}"pair as an identifying icon

Once you have imported a namespace, such as System.IO, you have access to all the functionality contained within that namespace. For example, in a console program you can now type something like the following:

You can also directly reference the System.IO namespace by using it as a prefix as in:

Both ways will work and will instantiate a file object named oFile. You then have access to all the nice Intellisense produced from the File object as shown in Figure 4. We’ll dive in to other namespaces in later articles and talk more about how and when to use a class in VB.NET.

You have access to all the Intellisense produced from the File object

More by Author

Best video game development tools, video game careers overview, the top task management software for developers, best online courses for .net developers, news & trends, using multiple programming languages to create an asp.net website, visual basic features in visual studio 2022, playing with strings: proper case, get the free newsletter.

Subscribe to Developer Insider for top news, trends & analysis

Making a Small Lotto Game in VB.NET

CodeGuru covers topics related to Microsoft-related software development, mobile development, database management, and web application programming. In addition to tutorials and how-tos that teach programmers how to code in Microsoft-related languages and frameworks like C# and .Net, we also publish articles on software development tools, the latest in developer news, and advice for project managers. Cloud services such as Microsoft Azure and database options including SQL Server and MSSQL are also frequently covered.

Advertisers

Advertise with TechnologyAdvice on CodeGuru and our other developer-focused platforms.

  • Privacy Policy
  • California – Do Not Sell My Information

Property of TechnologyAdvice. © 2023 TechnologyAdvice. All Rights Reserved Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.

  • Getting started with Visual Basic .NET Language
  • Environment Setup
  • Program Structure
  • Basic Syntax
  • Functions and Subs
  • Classes and Objects
  • Module Statement
  • Constructors
  • Access Modifiers
  • For Next Loop
  • For Each Loop
  • While End Loop
  • With End With Statement
  • Exit Statement
  • Continue Statement
  • GoTo Statement
  • Conditional Statements
  • Select Case
  • Collections
  • Exception Handling
  • File Handling
  • Windows Forms Application
  • Basic Controls
  • Events Handling
  • Inheritance
  • Polymorphism
  • Abstract Class
  • Regular Expressions
  • Database Operations
  • BackgroundWorker
  • ByVal and ByRef keywords
  • Connection Handling
  • Data Access
  • Debugging your application
  • Declaring variables
  • Dictionaries
  • Disposable objects
  • Error Handling
  • Extension methods
  • File/Folder Compression
  • Google Maps in a Windows Form
  • Introduction to Syntax
  • Multithreading
  • NullReferenceException
  • OOP Keywords
  • Option Explicit
  • Option Infer
  • Option Strict
  • Reading compressed textfile on-the-fly
  • Short-Circuiting Operators (AndAlso - OrElse)
  • Task-based asynchronous pattern
  • Type conversion
  • Unit Testing in VB.NET
  • Using axWindowsMediaPlayer in VB.Net
  • Using BackgroundWorker
  • Using Statement
  • Visual Basic 14.0 Features
  • WinForms SpellCheckBox
  • Working with Windows Forms
  • WPF XAML Data Binding

VB.NET Module Statement

Fastest entity framework extensions.

The Module statement declares the name of a module and introduces the definition of the variables, properties, events, and procedures that the module comprises.

  • It defines a reference type available throughout its namespace.
  • Every module has exactly one instance and does not need to be created or assigned to a variable.
  • Modules do not support inheritance or implement interfaces.
  • A module is not a type as a class or structure, and you cannot declare a programming element to have the data type of a module.
  • You can use Module only at the namespace level and must be declared in a source file or namespace, and it cannot be declared in a class, structure, module, interface, procedure, or block.
  • You cannot nest a module within another module, or any type.

Different Between Class and Module

A module is similar to a class, but there are some important differences as well.

  • Previous versions of Visual Basic recognize two types of modules: class modules (.cls files) and standard modules (.bas files) .
  • The current version calls these classes and modules, respectively.
  • You can control whether a member of a class is a shared or instance member.
  • Classes are object-oriented, but modules are not, so only classes can be instantiated as objects.

Modules are VB.NET counterparts to C# static classes. When your class is designed solely for helper functions and extension methods, and you don't want to allow inheritance and instantiation , you use a Module .

Let's consider the following example in which we have defined a Module .

To call the procedure defined in a Module , use the module name as shown below.

Got any VB.NET Question?

logo rip

  • Advertise with us
  • Cookie Policy
  • Privacy Policy

Get monthly updates about new articles, cheatsheets, and tricks.

Visual Basic Tutorial

Learn to program with visual basic.

visual basic modules tutorial

Thank you for visiting VisualBasicTutorial.NET, the newest Visual Basic tutorials on the web.

I’m of the firm belief that everyone can program. If you’re sitting there thinking, “not me” – it’s time to think again. You can program. I have been a software developer for over 20 years and am growing ever more frustrated at how inaccessible we programmers make this subject. The quality of teaching literature out there makes the task seem daunting and confusing. I’ll never forget the “lightbulb” moment when I “got” programming and the purpose of this site is to help expedite that “lightbulb” moment in a clear, concise set of interactive tutorials written in plain English with absolutely no jargon.

You might have the next billion dollar idea but don’t know where to start. You may be hoping for a career change and wish to take advantage of the boom in demand for skilled programmers. You may already be a developer but want to learn Visual basic. Or, you may just be interested to know what the heck programming is. If any of the above applies to you then Visual Basic Tutorial is for you.

Since I developed this site I’ve been asked why I chose Visual Basic as a teaching tool. There are far newer languages in vogue right now. Well it’s a language that I love, it’s a language that remains extremely popular and it’s a language that has simplicity at its core. Much like VisualBasicTutorial.net.

2 thoughts on “ Welcome! ”

Partner of the Website

HotSlots - Sugar Rush

In a symphony of collaboration and shared visions, hotslots.io emerges as our intricately chosen partner, weaving a tapestry of innovation and excitement into our collective journey. Nestled in the digital realm, HotSlots.io emerges as a vibrant beacon for casino enthusiasts, offering an electrifying blend of classic charm and modern gaming thrills. Here, players dive into a treasure trove of top-tier slots and live casino marvels, all while being lavished with generous welcome bonuses that truly set the stage for an unforgettable adventure. At HotSlots.io, every spin, every bet, opens the door to a world where entertainment knows no bounds.

Hi Chris, I want to learn visual basic in my spare time. I hope this tutorial will help me to learn VB very easily. Thank you.

Hi there! Let me know how you get on. I’m still working on the site so just let me know if there’s any improvements you’d like to see. If something’s not clear or somethings missing, just give me a nudge.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

AutomateExcel Logo

AutoMacro: Ultimate VBA Add-in

VBA Code Helper

Read all reviews

Return to VBA Code Examples

VBA Class Module Tutorial & Examples

visual basic modules tutorial

Editorial Team

visual basic modules tutorial

Reviewed by

Steve Rynearson

In this Article

Class Modules vs. Modules

Advantages of using class modules, disadvantages of using class modules, inserting a class module, creating an object item, creating a collection, using your new object, summary of creating an object using a class module, using a class module to create a variable repository, turning your object into an add-in.

This tutorial will teach you about Class Modules in VBA. You’ll learn what they are and how to use them.

VBA Class Modules – Introduction

When you insert modules into the Visual Basic Editor (VBE) in order to enter your code, you may have noticed that you can also insert what is called a ‘Class Module’.

vba class module

The class modules work in a very different way to the ordinary modules in that they facilitate creating a Component Object Model (COM) which can then be used within your normal VBA code

Effectively, you create an object which works in the same way as a built in Excel object such as ‘Worksheets’.  In the Worksheets Object , you have a number of properties and methods which allow you to get the number of worksheets within a workbook or each individual name of a worksheet, or numerous other information

When you create a new Object in this way, you are creating a building block which can be used anywhere within VBA. The Object has a number of properties and methods that can be accessed by your VBA code from anywhere within the workbook without having to keep re-writing the code over again.

As well as referring to your new object from a standard VBA module, you can also use it in the code behind a UserForm that is part of your custom application

You can also use it where you have placed Active X controls onto a worksheet, such as a command button or a drop down. These controls all use VBA, and your new object can easily be incorporated into the event code for these controls.

You can also turn your object into an Excel add-in. Your object will automatically be available to other users who have that add-in loaded.  This adds your own multi-tier architecture to your Excel application

Excel is a multi-tiered application.  There is the client services layer, which drives the actual worksheet window that that the user is familiar with. The Excel object model is the next layer underneath.  Press F2 in a VBA module and you will be able to see the huge number of objects and members of those objects that are the engine of Excel.  Note that your new object will also be displayed here.

Finally, underneath all of this, you have the data services layer which holds all the data that you have entered into the worksheets and cells.  Excel accesses this using the Excel Object model.

excel object model

Creating a Class Module allows you to extend the Excel Object Module with your own custom objects and members

This article explains to you how you to create a simple hierarchy of objects using Class Modules.

  • You can develop a robust building block which can be used in any number of different Excel applications
  • Once it is thoroughly tested, then is can be relied on to always produce the correct results in the same way as the built-in Excel objects
  • If updates are made to code elsewhere in the application, the new object will still continue to work in the same way
  • You can use your new object in other Excel applications as an add-in
  • The objects can be re-used in other applications and helps in debugging
  • They can be difficult to create and understand.
  • Naming conventions are very important because this is what you will see when use your object within a normal module.
  • If you have not created a class module before, they can be difficult to understand and there is a steep learning curve
  • Impossible to make changes at run-time – you have to re-set the project.
  • If Properties and Private Variables have the same name then infinite loops can occur resulting in errors

Select Insert | Class Module from the VBE (Visual Basic Editor) menu. The new Class Module will automatically be called ‘Class 1’, but this needs to be changed immediately to the name that you are going to use for your object

insert class module

You change the name in the Properties window where the arrow is pointing.  You simply type in your new name, and this will change in the Class Modules collection

If the Properties window is not visible, then select View | Properties on the VBE menu or press F4

Call your new class module ‘MyItem’ and double click the name in the tree-view in the Project Explorer to display the code window for it.

This example will create a top-level object called ‘MyItems’ with a member object below it called ‘MyItem’ which will hold the individual data for each item.  Once created it will work in the same way as a built in Excel Object. For example, there is an object called ‘Worksheets’ which is a collection of each worksheet within your workbook. There is also an object called ‘Sheet’ which represents each individual worksheet within your workbook, and holds all the properties and methods for each worksheet.  This object relates to the ‘Worksheets’ collection object.

You can iterate through the ‘Worksheets’ collection, viewing each ‘Sheet’ in turn.  In the same way you will be able to iterate through the ‘MyItems’ collection viewing the properties that you created in the ‘Myitem’ member.

The first thing to do is to create the sub object for the member level which will hold the actual items within the collection of the top-level object.  This is the equivalent of the members (e.g. name, visible, count) within the ‘Sheet’ object in Excel.  This code is entered into the class module called ‘MyItem’

Class modules have Properties and Methods.  Properties are effectively like variables, in that they hold values of data like variables, and Methods are like sub routines or functions.

In the sub object we are going to create two properties for the object – Item and Detail

Initially two string variables need to be declared to hold the values for the properties:

These need to be declared in the Declarations section at the top of the code for the class module so that they can be used in all sub routines throughout the module

They need to be given unique names to make them different from the properties that we are going to create, so an ‘m’ (for member) has been put in front of each name.

The variables are declared as Private so they cannot be seen by anyone using the object.  They are working variables for use within the object code and are not there as part of the final object.

The next step is to set up code to give access to the two properties. You do this by means of a Property Let and a Property Get statement for each property.  These must be Public otherwise the top-level object will not have any visible properties

This code creates the means to read and write values to the two properties (Item and Detail) using the two private variables that were defined in the declarations section of the module.

The ‘vdata’ parameter is used to pass data to the property concerned.

It is important that each property has a ‘Let’ and ‘Get’ statement and that the property name is the same in each case. You could end up with two different properties if miss-spelt  – one that you can read from and one that you can write to!

To help with creating this code, you can use Insert | Procedure on the VBE menu to create a code skeleton which will create the initial code for the ‘Get’ and ‘Let’ properties for a given property name

This will display a pop-up window where you type the property name in and select ‘Property’ on the radio buttons:

class module property

Click ‘OK’ and the skeleton code will be added into the class module:

This prevents any mistakes over names of properties. You simply add your code in between the ‘Public Property’ and ‘End Property’ statements.

You now have an object called ‘MyItem’ which will hold all the data for this exercise.

The next stage is to create a top-level object as a Collection object to give access to the properties that you have set up in the ‘MyItem’ object

Again, you need to define a working object to act as the collection object in the same way that you defined the two string variables in the ‘MyItem’ object.

Again, this this has to have a unique name which is why there is an ‘m’ (member object) in front of the name, and it is also declared as ‘Private’ so that it does not appear when the new object is being used

Next, you need to populate the Class_Initialize code. This runs when you first use the object within your code, and it determines what values will be loaded into the object

You can access this sub routine by selecting ‘Class’ in the first drop down and ‘Initialize’ in the second drop down of the module window

The code sets up an object called ‘objItem’ using the definition of ‘MyItem’ which we built as a class module earlier on.

It then creates a new Collection based on the ‘mItems’ object defined earlier

It iterates through values held on Sheet1 of the workbook and puts them into the properties that we created for the ‘MyItem’ object.  Note that when you use ‘objitem’, a drop down appears showing the two properties, exactly as if you were using a built-in Excel object.

The item object is then added into the collection object which now holds all the data in the property values.

The input data does not have to be taken from a worksheet.  It could be static values, or it could come from a connection to a database such as Microsoft Access or SQL Server, or it could come from another worksheet.

You then need to add a public function called ‘Item’

This allows you to refer to individual objects within the collection object by their index number.  This function provides a ‘mirror’ of what is going on in the ‘mMyItems’ collection in the background.

You will also need to add a property called ‘Count’ so that your code can establish how many ‘MyItem’ objects are in the ‘MyItems’ collection, should you wish to iterate through it.

In this case you only need a ‘Get’ property because it is read-only.  It uses the mItems collection because this already has a count property built into it.

You now have an object (MyItems) with a full hierarchy defined by the object ‘MyItem’

To make the whole thing work, you now need to populate a worksheet (Sheet1) with data so that the Class Initialize routine can collect this into the object

Your spreadsheet should look like this:

class module collection

You can now use your Collection object (MyItems) within a standard Excel VBA module. Enter the following code:

This code creates an object called ‘MyClass’ based on the collection object that you created called ‘MyItems’. This fire off the ‘Initialize’ routine that extracts all the data from the worksheet into the object.

It displays the number of items in the collection and then iterates through the collection showing the ‘Item’ text and the ‘Detail’ text. You will notice that when you refer to the ‘MyClass’ object in your code, you will see a list of the two member properties which helps in adding the correct property.

If you change the value of a cell in the input data on the spreadsheet, this will automatically be updated in the collection when you run the above code again, since when you dimension the object, the initialize routine runs and picks up all the new data

If you use the word ‘Static’ instead of ‘Dim’ the initialise routine does not run and the old values are kept, so long as the code is continuously running.  If the data on the spreadsheet changes this will not be reflected in the object

As you have seen, creating a hierarchy of class modules to use as an object is quite a complicated business, even for a structure as simple as the example provided here.  The scope for making mistakes is enormous!

However, it does have huge advantages in making your code more elegant and easier to read.  It is also easier to share with other Excel applications and developers by turning it into an Add-In.

In this example of how to create an object to hold data, it would be a normal approach to create a multi-dimensional array to hold the multi-column spreadsheet data, and you would write a line of code to update or read each element in the array. This would probably end up being quite messy, and mistakes could easily be made in addressing the various elements.

With your new object, you can simply refer to it and the members that you have created below it to hold the data.

Also, if the data changes in the spreadsheet (or in a linked database if you have used this as a data source within your class module) whenever you use the ‘Dim’ statement the initialize routine will be called and the data will be instantly updated. No need to write code to re-populate your array.

When you write VBA code you use variables all over the place, all with different scopes.  Some may only be defined for a particular procedure, some for a particular module, and some may be global variables that can be used all over the application

You can create a class module that will hold a large number of variables, and because it is an object, it can be used anywhere within your code, even on a user form or in an Active X control that you have placed on a worksheet.

The added advantage is that when you refer to your variable object, you will see a list of all the variable names held in the object sorted into ascending order.

To create a repository, you need to insert a new class module. You do this by using Insert | Class Module from the VB Editor menu

Change the name to ‘MyVariables’ using the same methodology as previously discussed in this article.

Enter the following code:

This code sets up ‘Let’ and ‘Get’ properties for two variables (‘Variable1’ and ‘Variable2’).  The Let and Get properties are required for each of your variables so that they are read / write

You can use your own names for the variables instead of the sample ones in this code, and you can add further variables, making sure that each new variable has a ‘Let’ and ‘Get’ statement.

The private declaration of the variable ‘mV’ is to create a working variable that is only used within the class module to transfer values.

To use the variable repository, enter the following code into a standard module:

This code creates a global instance of your ‘MyVariables’ object that you created.  You only need to do this declaration once from anywhere within your code.

The code first displays the value of ‘Variable1’ to show that it is empty.

A value of 10 is assigned to ‘Variable1’ and the new value within the object is then displayed to show that this property now holds this value.

Because the instance of the ‘MyVariables’ object has been defined globally, you can refer to any of the defined variables within the object from anywhere within your code.

This has a huge advantage in that if you want to use your variables anywhere in your code, you only need to define one global variable, and from that instance, all the variables can be freely accessed and modified throughout your code.

So far, the code for the object creation is within your workbook application.  However, if you want to be able to share your object with other developers or in other Excel applications of your own, you can turn it into an Add-In

To do this, all that needs to happen is to save the file as an Add-In.  Select File | Save As and a browser window will appear

Select the file type as Add-In (.xlam) from the file type drop down and click OK.  The file will be saved to the Add-In folder by default, but you can change the location.

You can then incorporate the add-in file into your Excel applications, giving you the flexibility to make use of your new object

vba class module example

To include your new Add-In into Excel, click on File on the Excel ribbon, and then click on Options at the bottom of the left-hand pane

Click on ‘Add-Ins’ in the left-hand pane in the pop-up window that appears.  At the bottom of the window is a button marked ‘Go’

Click on this and an ‘Add-In’ pop-up window will appear. Click on ‘Browse’ and then locate your Add-In file. You will then be able to refer to your object in your code.

vba class module tutorial

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

automacro

AutoMacro: VBA Add-in with Hundreds of Ready-To-Use VBA Code Examples & much more!

Geek Pedia

The Ultimate Beginner’s Guide to Visual Basic

The Ultimate Beginner's Guide to Visual Basic

Visual Basic (VB) is an accessible, yet powerful programming language designed by Microsoft. This guide introduces beginners to VB, providing a clear path to understanding its fundamentals and practical applications.

Understanding Visual Basic: An Overview

What is Visual Basic? VB is a user-friendly programming language known for its simplicity and readability, making it ideal for beginners. It’s used to create Windows applications, automate tasks, and develop complex programs.

History and Evolution of VB Developed in the early 1990s, VB has evolved significantly. Initially designed for simple GUI applications, it now supports complex programming needs.

Why Choose Visual Basic? VB’s ease of use, strong Microsoft support, and extensive community resources make it a great choice for new programmers.

Setting Up Your Environment

Before you can start programming in Visual Basic, you need to set up an Integrated Development Environment (IDE). Microsoft’s Visual Studio is the primary IDE for Visual Basic development. This section guides you through installing Visual Studio and familiarizing yourself with its interface, preparing you for your first VB project.

Installing Visual Studio

Visual Studio is a feature-rich IDE that supports various programming languages, including Visual Basic. Follow these steps to install it:

  • Download Visual Studio : Visit the Microsoft Visual Studio website and download the Community Edition, which is free for individual developers, open-source projects, academic research, education, and small professional teams.
  • After downloading, run the installer.
  • You will be prompted to choose workloads for your installation. A workload is a set of features needed for specific types of development.
  • For Visual Basic, select the “.NET desktop development” workload. This includes all necessary components for creating desktop applications using VB.
  • You can also explore and add other workloads based on your interests, but they are not necessary for basic VB development.
  • Click “Install” and wait for the process to complete. It might take some time, depending on your internet connection and the selected workloads.
  • Once installed, launch Visual Studio.
  • You might be prompted to sign in with a Microsoft account. This step is optional but recommended for syncing settings across devices and accessing additional services.

Navigating the Visual Studio Interface

After installation, familiarize yourself with the Visual Studio interface:

  • Start Page : When you launch Visual Studio, you’ll be greeted with a Start Page. Here, you can create new projects, open existing ones, and find useful resources.
  • Solution Explorer : This is where you’ll manage your project files. It’s a tree-like structure showing all the files and folders in your project.
  • Properties Window : This window shows properties of the selected item in your project, like controls on a form.
  • Main Coding Area : This is where you’ll write and edit your code. It’s a large, central part of the interface.
  • Toolbox : Contains various controls that you can drag and drop onto your forms when designing a GUI.

Creating Your First VB Project

Now, let’s start a new Visual Basic project:

  • In Visual Studio, go to “File” > “New” > “Project”.
  • In the “Create a new project” window, search for “Visual Basic”, and select “Windows Forms App (.NET Framework)”.
  • Click “Next”.
  • Give your project a name and location.
  • Set other details like the solution name and whether to place the project in a directory.
  • Once created, Visual Studio opens the main form (Form1) in design view.
  • Explore the Solution Explorer to see the files created by default, including your main form’s code file.
  • The Design View allows you to design the interface of your application.
  • You can switch to Code View by right-clicking the form and selecting “View Code”, where you’ll write VB code for functionalities.

Basics of Visual Basic Programming

Embarking on your journey with Visual Basic begins with understanding its core programming concepts. This section covers the basic syntax, variables and data types, and operators and expressions in Visual Basic, providing a solid foundation for your programming skills.

Understanding the VB Syntax

The syntax of a programming language is the set of rules that defines the combinations of symbols that are considered to be correctly structured programs. Visual Basic’s syntax is known for its readability and simplicity, making it an excellent choice for beginners.

  • Case Insensitivity : Unlike some other programming languages, VB is not case-sensitive. For example, Dim , dim , and DIM are all interpreted the same way.
  • Ending Statements : In VB, each statement is typically on a new line, and you don’t need a specific character (like a semicolon in C# or Java) to signify the end of a statement.
  • Comments : Comments are non-executable lines that help you document your code. In VB, comments are added using the ' (single quote) character at the beginning of the comment.

Variables and Data Types

Variables and Data Types

Variables are used to store data that can be changed during program execution. Each variable in VB must be declared before use, specifying its type.

  • Declaring Variables : Use the Dim statement to declare a variable, followed by the variable name and type. For example, Dim age As Integer .
  • Integer : For whole numbers.
  • String : For text.
  • Boolean : For True/False values.
  • Double : For large or decimal numbers.
  • Assigning Values : Assign values to variables using the = operator. For example, age = 21 .

Operators and Expressions

Operators are symbols that specify the type of operation to perform with the operands. In VB, you’ll commonly use:

  • Arithmetic Operators : Like + (addition), - (subtraction), * (multiplication), and / (division).
  • Comparison Operators : Like = (equal to), <> (not equal to), < (less than), > (greater than).
  • Logical Operators : Like And , Or , and Not , used in decision-making.

Expressions are combinations of operators and operands that VB can evaluate to produce another value. For example, Dim result As Integer = 5 + 3 .

By grasping these fundamental concepts of Visual Basic programming, you are now prepared to start writing simple programs and exploring more complex features. Remember, practice is key, so experiment with these concepts to solidify your understanding.

Working with Controls

One of the key features of Visual Basic (VB) is its ability to create interactive and user-friendly graphical user interfaces (GUIs). In VB, GUI elements like text boxes, buttons, and labels are referred to as ‘controls’. This section will explore how to work with these controls, focusing on their implementation, usage, and event handling.

Introduction to VB Controls

Controls are the building blocks of a Windows Forms application. They are objects on a form that enable user interaction. Common controls include:

  • Text Boxes ( TextBox ) : Allow users to input text.
  • Buttons ( Button ) : Can be clicked to trigger actions.
  • Labels ( Label ) : Display text, usually as information or labels for other controls.

Using Text Boxes, Buttons, and Labels

Adding controls to a form.

  • In the Visual Studio Design View, you’ll find a toolbox that contains various controls.
  • You can drag and drop these controls onto your form.
  • Once placed, you can move and resize them as needed.

Configuring Control Properties

  • Every control has properties like Text , Size , and Location that you can configure.
  • For instance, you can change the text of a button or label in the properties window.
  • These properties can be set at design time (in the Visual Studio IDE) or at runtime (through VB code).

Example: Creating a Simple UI

  • Imagine creating a basic UI with a text box for user input, a button to submit, and a label to display results.
  • Drag a TextBox , Button , and Label from the toolbox to your form.
  • Set their properties, such as Name , Text , and Location , either using the properties window or by writing code in the form’s constructor or load event.

Event Handling in VB

Events are actions or occurrences that happen during the running of a program. Controls respond to different events, like clicks, text changes, or key presses.

Understanding Event Handlers

  • An event handler is a block of code that executes in response to an event.
  • For example, clicking a button can trigger a Click event.

Writing Event Handlers

  • Double-clicking a control in the Design View automatically creates an event handler for its default event (e.g., the Click event for a button).
  • In the Code View, Visual Studio will generate a stub method where you can write the code that executes when the event occurs.

Example: Handling a Button Click

  • Suppose you have a button named submitButton .
  • Double-click submitButton in the Design View.
  • Visual Studio opens the Code View and creates an event handler named submitButton_Click .
  • Write VB code inside this method to define what happens when the button is clicked.

In this example, when the button is clicked, the text from the TextBox is displayed in the Label .

Writing Your First Program

Now that you’re familiar with setting up your environment and working with controls, it’s time to write your first program in Visual Basic. We’ll create a simple application that takes user input and displays a response. This section will guide you through planning your program, coding it step-by-step, and then running and testing it.

Planning Your Program

Before jumping into coding, it’s crucial to have a clear idea of what your program will do. For our first project, let’s create a simple “Greeting Generator” that asks for the user’s name and then displays a greeting.

  • Define the Objective : The program should prompt the user to enter their name and then display a personalized greeting.
  • Sketch the Interface : Plan a simple interface with a TextBox for the user to enter their name, a Button to submit, and a Label to display the greeting.
  • Plan the Logic : When the user clicks the Button, the program should read the text from the TextBox, construct a greeting, and display it in the Label.

Coding Step-by-Step

Coding Step-by-Step

Setting Up the Interface

  • Add Controls : Place a TextBox , a Button , and a Label on the form.
  • Configure Properties : Set appropriate names (e.g., nameTextBox , greetButton , greetingLabel ) and default texts for each control.

Writing the Code

  • Double-click the Button in the Design View to create a Click event handler in the Code View. 
  • In the greetButton_Click method, write the code to construct the greeting. 
  • The userName variable stores the text entered in the nameTextBox . 
  • The greeting is constructed using string concatenation and displayed in the greetingLabel . 

Running and Testing Your Application

Testing the program.

  • Run the Program : Press F5 or click the ‘Start’ button in Visual Studio to run your application.
  • Enter a Name : Type a name into the TextBox and click the Button.
  • Check the Output : Verify that the Label updates with the correct greeting.
  • If the program doesn’t work as expected, use Visual Studio’s debugging tools.
  • Set breakpoints, step through your code, and watch variables to understand what’s happening.

Refining Your Program

  • Once your basic functionality is working, consider adding features or improving the UI.
  • For instance, add a feature to clear the TextBox after displaying the greeting.

Debugging and Error Handling

Debugging and error handling are crucial skills in any programming language, including Visual Basic (VB). This section will guide you through identifying and fixing errors in your VB programs, as well as implementing error handling to make your applications more robust and user-friendly.

Common VB Errors

As a beginner, you’re likely to encounter several types of errors in your VB projects:

  • Syntax Errors : Occur when the code violates the grammatical rules of VB. Common syntax errors include misspelling keywords or missing punctuation, like parentheses or quotes.
  • Runtime Errors : Happen when the program is running. These could be caused by invalid user input, file not found, etc.
  • Logical Errors : These are the most challenging to detect as they don’t produce explicit error messages. Logical errors occur when the program runs without crashing but produces incorrect results.

Using the Debugging Tools

Visual Studio provides a set of powerful debugging tools to help you find and fix errors in your code.

  • Breakpoints : A breakpoint pauses the execution of your program so you can examine its state. Set a breakpoint by clicking in the margin next to a line of code.
  • Step Through Code : Once your program is paused, you can step through your code line by line to observe how variables and states change.
  • Watch and Local Windows : Use these to monitor the values of variables as your code executes.
  • Immediate Window : Allows you to execute VB commands on the fly, which is useful for testing potential fixes.

Effective Error Handling Techniques

Error handling in VB is managed using the Try...Catch...Finally block.

Try-Catch Block

  • Try Block : Enclose the code that might cause an error within a Try block.
  • Catch Block : If an error occurs, control is passed to the Catch block. You can specify different Catch blocks for different types of exceptions.
  • Finally Block (Optional): This block runs regardless of whether an error occurred. It’s often used for clean-up code.

Implementing Error Handling

  • Identify Risky Code : Look for code segments where errors might occur, like file operations or user input processing. 
  • Enclose in Try-Catch : Wrap these segments in Try...Catch blocks. 
  • Provide Useful Feedback : In the Catch block, give users clear, non-technical messages about what went wrong. 

Advanced Concepts in Visual Basic

As you become more comfortable with the basics of Visual Basic (VB), you can start exploring advanced concepts that enable you to build more dynamic and sophisticated applications. This section covers three key areas: working with databases, file handling, and creating custom user interfaces.

Working with Databases

Database connectivity is a crucial aspect of many applications. VB makes it relatively straightforward to connect to, query, and manipulate databases.

Database Connectivity

  • ADO.NET : VB uses ADO.NET for database operations. It provides a set of classes for interacting with data sources.
  • Connection Strings : Establish a connection to a database using a connection string, which specifies the database type, location, credentials, and other parameters.
  • SQL Queries : Execute SQL queries using VB to retrieve, insert, update, or delete data.

Example: Connecting to a SQL Database

  • Set Up a Connection : Create a SqlConnection object with your database’s connection string.
  • Open the Connection : Use the Open method to establish the connection.
  • Execute a Command : Use SqlCommand to execute queries.
  • Close the Connection : Always close the connection with the Close method.

File Handling in VB

File handling is another important aspect, allowing your programs to store and retrieve data from files.

Reading and Writing Files

  • System.IO Namespace : Contains classes for file operations.
  • StreamReader and StreamWriter : Use these classes for reading from and writing to text files.

Example: Writing to and Reading from a Text File

  • Writing to a File : Use a StreamWriter to write text to a file.
  • Reading from a File : Use a StreamReader to read text from a file.

Creating Custom User Interfaces

visual basic modules tutorial

While standard forms and controls are useful, sometimes you need to create a custom user interface for better user experience.

Custom Controls

  • VB allows the creation of custom controls by extending existing ones or creating new ones from scratch.

Graphics Programming

  • Use the System.Drawing namespace to draw graphics, like shapes and text, for custom appearances.

Event-Driven Customization

  • Customize control behavior by handling their events in unique ways.

Example: Customizing a Button Control

  • Create a custom button with unique visual properties.
  • Override the OnPaint method to change how the button is drawn.

Best Practices and Tips

As you grow in your VB programming journey, it’s important to adhere to best practices and helpful tips that can enhance the quality, performance, and maintainability of your code. This section covers essential practices and tips for effective VB programming.

Coding Standards and Naming Conventions

Consistency in coding style and naming conventions is crucial for readability and maintainability.

  • Use Descriptive Names : Choose variable and method names that clearly indicate their purpose. For instance, use totalAmount instead of vague names like temp or x .
  • Follow VB Naming Conventions : For variables and methods, use camelCase (e.g., calculateArea ). For classes and modules, use PascalCase (e.g., EmployeeDetails ).
  • Organize Code Logically : Group related methods together and use regions or separate modules/classes for different functionalities.

Optimizing VB Code for Performance

Writing efficient code ensures your applications run smoothly and responsively.

  • Avoid Unnecessary Loops and Calculations : Repeatedly performing the same calculation or loop can be a drain on performance. Optimize by storing results or rethinking logic.
  • Use StringBuilder for String Concatenation : In cases of extensive string manipulation, StringBuilder is more performance-efficient than using traditional concatenation ( & ).
  • Minimize Use of Global Variables : Excessive use of global variables can make your code harder to debug and maintain. Use local variables where possible.

Commenting and Documentation

Proper documentation is key to making your code understandable to others and your future self.

  • Comment Wisely : Write comments to explain the ‘why’ behind complex logic, not the ‘what’. Avoid stating the obvious.
  • Use XML Comments for Documentation : VB supports XML comments, which can be used to generate technical documentation for your code.

Error Handling and Testing

Robust error handling and thorough testing are essential for reliable applications.

  • Implement Global Error Handling : Besides local Try...Catch blocks, consider using a global error handler in the application framework.
  • Unit Testing : Regularly test individual parts of your code to ensure they work as expected.

Keep Learning and Updating

Technology evolves rapidly, and so do best practices.

  • Stay Updated : Regularly update your skills and knowledge. Follow blogs, forums, and official VB updates.
  • Explore New Features : VB is regularly updated with new features. Experiment with these to see how they can improve your coding.

Congratulations on taking your first steps into the world of Visual Basic programming! Through this journey, you have equipped yourself with the fundamental skills required to create basic applications, understand the essentials of VB programming, and explored advanced concepts to broaden your programming capabilities. Remember, the key to mastering a programming language lies in continuous practice and exploration. Each concept you’ve learned is a building block towards more complex and innovative creations.

As you progress, keep in mind that the field of programming is dynamic and constantly evolving. Staying updated with the latest developments in VB, engaging with the programming community, and continually challenging yourself with new projects will significantly enhance your skills and understanding. Visual Basic, with its simplicity and power, offers a versatile platform for you to unleash your creativity and problem-solving abilities. Happy coding, and may your journey in Visual Basic programming be fulfilling and exciting!

' src=

Angela Mavick

Leave a reply cancel reply.

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

Related Posts

ASP in Healthcare Enhancing Efficiency in Patient Management Systems

ASP in Healthcare: Enhancing Efficiency in Patient Management Systems

ActionScript in Modern Web Development

Exploring the Future of ActionScript in Modern Web Development

ASP and Machine Learning

ASP and Machine Learning Integration

VB6 Tutorial 65: Storing Code in a Module

Visual Basic 6 stores its code in the form of module that makes your program more structured. So its very important to learn about it.

Before you start

Its recommended that you first have the concepts of scope, procedure,functions and multiple forms. The following lessons are helpful.

  • Variable Scopes
  • Sub Procedures
  • Function Procedures
  • Multiple Forms

What is a module?

Module is a separate code file in Visual Basic that contains procedures and declarations.

Your entire VB6 program code does not come line by line. Instead they are separated in code files called modules, each of them is separated into procedures. Thus your VB6 program becomes well structured which is the key part of developing Visual Basic applications.

Form Module

The complete code including procedures and declarations part of a form is referred to as the form module.

Advantages of a BAS module

When the problem is large, its better to break it down into smaller parts to make it easy.

Problem Focus

Each function or sub should be focused on solving one problem, in a reliable and efficient manner. This keeps your bug hunting to a minimum, since your functions are only performing one function.

Once you have a solidly function piece of code, that sub or function can be called from anywhere, with a single reference.

Time & Effort

No more re-writing the same code repeatedly, both in the current project as well as future projects.

Accessibilty

Public procedures can be called from anywhere in your project.

How to add a Standard BAS module to the current project?

Project -> Add Module. Click Project menu from the menu bar and select add module.

Adding a module

A separate project file with the .bas extension is saved on your hard disk as soon as you add a standard module to your current project. You can change the Name property of your newly added standard module from the properties window. Choose a meaningful name, it will benefit you while writing the code.

The modules are shown in project explorer window:

Project Explorer Window

Contents of the standard module

  • Procedure definitions
  • Variable, type and constant declarations.

Contents of a module

The variables and constants declared using the Public keyword in the Declrations section of the module are global, accessible from all parts of your current application.

Here is a simple example of a code inside a BAS module:

Inside the BAS Module

BAS modules are especially useful in large projects. So if you're developing a big VB6 application, make use of them.

About this post

Posted: 2018-06-07 By: vb6boy Viewed: 3,725 times

VB6 Tutorial

Attachments

No attachments for this post

Loading Comments ...

No comments have been added for this post.

You must be logged in to make a comment.

Categories Terms of Use Privacy Policy Disclaimer Contact

18,235,470 visitors as of 2024-07-05 22:09:23

#1 Excel tutorial on the net

VBA ( Visual Basic for Applications ) is the programming language of Excel. If you're an Excel VBA beginner, these 16 chapters are a great way to start. Excel VBA is easy and fun!

1 Create a Macro : With Excel VBA you can automate tasks in Excel by writing so called macros. In this chapter, learn how to create a simple macro.

2 MsgBox : The MsgBox is a dialog box in Excel VBA you can use to inform the users of your program.

3 Workbook and Worksheet Object : Learn more about the Workbook and Worksheet object in Excel VBA.

4 Range Object : The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important object of Excel VBA.

5 Variables : This chapter teaches you how to declare, initialize and display a variable in Excel VBA.

6 If Then Statement : Use the If Then statement in Excel VBA to execute code lines if a specific condition is met.

7 Loop : Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines.

8 Macro Errors : This chapter teaches you how to deal with macro errors in Excel.

9 String Manipulation : In this chapter, you'll find the most important functions to manipulate strings in Excel VBA.

10 Date and Time : Learn how to work with dates and times in Excel VBA.

11 Events : Events are actions performed by users which trigger Excel VBA to execute code.

12 Array : An array is a group of variables. In Excel VBA, you can refer to a specific variable (element) of an array by using the array name and the index number.

13 Function and Sub : In Excel VBA, a function can return a value while a sub cannot.

14 Application Object : The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options.

15 ActiveX Controls : Learn how to create ActiveX controls, such as command buttons, text boxes, list boxes etc.

16 Userform : This chapter teaches you how to create an Excel VBA Userform.

Become an Excel VBA pro! You can find related examples and features on the right side of each chapter at the bottom of each chapter . Below you can find a complete overview.

1 Create a Macro: Swap Values | Run Code from a Module | Macro Recorder | Use Relative References | FormulaR1C1 | Add a Macro to the Toolbar | Enable Macros | Protect Macro

2 MsgBox: MsgBox Function | InputBox Function

3 Workbook and Worksheet Object: Path and FullName | Close and Open | Loop through Books and Sheets | Sales Calculator | Files in a Directory | Import Sheets | Programming Charts

4 Range Object: CurrentRegion | Dynamic Range | Resize | Entire Rows and Columns | Offset | From Active Cell to Last Entry | Union and Intersect | Test a Selection | Font | Background Colors | Sort a Range | Areas Collection | Compare Ranges

5 Variables: Option Explicit | Variable Scope | Life of Variables | Type Mismatch

6 If Then Statement: Logical Operators | Select Case | Tax Rates | Mod Operator | Prime Number Checker | Find Second Highest Value | Sum by Color | Delete Blank Cells

7 Loop: Loop through Defined Range | Loop through Entire Column | Do Until Loop | Step Keyword | Create a Pattern | Sort Numbers | Randomly Sort Data | Remove Duplicates | Complex Calculations | Possible Football Matches | Knapsack Problem

8 Macro Errors: Debugging | Error Handling | Err Object | Interrupt a Macro | Subscript Out of Range | Macro Comments

9 String Manipulation: Separate Strings | Reverse Strings | Convert to Proper Case | Instr | Count Words

10 Date and Time: Compare Dates and Times | DateDiff Function | Weekdays | Delay a Macro | Year Occurrences | Tasks on Schedule | Sort Birthdays | Date Format

11 Events: BeforeDoubleClick Event | Highlight Active Cell | Create a Footer Before Printing | Bills and Coins | Rolling Average Table

12 Array: Dynamic Array | Array Function | Month Names | Size of an Array

13 Function and Sub: User Defined Function | Custom Average Function | Volatile Functions | ByRef and ByVal

14 Application Object: StatusBar | Read Data from Text File | Write Data to Text File | Vlookup

15 ActiveX Controls: Text Box | List Box | Combo Box | Check Box | Option Buttons | Spin Button | Loan Calculator

16 Userform: Userform and Ranges | Currency Converter | Progress Indicator | Multiple List Box Selections | Multicolumn Combo Box | Dependent Combo Boxes | Loop through Controls | Controls Collection | Userform with Multiple Pages | Interactive Userform

Best of VBA +

Discover the most popular pages, each with a short description. Our Excel VBA course used to be $39 but is now completely free. Happy learning.

1 Run Code from a Module : As a beginner to Excel VBA, you might find it difficult to decide where to put your VBA code. This example teaches you how to run code from a module.

2 Macro Recorder : The Macro Recorder, a very useful tool included in Excel VBA, records every task you perform with Excel. All you have to do is record a specific task once. Next, you can execute the task over and over with the click of a button.

3 Add a Macro to the Toolbar : If you use an Excel macro frequently, you can add it to the Quick Access Toolbar. This way you can quickly access your macro.

4 InputBox Function : You can use the InputBox function in Excel VBA to prompt the user to enter a value.

5 Close and Open : The Close and Open Method in Excel VBA can be used to close and open workbooks. Remember, the Workbooks collection contains all the Workbook objects that are currently open.

6 Files in a Directory : Use Excel VBA to loop through all closed workbooks and worksheets in a directory and display all the names.

7 Import Sheets : In this example, we will create a VBA macro that imports sheets from other Excel files into one Excel file.

8 Programming Charts : Use Excel VBA to create two programs. One program loops through all charts on a sheet and changes each chart to a pie chart. The other program changes some properties of the first chart.

9 CurrentRegion : You can use the CurrentRegion property in Excel VBA to return the range bounded by any combination of blank rows and blank columns.

10 Entire Rows and Columns : This example teaches you how to select entire rows and columns in Excel VBA. Are you ready?

11 Offset : The Offset property in Excel VBA takes the range which is a particular number of rows and columns away from a certain range.

12 From Active Cell to Last Entry : This example illustrates the End property of the Range object in Excel VBA. We will use this property to select the range from the Active Cell to the last entry in a column.

13 Background Colors : Changing background colors in Excel VBA is easy. Use the Interior property to return an Interior object. Then use the ColorIndex property of the Interior object to set the background color of a cell.

14 Compare Ranges : Learn how to create a program in Excel VBA that compares randomly selected ranges and highlights cells that are unique.

15 Option Explicit : We strongly recommend to use Option Explicit at the start of your Excel VBA code. Using Option Explicit forces you to declare all your variables.

16 Logical Operators : The three most used logical operators in Excel VBA are: And, Or and Not. As always, we will use easy examples to make things more clear.

17 Select Case : Instead of multiple If Then statements in Excel VBA, you can use the Select Case structure.

18 Mod Operator : The Mod operator in Excel VBA gives the remainder of a division.

19 Delete Blank Cells : In this example, we will create a VBA macro that deletes blank cells. First, we declare two variables of type Integer.

20 Loop through Defined Range : Use Excel VBA to loop through a defined range. For example, when we want to square the numbers in the range A1:A3.

21 Do Until Loop : VBA code placed between Do Until and Loop will be repeated until the part after Do Until is true.

22 Sort Numbers : In this example, we will create a VBA macro that sorts numbers. First, we declare three variables of type Integer and one Range object.

23 Remove Duplicates : Use Excel VBA to remove duplicates. In column A we have 10 numbers. We want to remove the duplicates from these numbers and place the unique numbers in column B.

24 Debugging : This example teaches you how to debug code in Excel VBA.

25 Error Handling : Use Excel VBA to create two programs. One program simply ignores errors. The other program continues execution at a specified line upon hitting an error.

26 Subscript Out of Range : The 'subscript out of range' error in Excel VBA occurs when you refer to a nonexistent collection member or a nonexistent array element.

27 Separate Strings : Let's create a program in Excel VBA that separates strings. Place a command button on your worksheet and add the following code lines.

28 Instr : Use Instr in Excel VBA to find the position of a substring in a string. The Instr function is quite versatile.

29 Compare Dates and Times : This example teaches you how to compare dates and times in Excel VBA.

30 DateDiff Function : The DateDiff function in Excel VBA can be used to get the number of days, weeks, months or years between two dates.

31 Highlight Active Cell : Learn how to create a program in Excel VBA that highlights the row and column of the Active Cell (selected cell). This program will amaze and impress your boss.

32 Dynamic Array : If the size of your array increases and you don't want to fix the size of the array, you can use the ReDim keyword. Excel VBA then changes the size of the array automatically.

33 User Defined Function : Excel has a large collection of functions. In most situations, those functions are sufficient to get the job done. If not, you can use Excel VBA to create your own function.

34 Read Data from Text File : Use Excel VBA to read data from a text file. This file contains some geographical coordinates we want to import into Excel.

35 Vlookup : Use the WorksheetFunction property in Excel VBA to access the VLOOKUP function. All you need is a single code line.

36 List Box : Use Excel VBA to place a list box on your worksheet. A list box is a list from where a user can select an item.

37 Check Box : A check box is a field which can be checked to store information. To create a check box in Excel VBA, execute the following steps.

38 Loan Calculator : This page teaches you how to create a simple loan calculator in Excel VBA. The worksheet contains the following ActiveX controls: two scrollbars and two option buttons.

39 Currency Converter : Use Excel VBA to create a Userform that converts any amount from one currency into another.

40 Progress Indicator : Learn how to create a progress indicator in Excel VBA. We've kept the progress indicator as simple as possible, yet it looks professional. Are you ready?

Browse all Excel VBA examples .

  • Create a Macro
  • Workbook and Worksheet Object
  • Range Object
  • If Then Statement
  • Macro Errors
  • String Manipulation
  • Date and Time
  • Function and Sub
  • Application Object
  • ActiveX Controls

Follow Excel Easy

Excel Easy on Facebook

VBA • © 2010-2024 Excel is Awesome, we'll show you: Introduction • Basics • Functions • Data Analysis • VBA

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Getting started with VBA in Office

  • 10 contributors

Are you facing a repetitive clean up of fifty tables in Word? Do you want a particular document to prompt the user for input when it opens? Are you having difficulty figuring out how to get your contacts from Microsoft Outlook into a Microsoft Excel spreadsheet efficiently?

You can perform these tasks and accomplish a great deal more by using Visual Basic for Applications (VBA) for Office—a simple, but powerful programming language that you can use to extend Office applications.

This article is for experienced Office users who want to learn about VBA and who want some insight into how programming can help them to customize Office.

The Office suite of applications has a rich set of features. There are many different ways to author, format, and manipulate documents, email, databases, forms, spreadsheets, and presentations. The great power of VBA programming in Office is that nearly every operation that you can perform with a mouse, keyboard, or a dialog box can also be done by using VBA. Further, if it can be done once with VBA, it can be done just as easily a hundred times. (In fact, the automation of repetitive tasks is one of the most common uses of VBA in Office.)

Beyond the power of scripting VBA to accelerate every-day tasks, you can use VBA to add new functionality to Office applications or to prompt and interact with the user of your documents in ways that are specific to your business needs. For example, you could write some VBA code that displays a pop up message that reminds users to save a document to a particular network drive the first time they try to save it.

This article explores some of the primary reasons to leverage the power of VBA programming. It explores the VBA language and the out-of-the-box tools that you can use to work with your solutions. Finally, it includes some tips and ways to avoid some common programming frustrations and missteps.

Interested in developing solutions that extend the Office experience across multiple platforms ? Check out the new Office Add-ins model . Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.

When to use VBA and why

There are several principal reasons to consider VBA programming in Office.

Automation and repetition

VBA is effective and efficient when it comes to repetitive solutions to formatting or correction problems. For example, have you ever changed the style of the paragraph at the top of each page in Word? Have you ever had to reformat multiple tables that were pasted from Excel into a Word document or an Outlook email? Have you ever had to make the same change in multiple Outlook contacts?

If you have a change that you have to make more than ten or twenty times, it may be worth automating it with VBA. If it is a change that you have to do hundreds of times, it certainly is worth considering. Almost any formatting or editing change that you can do by hand, can be done in VBA.

Extensions to user interaction

There are times when you want to encourage or compel users to interact with the Office application or document in a particular way that is not part of the standard application. For example, you might want to prompt users to take some particular action when they open, save, or print a document.

Interaction between Office applications

Do you need to copy all of your contacts from Outlook to Word and then format them in some particular way? Or, do you need to move data from Excel to a set of PowerPoint slides? Sometimes simple copy and paste does not do what you want it to do, or it is too slow. Use VBA programming to interact with the details of two or more Office applications at the same time and then modify the content in one application based on the content in another.

Doing things another way

VBA programming is a powerful solution, but it is not always the optimal approach. Sometimes it makes sense to use other ways to achieve your aims.

The critical question to ask is whether there is an easier way. Before you begin a VBA project, consider the built-in tools and standard functionalities. For example, if you have a time-consuming editing or layout task, consider using styles or accelerator keys to solve the problem. Can you perform the task once and then use CTRL+Y (Redo) to repeat it? Can you create a new document with the correct format or template, and then copy the content into that new document?

Office applications are powerful; the solution that you need may already be there. Take some time to learn more about Office before you jump into programming.

Before you begin a VBA project, ensure that you have the time to work with VBA. Programming requires focus and can be unpredictable. Especially as a beginner, never turn to programming unless you have time to work carefully. Trying to write a "quick script" to solve a problem when a deadline looms can result in a very stressful situation. If you are in a rush, you might want to use conventional methods, even if they are monotonous and repetitive.

VBA Programming 101

Using code to make applications do things.

You might think that writing code is mysterious or difficult, but the basic principles use every-day reasoning and are quite accessible. Microsoft Office applications are created in such a way that they expose things called objects that can receive instructions, in much the same way that a phone is designed with buttons that you use to interact with the phone. When you press a button, the phone recognizes the instruction and includes the corresponding number in the sequence that you are dialing. In programming, you interact with the application by sending instructions to various objects in the application. These objects are expansive, but they have their limits. They can only do what they are designed to do, and they will only do what you instruct them to do.

For example, consider the user who opens a document in Word, makes a few changes, saves the document, and then closes it. In the world of VBA programming, Word exposes a Document object. By using VBA code, you can instruct the Document object to do things such as Open, Save, or Close.

The following section discusses how objects are organized and described.

The Object Model

Developers organize programming objects in a hierarchy, and that hierarchy is called the object model of the application. Word, for example, has a top-level Application object that contains a Document object. The Document object contains Paragraph objects and so on. Object models roughly mirror what you see in the user interface. They are a conceptual map of the application and its capabilities.

The definition of an object is called a class, so you might see these two terms used interchangeably. Technically, a class is the description or template that is used to create, or instantiate, an object.

Once an object exists, you can manipulate it by setting its properties and calling its methods. If you think of the object as a noun, the properties are the adjectives that describe the noun and the methods are the verbs that animate the noun. Changing a property changes some quality of appearance or behavior of the object. Calling one of the object methods causes the object to perform some action.

The VBA code in this article runs against an open Office application where many of the objects that the code manipulates are already up and running; for example, the Application itself, the Worksheet in Excel, the Document in Word, the Presentation in PowerPoint, the Explorer and Folder objects in Outlook. Once you know the basic layout of the object model and some key properties of the Application that give access to its current state, you can start to extend and manipulate that Office application with VBA in Office.

In Word, for example, you can change the properties and invoke the methods of the current Word document by using the ActiveDocument property of the Application object. This ActiveDocument property returns a reference to the Document object that is currently active in the Word application. "Returns a reference to" means "gives you access to."

The following code does exactly what it says; that is, it saves the active document in the application.

Read the code from left to right, "In this Application, with the Document referenced by ActiveDocument, invoke the Save method." Be aware that Save is the simplest form of method; it does not require any detailed instructions from you. You instruct a Document object to Save and it does not require any more input from you.

If a method requires more information, those details are called parameters. The following code runs the SaveAs method, which requires a new name for the file.

Values listed in parentheses after a method name are the parameters. Here, the new name for the file is a parameter for the SaveAs method.

You use the same syntax to set a property that you use to read a property. The following code executes a method to select cell A1 in Excel and then to set a property to put something in that cell.

The first challenge in VBA programming is to get a feeling for the object model of each Office application and to read the object, method, and property syntax. The object models are similar in all Office applications, but each is specific to the kind of documents and objects that it manipulates.

In the first line of the code snippet, there is the Application object, Excel this time, and then the ActiveSheet , which provides access to the active worksheet. After that is a term not as familiar, Range, which means "define a range of cells in this way." The code instructs Range to create itself with just A1 as its defined set of cells. In other words, the first line of code defines an object, the Range, and runs a method against it to select it. The result is automatically stored in another property of the Application called Selection .

The second line of code sets the Value property of Selection to the text "Hello World", and that value appears in cell A1.

The simplest VBA code that you write might simply gain access to objects in the Office application that you are working with and set properties. For example, you could get access to the rows in a table in Word and change their formatting in your VBA script.

That sounds simple, but it can be incredibly useful; once you can write that code, you can harness all of the power of programming to make those same changes in several tables or documents, or make them according to some logic or condition. For a computer, making 1000 changes is no different from making 10, so there is an economy of scale here with larger documents and problems, and that is where VBA can really shine and save you time.

Macros and the Visual Basic Editor

Now that you know something about how Office applications expose their object models, you are probably eager to try calling object methods, setting object properties, and responding to object events. To do so, you must write your code in a place and in a way that Office can understand; typically, by using the Visual Basic Editor. Although it is installed by default, many users don't know that it is even available until it is enabled on the ribbon.

All Office applications use the ribbon. One tab on the ribbon is the Developer tab, where you access the Visual Basic Editor and other developer tools. Because Office does not display the Developer tab by default, you must enable it by using the following procedure:

To enable the Developer tab

On the File tab, choose Options to open the Options dialog box.

Choose Customize Ribbon on the left side of the dialog box.

Under Choose commands from on the left side of the dialog box, select Popular Commands .

Under Customize the Ribbon on the right side of the dialog box, select Main Tabs in the drop down list box, and then select the Developer checkbox.

Choose OK .

In Office 2007, you displayed the Developer tab by choosing the Office button, choosing Options , and then selecting the Show Developer tab in Ribbon check box in the Popular category of the Options dialog box.

After you enable the Developer tab, it is easy to find the Visual Basic and Macros buttons.

Figure 1. Buttons on the Developer tab

Buttons on the Developer tab

Security issues

To protect Office users against viruses and dangerous macro code, you cannot save macro code in a standard Office document that uses a standard file extension. Instead, you must save the code in a file with a special extension. For example you cannot save macros in a standard Word document with a .docx extension; instead, you must use a special Word Macro-Enabled Document with a .docm extension.

When you open a .docm file, Office security might still prevent the macros in the document from running, with or without telling you. Examine the settings and options in the Trust Center on all Office applications. The default setting disables macro from running, but warns you that macros have been disabled and gives you the option to turn them back on for that document.

You can designate specific folders where macros can run by creating Trusted Locations, Trusted Documents, or Trusted Publishers. The most portable option is to use Trusted Publishers, which works with digitally signed documents that you distribute. For more information about the security settings in a particular Office application, open the Options dialog box, choose Trust Center , and then choose Trust Center Settings .

Some Office applications, like Outlook, save macros by default in a master template on your local computer. Although that strategy reduces the local security issues on your own computer when you run your own macros, it requires a deployment strategy if you want to distribute your macro.

Recording a macro

When you choose the Macro button on the Developer tab, it opens the Macros dialog box, which gives you access to VBA subroutines or macros that you can access from a particular document or application. The Visual Basic button opens the Visual Basic Editor, where you create and edit VBA code.

Another button on the Developer tab in Word and Excel is the Record Macro button, which automatically generates VBA code that can reproduce the actions that you perform in the application. Record Macro is a terrific tool that you can use to learn more about VBA. Reading the generated code can give you insight into VBA and provide a stable bridge between your knowledge of Office as a user and your knowledge as a programmer. The only caveat is that the generated code can be confusing because the Macro editor must make some assumptions about your intentions, and those assumptions are not necessarily accurate.

To record a macro

Open Excel to a new Workbook and choose the Developer tab in the ribbon. Choose Record Macro and accept all of the default settings in the Record Macro dialog box, including Macro1 as the name of the macro and This Workbook as the location.

Choose OK to begin recording the macro. Note how the button text changes to Stop Recording . Choose that button the instant you complete the actions that you want to record.

Choose cell B1 and type the programmer's classic first string: Hello World. Stop typing and look at the Stop Recording button; it is grayed out because Excel is waiting for you to finish typing the value in the cell.

Choose cell B2 to complete the action in cell B1, and then choose Stop Recording .

Choose Macros on the Developer tab, select Macro1 if it is not selected, and then choose Edit to view the code from Macro1 in the Visual Basic Editor.

Figure 2. Macro code in Visual Basic Editor

Macro code in Visual Basic Editor

Looking at the code

The macro that you created should look similar to the following code.

Be aware of the similarities to the earlier code snippet that selected text in cell A1, and the differences. In this code, cell B1 is selected, and then the string "Hello World" is applied to the cell that has been made active. The quotes around the text specify a string value as opposed to a numeric value.

Remember how you chose cell B2 to display the Stop Recording button again? That action shows up as a line of code as well. The macro recorder records every keystroke.

The lines of code that start with an apostrophe and colored green by the editor are comments that explain the code or remind you and other programmers the purpose of the code. VBA ignores any line, or portion of a line, that begins with a single quote. Writing clear and appropriate comments in your code is an important topic, but that discussion is out of the scope of this article. Subsequent references to this code in the article don't include those four comment lines.

When the macro recorder generates the code, it uses a complex algorithm to determine the methods and the properties that you intended. If you don't recognize a given property, there are many resources available to help you. For example, in the macro that you recorded, the macro recorder generated code that refers to the FormulaR1C1 property. Not sure what that means?

Be aware that Application object is implied in all VBA macros. The code that you recorded works with Application. at the beginning of each line.

Using Developer Help

Select FormulaR1C1 in the recorded macro and press F1. The Help system runs a quick search, determines that the appropriate subjects are in the Excel Developer section of the Excel Help, and lists the FormulaR1C1 property. You can choose the link to read more about the property, but before you do, be aware of the Excel Object Model Reference link near the bottom of the window. Choose the link to view a long list of objects that Excel uses in its object model to describe the Worksheets and their components.

Choose any one of those to see the properties and methods that apply to that particular object, along with cross references to different related options. Many Help entries also have brief code examples that can help you. For example, you can follow the links in the Borders object to see how to set a border in VBA.

Editing the code

The Borders code looks different from the recorded macro. One thing that can be confusing with an object model is that there is more than one way to address any given object, cell A1 in this example.

Sometimes the best way to learn programming is to make minor changes to some working code and see what happens as a result. Try it now. Open Macro1 in the Visual Basic Editor and change the code to the following.

Use Copy and Paste as much as possible when working with code to avoid typing errors.

You don't need to save the code to try it out, so return to the Excel document, choose Macros on the Developer tab, choose Macro1 , and then choose Run . Cell A1 now contains the text Wow! and has a double-line border around it.

Figure 3. Results of your first macro

Results of your first macro

You just combined macro recording, reading the object model documentation, and simple programming to make a VBA program that does something. Congratulations!

Did not work? Read on for debugging suggestions in VBA.

Programming tips and tricks

Start with examples.

The VBA community is very large; a search on the Web can almost always yield an example of VBA code that does something similar to what you want to do. If you cannot find a good example, try to break the task down into smaller units and search on each of those, or try to think of a more common, but similar problem. Starting with an example can save you hours of time.

That does not mean that free and well-thought-out code is on the Web waiting for you to come along. In fact, some of the code that you find might have bugs or mistakes. The idea is that the examples you find online or in VBA documentation give you a head start. Remember that learning programming requires time and thought. Before you get in a big rush to use another solution to solve your problem, ask yourself whether VBA is the right choice for this problem.

Make a simpler problem

Programming can get complex quickly. It's critical, especially as a beginner, that you break the problem down to the smallest possible logical units, then write and test each piece in isolation. If you have too much code in front of you and you get confused or muddled, stop and set the problem aside. When you come back to the problem, copy out a small piece of the problem into a new module, solve that piece, get the code working, and test it to ensure that it works. Then move on to the next part.

Bugs and debugging

There are two main types of programming errors: syntax errors, which violate the grammatical rules of the programming language, and run-time errors, which look syntactically correct, but fail when VBA attempts to execute the code.

Although they can be frustrating to fix, syntax errors are easy to catch; the Visual Basic Editor beeps and flashes at you if you type a syntax error in your code.

For example, string values must be surrounded by double quotes in VBA. To find out what happens when you use single quotes instead, return to the Visual Basic Editor and replace the "Wow!" string in the code example with 'Wow!' (that is, the word Wow enclosed in single quotes). If you choose the next line, the Visual Basic Editor reacts. The error "Compile error: Expected: expression" is not that helpful, but the line that generates the error turns red to tell you that you have a syntax error in that line and as a result, this program will not run.

Choose OK and change the text back to"Wow!".

Runtime errors are harder to catch because the programming syntax looks correct, but the code fails when VBA tries to execute it.

For example, open the Visual Basic Editor and change the Value property name to ValueX in your Macro, deliberately introducing a runtime error since the Range object does not have a property called ValueX. Go back to the Excel document, open the Macros dialog box and run Macro1 again. You should see a Visual Basic message box that explains the run-time error with the text, "Object doesn't support this property of method." Although that text is clear, choose Debug to find out more.

When you return to the Visual Basic Editor, it is in a special debug mode that uses a yellow highlight to show you the line of code that failed. As expected, the line that includes the ValueX property is highlighted.

You can make changes to VBA code that is running, so change ValueX back to Value and choose the little green play button underneath the Debug menu. The program should run normally again.

It's a good idea to learn how to use the debugger more deliberately for longer, more complex programs. At a minimum, learn a how to set break-points to stop execution at a point where you want to take a look at the code, how to add watches to see the values of different variables and properties as the code runs, and how to step through the code line by line. These options are all available in the Debug menu and serious debugger users typically memorize the accompanying keyboard shortcuts.

Using reference materials well

To open the Developer Reference that is built into Office Help, open the Help reference from any Office application by choosing the question mark in the ribbon or by pressing F1. Then, to the right of the Search button, choose the dropdown arrow to filter the contents. Choose Developer Reference . If you don't see the table of contents in the left panel, choose the little book icon to open it, and then expand the Object Model Reference from there.

Figure 5. Filtering on developer Help applies to all Office applications

Filtering on developer Help applies to all Office applications

Time spent browsing the Object Model reference pays off. After you understand the basics of VBA syntax and the object model for the Office application that you are working with, you advance from guesswork to methodical programming.

Of course the Microsoft Office Developer Center is an excellent portal for articles, tips, and community information.

Searching forums and groups

All programmers get stuck sometimes, even after reading every reference article they can find and losing sleep at night thinking about different ways to solve a problem. Fortunately, the Internet has fostered a community of developers who help each other solve programming problems.

Any search on the Web for "office developer forum" reveals several discussion groups. You can search on "office development" or a description of your problem to discover forums, blog posts, and articles as well.

If you have done everything that you can to solve a problem, don't be afraid to post your question to a developers forum. These forums welcome posts from newer programmers and many of the experienced developers are glad to help.

The following are a few points of etiquette to follow when you post to a developer forum:

Before you post, look on the site for an FAQ or for guidelines that members want you to follow. Ensure that you post content that is consistent with those guidelines and in the correct section of the forum.

Include a clear and complete code sample, and consider editing your code to clarify it for others if it is part of a longer section of code.

Describe your problem clearly and concisely, and summarize any steps that you have taken to solve the problem. Take the time to write your post as well as you can, especially if you are flustered or in a hurry. Present the situation in a way that will make sense to readers the first time that they read the problem statement.

Be polite and express your appreciation.

Going further with programming

Although this article is short and only scratches the surface of VBA and programming, it is hopefully enough to get you started.

This section briefly discusses a few more key topics.

In the simple examples in this article you manipulated objects that the application had already created. You might want to create your own objects to store values or references to other objects for temporary use in your application. These are called variables.

To use a variable in VBA, must tell VBA which type of object the variable represents by using the Dim statement. You then set its value and use it to set other variables or properties.

Branching and looping

The simple programs in this article execute one line at a time, from the top down. The real power in programming comes from the options that you have to determine which lines of code to execute, based on one or more conditions that you specify. You can extend those capabilities even further when you can repeat an operation many times. For example, the following code extends Macro1.

Type or paste the code into the Visual Basic Editor and then run it. Follow the directions in the message box that appears and change the text in cell A1 from Wow! to Yes! and run it again to see the power of looping. This code snippet demonstrates variables, branching and looping. Read it carefully after you see it in action and try to determine what happens as each line executes.

All of my Office applications: example code

Here are a few scripts to try; each solves a real-world Office problem.

Create an email in Outlook

Be aware that there are situations in which you might want to automate email in Outlook; you can use templates as well.

Delete empty rows in an Excel worksheet

Be aware that you can select a column of cells and run this macro to delete all rows in the selected column that have a blank cell.

Delete empty text boxes in PowerPoint

Be aware that this code loops through all of the slides and deletes all text boxes that don't have any text. The count variable decrements instead of increments because each time the code deletes an object, it removes that object from the collection, which reduces the count.

Copy a contact from Outlook to Word

Be aware that this code copies the currently open contact in Outlook into the open Word document. This code only works if there is a contact currently open for inspection in Outlook.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

IMAGES

  1. Visual Basic Tutorial 13

    visual basic modules tutorial

  2. Visual Basic Tutorial #8

    visual basic modules tutorial

  3. Modules

    visual basic modules tutorial

  4. 43 VISUAL BASIC Tutorial ( Modules )

    visual basic modules tutorial

  5. Visual Basic (VB) Tutorial

    visual basic modules tutorial

  6. Visual Basic Tutorial

    visual basic modules tutorial

VIDEO

  1. Understanding class modules in Visual basic 6

  2. Программирование на Visual Basic с нуля #2

  3. Visual Basic #18

  4. Class 7 Creating Visual Basic Modules and Procedures

  5. An Introduction to VBA Class Modules

  6. Chapter 1 Complete In One Video Visual Basic Programming

COMMENTS

  1. Modules

    Visual Basic provides several modules that enable you to simplify common tasks in your code, including manipulating strings, performing mathematical calculations, getting system information, performing file and directory operations, and so on. The following table lists the modules provided by Visual Basic. Expand table. Module. Description.

  2. Modules

    To do this, click on the Solution in the Solution Explorer, right click on the form and click Properties as shown below. You should now see the the Project Properties dialog. In the Startup Form dropdown select Form2. Go back to your project and Add a New Module called ModuleFormula. Double click on your Module to add code.

  3. Module Statement

    For more information, see Access levels in Visual Basic. All members of a module are implicitly Shared. Classes and Modules. These elements have many similarities, but there are some important differences as well. Terminology. Previous versions of Visual Basic recognize two types of modules: class modules (.cls files) and standard modules (.bas

  4. Visual Basic Tutorial 13

    http://www.programminghelp.org/Watch in 720pYou will learn how to create a module and use it to store a procedure or a function.

  5. VB.NET Tutorial 55

    In this tutorial, we cover how to create a module, store information inside a module and then access that information in your application. A module can be us...

  6. Classes vs. Modules in VB.NET

    Modules are VB counterparts to C# static classes. When your class is designed solely for helper functions and extension methods and you don't want to allow inheritance and instantiation, you use a Module.. By the way, using Module is not really subjective and it's not deprecated.Indeed you must use a Module when it's appropriate. .NET Framework itself does it many times (System.Linq.Enumerable ...

  7. Get started coding with Visual Basic (VB)

    Create a new code file. Start by creating a new file and adding some code to it. Open Visual Studio. Press Esc or click Continue without code on the start window to open the development environment. From the File menu on the menu bar, choose New File. In the New File dialog box, under the General category, choose Visual Basic Class, and then ...

  8. Organizing code in Visual Basic

    Visual Basic statements are organized into blocks, modules, classes and namespaces. This helps to make the code more maintainable and robust. Correct code organization prevents from making errors in the code. The basic building blocks of a Visual Basic program are: Assembly. Namespace.

  9. An Introduction to Classes

    In our last example we used a Visual Basic class, the Visual Basic Collection Class. By using this class, you are already familiar with the concepts of what we will do here. Microsoft, by providing us with the Collection Class, have provided us with a means to store, retrieve and manipulate data in one well named unit. A Class Module.

  10. Modules, Classes and Namespaces in VB.NET

    speak. An object is the most basic entity containing executable code, data, or both. VB.NET adds the concept of a Module to the mix mainly because it's been there for a very long time. In reality, a VB.NET module is something similar to a C# static class. For now, it's probably sufficient to view a module as a way to group a similar set of ...

  11. VB.NET

    A module is similar to a class, but there are some important differences as well. Previous versions of Visual Basic recognize two types of modules: class modules (.cls files) and standard modules (.bas files). The current version calls these classes and modules, respectively. You can control whether a member of a class is a shared or instance ...

  12. Welcome to the web's newest Visual Basic Tutorial

    Welcome! Thank you for visiting VisualBasicTutorial.NET, the newest Visual Basic tutorials on the web. I'm of the firm belief that everyone can program. If you're sitting there thinking, "not me" - it's time to think again. You can program.

  13. Tutorial: Create simple Visual Basic console apps

    In the start window, choose Create a new project. In the Create a new project window, choose Visual Basic from the Language list. Next, choose Windows from the Platform list and Console from the project types list. After you apply the language, platform, and project type filters, choose the Console Application template, and then choose Next.

  14. PDF Learning to Program With Visual Basic and .net Gadgeteer

    2 Learning to Program with Visual Basic and .NET Gadgeteer FOREWORD Computer programming can be fun! This book on Visual Basic and .NET Gadgeteer shows how. Aimed at high school students and first-time programmers, the authors use a combination of hardware and software to make programming come alive - audibly, visually, and tangibly.

  15. VBA Class Module Tutorial & Examples

    This tutorial will teach you about Class Modules in VBA. You'll learn what they are and how to use them. VBA Class Modules - Introduction. When you insert modules into the Visual Basic Editor (VBE) in order to enter your code, you may have noticed that you can also insert what is called a 'Class Module'. Class Modules vs. Modules

  16. Beginner's Guide to Visual Basic

    In Visual Studio, go to "File" > "New" > "Project". In the "Create a new project" window, search for "Visual Basic", and select "Windows Forms App (.NET Framework)". Click "Next". Give your project a name and location. Set other details like the solution name and whether to place the project in a directory.

  17. VB6 Tutorial 65: Storing Code in a Module

    Instead they are separated in code files called modules, each of them is separated into procedures. Thus your VB6 program becomes well structured which is the key part of developing Visual Basic applications. Form Module. The complete code including procedures and declarations part of a form is referred to as the form module. Advantages of a ...

  18. Introduction to Visual Basic Programming

    There are 4 modules in this course. This course is the second course in a series that aims to prepare you for a role working as a programmer. In this course, you will be introduced to the five main concepts in procedural programming: user input, console output, variable declaration and assignment, decision branching and iteration.

  19. Visual Basic docs

    Visual Basic documentation. Visual Basic is an object-oriented programming language developed by Microsoft. Using Visual Basic makes it fast and easy to create type-safe .NET apps.

  20. VBA in Excel (In Simple Steps)

    VBA (Visual Basic for Applications) is the programming language of Excel.If you're an Excel VBA beginner, these 16 chapters are a great way to start. Excel VBA is easy and fun!. 1 Create a Macro: With Excel VBA you can automate tasks in Excel by writing so called macros.In this chapter, learn how to create a simple macro.

  21. Visual Basic Fundamentals for Absolute Beginners: (01) Series

    Full course outline: Mod 01: Series Introduction. Mod 02: Installing Visual Studio Express 2013 for Windows Desktop. Mod 03: Creating Your First Visual Basic Program. Mod 04: Dissecting the First Visual Basic Program You Created. Mod 05: Quick Overview of the Visual Basic Express Edition IDE. Mod 06: Declaring Variables and Assigning Values.

  22. Excel VBA

    Learn VBA Macros Beginner to Advanced and Automate your Tasks, Reports, Dashboards and Analysis with Visual Basic Macros. ... Our platform offers a diverse range of courses and tutorials, empowering learners to acquire and enhance their technical skills. With a flexible, self-paced learning environment and expert instructors, we ensure that ...

  23. Getting started with VBA in Office

    Recording a macro. When you choose the Macro button on the Developer tab, it opens the Macros dialog box, which gives you access to VBA subroutines or macros that you can access from a particular document or application. The Visual Basic button opens the Visual Basic Editor, where you create and edit VBA code.. Another button on the Developer tab in Word and Excel is the Record Macro button ...