Function, Sub, Method, Property Differences

Posted on January 22, 2015April 21, 2015 by Wyman W

This tutorial explains differences among Function, Sub, Method, Property Excel / Access VBA. You may also want to read: Difference between Dim and Set Excel VBA Option Explicit to force declaration of variables Difference between ByRef ByVal Function, Sub, Method, Property Differences In my blog, I have categorized different VBA topics by their properties. If you don't understand Function, Sub, Method, Property Differences, you will find my tips confusing and difficult to use. Thos...

Excel RAND RANDBETWEEN Function to generate random number

Posted on January 12, 2015March 27, 2015 by Wyman W

This Excel tutorial explains how to use Excel RANDBETWEEN Function and Excel RAND Function to generate random number and explain the difference. Excel RAND RANDBETWEEN Function - generate random number Before Excel 2007, Excel uses RAND Function to generate a random number that is greater than zero and smaller than 1. Since Excel 2007, a new Function RANDBETWEEN was added, it is used to generate a random integer between two desired integers. Note that RANDBETWEEN and RAND Functions are...

Excel VBA INSTR Function

Posted on January 11, 2015September 1, 2016 by Wyman W

This Excel tutorial explains how to use Excel INSTR Function for VBA, and explain difference among vbUseCompareOption, vbBinaryCompare and vbTextCompare. Excel VBA INSTR Function Excel INSTR function is used to search a substring (part of the string) within a string and return the position of the first occurrence. For example, in the string "FinanceDepartment", the substring "Department" can be found at 8th position within "FinanceDepartment", the function will return number "8". If we...

Excel IFERROR Function

Posted on January 7, 2015December 16, 2015 by Wyman W

This Excel tutorial explains how to use Excel IFERROR function for worksheet. IFERROR can be used with vlookup to capture ISNA error. You may also want to read: Check Excel #N/A using ISNA Function What is Excel IFERROR Function? Excel IFERROR Function is an error handling function, if an "Error" is found, then a desired value is returned. This is a new function introduced in Excel 2007, which means Excel 2003 and prior versions cannot be used. Syntax of IFERROR IFERROR(value,value_if...

Excel SUMIFS Function

Posted on January 7, 2015 by Wyman W

What is Excel SUMIFS Function? Excel SUMIF Function sum up an array (a range of values) if condition is met. Excel SUMIFS function is an advanced version of SUMIF, allow you to set multiple conditions. If you only need to apply one criteria, please read the use of SUMIF Excel SUMIF Function Syntax of Excel SUMIFS Function SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] ) sum_range The actual cells to add, if you wa...

Excel SUMIF Function

Posted on January 7, 2015February 28, 2015 by Wyman W

What is Excel SUMIF Function? Excel SUMIF Function sum up an array (a range of values) if condition is met. Note that you can set only one condition, for multiple conditions you need the SUMIFS Function (with "s" in suffix) Read the below article about SUMIFS Function Excel SUMIFS Function Syntax of Excel SUMIF Function SUMIF( range, criteria, [sum_range] ) range The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or refe...

Excel VBA Intersect Method

Posted on January 2, 2015January 22, 2015 by Wyman W

Excel VBA Intersect Method for Range In Excel VBA, Intersect Method is used to return the intersection Range (the common area in the above picture). Syntax of Intersect Method expression .Intersect(Range1, Range2, ...) The intersection Range must be at least 2, in order to find the intersection area. If intersection area exists, Intersection Method returns a Range If intersection area does not exist, Intersection Method causes a Runtime Error, which can be avoided using "Is N...

Excel Networkdays function to exclude weekend

Posted on December 26, 2014March 1, 2015 by Wyman W

This tutorial explains how to use Excel Networkdays Function to exclude weekend and holiday. Excel Networkdays function to exclude weekend and holiday Excel Networkdays Function literally means "net working days", which calculates the number of days between two specific days, excluding weekend (Saturday and Sunday) and holiday. Both start date and end date inclusive. For example, from Jan 1st, 2015 (Thu) to Jan 5th, 2015 (Monday), total number of Networkdays is three. The function counts...

Excel Round Function

Posted on December 26, 2014March 7, 2015 by Wyman W

This tutorial explains how to use Excel Round Function Excel Round Function There are several kinds of Rounding in Excel - Round, Round up, Round down, Mround, plus Banker's Rounding in VBA. Excel Round Function is the most commonly used round, it uses "round half to up" logic, round up if the decimal part >=0.5, round down if decimal part <0.5 Syntax of Excel Round Function ROUND( number, digits ) number The number you want to apply rounding digits An integer that ...

Excel VBA Round Function and custom Round Function

Posted on December 26, 2014March 7, 2015 by Wyman W

This tutorial explains difference between Excel VBA Round Function and Excel worksheet Round Function, and create a custom Excel Round Function. Excel VBA Round Function and custom Round Function Excel VBA Round Function is completely different from Excel worksheet Round Function. For Excel spreadsheet Round Function, if decimal part is >=0.5, the integer rounds up, otherwise the integer rounds down. For Excel VBA Round Function, as well as Access VBA, the Round Function uses "Round t...