Table of Contents. Recording the Macro To create the Macro, follow these steps:.
This Excel tutorial explains how to open the Visual Basic Editor in Excel 2011 for Mac (with screenshots and step-by-step instructions). You can access the VBA environment in Excel 2011 for Mac by opening the Visual Basic editor. First, be sure that the Developer tab is visible in the toolbar in. 2011 with Lion. I'm talking about Excel, but I can't record macros on Word either. I tried to find these options, so I downloaded and re-installed Office for Mac. (I am working in. Attachment: 2011-42_ztest.xls. And also check if.
Activate an empty cell. Choose Developer ➪ Code ➪ Record Macro. The Record Macro dialog box will appear. Enter a unique, single-word (no space between letters or numbers) name for the macro, to replace the default Macro1 name. We have named our macro TimeStamp. We’ve entered an uppercase T in the Shortcut Key field of the Record Macro dialog box to assign this macro to a shortcut key Ctrl+Shift+T. Click OK, the Record Macro dialog box will close.
Now, enter this formula into the selected cell: =NOW. With the date cell selected, click the Copy button (or press Ctrl+C) to copy the cell to the Clipboard. Choose Home ➪ Clipboard ➪ Paste ➪ Values (V). This step will replace the formula (NOW) with static text so that the date and time do not update automatically. Worksheet shows refresh data when you do something in any cell and press Enter. Press Esc to cancel Copy mode. Choose Developer ➪ Code ➪ Stop Recording to stop recording your macro.
Or, you can click the Stop Recording button on the status bar. Read More: Running the macro Activate an empty cell and press Ctrl+Shift+T to execute the macro. It is almost sure that the macro will not work. The VBA code that is recorded in this macro depends upon a setting on the Advanced tab of the Excel Options dialog box. The option is: After Pressing Enter, Move Selection.
By default this setting is enabled. When this setting is enabled, the recorded macro won’t work as intended because the active cell has been changed when you have pressed Enter. Examining the macro Activate the VB Editor (press ALT + F11) and take a look at the recorded code. The following figure shows the recorded macro displayed in the Code window. This TimeStamp procedure is generated by the Excel macro recorder. This procedure has five statements:. The first statement inserts the NOW formula into the active cell.
The second statement selects cell A1— an action I performed. When I pressed Enter after inserting the formula in cell A1, the cell pointer moved to cell A2. I reselected the cell A1 using mouse pointer (or you can use navigation keys ). The third statement copies the cell. The fourth statement is displayed on two lines. The underscore character means that the statement continues on the next line. This statement pastes the Clipboard contents (as a value) to the current selection.
The fifth statement cancels the moving border around the selected range. From analyzing this code, we find a problem. The problem is that the macro is hard-coded to select cell A1.
If you execute the macro when a different cell is active, at first the code selects cell A1 and then copies the cell. This is not what we intended, and it causes the macro to fail. Read More: Note: You’ll also notice that the macro recorded some actions that you didn’t make. For example, see this code line: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False. It has specified several options for the PasteSpecial operation. These options are just by-products of the method that Excel uses to translate actions into code while recording a macro.
Rerecording the Macro You can fix the macro in several ways. If you’re an expert in VBA coding, you can edit the code so it works properly. Or you can rerecord the macro using relative references. TimeStamp macro now works properly. Testing the Macro When an Excel workbook is active, activate a worksheet. You can run this macro in the workbook that contains the VBA module. You can also open an existing workbook or create new one and run this macro.
It’s really awesome, right? Select a cell of the worksheet and press Ctrl+Shift+T. The macro will immediately enter the current date and time into the cell. You may need to widen the column to see the date and time. If the column is not widened automatically, just add this statement to the end of the macro (before the End Sub statement): ActiveCell.EntireColumn.AutoFit Happy Excelling ? Download Working File Download the working file from the link below.
Learn how to record a VBA Macro in Excel for Mac 2011 Here’s the video: And here’s the code that is shown in the video.