We'll use the Worksheet_Change Event to look up contact details for a given name in a cell. This is a "live" link because whenever we change the cell and enter a new name, the event handler fires and looks up the phone number, address and email and returns it to Excel. In this example, it's put into a cell comment, but we'll also show how we can return this information into adjacent cells.

Since the event fires whenever the worksheet is changed, we'll set up Outlook as a module-level variable, so it will be instantiated the first time the event fires and stay open throughout the life of the workbook. That should speed up the operation of the workbook somewhat.

As a bonus, we'll also check the Global Address List (GAL) for contact details, if the name isn't in the local address book!

This event should be placed in the Sheet module for the sheet you want to monitor. It checks any changed cell; you probably want to change this behavior to only check the one or two cells you'll put contact names into.

Outlook is instantiated the first time the event fires, but is kept open by the module-level variable so we don't have to keep instantiating it every time the event fires. Of course, if you change the event handler to ONLY fire when certain cells are changed, you can change the Outlook variable scope to local (by moving it into the event handler) since it won't be instantiated as often.

First we try and get the contact from the local Address Book. If the contact does not exist, we check the GAL. Either way, we put the contact details into a comment for the given cell. The contents will vary, and that is reflected in the way the cell is commented.

This technique would work well if you have a sales worksheet with a data validated list of sales or customer service people. As you select each name, that person's contact details are filled in on the worksheet. It's best if you have them set up as Contacts, since more information is available through the ContactItem Object than the AddressEntry Object (using the Outlook Object Model).

Return phone number to adjacent cell

A slight alteration of this function is needed to return specific information to the worksheet. We don't check the GAL anymore, because it only returns name and email address. So the contact name must be in the Address Book for this event handler to be successful.

Link Excel cells to Outlook data fields is Copyright © Code For Excel And Outlook. All Rights Reserved.

More stuff:

• Where do I put my Outlook code?
• Contribute to this blog
• VBA Search Engine
• Site Map

The National Weather Service's National Hurricane Center tracks storms that occur in the Atlantic and Eastern Pacific oceans. Starting in July 2010, weather advisories were made available via RSS. Let's take a look at one and see what we can do with it.

A list of available RSS feeds from NHC is here.

The one we'll parse is the Atlantic (English) feed from the "Specific Tropical Cyclone Feeds by Basin" section. Here's an example of what it looks like when this article was written:

Because of the way items are listed in the feed, we'll need to start parsing from the ninth node. Of course, you can grab more information, but I felt that the first eight nodes were useless.

The NOAAWeather Function

This function returns all weather items available in the Atlantic (English) RSS feed from NHC/NOAA as a String array. As usual, I've preserved the early bound references so you can study the MSXML object model if you wish.

The API response is cached, so you'll need to pass a parameter of True (or clear your temp folder) to force a requery. And don't forget to include the helper functions in your project.

As I mentioned earlier, I felt the first nine nodes were useless, so the number of "rows" in the array is nine less than the total. Since there are eleven nodes, the total output will be two "records", with six pieces of information each. Then it's a "simple" matter of parsing the XML to grab the data we want.

Sample usage

Output

The output looks like this:

There are no tropical cyclones at this time.
No tropical cyclones as of Mon, 26 Jul 2010 21:55:14 GMT
Mon, 26 Jul 2010 21:55:14 GMT

http://www.nhc.noaa.gov/

http://www.nhc.noaa.gov/

nhcwebmaster@noaa.gov (nhcwebmaster)
Atlantic Tropical Weather Outlook

000

ABNT20 KNHC 261747

TWOAT

TROPICAL WEATHER OUTLOOK

NWS TPC/NATIONAL HURRICANE CENTER MIAMI FL

200 PM EDT MON JUL 26 2010



FOR THE NORTH ATLANTIC…CARIBBEAN SEA AND THE GULF OF MEXICO…



TROPICAL CYCLONE FORMATION IS NOT EXPECTED DURING THE NEXT 48 HOURS.



$$

FORECASTER ROBERTS/PASCH

Mon, 26 Jul 2010 17:47:23 GMT

http://www.nhc.noaa.gov/gtwo_atl.shtml

http://www.nhc.noaa.gov/gtwo_atl.shtml?201007261747

nhcwebmaster@noaa.gov (nhcwebmaster)

Helper Functions

These functions should be pasted into a standard module in the same project as the code above.

Exploring the NOAA API is Copyright © Code For Excel And Outlook. All Rights Reserved.

More stuff:

• Where do I put my Outlook code?
• Contribute to this blog
• VBA Search Engine
• Site Map

On Jon Fournier's blog there's a VBA procedure to create a mailto: link in a macro. I like it but I feel it could use some more detail. I'll present an alternate with a few changes.

Default Subject and Body

His procedure allows you to specify a blank subject and body when calling it. I prefer to use a default subject and body, especially since the procedure is part of a larger tool. I can prefill in the subject and body with information I (as the developer) need.

Email Validation

There is no validation on the email address whatsoever. I've already written code that validates email addresses and we'll put it to good use here.

URL Encoding

The body is not URL encoded, so (when I tested it) words will drop off the body and not appear in the email. We're going to URL encode the body string so that the entire text will appear in the message.

API Usage

The code uses a Windows API to run the mailto link. I prefer the Windows Script Host Object Model (WSHOM) to run the link. To me it's simpler. I didn't check if there's a difference in speed, it's just a personal preference.

The StartEmail Procedure

The code starts by assuming a subject and body. I tried using a constant to define the name of the app (as the subject) but VBA won't allow that. See below for additional functions needed by this procedure.

After validating the email address (see Validate email address below), the URL to be run is constructed. The message body is URL encoded so that nothing is dropped. For example, if I pass "Hello World" to the original procedure, the body will say "Hello" and "World" will be dropped because the space in the URL isn't encoded.

Finally, to run the URL I use the WSHOM's shell scripting abilities. See Shell Scripting using VBA and the Windows Script Host Object Model for more code samples. Like I said earlier, I find it simpler than the Windows API.

To URL encode the message body, download the URLEncode function and change this line:

to this:

Validate email address

To validate email addresses, I use a web API. This goes further than Regex (or trying to hardcode valid domains) by actually asking the mail server if the email address is valid.

WSHOM Shell Object

To return the Shell Object I use this function.

Sample usage

Mailto Links in VBA is Copyright © Code For Excel And Outlook. All Rights Reserved.

More stuff:

• Where do I put my Outlook code?
• Contribute to this blog
• VBA Search Engine
• Site Map

Problem: Every day I come in to work and intend to work eight hours. If I come in at 8:30 AM, and given an hour lunch, what time do I need to leave to work exactly eight hours?

Furthermore, if I come in a different time (or take a shorter lunch break), I still need to know what time to leave.

Calculate Elapsed Time

Figuring out the number of hours I worked is simple. Just subtract my start time from my lunch start time, and subtract my lunch ending time from the time I leave.

In this example, the formula in E2 is:

=((B2-A2)*24)+((D2-C2)*24)

The problem is that this method requires trial and error to guess the correct time to leave. You have to repeatedly enter a time value into cell D2 and see if cell E2 equals eight. There has to be a better way.

Turns out there is!

We can calculate the time we need to leave using a formula. All you need to do is change E2 to a value (i.e. just enter the number of hours to be worked in cell E2) and use this formula to calculate the end time.

The formula in cell D2 is:

=C2+((E2-((B2*24)-(A2*24)))/24)

(it can also be written simply as =C2+((E2-((B2-A2)*24))/24))

So you enter your start time, the time you leave for lunch and the time you come back from lunch, and the formula tells you what time you can leave!

The way it works is simple: We start with the ending lunch time, and we need to add a certain amount of hours to it to reach eight total hours. How many hours do we add? The difference between 8 and the number of hours we've already worked.

In the above example, I've already worked four hours, so to get to eight I need four more hours added to the end of my lunch break (because eight minus four equals four): 1:30 PM + 4 hours = 5:30 PM.

Note that cells A2:D2 are formatted as Time values. The division by 24 in the examples above is necessary due to the way Excel treats time values.

Named Cells

The hardcoded values and vague cell references are pretty scary. So I named the cells (and added a constant called HoursPerDay which equals 24). Here's what the final formula looks like:

=LunchEnd+((NumHours-((LunchStart-StartTime)*HoursPerDay))/HoursPerDay)

All of you clock punchers out there may start heaping praise in 3 … 2 … 1 … go!

Calculate Working Hours In Reverse is Copyright © Code For Excel And Outlook. All Rights Reserved.

More stuff:

• Where do I put my Outlook code?
• Contribute to this blog
• VBA Search Engine
• Site Map

In Add Data Validation to any worksheet using VBA I demonstrated a technique for … adding data validation to any range. Surprise!

Now we'll try and do something with those data validation ranges. What I'd like to do is select a cell that has data validation on it and return the list of valid members (assuming that the source data is a Range or name that refers to a Range).

For example, if cell A1 has the 'List'-style data validation applied to it, and the source data is H1:H10, I want to return the values in H1:H10.

So I wrote a function that takes a Range Object as an argument and returns the source data for the Data Validation as a Range Object.

The GetValidationRange Function

This function isn't as robust as it could be. For example, if you choose the "List" option and enter a set of values directly into the dialog box, the function will fail because it's expecting a Range or a name that points to a Range. We'll work on another version in a bit. But here goes:

Sample usage

Now I can retrieve all possible valid values for a given data validation cell. Suppose I have a cell with data validation applied (cell A1) and it's linked to a chart on the worksheet. I want to cycle through all the values and print the newly updated chart each time the source data (the cell with data validation) changes.

Retrieve Data Validation Values

Instead of a Range Object, let's retrieve the values associated with a data validation range. The advantage of this function is that it will return the possible values available to a cell regardless of where those values come from — a range, a range name, a formula, or a literal list of values.

Because of this, the function is declared As Variant. If the type is "List" or "Custom" and the source is a formula or range name, the range values are put into a Variant.

The only difference here is that the values from the range, or the values typed directly into the source box, are put into the array.

Sample usage

Download sample workbook with all of these functions and procedures.

Get Data Validation Range is Copyright © Code For Excel And Outlook. All Rights Reserved.

More stuff:

• Where do I put my Outlook code?
• Contribute to this blog
• VBA Search Engine
• Site Map

According to the article, API developers should

• Auto-generate Documentation
• Include Sample Code
• Show Example Requests and Responses
• Explain Authentication and Error Handling

They also provide an example site that follows these guidelines. I'd like to add some more best practices, and also discuss a few sites who don't play nice.

1. Readability

One of the most important aspects of documentation is readability. If your documentation (visually) is difficult to follow, I won't be able to understand or implement it. The FlightCaster developer docs are an example of this. They've done a great job cleaning up the documentation (see my FlightCaster article), but there are no tables or lines delimiting each section, and links are duplicated on other pages. It took hours to sort out what methods I had already implemented when going through it.

2. Accuracy

This is by far the most important attribute your documentation can have — it must be exhaustive, and it MUST reflect the way the API currently works.

The worst examples of API documentation I've found so far are:

1. World Bank

The documentation is organized and includes sample method calls, but has a few drawbacks:

• Uses two different API endpoint URLs (api.worldbank.org and open.worldbank.org) at different times
• Inaccurate URLs (some methods indicate an API key, while the documentation says no key is required)
• Inconsistent results format

Let me explain that last one. The Country Query page describes the return results from a Countries query:

• 3 letter ISO 3166-1 alpha-3 code code
• 2 letter ISO 3166-1 alpha-2 code code
• Name
• Region id
• Region Name
• Income Level
• Lending Type
• Capital City
• Longitude
• Latitude

That's ten data points, but if you look at the example query, there are only eight nodes:

<wb:countries page="1" pages="1" per_page="50" total="1">
  <wb:country id="BRA">
    <wb:iso2Code>BR</wb:iso2Code>
    <wb:name>Brazil</wb:name>
    <wb:region id="LCR">Latin America & Caribbean</wb:region>
   <wb:incomeLevel id="UMC">Upper middle income</wb:incomeLevel>
   <wb:lendingType id="IBD">IBRD only</wb:lendingType>
   <wb:capitalCity>Brasilia</wb:capitalCity>
   <wb:longitude>-47.9291687</wb:longitude>
   <wb:latitude>-15.7801476</wb:latitude>
 </wb:country>
</wb:countries>

If you look carefully, the 3 letter ISO 3166-1 alpha-3 code code (BRA) is actually an attribute of the country tag, not a tag in itself. Same for the Region ID (LCR). This means that to extract the information that the documentation says the API returns, you need to do a traditional loop plus additional parsing. You'll see the awkwardness when I finally post my World Bank API VBA sample code.

Also, and I realize this is nitpicking, but who writes tags like that? It looks more like a WSDL document than XML. While technically correct, it's an odd implementation.

2. OAuth

The self-described Authoritative Guide to OAuth 1.0 has very little instructions on how to actually implement it on any given platform. It's more of an "About" page than a "How To" page. The history of OAuth doesn't belong in a guide explaining how to use it; put it in a FAQ or somewhere else on the site for those that care. The "Beginner's" guide actually contains more sample code than the "Authoritative" Guide, but most of the Beginner's guide is lost in a wordy, generic explanation of how OAuth operates (and I do mean wordy). You have to read through the Core (whatever that means) if you want any hope of understanding how to implement it.

I still do plan on posting detailed walkthroughs for using OAuth with VB(A) for services like Twitter. But they aren't making it easy to learn how!

Both guides are in love with their own terminology and makes wild assumptions about the technical expertise of their readers. For example, the Beginner's guide instructions for signing requests starts off with the following:

In the following walkthrough, the Consumer would like to access a Protect Resource located at http://photos.example.net/photos with the parameters size=original, file=vacation.jpg

Simple enough, right? Even if you don't know what "Consumer" means. But immediately following, it continues as follows:

The Consumer has previously registered with the Service Provider and obtained the Consumer Key dpf43f3p2l4k3l03 and Consumer Secret kd94hf93k423kf44. It has executed the OAuth workflow and obtained a Token nnch734d00sl2jdk and Token Secret pfkkdhi9sl3r4s00. To sign the request, the Consumer is using the HMAC-SHA1 signature method and generated a nonce string kllo9940pd9333jh and timestamp 1191242096.

Huh? How do I register and how do I get a consumer key? What is the "OAuth workflow" and how to I obtain a token and token secret? How do I use the HMAC-SHA1 signature method with OAuth? How do I generate a nonce string, what is the syntax and how long does it have to be? Why does the timestamp look like that? These are major assumptions that are casually glossed over by the guide. Nowhere in the guide (or anywhere else I looked on the site) are there links to any other resources, nor are there any links to service providers like Twitter or Google that use OAuth. I would be (extremely) grateful to hear otherwise.

The comments on the guides left by visitors are a mix of fanboy-praise and people who can't implement their code.

So my search for OAuth sample code, or some kind of walkthrough, will continue. I may just be bitter because Twitter requires OAuth now, and my userforms all broke, but as I mentioned earlier, I think using OAuth to protect something as silly as tweets is excessive. A tweet and $2.25 will get you on the subway.

Here are some great APIs I've had the pleasure of writing code for.

Business.Gov

This API is well documented with method calls and sample URLs. It lists return data formats as well as the format for individual return values. It is consistent and cleanly formatted. The first of a large volume of sample VBA code for this API may be found at Business.gov Web API – Financing.

Abbreviations

The documentation for this API is beautifully simple, yet complete. Sample XML responses are even included, so you don't even need to run test queries. VBA code for this API may be found at Abbreviations.com API Code Samples.

Any other APIs you'd like to mention, for better or worse?

Documentation Best Practices for Web APIs is Copyright © Code For Excel And Outlook. All Rights Reserved.

More stuff:

• Where do I put my Outlook code?
• Contribute to this blog
• VBA Search Engine
• Site Map

In previous articles I've written about how to extract information from webpages, and interact with them to navigate through a website.

This article won't strictly focus on IE, but on several collections available through the MSHTML Object Model.

Previous articles:

• An exploration of IE browser methods, part I
• An exploration of IE browser methods, part II
• Automate Internet Explorer

The GetMSXML Function

I've started using this function to return the XMLHTTP object. Hopefully I'll remember to use it consistently.

We'll use it because it's much faster than automating IE, as we learned previously, and we're more concerned with the HTML document once it's created rather than the method of actually creating it.

Creating MSHTML Documents

Another function we'll need creates the HTML document which we'll load the HTML into. We also saw this code in An exploration of IE browser methods, part II.

HTML Document Object Collections

These are the three collections we'll be working with here: Anchors, Images, Links. We dealt with them in An exploration of IE browser methods, part II, but here I'll be going into a bit more detail.

Each one accepts a HTML Document Object (which we've already loaded with content from a website) and returns a reference to the relevant collection. The collection can then be iterated to look for a value, compare values, and so on.

Anchors Collection

The Anchors Collection "retrieves a collection of all a objects that have a name and/or id property. Objects in this collection are in HTML source order."

The Images Collection

The Images Collection "retrieves a collection, in source order, of img objects in the document."

The Links Collection

The Links Collection "retrieves a collection of all a objects that specify the href property and all area objects in the document.".

Note that each collection returns objects "in source order", which means that the first link you see on a page will be the first link in the Anchors/Links Collection. Also, all three Collections have Length and Item Properties, which let you check the amount of items in the collection and return each individual item, respectively.

Looping through each collection

In the sample procedure below, we create a new HTML document and populate it with the source code from Google's homepage (hey, got a better site to test? Leave a comment!). Then we grab each collection in turn and print various attributes from each member.

An exploration of IE browser methods, part III is Copyright © Code For Excel And Outlook. All Rights Reserved.

More stuff:

• Where do I put my Outlook code?
• Contribute to this blog
• VBA Search Engine
• Site Map

In Exploring the NOAA API I posted code for consuming the NOAA RSS feed for Atlantic storms.

I also posted weather-finding functions using the Geonames API, the WebserviceX.NET API and even a cheap formula for opening weather.com.

Now let's explore even more weather-related functions (I'm sure you're as excited as I am!). This one is an API from weather.gov that provides state-by-state weather advisories which you can use in your VBA programs.

I noticed that in addition to providing a RSS feed, the feed URL itself only differs by the state abbreviation. So all we need to do is prompt for the state abbreviation and we can return any weather advisories in any state! And if you check weather.gov for all the state abbreviations, you can also look at weather for other U.S. territories and provinces.

The GetStateAdvisories Function

The following function takes a state abbreviation and returns any weather advisories issued by weather.gov for that state. Queries are cached, so the second parameter must be True to requery the API.

Because there is a series of irrelevant nodes that appear before the weather entries, we need to loop through the nodes to find out how many entries there are. After sizing the temporary array we'll use to hold the entry information, we loop again through the nodes.

If there are no active weather advisories, there will be one node and we don't really need to loop. Otherwise we loop through each entry and grab the weather advisory along with the URL from weather.gov.

Sample usage

This procedure will return and iterate through any available weather advisories for New York State.

Helper Functions

These are the ancillary functions used by the GetStateAdvisories function.

State Weather Advisories available by RSS is Copyright © Code For Excel And Outlook. All Rights Reserved.

More stuff:

• Where do I put my Outlook code?
• Contribute to this blog
• VBA Search Engine
• Site Map

Access to the files has been removed until further notice.

Thanks

Random Data Generator Add-In Issues is Copyright © Code For Excel And Outlook. All Rights Reserved.

More stuff:

• Where do I put my Outlook code?
• Contribute to this blog
• VBA Search Engine
• Site Map

No, not that kind of scraping. You can capture and download virtually any webpage using VBA. Here's one method for doing so.

I've written a simple function that lets you screen scrape almost any webpage you can pass as a parameter. I say "almost" because some pages are rendered in Javascript or Flash (or frames!) and don't really reveal any useful source code. The function will return the raw client-side output as a String with no parsing whatsoever. How you choose to parse the information is up to you.

The GetWebpage Function

This function should be familiar to anyone who has read any of my articles on XMLHTTP parsing. It uses the MSXML.XMLHTTP object to open and grab a web page. Simple as that. If you specify a filename as the second parameter, the webpage contents are written to a file.

To create an instance of the XMLHTTP Object, I've started using this function:

If you do choose to write the output to a file, you'll need these two functions:

Sample usage

Screen Scraping 101 with VBA is Copyright © Code For Excel And Outlook. All Rights Reserved.

More stuff:

• Where do I put my Outlook code?
• Contribute to this blog
• VBA Search Engine
• Site Map