The Visual Basic Development Environment

Open the Developer ribbon and choose Visual Basic to display the Visual Basic Editor (VBE). Figure 1-3 shows the VBE.

Figure 1-3. Excel's Visual Basic Editor

The default view is divided into three panes: the Project Explorer, the Property Sheet, and the code window.

The Project Explorer (Figure 1-4) lists open projects (workbooks) and the objects they contain. These can include worksheets, the workbook itself, standard code modules, class modules, and any UserForms in the project.

Objects are stored in folders representing their function. In Figure 1-4, you can see the worksheet objects in the Microsoft Excel Objects folder. Code is placed in its own folder, as are UserForms.

At the top of the Project Explorer pane is a toolbar that provides access to view code, view the selected object (choosing this command with Sheet1 selected will bring you to the Excel window with Sheet1 active), and toggle the folder view on or off. Toggling the folders off lists all of the objects together in one list regardless of type of object, as in Figure 1-5.

Project - VBAProject

*

m m a

_

ö Microsoft Excel Objects g] Sheetl (Sheetl)

I SheetZ (SheetZ)

I Sheet3 (Sheet3)

® ThisWorkbook Q Forms

[01 UserForml

- S Modules

ö Microsoft Excel Objects g] Sheetl (Sheetl)

I SheetZ (SheetZ)

I Sheet3 (Sheet3)

® ThisWorkbook Q Forms

[01 UserForml

- S Modules

Module 1

- s Class Modules

Figure 1-4. Excel objects grouped by object (with Toggle Folders on)

Project - VBAProject

X

iii m a

É

® Class 1 Ä Module 1 ([] Sheetl (Sheetl) ([] SheetZ (SheetZ) J] Sheet3 (Sheet3) ^T] ThisWorkbook [01 UserForml

® Class 1 Ä Module 1 ([] Sheetl (Sheetl) ([] SheetZ (SheetZ) J] Sheet3 (Sheet3) ^T] ThisWorkbook [01 UserForml

Figure 1-5. Excel objects with Toggle Folders off

The Property Sheet lists properties for the currently selected object in the Project Explorer, and will look very familiar to those VB 6.0 coders among us. Figure 1-6 shows an example of the Property Sheet for an Excel worksheet.

Use the code window to write, display, and edit Visual Basic code. You can open as many code windows as you have modules, so you can easily view the code in different forms or modules, and copy and paste between them.

You can open a code window from

• The Project window, by selecting a form or module and choosing the View Code button

• A UserForm window, by double-clicking a control or form, choosing Code from the View menu, or pressing F7

You can drag selected text to

• A different location in the current code window

• Another code window

• The Immediate and Watch windows

Properties - Sheetl x|

Sheetl Worksheet -J

Alphabetic | Categorized |

Sheetl Worksheet -J

Alphabetic | Categorized |

(Name)

Sheetl

DisplayPageBreaks

False

DisplayRightToLeft

False

EnableAutoFilter

False

EnableCalculation

True

EnableFormatConditionsC True

EnableOutiining

False

EnablePivotTable

False

EnableSelection

0 - xlNoRestrictions

Name

Sheetl

ScrollArea

StandardWidth

8.43

Visible

-1 - xlSheetVisible

Figure 1-6. VBA Property Sheet

The code window shown in Figure 1-7 will look very familiar to those with VB 6.0 experience.

Figure 1-7. The VBA code window

At the top of the code window are two drop-down lists. On the left is the Object box, where any objects associated with the current selection are listed. On the right is the Procedure/Events box, where all methods and events for the currently selected object are displayed.

With Sheet1 selected in the Project Explorer, choose Worksheet from the Object box in the code pane. The default method for the worksheet object, Worksheet_SelectionChange, is inserted into the code window. Open the Procedure/Events box to see other methods and events available to you, as shown in Figure 1-8.

Figure 1-8. The code pane with the procedure list open

In the bottom-left corner of the code pane are two command buttons that determine how your procedures are displayed: Full Module View and Procedure View (shown in Figure 1-9).

Option Explicit_

private Sut Worksheet_Activste() End 5-jfc private Sut WorJcsheet_SelectionChan< End Sut private Sut WorJcsheet_SelectionChan< End Sut

Figure 1-9. Full Module view

Figure 1-9. Full Module view

By default, Excel shows all procedures in a module (Full Module View). Clicking the Procedure View button (Figure 1-10) filters out all code except the procedure in which the cursor is located.

Figure 1-10. Procedure view

Immediately above the vertical scroll bar is the split bar, shown in Figure 1-11. Dragging this bar down splits the code window into two horizontal panes. Each pane can be scrolled separately, allowing viewing of two sections of a module at once. The information that appears in the Object box and Procedure/Events box applies to the code in the pane that has the focus. Dragging the bar to the top or the bottom of the window or double-clicking the bar restores the pane to its original single-pane view.

Sub TotalSales(} ■Author: Jim DeMarco ■Date: 6/24/07

■Purpose: Adds total sales for all regions Dim sFormula As String sFormula = "=SUM(R[-5]C:R[-1]C}"

range("B3"}.Select

ActiveCe11.FormulaRlCl = sFormula range("C8"}.Select

ActiveCell.FormulaRlCl = sFormula range("D3"}.Select

ActiveCell.FormulaRlCl = sFormula range("E8"}.Select

ActiveCell.FormulaRlCl = sFormula

Dim s Formula As String sFormula = "=SDM(R[-5]C:R[-l]C}"

range("B3"}.Select

ActiveCell.FormulaRlCl = sFormula range("C8"}.Select

ActiveCell.FormulaRlCl = sFormula range("D8"}.Select

ActiveCell.FormulaRlCl = sFormula range("E3"}.Select

ActiveCell.FormulaRlCl = sFormula

Figure 1-11. Code window with split panes

In addition to these items, there are a few other windows to help you write and test your code: the Immediate window, the Locals window, and the Watch window.

The Immediate Window

The Immediate window (Figure 1-12) allows you to do the following:

• Type or paste a line of code and press Enter to run it

• Copy and paste the code from the Immediate window into the code window, but not save code in the Immediate window

Immediate ^xjl

End Sub

Figure 1-12. The Immediate window

The Immediate window can be dragged and positioned anywhere on your screen unless you have made it a dockable window from the Docking tab of the Options dialog box.

You can close the window by clicking the Close box. If the Close box is not visible, doubleclick the Title bar to make the Close box visible, and then click it.

Note In break mode, a statement in the Immediate window is executed in the context that is displayed in the Procedure box. For example, if you type Print variablename, your output will be the value of variablename. This is the same as if the Print method had occurred in the procedure you were executing.

The Locals Window

The Locals window (Figure 1-13) automatically displays all of the declared variables in the current procedure and their values.

Locals

*

<Ready>

Expression

¡Value

_1 TVPS

Figure 1-13. The Locals window

When the Locals window is visible, it is automatically updated every time there is a change from run to break mode, and when you navigate in the stack display, as shown in Figure 1-14.

Figure 1-14. The Locals window shows function values.

You can use the Locals window to do the following:

• Resize the column headers by dragging the border right or left.

• Close the window by clicking the Close box. If the Close box is not visible, double-click the Title bar to make the Close box visible, and then click it.

Locals Window Elements

The Locals window is made up of the following components. These window elements allow you to open the call stack and see the actual values of your variable as they are processed.

Call Stack button: Opens the Call Stack dialog box, which lists the procedures in the call stack. The call stack lists all the functions that are currently being executed. Figure 1-15 shows that the GetRegionalTotals function is being run from within the GetTotals function. The function on top is called by the function below it.

Figure 1-15. The call stack

The Locals window shows the following items in its columns:

Expression: Lists the name of the variables. The first variable in the list is a special module variable that can be expanded to display all module-level variables in the current module. This data is read-only.

Value: Lists the value of the variable. When you click a value in the Value column, the cursor changes to an I-beam. You can edit a value here to alter your code execution.

Note All numeric variables must have a value. String variables can have an empty value.

Type: Lists the variable type (read-only).

The Watch Window

The Watch window (Figure 1-16) appears automatically when watch expressions are defined in the project (Figure 1-17).

Watches

:>(

Expression

lvalue

i Type

j Context

i

Figure 1-16. The Watch window

Figure 1-16. The Watch window

Figure 1-17. The Watch window takes action when values meet certain criteria.

You can use the Watch window to do the following:

• Change the size of a column header, by dragging its border to the right to make it larger or to the left to make it smaller

• Drag a selected variable to the Immediate window or the Watch window

Close the window by clicking the Close box. If the Close box is not visible, double-click the Title bar to make the Close box visible, and then click it.

Watch Window Elements

The Watch window list box columns display information about your watched expressions.

Expression: Stores a conditional phrase defined by the developer to evaluate the value of the watched variable. For example, if you wanted to know when a string variable named sCity was equal to New York, you would enter an expression of sCity = "New York".

Value: Lists the value of the expression at the time of entering break mode. You can edit a value here to alter code execution.

Type: Lists the expression type.

Context: Lists the context of the watch expression.

You can close the window by clicking the Close box. If the Close box is not visible, doubleclick the Title bar to make the Close box visible, and then click it.

0 0

Post a comment