Using Variables And Arrays

VBA enables subroutines and functions to call other subroutines and functions. When you call another function or subroutine, control passes from the current procedure to the procedure that is called. Upon completion of that procedure, control returns to the next line of code in the original procedure. You call a procedure using the Call statement before the procedure name.

Example:

Sub Main_Proc()

Dim LocalVar As Integer LocalVar=1 Call New_Proc LocalVar = LocalVar + 1 End Sub

In this example, the Main_Proc subroutine executes and creates a variable called LocalVar. The New_Proc subroutine is called. When that subroutine completes, control returns to the Main_Proc subroutine and LocalVar is incremented by one. While the New_Proc subroutine has control, the LocalVar variable is not available.

When one subroutine calls other functions and subroutines, you typically hide those functions and subroutines so that you cannot seperately call them. All subroutines that you do not hide display on the Macro dialog box for the corresponding workbook. To hide a subroutine or function, place the word Private before the procedure declaration statement.

Example:

Private Sub New_Sub() End Sub

0 Create a third subroutine.

■ The message box displays the contents of the PubVar variable after being passed between each subroutine.

0 Create a third subroutine.

_□ Type Call Sub1, replacing Sub1 with the first subroutine.

_0 Type Call Sub2, replacing Sub2 with the second subroutine.

Use the MsgBox function to display the contents of the PubVar variable.

Note: See Chapter 7 for more information on using the Msg function.

, Switch to Excel and run the macro.

■ The message box displays the contents of the PubVar variable after being passed between each subroutine.

0 0

Post a comment