Access SQL SELECT, SELECT TOP, SELECT INTO

Posted on July 5, 2015July 5, 2015 by Wyman W

This Access tutorial explains how to use SQL SELECT, SELECT TOP and SELECT INTO statement. Access SQL Select Statement SELECT is the most fundamental syntax in SQL. SELECT Statement defines what fields we want to retrieve in the Query. In this tutorial, I will demonstrate how to use SELECT, SELECT TOP and SELECT INTO statement. Access SQL SELECT Syntax of Select and Where SELECT expressions FROM tables WHERE criteria Select specific fields Assume that we have created the below ...

Excel VBA DIR Function

Posted on July 2, 2015July 2, 2015 by Wyman W

This Excel tutorial explains how to use Excel VBA DIR Function and loop through workbooks in a folder. You may also want to read: Excel loop workbooks in folders and subfolders with FileSystemObject Excel VBA DIR Function DIR Function returns the first file name that matches the criteria in Function argument (pathname and attributes). DIR Function is usually used with loop in order to manipulate multiple files of specific name. Syntax of DIR Function Dir(pathname, [attributes]) ...

Excel calculate Covariance, Coefficient of Correlation

Posted on June 28, 2015 by Wyman W

This Excel tutorial explains the meaning and calculation of Covariance and Coefficient of Correlation. You may also want to read: Excel Range, Variance, Standard Deviation Covariance, Coefficient of Correlation Assume that we have two sets of data - English and Mathematics results for each student. How can we tell whether English result has any relationship with Mathematics result? Name of Student English Result Math Result John 50 60 Mary 60 70 Peter 70 80 ...

Types of data in statistics (Interval, Nominal, Ordinal)

Posted on June 27, 2015April 10, 2016 by Wyman W

This tutorial explains the types of data in statistics, such as Interval, Nominal, Ordinal data. You may also want to read: Excel Range, Variance, Standard Deviation Excel central tendency (mean mode median) Types of data in statistics (Interval, Nominal, Ordinal) This tutorial is nothing related to Excel, but it is the foundation for my other Excel statistics tutorial. Understanding the data type is the first step to understand statistics, because it affects what data we should coll...

Excel export Excel to PDF with and without VBA

Posted on June 27, 2015December 14, 2015 by Wyman W

This Excel tutorial explains how to export Excel to PDF with and without VBA, how to export all worksheets to one PDF, and export all worksheets to separate PDF. You may also want to read: Select multiple worksheets using Excel VBA Excel export Excel to PDF In Excel, navigate to File > Save As, in the Save As Type, you can save a file as different file types. Below is the available types in Excel 2013. In this tutorial we are going to focus on exporting Excel to PDF. Now se...

Excel consolidate all Comments in the workbook

Posted on June 26, 2015February 5, 2017 by Wyman W

This Excel tutorial explains how to consolidate all Comments in the workbook using Macro. You may also want to read: Excel show all comments in worksheet Excel delete all comments in Workbook Excel consolidate all Comments in the workbook Previously I wrote a post about how to show all Comments on the Cell (unhide it), in this tutorial I will show a Macro that consolidate all comments in the workbook. VBA Code - consolidate all comments Public Sub consolComments() counter = 1 ...

Excel show all comments in worksheet

Posted on June 23, 2015February 5, 2017 by Wyman W

This Excel tutorial explains how to show all comments and hide all comments in workbook. You may also want to read: Excel consolidate all Comments in the workbook Excel delete all comments in Workbook Excel show comments in worksheet Comment is a yellow dialog box that you can write comment regarding specific Cell. To insert a Comment, right click on a Cell and then select Insert Comment When we insert a Comment into a Cell, there would be a red triangle on the top right of ...

Excel verify Number format and convert Text to Number

Posted on June 22, 2015July 12, 2015 by Wyman W

This Excel tutorial explains how to verify Number format in a column. If it is a Text then convert Text to Number. You may also want to read: Excel verify Text format and convert number to text Excel Cell Function to check Cell format, address, contents Excel verify Number format Number can be in two forms in Excel - Text and Number. If a Number is a Text, there is a small triangle on the top left of the Cell. By default, number in Text format aligns to the left while Number alig...

Excel VBA Inputbox and Application.Inputbox

Posted on June 21, 2015February 6, 2017 by Wyman W

This Excel tutorial explains difference between VBA Inputbox and Application.Inputbox and how to use them. You may also want to read: Excel VBA Msgbox and handling of return value Excel VBA use Inputbox to select options Excel VBA Inputbox and Application.Inputbox Inputbox is a pop up dialog box that displays a message and provides a text box for users to input free text. It is very similar to Msgbox except that Msgbox does not have a text box for user input. There are two kinds o...

Create Excel Add-in and add in Ribbon

Posted on June 14, 2015July 7, 2015 by Wyman W

This Excel tutorial explains how to add Excel Add-in in Ribbon. You may also want to read: Writing your first Excel Macro by recording Macro Why use Excel Add-in After you have created a Macro (Sub Procedure), you may want to distribute it for users to use. You can simply add the VBA code in the Module of a Workbook and then distribute the Workbook, but the problem is that the Macro cannot be used in other Workbook. Depending on the situation, you may want to distribute a Macro is thr...