Excel VBA delimit Cell value into rows

Posted on May 27, 2016August 5, 2016 by Wyman W

This Excel tutorial explains how to delimit cell value into rows using VBA and non VBA. You may also want to read: Convert cell contents into table Access Excel custom split Function to delimit text Excel VBA delimit Cell value into rows To demonstrate how to delimit Cell value into rows, I use email address as an example. One day I tried to copy a list of email addresses from Outlook into Excel. When I paste to Excel 2013, all email addresses are pasted to a singl...

Access VBA check if Query is empty

Posted on May 26, 2016May 26, 2016 by Wyman W

This Access tutorial explains how to check if table or Query is empty in Access VBA. You may also want to read: Microsoft Access VBA Export all Query to Text File Access VBA check if Query is empty In Access VBA, there are SQL related Functions that simulate SQL. Access DCOUNT Function is one to simulate SQL COUNT. To count the number of records in a Query, we can use * in the first argument of DCOUNT Function. For example, the below VBA counts the number of records in Query1 DC...

Excel get formula of a cell using FormulaText Function and VBA

Posted on May 19, 2016 by Wyman W

This Excel tutorial explains how to show / get formula of a cell using FormulaText Function and VBA. Search formula of a Cell Assume that you have the below spreadsheet and you don't know whether any cells contain a formula. Press CTRL + F to open Find and Replace dialog In the Find what text box, enter equal sign = , then press Find Next button Note that if a cell contains equal sign in Cell Text, it will also be searched. Show formula of a Cell Navigate to Formul...

Excel T.TEST Function to perform Student’s T Test

Posted on May 19, 2016 by Wyman W

This Excel tutorial explains how to use T.TEST Function to perform Independent Sample T Test and Paired-samples T Test. What is Independent T Test In statistical inference, we are interested to know whether a small sample comes from a population. To inference using sample mean, when the population standard deviation and population mean are known, we can use Z test to interference the population mean from sample mean. In reality, we do not have data of the whole population. Without the popul...

Access SQL SELECT DISTINCT Clause

Posted on May 16, 2016 by Wyman W

This Microsoft Access tutorial explains how to use SELECT DISTINCT Clause in Access Query. Microsoft Access SQL SELECT DISTINCT Clause SELECT DISTINCT can be used in Microsoft Access SQL to select unique values in a field. Note that you cannot use this keyword in Query Design View, you can only use it directly in SQL View. The outcome of DISTINCT is exactly the same as GROUP BY if you only try to find the unique values, but GROUP BY can be used together with other Aggregate Functions such a...

Excel VBA Worksheet.Select Method to select worksheets

Posted on May 15, 2016 by Wyman W

This Excel VBA tutorial explains how to use Worksheet.Select Method to select a single worksheet or multiple worksheets. Select worksheets in Excel When you click on a worksheet tab, the worksheet is highlighted. To select multiple worksheets, you can hold down Ctrl and then left click the mouse on each worksheet tab. To select all worksheets at once, right click on one of the sheet, and then click on Select All Sheets One practical use of selecting multiple worksheets is to print sel...

Excel VBA sort worksheet tab order

Posted on May 13, 2016June 6, 2017 by Wyman W

This Excel VBA tutorial explains how to sort worksheet tab in alphabetic order (ascending order or descending order). You may also want to read: Excel Range Sort Method to sort data Use VBA Excel Function to sort data in ascending order Access Excel assign number for custom sorting sequence Sort worksheet tab by name When you have a lot of worksheets in a workbook, you may want to sort them in alphabetic order for easy searching. As a behavior of Excel sorting, when an arr...

Excel VBA consolidate multiple workbooks into one workbook

Posted on May 12, 2016May 18, 2016 by Wyman W

This Excel VBA tutorial explains how to consolidate multiple workbooks into one workbook. You may also want to read: Excel VBA Consolidate worksheets into one worksheet Excel VBA combine worksheets columns into one worksheet Consolidate multiple workbooks into one workbook Assume that you have a lot of workbooks, each workbook contains multiple worksheets. You may want to copy all worksheets of each workbook into a master workbook. For example, I have a folder called "sample" under...

Excel IRR Function to calculate Internal Rate of Return

Posted on May 10, 2016 by Wyman W

This Excel tutorial explains how to use Excel IRR Function to calculate Internal Rate of Return. You may also want to read Excel NPV Function to calculate Net Present Value Internal Rate of Return Internal Rate of Return (IRR) is the discount rate of cash flow at which an investment breaks even. Similar to Net Present Value (NPV), which discounts a series of cash flow back to present value. For example, an initial investment is $10000, at the end of 1st year, the cash flow received is...

Access VBA NPV Function to calculate Net Present Value

Posted on May 8, 2016May 14, 2016 by Wyman W

This Access tutorial explains how to use NPV Function in Access VBA to calculate Net Present Value. You may also want to read: Excel NPV Function Net Present Value Net Present Value is to calculate the present value of future cash flow. For example, your client will give you $10000 when the project completes after 1 year, assume there will be inflation, the future $10000 will be less than the present $10000. Assume the inflation is 4%, the present value of $10000 is 10000/(1+4%) = 9615.3...