VBA or Visual Basic for Applications is a fantastic tool for automating a number of tasks across the Microsoft suite. Some of the greatest uses come from integrating Excel with Internet Explorer allowing a user to be able to scrape data off various websites and dump the data into Excel. This post will show how to write code allowing Excel to launch Internet Explorer and navigate to a webpage.

First off, you’ll want to start a new Excel file, and save it as a “Macro Enabled Workbook”, recognizable by the .xlsm file affix.
1- Save As

Next, under the Developer tab in Excel click “Macro Security” and enable macros. I usually select “Enable all macros” because I don’t ever run VBA code that I haven’t inspected personally, but consider selecting the security options that will be applicable to you.
3 - Security Settings
Finally, click the Visual Basic button in the developer tab, and once in the editor, from the tools menu, select “References”. While for different VBA projects you might enable different libraries, the basics are going to be: Visual Basic for Application, Microsoft Excel Object Library, OLE Automation, Microsoft Office Object Library.
4 - VBA referencesAt this point, you’ve completed the configuration needed to run a basic Visual Basic project. The following block of code will show you a simple way to control Internet Explorer from Excel. Create a new macro by selecting “Marco” from the developer tab. Name it something like “Basic_Navigation”. When you click “create” the editor window will pop open. Copy the following code into the editor, then close the Visual Basic window.

Sub Basic_Navigation()
    Set IE = CreateObject("InternetExplorer.Application")

    IE.Navigate "http://tipsformarketers.com"
    MsgBox ("Check Internet Explorer, click ok when done.")

    Set IE = Nothing ' Clean up
    Application.StatusBar = ""

End Sub

To run this script, click the macro button in the developer tab, select the macro you just created, and click “Run”. Excel will now open up Internet Explorer and navigate to TipsForMarketers.com. When you go back into Excel, there will be a message box which, upon confirmation, will close down Internet Explorer and return you to your spreadsheet.

While this simple script by itself isn’t that that amazing, you now have the basics of how to create a simple marco that controls Excel. Stay tuned for more in-depth walk-throughs that should help you out as a marketer!

Related posts

3 thoughts on “Visual Basic and Excel- Introduction to macro writing”

  1. […] If you are unfamiliar with VBA and Internet Explorer, be sure to check out my introduction post about Excel, VBA, and IE. […]

  2. […] 3 mentioned uses, there may be many more. The file is available below, and I’d recommend my intro post on using VBA in Excel to familiarize yourself with the basics of running a macro in […]

  3. […] you’ve never coded VBA, I’d recommend looking at my introductory piece before diving into this particular […]

Leave a Comment