VBA Tips
How Can I Easily Perform Multiple String Substitutions?
The VBA Replace function is excellent when you simply need to substitute one or more instances of a unique string with a similarly unique replacement string. However, if you have a string containing multiple instances of a token character, each of which must be replaced by different strings, you're out of luck.
A classic example of this is creating an ADO connection string where you need to dynamically substitute a specific server, database, username and/or password. In the example below we demonstrate a function that provides the ability to perform this kind of token-based string substitution quickly and easily.
Perform Multiple String Substitutions
How Can I Encrypt and Decrypt Data in My Application?
Encryption and decryption algorithms can seem like voodoo to most programmers, and if you aren't a cryptographer, most of them might just as well be. The example below demonstrates a very simple method of encrypting and decrypting data in your VBA application. It wouldn't fool a professional cryptographer for more than five minutes, but it will easily prevent 99.99% of other users from having any access to sensitive data. Although this example uses short strings for demonstration purposes, the encryption algorithm provided can process very large files containing all manner of data very rapidly.
Encryption/Decryption Demonstration
How Can I Shell Out to an Application and Have My VBA Code Wait for that Application to Finish?
You may find yourself needing to run an outside application that does not support COM Automation in the middle of a VBA procedure. Typically you need your VBA procedure to stop and wait until the outside application has finished executing. One good solution to this problem is demonstrated by the Shell and Wait example below.
The Shell and Wait example requires the use of Windows API calls. The zip file download contains two versions of this example, one that supports Excel 2003 and earlier (32-bit only) and one that supports Excel 2007 and later (32-bit and 64-bit).
How Can I Locate a Specific Child Window Handle?
Before you can perform any neat Windows API tricks you need to know the window handle of the window you want to operate on. Locating this can be a non-trivial task for child windows of applications with deeply nested window hierarchies. This example demonstrates how to enumerate the child windows of a specified parent and return the window handle of the child window you specify.
The Enumerate Child Windows example requires the use of Windows API calls. The zip file download contains two versions of this example, one that supports Excel 2003 and earlier (32-bit only) and one that supports Excel 2007 and later (32-bit and 64-bit).