SPSS define variables in Variable View

Posted on September 9, 2015 by Wyman W

This SPSS tutorial explains how to define variables in Variable View. You may also want to read: Types of data in statistics (Interval, Nominal, Ordinal) SPSS - Variable View In SPSS, understanding the Variable View is the first step before using any analytic functions. The purpose of Variable View is to define variables. In Excel, you don't have to define whether a column is number, date, or text, because Excel guesses the data type for you but of course it could be wrong in some cas...

Excel VBA LinkInfo Method

Posted on September 8, 2015 by Wyman W

This Excel VBA tutorial explains VBA LinkInfo Method to check status of linked workbook. You may also want to read: Excel find all external links and broken links in workbook Excel VBA LinkInfo Method Workbook LinkInfo Method is usually used to check status of linked workbook, so that we know whether the workbook link is broken or normal. Usually this Method is used with LinkSources Method. Syntax of LinkInfo Workbook.LinkInfo(Name, LinkInfo, Type, EditionRef) Name Required...

How to become Microsoft MVP (Excel MVP)

Posted on September 3, 2015March 20, 2016 by Wyman W

This article is to share my experience to become Microsoft MVP - Excel (Microsoft Most Valuable Professional). Introduction of Microsoft MVP I received the Microsoft MVP - Excel award on July 1st 2015 and it was my first Microsoft MVP award. I have done many research before I applied for Microsoft MVP, and I have seen many articles on the internet that copy the official information form the Microsoft website which is not helpful at all. In this article, I will share everything I know fro...

Excel Access VBA extract percentage from text

Posted on September 1, 2015December 26, 2016 by Wyman W

This Excel / Access tutorial explains how to extract percentage from text. Access Excel VBA extract number from text or extract alphabet Excel Extract Time from Date Time or Extract Date Access Excel extract file name from file path Excel Access VBA extract percentage from text Recently I got a report that contains percentage but it is mixed with other text. For example, Salary increased by 10% We are unable to do any calculation unless we extract the 10% or 10 out from the text. ...

Excel VBA separate line break data into different rows

Posted on August 31, 2015March 30, 2017 by Wyman W

This Excel VBA tutorial explains how to separate line break data into different rows. You may also want to read: Access Excel remove line break and carriage return Excel VBA separate line break data into different rows Recently I received a report from a HR system which is claimed to be a standard report. In the report, some data are grouped into one Cell and are separated by line break. I found this report format totally unacceptable. From the database perspective, each dependent...

Excel VBA protect worksheet with password

Posted on August 23, 2015January 31, 2017 by Wyman W

This Excel VBA tutorial explains how to use VBA to protect worksheet with password. You may also want to read: Excel VBA hide worksheet with password (xlVeryHidden) Excel VBA Add password to Excel workbook in folder Protect VBA code with password Different kinds of workbook protection There are several kinds of protection in Excel. Add password to workbook. Users are asked to open the Excel file with password. Protect workbook with password. Protect the structure of all w...

Excel VBA color code list – ColorIndex, RGB color, VB color

Posted on August 23, 2015August 23, 2015 by Wyman W

This Excel tutorial collects the VBA color code list for reference purpose, which includes ColorIndex, RGB color, VB color. Excel ColorIndex VBA Excel ColorIndex Property is to set color or get color for Objects like Cell color and Shape color. ColorIndex offers 56 basic colors plus the following special numbers. ColorIndex Value Explanation -4142 / xlColorIndexNone / xlNone No fill -4105 / xlColorIndexAutomatic / xlAutomatic Default color (white) Example 1: Se...

Excel VBA hide worksheet with password (xlVeryHidden)

Posted on August 23, 2015April 18, 2016 by Wyman W

This Excel VBA tutorial explains how to hide worksheet with password (xlVeryHidden). You may also want to read: Excel VBA protect worksheet with password Excel VBA Add password to Excel workbook in folder Protect VBA code with password Different kinds of workbook protection There are several kinds of protection in Excel. Add password to workbook. Users are asked to open the Excel file with password. Protect workbook with password. Protect the structure of all worksheets (...

Access Excel remove line break and carriage return

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

This Access / Excel tutorial explains how to remove carriage return and remove line break in a text. You may also want to read: Excel VBA separate line break data into different rows Access Excel remove carriage return and remove line break Recently our company uploaded employee data in spreadsheet data to the new system. One user reported that her home address shows some special code (something like hex code) that is supposed to be a space. Since the code appears after a comma, I suspec...

Access VBA DoCmd.TransferSpreadSheet Method

Posted on July 20, 2015August 28, 2017 by Wyman W

This Access tutorial explains how to use DoCmd.TransferSpreadSheet Method to export and import files. You may also want to read: Access VBA DoCmd.OutputTo Method Access VBA auto generate mass report by group to Excel Access Export all Queries to Excel Syntax of DoCmd.TransferSpreadSheet Method TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA) Name Required/Optional Description TransferType Optional The type of transfe...