E learning excel vba programming lesson 1

E learning excel vba programming lesson 1

• 1.
  Ameetz.com What isVBA? Visual Basic for Applications (VBA) is an extensible programming language that is made up of a core set of commands and extended on a per-application basis to be able to work directly with objects in that application. This means that VBA for Excel knows about things like workbooks, worksheets, cells and charts and more; VBA can probably be best described as an object-based (but not a true object oriented) language that is event driven. Let’s look at the event driven side of it first.
• 2.
  Ameetz.com EVENT DRIVENLANGUAGE Event driven means that nothing happens until something happens. In VBA, no code executes except in response to some event taking place (or at the command of the code once it is started by some event). An event can be any one of many things. Opening an Excel workbook creates, or triggers, the Open event, closing it triggers the Before Close event. OBJECT-BASED LANGUAGE Object based means that when referring to the components of the application, things like workbooks, worksheets, cells, charts, etc. are ‘objects’.
• 3.
  Ameetz.com An objecthas certain attributes. Just a a person has attributes like height, weight, eye and hair color, the objects in Excel have attributes (Properties) such as value, height, width, color and more. Additionally, objects can do things or have things done to them – these actions are known as Methods. For example, a workbook can be opened or closed; a cell can have its shading altered, a worksheet can be deleted.
• 4.
  Ameetz.com The ExcelVBA IDE (Integrated Development Environment)
• 5.
  GETTING TO THEVBA IDE Your first question may be “How the heck did you get there!?” The quickest way to open the VBA IDE (which I’ll simply call the VBE for Visual Basic Editor for the rest of this document), is to press [Alt]+[F11] while in the main/normal Excel window. You can also get there from the normal Excel menu via Tools | Macro | Visual Basic Editor:
• 6.
  The VBE menuand normal icon toolbar. The project Window , If not visible ctrl+R to bring to view The Code window – may be empty, or may be shared for use to display other things such as the Object Browser. The Properties window – displays and allows editing of the properties of the currently active object. The Immediate window – you can type in commands, set values, examine values and Debug.Print results show up in this window.
• 7.
  Option Explicit is a directive to the compiler that says that all user defined constants and variables must be declared before actually using them in the code. The up side of using Option Explicit is that errors in your code due to typographic errors or reuse of a variable as the wrong type are greatly reduced and when it does happen, the problems are more easily identified
• 8.
  To make surethat you don’t forget to always use Option Explicit, you can have a ‘permanent’ setting and affects all projects you create in any workbook after making the setting. Start by selecting [Tools] | Options from the VBE menu toolbar:
• 9.
  This is thedialog that appears once you use [Tools] | Options from the VBE menu toolbar. Check the “Require Variable Declaration” box to set up the VBE to always place the Option Explicit statement at the beginning of all new code modules in the future.
• 10.
  Code Modules Thereare different types code modules . General Purpose Code Modules Work Book Code Modules Work Sheet Code Modules General Purpose Code Modules These are code modules you’ll bring into existence and can contain code to do almost anything of a ‘general purpose’ nature. code to respond to custom menus you might develop, user defined functions (UDF) that you develop to perform actions and calculations by way of using the name of the UDF in a worksheet formula just like a built-in Excel worksheet function. Macros you record are placed into general purpose modules. Recording macros during different sessions with the workbook results in numerous modules that may contain as few as a single procedure (macro) in it. This results in being quite wasteful of resources. All macros recorded during a single session are typically placed into a single module.
• 11.
  WORKBOOK CODE MODULES There is one and only one code module per workbook that is associated with Workbook Event handling. At the technical level, this module, along with the worksheet event handling modules are Class Modules.
• 12.
  WORKSHEET CODE MODULES There is one and only one code module per worksheet that is associated with Worksheet Event handling. However, each sheet has its very own code module that is separate and distinct from all of the others even though they may all have event handlers for a given event for those worksheets. At the technical level, this module, just like the event handling module for the workbook are Class Modules
• 13.
  End of 1st Lesson Next Session - Procedures – Function and Sub