Simple search engine browsing can be accomplished in Excel in two ways. The simplest way is the use of the HYPERLINK function. A bit more complicated, but still relatively simple, is the Webbrowser control found in the VBA environment. On this page we'll explore both.
Hyperlink Function
First I recommend visiting your favorite search engine and do a sample search to inspect the URL. I tried searching for "Access bubble sort listbox" on several major search engines (mostly because I wanted to find out how to bubble sort a listbox's contents on an Access form, but you can search for anything).
- Ask
- Yahoo
- Bing
- Search
- Altavista
The URLs are displayed below. They're all very similar. Notice that the spaces in the search term are replaced with plus signs in every URL.
- http://www.ask.com/web?q=Access+bubble+sort+listbox
- http://www.google.com/search?hl=en&q=Access+bubble+sort+listbox
- http://search.yahoo.com/search?p=Access+bubble+sort+listbox
- http://www.bing.com/search?q=Access+bubble+sort+listbox
- http://www.search.com/search?q=Access+bubble+sort+listbox
- http://www.altavista.com/web/results?itag=ody&q=Access+bubble+sort+listbox
The HYPERLINK function will be used to create hyperlinks to open the default web browser. We'll pull the search terms from a worksheet cell, and use the SUBSTITUTE function to replace the spaces with plus signs in our search term. Here's a sample of what it looks like.
HYPERLINK takes two arguments: the string used as a URL to visit, and a friendly link text to display on the worksheet. Simple.
WebBrowser Control
The WebBrowser Control allows us to create web browsers in VBA. You can create a small window, or even write a complete browser housed inside Excel or another Office app.
The first thing you need to do is make sure you can reference the correct object library: shdocvw.dll. Simply going to Tools » References in the VBIDE and selecting "Microsoft Internet Controls" may not be enough.
Depending on your version of IE or Windows, your reference to Microsoft Internet Controls may actually point to ieframe.dll instead of shdocvw.dll. You'll need to browse to your System32 folder and find it explicitly; then your reference will update.
I've gone through the trouble of building a small browser app in Excel VBA; it works similar to the HYPERLINK example above. A combo box lists the search engines above, which you choose from the dropdown list. Here's how we populate the combo box. I use a slightly unorthodox method.
Dim SearchEngineList(1 To 6) As String
' populate listbox
Dim i As Long
For i = 1 To UBound(SearchEngineList)
Select Case i
Case 1
SearchEngineList(i) = "Bing"
Case 2
SearchEngineList(i) = "Google"
Case 3
SearchEngineList(i) = "Ask"
Case 4
SearchEngineList(i) = "Yahoo"
Case 5
SearchEngineList(i) = "Altavista"
Case 6
SearchEngineList(i) = "Search"
End Select
Next i
Me.SearchEngineList.List = SearchEngineList
Type in your search terms and the WebBrowser control will use its Navigate method to browse to and display the resulting search engine page. Here's the result when using Bing:
I've also written a custom function that checks if the form is complete. It's used in conjunction with the Go button to check if it should be enabled.
Function IsReady(frm As MSForms.UserForm) As Boolean
' check if form fields are completed
IsReady = True
Dim ctl As MSForms.Control
Dim txtbox As MSForms.TextBox
Dim cbobox As MSForms.ComboBox
Dim myForm As MSForms.UserForm
Set myForm = frm
For Each ctl In myForm.Controls
Select Case TypeName(ctl)
Case "TextBox"
Set txtbox = ctl
If txtbox.Value = "" Then
IsReady = False
Exit Function
End If
Case "ComboBox"
Set cbobox = ctl
If cbobox.Value = "" Then
IsReady = False
Exit Function
End If
End Select
Next ctl
End Function
IsReady(Me) is called from the Change events of the textbox and combo box on the Userform; it will only return False if any of the textboxes or combo boxes on the form are blank. It doesn't care how many there are on the form, or what they are called, so if we needed to add more controls or rename the existing ones, we wouldn't need to change this function at all.
I've written a VB.NET web browser similar to the above (with much more functionality), at some point I may post it here as well. For now, click on the button below to download a sample workbook containing the hyperlink formulas and the VBA userform/module.
