Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

Example: Creating a NumLock Class

In this section, I provide step-by-step instructions for creating a useful, albeit simple, class module. This class module creates a NumLock class that has one property ( Value ) and one method ( Toggle ).

Detecting or changing the state of the Num Lock key requires several Windows API functions, and the procedure varies depending on the version of Windows. In other words, it's fairly complicated. The purpose of this class module is to simplify things. All the API declarations and code are contained in a class module (not in your normal VBA modules). The benefits? Your code will be much easier to work with, and you can reuse this class module in your other projects.

After the class is created, your VBA code can determine the current state of the Num Lock key by using an instruction such as the following, which displays the Value property:

MsgBox NumLock.Value

Or your code can change the state of the Num Lock key by changing the Value property. The following instruction, for example, turns on the Num Lock key:

NumLock.Value = True

In addition, your code can toggle the Num Lock key by using the Toggle method:

NumLock.Toggle

It's important to understand that a class module contains the code that defines the object, including its properties and methods. You can then create an instance of this object in your VBA general code modules and manipulate its properties and methods .

To better understand the process of creating a class module, you might want to follow the instructions in the sections that follow. Start with an empty workbook.

Inserting a class module

Activate the Visual Basic Editor (VBE) and choose Insert Class Module. This adds an empty class module named Class1 . If the Properties window isn't displayed, press F4 to display it. Then change the name of the class module to NumLockClass (see Figure 29-1).

Figure 29-1: An empty class module named NumLockClass.

Adding VBA code to the class module

In this step, you create the code for the Value property. To detect or change the state of the Num Lock key, the class module needs the required Windows API declarations that are used to detect and set the Num Lock key. That code follows :

' Type declaration Private Type OSVERSIONINFO dwOSVersionInfoSize As Long dwMajorVersion As Long dwMinorVersion As Long dwBuildNumber As Long dwPlatformId As Long szCSDVersion As String * 128 End Type ' API declarations Private Declare Function GetVersionEx Lib "Kernel32" _ Alias "GetVersionExA" _ (lpVersionInformation As OSVERSIONINFO) As Long Private Declare Sub keybd_event Lib "user32" _ (ByVal bVk As Byte, _ ByVal bScan As Byte, _ ByVal dwflags As Long, ByVal dwExtraInfo As Long) Private Declare Function GetKeyboardState Lib "user32" _ (pbKeyState As Byte) As Long Private Declare Function SetKeyboardState Lib "user32" _ (lppbKeyState As Byte) As Long 'Constant declarations Const VK_NUMLOCK = &H90 Const VK_SCROLL = &H91 Const VK_CAPITAL = &H14 Const KEYEVENTF_EXTENDEDKEY = &H1 Const KEYEVENTF_KEYUP = &H2 Const VER_PLATFORM_WIN32_NT = 2 Const VER_PLATFORM_WIN32_WINDOWS = 1

Next , you need a procedure that retrieves the current state of the Num Lock key. I'll call this the Value property of the object. You can use any name for the property, but Value seems like a good choice. To retrieve the state, insert the following Property Get procedure:

Property Get Value() As Boolean ' Get the current state Dim keys(0 To 255) As Byte GetKeyboardState keys(0) Value = keys(VK_NUMLOCK) End Property

This procedure, which uses the GetKeyboardState Windows API function to determine the current state of the Num Lock key, is called whenever VBA code reads the Value property of the object. For example, after the object is created, a VBA statement such as this executes the Property Get procedure:

MsgBox NumLock.Value

You now need a procedure that sets the Num Lock key to a particular state: either on or off. You can do this with the following Property Let procedure:

Property Let Value(boolVal As Boolean) Dim o As OSVERSIONINFO Dim keys(0 To 255) As Byte o.dwOSVersionInfoSize = Len(o) GetVersionEx o GetKeyboardState keys(0) ' Is it already in that state? If boolVal = True And keys(VK_NUMLOCK) = 1 Then Exit Property If boolVal = False And keys(VK_NUMLOCK) = 0 Then Exit Property ' Toggle it If o.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS Then '(Win95) 'Toggle numlock keys(VK_NUMLOCK) = IIf(keys(VK_NUMLOCK) = 0, 1, 0) SetKeyboardState keys(0) ElseIf o.dwPlatformId = VER_PLATFORM_WIN32_NT Then ' (WinNT) 'Simulate Key Press keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0 'Simulate Key Release keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or _ KEYEVENTF_KEYUP, 0 End If End Property

The Property Let procedure takes one argument, which is either True or False . A VBA statement such as the following sets the Value property of the NumLock object to True by executing the Property Let procedure:

NumLock.Value = True

Finally, you need a procedure to toggle the NumLock state:

Sub Toggle() ' Toggles the state Dim o As OSVERSIONINFO o.dwOSVersionInfoSize = Len(o) GetVersionEx o Dim keys(0 To 255) As Byte GetKeyboardState keys(0) If o.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS Then '(Win95) 'Toggle numlock keys(VK_NUMLOCK) = IIf(keys(VK_NUMLOCK) = 0, 1, 0) SetKeyboardState keys(0) ElseIf o.dwPlatformId = VER_PLATFORM_WIN32_NT Then ' (WinNT) 'Simulate Key Press keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0 'Simulate Key Release keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or _ KEYEVENTF_KEYUP, 0 End If End Sub

Notice that Toggle is a standard Sub procedure (not a Property Let or Property Get procedure). A VBA statement such as the following one toggles the state of the NumLock object by executing the Toggle procedure.

NumLock.Toggle

Using the NumLockClass class

Before you can use the NumLockClass class module, you must create an instance of the object. The following statement, which resides in a regular VBA module (not the class module), does just that:

Dim NumLock As New NumLockClass

Notice that the object type is NumLockClass (that is, the name of the class module). The object variable itself can have any name, but NumLock certainly seems like a logical name for this.

The following procedure sets the Value property of the NumLock object to True , which results in the Num Lock key being turned on:

Sub NumLockOn() Dim NumLock As New NumLockClass NumLock.Value = True End Sub

The next procedure displays a message box that indicates the current state of the Num Lock key ( True is on; False is off):

Sub GetNumLockState() Dim NumLock As New NumLockClass MsgBox NumLock.Value End Sub

The following procedure toggles the Num Lock key:

Sub ToggleNumLock() Dim NumLock As New NumLockClass NumLock.Toggle End Sub

Notice that there's another way to toggle the Num Lock key without using the Toggle method:

Sub ToggleNumLock2() Dim NumLock As New NumLockClass NumLock.Value = Not NumLock.Value End Sub

It should be clear that using the NumLock class is much simpler than using the API functions. After you create a class module, you can reuse it in any other project simply by importing the class module.