Advanced Macro with VBA Programming (MIS 2)

• Chapter 1:
• What Is VBA?
• Okay, So What Is VBA?
• What Can You Do with VBA
• Inserting a bunch of text
• Automating a task you perform frequently
• Automating repetitive operations
• Creating a custom command
• Creating a custom button
• Developing new worksheet functions
• Creating complete, macro-driven applications
• Creating custom add-ins for Excel
• Advantages and Disadvantages of VBA
• VBA advantages
• VBA disadvantages
• VBA in a Nutshell
• An Excursion into Versions
• TEAM LinG
  
  
• Chapter 2:
• Jumping Right In
• First Things First
• What You’ll Be Doing
• Taking the First Steps
• Recording the Macro
• Testing the Macro
• Examining the Macro
• Modifying the Macro
• Saving Workbooks that Contain Macros
• Understanding Macro Security
• More about the Name And Time Macro
  
  
• Chapter 3:
• Working In the Visual Basic Editor
• What Is the Visual Basic Editor?
• Activating the VBE
• Understanding VBE components
• Working with the Project Explorer
• Adding a new VBA module
• Removing a VBA module
• Exporting and importing objects
• Working with a Code Window
• Minimizing and maximizing windows
• Creating a module
• Getting VBA code into a module
• Entering code directly
• Using the macro recorder
• Copying VBA code
• Customizing the VBA Environment
• Using the Editor tab
• Using the Editor Format tab
• Using the General tab
• Using the Docking tab
  
  
• Chapter 4:
• Introducing the Excel Object Model
• Excel Is an Object?
• Climbing the Object Hierarchy
• Wrapping Your Mind around Collections
• Referring to Objects
• Chapter 5:
• VBA Sub and Function Procedures
• Subs versus Functions
• Looking at Sub procedures
• Looking at Function procedures
• Naming Subs and Functions
• Executing Sub procedures
• Executing the Sub procedure directly
• Executing the procedure from the Macro dialog box
• Executing a macro by using a shortcut key
• Executing the procedure from a button or shape
• Executing the procedure from another procedure
• Executing Function procedures
• Calling the function from a Sub procedure
• Calling a function from a worksheet formula
  
  
• Chapter 6:
• Using the Excel Macro Recorder
• Is It Live or Is It VBA
• Recording Basics
• Preparing to Record
• Relative or Absolute
• Recording in absolute mode
• Recording in relative mode
• What Gets Recorded?
• Recording Options
• Macro name
• Shortcut key
• Store Macro In
• Description
  
  
• Chapter 7:
• Essential VBA Language Elements
• Using Comments in Your VBA Code
• Using Variables, Constants, and Data Types
• Understanding variables
• What are VBA’s data types
• Declaring and scoping variables
• Working with constants
• Working with strings
• Working with dates
  
  
• Chapter 8:
• Working with Range Objects
• Other Ways to Refer to a Range
• The Cells property
• The Offset property
• Referring to entire columns and rows
• Some Useful Range Object Properties
• The Value property
• The Text property
• The Count property
• The Column and Row properties
• The Address property
• The Has Formula property
• The Font property
• The Interior property
• The Formula property
• The Number Format property
• Some Useful Range Object Methods
• The Select method
• The Copy and Paste methods
• The Clear method
• The Delete method
  
  
• Chapter 9:
• Using VBA and Worksheet Functions
• What Is a Function
• Using Built-in VBA Functions
• VBA function examples
• VBA functions that do more than return a value
• Discovering VBA functions
• Using Worksheet Functions in VBA
• Worksheet function examples
• Entering worksheet functions
• More about Using Worksheet Functions
• Using Custom Functions
  
• Chapter 10:
• Controlling Program Flow and Making Decisions
• Going with the Flow, Dude
• The GoTo Statement
• Decisions, decisions
• The If-Then structure
• The Select Case structure
• Knocking Your Code for a Loop
• For-Next loops
• Do-While loop
• Do-Until loop
• Looping through a Collection
  
  
• Chapter 11:
• Automatic Procedures and Events
• Preparing for the Big Event
• Are events useful?
• Programming event-handler procedures
• Where Does the VBA Code Go?
• Writing an Event-Handler Procedure
• Introductory Examples
• The Open event for a workbook
• The BeforeClose event for a workbook
• The BeforeSave event for a workbook
• Examples of Activation Events
• Activate and deactivate events in a sheet
• Activate and deactivate events in a workbook
• Workbook activation events
• Other Worksheet-Related Events
• The BeforeDoubleClick event
• The BeforeRightClick event
• The Change event
• Events Not Associated with Objects
• The OnTime event
• Keypress events
  
  
• Chapter 12:
• Error-Handling Techniques
• Types of Errors
• An Erroneous Example
• The macro’s not quite perfect
• The macro is still not perfect
• Is the macro perfect yet
• Giving up on perfection
• Handling Errors Another Way
• Revisiting the Enter Square Root procedure
• About the On Error statement
  
  
• Chapter 13:
• Bug Extermination Techniques
• Species of Bugs
• Identifying Bugs
• Debugging Techniques
• Examining your code
• Using the MsgBox function
• Inserting Debug.Print statements
• Using the VBA debugger
• About the Debugger
• Setting breakpoints in your code
• Using the Watch window
• Using the Locals Window
• Bug Reduction Tips
  
  
• Chapter 14:
• VBA Programming Examples
• Working with Ranges
• Copying a range
• Copying a variable-sized range
• Selecting to the end of a row or column
• Selecting a row or column
• Moving a range
• Looping through a range efficiently
• Prompting for a cell value
• Determining the selection type
• Identifying a multiple selection
• Changing Excel Settings
• Changing Boolean settings
• Changing non-Boolean settings
• Working with Charts
• Modifying the chart type
• Looping through the Chart Objects collection
• Modifying chart properties
• Applying chart formatting
• VBA Speed Tips
• Turning off screen updating
• Turning off automatic calculation
• Eliminating those pesky alert messages
• Simplifying object references
• Declaring variable types
• Using the With-End With structure
  
  
• Chapter 15:
• Simple Dialog Boxes
• Why Create User Forms
• The Msg Box Function
• Displaying a simple message box
• Getting a response from a message box
• Customizing message boxes
• The Input Box Function
• Input Box syntax
• An Input Box example
• The Get Open Filename Method
• The syntax
• A Get Open Filename example
• Selecting multiple files
• The Get Save As Filename Method
• Getting a Folder Name
• Displaying Excel’s Built-in Dialog Boxes
  
  
• Chapter 16:
• User Form Basics
• Knowing When to Use a User Form
• Creating User Forms: An Overview
• Working with User Forms
• Inserting a new User Form
• Adding controls to a User Form
• Changing properties for a User Form control
• Viewing the User Form Code window
• Displaying a User Form
• Using information from a User Form
• A User Form Example
• Creating the User Form
• Adding the Command Buttons
• Adding the Option Buttons
• Adding event-handler procedures
• Creating a macro to display the dialog box
• Making the macro available
• Testing the macro
  
  
• Chapter 17:
• Using User Form Controls
• Getting Started with Dialog Box Controls
• Adding controls
• Introducing control properties
• Dialog Box Controls: The Details
• Check Box control
• Combo Box control
• Command Button control
  
  
• Chapter 18:
• User Form Techniques and Tricks
• Using Dialog Boxes
• A User Form Example
• Creating the dialog box
• Writing code to display the dialog box
• Making the macro available
• Trying out your dialog box
• Adding event-handler procedures
• Validating the data
• Now the dialog box works
• More User Form Examples
• A List Box example
• Selecting a range
• Using multiple sets of Option Buttons
• Using a Spin Button and a Text Box
• Using a User Form as a progress indicator
• Creating a tabbed dialog box
• Displaying a chart in a dialog box
• A Dialog Box Checklist
  
  
• Chapter 19:
• Accessing Your Macros
• Through the User Interface
• Command Bars and Excel 2007
• Excel 2007 Ribbon Customization
• Working with Command Bars
• Commanding the Command Bars collection
• Listing all shortcut menus
  
  
• Chapter 20:
• Creating Worksheet Functions
• and Living to Tell about It
• Why Create Custom Functions?
• Understanding VBA Function Basics
• Writing Functions
• Working with Function Arguments
• Function Examples
• A function with no argument
• A function with one argument
• A function with two arguments
• A function with a range argument
• A function with an optional argument
• A function with an indefinite number of arguments
• Functions That Return an Array
• Returning an array of month names
• Returning a sorted list
• Using the Insert Function Dialog Box
• Displaying the function’s description
• Argument descriptions
  
  
• Chapter 21:
• Creating Excel Add-Ins
• Okay . . . So What’s an Add-In?
• Why Create Add-Ins
• Working with Add-Ins
• Add-in Basics
• An Add-in Example
• Setting up the workbook
• Testing the workbook
• Adding descriptive information
• Protecting the VBA code
• Creating the add-in
• Opening the add-in
• Distributing the add-in
• Modifying the add-in
  
  
• Chapter 22:
• Ten VBA Questions (And Answers)
• The Top Ten Questions about VBA
  
  
• Chapter 23:
• (Almost) Ten Excel Resources
• The VBA Help System
• Microsoft Product Support
• Internet Newsgroups
• Internet Web Sites
• Excel Blogs
• Google
• Local User Groups

Mastering In Advanced SQL (MIS 3)

 

SQL - Overview

What is SQL?

MySQL With DBMS & RDBMS,

Oracle using PL/SQL,

MS Access version of SQL.

Why SQL?

A Brief History of SQL

SQL Process

Query Dispatcher

Optimization Engines

Classic Query Engine

SQL Query Engine, etc.

SQL Commands

DDL - Data Definition Language

Command & Description

Creates a new table, a view of a table.

Modifies an existing database object, such as a table.

Deletes an entire table,other objects in the database.

DML - Data Manipulation Language

Command & Description

Retrieves certain records from one or more tables.

Creates a record.

Modifies records.

Deletes records.

DCL - Data Control Language

Command & Description

Gives a privilege to user.

Takes back privileges granted from user.

SQL - RDBMS Concepts

What is RDBMS?

What is a table?

What is a field?

What is a Record or a Row?

What is a column?

What is a NULL value?

SQL Constraints

NOT NULL Constraint

DEFAULT Constraint

UNIQUE Constraint

PRIMARY Key

FOREIGN Key

CHECK Constraint

Dropping Constraints

Integrity Constraints

Data Integrity

Entity Integrity

Domain Integrity

Referential integrity

User-Defined Integrity integrity.

Database Normalization

Eliminating redundant data.

Ensuring data dependencies make sense.

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

RDBMS Databases

Concurrency

Read Consistency

Locking Mechanisms

Quiesce Database

Portability

Self-managing database

SQL*Plus

ASM

Scheduler

Resource Manager

Data Warehousing

Materialized views

Bitmap indexes

Table compression

Parallel Execution

Analytic SQL

Data mining

Partitioning

SQL - Syntax

SQL SELECT Statement

SQL - SORTING Results

SQL DISTINCT Clause

SQL WHERE Clause

SQL AND/OR Clause

SQL IN Clause

SQL BETWEEN Clause

SQL LIKE Clause

SQL ORDER BY Clause

SQL GROUP BY Clause

SQL COUNT Clause

SQL HAVING Clause

SQL CREATE TABLE Statement

SQL DROP TABLE Statement

SQL CREATE INDEX Statement

SQL DROP INDEX Statement

SQL DESC Statement

SQL TRUNCATE TABLE Statement

SQL ALTER TABLE Statement

SQL ALTER TABLE Statement (Rename)

SQL INSERT INTO Statement

SQL UPDATE Statement

SQL DELETE Statement

SQL CREATE DATABASE Statement

SQL DROP DATABASE Statement

SQL USE Statement

SQL COMMIT Statement

SQL ROLLBACK Statement

DATA TYPES

bigint

int

smallint

tinyint

bit

decimal

numeric

money

smallmoney

float

real

datetime

smalldatetime

date

time

char

varchar

varchar(max)

text

nchar

nvarchar

nvarchar(max)

ntext

binary

varbinary

varbinary(max)

image

sql_variant

timestamp

uniqueidentifier

xml

cursor

table

SQL - Operators

What is an Operator in SQL

SQL Arithmetic Operators

+ (Addition)

- (Subtraction)

* (Multiplication)

/ (Division)

% (Modulus)

SQL Comparison Operators

=

!=

<>

>

<

>=

<=

!<

!>

SQL Logical Operators

ALL

AND

ANY

BETWEEN

EXISTS

IN

LIKE

NOT

OR

IS NULL

UNIQUE

SQL - Expressions

Boolean

Numeric

Date

SQL - Using Joins

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL JOIN

SELF JOIN

CARTESIAN JOIN

SQL - UNIONS CLAUSE

The same number of columns selected

The same number of column expressions

The same data type and

Have them in the same order

SQL - NULL Values

SQL - Alias Syntax

SQL - Indexes

SQL - Using Views

Structure data in a way that users find intuitive.

Restrict access to the data.

Summarize data from various tables.

SQL - Transactions

Atomicity

Consistency

Isolatio

Durability

SQL - Wildcard Operators

The percent sign (%)

The underscore (_)

SQL - Date Functions

ADDDATE()

Adds dates

ADDTIME()

Adds time

CONVERT_TZ()

CURDATE()

Returns the current date

CURRENT_DATE(), CURRENT_DATE

Synonyms for CURDATE()

CURRENT_TIME(), CURRENT_TIME

Synonyms for CURTIME()

CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP

Synonyms for NOW()

CURTIME()

DATE_ADD()

Adds two dates

DATE_FORMAT()

Formats date as specified

DATE_SUB()

Subtracts two dates

DATE()

DATEDIFF()

DAY()

Synonym for DAYOFMONTH()

DAYNAME()

DAYOFMONTH()

Returns the day of the month (1-31)

DAYOFWEEK()

DAYOFYEAR()

Returns the day of the year (1-366)

EXTRACT

Extracts part of a date

FROM_DAYS()

FROM_UNIXTIME()

HOUR()

Extracts the hour

LAST_DAY

LOCALTIME(), LOCALTIME

Synonym for NOW()

LOCALTIMESTAMP, LOCALTIMESTAMP()

Synonym for NOW()

MAKEDATE()

MAKETIME

MAKETIME()

MICROSECOND()

MINUTE()

MONTH()

MONTHNAME()

NOW()

PERIOD_ADD()

PERIOD_DIFF()

QUARTER()

SEC_TO_TIME()

SECOND()

STR_TO_DATE()

SUBDATE()

SUBTIME()

SYSDATE()

TIME_FORMAT()

Formats as time

TIME_TO_SEC()

TIME()

TIMEDIFF()

TIMESTAMP()

TIMESTAMPADD()

TIMESTAMPDIFF()

TO_DAYS()

UNIX_TIMESTAMP()

UTC_DATE()

UTC_TIME()

UTC_TIMESTAMP()

WEEK()

WEEKDAY()

WEEKOFYEAR()

YEAR()

YEARWEEK()

Unit Values

MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

SECOND_MICROSECOND

MINUTE_MICROSECOND

MINUTE_SECOND

HOUR_MICROSECOND

HOUR_SECOND

HOUR_MINUTE

DAY_MICROSECOND

DAY_SECOND

DAY_MINUTE

DAY_HOUR

YEAR_MONTH

TIMESTAMPADD

FRAC_SECOND

SECOND, MINUTE

HOUR, DAY

WEEK

MONTH

QUARTER or

YEAR

SQL - Temporary Tables

Inserting Temporary Tables

Dropping Temporary Tables

SQL - Clone Tables

SQL - Sub Queries

Subqueries must be enclosed within parentheses.

A subquery can have only one column in the SELECT clause.

The GROUP BY command can be used.

Subqueries that return more than one row.

The SELECT evaluate to a BLOB, ARRAY, CLOB, or NCLOB.

A subquery cannot be immediately enclosed in a set function.

The BETWEEN operator cannot be used with a subquery.

SQL - Using Sequences

Using AUTO_INCREMENT column

Obtain AUTO_INCREMENT Values

Renumbering an Existing Sequence

Starting a Sequence at a Particular Value

SQL - Handling Duplicates

SQL - Injection

SQL - Database Tunning

Power BI Introduction

? Data Visualization, Reporting

? Business Intelligence (BI), Traditional BI, Self-Serviced BI

? Cloud Based BI, On Premise BI

? Power BI Products

? Power BI Desktop (Power Query, Power Pivot, Power View)

? Flow of Work in Power BI Desktop

? Power BI Report Server, Power BI Service, Power BI Mobile Flow

? of Work in Power BI / Power BI Architecture

? A Brief History of Power BI

Power Query

? Data Transformation, Benefits of Data Transformation

? Shape or Transform Data using Power Query

? Overview of Power Query / Query Editor, Query Editor User Interface The

? Ribbon (Home, Transform, Add Column, View Tabs)

? The Queries Pane, The Data View / Results Pane, The Query Settings Pane, FormulaBar

? Saving the Work

? Datatypes, Changing the Datatype of a Column Filter

? in Power Query

? Auto Filter / Basic Filtering

? Filter a Column using Text Filters

? Filter a Column using Number Filters

? Filter a Column using Date Filters

? Filter Multiple Columns

? Remove Columns / Remove Other Columns

? Name / Rename a Column

? Reorder Columns or Sort Columns

? Add Column / Custom Column Split

? Columns

? Merge Columns

? PIVOT, UNPIVOT Columns

? Transpose Columns

? Header Row or Use First Row as Headers

? Keep Top Rows, Keep Bottom Rows Keep

? Range of Rows

? Keep Duplicates, Keep Errors

? Remove Top Rows, Remove Bottom Rows, Remove Alternative Rows

? Remove Duplicates, Remove Blank Rows, Remove Errors

? Group Rows / Group By

M Language

? IF..ELSE Conditions, Transform Column () Types

? Remove Columns (), Split Columns (),Replace Value()

? Table. Distinct Options and GROUP BY Options Table. ? Group (), Table. Sort () with Type Conversions PIVOT

? Operation and Table. Pivot(). List Functions Using

? Parameters with M Language

Data Modeling

? Data Modeling Introduction

? Relationship, Need of Relationship

? Relationship Types / Cardinality in General

? One-to-One, One-to-Many (or Many-to-One), Many-to-Many

? AutoDetect the relationship, Create a new relationship, Edit existing relationships

? Make Relationship Active or Inactive

? Delete a relationship

DAX

? What is DAX, Calculated Column, Measures

? DAX Table and Column Name Syntax

? Creating Calculated Columns, Creating Measures Calculated

? Columns Vs Measures

? DAX Syntax & Operators

? DAX Operators

? Types of Operators

? Arithmetic Operators, Comparison Operators, Text Concatenation Operator, Logical

? Operators

DAX Functions Types

? Date and Time Functions

? Text Functions

? Logical Functions

? Math & Statistical Functions

? Filter Functions

Time Intelligence Functions

Date and Time Functions

? YEAR, MONTH, DAY

? WEEKDAY, WEEKNUM

? FORMAT (Text Function) à Month Name, Weekday Name

? DATE, TODAY, NOW

? HOUR, MINUTE, SECOND, TIME

? DATEDIFF, CALENDAR

? Creating Date Dimension Table

Text Functions

? LEN, CONCATENATE (&)

? LEFT, RIGHT, MID UPPER, 

? LOWER

? TRIM, SUBSTITUTE, BLANK

Logical Functions

? IF

? TRUE, FALSE NOT, ? OR, IN, AND

? IFERROR SWITCH

Math & Statistical Functions

? INT

? ROUND, ROUNDUP, ROUNDDOWN

? DIVIDE

? EVEN, ODD

? POWER, SIGN

? SQRT, FACT

? SUM, SUMX

? MIN, MINX

? MAX, MAXX

? COUNT, COUNTX

? AVERAGE, AVERAGEX

? COUNTROWS, COUNTBLANK

Filter Functions

? CALCULATE

? ALL

? RELATED

? Report View

Report View User Interface

? Fields Pane, Visualizations pane, Ribbon, Views, Pages Tab, Canvas Visual

? Interactions

? Interaction Type (Filter, Highlight, None)

? Visual Interactions Default Behavior, Changing the Interaction

Grouping and Binning Introduction

? Using grouping, Creating Groups on Text Columns

? Using binning, Creating Bins on Number Column and Date Columns

? Sorting Data in Visuals

? Changing the Sort Column, Changing the Sort Order

? Sort using column that is not used in the Visualization

? Sort using the Sort by Column button

? Hierarchy Introduction, Default Date Hierarchy

? Creating Hierarchy, Creating Custom Date Hierarchy

? Change Hierarchy Levels

? Drill-Up and Drill-Down Reports

? Data Actions, Drill Down, Drill Up, Show Next Leve

Visualizations

? Visualizing Data, Why Visualizations

? Visualization types, Create and Format Bar and Column Charts

? Create and Format Stacked Bar Chart Stacked Column Chart Create

? and Format Clustered Bar Chart, Clustered Column Chart

? Create and Format 100% Stacked Bar Chart, 100% Stacked Column Chart Createand

? Format Pie and Donut Charts

? Create and Format Scatter Charts

? Create and Format Table Visual, Matrix Visualization

? Line and Area Charts

? Create and Format Line Chart, Area Chart, Stacked Area Chart

? Combo Charts

? Create and Format Line and Stacked Column Chart, Line and Clustered ColumnChart

? Create and Format Ribbon Chart, Waterfall Chart, Funnel Chart

Power BI Service

? Power BI Service Introduction, Power BI Cloud Architecture

? Creating Power BI Service Account, SIGN IN to Power BI Service Account

? Publishing Reports to the Power BI service, Import / Getting the Report to PBI ServiceMy

? Workspace / App Workspaces Tabs

? DATASETS, WORKBOOKS, REPORTS, DASHBOARDS

? Working with Datasets, Creating Reports in Cloud using Published Datasets

Creating Dashboards

? Pin Visuals and Pin LIVE Report Pages to Dashboard

? Advantages of Dashboards

? Interacting with Dashboards

? Formatting Dashboard

? Sharing Dashboard