Excel Classes in VBA - What Are Visual Basic for Applications Classes, And How Can You Use Them in Microsoft Excel

Excel Classes in VBA - What Are Visual Basic for Applications Classes

, And How Can You Use Them in Microsoft Excel

Microsoft Excel's VBA (Visual Basic for Applications) programming language allows you to write macros in code modules. However, VBA also supports something called Object Orientated Programming (OOP) tosome extent, using something called class modules. This article explains what these are, and how to use them.

Starting at the End - Where you want to get to

In Excel you can refer to workbooks and worksheets as built-in objects. For example:

Dim ThisSheet As Worksheet

Set ThisSheet = Worksheets("Sheet1")

However, excitingly, you can also create your own objects. For example, suppose that you often want to create new worksheets with your company name in the top cell. What you'd really like to write is this:

DIM wb as OwlWorkbook

SET wb = New OwlWorkbook

wb.Title = "Wise Owl"

The only thing stopping you doing this is that Excel has never heard of an OwlWorkbook object, and certainly doesn't know that it has a Title property. Yet!

Creating the Class

To create the class above, go into Visual Basic within Excel (eg by pressing ALT + F11) then choose to insert a class module (you can do this by right-clicking in Project Explorer and choosing Insert from the short-cut menu which appears).

Now go to the Properties window (press F4 if you can't see it) and change the name of the class you've just added from Class1 to (say) OwlWorkbook.

Now you need to say what happens when someone creates an instance of your class. At the top of the screen is a dropdown saying General. Click on this and choose Class1 instead. Now on the right-hand side choose Initialize from the dropdown menu which appears (Excel may do this for you automatically).

You can now type in what you want to happen when someone creates a new object based on your class. In our case we want to create a new workbook, so you could type in something like:

Workbooks.Add

Finally, we need to give our workbook a Title property. To do this, type in the following code:

Property Let Title(ThisTitle As String)

Range("A1").value = ThisTitle

Range("A1").Font.Bold = True

End Property

Finally, create a subroutine to test out your class (this should go in a module, as normal). For example:

Sub test()

Dim ThisBook As OwlWorkbook

Set ThisBook = New OwlWorkbook

ThisBook.Title = "Company name here"

End Sub

When you run this subroutine it will automatically run the Initialize event in your class, then go to the Let property to create a new workbook and write your company name in cell A1!

POST COMMENT

Synonyms Application - Try it ! Prepare For The Answer to Your Eb5 Immigrant Program Application Backup Update Process Fails with Backup Exec Application Application Essay for a Counseling Program RBI: Disclose all fees in application form Provisional patent application and its benefits Sapphire Ball Is Made Use Of In an Assortment of Machine Applications Organic Photochromic Materials Of The Application - Organic Photochromic, Photochromic Materials - Case Appraisal Method For Applications Natural Sinusitis Remedies: Basic Applications Natto progress of current research and application Web Application Development To Optimize Revenue Generation Great Application Deletion Ways In Macintosh Operating System