Listing Quality Classes Feel Like Native Excel Functionality

Sub DemonstrateSettings()

Dim oSettings As Settings Dim oSetting As Setting

Set oSettings = New Settings

' Add a setting

Set oSetting = oSettings.Add("Test With oSetting

.ChangeEditMode True .Description = "This is a test .Value = "Testing" .EventHandler = "SayHello" End With

' Check out EventHandler oSetting.Value = "Show me the event handler!"

' Delete the setting oSetting.Delete

Set oSetting = Nothing Set oSettings = Nothing End Sub

Sub SayHe11o()

MsgBox "Hello" End Sub

It would be a good idea to step through this listing to get a good feel for the flow of everything, particularly the execution path when you're using the EventHandler property. As you can see, it is very easy to use these classes.

Let's review. To use the Settings and Setting classes in a project you need to do the following.

1. Ensure that one of the worksheets in the workbook is named "Settings". You can either copy an existing Settings worksheet (from a different workbook) into the workbook or name one of the existing sheets "Settings". If you rename one of the existing sheets, be sure to populate the first row in the worksheet with the appropriate column names: Name, Value, Type, Description, and Setting Change Event.

2. Import the Setting and Settings classes into the VBA project. You can achieve this by opening an existing project that uses the classes and dragging and dropping the class modules from one project to the next. Alternatively, you can use File ^ Import File... from the VBE menu and

New Setting")


locate the code files. This method assumes that you have exported the files at some point using File ^ Export File.

3. Before distributing the file, you'll probably want to hide the Settings worksheet.

If you find yourself using the Setting and Settings classes in every project, you may want to set up a workbook with all of the required components and then save the workbook as a template. Then, when you begin working on a new development project, you can start by creating a new workbook based on your development template.

I'll be using both of these classes for many other examples in the remainder of this book. You'll really get a kick out of this in Chapter 20 when I present a user interface you can use with these classes. Figure 12.3 provides a hint of what's to come in that chapter.

0 0

Post a comment