Chapter 7: Workbooks and Worksheets
|
| < Day Day Up > |
|
The workbook is the highest level of organization within Microsoft Excel, so you might think that there aren’t a whole lot of actions you can take on a workbook beyond creating new workbooks, saving changes, closing workbooks, or deleting workbooks you no longer need. While it is true that most “workbook” manipulations actually occur at the worksheet and cell level, you’ll still find plenty to do with workbooks. This chapter also discusses worksheets, both as worksheets and as members of the Sheets collection, so you’ll find the resources you need to create workbooks and set them up the way you want them (in terms of password protection, the number of worksheets, and the names of those worksheets) before you start manipulating the values contained in them.
The Workbooks Collection
The Workbooks collection contains references to every workbook that you have open in your copy of Excel. If there’s some change you want to make to every open workbook, you can use a For Each…Next loop to move through the collection and make those changes. The Workbooks collection contains a number of other useful methods that you can use to manipulate your existing workbooks, but the most basic ability is that of creation—you need to be able to create a new workbook before you can manipulate it.
Creating New Workbooks
One of the basic tasks you’ll want to complete when you program Excel is to create a new workbook. New workbooks can be the repository of new information or the target of worksheets copied from existing workbooks. Regardless of what you want to use the new workbook for, you can create the workbook using the following code:
Workbooks.Add
If you want to create a workbook that’s a copy of an existing workbook, you can do so by setting the Add method’s Template parameter, as in the following procedure:
Sub AddNewWorkbook()
Dim NewWbk As Workbook
Set NewWkbk = Workbooks.Add(Template:="C:\ExcelProg\MonthlySales.xls")
End Sub
Opening Workbooks
After you’ve created one or more workbooks, you’ll probably want to open them at some point with the Workbooks collection’s Open method, which, in its simplest form, appears as follows:
Workbooks.Open (FileName:="MonthlySales.xls")
You do have plenty more options when you open a workbook, however. The Open method has the following full syntax:
expression.Open(FileName, UpdateLinks, ReadOnly, Format,Password,WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify,
Converter,AddToMru, Local, CorruptLoad)
Table 7-1 describes the parameters available for use with the Workbooks.Open method. Of particular interest are the ReadOnly parameter, which requires that the user running your macro open the workbook in read-only mode; the Password parameter, which need only be set if the workbook is password protected; and the Delimiter parameter, which specifies the character used to separate fields if the file the user is opening is a text file.
| Parameter | Description |
|---|---|
| FileName | Required string specifying the name and path of the file to open. |
| UpdateLinks | Tells Excel how to handle any links from the workbook to other workbooks. 0 means no updates, 1 means update external references, 2 means update remote references, and 3 means update both external and remote references. |
| ReadOnly | When set to True, opens the workbook in read-only mode. |
| Password | A string that contains the password required to open the workbook. If you omit the argument, the regular Excel password protection routine will take over. |
| WriteResPassword | A string that contains the password required to write to a write- reserved workbook. If you omit the argument, the regular Excel password protection routine will take over. |
| IgnoreReadOnly Recommended | If the workbook was saved with the Read-Only Recommended option turned on, setting this parameter to True causes Excel to skip showing the Read-Only Recommended message box when the workbook is opened. |
| Origin | Indicates the operating system used to create the file. The three constants are xlWindows, xlMacintosh, and xlMSDOS. If this parameter isn’t specified, Excel uses the current operating system. |
| Format | Specifies the character used to separate one cell’s value from the next cell’s value. 1 means tab, 2 means comma, 3 means space, 4 means semicolon, 5 means there is no delimiter, and 6 indicates another character specified in the Delimiter parameter. |
| Delimiter | Contains the delimiter character indicated by a Format parameter value of 6. |
| Editable | If the file is an Excel 4 add-in, setting this argument to True opens the add-in as a visible window. If the argument is False or omitted, the add-in will be hidden and not able to be unhidden. This option doesn’t apply to add-ins created with Excel version 5.0 or later. If the file is an Excel template (*.xlt file), setting the argument to True opens the template for editing, whereas setting it to False or omitting it creates a new workbook based on the template. |
| Notify | If the file is in use, setting this parameter to True means Excel will open the file in read-only mode, keep checking for the file’s availability, and let the user know when the file can be opened in read-write mode. |
| Converter | An Excel constant indicating the first converter to try when the file is opened. These converters are additional files that let you convert files to Excel from programs Excel doesn’t already know how to open. You will usually need to get the converter from the other software vendor, but some converters are available on the Microsoft Office Web site. |
| AddToMru | When set to True, adds the workbook to the recently used file list. |
| Local | A Boolean variable that indicates whether to use the local language set in Excel or the local language set in VBA (if different). |
| CorruptLoad | When set to xlNormalLoad, opens the file normally. When set to xlRepairFile, attempts to repair the file. When set to xlExtractData, attempts to extract the data into a recovery file. |
| Note | You’ll learn more about opening text files later in this section. |
One of the most useful and versatile file formats is the text format. Regardless of the program you use to create a spreadsheet or database table, you can usually save it as text and then open it in Excel. For example, if you are working with a colleague at another company who uses a spreadsheet or database program that doesn’t read or create Excel-compatible files, you can always write the data to a text file, which can then be read into Excel. You lose any formatting or formulas from the original file when you go the text route, but getting the data from one place to another is an ability worth knowing about.
The key to using a text file to represent spreadsheet data is in creating a clear division between cells. Many programs use the comma as a delimiter, or character that represents the boundary between two cells. For example, Figure 7-1 displays a worksheet as a table with three rows of data and three cells per row.
10345,5738,6029
24082,7459,3108
5119,8003,14972
Figure 7-1: You can open a file that contains comma-delimited data directly into Excel.
It would be incorrect, however, to write the data to a text file with comma delimiters while using a comma as a thousands separator (in the United States) or decimal separator (in Europe). Figure 7-2 shows what would happen if the same data list were written using commas both as thousands separators and as delimiters:
10,345,5,738,6,029
24,082,7,459,3,108
5,119,8,003,14,972
Instead of the expected three rows of three cells, the above data file would produce a worksheet with three rows and four cells per row.
Figure 7-2: When delimiters appear in unexpected places, data chaos ensues.
You can use characters other than commas as delimiters if you need to bring in data that includes commas, such as text or numbers with thousands separators. In the Text Import Wizard, you can select an option button indicating which delimiter your file uses (comma, space, tab, semicolon, or another character you type in yourself). You can do the same thing in Visual Basic for Applications (VBA) by setting the parameters of the Workbooks.OpenText method. The OpenText method has the following full syntax:
expression.OpenText(FileName, Origin, StartRow, DataType, TextQualifier,
ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar,
FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator,
TrailingMinusNumbers, Local)
Table 7-2 lists and describes the available parameters.
| Parameter | Description |
|---|---|
| FileName | Required string specifying the name and path of the file. |
| Origin | Indicates the operating system used to create the file. The three constants are xlWindows, xlMacintosh, and xlMSDOS. If this parameter isn’t specified, Excel uses the current operating system. |
| StartRow | The number of the row from which Excel should begin reading data into the worksheet. |
| DataType | Specifies the column format of the data in the file using one of the following XlTextParsingType constants: xlDelimited, which indicates there is a delimiting character, or xlFixedWidth, which indicates each field is of a fixed length. If this argument is not specified, Excel attempts to determine the column format when it opens the file. |
| TextQualifier | Uses an XlTextQualifier constant to indicate the character used to indicate that a field contains a text value. The available constants are xlTextQualifierDoubleQuote (double quotes, the default), xlTextQualifierNone (no character indicates a field contains text), and xlTextQualifierSingleQuote (single quotes). |
| ConsecutiveDelimiter | Set these parameters to True if you want to treat two or more consecutive delimiter characters as a single cell boundary. |
| Tab, Semicolon, Comma, Space | Set this parameter to True if the named character is the delimiter used in the text file. |
| FieldInfo | An array containing parse information for individual columns of data. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the xlColumnDataType constants specifying how the column is parsed. Those constants are xlGeneralFormat (a General value), xlTextFormat (a Text value), xlMDYFormat (an MDY date), xlDMYFormat (a DMY date), xlYMDFormat (a YMD date), xlMYDFormat (an MYD date), xlDYMFormat (a DYM date), xlYDMFormat (a YDM date), xlEMDFormat (an EMD date), and xlSkipColumn. (Do not import the column.) You can use xlEMDFormat only if you have installed and selected Taiwanese language support. The xlEMDFormat constant specifies that Taiwanese era dates are being used. |
| TextVisualLayout | A variant, not used in the American English version of Excel, that controls how the workbook is displayed within the Excel interface. |
| Other | Set this parameter to True if you use OtherChar to define a non- standard delimiter character. |
| OtherChar | The character used as the delimiter in the file to be opened. If there is more than one character in the string, Excel uses the first character. |
| DecimalSeparator, ThousandsSeparator | The characters assigned to these two arguments indicate the decimal separator (a period in the United States) and the thousands separator (a comma in the United States). |
| TrailingMinusNumbers | A value that indicates whether a number comes after the minus sign (True, the default value) or if the minus sign comes after a negative number (False, almost never used). Unless you have a specific reason to set this parameter to False, you should never include it in the OpenText method’s call. |
| Local | A Boolean variable that indicates whether to use the local language set in Excel or the local language set in VBA (if different). |
A routine to open a text file named SalesExport.txt that uses semicolons as its delimiter characters would look like this:
Sub BringInText()
Workbooks.OpenText Filename:="SalesExport.txt", Semicolon:=True
End Sub
When you write a program that changes other files, it’s important to remember that you’re assuming you have complete control over the files and that they’re not open. The best-written code in the universe is little good to you if you try to open a file but get a read-only copy of the file because one of your co-workers opened it to fill in some numbers for a project briefing.
The following procedure lets you avoid those problems by checking to see if a particular workbook is open:
Sub CheckIfOpen()
Dim Wkbk As Workbook
Dim Filename As String
Filename = InputBox("Type the name of the file you want to check.")
For Each Wkbk in Application.Workbooks
If Wkbk.Name = Filename Then
MsgBox (Filename & " is open; changes may result in errors.")
End If
Next Wkbk
End Sub
| Caution | Remember that the name of a file is case-sensitive and includes the file extension, which is usually .xls for Excel files. Typing SalesSummary or salessummary.xls into this procedure’s InputBox wouldn’t indicate that the file SalesSummary.xls was open! |
Saving Workbooks
Just as it’s important to save your workbooks when you enter data or change formatting manually, it’s vital that you save your workbooks when you make significant changes using VBA. One scenario where it’s possible for things to go wrong is if you were to import data into a workbook but close the workbook before you save the new data. If the source file is on another computer and you aren’t able to re-establish your connection to it for some reason, it would be as if you hadn’t run the macro at all.
There is a property of the Application object that comes in handy when you want to save the workbook that contains your macro code. That property is the ThisWorkbook property, which returns a Workbook object representing the workbook that contains the VBA code you’re running. With the new Workbook object in hand, you can call the Save method to save a copy of your workbook.
The code to save the workbook containing the code is simply this:
ThisWorkbook.Save
| Note | If you use the ThisWorkbook.Save method to save a workbook for the first time, Excel attempts to save the workbook using its current name. If it’s the first workbook you’ve created in this Excel session, its name will be Book1. If there is another workbook in the same directory with the same name, a message box will appear offering you the opportunity to overwrite the existing file by clicking Yes or to abort the operation by clicking No or Cancel. If you decide not to overwrite the existing file, a Microsoft Visual Basic run-time error message box appears, indicating that the method failed (error 1004). |
If you want to save a workbook with a new name or in a new location, you can use the SaveAs method of the ThisWorkbook property. However, just as clicking the Save toolbar button is much less complicated than clicking File, Save As to open the Save As dialog box and all of its possibilities, so is using the ThisWorkbook.Save method much less complicated than using the ThisWorkbook.SaveAs method. But, truth to tell, using the ThisWorkbook.SaveAs method is fairly straightforward. The SaveAs method has the following full syntax:
expression.SaveAs(FileName, FileFormat, Password, WriteResPassword,
ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru,
TextCodePage, TextVisualLayout, Local)
The parameters are listed in Table 7-3 for your convenience.
| Parameter | Description |
|---|---|
| FileName | The name and path of the file to be saved. |
| FileFormat | The Excel constant representing the file format in which to save the file. There are 44 Excel file format constants, which you can look up in the Visual Basic Editor help system by typing xlFileFormat in the Ask A Question box. |
| Password | Sets a password for the file. The password must be 15 characters or fewer. |
| WriteResPassword | Sets a password for restricting who may write changes to the file, while still allowing the file to be opened as read-only. |
| ReadOnlyRecommended | When set to True, displays a dialog box recommending the user open the file in read-only mode. |
| CreateBackup | When set to True, creates a backup copy of the workbook. |
| AccessMode | Indicates whether the file is saved in exclusive mode (xlExclusive), in no changes mode (xlNoChange), or as a shared file (xlShared). In exclusive mode, only one user may have the workbook open and make changes at a time. Saving a file with no changes leaves the access mode unchanged, while saving a file as a shared file lets more than one user have read/write access to the file at a time. |
| ConflictResolution | Indicates how Excel should handle conflicting changes in a shared workbook by setting ConflictResolution to one of these XlSaveConflictResolution constants: xlUserResolution, the default, which displays the Conflict Resolution dialog box; xlLocalSessionChanges, which causes Excel to automatically accept the local user’s changes; or xlOtherSessionChanges, which causes Excel to accept other changes instead of the local user’s changes. |
| AddToMru | When set to True, adds the file name to the list of most recently used files on the File menu. |
| TextCodePage | A variant, not used in the American English version of Excel, that controls how the characters in a workbook are interpreted and displayed. |
| TextVisualLayout | A variant, not used in the American English version of Excel, that controls how the workbook is displayed within the Excel interface. |
| Local | A Boolean variable that indicates whether to use the local language set in Excel or the local language set in VBA (if different). |
The properties you’ll probably use the most in your work are FileName and AddToMru, with FileFormat and Password in the running for third place in your heart. The AddToMru property, which puts a file on the recently used files list that appears on the File menu, might seem to be an odd choice, but you can use that property to remind yourself which workbooks you need to work with the next time you or one of your colleagues run Excel. For example, if you wrote a macro that updated the values in a series of workbooks that would later need to be reviewed by the president of your company, adding the names of the updated workbooks to the most recently used files list would make it easy for your boss to find the files that need a look.
If you want to save every open workbook, you can write a macro to do just that using a For Each…Next loop, as in the following procedure:
Sub SaveThemAll()
Dim Wkbk as Workbook
For Each Wkbk in Workbooks
If Wkbk.Path <> "" Then Wkbk.Save
Next Wkbk
End Sub
The If…Then statement in the For Each…Next loop checks whether a workbook has an undefined path, meaning that the workbook in question has never been saved. This check is important if you want the procedure to run without human intervention; if someone needs to be on hand to save the workbooks, you might as well save them manually.
A related method that’s available for use with workbooks is the SaveCopyAs method, which saves a copy of the current workbook under a new name. The SaveCopyAs method is a great way to make backup copies of a workbook during a lengthy procedure to guard against data loss, and as part of a general backup and file maintenance policy. The syntax of the procedure is simply this:
ThisWorkbook.SaveCopyAs "path\filename.xls"
| Warning | If you use the SaveCopyAs method to save a file using an existing file name, the macro will overwrite the existing file without asking permission. |
Activating Workbooks
In the Save and SaveAs property discussions, you noticed that the code used the ThisWorkbook object, which refers to the workbook to which the VBA code is attached. The ActiveWorkbook object is related to the ThisWorkbook object in that it refers to a workbook (obviously), but it refers to the workbook you have chosen to act on, not the workbook to which the code is attached. You can change the active workbook by calling the Workbooks collection’s Activate method, as in the following line of code:
Workbooks("2004Q3sales.xls").Activate
Closing Workbooks
When you’re done changing a workbook, it’s a good idea to close it, both to save system resources and to reduce the likelihood that something will happen to the file while it’s open. After all, all it takes is a stray keystroke here or there and the best data can become a meaningless jumble. As with the Save and SaveAs techniques you saw earlier in this section, you can use the ThisWorkbook property of the Application object to invoke the Close method, as in the following statement:
ThisWorkbook.Close
If you want to close another workbook from within a procedure, you can use this type of statement:
Workbooks("name").Close When you’ve reached the end of a macro and want to clean up by saving and closing every open workbook, you can use the following procedure:
Sub CloseAll()
Dim Wkbk as Workbook
For Each Wkbk in Workbooks
If Wkbk.Name <> ThisWorkbook.Name Then
Wkbk.Close SaveChanges:=True
End If
Next Wkbk
ThisWorkbook.Close SaveChanges:=True
End Sub
This procedure checks each workbook to ensure it isn’t the workbook containing the VBA code. If the code were to close its own workbook while any other workbooks were open, any remaining open workbooks wouldn’t be affected because the code would stop running.
| Caution | The procedure does display a Save As dialog box if any of the open workbooks are new. |
Workbook Properties
Even though workbooks are the focal point of Excel, you’ll actually spend less time manipulating workbooks than working with worksheets and cell ranges. Even so, there are a number of workbook properties you can use to help you and your colleagues work with Excel effectively.
Using the ActiveChart Property
Charts are great for visually summarizing data, but if you’re constrained to displaying a chart within a relatively small area and don’t have the room to include a legend or other information, you can use message boxes to display information about the chart that’s selected. The following macro might be attached to a command button on a worksheet offering help to the user on the information in the active chart.
Sub ChartHelp()
ChartChosen = ActiveChart.Name
Select Case ChartChosen
Case "": Exit Sub
Case "Sheet1 Chart 1": MsgBox "This chart shows the sales for 2000-2004."
Case "Sheet1 Chart 2": MsgBox "This chart shows the profits for 2000-2004."
End Select
End Sub
For more information on creating and manipulating charts in VBA, see Chapter 15, “Charts.”
Displaying Drawing Objects
Workbooks are full of data and, regardless of how well you structure the data, it might not always be easy to follow what’s going on. Adding drawing objects, such as text boxes and arrows, lets you call out special features of your worksheets and provide helpful information to your users. If you use drawing objects to provide that sort of information, you also have the option to hide or display the objects at will by using the DisplayDrawingObjects method. The following code assumes you have a series of drawing objects that might obscure your data if left on the worksheet, but would help explain what’s going on if the user could turn them on and off as desired. Figure 7-3 shows the message box produced by the procedure: clicking OK hides the drawing objects, whereas clicking Cancel leaves the objects visible.
Public Sub ShowObjects()
ThisWorkbook.DisplayDrawingObjects = xlDisplayShapes
Answer = MsgBox(Prompt:="Click OK to hide the drawing _
objects.", Buttons:=vbOKCancel + vbQuestion)
If Answer = vbCancel Then
Exit Sub
Else
ThisWorkbook.DisplayDrawingObjects = xlHide
End If
End Sub
Figure 7-3: You can create procedures that let your users decide whether to hide drawing objects or leave them visible.
There are three available Excel constants that can be used as values for the DisplayDrawingObjects property. They are xlDisplayShapes, which shows all shapes in their full glory; xlHide, which hides all shapes; and xlPlaceHolders, which causes Excel to print just the objects on your worksheet.
Managing File Settings
There are two aspects of a workbook’s information you can use when you manipulate the workbook: the workbook’s path and the workbook’s full name. A path is the complete directory listing for a file, such as C:\Excel\Data, while a workbook’s full name would include the workbook’s file name, such as C:\Excel\Data\Q12004.xls. You can refer to these properties of the workbook that contains your macro code using these two methods:
ThisWorkbook.Path
ThisWorkbook.FullName
When you’re working in the Visual Basic Editor and want to add the full name or path of the workbook to which you’re adding code, you can click View, Immediate Window to open the Immediate Window and type MsgBox (ThisWorkbook.FullName) or MsgBox (ThisWorkbook.Path) to find the directory and file name information for your procedures. This information isn’t of great importance if you don’t plan to transfer your code outside of the current workbook, but if you need to refer to this particular workbook from elsewhere, you will need to use the full name of the file. And, while you could get the same information by trying to save the file and clicking the Save In down arrow to find the folder in which your workbook is stored, using the Immediate Window means you don’t have to leave the Visual Basic Editor, which should help you keep your work flowing.
| Note | The full name of a file includes a path, but the path will be blank if the file hasn’t been saved yet. |
Earlier in this chapter you used the Save and SaveAs methods of the ThisWorkbook property to save the files you change using VBA. Excel keeps track of whether a workbook has unsaved changes, which is helpful if you want to check the workbook and save it every time you run a procedure that affects the workbook’s contents, or if you want to close a workbook without saving any of the changes you’ve made (such as when you run a series of formatting routines to highlight different aspects of your data but want everything reset when you’re done). The following code fragment would have Excel close the active workbook without saving any unsaved changes:
ThisWorkbook.Saved = True
ThisWorkbook.Close
Another aspect of saving values in a workbook relates to how extensively you use links to external data sources. For example, if you were part of an enterprise that made a lot of individual sales that you tracked in a Microsoft Access database, you might want to create links from each cell in the workbook to the corresponding cell in the database table. If you had Excel continue its default behavior of recalculating the workbook by checking the values in the database table, you could have a long wait on your hands every time you open the workbook.
The code you use to save the link values is
ThisWorkbook.SaveLinkValues = True
Setting the SaveLinkValues property to False would cause Excel to re-check the values every time the workbook is opened and, if the values were not available, would generate an error.
Requiring a Password to Open a Workbook
Figuring out ways to maintain the integrity of your data in a corporate environment is one of the most important parts of working in the information industry. While you certainly need to protect your data against snoopers who get into your system from the outside or from internal users who gain extra privileges and browse through the intranet, you can also grant anyone access to one of your workbooks but still require them to provide the proper password when they try to save any changes to the workbook.
The following code listing lets a user set a password that will be required to open the workbook. Once someone opens the workbook, they’ll be able to make any changes they want, but only if they know the password!
Sub SetPassword()
Dim strPassword1 As String
Dim strPassword2 As String
strPassword1 = InputBox ("Type a password for the workbook.")
strPassword2 = InputBox ("Re-type the password.")
If strPassword1 <> strPassword2 Then
MsgBox ("The passwords don't match. Please try again.")
Else
ThisWorkbook.Password = strPassword1
MsgBox ("The password is set.")
End If
End Sub
| Important | You probably noticed that the SetPassword procedure required the user to type in the same password twice to set the password for the workbook. When you build routines that restrict access to data, you should always make sure to verify the password is what the user intended it to be. Remember, if the data is important enough to protect, it’s worth it to add extra measures to safeguard the password. |
Another way you can protect a workbook is to prevent users from saving changes unless the user knows the password used to protect the workbook. When a workbook is write-protected, the WriteReserved property is set to True. Writing code to change a write-protect password on the fly is messy, because the user would need to type in the current password and then set a new one, so the following routine checks to make sure the active workbook is not write-protected before allowing the user to set a password users must enter before being allowed to save changes:
Sub SetWritePassword()
Dim strPassword1 As String
Dim strPassword2 As String
strPassword1 = InputBox ("Type a password for changes to be saved.")
strPassword2 = InputBox ("Re-type the password.")
If strPassword1 <> strPassword2 Then
MsgBox ("The passwords don't match. Please try again.")
Else
If ActiveWorkbook.WriteReserved = False Then
ActiveWorkbook.WritePassword = strPassword1
Else MsgBox ("The workbook is already write protected.")
End If
MsgBox ("The password is set.")
End If
End Sub
Protecting Workbooks from Changes
When you use the WritePassword method to protect your workbook, you’re requiring users to know a password so they can open the workbook. Of course, once they have opened the workbook, they can make any changes they like to it. If you want to add a second layer of protection, you can do so by setting a separate password that must be entered before users would be able to make any changes to your workbook. By using the Protect method, you can prevent users from changing the workbook by adding worksheets, deleting worksheets, displaying hidden worksheets (that is, changing the workbook’s structure), and also prevent users from changing the sizes or positions of the windows in your workbook (changing the workbook’s windows).
| Note | When you use the Protect method attached to a Workbook object, the protections you set will apply to the entire workbook. |
The Workbook object’s Protect method has the following syntax:
Protect[Password], [Structure], [Windows]
No need for a table this time! The Password parameter is the password (that’s required), but the Structure and Windows parameters are optional. By default they’re set to False, but if you set them to True, as in the following code example, then the structure and windows layout of the workbook will be protected.
Sub SetProtection()
Dim strPassword1 As String
Dim strPassword2 As String
'First, check to be sure the workbook isn't protected already.
If (ActiveWorkbook.ProtectStructure <> True And _
ActiveWorkbook.ProtectWindows <> True) Then
strPassword1 = InputBox("Type a password to protect the workbook.")
strPassword2 = InputBox("Re-type the password.")
'Verify the passwords are the same
If strPassword1 <> strPassword2 Then
MsgBox ("The passwords don't match. Please try again.")
Else
ActiveWorkbook.Protect Password:=strPassword1, Structure:=True, _
Windows:=True
MsgBox ("The password is set.")
End If
'Back in the part of the routine that checks for protection.
Else
MsgBox ("The workbook is already protected.")
End If
End Sub
 |
When Is an Error Not an Error?
It’s interesting to note that attempts to protect a workbook that’s already protected don’t generate an error message: they just fail. An earlier version of the Sub SetProtection procedure just shown checked for errors and, rather than stopping when the procedure was run against a protected workbook, the routine blithely continued to the end without notifying the user that anything was wrong. The only reliable way to check for protection is to query the ProtectStructure and ProtectWindows properties.
|
If you want to remove the protection from a workbook, you can do so through the Unprotect method. The Unprotect method has a single parameter, the password required to unprotect the workbook.
ActiveWorkbook.Unprotect Password:=password
The following procedure lets the user attempt to unprotect a workbook:
Sub UnprotectThisWkbk()
Dim strPassword As String
If (ActiveWorkbook.ProtectStructure = True Or _
ActiveWorkbook.ProtectWindows = True) Then
strPassword = InputBox ("Type the password to remove protection from _
this workbook.")
ActiveWorkbook.Unprotect Password:=strPassword
Else
MsgBox ("The workbook is not protected.")
End If
End Sub
The UnprotectThisWkbk procedure checks to see if either the ProtectStructure or the ProtectWindows property is set to True because it’s possible to protect one aspect of your workbook and not the other.
|
Creating Passwords That Will Stand the Test of Time
The best passwords are random strings of characters, but random characters are hard to remember. One good method of creating hard-to-guess passwords is to combine elements of two words with a number in between. For example, you might have a password prog#2003exce, which could be read as “programming version 2003 of Excel.” In any event, avoid dictionary words in English or any other language, as they can be found easily by password-guessing programs available on the Internet. The Excel encryption algorithm is strong enough to force a cracker to spend around 30 days on average to find the key that unlocks a workbook, but sensitive financial data has a significantly longer shelf life than a month, so if your data is truly sensitive and you want to guard against theft or accidental disclosure, you should use a stronger commercial encryption program.
|
Workbook Methods
Workbooks are the basic document in Excel, so it’s not surprising that there are a lot of methods you can invoke to take action in your workbooks. You can add a workbook to your list of favorite files and URLs, activate a workbook so you can work with it without naming the workbook in every command, or preview a workbook before you print it.
Activate Method
When you first start writing macros, it’s likely that you’ll work within a single workbook most of the time. The simpler macros you create for tasks such as manipulating the values in a worksheet or backing up a workbook by saving a copy of the file using the SaveCopyAs method all use properties and methods within the active workbook. When you want to start working on several workbooks from within the same macro, however, you need to let the VBA engine know that you’re changing gears. To do so, you use the Workbook object’s Activate method. As an example, the following code snippet would make the workbook named Q42003Sales.xls the active workbook:
Workbooks ("Q42003Sales.xls").Activate | Caution | It’s important to remember that the ThisWorkbook property and the ActiveWorkbook property might refer to different workbooks. ThisWorkbook always refers to the workbook that contains the module with the code you’re executing, but the file referred to by the ActiveWorkbook property can be changed. |
Once you change the active workbook, every method you call using the ActiveWorkbook property will affect the active workbook. For example, if you are working with five workbooks but have just made changes to two of them, you could activate the workbooks in turn, save them, and return to the original workbook, as in the following procedure:
Sub SaveAfterChange()
Workbooks("Q4SalesSummary.xls").Activate
ActiveWorkbook.Save
Workbooks("2004SalesSummary.xls").Activate
ActiveWorkbook.Save
Workbooks("Q42004Sales.xls").Activate
End Sub
AddToFavorites Method
Earlier in this chapter you saw how setting the AddToMru property caused the name of a saved file to be added to the recently used file list at the bottom of the File menu. You can also add a file to the list of Favorites that shows up in Internet Explorer, My Computer (as shown in Figure 7-4), or on the Web toolbar in Excel.
Figure 7-4: The Favorites list contains links to files and hyperlinks you want to remember and access quickly.
| Tip | Make Your Favorites Easy to Find |
You invoke the AddToFavorites method to add a workbook to the Favorites list using any method attached to an object that references a workbook object, as in the following two code fragments:
ActiveWorkbook.AddToFavorites
ThisWorkbook.AddToFavorites
FollowHyperlink Method
One useful way to create help and informational files for the Excel applications you create is to save them as Web pages, which lets you set up a series of hyperlinks users can click to get help on using a form or other object. The following code listing presents the basic syntax of the FollowHyperlink method, with Table 7-4 fleshing out the details of the most-used parameter.
expression.FollowHyperlink(Address, SubAddress, NewWindow, ExtraInfo, Method,
HeaderInfo)
| Parameter | Type | Description |
|---|---|---|
| Expression | Required | An expression that returns a Workbook object. |
| Address | Required String | The address of the target document. |
| SubAddress | Optional Variant | The location within the target document. The default value is the empty string. |
| NewWindow | Optional Variant | True to display the target application in a new window. The default value is False. |
You won’t need to set most of these parameters when you use the FollowHyperlink method, but the NewWindow parameter does come in handy when you want to be sure that the document you open appears in a separate window and doesn’t overwrite any existing information. The parameters that are listed in the code but not in the table are only used if you need to call the Web page using specific Hypertext Transfer Protocol (HTTP) instructions. You'll need to check with your network administrator for the proper settings.
The following example displays the Web page at http://example.microsoft.com in a new window:
ActiveWorkbook.FollowHyperlink Address:="http:
//example.microsoft.com", _ NewWindow:=True
For more information on using hyperlinks and the Web, see Chapter 25, “Excel and the Web.”
Printing and Previewing Workbooks
Printing workbooks is one of the most valuable tasks you can perform because it lets you communicate your data to other individuals in a fixed form that isn’t dependent on a computer. The venerable device from which you read these words (a book) is one implementation of that concept. When you want to give your colleagues the ability to print a copy of a workbook, you use the PrintOut method.
expression.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile,
Collate, PrToFileName)
| Note | The PrintOut method is also available for sheets, charts, objects, and cell ranges. |
The parameters of the PrintOut method correspond to most of the controls in the Print dialog box, shown in Figure 7-5. The only exceptions are the Properties button, which displays the control program for the active printer, and the Find Printer button, which uses the Directory Service (if available) to display the available printers on the network, but neither of those facilities are vital to printing a workbook on a known network configuration.
Figure 7-5: The PrintOut method includes all of the important controls from the Print dialog box.
| Note | You should have an error-handling routine in place to inform the user if the workbook fails to print. If possible, you should also write a routine to print the workbook on another printer and, in any case, inform the user on which printer the procedure printed the workbook. |
Table 7-5 lists and describes the parameters available for use with the PrintOut method. The settings and descriptions are straightforward, with one exception. When you set the Preview parameter to True, Excel displays the workbook in Print Preview mode, within which the user must click the Print button to begin printing.
| Parameter | Type | Description |
|---|---|---|
| Expression | Required | An expression that refers to a workbook, a worksheet, a chart, an object, or a cell range. |
| From | Optional Variant | The number of the page at which to start printing. If this argument is omitted, printing starts at the beginning. |
| To | Optional Variant | The number of the last page to print. If this argument is omitted, printing ends with the last page. |
| Copies | Optional Variant | The number of copies to print. If this argument is omitted, one copy is printed. |
| Preview | Optional Variant | True to have Excel invoke print preview before printing the object. False (or omitted) to print the object immediately. |
| ActivePrinter | Optional Variant | Sets the name of the active printer. |
| PrintToFile | Optional Variant | True to print to a file. If PrToFileName isn’t specified, Excel prompts the user to enter the name of the output file. |
| Collate | Optional Variant | True to collate multiple copies. |
| PrToFileName | Optional Variant | If PrintToFile is set to True, this argument specifies the name of the file you want to print to. |
If you want to take a user directly to Print Preview, you can do so using the PrintPreview method. The PrintPreview method, which also applies to sheets, charts, objects, and cell ranges, has a single parameter: EnableChanges. When the EnableChanges parameter is set to True (the default), the user is able to change the workbook’s page setup and margin settings before printing. When EnableChanges is set to False, the Page Setup and Margins buttons are disabled, but the user can still choose whether to print (by clicking the Print button) or not (by clicking the Close button).
|
| < Day Day Up > |
|
EAN: 2147483647
Pages: 161