Notes on the QRC VBA course. VBA is pretty useful for doing tedious tasks on excel itself.

VBA-notes

' Subroutines are run directly as macros (think of it as a main function)
Sub subroutine
' Do things here
Range("A1").Value = 5
End sub

' Declaring variables. Saves memory, recommended way.
Dim i as Integer
i = 1

' Control flow
If condition_1 Then
   result_1

ElseIf condition_2 Then
  result_2

Else
   result_else

End If


For d = 0 To 10 Step 0.1
    dTotal = dTotal + d
Next d


For Each wSheet in Worksheets
    MsgBox "Found Worksheet: " & wSheet.Name
Next wSheet


Do While CONDITION 
Loop

' Pretty cool: change properties of a particular object
With Worksheets("Sheet1").Cells.Font 
    .Name = "Arial" 
    .Size = 8 
End With


Select Case score
    Case Is >= 80
        result = "A"
    Case Is >= 70
        result = "B"
    Case Else
        result = "D"
End Select




' Manipulating Cells
' Cells(row, col) returns that particular cell object.
' Manipulating the cell is as simple as changing the properties of this object
' Range is a method that can return a range of cells e.g Range("A1", "Z100") 
' just as you would drag your mouse.

' Highlight all cells and change value to 5
With Worksheets("Sheet1").Cells
    .Interior.ColorIndex = 8
    .Value = 5
End With

' Add random numbers to a range of cells
Worksheets("Sheet1").Range("A1:D5").Formula = "=RAND()" 




' System interaction : You can open workbooks
Workbooks.Open("PATH/TO/XLS")