Now that CDXZipStream works with Calipers's Maptitude software did you know that you can use the same CDXZipStream functions in applications other than Excel? This includes Office 365 applications such as Access that support VBA (Visual Basic for Applications) or any application programming environment that supports COM (Component Object Model) connections such as Python. CDXZipStream does not have to be running in Excel for this to work.
We’ve talked about programming in VBA with CDXZipStream in other articles which include.
Calling CDXZipStream with Visual Basic – Part 1
Calling CDXZipStream with Visual Basic – Part 2
Using CDXZipStream with VBA in Microsoft Access
So rather that cover this again will talk about what is involved to use the CDXZipStream Maptitude fuctions with VBA.
Caliper Maptitude does have extensive documentation with how to interact with the program using C#, Python VBA and other environments. This is the information we used to get the program to work with Excel. But this environment is primarily suited to programmer’s and we wound up generating several thousand lines of code to handle the geocoding and routing calls in CDXZipStream. Rather than doing this work yourself you can use CDXZipStream and VBA to access each these functions in 20 lines of code.
As an example the spreadsheet Maptitude VBA Calls.xlsm contains code in VBA Module 1 for both the routing and geocoding CDXZipStream calls as follows:
- Public oAdd As Object
- Public Function GeocodeMaptitude(Output As Integer, Address As String, Optional City As String, Optional State As String, Optional PostalCode As String, Optional Country As String, Optional Ambiguous As Integer)
- On Error GoTo Err
- If oAdd Is Nothing Then Set oAdd = CreateObject("CDXZipStreamCF.Connect")
- GeocodeMaptitude = oAdd.CDXLocateMaptitude(Output, Address, City, State, PostalCode, Country, Ambiguous)
- Exit Function
- Err:
- GeocodeMaptitude = "An Error Number " & Err.Number & "Occurred: " & Err.Description
- End Function
- Public Function RouteMaptitude(iMPType As Integer, Output As Integer, ParamArray WPoints())
- Dim TmpArray As Variant
- On Error GoTo Err
- TmpArray = WPoints
- On Error GoTo Err
- If oAdd Is Nothing Then Set oAdd = CreateObject("CDXZipStreamCF.Connect")
- RouteMaptitude = oAdd.CDXRouteMaptitudeWrapper(iMPType, Output, TmpArray)
- Exit Function
- Err:
- RouteMaptitude = "An Error Number " & Err.Number & "Occurred: " & Err.Description
- End Function
These calls work exactly like the CDXZipStream so you can use the CDXZipStream documentation for CDXRouteMaptitude and CDXLocateMaptitude to set the parameter required. For instance to return the latitude and longitude of zip code 07869 the following formula would be used:
=GeocodeMaptitude(3,"07869")
You can also geocode by full address or city/state as CDXZipStream will automatically manage the individual requests to Maptitude layers for these parameters. The function to reverse geocode as follows
=GeocodeMaptitude(0,40.84,-74.56)
As an example of routing to determine driving distance you can use the following call
=RouteMaptitude(1,0,"07869","07860")
that will determine the driving distance in miles between these two zip codes using the quickest route. This can also be used with full addresses and more than just two stops.
CDX Technologies can also provide consulting services if you want to build these functions directly into your application.
We think you will find this approach will save you considerable time, expense and aggravation.