Excel VBA Application Calculation Property

Posted on March 11, 2015March 12, 2015 by Wyman W

This tutorial explains Excel Application Calculation Property and difference among xlCalculationAutomatic, xlCalculationManual, xlCalculationsemiautomatic Excel VBA Application Calculation Property In Excel worksheet, there are three options under Workbook Calculation. The options in Workbook Calculation uses Calculation Property in Excel VBA, which means if you execute the corresponding VBA code, the Excel options change accordingly. Excel Option Description VBA Automatic...

Excel Access MRound custom Function round to nearest multiple

Posted on March 8, 2015March 17, 2019 by Wyman W

This tutorial explains how to create a custom Excel Access MRound Function to simulate Excel spreadsheet MRound Function round to nearest multiple. You may also want to read: VBA Excel Access roundup rounddown Function Excel MRound Function Excel Round Function is used to round to nearest digit, while Excel MRound Function is used to round to nearest multiple. The first time I used MRound Function is to round employee bonus to nearest $100, because we want the bonus amount looks better. ...

Excel MRound Function round to nearest multiple

Posted on March 7, 2015March 7, 2015 by Wyman W

This tutorial explains how to use Excel MRound Function round to nearest multiple Excel MRound Function round to nearest multiple Excel Round Function is used to round to nearest digit, while Excel MRound Function is used to round to nearest multiple. The first time I used MRound Function is to round employee bonus to nearest $100, because we don't want the bonus amount looks better. For example, $11040 is rounded to $11000. If you fail to use Excel MRound Function, navigate to Developer &g...

Excel VBA Custom Function check if worksheet exists

Posted on March 5, 2015December 26, 2016 by Wyman W

This tutorial explains how to use Excel VBA Custom Function to check if worksheet exists, and add new worksheet if worksheet doesn't exist Excel VBA Custom Function check if worksheet exists Sometimes we want to manipulate worksheet in a workbook but we are not sure if the worksheet exists. For example, we want to add a worksheet, if it already exists, skip the adding worksheet code. VBA code - Custom Function to check if worksheet exists There are many Functions on the internet that do the ...

Excel VBA Worksheets.Add Method to add new worksheet

Posted on March 5, 2015April 28, 2019 by Wyman W

This tutorial explains how to add new work sheets using Worksheets.Add Method in Excel VBA, or add new worksheets at the back or before specific worksheet You may also want to read: Excel VBA Worksheets.Copy Method to copy worksheet Excel VBA Worksheets.Add Method to add new worksheet Excel VBA Worksheets.Add Method is to add new worksheet in a workbook. Syntax of Excel VBA Worksheets.Add Method Worksheets.Add([Before],[After],[Count],[Type]) Before Optional. Add new worksheet ...

Access Excel custom split Function to delimit text

Posted on February 22, 2015December 26, 2016 by Wyman W

This tutorial shows how to create a custom Access Excel VBA custom Function to delimit text with the help of VBA "Split" Function instead of "Text to Columns". Delimit text in Excel (non-VBA) In Excel worksheet, there is a function called "Text to Columns" (Data > Text to Columns) to separate a text by delimiter. For example, Cell A1 contains "a,b,c" and after split by comma, A1 contains "a", B1 contains "b" and C1 contains "c". An alternative way to do that is using Left Function with...

Access Excel VBA Check Prime Number using custom Function

Posted on February 14, 2015March 8, 2015 by Wyman W

This tutorial shows a custom Access Excel VBA Function that can check prime number, the Function returns TRUE or FALSE. Access Excel VBA Check Prime Number using custom Function In this tutorial, I will show a custom Access Excel Function that can check prime number, returning TRUE or FALSE. Prime number is an integer that can only be divided by 1 or itself (without remainder). Below are some examples of prime number. 1–20 2 3 5 7 11 13 17 19 23 29 31 37 41 43 47 53 59 ...

VBA Access Excel Remove duplicates in text

Posted on February 10, 2015April 7, 2015 by Wyman W

This tutorial provides a custom Access Excel VBA Function to remove duplicates in text, parameters provide flexibility of case sensitivity and delimiter You may also want to read: Access Excel Scripting Dictionary store unique array item VBA Access Excel remove duplicates in text This tutorial is to help you to remove duplicates in text (in a Cell), but it is not about removing duplicated Cell value in a column. To remove duplicated Cell value in a column, highlight the column, navigate ...

Excel VBA ASC Function to get ASCII value of text

Posted on February 8, 2015April 13, 2015 by Wyman W

This page explains how to use Excel VBA ASCFunction to get ASCII value of a text, and how to check if a text contains an alphabet, number or symbol What is Excel VBA ASC Function? Excel VBA ASC Function returns the ASCII value of the first character in a text. ASCII (American Standard Code for Information Interchange) uses 8-bit code units, an old encoding system which stores mainly numbers, lowercase letters a to z, uppercase letters A to Z, basic punctuation symbols, control codes. Many ol...

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