Visual Basic For Applications In Microsoft Office 2007


Not all macros are keyboard based. In addition to the keyboard-recorded macros, lots of macros provide additional commands that go further than the simple selection of menu options. Microsoft standardized the Visual Basic for Application (VBA) programming language several years ago to form the foundation behind macros.

When you record a macro, your Office application isn’t in fact recording your keystrokes and mouse clicks, even though that is the practical upshot of what it’s doing. In reality, every time you press a key, select from a menu, type text, or click a button, the application adds a new VBA command to that macro’s program. Until you learn a programming language, its programs can be cryptic in reality. For example, a macro you record that colors a selected paragraph’s background yellow and converts the paragraph’s first letter to a drop cap letter is actually a VBA program that looks like this:

Sub DropCap_YellowBN()

‘

‘ DropCap_YellowBG Macro

‘ Follows company standards for lead-in paragraphs so the initial letter


‘ has a drop cap and the paragraph is colored with a yellow background.
‘

Selection.Shading.Texture = wdTextureNone

Selection.Shading.ForegroundPatternColor = wdColorAutomatic

Selection.Shading.BackgroundPatternColor = wdColorYellow

Selection.MoveLeft Unit:=wdCharacter, Count:=1

Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend

With Selection.Paragraphs(1).DropCap

.Position = wdDropNormal

.FontName = “+Body”

.LinesToDrop = 3

.DistanceFromtext = InchesToPointsi(0)

End With

Windows(“Computers and You”).Activate

End Sub


Recording your keystrokes and mouse clicks is a lot easier than writing all those VBA programming language statements! However, if you knew VBA, you could have written this code in its place of recording the macro and get the same results. In Office 2007, you get a complete Visual Basic environment from which you can write VBA programs that control almost every feature of your document work. From the Developer ribbon, you can click the Visual Basic button to see a programming environment open on your screen.




When you write a Visual Basic program to control an Office application, you’ll use Visual Basic’s own environment.


You might wonder how Visual Basic can supplement and add functionality to an Office application other than automating simple, regular tasks. After all, Excel supports VBA, but with all the worksheet power and commands that Excel already provides, how can a VBA program controlling Excel’s environment and worksheet add any advantage to the Excel user?

If all VBA did was automate simple, regular tasks in recorded macros, VBA would be a welcome addition to Office. Simple recorded macros, although, must run in the same environment and with the same set of worksheets and columns each time. The keyboard macro has no room for uncertainty. For example, what if one of your company divisions was to shut down for remodeling one month? The keyboard macro that you may have recorded to consolidate all your company’s four divisions would consolidate either a blank worksheet or an old one for the missing division’s new data, producing an error.

Because Visual Basic is a complete programming language, the employee who needs such a consolidated report, for example, can write a series of commands that handle unexpected conditions more elegantly than keyboard-recorded macros can. Perhaps the VBA program could read each worksheet and, if data other than zeros shows for the month totals, add that worksheet to the summary but ignore any other worksheet in which the division had no activity for the period. Such a macro—a Visual Basic program written in the VBA language—would work when a recorded macro would not.