How to Write a Macro Coding MS Excel

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.

Difficulty: Moderate

Instructions

Write a Macro in MS Excel Using the VBA Editor

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

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. 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. 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. 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. 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. 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. 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. 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. 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. Right-click on any object tin your worksheet, such as a graph, chart or company logo.

12. 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. Click on the macro you want to assign from the Macro name box.

Delete a Macro from Excel

14. 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. Click on "View", then click "Unhide".

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

Create a Macro from an Existing Macro

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

18. Click on "Macros". This is located in the Developer tab in Windows Vista or under Tools for other versions of Windows.

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

20. Click "Edit".

21. 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.