More VBA

Let's start by recording a new macro

Something that will put a time stamp is a cell.
So Select cell A1
DO NOT Select Use Relative Refences
Select Record macro in the Code command group of the Developer Tab
- Call it time
- Make the Shortcut ctrl-shift-t
Open the VBA editor window for Module1
=now()
Mark the cell as bold.
Stop recording macro by pressing Stop Recording in the Code command Group of the Developer tab.
Press ctrl-shift-T
- Note, nothing happened.
- Arg, add a new worksheet and try again
- What is happening

Let's try this again but this time select Use Relative References

Note the difference in the code.

I see this as a great way to "cheat" when writing macros.

Just like with most HTML you can view source code.
You can record macros and watch what they do.

Can you find the commands to

Change the font color of the active cell?
Change the font to bold, italic, underline?

By the way, if you don't want to use a shortcut for a macro,

On the code workgroup click on the Macros button.
this gives you access to the list of macros.

Personal Macro Workbook

There is a personal macro workbook.
You can store macros there.
It is called personal.xlsb
It is stored on your hard drive so it will not move with you from workstation to workstation.
I have some fear that it might go away as you change versions of the OS or windows.
- It was a pain to eliminate.
You can also map your personal macros to buttons on the ribbon
See pages 933 for instructions.

He seems to be timid about coding.

He suggests a combied record/code method for creating macros.
I think this is because he is addressing non-coders.
You should not have this problem.

In general

Control structures exist in all languages
- Some form of if-elseif-else
  - VB marks control blocks , you know the {} stuff in C with the structures.
  - ```
  If (expression) Then
      stmt
  End If
  
  If (expression) Then
      stmt 
  Else
      stmt
  End If
  
  If (expression) Then
      stmt
  ElseIf (expression) Then
      stmt
  Else
      stmt
  End If
```
- Given
  - ActiveCell.Value is the value of a cell
  - ActieCell.Font.Color = RGB(r,g,b) sets the color of the font in a cell.
- Can you create a macro that will
  - color positive numbers green in italics,
  - negative numbers red in bold
  - and zero blue and underlined?
A random font color
- Rnd() generates a number between 0 and 1 uniformly distributed.
- Int() turns a number into an int.
- Write a function that sets a cell to a random value.
- Use Selection.Font.Color not ActiveCell.Font.Color
Variables
- While not necessary, you can declare variables with a Dim statement.
- A reference
- This lets you control the scope, type and other aspects of the variable.
- Dim red As Integer
- Enter the following code
- ```
Sub Psyc()
    Dim red As Integer
    Dim green As Integer
    Dim blue As Integer
    
    red = Rnd() * 255
    green = Rnd() * 255
    blue = Rnd() * 255
    
    Selection.Font.color = RGB(red, green, blue)
End Sub	
	
```
- Notice, the use of Selection, not ActiveCell
  - This will allow us to change all selected cells.
- Run this.

A MsgBox allows some simple form of communications

Reference
But basically you can give it a string to display.
Modify your code

    blue = Rnd() * 255

    msg = "red " & red & " green " & green & " blue " & blue
    MsgBox (msg)

    Selection.Font.color = RGB(red, green, blue)

Try it now.

The debugger.
- Select a range of cells
- Click in Psyc()
- Select Debug then Step Into
- Notice F8 is the step button.
- Press F8 Twice until green= Rand() * 255
- Hover over red, green, blue, notice the values.
A selection is a property of the active object.
- Reference.
- It could be Nothing.
- TypeName returns a string identifying the type of the selection.
- Build a selectd object identifier, and try pictures, ranges, cells, ...
- As we have seen above, we can set the values of a selection.

A Range is a collection of cells

reference
Using a range we can loop over values in a selection.

Sub Funky()
    If (Typename(Selection) = "Range") Then
        For Each cell In Selection
            cell.Font.color = RGB(Rnd() * 255, Rnd() * 255, Rnd() * 255)
    	    cell.value = Int(Rnd() * 50)
        Next cell
    End If
End Sub

There is also a for loop

Sub Fact()
    Sum = 0
    prev = 1
    tail = 1
    For i = 0 To ActiveCell.Value
        tail = prev
        prev = Sum
        Sum = tail + prev
    Next i
    MsgBox (Sum)
End Sub

And a for loop

Sub Sum()
    Dim i, total
    i = 0
    total = 0
    While (i <= ActiveCell.Value)
       total = total + i
       i = i + 1
    Wend
    MsgBox (total)
End Sub