Excel VBA formulas for Spreadsheet

© sharath2630@gmail.com, 2016. All rights reserved.

Edition 1

Contents

Introduction

Exercise 1: IIF Function

Exercise 2: Custom Address Function

Exercise 3: Standard IF Function

Exercise 4: Specific IF Function

Exercise 5: IF,AND Function

Exercise 6: Multi OR Function

Exercise 7: Simple Multiplication

Exercise 8: Condensing HLOOKUP function

Exercise 9: Simplifying excel sheet Match function

Exercise 10: Rlookup (Right lookup) Function

Exercise 11: Simplified Vlookup formula version

Exercise 12: Choose Function

Exercise 13: Excel Run Time Version Function

Exercise 14: IsDate Function

Exercise 15: Auto recalculate

Exercise 16: Convert VBA Function to Excel Function

Exercise 17: National festive days Function

Exercise 18: Static Commission Function

Exercise 19: A Custom Function

Exercise 20: View the Formula of cell in another Cell

Exercise 21: Function with Case Insensitive

Exercise 22: Columnwidth function

Exercise 23: Rowheight function

Exercise 24: Monthname Function

Exercise 25: Space Function

Conclusion

1. Introduction

This book is written to provide the basics of the Excel VBA Formula for excel spread sheet, this book VBA Formulas offer ease and convenience at your understanding

The Book Intends for Excel VBA User’s & beginners.

The Book Covers Way to work with VBA Formulas for spreadsheet

Influence the Basic Understanding of Formulas & VBA Formulas to Strike Similarity in spreadsheet management.

The book is recommended to all the potential Learners who look for help in understanding the Excel VBA Formula’s fundamentals and will get insight of an appropriate Way to do so. All functions are 2016 Excel IDE Designed

Exercise 1: IIF Function

1. Create a UDF (User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Altl + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Will Create A new UDF (User Defined Function) with name imedife,we Can use this As we Required. now type in Excel the function = imedife ,the UDF shows in small letter to differentiate itself from Standard Excel Formula

This Code lines will create function imedife ()

In Cell B1 Type

= imedife (A1)

2. Use Excel Formulas (Lookup,Vlookup)

Syntax

IF(logical_test, value_if_true, [value_if_false])

=IF(A1<18,"Minor","Major")

Exercise 2: Custom Address Function

1. Create a UDF (User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Altl + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Will Create A new UDF (User Defined Function) with name Emplycity,we Can use this As we Required. now type in Excel the function =Emplycity ,the UDF shows in small letter to differentiate itself from Standard Excel Formula

This Code lines will create function Emplycity ()

In Cell C1 Type

= Emplycity (A1)

2. Use Excel Formulas (Lookup,Vlookup) Syntax

LOOKUP(lookup_value, array)

=LOOKUP(6983,A1:C10) ,

Excel will perform search to find out the city of the employeeID 6983

Syntax

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

=VLOOKUP(6983,A1:C10,3,TRUE)

Excel will perform search to find out the city of the employeeID 6983

Exercise 3: Standard IF Function

1. Create a UDF (User Defined Function)

Use the If Then statement in Excel VBA code lines function to act if a specific condition is met. To create this Function, execute the following steps

Open Excel VBA (Altl + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Will Create A new UDF (User Defined Function) with name grade(S),we Can use this As we Required. type in Excel the function = grade(A1) ,the UDF Comes up in small letter’s to differentiate itself from Standard Excel Formula

This Code lines will create function grade ()

In Cell B1 Type

= grade(A1)

2. Use Excel Formulas (IF) Syntax

IF(logical_test, value_if_true, [value_if_false])

=IF(B1>79,"Excellent",IF(B1>49,"Good",IF(B1>19,"Average",IF(B1>1,"Worst","N/a"))))

Exercise 4: Specific IF Function

1. Create a UDF (User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Altl + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

Use the If Then statement in Excel VBA code lines function to act if any of 2 specific condition is met. Insert the below code in a New Module

This Will Create A new UDF (User Defined Function) with name Lans(Lval) we Can use this As we Required. type in Excel the function = Lans(Lval) ,the UDF Comesup in small letter’s to differentiate itself from “tandard Excel Formula

This Code lines will create function Lans()

In Cell B1 Type

= Lans(A1)

2. Use Excel Formulas (IF) Syntax

IF(logical_test, value_if_true, [value_if_false])

=IF(A1=100,"Great", IF(A1=99,"Great","ok") )

OR(logical1, [logical2], ..

=IF(OR(A1=100,A1=99),"Great","ok")

Exercise 5: IF,AND Function

1. Create a UDF (User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Altl + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

Use the If Then statement in Excel VBA code lines function to act if the both 2 specific condition is met. Insert the below code in a New Module.

This Will Create A new UDF (User Defined Function) with name Lhans(Lval, Lvxc) we Can use this As we Required. type in Excel the function As =Lhans(Lval, Lvxc), the UDF Comes up in small letter’s to differentiate itself from “tandard Excel Formula

This Code lines will create function Lhans()

In Cell C1 Type

= Lhans(A1,B1)

2. Use Excel Formulas (IF,AND)

Syntax

IF(logical_test, value_if_true, [value_if_false])

AND(logical1, [logical2], ...)

=IF(AND(A1=100,B1=99),"Great","ok")

Exercise 6: Multi OR Function

1. Create a UDF (User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Altl + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

Use the If Then statement in Excel VBA code lines function to act if the both 2 specific condition is met. Insert the below code in a New Module.

This Will Create A new UDF (User Defined Function) with name Lhazs(Lval, Lvxc, Lvzl) we Can use this As we Required. type in Excel the function As = Lhazs(Lval, Lvxc, Lvzl)), the UDF Comes up in small letter’s to differentiate itself from “tandard Excel Formula,if any one condition met it turns TRUE.

This Code lines will create function Lhazs()

In Cell D1 Type

=Lhazs(A1,B1,C1)

2. Use Excel Formulas (XOR)

Syntax

XOR(logical1, [logical2],…)

=XOR(A1>30,B1>50,C1=20)

Exercise 7:Simple Multiplication

To create this Function, execute the following steps

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Code lines will create function Multiply()

In Cell C1 Type

= Multiply(A1,B1)

2. Use Excel Formulas

=A1*B1

Exercise 8: Condensing HLOOKUP function

To create this Function, execute the following steps

Open Excel VBA (Altl + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Code lines will create function hlkup() In Cell C2 Type

= hlkup(product,Range("produ"), 3, 0)

Syntax

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

=HLOOKUP(C25,B17:D22,3,FALSE)

Exercise 9. Simplifying excel sheet Match function

1. Create a UDF (User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Altl + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Code lines will create function Matched ()

In Cell E2 Type

= Matched (product, Range("stk"), 0)

Syntax

2. Use Excel Formulas (XOR)

MATCH(lookup_value, lookup_array, [match_type])

=MATCH(E17,$C$17:$C$22,0)

Exercise 10: Create Rlookup (Right lookup) Function

1. Create a UDF Rlookupname (User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Altl + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

Define Name With Below Range

score :A2:C8

screval :C2:C8

Scorval:A2:A8

1. Open Excel VBA (Altl + F11)

Insert the below code in a New Module

This Code lines will create function Rlookupname ()

In Cell D1 Type

= Rlookupname(88)

It will show result As 4

2. Use Excel Formulas (Index,Match)

Syntax

INDEX(array, row_num, [column_num])

MATCH(lookup_value, lookup_array, [match_type])

=INDEX(A2:C8,(MATCH(G2,C2:C8,0)),2)

Exercise 11 : Create simplified Vlookup version

1. Create a UDF Vlkup( User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Altl + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

Define Name With Below Range score :A2:C8

Syntax

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

=VLOOKUP(8,score,3,FALSE)

Exercise 12: Choose Function

1. Create a UDF DayName( (User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

The Code Will Create A new UDF (User Defined Function) with name DayName,With this now type in Excel the function = DayName ,the UDF shows in small letter to differentiate itself from Standard Excel Formula

=DayName(11/4) returns day name in current year

=DayName(11/5/2015) returns day name in the year 2015

2. Use Excel Formulas (Choose)

Syntax

CHOOSE(index_num, value1, [value2], ...)

= CHOOSE(3,"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

Excel will perform search for 3rd And the Day Results As Tuesday

In File – Otions –Advanced – General - Enter Custom list As Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday

Upon Entering A day Drag the Cell bottom for Next day to follow

Exercise 13: Excel RT Version Function

1. Create a UDF (User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Will Create A new UDF (User Defined Function) with name xlVers(),we Can use this As we Required. type in Excel the function = xlVers(),the UDF Comes up in small letter’s to differentiate itself from Standard Excel Formula

In Cell A1 Type

= xlVers()

It will show result As 16

Exercise 14: IsDate Function

1. Create a UDF (User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Will Create A new UDF (User Defined Function) with name isDat() we Can use this As we Required. type in Excel the function = isDat() ,the UDF Comes up in small letter’s to differentiate itself from “tandard Excel Formula.

In Cell B1 Type

= isDat (A1)

It will show result As TRUE

Exercise 15: Auto recalculate

To create this Function, execute the following steps

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

Use the Application.Volatile True statement in Excel VBA function code lines to force the formula to recalculate, Insert the below code in a New Module.

In This the code line Application Volatile True forces the function to recalculate every time the cell or range value changes, the new UDF (User Defined Function) with name Qtravg recalculate’s every time.

In Cell F2 Type

= Qtravg()

2. Use Excel sheet options

Press Ctrl + ALT + F9 in sheet

Press F9 to recalculate entire spreadsheet

Exercise 16: Convert VBA Function to Excel Function

Create a UDF (User Defined Function)

Use Defined Name

To create this Function, execute the following steps

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

Use the FIX Function of Excel VBA code lines function to use it in Excel sheet ,Insert the below code in a New Module.

This Will Create A new UDF (User Defined Function) with name Stdyr() we Can use this As we Required. type in Excel the function As = Stdyr(), the UDF Comes up in the year as 2016 which is constant figure

Keep the Cell Format as General In Spreadsheet

This Code lines will create function Stdyr()

In Cell A1 Type

= Stdyr(A1)

2. Defined Name

Click on Formulas Defined Formulas Define Name

Define Name for Any cell With Value “2016 “, Type in any cell As =stdyr

Exercise 17: National festive days Function

1. Create a UDF (User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

= FestivDay( (A1)

This Will Create A new UDF (User Defined Function) with name FestivDay( (A1)we Can use this As we Required. type in Excel the function As = FestivDay( A1), the UDF Comes up in small letter’s to differentiate itself from “tandard Excel Formula,if the day fall’s to National

This Code lines will create function FestivDay()

In Cell B1 Type

= FestivDay(A1)

2. Use Excel Formulas (NOT,OR)

Syntax

=NOT(OR(A1>10,B1>10,C1>10))

Exercise 18: Static Commission Function

To create this Function, execute the following steps

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Code lines will create function Commission ()

Use Excel Spreadsheet

Click on sheet and insert the function

Exercise 19: A Custom Function

1. Create a UDF (User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Code lines will create function Brickwall()

In Cell B1 Type

= Brickwall(A1)

Syntax

2. Use Excel Formulas (Bricks *2.25)

Exercise 20: View the Formula of cell in another Cell

1. Create a UDF Formuladisplay (User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

Insert the below code in a New Module

This Code lines will create function Formuladisplay () , In Cell D1 Type

= Hedqtrs( (C1)

Syntax FORMULATEXT(reference)

2. Use Excel Formulas (FORMULATEXT)

=FORMULATEXT(C1)

Exercise 21: Function with Case Insensitive

Create a UDF Hedqtrs( User Defined Function)

To create this Function, execute the following steps

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Code lines will create function Hedqtrs() , In Cell B1 Type

= Hedqtrs( (A1)

2. Use Excel Formulas (IF,PROPER)

Syntax

IF(logical_test, value_if_true, [value_if_false])

PROPER(text)

Use (IF,PROPER) function together, In Cell B1 Type

=IF(PROPER(A1)="City","NEWYORK"," ")

Exercise 22: Column Width function

To create this Function, execute the following steps

1. Create a UDF Columnwidth() ( User Defined Function)

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Code lines will create function Columnwidth() In Cell B1 Type

=Columnwidth(A1)

In Excel Sheet

click on Home Cells Format Column width to know the size of the Column

Exercise 23: Rowheight() function

To create this Function, execute the following steps

1. Create a UDF Rowheight() ( User Defined Function)

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Code lines will create function Rowheight()

In Cell B1 Type

= Rowheight(A1)

In Excel Sheet

click on Home Cells Format Row height to know the size of the Column

Exercise 24: Monthname Function

To create this Function, execute the following steps

1. Create a UDF Monthname() ( User Defined Function)

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Code lines will create function Monthname() In Cell B1  Type

=Monthname(A1)

2. Use Excel Formulas (CHOOSE)

Syntax

CHOOSE(index_num, value1, [value2], ...)

=CHOOSE(A1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13)

Exercise 25: Space Function

To create this Function, execute the following steps

Open Excel VBA (Alt + F11)

Insert a New Module

In the Project Explorer, double click on the Module

Add the following code line:

This Code lines will create function Spce() In Cell B1 Type

= Spce(3)

In D1 As =A1&B1&C1

2. Use Keyboard space button

Conclusion

A User Defined function that is located in a code module in a workbook will go along with the Workbook As long as you share. Likewise the function remains with the workbook and will always be available to share.

If your workbook refers UDF you can save the Macro code –Module in Basic file (.bas) format Mail the. bas file to someone with whom file you want the UDF to be available else you can share the file also.

This book was distributed courtesy of:

For your own Unlimited Reading and FREE eBooks today, visit:
http://www.free-ebooks.net/

Share this eBook with anyone and everyone automatically by selecting any of the options below:

Share on Facebook

Share on Twitter    

Share on LinkedIn

To show your appreciation to the author and help others have wonderful reading experiences and find helpful information too, we'd be very grateful if you'd kindly
post your comments for this book here.

COPYRIGHT INFORMATION

Free-eBooks.net respects the intellectual property of others. When a book's copyright owner submits their work to Free-eBooks.net, they are granting us permission to distribute such material. Unless otherwise stated in this book, this permission is not passed onto others. As such, redistributing this book without the copyright owner's permission can constitute copyright infringement. If you believe that your work has been used in a manner that constitutes copyright infringement, please follow our Notice and Procedure for Making Claims of Copyright Infringement as seen in our Terms of Service here:

http://www.free-ebooks.net/tos.html