Questions

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


2) Can you explain the difference between the Stack and the Heap ?
The stack contains value data types on a first in, first out basis.
The heap contains reference data types.


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

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

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


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

7) Can you think of any situations when you have to use a Variant data type ?
*) 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 returned from a function.
*) When you use the "Application.GetSaveAsFileName" method.


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

9) What is the difference between ByVal and ByRef and which is the default ?
ByRef - (default) - passes a pointer to the variable in memory.
ByVal - passes a copy of the variable.
Passing ByRef is the default when the passing mechanism is not specified.
The following two subroutines are identical.

Sub MySubroutine(iNumber As Integer) 
End Sub
Sub MySubroutine(ByRef iNumber As Integer)
End Sub

10) What is the 'Call' statement and when would it be used ?
The Call statement can be used when passing execution to a subroutine.
When it is used you must enclose any arguments in parentheses.
You should always use the Call keyword because it makes your code easier to read.

Sub MySubroutine() 
   MySub1
   MySub2 50
   Call MySub1
   Call MySub2(50)
End Sub

Sub MySub1()
End Sub
Sub MySub2(ByVal iNumber As Integer)
End Sub

11) What is the difference between a Sub and a Function ?
A Sub (subroutine or procedure) is a method that does not return a value.
A Function is a method that ALWAYS returns a value.
If a Function has no 'As' keyword then the default is 'As Variant'
The following two functions are identical.

Function MyFunction() 
   MyFunction = 100
End Function
Function MyFunction() As Variant
   MyFunction = 100
End Function

12) Can you use the Call statement when passing execution to a Function ?
Yes. You can use the Call keyword when you want to ignore the returned value.
In the first two subroutine calls the value returned from these functions is assigned to the variable 'myResult'.
In the last four subroutine calls the value returned from these functions is ignored.

Sub MySubroutine() 
   Dim myResult as Integer

   myResult = MyFun1
   myResult = MyFun2(50)
   MyFun1
   MyFun2 50
   Call MyFun1
   Call MyFun2(50)
End Sub

Function MyFun1() As Integer
   MyFun1 = 100
End Function
Function MyFun2(ByVal iNumber As Integer) As Integer
   MyFun2 = 100 + iNumber
End Function

13) Can you explain why the last subroutine call does not increment the amount variable ?
There are only two ways to call a subroutine.
The first is to not use the Call method in which case the arguments do not appear in parentheses.
The second is to use the Call method in which case the arguments have to appear in parentheses.
In the last subroutine call the argument (or expression) is being evaluated before being passed to the mySub2 subroutine.
You can enclose expressions inside there own set of parentheses to force them to be evaluated first.

Sub MySubroutine() 
   Dim amount As Integer
   amount = 50
   Debug.Print amount '50

   mySub2 amount
   Debug.Print amount '100

   Call mySub2(amount)
   Debug.Print amount '150

   mySub2 (amount)
   Debug.Print amount '150
End Sub

Sub mySub2(ByRef amount As Integer)
   amount = amount + 50
End Sub

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) What is the difference between the TYPENAME function and the VARTYPE function ?
TYPENAME - Returns the data type of a variable as a string.
VARTYPE - Returns the data type of a variable as an integer enumeration.


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

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


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

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


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


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

22) What is the difference between a Literal Constant and a Symbolic Constant ?
A literal constant is a specific value such as a number, date or text.

Dim myDate As Date 
myDate = #12/31/2020#

A symbolic constant is a literal constant that is represented by a name.

Public Const sVALUE As String = "text" 

23) What is an Enumeration ?
An Enumeration provides a way of grouping a collection of symbolic constants so they can be identified easily.
The underlying data type cannot be explicitly defined because all enumerations are defined with a Long data type.
An Enumeration is a value data type.


24) Can you define an Enumeration that contains three cities ?
If you do not specify any integer (Long) numbers, the first item will have a zero (0) value and subsequent items will be in sequential order.

Public Enum enMyCities1 
   London 'defaults to the value 0
   Tokyo 'defaults to the value 1
   Paris 'defaults to the value 2
End Enum

Public Enum enMyCities2
   London = 100
   Tokyo = 200
   Paris = 300
End Enum

25) Is it possible to return the string names instead of the numbers from an Enumeration ?
No. This can only be achieved by creating a function that matches the numerical values to their corresponding string names.

Public Function GetCityNames(ByVal enValue As enMyCities) As String 
   Select Case enValue
      Case enMyCities.London : GetCityNames = "London"
      Case enMyCities.Tokyo : GetCityNames = "Tokyo"
      Case enMyCities.Paris : GetCityNames = "Paris"
   End Select
End Function

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


27) Can you define a Type that contains one String and one Integer ?

Public Type TPerson 
   sNAME As String
   iAGE As Integer
End Type

28) Can you describe the 'Byte' data type ?
The Byte data type is used to store positive integer numbers.
The range is 0 to 255.
The size of this data type is 1 byte (8 bits).

Dim myByte As Byte 
myByte = 0
myByte = 255

29) Can you describe the 'Integer' data type ?
The Integer data type is used to store integer numbers.
The range is -32,768 to 32,767.
The maximum value is (2^15) - 1 = 32767.
The size of this data type is 2 bytes (16 bits).

Dim myInteger As Integer 
myInteger = -32768
myInteger = 32767

30) Can you describe the 'Long' data type ?
The Long is used to store integer numbers.
The range is -2,147,483,648 to 2,147,483,647.
The maximum value is (2^31) - 1 = 2147483647.
The size of this data type is 4 bytes (32 bits).

Dim myLong As Long 
myLong = -2147483648
myLong = 2147483647

31) Can you describe the 'Single' data type ?
The Single data type is used to store single precision floating point real numbers.
This can hold large numbers but with very little precision up to 7 significant figures.
The smallest value is -3.402823 E+38.
The largest value is 3.402823 E+38.
The size of this data type is 4 bytes (32 bits).

Dim mySingle As Single 
mySingle = -3.402823E+38
mySingle = 3.402823E+38

32) Can you describe the 'Double' data type ?
The Double is used to store double precision floating point real numbers.
This can hold very large numbers with good precision up to 15 significant figures.
The smallest value is -1.79769313486231 E+308.
The largest value is 1.79769313486231 E+308.
The size of this data type is 8 bytes (64 bits).

Dim myDouble As Double 
myDouble = -1.79769313486231E+308
myDouble = 1.79769313486231E+308

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

34) What is the difference between Fixed Point and Single/Double Floating Point ?
Both are represented in binary (base 2).
Floating point data types support fewer significant digits than fixed point data types (decimal) but can represent larger or smaller numbers.


35) What is the difference between the CDATE function and the CVDATE function ?
CDATE - Returns an actual Date data type
CVDATE - Returns a Variant data type whose sub type is Date. Only provided for backwards compatibility.


36) What is Option Explicit and what does it do ?

Option Explicit 

Including this at the top of a module means that all variables must be explicitly declared using the Dim statement.
This statement only affects the module it is declared in.


37) What problems do you have if you don't use Option Explicit ?
Variables do not have to be declared before they are used.
Mistyping a variable name will create a new variable and will probably create a run-time error.


38) What is a Type Declaration Character ?
Also known as a variable declaration character or type declaration suffix.
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

39) What is a Literal Number Suffix ?
Also known as numeric literal suffixes.
When you use the Variant data type the compiler is able to determine the correct data type for a numerical literal however there might be times when the default data type is different to the desired one.

Dim myNumberV1 As Variant 
Dim myNumberV2 As Variant
myNumberV1 = 100
myNumberV2 = 100#

Debug.Print VBA.TypeName(myNumberV1) 'Integer
Debug.Print VBA.TypeName(myNumberV2) 'Double

40) Can you identify which line causes a run-time error ?

Dim myDouble As Double 
Dim myInteger As Integer
myDouble = 3276700
myInteger = 32767
myDouble = myInteger * 100# 'TypeName(100#) = Double
myInteger = 32767
myDouble = myInteger * 100 'TypeName(100) = Integer

The last line causes an overflow run-time error.
This is because the data type when using the multiply operator (*) is always equal to the data type of the largest expression.
The expression "100#" has a data type of Double, therefore the result also has the data type Double


41) How would you declare a Variable Length String ?

Dim myString As String 

42) What is the best way to assign an empty string ?
Always assign your string to vbNullString when you need to empty the contents.
Avoid using an empty string ("") as this is slower and takes up more memory.
Zero-Length String ("") - Is an actual string literal and has a size of 6 bytes.
vbNullString - Is a special character that denotes an empty string which is equivalent to zero.

myString = VBA.Constants.vbNullString 

43) What is the best way to check for an empty string ?
Always check the length of the string using the LEN when you need to check if it is empty.
Avoid comparing with an empty string ("") as this requires 6 bytes of memory.

If VBA.Len(myString) = 0 Then 

44) What is the difference between the LEFT function and the LEFT$ function ?
LEFT - Returns a Variant data type whose sub type is String.
LEFT$ - Returns a String data type.
A variable declared as a Variant will get a variant-string data type from both functions because of implicit data type conversion.
A variable declared as a String will get a string data type from both functions because of implicit data type conversion.

Dim myString As String 
myString = "some text"
Debug.Print VBA.Left(myString,4)
Debug.Print VBA.Left$(myString,4)

45) How would you declare a Fixed Length String with 10 characters ?
A fixed length string is padded with null characters CHR(10) when it is declared.
The CHR returns the character with the corresponding ANSI number.

Dim myString As String * 10 

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

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

48) Can you describe the 'Error' Function ?
This function returns the error message that corresponds to a given error number.
The line below displays the text "Type mismatch"

Debug.Print VBA.Error(13) 

49) Can you describe the 'Error' Statement ?
This statement can be used to simulate the occurrence of an error and is only available for backwards compatibility.
Err.Raise replaces this statement and should be used instead.

Error 13         'type mismatch error  
Error 9999 'application-defined or object-defined error

Call Error(13) 'does not work and will generate an error

Err.Raise(13)
Err.Raise(9999)

50) Is it possible to create your own user defined errors ?
You can create error values using the CVERR function.

Dim MyError As Variant 
MyError = VBA.CVErr(13)
Debug.Print VBA.TypeName(myError) 'Error
Debug.Print VBA.VarType(myError) '10

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

Debug.Print VBA.IsError(MyError)    'True  

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

52) What is displayed in the Immediate Window ?

Public Sub MySubroutine() 
    Debug.Print VBA.TypeName(MyFunction1) 'displays Empty
    Debug.Print VBA.TypeName(MyFunction2) 'displays Empty
    Debug.Print VBA.TypeName(MyFunction3) 'displays Empty
End Sub

Public Function MyFunction1()
End Function

Public Function MyFunction2() As Variant
End Function

Public Function MyFunction3() As Variant
Dim myVariant As Variant
   MyFunction3 = myVariant 'defaults to Empty
End Function

A Function always has a value returned from it.
If the data type is not explicitly provided then a Variant data type will be used.


53) What is displayed in the Immediate Window ?

Public Sub MySubroutine() 
   Debug.Print MyFunction1 'displays 100
   Debug.Print MyFunction2 'displays 0

   Dim myObject As Object
   Set myObject = MyFunction3
   Debug.Print (myObject Is Nothing) 'displays True
End Sub

Public Function MyFunction1() As Long
   MyFunction1 = 100
End Function

Public Function MyFunction2() As Long
   Exit Function
   MyFunction2 = 100
End Function

Public Function MyFunction3() As Object
End Function

The first function returns 100 because this is the value that is assigned to the function name.
The second function returns 0 because if a value has not been assigned to the function name (before an Exit Function or End Function) the default value for the return data type is passed back.
The third function returns Nothing because when a value is not assigned to the function name the default value for the return data type is passed back. In this case it is Object whose default value is Nothing.
When a function returns a reference data type it must be assigned to a variable using the Set statement.


54) Is it possible to return more than one value from a Function or Subroutine ?
Yes, there are several ways this can be achieved:
*) By returning an array from a function.
*) By returning a collection from a function.
*) By returning a class from a function.

Public Function MyFunction() As String() 
Public Function MyFunction() As VBA.Collection
Public Function MyFunction() As MyClass

*) Passing in several arguments to a subroutine using ByRef.

Public Sub MySubroutine(ByRef Argument1 As String, _ 
                        ByRef Argument2 As String)

*) Passing in an array, which can only be passed by reference.
*) Passing in a collection, which is always passed by reference.
*) Passing in a class, which is always passed by reference.

Public Sub MySubroutine(ByRef vaArray() As String) 
Public Sub MySubroutine(ByRef myCollection As VBA.Collection)
Public Sub MySubroutine(ByRef myClassVariable As MyClass)

55) What is a Static Variable ?
A static variable is a local variable whose value will be preserved within the subroutine or function.
You should always initialise a static variable before using it.

Sub MySubroutine() 
   Static myStatic As Integer
End Sub

56) How is a Static variable different to a Public variable ?
A public variable will preserve its value and will be visible to all the other subroutines and functions.
A static variable is only visible in the subroutine or function where it is declared.


57) How would you declare a Fixed Array of Integers ?
This array contains 10 items: arValues(0) to arValues(9)

Dim arValues1(10) As Integer 
Dim arValues2(0 To 10) As Integer

Debug.Print LBound(arValues1) & "-" & UBound(arValues1) '0-10
Debug.Print LBound(arValues2) & "-" & UBound(arValues2) '0-10

58) How would you declare a Dynamic Array of Integers ?

Dim arValues() As Integer 

59) What is the difference between a Fixed Array and a Dynamic Array ?
A dynamic array can be resized multiple times at run-time using the ReDim statement.
A fixed array cannot be resized and the size is defined when it is declared.


60) Is it possible to resize a Dynamic Array ?
Yes. You can use the ReDim statement.
This can only change the size of the upper bound (not the lower bound).
You can only change the size of the last dimension if the array has multiple dimensions.

Dim arValues() As Integer 
ReDim arValues(2)
arValues(1) = 10
arValues(2) = 20
ReDim arValues(6)

61) Is it possible to resize a Dynamic Array without losing the current values ?
Yes. You can use the keyword Preserve.

ReDim Preserve arValues(6) 

62) How would you initialise an Array of Integers ?
You can either initialise one by one or inside a loop.

Dim arValues(3) As Integer 
arValues(0) = 10
arValues(1) = 20
arValues(2) = 30
arValues(3) = 40

63) Is it possible to use the ARRAY function to initialise an array ?
Yes. The Array must be declared with a Variant data type.
ARRAY - Returns an array containing the given values.

Dim arValues() As Variant 
arValues = VBA.Array(10,20,30,40)

64) Is it possible to initialise an Array without looping ?
Yes. This code will initialise the array to contain the value 8.
In Excel VBA you can use a combination of the Evaluate method and the IF and ISERROR functions.

Dim arValues(3) As Integer 
arValues = Application.Evaluate("IF(ISERROR(A1:D1), 8, 8)")

65) What is the difference between the following two lines of code ?

Dim arValues() As Variant 
Dim arValues As Variant

The first line declares an array of the data type Variant.
The second line declares a Variant data type which can hold any data type (including an array).


66) What function would you use to quickly filter an array of values based on a substring ?
FILTER - Returns an array containing a subset of values that contain a substring (Variant).
The filtered array contains all the items that contain the letter "e".

Dim arValues(2) As String 
Dim arFiltered As Variant

arValues(0) = "mon"
arValues(1) = "tue"
arValues(2) = "wed"

arFiltered = VBA.Filter(arValues, "e")
'arFiltered(0) = "tue"
'arFiltered(1) = "wed"

67) Is it possible to start an array at index position 1 (instead of 0) ?
Yes there are two ways this can be done.
You can explicitly define the base of the array.

Option Base 0  'default  
Dim arValues2(1 To 10) As Integer
Debug.Print LBound(arValues2) & "-" & UBound(arValues2) '1-10

You can place Option Base 1 at the top of a module.
The Option Base statement only affects the module it is declared in.

Option Base 1 
Dim arValues2(10) As Integer
Debug.Print LBound(arValues2) & "-" & UBound(arValues2) '1-10

68) How can you tell if an Array is 0 or 1 based ?
You can use the LBOUND function to return the lower bound of the array.

If (VBA.LBound(arValues) = 0) Then 
If (VBA.LBound(arValues) = 1) Then

69) How many items are there in this Fixed Array ?

Option Base 0 
Dim arValues(3) As Integer

There are four items: arValues(0), arValues(1), arValues(2) and arValues(3).


70) How many items are there in this Fixed Array ?

Option Base 1 
Dim arValues(3) As Integer

There are three items: arValues(1), arValues(2) and arValues(3).


71) How would you declare a 2-Dimensional Fixed Array ?

Dim arValues(1 to 10, 1 to 4) As String 

72) How would you declare a 2-Dimensional Dynamic Array ?

Dim arValues( , ) As String 

73) What would the syntax be for passing a Fixed Array into a subroutine ?

Public Sub MySubroutine(ByRef arValues() As String) 
Public Sub MySubroutine(ByRef arValues As Variant)
Public Sub MySubroutine(ByVal arValues As Variant)

74) Is there any difference between the following two lines of code ?

Public Sub MySubroutine(ByRef arValues() As Integer) 
Public Sub MySubroutine(ByVal arValues() As Integer) 'compile error

The first line will pass in an array by reference.
The second line will generate a compile error because arrays cannot be passed by value. They must always be passed by reference.


75) What would the syntax be for returning a Fixed Array from a function ?

Public Function MyFunction() As String() 
Public Function MyFunction() As Long()
Public Function MyFunction() As Variant

76) What is a Collection object ?
A collection is a built-in object that you can use to represent a group of objects.

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

77) 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 MyDictionary As Scripting.Dictionary 
Set MyDictionary = New Scripting.Dictionary
MyDictionary.Add "key1", "Item_1"
MyDictionary.Add "key2", "Item_2"
MyDictionary.Add "key3", "Item_3"

78) 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) but 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 vaKeyValues As Variant 
vaKeyValues = 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.

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

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


80) What is the difference between Early Binding and Late Binding ?
Early binding allows for compile-time checking and requires you to explicitly declare the data type of the object.
Late binding relies on run-time checking and uses the generic Object data type.


81) What is the difference between a Do-While loop and a Do-Until loop ?
In a Do-While loop the condition must be True.
In a Do-Until loop the condition must be False.


82) Write a Do-While loop that is executed at least once ?

Do 
   statements
Loop While boolean-expression = True

83) Write a Do-Until loop that has the condition at the top ?

Do Until boolean-expression = False 
    statements
Loop

84) What does 'On Error Resume Next' mean ?

On Error Resume Next 

Adding this line above your code will mean that if an error is encountered it will be ignored and execution will continue to the next line.
The scope of this statement is to the end of the subroutine or function (Exit Sub, End Sub, Exit Function, End Function).
If another On Error statement is reached any subsequent errors will be handled differently.
Assuming your (Tools > Options)(General Tab, "Break on All Errors") is not selected.

On Error Resume Next 

Dim lNumber As Long
Dim sText As String
lNumber = "100 / 0"
Debug.Print "ignores the first error"
sText = 100 / 0
Debug.Print "also ignores the second error"

85) What does 'On Error GoTo 0' mean ?

On Error GoTo 0 

Resets the Err object and resets the Err.Number to zero.
Disables any enabled error handling in the current subroutine or function.
If you want any error handling after this statement it must be re-enabled.


86) What does 'On Error GoTo -1' mean ?
Clears the error handling (Err object) allowing you to define another error trap.

On Error GoTo -1 

87) When debugging what do the following shortcut keys do ?
(F5) - Runs or continues in Debug Mode.
(F8) - Step Into, Moves to the next line of code or into a subroutine.
(Shift + F8) - Step Over, Moves to the next line of code or runs the whole subroutine.
(Ctrl + Shift + F8) - Step Out, Executes all the remaining lines of code in the current subroutine.


88) When debugging have you ever used the 'Break on All Errors' option ?
This option can be found on the (Tools > Options)(General Tab) dialog box.
If checked any errors cause the project to enter break mode, whether or not an error handler is active and whether or not the code is in a class module.


89) What function would you use to determine if a file exists ?
You can use the DIR function to try to return the name of a file or directory.

If (VBA.Dir("C:\temp\myfile.xla") <> VBA.Constants.vbNullString) Then 
   Call VBA.MsgBox("Exists")
End If

You could also use the Microsoft.Scripting.Runtime library.

Set fso = New Scripting.FileSystemObject 
If (fso.FileExists("C:\temp\myfile.txt") = True) Then
   Call VBA.MsgBox("Exists")
End If

90) Write code to write data to a text file using a TextStream ?

Dim fso As Scripting.FileSystemObject 
Dim txTextStream As Scripting.TextStream
Set fso = New Scripting.FileSystemObject

Set txTextStream = fso.CreateTextFile("C:\temp\myfile.txt", True)
txTextStream.Write("some text")
txTextStream.WriteBlankLines(2)
txTextStream.WriteLine("some more text")
txTextStream.Close

Set txTextStream = Nothing
Set fso = Nothing

91) Write code to read data from a text file using a TextStream ?

Dim sMyText As String 
Dim sMyText2 As String

If (fso.FileExists("C:\temp\myfile.text") = False) Then Exit Sub
Set txTextStream = fso.OpenTextFile("C:\temp\myfile.txt", _
   ForReading, True, TriStateUseDefault)

Do Until (txTextStream.AtEndOfStream = True)
   sMyText = sMyText & txTextStream.ReadLine
Loop

sMyText2 = txTextStream.ReadAll
Debug.Print sMyText = sMyText2 'True

txTextStream.Close

92) What is a Type Library and why would you use one ?
A type library is a file that contains a detailed description of an external component.
It contains information about all the objects, methods, properties and constants.
It must be referenced if you want to have Early Binding to the exposed object.


93) How would you add a Type Library to your project ?
You can add a type library using the (Tools > References) dialog box.
References to other application object models or type libraries can be added to allow their objects to be accessible from VBA.


94) What is Object Orientated programming ?
This is a style of programming that is based on objects containing methods, properties and events arranged in a hierarchy.
This type of programming is based around four key features:
Polymorphism - Create routines that can operate on objects of different types. This is handled by late binding and multiple interfaces.
Encapsulation - Hide parts of the implementation and hide complexity. This is achieved using access modifiers.
Inheritance (Interface) - (Public Inheritance) Define methods (without implementation) that must be declared in a derived class.
Inheritance (Implementation) - (Private Inheritance) Inherit method implementation from a base class.


95) Is VBA an Object Oriented programming language ?
No. It does not have implementation inheritance which is the ability to inherit method implementation from a base class.
You can incorporate interface Inheritance by using the Implements keyword.
You can incorporate Polymorphism by using late binding and the Object data type.
You can incorporate Encapsulation by using the private access modifier inside a class.


96) What is a Class module and how is it different from a Standard module ?
A class module is a special type of module that allows you to create your own customised objects with their own methods, properties and events.
Class module data exists for each instance of the class.
A standard module can contain subroutines and functions which can be accessed from anywhere in the project.
Standard modules are used to store variables, constants and declarations (APIs).


97) How would you create a class in VBA ?
A class module lets you create your own classes and interfaces.
The module name is the name of the class or interface.
Userforms are a type of class module although they behave slightly differently to the ones that you can create.


98) What is an Interface class ?
An interface class defines a set of declarations (properties and methods) which have to be implemented in a class.


99) Can you define an Interface that contains one read/write property and one method ?
Class module - MyInterface.

Public Property Get Property_Name() As String 
End Property

Public Property Let Property_Name(ByVal sValue As String)
End Property

Public Sub MyMethod()
End Sub

100) What is the difference between Property Let and Property Set ?
Let is for value data types
Set is for reference data types

Public Property Let Property_Name(ByVal sValDataType As String) 
   valField = sValDataType
End Property

Public Property Set Property_Name(ByVal oRefDataType As Object)
   Set privateField = oRefDataType
End Property

101) When would you use the 'Implements' keyword ?
Class module - MyClass

Implements MyInterface 

Public Sub MyMethod()
End Sub

Never use Implements behind an Excel Worksheet.


102) Can you describe the Class_Initialize and Class_Terminate subroutines ?
Class_Initialize - This event will occur when an instance of a class is created.

Set ref1 = New MyClass 

Class_Terminate - This event will occur when an instance of a class is destroyed.

Set ref1 = Nothing 

103) What is a Userform/Form and why would you use one ?
Userforms are custom user interface screens.
A userform is a dialog box that can be used for data entry or displaying information.
A userform is a type of class module.
When you add a Userform to a project the following reference is added automatically.
Microsoft Forms 2.0 Object Library.


104) How would you implement a Feature Switch in VBA ?
Compilation Constants
#VBA7


105) Useful VBA Functions
ENVIRON - Returns information about the current operating system environment (String).


106) Working with XML
??


107) What does ODBC stand for ?
ODBC - Open DataBase Connectivity
This is an open standard interface API for accessing any relational database on any operating system.


108) What is an ODBC Driver ?
A database driver is a piece of software that is installed to allow communication between a client application and a relational database that supports ODBC.
You can have multiple ODBC drivers installed which can target different types of relational databases.
These drivers can be managed from the ODBC Data Source Administrator dialog box.


109) What does DNS stand for and how it used ?
DNS - Data Source Name
A .dns file contains database connection information in a text file to provide flexibility and easy maintenance.
There are three different types:
User DNS -
System DNS -
File DNS -


110) What does DAO stand for ?
DAO - Data Access Objects
ACE - Access Connectivity Engine
Also known as ACEDAO, DAO or JET.
In Access 2007 the name was changed from DAO to ACE to correspond with a new version that introduced new functionality and a new file extension (.ACCDB).


111) Can you describe the main objects in DAO ?
Workspace - Contains information about the current session and open databases.
Connection - Contans information about the ODBS data source.
Database - Represents an open database.
Recordset - Contains a group of records returned from the data source.


112) What is the difference between the DAO 3.6 and Database Engine Object references ?
Office 2016 uses "Microsoft Office 16.0 Access Database Engine Object"
Office 2013 uses "Microsoft Office 15.0 Access Database Engine Object"
Office 2010 uses "Microsoft Office 14.0 Access Database Engine Object"
Office 2007 uses "Microsoft Office 12.0 Access Database Engine Object"
Office 2003 used "Microsoft Office DAO 3.6 Object Library"


113) Write the code to connect to the Northwind Access database using DAO and execute a SQL statement ?

Dim dbDAODatabase As DAO.Database 
Dim dbDAORecordset As DAO.Recordset
Dim sSQLQuery As String

sSQLQuery = "SELECT * FROM Customers"
Set dbDAODatabase = DAO.DBEngine.OpenDatabase("C:\temp\northwind.accdb")
Set dbDAORecordset = dbDAODatabase.OpenRecordset(sSQLQuery)

Set dbDAORecordset = Nothing
Set dbDAODatabase = Nothing

114) Can you use DAO to connect to a SQL Server database ?
Yes. Although the preferred method is ADO.

Dim sConnectionString As String 
Dim dbDAOWorkspace As DAO.Workspace
Dim dbDAOConnection As DAO.Connection
Dim dbDAODatabase As DAO.Database
Dim dbDAORecordset As DAO.Recordset

Set dbDAOWorkspace = CreateWorkspace("", "admin", "", dbUseODBC)
sConnectionString = "ODBC;DATABASE=MyDB;DSN=MYDSN"
Set dbDAOConnection = dbDAOWorkspace.OpenConnection _("", , ,sConnectionString)
Set dbDAODatabase = dbDAOConnection.Database
Set dbDAORecordset = dbDAODatabase.OpenRecordset(sSQLQuery, dbOpenDynamic)

115) What does ADO stand for ?
ADO - ActiveX Data Objects - also known as ADODB
This is used to communicate with an external data source (database).
ADO has a flat object model.
This has nothing in common with ADO.NET and is not receiving any more current development.
Can access both relational and non-relational data sources.


116) Can you describe the main objects in ADO ?
Connection - Contains information about the OLE DB data source.
Command - Contains the instructions either SQL or stored procedures.
Parameter - Contains any additional parameters that are required by the Command object.
Recordset - Contains a group of records returned from the data source.


117) Write the code to connect to the Northwind Access database using ADO and execute a SQL statement ?

Dim dbADORecordset as ADODB.Recordset 
Dim sConnectionString as String
Dim sSQLQuery as String

sConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=C:\temp\northwind.accdb"
sSQLQuery = "SELECT * FROM Customers"

Set dbADORecordSet = New ADODB.Recordset
dbADORecordSet.Open sSQLQuery, sConnectionString

Set dbADORecordset = Nothing

118) Write the code to connect to a SQL Server database using ADO and execute a stored procedure that takes one argument and returns a recordset ?

Dim dbADOConnect As ADODB.Connection 
Dim dbADOCommand As ADODB.Command
Dim dbADORecordset As ADODB.Recordset
Dim dbADOParameter As ADODB.Parameter
Dim sConnectionString As String
Dim vaReturnValues As Variant

sConnectionString = "------------------------"
Set dbADOConnect = New ADODB.Connection
dbADOConnect.Open sConnectionString, sUserID, sPassword, adConnectUnspecified
dbADOConnect.CursorLocation = ADODB.adUseClient

Set dbADOCommand = New ADODB.Command
dbADOCommand.CommandText = "ProcedureName"
dbADOCommand.CommandType = ADODB.dbCmdStoredProc
dbADOCommand.ActiveConnection = dbADOConnect

Set dbADOParameter = New ADODB.Parameter
dbADOParameter = dbADOCommand.CreateParameter("ReturnedName", _
                              datatype, ADODB.adParamOutput, size)
dbADOCommand.Parameters.Append dbADOParameter
Set dbADOParameter = Nothing

Set dbADOParameter = New ADODB.Parameter
dbADOParameter = dbADOCommand.CreateParameter("ParameterName1", _
                              datatype, ADODB.adParamInput, size, "ParameterValue1")
dbADOCommand.Parameters.Append dbADOParameter
Set dbADOParameter = Nothing

Set dbADORecordSet = New ADODB.Recordset
Set dbADORecordSet = dbADOCommand.Execute
vaReturnValues = dbADOCommand("ReturnedName")

Set dbADORecordset = Nothing
Set dbADOCommand = Nothing
Set dbADOConnect = Nothing

119) What is the quickest way to display a recordset on a worksheet ?
You can use the CopyFromRecordset property assuming the recordset does not contain any OLE object fields or array data such as hierarchical recordsets.

Range("A1").CopyFromRecordset dbRecordSet 

This works with both ADO and DAO Recordsets.


120) If you want to communciate with an Access database should you use ACE or ADO ?
ACE is Microsoft's preferred approach and is the default used in Access 2007 and later.


121) What is the Web Services ToolKit ?
Introduced in Office 2002 it is a tool that generates the corresponding VBA classes to allow you to access a SOAP web service.
This installed an additional menu (Tools > Web Service Reference) inside your VBA Editor.
This tool is not supported by Microsoft and was removed in Office 2007.
The alternative is to use .NET Web Services alongside the Office Developer Tools in Visual Studio.


122) Can you describe the 'Application.ExportXML' method in Access ?
This allows you to export data in XML format.


123) Would you need to make any changes to your code if you were running 64-bit Office ?



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