University:
Georgia Southern University
Course:
ENGR1121 | Computing in Mechanical Engineering
Academic year:

2024
Views:

251

Pages:

37
Author:

Jalen W.

RECORDING A MACRO • A macro is a sequence of consecutive keystrokes and/or mouse actions that is recorded for later playback. • Thus, a macro allows you to automate a series of instructions within an Excel spreadsheet. • Generally speaking, the more complicated the instructions, the more useful the macro. • Every macro has a unique name and a shortcut key (such as Ctrl-P, Ctrl-X, etc.) associated with it. • Once a macro has been recorded, its instructions can be executed automatically simply by selecting the macro by name or by pressing its shortcut key. Add the Developer tab to the Ribbon Select Record Macro from the Code group within the Ribbon’s Developer tab, resulting in the Record Macro dialog box • After the required information has been entered into the Record Macro dialog box, click on OK. • You may then begin entering instructions (i.e., keystrokes or mouse movements) anywhere within the worksheet. • All of the completed instructions will be included within the macro. • When you are finished recording, click on the Stop Recording button within the Code group of the Ribbon’s Developer tab EXECUTING A MACRO • Once a macro has been saved, it can be executed simply by holding down the Ctrl key and pressing the assigned shortcut key. • Or, by selecting Macros from the Code group within the Ribbon’s Developer tab, selecting a macro from the list shown in the resulting Macro dialog box, and then clicking on Run . Example 14.1 Formatting a Number within a Cell • Let us record and execute a macro that will format any number in the following manner: display two decimal places, include commas to separate thousands, millions, etc., and display the number in boldface. begin by selecting any empty cell—say, cell B3—and then select Record Macro from the Code group within the Ribbon’s Developer tab Right-click on cell B3 and select Format Cells. click on the Font tab within the Format Cells dialog box End the recording by clicking on the Stop Recording button within the Developer tab if we enter the number 12345.6789 into cell B3, it will appear as 12,345.68 as shown if we enter this same value into any other cell click on cell B5 to make it active and then press Ctrl-a CELL ADDRESSING WITHIN A MACRO • if a macro does not reference any cells other than the currently active cell, then it can be applied to any cell within the worksheet. • if a macro does include references to other cells • absolute addressing to reference other cells when it is recording. Therefore, the instructions will always be carried out using the same cells that were referenced when the macro was recorded • This restriction may be removed, however, by using relative addressing rather than absolute addressing when recording the macro Example 14.2 Evaluating a Polynomial • Create a macro to evaluate the polynomial • Using the default recording method (absolute addressing). • Using relative addressing. macro will use absolute addressing, which is the default recording mode • begin recording. • Enter the Formula • Stop recording • enter a value for x in cell B3. If we then press Ctrl-p from any cell within the worksheet • if the value of x is entered anywhere other than cell B3, the macro will not return the correct result Relative Addressing • Click on the Use Relative References button to turn on relative addressing. can be used with a value of x entered into any cell within the worksheet. The only restriction is that there must be a value of x entered in two cells above the cell in which the macro is executed. Otherwise, the macro will return a value of y = 5, corresponding to x = 0. a worksheet with several x-values entered in row 3. The corresponding y-values are shown in row 5. Each y-value was obtained by pressing Ctrl-rafter entering the corresponding xvalue directly above it in row 3. Saving a Macro • The currently active workbook. • Available only to this • A new workbook. • Available only to current • A special file called the Personal Macro Workbook. • Available to all workbooks VIEWING A MACRO IN VBA • The instructions that constitute a macro are members of a special programming language developed by Microsoft, called Visual Basic for Applications (commonly referred to as VBA) • To view the instructions within a macro, select the Ribbon’s Developer tab and click on the Macros button within the Code group. Or, you may select the Macros button within the Ribbon’s View tab. • You may then view the macro by selecting it and then clicking the Edit button. If the macro you wish to view is stored within the Personal.xlsb file, you may need to “unhide” this file before its contents can be viewed. To do so, select the View tab on the Ribbon, click the Unhide button within the Window group, and select Personal.xlsb from the drop-down menu Example 14.3 Viewing a Macro in VBA first bring up the Excel file containing this macro. (Recall that this macro was not stored in Personal.xlsb; hence, we must bring up the worksheet containing the macro.) EDITING A MACRO IN VBA • save the edited macro as a new macro rather than a replacement for an existing macro, be sure to change the macro name in the Sub statement. • Saving a macro with a new name does not assign a shortcut key. To do so, leave the VBA editor (after saving the new macro) and return to Excel. Then, from the Ribbon’s Developer tab, click on Macros within the Code group. (Or, from the View tab, click on Macros.) When the Macro dialog box appears, click on the Options button. You may then specify a shortcut key. Reading work • Example 14.4 Editing a Macro USER-DEFINED FUNCTIONS • built-in library functions (worksheet functions) • user-defined (or customized) functions that can be created in VBA. • accessed in the same manner as Excel library functions. • can be saved within an individual worksheet for exclusive access within that worksheet, or • in the PERSONAL.XLSB file, where they can be accessed by all Excel worksheets. Functions • Function name • Functions return one and only one value • Inputs and outputs • Arguments (inputs) • Numbers, strings, cells VBA Fundamentals • An assignment statement consists of a variable name, followed by an equal sign, followed by an expression. • Expressions generally consist of constants, variables, function references, and operators. • They generally represent numerical quantities, strings, or logical (i.e., true/false) conditions. • In an assignment statement, the value of the expression on the right side of the equal sign is assigned to the variable name on the left. Function Example 14.5 Creating a UserDefined Function • create a user-defined function, called PolyFunction, that will allow us to evaluate enter the function definition into the VBA editor. To do so, we select the Ribbon’s Developer tab and click on Visual Basic within the Code group. We then select Modules/Module1 from the Insert menu and type the six-line function definition into the VBA editor. We then return to Excel by clicking on the Excel icon at the extreme left of the toolbar. access the new function within an Excel worksheet Reading work • Example 14.6 Creating a User-Defined Function with Optional Arguments

Creating and Executing Macros

of 37

Description

Struggling with your assignment and deadlines?

Free up your schedule!

Take 5 seconds to unlock