Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

OPRE 6332 QUIZ > Chapter 24 > Flashcards

Chapter 24 Flashcards

1
Q

Visual Basic for Applications (VBA)

A

A programming language is a language with which we give instructions to a computer. Computers essentially ‘understand’ only a language made up of zeroes and ones. Such a language, in zeroes and
ones, is called a machine language. Zeroes and ones are hard for humans to read and interpret quickly. Therefore, we have a variety of languages which are more user-friendly and where we can use words used in normal human languages, such as English. Computer languages used by humans to write programs are sometimes called high-level languages. In contrast, a machine language is referred to as a low-level language.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Visual Basic for Applications (VBA)

A

When we write programs in a high-level language, we need to be able ‘translate’ this language to a machine language. This translation job is done by another computer program called a compiler. A computer
compiles the instructions (given in the high-level language) to zeroes and ones (understood by the computer).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Visual Basic for Applications (VBA)

A 
In communicating (in any medium) humans may make errors that lead to the message being unclear. In other words, we need to follow rules for communication – such as using the right grammar and syntax. In the same way, we need to follow the rules of the language in writing a program in a given language. In this context, the compiler performs a 
useful function; as it translates from the high-level language to the low-level language, it checks to see that the instructions we are writing follow the correct syntax.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Visual Basic for Applications (VBA)

A

In the process of such translation, or compiling, the compiler also serves another useful function. It identifies errors related to grammar or syntax in the instruction and helps contribute to the accuracy of the
program we are writing.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Visual Basic for Applications

A

A number of high-level languages are available today; each has its own features and strengths. VBA is a high-level language. Its origins can be traced back to BASIC,
(Beginner’s All Purpose Symbolic Instruction Code). Microsoft introduced Visual Basic (VB) with a view to providing an easier programming environment. Visual Basic is a stand-alone language, in the sense that it
does not need another application within which it has to run. Using Visual Basic, we can create sophisticated software applications. Visual
Basic for Applications, as the name suggests, is a language that functions within an application or another software program. The main function of
VBA – in a given application – is to help us work with that application more efficiently.
We will use VBA in the context of Excel. However, VBA can also be used in many other Microsoft Office Applications such as Word, PowerPoint etc. A major advantage of learning VBA is that similar concepts are applied within other applications. Learning VBA in any one application opens up a portal to other programming environments that use VBA.
By learning VBA, you will be able to:
- use Excel more efficiently
- use Excel more effectively
- think through a problem more logically
- understand programming in other applications

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is needed from you

A
  • You do not need to be a programmer or have prior programming experience
  • You should be ready to put in a lot of time experimenting and practicing
  • You should have the ‘DEVELOPER’ menu or tab on Excel. If you do not see a ‘Developer’ menu, you need to add this.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

How to add the DEVELOPER tab

A
  • FILE -> Options -> Customize Ribbon

- On the right hand of the dialog box, check DEVELOPER.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Where in the EXCEL is the VBA program or code

A

In Excel, as in other Microsoft Office applications, the visual basic code or program is stored in a ‘module’ in the Visual Basic Editor (VBE).
How to go to a module in the VBE:
- Keyboard: by pressing ALT-F11
- Mouse: DEVELOPER Ribbon -> Code group -> Visual Basic.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Visual Basic Editor (VBE)

A

Just as a workbook holds worksheets, the VBE is a ‘place’ that holds

  • Modules: Modules contain the actual code or program. Modules are containers that hold one or more programs.
  • Project Explorer: Just as the Windows Explorer shows what drives, folders, files are available, the Project Explorer shows what workbooks are open, what worksheets there are in each workbook, and what modules are there. Chances are that you will see files that you do not recognize. These are files used by Excel
  • Properties Window: The Properties Window that shows the properties of the module that we are looking at.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Macros

A

A macro is a small program within an application such as Excel. Macros are sometimes also referred to as procedures. Macros can be created by the user for use in Excel (and in many other programs). A simple way to start understanding macros (or procedures or programs) is to record a macro. Just as a voice recording captures your voice, recording a macro in Excel captures your actions in Excel.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

How to record a macro

A
  • Click on the Developer tab. To the left, you will see a button for ‘Record Macro’. Click on this button. You will be asked for four things. Some of these are optional, but go ahead and get into the habit of providing all four items of information asked.
  • First, provide a Macro Name. You can call it “FirstMacro”. (Note that the macro name should be one word, without spaces, and should not start with a number).
  • Second, provide a Shortcut Key. (The Shortcut Key is a combination of CTRL followed by a letter. Just as clicking on the ‘play’ button on your voice recorder will play your voice, clicking the Shortcut Key will repeat your actions in Excel.)
  • Third, provide a location to store the module (Should the module be stored in your current workbook, a new workbook, or a workbook called
    Personal Macro Workbook? Usually you will store the macro in the current workbook.)
  • Finally, provide a description of what the macro is going to do.

Once you have provided this information, and click “Ok”, your macro will begin recording. Note that the ‘Record Macro’ on the Developer Tab has changed to ‘Stop Recording’. Your actions in Excel are now being
recorded.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Saving your Macro

A

As macros can be written for malicious purposes, there are usually security concerns around macros. For this reason, Excel 2013 requires a separate file type to indicate that the Excel file contains macros. Older versions of Excel – prior to Excel 2007 – had a 3-character file extension. Excel 2010 and Excel 2013 have 4-character file extensions.
When you try to save a workbook that contains a macro, Excel prompts you to save the workbook as a macro-enabled workbook.
If you choose to continue saving the workbook as the more usual .`xlsx file, the contents of the workbook will be saved, but the macro will be lost. The file extension .xlsm denotes a workbook containing a macro.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Naming your Macro

A

Just as with range names, we need to follow some rules when naming a macro.
The name:
- must begin with a letter
-cannot contain a period or a punctuation mark 0r special characters
- can include one or more underscore _ marks
- cannot have spaces
- cannot exceed 255 characters
- cannot use reserved words

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Examples of Valid Macro names

A
  • TotalRows
  • total_rows_10
  • Total_number_of_Rows
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Examples of invalid macro names

A
  • Total.Rows (contains a period)
  • 5Rows (starts with a number)
  • Total&Rows (includes special characters)
  • Total Rows (Space not allowed)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Caution while copying and pasting from Microsoft Word to Visual Basic Editor

A

When copying and pasting from Microsoft Word to the Visual Basic Editor in the Integrated Development Environment, we need to be careful when copying quotation marks known as smart quotes. Smart quotes can copy over as symbols rather than as quotes. Running the macro will generate an error message.

17
Q

Looking at the code in the macro

A

On the Developer Tab, click on the ‘Macros’ button. Below the large window with ‘Macro name’ is a smaller window titled ‘Macros in’. Choose ‘This Workbook’ from the drop down box. You will see the name
of the macro you recorded in the large window titled ‘Macro Name’. Click on FirstMacro. Then click on edit.
You will see something very similar to the following:
Sub FirstMacro()
‘ FirstMacro Macro
‘ My first macro
‘ Keyboard Shortcut: Ctrl+l
Range(“E5”).Select
ActiveCell.FormulaR1C1 = “Hello World!”
Range(“E6”).Select
End Sub

18
Q

What do the lines in the code mean

A

VBA conveniently gives the words in the code different colors which makes it easy for us to read the code. Words in blue are reserved words, or keywords that mean specific things to Excel. For example, you cannot use the word ‘Sub’ as a macro name. Words in green are comments.

Comments are provided for the benefit of the user, and are not lines in the program itself. For example, the description of the program you provided is shown as a comment. Comments start with a single quotation mark. You can write anything here. Excel does not care what you write and will ignore any line that starts with a single quotation mark.

The first line in each macro begins with the word ‘Sub’ followed by the name of the macro and the first line ends with open and close parentheses like these (). The last line has the words ‘End Sub’. The entire program or macro has to be contained within the lines Sub, and End Sub. If you have any text before the ‘Sub’ line or after the ‘End Sub’ line, or Excel will indicate an error when you try to run the macro.

The line ActiveCell.FormulaR1C1 = “Hello World” indicates that you entered the words “Hello World” in the active cell. The active cell is simply the cell where the cursor is in Excel. FormulaR1C1 is Excel’s way of
referring to the value in that cell. The period ‘.’ separates the object from a property of the object; e.g., the cell-object is separated by a period
from the value-property. Just as you can say Car.Door to refer to the door of your car, or Car.Engine to refer to the engine of your car, you can say Activecell.FormulaR1C1 to indicate the value in the cell where your cursor is. The cell is considered an object (just as your car is an object). The characteristic (of value) is considered to be a property or attribute of the object (just as the car door is an attribute of the car).

19
Q

Replaying (or running) the macro

A

Before you run your macro, it is important to remember that once an Excel macro is executed you cannot undo the results of the execution. In other words, CTRL-Z to undo will not bring the spreadsheet back to its pre-macro state. Hence, before you run a macro, it is a good idea to make a backup copy of your workbook.
Replay the macro. Go to any other worksheet in the workbook where you created the macro. Press the short cut key that you provided when you recorded the macro. The macro will enter “Hello World” in cell E5 in that worksheet. After it enters “Hello World” the macro will then activate cell E6 (because
that is where you went after you entered “Hello World”).

20
Q

Objects

A

An object represents a building block in Excel such as a cell, a worksheet or a workbook. The Excel Object Model refers to a description of all the objects in Excel.
A group of objects is known as a collection. A collection is itself an object. You can think of The University of Texas at Dallas as an object
which is a collection of schools. Schools are collections of buildings. Buildings are collections of classrooms (and other rooms). Similarly, you
can think of the Excel application as an object that is a collection of different workbooks that you may have open at the same time. Each workbook is a collection of worksheets. Each worksheet is a collection of rows and columns.

21
Q

Properties of Object

A

Properties are something that an object has. Properties describe the object. E.g., a car is an object. A car has the property of having one or more doors. Similarly, in Excel, the Range object has the property ‘Title’. That means that a range can have a title.
Properties define the characteristics of an object. A property can also define the way the object behaves. E.g., a cell can take on a value, a color, a border.

22
Q

Methods

A

Methods do something to or with the object. A method is an action that can be performed on or with objects. For example, a car is an object. You can say Car.Door.Open to indicate ‘open car door’. (Note it is
not clear which door to open; but we can refine this further by saying Car.DriverSideDoor.Open). Similarly, you can say Range.Delete to say that the range should be deleted.

23
Q

The Object Browser

A

When you are in the Visual Basic Editor (VBE), press F2 (function key F2) to see the Object Browser. The Object Browser shows the objects and the methods and properties (if any) associated with it. The green
symbol next to an item indicates that the item a method. The hand symbol indicates that the item is a property.

24
Q

What are events in Excel

A

Events are various things that can happen in a program such as Visual Basic. Visual Basic programs can be structured around events. For instance, opening a workbook, activating a worksheet, choosing a particular cell, these are all events. We can write a program that is
triggered when an event happens.

25
Q

How to invoke Excel worksheet functions

A

Some, but not all, of the functions that you can use in a worksheet are available for use in VBA. E.g., you can use functions such as LEFT, RIGHT, or MID, in Excel VBA and Excel VBA will understand you are
referring to string functions. However, some other functions such as Vlookup, are not directly usable in VBA. To use (or invoke) these functions, you need to prefix these functions with the words
APPLICATION.WORKSHEETFUNCTION. This tells VBA that you are invoking (or using, or calling) a function that is normally used in a worksheet. Unfortunately, not all worksheet functions are available for use in VBA (even by using the APPLICATION.WORKSHEETFUNCTION
prefix). Luckily, we do not have to remember which functions are available and which are not. When you are in the Visual Basic Editor (VBE) and type APPLICATION.WORKSHEETFUNCTION. (that is, application period worksheetfunction period) followed by the worksheet function. Excel VBA helpfully provides a list of available worksheet functions.

26
Q

Referring to a cell, worksheet, workbook

A
  • Active cell is the cell in which the cursor presently is. (In VBA code, the active cell refers to the cell which will be read, or where a value will be placed)
  • Active worksheet is the worksheet presently being processed, or referred to
  • Active workbook is the current workbook (when you have more than one workbook open, active workbook helps select the workbook where you want actions to be completed)
27
Q

How to select a cell, row, column, range

A
  • Selecting a cell: You can select a cell with the code: Cells(RowNumber, ColumnNumber).select
    RowNumber and ColumnNumber supply two arguments for the Cells() function. You do not have to call the two arguments ‘RowNumber’ and ‘ColumnNumber’. You can call the two arguments almost anything you want. Note that the first argument for the Cells() function is always the row number and the second argument is always the column number.
  • Selecting a row: You can select a row with the code: Cells(SpecificRowNumber, AnyColumnNumber).entirerow.select
    Here the row number is important as you are selecting the entire row. The column number does not play an important role.
    You can also select a row with the code: Range(“A1”).EntireRow.Select
    Here, row 1 will be selected.
  • Selecting a column: You can select a column with the code Cells(AnyRowNumber, SpecificColumnnumber).entirecolumn.select
    Here the column number is important as you are selecting the entire column. The row number does not play an important role.
    You can also select a column with the code: Range(“A1”).EntireColumn.Select
    Here column A will be selected.
    -Selecting a range: You can select a range in different ways. Remember that for a range, one end of the range is the upper left cell and the other end is the lower right cell in a rectangular block. Both the
    upper left cell of the range and the lower right cell of the range, are determined by their respective row and column numbers. Using the cells approach, you can select a range in one of the following ways:
    Range(Cells(2,3), Cells(10,4)).Select
    Range(“C2:D10”).Select
28
Q

Examples of Code

A

When you are in the Integrated Development Environment, you can create a module by clicking on the Insert menu and then choosing Module. If you have recorded a macro and stored it in that workbook, you will see that it already has a module. A module can have more than one macro (also known as procedure) in it.

29
Q

IF-THEN Condition

A 
How to use IF condition in visual Basic for Applications. In any worksheet type the number 100 into cell A1. Then copy and paste the following code in a module. 
Sub Grading() 
 GradePoints = ActiveSheet.Range("A1").Value 
 If GradePoints > 93 Then 
 LetterGrade = "A" 
 ElseIf GradePoints > 83 Then 
 LetterGrade = "B" 
 ElseIf GradePoints > 73 Then 
 LetterGrade = "C" 
 Else 
 LetterGrade = "F" 
 End If 
 ActiveSheet.Range("B1").Value = LetterGrade 
End Sub

If you change the number 100 in cell A1 to 50, the letter grade in cell B1 will change from A to F.
The first line (after the name of the macro) takes the value in cell A1 in the active sheet and stores it in a variable called GradePoints.
The IF condition starts with the word IF, and ends with ENDIF. It is not necessary to have ELSEIF as a part of your IF condition. ELSEIF is useful if you have multiple possible values in the IF condition.

30
Q

FOR-NEXT LOOP

A

Visual Basic for Applications is very useful if you want to do a repetitive task. Let us say that you want to put a number starting from 1 to 100 in 100 cells, and that you want to color even numbers red and odd numbers green. Here is the code to do this.
Sub OddEvenColor()
For rownumber = 1 To 100
Cells(rownumber, 1).Value = rownumber
If Application.WorksheetFunction.IsEven(rownumber) Then
Cells(rownumber, 1).Interior.Color = vbRed
Else
Cells(rownumber, 1).Interior.Color = vbGreen
End If
Next
End Sub
The first line (after the name of the macro) starts a counter from 1 to 100. Just as we started the IF condition with IF and finished it with END IF, a FOR loop starts with FOR and ends with NEXT. Here the loop assigns the value 1 to a variable. The variable is ‘rownumber’.
Each time the program sees NEXT it increments the variable ‘rownumber’ by 1 until it completes 100.
The line Cells (rownumber, 1).Value = rownumber assigns the value represented by ‘rownumber’ to a cell. The reserved word ‘cells’ has two arguments: the first number represents the row, and the second number represents the column. You can refer to any cell on the active sheet by referring to that cell’s row and column. (If you do not specify which worksheet, it is assumed that the cells are in the current worksheet in the current workbook.)
The next line If Application.WorksheetFunction.IsEven (rownumber) then checks if the rownumber is even. To do this, we use the function Is Even (). The function IsEven() takes one argument, a number. If the number is even, the function returns the value ‘TRUE’. We read this as saying it is true that rownumber is even. The function IsEven() is available for use in a worksheet. To let Excel know that we are using a
function that is available in a worksheet we write the code as:
Application.WorksheetFunction.IsEven(rownumber).

If the cell contains an even number then we want to color the cell red. We take the cell object and refer to its interior property. (This distinguishes it from the cell’s border property. So we can use different
commands to color the border differently). We then assign the color red to the interior of the cell. Colors in VBA can be represented by various numbers that are difficult to remember. For convenience, Excel provides some values that are called constants. vbRed, vbGreen, vbYellow etc., are constants.

31
Q

FOR-EACH-NEXT LOOP

A

This loop is useful to address all objects in a collection. For instance, you want to name each worksheet in your workbook JohnDoeSheet1, JohnDoeSheet2 and so forth. You can do this as follows:
Sub NameSheetJohnDoe ()
For Each wsSheet In ActiveWorkbook.Worksheets
wsCount = wsCount + 1
wsSheet.Name = “JohnDoeSheet” & wscount
Next wsSheet
End Sub
The first line (after the name of the procedure, or macro) refers to each object in the collection. Here the collection is all the worksheets in the activeworkbook. (Activeworkbook is the workbook you are currently in. You could have other workbooks open, but these will not be affected). wsSheet is the variable name to represent each object in the collection.
The line wsCount = wsCount + 1 is used to increment the variable wscount by one each time it is encountered.
The line wsSheet.Name refers to the Name property of the object wsSheet.
The line Next wsSheet closes the loop and the code repeats from the line For Each until each object in the collection has been addressed.

If you wanted to name all worksheets in all open workbooks, you can set up a loop inside another loop.
Sub NameSheetJohnDoe()
For Each wbook In Application.Workbooks
wbook.Activate
For Each ws In ActiveWorkbook.Worksheets
wscount = wscount + 1
ws.Name = “JohnDoeSheet” & wscount
Next ws
Next wbook
End Sub

The first line (after the procedure name) refers to the collection of workbook objects in the Excel application. One by one, the code ‘activates’ each workbook object in the collection so that that
workbook becomes the ActiveWorkbook. It then names each sheet in the active workbook.

32
Q

DO-WHILE LOOP

A

Suppose you want to do an action while some condition is true, and you want to continue doing this action until that action becomes false. For such an action, you will use a Do While loop.
In the following example, we want to toss three coins and see if all three coins turn up heads. We will keep tossing three coins at a time, until such time we get three heads. We want to determine the number of tosses it takes before we get three heads together.

We will use the following code.
Sub ThreeHeadsTogether()
ThreeHeads = False
Do While ThreeHeads = False
CoinTossNumber = CoinTossNumber + 1
‘we will assume that if Coin1 has a value of 1 it is heads,
otherwise tails
Coin1 = Application.WorksheetFunction.RandBetween(1, 2)
Coin2 = Application.WorksheetFunction.RandBetween(1, 2)
Coin3 = Application.WorksheetFunction.RandBetween(1, 2)
If Coin1 = 1 And Coin2 = 1 And Coin3 = 1 Then
ThreeHeads = True
End If
Loop
MsgBox CoinTossNumber
End Sub

To start, we declare a variable ThreeHeads to have a value of False. Then, the next line Do While ThreeHeads = False starts the loop that will be repeated as long as the variable ThreeHeads as a value False.
To keep track of how many times we are tossing the coins, we have a variable CoinTossNumber that is incremented each time we toss the coins.
We then toss three coins, Coin1, Coin2, and Coin3. We will generate a random number between 1 and 2. That is each time we generate the random number, we get either 1 or 2. We will also assume that a 1
represents heads and 2 represents tails. To generate a random number we will use the worksheet function RandBetween(1,2). The RandBetween(x,y) function takes two arguments: the first provides the
lower boundary; the second provides the upper boundary. Since the RandBetween function is a worksheet function, we have to indicate this by writing Application.WorksheetFunction.RandBetween(1,2).

The random value generated is stored in the three variables Coin1, Coin2, and Coin3.
The IF THEN condition evaluates if all three coins are showing heads. If they are, then the variable ThreeHeads takes the value TRUE.
The Loop then goes back to the Do While ThreeHeads=False line. However, ThreeHeads now has the value True, so the loop does not continue.
The line MsgBox CoinTossNumber shows the values of the CoinTossNumber which reflects how many times we tossed the three coins.

33
Q

SELECT CASE - END SELECT

A 
The Select Case code begins with Select Case and ends with End Select. It works similar to the IF-THEN-END IF construct. The procedure that we wrote earlier called Grading is repeated below but this time using Select Case. 
Sub Grading() 
 GradePoints = ActiveSheet.Range("A1").Value 
 Select Case GradePoints 
 Case Is > 93 
 LetterGrade = "A" 
 Case Is > 83 
 LetterGrade = "B" 
 Case Is > 73 
 LetterGrade = "C" 
 Case Else 
 LetterGrade = "F" 
 End Select 
 ActiveSheet.Range("B1").Value = LetterGrade 
End Sub
34
Q

How to store code

A

You usually store code in a module. The module can reside in the current workbook or the Personal Macro Workbook. Whenever you start Excel, the Personal Macro Workbook opens automatically. Any macro
that is stored in the Personal Macro Workbook is then available for you.
For instance, if you have a macro that expands a column or increases the height of a row, and store this macro in the Personal Macro Workbook, then you can invoke (or use, or call) the macro from any worksheet that you are working in.
Excel stores the Personal Macro Workbook in a folder called XLSTART. Any file that is stored in XLSTART is opened automatically each time that you open Excel. You may find it convenient to keep the Personal Macro Workbook hidden so that you have the macros in that workbook available for use, but it does not get in the way of your work.

35
Q 
If you run the following code, which cell will have "Hello World"? 
 Sub Macro1() 
 columnnumber = 1 
 rownumber = 10 
 Cells(columnnumber, rownumber).Select 
 ActiveCell.Value = "Hello World" 
 End Sub

a. A10
b. J1
c. E10
d. K10

A

b. J1

36
Q

In Excel, VBA code is stored in:

a. A worksheet
b. A workbook
c. A module
d. A Visual Basic Application

A

c. A module

37
Q 
In VBA, the code beginning with 'Select Case' performs a 
function similar to that of: 
a. Do Loop 
b. If Then 
c. Do While 
d. None of the above
A

b. If Then

38
Q

If I enter the code: Range(“B1:F5”).cells(12) = “ZYZ” in a
procedure in a module in VBA, in which cell will I get “ZYZ”?
a. B12
b. F12
c. C3
d. D7

A

c. C3

39
Q

Which of the following statements is TRUE with regard to VBA?

a. VBA is also called ‘machine-language’
b. VBA is a low-level language
c. VBA is used within an application
d. VBA is a stand-alone language

A

c. VBA is used within an application

OPRE 6332 QUIZ (13 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions