Excel - Basic Vba

Front 1

VBA

Back 1

SUB NameRange ()

formula

END SUB

Front 2

Running, Stepping into, Stopping Macros

Back 2

F5 = Runs Macro

F8 = Steps into Macro

STOP = add into Macro to stop operation at certain point

Front 3

RANGE OBJECT

Back 3

= Range (A1:C5) = "quotes if text"

*This tells excel where to insert ""

Front 4

Code for Range Object

Back 4

Sub InsertRange()

Range("a1:a6") = "hello"

End Sub

*numbers do not need text

Front 5

CELL OBJECT

Back 5

Sub insertCellObject()

Range(Cells(1, 1), Cells(6, 6)) = "GO AWAY"

Cells(3, 1) = 5

End Sub

*rows come first, then columns

Front 6

Using Range Object in conjunction with Cells

Back 6

You can type in a range object using cells

Range(Cells(1, 1), Cells(6, 6)) = "GO AWAY"

OR

A range using the exact object

Range("A1:F6") = "GO AWAY"

Front 7

NAMING A RANGE

Back 7

You can name a range and use this in equation

Sub ameliasRange()

Range("ameliasRange") = 47

End Sub

Front 8

In VBA - when excel

Capitalises

Back 8

Capitalises - this means that excel has predefined it and you can not mess with it eg date + hit enter = Date

Front 9

FOR & NEXT

VARIABLES = x

Back 9

For x = 1 To 10

Cells(x, 1) = x + x * 5 [in the first column, moving down x rows]

Cells(x, 2) = Date + 1 * x

[in the second column, moving down x rows]

Cells(x, 3) = "hohoho"

Next x

End Sub

*loops until x = 10,calculate outcome mathimatically

Front 10

FOR & NEXT

Back 10

*starting at x = 2, so that you can avoid Headers. ie It wont overwrite

* Look at the x operation,

=x+1 = adds 2 to x

=x+x*1 = adds =2+2*" = 5

Front 11

IF & THEN

Back 11

Sub If_Next_Else()

For X = 3 To 10

Cells(X, 1) = Date + 2
Cells(X, 2) = X + 5 * X


If Cells(X, 2) > 50 Then

Cells(X, 3) = "WIN"

End If

Next X

End Sub

Front 12

IF & THEN & ELSE

Back 12

Sub If_Next_Else()

For x = 3 To 10

Cells(x, 1) = Date + 2
Cells(x, 2) = x + 5 * x


If Cells(x, 2) > 50 Then

Cells(x, 3) = "WIN"


Else: Cells(x, 3) = "lost"
End If

Next x

End Sub

Front 13

Other VBA Tips

Using VBA

Back 13

-Ctrl J - Brings up List Property/Methods

-Comment Block and Uncomment Block. You can comment out lines of code and and they are skipped over

-ToggleBreakpoint F9

-Object Browser F2 / F7 gets you back to code

-Property Window F4 (propertys of the worksheet)

Front 14

Create Message Box

Back 14

Sub Message_Box()

MsgBox "Hello World!", vbOKOnly, "Howdy"
End Sub

*The message, the options, the header of msg