Excel VBA Flashcards
What statement must be added in the module to ensure that variable declaration is required?
Option Explicit
What is the excel object model?
a clearly defined set of objects that are structured by their relationship to each other
What are two ways to reference a worksheet in vba?
By it’s worksheet name or by the index number:
Worksheets(1).Select
Worksheets(“MySheet”).Select
What is object property?
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
What are methods in VBA?
Methods are the actions the can be performed on an object.
What is the cells property?
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
What are two ways to test an expression
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
True or False? Are variable names case-sensitive in VBA?
False
What does a data type specify?
Specifies how data is stored in memory
What is string data type used for?
A string data type is used for storing text
What data type is typically used for processing numbers without losing any precision?
The double data type
What data type is typically used for processing numbers for values less than 32,767?
The Long data type
If a data type is not declared what is the default data type assigned?
The variant data type
What is a local variable?
a variable declared within a procedure. When the procedure ends, the variable no longer exists.
What are public variables?
public variables are variables that are available to all the procedures in all the VBA modules.
What is an array and how is it declared in vba?
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.
What is a dynamic array and how is it declared in vba?
A dynamic array doesn't have a preset number of elements. It is declared using the following syntax: Dim MyArray( ) As Integer.
What statement is used when declaring a dynamic array in VBA?
In order to use a dynamic array in your code, the ReDim statement must be used ReDim MyArray (1 to x).
When declaring the array size with dynamic arrays how do you ensure the array’s existing dimensions are not destroyed?
Use the preserve statement in conjunction with the ReDim statement as shown below:
ReDim Preserve MyArray (1 to y)
How do you use a worksheet function in VBA?
Worksheet function can by using the following statement:
Application.WorksheetFunction.Sum
Sum is an example of a function found in the excel application
What is the syntax for the MsgBox Function in VBA?
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.
