VBA Flashcards
(140 cards)
1
Q
What is a programming language?
A
- A system of statements used to control a computer.
2
Q
What is required of a programming language for it to be able to be manipulated a machine?
A
It needs to:
- Accept input: from external data or from the user;
- Store data;
- Manipulate data i.e. transform it;
- Produce output.
3
Q
Visualise the Object-Oriented Programming paradigm.
A
4
Q
List the top 3 levels (from the top down) of the Excel Object Model.
A
- Application Object;
- Workbook Object;
- Worksheet Object
Everything in the Excel program (down to individual cells) is an object (within another object and/or containing other objects).
5
Q
What are some properties, methods and events of the Application Object?
A
- Properties: Version; Caption; Default file path; Workbook collection…
- Methods: Calculate; CheckSpelling; Quit…
- Events: SheetCalculate; NewWorkbook; WorkbookBeforeClose…
6
Q
What are some properties, methods and events of the Workbook Object?
A
- Properties: Name; Sheets (collection); Saved…
- Methods: Close; PrintOut; Save…
- Events: BeforeClose; NewSheet; BeforeSave…
7
Q
What are some properties, methods and events of the Worksheet Object?
A
- Properties: Name; UsedRange; Visible…
- Methods: Activate; Copy; Delete…
- Events: Activate; Deactivate; Change…
8
Q
On which tab can the “Macros” dialogue box be found?
A
View
9
Q
What’s the keyboard shortcut that changes between the VBA editor and the Excel workbook?
A
Alt+F11
10
Q
What’s the keyboard shortcut that runs the code after you mouse-click inside a Sub?
A
F5
11
Q
What’s the keyboard shortcut that opens the Project Explorer pane?
A
Ctrl+R
12
Q
What’s the keyboard shortcut that opens the Excel Object Browser?
A
F2
13
Q
In the VBA Object Browser, what image appears besides a property?
A
14
Q
In the VBA Object Browser, what image appears beside an action?
A
15
Q
In the VBA Object Browser, what image appears beside an event?
A
16
Q
What’s best practice with regards to the number of workbooks open while using VBA?
A
One workbook open at a time.
17
Q
Where can the VBA Project Properties dialogue box be found? What can you do with it?
A
In the Tools tab of the VBA editor.
Here you can change the name of the project and add a discription to it.
You can also set a password protection of your code.
18
Q
Where can you protect visibility of your VBA code from others?
A
Tools > VBAProject - Project Properties > Protection
Make sure you don’t forget your password.
19
Q
What’s the keyboard shotcut to close an excel file?
A
Ctrl+W
20
Q
What’s the keyboard shotcut to open an excel file?
A
Ctrl+O
21
Q
What’s the keyboard shotcut to save an excel file?
A
Ctrl+S
22
Q
What are the three types of module in VBA?
A
Object modue;
Standard module;
and
Class module;
23
Q
How can the code of a module be exported?
A
Right-click a module, select “export file” and then save as either a .bas (basic file) or a .txt (simple text file).
24
Q
What are the two main types of procedure you can create in VBA?
A
Sub routines;
and
Functions.
25
What are the differences between a Sub Routine and a Function?
Sub Routines can manipulate information in objects and perform calculations but cannot be part of formulas.
Functions can be used in formulas e.g. SUM or SUMIF.
26
Visualize a Sub Routine called Commission that assignes the value in cell C7 to a variable called Sale and the multiplies this by the value of a variable called Rate.
Store the result in a variable called Commission and produce a message box with the value.
27
When you create a function in VBA, how do you assign the result of a function?
You assign the name of the result of the function to the name of the function.
28
"Visualize a Sub Routine called Commission that assignes the value in cell C7 to a variable called Sale and the multiplies this by the value of a variable called Rate.
Store the result in a variable called Commission and produce a message box with the value."
State the limitation of the above Sub Routine and visualise a Function that does the same.
You can only use the Sub Routine for a few variables at a time by using loops.
It is more efficient to use a function.
29
How do you decide between the use of a function and Sub Routine?
If the procedure is to be used in a formula, use a Function; if not, use a Sub Routine.
30
When code is written under the "Modules" folder or the "This Workbook" code module in the VBA Project pane, what's the "Excel" assumption?
Excel assumes that it is available to any open workbook.
31
What's the keyboad shortcut to create a new workbook when on is already open?
Ctrl+N
32
How is a procedure made only available to the workbook in which it is created?
By putting the word "Private" before the Sub Routine or Function declaration.
33
What will "Sheet" and "This Workbook"-level modules usually be used for?
Where will normal Sub Routines ussually go?
Event handling procedures only.
The rest of the procedures usually go under the Standard modules.
34
What type of procedures cannot be put inside Sheet-level modules?
Functions.
35
How are comments added in VBA?
What's best practice with regards to the frequency of comments?
By typing and apostrophe ( ' ) before a sentence.
Comment often and clearly.
36
How can macros be ran through shape-buttons?
Insert tab \> Illustrations \> Shapes
Select a shape (and label it) and then right-click it and select "Assign Macro...".
Select the module you want to assign to it.
37
How do you add a Macro button to the quick-access tool bar?
File \> Options \> Quick Access Tool Bar \> (Choose Commands From) Macros (Select Macro) \> Add
38
How do you add a macro to the Ribbon?
Select the tab you wish for it to be in and...
39
What is a good shortcut technique to writting VBA code?
Record the macro then lapidate the resulting code as you see fit.
40
What are the 11 common VBA data types?
Byte: 0 to 255;
Boolean: True or False (0 or 1);
Integer: -32,768 to 32,767;
Long: -2.14 billion to 2.14 billion;
Single (six decimal places): very big range;
Double (fourteen decimal places): astronomical range;
Currency(4 decimal places): +/- 922 trilion;
Decimal(28 decimal places);
Date: from January 1st, 100 to December 31, 9999;
Object: An object reference (e.g. a worksheet or a range of cells);
Variant: allows any type of data.
41
Why should you use specific data types in VBA if the Variant data type is available?
1. Other languages tend to be strongly typed so this is good practice of a tranferable skill;
2. If you use the Variant (generic) data type, you will not benefit from type mismatch error messages and you may not have an anchor from which to investigate why the code is not working.
42
How are variables declared in VBA?
By using the keyword "Dim" (for Dimension) and the variable type.
43
How do you make it a requirement that variables are declared before they are initialised?
By using the key-command "Option Explicit" at the top of your module.
44
What is the correct way to declare multiple variables on the same line?
45
What is the default scope of variables declared inside Sub Routines for Functions?
They have localized scope i.e. they only exist inside that Sub Routine or Function.
46
How are variables made global i.e. accessible to all Sub Routines and Functions in a code module?
What can't you do to it?
Declare it above all Sub Routines and Functions.
You can't assign a value to it outside Sub Routines or Functions.
47
How and where are constant "variables" declared?
Use the keyword "Const" and declare-assign it at the top of the module.
48
What happens to a standard variable after you run it and reset?
How to you rectify that?
What are the limitations of static variables in VBA?
It defaults back to 0.
Create a static variable:
In stead of using the keyword "Dim" use "Static".
The limitations are that the cumulative values are only retained while the workbook is open.
49
What is the VBA symbol for integer division?
what does it do?
\
It divides numbers and eliminates any decimal places from the result.
50
What is the modulo operator in VBA?
Is is the "Mod" keyword rather than a symbol.
51
What symbol is used to concatenate messages with different data types?
&
52
How is a 1-dimention array declared in VBA?
53
How is an array with assigned values declared in VBA?
What data type must be assigned to it?
Variant
54
How is a 2-dimension array declared in VBA?
55
What is a dynamic array?
An array that can be created and thenre-dimenssioned as you see fit.
56
How is a dynamic array declared and manipulated?
57
What happens when you re-dimension an array?
How do you prevent that?
The existing values are erased.
Use the the "Preserve" keyword after "ReDim" and before the variable name.
58
In VBA, what are object variables? Give examples.
Variables that represent objects in the Excel Object Model e.g. cells, cell ranges, worksheets or even workbooks.
59
How do built-in colour names have in common in VBA?
They start with the letters "vb"
60
How can you streamline code referencing in VBA?
Use "With... End With" statements.
61
Give an example of a circumstance in which you'd use a code loop in VBA?
When dealing with cell ranges or arrays.
62
Visualize code that assigns the values in column C to an array and then display them in successive message box prompts.
63
How can you skip values in an array?
Use the "Step" keyword.
64
How you skip values backwards?
65
What data type must be assigned to a variable that is being used to step through a "For Each" loop?
Variant
66
Visualise VBA code that defines 2 variables as workbook and worksheet and then appends the word "test" to the end of each worksheet name.
67
Which loop is used in VBA when you want code to run until a specific condition is met?
The "Do...Loop".
68
Visualise code that uses a standard "Do...Loop" to run until it finds an empty cell down column A.
69
Visualise code that uses a "Do...Loop Until" to run until it finds an empty cell down column A.
70
Visualise code that uses a "Do...Loop While" to run until it finds an empty cell down column A.
71
How you you step through code line-by-line ("debug")?
Press F8
72
How do you stop an infinite loop?
Press Esc or Ctrl+C
73
What does the "IIf" funtion do in VBA code?
It mimicks the very same funtionality and format as the "If" function in Excel.
74
Visualise a simple "If...Then" code line in VBA.
75
Visualise an "If...Then...Else" code line in VBA.
76
Visualise an "If...Then...ElseIf" code line in VBA.
77
Visualize a Case statement in VBA.
78
What happens when there is an error related to data types?
79
What is the default error-handling? In what situation would you use it?
"On Error GoTo 0"
Only used if we intend to change from another error-handling mode back to the default one.
80
How do error handlers work? What happens if there is no error?
If there is no error the code will run until it reaches the "Exit Sub" line.
81
What does the "On Error Resume Next" line do? What are the issues to be weary of?
It stipulates that if an error is found, the code should keep running as best it can.
The issue is that this means that no error message will pop up.
82
What will the below code return?
83
How can other Sub Routines be called inside a Sub Routine?
84
If you're calling Subs inside other Subs, what needs to be done in relation to variables that will be used by all these Subs?
Make the variables global.
85
How do you step over code while "debug-stepping"?
How do you step out?
Shift+F8
Ctrl+Shift+F8
86
How are breakpoints set up in VBA?
Click on the bar to the left of the code line.
87
Why use breakpoints?
To help debug your program.
88
What is the "Immediate" window?
It's a window inside which you can "play" with your code line by line and see what happens in certain situations.
The window will only run the current line on code. It can, however, run multiple commands if they are separated by a colon.
89
What line of code can you use to find out what's the current value of a variable?
Have the "Immediate" window open and use "Debug.Print variable-name" inside the Sub.
90
What's the keyboard shortcut to open the "Immediate" window?
Ctrl+G
91
What does a "Watch" do?
It displays the value of a variable continuously.
92
How do you add a "Watch"?
Debug \> Add Watch... \> Type the name of the variable
93
What is the use of "Watch"?
It helps verify that the code produces the correct results and that it follows the correct path to generate those results.
94
How do you write content into a single Excel cell using VBA code?
95
How can you write code that simultaneously writes to all the cells in the table, below?
96
In Excel, what's the maximum number of active cells that can be had at one time?
1
97
After you run VBA code, what happens if you press Ctrl+Z?
Nothing, as you cannot undo VBA code.
98
Visualise code that selects a range and copies/cuts and pastes it to the active cell in another worksheet.
99
What happens if you use VBA to find a value and it finds nothing?
It returns an error.
100
Visualize code that finds a value that the user inputs.
101
Visualize code that finds a value that the user inputs and also returns the address.
102
What is good practice in relation to the structure of Excel Workbooks?
Every Workbook should have a consistent structure; with each data point positionally fixed in relation to all the others.
103
What VBA function deals with distances between cells?
OFFSET
104
What should be done before using the OFFSET function?
Determine the active cell.
105
Visualize VBA code that finds the first blank line and adds Name, email and suggetion (from another worksheet) to that line.
106
What is the main benefit of using the "Offset" function?
It allows the code to reference 1 active cell rather than having to activate multiple cells; this make the code run much faster.
107
When using IF/CASE statements, which cases should be determined first?
The most restrictive cases should come before less restrictive ones.
108
What sign is used to cocatenate?
&
109
What is good practice in relation to user experience?
Provide feedback through message boxes (prompts) that explain the results or the expectations.
110
Visualize 3 VBA functions: one that returns the first 4 digits of a string, one that returns 2 digits starting with the 5th, and one that returs the leftmost 3.
111
How are Worksheets activated?
By name - Worksheets("Sheet2").Activate
or
By relative position - Worksheets(1).Activate - This is a relative reference so if the positions change, the workseet activation changes.
112
In VBA code, what is the difference between "Sheets" and "Worksheets" objects?
Sheets is broader than Worksheets. Sheets can include any type of sheet - including chartsheets, macrosheets from older versions of Excel etc...
113
Where does a new sheet appear when you use Sheet.Add?
How can you specify where you want the sheet to be added?
Sheets.Add _After:=_ Worksheets("Sheet2")
To the left of the active sheet.
114
How can you specify where you want the sheet to be added?
Use the "After" command -
Sheets.Add _After:=_ Worksheets("Sheet2")
115
How do you add a worksheet to the end of the sheets bar?
Sheets.Add After:=Worksheets(_Sheets.Count_)
116
How can you use VBA to rename a worksheet from "Sheet3" to "Test"?
Worksheets("Sheet3").Name = "Test"
117
How can you use VBA to delete a worksheet called?
Worksheets(Sheet1").Delete
118
Visualise VBA code that disables a "worksheet-delete" display alert and then turns it back on after the delete.
119
How is a workbook open with VBA?
120
How is a workbook activated with VBA?
121
How is a workbook SavedAs with VBA?
122
How is a workbook closed with VBA?
use the ".Close" method
123
When using the Find method in VBA, which argument will search the entire worksheet?
LookAt:=xlWhole
124
In VBA, how to you avoid screen flashing (screen updates)?
Below the variable declarations, code:
Application.ScreenUpdating = False
and
Application.ScreenUpdating = True ;
at the end of your code.
125
In VBA, visualise a sub that uses Excel's built-in functions to randomly selects a prize from an array of 6 elements and return a mesage box with that prize.
126
What code will make this message box appear?
127
How can you discover all the available message box interactive options available in VBA?
128
Visualize VBA code that asks the user to enter the location of the cell to be calculated upon.
129
What type of input box allows for cells to be selected with the mouse pointer?
Type:=8
130
Visualize VBA code that calls 2 sub routines inside another sub routine.
131
What reasons are there for calling sub routines inside other sub routines?
1. To make the code cleaner (putting comments on the subsidiary sub routines)
2. To have one main Sub routine that has access to subsidiary sub routines;
3. Code is easier to maintain i.e. main code is in one place;
4. More portable. One page, one code.
132
Which built-in Excel function selects a random number between two specified numbers?
RandBetween
133
What should be done when you want a event to be read only by a specific Workbook - as opposed to any open Workbook (which can read from any general Module)?
Declare the Sub as "Private":
Private Sub ...()
134
Visualize code that prevents a workbook from closing unless a certain condition is met.
135
Visualize code that runs automatically once a Workbook is open.
136
Visualize the code that asks the user if they are sure they want to save the workbook.
137
What must be the parameters when using the "Worksheet\_Change" event handler?
Private Sub Worksheet\_Change(_ByVal Target As Range_)
138
In vbOkCancel, what values are delivered for each choice?
Ok = 1
Cancel = 2
139
Visualize VBA code that asks the user if they are sure they want to perform a change to a worksheet. If not, the code undoes the change and returns a message saying that the change was undone.
140
What must always accompany a "Workbook\_Activate()" event handler Sub?
A "Workbook\_Deactivate()" event handler Sub.
