We’ve talked previously about the advantages of formula arrays in the following blog article.
For performance reasons we recommend that arrays be used when working with large address lists. Formulas arrays allow Excel to request a bulk data call, instead of copying individual formulas one at a time. Since much of the calculation time is caused by internet delays in reaching cdxtech.com servers a single request will have significantly faster response time.
When using CDXStreamer manually, you can enter formula arrays using the standard Address Verfication right click-dialog. Make sure you enable the “Autocopy” option which applies the array to all contiguous items in your address list. Formula arrays can also be entered manually should you choose using CTRL-SHIFT-ENTER.
But what if you have a template where you want to automate multiple data items for a variable length list? This is where the use of VBA comes to the rescue. The template VBAFormulaArrays.xlsm is an example of using VBA. It will calculate the corrected and verified address information for a list of addresses (address, city, state and zip) in columns A thru D. The data returned includes the verified address plus Zip+4 and County. Run the macro “GetData” to automatically enter the formulas. You must have CDXStreamer installed with an active demo or purchased account for this to work.
The macro code can to do this can be reviewed in the upprotected Module1 in the GetData subroutine. The code first determines the length of this list and then subsequently uses the formula array property of each column range to enter the data.
This concept can easily be applied to other formulas for speed advantages in requesting data over the internet in bulk.