Visual Basic/Data Types

Data types in Visual Basic can be divided into three groups: Native: Types that are understood directly by the Visual Basic compiler without assistance from the programmer User-defined: commonly referred to by the initials UDT, meaning User defined Type, these correspond to Pascal records or C structs Classes: the basis for object oriented programming in Visual Basic. Classes include forms, add-ins, and database designers. If you are using Excel VBA, remember that VBA is essentially VB6, and hence those rules apply. Built in Types Visual Basic 7 and newer (i.e, VB.NET) > The built in types are: Visual Basic typeCommon language runtime type structureNominal storage allocationValue rangeBooleanBooleanDepends on implementing platformTrue or FalseByteByte1 byte0 through 255 (unsigned)Char(single character)Char2 bytes0 through 65535 (unsigned)DateDateTime8 bytes0:00:00 (midnight) on January 1, 0001 through 11:59:59 PM on December 31, 9999DecimalDecimal16 bytes0 through +/-79,228,162,514,264,337,593,543,950,335 (+/-7.9...E+28) † with no decimal point; 0 through +/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest nonzero number is +/-0.0000000000000000000000000001 (+/-1E-28) †Double(double-precision floating-point)Double8 bytes -1.79769313486231570E+308 through -4.94065645841246544E-324 † for negative values; 4.94065645841246544E-324 through 1.79769313486231570E+308 † for positive valuesIntegerInt324 bytes -2,147,483,648 through 2,147,483,647 (signed)Long(long integer)Int648 bytes -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807 (9.2...E+18 †) (signed)ObjectObject(class)4 bytes on 32-bit platform 8 bytes on 64-bit platformAny type can be stored in a variable of type ObjectSByteSByte1 byte -128 through 127 (signed)Short(short integer)Int162 bytes -32,768 through 32,767 (signed)Single(single-precision floating-point)Single4 bytes -3.4028235E+38 through -1.401298E-45 † for negative values; 1.401298E-45 through 3.4028235E+38 † for positive valuesString(variable-length)String(class)Depends on implementing platform0 to approximately 2 billion Unicode charactersUIntegerUInt324 bytes0 through 4,294,967,295 (unsigned)ULongUInt648 bytes0 through 18,446,744,073,709,551,615 (1.8...E+19 †) (unsigned)User-Defined(structure)(inherits from ValueType)Depends on implementing platformEach member of the structure has a range determined by its data type and independent of the ranges of the other membersUShortUInt162 bytes0 through 65,535 (unsigned) Byte, Integer & Long Visual Basic 6 and older < Example: Dim a as Byte Dim i as Integer Dim x,y as Long 'Define two variables. Note that only the last variable will be a long integer. Now those variables will only be capable of storing integer values (without decimal). Long integers can store a number with a bigger range of value than integers but they occupy a bigger space of RAM. Type Storage Range of Values Byte 1 byte 0 to 254 Integer 2 bytes -32,768 to 32,767 Long 4 bytes -2,147,483,648 to 2,147,483,647 Some functions you need to know: Int() Int() converts a decimal value into an integer value: Dim i as Integer i=Int(3.9) Print i 'Prints 3 Single & Double These data types can store decimal values. "Double" compared to "Single" is similar to the "Long" compared to "Integer": Type Storage Range of Values Single 4 bytes -3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values. Double 8 bytes -1.79769313486232E+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232E+308 for positive values. Some useful functions: Round() Round() rounds off a decimal to a certain number of decimal digits that the programmer wants. The first argument should be a decimal value which you want to round off. The second argument specifies the number of decimal digits you want, for example: Dim pi as Double pi=3.141592653589 pi=Round(pi,2) 'Rounds off 3.141592653589 to only two decimal digits Print pi 'Prints 3.14 String A string is an array of characters. As an example: Dim a As String a = "This is a string" Strings can be concatenated (connected together to form a new string) using the "&" operator. For example, dim b as String b = "Wiki" & "book" & "s" Print b 'Prints "Wikibooks" A normal string variable occupies 10 bytes of RAM, plus the string's size, and can hold up to 2 billion characters! Some frequently used built-in string constants: vbTab, vbCrLf vbTab contains a string that does the same thing as the Tab key on your keyboard, while vbCrLf creates a character return and a line feed(similar to the Enter key): Print "Jack:" & vbTab & "1 pie" & vbCrLf & "me:" & vbTab & "10 pies" Will print: Jack: 1 pie me: 10 pies To include special characters and quotation marks in the strings, the Chr() function may be used: Dim a As String Print "A quotation mark: [" & Chr(34) & "]" a = "Replace 'apostrophes' for quotation marks" Replace( a, "'", Chr(34) ) Print a Some string functions: Str(),Val(),inStr(),Mid(),Replace(),Trim() In fact there are tons of built-in string manipulation functions available. But right now, I'll just introduce two: Str() and Val(). Str() converts any numerical value into a string value while Val() converts a string value into a numerical value(only when it's convertible). Dim MyString As String Dim MyNumber As Single MyString=Str(300) 'converts a number into a string MyNumber=Val("300") 'converts a string into a number Even if you don't do the conversion, you will not end up getting Type Mismatch Errors. However, it is considered better to use explicit type conversions, because it is easier to debug. Even if you do be prepared for next to impossible to debug problems caused by VB refusing to convert something and refusing to tell you what the heck it is. VB is extremely touchy and raises an exception at the least expected times. Structure An example definition of a structured type: Type E2Point x As Double y As Double End Type Sub Test() Dim MyPoint As E2Point Dim MyPoint2 As E2Point MyPoint.x = 4 MyPoint.y = -5 MyPoint2 = MyPoint 'Make a copy MyPoint2.x = 3 MyPoint2.y = -6 Debug.Print MyPoint.x, MyPoint.y '4, -5: not overriden with 3 and -6 Debug.Print TypeOf MyPoint Is E2Point 'True Debug.Print TypeOf MyPoint Is Object 'False End Sub The type has to be defined outside of a procedure. A variable of a structure type is not an object. Links: Type Statement, Office 2013, msdn.microsoft.com Type Statement at Visual Basic for Applications Reference, msdn.microsoft.com Enumeration An example definition of an enumerated type: Enum Colors Red '=0 Green '=1 Blue '=2 End Enum Enum Colors2 Red2 = 1 Green2 '=2 Blue2 '=3 End Enum Sub Test() Debug.Print Red, Green, Blue Debug.Print Red2, Green2, Blue2 Dim MyVar As Colors 'Ends up being typed as Long MyVar = 8 'Does not lead to an error: no restriction on values End Sub Links: Enum Statement, Office 2013, msdn.microsoft.com Enum Statement at Visual Basic for Applications Reference, msdn.microsoft.com Type Test To find out about the type of a variable, you can use "TypeOf ... Is ..." test. The tested types can only be object types and structure types, but the test can be applied to any variable, whether typed as an integer, a string or an object. An example of TypeOf in Excel: Set MyVar = Selection Debug.Print "Selection is an object: " & TypeOf MyVar Is Object Debug.Print "Selection is a range: " & TypeOf MyVar Is Range Debug.Print "Sheets is of type Sheets: " & TypeOf Sheets Is Sheets MyStr = "Hello" Debug.Print "Text is an object: " & TypeOf MyStr Is Object If TypeOf MyVar Is Range Then Set MyCells = MyVar.Cells End If An example test with a "Select Case True": Set MyVar = new Collection Select Case True Case TypeOf MyVar is Range Debug.Print "Range" Case TypeOf MyVar is Collection Debug.Print "Collection" Case Else Debug.Print "Other cases" End Select You can further find out about a type using IsObject and TypeName functions: Debug.Print IsObject(Selection) 'True Debug.Print IsObject("Hello") 'False Debug.Print TypeName("Hello") 'String Debug.Print TypeName(4) 'Integer Debug.Print TypeName(3.5) 'Double Debug.Print TypeName(Selection) 'Range Links: If...Then...Else Statement at Visual Basic for Applications Reference, msdn.microsoft.com TypeName Function at Visual Basic for Applications Reference, msdn.microsoft.com IsObject Function at Visual Basic for Applications Reference, msdn.microsoft.com IsArray Function at Visual Basic for Applications Reference, msdn.microsoft.com IsDate Function at Visual Basic for Applications Reference, msdn.microsoft.com IsNumeric Function at Visual Basic for Applications Reference, msdn.microsoft.com IsNull Function at Visual Basic for Applications Reference, msdn.microsoft.com Files Procedures and Functions