Programming Excel with VBA and .NET : Variables (part 4) - User-Defined Types & Objects

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
11/15/2011 9:21:38 AM

8. User-Defined Types

You can create your own composite types out of the existing Visual Basic types. These composite types are called user-defined types in Visual Basic and they are used primarily for advanced tasks such as reading and writing binary files or working with Windows API functions .

Use the Type statement to define a user-defined type:

    ' Code in Variables module
Private Type POINTAPI
x As Long
y As Long
End Type

Private Declare Function GetCursorPos _
Lib "user32" (lp As POINTAPI) As Long

The preceding module-level definition creates a type named POINTAPI that contains two Long types. This definition matches the argument returned by the GetCursorPos Windows API function, and it enables you to get at the values returned by that function in code. For example, the following procedure displays the location of the cursor:

    Sub ShowCursorPosition( )
Dim point As POINTAPI
GetCursorPos point
MsgBox point.x & " " & point.y
End Sub

The preceding code declares the point variable using the POINTAPI type defined earlier; then it calls the Windows GetCursorPos function to fill in the value of point. It is common for Windows API functions to return values through user-defined types in this way (Windows calls user-defined types structures ). Variables with user-defined types use the period to get items from within the type. Thus, point.x gets the value of the x-coordinate in the preceding example.

9. Objects

Object is the general term for an instance of a class. Visual Basic has an Object type that you can use to create variables that reference any generic object; however, you usually want to create variables of a specific class of object. Objects are a special kind of variable because you can control when they are created. Other types of variables in Visual Basic are initialized whenever they are declared, but that's not true with objects .

The easiest way to create an object variable is to include the New keyword in the variable declaration. For example, the following line creates a new object variable from the PublicClass class definition:

    Dim obj As New PublicClass

Once created, you can use that object's properties and methods to do whatever it is you want to do. New is an executable statement; if you use it at the module level, the actual creation of the object is delayed till the first time the object is referenced within a procedure—a confusing situation that is best to avoid. If you declare an object variable at the module level, omit New, then create the object within a procedure explicitly. For example, the following code creates a global object variable and creates the object the first time UseObject runs:

    ' Global object variable
Public g_obj As PublicClass

Sub UseObject( )
' Create global object variable
If g_obj Is "Nothing" Then Set g_obj = New PublicClass
' Show that the object exists
Debug.Print g_obj.CREATED
End Sub

There are a few significant things to point out about the preceding code:

  • The module-level declaration uses a specific class type. That makes the class's methods and properties available to Visual Basic's Auto Complete feature as you write code.

  • TypeName(g_obj) = "Nothing" is True if the object has not been created. In that case, the Set statement creates a new instance of the object.

  • Visual Basic also provides an IsEmpty function to check if an object has been created, but that works only with the generic Object type—it doesn't work with specific classes.

To destroy an object, set the object variable to Nothing:

    Set g_obj = Nothing

This is not necessary when the object is a local variable, since those are automatically destroyed when the procedure ends. However, global, module-level, and Static variables exist as long as the workbook is open unless you explicitly destroy them.

Excel provides many objects that you can use from Visual Basic, but they can be created only through other Excel objects. For example, the following two statements are equivalent, and neither one creates a new worksheet!

    Dim ws1 As Worksheet
Dim ws2 As New Worksheet

To create an object from Excel, you usually use the Add method of the object's collection class:

    Sub CreateExcelObject( )
' Declare a Worksheet object variable
Dim ws As Worksheet
' Create the Worksheet
Set ws = Worksheets.Add
End Sub

Since Excel controls the creation of its objects, it also controls their destruction. Setting ws to Nothing just destroys the object reference; it doesn't remove the worksheet. To destroy an Excel object, you usually use the object's Delete method as shown here:


Visual Basic includes the keyword With to create blocks of code that work with a specific object. The With statement creates a shorthand for repeatedly referring to the same object, and you will frequently see it in recorded code. For example, the following code creates a new worksheet and sets the object's properties using With:

    Sub UseWith( )
' Create a new worksheet in a With statement

With Worksheets
.Name = "New Worksheet"
.Range("A1") = "Some new data..."
ActiveWindow.DisplayGridlines = False
End With
End Sub

The Worksheets.Add method returns a reference to the worksheet object that is then used by the subsequent properties. Each property begins with a period inside of the With block. Statements that don't refer to the object, such as ActiveWindow, simply omit the period.

There's nothing wrong with using With, but I prefer to use the variable name explicitly. That's just my style.

  •  Windows Vista : Performing Local PC Administration (part 2) - Performing common workstation administration tasks
  •  Windows Vista : Performing Local PC Administration (part 1) - Working with workstation administration tools
  •  Filtering Out Evil with Firewalls (part 3) - Manually Configuring a Firewall's Ports
  •  Filtering Out Evil with Firewalls (part 2)
  •  Filtering Out Evil with Firewalls (part 1)
  •  Windows 7 : Windows Driver Foundation Architecture (part 4) - Tools for Development and Testing
  •  Windows 7 : Windows Driver Foundation Architecture (part 3) - Driver Frameworks
  •  Windows 7 : Windows Driver Foundation Architecture (part 2) - Integrated I/O Queuing and Cancellation
  •  Windows 7 : Windows Driver Foundation Architecture (part 1)
  •  Windows 7 : Using Advanced Security Options (part 2) - Configuring Windows Defender
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    Video Sports
    programming4us programming4us