Programming DifferencesProbably 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 wont 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.
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: 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. 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). 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.
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). The selected text is reversed using the ReverseText macro.
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:
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. |
|
![]() |
![]() |
|||