Study Guides (238,488)
United States (119,808)
CS 105 (2)
Kai (1)

CS105 Midterm 2.docx

7 Pages
Unlock Document

University of Illinois
Computer Science
CS 105

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. 6) Macros d. Macros are user-recorded VBA programs e. Advantages: i. No programming needed/ easy f. Disadvantages: 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: 1. With 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 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. 2) MsgboxProcedure a. The MsgboxProcedure displays a message to the user. It has three positional arguments. 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 values. 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 1) Data—Variables 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: i. vbWhite 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
More Less

Related notes for CS 105

Log In


Don't have an account?

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.