Using The Visual Basic Editor

33xj nc

Instead of copying the macro code to the ThisWorkbook object , you can create a macro in the ThisWorkbook object code module that calls the macro code in the appropriate code module. For example, if you have a subroutine in a module called SetToolbars that you recorded with the Macro Recorder, you can create another subroutine called Workbook_Open() in the ThisWorkbook object that calls the recorded macro, as shown in the following sample code.

Example:

Sub Workbook_Open()

Call SetToolbars End Sub

From the Workbook_Open() subroutine, you can call any other procedure within the same workbook project. To call another procedure, you need to use the Call statement before the name of the procedure you want to call. When Excel executes the main procedure, if it encounters a Call statement it executes that procedure and returns to the main procedure.

When you create a Workbook_Open macro, you actually create a subroutine that captures the Open event for the corresponding Workbook object. Events occur in Excel whenever anything occurs, whether initiated by you or the application. You can capture many different events to trigger specific code. See Chapter 15 for more detailed information on working with Excel events.

I Type a question For help

Module2 Module Alphabetic | .lategoriqed

Module2 Module Alphabetic | .lategoriqed

rare

I Type a question For help

B"SU! g Li rare

0 Open the Visual Basic Editor.

Note: See the section "Activate the Visual Basic Editor" to open the editor.

0 Click the current project.

I Type a question For help

"E

Modulel nodules I VBAProject (lllfi. i-l I VBAProject (PERSON/

+ LJ Microsoft

□ Double-click the last module in the list.

_Q Highlight the entire macro code from Sub Workbook_Open() to the End Sub command.

'-0 Click the Copy button

Standard").Visible = Fa Standard").Visible = Tr

Formatting") .Visible Settings") .Visit 3-D Settings").Visil Borders").Visible = Tru ri5h..nish..o> . H ThisWorkbook I

Modulel nodules I VBAProject (lllfi. i-l I VBAProject (PERSON/

+ LJ Microsoft

Standard").Visible = Fa Standard").Visible = Tr

Formatting") .Visible Settings") .Visit 3-D Settings").Visil Borders").Visible = Tru

"EE Click the Paste button m Double-click the ThisWorkbook object on the tree in the Project Explorer under PERSONAL.XLS.

Place the cursor in the Code window for the selected object, under any existing

"EE Click the Paste button

E Close the Visual Basic Editor.

■ When you open Excel again, the Workbook_Open macro runs and executes the specified code.

0 0

Post a comment