Can CDXZipStream be used in other applications besides Excel, such as Microsoft Access, Word, Powerpoint, or Sharepoint? Yes it can! Using some simple Visual Basic code, it is fairly easy to “call” CDXZipStream and get the data you need from the application of your choice. In this way you can obtain CDXZipstream data without using custom function formulas in worksheet cells. Other advantages to this approach is that it can be faster for large data sets, and it eliminates delays associated with Excel or any other application recalculating worksheet formulas. We use this approach in our free, downloadable templates, which are especially quick and efficient for processing long lists of data.
In a previous post we reviewed how to use VBA, or Visual Basic for (Office) Applications, to access CDXZipStream custom functions CDXZipCode, CDXLocateMP, CDXDistance2WP, and CDXRouteMP. So let’s finish the task for all the other available functions:
To start, open Excel and press ALT-F11 to access the Visual Basic for Applications (VBA) editor. From the Insert menu of the editor, select Module, and in the new module window that opens you can now apply the following code for each function:
CDXFindZip – finds the ZIP code(s) for a given city and state.
In the first line of code we use the createobject statement to connect to CDXZipStream:
Set oAdd = CreateObject("CDXZipStreamCF.Connect")
Then simply ask for the data (that is assigned here to a variable called ZIP):
ZIP = CDXFindZip("Abington","Massachusetts")
The code can be generalized to obtain data for a long list city and state pairs. In Microsoft Excel, for example, just loop through the worksheet range containing the city and state data. Here is the basic code you can copy to a module, and modify as needed:
Sub FindZIP
Dim oAdd as object
Dim City as string
Dim State as string
Dim Zip as string
Dim N as integer
Set oAdd = CreateObject("CDXZipStreamCF.Connect")
For N = 1 to 100
City = Sheets(1).Range(“A” & N & “”) ‘Get each city from the worksheet list
State = Sheets(1).Range(“B” & N & “”) ‘Get each state from the worksheet list
Zip = oAdd.CDXFindZip(City, State)
Sheets(1).Range(“C” & N & “”) = Zip ‘Place the returned data in the worksheet next to its corresponding zip code
Next N
End Sub
All other functions follow a similar format:
CDXDistance – finds the straight-line distance between two ZIP codes
Dim oAdd as object
Dim Zip1 As String
Dim Zip2 As String
Dim Distance As String
Set oAdd = CreateObject("CDXZipStreamCF.Connect")
Distance = oAdd.CDXDistance(Zip1, Zip2)
The equation above obtains the distance in miles. Use the following formats to obtain distance in kilometers and nautical miles, respectively:
Distance = oAdd.CDXDistance(Zip1, Zip2, “KM”)
Distance = oAdd.CDXDistance(Zip1, Zip2, “NM”)
CDXRadius – finds all ZIP codes within a radius distance of a target ZIP code
Dim oAdd as object
Dim Zip As String (The target Zip code, the center of the radius area)
Dim Output As Integer (Specifies whether the returned data are in a single string or array)
Dim Radius As Double (The radius distance from the target)
Dim Unit As String (Specifies the returned distance data as miles, kilometers, or nautical miles)
Dim Database As Integer (Specifies use of U.S. Zip codes, or Canadian postal codes, or both)
Dim Zip_list as variant (The results returned as a string or array)
Set oAdd = CreateObject("CDXZipStreamCF.Connect")
Zip_list = oAdd.CDXRadius(Zip, Radius, Unit, Output, Database)
If the data is returned as an array, it will also be necessary to extract the data to individual cells in the worksheet, as follows:
For I = 0 To UBound(Zip_list, 1)' Retrieve data from array
Sheets(1).Cells(I + 11, N) = Zip_list (I, 0)
Sheets(1).Cells(I + 11, N + 1) = Zip_list (I, 1)
Next I
CDXClosest – finds the first, second, or third closest ZIP code from a target ZIP code
Since this function requires a range as input, the code is slightly different.
Dim oAdd As Object
Dim Target As String (The target Zip code)
Dim Output As Integer (The type of returned data, i.e. first closest, second closest, etc.)
Dim ZipArray As Variant (The range of Zip codes that will be searched)
Dim Result As String
To create the ZipArray, loop through the range that contains the data, in this case, in rows 1 through 10:
ReDim ZipArray (1 to 10)
For N = 1 To 10
ZipArray(N) = Sheets(1).Range("A" & N & "")
End If
Next N
Set oAdd = CreateObject("CDXZipStreamCF.Connect")
Zip = oAdd.CDXClosestZipWrapper(Target, Output, ZipArray)
CDXZiplist – finds all ZIP codes for a given city, county, or state.
Dim oAdd As Object
Dim Output As Integer (Specifies whether the returned data are in a single string or array)
Dim Postal_option As Integer (Specifies whether all data, or only Zip or postal data, is returned)
Dim State As String
Dim County As String
Dim City As String
Dim Zip_list as variant
(Note that city and county data are optional. For example, specifying only state data would provide all the Zip codes for the specified state.)
Set oAdd = CreateObject("CDXZipStreamCF.Connect")
Zip_list = oAdd.CDXZipList(State, County, City, Output, Postal_option)
Since the data here can be returned as an array, in this case it will also need to be extracted to the worksheet cells as shown earlier for the CDXRadius function.