Faysal Easy Excel

This function is available in: MS Excel 2007 and above version, MS Excel 2011 for Mac and above version, Android, iPhone, Online etc. There are 7 popular lookup functions in MS Excel. One of them is Choose(). This function returns a value from a list (maximum 254 items in a list) whose nth number has given. This function requires a position number and a list of range. Then the function will return the value from list of range by using the mentioned position number. Functions Structure: The structure of this function is: =CHOOSE(index_num,value1,value2,value3,......,value254) or =CHOOSE(index_num,list_range) There are 2 main arguments or parts in this function. These are: (a) index_num: Need to mention a position number 1 to 254 to return that position's value. (b) value1: This is the 1st item name of list. And this could be maximum value254. You can use a range as list_range also. Examples: Assume that, below is sample table B3:C8 and you are reque...

HLookup function (Horizontal Lookup) is commonly used in MS Excel to get values from a data table by matching a value in lookup row and return the value from the matched column. The match type can be define as Exactly match or Approximate match. It works like an English T shape. It matches a value in a row and returned value from another row of same column where value matched. Function Structure: The structure of this function is: =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]) There are 4 arguments or parameters in this function. The first 3 arguments or parameters are essential. That means if you want to use HLookup function, then you must mention the first 3 parameters. And the last parameter is optional. By default the last parameter is set to True (Approximate match). But for a better result you need to set the 4th parameter. All of these parameters are briefed here: lookup_value: Need to mention which value to lookup. table_array: Range of data tab...

There are 4 different ways in Module to call a procedure. To show it how it works, a Module taken from Insert | Module as Module1 and again taken from the same way Module2 in VBA Environment. Way 1 - Written in Module1: Simple procedure calling in a Module: Sub new_player () Range( "A1" ).Value = "Balak" End Sub Way 2 - Written in Module1: Call a procedure withing another procedure in the same Module: Sub player_come_coach ()    new_player End Sub Way 3 - Written in Module1: Call a procedure withing another procedure but new calling technique in the same Module: Sub coachRetired ()    Call new_player    Range( "H1" ).Value = "Was a Captain of Germany Football Team" End Sub Note: You can test your procedure in Immediate window. Image 1: Calling a procedure within a same Module (Module1) Way 4 - Written in Module 2: Call a procedure withing another procedure from different Module: Sub coachR...

There are 2 types of Procedures in Visual Basic. These are, Public and Private. Image 1: Types of Procedure Public Procedure: Public procedure is accessible from any module of the same workbook. By default procedure is Public Procedure in Excel VBA. If you run the macro dialog box, then you will see the Macro list which is recorded or written as Public procedure. Image 2: Two different types of Public Procedure Example Public Procedure can accessible from any other module or any others opened workbook. If we write with a Sub code then by default it counted as Public Procedure. Public Procedure always displayed in Macro list box: Image 3: Public Procedure always shows in Macro list Private Procedure: Private procedure is only accessible with the same module where the private sub procedures present in the same module of the same workbook. You will see the Private procedures only while you select the Personal workbook from macro dialog box. Image 4: Private Procedure Examp...

The Lookup function is used for finding a specific result from a single row or a single column, based on a lookup_value with same range of single row or single column. It does not allow 2 or more rows or columns range to find the lookup_value or returned value like others lookup function VLookup, HLookup do. Lookup Function: The structure of this function is: =LOOKUP( lookup_value , lookup_vector , result_vector ) lookup_value:   The value which you want to look up in a specific row or column range. lookup_vector: It is a single row or single column range that contains the values to be looked up. These values must be in ascending order. That means before using this Lookup you must sort the lookup_vector range as Ascending order. Descending order will return the incorrect value. result_vector: It is the single row or single column range from which you want to return the value. The range size need to keep same as lookup_vector. Example: Table B4:D7 is a poin...

In a macro there will be lot of codes and a lot of sections. It is very much possible for you to develop or modify it in future. To develop the specific code you need to find and go to that line. If you use a Macro Comment, you can easily understand why this codes are written for. And easily find the target code with the help of comments and edit or develop it. Macro Comments and simple code written in Excel VBA. It is not for execute with others Excel VBA Code. It is just for taking a note, that what is the code for. It is becomes more useful while your codes increases more. You can use multiple comments. It is very easy to Deleting or Writing. Example: Sub comments_writing1 ()    'This is a test of displaying 2 words the cell A1 and cell B1 in active sheet    Range( "A1" ).Value = "Messi"    Range( "B1" ).Value = "Ronaldo" End Sub After running the above program, the active sheet will display 2 names like below: Ima...

A VBA Procedure is also define as Macro, is a set of codes which makes MS Excel to perform task within very short time as an action. The length of this code is depends on your requirement. Example: Image 1: VBA Programming example Here in this code: hello_1() : is the sub procedure. msgbox : is the inner task written by programmer, depends on his requirement. End Sub : is the end procedure. The result is: Image 2: VBA Message Working Process: Run: To start the working process of VBA Procedure, it needs to run. Input: Under VBA Procedure if any "user input" related code written, then it would ask the user to enter data. Calculate: hen it will work on it automatically as per code written in VBA Code window. Result: Finally it shows the result as the programmer desired. Image 3: Working process of VBA Types of VBA Procedure: 1. Sub Procedure(): All codes are written under a Sub Procedure. it is written into Module in Code window an...