Introduction to Excel VBA Programming

 Knowledge

Upon completion of this course you will:

Understand the basic concepts of Excel and VBA language

Know Excel functions, Charts, Macros, PivotTable, Pivot Chart

Know how to use VBA essential tools such as Macro, MsgBox, Objective, Procedure, Control and Userform

Skills

Upon completion of this course you will be able to:

Analyze and visualize data with Pivot table and Pivot Chart 

Develop custom worksheet functions and user-oriented applications 

Knowledge

Upon completion of today’s session, the student will:

Understand the basic concepts of Excel chart

Skill

Upon completion of today’s session, a student will be able to:

Select and use charts

Create, resize, move, and delete charts

Why we need to use Excel Charts

How to use Excel Charts?

How to select the right Excel Charts to display your data

Deleting, Moving, and Resizing Charts

Create a Chart with Two Y-axes and One Shared X-axis

Switch Row/Column, Chart Title, Legend Position, Data Labels 

Lab work

In Microsoft Excel, charts are used to make a graphical representation of any set of data. A chart is a visual representation of data, in which the data is represented by symbols such as bars in a bar chart or lines in a line chart.

A chart is a tool you can use in Excel to communicate data graphically. Charts allow your audience to see the meaning behind the numbers, and they make showing comparisons and trends much easier. In this lesson, you'll learn how to insert charts and modify them so they communicate information effectively.

Select the cells

Create a Chart in Excel 2007 and Newer Versions

Note: In the new versions of Excel, hover the cursor over a chart type or sub-type on the Insert ribbon to display a description of the chart.

Click the Insert tab.

Click the chart type from the Charts section of the ribbon. The sub-type menu displays.

Click the desired chart sub-type. The chart appears on the worksheet.

If you want to create a second chart, click somewhere in the worksheet to "deselect" the current chart first, or the new chart will replace the current chart.

Create a Chart in Excel 2003, 2000, and 98

Note: In older versions of Excel, click the chart type or sub-type in the Chart Wizard to display a description of the chart.

Click Insert | Chart. The Chart Wizard appears.

Step 1: Click the desired chart type in the left column, and click one of the chart sub-types in the right column. Click Next.

Step 2: Excel assumes you wish to keep the series data in rows. You may click "Columns" to see how the chart changes. When finished, click Next.

Step 3: Type a chart title. If you wish to add a title for the axes, do so. Then click Next.

Step 4: Excel assumes you want the chart placed on the worksheet. If you would like the chart placed in a new sheet, click the radio button, type a sheet name, and click Finish.

To select an existing chart, click on its border, or click in an empty space inside the chart. When selecting a chart, be careful not to click on an element inside the chart or that element will be selected instead.

To delete a chart that has just been created, click the Excel Undo button. To delete an existing chart, select the chart and press the Delete key, or right-click and select Cut.

To move a chart to a different place on the worksheet, select the chart and drag it to the desired location.

To move a chart to a new or different spreadsheet in the same workbook, select the chart, right-click, and select Move Chart. Then choose the sheet or type in a new sheet name, and click OK

To resize a chart, select the chart and drag any of the chart's corners.

For best results, keep the Excel chart simple and uncluttered. It is better to use multiple charts to express patterns and relationships between data than to use one chart that is too busy and over-complicated.

A colorful chart is ideal for online presentations or for printing on a color printer. But shades of gray are best for monochrome printing of charts

For best results, keep the Excel chart simple and uncluttered. It is better to use multiple charts to express patterns and relationships between data than to use one chart that is too busy and over-complicated.

A colorful chart is ideal for online presentations or for printing on a color printer. But shades of gray are best for monochrome printing of charts.

Here are some of the types of charts that you can create in Excel.

A bar chart is a graph that shows horizontal bars with the axis values for the bars displayed on the bottom of the graph.

It is a graphical object used to represent the data in your Excel spreadsheet.

You can use a bar chart when:

You want to compare values across categories.

The category text is long and difficult to display in a column chart.

A column chart is a graph that shows vertical bars with the axis values for the bars displayed on the left side of the graph.

It is a graphical object used to represent the data in your Excel spreadsheet.

You can use a column chart when:

You want to compare values across categories.

A pie chart is a circle that is divided into slices and each slice represents a proportion of the whole.

It is a graphical object used to represent the data in your Excel spreadsheet that uses 1 series of data to create the graph.

You can use a pie chart when:

You want to show numbers as a proportion of the whole (ie: the numbers equal 100%).

There are a limited number of pie slices. If there are too many pie slices, then a pie chart is not a recommended graph to use.

A line chart is a graph that shows a series of data points connected by straight lines.

It is a graphical object used to represent the data in your Excel spreadsheet.

You can use a line chart when:

You want to show a trend over time (such as days, months or years). In this case, the time values would be your categories.

The order of your categories (ie: time values) is important.

Select the cells you want to chart, including the column titles and row labels. These cells will be the source data for the chart

Click the Insert tab.

In the Charts group, select the desired chart category (Column, for example).

Select the desired chart type from the drop-down menu (Clustered Column, for example).

The chart will appear in the worksheet.

This Excel tutorial explains how to create a chart with two y-axes and one shared x-axis in Excel 2007 (with screenshots and step-by-step instructions).

Question: How do I create a chart in Excel that has two Y-axes and one shared X-axis in Microsoft Excel 2007?

Answer: First, select the Insert tab from the toolbar at the top of the screen. In the Charts group, click on the Column button and select the first chart (Clustered Column) under 2-D Column.

A blank chart object should appear in your spreadsheet. Right-click on this chart object and choose "Select Data..." from the popup menu.

When the Select Data Source window appears, we need to enter the data that we want to graph. In this example, we want column A to represent our X-axis and column C to represent our primary Y-axis (left side) and column E to represent our secondary Y-axis (right side). One more complication to this example is that our data is not all in adjacent cells, so we need to select columns A, C, and E separated by commas.

To do this, highlight cells A2 to A7 then type a comma, then highlight cells C2 to C7, then type another comma, and the highlight cells E2 to E7. You should see the following in your Chart data range box:

=Sheet1!$A$2:$A$7,Sheet1!$C$2:$C$7,Sheet1!$E$2:$E$7

Click on the OK button.

Now, right-click on one of the data points for Series2 and select "Change Series Chart Type" from the popup menu.

When the Change Chart Type window appears, select the 4th chart under the Line Chart section. Click on the OK button

Now when you view the chart, you should see that Series 2 has changed to a line graph. However, we still need to set up a secondary Y-axis as Series 2 is currently using the primary Y-axis to display its data.

To do this, right-click on one of the data points for Series 2 and select "Format Data Series" from the popup menu

When the Format Data Series window appears, select the "Secondary Axis" radio button. Click on the Close button.

Now, if you want to add axis titles, select the chart and a Layout tab should appear in the toolbar at the top of the screen.

Click on the Layout tab. Then select Axis Titles > Primary Horizontal Axis Title > Title Below Axis.

An Axis Title at the bottom of the graph should appear, just overwrite "Axis Title" with the text that you'd like to see.

Next, under the Layout tab in the toolbar, select Axis Titles > Primary Vertical Axis Title > Horizontal Title.

An Axis Title to the left of the graph should appear, just overwrite "Axis Title" with the text that you'd like to see.

Next, under the Layout tab in the toolbar, select Axis Titles > Secondary Vertical Axis Title > Horizontal Title.

Now, you should see that your chart is designed with a common X-axis and 2 different Y-axes.

Select the range A1:D7.

2. On the Insert tab, in the Charts group, choose Line, and select Line with Markers

1. Select the chart.

2. On the Insert tab, in the Charts group, choose Column, and select Clustered Column

If you want the animals, displayed on the vertical axis, to be displayed on the horizontal axis instead, execute the following steps.

1. Select the chart. The Chart Tools contextual tab activates.

2. On the Design tab, click Switch Row/Column

To add a chart title, execute the following steps.

1. Select the chart. The Chart Tools contextual tab activates.

2. On the Layout tab, click Chart Title, Above Chart.

3. Enter a title. For example, Population.

By default, the legend appears to the right of the chart. To move the legend to the bottom of the chart, execute the following steps.

1. Select the chart. The Chart Tools contextual tab activates.

2. On the Layout tab, click Legend, Show Legend at Bottom

You can use data labels to focus your readers' attention on a single data series or data point.

1. Select the chart. The Chart Tools contextual tab activates.

2. Click an orange bar to select the Jun data series. Click again on an orange bar to select a single data point.

3. On the Layout tab, click Data Labels, Outside End

Q.1 How do we create a chart in excel 2007 and newer versions?

Q.2 How can we resize a chart?

Create a table with the columns: Student ID, Student Name, Year of Passing, Subject, Marks, Grade, CGPA.

Using the above table, create a combination chart to compare each student with their marks and respective CGPA. Also create a bar chart to see no. of students who passed/failed across years.

Lesson 2 PivotTable and PivotChart

Knowledge

Upon completion of today’s session, the student will:

Know PivotTable and PivotChart

Skill

Upon completion of today’s session, a student will be able to:

To analyze and visualize data with PivotTable and PivotChart

Create a PivotTable

Use the Field List to arrange fields in a PivotTable

Change the source data for a PivotTable

Update (refresh) data in a PivotTable  

Filter PivotChart 

Change PivotChart Type

Lab work

Homework exercises

The PivotTable is one of Excel's most powerful functions. Powerful, because it can help you summarize and make sense of large data sets. A PivotTable is a report that lets you, "summarize, analyze, explore, and present a summary" of your data. And it is particularly useful "when you have a long list of figures to sum, and aggregated data or subtotals would help you look at the data from different perspectives and compare figures of similar data."

 In other words, a PivotTable lets you extract meaning from that seemingly endless jumble of numbers on your screen. And more specifically, it lets you group your data together in different ways so you can more easily make comparisons

Select the table or cells (including column headers) containing the data you want to use.

From the Insert tab, click the PivotTable command. The Create PivotTable dialog box will appear. Choose your settings, then click OK. A blank PivotTable and Field List will appear on a new worksheet. Once you create a PivotTable, you'll need to decide which fields to add. Each field is simply a column header from the source data. In the PivotTable Field List, check the box for each field you want to add

In our example, we want to know the total amount sold by each salesperson. So we'll check the Salesperson and Order Amount fields. The selected fields will be added to one of the four areas below the Field List. In our example, the Salesperson field has been added to the Rows area, while the Order Amount has been added to the Values area. Alternatively, you can click, hold, and drag a field to the desired area.

The PivotTable field list appears. To get the total amount exported of each product, drag the following fields to the different areas.

1. Product Field to the Row Labels area.

2. Amount Field to the Values area.

3. Country Field to the Report Filter area.

Click the PivotTable report.

On the Options tab, in the Data group, click Change Data Source, and then click Change Data Source. ...

Do one of the following: ...

To use a different connection, click select a Use an external data source, and then click Choose Connection.

On the Options tab, in the Data group, do one of the following:

To update the information to match the data source, click the Refresh button, or press ALT+F5. ...

To refresh all PivotTables in the workbook, click the Refresh button arrow, and then click Refresh All.

Create a PivotChart for an existing PivotTable. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon. Click Analyze > PivotChart. In the Insert Chart dialog box, click the chart type and chart subtype you want. You can use any chart type except an XY (scatter), bubble, or stock chart.

Click the arrow in the cell with the item currently displayed in the report filter displayed on the PivotTable report or PivotChart Filter Pane.

Do one of the following: Select a single item Click the item that you want.

On the Design tab, click Change Chart Type. Note: pie charts always use one data series (in this case, Apple). To get a PivotChart of a country, swap the data over the axis. Select the chart.

Create the PivotTable and then click any cell in the PivotTable on which you want to base the chart.

Click the PivotChart button in the Tools group of the PivotTable Tools Options tab.

The Insert Chart dialog box appears.

You have many design choices for your PivotChart.

Click the thumbnail of the type of chart you want to create.

Click OK. 

Excel displays the PivotChart in the worksheet.

How to Create an Excel PivotTable

There are several ways to build a PivotTable. Excel has logic that knows the field type and will try to place it in the correct row or column if you check the box. For example, numeric data such as Precinct counts tends to appear to the right in columns. Textual data, such as Party would appear in rows.

While you can simply check fields to display and let Excel build your PivotTable, I prefer to use the “drag and drop” method. This is partly because I like to visualize my data in columns and rows. I think it may also be easier if you have fields, which can appear to be numbers like a precinct value.

Open your original spreadsheet and remove any blank rows or columns.

Make sure each column has a heading, as it will be carried over to the Field List.

Make sure your cells are properly formatted for their data type.

Highlight your data range

Click the Insert tab.

Select the PivotTable button from the Tables group.

Select PivotTable from the list.

The Create PivotTable dialog appears.

Double-check your Table/Range: value.

Select the radio button for New Worksheet.

Click OK.

A new worksheet opens with a blank PivotTable. You’ll see that the fields from our source spreadsheet were carried over to the PivotTable Field List.

Drag an item such as PRECINCT from the PivotTable Field List down to the Row Labels quadrant. The left side of your Excel spreadsheet should show a row for each precinct value. You should also see a check mark appear next to PRECINCT.

The next step is to ask what you would like to know about each precinct. I’ll drag the PARTY field from the PivotTable Field List to the Column Labels quadrant. This will give an extra column for each party. Note that you won’t see any numerical data.

To see the count for each party, I need to drag the same field to the Values quadrant. In this case, Excel determines I want a Count of PARTY. I could double-click the entry and choose another Field Setting. Excel has also added Grand Totals.

Q. 1 How do we refresh data in a PivotTable? 

Q. 2 How will you change PivotChart type?

Q.3 How do you create and use a pivot table?

Q.4 How do you create a PivotChart from an existing PivotTable, and another PivotChart from other source, and how to work with the PivotChart?

Solve the questions based on the data and questions listed in the attached Excel file.

Lesson 3 Introduction to Excel VBA Programming

Knowledge

Upon completion of today’s session, the student will:

Understand the basic concepts of VBA language

Skill

Upon completion of today’s session, a student will be able to:

Records, test, and modify Macros.

Work with Visual Basic Editor.

What is VBA? What can VBA do?

What is Macro?

Recording, Testing, Examining, Modifying Macro

Working with the Visual Basic Editor

Working with the Project Window, Code Window

Customizing the VBA Environment 

Lab work

Homework Exercises

VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft — you know, the company that tries to get you to buy a new version of Windows every few years. Excel, along with the other members of Microsoft Office, includes the VBA language (at no extra charge). In a nutshell, VBA is the tool that people like you and me use to develop programs that control Excel.

Inserting a bunch of text 

Automating a task you perform frequentl

Automating repetitive operations

Creating a custom command

Creating a custom button

Developing new worksheet functions

Creating custom add-ins for Excel

Creating complete, macro-driven  applications 

If you often need to enter your company name, address, and phone number in your worksheets, you can create a macro to do the typing for you. You can extend this concept as far as you like. For example, you might develop a macro that automatically types a list of all salespeople who work for your company.

If you need to perform the same action on, say, 12 different Excel workbooks, you can record a macro while you perform the task on the first workbook and then let the macro repeat your action on the other workbooks. The nice thing about this is that Excel never complains about being bored. Excel’s macro recorder is similar to recording live action on a video recorder. But it doesn’t require a camera, and the battery never needs to be recharged

Do you often issue the same sequence of Excel menu commands? If so, save yourself a few seconds by developing a macro that combines these commands into a single custom command, which you can execute with a single keystroke or button click. You probably won’t save that much time, but you’ll probably be more accurate. And the guy in the next cubicle will be really impressed.

You can customize your Quick Access toolbar with your own buttons that execute the macros you write. Office workers tend to be very impressed by buttons that perform magic. And if you really want to impress your fellow employees, you can even add new buttons to the Ribbon.

Although Excel includes hundreds of built-in functions (such as SUM and AVERAGE), you can create custom worksheet functions that can greatly simplify your formulas. I guarantee you’ll be surprised by how easy this is. (I show you how to do this in Chapter 20.) Even better, the Insert Function dialog box displays your custom functions, making them appear built-in. Very snazzy stuff.

You’re probably familiar with some of the add-ins that ship with Excel. For example, the Analysis ToolPak is a popular add-in. You can use VBA to develop your own special-purpose add-ins. I developed my Power Utility Pak add-in by using only VBA, and people all around the world pay me real money so they can use it.

If you’re willing to spend some time, you can use VBA to create large-scale applications complete with a custom Ribbon tab, dialog boxes, on-screen help, and lots of other accoutrements. This book doesn’t go quite that far, but I’m just telling you this to impress you with how powerful VBA really is.

Just like you and I, Excel has a language too, the one it can speak and understand. This language is called as VBA (Visual Basic for Applications).

When you tell instructions to Excel in this VBA language, Excel can do what you tell it. Thus enabling you to program Excel so that you can automate a boring report, format a big&ugly chart, clean-up some messy data or just play some random noises.

A macro is nothing but a set of instructions you give Excel in the VBA language.

✓ You perform actions in VBA by writing (or recording) code in a VBA module. You view and edit VBA modules by using the Visual Basic Editor (VBE).

Getting Started with Excel VBA Programming 

 ✓ A VBA module consists of Sub procedures. A Sub procedure has nothing to do with underwater vessels or tasty sandwiches. Rather, it’s a chunk of computer code that performs some action on or with objects (discussed in a moment). The following example shows a simple Sub procedure called AddEmUp. This amazing program displays the result  of 1 plus 1: Sub AddEmUp()

    Sum = 1 + 1

    MsgBox “The answer is “ & Sum

 End Sub

✓ A Function procedure returns a single value. You can call it from another VBA procedure or even use it as a function in a worksheet formula. An example of a Function procedure (named AddTwo) follows. This Function accepts two numbers (called arguments) and returns the sum of those values:

 Function AddTwo(arg1, arg2)

    AddTwo = arg1 + arg2

 End Function

A Function procedure that doesn’t work correctly is said to be dysfunctional.

Excel provides dozens and dozens of objects that you can manipulate. Examples of objects include a workbook, a worksheet, a cell range, a chart, and a shape. You have many more objects at your disposal, and you can manipulate them by using VBA code. 

✓ Objects are arranged in a hierarchy. Objects can act as containers for other objects. At the top of the object hierarchy is Excel. Excel itself is an object called Application. The Application object contains other objects such as Workbook objects and Add-In objects. The Workbook object can contain other objects, such as Worksheet objects and Chart objects. A Worksheet object can contain objects such as Range objects and PivotTable objects. The term object model refers to the arrangement of these objects. (Object model mavens can find out more in Chapter 4.) 

✓ Objects of the same type form a collection. For example, the Worksheets collection consists of all the worksheets in a particular workbook. The Charts collection consists of all Chart objects in a workbook. Collections are themselves objects.

You refer to an object by specifying its position in the object hierarchy, using a dot (a.k.a., a period) as a separator. For example, you can refer to the workbook Book1.xlsx as Application.Workbooks(“Book1.xlsx”)

  This refers to the workbook Book1.xlsx in the Workbooks collection. The Workbooks collection is contained in the Application object (that  is, Excel). Extending this to another level, you can refer to Sheet1 in Book1.xlsx as

Application.Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”)

  As shown in the following example, you can take this to still another level and refer to a specific cell (in this case, cell A1):

Application.Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1”)

✓ If you omit specific references, Excel uses the active objects. If Book1. xlsx is the active workbook, you can simplify the preceding reference as follows: Worksheets(“Sheet1”).Range(“A1”)

  If you know that Sheet1 is the active sheet, you can simplify the reference even more: Range(“A1”)

You can think of a property as a setting for an object. For example, a Range object has such properties as Value and Address. A Chart object has such properties as HasTitle and Type. You can use VBA to determine object properties and also to change properties. ✓ You refer to a property of an object by combining the object name with the property name, separated by a dot. For example, you can refer to the Value property in cell A1 on Sheet1 as follows: Worksheets(“Sheet1”).Range(“A1”).Value

✓ You can assign values to variables. A variable is a named element that stores information. You can use variables in your VBA code to store such things as values, text, or property settings. To assign the value in cell A1 on Sheet1 to a variable called Interest, use the following VBA statement: Interest = Worksheets(“Sheet1”).Range(“A1”).Value

✓ Objects have methods. A method is an action Excel performs with an object. For example, one of the methods for a Range object is ClearContents. This aptly named method clears the contents of the range. ✓ You specify a method by combining the object with the method, separated by a dot. For example, the following statement clears the contents of cell A1: Worksheets(“Sheet1”).Range(“A1”).ClearContents

✓ VBA includes all the constructs of modern programming languages, including variables, arrays, and looping.

In other words, if you’re willing to spend a little time mastering the ropes, you can write code that does some incredible things.

The Visual Basic Editor is a program within Excel that allows you to communicate with Excel. We will open it and start by setting it up so that working within it becomes easy and efficient.

Print this page, open Excel and open a new workbook (Book1).

On your keyboard press the "ALT" key (left of the space bar) and hold, strike the "F11"key (the F11 key is at the top of your keyboard).You now see the Visual Basic Editor. Again press "ALT/F11" and you are back into Excel. Use the "ALT/F11" key to go from Excel to the VBA and back.

When you first open the VBE you will see is a window somewhat like the image below.

If there are any open windows within the VBE like in the image below click on the Xs to close them and see a gray rectangle filling up the bottom part of the screen like in the image above.

To be efficient when working with the VBE there should always be 3 windows showing like in the image below;  the Project Window (1),  the Code Window ( 2), and the Properties Window (3), arranged as in the image below. You can resize the windows by left-clicking where the red stars are, holding and moving sideways or up and down. We will study each of the three windows in lessons 2, 3 and 4 but first we will set them up in the VBE

Remember that you will perform this task only once as each time you will open the VBE it will remain setup.

Step 1: Close all the windows that are open in the VBE to end up with this:

Step 2: Go to the menu bar "View" and click "Project Explorer". The result will be somewhat like the image below

If the project window already appears as a column on the left side of the screen there is nothing else that you have to do for now. If the project window appears in the middle of the gray area like above, right-click in the white space in the middle of the project window and check "Dockable". Then click on the top blue bar of the Project window, hold and drag it left until the cursor (white arrow) touches the middle of the left side of the screen. When you let go of the mouse button the end result should be like shown in the image below. Congratulations you have setup the first major window of the VBE.

Step 3: Move your cursor on the line separating the project window and the gray rectangle. When it turns to two small parallel lines and arrows click, hold and move the lines sideways. Resize the two windows as you want them.

Step 4: Go back to the menu bar "View" and click "Properties Window". The Properties window will appear somewhat like in the image below

If the Properties window is already located below the Project window there is nothing left to do. If it shows like in the image above, right-click in the white space in the middle of the Properties window and check "Dockable". Then click on the top blue bar of the Properties window and drag it left and down until the cursor (white arrow) touches the center of the bottom of the Project window. When you let go of the mouse button the end result should be as the image below. Congratulations you have setup the second major window of the VBE.

Step 5: Move your cursor on the line separating the project window and the properties window. When it turns to two small parallel lines and arrows click, hold and move the lines vertically. Resize the two windows as you want them.

Step 6: To add the code window to the setup, you just have to double click on the name of a component in the Project window (Sheet1, Sheet2, Sheet3 or ThisWorkbook) and its code window appears within the gray rectangle. You can maximize any Code window by clicking on its "Maximize" button  Maximize button.

The final result looks like the image below. The words "Option Explicit" might not be present in your Code window. We will address this issue later in the lesson on variables (Lesson 19). You might also have a VBAProject named FUNCRES.XLA or FUNCRES.XLAM in the project window. Forget about this project for now.

Step 6: Now go to Excel and close it. Re-open Excel, go to the VBE (ALT/F11) and you will see that the VBE setup persists. Congratulations, you are now ready to work in the Visual Basic Editor.

We will discover more about each of these three windows in chapters 2 (Project Window), 3 (Properties Window) and 4 (Code Windows).

Remember that you can use "ALT/F11" to navigate from Excel to the VBE and back.

Close the VBE and close Excel without saving anything

Recording the Macro Here comes the hands-on part. Follow these instructions carefully:

1. Select a cell — any cell will do. 

2. Choose Developer➪Code➪Record Macro or click the macro recording button on the status bar.  The Record Macro dialog box appears, as shown in Figure

3. Enter a name for the macro.  Excel provides a default name (something like Macro1), but it’s better to use a more descriptive name. NameAndTime (with no spaces) is a good name for this macro. 

4. Click in the Shortcut Key box and enter Shift+N (for an uppercase N) as the shortcut key.  Specifying a shortcut key is optional. If you do specify one, then you can execute the macro by pressing a key combination — in this case, Ctrl+Shift+N. 

5. Make sure the Store Macro In setting is This Workbook.

6. You can enter some text in the Description box, if you like.  This step is optional. Some people like to describe what the macro does (or is supposed to do). 

7. Click OK.  The Record Macro dialog box closes, and Excel’s macro recorder is turned on. From this point, Excel monitors everything you do and converts it to VBA code. 

8. Type your name in the active cell. 

9. Move the cell pointer to the cell below and enter this formula: =NOW()  The formula displays the current date and time. 

10. Select the formula cell and press Ctrl+C to copy that cell to the Clipboard. 

11. Choose Home➪Clipboard➪Paste➪Values (V).  This command converts the formula to its value. 

12. With the date cell selected, press Shift+up arrow to select that cell and the one above it (which contains your name). 13. Use the controls in the Home➪Font group to change the formatting to Bold and make the font size 16 point. 14. Choose Developer➪Code➪Stop Recording.  The macro recorder is turned off.

Congratulations! You just created your first Excel VBA macro. You may want to phone your mother and tell her the good news

Now you can try out this macro and see whether it works properly. To test your macro, move to an empty cell and press Ctrl+Shift+N.

In a flash, Excel executes the macro. Your name and the current date and time are displayed in large, bold letters.

 Another way to execute the macro is to choose Developer➪Code➪Macros (or press Alt+F8) to display the Macros dialog box. Select the macro from the list (in this case, NameAndTime) and click Run. Make sure you select the cell that will hold your name before executing the macro.

You’ve recorded a macro, and you’ve tested it. If you’re a curious type, you’re probably wondering what this macro looks like. And you might even wonder where it’s stored.

Remember when you started recording the macro? You indicated that Excel should store the macro in This Workbook. The macro is stored in the workbook, but you need to activate the Visual Basic Editor (VBE, for short) to see it.

Follow these steps to see the macro:

 1. Choose Developer➪Code➪Visual Basic (or press Alt+F11).  The Visual Basic Editor program window appears, as shown in Figure 2-3. This window is highly customizable, so your VBE window may look a bit different. The VBE program window contains several other windows and is probably very intimidating. Don’t fret; you’ll get used to it. 

2. In the VBE window, locate the window called Project.  The Project window (also known as the Project Explorer window) contains a list of all workbooks and add-ins that are currently open. Each project is arranged as a tree and can be expanded (to show more information) or contracted (to show less information).   The VBE uses quite a few different windows, any of which can be either open or closed. If a window isn’t immediately visible in the VBE, you can choose an option from the View menu to display the window. For instance, if the Project window is not visible, you can choose View➪Project Explorer (or press Ctrl+R) to display it. You can display any other VBE window in a similar manner. I explain more about the components of the Visual Basic Editor in Chapter 3. 

3. Select the project that corresponds to the workbook in which you recorded the macro.  If you haven’t saved the workbook, the project is probably called VBAProject (Book1). 

4. Click the plus sign (+) to the left of the folder named Modules.  The tree expands to show Module1, which is the only module in the project. 

5. Double-click Module1.  The VBA code in that module is displayed in a Code window.

As you might expect, not only can you view your macro in the VBE, you can also change it. Even though you probably have no idea what you’re doing at this point, I bet you can make these changes to the code:

 ✓ Change the name that’s entered into the active cell. If you have a dog, use your dog’s name.

 ✓ Change the font name or size. ✓ See if you can figure out the appropriate location for this new statement that makes the cells italic: Selection.Font.Italic = True

 Working in a VBA code module is much like working in a word-processing document (except there’s no word wrap, and you can’t format the text). On second thought, I guess it’s more like working in Windows Notepad. You can press Enter to start a new line, and the familiar editing keys work as expected.

After you’ve made your changes, jump back to Excel and try out the revised macro to see how it works. Just as you can press Alt+F11 in Excel to display the VBE, you can press Alt+F11 in the VBE to switch back to Excel.

The VBE is a separate application where you write and edit your VBA macros. It works seamlessly with Excel. By seamlessly, I mean that Excel takes care of opening the VBE when you need it.

In Excel 2013, every workbook displays in a separate window. However, there is only one VBE window, and it works with all open Excel windows.

 You can’t run the VBE separately; Excel must be running in order for the VBE to run.

The quickest way to activate the VBE is to press Alt+F11 when Excel is active. To return to Excel, press Alt+F11 again. Or, you can just click the Close button in the VBE’s title bar. When the VBE window closes, Excel is activated.

 You can also activate the VBE by using the Developer➪Code➪Visual Basic command. If you don’t have a Developer tab at the top of your Excel window, refer to the beginning of Chapter 2 where I explain how to get that handy Developer tab to show up.

Figure shows the VBE program, with some of the key parts identified. Because so much is going on in the VBE, I like to maximize the window to see as much as possible.

Chances are your VBE program window won’t look exactly like what you see in Figure. The VBE contains several windows, and it’s highly customizable. You can hide windows, rearrange windows, dock windows, and so on.

Actually, the VBE has even more parts than are shown in Figure 3-1. I discuss these additional components throughout the book, when they become relevant.

The VBE menu bar works just like every other menu bar you’ve encountered. It contains commands that you use to do things with the various components in the VBE. You also find that many of the menu commands have shortcut keys associated with them.

The VBE also features shortcut menus. You can right-click virtually anything in the VBE and get a shortcut menu of common commands.

Toolbar The Standard toolbar, which is directly under the menu bar by default (refer to Figure 3-1), is one of four VBE toolbars available. You can customize the toolbars, move them around, display other toolbars, and so on. If you’re so inclined, use the View➪Toolbars command to work with VBE toolbars. Most people (including me) just leave them as they are.

The Project window displays a tree diagram that shows every workbook currently open in Excel (including add-ins and hidden workbooks). Double-click items to expand or contract them within the outline. I discuss this window in more detail in the upcoming “Working with the Project Window” section.

If the Project window is not visible, press Ctrl+R or use the View➪Project Explorer command. To hide the Project window, click the Close button in its title bar. Or right-click anywhere in the Project window and select Hide from the shortcut menu.

Code window A Code window is where you put your VBA code. Every object in a project has an associated Code window. To view an object’s Code window, doubleclick the object in the Project window. For example, to view the Code window for the Sheet1 object in Book1, double-click Sheet1 in the VBAProject for Book1. Unless you’ve added some VBA code, the Code window will be empty.

You find out more about Code windows later in this chapter’s “Working with a Code Window” section.

Immediate window The Immediate window may or may not be visible. If it isn’t visible, press Ctrl+G or use the View➪Immediate Window command. To close the Immediate window, click the Close button in its title bar (or right-click anywhere in the Immediate window and select Hide from the shortcut menu).

When you’re working in the VBE, each Excel workbook and add-in that’s open is a project. You can think of a project as a collection of objects arranged as an outline. You can expand a project by clicking the plus sign (+) at the left of the project’s name in the Project window. Contract a project by clicking the minus sign (–) to the left of a project’s name. Or you can double-click the items to expand and contract them.

 If a project is password-protected, you’re prompted for the password when you double-click a project name. If you don’t know the password, you can’t expand the project — which means that you can’t view or modify any part of the project.

Figure 3-2 shows a Project window with three projects listed: an add-in named pup7.xlam, a workbook named investments.xlsm, and the Personal Macro Workbook (which is always named PERSONAL.XLSB). Of the three, only the investments.xlsm 

Every project expands to show at least one node called Microsoft Excel Objects. This node expands to show an item for each sheet in the workbook (each sheet is considered an object) and another object called ThisWorkbook (which represents the Workbook object). If the project has any VBA modules, the project listing also shows a Modules node. And, as you see in Part IV, a project may also contain a node called Forms, which contains UserForm objects (which hold custom dialog boxes).

The concept of objects may be a bit fuzzy for you. However, I guarantee that things become much clearer in subsequent chapters. Don’t be too concerned if you don’t understand what’s going on at this point

Adding a new VBA module Follow these steps to add a new VBA module to a project:

 1. In the VBE, select the project’s name in the Project window. 2. Choose Insert➪Module.

Or

 1. Right-click the project’s name. 2. Choose Insert➪Module from the shortcut menu.

 When you record a macro, Excel automatically inserts a VBA module to hold the recorded code. Which workbook holds the module for the recorded macro depends on where you chose to store the recorded macro, just before you started recording.

Removing a VBA module Sometimes you need to remove a VBA module from a project. For example, it may contain code that you no longer need, or it’s empty because you inserted the module and then changed your mind. To remove a VBA module from a project:

 1. Select the module’s name in the Project window. 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.  Excel, always trying to keep you from doing something you’ll regret, will ask if you want to export the code in the module before you delete it. Almost always, you don’t. (If you do want to export the module, see the next section.)

You can remove VBA modules, but there is no way to remove the other code modules — those for the Sheet objects or ThisWorkbook

Exporting and importing objects Every object in a VBA project can be saved to a separate file. Saving an individual object in a project is known as exporting. It stands to reason that you can also import objects to a project. Exporting and importing objects might be useful if you want to use a particular object (such as a VBA module or a UserForm) in a different project. Or, maybe you want to send a coworker a copy of a VBA module, which she can then import into her project.

Follow these steps to export an object:

 1. Select an object in the Project window. 2. Choose File➪Export File or press Ctrl+E.  You get a dialog box that asks for a filename. Note that the object remains in the project; only a copy of it is exported. Excel provides the file extension for you, and the extension depends on what type of object you’re exporting. In all cases, the result is a text file. If you’re so inclined, you can open it with a text editor and take a look.

Importing a file to a project goes like this:

 1. Select the project’s name in the Explorer window. 2. Choose File➪Import File or press Ctrl+M.  You get a dialog box that asks for a file. Locate the file and click Open. You should only import a file if the file was exported by using the File➪Export File command.

When you’re working in the VBE, each Excel workbook and add-in that’s open is a project. You can think of a project as a collection of objects arranged as an outline. You can expand a project by clicking the plus sign (+) at the left of the project’s name in the Project window. Contract a project by clicking the minus sign (–) to the left of a project’s name. Or you can double-click the items to expand and contract them.

 If a project is password-protected, you’re prompted for the password when you double-click a project name. If you don’t know the password, you can’t expand the project — which means that you can’t view or modify any part of the project.

Figure 3-2 shows a Project window with three projects listed: an add-in named pup7.xlam, a workbook named investments.xlsm, and the Personal Macro Workbook (which is always named PERSONAL.XLSB). Of the three, only the investments.xlsm 

Every project expands to show at least one node called Microsoft Excel Objects. This node expands to show an item for each sheet in the workbook (each sheet is considered an object) and another object called ThisWorkbook (which represents the Workbook object). If the project has any VBA modules, the project listing also shows a Modules node. And, as you see in Part IV, a project may also contain a node called Forms, which contains UserForm objects (which hold custom dialog boxes).

The concept of objects may be a bit fuzzy for you. However, I guarantee that things become much clearer in subsequent chapters. Don’t be too concerned if you don’t understand what’s going on at this point.

Follow these steps to add a new VBA module to a project:

 1. In the VBE, select the project’s name in the Project window. 2. Choose Insert➪Module.

Or

 1. Right-click the project’s name. 2. Choose Insert➪Module from the shortcut menu.

 When you record a macro, Excel automatically inserts a VBA module to hold the recorded code. Which workbook holds the module for the recorded macro depends on where you chose to store the recorded macro, just before you started recording. 

Sometimes you need to remove a VBA module from a project. For example, it may contain code that you no longer need, or it’s empty because you inserted the module and then changed your mind. To remove a VBA module from a project:

 1. Select the module’s name in the Project window. 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.  Excel, always trying to keep you from doing something you’ll regret, will ask if you want to export the code in the module before you delete it. Almost always, you don’t. (If you do want to export the module, see the next section.)

You can remove VBA modules, but there is no way to remove the other code modules — those for the Sheet objects or ThisWorkbook.

Every object in a VBA project can be saved to a separate file. Saving an individual object in a project is known as exporting. It stands to reason that you can also import objects to a project. Exporting and importing objects might be useful if you want to use a particular object (such as a VBA module or a UserForm) in a different project. Or, maybe you want to send a coworker a copy of a VBA module, which she can then import into her project.

Follow these steps to export an object:

 1. Select an object in the Project window. 2. Choose File➪Export File or press Ctrl+E.  You get a dialog box that asks for a filename. Note that the object remains in the project; only a copy of it is exported. Excel provides the file extension for you, and the extension depends on what type of object you’re exporting. In all cases, the result is a text file. If you’re so inclined, you can open it with a text editor and take a look.

Importing a file to a project goes like this:

 1. Select the project’s name in the Explorer window. 2. Choose File➪Import File or press Ctrl+M.  You get a dialog box that asks for a file. Locate the file and click Open. You should only import a file if the file was exported by using the File➪Export File command.

Working with a Code Window As you become proficient with VBA, you spend lots of time working in Code windows. Macros that you record are stored in a module, and you can type VBA code directly into a VBA module.

Minimizing and maximizing windows If you have several projects open, the VBE may have lots of Code windows at any given time. Figure shows an example of what I mean.

Code windows 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 window that they’re working on. Doing so lets you see more code and keeps you from getting  distracted.

To maximize a Code window, 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 window to its original size, click the Restore button. When a window is maximized, its title bar isn’t visible, so you’ll find the Restore button below the VBE title bar.

Sometimes you may want to have two or more Code windows 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 windows manually or use the Window➪Tile Horizontally or Window➪Tile Vertically command to arrange them automatically.

You can quickly switch among Code windows by pressing Ctrl+F6. If you repeat that key combination, you keep cycling through all the open Code windows. Pressing Ctrl+Shift+F6 cycles through the windows in reverse order.

Minimizing a Code window gets it out of the way. You can also click the window’s Close button (which displays X) in a Code window’s title bar to close the window 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 window. By the way, working with these Code windows sounds more difficult than it really is.

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 the data type for variables you plan to use, or set some other module-wide options. Declarations are basically housekeeping statements. They aren’t actually executed.

 ✓ Sub procedures: A set of programming instructions that, when executed, performs some action.

 ✓ 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. Well, there is a limit — about 64,000 characters per module. By way of comparison, this particular chapter has about half that many characters. After more than 15 years of VBA programming, I haven’t even come close to reaching the 64,000-character limit. And if I did, the solution is simple: Just insert a new module.

How you organize a VBA module is completely up to you. Some people prefer to keep all their VBA code for an application in a single VBA module; others like to split up the code into several different modules. It’s a personal choice, just like arranging furniture.

An empty VBA module is like the fake food you see in the windows of some Chinese restaurants; it looks good but it doesn’t really do much for you. 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:

 ✓ Enter the code directly.

 ✓ Use the Excel macro recorder to record your actions and convert those actions to VBA code.

 ✓ Copy the code from one module and paste it into another.

Sometimes, the best route is the most direct one. Entering code directly involves . . . well, entering the code directly. In other words, you type the code using your keyboard. 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.

Use the Tab key to indent some of the lines to make your code easier to read. Indenting isn’t necessary, but it’s a good habit to acquire. As you study the code I present in this book, you’ll understand why indenting code lines is helpful.

A single line of VBA code can be as long as you need it to me. However, you may want to use the line-continuation characters 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. And don’t forget the space. An underscore character that’s not preceded by a space won’t do the job. 

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 I indented the second and third lines of this statement. Indenting is optional, but it helps clarify the fact that these lines are not separate statements. 

The white-coated engineers who designed the VBE anticipated that people like us would be making mistakes. Therefore, the VBE has multiple levels of undo and redo. If you deleted a statement that you shouldn’t have, use the Undo button on the toolbar (or press Ctrl+Z) until the statement shows up again. After undoing, you can use the Redo button to perform the changes you’ve undone. I recommend playing around with this feature until you understand how it works.

Are you ready to enter some real-live code? Try the following steps

. Create a new workbook in Excel.

2. Press Alt+F11 to activate the VBE. 

3. Click the new workbook’s name in the Project window. 

4. Choose Insert➪Module to insert a VBA module into the project. 

5. Type the following code into the module:

   Sub GuessName()    

     Msg = “Is your name “ & Application.UserName & “?” 

       Ans = MsgBox(Msg, vbYesNo)

        If Ans = vbNo Then MsgBox “Oh, never mind.”

          If Ans = vbYes Then MsgBox “I must be psychic!”

        End Sub

6. Position the cursor anywhere within the text you typed and press F5 to execute the procedure.

When you enter the code listed in Step 5, you might notice that the VBE makes some adjustments to the text you enter. For example, after you type the Sub statement, the VBE automatically inserts the End Sub statement. And if you omit the space before or after an equal sign, the VBE inserts the space for you. Also, the VBE changes the color and capitalization of some text. This is all perfectly normal. It’s just the VBE’s way of keeping things neat and readable.

If you followed the previous steps, you just wrote a VBA Sub procedure, also known as a macro. When you press F5, Excel executes the code and follows the instructions. In other words, Excel evaluates each statement and does what you told it to do. (Don’t let this newfound power go to your head.) You can execute this macro any number of times — although it tends to lose its appeal after a few dozen times.

There’s a chance that the GuessName macro won’t work. When you try to run it, Excel may complain and pop up an error message: Compile Error: Variable Not Defined. Don’t worry; there’s an easy fix for that. If you get that error, look at the top of your module, and you’ll see this text: Option Explicit. Just delete that line, and the macro should work. That line, when present at the top of a module, means that you must “declare” all of your variables. I cover this topic in Chapter 7 (see “Declaring and scoping variables”). If that line was added, it means that your VBE is set up to add the line automatically. For now, don’t worry about it. Just delete the line and forget about the rude interruption.

For the record, this simple macro uses the following concepts, all of which are covered later in this book:

 ✓ Defining a Sub procedure (the first line) ✓ Assigning values to variables (Msg and Ans)

 ✓ Concatenating (joining) a string (using the & operator) ✓ Using a built-in VBA function (MsgBox) ✓ Using built-in VBA constants (vbYesNo, vbNo, and vbYes) ✓ Using an If-Then construct (twice) ✓ Ending a Sub procedure (the last line)

Not bad for a beginner, eh?

Another way you can get code into a VBA module is by recording your actions, using the Excel macro recorder. If you worked through the hands-on exercise in Chapter 2, you already have some experience with this technique.

 By the way, there is absolutely no way you can record the GuessName procedure shown in the preceding section. You can record only things that you can do directly in Excel. Displaying a message box is not in Excel’s normal repertoire. (It’s a VBA thing.) The macro recorder is useful, but in many cases, you’ll probably need to enter at least some code manually.

Here’s a step-by-step example that shows you how to record a macro that inserts a new worksheet and hides all but the first 10 rows and all but the first 10 columns. If you want to try this example, start with a new, blank workbook and follow these steps:

1. Activate a worksheet in the workbook.  Any worksheet will do.

2. Click the Developer tab and make sure that Use Relative References is not highlighted.  This macro will be recorded using Absolute References.

3. Choose Developer➪Code➪Record Macro, or you can click the icon next to the Ready indicator in the left side of the status bar.  Excel displays its Record Macro dialog box.

4. In the Record Macro dialog box, name the macro TenByTen and type Shift+T for the shortcut key.

The macro can be executed when you press Ctrl+Shift+T

5. Click OK to start recording.

Excel automatically inserts a new VBA module into the project that corresponds to the active workbook. From this point on, Excel converts your actions into VBA code. While recording, the icon in the status bar turns into a small square. This is a reminder that the macro recorder is running. You can also click that blue square to stop the macro recorder.

6. Click the New Sheet icon to the right of the last sheet tab.  Excel inserts a new worksheet.

7. Select the entire Column K (the 11th column) and press Ctrl+Shift+RightArrow; then right-click any selected column and choose Hide from the shortcut menu.  Excel hides all of the selected columns.

8. Select the entire Row 11 and press Ctrl+Shift+Down Arrow; then rightclick any selected row and choose Hide from the shortcut menu.  Excel hides all of the selected columns.

9. Select cell A1.

 10. Choose Developer➪Code➪Stop Recording, or click the Stop Recording button in the status bar (the small square).

Excel stops recording your actions.

To view this newly recorded macro, press Alt+F11 to activate the VBE. Locate the workbook’s name in the Project window. You see that the project has a new module listed. The name of the module depends on whether you had any other modules in the workbook when you started recording the macro. If you didn’t, the module will be named Module1. You can double-click the module to view the Code window for the module.

Here’s the code generated by your actions:

Sub TenByTen()

 ‘

 ‘ TenByTen Macro

 ‘

 ‘ Keyboard Shortcut: Ctrl+Shift+T

 ‘

    Sheets.Add After:=ActiveSheet

    Columns(“K:K”).Select

    Range(Selection, Selection.End(xlToRight)).Select

    Selection.EntireColumn.Hidden = True

    Rows(“11:11”).Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.EntireRow.Hidden = True

    Range(“A1”).Select

 End Sub

To try out this macro, activate any worksheet and press the shortcut key that you assigned in Step 4: Ctrl+Shift+T.

If you didn’t assign a shortcut key to the macro, don’t worry. Here’s how to display a list of all macros available and run the one you want:

1. Choose Developer➪Code➪Macros.  Keyboard fans can press Alt+F8. Either of these methods displays a dialog box that lists all the available macros. 

2. Select the macro in the list (in this case, TenByTen). 

3. Click the Run button.  Excel executes the macro, and you get a new worksheet with 10 visible rows and 10 visible columns.

You can execute any number of commands and perform any number of actions while the macro recorder is running. Excel dutifully translates your mouse actions and keystrokes to VBA code. 

And, of course, you can also edit the macro after you record it. To test your new skills, try editing the macro so that it inserts a worksheet with nine visible rows and columns — perfect for a 

Sudoku puzzle.

The final method for getting code into a VBA module is to copy it from another module or from some other place (such as a website). For example,  a Sub or Function procedure that you write for one project might also be useful in another project. Instead of wasting time reentering the code, you can activate the module and use the normal Clipboard copy-and-paste  procedures (I’m rather fond of the keyboard shortcuts, Ctrl+C to copy and Ctrl+V to paste). After pasting it into a VBA module, you can modify the  code if necessary.

By the way, you’ll find lots of VBA code examples on the web. If you’d like to try them, select the code in your browser and press Ctrl+C to copy it. Then, activate a module and press Ctrl+V to paste it.

To help make things as comfortable as possible (no, please keep your shoes on), the VBE provides quite a few customization options.

When the VBE is active, choose Tools➪Options. You’ll see a dialog box with four tabs: Editor, Editor Format, General, and Docking.

Using the Editor tab

Using the Editor Format tab

Using the General tab 

Using the Docking tab

shows the options accessed by clicking the Editor tab of the Options dialog box. Use the options in the Editor tab to control how certain things work in the VBE.

Auto Syntax Check option The Auto Syntax Check setting determines whether the VBE pops up a dialog box if it discovers a syntax error while you’re entering your VBA code. The dialog box tells roughly what the problem is. If you don’t choose this setting, VBE flags syntax errors by displaying them in a different color from the rest of the code, and you don’t have to deal with any dialog boxes popping up on your screen.

I usually keep this setting turned off because I find the dialog boxes annoying, and I can usually figure out what’s wrong with a statement. But back in in the olden days (before I was a VBA veteran), I found this assistance quite helpful.

option If the Require Variable Declaration option is set, VBE inserts the following statement at the beginning of each new VBA module you insert:

Option Explicit

Changing this setting affects only new modules, not existing modules. If this statement appears in your module, you must explicitly define each variable you use. In Chapter 7, I explain why you should develop this habit.

Normally, I keep this option turned off because I prefer to add the Option Explicit statement myself.

If the Auto List Members option is set, VBE provides some help when you’re entering your VBA code. It displays a list that would logically complete the statement you’re typing. This bit of magic is sometimes called IntelliSense.

This is one of the best features of the VBE, and I always keep it turned on. Figure 3-6 shows an example (which will make lots more sense when you start writing VBA code).

If the Auto Quick Info option is set, VBE displays information about functions and their arguments as you type. This can be very helpful. Figure 3-7 shows this feature in action, telling me about the arguments for the MsgBox function.

If the Auto Data Tips option is set, VBE displays the value of the variable over which your cursor is placed when you’re debugging code. When you enter the wonderful world of debugging, as I describe in Chapter 13, you’ll appreciate this option.

The Auto Indent setting determines whether VBE automatically indents each new line of code the same as the previous line. I’m big on using indentations in my code, so I keep this option on.

Use the Tab key to indent your code, not the spacebar. Also, you can use Shift+Tab to “unindent” a line of code. If you want to indent more than just one line, select all the lines you want to indent. Then press the Tab key.

The VBE’s Edit toolbar (which is hidden by default) contains two useful buttons: Indent and Outdent. These buttons let you quickly indent or “unindent” a block of code. Select the code and click one of these buttons to change the block’s indenting.

The Drag-and-Drop Text Editing option, when enabled, lets you copy and move text by dragging and dropping with your mouse. I keep this option turned on, but I never use it. I prefer to copy and move by using the keyboard.

The Default to Full Module View option sets the default state for new modules. (It doesn’t affect existing modules.) If set, procedures in the Code window appear as a single scrollable list. If this option is turned off, you can see only one procedure at a time. I keep this option turned on.

When the Procedure Separator option is turned on, separator bars appear at the end of each procedure in a Code window. I like the idea of separator bars, so I keep this option turned on.

shows the Editor Format tab of the Options dialog box. With this tab, you can customize the way the VBE looks.

The Code Colors option lets you set the text color and background color displayed for various elements of VBA code. This is largely a matter of personal preference. Personally, I find the default colors to be just fine. But for a change of scenery, I occasionally play around with these settings.

The Font option lets you select the font that’s used in your VBA modules. For best results, stick with a fixed-width font such as Courier New. In a fixed-width font, all characters are exactly the same width. This makes your code more readable because the characters are nicely aligned vertically, and you can easily distinguish multiple spaces (which is sometimes useful).

The Size setting specifies the point size of the font in the VBA modules. This setting is a matter of personal preference determined by your video display resolution and how many carrots you’ve been eating.

This option controls the display of the vertical margin indicator bar in your modules. You should keep this turned on; otherwise, you won’t be able to see the helpful graphical indicators when you’re debugging your code.

Shows the options available under the General tab in the Options dialog box. In almost every case, the default settings are just fine.

The most important setting is Error Trapping. I strongly suggest that you use the Break On Unhandled Errors setting (which is the default). If you use a different setting, your error-handling code won’t work. You can read more about this in Chapter 12.

If you’re really interested in these options, click the Help button for details.

Figure  shows the Docking tab. These options determine how the various windows in the VBE behave. When a window is docked, it is fixed in place along one of the edges of the VBE program window. This makes it much easier to identify and locate a particular window. If you turn off all docking, you have a big, confusing mess of windows. Generally, the default settings work fine.

You’ll find that sometimes the VBE seems to have a mind of its own when you’re trying to dock a window. If docking doesn’t seem to work correctly, just stick with it, and you’ll get the hang of things.

Q. 1 How do you create a new VBA module?

Q. 2 Write in detail steps to record a macro?

Q. 3 what are object browser and how is it used?

Q. 4 How do you execute a macro from a macro dialog box?

Q. 5 How do you assign a macro to a button or shape?

Write/Record a macro, which will always display a date 2 days beyond the current date. For example, if today is 2nd January 2023, the cell A1 on the sheet should display 4th January 2023.

Write/Record a macro to take values present in cells A1 and A2, and display the sum in cell A3. Display their difference in A4. If the difference is less than 0, the cell A4 should be colored in red.