2016 ms excel_bm

September 16 MS Excel 2007 Manager - Rajesh Math 1

MS Excel Training

For Business Managers/Working Professionals

Rajesh Math


Introduction

Rajesh Math

Freelance Corporate Trainer

Currently Director Adroit IT Network Solutions specializing in Financial

applications

MS(CS),Boston University,USA

MBA(Systems) from Pune University

19 years ITES/KPO experience delivering IT solutions(13 years in US)

4 years Management /ITES Training


Agenda

Excel Introduction

History

Applications

Online Help

Excel Techniques

Named Ranges

VLOOKUP and HLOOKUP

Data validation

Excel Tips-Tricks

Little know Secrets of Excel

Menus Ribbon

Templates : Reusing Documents

Office Online Templates

Custom Templates

Pivot Table

Creating Pivot Table

Creating Pivot Charts

Data Table

Scenarios for what-if Analysis


Agenda

Excel Formulas

Sorting and Filtering

Connecting Excel To Database

Connecting Excel to Web

Macros

Difference in Absolute and Relative Referencing

Concept of macro

Where macros can be stored

Assigning macros to key board short-cuts, buttons, icons (toolbars)

Introduction to VB Editor

Project Explorer

Creating Modules

Variables, Constants

Message Box, Input Box

VBA If and Loops


MS Excel History

Spreadsheet Program – organize your data into lists and then

summarize , compare and present your data graphically.

Microsoft Excel (full name Microsoft Office Excel) is a spreadsheet-

application written and distributed by Microsoft for Microsoft Windows

and Mac OS X. It features calculation, graphing tools, pivot tables and a

macro programming language called VBA. It has been the most widely

used spreadsheet application available for these platforms since version 5

in 1993 Excel is part of Microsoft Office.


What is Excel ?

It allows users to input data into cells included in a grid made of rows and

columns. The user can introduce functions and, referring to the cells by its name

(formed by the letter or combinations of letters that identifies the column and the

number that identifies the row), can display the result in that cell. The result will

change dynamically if the content of the included cells changes.

Each file produced by Excel is called a book. Each book can contain several

sheets, with different names . Each cell can contain a different piece of

information, and can be referred by its unique name.

A spreadsheet is a table used to store various types of data. The data is arranged

in rows and columns to make it easier to store, organize, and analyze the

information. A spreadsheet application is a computer program such as Excel,

Lotus 1-2-3, OpenOffice Calc, or Google Spreadsheets. It has a number of built

in features and tools, such as functions, formulas, charts, and data analysis tools

that make it easier to work with large amounts of data.

Why Excel ?


Excel, as you probably know, is the world‘s most widely-used spreadsheet

program, and is part of the Microsoft Office suite. Other spreadsheet programs

are available, but Excel is by far the most popular and has become the world

standard.

Much of the appeal of Excel is due to the fact that it‘s so versatile. Excel‘s forte,

of course, is performing numerical calculations, but Excel is also very useful for

non-numerical applications. Here are just a few of the uses for Excel:

Number crunching: Create budgets, analyze survey results, and perform just about

any type of financial analysis you can think of.

Creating charts: Create a wide variety of highly customizable charts.

Organizing lists: Use the row-and-column layout to store lists efficiently.

Accessing other data: Import data from a wide variety of sources.

Creating graphics and diagrams: Use Shapes and the new SmartArt to create

professional-looking diagrams.

Automating complex tasks: Perform a tedious task with a single mouse click with

Excel‘s macro capabilities.


MS Excel Screen Terms

Excel 2003 v/s Excel 2007



MS Excel Worksheet Movement Keys

Online Help



Excel Tips-Tricks

When working with large workbooks in which all of the worksheet tabs are

not visible, you may get frustrated with scrolling to find the sheet you need.

Here's a really quick tip to pass on. Right-click on any of the tab-scrolling

buttons (to the left of the sheet tabs) and select the tab you want by clicking

on the name.

1.Faster Way to Access Any Worksheets

2.Add color to sheet tabs

On the Format menu, point to Sheet, and then click

Tab Color. You can also right-click on the sheet tab and

then click Tab Color.

Click on the color you want and click OK.


Excel Tips-Tricks

Here's a quick tip to access menus using the keyboard.

Instead of selecting an option from menu using an mouse you can select it

using a combination of ALT key and the key shown below. These keys are

displayed on pressing an ALT Key e.g. Alt-M will take me to formulas

3 Alt Activated Menus


Excel Tips-Tricks

4.Add Time and Date to Printed Spreadsheets

If printouts of multiple versions of your Microsoft Excel 2007 spreadsheets

get distributed amongst officemates, it can prove cumbersome to determine

who has the latest version of a spreadsheet. This can be especially troubling if

office workers make copies of copies of your spreadsheet so others can make

suggestions, thus you may not know which version a particular person has.

1. Select Page-Layout -> Print Titles->Header/footer

2. The "Page Setup" multi-tabbed dialog box appears, with the

"Header / Footer" tab selected. Either click the "Custom Header" or

"Custom Footer" button.

3. A "Header" or "Footer" dialog box appears. Click on which

section of the header or footer you want the date and or time to

appear, either the left, center, or right section.


Excel Tips-Tricks

On the Insert menu, click Symbol. Note In the Symbol dialog box, on the

Symbols tab, in the Font box, click Wingdings.

Scroll to the bottom of the list, where you will find several symbols.

Double-click the symbol that you want.

5. Insert a symbol by using the Symbol command

Excel Templates


A Template is essentially a model that serves as the basis for more usage.

An Excel template is a workbook that‘s used to create other workbooks.

Using Excel Standard Templates


To create a workbook based on a template, just select the template

What you do next depends on the template. Every template is different,

but most online templates are self-explanatory.

Some workbooks require customization. Just replace the generic

information with your own information.

Custom Template(s)

A custom template is essentially a normal workbook, and it can use any Excel feature, such

as charts, formulas and macros. Usually, a template is set up so that the user can enter

values and get immediate results. In other words, most templates include everything but

the data, which is entered by the user.

Excel supports three types of templates, which I discuss in the following sections:

1. The default workbook template: Used as the basis for new workbooks.

2. The default worksheet template: Used as the basis for new worksheets inserted into a

workbook.

3. Custom workbook templates: Usually, these ready-to-run workbooks include formulas,

but they can be as simple or as complex as you like. Typically, these templates are set up

so that a user can simply plug in values and get immediate results. The Microsoft Office

Online templates are examples of this type of template.


Handson #1

Use the invoice template available on Office Online to create an invoice

** Create a template of Your Weekly Status Report


Absolute / Relative References

An absolute reference uses two dollar signs in its address: one for the

column letter and one for the row number (for example, $A$5). Excel

also allows mixed references in which only one of the address parts is

absolute (for example, $A4 or A$4).

By default, Excel creates relative cell references in formulas. The

distinction becomes apparent when you copy a formula to another cell.


When you copy a formula, you have the choice of making it relative or absolute. Relative

references adjust to location. Absolute references remain constant regardless of location.

Handson #2

Use the Ex02_Abs_Relative_Formulae.xlsx to calculate the cost of items

ordered and installation cost to calculate the net amount



Introducing the Ribbon

Ribbon

The commands available in the Ribbon vary, depending upon which tab is selected. The Ribbon is

arranged into groups of related commands. Here‘s a quick overview of Excel‘s tabs.

Home: You‘ll probably spend most of your time with the Home tab selected. This tab contains the basic Clipboard

commands, formatting commands, style commands, commands to insert and delete rows or columns, plus an assortment

of worksheet editing commands

Insert: Select this tab when you need to insert something in a worksheet—a table, a diagram, a chart, a symbol, and so

on.

Page Layout: This tab contains commands that affect the overall appearance of your worksheet, including settings that

deal with printing.

Formulas: Use this tab to insert a formula, name a range, access the formula auditing tools, or control how Excel

performs calculations.

Data: Excel‘s data-related commands are on this tab.

Review: This tab contains tools to check spelling, translate words, add comments, or protect sheets.

View: The View tab contains commands that control various aspects of how a sheet is viewed.Some commands on this

tab are also available in the status bar.

Developer: This tab isn‘t visible by default. It contains commands that are useful for programmers. To display the

Developer tab, choose Office ➪ Excel Options and then select Popular. Place a check mark next to Show Developer Tab

In The Ribbon.

Add-Ins: This tab is visible only if you‘ve loaded a workbook or add-in that customizes the menu or toolbars. Because

menus and toolbars are no longer available in Excel 2007, these customizations appear in the Add-Ins tab.


Let us review Ex01_Table.xlsx


Handson #3

Excel ->Date

To Excel, a date is simply a number. More precisely, a date is a serial

number that represents the number of days since the fictitious date of

January 0, 1900. A serial number of 1 corresponds to January 1, 1900; a

serial number of 2 corresponds to January 2, 1900, and so on.

Excel support dates from January 1, 1900, through December 31, 9999

(serial number = 2,958,465).

The following function displays the current date in a cell: =TODAY()

The DATEVALUE function converts a text string that looks like a date

into a date serial number. The following formula returns 39316, the date

serial number for August 22, 2007: =DATEVALUE(―8/22/2007‖)

The following formula returns TRUE if the year of the date in cell A1 is

a leap year. Otherwise, it returns FALSE.

=IF(MONTH(DATE(YEAR(A1),2,29))=2,TRUE,FALSE)


Auto Populate Dates


Named Range

A name is a meaningful shorthand that makes it easier to understand the purpose of a cell reference (cell reference:

The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the

intersection of column B and row 3 is B3.) These are useful in

a. Constant : A value that is not calculated. For example, the number 210 and the text "Quarterly Earnings" are

constants. An expression, or a value resulting from an expression, is not a constant.

b. Formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together

produce a new value. A formula always begins with an equal sign (=)

c. Table (table: A collection of data about a particular subject that is stored in records (rows) and fields (columns),

each of which may be difficult to comprehend at first glance.

Following are examples to improve clarity and understanding.

Example Type Example with no name Example with a name

Reference =SUM(C20:C30) =SUM(FirstQuarterSales)

Constant =PRODUCT(A5,8.3) =PRODUCT(Price,WASalesTax)

Formula =SUM(VLOOKUP(A1,B1:F20,5,FALSE), —G5) =SUM(Inventory_Level,—Order_Amt)

Table C4:G36 =TopSales06

Types of Names :

Defined name A name that represents a cell, range of cells, formula, or constant value.

Table name A name for an Excel table, which is a collection of data about a particular subject that is stored in records

(rows) and fields (columns).



Lookup Functions

Returns a value either from a one-row or one-column range or from an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.). The LOOKUP function has two syntax forms: the vector form and the array form. If you want to then see Usage. Look in a one-row or one-column range (known as a vector) for a value and return a value from the same position in a second one-row or one-column range

Vector form: Use the vector form when you have a large list of values to look up or when the values may change over time.Look in the first row or column of an array for the specified value and return a value from the same position in the last row or column of the array

Array form: Use the array form when you have a small list of values and the values remain constant over time. Note: You can also use the LOOKUP function as an alternative the IF function for elaborate tests or tests for more than seven conditions. See the examples in the array form.


HLOOKUP, VLOOKUP Functions

Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value The value to search in the first column of the table array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.). Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

Table_array Two or more columns of data. Use a reference to a range or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.

Col_index_num The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is:

Less than 1, VLOOKUP returns the #VALUE! error value.

Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Hlookup



Open the Ex04Lookup.xls sample and look at cell E6.

o =VLOOKUP(E4,Toolist,2,FALSE)

o Try Changing the contents of E4 to a new value in the list A3..A18

and see the effect on E6

Open the Lookup.xls sample and look at Hlookup Example.

o =HLOOKUP(C9,B3:P4,2,FALSE)

o Try Changing the contents of C9 to a new value in the list B3..P4

and see the effect on E6

*** http://www.experiglot.com/2007/12/27/how-to-use-vlookup-in-excel-part-ii-

some-tips-and-advanced-excel-tricks/

Handson #4

http://www.experiglot.com/2007/12/27/how-to-use-vlookup-in-excel-part-ii-some-tips-and-advanced-excel-tricks/




























Data Validation

Microsoft Excel data validation lets you define what type of data you

want entered in a cell. For example, you can allow entry of a letter grade

with only the letters A through F. You can set up data validation to

prevent users from entering data that isn't valid, or allow invalid data

but check for it after the user is finished. You can also provide messages

to define what input you expect for the cell, and instructions to help users

correct any errors.

Data validation is particularly useful when you're designing forms or

worksheets that other people will use to enter data, such as budget forms

or expense reports.


Data Validation

When data is entered that doesn't meet your requirements, Excel displays a message with instructions you provide.

When data is entered that doesn't meet your requirements, Excel displays a message with instructions you provide.

Types of data you can validate:

Excel lets you designate the following types of valid data for a cell:

• Numbers: Specify that the entry in a cell must be a whole number or a decimal number. You can set a minimum or maximum, exclude a certain number or range, or use a formula to calculate whether a number is valid.

• Dates and times: Set a minimum or maximum, exclude certain dates or times, or use a formula to calculate whether a date or time is valid.

• Length: Limit how many characters can be typed in a cell, or require a minimum number of characters.

• List of values: Make a list of the choices for a cell — such as small, medium, large — and allow only those values in the cell. You can display a dropdown arrow when a user clicks the cell to make it easy to pick from your list.


Try the smaller sample Ex05DataEntryValidation.xls and try to input

different values.

Try to open the next sample Ex05DataValidationSamples.xls

Handson #5

Pivot Table

The first step in creating a Pivot Table is to organize your data in a list of rows

and columns. In Excel 2007, you can format this list as an Excel Table, and use

that as the dynamic source for your Pivot Table.

Before you create a pivot table, make sure your data is organized correctly. There

are instructions on the following pages, for setting up your source data in a table,

organized into rows and columns.


Adding Fields to the Pivot Table

An empty pivot table is created in your workbook, either on a new sheet, or the

existing sheet that you selected. When you select a cell within the pivot table, a

PivotTable Field List appears, at the right of the worksheet.


Grouping Pivot Table

In a Pivot Table, you can group the items in a Row or Column field.

For example, items in a date field can be grouped by month, and items in

a number field can be grouped by tens.

In the Sample Pivot Sales book -> TestPivot sheet

Right-click the Date field button.

Choose Group to select the Group Field


Generate Pivot Chart


Open Ex06PivotSales.xlsx

Let us review Ex06PivotTableExpensesByWeekCategory.xls

Verify the results using Sort & Filter(which is covered later)


Handson #6

Data Table

In Excel, a Data Table is a way to see different results by altering an input cell in your

formula. Data Tables is also an advanced topic in Microsoft Excel that falls under the

category of What-If Analysis. What-If or Sensitivity Analysis is carried out to study the

variation of the output to changes in the input variable.

Consider a case of compound interest, where you invest a certain amount of money in a

bank deposit and the amount is compounded every year.

Formula for calculating compound interest:

A = P * (1 + r/n) ^ nt

Where:

P = principal amount (initial investment)

r = annual interest rate (as a decimal)

n = number of times the interest is compounded per year

t = number of years

A = amount after time t

Now, if suppose we want to see what the final amount will be at different interests rates,

we can quickly use a data table for the same.


http://en.wikipedia.org/wiki/What-if_analysis



http://en.wikipedia.org/wiki/Compound_interest

Data Table


Open a new excel file and enter the following given screenshot below:

The cell B5 has the formula =B1 * (1 + B2) ^ B3

As you can see, the $5000 invested at 7.5% for 5 years will give 7,178.15.

Now, we will create a data table to see what amount we will receive by changing

the interest rate.

Fill cells A6 to A10 with different interest rates. I've filled it with values from 6% to

10%. Now, select the cells A5:B10.

In Excel 2007, goto Data > What-If Analysis > Data Table. In Excel

2003, the menu path is Data > Table or you can use the shortcut key

Alt + D + T in this order.

The Column Input Cell is the variable

whose value we are going to vary

Open Ex05zDataTable.xlsx


Handson #5

Scenarios/What-if Analysis

Scenarios come under the heading of "What-If Analysis" in Excel 2007.

They are similar to tables in that you are changing values to get new

results. For example, What if I reduce the amount I'm spending on food?

How much will I have left then? Scenarios can be saved, so that you can

apply them with a quick click of the mouse

.\..\Sample Files\Excel2007_Scenario_Analysis.htm


E:/IndiaOperations/MSOfficeTrng/Office2007/Nov18/SampleFiles/Excel2007_Scenario_Analysis.htm







Formulas

Formulas are one of the most useful and well used features in

spreadsheets such as Excel. Formulas can be used to perform basic

number crunching, such as addition or subtraction, as well as more

complex calculations such as payroll deductions or averaging a student's

test results. Using formulas effectively is central to getting the most out

of your spreadsheets. ADDITION cell A1 to A10 = sum (A1: A10)

AVERAGE cell A1 to A10 = average (A1: A10)

MAXIMUM cell A1 to A10 = max (A1: A10)

MINIMUM cell A1 to A10 = min (A1: A10)

IF(A1 <= 0; "ordering", "stock")

This would mean: If the contents of cell A1 is less than or equal to zero, then it displays "to

order", otherwise (if the contents of cell A1 is greater than zero) on display "at stock".


Excel If

In Excel, If function returns one value if a specified condition evaluates

to TRUE, or another value if it evaluates to FALSE.

The syntax for the If function is:

If( condition, value_if_true, value_if_false )

condition is the value that you want to test.

value_if_true is the value that is returned if condition evaluates to TRUE.

value_if_false is the value that is return if condition evaluates to FALSE.

e.g IF(Service=‗Y‘,ServiceTax=12.33,ServiceTax=0)


Excel If Example


Excel Nested If

Rules -> If (cell A1) is less than 20, then times it by 1,

If it is greater than or equal to 20 but less than 50, then times it by 2

If its is greater than or equal to 50 and less than 100, then times it by 3

And if it is great or equal to than 100, then times it by 4

Answer: You can write a nested IF statement to handle this:

=IF(A1<20, A1*1, IF(A1<50, A1*2, IF(A1<100, A1*3, A1*4)))


Let us review Ex07_IF_Function


Handson #7


Sort and Filter :Sort

Sort and Filter Sorting and Filtering allow you to manipulate data in a

worksheet based on given set of criteria.

Basic Sorts To execute a basic descending or ascending sort based on

one column:

1. Highlight the cells that will be sorted

2. Click the Sort & Filter button on the Home tab

3. Click the Sort Ascending (A-Z) button or Sort Descending (Z-A)

button


Sort

Custom Sorts To sort on the basis of more than one column:

a. Click the Sort & Filter button on the Home tab

b. Choose which column you want to sort by first

c. Click Add Level

d. Choose the next column you want to sort

e. Click OK


Filtering

Filtering allows you to display only data that meets certain criteria. To filter:

Click the column or columns that contain the data you wish to filter

On the Home tab, click on Sort & Filter

Click Filter button

Click the Arrow at the bottom of the first cell

Click the Text Filter

Click the Words you wish to Filter

Let us review Ex08-Sort-Filter example


Handson #8


Connecting Excel To Database


Connecting Excel To Web

Let us review Ex09_GetDBAndWebData.xlsx


Handson #9


What is Excel Macro ?

It allows users to input data into cells included in a grid made of rows

and columns. The user can introduce functions and, referring to the cells

by its name (formed by the letter or combinations of letters that identifies

the column and the number that identifies the row), can display the result

in that cell. The result will change dynamically if the content of the

included cells changes.

If you perform a task repeatedly in Microsoft Excel, you can automate the

task with a macro. A macro is a series of commands and functions (function:

A prewritten formula that takes a value or values, performs an operation,

and returns a value or values. Use functions to simplify and shorten formulas

on a worksheet, especially those that perform lengthy or complex

calculations).


What is Excel Macro ?

When you find yourself repeatedly performing the same actions or tasks

in a spreadsheet, it might be time for you to create a macro.

A macro is a recording of each command and action you perform to

complete a task. Then, whenever you need to carry out that task in a

spreadsheet, you just run the macro instead.

Complex macros are usually created in Excel with the Macro editor.

Simpler macros can, however, be created using the Excel macro

recorder.

Buzzwords

VBA newcomers are often overwhelmed by the terminology that is used in VBA. I’ve put together some key

definitions to help you keep the terms straight. These terms cover VBA and UserForms two important elements

that are used to customize Excel.

1. Code: VBA instructions that are produced in a module sheet when you record a macro. You also can enter VBA

code manually.

2. Controls: Objects on a UserForm (or in a worksheet) that you manipulate. Examples include buttons, check

boxes, and list boxes.

3. Function: One of two types of VBA macros that you can create. (The other is a Sub procedure.) A function

returns a single value. You can use VBA functions in other VBA macros or in your worksheets.

4. Macro: A set of VBA instructions performed automatically.

5. Method: An action taken on an object. For example, applying the Clear method to a Range object erases the

contents and formatting of the cells.

6. Module: A container for VBA code.

7. Object: An element that you manipulate with VBA. Examples include ranges, charts, drawing objects, and so on.

8. Procedure: Another name for a macro. A VBA procedure can be a Sub procedure or a Function procedure.

9. Property: A particular aspect of an object. For example, a Range object has properties, such as Height, Style, and

Name.

10. Sub procedure: One of two types of Visual Basic macros that you can create. The other is a function.

11. UserForm: A container that holds controls for a custom dialog box and holds VBA code to manipulate the

controls.

12. VBA: Visual Basic for Applications. The macro language that is available in Excel, as well as in the other

applications in Microsoft Office.

13. VB Editor: The window (separate from Excel) that you use to create VBA macros and UserForms.



Excel Macros : VBA

The Four Windows in the Visual Basic Editor

When you want somebody to do some work for you open your Email program and you send him a message in a language that he understands (English, Spanish, French...). When you want Excel to do some work for you you open the Visual Basic Editor and you write the instructions in a language that Excel understands VBA ( Visual Basic for Application).

You will develop, test and modify VBA procedures (macros) in the Excel Visual Basic Editor (VBE). It is a very user friendly development environment. The VBA procedures developed in the Excel Visual Basic Editor become part of the workbook in which they are developed and when the workbook is saved the VBA components (macros, modules, user forms. etc.) are saved at the same time. When you send the workbook to the "Recycling bin" the VBA procedures are gone.

The immediate window is used when you are developing your macro code


The Four Windows in the Visual Basic Editor


Excel Macros

A collection of declarations, statements, and procedures stored

together as one named unit.

There are two types of modules:

1. Standard modules

2. Class modules. Both can be run whenever you need to perform

the task.

e.g. if you often enter long text strings in cells, you can create a macro to

format those cells so that the text wraps.

Excel macros are simple, powerful, and easy to customize. Looping

macros are especially useful for working with ranges of data.

More About VBA Macros

If you store one or more macros in a workbook, the file must be saved with macros

enabled, which is a file with an XLSM extension.

A VBA macro (also known as a procedure) can be one of two types:

1. Sub : It is a new command that either the user or another macro can execute.

2. Function : A function always returns a single value (just as a worksheet function

always returns a single value).


Macros Module

A VBA module, which is displayed in a separate window, works like a text editor. You can

move through the sheet, select text, insert, copy, cut, paste, and so on.

You perform actions in VBA by writing (or recording) code in a VBA module sheet and

then executing the macro in any one of various ways. VBA modules are stored in an Excel

workbook, and a workbook can hold any number of VBA modules.

When you enter code in a module sheet, you‘re free to use indenting and blank lines to

make the code more readable. (In fact, this is an excellent habit.)

After you enter a line of code (by pressing Enter), it‘s evaluated for syntax errors. If none

are found, the line of code is reformatted, and colors are added to keywords and identifiers.

Most user-created macros are designed for use in a specific workbook, but you may want

to use some macros in all your work. You can store these general-purpose macros in the

Personal Macro Workbook so that they‘re always available to you. The Personal Macro

Workbook is loaded whenever you start Excel.


Variable Declaration

Variables are used to store information temporarily. As a program runs, it holds

values temporarily in memory. Variables are names that the program associates

with specific locations in memory. The value to which the program refers in

these areas can change throughout a session of program operation.

Each variable has a specific type, that indicates how much memory the data

requires and the operations that can be performed on that kind of data.

Use Option Explicit : By default, VBA doesn't require that you declare your

variables using the Dim statement. If the compiler encounters a name that it

doesn't recognize as an existing variable, one of the VBA reserved words, or a

property or method of a referenced typelib, it will create a new variable by that

name. While this may seem convenient, it can lead to bugs in the code that are

difficult to find (although once found they are simple to fix). Suppose you

declare a variable with the name Index1 and later misspell that name as Idnex1,

the compiler will not flag that as an error. Instead, it will create a new variable

named Idnex1 and initialize it to an empty string, a value of 0, or a Nothing

object reference, depending on the context in which it is used


Declarations

The Dim (stands for dimension) statement is used to declare variables and allocate storage space. It may

appear in a General Declarations section at the top of a code module -or- immediately following a

procedure declaration.

For example ...

Sub SampleCode()

Dim MyNumber as Integer

' Other program statements go here

End Sub

Variables declared within a procedure are not available to other procedures and they only retain values

for the life of that procedure. This refers to a variable's scope. Variables declared at a module-level (or

within a form's General Declarations section) are available to all procedures within that module or form

-and- they continue to retain assigned values for the life of the program. However, these variables are

not available to procedures outside of the module in which they are declared. One alternative to the Dim

statement is the Public keyword, which expands the scope of the variable to make it available to other

procedures outside it's own module or form. It's a better practice to use the narrowest possible scope for

your variables.

Note: Other approaches to scope involve Global, Private, and Static declaration. Also, very similar to

variables are Constants.


Develop a Macro to Change the color of the background

Develop a Function CalcCube which calculates the cube of a number

** See How you can use it as Worksheet Function !!


Handson #8

Msgbox & InputBox

Msgbox : Displays a message in a dialog box, waits for the user to click a

button, and returns an Integer indicating which button the user clicked.Syntax

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

Displays a prompt in a dialog box, waits for the user to input text or click

a button, and returns a String containing the contents of the text box.Syntax

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

' Display message, title, and default value.

MyValue = InputBox(Message, Title, Default)


ms-help://MS.EXCEL.DEV.12.1033/EXCEL.DEV/content/HV01200929.htm


Excel VBA Programming

Loops

1. Do Loop : Suppose you wanted to count the number of rows in a

range of data that can sometimes be small and sometimes really big.

You'd want to use a Do…Loop. This type of loop performs an

action as many times as necessary. It would count whatever number

of rows it found in the range.

Here the While condition is used so that the loop runs as long as the

cell being acted on is not blank. The row being worked on is x, and

(x,1) is the first cell in that row. Used together, the signs <> mean

"does not equal." The quotation marks with nothing between them

indicate a blank cell.

If you wanted the loo



Loops

2. The For Each…Next Loop : You would use the For Each…Next

loop to perform an action on every cell in a range of data. Suppose,

for example, that you wanted to make the word "OK" darker than

other text everywhere in a selected range .

"MyCell" means whatever cell the loop is working on, and "For Each"

means that the loop will work on all cells in the selection. If the loop

finds a cell containing only the word "OK", then it makes that word

darker.



Loops

3. The Do…Loop and the For Each…Next loop are powerful in simple ways. Now we're going to ratchet up the complexity a bit by introducing nested loops. You use nested loops when you need to perform an action on a range of data more than once, or through more than one range of data. For an analogy to nested loops, think of the Earth revolving around the Sun. One full revolution around the Sun, a year, is like the outside loop, and one rotation of the Earth around its axis, a day, is like the inside loop nested within the outside loop. For each year, there are 365 inside loops, and on every January 1st, the outside loop repeats.


QUESTIONS ?

2016 ms excel_bm

Data & Analytics

Transcript of 2016 ms excel_bm