Programming Differences

Probably the best way to illustrate how Visual Basic and VBA differ in the way they are programmed is to create two separate but similar projects, one using Visual Basic and one using VBA. The first project will be created in Visual Basic. It will be so simple that you probably won’t even want to try it out.

The VB program uses a TextBox control and a CommandButton control. When the CommandButton is clicked, the text in the TextBox is parsed (separated into words) and reversed. For example, if the phrase Let the fun begin was entered, it would be changed to begin fun the Let. Not the most useful program, to be sure, but it will serve to illustrate the differences between a VB and a VBA program.

In this text, VBA projects are often referred to as programs. However, in VBA they are usually called macros. In this context, you can think of a macro and a program as being synonymous.

The interface for the VB program will look like the one in Figure 39.4 and as stated earlier it will use one TextBox control (txtInputArea) and one CommandButton control (cmdReverse). Listing 39.1 shows all the code for the program:

Figure 39.4

The user interface for the Visual Basic version of the Reverse Text program.

Listing 39.1. - The code for the Visual Basic version of the Reverse Text program.

Private Sub cmdReverse_Click()
Dim intSpacePos As Integer
Dim intLastPos As Integer
Dim strNewWord As String
Dim strNewText As String
' You can set this value depending on your preferences.
' If you want to have this routine strip out any non-
' alphanumeric characters (A-Z, a-z, 0-9, Space), then
' set it to True. Otherwise, set it to False.
booAlphanumericOnly = True
' If there's nothing in the text box, exit the routine.
If txtInputArea.Text = "" Then Exit Sub
' Add a space to the end of the text so it can parse
' the last word.
txtInputArea.Text = txtInputArea.Text & Chr(32)
' Parse the contents of the text box (txtInputArea).
Do
    intSpacePos = InStr(intLastPos + 1, _
        txtInputArea.Text, Chr(32))
    If intSpacePos Then
        strNewWord = Mid(txtInputArea.Text, _
            intLastPos + 1, intSpacePos - intLastPos)
        ' Strip out non-alphanumeric characters?
        If booAlphanumericOnly Then
            strNewWord = StripPunctuation(strNewWord)
        End If
        ' Add the new word to the front of the string,
        ' so the string of words becomes reversed.
        strNewText = strNewWord & strNewText
    End If
    intLastPos = intSpacePos
Loop Until (intSpacePos = 0)
' Assign the new string (which should be reversed) back
' to the text box.
txtInputArea.Text = strNewText
End Sub
Function StripPunctuation(strWordIn) As String
Dim bytChar As Byte
Dim intCharPos As Integer
Dim strNoPuncWord As String
strNoPuncWord = ""
' Loop through the current word and discard anything
' that isn't alphanumeric or a space.
For intCharPos = 1 To Len(strWordIn)
    bytChar = Asc(Mid(strWordIn, intCharPos, 1))
    Select Case bytChar
        Case 32:
            ' Add spaces (ASCII 32).
            strNoPuncWord = strNoPuncWord & Chr(32)
        Case 48 To 57:
            ' Add characters 0-9 (ASCII 48-57).
            strNoPuncWord = strNoPuncWord & Chr(bytChar)
        Case 65 To 90:
            ' Add characters A-Z (ASCII 65-90).
            strNoPuncWord = strNoPuncWord & Chr(bytChar)
        Case 97 To 122:
            ' Add characters a-z (ASCII 97-122).
            strNoPuncWord = strNoPuncWord & Chr(bytChar)
    End Select
Next intCharPos
StripPunctuation = strNoPuncWord
End Function
Although the code is somewhat lengthy, the program is still simple. It loops through the text in the text box (txtInputArea) looking for spaces (also known as parsing). When it finds a space, it extracts a word and adds it to the beginning of a string (strNewText). When the loop is done, strNewText contains all the words in the text box, except reversed.

A Boolean variable (booAlphanumericOnly) is used to determine whether nonalphanumeric characters should be stripped out of the string. If alphanumeric characters, such as punctuation, are left in, then the text string, This is a test. would be reversed to test. a is This. Stripping out the nonalphanumeric characters fixes this and results in test a is This. If booAlphanumericOnly is set to True, then the function StripPunctuation is called during the parsing loop. It strips out any unwanted characters.

If you want to try out the Visual Basic program, go ahead. Just remember to call the TextBox control txtInputArea and the CommandButton control cmdReverse.

Next, a similar program will be created using VBA. Instead of using text entered into a TextBox, however, the VBA program will use any currently selected text. The host application will be Microsoft Word, so you can select any text you want reversed in a document and then invoke the program and have it do its thing.

To implement the reverse text processing code in VBA, start up the host application, which in this case will be Microsoft Word. Choose Tools[hr]|[hr]Macro and then select Macro from the pop-up window (or press the shortcut key, Alt+F8). This will display the Macros dialog box (see Figure 39.5), which lists all the macros available for the current document. Unless the document that you are using was generated from a template that included macros or you have been fooling around with VBA on your own, the list of macros should be empty.

Figure 39.5

The Macro dialog box, which lists all the macros available to the current document.

To create a new macro, type in a name (ReverseText is suggested) and click the Create button. After a few moments, Word brings you directly to the VBA environment. It also provides you with an empty Sub procedure for your new macro as well as comment lines that indicate when the macro was created (see Figure 39.6).

Figure 39.6

When you add a new macro, the VBA environment is called up and an empty Sub procedure is added for you.

You can now add the code for the ReverseText Sub procedure (see Listing 39.2). This is essentially the same code that was used with the VB version of the program, but with a few minor - but important - changes.

Listing 39.2. - The code for the ReverseText Sub procedure, a Microsoft Word macro.

Sub ReverseText()
'
' ReverseText Macro
' Macro created 02/25/98 by Rob Thayer
'
Dim intSpacePos As Integer
Dim intLastPos As Integer
Dim strNewWord As String
Dim strNewText As String
Dim strSelText As String
' You can set this value depending on your preferences.
' If you want to have this routine strip out any non-
' alphanumeric characters (A-Z, a-z, 0-9, Space), then
' set it to True. Otherwise, set it to False.
booAlphanumericOnly = True
' Add a space to the end of the selected text so it can parse
' the last word.
strSelText = Word.Selection & Chr(32)
' If there's nothing in the selection, exit the routine.
If RTrim(strSelText) = "" Then Exit Sub
' Parse the contents of the selection (strSelText).
Do intSpacePos + 1, _ strSelText, Chr(32)) If intSpacePos Then strNewWord="Mid(strSelText," _ intLastPos + 1, intSpacePos intLastPos) ' Strip out non-alphanumeric characters? If booAlphanumericOnly Then strNewWord="StripPunctuation(strNewWord)" End If ' Add the new word to the front of the string, ' so the string of words becomes reversed. strNewText="strNewWord" & strNewText End If intLastPos="intSpacePos" Loop Until (intSpacePos="0)" ' Assign the new string (which should be reversed) back ' to the selection. Word.Selection="strNewText" End Sub 
The biggest difference here is that the txtInputArea control is no longer used to provide the text to be processed. Instead, a new string (strSelText) is used. It gets its value from another object, Word.Selection. This is important, and it merits further discussion.

In VBA, you often utilize objects and classes from the host application (in this case, Word). All the Office97 applications use a wide variety of different objects. In Word, there is a Documents collection, a Document object, an ActiveDocument object, and many others. There is also a Selection object, which is what is being used here. The Selection object contains any text currently selected.

The VBA macro you are constructing here is applied to the current selection. However, it could just as easily be applied to the entire document if, instead of assigning strSelText the value of Word.Selection, it was assigned the value of Word.ActiveDocument.Content, which refers to the entire content of the whichever document is active. Using the current selection, however, makes more sense.

To become fluent in using VBA, you must be familiar with the various objects and classes implemented in the host application or applications for which you want to create macros. This is a key point. You wouldn't be able to use Visual Basic effectively if you did not have an understanding of VB's objects (forms, text boxes, command buttons, and so on). Likewise, you cannot use VBA effectively if you do not understand the objects available to it. A discussion of Office97 objects and classes is beyond the scope of this chapter. Consult a book on VBA programming for more information (Using Visual Basic for Applications Special Edition by Que Publishing is recommended).

To continue with the VBA project, the StripPunctuation function needs to be added next (see Listing 39.3). This is identical to the function used in the Visual Basic project created earlier.

Listing 39.3. - filename.ext - The StripPunctuation function, which is used by the ReverseText Sub procedure (a Word macro).

 

Function StripPunctuation(strWordIn) As String
Dim bytChar As Byte
Dim intCharPos As Integer
Dim strNoPuncWord As String
strNoPuncWord = ""
' Loop through the current word and discard anything
' that isn't alphanumeric or a space.
For intCharPos = 1 To Len(strWordIn)
    bytChar = Asc(Mid(strWordIn, intCharPos, 1))
    Select Case bytChar
        Case 32:
            ' Add spaces (ASCII 32).
            strNoPuncWord = strNoPuncWord & Chr(32)
        Case 48 To 57:
            ' Add characters 0-9 (ASCII 48-57).
            strNoPuncWord = strNoPuncWord & Chr(bytChar)
        Case 65 To 90:
            ' Add characters A-Z (ASCII 65-90).
            strNoPuncWord = strNoPuncWord & Chr(bytChar)
        Case 97 To 122:
            ' Add characters a-z (ASCII 97-122).
            strNoPuncWord = strNoPuncWord & Chr(bytChar)
    End Select
Next intCharPos
StripPunctuation = strNoPuncWord
End Function
To try out the macro, exit VBA (don't worry; you won't lose your work) and go back to the Word document. Type in some text and then select it (see Figure 39.7). Press Alt+F8 to display the Macro dialog box. Note that the ReverseText macro is now listed.

Figure 39.7

Selected text in Microsoft Word, which will be processed by the ReverseText macro.

With the ReverseText macro selected, click the Run button. The ReverseText procedure will then be executed, and the selected text will be reversed (see Figure 39.8).

Figure 39.8

The selected text is reversed using the ReverseText macro.

The ReverseText macro is somewhat limited in that it is only designed to work with single lines of selected text. Modifications to facilitate the handling of multiple lines of text can be made relatively easily, but were not included originally in an attempt to keep the code as simple as possible.

As you can see, the VBA macro and the VB program created earlier do basically the same thing; they are just implemented differently. To review, note the differences between the VB and VBA projects:

  • The VB program used the contents of a TextBox control for the text to be processed. The VBA macro uses the Word object Word.Selection.
  • In the VB program, the main processing code was contained in a command button's Click event. In the VBA macro, the code is contained in a Sub procedure.
  • The VB program uses a form with controls for its user interface. The VBA macro works from within its host application (Microsoft Word). Essentially, Word is utilized as its user interface.

Consider the last difference pointed out in the preceding list. The VBA macro has no user interface. Instead, it uses Microsoft Word and works "behind the scenes." Of course, this is not always the case. There might be times when you want a user interface to be displayed, perhaps to enable the user to select different options or customize the macro that will be performed. Because VBA works with a host application, a form is not automatically displayed by default like it is in a VB program. So how do you implement a form-like interface in VBA?

First, you design the form[md]that much is obvious. In VBA, a form is called a UserForm. To display it, you would use a line of code such as the following:

UserForm1.Show

This code would be placed in the Sub procedure of the macro. So if it were to display a form for the ReverseText macro, the ReverseText Sub might look like this:
Sub ReverseText()
    UserForm1.Show
End Sub
The form (UserForm1) could contain controls that in turn call other procedures or respond to events, just like they do in Visual Basic. After forms are used, VBA becomes easier to understand to VB programmers.

As you get into VBA and start developing more complex macros, you'll find other differences from Visual Basic. But using what you've learned in this chapter, you should have an understanding of how VBA works. Your skills as a VB programmer will take you the rest of the way.

Top Home