Excel Macros For Dummies. Dick Kusleika
Чтение книги онлайн.
Читать онлайн книгу Excel Macros For Dummies - Dick Kusleika страница 15
Название: Excel Macros For Dummies
Автор: Dick Kusleika
Издательство: John Wiley & Sons Limited
Жанр: Программы
isbn: 9781119844457
isbn:
In general, a VBA module can hold three types of code:
Declarations: One or more information statements that you provide to VBA. For example, you can declare module-level variables (variables that apply to all procedures in the module instead of just one), or set some other module-wide options.
Sub procedures: A set of programming instructions that performs some action. All recorded macros are Sub procedures.
Function procedures: A set of programming instructions that returns a single value (similar in concept to a worksheet function, such as Sum).
A single VBA module can store any number of Sub procedures, Function procedures, and declarations. How you organize a VBA module is dependent on how may macros you have. If you have just a few, one module is probably all you need. If you start to get a lot of macros in a workbook, splitting them into well-named folders is best so you can easily find them later. Simply cut and paste any code from one module to another to move it.
Follow these steps to manually add a new VBA module to a project:
1 Select the project’s name in the Project Explorer.
2 Choose Insert ⇒ Module.
Or you can
1 Right-click the project’s name.
2 Choose Insert ⇒ Module from the shortcut menu.
The new module is added to a Modules folder in the Project Explorer (see Figure 2-3). Any modules you create in a given workbook are placed in this Modules folder.
FIGURE 2-3: Code modules are visible in the Project Explorer in a folder called Modules.
If you want to change the name of your module, select the module in the Project Explorer and press F4 to show the Properties window. Modules only have one property, Name, so it's easy to find where to change it.
Removing a VBA module
You may want to remove a code module that is no longer needed. To do so, follow these steps:
1 Select the module’s name in the Project Explorer.
2 Choose File ⇒ Remove xxx, where xxx is the module name.
Or
1 Right-click the module’s name.
2 Choose Remove xxx from the shortcut menu.
Whichever method you choose, Excel asks you if you want to export the module before removing it. Click Yes to create an export file that you can re-import into your project if you find you deleted it in error.
Working with a Code Pane
As you become proficient with VBA, you spend lots of time working in Code panes. Macros that you record are stored in a module, and you can type VBA code directly into a VBA module’s Code pane.
Minimizing and maximizing windows
Code panes are much like workbook windows in Excel. You can minimize them, maximize them, resize them, hide them, rearrange them, and so on. Most people find it much easier to maximize the Code pane that they’re working on. Doing so lets you see more code and keeps you from getting distracted.
To maximize a Code pane, click the maximize button in its title bar (right next to the X). Or, just double-click its title bar to maximize it. To restore a Code pane to its original size, click the Restore button.
Sometimes, you may want to have two or more Code panes visible. For example, you may want to compare the code in two modules or copy code from one module to another. You can arrange the panes manually, or choose Window ⇒ Tile Horizontally or Window ⇒ Tile Vertically to arrange them automatically.
You can quickly switch among Code panes by pressing Ctrl+Tab. If you repeat that key combination, you keep cycling through all the open Code panes. Pressing Ctrl+Shift+Tab cycles through the panes in reverse order.
Minimizing a Code pane gets it out of the way. You can also click the pane's Close button in the title bar to close it completely. (Closing a window just hides it; you won't lose anything.) To open it again, just double-click the appropriate object in the Project Explorer. Working with these Code panes sounds more difficult than it really is.
Getting VBA code into a module
Before you can do anything meaningful, you must have some VBA code in the VBA module. You can get VBA code into a VBA module in three ways:
Use the Excel macro recorder to record your actions and convert them to VBA code.
Enter the code directly.
Copy the code from one module and paste it into another.
Chapter 1 shows you how to create code by using the Excel macro recorder. However, not all tasks can be translated to VBA by recording a macro. You often have to enter your code directly into the module. Entering code directly basically means either typing the code yourself or copying and pasting code you have found somewhere else.
Entering and editing text in a VBA module works as you might expect. You can select, copy, cut, paste, and do other things to the text.
A single line of VBA code can be as long as you like. However, you may want to use the line-continuation character to break up lengthy lines of code. To continue a single line of code (also known as a statement) from one line to the next, end the first line with a space followed by an underscore (_). Then continue the statement on the next line. Here’s an example of a single statement split into three lines:
Selection.Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlGuess, _ Orientation:=xlTopToBottom
This statement would perform exactly the same way if it were entered in a single line (with no line-continuation characters). Notice that the second and third lines of this statement are indented. Indenting is optional, but it helps clarify the fact that these lines are not separate statements.
The VBE has multiple levels of undo and redo. If you delete a statement that you shouldn’t have, click the Undo button on the Standard toolbar (or press Ctrl+Z) until the statement appears СКАЧАТЬ