VBA-Macro code

 VBA ─ Overview 

VBA stands for Visual Basic for Applications an event-driven programming language from Microsoft that is now especially used with Microsoft office applications such as MS-Excel, MS-Word, and MS-Access.

It helps to build customized applications and solutions to reach out the abilities of those applications. The advantage of this facility is that you NEED NOT have visual basic application installed on our desktop or Laptop, however, installing Office will help from complicacy in achieving the purpose.

Right from MS-Office 97 to MS-Office 2013, you can use VBA in all office versions,with any of the latest versions available. Among VBA, Excel VBA is the most popular. The benefit of using VBA is that you can create very powerful tools in MS Excel using linear programming.

MS-Excel provide only basic inbuilt functions which might not be sufficient to
perform complex calculations. Under such circumstances, VBA becomes the most obvious solution.

For example, it is very hard to calculate the monthly repayment of a loan using Excel's built-in formulas. Rather, it is easy to program a VBA for such a calculation.

Accessing VBA Editor
In Excel window, press "ALT+F11". A VBA window opens up as shown in the following
screenshot.

Watch Video to Understand this Post

 VBA ─ Excel Macros 

So guys this is the window where we are going to learn VBA. In this guide we cover the basics in a easy-to-understand manner, perfect for the absolute beginner. you will learn how to write a simple macro in a step by step


Step 1: click File -> Options to enable 'Developer' menu in Excel application.

Step 2: Click ‘Customize the Ribbon’ tab and check 'Developer'. Click 'OK'.

Step 3: The 'Developer' ribbon appears in the menu bar. Go to developers tab to access the vba options.

Step 4: Click the 'Visual Basic' button to open the VBA Editor.

Step 5: Start scripting by adding a button. Click Insert -> Select the button. Click on Excel where you want to show the button.

Step 6: do a right-click and choose 'properties'. From dropdown menu.

Step 7: Edit and change the Name and Caption as shown in the following screenshot.

Step 8: Now double-click the button and the sub-procedure outline will be displayed as shown in the following screenshot.

Step 9: Start coding by simply adding a message with Msgbox.

Private Sub Greetings_Click()

       MsgBox  "Hi Learner"

End sub

Step 10: Click the button to execute the sub-procedure. The output of the sub-procedure is shown in the following screenshot.

Note: In Next post, we will demonstrate using a simple button, as explained from
step#1 to 10. Hence , it is important to understand this session thoroughly.




Scroll down to Click Next Post

Watch Video to Understand this Post

SUBSTITUTE

SUBSTITUTE

---

About : Substitutes new text for old text in a text string

Category : Text
Location : Built-in

What Does It Do ?

---

This function replaces a specified piece of text with a different piece of text.
It can either replace all occurrences of the text, or a specific instance.
The function is case sensitive.

Syntax :
=SUBSTITUTE(OriginalText,TextToRemove,TextToInsert,InstanceToUse)

The InstanceToUse is optional, if it is omitted all instances will be substituted.



Formatting

---

No special formatting is needed.



 Know More about This Function Please Click below link.
 Substitute Function | Remove "Line Break" | In Hindi




Note

---

To cope with upper or lower case in the substitution you can use other text functions
such as =UPPER(), =LOWER() or =PROPER() to ensure that the substitution will take place.


Table 1 shows how differing text cases alter the result of the substitution.

Table 2 shows how the =PROPER() function has been used to take account of the mixed cases.

Substitute Function | Remove "Line Break" | In Hindi

CELL Function

CELL Function

---

Returns information about the formatting, location, or contents of a cell.



Category: Information
Location: Built-in

What Does It Do ?

---

This function examines a cell and displays information about the contents, position and formatting.

Syntax :

=CELL("TypeOfInfoRequired",CellToTest)

The TypeOfInfoRequired is a text entry which must be surrounded with quotes " ".

Formatting

---

No special formatting is needed.

Codes used to show the formatting of the cell.

Example

---

The following example uses the =CELL() function as part of a formula which extracts the filename.

DATEDIF Function

DATEDIF Function

---

Age Calculation - Using DATEDIF()


Category : Sample
Location : Sample




You can calculate a persons age based on their birthday and todays date.
The calculation uses the DATEDIF() function.
The DATEDIF() is not documented in Excel 5, 7 or 97, but it is in 2000.
(Makes you wonder what else Microsoft forgot to tell us!)

You can put this all together in one calculation, which creates a text version.

 ="Age is "&DATEDIF(C2,TODAY(),"y")&" Years, "&DATEDIF(C2,TODAY(),"ym")&" Months and "&DATEDIF(C2,TODAY(),"md")&" Days"

Another way to calculate age

This method gives you an age which may potentially have decimal places representing the months.

If the age is 20.5, the .5 represents 6 months.

=(TODAY()-C2)/365.25

INDEX Function Multiple Syntax - 3

INDEX Function

---

About: Uses an index to choose a value from a reference or array



Category: Lookup
Location: Built-in

What Does It Do ?

---

This function picks a value from a range of data by looking down a specified number
of rows and then across a specified number of columns.
It can be used with a single block of data, or non-continuous blocks.

Syntax: There are various forms of syntax for this function.

Syntax 1

---

=INDEX(RangeToLookIn,Coordinate)
This is used when the RangeToLookIn is either a single column or row.
The Co-ordinate indicates how far down or across to look when picking the data from the range.

Both of the examples below use the same syntax, but the Co-ordinate refers to a row when
the range is vertical and a column when the range is horizontal.

Syntax 2

---

=INDEX(RangeToLookIn,RowCoordinate,ColumnColumnCordinate)
This syntax is used when the range is made up of rows and columns.

Syntax 3

---

=INDEX(NamedRangeToLookIn,RowCoordinate,ColumnColumnCordinate,AreaToPickFrom)
Using this syntax the range to look in can be made up of multiple areas.
The easiest way to refer to these areas is to select them and give them a single name.

The AreaToPickFrom indicates which of the multiple areas should be used.

Lets Understand With Example

---

In the following example the figures for North and South have been named as one
range called NorthAndSouth.


This is an extended version of the previous example.
It allows the names of products and the quarters to be entered.
The =MATCH() function is used to find the row and column positions of the names entered.
These positions are then used by the =INDEX() function to look for the data.

  =INDEX(EastAndWest,MATCH(F100,C91:C93,0),MATCH(F101,D90:G90,0),IF(F102=C90,1,IF(F102=C95,2)))

---

Share This On:

IF Function

IF Function

---

About : Specifies a logical test to perform



Category: Logical
Location: Built-in




What Does It Do?

---

This function tests a condition.
If the condition is met it is considered to be TRUE.
If the condition is not met it is considered as FALSE.
Depending upon the result, one of two actions will be carried out.

Syntax :
 =IF(Condition,ActionIfTrue,ActionIfFalse)

The Condition is usually a test of two cells, such as A1=A2.
The ActionIfTrue and ActionIfFalse can be numbers, text or calculations.

Formatting

---

No special formatting is required.

Example 1

---

The following table shows the Sales figures and Targets for sales reps.

Each has their own target which they must reach.

The =IF() function is used to compare the Sales with the Target.

If the Sales are greater than or equal to the Target the result of Achieved is shown.

If the Sales do not reach the target the result of Not Achieved is shown.

Note that the text used in the =IF() function needs to be placed in double quotes "Achieved".

Example 2

---

The following table is similar to that in Example 1.

This time the Commission to be paid to the sales rep is calculated.

If the Sales are greater than or equal to the Target, the Commission is 10% of Sales.

If the Sales do not reach Target, the Commission is only 5% of Sales.

Example 3

---

This example uses the =AND() within the =IF() function.

A builders merchant gives 10% discount on certain product lines.

The discount is only given on products which are on Special Offer, when the Order Value is £1000 or above.

The =AND() function is used with the =IF() to check that the product is on offer and that the value of the order is above £1000.

Find Formula

FIND

---

About: Finds one text value within another (case-sensitive)


Category: Text
Location: Built-in

What Does It Do?

---

This function looks for a specified letter inside another piece of text.

When the letter is found the position is shown as a number.

If the text contains more than one reference to the letter, the first occurrence is used.

An additional option can be used to start the search at a specific point in the text, thus enabling the search to find duplicate occurrences of the letter.

If the letter is not found in the text, the result #VALUE is shown.

Syntax
=FIND(LetterToLookFor,TextToLookInside,StartPosition)

LetterToLookFor : This needs to be a single character.

TextToLookInside : This is the piece of text to be searched through.

StartPosition : This is optional, it specifies at which point in the text the search should begin.

Formatting

---

No special formatting is needed, the result will be shown as a number.

Match Formula

MATCH Formula

---

About :Looks up values in a reference or array

Category : Lookup

Location : Built-In

Match Formula

What Does It Do ?

---

This function looks for an item in a list and shows its position.
It can be used with text and numbers.

It can look for an exact match or an approximate match.

Syntax
=MATCH(WhatToLookFor,WhereToLook,TypeOfMatch)

The TypeOfMatch either 0, 1 or -1.


Using 0 will look for an exact match. If no match is found the #NA error will be shown.

Using 1 will look for an exact match, or the next lowest number if no exact match exists.
   If there is no match or next lowest number the error #NA is shown.
   The list of values being examined must be sorted for this to work correctly.

Using -1 will look for an exact match, or the next highest number if no exact match exists.
   If there is no exact match or next highest number the error #NA is shown.

   The list must be sorted for this to work properly.

Examples 1

---

Using the 0 option suitable for an exact match.
The Ascending list gives the exact match.
The Descending list gives the exact match.
The Wrong Value list cannot find an exact match, so the #NA is shown.

Example Match -1

=MATCH(F8,F3:F6,0)





Examples 2

---

Using the 1 option suitable for a ascending list to find an exact or next lowest match.
The Ascending list gives the exact match.
The Descending list gives the #NA error.
The Wrong Value list finds the next lowest number..

Example Match_2

 =MATCH(D8,D3:D6,1)






Examples 3

---

Using the -1 option suitable for a descending list to find an exact or next highest match.
The Ascending list gives the #NA error.
The Descending list gives the exact match.
The Wrong Value list finds the next highest number.






 =MATCH(B8,B3:B6,-1)



Examples 4

---

The tables below were used to by a bus company taking booking for bus tours.
They need to allocate a bus with enough seats for the all the passengers.
The list of bus sizes has been entered in a list.
The number of passengers on the tour is then entered.
The =MATCH() function looks down the list to find the bus with enough seats.
If the number of passengers is not an exact match, the next biggest bus will be picked.
After the =MATCH() function has found the bus, the =INDEX() function has been used
to look down the list again and pick out the actual bus size required.

Example Match_4

 =INDEX(D3:D7,MATCH(D9,C3:C7,-1),0))





Examples 5

---

The tables below were used by a school to calculate the exam grades for pupils.
The list of grade breakpoints was entered in a list.
The pupils scores were entered in another list.
The pupils scores are compared against the breakpoints.
If an exact match is not found, the next lowest breakpoint is used.
The =INDEX() function then looks down the Grade list to find the grade.

Example Match_5

 =INDEX(C3:C6,MATCH(F6,B3:B6,1),0)

---

Share This On:

OFFSET function And the Sum formula

OFFSET function And the Sum formula

---

About : To calculate the total of a specific group of months the =OFFSET() function has been used.


Category : Lookup
Location : Built-In

Sometimes it is necessary to base a calculation on a set of cells in different locations.
An example would be when a total is required from certain months of the year, such as the last 3 months in relation to the current date.

One solution would be to retype the calculation each time new data is entered, but this would be time consuming and open to human error.

A better way is to indicate the start and end point of the range to be calculated by using the =OFFSET() function.

The =OFFSET() picks out a cell a certain number of cells away from another cell.
By giving the =OFFSET() the address of the first cell in the range which needs to be totaled, we can then indicate how far away the end cell should be and the =OFFSET() will give us the address of cell which will be the end of the range to be totaled.


The =OFFSET() needs to know three things;

1. A cell address to use as the fixed point from where it should base the offset.
2. How many rows it should look up or down from the starting point.
3. How many columns it should look left or right from the starting point.

=SUM(D3:OFFSET(D3,0,0))
This example uses D3 as the starting point and offsets no rows or columns which
results in the range being summed as D3:D3.

=SUM(D3:OFFSET(D3,0,1))
This example uses D3 as the starting point and offsets 1 col to pick out cell D3 resulting in a the range D3:D3 being summed.

=SUM(D3:OFFSET(D3,0,2))
This example uses D3 as the starting point and offsets 2 cols to pick out cell D3 resulting in a the range D3:D3 being summed.






Using =OFFSET() Twice In A Formula

---

The following examples use =OFFSET() to pick both the start and end of the range which needs to be totaled.

=SUM(OFFSET(E45,0,1):OFFSET(E45,0,1))

The cell D3 has been used as the starting point for both offsets and each has been offset by just 1 column. The result is that just cell E3 is used as the range E3:F3 for the sum function to calculate.

=SUM(OFFSET(D3,0,1):OFFSET(D3,0,2))

The cell D3 has been used as the starting point of both offsets, the first offset is offset by 1 column, the second by 2 columns. The result is the range E3:F3 which is then totaled.

=SUM(OFFSET(D3,0,1):OFFSET(D3,0,3))

The cell D3 has been used as the starting point for both offsets, the first offset is offset by 1 column, the second by 3 columns. The result is the range E3:G3 which is then totaled.

Lets understand with some more examples

---

The following table shows five months of data.
To calculate the total of a specific group of months the =OFFSET() function has been used.
The Start and End dates entered in cells E2 and E3 are used as the offset to produce a range which can be totaled.

 =SUM(OFFSET(C10,0,MONTH($E$2)):OFFSET(C10,0,MONTH($E$3)))




Explanation

---

The following formula represent a breakdown of what the =OFFSET function does.
The  formula displayed below are only dummies, but they will update
as you enter dates into cells E2 and E3.


Formula 1.

---

=SUM( OFFSET(C10,0,MONTH(E2)) : OFFSET(C10,0,MONTH(E3)) )
This is the actual formula entered by the user.




Formula 2.

---

 =SUM(OFFSET(C10,0,MONTH(2)) : OFFSET(C10,0,MONTH(3)))
="=SUM( OFFSET(C10,0,MONTH("&TEXT(MONTH($E$2),"0")&")) : OFFSET(C10,0,MONTH("&TEXT(MONTH($E$3),"0")&")) )"

This shows how the =MONTH function calculates the month number.
In this example the values of the months are 2 and 3 for Feb and Mar.
These values are the 'offsets' relative to cell C10.



Formula 3.

---

=SUM(OFFSET(C10,0,2) : OFFSET(C10,0,33))
="=SUM( OFFSET(C10,0,"&TEXT(MONTH($E$2),"0")&") : OFFSET(C10,0,"&TEXT(MONTH($E$3),"0")&") )"

This shows where the month numbers are used in the =OFFSET function.




Formula 4.

---

=SUM(E10:F10)
=SUBSTITUTE("=SUM( "&@CELL("address",OFFSET(C10,0,MONTH($E$2)))&":"&@CELL("address",OFFSET(C10,0,MONTH($E$3)))&" )","$","")

This shows how the =OFFSET eventually equates to cell addresses to be used as a range for the =SUM function.

---

Share This On:

TRANSPOSE ARRAY FORMULA

TRANSPOSE

---

About : Returns the transpose of an array

Category : Lookup
Location : Built-In

Transpose Formula

=Transpose(A1:C5) + Ctrl+Shift+Enter




Check out the Excel Shortcut Keys

What Does It Do ?

---

This function copies data from a range, and places in it in a new range, turning it so that the data originally in columns is now in rows, and the data originally in rows is in columns.

The transpose range must be the same size as the original range.

The function needs to be entered as an array formula.

To enter an array formula you must first highlight all the cells where the formula is required.
Next type the formula, such as =TRANSPOSE(A1:A5).
Finally press Ctrl+Shift+Enter to confirm it.

If changes need to be made to the formula, the entire array has to be highlighted, the edits can then be made and the Ctrl+Shift+Enter used to confirm it.

Syntax

---

=TRANSPOSE(Range)



Formatting

---

No special formatting is needed.



Check out the Excel Shortcut Keys

Share This on :

HLOOKUP Formula

HLOOKUP Formula

---

About: Looks in the top row of an array and returns the value of the indicated cell



Category: Lookup
Location: Built-in

What Does It Do ?

---

This function scans across the column headings at the top of a table to find a specified item.
When the item is found, it then scans down the column to pick a cell entry.

Syntax :
=HLOOKUP(ItemToFind,RangeToLookIn,RowToPickFrom,SortedOrUnsorted)

The ItemToFind is a single item specified by the user.
The RangeToLookIn is the range of data with the column headings at the top.
The RowToPickFrom is how far down the column the function should look to pick from.
The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.

Formatting

---

No special formatting is needed.

Example 1.

---

This table is used to find a value based on a specified month and name.
The =HLOOKUP() is used to scan across to find the month.
The problem arises when we need to scan down to find the row adjacent to the name.
To solve the problem the =MATCH() function is used.

The =MATCH() looks through the list of names to find the name we require. It then calculates the position of the name in the list. Unfortunately, because the list of names is not as deep as the lookup range, the =MATCH() number is 1 less than we require, so and extra 1 is added to compensate.

The =HLOOKUP() now uses this =MATCH() number to look down the month column and picks out the correct cell entry.

The =HLOOKUP() uses FALSE at the end of the function to indicate to Excel that the column headings are not sorted, even though to us the order of Jan,Feb,Mar is correct.
If they were sorted alphabetically they would have read as Feb,Jan,Mar.

Example 2.

---

This example shows how the =HLOOKUP() is used to pick the cost of a spare part for different makes of cars.

The =HLOOKUP() scans the column headings for the make of car specified in column B.
When the make is found, the =HLOOKUP() then looks down the column to the row specified by the =MATCH() function, which scans the list of spares for the item specified in column C.

The function uses the absolute ranges indicated by the dollar symbol $. This ensures that when the formula is copied to more cells, the ranges for =HLOOKUP() and =MATCH() do not change.

Example 3.

---

In the following example a builders merchant is offering discount on large orders.
The Unit Cost Table holds the cost of 1 unit of Brick, Wood and Glass.
The Discount Table holds the various discounts for different quantities of each product.
The Orders Table is used to enter the orders and calculate the Total.

All the calculations take place in the Orders Table.
The name of the Item is typed in column C.


The Unit Cost of the item is then looked up in the Unit Cost Table.
   The FALSE option has been used at the end of the function to indicate that the product
   names across the top of the Unit Cost Table are not sorted.
   Using the FALSE option forces the function to search for an exact match. If a match is
   not found, the function will produce an error.
 =HLOOKUP(B19,$D$3:$F$4,2,FALSE)



The discount is then looked up in the Discount Table
If the Quantity Ordered matches a value at the top of the Discount Table the =HLOOKUP will
look down the column to find the correct discount.
   The TRUE option has been used at the end of the function to indicate that the values
   across the top of the Discount Table are sorted.
   Using TRUE will allow the function to make an approximate match. If  the Quantity Ordered does
   not match a value at the top of the Discount Table, the next lowest value is used.
   Trying to match an order of 125 will drop down to 100, and the discount from
   the 100 column is used.
   =HLOOKUP(C19,$D$7:$F$10,MATCH(B19,$C$8:$C$10,0)+1,TRUE)

Unit Cost =HLOOKUP(B19,$D$3:$F$4,2,FALSE)

Discount =HLOOKUP(C19,$D$7:$F$10,MATCH(B19,$C$8:$C$10,0)+1,TRUE)

Total        =(C19*D19)-(C19*D19*E19)

---

Share This On:

Choose Function

Choose Function

---

About: Chooses a value from a list of values



Category: Lookup
Location: Built-in

What Does It Do?

---

This function picks from a list of options based upon an Index value given to by the user.

Syntax
 =CHOOSE(UserValue, Item1, Item2, Item3 through to Item29)

Formatting

---

No special formatting is required.

Lets understand with Example

---

The following table was used to calculate the medals for athletes taking part in a race.
The Time for each athlete is entered.
The =RANK() function calculates the finishing position of each athlete.
The =CHOOSE() then allocates the correct medal.
The =IF() has been used to filter out any positions above 3, as this would cause
the error of #VALUE to appear, due to the fact the =CHOOSE() has only three items in it.

---

Share This On:

Vlookup Formula Syntax, Description, Examples - 3

Vlookup Formula

---

About :Vlookup formula Looks in the first column of an array and moves across the row to return the of a cell



Category : Lookup

Location : Built-In

Vlookup Example

 =VLOOKUP(E7,A2:F4,E8,FALSE)





What Does It Do ?

---

This function scans down the row headings at the side of a table to find a specified item.
When the item is found, it then scans across to pick a cell entry

Syntax:
=VLOOKUP(Lookup_Value, Table_Array, Col_Index_Num, [Range_Lookup])

1. The Lookup_Value - ItemToFind is a single item specified by the user.

2. The Table_Array - RangeToLookIn is the range of data with the row headings at the left hand side.

3. The Col_Index_Num ColumnToPickFrom is how far across the table the function should look to pick from.

4. The [Range_Lookup] - Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.

Formatting:

---

No special formatting is needed.

Lets understand with some more examples

Example 1

---

1. This table is used to find a value based on a specified name and month.

2. The =VLOOKUP() is used to scan down to find the name.

3. The problem arises when we need to scan across to find the month column.

4. To solve the problem the =MATCH() function is used.

The =MATCH() looks through the list of names to find the month we require. It then calculates the position of the month in the list. Unfortunately, because the list of months is not as wide as the lookup range, the =MATCH() number is 1 less than we require, so and extra 1 is added to compensate.

The =VLOOKUP() now uses this =MATCH() number to look across the columns and picks out the correct cell entry.

The =VLOOKUP() uses FALSE at the end of the function to indicate to Excel that the row headings are not sorted.

Vlookup Example - 1

 =VLOOKUP(D8,A2:D6,MATCH(D9,B1:D1,0)+1,FALSE)

Example 2

---

1. This example shows how the =VLOOKUP() is used to pick the cost of a spare part for different makes of cars.

2. The =VLOOKUP() scans down row headings in column F for the spare part entered in column C.

3. When the make is found, the =VLOOKUP() then scans across to find the price, using the result of the =MATCH() function to find the position of the make of car.

The functions use the absolute ranges indicated by the dollar symbol . This ensures that when the formula is copied to more cells, the ranges for =VLOOKUP() and =MATCH() do not change.

Vlookup Example - 2

 =VLOOKUP(B9,E3:H7,MATCH(A9,F2:H2)+1,FALSE)

Example 3

---

1. In the following example a builders merchant is offering discount on large orders.

2. The Unit Cost Table holds the cost of 1 unit of Brick, Wood and Glass.

3. The Discount Table holds the various discounts for different quantities of each product.
4. The Orders Table is used to enter the orders and calculate the Total.


All the calculations take place in the Orders Table.
The name of the Item is typed in column C of the Orders Table.

The Unit Cost of the item is then looked up in the Unit Cost Table.
The FALSE option has been used at the end of the function to indicate that the product names down the side of the Unit Cost Table are not sorted.
Using the FALSE option forces the function to search for an exact match. If a match is not found, the function will produce an error.

=VLOOKUP(B15,A3:B5,2,FALSE)



The discount is then looked up in the Discount Table
If the Quantity Ordered matches a value at the side of the Discount Table the =VLOOKUP will look across to find the correct discount.
The TRUE option has been used at the end of the function to indicate that the values down the side of the Discount Table are sorted.
Using TRUE will allow the function to make an approximate match. If the Quantity Ordered does not match a value at the side of the Discount Table, the next lowest value is used.
Trying to match an order of 125 will drop down to 100, and the discount from the 100 row is used.

=VLOOKUP(C15,D3:G5,MATCH(B15,E2:G2,0)+1,TRUE)

Vlookup Example - 3

Formula for :
Unit Cost:
=VLOOKUP(B15,A3:B5,2,FALSE)

Discount:
=VLOOKUP(C15,D3:G5,MATCH(B15,E2:G2,0)+1,TRUE)

Total:
=(C15*D15)-(C15*D15*E15)

---

Share This on :

Excel Formula

You will find here all mostly used excel based formulas. Which are using the MIS persons to prepare and automated their excel reports.