1 / 8

Excel Macro Recording: How to Record, Store, and Modify Macros

Learn how to record macros in Excel, choose where to store them, and modify the recorded code. This guide includes examples and tips to help you master macro recording.

jenac
Download Presentation

Excel Macro Recording: How to Record, Store, and Modify Macros

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 5 Recording Macros

  2. How to Record a Macro • Use Tools/Macro/Record New Marco • Give the following information: macro name; provide a description of the macro; give it a shortcut key, and tell Excel where to record the code. • You can store a macro in the current workbook, in a new workbook, or in a special workbook called Personal.xls. • Macro stored in current workbook can only be used in that workbook. • If you want macros to be available all the time you are using Excel then the Personal.xls file is a better storage location. • The Personal.xls file is a special file that Excel stores in its XLStart folder. It is opened every time Excel is opened. • Default path is C:\Program Files\Microsoft Office\Office\XLStart. • The file is opened as a hidden file which masks it’s presence. • After you start recording, there will be a Stop recording box. If the box does not appear, Tools/Macro/Stop Recording will allow you to stop recording. • If you already have a module in your current workbook, Excel will create a new module and placed the recorded macro in it. • This means you might have to search through your modules to find newly created code.

  3. Recorded Macro Examples #1 • Code #1: • Sub SumFormula() • ‘ SumFormula Macro • ‘Macro recorded 4/5/2000 by Chirs Albright • Range(“B7:B14”).Select • ActiveWorkbook.Name.Add Name:=“MonthlyCosts”. RefersToR1C1:=_ • “=Exercise1!R7C2:R14C2” • Range(“B15”).Select • ActiveCell.FromulaR1C1 = “=Sum(MonthlyCosts)” • Range(“B16”).Select • End Sub • Modified Code: • Sub SumFormula() • With Worksheets(“Exercise1”) • .Range(“B7:B14”).Name = “MontlyCosts”) • .Range(“B15”).Formula = “Sum(MontlyCosts) • End With • End Sub

  4. Recorded Macro Examples #2 • Coping a formula down a sheet: • Sub CopyPaste() • ‘ CopyPaste Macro • ‘ Macro recorded 4/5/2000 by Charles Albright • Range(“D7”).Select • Selection.Copy • Range(“D7:D15”).Select • ActivateSheet.Paste • Application.CutCopyMode=False • End Sub • Modified Code: • Sub CopyPaste() • With Worksheets(“Exercise2”) • .Range(“D7”).Copy Destination:=Range(“D7:D15”) • End With • ‘ next line is equivalent to pressing the Esc key to get rid of the dotted line around copy range • Application.CutCopyMode = False • End Sub

  5. Recorded Macro Examples #3 • Sub PasteValues() • ' PasteValues Macro • ' Macro recorded 4/5/2000 by Chris Albright • Range("D8:D16").Select • Selection.Copy • Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ • SkipBlanks:=False, Transpose:=False • Application.CutCopyMode = False • End Sub • ' Cleaned up macro • ' Note: This macro is somewhat more general. It copies and pastes • ' to the current selection, whatever range it happens to be. • Sub PasteValues1() • With Selection • .Copy • .PasteSpecial Paste:=xlValues • End With • Application.CutCopyMode = False • End Sub

  6. Recorded Macro Examples #4 • Sub Formatting() • ' Formatting Macro • ' Macro recorded 4/5/2000 by Chris Albright • With Selection.Font • .Name = "Roman" • .Size = 10 • .Strikethrough = False • .Superscript = False • .Subscript = False • .OutlineFont = False • .Shadow = False • .Underline = xlNone • .ColorIndex = xlAutomatic • End With • With Selection.Font • .Name = "Times New Roman" • .Size = 12 • .Strikethrough = False • .Superscript = False • .Subscript = False • .OutlineFont = False • .Shadow = False • .Underline = xlNone • .ColorIndex = xlAutomatic • End With • Selection.Font.Bold = True • Selection.Font.ColorIndex = 3 • End Sub • ' Cleaned up macro - this one needs it! • Sub Formatting1() • With Selection.Font • .Name = "Times New Roman" • .Size = 12 • .Bold = True • .ColorIndex = 3 • End With • EndSub

  7. Recorded Macro Examples #5 • Sub ChartOnSheet() • ' ChartOnSheet Macro • ' Macro recorded 4/5/2000 by Chris Albright • Charts.Add • ActiveChart.ChartType = xlColumnClustered • ActiveChart.SetSourceData Source:=Sheets("Example5").Range("A5:B10"), _ • PlotBy:=xlColumns • ActiveChart.Location Where:=xlLocationAsObject, Name:="Example5" • With ActiveChart • .HasTitle = True • .ChartTitle.Characters.Text = "Grade Distribution" • .Axes(xlCategory, xlPrimary).HasTitle = False • .Axes(xlValue, xlPrimary).HasTitle = False • End With • ActiveChart.HasLegend = False • ActiveSheet.Shapes("Chart 3").IncrementLeft 67.5 • ActiveSheet.Shapes("Chart 3").IncrementTop 10.5 • ActiveWindow.Visible = False • Windows("RecordingFinished.xls").Activate • Range("A2").Select • End Sub • ' Cleaned up macro • Sub ChartOnSheet1() • Charts.Add • With ActiveChart • .ChartType = xlColumnClustered • .SetSourceData Source:=Sheets("Example5").Range("A5:B10"), _ • PlotBy:=xlColumns • .Location Where:=xlLocationAsObject, Name:="Example5" • .HasTitle = True • .ChartTitle.Characters.Text = "Grade Distribution" • .Axes(xlCategory, xlPrimary).HasTitle = False • .Axes(xlValue, xlPrimary).HasTitle = False • .HasLegend = False • End With • With ActiveSheet.Shapes("Chart 3") • .IncrementLeft 67.5 • .IncrementTop 10.5 • End With • ActiveWindow.Visible = False • Windows("RecordingFinished.xls").Activate • Range("A2").Select • End Sub

  8. Recorded Macro Examples #6 • Sub Sorting() • ' Sorting Macro • ' Macro recorded 4/5/2000 by Chris Albright • Range("D6").Select • Selection.Sort Key1:=Range("D6"), Order1:=xlDescending, Header:=xlGuess, _ • OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom • End Sub • ' Cleaned up macro • Sub Sorting1() • Range("D6").Sort Key1:=Range("D6"), Order1:=xlDescending, _ • Header:=xlYes • End Sub

More Related