IT Skills

Double-clicking any of these objects displays the code associated with the item, if any.

The event-handler procedures that you write go into the Code window for the ThisWorkbook item (for workbook-related events) or one of the Sheet objects (for worksheet-related events).

In Figure, the Code window for the Sheet1 object is displayed, and it hap-pens to have a single event-handler procedure defined. Notice the two drop-down controls at the top of the Code module? Keep reading to find out why those are useful.

The VBE helps you out when you’re ready to write an event-handler proce-dure; it displays a list of all events for the selected object.

At the top of each Code window, you find two drop-down lists:

✓ The Object drop-down list (the one on the left)

✓ The Procedure drop-down list (the one on the right)

By default, the Object drop-down list in the Code window displays General. If you’re writing an event-handler for the ThisWorkbook object, you need to choose Workbook from the Object drop-down (it’s the only other choice).

If you’re writing an event-handler for a Sheet object, you need to choose Worksheet (again, the only other choice).

After you’ve made your choice from the Object drop-down list, then you can choose the event from the Procedure drop-down list. Figure shows the choices for a workbook-related event.

When you select an event from the list, VBE automatically starts creating an event-handler procedure for you. This is a very useful feature, because you can verify that the proper arguments are used.

Here’s a little quirk. When you first selected Workbook from the Object list, VBE always assumes that you want to create an event-handler procedure for the Open event and creates it for you. If you’re actually creating a Workbook_ Open procedure, that’s fine. But if you’re creating a different event-procedure, you need to delete the empty Workbook_Open Sub that Excel created.

VBE’s help goes only so far, however. It writes the Sub statement and the End Sub statement. Writing the VBA code that goes between these two state-ments is your job.

You don’t really have to use those two drop-downs, but it makes your job easier because the name of the event-handler procedure is critically impor-tant. If you don’t get the name exactly right, it won’t work. Plus, some event-handler procedures use one or more arguments in the Sub statement. There’s no way you can remember what those arguments are. For example, if you select SheetActivate from the event list for a Workbook object, VBE writes the following Sub statement:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

In this case, Sh is the argument passed to the procedure and is a variable that represents the sheet in the activated workbook. 

In this section, I provide a few examples so that you can get the hang of this event-handling business.

One of the most commonly used events is the Workbook Open event. Assume that you have a workbook that you use every day. The Workbook_Open procedure in this example is executed every time the workbook is opened. The procedure checks the day of the week; if it’s Friday, the code displays a reminder message for you.

To create the procedure that is executed whenever the Workbook Open event occurs, follow these steps:

1. Open the workbook. Any workbook will do.

2. Press Alt+F11 to activate the VBE.

3. Locate the workbook in the Project window.

4. Double-click the project name to display its items, if necessary.

5. Double-click the ThisWorkbook item.

The VBE displays an empty Code window for the ThisWorkbook object.

6. In the Code window, select Workbook from the Object (left) drop-down list.

The VBE enters the beginning and ending statements for a Workbook_ Open procedure.

7. Enter the following statements, so the complete event-procedure looks like this:

Private Sub Workbook_Open()

Dim Msg As String

If WeekDay(Now) = 6 Then

Msg = “Today is Friday. Make sure that you “

Msg = Msg & “do your weekly backup!”

MsgBox Msg

End If

End Sub

Workbook_Open is executed automatically whenever the workbook is opened. It uses VBA’s WeekDay function to determine the day of the week. If it’s Friday (day 6), a message box reminds the user to perform a weekly file backup. If it’s not Friday, nothing happens.

If today isn’t Friday, you might have a hard time testing this procedure. Here’s a chance to test your own skill at VBA. You can modify this procedure any way you like. For example, the following version displays a message every time the workbook is opened. This gets annoying after a while, trust me.

Private Sub Workbook_Open()

Msg = “This is Frank’s cool workbook!”

MsgBox Msg

End Sub

A Workbook_Open procedure can do almost anything. These event-handlers are often used for the following:

✓ Displaying welcome messages (such as in Frank’s cool workbook) ✓ Opening other workbooks

✓ Activating a particular worksheet in the workbook ✓ Setting up custom shortcut menus

The BeforeClose event for a workbook

Here’s an example of the Workbook_BeforeClose event-handler procedure, which is executed automatically immediately before the workbook is closed. This procedure is located in the Code window for a ThisWorkbook object:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Msg As String

Dim Ans As Integer

Dim FName As String

Msg = “Would you like to make a backup of this file?”

Ans = MsgBox(Msg, vbYesNo)

If Ans = vbYes Then

FName = “F:\BACKUP\” & ThisWorkbook.Name

ThisWorkbook.SaveCopyAs FName

End If

End Sub

This routine uses a message box to ask the user whether he would like to make a backup copy of the workbook. If the answer is yes, the code uses the SaveCopyAs method to save a backup copy of the file on drive F. If you adapt this procedure for your own use, you probably need to change the drive and path.

Excel programmers often use a Workbook_BeforeClose procedure to clean up after themselves. For example, if you use a Workbook_Open procedure to change some settings when you open a workbook (hiding the status bar, for example), it’s only appropriate that you return the settings to their original state when you close the workbook. You can perform this electronic house-keeping with a Workbook_BeforeClose procedure.

There is an unfortunate caveat with the Workbook_BeforeClose event. If you close Excel and any open file has been changed since the last save, Excel will show its usual “Do you want to save changes” message box. Clicking the Cancel button cancels the entire closing process. But the Workbook_ BeforeClose event will have been executed anyway.

The BeforeSave event, as its name implies, is triggered before a workbook is saved. This event occurs when you use either the File➪Save or File➪Save As command.

The following procedure, which is placed in the Code window for a ThisWorkbook object, demonstrates the BeforeSave event. The routine updates the value in a cell (cell A1 on Sheet1) every time the workbook is saved. In other words, cell A1 serves as a counter to keep track of the number of times the file was saved.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _

As Boolean, Cancel As Boolean)

Sheets(“Sheet1”).Range(“A1”).Value = _

Sheets(“Sheet1”).Range(“A1”).Value +1

End Sub

Notice that the Workbook_BeforeSave procedure has two arguments, SaveAsUI and Cancel. To demonstrate how these arguments work, examine the following macro, which is executed before the workbook is saved. This procedure prevents the user from saving the workbook with a different name. If the user chooses the File➪Save As command, then the SaveAsUI argument is True.

When the code executes, it checks the SaveAsUI value. If this variable is True, the procedure displays a message and sets Cancel to True, which cancels the Save operation.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _

As Boolean, Cancel As Boolean)

If SaveAsUI Then

MsgBox “You cannot save a copy of this workbook!”

Cancel = True

End If

End Sub

Keep in mind that this procedure won’t really prevent anyone from saving a copy with a different name. If someone really wants to do it, they can just open the workbook with macros disabled. When macros are disabled, event-handler procedures are also disabled — which makes sense because they are, after all, macros.

Another category of events consists of activating and deactivating objects — specifically, sheets and workbooks.

Excel can detect when a particular sheet is activated or deactivated and execute a macro when either of these events occurs. These event-handler procedures go in the Code window for the Sheet object.

You can quickly access a sheet’s code window by right-clicking on the sheet’s tab and selecting View Code.

The following example shows a simple procedure that is executed whenever a particular sheet is activated. This code simply pops up a message box that displays the name of the active sheet:

Private Sub Worksheet_Activate()

MsgBox “You just activated “ & ActiveSheet.Name

End Sub

Here’s another example that activates cell A1 whenever the sheet is activated:

Private Sub Worksheet_Activate()

Range(“A1”).Activate

End Sub

Although the code in these two procedures is about as simple as it gets, event-handler procedures can be as complex as you like.

The following procedure (which is stored in the Code window for the Sheet1 object) uses the Deactivate event to prevent a user from activating any other sheet in the workbook. If Sheet1 is deactivated (that is, another sheet is acti-vated), the user gets a message and Sheet1 is activated.

Private Sub Worksheet_Deactivate()

MsgBox “You must stay on Sheet1”

Sheets(“Sheet1”).Activate

End Sub

By the way, I don’t recommend using procedures, such as this one, that attempt to take over Excel. These so-called “dictator” applications can be very frustrating and confusing for the user. Rather, I recommend training the user how to use your application correctly.

The previous examples use events associated with a specific worksheet. The ThisWorkbook object also handles events that deal with sheet activation and deactivation. The following procedure, which is stored in the Code window for the ThisWorkbook object, is executed when any sheet in the workbook is activated. The code displays a message with the name of the activated sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

MsgBox Sh.Name

End Sub

The Workbook_SheetActivate procedure uses the Sh argument. Sh is a vari-able that represents the active Sheet object. The message box displays the Sheet object’s Name property. The next example is contained in a ThisWorkbook Code window. It consists of two event-handler procedures:

Executed when any sheet in the workbook is deactivated. It stores the sheet that is deactivated in an object vari-able. (The Set keyword creates an object variable.)

Executed when any sheet in the workbook is activated. It checks the type of sheet that is activated (using the TypeName function). If the sheet is a chart sheet, the user gets a mes-sage (see Figure). When the OK button in the message box is clicked, the previous sheet (which is stored in the OldSheet variable) is reactivated.

The preceding syntax considers a view to be a table. The SELECT, DELETE, INSERT, UPDATE, TRIGGER, and REFERENCES privileges apply to tables and views only. The USAGE privilege applies to domains, character sets, collations, and translations. The UNDER privilege applies only to types, and the EXECUTE privilege applies only to routines. The following sections give examples of the various ways you can use the GRANT statement and the results of those uses.

A user name is one type of authorization identifier but not the only one. It identifies a person (or a program) who is authorized to perform one or more functions on a database. In a large organization with many users, granting privileges to every individual employee can be tedious and time-consuming. SQL:2003 addresses this problem by introducing the notion of roles.

A role, identified by a role name, is a set of zero or more privileges that can be granted to multiple people who all require the same level of access to the database. For example, everyone who performs the role SecurityGuard has the same privileges. These privileges are different from those granted to the people who have the role SalesClerk.

As always, not every feature mentioned in the SQL:2003 specification is avail-able in every implementation. Check your DBMS documentation before you try to use roles.

You can create roles by using syntax similar to the following:

CREATE ROLE SalesClerk ;

After you’ve created a role, you can assign people to the role with the GRANT statement, similar to the following:

GRANT SalesClerk to Becky ;

You can grant privileges to a role in exactly the same way that you grant priv-ileges to users, with one exception: It won’t argue or complain.

To grant a role the privilege of adding data to a table, follow this example:

 

GRANT INSERT

ON CUSTOMER

TO SalesClerk ;

This privilege enables any clerk in the sales department to add new customer records to the CUSTOMER table.

To enable people to view the data in a table, use the following example:

GRANT SELECT

ON PRODUCT

TO PUBLIC ;

This privilege enables anyone with access to the system (PUBLIC) to view the PRODUCT table’s contents.

This statement can be dangerous. Columns in the PRODUCT table may con-tain information that not everyone should see, such as CostOfGoods. To provide access to most information while withholding access to sensitive information, define a view on the table that doesn’t include the sensitive columns. Then grant SELECT privileges on the view rather than the underly-ing table. The following example shows the syntax for this procedure:

CREATE VIEW MERCHANDISE AS

SELECT Model, ProdName, ProdDesc, ListPrice

FROM PRODUCT ;

GRANT SELECT

ON MERCHANDISE

TO PUBLIC ;

Using the MERCHANDISE view, the public doesn’t get to see the PRODUCT table’s CostOfGoods column or any other column except the four listed in the CREATE VIEW statement.

In any active organization, table data changes over time. You need to grant to some people the right and power to make changes and to prevent everyone else from doing so. To grant change privileges, follow this example:

GRANT UPDATE (BonusPct)

ON BONUSRATE

TO SalesMgr ;

The sales manager can adjust the bonus rate that salespeople receive for sales (the BonusPct column), based on changes in market conditions. The sales manager can’t, however, modify the values in the MinAmount and

MaxAmount columns that define the ranges for each step in the bonus sched-ule. To enable updates to all columns, you must specify either all column names or no column names, as shown in the following example:

GRANT UPDATE

ON BONUSRATE

TO VPSales ;

Customers go out of business or stop buying for some other reason. Employees quit, retire, are laid off, or die. Products become obsolete. Life goes on, and things that you tracked in the past may no longer be of interest to you. Someone needs to remove obsolete records from your tables. You want to carefully control who can remove which records. Regulating such privileges is another job for the GRANT statement, as shown in the following example:

GRANT DELETE

ON EMPLOYEE

TO PersonnelMgr ;

The personnel manager can remove records from the EMPLOYEE table. So can the DBA and the EMPLOYEE table owner (who’s probably also the DBA). No one else can remove personnel records (unless another GRANT statement gives that person the power to do so).

If one table includes a second table’s primary key as a foreign key, information in the second table becomes available to users of the first table. This situation potentially creates a dangerous “back door” through which unauthorized users can extract confidential information. In such a case, a user doesn’t need access rights to a table to discover something about its contents. If the user has access rights to a table that references the target table, those rights often enable him to access the target table as well.

Suppose, for example, that the table LAYOFF_LIST contains the names of the employees who will be laid off next month. Only authorized management has SELECT access to the table. An unauthorized employee, however, deduces that the table’s primary key is EmpID. The employee then creates a new table SNOOP, which has EmpID as a foreign key, enabling him to sneak a peek at LAYOFF_LIST. It’s high on the list of techniques every system administrator should know.)

CREATE TABLE SNOOP

(EmpID INTEGER REFERENCES LAYOFF_LIST) ;

Now all that the employee needs to do is try to INSERT rows corresponding to all employee ID numbers into SNOOP. The table accepts the inserts for only the employees on the layoff list. All rejected inserts are for employees not on the list.

SQL:2003 prevents this kind of security breach by requiring that a privileged user explicitly grant any reference rights to other users, as shown in the fol-lowing example:

GRANT REFERENCES (EmpID)

ON LAYOFF_LIST

TO PERSONNEL_CLERK ;

Domains, character sets, collations, and translations also have an effect on security issues. Created domains, in particular, must be watched closely to avoid their use as a way to undermine your security measures.

You can define a domain that encompasses a set of columns. In doing so, you want all these columns to have the same type and to share the same con-straints. The columns you create in your CREATE DOMAIN inherit the type and constraints of the domain. You can override these characteristics for specific columns, if you want, but domains provide a convenient way to apply numerous characteristics to multiple columns with a single declaration.

Domains come in handy if you have multiple tables that contain columns with similar characteristics. Your business database, for example, may con-sist of several tables, each of which contains a Price column that should have a type of DECIMAL(10,2) and values that are nonnegative and no greater than 10,000. Before you create the tables that hold these columns, create a domain that specifies the columns’ characteristics, as does the fol-lowing example:

CREATE DOMAIN PriceTypeDomain DECIMAL (10,2)

CHECK (Price >= 0 AND Price <= 10000) ;

Perhaps you identify your products in multiple tables by ProductCode, which is always of type CHAR (5), with a first character of X, C, or H and a last character of either 9 or 0. You can create a domain for these columns, too, as in the following example:

CREATE DOMAIN ProductCodeDomain CHAR (5)

CHECK (SUBSTR (VALUE, 1,1) IN (‘X’, ‘C’, ‘H’)

AND SUBSTR (VALUE, 5, 1) IN (9, 0) ) ;

With the domains in place, you can now proceed to create tables, as follows:

(ProductCode ProductCodeDomain,

ProductName CHAR (30),

Price PriceTypeDomain) ;

In the table definition, instead of giving the data type for ProductCode and Price, specify the appropriate domain. This action gives those columns the correct type and also applies the constraints you specify in your CREATE DOMAIN statements.

Certain security implications go with the use of domains. What if someone wants to use the domains you create — can this cause problems? Yes. What if someone creates a table with a column that has a domain of PriceTypeDomain? That person can assign progressively larger values to that column until it rejects a value. By doing so, the person can determine the upper bound on PriceType that you specify in the CHECK clause of your CREATE DOMAIN statement. If you consider that upper bound private informa-tion, you don’t want to enable others to use the PriceType domain. To pro-tect you in situations such as this example, SQL enables only those to whom the domain owner explicitly grants permission to use domains. Thus only the domain owner (as well as the DBA) can grant such permission. You can grant permission by using a statement such as the one shown in the following example:

GRANT USAGE ON DOMAIN PriceType TO SalesMgr ;

Different security problems may arise if you DROP domains. Tables that con-tain columns that you define in terms of a domain cause problems if you try to DROP the domain. You may need to DROP all such tables first. Or you may find yourself unable to DROP the domain. How a domain DROP is handled may vary from one implementation to another. SQL Server may do it one way, whereas Oracle does it another way. At any rate, you may want to restrict who can DROP domains. The same applies to character sets, collations, and translations.

Sometimes the execution of one SQL statement triggers the execution of another SQL statement, or even a block of statements. SQL:2003 supports triggers. A trigger specifies a trigger event, a trigger action time, and one or more triggered actions. The trigger event causes the trigger to execute or “fire.” The trigger action time determines when the triggered action occurs, either just before or just after the trigger event. The triggered action is the execution of one or more SQL statements. If more than one SQL statement is triggered, the statements must all be contained within a BEGIN ATOMIC...

END structure. The trigger event can be an INSERT, UPDATE, or DELETE statement.

For example, you can use a trigger to execute a statement that checks the validity of a new value before an UPDATE is allowed. If the new value is found to be invalid, the update can be aborted.

A user or role must have the TRIGGER privilege in order to create a trigger. An example might be:

CREATE TRIGGER CustomerDelete BEFORE DELETE

ON CUSTOMER FOR EACH ROW

WHEN State = NY

INSERT INTO CUSTLOG VALUES (‘deleted a NY customer’) :

Whenever a New York customer is deleted from the CUSTOMERS table, an entry in the log table CUSTLOG will record the deletion.

Most Excel programmers never need to worry about most of the events in this list. You should, however, at least know that these events exist because they may come in handy someday. Now, I discuss the most com-monly used events. To simplify things, I talk about two types of events: work-book and worksheet.

Table lists most of the workbook-related events. If, for some reason, you need to see the complete list of workbook-related events, you’ll find it in the Help system.

The Windows Magnifier is a great accessibility tool that allows you to zoom into any portion of your screen you find hard to read. This feature can be found in the Control Panel in the Ease of Access Center. It can also be quickly accessed by holding down the Windows key and pressing the + (plus) key to zoom in or - (minus) key to zoom out. When enabled the Magnifier box and the current magnification will be shown, as seen in the picture.