3.0 Record Macro Functions, Debugging and testing, V-Lookup function

Question 1

Recording Macros

Answer 1

to develop a macro is to write it directly in VBA
- when you record a macro, VBA code automatically generated
- two main purposes
- to automate a sequence of Excel command used repeatedly;
- to learn how to write VBA code
- by recording macro we can obtain necessary instructions or syntax to do specific task in VBA
- eg. want to learn how to move (select) cell below relative to active cell
▪ select “Relative References” in toolbar (Developer > Code > Use Relative Reference)
▪ click “record macro”
▪ click down arrow once in worksheet
▪ click “stop recording”
▪ look at macros created and find related line
- example on right: this statement sets number format of a cell to scientific notation with two decimal places

Question 2

debugging and testing

Answer 2

process of hunting down and correcting errors (bugs)
- three major varieties of errors
- syntax errors
- executive (run-time) errors
- logic errors

Question 3

three major varieties of errors

debugging and testing

Answer 3

syntax errors
executive (or run-time) errors
logic errors

Question 4

passive debugging

Answer 4

debug button interrupts program execution and puts you into break mode

Question 5

active debugging

Answer 5

by pressing F8 key (or Fn F8) you are stepping through the program
- to stop execution of program at particular location set up a breakpoint by pressing F9 (or Fn F9) or click
on the margin
- to remove breakpoint, click the dot

Question 6

syntax errors

Answer 6

violate spelling conventions, placement of parentheses, number formatting, etc
▪ eg. Compile error: Invalid character

Question 7

executive (or run-time) errors

Answer 7

result when a statement attempts an invalid operation
▪ eg. Run-time error ‘11’: Division by zero

Question 8

logic errors

Answer 8

due to faulty program logic (garbage in, garbage out)

Question 9

Vlookup

Answer 9

vertical (top to bottom)
- looks up value in LEFT column of a table, jumps to column N of the table (the matched column is column 1) and returns value in that location

Question 10

hlookup

Answer 10

horizontal (left to right)
- looks up value in top row of a table, jumps down to row N of same table (same column) and returns value
in that location

Question 11

Message box display

Answer 11

Msg = “Do you want to continue?”
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = “MsgBox DEMO”
Response = MsgBox(Msg, Style, Title)

Question 12

Input Box Display

Answer 12

Msg = “Please enter the length of the beam”
Length = InputBox (Msg, “Length (m)”)