VBA Macros

• Continuing with Chapter 39
• There are two types of VBA macros
  • Sub macros can automate a task.
  • Function macros can add a new function to excel.
• Let's build one of each
  • On the Developer tab select Visual Basic in the Code Workgroup.
  • 
  • Select the Insert menu and insert a module
  • 
  • Type in the following code

  • Function World()
    	World = "Hello World!"
    End Function
    
    Sub Hello()
        ActiveCell.Value = "Hello World!"
        ActiveCell.Font.Color = RGB(255, 0, 0)
    End Sub
           

  • Go back to the worksheet and type =World() in a cell.
  • Try =length(World()) in another cell
• Naming the sub macro
  • On the Developer tab, select Macros from the Code Workgroup
  • 
  • Select the Hello macro and click on Options
  • 
  • In the Shortcut Key box put an H
  • And add a description
  • 
  • Now return to the worksheet, select a cell and press ctrl-shift-H
• Look these over, are you happy with the code?
• Let's introduce a syntax error
  • Delete one of the values from the RGB call
  • 
  • Try to call it (press ctrl-shift-H in a cell)
    • In excel, nothing happens but my cursor spins
    • In VBA it tells me I have a compile error
    • 
    • This will put me into the function with the editor pointing to where I have a problem.
    • Note, at the top of the window it shows I am debugging [break]
    • 
    • Fix the problem and exit debugging mode
    • Press on either continue or reset
    •