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
Dim val12 As enMyCities
Type myUserDefinedType

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) 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 myObject 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 (myObject 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.


7) 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.


8) 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 myVariant 
Dim myVariant As Variant

9) 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.


10) 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 myVariable As Variant
myVariable = 100

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

11) 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 myVariant As Variant    'Empty is the default  
Set myVariant = Nothing

12) Can you describe the 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 myDecimal As Variant 
myDecimal = VBA.CDec(123.456)
Debug.Print VBA.TypeName(myDecimal) 'Decimal

13) Can you describe the Currency data type ?
The Currency is used to store fixed point real numbers.
This has 15 digits to the left of the decimal point and 4 digits to the right.
The smallest value is -922,337,203,685,477.
The largest value is 922,337,203,685,477.
The size of this data type is 8 bytes (64 bits).

Dim myCurrency As Currency 
myCurrency = -922337203685447
myCurrency = 922337203685447

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 myText As String 
Debug.Print VBA.TypeName(myText) '"String"
Debug.Print VBA.VarType(myText) '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 myVariant As Variant 
Debug.Print myVariant = 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 myVariant As Variant 
myVariant = Empty

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

If (VBA.IsEmpty(myVariant) = 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 myVariant As Variant 
myVariant = Null

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

If (VBA.IsNull(myVariant) = 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 ?

Public Type TPerson 
   sNAME As String
   iAGE As Integer
End Type

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

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

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

In the first block an instance is assigned immediately. 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.


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