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

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

Excel VBA FollowHyperlink Method

Posted on December 25, 2014March 2, 2016 by Wyman W

This Excel VBA tutorial explains how to use VBA FollowHyperlink Method to open website. VBA FollowHyperlink Method FollowHyperlink is used to tell Excel to open up a browser and go to a specific website, or download a file in specific folder, depending on your "Hyperlink" address. In Excel spreadsheet, there is a similar worksheet function called Hyperlink, but it converts a text to hyperlink but FollowHyperlink function actually "go to" hyperlink. Syntax of FollowHyperlink Method expre...

Excel VBA FormulaR1C1 Property of Range

Posted on December 24, 2014January 8, 2015 by Wyman W

What is FormulaR1C1 Property in Excel VBA? In R1C1, "R" refers to row, while "C" refers to column. Formula R1C1 allows you set or retrieve formula for a cell, using relative row and column. Why do you need FormulaR1C1? For example, you want to set a formula in C2 to sum A2 and B2, you can type Range("C2").formula= "=A2+B2" or Range("C2").Value = "=A2+B2" Instead of hard code "A2+B2", you can also tell Excel to sum the two cells on the left of the formula cell using FormulaR1C1. "A2+B2...

Excel VBA OFFSET Property for Range

Posted on December 23, 2014February 26, 2017 by Wyman W

This Excel VBA tutorial explains how to use Excel VBA OFFSET property, and explain difference between VBA OFFSET Property and worksheet OFFSET Function. Excel VBA OFFSET Property Excel VBA OFFSET property returns the Range object which is number of rows and columns away from a specific range. You can specify to shift up, down, left or right from a cell. For example, if column A contains employee ID, and column B contains employee name, you can find the employee name in B2 by shifting A2 to ...

Excel VBA run hyperlink

Posted on December 18, 2014February 25, 2015 by Wyman W

Solution to trigger hyperlink Use FollowHyperlink function to go to a website. Public Sub hyper() ThisWorkbook.FollowHyperlink Range("C6").Value End Sub For details of the FollowHyperlink, check out the below article Excel VBA function FollowHyperlink

Excel VBA change cell range to array

Posted on December 17, 2014October 24, 2016 by Wyman W

Solution to change cell range to array In the below solution, I have made a Sub to define array (defineArray), a Sub to delete worksheet(delWS), a Sub to move worksheet(moveWS) First, I define an array at Module level, so that all Sub within the Module can access the array. In Sub defineArray(), I put all the items from Range A4 to the last row of column A into wsArray() In Sub delWS(), I put "On Error Resume Next" to prevent error message when Macro tries to delete a wor...