BMGT 302 Midterm Review

R1C1

(Row number, column number) D7 -> (R7C4)

Ranges

A Range object is a cell or block of cells that is contained on a Worksheet object

Collection

A collection is an object that contains a group of like objects (i.e. Worksheet collection: an entire group of worksheet objects in your workbook)

Enumerations

A collection of named constants that are related XlDirection: xlDown, xlToLeft, xlToRight, and xlUp

Constant

A constant is a value in your macro that does not change while the macro is running Const myMonths as Integer = 12

Date

A floating-point number with the date to the left of the decimal point and the time to the right of it

Function

A function differs from a sub in that it returns a value

What is a loop?

A loop is a method of performing a task more than once

Parameter

A parameter represents a value that the procedure expects you to pass when you call it. The procedure's declaration defines its parameters. Sub DisplayName (IName As String, fName As String)

Flowchart

A pictorial representation of the logical steps

Currency

A positive or negative number with up to 15 digits to the left of the decimal point and up to 4 digits to the right of it

What is VBA?

A programming language created by Microsoft to automate operations in applications

Object

A reference to an object, such as a range of cells, a chart, a pivot table, a workbook, a worksheet, or any one of the many other objects that are a part of the Excel application

Procedures

A section of code that performs a particular task. 1.) Subs (also called macros) are stored in Modules 2.) Functions (returns a value)

Cells

A single cell is a range as far as VBA is concerned

Strings

A string is simply text, surrounded by double quotes. Also called a literal. product = InputBox("Enter the product's name.") MsgBox "The product's name is " & product & "."

VBE

A user-friendly development environment where programmed instructions are maintained

Variable

A variable is a name given by you, to which you assign a piece of data that is stored in an area of the computer's memory, allowing you to refer to the data when you need to later Dim myValue as integer

Identifying the UsedRange

Activesheet.UsedRange.Select

Built-in methods of Collections

Add, Count, Item, Remove

Application Software

All programs that perform specific tasks for users (MS Word, Outlook, Excel)

User-defined Types

Allows you to create new, composite data types. Used to store related information about a given entity

Pseudocode

An English-like representation of a numbered list of instructions to perform some task 1.) get price of item 2.) get sales tax rate 3.) sales tax = price of item times sales tax rate 4.) final price = price of item plus sales tax 5.) display final price 6.) halt

Argument

An argument represents the value that you pass to a procedure parameter when you call the procedure. The calling code supplies the arguments when it calls the procedure. Call DisplayName(lastName, firstName)

Workbook object

An excel file is a workbook object. The Workbooks collection contains the references to every Workbook object that is open in the same instance of Excel

Byte

An integer from 0 to 255

Long

An integer ranging from -2,147,483,648 to 2,147,483,657

Integer

An integer ranging from -32,768 to 32,767

Algorithm

An ordered sequence of unambiguous and well-defined instructions that performs some task and halts in finite time

Decimal

An unsigned integer scaled to the power of 10. The power of 10 scaling factor specifies the number of digits to the right of the decimal point, and ranges from 0 to 28

Arrays ( )

Any variable followed by ( ) is assumed to be an array by VBA

VBA Object Model

Application -> Workbooks -> Worksheets -> Ranges

Using variables

Assignment statement - assigns value to left side of = unitCost = 1.20 unitsSold = 20 totalCost = unitCost * unitsSold

Data Types

Boolean, Byte, Currency, Data, Decimal, Single, Double, Object, Integer, String, Variant, Long,

Methods of Range

Clear, ClearContents, Copy, PasteSpecial, Resize, Select, Sort

Column Absolute

Column value does not change, but row value will change ($B2)

Variant

Data type for all variables that are not explicitly declared as some other type, which can contain any kind of data except fixed-length string data

Array declaration

Dim employee(100) As String, salary(100) As Currency This declares two arrays with 101 elements

Declaring variables

Dim myValue1 as Integer, myValue2 as Integer, myValue 3 as Integer

Referencing Ranges: Use a Range object variable

Dim rngSales as Range Set rngSales = Range("Sales")

Message boxes

Display messages or results (Message, Icon*, Buttons*, Title*) MsgBox "The product's unit price is $2.40"

What is an iteration?

Each cycle of executing the loop structure's commands is called an iteration Fixed: executes a specified number of times that you hard-code directly as a numeric expression Indefinite: executes a flexible number of times that is usually defined by a logical expression

Event handlers

Event handlers are procedures that are automatically triggered upon the occurrence of an event (i.e. double-clicking a cell, adding a worksheet, changing a cell value)

Input boxes

Get inputs from users price = InputBox("Enter the product's unit price."

String Functions nCharacters = Len("S. Christian Albright")

Get the number of characters *returns value of 21; spaces included

Condition: Boolean Expression

If....Then...Else If condition Then true_instructions [Else false_instructions] If numberOrdered <= 200 Then unitCost = 1.30 If numberOrdered <= 200 Then unitCost = 1.30 Else unitCost = 1.20

Methods

Methods are actions that can be performed by objects. Methods can have arguments that specify how a method is performed. [object.method] -> Range("A1:D10").ClearContents

Absolute Referencing

Neither row nor column value will change ($B$2)

Assigning values to object variables

Object variable points to an object instance Dim scoreRange As Range Set scoreRange = Active.Worksheets ("Data").Range("Scores")

Properties

Objects have inherent qualities called properties. All properties the same across all objects, but we can change the values of these properties [object.property] -> ActiveSheet.Name = "Hello"

What are the 3 tiers of a software program?

Presentation tier > logic tier > data tier

Declare a constant that is available to all subroutine models

Public Constant StudentNumber As Integer = 20

Referencing Ranges: Use an address

Range ("A1") or Range ("A1:B10")

Referencing Ranges: Use Offset property

Range ("A5").Offset(2,3)

Referencing Ranges: Use a range name

Range ("Sales")

CurrentRegion property

Refers to a localized range of contiguous data ActiveCell.CurrentRegion.Select

Properties of Range: Cells

Retrieve a particular cell in the range, returns a Range object Range("D1:D10").Cells(3) -> Range ("D3")

Properties of Range: End

Return a reference to the last consecutive cell in a direction Range("A1").End(xlDown).End(xlToRight)

Properties of Range: Offset

Returns a reference relative to a range, where the range is usually a single cell Range ("A1").Offset (6,4).Select Range ("A1").Offset(0,0).value

Properties of Range: Address

Returns address as a string "B2:C6"

Properties of Range: Column

Returns the index of the first column in the range (a number, 1, 2, 3...)

Properties of Range: Name

Returns the name of the range (if any has been specified) Range("B3:E20").Name = "Sales"

Relative Referencing

Row and column value will change (B2)

Row Absolute

Row value does not change, but column value will change (B$2)

Variable Scope

Scope is where a variable is available to be referenced or used in an instruction (Local in sub, Public in top of module)

String Functions Instr(1,"Albright, Chris",",")

Searches string, returns a value of 9 because first comma is 9th character

String Functions Instr(1,"Albright, Chris",".")

Searches string, returns value of 0 (not found) because there are no period occurences in string

What is Syntax?

Syntax is a set of rules that apply to creating a computer instruction, these rules determine what a valid instruction must have and in what order

Split function

The Split function is used to split a string into an array based on a delimiter. A delimiter is a character such as a comma or space that separates the items

Worksheets

The collection of all worksheets in the workbook. The Worksheets collection enables you to refer to the Worksheet objects' names or index numbers

Do Loop: Posttest

The condition is evaluated after the instructions within the loop are processed. The instructions are always processed at least once

Do Loop: Pretest

The condition is evaluated before the instructions within the the loop are processed. The instructions may be processed zero or more times

Boolean

True or False, 1 or 0, On or Off

System Software

Used to control and manage computer devices and operations (i.e. Operating System, Utility Program)

String

Variable length can contain up to 2 billion characters, fixed-length can contain up to 64,000 characters

Referencing Ranges: Use End property

With Range("A1") Range(.Offset(0,0), .End(xlDown).End(xlToRight)).Select End With

Using Excel Functions

WorksheetFunction.SUM(Range("A1:A10"))

Arrays

a group of elements of the same type that have a common name

Macro, subroutine, procedure

a set of logically related lines of code that accomplishes a specific task

Program

all of the subs in an application

Application Software

all programs that perform specific tasks for users

How do you refer to a specific element in an array?

by using the array name and an index number

Referencing Ranges: Use a string variable for a range name

salesName = Range("Sales").Name Range(salesName)

ActiveCell

the object name in VBA of the single active cell on the active worksheet

Code Line Continuation

the underscore character, _, pre-ceded by a space. (Don't forget the space) MsgBox InputBox("Type your full address: city, state, zip code." , "User's address"), _ vbInformation, "User's Address"

System Software

used to control and manage computer devices and operations

Immediate Window

useful for issuing one-line VBA commands

Built-in constants

vbBlack, vbBlue, vbCyan, vbGreen,

By default, arrays are ______ based

zero-based