Excel Access MRound custom Function round to nearest multiple

Posted on March 8, 2015October 4, 2016 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 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. For example, $11040 is rounded to $11000. Access MRound Function As of Acces...

Access VBA DoCmd.RunSQL Method and Error 2342

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

This tutorial explains how to use Access VBA DoCmd.RunSQL Method command and cause of Error 2342 (due to using non-action query such as Select statement) Access VBA DoCmd.RunSQL Method and Error 2342 DoCmd.RunSQL Method runs specified SQL statement. The SQL statement can only be action query (such as create table, modifying data), but not Select statement. Syntax of DoCmd.RunSQL Method DoCmd.RunSQL(SQLStatement, UseTransaction) SQLStatement A string expression that's a valid SQL st...

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

Change font color and format in Visual Basics Editor VBE

Posted on December 6, 2014March 2, 2015 by Wyman W

This tutorial explains how to change font color and formats in Visual Basics Editor (VBE) to make your VBA code more readable. This article is suitable for Excel and Access. Change the VBE font color and format, why? Depending on the color and brightness setting of your monitor, the default font color in VBE (Visual Basic Editor) is not always easy to read. It is possible to change the VBE font color and format, for different types of codes, such as comment, syntax error, identifier, etc. ...

Custom VBA Excel Access Networkdays Function

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

This tutorial explains how to create a custom Excel Access Networkdays Function to simulate Excel Networkdays Function, exclude holiday and weekend Why do we need Access Networkdays Function? As of Access 2013 version, there is no Access Networkdays Function available, the function is only available in Excel all versions. In Excel , Networkdays Function is used to calculate the number of "net working days" between two days, excluding Saturday and Sunday, and include both start date and end ...