excel - string encryption/decryption

Question

I am interested if it's possible to do string encryption/decryption using Excel Visual Basic and some cryptographic service provider.

I have found a walk-through Encrypting and Decrypting Strings in Visual Basic, but it seems it's valid for standalone Visual Basic only.

So would you suggest me another encryption method or show how the walk-through could be adopted for Excel Visual Basic?

Answer 1

The link you provide shows how to perform string encryption and decryption using VB.NET, and thus, using the .NET Framework.

Currently, Microsoft Office products cannot yet use the Visual Studio Tools for Applications component which will enable Office products to access the .NET framework's BCL (base class libraries) which, in turn, access the underlying Windows CSP (cryptographic server provider) and provide a nice wrapper around those encryption/decryption functions.

For the time being, Office products are stuck with the old VBA (Visual Basic for Applications) which is based on the old VB6 (and earlier) versions of visual Basic which are based upon COM, rather than the .NET Framework.

Because of all of this, you will either need to call out to the Win32 API to access the CSP functions, or you will have to "roll-your-own" encryption method in pure VB6/VBA code, although this is likely to be less secure. It all depends upon how "secure" you'd like your encryption to be.

If you want to "roll-your-own" basic string encryption/decryption routine, take a look at these link to get you started:

Encrypt a String Easily
Better XOR Encryption with a readable string

vb6 - encryption function
Visual Basic 6 / VBA String Encryption/Decryption Function

If you want to access the Win32 API and use the underlying Windows CSP (a much more secure option), see these links for detailed information on how to achieve this:

How to encrypt a string in Visual Basic 6.0

Access to CryptEncrypt (CryptoAPI/WinAPI) functions in VBA

That last link is likely the one you'll want and includes a complete VBA Class module to "wrap" the Windows CSP functions.