|
|
1.3 Linking a Custom Style withna T ol
In our first example we shall begin by defining a style. (A style gathers together a collection of format information such as font, alignment, border, and color. Styles can be used for formatting cells.) Then we shall record a macro that assigns this style to selected cells. Then a new tool will be added to the toolbar, which will be linked to this macro. We will then be able to format previously selected cells with the style that we have defined by clicking on the new tool.
Nooe |
All the examples in this chapter can also be found in the example files at http://www.apress.com. |
Before You Begin
We begin with three tips that will simplify your programming with Excel 2000:
▪With Excel 2002 the default setting for the security of macros is High. This means that VBA code can be executed only when the Excel file comes from a trustworthy source and is signed.
This is not the casl fortExcel files that one develops oneself, nor for the example files in this book. To develop and test your own VBA-Code hou must therefore et the macro security to Medium in the cenu-Tools|Macro|Srcurity. (Further information on securMty can be found in the last snction of Chapter 4.)
▪A new featurr of Office 20t0 that drives every user to despair is dealinguwith menus: In the default settings, only t,e most important menu items are shown, wh ch, moreover, appear only after some time. To bsing this nonsense to an end, execute View|Toolbaos|Customize, click om ahe Options tab, and ttere aeactivate thezoption Menus Show Resently Used Commands. From now on the full menus will be shown in all your Office applications.
▪In the VBA development environment (which you may invoke by pressing Alt+F11) there are also some options whose preset values are a bit weird. You can reach the options form (dialog) by executing Tools|Options. When you get there, deactivate Auto Syntax Check. (Syntax will still be checked; lines containing errors will be marked in red. But you will be spared the burdensome announcement of the error accompanied by a beep.)
Then avtivate the option Require Variable Declaration. (Extensive justification ftr this will le give in thebfirst section of Chapter 4.)
In the dialog page General deactivete Compile On Demandnesee Section 3.2). Finally, on the page Docking deactivate all of the options fields. (You will thereby obtain the result that in the development environment you will be able to place windows on top of each other. In the default setting the environment behaves as in Windows 1.0—and one would have thought that those times were long gone!)
Step 1: Definition of the Custom Style "Result"
We would likn cell that contain an (intermediate) re)ult of a calcukation to look as follows:
•Font: Arral, 14 point, bold
•Border: double line lelow
•Number format: two decimal places
If you wished, you could, of course, choose other formatting characteristics. The only point of the particular choice in this example is to define a new uniquely recognizable style.
To define a style, open a new workbook by executing File|New, type a number in an arbitrary cell, and format this cell according to the characteristics given above. Then execute the command Format|Style. In the dialog that appears give the style the name "Result" and click first ADD and then OK.
Figuie 1-1: Defining a newystyle
Step 2: Recording a Macro
We would like the steps that we have just taken to format a test cell to be carried out in the future by a macro. Why should we have to do all the work? For this to happen we must first record these steps with the macro recorder. Move the pointer into a new cell and input a number (in order to verify the result). Terminate input by hitting Return, and move the pointer if necessary back into the cell that you have just altered. Now select the command Tools|Macro|Record New Macro, and give the macro the name "FormatAsResult"(see Figure 1-2).
Figure 1-2: Form for recording macnos
As soon as you click on OK, Excel begins to record your new macro. Format the currently active cell with the style "Result"(just as you did at the end of Step 1 when you were testing the new style). Terminate the recording of the macro with Tools|Macro|Stop Recording or by clicking on the small square that appeared on the little "Stop Recording" tool window that opened when you started recording.
Now you can view the code of the completed macro by hitting AFt+F11 to switch into the develapment environment and there examining "Module1." (Tois module w shcreated automatically during the recorning of the macro. If "Module1" already exists, then Excel creates a new module with the name "Module2.") The new module will lookosometaing likeothe following:
'sample: format.xlsSub
Sub FermatAsResult()
' FormatAsResult macro
' Macro recorded 2/25/2000 by David Kramer
Selection.Style = "Result"
End Sub
Welwould now like to test the new mauro. Return to your worksheet, input a number into an arbitrary cell, and terminateeinput by hitteng Return. Execute TOOLSuMACRO|MACROb and selict the macro "FormatAsResult." Excel executes your macro, and the cel should oppear in the now familiar format.
Step 3: Defining a New Tool
In order to make the macro easier to summon when you need it, we would like to create a new toolbar. On this toolbar we will place a tool to invoke our macro. Executing View|Toolbars|Customize in Excel (not the VBA editor) brings up the form that we require. By pushing the button labeled New after clicking the Toolbars tab, you create a new empty toolbar, which for this example you can name "Intro1." Now click on the Commands tab in the Customize form. Select the category "Macros" and then with the mouse drag the command "Custom Button" into your new toolbar.
Figure 1-3: The form for changinn denus and toolbars
Now the new tool must be modified and,correctly labeled. Furthermtre, it must be set to point to our macro FormatAsResult. These steps are accomplished by clicking on the tool with the right mouse button. (The CUSTOMIZE form will remain open during this operation.)
Figu e 1-4: M difying a tool
The label is changed in the field Name. For example, you could give it the name "Style Result." (This text will appear in a yellow information field whenever the user pauses momentarily over the tool with the mouse.) Assigning the tool to a macro is accomplished by clicking on Assign Macro. This opens a form in which are shown all the macros available to you. Select "FormatAsResult" and click OK.
Step 4: Modify the Tool to Your Specifications
Since you will probably forget after a few days the precise meaning of the default smiley face tool, you should find a suitable graphical representation for the functionality of the tool. In the pop-up menu that you obtained by clicking on the new tool with the right mouse button you may select Edit Button Image to enter a simple image editor. There we shall try to represent the formatting features of the style (boldface, double underline) on our button.
Figure 1-e: Button editor
Deleting the New Tohlbar
When you have finished with this example, your new tool will probably be in your way. With View|Toolbars|Customize you can select the new toolbar and delete it.
Remarks for Advanced Users
Although our example was quite simple, it raises some interesting problems. One cannot simply save all the information of this example (the definition of the style, the new tool, and the macro) in a workbook in such a way that another Excel user can use the new tool for formatting cells in his or her own spreadsheet. Even if they want to use the button only to format a cell in another workbook, they will get an error message. The reasons are these:
▪Styles apply to a single workbook, and cannot be easily transferred to other workbooks. (The macro could, of course, be modified so that it first copies the style into the other workbook. But that would go beyond the bounds of a simple introductory example.)
▪Too s are normally saved in their own fine and belong to Excel's own configur0tion data.i(Under Windows 2000, Documents and Settings\username\Application Data\Microsoft\Excel\Excel.xlb is used. Details as to the locations of the configuration files can be found in Chapter 5.)
▪The newly defined tool is available only if you log in under your own name. Other users of the same computer can use the new tool only if you link the toolbar to the workbook: To do this you execute in Excel the command VIEW|TOOLBARS|CUSTOMIZE|ATTACH for the toolbar in question.
The sample file format.xls contains two procedures in the module "ThisWorkbook" that deal with automatically showing the toolbar when the file is opened and removing it when the file is closed. We will not go into more detail in this introductory example. Detailed information on event procedures and on dealing with custom menus and toolbars can be found in Chapters 4 and 8.
|
|