As you have probably noticed, Excel can be quite powerful for number crunching applications. If you need to do statistical, chemical, loan, and many other calculations these are easily accomplished using basic, built-in, Excel functions. This application uses basic SUM functionality to tally the total mileage. That is the easy part. We will extend this functionality greatly using VBA code to perform a VLOOKUP of the total mileage in both the “Location” and the “LongLat” sheets. Our code will then display this information in a user-friendly form (shown below).
This form was created using the VBA editor (alt-<F11> or click the button on the developer bar). See Appendix A for directions on how to display the developer bar in Excel 2010.
The name of the form is “WhereAmI” and the screenshot above shows that it contains nine labels and two buttons. The properties, other than what is shown here for “Caption,” will include “Visible =False” false for Label7. The most interesting functionality is to decide that we have a picture and display the button which will open the URL in our web browser. Those macros, and an explanation of this form, are displayed and explained in the following sections. First, we will take a look at the rather short code behind the two buttons.
The “Close” button (appropriately named btnClose) has a single function – close the open form and return focus to the spreadsheet. As shown below, that is accomplished with a single “Unload” command.
Private Sub btnClose_Click()
Unload Me
End Sub
Private Sub btnView_Click()
Dim link As String
link = Label7.Caption
ActiveWorkbook.FollowHyperlink Address:=link, NewWindow:=True
End Sub
The other section of code is called by the “View Picture” button – named btnView. The purpose of this section is to open the link to the picture and/or additional information using the default web browser. When running the program, you will notice that the top label (label7) is hidden. We don’t need to see the destination link but it is still available for use.
With only three lines of code, we will be able to greatly impress the user of our program. First, we set a variable (named “link”), as a string. This variable is then assigned to the destination URL for the picture. Our final line loads the default web browser with a new window directed to that link.
Let’s move on to the User sheet and VBA macro creation …
