Learn Offline VBA Programming - Tutorials for VBA

了解Excel和VBA宏處於離線簡單的方法的例子

Introduction to MS-Excel:

• Introduction to MS Excel, Quick review on MS Excel Options, Ribbon, Sheets and

• Difference between Excel 2003, 2007, 2010 and 2013

• Saving Excel File as PDF, CSV and Older versions

• Using Excel Shortcuts with Full List of Shortcuts

• Copy, Cut, Paste, Hide, Unhide, and Link the Data in Rows, Columns and Sheet

• Using Paste Special Options

• Formatting Cells, Rows, Columns and Sheets

• Protecting & Unprotecting Cells, Rows, Columns and Sheets with or without Password

• Page Layout and Printer Properties

• Inserting Pictures and other objects in Worksheets

Working with Formulas/Functions:

• Lookup and Reference Functions: VLOOKUP, HLOOKUP, INDEX, MATCH, etc

• Logical Function: IF / ELSE, AND, OR, NOT, TRUE, NESTED IF/ELSE etc

• Database Functions

• Date and Time Functions: DATE, DATEVALUE, DAY, DAY360, SECOND, MINUTES, HOURS, NOW, TODAY, MONTH, YEAR, YEARFRAC, TIME, WEEKDAY, WORKDAY etc

• Information Functions :

• Math and Trigonometry Functions: RAND, ROUND, CEILING, FLOOR, INT, LCM, MOD, EVEN, SUMIF, SUMIFS etc

• Statistical Functions: AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, COUNT, COUNTA, COUNTBLANK, COUNTIF,FORECAST, MAX, MAXA,MIN, MINA, STDEVA etc

• Text Functions: LEFT, RIGHT, TEXT, TRIM, MID, LOWER, UPPER, PROPER, REPLACE, REPT, FIND, SEARCH, SUBSTITUTE, TRIM, TRUNC, CONVERT, CONCATENATE, DOLLAR etc

• And More

Conditional Formatting:

• Using Conditional Formatting

• Using Conditional Formatting with Multiple Cell Rules

• Using Color Scales and Icon Sets in Conditional Formatting

• Creating New Rules and Managing Existing Rules

Data Sorting and Filtering:

• Sorting Data A-Z and Z-A

• Using Filters to Sort Data

• Advance Filtering Options

Pivot Tables:

• Creating Pivot Tables

• Using Pivot Table Options

• Changing and Updating Data Range

• Formatting Pivot Table and Making Dynamic Pivot Tables

Pivot Charts:

• Creating Pivot Charts

• Types of Pivot Charts and Their Usage

• Formatting Pivot Charts and Making Dynamic Pivot Charts

Data Analysis:

• Advanced Filtering

• Data validation

• Consolidation

• Groups

• Subtotal

Excel Macros:

• What is Excel Macro ?

• How to Record / Run Excel Macro ?

• How Add Developer’s Tab in Excel 2007 / 2010

• How to add different types of controls like Text Box, Radio button, button etc. in Excel

VBA Macros Designing:

• Visual Basic Editor in Excel

• VBA Control Property

• How to write Excel Macro – Your First Excel Macro

• Excel Macro Tutorial : Excel User Form

• VBA Spin Button

• Toggle Button in Excel

• ComboBox / Drop Down List In Excel

• ListBox In Excel

• How to Open Excel Workbook using Excel Macro¬

• Using RANGE Object in Excel Macro

• What is difference between CELLS() and RANGE()¬¬¬

• Excel Macros, Excel Terms, Macro comments

• Userforms

VBA Programming:

• Variables in Excel VBA

• Implicit and Explicit declaration of variables in Excel Macro

• Important : Declaring multiple Variables with one Dim Statement

• For Next Loop in Excel Macro

• While Loop and Do While Loop in Excel VBA

• VBA Programming : If Then Statements / If else Statements

• VBA Programming : Decision – Select Case in Excel VBA

• Variables in Excel VBA

• Implicit and Explicit declaration of variables in Excel Macro

• Important : Declaring multiple Variables with one Dim Statement

• For Next Loop in Excel Macro

• While Loop and Do While Loop in Excel VBA

• VBA Programming : If Then Statements / If else Statements

• VBA Programming : Decision – Select Case in Excel VBA

• Events, Error Handling

• Excel Objects, Programming Charts

• Message box, Input box