1 of 13

EXCEL VBA MACROS: AN INTRODUCTION

… A PROFESSIONAL’S PERSPECTIVE

CA Vineet Gupta

B.Com, F.C.A, DISA(ICAI)

IP(IBBI), RV(IBBI)

📞 : +91-9811022855

: vineet@vrskca.com

2 of 13

WHAT IS A MACRO ?

A MACRO is a compilation of a set of actions or commands that can be run as many times as desired.
Task(s) in MS Excel that are to be done repeatedly can be recorded in a MACRO which can then be automated.
When a new MACRO is created, various commands, buttons, mouse clicks and even keystrokes are recorded.
A MACRO created, can be edited to make changes the way it is intended to work.
Various MACROS can be joined to create a super macro.

3 of 13

BENEFITS OF USING MACROS

Considerable Time Saving
Reduce the possibility of Human Error
Accuracy in Processing of Data
Increased Efficiency
Saving in Cost of Manpower

4 of 13

PRE-REQUISITES TO �GETTING STARTED WITH MACROS

Inclination to automize working on Spreadsheets.
Knowledge of various Functions and Formulas in Excel.
Remembering few basic commands to be used in MACRO creation.

5 of 13

HOW TO LEARN TO CREATE A MACRO?

Recording MACROS in Excel Sheet and Explore
Internet Surfing to find various solutions
Refer to Books on the Topic
Classroom Training

6 of 13

HOW TO LEARN TO CREATE A MACRO?.........CONT’D

Types of addresses

- Absolute Address : means say A5, Z8 etc

-Relative Address : means current cell or 2 rows down or 2 columns right of current cell etc

Ways of addressing a cell

-Range(“a5”) Range(“A5:G9”)

- Cells(5,1) range(cells(5,1),cells(9,7))

7 of 13

Some Simple Codes that we can repeatedly use while CREATING Macros

Select a range of rows	Rows("2:7").Select
Selecting a range of columns	Columns("B:L").Select
Selecting a range	Range("a8:g100").Select
Underline	Selection.Font.Underline = xlUnderlineStyleSingle
Delete	Selection.EntireRow.Delete
Insert	Selection.EntireColumn.Insert

8 of 13

Some Simple Commands that we can repeatedly use while CREATING Macros ……Cont’d.

To Find Last Used Row in a sheet	With ActiveSheet.UsedRange�lastrow = .Rows(.Rows.Count).Row�End With
To Find Last Used Column in a sheet	With ActiveSheet.UsedRange�lastcolumn = .Columns(.Columns.Count).Column�End With
Selecting a range with variable	Range("A8:G" & lastrow).Select
Sorting of Data	Recording the Macro
Bold	Selection.Font.Bold = True

10 of 13

LET US SEE SOME PRACTICAL USAGE OF MACROS IN ACCOUNTANCY PRACTICE

Preparing Balance Sheets in MS Excel using ODBC directly from Tally Data.

- Proprietorship Firm - Partnership/LLP

Transferring DATA from MS Excel to Tally.
Recording of Bank Statements received in .txt or .rpt format in Tally.

11 of 13

Practical usage of macros in Accountancy Practice � Contd…….

As Audit Tools such as analysis of debtors, creditors, Calculation of TDS liability in any account etc.
Analysis for and preparation of Bank Concurrent Audit Reports for higher data coverage in shorter time.
Creating Time Saving Everyday Use Utilities to Increase Efficiency and Boost Productivity.

13 of 13