Monday, March 12, 2012

How to Use a Subroutine in Excel Visual Basic


1. Open the Visual Basic Editor. In Excel 2003, from the menu, select 'View,' 'Toolbars,' 'Visual Basic.' From the new toolbar, click the Visual Basic Editor icon. In Excel 2007, on the Developer tab, in the Code group, click 'Visual Basic.'
2. Insert a new module. From the menu, click 'Insert,' 'Module.' You will see the new module listed in the Project Explorer with the name 'Module1.'
3. Insert a new procedure. From the menu, click 'Insert,' 'Procedure.'
4. In the 'Add Procedure' pop-up box, type a name for your new subroutine. Use underscores, rather than spaces, to separate words.
5. Under 'Type,' select 'Sub.'
6. Under 'Scope,' choose 'Public' or 'Private.' A public subroutine is accessible to other procedures; a private subroutine only works within the same procedure.
7. If desired, check the 'All Local variables as Static' checkbox. Static variables retain their values when you exit a procedure. The default behavior clears all variables.
8. Click 'OK.' In the code window, you will see the Sub and End Sub statements.
9. Add code. Between 'Sub' and 'End Sub,' insert the VBA instructions for your subroutine. For instance, use the following code to display a message box with a greeting:Sub Test()
MsgBox 'Hello World'
End Sub
10. Run the subroutine. From the menu, select 'Run.' In Excel 2003, the subroutine will execute. In Excel 2007, select the subroutine in the pop-up box, and click 'Run' to start execution.

Blogger news