Locate the workbook in the Project window.
Double-click the project name to display its items, if necessary.
Double-click the ThisWorkbook item.
The VBE displays an empty Code window for the ThisWorkbook object.
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:
The Code window should look like Figure 11-3.
Literature
1. Джон Уокенбах, Excel VBA Programming For Dummies 5 августа 1999 г.
2. Джон Мюллер, VBA for dummies 2003 г.
3. Ричард Мансфилд, Mastering VBA for Microsoft Office 2007 2008 г.
4. Билл Джелен, Трейси Сирстад, Excel 2013 VBA and Macros 2013 г
5. Ричард Мансфилд, Mastering VBA for Office 2010 2010 г
6. Ричард Шеперд, Excel 2007 VBA Macro Programming 2009 г
7. Майк Мак-Грат, EXCEL VBA IN EASY STEPS. 2017 г.
Lecture № 10. VBA Programming Examples
In This Chapter
1. Exploring VBA examples
2. 
Making your VBA code run as fast as possible
Мy philosophy for figuring out how to write Excel macros places heavy emphasis on examples. I find that a good example often communicatesa concept much better than a lengthy description of the underlying theory. Because you’re reading this book, you probably agree with me. This chapter presents several examples that demonstrate common VBA techniques.
I organize these examples into the following categories:
✓ Working with ranges
✓ Changing Excel settings
✓ Working with charts
✓ Speeding up your VBA code
Although you may be able to use some of the examples directly, in most cases you must adapt them to your own needs.
Working with Ranges
Most of your VBA programming probably involves worksheet ranges. (For a refresher course on Range objects, refer to Chapter 8.) When you work with Range objects, keep the following points in mind:
✓ Your VBA doesn’t need to select a range to work with it.
✓ If your code does select a range, its worksheet must be active.
The macro recorder doesn’t always generate the most efficient code. Often, you can create your macro by using the recorder and then edit the code to make it more efficient.
Literature
1. Джон Уокенбах, Excel VBA Programming For Dummies 5 августа 1999 г.
|
|
|
2. Джон Мюллер, VBA for dummies 2003 г.
3. Ричард Мансфилд, Mastering VBA for Microsoft Office 2007 2008 г.
4. Билл Джелен, Трейси Сирстад, Excel 2013 VBA and Macros 2013 г
5. Ричард Мансфилд, Mastering VBA for Office 2010 2010 г
6. Ричард Шеперд, Excel 2007 VBA Macro Programming 2009 г
7. Майк Мак-Грат, EXCEL VBA IN EASY STEPS. 2017 г.
Lecture № 11. UserForm Basics
In This Chapter
1. Finding out when to use UserForms
2. Understanding UserForm objects
3. Displaying a UserForm
UserForm is useful if your VBA macro needs to pause and get some information from a user. For example, your macro may have some options that can be specified in a UserForm. If only a few pieces of information are required (for example, a Yes/No answer or a text string), one of the techniques I describe in Chapter 15 may do the job. But if you need to obtain more information, you must create a UserForm. In this chapter, I introduce you to UserForms. You’ll be pleased to make their acquaintance.
You can make this macro even more useful. For example, it would be nice if the macro could also change the text in the cells to either lowercase or proper case (capitalizing the first letter in each word). One approach is to
create two additional macros — one for lowercase and one for proper case. Another approach is to modify the macro to handle the other options. If you use the second approach, you need some method of asking the user which type of change to make to the cells.
The solution is to display a dialog box like the one shown in Figure 16-1. You create this dialog box on a UserForm in the VBE and display it by using a VBA macro. In the next section, I provide step-by-step instructions for creating this dialog box. Before I get into that, I set the stage with some introductory material.
 |
1
In VBA, the official name for a dialog box is a UserForm. But a UserForm is really an object that contains what’s commonly known as a dialog box. This distinction isn’t important, so I tend to use these terms interchangeably.
|
|
|
Creating UserForms: An Overview
When creating a UserForm, you usually take the following general steps:
Дата добавления: 2019-03-09; просмотров: 256; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!