VBA

Question 1

What is a object in programming?

Answer 1

In programming an object is a noun. A noun defines a something like a car. You can also have objects within objects so you can have a car as an object and a person within the car as another object.

Question 2

What is a property in programming?

Answer 2

A property in programming is an attribute or an adjective in real life. For example, a car can be red or a person can be tall or short. A property just describes something about the noun/object.

Question 3

What is a method in programming?

Answer 3

A method is programming is like a verb. The method describes something the object does. Running a program is a method, opening/closing a window is a method. For example, car.drive would be considered a method since it causes the object the car to do something or perform an action.

Question 4

How do you write syntax in VBA?

Answer 4

You write the syntax differently from the English language. Instead of saying kick the ball, you would say Noun.verb or Object.method or ball.kick. For example: dinner.eat, car.drive, workbook.open, worksheet.activate.

Question 5

What are some examples of objects in Excel?

Answer 5

Worksheets, Cells, Workbooks

Question 6

What are collections in Excel?

Answer 6

A collection is a series of objects, for example all worksheets or all cells or all workbooks. All cars is an example of a collection or grouping.

Question 7

How do you identify items in a collection?

Answer 7

You can use an index number. This is not the best way though. For example, you can say ball(1).kick or ball(2).kick to identify different objects in a collection. A better method, however, is to use the name of the object, like, balls(soccer).kick. That means that you will always kick the right object no matter where its position is in the collection.

Question 8

What is a parameter?

Answer 8

A parameter in programming is great. It acts like an adverb in the English language. It describes more about the verb. He drove fast. Fast is the adverb to drove. In programming you would say Balls(“Soccer”).kick:Right. This would kick the soccer ball rightward. You can leave off the adverb if the default direction is right.

Question 9

How do you add parameters in Excel?

Answer 9

Shapes.addshape Left:=10, Top:=20, Width:=30, Height:=15. Do not use shortcuts such as shapes.addshape 10,20,100,15 because it is bad code form since most people will not remember which is which.

Question 10

How do you activate the developer tab?

Answer 10

Go to File> Options > Customize Ribbon> Check Developer Tab

Question 11

How do you create a message box?

Answer 11

?msgbox(“This is a message box.”)

Question 12

What is the immediate box?

Answer 12

The immediate box can run code immediately, as a test, without running a macro.

Question 13

What is the Locals box good for?

Answer 13

The locals box is useful for debugging and error handling.

Question 14

How to write comments on your code?

Answer 14

To write a comment just put a single quote ‘ then write your comment. The program will not execute anything on that line. Example: ‘This is a comment.

Question 15

How to start a Macro?

Answer 15

You start a macro by writing Sub > the macro name.

Example: Sub FirstMacro( ). Excel will automatically end the sub by inserting End Sub a few lines down.

Question 16

How do you run a VBA Macro?

Answer 16

You would go to the Developers Tab > The Visual Basic Editor > Create the Macro > Hit the Play Button in the top Bar

Question 17

How to Select a Range in VBA?

Answer 17

Sub FirstMacro( )
Range(“A1:A15)” = 15
Range (“g9”) = 150
End Sub

Question 18

In what direction does Excel execute code?

Answer 18

Excel executes code from the top to the bottom. In the same way we read. This is important especially when performing calculations to understand what will be executed first and then second.

Question 19

What does this code do?

Range (“A1, F10”) = 1000

Answer 19

This code will place 1000 in cells A1 and F10, but not in the cells as a range. To select cells in a range, you must insert a colon between the cell numbers. You could also do something like this: (A1:F10, F15). This would do a range and include one other cell.

Question 20

How do you add text to a cell?

Answer 20

Sub My_Text( )
Range(“A1”) = “Alison”
End Sub
All text must be surrounded by quotes.

Question 21

What is a name range?

Answer 21

A name range is a way to highlight a group of cells that you choose. If you want to select/highlight 10 rows down to 5 letter across, you would select it then go to the box at the top write which shows the cell number and click into it. Then you would be able to name this range anything. Afterwards, you would click into the same box to select your name range which would highlight all of the cells that you chose.

Question 22

How would you use a name range in VBA macro?

Answer 22

You could insert your name range right into a macro. For example, you could choose to set all cells in your name range to 10.
You would name the macro> Sub my_Named_Range( ),
type and insert the name of your name range Range(“my_table”) = 10,
then close macro with End Sub.

Sub my_Named_Range( )
Range(“my_table”) = 10
End Sub

Question 23

What does range( ).column and range( ).row do?

Answer 23

.column will return the column #, not letter that it selected.

.row will return the row # that is selected.

Question 24

What does 
Sub select_group( )
Range ("A1").select
End Sub
perform?

Answer 24

This just selects the cells in your range. This is a method since it performs an action.

Question 25

What does Range( ).Count function do.

Answer 25

This function will count the # of cells in a range. It is not like the Count function in excel or =count. The count function for a worksheet is called WorksheetFunction.Count.

Question 26

What does range( ).forumla do?

Answer 26

It will create a formula for the range that is selected in parenthesis.

Question 27

When you have double quotes inside of a number format how do you ignore it when running a macro?

Answer 27

If you have a double quote inside of a function, use another set of quotes to get Excel to ignore it. Example: “” It “”