Excel VBA Online Tutorial - learn VBA Programming, enhance your Excel skills!
Learn Excel VBA programming with our easy to understand Excel VBA Online Tutorial – access examples & illustrations, live codes and downloadable files which will make learning VBA simple and quick for you. The VBA course starts with Excel VBA Basics, teaches how to create your own VBA Procedures & Macros, explains how to use Automation with VBA (Send Email from Excel, Automate PowerPoint, …) and then illustrates how to Connect with Databases (Access).
VBA for Excel
VBA is a programming language used to work with Microsoft Excel, and also with other Microsoft Office applications like Microsoft Word, Microsoft Access, PowerPoint, … and with many third-party applications. VBA stands for Visual Basic for Applications and reference to Excel VBA indicates that the Application is Excel. It is a language which Excel understands and is used to give instructions to Excel to program and automate tasks.
Benefits of VBA
VBA programming is used to get enhanced functionality which might be beyond an Excel spreadsheet;
VBA is used to automate repetitive tasks in Excel;
VBA is used to integrate Excel with other Office applications such as Microsoft Access.
Our Excel VBA Online Tutorial
Learn Excel VBA programming with our easy to understand Excel VBA Online Tutorial – access examples & illustrations, live codes and downloadable files which will make learning VBA simple and quick for you. If you are finding VBA difficult to learn, or if you find it too complicated, or if you feel it is beyond your capacity, then you have come to the RIGHT place. We keep it easy! Our tutorial is in plain language, in English, and will enable you to master Excel VBA effortlessly.
Brief Contents: Excel VBA Basics; VBA Objects; Working with Variables; Conditional Statements; VBA Loops; Application Object; Workbooks & Worksheets; Cells & Ranges in VBA; String & Date Functions; VBA Procedures & Functions; VBA Arrays; Error Handling; Events; Custom Classes & Objects; UserForms; Charts, Shapes, ActiveX & Form Controls; PivotTables with VBA; Automation with VBA (Send Email from Excel, Automate PowerPoint, …); Connect with Databases – ADO, ADOX & DAO;
Methodology: You will create a Free Login Account on our site (‘VBA Tutorial Login’ on the Left Panel), and then Login with your username and password to access our Online Tutorial. You will get complete access to the Excel VBA course, structured in appropriate Sections & Chapters, with Live Codes & Downloadable Excel Files. You can learn VBA from the comfort of your house or office, 24 x 7, without any time limits, explained in a simple and easy manner.
The tutorials are based primarily on Microsoft Excel 2007.
Is the VBA Tutorial for YOU?
The tutorial presumes a basic knowledge of Microsoft Excel for the user, and familiarity with basic Excel concepts and using a Spreadsheet.
We expect the VBA Tutorial to be useful for users who are either beginners in VBA, as well as those already having a reasonable working knowledge of VBA.
Why Us?
Check out what some existing users have to say about our Excel VBA Tutorial (click for all Testimonials):
“I’ve been to all kinds of excel schools, addin, books, excel program like … Finding ways to make me understanding excel in shortcut mode. Wanted to know the code and just copy with simple reason about the code. Done. period. I’m thankful for your work … and give me more information if there’s more.”
ExcelAnytime
“I was completely taken back by the amount of information and examples that were offered. I stopped writing my code, and I spent over two hours exploring the MANY facets of this site. The whole time, I was saying, ” Wow!…. Wow!… Wow!” . Your explanations and examples have helped me a great deal! “
ExcelAnytime
“Good Work! One of the best sites i have come across.!! I was stuck up in a problem fr 3 days….n i got it solved here..!! Many Many thanks :)”
ExcelAnytime
“I have been teaching myself VBA over the past 5 months by incorporating it into many useful projects. I have purchased several well-known books and searched the internet extensively as I’ve encountered problems. I don’t know what took me so long to come across your site, but it is without a doubt, the best site on the web for getting a good handle on VBA. The clear examples and descriptive notes are very informative. You provide much depth while keeping it simple.”
ExcelAnytime
“Thank you for sharing your excellent, clear VBA explanations. It saves my time.”
ExcelAnytime
“I find your site very informative and written in understandable language.”
ExcelAnytime
“Thanks very much. Excellent tutorial … Looked like a nightmare on other sites. This one broke it down and showed it best. Cheers!
ExcelAnytime
Get the VBA Advantage Today
- learn Excel VBA & Macros, take your Excel skills to the next level!
We want you to come join us, in the marvelous world of vba programming, to discover the magic of vba!
Detailed Contents of Excel VBA Online Tutorial
| Sections |
Chapters | Topics |
|---|---|---|
|
Excel VBA Basics
|
Excel VBA – Introduction
|
Programming Languages & Visual Basic;
Visual Basic for Applications (VBA), VBA is GUI-based; Visual Basic is not truly an Object-Orientated Programming (OOP) Language;
VBA is an Event-driven programming language; Enable VBA / Macros in Excel 2007 & 2003;
|
|
VBE – the VBA Code Editor
|
Launch the Visual Basic Editor;
Visual Basic Editor components – Code Window, Project Explorer, Properties Window, The Programming Workspace;
|
|
|
Record & Run Macros
|
Record and Run Macros using the Excel Macro Recorder – the Record Macro dialog box, Naming a Macro, Assign a Keyboard Shortcut, Store a Macro, Add a Description, Recording a Macro, View, Edit & Run a Recorded Macro, Use Relative References, Limitations of Recording a Macro, Storing a Macro in Personal Macro Workbook;
|
|
|
Starting with VBA, Writing Code
|
Basic concepts in brief:
Excel VBA Objects, Properties & Methods;
Event Procedures in VBA;
Visual Basic Editor (VBE);
Modules in Excel VBE;
VBA Procedures;
Creating a sub-procedure;
Run or Execute a Procedure;
Line Continuation within VBA code;
Auto Syntax Check;Comment Text within VBA code;
Indenting your VBA code;
Using Variables in VBA;
Keywords in VBA;
Arithmetic Operators;
String Operators;
Using Message Box in your code;
Examples of writing vba code;
|
|
|
Message Box & Input Box
|
Using Message Box in vba code – MsgBox Function;
Using Input Box in vba code – InputBox Function, Application.InputBox Method, Accepting formulas in InputBox, Accepting a cell reference as a Range object in InputBox;
|
|
|
Excel VBA Objects
|
Working with Objects
|
VBA Objects;
The Excel Object Model;
Access an Object / Access a Single Object from its Collection; Properties and Methods of Objects;
Working with Objects in Excel VBA – Excel VBA IntelliSense, Using With…End With Statement to refer to Objects, Using Variables in VBA, Keywords in VBA, Assign an Object to a Variable, using the Set Keyword;
|
|
Working with Variables
|
Declare Variables in VBA
|
Declaring Variables – variables are initialized to a default value, assign a value to a variable, assign an object reference, using Option Explicit;
Valid and Invalid Declaration of Variables; Dim statements declared at 3 basic levels – Procedure Level, Module Level, Project Level;
Initialize variables;
Static Statement;
ReDim Statement to Resize Dynamic Array;
Variable Names – Naming Conventions for Variables; Variable Data Types;
|
|
Conditional Statements
|
If…Then…Else Statements
|
If…Then…Else Statements – Multiple line statements, Nesting, using NOT Operator with IF statement, Single line If…Then…Else Statements, Select…Case, compared to If…Then…Else Statements;
|
|
Select…Case Statement
|
Select…Case Statement (VBA) – using the To keyword to specify the upper and lower range of values, using the Is keyword (with a comparison operator) to compare values, using a comma to separate multiple expressions or ranges in each Case clause, Option Compare Setting, Nesting, GoTo statement, compared to If…Then…Else Statements;
|
|
|
Excel VBA Loops
|
Excel VBA For & Do Loops
|
The For Loop – the For … Next Statements, the For Each … Next Statements, Nesting Loops, the Exit For Statement;
The Do While Loop – the Do While … Loop Statements, the Do … Loop While Statements, the Exit Do Statement;
The Do Until Loop – the Do Until … Loop Statements, the Do … Loop Until Statements, the Exit Do Statement;
|
|
Excel Application Object
|
Application Object, Properties & Methods
|
Excel VBA Application Object;
Instances where using the Application qualifier is required – Height, Width and WindowState Properties, DisplayFullScreen Property, DisplayFormulaBar Property, Calculation Property, EditDirectlyInCell Property, ScreenUpdating Property, DisplayAlerts Property, DefaultFilePath Property, Quit Method, Application.OnTime Method, ActivateMicrosoftApp Method, Application.GetOpenFilename Method;
When it is not required to specify the Application qualifier – ActiveCell Property, ActiveWindow Property, ActiveWorkbook Property, ThisWorkbook Property, ActiveSheet Property, ActiveChart Property, ActivePrinter Property, Selection Property, Sheets Property, Range Property, Calculate Method;
|
|
Application.OnTime VBA, Schedule Macros
|
Excel Application.OnTime Method – Scheduling OnTime Events;
Stop or Cancel a Running Procedure (using the OnTime method);
|
|
|
Excel VBA Workbooks
|
Workbook Object, Properties & Methods
|
Reference a Workbook object – Workbooks.Item Property, ActiveWorkbook Property, ThisWorkbook Property;
Open, Add, Copy & Close Workbooks – Workbooks.Open Method, Workbooks.Add Method, Close Method, OpenDatabase Method, SaveCopyAs Method;
Saving Workbooks – Workbook.Save Method, Workbook.Saved Property;
Often used Methods & Properties of the Workbook Object – Workbook.Activate Method, PrintPreview Method, Workbook.SendMail Method, Workbook.ActiveSheet Property, ActiveChart Property, FileFormat Property, Name Property, Password Property, Path Property;
|
|
Excel VBA Worksheets
|
Worksheet object, Properties & Methods
|
The Worksheet Object, Sheets Object, Chart Object, 4 types of sheets – worksheet, chart sheet, macro sheet,dialog sheet;
Reference a Worksheet object – Item Property, The Active Object, Code Name and Sheet Name;
Activate or Select a Worksheet – ActiveSheet Property, Activate Method, Select Method;
Adding & Naming Worksheets – Add Method, Default worksheet names on creating a new workbook, Name property;
Copy a Worksheet, Move or Change Worksheet Sequence, Hide or Display Worksheets, Remove or Delete a Worksheet – Copy Method, Move Method, Visible Property, Delete Method; Worksheet Page Layout & Views – PageSetup Property, PrintPreview Method, DisplayPageBreaks Property, View Property;
Calculating Worksheets – Calculation Property, Calculate Method; Speed up your VBA Code by Turning off Screen Updates and Automatic
Calculations – Screen Updates and Automatic Calculations, CalculateBeforeSave Property, EnableCalculation Property;
|
|
Run Macros on Protected Worksheet
|
Run VBA Macros on Protected Worksheet;
Unprotect & Protect;
Error Handler;
UserInterfaceOnly argument in the Protect method;
Run macro on protected worksheet, code remaining visible & editable, but protection password hidden;
Using the UserInterfaceOnly argument to Enable Auto Filter;
Using UserInterfaceOnly argument to Allow Grouping; Worksheet.Protect Method;
|
|
|
Excel VBA Cells & Ranges
|
Referencing Cells & Ranges – 1
|
Range Property, Cells / Item / Rows / Columns Properties, Offset & Relative Referencing, Cell Address – Range Object, Range property, Shortcut Range Reference, Cells Property, Item property, Columns Property, Rows Property, Accessing a worksheet range with vba code (Referencing a single cell, Referencing a range of cells, Referencing Rows or Columns, Relative Referencing), Range.Row Property, Range.Column Property, Range.Address Property;
Activate & Select Cells;
The ActiveCell & Selection – Select method, ActiveCell Property, Selection property, Activate Method;
Entire Row & Entire Column Properties, Inserting Cells/Rows/Columns using the Insert Method – EntireRow Property, EntireColumn Property, Insert Method;
|
|
Referencing Cells & Ranges – 2
|
Ranges, Union & Intersect – Union Method, Intersect Method, using Intersect method with worksheet change or selection change events;
Resize a Range – Offset & Resize properties of the Range object;
Contiguous Block of Cells, Collection of non-contiguous ranges, Cells Meeting a Specified Criteria, Used Range, Cell at the End of a Block / Region – Using the Areas Property, CurrentRegion Property, SpecialCells Method, UsedRange property, End Property, find Last Used Row in a worksheet;
|
|
|
Find Method in Excel VBA
|
Find Method in Excel VBA;
Find multiple occurrences of an item or value in a range;
Using Find Method to do VLookUp;
Using Find Method to Search for a Date;
|
|
|
VBA – Last Used Row & Column
|
Use End(xlUp) to determine Last Row with Data, in one column;
Use End(xlToLeft) to determine Last Column with Data, in one row;
UsedRange property to find the last used row number in a worksheet;
UsedRange property to find the last used column number in a worksheet;
UsedRange property to find number of used rows in a worksheet;
UsedRange property to find number of used columns in a worksheet;
UsedRange property to find the first used row number in a worksheet;
UsedRange property to find the first used column number in a worksheet;
Use End(xlDown) to determine Last Row with Data, at the End of a Block in a column;
Use End(xlToRight) to determine Last Column with Data, at the End of a Block in a row;
FIND method to determine Last Row with Data, in a worksheet;
FIND method to determine Last column with Data, in a worksheet;
SpecialCells method to find Last Used Row in worksheet;
SpecialCells method to find Last Used Column in worksheet;
|
|
|
Text, String & Date Functions
|
Excel Text & String Functions – 1
|
Excel VBA String Functions for Finding and Replacing Text, with Examples: LEFT, RIGHT, MID, LEN, REPLACE;
InStr & InStrRev Functions (VBA);
|
|
Excel Text & String Functions – 2
|
Excel VBA String Functions: SPLIT, JOIN, CONCATENATE – Excel VBA Functions to Split a String into Substrings, and Join an Array of Substrings to form a String, with Examples.
|
|
|
Functions – CODE & CHAR / Asc & Chr
|
Excel Text and String Functions: Excel CODE & CHAR Functions, VBA Asc & Chr Functions, with examples – Changing case (uppercase / lowercase) of alphabets in a string, format or manipulate data with ANSI codes;
|
|
|
ASCII & ANSI Code, Extended ASCII characters
|
ASCII Code;
ASCII to Extended ASCII characters (8-bit system) and ANSI Code;
Extended ASCII characters (8-bit) and UNICODE;
The Windows SDK provides function prototypes in generic, Windows code page (ANSI) and Unicode versions;
Excel uses ANSI code system for Windows computers; ASCII Non-Printing Control Codes Chart;
ASCII Printing Characters Chart;
Extended ASCII Printing Characters Chart;
|
|
|
Column No. to Letter, Column Letter to No.
|
VBA Codes to Convert Excel Column Number to corresponding Column Letter;
VBA Codes to Convert Excel Column Letter to corresponding Column Number;
|
|
|
VBA Convert to UpperCase / LowerCase
|
Worksheet vba codes for: Convert Text and Cells with Formulas to UpperCase;
Convert Text and Cells with Formulas to UpperCase;
Convert Text and Cells with Formulas to UpperCase;
Convert Text to UpperCase;
|
|
|
Excel VBA Dates & Time, Format Function
|
Excel VBA Dates & Time – Excel Dates Equate to Serial Numbers, Date Data Type in VBA, System’s Locale settings vs. Code Locale, Format specified in your computer’s regional settings;
Format Function in VBA – Predefined Named Date/Time Formats, System-defined Format, Predefined Named Numeric Formats, User-Defined Number Formats, User-Defined Date & Time Formats, User-Defined String Formats, Multiple Sections for User-defined Formats;
|
|
|
Excel VBA Date & Time Functions
|
VBA DateSerial Function;
DateValue Function;
TimeSerial Function;
TimeValue Function;
IsDate Function;
CDate Function;
DateAdd Function;
DateDiff Function;
DatePart Function;
Date Function;
Now Function;
MonthName Function;
Day Function;
Month Function;
Year Function;
Hour Function;
Minute Function;
Second Function;
WeekDay Function;
WeekdayName Function;
Find Method to Search for a Date;
|
|
|
Custom Number Formats, Date Formats
|
Excel Home Tab, Format Cells dialog box;
VBA, NumberFormat property;
Number Codes; Specify Font Color;
Four Sections of Code;
Specify Conditions;
Custom Date & Time Formats;
|
|
|
Excel VBA Procedures
|
Subs & Functions, Placement in Modules
|
Sub procedures (Event Procedures & User Created), Function Procedures (Built-in & User-defined) & Property Procedures;
Naming Rules & Conventions for Procedures;
VBA Procedures Scope – Public vs Private; Placement of Macros / Sub procedures in appropriate Modules – Standard Code Modules, Workbook module, Sheet Modules, UserForm Modules, Class Modules;
Calling Procedures – Calling Sub procedures in the Same Module, Calling Functions in the Same Module, Calling Procedures in Outside Modules;
Executing Procedures – Executing Function Procedures, Executing Event Procedures, Executing Sub Procedures;
|
|
Pass Arguments to Procedures, ParamArray
|
Argument Data Types;
Passing Arguments By Value;
Passing Arguments By Reference;
Optional Arguments;
Pass an Arbitrary or Indefinite Number of Arguments – Parameter Arrays (ParamArray);
|
|
|
VBA – Empty, ZLS, Null, Nothing, Missing
|
Empty, Blank, ZLS (zero-length string), Null String & vbNullString;
IsEmpty Function;
VarType Function;
Null;
IsNull Function;
Nothing;
Missing;
IsMissing function;
|
|
|
Excel VBA Arrays
|
Excel VBA Arrays
|
VBA Arrays;
Declare Arrays, Sizing an Array, Determining the Upper and Lower Bounds;
One-Dimensional and Multi-Dimensional Arrays;
Fixed-size and Dynamic Arrays;
ReDim Statement to Resize Dynamic Array;
use the ‘Preserve’ Keyword with the ReDim statement;
Excel VBA Functions – SPLIT a String and return an Array of Substrings, JOIN an Array of Substrings to form a String;
Create a 200-Year Calendar in VBA, using Arrays with Loops;
ParamArray (Parameter Array);
|
|
Error Handling & Debugging
|
Error Handling in VBA
|
VBA Erros & Error Handling, Error Handling Setting in VBE, Error Handler, On Error Statements, Using an Exit Statement, Error Handling in Nested Procedures & The Resume Statement, Get Information from the Error Object, Raise Method of the Err Object – Generate a Run-time error.
|
|
Debugging Tools in VBE
|
Debug Code by using a Message Box, Use Breakpoints to Debug Code, Using Break Mode to Debug Code, Stepping Through Code, Debugging Views – Immediate Window, Locals Window, Watch Window, Call Stack.
|
|
|
Excel VBA Events
|
Excel VBA Built-in Events
|
Excel VBA Events; Event Procedures;
Built-in Event procedures & Custom events;
Excel events broadly classified – Workbook events, Worksheet events, Chart events, UserForm events, Application events, Events not associated with objects;
|
|
Worksheet Change Event
|
Worksheet_Change Event;
Target parameter;
Preventing Event Loops with Application.EnableEvents = False;
|
|
|
Worksheet Selection Change Event
|
Worksheet_SelectionChange Event;
Preventing Event Loops with Application.EnableEvents = False;
|
|
|
Custom Classes & Objects
|
Custom Classes & Objects, Custom Events
|
Excel VBA Custom Classes & Objects – Insert a Class Module, Name a Class Module, Instancing Property of a Class Module, Instantiate a Class Object, Create Class Properties, Using Property Procedures to Create Properties (Property Get, Property Let, Property Set);
Custom Class Events – Define a Custom Event, Raise an Event, External Code to Raise the Event, Create an Event Procedure;
|
|
ActiveX & Form Controls, AutoShapes
|
ActiveX & Form Controls, AutoShapes
|
Built-in Excel Data Form;
Using ActiveX controls, Form controls & AutoShapes in Excel Worksheet;
Shape object & OLEObject object;
ActiveX Controls in VBA;
Add an ActiveX Control, a Form Control or an AutoShape, in Worksheet;
Illustrating Form Controls, ActiveX Controls & AutoShapes – Button (Form Control) & Command Button (ActiveX Control), Check Box (Form Control or ActiveX Control), List Box & Combo Box (Form Control / ActiveX Control), Option Button (Form Control or ActiveX Control), Toggle Button (ActiveX Control), ScrollBar & SpinButton Controls(Form Control or ActiveX Control), TextBox (ActiveX Control), Label (Form Control or ActiveX Control), Illustrating Auto Shapes & Add a connector;
|
|
UserForms in Excel VBA
|
UserForm Basics, Add Controls
|
UserForms in Excel VBA;
Create a UserForm; UserForm ToolBox Controls – A Snapshot;
UserForm Basics; Trapping UserForm Events;
Add Controls to a UserForm – using the Add Method;
|
|
UserForm and Controls – Properties
|
Design-Time, Run-time and Break-time;
Setting control properties with vba;
Basic Properties common to the UserForm and most Controls – Name Property, Caption Property, Height & Width Properties, Left & Top Properties, Value Property;
Other Properties common to the UserForm and most Controls – Accelerator Property, Alignment Property, AutoSize Property, BackColor Property, BackStyle Property, BorderColor Property, BorderStyle Property, ControlSource Property, ControlTipText Property, Enabled Property, Locked Property, Font Object, ForeColor Property, MouseIcon Property, MousePointer Property, Picture Property, PicturePosition Property, SpecialEffect Property, TabIndex Property, TabStop Property, Visible Property, WordWrap Property;
Specifying Color in Properties Window;
Applicability of Properties to UserForm and its Controls – A SnapShot;
|
|
|
Label, TextBox & CommandButton
|
Label Control; CommandButton – Click event;
TextBox – AutoTab Property, EnterKeyBehavior Property, MaxLength Property, MultiLine Property, PasswordChar Property, ScrollBars Property, Text Property;
|
|
|
ComboBox & ListBox
|
Difference between ListBox and ComboBox;
Key Properties of ComboBox and ListBox – AddItem Method, BoundColumn Property, Clear Method, Column Property, ColumnCount Property, ColumnHeads Property, List Property, ListCount Property, ListIndex Property, ListRows Property, MultiSelect Property, RemoveItem Method, RowSource Property, Selected Property, Style Property, TextColumn Property;
Add Items/Data to (Populate) a ListBox or ComboBox – Setting the RowSource property of a ListBox or ComboBox in a UserForm, Populate a ComboBox or ListBox from an Array, Populate a ComboBox or ListBox with AddItem method, Populate a multi-column ComboBox or ListBox using AddItem method and List & Column properties, Populate a multi-column ListBox from a worskheet range, using AddItem method and List property, Add a new item/row to the list if ComboBox is bound to data in a worksheet;
Extract ListBox & ComboBox Items, with VBA – Display selected ComboBox item in TextBox, Copy selected ComboBox item to a worksheet range, Copy ComboBox item determined by its position, to a worksheet range;
Delete ListBox rows using the RemoveItem Method;
|
|
|
CheckBox, OptionButton, ToggleButton
|
CheckBox – Value property, TripleState property;
OptionButton – GroupName property, using a Frame Control, Value property;
Difference between CheckBox and OptionButton;
ToggleButton;
|
|
|
Frame, MultiPage & TabStrip
|
Frame Control;
MultiPage Control – Dynamically Add/Remove a Page, Dynamically Access an individual Page, Create a wizard using a Single UserForm and MultiPage control;
TabStrip Control – Dynamically Add/Remove a Tab, Difference between a MultiPage control and TabStrip control, Selecting a Tab;
|
|
|
ScrollBar & SpinButton
|
ScrollBar Control – SmallChange & LargeChange Properties, Min and Max Properties, Orientation Property;
SpinButton Control – SmallChange & Min & Max & Orientation Properties, Difference between ScrollBar & SpinButton controls;
|
|
|
Image & RefEdit
|
Image Control – LoadPicture function, PictureSizeMode Property, PictureSizeMode Property, PictureAlignment Property, Use LoadPicture property with GetOpenFilename Method;
RefEdit control;
|
|
|
Charts in Excel VBA
|
Add a Chart, Chart & ChartObject objects
|
Charts in Excel VBA;
Worksheet & Chart Sheet in Excel – Chart objects in VBA;
Add a Chart – Charts.Add Method, ChartObjects.Add Method;
ChartObject object – Commonly used Properties & Methods;
Chart object – Commonly used Properties & Methods;
|
|
Chart Elements: Chart Title, Chart & Plot Area, Axes
|
Chart Title – Commonly used Properties & Methods of the ChartTitle object, Child Objects for the ChartTitle Object;
Chart Area – Commonly used Properties & Methods of the ChartArea object, Child Objects for the ChartArea Object;
Plot Area – Commonly used Properties & Methods of the PlotArea object, Child Objects for the PlotArea Object, PlotArea Alignment , ChartTitle Alignment, AxisTitle Alignment, Legend Alignment;
Chart Axis – Axes object & Axis object, Tick Marks & Gridlines, Commonly used Properties & Methods of the Axis object, Properties & Methods of the AxisTitle object, Properties & Methods of the TickLabels object, Child Objects for the Axis Object;
|
|
|
Chart Elements: Chart Series, Data Labels, Legend
|
Chart Series – SeriesCollection object & Series object, Commonly used Properties & Methods of the Series object, Child Objects for the Series Object, ErrorBars & LeaderLines, Combination Chart;
DataLabels Object & DataLabel Object – Commonly used Properties & Methods of the DataLabels Object / DataLabel Object, Child Objects for DataLabels Object / DataLabel Object;
Chart Legend – LegendKey & LegendEntry, Commonly used Properties & Methods of the Legend object, Child Objects for the Legend Object.
|
|
|
Border / ChartFillFormat / Interior / Font Objects
|
Child Objects commonly used with Chart Elements – Border Object, ChartFillFormat Object, Interior Object & Font Object with Properties & Methods.
|
|
|
ChartFormat object, Formatting, 3D Charts
|
Line, fill & effect formatting for chart elements:
FillFormat object – Setting ForeColor & BackColor, Setting a Solid Fill, Setting Gradient Type, Gradient Stops, Setting Pattern, Setting Texture, Setting Picture for the Fill;
LineFormat object – Setting ForeColor & BackColor, Setting Style or Dash Style, Setting Pattern, Line Arrowheads, Transparency & Weight Properties;
ShadowFormat object;
GlowFormat object;
SoftEdgeFormat object;
ThreeDFormat object – Manipulating 3-D Charts;
|
|
|
Create Line, Column, Pie, Scatter, Bubble charts
|
Create an Embedded Chart – Line with Markers;
Creating a Clustered Stacked Column Chart;
Create a Clustered Stacked Bar Chart displaying Variance between Series;
Pie Chart;
XY Scatter Chart & Bubble Chart – Single series with X & Y values, Multiple series where all series share the same Y values with distinct X values, Manipulate both X & Y error bars;
|
|
|
Excel Pivot Tables with VBA
|
Create Pivot Table using VBA
|
Use VBA to Create and Customize PivotTable & PivotChart reports;
The PivotTableWizard method;
|
|
Referencing Pivot Table Range
|
TableRange1 Property and TableRange2 Property;
PivotField.LabelRange Property and PivotItem.
LabelRange Property;
RowRange Property and ColumnRange Property;
PivotTable.DataBodyRange Property;
DataLabelRange Property;
PageRange Property;
PivotField.DataRange Property and PivotItem.
DataRange Property;
PivotTable.PivotSelect Method;
Intersect Method;
|
|
|
Reference Pivot Fields & Items
|
Using the PivotTable.PivotFields Method to access a Pivot Field;
PivotTable.ColumnFields Property to access a column field;
PivotTable.RowFields Property to access a row field;
PivotTable.DataFields Property to access a data field;
PivotTable.PageFields Property to access a page field;
Reference PivotItem in a PivotField;
PivotTable.HiddenFields Property to return hidden fields;
PivotTable.PivotSelect Method to Select a part of the PivotTable;
|
|
|
Pivot Table Move & Copy
|
Address of Pivot Table;
Move PivotTable to a new location;
Copy a PivotTable and paste as data or values or paste as a PivotTable;
|
|
|
Pivot Table Layout & Design
|
Layout Forms for PivotTable report, column header and row header;
Hide or Display Subtotals;
Subtotal Location, Inserting blank lines, Show/hide GrandTotals & Grand Total Name, Set Number Format;
Pivot Table Styles – Style Gallery;
PivotTable Style Options – Row Headers, Column Headers, Banded Rows &
Banded Columns;
|
|
|
Pivot Table Properties & Settings
|
Get data from a PivotTable using VBA – PivotTable.GetPivotData Method;
Disable or Enable DrillDown of PivotTable Fields; Display the PivotTable FieldList;
Sort Fields in Ascending order in the PivotTable FieldList; Return the Parent object of a PivotTable object;
Allow PivotTable Update only Manually;
PivotTable.Name Property returns the name of a PivotTable object;
PivotTable Print Settings – PivotTable.PrintTitles Property, PivotTable.
RepeatItemsOnEachPrintedPage Property, Repeat specific rows at the top or columns on the left while printing a PivotTable report, PivotTable.PrintDrillIndicators Property;
Save the PivotCache data while saving the workbook;
Create a new PivotTable, each on a new worksheet, for each item of a Page field;
Return the data source of a PivotTable report;
Include hidden page field items in subtotals and totals;
Pivot Table versions & Excel version;
|
|
|
Refresh Pivot Table & Cache
|
PivotCache.Refresh Method to Refresh the Cache of the Pivot Table;
PivotTable.RefreshTable Method to update the PivotTable report;
Automatically refresh Pivot Tables in all worksheets on opening a workbook;
Return the Date when PivotTable was last refreshed, and Name of the user who refreshed;
|
|
|
Group Items, Data & Date Values
|
Group Date Values in a PivotTable report, using the Group method;
Group Date Values by Weeks;
Group Numeric Items; Group Specific Text Field Items;
|
|
|
Sort Fields, Values & Dates, Custom Lists
|
PivotField.AutoSort method – set sort order of a PivotTable field;
Range.Sort Method – to sort a range of values in a PivotTable report; Set the sort order manually;
Sort a PivotTable report using Custom Lists – Application.AddCustomList Method, Application.DeleteCustomList Method, Application.GetCustomListContents Method, Application.GetCustomListNum Method, SortUsingCustomLists Property;
|
|
|
Filter Data, Items, Values & Dates
|
PivotField.AutoShow method – to display the Top or Bottom items in a PivotTable Field;
PivotFilters.Add Method – to add new filters to a PivotTable report;
Manual Filter;
Clear Filters;
PivotTable.AllowMultipleFilters Property – apply multiple filters to a single PivotField;
|
|
|
Summary Functions, Custom Calculations
|
Pivot Table Summary Functions – xlAverage, xlCount, xlCountNums, xlMax, xlMin, xlProduct, xlStDev, xlStDevP, xlSum, xlVar and xlVarP;
Pivot Table Custom Calculations – xlNoAdditionalCalculation, xlDifferenceFrom, xlPercentOf, xlPercentDifferenceFrom, xlRunningTotal, xlPercentOfRow, xlPercentOfColumn, xlPercentOfTotal and xlIndex;
|
|
|
Insert Calculated Fields, Create Formulas
|
Pivot Table Calculated Fields – CalculatedFields.Add Method;
Pivot Table Calculated Items – CalculatedItems.Add Method, PivotTable.ListFormulas Method;
|
|
|
Create Pivot Table Charts
|
Create a PivotChart based on an existing PivotTable report;
Add embedded chart based on an Excel Database;
Create a Chart (add a new chart sheet) based on an Excel Database;
Create an embedded PivotChart based on an existing PivotTable report;
|
|
|
Automation with VBA
|
Automate Microsoft Word from Excel
|
Automating an Office Application – Automation Process, Early Binding & Late Binding in Automation, Adding a reference to the Object Library, Built-in Constants and their Numerical Values, The Getobject function and CreateObject function;
Automating Word from Excel;
|
|
Automate Microsoft Outlook from Excel
|
Create a new instance of the Outlook application;
Using the Application.GetNamespace Method;
Reference Existing Outlook Folders and Create New Folders in Automation;
Create New Outlook Items and Reference Outlook Items;
NameSpace.GetDefaultFolder Method; Folders.Add Method;
Items.Add Method and the Application.CreateItem Method;
Using the Find and FindNext methods to search a folder;
Using the Restrict Method;
|
|
|
Export contacts from Outlook to Excel
|
Export contacts from Outlook to Excel – automate in vba using Early Binding and Late Binding;
Export contacts from a specific Contact Items Folder or from the default Contact Items Folder to an Excel Worksheet;
|
|
|
Import Contacts from Excel to Outlook
|
Import Contacts from Excel to Outlook – automate in vba using Early Binding and Late Binding;
Import data from an Excel Worksheet to the specified Contacts folder or to the default Contacts folder;
|
|
|
Automate Outlook, Send Email from Excel
|
Sending Email from Excel using Outlook – automate in vba using Early Binding and Late Binding;
Send text, Send contents from the host workbook’s worksheet range as Mail Body, Send an attachment with the mail;
Send multiple mails to ids sourced from the Host Workbook’s sheet;
Copy the Host Workbook or Create a New Workbook, Add a New Sheet and Copy-Paste a Range to the Workbook, then send as an attachment with
the mail;
Find a mail item in the Inbox folder meeting a specified criteria, delete or add attachment, forward it to a specific email id, and move the mail item to a newly created mail folder;
|
|
|
Automate PowerPoint from Excel
Automate Microsoft PowerPoint from Excel using vba & Run a Slide Show;
Create a new PowerPoint ppt of 4 sli
|
des with sound clips and chart, run & view the slide show, automatically close & quit the PowerPoint application;
|
|
|
Connect with Databases (ADO)
|
Microsoft Access: ADO Library
|
Connect with Databases using DAO, RDO and ADO Objects;
ADO Objects & Programming model; Connecting to a Data Source (viz. Microsoft Access) using the ADO Connection Open Method;
The Connection Object; Establish Connection to a Data Source; Access records from a database table;
The ADO Recordset Open Method, to open an ADO Recordset object;
Create a new record using the Recordset Object’s AddNew method;
Update Method (ADO Recordset);
Close & Delete Methods;
Moving between Records in a Recordset;
Count the number of Records in a Recordset;
Access fields in a Recordset;
|
|
Execute SQL statements – ADO
|
Microsoft Access – Use ADO to Execute SQL statements to Manage your Database;
SQL Commands explained;
Using the ADO connection Execute method to execute the specified query and SQL statements;
Use the OpenSchema Method to access information about database tables and columns;
Create a database table using ADO with SQL statements;
ADO Find Method to Find or Locate a specific Record; ADO Filter Property to FilterRecords;
|
|
|
Import / Export Data, Access to Excel
|
Using ADO to Import data from an Access Database Table to an Excel worksheet (your host application);
Using ADO to Export data from Excel worksheet (your host application) to Access Database Table;
|
|
|
Connect to Access from Excel – ADOX
|
Create an Access Database using ADOX;
ADOX Table Object & Tables Collection – Properties & Methods; ADOX Column Object & Columns Collection – Properties & Methods;
Reference & Delete Database Tables and Columns and Reference & Edit Field Properties using ADOX;
Create an Index using ADOX;
Create Relationship between Tables using ADOX;
ADO Command Object, ADOX View Object, Parameter Queries, Create & Execute Stored Queries / Action Queries with ADOX;
|
|
|
Connect with Databases (DAO)
|
Connect to Access from Excel – DAO
|
DAO Objects & Programming model;
The DBEngine object; Workspace Object & Workspaces Collection;
DAO Databases;
Tables of a DAO Database; Fields / Columns of a Table;
Recordset & Records of a DAO Database Table;
|
|
Create Index & Relationships, Exceute Query
|
Create an Index using DAO;
Create Relationship between Fields of Database Tables using DAO;
Create and Exceute a Query, including Action & Parameter Query, using DAO;
|
|
|
Import / Export Data from Access to Excel
|
Import data from an Access Database Table to an Excel worksheet (your host application);
Export data from Excel worksheet (your host application) to an Access Database Table;
|