Simple macros in excel by Mahathijs4

VIEWS: 8 PAGES: 6

									Simple Macros in
Excel
MS Excel
Table of Contents:
   INTRO TO MACROS ................................................................................................................................... 3
   HOW TO CREATE A MACRO? ..................................................................................................................... 3
   CREATING A SIMPLE MACRO ..................................................................................................................... 4
   HOW TO RUN A MACRO? .......................................................................................................................... 4
   MACRO CODE ........................................................................................................................................... 5




                                                                                                                                                         2
Intro to macros
    Macros are sets of instructions in Microsoft Excel for tasks to perform on a given worksheet or data.
By recording or programming certain actions, Excel users utilize macros to automate repetitive tasks,
format data sheets, or perform complex mathematical operations. By using macros, Excel users can
launch a virtually infinite number of tasks and processes with the simple touch of one button, greatly
speeding complicated spreadsheet work.
    Macros are easy to create: you tell Excel to start recording, perform actions as you normally do, and
then tell Excel when you're done. Behind the scenes, Excel uses a programming language called Visual
Basic® for Applications (VBA) to record your instructions. You don't have to know anything about
programming or VBA to create and use macros that will save you time and make your work easier. Get
started with macros and make your work simpler



How to create a macro?
    Excel macros can be created by writing a code in VB Script but the easiest way is to use the “Record
Macro” option in the “Developer” tab or under “Marco” in “View” tab, this doesn’t need any coding
knowledge and a sequence of commands you use to perform a certain task can be done by recording it in
3 simple steps.




    Or




Below are the steps to show how to create a macro
     Step 1: click on “Record Macro” a pop up appears as shown. Enter the appropriate ‘Marco name’ and
set a shortcut key to run this macro in future. Macro names must start with a letter and can include
letters, numbers, and underscore characters, but can't include spaces.




                                                                                                            3
    Step 2: Click on ‘OK’, now the macro started to record

    Step 3: After recording the macro click on “Stop recording” in the “View” tab to stop recording




Creating a Simple Macro
    This shows you how to create and use a simple macro that formats a cell, and then change this macro
to make it do even more. It also explains your options for storing macros so that you can use them from
any workbook. Now let’s create a simple Macro which will format the data in a cell. Consider the following
things to be done on a cell:
    1. Click on font
    2. Make it bold
    3. Highlight it with red
    4. Make the border more thick
    5. Then click on “OK”

These things can be done in just 1 click using Macros by following the steps below.

Step 1: Click on the cell which has to be formatted and “Record Macro” in the “View/Developer” tab. (If
the shortcut key is not set will not be a problem to run the macro and also this can be set in future)
Step 2: right click and select font, then perform all the activities necessary i.e. make it bold, highlight it
with red, make the border more thick and then click on “OK”
Step 3: Click on “Stop recording”.

    Now the simple macro has been created with the sequence of commands which you wanted. The next
question is how to run this macro; it’s again very easy like creating a macro.



How to run a Macro?
This is again very simple. So now let’s consider the same example stated above to create the macro and
see how it can be run whenever necessary.

Click on the cell which has to be formatted and then “Macros” and run




                                                                                                                 4
The macro can also be assigned to a shortcut key to run. Click on the “Options” and assign a key, just be
clicking CTRL and the assigned key the cell will be formatted as recorded in the Macro




Macro code
Macro is coded using VB script. The code of recorded macro looks like this:


Sub Macro1()
'
' Macro1 Macro
'

'
    With Selection.Font
      .Name = "Calibri"
      .FontStyle = "Bold"
      .Size = 11
      .Strikethrough = False
      .Superscript = False
      .Subscript = False
      .OutlineFont = False
      .Shadow = False
      .Underline = xlUnderlineStyleNone
      .Color = 255
      .TintAndShade = 0
      .ThemeFont = xlThemeFontMinor
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
      .LineStyle = xlContinuous
      .ColorIndex = xlAutomatic
      .TintAndShade = 0
      .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
      .LineStyle = xlContinuous
      .ColorIndex = xlAutomatic
      .TintAndShade = 0


                                                                                                            5
   .Weight = xlThick
 End With
 With Selection.Borders(xlEdgeBottom)
   .LineStyle = xlContinuous
   .ColorIndex = xlAutomatic
   .TintAndShade = 0
   .Weight = xlThick
 End With
 With Selection.Borders(xlEdgeRight)
   .LineStyle = xlContinuous
   .ColorIndex = xlAutomatic
   .TintAndShade = 0
   .Weight = xlThick
 End With
 Selection.Borders(xlInsideVertical).LineStyle = xlNone
 Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub




                                                            6

								
To top