CP212 - VBA Final Exam Questions With
Correct Answers
What are the 3 rules of working with arrays? - ANSWER-1. You must indicate a base
type for the array
2. You must indicate the number of elements in the array
3. You must indicate what index you want to begin with
What is the default first index of an array? - ANSWER-0
What is the option based statement? - ANSWER-A statement that lets you change the
base (first) index of the array
ex. Option Base 1 changes the first index to 1
Where should the option based statement be placed? - ANSWER-At the top of each
module
What are two ways of overriding the 0-based indexing of an array? - ANSWER-1. Using
an option based statement
2. Explicitly declaring how the array will be indexed
ex. Dim employee (1 to 100) as String
How many elements will you get if you:
- do not use option base 1
- declare an array as "salary(100)" - ANSWER-101
How can you declare an array with an unknown size? - ANSWER-Declare that you
need an array. In the Dim statement, put empty parentheses next to the variable name
ex. Dim employee() as String
How many times can you use Redim to readjust the size of the array? - ANSWER-As
many times as you like
ex. Redim employee (1 to nEmployees)
What 2 things happen when you use the Redim statement? - ANSWER-1. The size of
the array is adjusted
2. The previous contents of the array are deleted?
,How can you override the default behaviour of Redim which deletes the array contents?
- ANSWER-Using the keyword preserve
ex. Redim Preserve employee (1 to nEmployees)
Do you need to declare the number of each element when working with
multidimensional arrays? - ANSWER-Yes
ex. Dim employee (1 to 10, 1 to 100) As String
What does the keyword Array (the Array function) do? - ANSWER-Used to populate the
list with variables that are in the Array parameters
ex. days = Array("Mon","Tue","Wed")
What must the array variable be declared as in order to use the Array keyword? -
ANSWER-Variant
What does it mean if a sub is public? - ANSWER-Any other sub in the entire project can
call the sub
What does it mean if a sub is private? - ANSWER-The sub is only callable within its
module
What are the 3 reasons as to why long subs are bad? - ANSWER-1. Hard to read
2. Hard to debug
3. Difficult to reuse code in other programs
What are modularizing programs? - ANSWER-Programs with a sequence of relatively
short subs, each of which performs a specific task
What are the 3 advantages to modularizing programs? - ANSWER-1. Easier to read
2. Easy to debug
3. More likely able to be used in other programs
Do you need to use the keyword Call to invoke another sub? - ANSWER-No
What are the two ways of invoking a sub from another sub? - ANSWER-1. Using the
Call keyword
ex. Call sub_Name
2. Omitting it
ex. sub_Name
, What is passing by reference? - ANSWER-The default method of passing a variable
from one sub to the other where you explicitly declare you want to pass the variable by
reference using the keyword ByRef
ex. Sub DisplayName (ByRef lastName as String, ByRef firstName as String)
What is the passing arguments method? - ANSWER-Calling a sub with arguments
which should be locally declared
ex. Call subName(argument1, argument2)
Why is the passing arguments method favoured over the module level variables
method? - ANSWER-The sub would be totally self contained
- code can be reused because it is not dependent on module level variables
What is the module level variables method? - ANSWER-Using variables that are
declared at a module level which are used in the main sub which can be accessed by
other subs
What is the by value method? - ANSWER-Using variables by copying it and sending it
to the called sub sot that changes made there to the variable will not be reflected in the
calling sub
ex. Sub DisplayName (ByVal lastName as String, ByVal firstName as String)
What does the UBounds keyword do? - ANSWER-It returns the largest index in the
array
What are the 2 differences between a function sub and a regular sub? - ANSWER-1. It
begins and ends with (Function, End Function) instead of (Sub, End sub)
2. It returns a certain type of variable which is ASSIGNED to its name (whereas a sub
has no returns)
What are two ways of using function subroutines? - ANSWER-1. Calling it from another
sub
2. Used as a new function in an excel formula
Will you be able to access a function in workbook 2 if it was written in workbook 1? -
ANSWER-No, because the workbook only recognizes functions written in its own
modules
How can you get access to functions of workbook1 in workbook2? - ANSWER-Setting a
reference to workbook1 in workbook2
Are event handler subs public or private by default? - ANSWER-Private
Correct Answers
What are the 3 rules of working with arrays? - ANSWER-1. You must indicate a base
type for the array
2. You must indicate the number of elements in the array
3. You must indicate what index you want to begin with
What is the default first index of an array? - ANSWER-0
What is the option based statement? - ANSWER-A statement that lets you change the
base (first) index of the array
ex. Option Base 1 changes the first index to 1
Where should the option based statement be placed? - ANSWER-At the top of each
module
What are two ways of overriding the 0-based indexing of an array? - ANSWER-1. Using
an option based statement
2. Explicitly declaring how the array will be indexed
ex. Dim employee (1 to 100) as String
How many elements will you get if you:
- do not use option base 1
- declare an array as "salary(100)" - ANSWER-101
How can you declare an array with an unknown size? - ANSWER-Declare that you
need an array. In the Dim statement, put empty parentheses next to the variable name
ex. Dim employee() as String
How many times can you use Redim to readjust the size of the array? - ANSWER-As
many times as you like
ex. Redim employee (1 to nEmployees)
What 2 things happen when you use the Redim statement? - ANSWER-1. The size of
the array is adjusted
2. The previous contents of the array are deleted?
,How can you override the default behaviour of Redim which deletes the array contents?
- ANSWER-Using the keyword preserve
ex. Redim Preserve employee (1 to nEmployees)
Do you need to declare the number of each element when working with
multidimensional arrays? - ANSWER-Yes
ex. Dim employee (1 to 10, 1 to 100) As String
What does the keyword Array (the Array function) do? - ANSWER-Used to populate the
list with variables that are in the Array parameters
ex. days = Array("Mon","Tue","Wed")
What must the array variable be declared as in order to use the Array keyword? -
ANSWER-Variant
What does it mean if a sub is public? - ANSWER-Any other sub in the entire project can
call the sub
What does it mean if a sub is private? - ANSWER-The sub is only callable within its
module
What are the 3 reasons as to why long subs are bad? - ANSWER-1. Hard to read
2. Hard to debug
3. Difficult to reuse code in other programs
What are modularizing programs? - ANSWER-Programs with a sequence of relatively
short subs, each of which performs a specific task
What are the 3 advantages to modularizing programs? - ANSWER-1. Easier to read
2. Easy to debug
3. More likely able to be used in other programs
Do you need to use the keyword Call to invoke another sub? - ANSWER-No
What are the two ways of invoking a sub from another sub? - ANSWER-1. Using the
Call keyword
ex. Call sub_Name
2. Omitting it
ex. sub_Name
, What is passing by reference? - ANSWER-The default method of passing a variable
from one sub to the other where you explicitly declare you want to pass the variable by
reference using the keyword ByRef
ex. Sub DisplayName (ByRef lastName as String, ByRef firstName as String)
What is the passing arguments method? - ANSWER-Calling a sub with arguments
which should be locally declared
ex. Call subName(argument1, argument2)
Why is the passing arguments method favoured over the module level variables
method? - ANSWER-The sub would be totally self contained
- code can be reused because it is not dependent on module level variables
What is the module level variables method? - ANSWER-Using variables that are
declared at a module level which are used in the main sub which can be accessed by
other subs
What is the by value method? - ANSWER-Using variables by copying it and sending it
to the called sub sot that changes made there to the variable will not be reflected in the
calling sub
ex. Sub DisplayName (ByVal lastName as String, ByVal firstName as String)
What does the UBounds keyword do? - ANSWER-It returns the largest index in the
array
What are the 2 differences between a function sub and a regular sub? - ANSWER-1. It
begins and ends with (Function, End Function) instead of (Sub, End sub)
2. It returns a certain type of variable which is ASSIGNED to its name (whereas a sub
has no returns)
What are two ways of using function subroutines? - ANSWER-1. Calling it from another
sub
2. Used as a new function in an excel formula
Will you be able to access a function in workbook 2 if it was written in workbook 1? -
ANSWER-No, because the workbook only recognizes functions written in its own
modules
How can you get access to functions of workbook1 in workbook2? - ANSWER-Setting a
reference to workbook1 in workbook2
Are event handler subs public or private by default? - ANSWER-Private
