VBA For Excel

i

Website: - www.booksclinic.com B.D. Complex, Near Tifra Over Bridge, Bilaspur, Chhattisgarh, India, 495001

This book has been published with all reasonable efforts taken to make the material error-free after the consent of the author. All rights reserved no part of this book may be reproduced, stored in a retrieval system, or transmitted, in any form by any means, electronic, mechanical, magnetic, optical, chemical, manual photocopying, recording or otherwise, without the prior written consent of its writer. ISBN: 978-93-90192-99-1 Book: Academic First Edition Published by Booksclinic Publishing 2020 Copyright © Hirdesh Bhardwaj 2020 Price: Rs.210/First Edition: 2016 Revised Edition: 2020

Printed in India

ii

VBA For Excel Absolutely for Beginner

By

Hirdesh Bhardwaj

iii

iv

About the Author Hirdesh Bhardwaj, the Founder and CEO of Webs Jyoti - Development & Training Organization is known for his excellent training and development skills. Well known as innovator in the field of IT, he has more than 13 years of experience with the well-reputed MNCs and Institutions. His Books are referenced & taught in NIELIT -Govt. of India, Jiwaji University Gwalior, MMYVV University, Mangalore University, J.C Bose University & Directorate of School Education Kashmir. Hirdesh has conducted corporate training and lectures at Olam Agro, Cars 24, Nestle, Beumer Group, PGS Partner, GET Global, Process 9, Hema Engineering, National Security Guard- NSG HQ, Realistic Realtors, India Shelter Finance Corporation, Jastech Systems, National Skills Foundation of India, MB Informatics, SAP Lab India, MB Informatics, Alpine India, FIA Global, Pace Academy & Dudhi Plasters.

Our Services @ Webs Jyoti Webs Jyoti is a professional Web development and Information Technology (IT) organization based in Gurgaon, offering web designing and Information Technology services for the global entrepreneurs. We provide web site development services to clients across INDIA and other parts of the world. We specialize in web site design, Education Software‟s, e-commerce & CMS.

v

vi

Thank You I would like to thank all of these people who has always being supportive from time to time:  My Parents, my God and Goddess, for their kind wishes and blessings.  My beloved wife Chanchal, appreciate the support provided by her.  My 2-year old son Vansh, who thinks that his Dad writes such long books, works for the websites, takes training and seminars, but wishes it included chapters like Cartoon Corner and Ninja's Stories.  My Sisters Mona and Preeti (Like a daughter too), who wished his brother, would devote more time with them instead of working on this book.  My Younger Brother Jaiprakash, who always complained that his brother always scold him to work hard and to walk in the early morning to lose his weight.  My team @ Webs Jyoti, like a family they think that sir treat them still as student and force to learn something new every day.  My creative team, P.SerignNdiaye, Jeff Sidabutar andHernan Apraez Pastrana who always ask me to put more and more real time examples from our development house.

vii

viii

Special Thanks Mr. Rajendra Kapoor Ms. Manju Sharma Ms. Sunita Rathi Ms. Manasi Mohapatra Ms. Farzana Anjum

ix

Content list S.no.

Content

Page

1.

Introduction to VBA Macro Introduction to VBA Macro Basics and its programming scope. Message Boxes in VBA Learn MsgBox, InputBox and various input/output Windows in VBA. VBA Excel - Cells, Ranges and Offset Working with Excel„s Range, Cells and Offset in VBA. Error Handling in VBA Learn how to handle errors and exceptions in VBA Programming Select Case – VBA Learn how to use Select Case (Switch Case) Statements in VBA. VBA - Loops Learn the concepts of loops and flow control statements in VBA. VBA – Arrays What are arrays in VBA Macros – single and multi-dimensional arrays. VBA - Workbook & Worksheet operations Explore the major activities of Workbook and worksheet in VBA. VBA - Events Learn the different type of Events and procedure in VBA VBA - Filters and Reporting Explore excel Filters (Auto Filter, Advanced Filters) and Pivot Table using VBA.

1 - 25

2.

3.

4.

5.

6.

7.

8.

9.

10.

x

26-47

48-62

63-71

72-77

78-100

101-108

109-114

115-121

122-135

11.

12.

13.

VBA - User Form & Controls Explore the World of GUI- User Form and Controls in VBA VBA - Send Automated Mail Explore the Automated Mail and outlook mailing activities in VBA. VBA - Database Connection Retrieve data from database, querying data into excel using VBA DAO.

xi

136-153

154-164

165-178

xii

Hirdesh Bhardwaj

Introduction Completing this book is one of the hardest things that I have ever done. It took me almost two years to get it into the form you are reading. At most places I have tried to show how Macros work and more importantly why we use VBA Macros in the real time data. Most of the examples of this book are taken from our training sessions and from our in-house development projects. More than anything else, I have tried to design this book for a programmer struggling with a new and complex programming. I have poured my best efforts into these pages. I trust you would find this book useful.

We Want to Hear from You! We value your feedback and want to know what we‟re doing right, what we could do better, and any other wisdom you‟re willing to pass our way When you write, please be sure to include this book‟s title as well as your name, email address and phone number.

For feedback & suggestions email me: [email protected]

1

VBA For Excel

What is a macro? A macro is a set of code that runs in Office Application and helps us to automate tasks that we frequently use in corporate world. Let's assume I should prepare a report in Excel and as for this I need to use content of many worksheets. In making a report, I generally use the following steps which are: -Open Workbook -Open desired worksheet -Make selection of the data -Use Auto Filter and other relevant task -Copy the desired data after sorting and filtering -Now open another workbook / worksheet to paste selected data for making dashboard I use all the steps above for so many times to prepare reports on daily basis data. As you noticed, the steps above is more likely consuming amount of time and therefore, I use macro to automate the same task in a single click. While you are writing the code, you should have a clear idea of what it does and how it works otherwise your macro may stuck and your entire work may interrupt. Hence, VBA Macro is the best solution for the steps above. So in short, a macro is a recording or capturing of your task step by step you takes in Excel that you can follow by using a single button..

2

Hirdesh Bhardwaj

What is VBA? You recently aware with macros, so now let‟s get to know more with another term called "VBA- Visual Basic for Applications". VBA is a programming language, developed by Microsoft. Before you think about the programming language, it is essential to know that programming languages are used to communicate with your computer and not to human. Therefore, VBA is a tool to communicate and passes your instructions to Excel or any office application. However, macro is not a programming language. As explained by John Walkenbach (one of the most popular author) "A macro is a sequence of instructions that automates some aspect of Excel."

Tools Before you are going to write your first VBA code, you need to check whether the VBA Editor is installed on your computer. By pressing ALT+F11 is a quick way to test on either your Visual Basic has been installed or not. If the VBA editing window opens you are ok, otherwise you need to install VBA Editor first.

3