Our clients who use CDXZipStream or CDXStreamer might find useful several free apps offered in the Microsoft Windows app store that can be used with location-based data.  Bing Maps for Excel and Geographic Heat Map can create great worksheet maps for data presentation, and others such as Bubbles, People Graph, and Modern Trends use creative approaches to present data that are not available in the charting options offered within Excel.  For cleaning up ZIP code or address data prior to use with CDXZipStream and CDXStreamer, the app called Remove Unwanted Characters is also a good tool.  So let’s take a closer look …   

(Note: These apps work with Excel 2013 and/or online Excel through Office 365.  You can search for Office apps by clicking on the Store icon in the Apps area of the Insert tab, located on the Excel ribbon.  The link to each app is also provided below.  And here’s a tip:  If you need to remove any of the images created by these apps, click on the arrow at the upper right-hand corner of the app image, pick Select, and press the Delete key on your keyboard.)

Bing Maps

Plot and visualize your location-based data using Bing Maps.  If you have a group of locations in an Excel worksheet, just use your cursor to highlight the locations along with any associated data, such as demographics obtained through CDXZipStream.  Locations can be based on address, county, state, city, latitude\longitude, or ZIP code.  This little app inserts a map right into the worksheet with pushpins or other symbols of your choosing, showing both the locations and related data.  The number of locations is limited to 100 per map.  Here’s an example of a data visualization map showing median income data from CDXZipStream by ZIP code: 

Microsoft also offers Power Map for Excel, which provide 3D mapping of up to a million data points.  It is available from the Insert tab as part of Office 365, although it can also be downloaded as an unsupported add-in for Office 2013 Professional Plus.  Please see the Microsoft website for more information.  It is not available through the Windows app store.

Geographic Heat Map

The resulting display from this tool is a professional-looking heat map that can display data on the state level.  Here’s an example showing median earnings data from CDXZipStream from all states:

Bing Maps for Excel can do a version of this as well, although the maps produced by Geographic Heat Map are a bit more eye-catching.  There are not a lot of options in this app beyond selection of different color schemes, but it is very easy to use and it does exactly as advertised.

There are other creative graphing and charting apps that can be applied to geographic data, although not in map form.  These provide nice alternatives to the more conventional charting options provided within Excel:

Bubbles 

The Bubbles app for Office represents your data as bubbles of different sizes and colors, showing data distributions at a glance.  You can even analyze more complex data sets from two separate tables.  Bubble colors can be set with any hex values you specify.

 People Graph

Another creative way of showing data using people symbols, especially appropriate for demographics or population data available from CDXZipStream.

Modern Trend 

With just a few selections in the Modern Trend app, you can create an interactive trend chart. You can also pin specific data points on the chart, and add descriptions on the canvas.  Multiple themes and layouts are available.

Remove Unwanted Characters

As the name implies, the Remove Unwanted Characters app automatically removes any unwanted characters in your Excel data, even all non-printing characters and line breaks that sometimes occur when data is imported or copied from the web or other applications.  Since these often “invisible” characters can invalidate ZIP codes or address data, this is a great tool to use prior to running CDXZipStream or CDXStreamer.   Formulas in the data range will be overwritten with values when using this app.

There may be situations where our Microsoft Excel add-in CDXZipStream does not install correctly, which can explain why Excel returns #NAME? for a CDXZipStream function, or why the CDXZipStream toolbar is not visible.  Here’s what you can do:

Check Your Excel Version

Microsoft introduced a 64-bit version of Excel in 2010 for users that require lots of RAM.  CDXZipStream is currently not compatible with 64-bit versions of Excel 2010 or higher. (It is compatible with either 32 or 64-bit Windows.)  If you are using Excel 2010 or higher, check to see if it is a 32 or 64-bit version by selecting “Help” from the “File” tab (Excel 2010) or “Account” from the “File” tab (Excel 2013) and looking under the section “About Microsoft Excel”.  Here’s an example of what you will see in Excel 2010:

If you are using 64-bit Excel, please refer to this article for more information.  If you are using 32-bit Excel, continue to the steps below.

Enable the CDXZipStream Add-in

Check to see if the CDXZipStream add-in is enabled in Excel.  The process for doing this varies somewhat, depending on the Excel version.

Excel 2003:  From the “Help” menu click on “About Microsoft Excel.”  In the lower right-hand corner of the screen that appears press “Disabled Items”.  If any add-ins beginning with “CDXZipStream” are on the list, click on them to re-enable.  Then restart Excel.

Excel 2007:  Click the Microsoft Office button (the big circle in the upper left-hand corner), select “Excel Options”, and then “Add-ins”.  Check the “Inactive Application Add-ins” or “Disabled Applications Add-ins” lists for add-ins that start with “CDXZipStream”.  If any appear, use the “Manage” function at the bottom of the screen.  Select the type “COM Add-ins” and then make sure that add-ins with “CDXZipStream” are checked.  Then restart Excel.

Excel 2010/2013: From the File tab selection “Options”, then “Add-ins”.  Check the “Inactive Application Add-ins” or “Disabled Application Add-ins” lists for add-ins that start with “CDXZipStream”.   Here's what these lists look like in Excel 2013 (with the CDXZipStream references highlighted):

 If any appear under the Inactive or Disabled lists, use the “Manage” function at the bottom of the screen.  Select the type “Disabled Items” and then press the “Go” button:

Re-enable any add-ins that begin with “CDXZipStream”.  If no CDXZipStream add-ins are disabled, use the “Manage” function again and select the type “COM Add-ins”, then press the “Go” button.  In the screen that appears make sure that add-ins with "CDXZipStream" are checked and click “OK”.  Then restart Excel. 

Enable the CDXZipStreamCF.Connect Add-in

If the above steps do not correct the problem please follow the procedure below:

Excel 2003:  Select “Add-ins” from the “Tools” menu and make sure that “CDXZipStreamCF.Connect” is checked. If you don't see it you can use the Automation button to select it from the list that appears. 

Excel 2007:  Click the Microsoft Office Button (the big circle in the upper left hand corner), select “Excel Options”, and then select “Add-Ins”. Use the "Manage" function at the bottom of the screen. Select the type "Excel Add-ins", press "Go" and make sure that the add-in “CDXZipStreamCF.Connect” is checked. If you don't see it you can use the Automation button to select it from the list that appears. 

Excel 2010/2013: From the File tab select “Options”, then “Add-ins”.  Use the "Manage" function at the bottom of the screen to select the type "Excel Add-ins", and then press "Go". In the screen that appears make sure that add-ins with “CDXZipStreamCF.Connect” are checked and press "OK". If you don't see any you can use the Automation button to select from the list that appears. 

If an error occurs when enabling add-ins you will need to run a repair of Microsoft Office. The process for this depends on your versions of Windows and Excel.  Please see this Microsoft article for more details.

Repair/Reinstall CDXZipStream

If the above steps do not work, you will need to repair or reinstall CDXZipStream.

Note that CDXZipStream is installed on a profile basis.  If you have a user or restricted rights profile, have your IT group raise your profile to administrative, install CDXZipstream, and then lower the security back.

Make sure all instances of Excel are closed on your system.  To confirm this, start the Windows Task Manager by pressing CTL-ALT-DEL and then clicking on this option.  Select the processes tab and then sort by clicking on the “Image Name” header.  The result should look like this:

If there are any occurrences of “EXCEL.EXE”,  right-click on each one and click “End Process”. 

Download and then run the latest copy of the CDXZipStream setup file which can be found at this link.

When the install starts, click on “Run” and when prompted press “Next”.  Then select the “Repair Option” and press Next.  On the following screen click “Install” to set up the program.  If you are unable to perform a repair, uninstall the CDXZipStream program using Window’s “Add/Remove Programs”, and then reinstall.

Awhile back we thought it would be a good idea to devote a blog article about how to report mapping and routing errors in Microsoft MapPoint.  Our clients, who use the CDXZipStream MapPoint version to calculate driving distance and perform route optimization, sometimes tell us about interesting, even obviously incorrect results obtained from MapPoint, and we thought that Microsoft must have some procedure in place for reporting and rectifying those errors.  Unfortunately, that doesn’t appear to be the case.  Here’s our story:  

One of our clients needed to calculate the driving distance between ZIP codes 37406 and 35209.  For the driving distance from 37406 to 35209, MapPoint would calculate 242.6 miles, but the reverse drive was significantly less at 158.7 miles.  The discrepancy occurred in both MapPoint 2011 and 2013.

Here’s a MapPoint map of the part of the route that shows the error, where the route heads north and then backtracks south toward its final destination:

Bing Maps, Google Maps, and Mapquest did not have this problem.  All calculated a driving distance of 155 - 160 miles, coming and going.

OK, so nobody’s perfect.  Maybe there was a good explanation for the discrepancy that could be addressed by better map data or a modification of MapPoint’s routing algorithm.  

Under MapPoint’s Help menu there’s an option to select “Send map feedback” to a company called HERE (owned by the Finnish company Nokia, and formerly called NAVTEQ) which is the supplier of MapPoint map data.  We wound up on the HERE map creator website and used a feedback widget to tell them about the problem.  Three business days later we got a nice email from a guy named Ralf:

Thanks for your feedback! It is important to us, and we’re giving it careful consideration.

You are welcome to report map changes in HERE Map Creator and send inquiries regarding HERE Map Creator using this feedback widget. For questions about the functionality of products based on HERE map data please send your inquiry to the manufacturer directly.

The routing issue you experienced is not reproducible on our HERE maps either.

Kind regards,

Ralf, on behalf of

The HERE Map Creator Team

So we needed to contact the manufacturer, Microsoft.  We did a Google Bing search for Microsoft support, and from support.microsoft.com we clicked on View All Products.  After entering MapPoint in the product search box, no matches were found.  Not a good sign, but we were then directed to click on Contact us and found a technical support link on the next page.   Another search did find MapPoint 2013 in the product listing, and after signing into a Microsoft account, doing another product search, and entering the product code ID, we finally got to the support options, which are 1) Microsoft calls us, 2) we send an email, or 3) we call Microsoft.

Options 1 and 2 both resulted in an error message, which occurred over the course of several days:

Option 3 only provided a TDD/TYY support number which is compatible with telecommunications devices for the deaf, which is not applicable in this case. 

The next step was to call the general support number from the error message above.  Not a great option, but we tried it anyway.  After listening to the menu selections which don’t include MapPoint, we selected “Other” and were inexplicably routed to someone in Office support, who passed us off to Sales, who transfered us back to another menu selection. This time there was no “Other” option so we randomly selected Windows support.   At least they passed us along to someone who provides help with MapPoint installation issues.  They could not address this particular problem, but they recommended we send our issue to a third-party website, which did have a workable feedback widget.  Within a couple of hours we received a somewhat amused email saying that they had no idea why Microsoft recommended we contact them, since MapPoint is not their product and they have no knowledge of how their routing or database engine works.

We also posted this issue on the Microsoft forum for AutoRoute, Streets and Trips, and MapPoint, and after 10 days have received no response.  

So unfortunately at this point we cannot recommend a good method for reporting MapPoint routing errors. Based on our experience here, Microsoft does not provide the technical support to acknowledge that these errors exist, never mind correct them.  Hopefully things will change – we’ll continue to test the Microsoft on-line technical support facility – and will give you an update if they do.

Up to this point we’ve focused primarily about how to use Visual Basic in Microsoft Excel to programmatically access the functionality of our ZIP code and location analysis tool, CDXZipStream.  (Please refer to our previous posts Calling CDXZipStream with Visual Basic – Part I and Part II.)  Now it’s time to do the same for Microsoft Access.  Side note:  The form of Visual Basic when implemented in Microsoft Office products is referred to as Visual Basic for Applications, or VBA.  We’ll use these terms here interchangeably.

The approach to accessing CDXZipStream functions is very similar for Access and Excel, although there are obviously significant differences in how the input and output data are handled since the data resides in database tables versus worksheets.  So let’s start out with the same simple example that we used previously for the VBA code for Excel, where we request city information for a given ZIP code.  The CDXZipStream formula is:

= CDXZipCode ("07869", "City")

In a Visual Basic module we use the CreateObject statement to connect to CDXZipStream:

Dim oAdd as object

Set oAdd = CreateObject("CDXZipStreamCF.Connect")

Then simply ask for the data:

City = oAdd.CDXZipCode("07869", "City") 

To loop through records in a table and return city information for a list of ZIP codes, here’s how it would look:

Sub GetCityData()

Dim db As Database

Dim rs As Recordset

Dim oAdd As Object

Set oAdd = CreateObject("CDXZipStreamCF.Connect")

Set db = CurrentDb

Set rs = db.OpenRecordset("Select * from MyTable")

Do Until rs.EOF  

  rs.Edit

  rs!City = oAdd.CDXZipCode(rs!ZIPCODE, "City")

  rs.Update

rs.MoveNext

Loop

End Sub

The code assumes that the Access database contains a table called MyTable that includes at least two text fields, ZIPCode and City.   

Let’s also look at a more complex example where both the input and output data are in the form of arrays, as when optimizing stops in a driving route.  Let’s say we have a table that contains up to five input addresses per record, with each record representing a driving route.  We want to determine the order of the addresses that result in the quickest driving time, and output the new order of addresses to the same table.  The code below loops through each record, to optimize all driving routes in the table:   

Sub GetOptimizedRoute()

Dim db As Database

Dim rs As Recordset

Dim oAdd As Object

Dim InputArray As Variant

Dim OutputArray As Variant

Set oAdd = CreateObject("CDXZipStreamCF.Connect")

Set db = CurrentDb

Set rs = db.OpenRecordset("Select * from MyTable")

Do Until rs.EOF = True

'Count the number of addresses in the input array

    InputArray_cnt = 0

   For I = 1 To 5

       If IsNull(rs.Fields("Address" & I & "")) = False Then InputArray_cnt = InputArray_cnt + 1   

    Next I

'Optimization requires at least 4 addresss

If InputArray_cnt < 4 Then GoTo Skip_record 

'Dimension the input array according to previous count

ReDim InputArray(InputArray_cnt - 1)  

'Populate the input array, while skipping fields with no data

X = 0

    For I = 1 To 5

       If IsNull(rs.Fields("Address" & I & "")) = False Then 

           InputArray(X) = rs.Fields("Address" & I & "")

           X = X + 1

       End If   

    Next I

OutputArray = oAdd.CDXRouteMPWrapper(0, 8, InputArray)

     ' 0 is the parameter that specifies the quickest route

     ' 8 is the parameter that specifies the output as an array of addresses in optimized order 

' Put the output array results in the output address fields

If IsArray(OutputArray) = True Then

    For I = 1 To InputArray_cnt

       rs.Edit

       rs.Fields("Address" & I & "_opt") = OutputArray(I - 1, 0)

       rs.Update

    Next I  

'If a non-array result is returned due to error, send the result to the first address field

    Else:  

       rs.Edit

       rs.Fields("Address1_opt") = OutputArray

       rs.Update

End If

Skip_record:

rs.MoveNext

Loop

End Sub

MyTable here contains input fields Address1 through Address5, and output fields Address1_opt through Address5_opt; the latter addresses are listed in their optimized order.  Not all the input address fields need to populated, and for real world examples it would be easy to add fields as necessary to accommodate many more address stops on a route.  However, a minimum of four addresses must be input for optimization to proceed.

Note: You can copy and paste the code here to test it in an Access VBA module, although it may require some modification to fit your particular needs.  Don't forget to include the Option Compare Database statement at the top of each module!

Microsoft Excel has quite a few built-in worksheet functions, such as SUM, AVG, or MAX, but there are a lot of operations that aren’t covered by the standard functions, and that’s where custom functions come into play.  For instance, our Microsoft Excel add-in CDXZipStream uses a variety of custom functions for analyzing ZIP Code and address data.  Here we’d like to show you how to create your own custom functions, and as an example, we’re going to create a function that formats ZIP code data into a consistent five-digit format.

The first step is to open Excel, then use ALT-F11 to open the Visual Basic for Applications (VBA) editor.  All custom functions in Excel must use VBA, the programming language for Office applications.  From the Insert menu of the editor, select Module.  In the new module window that opens, copy and paste the following:

 Function Zipformat (zipcode)

Zipformat = Left(Format(Application.WorksheetFunction.Clean(zipcode), "00000"), 5)

End Function

The custom function is called Zipformat, and the only input parameter required is the ZIP code you wish to format.  The line of code that contains the formula removes non-printing characters (using the worksheet function “clean”), and restores leading zeroes to the ZIP code if they are dropped by Excel.  It also converts ZIP +4 data to a five digit length.

Now use ALT-F11 again to return to the original worksheet, and let’s try it out.  If we have the ZIP code 07869-4222 in cell D2, we simply use our new custom function like this:

=zipformat(D2)

 And the result looks like this:

In the case of ZIP code 07869, where Excel drops the leading zero, we can also use the zipformat custom function like this:

And the leading zero is restored.

You can see that developing custom functions is quite easy, and can really help you save time with repetitive tasks.  It's also possible to develop more sophisticated functions that use if/then statements, or even a dialog box for user input and messaging.  However, custom functions are limited primarily to returning a value to a formula in a worksheet, and cannot do more global tasks like resizing windows or changing cell formatting.  For those situations, consider recording a macro to obtain the vba code, then generalizing it to work in different scenarios.  

The formula used in the zipformat function above is equivalent to the worksheet formulas used in the video tutorial Find ZIP Codes in a Radius – Update.  The formulas are used to reformat ZIP Codes to ensure they are consistent with data used in a VLOOKUP formula; this is important to ensure that the VLOOKUP function works correctly.  

For more detailed information on creating custom functions, please see the Microsoft article here.