Difference makes the DIFFERENCE
Categorised Menu List
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
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.
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.
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: 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.**
Byte Data Type:
range from 0 to 255
Integer Data Type:
range from –32,768 to 32,767
String Data Type:
Date Data Type:
range from January 1, 1900
to December 31, 9999.
Long Data Type:
range from –2,147,483,648 to 2,147,483,647
Single Data Type:
range from –3402823E38 to –1.401298E–45 or from 1.401298E–45 to 3.402823E38.
Double Data Type:
range from –1.79769313486232E308 to –4.94065645841247E-324 or 1.79769313486232E308 to 4.94065645841247E-324.
Currency Data Type: range from –922,337,203,477.5808 to 922,337,203,685,477.5807.
Dim i as Integer
Dim i as Integer, j as Integer
Dim i, j as Integer
Dim i as Integer, j as String
Dim i as Integer, j as String, k as Date
** 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
Dim intEmpNo as Integer
Dim lngEmpSalary as Long
Dim strEmpName as String
Dim dtJoinDate as Date
(Sample Code and Explanation)
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.