Notes from Instructor Demos During Class Use this as a quick ref to jog your memory on the syntax. There may be some spelling mishaps.
Cells and Range
Cells() format is "down, then across" (as opposed to "down, not across", which should be the motto of anyone using VBA). Cells(8,15) refers to 8 down, 15 across. (or H9)
' Inserting Data Via Cells
Cells(2,1).Value = "Cat"
Cells(2,2).Value = "In"
Cells(2,3).Value = "The"
Cells(2,4).Value = "Hat"
' Inserting data via Ranges
Range("F1").Value = "I"
Range("F2").Value = "Am"
Range("F3").Value = "Sam"
Range("F5:F7") = 5
Variables
Dim name As String
name = "Gandalf"
Dim title As String
title = "The Great"
Dim fullname As String
fullname = name + " " + title
MsgBox (fullname)
Dim age1 As Integer
Dim age2 As Integer
age1 = 5
age2 = 10
Dim price as Double
Dim tax As Double
price = 19.99
tax = 0.05
Dim lightspeed As Long
lightspeed = 299792458
MsgBox (age1 + age2)
Cells(1,1).Value = price * (1+ tax)
MsgBox ("I am " + Str(age1) + " years old.")
Dim money_grows_on_trees As Boolean
money_grows_on_trees = False
Arrays
Dim ingredients(5) as String
ingredients(0) = "Chocolate Bar"
ingredients(1) = "Peanut Butter"
ingredients(2) = "Jelly"
ingredients(3) = "Macaroni"
ingredients(4) = "Potato salad"
ingredients(5) = "Dragonfruit"
' defining this extends the array with another index value
MsgBox (ingredients(4))
MsgBox (ingredients(0))
'Define a string
Dim Shakespeare As String
Shakespeare = "To be or not to be. That is the question"
'Define array
Dim words() As String
' Split that previous string into it's elements and assign the elements to the array, separated by a "space" character
Words = Split(Shakespeare, " ")
MsgBox (Words(5))
Conditionals
If Range("A2").Value > Range("B2").Value Then
MsgBox ("Num 1 is greater than Num 2")
End if
If Range("A5").Value > Range("B5").Value Then
MsgBox ("Num 3 is greater than Num 4")
ElseIf Range("A5").Value < Range("B5").Value Then
MsgBox ("Num 4 is great4er than Num 3")
Else
MsgBox ("Num 3 and Num 4 are Equal")
End If
If (Range("A8").Value > Range("C8").Value And Range ("B8").Value > Range("D8").value Then
MsgBox ("Both Num 5 and Num 6 are greater than Num 7")
End Sub
For Loop
Dim i As integer
For i = 1 To 20
' Going DOWN
' Places value of 1 in A1 to A20
Cells(i,10).Value = 1
' Going ACROSS
' Places value of 5 in A1 to T1
Cells(10,i).Value = 5
Cells( i+1,2 ).Value = i+1
Next i
Conditional Loop (modulus)
For i = 1 To 10
' Use Modulus functions to determine if a number is divisible by 2
If Cells(i,1).Value Mod 2 = 0 Then
Cells(i,2).Value = "Even Row"
Else
Cells(i,2).Value = "Odd Row"
End If
Next i
Nested For
Dim TargetStudent As String
For i = 1 To 3
For j = 1 To 5
MsgBox ("Row: " & i & " Column: " & j & " | " & Cells(i,j).Value)
Next j
Next i