{"id":717,"date":"2019-07-10T20:34:18","date_gmt":"2019-07-10T20:34:18","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=717"},"modified":"2019-07-10T20:39:21","modified_gmt":"2019-07-10T20:39:21","slug":"whats-the-use-of-macros-in-excel","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2019\/07\/10\/whats-the-use-of-macros-in-excel\/","title":{"rendered":"What’s The Use Of Macros In Excel?"},"content":{"rendered":"

Macros are used to make frequent tasks automated and easier. <\/p>\n

If you need to add a column to your Excel worksheet on a daily basis, just record a macro that adds a column.<\/p>\n

Or, you may need to update the sales reports you receive from the various locations your company does business in. So you’ll use a macro to update the emailed worksheet with a header before submitting it to management.<\/p>\n

Here is how that macro would be generated:<\/p>\n

You can create your Excel file as a “xlsx” (Excel file that doesn’t contain macros), and then save it as a “xlsm”.<\/p>\n

\"\"<\/a><\/p>\n

Tip: You are better off sending off the “xlsm” file instead of the “xlsx” file because you know ahead of time that the file is going to contain the “Header” macro.<\/p>\n

To create your “Header” macro you will do this:<\/strong><\/p>\n

Click on your “Developer” tab (See the http:\/\/www.vbastring.com\/blog\/about-me\/<\/a> if you need help) -> “Record Macro” -> then change the name of “Macro1” to something else like “Header”<\/p>\n

\"\"<\/a><\/p>\n

You’ll make this:<\/strong><\/p>\n

\"\"<\/a><\/p>\n

Look like this:<\/strong><\/p>\n

\"\"<\/a><\/p>\n

When you are finished doing your formatting, click “Stop Recording”<\/p>\n

\"\"<\/a><\/p>\n

Here is the macro code you just recorded:<\/p>\n

Sub Header()\r\n'\r\n' Header Macro\r\n'\r\n\r\n'\r\n    Rows(\"1:5\").Select\r\n    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove\r\n    Range(\"A1\").Select\r\n    ActiveCell.FormulaR1C1 = \"International Motors\"\r\n    Range(\"A2\").Select\r\n    ActiveCell.FormulaR1C1 = \"Budget Report\"\r\n    Range(\"A3\").Select\r\n    ActiveWindow.SmallScroll Down:=-3\r\n    Range(\"A1:D1\").Select\r\n    Selection.Font.Size = 10\r\n    Selection.Font.Size = 9\r\n    Selection.Font.Size = 8\r\n    Selection.Font.Size = 9\r\n    Selection.Font.Size = 10\r\n    Selection.Font.Size = 11\r\n    Selection.Font.Size = 12\r\n    Selection.Font.Size = 14\r\n    Selection.Font.Size = 16\r\n    Selection.Font.Size = 18\r\n    Selection.Font.Size = 20\r\n    Selection.Font.Size = 22\r\n    Selection.Font.Size = 24\r\n    Selection.Font.Size = 26\r\n    Selection.Font.Size = 24\r\n    Selection.Font.Italic = True\r\n    With Selection.Font\r\n        .Name = \"Times New Roman\"\r\n        .Size = 24\r\n        .Strikethrough = False\r\n        .Superscript = False\r\n        .Subscript = False\r\n        .OutlineFont = False\r\n        .Shadow = False\r\n        .Underline = xlUnderlineStyleNone\r\n        .ThemeColor = xlThemeColorLight1\r\n        .TintAndShade = 0\r\n        .ThemeFont = xlThemeFontNone\r\n    End With\r\n    Selection.Font.Bold = True\r\n    With Selection\r\n        .HorizontalAlignment = xlGeneral\r\n        .VerticalAlignment = xlCenter\r\n        .WrapText = False\r\n        .Orientation = 0\r\n        .AddIndent = False\r\n        .IndentLevel = 0\r\n        .ShrinkToFit = False\r\n        .ReadingOrder = xlContext\r\n        .MergeCells = False\r\n    End With\r\n    With Selection\r\n        .HorizontalAlignment = xlCenter\r\n        .VerticalAlignment = xlCenter\r\n        .WrapText = False\r\n        .Orientation = 0\r\n        .AddIndent = False\r\n        .IndentLevel = 0\r\n        .ShrinkToFit = False\r\n        .ReadingOrder = xlContext\r\n        .MergeCells = False\r\n    End With\r\n    Selection.Merge\r\n    Range(\"A2:D2\").Select\r\n    With Selection\r\n        .HorizontalAlignment = xlCenter\r\n        .VerticalAlignment = xlBottom\r\n        .WrapText = False\r\n        .Orientation = 0\r\n        .AddIndent = False\r\n        .IndentLevel = 0\r\n        .ShrinkToFit = False\r\n        .ReadingOrder = xlContext\r\n        .MergeCells = False\r\n    End With\r\n    Selection.Merge\r\n    Selection.Font.Size = 12\r\n    Selection.Font.Size = 14\r\n    Selection.Font.Size = 16\r\n    Selection.Font.Size = 18\r\n    Selection.Font.Italic = True\r\n    With Selection.Font\r\n        .Name = \"Times New Roman\"\r\n        .Size = 18\r\n        .Strikethrough = False\r\n        .Superscript = False\r\n        .Subscript = False\r\n        .OutlineFont = False\r\n        .Shadow = False\r\n        .Underline = xlUnderlineStyleNone\r\n        .ThemeColor = xlThemeColorLight1\r\n        .TintAndShade = 0\r\n        .ThemeFont = xlThemeFontNone\r\n    End With\r\n    Selection.Font.Bold = True\r\n    Range(\"A1:D1\").Select\r\nEnd Sub\r\n<\/pre>\n
*********** When you are ready, delete the top 5 rows, and just send out the new xlsm file containing the macro.<\/p>\n
Then when they send it back at the end of the month you just need to run the new “Header” macro like in the image below:<\/strong><\/p>\n
\"\"<\/a><\/p>\n
Sure macros can save time by automating repetitive tasks, but the problem is that most people can’t read the code they just recorded.  So if they want to change the macro a little bit, or delete all the extraneous keystrokes (because the macro recorder, records everything!), they have to use the macro recorder to record the macro all over again<\/u>.   <\/p>\n
The point of this site is to help tutor you in the “way” of VBA, and help you understand <\/strong>what you just recorded.<\/p>\n
To start learning Excel VBA click here:<\/strong><\/p>\n
http:\/\/www.vbastring.com\/blog\/excel-vba-tutorial-1\/<\/a><\/p>\n
Also I encourage you to 1st get the videos<\/a>, and sign up for my notification list.<\/p>\n
Let me know if you have any questions.<\/p>\n
****************************************************<\/p>\n\n\n
\n
<\/div>\n