Programming Excel with VBA and .NET : Knowing the Basics - Classes and Modules

1/30/2011 10:29:00 AM
Excel stores recorded code in modules and stores code associated with workbooks and worksheets in classes . Here's why:
Modules are static

That is, they exist without having to be created in memory at runtime. That means the code in modules is always available to Excel; however, it limits what type of code they can contain. Specifically, modules can't contain event procedures.

Classes are dynamic

They must be instantiated at runtime (that means an instance of an object must be created from the class at runtime). Classes can contain event procedures because of this relationship with an object.

Workbook, chart, and worksheet classes are automatically instantiated by Excel because those classes are associated with visible Excel objects: the current workbook and each of the sheets it contains. Because of that relationship, Visual Basic shows those classes as Microsoft Excel Objects in the Project window (Figure 1).

Figure 1. Excel Visual Basic projects store code in three different folders

Because Excel instantiates classes automatically, how you create objects from classes is mostly hidden and therefore often not completely understood. To see how creating an object from a class works, create a new workbook in Excel, start the Visual Basic Editor, and follow these steps:

  1. Choose Insert → Class Module. Visual Basic creates a new class and adds it to the Class Modules folder of the Project window.

  2. Select the Properties window and rename the class PublicClass and set the Instancing property to 2 - PublicNotCreatable, as shown in Figure 2.

Figure 2. Create a user class

Click on the class's Code window and type the following code:

    ' From PublicClass class
Dim m_name As String

Public Sub SetName(name As String)
m_name = name
End Sub

Public Sub ShowName( )
Debug.Print m_name
End Sub

Now move the cursor to the ShowName procedure and press F5. Excel doesn't run the procedure; instead, it displays the Macros dialog, and it's empty! (See Figure 3.)

Figure 3. Procedures in user classes don't show up here because they don't have an instance

In order to run the ShowName procedure, you need to choose Insert → Module and add the following code:

    ' From Tests module.
Sub TestUserClass( )
Dim obj As New PublicClass
obj.SetName ("New object")
End Sub

TestUserClass creates an instance of PublicClass as a new object named obj, then calls the SetName and ShowName methods of that object. In short, you can't do anything with a class until you create an object from it. Why is that useful? Because each object has its own storage. For example, you can create three different objects from the same class if you like:

    ' From Tests module.
Sub MultipleObjects( )
Dim obj1 As New PublicClass, obj2 As New PublicClass, obj3 As New PublicClass
obj1.SetName ("First object")
obj2.SetName ("Second object")
obj3.SetName ("Third object")
End Sub

The preceding code displays each object's name in the Immediate window:
    First object
Second object
Third object

You can't do that with code stored in a module because the m_name variable changes each time you call SetName. With modules, you have only one instance, and you can't create that instance:

    ' From Tests module.
Sub MultipleModules( )
' Code in modules is static, there's no such thing as:
'Dim mod As New PublicProcedures
PublicProcedures.SetName ("First object")
PublicProcedures.SetName ("Second object")
PublicProcedures.SetName ("Third object")
' All display "Third object"
End Sub

Most programmers omit the module name when calling procedures from a module, but you can include it if you like, as shown by this example. The module name is required only if there is a procedure with the same name in two or more modules.

The classes that Excel provides for sheets and workbooks are single-instance classes . That means they follow some special rules that are different from user classes. You can create multiple variables that refer to a single-instance class, but all those variables refer to the same object. For example, the following code creates object variables that refer to the same worksheet:

    ' From Tests module
Sub TestSheetClass( )
Dim obj1 As New Sheet1, obj2 As New Sheet1
obj1.Name = "New name"
Debug.Print obj2.Name
End Sub

When you run the preceding code, Debug.Print displays New Name in the Immediate window! This limitation comes from Excel—you can't have two sheets with the same name in a single Excel workbook, and you can't have two workbooks with the same name open at the same time in Excel.

Since Excel handles the creation of workbook and sheet classes, the New keyword in the preceding code is misleading: you can't really create new instances of those classes. However, you can create new instances of generic versions of those objects using Excel's Add method:

    ' Creates a new, blank worksheet
Sub NewWorksheet( )
Dim ws As Worksheet
Set ws = Worksheets.Add
ws.Name = "New sheet"
End Sub

One other quirk of workbook and sheet classes is that you can run their procedures from the Code window by pressing F5. You don't need to first create an instance of those classes—Excel's already done it.

  •  Windows 7 : Indexing Your Computer for Faster Searches (part 3) - Optimizing File Properties for Indexing
  •  Windows 7 : Indexing Your Computer for Faster Searches (part 2) - Specifying Files Types to Include or Exclude
  •  Windows 7 : Indexing Your Computer for Faster Searches (part 1) - Adding or Removing Indexed Locations
  •  Windows Server 2008 : Designing Organizational Unit and Group Structure - Exploring Sample Design Models
  •  Windows Server 2008 : Designing Organizational Unit and Group Structure - Understanding Group Design
  •  Windows Server 2008 : Designing Organizational Unit and Group Structure - Group Policies and OU Design
  •  Windows 7 : Searching Your Computer (part 2) - Search Filters
  •  Windows 7 : Searching Your Computer (part 1)
  •  Windows 7 : Putting Windows Explorer to Work for You
  •  Windows Server 2008: Using OUs to Delegate Administration
  •  Windows Server 2008: Defining AD Groups
  •  Windows Server 2008: Defining Organizational Units in AD DS
  •  Windows 7 :Navigating Your Computer with the Address Bar (part 2) - Using Selected Paths to Quickly Navigate Your Computer
  •  Windows 7 :Navigating Your Computer with the Address Bar (part 1) - Accessing Locations on Your Computer
  •  Windows 7 : Exploring and Searching Your Computer - Exploring Your Documents
  •  Designing a Windows Server 2008 R2 Active Directory : Renaming an AD DS Domain
  •  Designing a Windows Server 2008 R2 Active Directory : Understanding the Placeholder Domain Model
  •  Designing a Windows Server 2008 R2 Active Directory : Understanding the Empty-Root Domain Model
  •  Windows 7: Managing Software Once It’s Installed (part 3) - Viewing and Managing Startup Programs
  •  Windows 7: Managing Software Once It’s Installed (part 2) - Viewing and Managing Currently Running Programs
    Most View
    Nvidia GeForce GTX 760 2 GB Graphic Card Review (Part 2)
    In-Ear Headphones - Top Up Your Mobile
    Windows 7 : Managing Pictures with Windows Live Photo Gallery (part 8) - Importing Images from a Digital Camera or Memory Card
    ASP.NET 4 in VB 2010 : The Data Controls - The DetailsView and FormView
    Denon AVR 4520 - Stairway To AV Heaven (Part 2)
    Windows Small Business Server 2011 : Installing the Second Server (part 4) - Enable Updates and Feedback
    The Secret Processor Revolution (Part 2)
    Toshiba Camileo Clip - Full HD Mini Camcorder
    Giants Of The Phone World (Part 2) : Micromax A110 Canvas 2, Lenovo K860, Micromax Canvas HD
    Remotely Access Your PC (Part 2)
    Top 10
    Gigabyte P35K - The 15.6”GIGABYTE Gaming Laptop
    Microsoft Surface Pro 2 and Surface 2
    Venom BlackBook 17 - A Powerhouse Notebook Computer
    7 Tips For Using iOS 7
    Top 7 Apps for Your First iPad
    The Best Mobile Apps (Part 3) - Switchr, QuizUp, Hudway Augmented Reality
    The Best Mobile Apps (Part 2) - Pages
    The Best Mobile Apps (Part 1) - BBM
    Apple MacBook Pro With Retina Display 13in (Late-2013)
    Asus MB168B+ - Portable Monitor