VBA
- VBA stands for Visual Basics Application
- You can develop apps in MS Office with VBA
- the developer Ribbon provides you with access to VBA tools
- File->Options->Customize Ribbon-> check Developer Ribbon
- Excel
- Macros- are relatively automated means of programming actions
- Macros are stored in modules
- You can bypass using Macros by simply writing VBA code in your own modules
- sub routine is basically a method in VBA
- Office has a built-in macro recorder that translates your actions into VBA
- After you record the macro you will be able to see the layout and syntax
- Before you record or write macro, plan the steps and commands you want the macro to
- A Module is a container for procedures as shown in our prior example
- a procedure is a unit of code enclosed wither between the Sub and End statement
- A.K.A. Sub Routine
- 2 ways to call
- What is Range?
- a range is one or more cells. 3 types:
- one cell, like A1 or C3
- multiple cells, like A1:C3
- multiple non-contiguous cells, like A1, B2, C3
- every cell selected in the range MUST be in the same work sheet.
- SYNTAX:
- the basic syntax of VBA range is like this:
- Range(Cell1;Cell2)
- where cell 1 is required and is the first range or cell to be acted upon.
- cell 2 is actually optional
- Examples: ("A1") or ("A1:A10")
- Range("MyName")
- You can even NEST ranges inside of other ranges and select strings:
- Range("A1:A20", Range("SalesVolulme"), Range("MyName")
- You can also change formatting in a range of cells
- for instance, bold...
- Objects
- the fundamental building block of high level programming, like VB and C# and Java
- a special type of variable made from a class that can have both data and code
- Collection
- a group of objects of the same class
- The most used Excel objects are workbooks, worksheets, sheets and ranges
- objects are instances of class
- A workbook = a collection of all workbook objects, like worksheets, cells, etc.
- the worksheet object represents a worksheet; the sheet object reps the sheet
- A workbook is the same thing as an Excel file.
- Worksheets
- are separate objects in workbook, defined by this: Worksheets ("Sheet 1")
- You can also call a worksheet by its indexed number, like this:
- Worksheets(1)
- Both of these refer to the first worksheet in the collection
- Range
- Represents a cell, a row, a col, a selection of cells, etc.
- the following example places text "KC Christian" in range K1:H5, on sheet 5