Excel With VBA

What’s The Use Of Macros In Excel?

Macros are used to make frequent tasks automated and easier.

If you need to add a column to your Excel worksheet on a daily basis, just record a macro that adds a column.

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.

Here is how that macro would be generated:

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

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.

To create your “Header” macro you will do this:

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

You’ll make this:

Look like this:

When you are finished doing your formatting, click “Stop Recording”

Here is the macro code you just recorded:

Sub Header()
'
' Header Macro
'

'
    Rows("1:5").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "International Motors"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Budget Report"
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=-3
    Range("A1:D1").Select
    Selection.Font.Size = 10
    Selection.Font.Size = 9
    Selection.Font.Size = 8
    Selection.Font.Size = 9
    Selection.Font.Size = 10
    Selection.Font.Size = 11
    Selection.Font.Size = 12
    Selection.Font.Size = 14
    Selection.Font.Size = 16
    Selection.Font.Size = 18
    Selection.Font.Size = 20
    Selection.Font.Size = 22
    Selection.Font.Size = 24
    Selection.Font.Size = 26
    Selection.Font.Size = 24
    Selection.Font.Italic = True
    With Selection.Font
        .Name = "Times New Roman"
        .Size = 24
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("A2:D2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Selection.Font.Size = 12
    Selection.Font.Size = 14
    Selection.Font.Size = 16
    Selection.Font.Size = 18
    Selection.Font.Italic = True
    With Selection.Font
        .Name = "Times New Roman"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Selection.Font.Bold = True
    Range("A1:D1").Select
End Sub

*********** When you are ready, delete the top 5 rows, and just send out the new xlsm file containing the macro.

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:

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.

The point of this site is to help tutor you in the “way” of VBA, and help you understand what you just recorded.

To start learning Excel VBA click here:

http://www.vbastring.com/blog/excel-vba-tutorial-1/

Also I encourage you to 1st get the videos, and sign up for my notification list.

Let me know if you have any questions.

****************************************************

admin