Leveraging your VB skills for MSOffice

Leveraging your VB skills for MSOffice
Introduction
Applications have become incredibly powerful
Modern applications have amazing customization potential
Recent high-end application programs have had extensive macro facilities
With Office ’97, Microsoft incorporated a standardized version of VB into Office
If you have any office products, you already have a form of VB!!!
In addition, you can go ‘the other way,’ incorporating office programs and features into programs you write in ‘pure’ vb.
This leads to nice interchangeability.
Why would you do this?
If your project will be a text editor, why not simply modify an existing editor (or word processor)
This allows you to add features to software.
Makes applications EXTREMELY customizable.
Interface is already familiar to users.

Keyboard Macros
description
Keyboard macros have been with us for a very long time.
They allow us to record a set of keystrokes,
assign them to a key combination, button, or menu item.
advantages
extremely simple to build
Very handy for the right kind of problem
Increase productivity in repeated tasks
disadvantages
Very little flexibility
No control structures
No looping, branching, user input
Creating them (in Word)
Go to Tools menu, choose Macros, record new macro
You will then get a screen that looks like this:

Give it a name, and you will see a small toolbar that looks like this:

This is a little VCR-like interface, which will help you control the operation of the macro.
You are in ‘record mode’ now, which means your actions are being recorded.
Perform the actions you want to record, then hit the (square) stop button.
Strategies for keyboard macros
It often makes sense to rehearse a few times before you record.
Try to find a good starting point. For example, if you want to change a line at a time, start at the beginning of a line, and be sure you end at the beginning of the next line.
Think about variations in your procedures. If, for example, you want to delete the last word of every line, you will not be able to just use the right arrow to move to the end of the line, as different lines have different lengths. You will need to use the ‘end’ key instead. Likewise, you could not use the backspace key to delete the last word, as the last words of each line will (presumably) be of different lengths.
Think about the context. Some macros will assume there is a document already in place, and some will actually need to create a new document.

Running them.
Go back to the tools -> macros menu and look at the macros. A list of macros will appear, with your new macro on the list. Double-click on this guy, and it will run.
Adding them to Word menus and toolbars
The tools_customize menu is a very powerful feature.
Even without custom macros, it enables you to significantly change the look and feel of Word.
It looks like this:

Open it up, and choose the commands option. Scroll down the list until you see Macros.
Select this, and you will see a (probably short) list of macros that you have defined.
Select your macro.
Assigning a key sequence
You can now click on the keyboard button, which allows you to select a new key combination for this command.
This will also let you scroll through existing key combinations.
This can be handy for ‘power users’
will probably not be used by novices, as they won’t know it’s there
Creating a toolbar button for a macro
Select the macro in the customize box
Drag it to the toolbar.
It will be placed in an existing toolbar
It may be better to create a new toolbar first
(A new toolbar is usually a better choice)
In the customize dialog, choose toolbars, then new.
The new toolbar can be moved around like any other toolbar
Editing a button
Once a button is created, you can edit it.
Right click on the button, and examine the options
Note you can edit the image,
This is a simple icon editor
You can do some very nice work with this.
You can also decide how the name and image shows

How VB fits in
Discussion
Macro editing is fine, but more control would be nice.
Specifically, we would like some real programming (control structures, file handling)
Some nicer interface design would be good too.
Viewing your macros in the VB editor
In the tools_macros window, you will see the VB editor as an option
Click on it, and you will see an old friend.
Note this is a SPECIAL version of VB.
It is NOT exactly the same program you have been using as VB
It is very close, as you can see.
Especially note the project window.
Mine looks something like this:

Notice how similar it is to the project window in straight VB.
You might need to play around with it a bit to see all the parts
You will note something called ‘Normal’ which contains modules.
These modules are very much like VB Modules.
(because that’s what they are, essentially!)
When you create macros, they are by default attached to the ‘Normal’ template
This is word’s default template.
Macros attached to it will work in every document.
Don’t put special purpose macros here.
They should go to specific documents or projects
In the project section, you will see ‘word objects’ and ‘thisDocument’.

Example with user Form

The code…
'code for 'dear john' dialog

'expects cmdOK, cboReason, txtRecip
'designed to be in Word

Private Sub cmdOK_Click()
    'Create
    Dim doc As Document
    Set doc = Documents.Add
    doc.Activate
    doc.Range.InsertAfter "Dear "
    doc.Range.InsertAfter txtRecip.Text
    doc.Range.InsertParagraphAfter
    
    doc.Range.InsertAfter "I must leave you because of "
    doc.Range.InsertAfter cboReason.Text
    doc.Range.InsertParagraphAfter
    doc.Range.InsertAfter "Love, "
    doc.Range.InsertParagraphAfter
    doc.Range.InsertAfter "      Me."
End Sub

Private Sub UserForm_Activate()
  cboReason.AddItem "bad hair"
  cboReason.AddItem "bad breath"
  cboReason.AddItem "bad karma"
  
End Sub

Functions and Excel

The grader functions…
'Excel Grading macros in VBA

Function gradeOf(cell) As String
  'Looks at a cell, returns a letter grade using my standards
  
  Dim grade As String
  
  Select Case cell
    Case Is < 0.65
      grade = "F"
    Case Is < 0.7
      grade = "D"
    Case Is < 0.74
      grade = "C-"
    Case Is < 0.78
      grade = "C"
    Case Is < 0.8
      grade = "C+"
    Case Is < 0.84
      grade = "B-"
    Case Is < 0.88
      grade = "B"
    Case Is < 0.9
      grade = "B+"
    Case Is < 0.94
      grade = "A-"
    Case Is < 0.98
      grade = "A"
    Case Else
      grade = "A+"
  End Select
  gradeOf = grade
End Function

Function countAs(theRange) As Integer
  'Given a range of cells, counts how many As Are in that range
  Dim curVal
  
  countAs = 0
  
  For Each curVal In theRange
    'MsgBox curVal
    If InStr(curVal, "A") Then
      countAs = countAs + 1
    End If
  Next
  
End Function

Function countBs(theRange) As Integer
  'Given a range of cells, counts how many As Are in that range
  Dim curVal
  
  countBs = 0
  
  For Each curVal In theRange
    'MsgBox curVal
    If InStr(curVal, "B") Then
      countBs = countBs + 1
    End If
  Next
  
End Function

Function countCs(theRange) As Integer
  'Given a range of cells, counts how many As Are in that range
  Dim curVal
  
  countCs = 0
  
  For Each curVal In theRange
    'MsgBox curVal
    If InStr(curVal, "C") Then
      countCs = countCs + 1
    End If
  Next
  
End Function

Function countDs(theRange) As Integer
  'Given a range of cells, counts how many As Are in that range
  Dim curVal
  
  countDs = 0
  
  For Each curVal In theRange
    'MsgBox curVal
    If InStr(curVal, "D") Then
      countDs = countDs + 1
    End If
  Next
  
End Function

Function countFs(theRange) As Integer
  'Given a range of cells, counts how many As Are in that range
  Dim curVal
  
  countFs = 0
  
  For Each curVal In theRange
    'MsgBox curVal
    If InStr(curVal, "F") Then
      countFs = countFs + 1
    End If
  Next
  
End Function