VBA Flashcards
learn VBA
Select all cells in Current region, VBA and keyboard?
Selection.CurrentRegion.Select; Ctrl+Shift+8 (*)
Return the color index to a Message Box
MsgBox Selection.Interior.ColorIndex
How do you force the declaration of variables?
Tools/Options (in VBA). Add tick to “Require Variable Declaration”.
What does Option Explicit do?
Make you declare all variables
What is the scope of a variable
How far it reaches
How is a variable declared locally (within a single procedure)?
By using a Dim or Static statement within the procedure.
How is a variable declared within the current module?
By using a Dim statement before the first Sub or function.
What does a Public statement do?
Declares a variable in all Subs and Functions in all modules in the current workbook
What does Static do when used in place of Dim?
Freezes the variable
What is the shortcut for Step Over in debugging?
Shift + F8
Shortcut for Debug run to cursor (runs up until the cursor)
Ctrl + F8
How do you add a Watch
Debug menu, Add Watch
How do you step out of a sub procedure
Ctrl + Shift + F8
When debugging, how do you add a break point and what does it do?
LHB in left margin. It breaks the sub.
How do you halt your code if debugging when a criteria is satisfied?
Insert “Debug.Assert” with a criteria such as “s < 5”. Or you can use “Debug Menu, Add Watch.. (Break When Value is True)
How do you change from absolute to relative references when recording a Macro?
Developer Tab, Use Relative References
What is counter intuitive about Debug.Assert
Debug.Assert runs until the criteria is FALSE
What should you avoid when using Dim in functions
Redimming of arguments
in functions, why (a as Double, b as Double) As Double
Because we also DIM th output
How do you debug a function?
Break point. In Excel, click into the function, hit Enter. in VBA you can now step through.
Why should you avoid message boxes in cells?
Because answer does not get returned to cell and because if copy to multiple cells, you get as many pop ups!
The order of VBA operators
^, -, *,/ ,\, mod, +/-, string, relational operators, logical operators
What are properties
Attributes of objects
What are methods
actions to be taken on objects
What are events
happenings that objects respond to
How do you refer to the range “A1” in the worksheet “Data”
Worksheet (“Data”).Range(“A1”)
What is a difference between a range and a selection
A range does not change
How to you assign a value to a variable “x” via an Input Box
x = InputBox (“????”)
How do you assign the value of the 2nd cell across and 2nd cell down to x
x = Selection.Cells (2,2)
How do you move 2 cells down and 2 cells to the right
ActiveCell.Offset (2,2)
How many variables is it good practice to have?
As many as possible as you can monitor in the Locals window
How do you assign a cell to a variable?
Dim Rng as Range Set RNG = Workbooks (“WorkbookName.xlsx”).Worksheets (“SheetName”).Cells (1,1)
How do you assign a range to a variable?
Dim Rng as Range Set Rng = Workbooks(“WorkbookName.xlsx”).Worksheets(“SheetName”).Range(“A1:D4”)
How do you select range by address?
Range(“A1:D4”).Select
How do you select cells by row and column?
Cells(1,1).Select
How do you change the value of a range by address?
Range(“A1:D4”).Value = “Text Here”
Using the cells function, select A1 in 2 ways
Cells(1,1).Select Cells(1,”A”).Select
How do you select the Row and Column of the active cell?
Cells(ActiveCell.Row,ActiveCell.Column)
How do you move the cursor to row 1 but stay in the same column?
Cells(1,ActiveCell.Column).Select
How do you select column D
Columns(“D”).Select
How do you select rows 4 and 9
Range(“4:4,9:9”).Select
How do you get to the bottom of a range?
Selection.End(x1Down).Select
Give an example of how to use a message box to make a decision about whether to delete or not?
Sub CellNavigation() If MsgBox(“Delete data?”, vbYesNo) = vbYes _ Then Cells.Delete MsgBox “Data Deleted” Else MsgBox “Data Not Deleted” End If End Sub
What are the VBA Message Box Buttons available?
vbOKOnly displays OK vbOKCancel displays OK - Cancel vbAbortRetryIgnor vbYesNoCancel vbYesNo vbRetryCancel
What is the vb value of the Cancel button on the Message Box
vbCancel
How would you describe the difference between the active cell and the selected cell?
many cells can be selected, but only one may be the active cell at any given time
If the active cell is A1, what cell will ActiveCell.Offset(1,2) select
C2
What can be data can be extracted from the following cell which is the active cell ABC-123 which is in cell B3?
ActiveCell.Address ActiveCell.Address(0,0) = B3 AcitveCell.Column = 2 ActiveCell.Row = 3 ActiveCell.Width = 48 ActiveCell.height = 15 ActiveCell.interiorColor = 16777215 Left(AciveCell,3)=ABC Len(AciteCell) = 7
How would you return the Color Index of cell A1 to a message box
MsgBox Range(“A1”).Interior.ColorIndex
How would you change the color index of cell A1 to 48?
Range(“a1”).Interior.ColorIndex = 48
Copy A1:A4 to B1:B4
Range(“A1:A4”).Copy Range(“B1:B4”)
Write script to count the rows and columns in A1:C5 using nr and nc as variables.
Dim nr As Integer, nc As Integer nr = Range(“A1.C5”).Rows.Count nc = Range(“A1.C5”).Columns.Count
How do you get data into subs?
Input box Active Cell (chosen before sub run) Selection (chosen before sub run) A fixed range
How do you get data out of subs
Message box Active Cell (chosen before sub run) Selection (chosen before sub run) A fixed range
What does Cells (5,2) refer to in range terms?
Range (“E2”)
What does the property cells refer to?
All the cells in the corresponding object.
What does “Selection” have in common with “object”
Most of the time they can be used interchangeably.
For “Program Flow”, what are the 3 fundamental structures
Sequence Selection Repetition
what is the answer to 5 mod 2
5/2 which is 2 and 1/2, so the mod is 1
Write code to produce a message if an InputBox number is even
Sub MessageIsEven() Dim x As Integer x = InputBox(“enter value”) If x Mod 2 = 0 Then MsgBox (“even steven”) End If End Sub
What is the basic structure for multi-alternative if..then
If Then ElseIf Then ElseIf Then Elseif Then End If
What is the value of a variable if you do not set its value?
0
What is the normal structure for a basic “General Loop”?
Do
pre-test code block
IfThen Exit Do
post-test code block
Loop
If required MsgBox or other output
Simple Do Loop for testing if x > 5
Sub DoLoop() Dim x As Integer Do x = x +2 If x > 20 Then Exit Do x = x -1 Loop MsgBox (x) End Sub
What is difference between a basic loop and a Do While loop
With the basic loop, you exit when the condition is true. For a do While you keep going while the condition is true.
Code for Do Until i=6
Sub dountil() Dim i As Integer Do i = i + 1 Loop Until i = 6 MsgBox i End Sub
Write code to validate that % input is between 0 and 100, outputing a message if not validated.
Sub ValidateInput() Dim P As Double Do P = InputBox(“Enter percent conversion:”) If P >= 0 And P <= 100 Then Exit Do MsgBox (“Percentage must be between 0 and 100”) Loop End Sub
Sub to delete cells in a given column using ActiveCell
Sub Delete99() Dim nr As Integer, i As Integer nr = Selection.Rows.Count For i = 1 To nr If ActiveCell = -998 Or ActiveCell = -999 Then ActiveCell.Clear End If ActiveCell.Offset(1, 0).Select Next i End Sub
Sub to delete cells in a given column using Selection where value is –999
Sub Delete999() Dim nr As Integer, i As Integer nr = Selection.Rows.Count For i = 1 To nr If Selection.Cells(i, 1) = -999 Then Selection.Cells(i, 1) = “” End If Next i End Sub
Write a sub to bubblesort
Sub bubblesort() Dim n As Integer, i As Integer, j As Integer Dim Temp As Double n = Selection.Rows.Count For i = 2 To n For j = 2 To n If Selection(j - 1, 1) > Selection(j, 1) Then Temp = Selection.Cells(j, 1) Selection.Cells(j, 1) = Selection.Cells(j - 1, 1) Selection.Cells(j - 1, 1) = Temp End If Next j Next i End Sub
how do you get the month from a cell with a date in it?
Month(Range(“A1”))
What value will Weekday(Date, vbMonday) return if the current day is Monday?
1
What is the general form of a one way if statement?
General Form:- If Then statements End If
Give an example of a one way if statement. Also show as a one line statement.
If x = 6 Then y = 2 End If If x = 6 Then y =2
How do you test if an activecell is empty
If IsEmpty(ActiveCell) Then
How to you replace the active Cell with zero if it is empty (sub)? How can you modfiy the second line of code?
Sub ReplaceBlankWithZero() If ActiveCell = “” Then ActiveCell = 0 End If End Sub Replace with If IsEmpty (AcitveCell) Then
Write a sub to add 20 to the active cell if +ve and deduct 20 if -ve
Sub testcell() If ActiveCell > 0 Then ActiveCell = ActiveCell + 20 Else ActiveCell = ActiveCell - 20 End If End Sub
Function that will output “biz” if the number is divisible by 3, “buz” if the number is divisible by 5, and “bizbuz” if the number is divisible by 3 and 5.
Function bizbuz(x As Integer) As String
If x Mod 3 = 0 Then
If x Mod 5 = 0 Then
bizbuz = “bizbuz”
Else bizbuz = “biz”
End If
Else If x Mod 5 = 0 Then
bizbuz = “buz”
End If
End If
End Function
What is the basic structure for a nested 2 level IF
If If Else End If Else If End If End If
How would you create a function grade in VBA that would assign a letter grade to a numeric score? 90 =< score<100 A 80 =< score<90 B 70 =< score<80 C 60 =< score<70 D score < 60 F
Function Grade(score As Double) As String If score >= 90 Then Grade = “A” ElseIf score >= 80 Then Grade = “B” ElseIf score >= 70 Then Grade = “C” ElseIf score >= 60 Then Grade = “D” Else Grade = “F” End If End Function
Create a VBA sub that will: Choose a number between 1 and 10 (inclusive) then perform the “guessing game” until the user guesses the chosen number.
Sub guessingame() Dim g As Integer, r As Integer r = WorksheetFunction.RandBetween(1, 10) Do g = InputBox(“have a guess between 1 and 10”) If g = r Then Exit Do MsgBox (“guess again”) Loop End Sub
Create a function that will count the number of the first n integers that are divisible by either 3 or 5?
Function Divisible(n As Integer) As Integer Dim i As Integer, c As Integer For i = 1 To n If i Mod 3 = 0 Or i Mod 5 = 0 Then c = c + 1 End If Next i Divisible = c End Function
Create a VBA sub that will count the number of 5’s in a selection (column vector)?
Sub CountFives() Dim nr As Integer, i As Integer, c As Integer nr = Selection.Rows.Count For i = 1 To nr If Selection.Cells(i, 1) = 5 Then c = c + 1 Next i MsgBox (“There are “ & c & “ fives in your selection”) End Sub
Find the 7s in a selection in Column A and output the row number of each instance of a 7 to column c starting in row 1.
Sub FindSeven() Dim i As Integer, nr As Integer, c As Integer nr = Selection.Rows.Count For i = 1 To nr If Selection.Cells(i, 1) = 7 Then c = c + 1 Range(“C” & c) = i End If Next i End Sub
At what value to indicees exit?
At one greater than their last value.
Add to each element in a selection (array)
Sub AddFive()
Dim i As Integer, j As Integer
Dim nr As Integer, nc As Integer
nr = Selection.Rows.Count
nc = Selection.Columns.Count
For i = 1 To nr
For j = 1 To nr
Selection.Cells(i, j) = Selection.Cells(i, j) + 5
Next j
Next i
End Sub
Create a 2 by 2 Array
Option Base 1
Sub CreateArray()
Dim A(2, 2) As Integer
A(1, 1) = 3
A(1, 2) = 5
A(2, 1) = 4
A(2, 2) = 1
Range(“A1:B2”) = A
End Sub
Create a VBA function that counts the number of elements in an array that are divisible by n
Function CountDivisibleByN(rng As Range, n As Integer) As Integer
Dim i As Integer, j As Integer
Dim nr As Integer, nc As Integer, c As Integer
nr = rng.Rows.Count
nc = rng.Columns.Count
For i = 1 To nr
For j = 1 To nc
If rng.Cells(i, j) Mod n = 0 Then
c = c + 1
End If
Next j
Next i
CountDivisibleByN = c
End Function
Add 5 to each element in a selection but place the result in a different range (2 rows down, same column)
Sub AddFive()
Dim i As Integer, j As Integer
Dim nr As Integer, nc As Integer
nr = Selection.Rows.Count
nc = Selection.Columns.Count
For i = 1 To nr
For j = 1 To nc
ActiveCell.Offset(nr + i, j - 1) = Selection.Cells(i, j)
Next j
Next i
End Sub
How do you Create a 2 x 2 Array?
Sub CreateArray()
Dim A(2, 2) As Integer
A(1, 1) = 3
A(1, 2) = 5
A(2, 1) = 4
A(2, 2) = 1
Range(“A1:B2”) = A
End Sub
How would you populate cells A1 to B2 using VBA and an input box to collect the inputs from the user?
Sub CreateArray()
Dim A(2, 2) As Integer, ia As Integer, j As Integer
For i = 0 To 1
For j = 0 To 1
A(i, j) = InputBox(“Please ener element “ & i & “,” & j)
Next j
Next i
Range(“a1:b2”) = A
End Sub
How do you dim an array when you don’t know the dimensions? And what do you do once you know the array dimensions
a. Dim A() As Integer
b. ReDim A (nr,nc) as integer
How do you make an array start at 1 instead of zero?
Type “Option Base 1” before the sub
Code to take a selection in Excel and store in VBA as Static array
Sub ImportArray() Static A() As Variant A = Selection MsgBox A(2, 2) End Sub
How to you populate a selection in Excel from an array in VBA using a fomula
Option Base 1
Sub ExportArray()
Dim i As Integer, j As Integer
Dim A(3, 3) As Integer
For i = 1 To 3
For j = 1 To 3
A(i, j) = 2 * i + j
Next j
Next i
Selection = A
End Sub
Export an array to a specified range in VBA with a formula to calc the VBA values to export?
Sub ExportArray()
Dim i As Integer, j As Integer
Dim A(3, 3) As Integer
For i = 1 To 3
For j = 1 To 3
A(i, j) = 2 * i + j
Range(“B2:D4”).Cells(i, j) = A(i, j)
Next j
Next i
End Sub
Function to count the number of items divisible by a specified integer n in a selection specified.
Function CountDivisibleByN(rng As Range, n As Integer) As Integer
Dim i As Integer, j As Integer
Dim nr As Integer, nc As Integer, c As Integer
nr = rng.Rows.Count
nc = rng.Columns.Count
For i = 1 To nr
For j = 1 To nc
If rng.Cells(i, j) Mod n = 0 Then
c = c + 1
End If
Next j
Next i
CountDivisibleByN = c
End Function
Array Function for returning whether a cell has a value of n. 1 for true, zero for false.
Option Base 1
Function DivisibleByN(rng As Range, n As Integer) As Variant
Dim i As Integer, j As Integer
Dim nr As Integer, nc As Integer
Dim B() As Variant
nr = rng.Rows.Count
nc = rng.Columns.Count
ReDim B(nr, nc) As Variant
For i = 1 To nr
For j = 1 To nc
If rng.Cells(i, j) Mod n = 0 Then
B(i, j) = 1
End If
Next j
Next i
DivisibleByN = B
End Function
Create a VBA array function “SortVector” that sorts an input vector in ascending order
Function SortVector(rng As Range)
Dim i As Integer, j As Integer, n As Integer
Dim Temp As Integer, A() As Variant
n = rng.Rows.Count
ReDim A(n, 1)
A = rng
For i = 2 To n
For j = 2 To n
If A(j - 1, 1) > A(j, 1) Then
Temp = A(j, 1)
A(j, 1) = A(j - 1, 1)
A(j - 1, 1) = Temp
End If
Next j
Next i
SortVector = A
End Function
What must Array functions output as?
Variant
ReDim preserve sub to return a shopping list based on user input?
Option Explicit
Option Base 1
Sub ShoppingList()
Dim i As Integer, ans As Integer, L() As Variant
Do
ans = MsgBox(“Would you like to add an item to the shopping list?”, vbYesNo)
If ans = 7 Then Exit Do
i = i + 1
ReDim Preserve L(i) As Variant
L(i) = InputBox(“Enter new item:”)
Loop
Range(“A1:A” & i) = WorksheetFunction.Transpose(L)
End Sub
Create a VBA array function called diagonals that extracts the diagonal elements of a square matrix and places them in a column vector
Option Base 1
Function Diagonals(rng As Range)
Dim i As Integer, n As Integer
Dim D()
n = rng.Rows.Count
ReDim D(n, 1)
For i = 1 To n
D(i, 1) = rng.Cells(i, i)
Next i
Diagonals = D
End Function
Use a message box to outpu part of a string using Mid in VBA
Sub Strings()
Dim s As String
s = “pneumonoultranisc”
MsgBox Mid(s, 5, 7)
End Sub
Find the length of a string in VBA
Sub Strings()
Dim s As String
s = “pneumonoultranisc”
MsgBox Len(s)
End Sub
Find text position of “tran” in a string
Sub Strings()
Dim s As String
s = “pneumonoultranisc”
MsgBox InStr(s, “tran”)
End Sub
Find the position of the second No in the string “pneumonoultraniscno”
Sub Strings()
Dim s As String
s = “pneumonoultraniscno”
MsgBox InStr(15, s, “no”)
End Sub
Msg Box a string in upper case
Sub Strings()
Dim s As String
s = “pneumonoultraniscno”
MsgBox UCase(s)
End Sub
What are VBA options for concatenation?
“&”
Join
Join 3 strings with “A”, “B”, and “C” in them
Option Base 1
Sub JoinStrings()
Dim B(3) As String
B(1) = “A”
B(2) = “B”
B(3) = “C”
MsgBox Join(B)
End Sub
Join 3 strings with “A”, “B”, and “C” in them with spaces removed
Option Base 1
Sub JoinStrings()
Dim B(3) As String, Joinedmsg As String, msg As String
B(1) = “A”
B(2) = “B”
B(3) = “C”
Joinedmsg = Join(B)
For i = 1 To Len(Joinedmsg) Step 2
msg = msg + Mid(Joinedmsg, i, 1)
Next i
MsgBox msg
End Sub
Join 3 strings with “AB”, “CDE”and “FG” in them with spaces removed
Option Base 1
Sub JoinStrings()
Dim B(3) As String, Joinedmsg As String, msg As String
Dim i As Integer
B(1) = “AB”
B(2) = “CDE”
B(3) = “FG”
Joinedmsg = Join(B)
For i = 1 To Len(Joinedmsg)
If Not Mid(Joinedmsg, i, 1) = “ “ Then
msg = msg + Mid(Joinedmsg, i, 1)
End If
Next i
MsgBox msg
End Sub
Ask user for a sentence and split into a vector in VBA
Sub SplitAndJoin()
Dim sentence As String, A() As String
sentence = InputBox(“Please enter a sentence.”)
A = Split(sentence, “ “)
End Sub
Ask user for a sentence and msg box one word at a time.
Sub SplitAndJoin()
Dim sentence As String, A() As String
sentence = InputBox(“Please enter a sentence.”)
A = Split(sentence, “ “)
For i = 0 To UBound(A)
MsgBox A(i)
Next i
MsgBox Join (A) End Sub
Ask user for a sentence and msg box one word at a time and Msg Box the whole sentence
Sub SplitAndJoin()
Dim sentence As String, A() As String
sentence = InputBox(“Please enter a sentence.”)
A = Split(sentence, “ “)
For i = 0 To UBound(A)
MsgBox A(i)
Next i
MsgBox Join(A)
End Sub
Take a string in a column in VBA and combine into a single word.
eg, the folowing vector is selected
G
A
T
T
A
C
A
Sub CombineColumn()
Dim B As Object, msg As Variant, i As Integer, nr As Integer
Set B = Selection
nr = Selection.Rows.Count
For i = 1 To nr
msg = msg + B(i)
Next i
MsgBox msg
End Sub
extract part numbers from 67-345-23
Option Base 1
Function parts(s As String) As Variant
Dim L As Integer
Dim firstdash As Integer, seconddash As Integer
L = Len(s)
Dim p(3) As Variant
firstdash = InStr(1, s, “-“)
seconddash = InStr(firstdash + 1, s, “-“)
p(1) = Left(s, firstdash - 1)
p(2) = Mid(s, firstdash + 1, seconddash - firstdash - 1)
p(3) = Right(s, L - seconddash)
parts = p
End Function
Take a string from a user and export to a text file.
When do you need to use WorksheetFunction.Transpose(?)
on string vectors because they are row vectors.
