Excel VBA Option Explicit to force declaration of variables

Posted on February 4, 2015January 30, 2017 by Wyman W

This tutorial explains how to use Option Explicit to force declaration of variables in Excel VBA (enable Require Variable Declaration) You may also want to read: Difference between Dim and Set Difference between ByRef ByVal Function, Sub, Method, Property Differences What is VBA Option Explicit? Option Explicit is a keyword declared on top of the Visual Basic Editor in the coding Windows outside any Procedures. In the presence of Option Explicit keyword, all variables must be de...

Access Excel VBA MID Function to extract substring

Posted on February 4, 2015September 27, 2016 by Wyman W

This tutorial explains how to use MID Function to extract substring from a string in Excel / Access VBA Excel / Access VBA MID Function MID Function is quite similar to LEFT Function and RIGHT Function, where RIGHT Function extracts a substring on the right, LEFT Function extracts a substring on the left, while MID extracts a substring in the middle of the string. MID Function is commonly used with INSTR Function, which returns the position of the string (In Excel worksheet, use Search Func...

Excel MID Function to extract substring

Posted on February 4, 2015December 25, 2016 by Wyman W

This Excel tutorial explains how to use Excel MID Function to extract substring from a string, and use with FIND and SEARCH and LEN Function. You may also want to read: Extract text in brackets Remove text in brackets Excel MID Function MID Function is quite similar to LEFT Function and RIGHT Function, where RIGHT Function extracts a substring on the right, LEFT Function extracts a substring on the left, while MID extracts a substring in the middle of the string. MID Function is co...

Excel Extract Time from Date Time or Extract Date

Posted on February 4, 2015December 7, 2015 by Wyman W

This Excel tutorial explains how to extract time from Date Time (timestamps) or extract date from Date Time in Excel and how to convert date time to time serial (decimal number). You may also want to read: Excel Access VBA extract percentage from text Access Excel VBA extract number from text or extract alphabet Access Excel extract file name from file path Excel Display Time from Date Time Excel Custom Format Excel Extract Time from Date Time / extract date from Date Time Sometim...

Excel VBA Workbooks.Add Method to create new workbook

Posted on February 4, 2015March 5, 2015 by Wyman W

This page explains Excel VBA Workbooks.Add Method, illustrate how to create new workbook in Excel VBA or move data from one workbook to a new workbook What does Excel VBA Workbooks.Add Method do? Excel VBA Workbooks.Add Method is used to create new workbook. Suppose you have a workbook called workbook1, inside workbook1 you need to create new workbook and manipulate it, then you need to insert the Workbooks.Add Method in workbook1. After you have created the workbook, the workbook becomes ac...

VBA Excel Hide Blank Rows Automatically

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

VBA Excel Hide Blank Rows Automatically This is a question originally posted in Microsoft Community about Excel hide blank rows. https://answers.microsoft.com/en-us/office/forum/office_2007-excel/automatically-unhide-a-row-on-sheet-2-when-data-is/b1c85ae4-d0a8-4339-86c4-4ee68471ff73 The user has a worksheet called Sheet1 that is dynamically changed. Another worksheet, Sheet2, contains a range of formula that references to Sheet1. If the corresponding Sheet1 data is blank, Sheet2 Cell show...

Excel DateValue Function to convert text to Date

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

What does Excel DateValue Function do? Excel DateValue function is used to convert a text to Date Serial, a numeric value that represents the Date. Why do you need Excel DateValue Function? Date is being treated specially in Excel. The format you see in Excel worksheet such as dd/mm/yyyy is only a displayed formatting, the underlying value of a Date is a numeric value. 1/1/1900 is considered as the first date in Excel, the numeric value of it is 1, and add one to each day onwards. Everytim...

Excel Date Function

Posted on January 27, 2015March 14, 2016 by Wyman W

This Excel tutorial explains how to use Excel Date Function. You may also want to read: Excel first date of month and last date of month Excel Date Function Excel Date function is used to transform year, month, day into Date Serial, a numeric value that represents the Date. Why do you need Excel Date Function? Date is being treated specially in Excel. The format you see in Excel worksheet such as dd/mm/yyyy is only a displayed formatting, the underlying value of a Date is a numeric v...

Excel Use Date in IF Condition

Posted on January 27, 2015February 27, 2015 by Wyman W

Excel Use Date in IF Condition (worksheet solution) If you use a Text as a condition in IF, you can simply use double quote on the text such as "Text", but to use Date in IF condition, you cannot simply use double quote on a date. Lets recap how we apply condition on different data type. To apply condition on Number: =IF(A1>5,"large number","small number") To apply condition on Text: =IF(A1="John","My friend","Stranger") What about Date? Should we just use double quote as we ...

Access Excel Function HKID Check Digit (last digit)

Posted on January 27, 2015October 13, 2015 by Wyman W

This Excel tutorial explains the algorithm of HKID Check Digit (last digit) and provide a custom Access Excel VBA Function to verify the Check Digit. (香港身份証最後號碼) You may also want to read: Access Excel Generate random HKID Hong Kong ID card number HKID Check Digit Hong Kong ID card number has a last digit with bracket called "Check Digit". Check Digit is a number to verify if the previous number and alphabet are correct. By understanding the algorithm of calculating Check Digit, you ...