Write a Complicated Microsoft Office Macro
You may be aware that Microsoft Office has a feature to create custom macros. This feature allows a user to create an automated process that he/she wishes to perform over and over again. If you're not a programmer or just not familiar with Microsoft Office, it may be frustrating to have such a powerful feature and not be able to use it to its full potential. Following these steps will increase your chances of successfully creating your own macro and learning something new in the process.
Contents
Steps
- Write down the individual steps you want your macro to accomplish. Write one step per line. Think like a computer. The macro is a set of commands telling your computer exactly what to do and gives all the information needed to do it.
- Record several small macros. Each macro should perform a part of the task of your complete macro.
- Open Visual Basic Editor. You will find the code for your recorded macros in several modules within either your current workbook or your Personal workbook.
- Combine the macros under one main macro using your written procedure as a guide. Examine the code in each macro to understand what the code means, then copy and paste it into your main macro in the correct order that it needs to be executed.
- Modify the code to fit your procedure. The recorded macros may not fit exactly what you want to do. You may have to modify the code if you want to change the macro's behaviour.
- Copy the code into a search engine. If you just can't grasp what the code is meant to do, Find Information Online by copying the code into the search engine and adding key words like "excel", "macro" or "VBA".
- Run the macro line by line. While in Visual Basic Editor, you may press the F8 key to enter debugging mode and run your macro line by line. You may also create watches for each variable in your macro. Use these tools to see exactly what each line of code is doing.
Tips
- Office.microsoft.com is an excellent source for information on writing macros.
- Go to Support.microsoft.com/search/ search term "excel macros" for examples of processes that you cannot duplicate through recording macros. (Example: Loops; If-then processes).
Warnings
- Syntax errors are a common source of frustration in writing working macros. Look for common syntax errors such as missing commas and parentheses.
