Lynda: Up and Running With VBA In Excel

Question 1

What does a programming language need to enable? (four things)?

Answer 1

• Accept Input
• Store Data
• Manipulate Data
• Produce Output

Question 2

What are the three elements of an object in an Object-Oriented Programming Language?

Answer 2

• Properties
• Methods
• Events

Question 3

What are some examples of common objets in Excel VBA?

Answer 3

• Application
• Workbook
• Worksheet

Question 4

What are examples of properties in a workbook object in Excel VBA?

Answer 4

• Name
• Sheets
• Saved

Question 5

What are some examples of methods for a workbook object in Excel VBA?

Answer 5

• Close
• PrintOut
• Save

Question 6

What are some examples of Events for a workbook object in Excel VBA?

Answer 6

• BeforeClose
• NewSheet
• BeforeSave

Question 7

What are some examples of properties for a Worksheet object in Excel VBA?

Answer 7

• Name
• UsedRange
• Visible

Question 8

What are some examples of methods for a Worksheet object in Excel VBA?

Answer 8

• Activate
• Copy
• Delete

Question 9

What are some examples of Events for a Worksheet object in Excel VBA?

Answer 9

• Activate
• Deactivate
• Change

Question 10

Where can you find a list of all objects and their properties in Excel?

Answer 10

In the VBA window, look for the “Object Browser”

Question 11

How do you create a new function in VBA? Example (sales Tax)?

Answer 11

In the project window enter “function instead of Sub, and then name it your function (all caps to match existing functions), then parentheses to define inputs and outside parentheses to define output

Example:

Function SALESTAX(curSales As Currency, dblRate As Double), As Double

SALESTAX = dblRate * curSales

End Function

Question 12

Why should you make most of your Subroutines “Private”? How?

Answer 12

Most “Subs” should be “Private” unless they absolutely need to be accessible from other workbooks, such as a general use conditional subroutine, etc. Otherwise someone might accidentally use the macro on their workbook and cause disastrous results. You can make a “Sub” private by putting “Private” in front of it so it looks like “Private Sub”. This will prevent it from being accessed in other workbooks.

Question 13

How do you add effective comments to your VBA code?

Answer 13

First comment should be your name, and when the code was created

Next should be a description of what the code does, and the workbook name, in case someone copies it elsewhere

Question 14

What are data types in Excel VBA? Examples? How can you find a list of them all?

Answer 14

Data types are categories which limit the options of a variable to save memory and load faster when completing complex operations

Examples: Byte has to be between 0 and 255 and must be an integer. Boolean can only be True or False

Go to “Help” and type “Data type Summary” in the search, then click on the article that comes up

Question 15

What does “Dim” stand for in VBA? Why is it used?

Answer 15

Dim stands for Dimension

It is used at the top of your code to define a variable as a certain data type to save memory.

Question 16

What does “Option Explicit” do in VBA? Why is it valuable? How is it used?

Answer 16

Option Explicit is declared above the “Sub” and will require that all variables are defined at the top of the “Sub”, otherwise it will generate an error. It is valuable to prevent typos in errors throughout the code and the generation of new variables as a result.

Question 17

How do you create a global variable in VBA? Why is this useful?

Answer 17

You can create a global variable in excel by defining it in the declarations section above the “Sub”s. This is valuable because it is faster than redeclaring variables over and over in every Sub if they will be the same.

Question 18

How do you define a variable as “Static”? Why is this valuable?

Answer 18

Instead of using “Dim” you enter “Static” before the variable. This is useful because the variable will increment across subroutines if a value is added to it, instead of looping to “0”. You cannot make a static global variable.

Question 19

How do you define a variable as a “Constant”? Why is this useful?

Answer 19

A “constant” is defined by putting “Const” before a variable, but only in the declarations area above the “Subs”. This allows the variable to stay constant throughout subs. If the value is change locally, it will overwrite the constant, but only in the “Sub” where it’s overwritten.