The Conundrum of VBA Macros: Unraveling Microsoft's Restrictive Stance

In the world of software development, automation is king. And when it comes to automating tasks within Microsoft Office applications like Excel, Word, and PowerPoint, Visual Basic for Applications (VBA) macros have long been the tool of choice. However, recent trends indicate a tightening of the reins by Microsoft on the use of VBA macros, leaving developers and users alike grappling with the implications.

VBA macros provide a powerful way to automate repetitive tasks, streamline workflows, and enhance productivity within Office applications. With VBA, users can write custom scripts to perform actions ranging from simple formatting tasks to complex data manipulations. For years, VBA has been a staple tool for power users and developers looking to extend the capabilities of Office applications beyond their out-of-the-box functionality.

At CDX Technologies, we've taken the step of code signing our Templates and VBA add-ins. This identifies our organization as the originator and only editor of the code.  But the advantage of this has been diminished by Microsoft's recent changes.

Microsoft's approach to VBA macros has become increasingly restrictive in recent years, driven by concerns over security and potential misuse. One of the most significant moves was the introduction of the "Macro Security" feature, which prompts users to enable macros only from trusted sources or disables them altogether by default. While this feature aims to protect users from malicious code, it also adds an extra layer of friction for legitimate macro usage.

Moreover, Microsoft has implemented additional measures to restrict VBA macros, particularly in cloud-based versions of Office applications such as Office 365 and SharePoint Online. These platforms often limit or disable the execution of VBA macros altogether, citing security and compatibility reasons. This shift towards cloud-centric computing has left many traditional VBA developers feeling sidelined, as their once-reliable toolset becomes increasingly marginalized.

Another area of concern is Microsoft's emphasis on alternative technologies such as Office Add-ins and Office Scripts. While these newer solutions offer some advantages in terms of security and cross-platform compatibility, they often lack the flexibility and power of VBA macros. Additionally, transitioning existing VBA codebases to these new platforms can be a daunting task, requiring significant time and resources.

Despite these challenges, there are still ways for developers to leverage VBA macros effectively within the Microsoft ecosystem. One approach is to adopt a hybrid strategy, combining the best aspects of VBA macros with newer technologies like Office Add-ins. By encapsulating VBA functionality within secure, sandboxed environments, developers can mitigate some of the security concerns associated with traditional macros.

Furthermore, Microsoft has introduced tools like the Office JavaScript API, which allows developers to interact with Office applications using modern web technologies. While not a direct replacement for VBA macros, these APIs offer a path forward for developers looking to modernize their Office automation workflows while maintaining compatibility with legacy systems.

In conclusion, while Microsoft's stance on VBA macros may seem restrictive at first glance, it's essential to recognize the underlying motivations driving these changes. By prioritizing security and embracing newer technologies, Microsoft aims to create a more robust and resilient ecosystem for Office users and developers alike. While the transition may be challenging, embracing these changes can ultimately lead to more secure, efficient, and future-proof automation solutions within the Microsoft ecosystem.

2022 American Community Survey Data Release

The CDXZipStream ACS data feeds for state, county, ZIP code (ZCTA), place, CBSA (Core-Based Statistical Area) and Tract have been updated with the latest data releases from the U.S. Census Bureau. These data are from the American Community Survey, which surveys hundred of thousands or individual addresses monthly. It is the largest and most detailed survey that the US Census conducts. These feeds are available in the CDXZipStream Premium ACS version. Data are aggregated over the years 2018 through 2022.

 

The demographic areas covered in all ACS feeds are: age, population, household size, household income, individual earnings, race and ethnicity, educational attainment, school enrollment, housing value and rent, mortgage status, and marital status. For a full listing of all data fields included in the feeds, please download the CDXZipStream Data Feeds and Definitions file here.

 

If you already own CDXZipStream ACS Premium and have an active data maintenance contract, select the "Account" option on the CDXZipStream Excel menu and click on "Check for Available Database Updates". Then select the option to update your premium database. 

 

For users without an active account you can update your data feeds by logging into your on-line account on CDXTech.com. Under "My Account", then "Product Management", select "Buy Data Update" and then purchase the Premium Maintenance contract. If you don't have the correct CDXZipStream version select "Upgrade License" in the account area and select the option to upgrade to the ACS version. The latest data will automatically be included with your purchase.

New User Account Interface at CDXTech.com

We've updated our user interface when you log into our website. So things will look a bit different than the last time you visited. All of the menu navigation options will now be available on the left hand side of the screen as shown in the image below.

The previous menu appeared at the top did not persist when clicked as the new interface does. To expand or contract the various areas, simply press the + or - icons shown. The menu items have exactly the same functionality as in the previous interface. If you have products other than CDXZipStream they will be show as separate sections.

 

Quick Radius Report with CDXDistance

We've covered in past articles how to use the CDXRadius function to analyze the closest locations in mailing lists and other data applications. The basic technique is to create a CDXRadius formula report and then evaluate the distance of every item in the list using an Excel VLookup command. For more detail on this please see the support article we've previously published Advanced Zip Code Radius Calculator and Finder with Microsoft.

 

The technique with CDXRadius works best with large lists and if you will be using the same radius report repeatedly. For shorter lists and quick reports you can use the CDXDistance functions by adding a simple formula.

 

The spreadsheet below shows an example customer list where we want to evaluate all customers within 100 miles of the zip code 07869.

To do this simply enter the following formula beginning in the first row of your data which in this case is cell H3 referencing the first customer zip in G3.

 

=IF(@CDXDistance(G3,"07869")<=100,"YES","NO")

 

and then copy this down your list.

 

The formula returns "YES" or "NO" depending if the customer is within the specified radius. Should you wish you can also include fixed cell references (i.e. $A$1) for the central zip code and radius distance to make updating easier. This technique is also more forgiving than using VLookup when your customer data includes zip codes without leading zeroes or with Zip+4. It also works well with our Canadian database as well as with US data.

 

CDXZipStream Bulk Radius Analysis Template

One common request we often hear is how to run radius calculations in bulk. For instance, if you'd like to know all ZIP Codes within a 10 mile radius of a list of Zip Codes. This information can be used in performing a nearest store report, optimizing business locations or other applications. We've previously covered techniques for this as described in the blog post Get Radius Information for a List of ZIP Codes.

 

To help automate this we've developed a template to streamline the creation of these lists. Just one click can analyze thousands of zip codes. The free template along with may others is available to CDXZipStream users on our website who have the Lite Version of CDXZipStream or better.

 

Shown below is an example of a bulk radius report.

Just enter your ZIP Code list in column B. You can use the CDXZipList right-click function to generate a list of zip codes by city, state or county for this input. Use the option to only return the zip code column.

 

Then specify a radius distance and press "Calculate". Beginning in Column C, all ZIP Codes within that radius will be shown in individual cells. A radius distance up to 200 miles can be specified. The template has also just been updated to be able to use the latitude and longitude formats (i.e. 40.84|-74.56) rather than zip codes. This format was added with the CDXZipStream 5.4 release covered in last months newsletter.

 

You can also choose to return both the zip code and distance by clicking the check box on the right of the screen. Options to use the U.S. or Canadian databases are also available.