CS105 Midterm 2: REVIEW NOTES
LECTURE 6: VBA for Applications
1) VBA= Visual Basic Applications
a. Allows user to program in Microsoft Applications
1. Create/improve macros
2. Create user interfaces
3. Make own program.
b. Includes an IDE, or Integrate Development Environment
c. Uses Objects (Ex. Cells, ranges, sheets, buttons)
2) Making a Form Button:
a. Developer Tab > Button from “Form Control” > Drag + Drop a Button
3.) Assign Macros Dialog
a. Sub Procedures can begin with: Public Sub
They cannot begin with:
1. Public Function
2. Private Function
3. Private Sub
4. ) Events and Procedures
a. Events drive procedures
a. Event (something you do) DRIVES a Procedure or Function
(code). Then, Excel or VBA does something in return.
b. Event-driven Programming
a. It is a style of programming where the flow of the program is
determined by the events
b. Responds to user-initiated events (keystroke, click)
c. The event procedure is also known as the event handler.
5) Use of VBA
a. Users will provide input using Controls, InputBoxes, spreadsheets cells,
etc. The user then performs actions that trigger the events.
b. Program code (PROCEDURE) associated with the spreadsheet responds
the events after processing the input.
c. VBA programs will not run unless they are triggered by an event.
d. Macros are user-recorded VBA programs
i. No programming needed/ easy
i. Can only replay recorded tasks, cannot do arbitrary-sized tasks
ii. No logic (if, loops, etc)
g. A macro always begins with a Sub and ends with an End Sub
i. To perform multiple actions on the same macro, use:
2. End With
ii. The macro first needs to select the object, and then execute code
h. To record a macro: i. Developer > Record Macro
ii. Any comments in a macro begin with an apostrophe.
iii. Comments help the programmer and others read the code.
i. Procedures are also known as “subprocedures”
1. They are also known as “sub” or “procedure”
ii. Assigning a value to a cell
1. [cell reference]. Value = expression
2. EX. Range (“B3”) . Value = 9
3. The equal sign means to “take the value on the right side
and assign it to the cell on the left side”
LECTURE 7: Message Boxes, Input Boxes, Objects
1) Storage of VBA
a. VBA code is stored in Modules.
a. The MsgboxProcedure displays a message to the user. It has three
b. Value = Msgbox “Message caption”, [Button/s Icon]
3) Inputbox Procedure
a. The Inputbox gathers information from the user and puts it into the
spreadsheet as specified.
b. Range (“X”). Value = Inputbox (“Message”, “Title” )
c. The & can connect two strings. This is also known as concentrating two
strings, or joining them together.
4) Objects in Excel
a. Objects represent program entities, or elements that the program
manipulates. (Ex. Worksheet, Chart, Cell, Range)
b. Objects have:
i. Properties—things you can get/set (Ex. Captions, names, etc)
ii. Methods—things you can do (Ex. Delete, select, add, clear, etc)
5) Cells and Objectivity
a. The cells and ranges of cells are objects as well
b. Range (“A1”) refers to a cell on a worksheet. This cell is an object.
c. Cells have properties:
i. Value, Row, Column
d. Cells also have methods:
i. Select, Clear, ClearFormats
6) Calling Event Procedures
a. You can run one procedure from another procedure. In order to do this,
you “call” it, even if it is stored in another module or worksheet.
b. You call a subprocedure by writing its name, with the action if necessary.
7) Comments in VBA
a. Are not part of the code
b. An apostrophe is used at the beginning of the line
c. Appear in green LECTURE 8: Cell Notation, Message Boxes as Functions
1) Cell and Range Notation
a. Cell Notation
i. Cells (3,1) refer to R3C1.
ii. Row first, then column.
iii. Ex. B3 is the same as Cells (3,2)
b. Range Notation
i. Range can be used to specify a single cell.
1. Ex. Range (“A1”). Value
ii. Range can also be used to specify a range of cells.
1. Ex. Range (“B7:B9”). Clear
2) Activating Cells
a. Click on cell > Range (“”). Select > Range (“”). Activate
b. Clearing Columns
i. You can clear a column using the method, ClearContents
ii. Range (“D:D”). ClearContents
iii. ClearContents does not clear the formatting, just the
numbers or formulas that are contained within the cells.
iv. Clear : Clears everything
v. ClearFormats: Clears formatting, not contents
vi. ClearContents: Clears contents, not formatting
3) InputBox Function
a. This behaves as a function: it uses parentheses and has return
b. Inputbox (Prompt, [Title], [Default])
c. The [Title] default is “Microsoft Excel”
d. The [default] default is (blank)
e. It returns as a string. You MUST use the return value.
LECTURE 9: VARIABLES AND CONSTANTS
a. Variables are memory locations that hold data that can change
during the execution of the program.
b. The properties of an object are variables that can change during a
procedure (except the name property)
2) Use of Variables
a. User-defined variables have a type:
i. Variant means the data can be used as a text or as a number.
ii. Integer meanx the data is a whole number.
3) Rules for naming constants and variables
a. Names must:
i. Have no spaces or periods
ii. Have no conflict with reserved words (eg. Print, name, value)
b. Names should:
i. Be meaningful
ii. Have a prefix that indicates the type
c. Intrinsic constants are built in:
ii. vbMagenta d. Data items that remain the same are called constants. Constants
remain the same until the programmer changes them. The use
cannot alter them.
e. You declare a constant identifier in VBA by giving its name, data
type, and value.
4) Constants and data types:
a. Data types determine the storage space a program will use to store a
constant or variable.
b. Some data types are:
i. Boolean (True or False) bInAnswer
ii. Currency (Four decimal places) curIncome
iii. Single (7 Significant digits) sngNumber
iv. Double (15 Significant digits) dblMass
v. Integer (Whole numbers) intIndex
vi. Long (Larger whole numbers) lngPopulation
vii. String (Letters, digits, and other characters) strName
viii. Variant (Holds everything, converts data) vntInput
1. Eg. Three 3
c. Programs execute code one line at a time, going from top to bottom.
d. A local variable may be used only once in a single procedure
i. It is:
1. Declared in that procedure (eg. A button click)
2. Only available to that procedure
3. Used in that procedure and then discarded when that
procedure finishes (eg. The next button cli