PowerPoint Presentation
Chapter 38: Subroutines and User Interaction
Copyright By PowCoder代写 加微信 powcoder
VBA: Subroutines
A subroutine is a VBA user routine used to automate routine or repetitive operations in Excel. Subroutines are sometimes called macros. The command is used to perform tasks that may involve updating a cell, performing a calculation, or importing a file into the Excel application.
Subroutine (sub) Function
A sub performs a task but does not return a value. A function returns a value of the tasks performed.
Subs can be recalled from anywhere in the program and in multiple types. Functions are called by a variable.
Subs cannot be used directly in spreadsheets as formulas. Functions are used directly in spreadsheets as formulas.
VBA: Our first subroutine
The commas separate the three arguments of every MsgBox :
• “Hi” is the message which will be displayed.
• The second argument is empty: Notice the space between the commas. This argument can be used to define buttons for the message box.
• The third argument is “I say Hi”—this is the message box title.
VBA: Activate/Run a subroutine
Ribbon Developer Macros
Keyboard shortcut [Alt] + F8.
If you don ’ t have the Developer tab, go to File Options Customize the Ribbon.
In the right side of the resulting screen, mark the Developer box.
VBA: Shortcut to run a subroutine
Using a keyboard shortcut is a faster way to make a subroutine run. To attach a shortcut to our subroutine:
• Select the Options button from the macro selection box.
• Type a character in the provided space and click OK .
• Close the macro selection box using the corner X .
You can now activate the subroutine using the shortcut.
VBA: Run a subroutine with a button
We can also insert a button on the worksheet to run a subroutine on that worksheet. To illustrate, we insert a button that runs the subroutine “SayHi”:
1. Select the Developer tab on the ribbon.
2. In the Controls group, click Insert.
3. From the Form Controls, select Button.
4. Drag the crosshair on the sheet to draw the button.
5. Once you release the mouse button, the Assign Macro dialogue will appear.
6. Select our subroutine and click OK .
VBA: User interaction – MsgBox
If click on “OK”
The default message
If click on “Cancel”
VBA: User interaction – InputBox
InputBox is an internal VBA function used to get textual information from the user into a variable in a subroutine. The workings of the function are demonstrated in the following present value calculator subroutine.
VBA: Use subroutine to format
ActiveCell.CurrentRegion is the range around the active cell (C5 in the screenshots), which is the same range that would be selected by pressing [Ctrl] + A in the worksheet (B4:D8 in the screen shots).
VBA: Use subroutine to format
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com