LIVE GST Certification Course by CA Arun Chhajer begins 27th April. Register Now!!

How to create macro in excel - short and sweet way :)

CA CS CIMA Prakash Somani (Landmark Group) (23502 Points)

23 June 2010

1. Macro is used when you need to repeat a certain task
2. To run a macro you first need to record it
3. To record the macro go to View > Macro > Record macro
4. Once recording started it will show sign at the left side of status bar
5. then you can start all the activities you need to repeat
6. Click on icon again to stop the recording
7. Press Ctrl+S > it will show you a massage click no and save the file as Excel macro-enabled workbook
8. Keep this file open and open a new excel file
9. open the file where you want to run the macro and click on run macro
10. Do write your comments in case of any issues or if you like it

Reply
Follow
Share

8 Replies

CMA Sonu... (Asst. Manager - Finance) (158 Points)
Replied 23 June 2010

Wonderful piece of work sir ...

Just one correction..

correct path is

Tools > Macro >Record

Thanks,

-sonu

CA. Shwetank Vats (Chartered Accountant) (653 Points)
Replied 23 June 2010

Thanks sir

CA CS CIMA Prakash Somani (Landmark Group) (23502 Points)
Replied 24 June 2010

Hi Sonu, the path you have provided must be in excel 2003... the path i provided is for Excel 2007 and for your information now EXCEL 2010 is already in market

1 Like

Mukund Patel (Student) (477 Points)
Replied 24 June 2010

Sir Can you Give me one Example of it...........

Prem Kumar (Articled student--Final) (375 Points)
Replied 24 June 2010

Hello Everyone,

To startup with the topic of A macro is a series of commands and functions that are stored in a Visual Basic module and can be run whenever you need to perform the task, Macro can be Created using two ways

1. Using Macro Recorder

2. Using VBA

Since Using Macro Recorder has been explained by Sir("CA Somani") i would like to explain on the VBA.

Coming to VBA it stands for Visual Basic For Applications it can be used in applications like (Excel, word, PowerPoint etc..)

Press Alt+F11

You can see the Microsoft Visual basic opens

On the left hand side you can see the Project explorer(or you can view it form (Pressing Ctrl +R)

now using Project Explorer you can browse the entire Project

You we need to understand meanings for few terms like Class, Object, Method, Properties...etc

Class: Class can be defined as a MODULE that helps the objects and servers as a templet for Objects

Objects in VBA:An object represents an element of an application, such as a worksheet, a cell, a chart, a form, or a report. Some of the Objects are given for further reference

Workbooks, Worksheets, Range, Column, Row, Selection, Windows, Charts

Methods refer to action that has to be taken on the object by the computer. Some
common methods are Activate, Select, Add, Clear, Close, Copy, Cut, Insert, Move, Open, Printout, Quit, Save, Select...etc

A property is an attribute of an object that defines one of the object's characteristics, such
as size, color, or screen location, or an aspect of its behavior, such as whether it is
enabled or visible. To change the characteristics of an object, you change the values of its
properties.
To set the value of a property, follow the reference to an object with a period, the
property name, an equal sign (=), and the new property value.

Due to lack to time i am stopping my discussion on this...

send your valuable suggestions and queries on this post

Regards

Prem Kumar

2 Like

Piruthivi rajan (Student) (56 Points)
Replied 24 June 2010

Hello sir,

Really its very great, particularly your clarification relating to excel version.

Prem: Please provide some more details relating to Macro via VBA because i didnt understand how to save and run the macro?

Thanks in advance....

Vikky Vasvani (Executive -Accounts & Finance) (321 Points)
Replied 25 June 2010

Dear Mr. Prakash Ji , & Mr. Prem Kumar

Thanks for about important topic about Excel Macro. This is very good option to save time. But this is not eary as it we know Macro + VBA . we can do many things. Pls give us full information about

Excel Macro + VBA . Because " A Little Knowledge is Dangerou."

Thanks in Advance.

Regards,

Vikky Vasvani