VBA Flashcards
What is a programming language?
- A system of statements used to control a computer.
What is required of a programming language for it to be able to be manipulated a machine?
It needs to:
- Accept input: from external data or from the user;
- Store data;
- Manipulate data i.e. transform it;
- Produce output.
Visualise the Object-Oriented Programming paradigm.
List the top 3 levels (from the top down) of the Excel Object Model.
- 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).
What are some properties, methods and events of the Application Object?
- Properties: Version; Caption; Default file path; Workbook collection…
- Methods: Calculate; CheckSpelling; Quit…
- Events: SheetCalculate; NewWorkbook; WorkbookBeforeClose…
What are some properties, methods and events of the Workbook Object?
- Properties: Name; Sheets (collection); Saved…
- Methods: Close; PrintOut; Save…
- Events: BeforeClose; NewSheet; BeforeSave…
What are some properties, methods and events of the Worksheet Object?
- Properties: Name; UsedRange; Visible…
- Methods: Activate; Copy; Delete…
- Events: Activate; Deactivate; Change…
On which tab can the “Macros” dialogue box be found?
View
What’s the keyboard shortcut that changes between the VBA editor and the Excel workbook?
Alt+F11
What’s the keyboard shortcut that runs the code after you mouse-click inside a Sub?
F5
What’s the keyboard shortcut that opens the Project Explorer pane?
Ctrl+R
What’s the keyboard shortcut that opens the Excel Object Browser?
F2
In the VBA Object Browser, what image appears besides a property?
In the VBA Object Browser, what image appears beside an action?
In the VBA Object Browser, what image appears beside an event?
What’s best practice with regards to the number of workbooks open while using VBA?
One workbook open at a time.
Where can the VBA Project Properties dialogue box be found? What can you do with it?
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.
Where can you protect visibility of your VBA code from others?
Tools > VBAProject - Project Properties > Protection
Make sure you don’t forget your password.
What’s the keyboard shotcut to close an excel file?
Ctrl+W
What’s the keyboard shotcut to open an excel file?
Ctrl+O
What’s the keyboard shotcut to save an excel file?
Ctrl+S
What are the three types of module in VBA?
Object modue;
Standard module;
and
Class module;
How can the code of a module be exported?
Right-click a module, select “export file” and then save as either a .bas (basic file) or a .txt (simple text file).
What are the two main types of procedure you can create in VBA?
Sub routines;
and
Functions.
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.
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.
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.
“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.
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.
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.
What’s the keyboad shortcut to create a new workbook when on is already open?
Ctrl+N
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.
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.
What type of procedures cannot be put inside Sheet-level modules?
Functions.
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.
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.
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
How do you add a macro to the Ribbon?
Select the tab you wish for it to be in and…
What is a good shortcut technique to writting VBA code?
Record the macro then lapidate the resulting code as you see fit.
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.
Why should you use specific data types in VBA if the Variant data type is available?
- Other languages tend to be strongly typed so this is good practice of a tranferable skill;
- 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.
How are variables declared in VBA?
By using the keyword “Dim” (for Dimension) and the variable type.
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.
What is the correct way to declare multiple variables on the same line?
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.
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.
How and where are constant “variables” declared?
Use the keyword “Const” and declare-assign it at the top of the module.
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.
What is the VBA symbol for integer division?
what does it do?
\
It divides numbers and eliminates any decimal places from the result.
What is the modulo operator in VBA?
Is is the “Mod” keyword rather than a symbol.
What symbol is used to concatenate messages with different data types?
&
How is a 1-dimention array declared in VBA?
How is an array with assigned values declared in VBA?
What data type must be assigned to it?
Variant
How is a 2-dimension array declared in VBA?
What is a dynamic array?
An array that can be created and thenre-dimenssioned as you see fit.
How is a dynamic array declared and manipulated?
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.
How can you streamline code referencing in VBA?
Use “With… End With” statements.
Visualize code that assigns the values in column C to an array and then display them in successive message box prompts.
How can you skip values in an array?
Use the “Step” keyword.
How you skip values backwards?
What data type must be assigned to a variable that is being used to step through a “For Each” loop?
Variant
Visualise VBA code that defines 2 variables as workbook and worksheet and then appends the word “test” to the end of each worksheet name.
Visualise code that uses a standard “Do…Loop” to run until it finds an empty cell down column A.
Visualise code that uses a “Do…Loop Until” to run until it finds an empty cell down column A.
Visualise code that uses a “Do…Loop While” to run until it finds an empty cell down column A.
What does the “IIf” funtion do in VBA code?
It mimicks the very same funtionality and format as the “If” function in Excel.
Visualise a simple “If…Then” code line in VBA.
Visualise an “If…Then…Else” code line in VBA.
Visualise an “If…Then…ElseIf” code line in VBA.
Visualize a Case statement in VBA.
What happens when there is an error related to data types?
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.
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.
What will the below code return?
How can other Sub Routines be called inside a Sub Routine?
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.
How are breakpoints set up in VBA?
Click on the bar to the left of the code line.
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.
How do you add a “Watch”?
Debug > Add Watch… > Type the name of the variable
How do you write content into a single Excel cell using VBA code?
How can you write code that simultaneously writes to all the cells in the table, below?
Visualise code that selects a range and copies/cuts and pastes it to the active cell in another worksheet.
Visualize code that finds a value that the user inputs.
Visualize code that finds a value that the user inputs and also returns the address.
Visualize VBA code that finds the first blank line and adds Name, email and suggetion (from another worksheet) to that line.
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.
Visualise VBA code that disables a “worksheet-delete” display alert and then turns it back on after the delete.
How is a workbook open with VBA?
How is a workbook activated with VBA?
How is a workbook SavedAs with VBA?
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.
What code will make this message box appear?
How can you discover all the available message box interactive options available in VBA?
Visualize VBA code that asks the user to enter the location of the cell to be calculated upon.
What type of input box allows for cells to be selected with the mouse pointer?
Type:=8
Visualize VBA code that calls 2 sub routines inside another sub routine.
What reasons are there for calling sub routines inside other sub routines?
- To make the code cleaner (putting comments on the subsidiary sub routines)
- To have one main Sub routine that has access to subsidiary sub routines;
- Code is easier to maintain i.e. main code is in one place;
- More portable. One page, one code.
Visualize code that prevents a workbook from closing unless a certain condition is met.
Visualize code that runs automatically once a Workbook is open.
Visualize the code that asks the user if they are sure they want to save the workbook.
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.
