Understanding Scripting of Web Services

In many respects, VBA is a scripting or macro language, rather than a full development language such as Visual Basic or C#. The host application interprets the VBA code when you run the macro based on an event such as selecting a menu entry, clicking a button, or opening a file. Consequently, the host application performs every task in real time ”you can't decide much in advance. Although VBA is far more powerful than Web scripting languages such as JavaScript, it still has some of the benefits and problems of any scripting language. The following sections discuss how VBA scripting can affect your application and one technique for circumventing scripting issues.

Advantages and Disadvantages of VBA

VBA is a good scripting language and far more capable than VBScript (but less capable than Visual Basic). One of the biggest advantages of using VBA is that you get instant feedback. You don't have to compile the application, nor do you have to do anything fancy to see the result of your code ”all you need to do is run it. This means the development cycle is much shorter and you can get your application going faster.

Unlike JavaScript and browser applications in general, most VBA environments provide good debugging. You run the program in a special environment that lets you see variables and the short- term results of operations. This is especially helpful when working with Google Web Services because you can see how your code interacts with the Web service without resorting to using on-screen data presentation or similar tricks required for browser applications in many cases.

A new developer can also learn VBA relatively quickly compared to a full-fledged programming language such as Visual Basic or C#. VBA is a subset of Visual Basic, which means it has fewer commands to learn. However, even with fewer features, VBA developers can create robust Google Web Services applications. Many of the advanced features that VBA lacks don't affect applications of this type.

The biggest disadvantage of using VBA is performance. Most interpreted languages are slower than compiled languages because an interpreter (a special program that reads the code) must convert the human-readable information into machine code during runtime. VBA developers use a variety of techniques to improve performance, but even great programming techniques can only improve performance so much. At some point, the application runs as fast as possible and you have to live with the performance implications.

From a Google Web Services perspective, VBA also presents display problems. You can only display data in the way that the host application allows. It's possible to force the application to display data in nonstandard ways, but only at the cost of long development time and poor performance. Choosing the correct application for your needs is essential when using VBA.

An Alternative to VBA

If you want to work with Google Web Services within any Office product, you need to use VBA because Google Web Services requires SOAP for access (unless you want to use a Web server or other intermediary). However, it's possible to obtain a lot of data from Google so long as you use their native format. It's possible that you can accomplish some tasks without resorting to programming by adding a simple URL to your report or other Office document. The problem, of course, is that you always give something up in order to gain simplicity. Although an embedded URL does make it easier for someone to get data from Google, the data is still in Google's format, not your preferred format.

Most versions of Office support hyperlinks in some fashion ”you can even use them in Office 97 products. To create the hyperlink in an application such as Word, right-click the location in the document that you want to contain the hyperlink and choose Hyperlink from the context menu. When you see the Insert Hyperlink dialog box shown in Figure 5.1, select the Place in This Document option and type the Google search URL you want to embed. Chapter 2 discusses all of the search arguments you can use. All you need to do is combine them with the Google search URL of http://www.google.com/search?q=. For example, if you want to look for all occurrences of VBA on my Web site, you'd type http://www.google.com/search?q=VBA+%22DataCon+Services%22+site%3Awww.mwt.net. Notice that all of the special characters are escaped (see the "Sending Special Characters Using URL Encoding" section of Chapter 3 for details). Click OK. Now, whenever you Ctrl+click the hyperlink, Word will request the information from Google Web Services.

Figure 5.1: Use the Insert Hyperlink dialog box to add a hyperlink to your application.

You can also create special searches using the Advanced Search page. In this case, you'll find that the search URL is longer. It's easy to figure out the pieces that you actually need and use them for a hyperlink. For example, you could also use http://www.google.com/search?as_q=VBA&as_epq=DataCon+Services&as_sitesearch=www.mwt.net to search for VBA topics on my Web site.

Older versions of Office might require that you use the Insert Hyperlink command to display the Insert Hyperlink dialog box shown in Figure 5.2. This dialog box works about the same as the one shown in Figure 5.1 ”it just doesn't include as many options. Again, type the URL that you want to receive from Google and click OK.

Figure 5.2: Older versions of Office might require that you create hyper-links using the menu system.