Excel Record Macro

Posted on January 5, 2015January 11, 2015 by Wyman W

What is Macro and what is Excel Record Macro function? Macro is a set of actions that is intended to be repeating all over again. Macro is not a word just for Excel, you can find many other software help users record a set of actions for Windows or even for specific games. The first Macro I used was EZ Macro about 15 years ago, which helps me repeat a set of actions in an online game endlessly in order to get my skill points up. Today I still use keyboard and mouse that can record Macro...

Excel VBA Union Method

Posted on January 5, 2015February 9, 2015 by Wyman W

Excel VBA Union Method for Range Union Method returns a Range which is combined from two or more Range. In the above diagram, Union A and B returns area A plus B, where the intersection area only counts once. The first time I used Union was that I needed to do several formatting for several columnS but they were not side by side. For example, I need to change color for column A, column C and column Z, the best way to do is to combine all three columns as a new Range, and then apply c...

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...

VBA Excel Worksheet.Change Event

Posted on January 1, 2015July 19, 2016 by Wyman W

This Excel VBA tutorial explains how to use Worksheet.Change Event. You may also want to read: Excel VBA get value before Worksheet_Change event Excel automatically refresh pivot table VBA Excel Worksheet_Change Event Excel predefines some popular actions that you would do on different Objects (worksheet, workbook, button, etc), those actions are called Event. For example, activating a worksheet is an Event, closing a workbook is an Event, clicking on a button is an event. Each Objec...

Excel VBA get value before Worksheet_Change event

Posted on January 1, 2015April 10, 2016 by Wyman W

This Excel VBA tutorial explains how to get value before Worksheet_Change event. Problem with getting value before Worksheet_Change event When we use Worksheet_Change event, the event is triggered after we edit a Cell (even no value is changed). For example, when A1 value is 10, we double click range A1 to change value to 20. As we press enter, Worksheet_Change event is triggered. As you can see, the old value 10 is not stored anywhere, but sometimes we do need the old value to do calculation....

Excel Find records of Minimum Date or Maximum Date

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

This tutorial explains how to find minimum date and maximum date of each record in Excel by sorting and remove duplicate. You can find more tutorials at http://Access-Excel.tips Find records of Minimum Date or Maximum Date, Why? Find records of minimum date or maximum date is an essential skills in employee records. Some have data use an unique key such as employee ID plus a date as the unique record. For example, when Peter (Employee ID 001) was hired on 1/1/2014 and then terminated on 1/5...

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...

Access Round Function and custom Round Function

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

This tutorial explains how to use Access Round Function (round to even logic) and create a custom Round Function to simulate Excel Round Function. Access Round Function - Round to even logic Access Round Function is completely different from Excel Round Function. For Excel Round Function, if decimal part is >=0.5, the integer rounds up, otherwise the integer rounds down. For Access Round Function, as well as Access VBA, Excel VBA, the Round Function uses "Round to even" logic. Integ...