Go Back   TechArena Community > Software > Tips & Tweaks
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to write Macro in Microsoft Excel 2010

Tips & Tweaks


Reply
 
Thread Tools Search this Thread
  #1  
Old 06-06-2012
Member
 
Join Date: Mar 2011
Posts: 120
How to write Macro in Microsoft Excel 2010
  

Macros are very efficient programs in Microsoft Excel. They help you to perform regular task and save your time. Many time while making certain report or using Excel you need to perform some clicks regularly. Excel Macro allows you to record those keystrokes and perform regular action again and again. Till yet it is used only by advance users. But Microsoft Excel 2010 you get a simpler option and easy to manage advance task via Macro. I had seen and tested this number of times. With your pre-define keys you can activate macro and let Excel perform your regular task. Macros are recommended for those who need to perform same type of job again and again.

Macro helps to save your time. Macro is a feature excels which record your keystroke, clicks, etc. It is very easy to execute and can be recorded frequently. It is also possible to perform some kind of complex operating via it. I had listed both types of work below. If you are using this first time then there are some exercise that make you use to Macros. For example getting a Date in Excel sheet via macro. For that you will need to type = TODAY() or =NOW() function in the cell of Excel. Do one thing Run Macro recording and then type those in a cell. Once you are done save the Macro. Now next time when you need today’s date, just choose a cell and run Macro. The date will be inserted by adding that formula.

Second thing you can use Macro's is for applying formatting. I have number of reports which are formatted on the bases of my requirement. Like numbers marked with Red are negative while with green are positive. Now in order to create a new report I might need to add those one more time. I use Macro instead of making those changes one by one. Macros are very easy to use and help you to run automated task. Also Macro can record a number of commands and store them for your future reference. Macro records everything that you do. To run that you just need to execute the saved macro. The dialog box appear at the top from where you can run it. You can also assign a set of key combination for that.

To work with advance Macro programming you can use Visual Basic Editor. This tool allows you to edit Macro and I will recommend this if you want to learn the advance process. For beginners it is recommended that you must use Office online help. Anyhow for security purpose you must not open files or run Macros from files which you receive in email. There can be virus. So better disable macros before opening an office file. You can certify your files with Digital Signature.

Create a Macro

Go in View Menu and then click on Macros. In that click on Record Macros. You can see a dialog box on the screen. You will need to add details related to Macro you are creating. Like the name or any other information you want to specify. Later on the next box is for providing a keyboard shortcut which can activate the macro. Once you are done with detailing stuff click on Record Macro. Now do your regular task. The Macro will record information and keep in safe. Macros in Excel record all the steps that you perform. You have to press the right keys to get the most accurate setup. Once you are done can then see it by clicking on View Macros.

Edit Macro from Visual Basic

In Microsoft Office 2010 you will need to enable Developer tab from Excel Options. Once you are done you can see Macro tab. With the help of Visual Basic Editor you can run and edit your existing Macros. Hit F11 from your keyboard to run Visual Basic Editor. The window of Visual Basic Editor is divided into three parts. The first is project window then comes code and then properties windows. You can keep them on to find the right modification options. First for practice create a Macro. You can see your spreadsheet listed on the left side. Then click on View > Project Explorer. Under this you can find your sheet. You can dock this item on the screen for reference. In order to add a code you will need to double click on the object selected from Project Window. Do modification as per your need. Remember that this is only for advance users and those who are just learning needs to start from spreadsheet.

The above method if for beginners. I will explain in more detail about using Macro in Microsoft Office 2010. Macro is one of the most powerful options in Excel. Office 2010 gives you more accurate options to configure macro and perform repetitive task. I will not recommend you to use this if there are even minor changes in your work. Because one you hit run Macro button it will make changes as per setup done before. It is necessary to analyze data after running it. Because if there is some modification done that you do not like, you can modify the same. For advance users I will advice to use VBA programming. The reason is to use Macro in more advance way and to perform more complicated task in easy manner. Excel has Visual Basic support. So this is just the beginning of learning .

Writing your own code using VBA is simpler this time. On the basis of day to day work you can generate an application for your work. There are some simple things which are needed to be following. Also you must understand some basic concept of programming. The applications in Microsoft office are created from stuff called as Objects. Object are the one which act as a bridge between application and the commands you send. So here there are different objects that can be used. If you are thorough with objects then creating macros will be an easy job. First just get a short overview of objects. Application is based on set of Object models. This are the one which accumulate to become an entire applications or UI. You work with objects by modifying the properties. The changes you done will affect the working. They are also called as Methods. So by default there is certain Method assigned to each object. Then comes the Collection. The collection is set of everything. I will recommend you to learn more about Object model so that you can get easy with Visual Basic Editor.

Reply With Quote
  #2  
Old 06-06-2012
Member
 
Join Date: Aug 2011
Posts: 539
Re: How to write Macro in Microsoft Excel 2010

That actually looks a very advance process. It might take some one time to understand the advance way to use Macros. Anyhow is there a simple video tutorial available on that.
Reply With Quote
Reply

  TechArena Community > Software > Tips & Tweaks
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to write Macro in Microsoft Excel 2010"
Thread Thread Starter Forum Replies Last Post
VB Script or batch file to run Excel Macro 2010 Fakhry Software Development 2 19-06-2012 12:23 PM
How to rename from button with macro in Microsoft Excel Kanshin MS Office Support 16 24-02-2012 12:59 PM
Need Macro command to print and email a pdf file in Microsoft Excel Tritanic MS Office Support 3 25-01-2012 11:03 AM
How to Write Equations in Microsoft Word 2010 bajiraoS Windows Software 3 09-09-2011 10:23 PM
How to Write Macro in Excel using VBA Trog Guides & Tutorials 3 10-02-2011 06:25 PM


All times are GMT +5.5. The time now is 08:30 AM.