1)



1) VBA = Visual Basic for Application = code that is “behind” the scenes in Excel

2) Is VBA the same as Visual Basic or Basic code language?

a. No. It is similar in some ways such as If constructs

3) Code = procedure = computer code = VBA = Macro = these are all synonyms

4) In Excel, Macro = VBA code whether or not the code was created with the Macro recorded in Excel or it was created in the VBA Editor

5) Behind the scenes in Excel is the VBE

6) VBE = Visual Basic Editor

7) VBE is where you write and view the code

8) From Excel to VBE:

a. Alt + F11 ( Takes you from Excel to the VBE

9) From VBE back to Excel:

a. Alt F11 ( Takes you from VBE to Excel

10) VBE:

a. [pic]

11) In Excel:

a. To Add Developer Ribbon

[pic]

Figure 1: Developer Ribbon that was added in the Excel Options area.

To add the Developer Ribbon you would use the keyboard shortcut to get to Excel Options as follows: Alt + F + I (Tap the Alt key, then tap the “F” key, then tap the “I” key). Excel Options is also accessible by clicking on the Orb and then click on the Excel Options button. After you open the Excel Options dialog box, you would check the “Show Developer tab in the Ribbon” checkbox and then click the Open button and the lower right corner. See Figure 19:

[pic]

Figure 2: Excel Options

b. To change security level to allow VBA: Developer Ribbon, Security button:

i. Macro Security:

1. Very High

a. “Sandbox”

i. Network administrator sets up protected network directory (trusted location) and only macros from the sandbox can be used

ii. Theory: viruses can’t get into trusted area

2. High

a. You choose to run trusted (digitally signed) macros

i. Requires buying digital certificate program such as VeriSign

3. Medium

a. You choose whether to Disable or Enable

4. Low

a. All macros can run

ii.

c. To use the Macro recorder: Developer Ribbon, Record Macro button

i. Relative Reference button on

1. On: Relative cell references (uses OFFSET function)

ii. Relative Reference button off

1. Creates absolute cell references (Uses RANGE function)

iii. After you turn the Macro recorder on, it watches even move you make and records your actions in “code”.

d. Alt + F8 ( Open Macro List

e. Assign Macro to button:

i. [pic]

12) In VBE:

a. Run code:

b. F5

i. Cursor in code, runs code

ii. Cursor not in code, opens Macro dialog box

c. F8:

i. Run code one line at a time to help find problems

d. Break Mode occurs after a line of code has a mistake

e. Alt R + R

i. Reset, which takes you from Break mode back to normal mode

f. Most all of the same keyboard shortcuts for Copy, Paste, navigating and other actions are the same in the VBE as they are in Excel.

13) Example of code:

'4th code

'by MGirvin

'9/8/2007

'Number literal and String literal example

‘Takes use input and squares the number, then reports back in message box

Sub MathGenius()

TheNumber = InputBox("Please type is a number that will be squared...")

Square = TheNumber ^ 2

MsgBox "The Square of the " & TheNumber & " is " & Square

End Sub

-----------------------

Project Explorer (Ctrl + R)

Lists objects such as worksheet, workbook, Modules

Properties of objects

(F4)

Programming “code window” (F7)

Object list

Object list

Apostrophe (‘) tells the code window that this line is not code. It is very important to

“Sub” means subroutine. This word starts most code

Name of code followed by two parenthesis: MathGenius()

Variable (no spaces allowed): TheNumber and Square

Words that you type that Excel knows what to do with. If you type “Input” Excel knows to create a dialog box that allows the user to enter a number. If you type “MsgBox”, Excel knows to create message box. (Method and function)

“End Sub” is necessary to end the subroutine

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download