add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Even if you have never learned programming, this chapter will help you write lines of code easily before you ever need to take a course in programming.
First of all, to set things straight, “macro” is an old term used for Excel’s programming language, before Excel adopted the Visual Basic for Applications language (abbreviated as VBA).
Open the first chapter of the book again. In Chapter 1, Time is Money, Give the Mouse a Rest, you learned to move quickly around a worksheet from cell to cell, to select cells, to move between sheets and more, without using the mouse.
In a moment, you will begin relearning all the topics that were presented in Chapter 1; only this time, you will write the lines of code that make work happen with the keys of the keyboard and without use of the mouse.
Start by selecting a cell. Selecting a cell is the most fundamental action that you carry out in Excel.
The line of code for selecting a single cell:
Explanation: use Range to select a cell or cells, a row or a column.
To select a number of cells:
To select a number of rows:
To select a column:
To select a number of columns:
Now that you know how to write lines of code in the VBA macro language, you are very probably asking yourself where to write the code and how to activate the macro.
Press the keyboard shortcut Alt+F11.
VBE (Visual Basic Editor) opens.
You don’t see a dialog box that resembles the dialog box in the picture?
1. On the left side, you don’t see the VBA Project dialog box (with the names of the files and sheets)? Click the ProjectExplorer icon (note the arrow which points to the 14th icon from the left).
2. The window is gray, and there is no white pane on the right side? Add a module by selecting the name of a file (for example, Book1). Select Insert, Module. The sheet of the module you inserted is added to the workbook Book1.
Adding a macro to a module
For the macro syntax, see the figure below. All of the lines of code are written in the Module sheet, in Sub.
The Sub line contains the name of the macro. The name is written as a series of characters (similar to a defined name for a cell). There are parentheses after the name. At the end of the Sub line, there are the words End Sub.
To make your work easier, write the word Sub, leave a space, and after it, the name of the macro. Press Enter, and both the parentheses and the extra line End Sub are inserted automatically.
In the macro, type one line of code to select a cell in a sheet.
After you type the closing parenthesis of the cell address and type a period, a list opens from which you can select the correct word to complete the action. Find the word Select, and press Enter or use the Tab key to insert the word into the line of code.
Running a macro from the VBE dialog box
Have you finished typing the line of code in the macro? Do you want to run the macro?
With the mouse, select any area within the macro and press F5. That’s right, nothing happens. In order to view the activation of the macro, you must minimize the VBE dialog box on top of the Excel window.
The technique: go back to the Excel window, and press Alt+F11 again. Click the Maximize button, return to the VBE dialog box, and double-click the blue title bar at the top of the dialog box to minimize it.
Now use F8 to run the macro. The F8 key allows you to run the macro one line at a time, and view the results. The F8 key is vital for debugging. The F5 key runs the macro from start to finish.
You have begun to run the macro. Before the macro begins, its syntax is checked for errors and more by the VBA program (Visual Basic for Applications). This is the macro program for Excel and other Office programs. Error messages are displayed so that you can correct the syntax and the errors.
If you have received a Run-time error, click the Debug button. The result – the line containing the bug is colored yellow. Check this line, correct the mistake and continue running the macro using F8, or click the Reset icon (the blue square on the toolbar) to stop running the macro.
While in Debug mode, hover your mouse pointer over any variable to see the current value of the variable.
Running a macro up to a stopping point
As you write more lines of code to build a more complex macro, the number of times you run it to examine it grows. You certainly will not want to check a line of code over and over again, if it has already been checked.
The technique is to run the macro quickly by using the F5 key, up to a particular stopping point, and then to continue running the macro one line at a time by using the F8 key.
Select a line in the code and press F9, or click the gray line to the left of the line of code. The stopping point is indicated by a brown circle, and the line of code is colored brown. To remove this indication, press F9 or click the brown circle.
Stopping a macro
You have run the macro, and it becomes clear that you have made terrible mistakes. You have to stop the running macro. Press Ctrl+Break. Do not forget to click the Reset icon after stopping the macro.
Using an icon to run a macro
Run a macro in an Excel sheet by attaching the macro to a button.
You can attach a macro to various types of objects – a button, an icon, a menu, a picture, a text box, and more.
To insert a button into a sheet, display the Forms toolbar. Select one of the toolbars, right-click, and select Forms. Click the button image, select a cell in the sheet, and left-click to draw the button. When you finish drawing and release the mouse, the Assign Macro dialog box opens. Select the macro and click OK, Click the button to run the macro.
Attaching a macro to an icon on a toolbar
Attach a macro to a button or object such as a picture or text box in order to activate a specific macro, which is activated only within the sheet containing the button.
If you want to create a macro that assists you in your work in Excel, store the macro in a personal workbook, and attach it to an icon.
1. Select one of the toolbars, right-click, and select Customize.
2. Select the Commands tab.
3. From Categories, select Macros.
4. Select Custom Button (with the yellow smiley-face), and drag it onto a toolbar.
5. Select the icon (do not close the Customize dialog box), and right-click.
6. Select Assign macro, select the macro, and click OK.
7. Close the Customize dialog box.
Recording a macro
Recording a macro allows you to type lines of code into the macro in a module without using the keyboard.
The technique for recording a macro is very useful when you begin using the VBA program, do not know the program, or do not have enough experience in writing lines of code in a macro. As you gain more knowledge and experience, you will begin to record lines of code less and less frequently.
To record a macro, display the Visual Basic toolbar, or from Tools, select Macro, Record New Macro.
To insert a macro icon into the Excel menu (recommended and easy to use):
Select one of the toolbars, right-click, and from the shortcut menu, select Customize. Select the Commands tab. From Categories, select Built-In Menus, drag the Macro icon to the Excel menu, and position it after Tools or Data. Click Close.
Begin recording. Click the Record Macro icon (blue circle) on the Visual Basic toolbar, or from the Macros menu, in Macro Name, type a name for the macro. Be sure to type continuous characters; for example, type Macro1 and not Macro 1 (with a space).
Shortcut keys
In Shortcut key, type an upper or lower case letter. The use of shortcut keys allows you to use shortcuts to activate the macro.
Storing a macro
When you record the macro, you also determine where to record it; that is, you determine where you want to save the macro that you record.
Storing a macro in a personal workbook
The personal workbook (Personal.xls) is not an open workbook. It is created immediately after you record a macro, when you choose to save the recording in the personal workbook.
The purpose of the personal workbook is to save a macro in a workbook that will open automatically when you open Excel, thereby enabling you to use the macro that is stored in a module in this workbook. After Excel is started, the workbook is hidden. If you want to display it, select Unhide from the Window menu.
It is not recommended you use the personal workbook for every macro you save. In the personal workbook, save only those macros that will assist you in general tasks, and not in tasks that are performed in a specific sheet or workbook.
After you select the location where you want to store the macro, click OK.
Now perform the same lines that you typed in the macro at the beginning of the chapter. Select cell(s), row(s), or column(s).
Stop recording
You finished recording. Click the Stop Recording button (blue square) on the toolbar, or select Tools, Macro, Stop Recording.
Checking the lines of code you recorded
Press Alt+F11, and double-click the name of the module in VBAProject (Book1) (in the example, the macro was recorded in the Book1 workbook). The result is usually not perfect, to put it mildly. You will usually record unnecessary lines of code, such as selecting a cell or cells that you did not intend to select.
The solution: delete the unnecessary lines, and insert lines of code manually. In short, fix the macro.
Another way to check or enter the macro that you want to edit is to use the Alt+F8 shortcut in the Excel sheet, which provides a list of all the macros that are stored in every open workbook in the Macro dialog box. Select the macro you want to edit, and click Edit.
Visual Basic Editor – VBE
Colors in lines of code
1. Blue – keywords such as Sub, End Sub.
2. Green – information, comments, or a skipped line of code. Select the ‘ (apostrophe) sign before a line of code. The color changes to green, and the macro skips the line while it runs. Comments are useful for explaining your macro in English so that you can recall several months later what you were trying to do.
3. Red – syntax error that must be checked and corrected.
4. Yellow – step mode run of a macro by using the F8 key, or a line containing a bug.
5. Brown – break point for the macro.
Changing the module name
Select a module, click the Properties icon, and type the new name of the module in the Name box.
Note
The name of the module must be a single word, that is, continuous characters.
Be sure to type a name for the module that is different from the name of the macro. Module names and macro names must be unique.
Deleting a module
Select a module, right-click, and select Remove Module1.
Easy Help on any command
Use Help. It is excellent, and you will learn a great deal from it.
In the module, type a word (for example, Close). Select the word, and press F1.
Have you found the solution to your question? Copy the line of code into the module from the help topic.
Using the recording as help
Record every action that you want to turn into a line of code. Select the relevant
lines in the macro you recorded. Copy the lines by clicking Ctrl+C. In the macro
you are creating, select the location to paste, and press Ctrl+V.