Excel VBA

Question 1

What statement must be added in the module to ensure that variable declaration is required?

Answer 1

Option Explicit

Question 2

What is the excel object model?

Answer 2

a clearly defined set of objects that are structured by their relationship to each other

Question 3

What are two ways to reference a worksheet in vba?

Answer 3

By it’s worksheet name or by the index number:
Worksheets(1).Select
Worksheets(“MySheet”).Select

Question 4

What is object property?

Answer 4

An object property is a characteristic of an object. For example:
Worksheet(“Sheet1”).Name = “MySheet”
In the above code the “Name” is property of the worksheet object

Question 5

What are methods in VBA?

Answer 5

Methods are the actions the can be performed on an object.

Question 6

What is the cells property?

Answer 6

The cells property allows the user to specify a value in worksheet object. It takes two inputs, a row and column index, in order to determine the cell reference in the workbook. For example:
Worksheets(“Sheet1”).Cells(1, 2) = 9
The above code specifies that cell B1 ( row 1 column 2) = 9

Question 7

What are two ways to test an expression

Answer 7

Use the debug.print function in your sub procedure followed by the expression as shown below:
Debug.Print Range(“A1”).Value
Or use the immediate window using syntax shown below:
? Range(“A1”).Value

Question 8

True or False? Are variable names case-sensitive in VBA?

Answer 8

False

Question 9

What does a data type specify?

Answer 9

Specifies how data is stored in memory

Question 10

What is string data type used for?

Answer 10

A string data type is used for storing text

Question 11

What data type is typically used for processing numbers without losing any precision?

Answer 11

The double data type

Question 12

What data type is typically used for processing numbers for values less than 32,767?

Answer 12

The Long data type

Question 13

If a data type is not declared what is the default data type assigned?

Answer 13

The variant data type

Question 14

What is a local variable?

Answer 14

a variable declared within a procedure. When the procedure ends, the variable no longer exists.

Question 15

What are public variables?

Answer 15

public variables are variables that are available to all the procedures in all the VBA modules.

Question 16

What is an array and how is it declared in vba?

Answer 16

a group of elements of the same type that have a common name. Declared using the following syntax:
Dim MyArray(100) As Interger. The 100 within the parenthesis refers to the array size.

Question 17

What is a dynamic array and how is it declared in vba?

Answer 17

A dynamic array doesn't have a preset number of elements. It is declared using the following syntax:
Dim MyArray( ) As Integer.

Question 18

What statement is used when declaring a dynamic array in VBA?

Answer 18

In order to use a dynamic array in your code, the ReDim statement must be used
ReDim MyArray (1 to x).

Question 19

When declaring the array size with dynamic arrays how do you ensure the array’s existing dimensions are not destroyed?

Answer 19

Use the preserve statement in conjunction with the ReDim statement as shown below:
ReDim Preserve MyArray (1 to y)

Question 20

How do you use a worksheet function in VBA?

Answer 20

Worksheet function can by using the following statement:
Application.WorksheetFunction.Sum
Sum is an example of a function found in the excel application

Question 21

What is the syntax for the MsgBox Function in VBA?

Answer 21

MsgBox(prompt[, buttons][,title][,helpfile,context])

prompt: Required. The message display in the pop-up display
buttons: Optional. A value that specifies which buttons and which icons, if any appear in the message box. Use built-in constants - for example, vbYesNo.

Title: Optional. The text that appears in the message box’s title bar. The default is Microsoft excel.