Categorised Menu List

Datatypes in Excel Spreadsheet

To have a primary understanding of Datatypes in Excel and for the comfort of the reader, we can to consider them in two types, one for SpreadSheet operations and the other for VBA Programming. The following is for spreadsheet and, next, we look into VBA or Programming datatypes 

gallery/datatypes_01
  • Numeric Datatype: When the entire cell content contains only numbers, with exception to decimal point, system considers as Numeric Datatype. Generally, it is right aligned. All arithmetic operations are possible with this type of data.
  • String or Text Datatype: When atleast one character in the entire cell contains a non-numeric character, it is considered as Text or String datatype. Generally, it is left aligned.

To place a number as a string or text datatype, then place a "Single Quote" before the number, eg: in Cell A1, type '21, now the text is left aligned. It changes its form to String type, meaning arithmetic operations donot work now.

 

to Explain:
if the datatype is numeric, i.e., if the cell contains 21+21, then, it displays 42 as result, where as if the datatype is string or text type, i.e., if the cell contains 21+21, then, it displays result as 2121. This is because, it is written as '21+'21, Press F2 on keyboard to observe the difference.

  • Date Datatype: While cosidering Date datatypes, the key point to remember:
    The way it appears is not the way it is stored. eg: if it appears as 12-Sep-2014, it is not stored as 12-Sep-2014, but it is stored as 41894.00, if the cell has a value like 12-Sep-2015, then it is stored as 42259.00.

The reason behind this that Excel stores dates as numbers, but, dispalys as Date, hence 12-Sep-2015 is seen as 09/12/2015, at times, 12/09/2015, and others is also possible.  If I type =now() in Excel Spreadsheet, it displayed as 42277.98065, meaning: Integer part is DATE and real part is TIME (numbers before decimal indicates DATE and numbers after decimal represents time)

The Integer part or the numbers before decimal: In Excel, date starts at 01-Jan-1900, which is denoted as 1, 2-Jan-1900 is denoted as 2, like wise 31-Jan-1900 as 31, 01-Feb-2015 is denoted as 32, 01-Feb-1900 is denoted as 33, likewise, the date number grows on and on... till 9999(Excel start date can be changed, but, by default, it takes 01-Jan-1900.)

The real part or the part after decimal indicates time, where the max value for time can be 86400, which is 24 hrs x 60mins x 60 secs. The current time is displayed as number, for eg: Now, it the time is 12:00:01 (midnight), it is displayed as 1, 12:00:02 is dispalyed as 2 and so on till 86400 secs, which are the number of seconds we have per day.

Hence, what appears in a cell is not really what it is... but, a formatted value... 

Datatypes in Excel VBA

Datatypes: The type of data that we intend to place in a variable or may be an outcome of a particular procedure/programme.
The most general and frequently used datatypes are integer, string and boolean. When the value may not fit in the definition of integer, prefer to have long. You donot need to get by-heart any of these but remember their order, either ascending order or descending order, how-ever you may find it comfortable.
The emphasis is on error-handling - when the outcome doesnot fit into the given datatype, system displays an OVERFLOW error.
** for the benefit of the reader, all the numbers written over here are taken from Microsoft website, I didnot check, if they can store such large values, I checked only with Integer, Long, Boolean and Single, inclusive of Date Datatype.**

Different Types of Data for storage

Byte Data Type:
range from 0 to 255

  • Smallest numeric data type to place any number between 0 and 255 inclusive. Doesn't include any negative values.

Integer Data Type:
range from –32,768 to 32,767

  • Use this data type to store only whole numbers that range from –32,768 to 32,767, in total, it can contain 65536 numbers, which may be either positive or negetive.

String Data Type:

  • String is another very common data type; it stores values or numbers, but treats them as text. There are two varieties: fixed and variable. A fixed string can handle from 1 to 65,400 characters. To declare a fixed string, use the Dim statement in the form.

Date Data Type:
range from January 1, 1900
to December 31, 9999.

  • The Date data type stores a specially formatted numeric value that represents both the date and time. You don't have to store both the date and time value. The Date data type accepts either the date or the time, or both.

Long Data Type:
range from –2,147,483,648 to 2,147,483,647

  • The Long data type is also numeric data type used for storing only whole numbers. The storage scope of LONG datatype is much bigger than integer datatype.

Single Data Type:

range from –3402823E38 to –1.401298E–45 or from 1.401298E–45 to 3.402823E38.

  • The Single data type stores precision numbers—numbers with decimal places or fractional numbers. The data type is similar to Double, but the range is smaller.

Double Data Type:

range from –1.79769313486232E308 to –4.94065645841247E-324 or 1.79769313486232E308 to 4.94065645841247E-324.

  • Use the Double data type to store precision floating point numbers

Currency Data Type: range from –922,337,203,477.5808 to 922,337,203,685,477.5807.

  • Use the Currency numeric data type to store monetary values. 

Dim i as Integer

  • declares a variable by name i with datatype integer
  • this variable can accomodate any value between -32678 to +32767
  • beyond which, system displays error

 

Dim i as Integer, j as Integer

  • declares two variables i and j as integers

 

Dim i, j as Integer

  • Declares i as variant datatype (which can accomodate any datatype) this type is not recommended, it is suggested to use datatype always
  • j as integer datatype

 

Dim i as Integer, j as String

  • declares i as Integer
  • j as string datatype

 

Dim i as Integer, j as String, k as Date

  • declares i as integer datatype
  • declares j as string datatype
  • declares k as date datetype

** always specify datatype, else VBA would consider it as variant datatype Capitalisation of first letter is done automatically, as VBA is not case-sensitive this means, lower case letters and upper case letters have the same meaning

 

Dim i as integer = dim i as integer => system converts as Dim i as Integer

 

Like wise: Declarations with other datatypes hold good.

 

Dim i as integer

dim j as long

dim k as string

dim l as single

dim m as boolean and so on...

 

It is suggested to include datatype in variable name, also, prefer to have meaningful names for variables as shown below,

- so that, it will help us to identify the datatype and

- the purpose for which the variable is declared

 

eg:

Dim intEmpNo as Integer

Dim lngEmpSalary as Long

Dim strEmpName as String

Dim dtJoinDate as Date

Person's Link for additional information:
http://www.quepublishing.com/articles/article.aspx?p=339477&seqNum=2. 

(this link is working as on today (ie., 09/01/2016)

Variable Declaration

(Sample Code and Explanation)

Data Types in SPREADSHEETS

TYPES OF DATA AND USES

Boolean Data Type:
range either 1 or 0

Use the Boolean data type to store logical data that contains only one value: either on or off, either true or false, either yes or no, and so on. The keywords True and False are predefined constants and are interchangeable with the values –1 and 0, respectively.