Data Analysis and Management
Advance VBA Programming

Advance VBA Programming

Course Duration : 40 Hrs

Advance VBA Programming course, to get in-depth of VBA, and to work on multiple Microsoft applications being offered by ACLM Institute of Professional Studies. Visual Basic for Application (VBA), a powerful tool by Microsoft Inc., provides flexibilites to work on all Microsoft applications like MS-Word, MS-Excel, MS-Power Point, MS-Outlook. Even the VBA is capable to run on multiple browser.

The primary focus under Advance VBA Programming course is Excel with VBA & SQL. Therefore, we request candidate(s) to have good command over excel with respect to formulation, formatting and various excel tools.

Advance VBA Programming course is distributed into three major sections:

Section 1: Excel Macros Programming

In this section we’ll teach to record, write, modify and test macros in the Visual Basic Editor. You will also learn about security and discover “events” (an event is what starts the macro).

Section 2: Excel VBA Vocabulary

Developing a macro is communicating with Excel and to do so you need to use a language called Visual Basic for Applications (VBA). In section 2 you will learn all the VBA vocabulary that is essential to work with business data (accounting, sales, production and others).

Section 3: VBA Forms and Controls for Excel

The userform is a small or large dialog window that you create and allows the user to submit their inputs, that will be used by your macros / programme. To those userforms, you will add controls (command buttons, text boxes, list boxes and others) and program them.

Topics Covered Under Advance VBA Programming

Section 1: Advance VBA Programming

Visual Basic Editor (VBE) in Excel Advance VBA Programming 
  • The Visual Basic Editor is the user friendly program that you will use to talk with Excel. In it you can  create your VBA procedures (macros) and userforms. You will then be able to modify and test these components easily step by step in the VBE.
The Project  Window in the Visual Basic Editor of Excel
  • The Project Window lists all your open projects with their sheets, modules and forms. In the Project Window you will  add modules and create forms. When you select one of the components its properties will show in the Properties Window and the VBA code that you have created for the selected component will appear in the Code Window.
The Properties  Window in the Visual Basic Editor of Excel
  • The Properties Window shows you the properties of the object that is selected in the Project Window (sheet, module) or the properties of the control (command button, text box, list box, etc…) that is selected on the forms. You will use this window often  when you start developing forms (userforms).
The Code  Window in the Visual Basic Editor of Excel
  • All the action happens in the Code Window. In this large window you or the Macro Recorder will write macros. You will also test and fine tune all your macros in the Code Window.
Developing Macros in the VBE
  • In this lesson you will learn  how to  create a new VBA procedure. You will organize sets of sentences to tell Excel what to do. You can key them  in or copy/paste them from recorded macros, from one of your old macros or from the Internet where you will find millions of free macros.
Testing  Macros in the VBE
  • When you develop macros in Excel you spend 20% of your time analyzing the project, 15% writing your VBA procedures and 65% testing and fine tuning them. Split your screen, use the F8* key and you can see what your procedure does in Excel step by step. Back up, correct and re-test.
Excel Macro Recorder
  • The Macro Recorder is the best teacher and it will be a great assistant (writing code for you) even when you become an expert at programming in VBA. In this lesson  you will learn about the Macro Recorder and you will try it. You will also run and test the macro that you have recorded.
Macros Help and Assistance
  • There is plenty of help and assistance available within Excel when you develop macros. As you have discovered in the previous lesson the Macro Recorder is a great teacher and assistant.
  • In this lesson we investigate the two other sources of assistance within the Visual Basic Editor of Excel: the Help Files and the Object Browser.
VBA Events
  • Once you have developed your macros you need to trigger them  so that they  start. The trigger is called an event.
  • The most frequently used event is the click on a button. In this lesson you will learn how to add a button to your sheet and how to connect it to your macro. You will also learn how to start a macro by opening a workbook (also called spreadsheet or Excel file), by closing a workbook and even by changing the value of a cell.
VBA Security and Protection
  • You cannot harm your computer with macros so be bold in experimenting with macros you will learn more and faster. In this lesson you will learn how to protect you code, your sheets and your workbooks.

Section 2: Advance VBA Programming

VBA Coding Tips Advance VBA Programming 
  • Assembling VBA words into sentences is called coding. Here are interesting tips to make things easier when you start coding.
Dealing with Errors
  • VBA tells you immediately when the code that you have written is wrong.   When the logic is wrong or when the  user gives a wrong answer these errors need to be handled. In this lesson you will learn the necessary vocabulary to deal with errors.
Working with  the Application
  • The Application is EXCEL itself. Add  15 new VBA words to your vocabulary like Application.Quit, Application.ScreenUpdating = False, Application.CutCopyMode=False and others .
Working with  the Workbooks
  • Some people call them spreadsheets or Excel files VBA calls them workbooks. Here are other VBA words to add to your vocabulary. You will learn  to work with ThisWorkbook (the workbook in which runs the macro) with many workbooks and even with all the workbooks in a directory.
Working with  the Worksheets
  • There can be 256 sheets  in a single workbook. In this lesson you will discover  the vocabulary to move from one to the other, to copy/paste from one to the other, to add and delete worksheets and even go from the first to the last sheet of a workbook to do  something on all of them. You will also learn how to copy the sheets into another workbooks or to transform a single sheet into a workbook.
Moving Around the Worksheet
  • In this lesson you will improve your VBA vocabulary with 40 some words to work within the worksheet. You will learn how to select a cell or a group of cells and  how to count the rows and columns.
  • You will learn how to change the value of a cell or insert a formula in it. The 5  VBA words that you will use the most moving around the sheets are Range, Select, Offset, Activecell and CurrentRegion. What you can do with these  5 words and tens of other important words is  covered in this lesson.

Section 3: Advance VBA Programming

Working with  Message and Input Boxes Advance VBA Programming 
  • You will  use message boxes or input boxes to communicate with the user. Through these pop-ups  the user can supply bits of data or say ” Yes, No, Ok, Cancel” and other short answers during the execution of a macro.
Excel VBA Vocabulary to Filter and Sort Data
  • Excel offers you the most powerful database tools (sorting, filtering, etc…). With VBA these tools become even more powerful. You will learn how to use them with the data that you extract from large centralized databases (SAP, Oracle, EssBase, Access, etc..),  from accounting and sales programs and  with financial data  that you can download from the Internet.
Working with  Variables
  • The variable is the concept that will launch your creativity and allow you to develop real programs in Excel.  It will empower you to develop sophisticated programs and work extremely rapidly with very large sets of data. Before learning about variables you develop macros after you develop programs.
Working with  Statements
  • They are the KILL, the IF_THEN_ELSE_END IF, the SELECT-CASE, the DO_LOOP, the FOR_NEXT….
Working with  Functions
  • There are Excel functions and VBA functions. Three topics are covered in this lesson. You can use existing Excel functions within  VBA or you can create brand new Excel functions with VBA and  you can use VBA functions.
Working with  external data and SQL
  • When you connect to outside sources of data (large databases, text files, other Excel workbooks,  Access, etc.) the computer    is using SQL (Structured Query Language) a specialized language to work with data.
  • You can use Excel’s functionalities to connect and extract data but you can also use directly the SQL language top extract data.  It is the  fastest way to access any external data.
Working with  Windows and  other Microsoft Programs FROM Excel
  • With VBA for Excel you can develop VBA procedures (macros) to work within Excel while calling  other Microsoft programs like Access, Notepad, Word, Project and even Windows.
Forms (Userforms) in VBA for Excel
  • You have used message boxes and input boxes to communicate with the user while the macro was  running.  When these tools are no longer sufficient you need to develop useforms.
Userforms Properties and VBA Code
  • In this lesson you will learn how to set the properties of the userform and you will develop code within the two important events that are ” On Activate” and ” On Close” .
Properties and VBA code for Command Buttons
  • The command button is the control where most of the code resides and everything happens when you   ” CLICK ” on it.
Properties and VBA code for Labels
  • Labels are just labels. You use them to describe functions and to share information with the user.
Properties and VBA code for Text Boxes
  • The user is now talking to you.There are very few  userforms without text boxes. Text boxex having been created to handle text you need to discover how to use them with numbers, percentages, currencies, etc.
Properties and VBA code for Combo Boxes
  • The combo box is the ultimate control. It is a drop-down list and you will learn how to develop sets of combo boxes where the choices offered in the second combo box depend on the choice made in the first one. They are called cascading combo boxes.
Properties and VBA code for List Boxes
  • You will develop list boxes when you want to allow the user the possibility of multiple choices.
Properties and VBA code for Check Boxes, Option Buttons and Frames
  • The ” True/False” controls to be used as a group within a frame
Properties and VBA code for Spin Buttons
  • You can test different values increasing them step by step until you find the right one and you do it with a spin button.
Excel Image Controls

Who Should Attend

  • This course has been designed for senior level professionals, team leaders, top management personnel’s, HR managers, Finance / Account personnel’s including data analyst and developers.
  • One, who want to make career in data analysis, has always been suggested to try our level – I course first.

Pre-requisites

  • we request candidate(s) to have good command over excel with respect to formulation, formatting and various excel tools.
  • We also recommend to have data management exposure for the better understanding
  • Pre knowledge of any other programming language will boost the candidate for better understanding of tools and its functioning.

What You Need To Bring

  • Laptop / Notebook Must
  • Sufficient time at-least to devote 2-3 hrs a day.

Key Takeaways

On completion of this training, you should be able to:

  • automate data as per your company requirement
  • you should also be able to generate dynamic charts / graphs for better representation and finally
  • you can also develop your own dashboard with complex data sets from various sources.

About Trainer

Major

  • B.Sc., HDCPA, BCA + MCA

Tools of expertise

  • SAS (BASE + ADVANCE)
  • Microsoft Certified VBA Expert (Since 2003)
  • SQL, MYSQL & ORACLE DEVELOPER
  • OCJP (Oracle Certified Java Professionals)

Experience

  • 20+ years of experience in application development, automation and data analysis
  • Delivered 250+ projects including research papers and data automation

Leave a Reply

Your email address will not be published. Required fields are marked *

Learn AI with ACLM. A futuristic course for new era...

X