Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 37 views

    Writing A Macro

    Uploaded by

    SAUGATA RAY
    This document provides instructions for writing macros in Microsoft Excel using Visual Basic for Applications (VBA). It explains that macros can automate repetitive tasks and customize Excel documents. The instructions describe how to open the VBA editor, create modules to store macros, and write code using VBA language to perform tasks like looping through rows, formatting cells, combining data from multiple cells, and changing cell colors or deleting duplicate data. The document also provides steps for assigning existing macros to objects in Excel worksheets and deleting or copying macros.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd

    Writing A Macro

    Uploaded by

    SAUGATA RAY
    37 views8 pages
    This document provides instructions for writing macros in Microsoft Excel using Visual Basic for Applications (VBA). It explains that macros can automate repetitive tasks and customize Excel documents. The instructions describe how to open the VBA editor, create modules to store macros, and write code using VBA language to perform tasks like looping through rows, formatting cells, combining data from multiple cells, and changing cell colors or deleting duplicate data. The document also provides steps for assigning existing macros to objects in Excel worksheets and deleting or copying macros.

    Original Description:

    Original Title

    writing a macro

    Copyright

    © © All Rights Reserved

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides instructions for writing macros in Microsoft Excel using Visual Basic for Applications (VBA). It explains that macros can automate repetitive tasks and customize Excel documents. The instructions describe how to open the VBA editor, create modules to store macros, and write code using VBA language to perform tasks like looping through rows, formatting cells, combining data from multiple cells, and changing cell colors or deleting duplicate data. The document also provides steps for assigning existing macros to objects in Excel worksheets and deleting or copying macros.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    37 views8 pages

    Writing A Macro

    Uploaded by

    SAUGATA RAY
    This document provides instructions for writing macros in Microsoft Excel using Visual Basic for Applications (VBA). It explains that macros can automate repetitive tasks and customize Excel documents. The instructions describe how to open the VBA editor, create modules to store macros, and write code using VBA language to perform tasks like looping through rows, formatting cells, combining data from multiple cells, and changing cell colors or deleting duplicate data. The document also provides steps for assigning existing macros to objects in Excel worksheets and deleting or copying macros.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    of 8

    How to Write a Macro Coding MS Excel

    By Kyoko Walton, eHow Editor


    Rate: (1 Ratings)
    Macros are bits of code that can run repetitive tasks from within the Excel
    workbook . Learning to write a Macro for Excel will empower you to take control
    over the documents and data you work with on a daily basis. In short, this article
    will instruct the reader in how to create bits of code that will revolutionize your
    multitasking experience with Excel. Macros produce a specific effect in your
    Excel documents. The language used in Macros is Visual Basic for Applications
    (VBA). You will use this language to create modules attached to the Excel
    workbook, wherein you will store your macros. Macros will really add functionality
    and ease of use to your Excel documents. With macros, you can organize
    multiple sheets of data into one sheet or formulate data on a sheet to meet any of
    your user requirements.
     Email
     Send to Phone
     Print Article
     Add to Favorites
     Flag Article

    How to Write a Macro Coding MS Excel


    By Kyoko Walton, eHow Editor
    Rate: (1 Ratings)
    Macros are bits of code that can run repetitive tasks from within the Excel
    workbook . Learning to write a Macro for Excel will empower you to take control
    over the documents and data you work with on a daily basis. In short, this article
    will instruct the reader in how to create bits of code that will revolutionize your
    multitasking experience with Excel. Macros produce a specific effect in your
    Excel documents. The language used in Macros is Visual Basic for Applications
    (VBA). You will use this language to create modules attached to the Excel
    workbook, wherein you will store your macros. Macros will really add functionality
    and ease of use to your Excel documents. With macros, you can organize
    multiple sheets of data into one sheet or formulate data on a sheet to meet any of
    your user requirements.
     Email
     Send to Phone
     Print Article
     Add to Favorites
     Flag Article

    Instructions
    Write a Macro in MS Excel Using the VBA Editor
    1. Step 1

    Click the Tools drop-down menu in Excel. Point to "Macro". Click on "Visual
    Basic Editor".
    2. Step 2

    Create a place to store your macro. In the Insert menu on the VBA editor,
    click "Module". The module will open in a new window. This module will
    house the macro that you are going to write. The module is actually stored in
    a folder labeled “VBA Project”.
    3. Step 3

    Type the word “Sub” in the blank window. Do not write over the text that
    reads “End Sub”. After the Sub, type a name for your macro, like
    “MyLoopMacro”. A loop is the easiest macro to write. A loop will count rows
    automatically until it reaches a specified point to stop. This can be useful for
    large amounts of data on a spreadsheet.
    4. Step 4

    Tell the loop to stop on an empty cell. In the VBA window, type the following
    code: Do While Cells(x, 1). Value < > “”X = x + 1Y = y + 1Loop
    5. Step 5

    Tell your macro that you want the word “Name” to appear darker than other
    text throughout your Excel workbook. Add the following code in the VBA
    editor:IfMyCell.Value Like “Name” ThenMyCell.Font.Bold = TrueEnd If
    6. Step 6

    Code your macro to do more with cells in a spread sheet. This is just an
    example but one that you can use for a number of things in a macro. You
    are going to write code that will combine two cells into an additional cell.
    This could be valuable if you have a spreadsheet that can combine first and
    last names, pair data together or for any other reason you’d want to carry
    data over to another column in the worksheet. Type the following code in the
    VBA editor: Sub LoopRange1() X = 3 Do While Cells(x, 3). Value <> “ ”
    7. Step 7

    Add more code to your macro. So far you have assigned the value “x” to
    start at row 3 in your worksheet. You have also created a command to loop
    until a blank row is discovered by the macro. Next, type the following three
    lines of code to carry the values of the third and fourth columns together with
    a space between them in the fifth column: Cells(x, 5). Value = Cells(x, 3).
    Value + _ “” + Cells(x, 4). Value x = x + 1
    8. Step 8

    Make the cells change color to indicate a word discovered in a spreadsheet.


    This could be very useful for finding specific data entered by marketing. Now
    you are going to create the variable in your code to look for the word
    “competitor” in the worksheet. Type the following code into the VBA window:
    Sub LoopRange2()Dim CompetitorCell As RangeThis code declares the
    variable “CompetitorCell”. Now hit "Enter" to create a space between the last
    string of text in your code. Type the following code.If CompetitorCell.Value
    Like “*Competitor*” ThenMyCell.Interior.ColorIndex = 3ElseIf MyCell.Value
    Like “*Movie*” Then Mycell.Interior.ColorIndex = 4ElseIf MyCell.Value = “”
    ThenMyCell.Interior.ColorIndex =xINoneElseMyCell.Interior.ColorIndex =
    5End IfNextEnd Sub
    9. Step 9

    Create a macro that will delete duplicate data in your worksheet. This can be
    wonderful for tracking duplicate accounts in your spreadsheet system. Type
    the following code to create and test your own macro. Sub LoopRange3()X
    = ActiveCell.RowY = x + 1Do While Cells(x, 4). Value <> “ ”Do While Cells(y,
    4). Value <> “ ”If (Cells(x, 4).Value = Cells(y, 4).Value) _And (Cells(x,
    6).Value = _Cells(y,6).Value) ThenCells(y, 4).EntireRow.DeleteElsey = y +
    1End IfLoopx = x +1y = x +1LoopEnd Sub
    10. Step 10

    Close the VBA window. Click Save. Your macro has been created. The code
    becomes easy to understand when you practice with it. All you need to do is
    change the variable names, the loop commands and the cells in the
    spreadsheet that you want your macros to perform a task with.

    Assign an Existing Macro to an Object in Excel


    11. Step 1

    Right-click on any object tin your worksheet, such as a graph, chart or


    company logo.
    12. Step 2

    Left-click "Assign Macro". A side-out menu appears after you right-click,


    which you then can select options from. In this case, just click "Assign
    Macro".
    13. Step 3

    Click on the macro you want to assign from the Macro name box.

    Delete a Macro from Excel


    14. Step 1

    Click on the File menu in Excel. Click on "Open Workbook". If the workbook
    is hidden in the Personal.xlsb macro workbook, then you need to unhide the
    workbook.
    15. Step 2

    Click on "View", then click "Unhide".


    16. Step 3

    Click "PERSONAL" under the Unhide workbooks tab and then click "OK".

    Create a Macro from an Existing Macro


    17. Step 1

    Open the workbook that contains an existing macro that you want to copy a
    portion of for your new macro.
    18. Step 2

    Click on "Macros". This is located in the Developer tab in Windows Vista or


    under Tools for other versions of Windows.
    19. Step 3

    Click on the name of the macro you wish to edit.


    20. Step 4

    Click "Edit".
    21. Step 5

    Select the code from the code window that opens with your mouse. Press
    Ctrl+C to copy the selection. Click the module in the Procedure box where
    you want to place the copied code. Click Ctrl+V to paste the code.

    How to Build Macros in MS Excel and


    Word
    By Donna Rieger, eHow Editor
    Rate: (3 Ratings)
    Some repetitive tasks in Microsoft Word or Excel can be recorded, saved and
    run by clicking a toolbar shortcut. The process is pretty easy and can help teach
    you how to use macros to make your life easier and also start you on the road to
    compiling and writing your own Visual Basic modules. Follow these instructions,
    then use your imagination to build macros that relate to the work that you do
    most.
     Email
     Send to Phone
     Print Article
     Add to Favorites
     Flag Article

    Difficulty: Moderate
    Instructions
    Things You'll Need:
     Computer with Internet access
     Microsoft Word or Excel
     Paper

    1. Step 1

    Build a macro for text formatting. With a document or spreadsheet open,


    from the "Tools" menu select "Macro," then "Record New Macro." In this
    window, type in a name for your macro (such as "FormattingForProjectA" or
    "FormattingSpecial"). In MS Word be sure that the "Store Macro In:" is set
    to "All Documents" so that you can use the macro in any word document. In
    MS Excel, store the macro either in "This Workbook" or the" Personal Macro
    Workbook," depending on whether the macro will be used in more than one
    workbook.
    2. Step 2

    "Record Macro" and "Stop" windows.

    Click "OK." You will notice a small window named "Stop" at the top. Move
    this window if necessary, but do not close it, as this stops your macro
    recording. Select the text that needs to be changed and make the necessary
    changes such as bold, underline, italic, text color or highlighting. When you
    are finished, click the solid square in the small "Stop" window and your
    macro recording will stop.
    3. Step 3

    Add your macro to a toolbar for one-click use. In MS Word, right-click a


    toolbar and select "Customize." On the "Commands" tab of the "Customize"
    window, select "Macros" from the "Categories" column on the left. Then find
    the macro you just named in the "Commands" column on the right and drag
    it to the toolbar. With the "Customize" window open, right-click your new
    toolbar shortcut to change the name (just the shortcut name not the macro
    name), add a button icon or perform other formatting. When you are
    finished, close the "Customize" window. In Excel, right-click a toolbar and
    select "Customize." On the "Commands" tab of the "Customize" window,
    select "Macros" from the "Categories" column on the left. Drag the custom
    button to the desired toolbar. With the "Customize" window open, right-click
    your new toolbar shortcut and select "Assign Macro." Find and select your
    new macro and click "OK." You can at the same time change the name (just
    the shortcut name not the macro name), change the button icon or perform
    other formatting. When you are finished, close the "Customize" window.
    4. Step 4

    Add the macro to a toolbar.

    Now you can test your macro by selecting some text and clicking the
    shortcut. If the macro does not work as expected, you can try re-recording
    by going through all of the steps above again (you can delete the old
    shortcut when you open the "Customize" window). Another way to fix a
    recorded macro is to go to the "Tools" menu, select "Macro" and "Visual
    Basic Editor."
    5. Step 5

    Visual Basic Editor


    Learn some simple Visual Basic writing by examining recorded macros and
    combining or changing them while using the program help files and the
    Microsoft knowledge base as learning tools. Record multiple macros that
    perform different actions and combine them to further automate a single
    shortcut. Or find ways to enhance your macros by organizing entire new
    toolbars containing multiple macro shortcuts for formatting, changing
    margins, finding and replacing data or just opening menu items to name a
    few (see Resources below).

    How to Record an Excel Macro


    By eHow Computers Editor
    Rate: (16 Ratings)
    A macro automates a complex task. Excel macros can perform complicated
    series of actions or simply record commonly used commands. These instructions
    apply to Excel 97.
     Email
     Send to Phone
     Print Article
     Add to Favorites
     Flag Article

    Difficulty: Moderate
    Instructions
    Things You'll Need:
     Excel Book
     Microsoft Excel

    1. Step 1

    Open the Tools menu and select Macro, then select Record a Macro.
    2. Step 2

    Fill in the blanks - the macro name, description, shortcut-key combination


    and worksheet where the macro will be available - in the Record Macro box
    that appears. The macro can be available from only one worksheet or from
    any worksheet.
    3. Step 3

    Click on OK.
    4. Step 4
    Notice that the word "recording" appears in the lower left of the Excel screen
    while recording is in progress.
    5. Step 5

    Perform a series of keystrokes or mouse actions. The macro will record


    them. You can record any normal Excel action.
    6. Step 6

    Open the Tools menu and choose Stop Recording to complete the macro.
    7. Step 7

    Activate the macro by pressing the shortcut keys you have selected or by
    selecting the macro from a list on

    You might also like