User FAQs

If you have a question, please send it to us.


1) What does Strongly Typed mean ?
A programming language is strongly typed if there are no implicit type conversions.
A strongly typed programming language is one in which each data type is predefined as part of the programming language.
All constants and variables must be defined with a specific data type.
An advantage of strong data typing is that it imposes a rigorous set of rules on a programmer and therefore guarantees a certain consistency of results.


2) Is VBA a Strongly Typed programming language ?
No. Type conversions can be implicit. This has nothing to do with Option Explicit.

Dim myString As String 
myString = "1000"
Dim myDouble As Double
myDouble = myString * 1.5

Debug.Print myDouble '1500
Debug.Print VBA.TypeName(myDouble) 'Double

3) What is the difference between Value data types and Reference data types ?
A value type is also known as a primitive or simple type.
When a value data type is created, a single space in memory is allocated on the stack and is accessed directly.
Value data types are automatically given a default value of zero (or equivalent) when they are declared.
A reference type is also known as an object type.
When a reference data type is created, memory is allocated on the heap and is not accessed directly. Instead a pointer to the memory location is used.
Reference data types are automatically given a default value of Nothing when they are declared.
When you want to assign something to a reference type you must use the Set statement.


4) Can you give some examples of Value data types ?

Dim val1 As Byte 
Dim val2 As Integer
Dim val3 As Long
Dim val4 As Single
Dim val5 As Double
Dim val6 As Currency
Dim val7 As Date
Dim val8 As String 'Variable Length
Dim val9 As String * 10 'Fixed Length
Dim val10() As Integer 'Array
Dim val11 As TPerson 'User Defined Type
Dim val12 As enMyCities 'Enumeration

5) Can you give some examples of Reference data types ?

Dim ref1 As Object 
Dim ref2 As MyClass
Dim ref3 As VBA.Collection
Dim ref4 As Scripting.FileSystemObject
Dim ref5 As Scripting.Dictonary

6) What is a Type Declaration Character ?
It is possible to declare variables by appending a special character to the end of the variable name.

Dim iNumber@   'shorthand for As Currency  
Dim iNumber# 'shorthand for As Double
Dim iNumber% 'shorthand for As Integer
Dim iNumber& 'shorthand for As Long
Dim iNumber! 'shorthand for As Single
Dim iNumber^ 'shorthand for As LongLong - 64bit only
Dim iText$ 'shorthand for As String

7) Can you describe the Nothing keyword in the context of reference data types ?
The Nothing value is the default value when you declare a reference data type.

Dim oMyObject As Object 

It is possible to explicitly assign the Nothing value to a reference data type.
You must use the Set statement.

Dim myClassVar As MyClass 
Set myClassVar = Nothing

You can use the 'Is' keyword to test for this value

If (oMyObject Is Nothing) Then 
End If
If (myClassVar Is Nothing) Then
End If

You cannot assign the Nothing value to a value data type. You will get a compile error, Object not found.


8) What are the data types of these two variables ?

Dim one, two As Integer 

The first variable (one) has type Variant.
The second variable (two) has type Integer.


9) Can you describe the Variant data type ?
The Variant is used to hold any data type, value or reference (except for fixed length strings).
It can even hold objects and user defined types.
This is the default data type if one is not specified.
The size of this data type is 16 bytes (128 bits).
The following two declarations are identical.

Dim vMyVariant 
Dim vMyVariant As Variant

10) When should I use the Variant data type ?
Never use the Variant data type unless absolutely necessary.
The only situation when you should use the Variant data type is:
*) When you don't know the data type.
*) When you are writing a general helper routine that needs to work with a number of different data types.
*) When you want to pass an array of any data type into a subroutine or return an array from a function.
*) When you use the "Application.GetSaveAsFileName" method.


11) When you declare a variable as a Variant, is it a Value data type or a Reference data type ?
The Variant keyword is just a placeholder for any data type.
A Variant data type can be either a Value data type or a Reference data type.

'assigning a value data type
Dim vMyVariable As Variant
vMyVariable = 100

'assigning a reference data type
Dim vMyVariable As Variant
Set vMyVariable = New VBA.Collection

12) Can you describe the Nothing keyword in the context of the Variant data type ?
You can explicitly assign the Nothing value to a Variant data type.
You must use the Set statement.

Dim vMyVariant As Variant    'Empty is the default  
Set vMyVariant = Nothing

13) Can you describe the Variant/Decimal data type ?
The Decimal is used to store fixed point real numbers.
The Variant data type has a special sub type called Decimal.
This data type must be declared as a Variant and can only be created using the CDEC conversion function.
This can hold very large numbers and maintains precision up to 29 significant figures.
This has a binary integer value and an integer scaling factor that specifies what portion of the value is a decimal fraction.
The range is +/-79,228,162,514,264,337,593,543,950,335 (with no decimal places).
The range is +/-7.9228162514264337593543950335 (with 28 decimal places).
The size of this data type is 16 bytes (128 bits).

Dim dvMyDecimal As Variant 
dvMyDecimal = VBA.CDec(123.456)
Debug.Print VBA.TypeName(dvMyDecimal) 'Decimal

14) Can you identify the underlying data type of a variable ?
Yes. There are two built-in functions you can use: TYPENAME and VARTYPE.

Dim sMyText As String 
Debug.Print VBA.TypeName(sMyText) '"String"
Debug.Print VBA.VarType(sMyText) 'VBA.VarType.vbString

15) Can you describe the Empty keyword ?
The Empty value is the default value when you declare a Variant data type.
The Empty value is equivalent to 0, empty string and False, in the context of value data types.

Dim vMyVariant As Variant 
Debug.Print vMyVariant = Empty 'True
Debug.Print Empty = 0 'True
Debug.Print Empty = "" 'True
Debug.Print Empty = False 'True
Debug.Print Empty = VBA.Constants.vbNullString 'True

You can explicitly assign the Empty value to a Variant data type.

Dim vMyVariant As Variant 
vMyVariant = Empty

You can use the ISEMPTY function to test for this value.

If (VBA.IsEmpty(vMyVariant) = True) Then 

16) Can you describe the Null keyword ?
The Null value can only be assigned explicitly to a Variant data type.
It is often used to indicate an invalid value or an error.

Dim vMyVariant As Variant 
vMyVariant = Null

You can use the ISNULL function to test for this value.

If (VBA.IsNull(vMyVariant) = True) Then 

17) What is the TypeOf Operator ?
This operator compares a reference data type to an actual data type and returns either True or False.

If (TypeOf ActiveWorkbook.ActiveChart Is Excel.Worksheet) Then 
End If
If (TypeOf myClassVar Is MyClass) Then
End If

18) When would you use the Type keyword ?
This keyword can be used to define a User Defined data type.
User defined data types can contain one or more elements.
A user defined data type is a value data type.


19) Can you define a Type that contains one String and one Integer ?
When using user defined types always prefix them with the word "type_".

Public Type type_Person 
   sName As String
   iAge As Integer
End Type

20) What is the difference between the following two blocks of code ?

Dim oMyChart As New Chart 
oMyChart.ChartTitle = "my chart"

Dim oMyChart2 As Chart
Set oMyChart2 = New Chart
oMyChart2.ChartTitle = "my chart"

In the first block an instance is assigned immediately using Dim As New. You have less control and you are unable to check if the object is Nothing.
In the second block an instance is created just before it is needed. You have more control and you can check if the object is Nothing.


21) What is a Collection object ?
A Collection is a built-in object that you can use to represent a group of objects.
You can only refer to the members of this collection, by their position.

Dim colMyCollection As VBA.Collection 
Set colMyCollection = New VBA.Collection
colMyCollection.Add "Item_1"
colMyCollection.Add "Item_2"
colMyCollection.Add "Item_3"

22) How to check if an item exists in a Collection ?
If you want to refer to a member of a collection, using the name (assuming it is unique) you need to add a key value as well.

Dim colMyCollection As VBA.Collection 
Set colMyCollection = New VBA.Collection
colMyCollection.Add "Item_1", "Item_1"
colMyCollection.Add "Item_2", "Item 2"
colMyCollection.Add "Item_3", "Item 3"

23) What is a Dictionary object ?
Requires - Microsoft.Scripting.Runtime library.
A dictionary object can contain pairs of objects where each pair consists of an item and a unique key.
The item can be any data type (including objects).
The key can be any data type (except for arrays) although strings or numbers are typically used.
Dictionaries are often compared to associative arrays otherwise known as hash tables.

Dim dicMyDictionary As Scripting.Dictionary 
Set dicMyDictionary = New Scripting.Dictionary
dicMyDictionary.Add "key1", "Item_1"
dicMyDictionary.Add "key2", "Item_2"
dicMyDictionary.Add "key3", "Item_3"

24) What are the differences between a Collection and a Dictionary ?
*) When adding items the order of the arguments is different. The Collection.Add arguments are in the order Item, Key. The Dictionary.Add arguments are in the order Key, Item.
*) Unique keys are mandatory in a Dictionary but optional in a Collection.
*) Items can only be returned using the key in a Dictionary but can be returned using the key or the ordinal position (1 based) in a Collection.
*) Invalid keys will generate an error when passed to a Collection but in a Dictionary will return an empty string.
*) Unique keys are case sensitive in a Dictionary (by default, although easily changed) but are not case sensitive in a Collection (by default).
*) The Dictionary has an Exists method to test for a particular key. In a Collection you must try to retrieve the item and then handle if the key cannot be found.
*) A list of keys is available from a Dictionary using the Keys method. In a Collection you cannot get a list of the keys.

Dim vKeyValues As Variant 
vKeyValues = MyDictionary.Keys()

*) The items are read/write in a Dictionary but read-only in a Collection. In a Collection you cannot change a particular item you must remove and add a new item.
*) The keys are read/write in a Dictionary but read-only in a Collection.
*) You can remove all the items from a Dictionary in one go without destroying the object itself. In a Collection you can only remove one item at a time, or destroy and recreate the whole collection.
*) Both support the For-Each-Next statement for enumerating through the items. A Dictionary loops through the Keys. A Collection loops through the Items.
*) You can add an item implicitly to a Dictionary using the Item property. In a Collection you can only use the Add method.

dicMyDictionary.Item("key4") = "item_4"  'doesn't exist but gets added  
vValue = dicMyDictionary.Item("key5") 'doesn't exist but gets added

25) When would you use a Dictionary instead of a Collection ?
*) Retrieve keys as well as the items.
*) Accommodate changes in items and/or keys.


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext