Cell based web-search in excel

Ever wanted to just right-click  a cell and search it, the same way you can on most modern browsers?

Look no further - within this article, I'll do my best to show you how to add this functionality to your excel with an addin. 

Step 1 - How are the Search strings composed in the browser? 

If we start with Duck Duck Go,  when we search a phrase, you'll notice in the URL it has a 'q=' in the URL ( web address)  followed by what you'd typed in the search box, separated by '+' signs.

https://duckduckgo.com/?q=test+query+string

Google follows the same pattern, albeit with a lot of extra junk in the code. It can still be distilled to:

https://www.google.co.uk/search?q=test+query+string

Step 2 -  Create a new Macro Enabled workbook and add the code  - 

We need to have somewhere to put our code, so let's create a new macro workbook -

 Just save down an empty workbook and as the 'Macro Enabled' type ( .xlsm )

 Now hit ALT + F11  or if you have the developer tab enabled, click the 'Visual Basic' button. 

Once open, you should see a tree view of your workbook, right click on the folder titled 'Microsoft Excel Objects' and  choose INSERT> MODULE 

Then click on it to open the code window :

 The first thing to add is the Sub-routine to trigger excel to open a new browser tab with out cell contents. 

Sub MultiHyperLink(SelRange As Range, URLStr As String )

Dim Cc As Range

For Each Cc In SelRange

'
' ::: ITERATE THROUGH CELLS IN SELECTED RANGE TO OPEN HYPERLINK BASED ON TEXT :::
'

    ActiveWorkbook.FollowHyperlink (URLStr & Cc.Text)

Next Cc

End Sub

 The above loops through a selected range, and triggers excel to open a new browser window with a web address (URL) based off the the two strings, one supplied as an input ( URLStr )  which is the strings we were looking at earlier like 'https://duckduckgo.com/?q=', and the second part ( CC.text ) is the text value of the cell. This value is ignoring cell formulas etc.,  and is as you'd see it on screen in Excel.

The next sub we'll add is a test for duck-duck-go 

Sub Search_DuckDuck()

    Call MultiHyperLink(Selection, "https://duckduckgo.com/?q=")

End Sub

Test it by typing some random searches in cells, selecting the range, the running the macro via the macro menu.

This will then open multiple tabs based on the cell text.

You could stop here, and simply assign these macros to buttons, but what about creating these as context menu items, so you could right click any cell in any workbook, and web-search it?

Continue reading to find out..

Step 3 - Creating a context menu 

Tools for Editing Excel / Office UI  

First off, we need to download the Custom UI Editor. 

If you head to this page on Ron de Bruin's site  ( save this to favourites, as this site has lots of info on customising all aspects of Excel )

https://www.rondebruin.nl/win/s2/win001.htm

You can edit the UI to create custom elements yourself without it, however this entails a bunch of manual XML editing

The Custom UI tool makes it much quicker, with the added bonus of parsing your XML for errors, and allowing you to import custom images for buttons etc. 

Creating the menu in Custom UI Editor for Microsoft Office 

Open Custom UI editor, then load up your macro-enabled workbook.

Once open, right click on your filename and select 'Office 2010 Custom UI Part' 

In the new 'CustomUI14.xml' window, paste the below code .

<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

<contextMenus>

      <contextMenu idMso="ContextMenuCell">

         <menu id="MySearchMenu" label="Web Search" insertBeforeMso="Cut"  >
            <button id="BTN_D" label="Duck-Duck-Go" onAction="Search_DuckDuck" />
            <button id="BTN_G" label="Google" onAction="Search_Google" />
         </menu>
      </contextMenu>
   </contextMenus>


</customUI>

To check the xml is valid, click the icon with the tick, this will let you know the code will work once you open it in excel. 

Save and close the file, now open in Excel! 

If you right click a cell, you'll now see the new 'Web Search' menu, together with sub-menus for Google and Duck-Duck-Go 

 Just add in 'control as IRibbonControl' between the brackets on the sub, this tells excel to use the context menu to run the sub.

Here's the full code again, with the amended references for the search subroutines. 

Sub MultiHyperLink(SelRange As Range, URLStr As String)

Dim Cc As Range

    For Each Cc In SelRange
    
    '
    ' ::: ITERATE THROUGH CELLS IN SELECTED RANGE TO OPEN HYPERLINK BASED ON TEXT :::
    '
    
    
        ':: MAKE SURE CELL HAS SOME TEXT TO SEARCH!
        If Len(Cc.Text) > 0 And Len(URLStr) > 0 Then
        
            ' :: OPEN HYPERLINK !
            ActiveWorkbook.FollowHyperlink (URLStr & Cc.Text)
        
        End If
    
    Next Cc

End Sub
Sub Search_DuckDuck(control As IRibbonControl)

    Call MultiHyperLink(Selection, "http://duckduckgo.com/?q=")

End Sub

Sub Search_Google(control As IRibbonControl)

    Call MultiHyperLink(Selection, "http://www.google.co.uk/search?q=")

End Sub

Save the excel workbook again, and now you can test it. 

Extra stuff - Making it pretty, making it an add-in

You could stop with just context menus, but why not turn this into an add-in for Excel , and add some images to the menu items so they're easier to see?  

 Jump back into the Custom UI editor , and add in the additional code :

The new imageMSO part is a reference to existing Excel UI Icons , in this case we've added a magnifier glass and two coloured squares to the menu. 

Save and re-open in excel, and now the menu stands out a bit :

Creating an add-in 

So you want this to be a feature that's always loaded when you open excel, or want to share it with others?   

Go into the VBA editor again ( ALT+F11 )

Click on 'ThisWorkbook' , then on the Properties window, click on the 'IsAddin' dropdown and change to 'True'.

Hit the save button at the top, Excel will give you an error saying it's the wrong file type, you'll then be prompted to save as an addin :

This can then be distributed to other users.