Convert any number to a string equivalent (like Excel column letters)

Category:
String Manipulation
Type:
Snippets
Difficulty:
Intermediate
Author:
Alan L. Lesmerises

Version Compatibility: Visual Basic 6, Visual Basic 5

More information:
This is an extended version of the snippet included under the Office/VBA category called "Convert an Excel column number to its string equivalent" (ID=4303). I was asked by someone who saw the other function routine how to extend the functionality to any number of characters (i.e., more than 2). This was an interesting problem -- one that had me going for a while. The basic difficulty lies in the fact that the number point where an additional character is added (like going from ZZ to AAA) is not calculated simply (I think you started to see that). The groupings are as follows: A to Z 1 to 26 AA to ZZ 27 to 702 AAA to ZZZ 703 to 18278 AAAA to ZZZZ 18279 to 475254 etc. That series of numbers (26, 702, 18278, 475254, ...) correspond to a cumulative sum of increasing powers of 26. In other words: 26 = 26 ^ 1 702 = 26 ^ 1 + 26 ^ 2 18278 = 26 ^ 1 + 26 ^ 2 + 26 ^ 3 475254 = 26 ^ 1 + 26 ^ 2 + 26 ^ 3 + 26 ^ 4 etc. By taking a cumulative sum of (26 ^ i), you can calculate these break points, and determine how many characters will be needed to represent the number you pass to the subroutine. After thinking about it for a while, I realized that I could build the string one character at a time (from right to left), and use the cumulative sum value (above) to help determine what each character needed to be, and thereby create the whole string. I ran into a limit in an earlier version of this function with numbers larger than 321,272,406 -- numbers larger than that would cause it to blow up because the function calculates a CumSum value for 26^7 (8,353,082,582) which is greater than the limit for Long Integers (2,147,483,648). Therefore, I incorporated the capability to handle extremely large integer values using the Variant data type and forcing the values to a 'Decimal' Variant data subtype using the CDEC(x) function. That gives it the ability to handle integer values up to 79,228,162,514,264,337,593,543,950,335 (over 7.9 x 10^28). I think this will allow input values up to around 26^20 (approx. 1.9 x 10^28), but I can't be entirely sure. I've tested it for input values up to 999,999,999,999,999 (10^15 - 1) which produces strings 12 characters long, & it seems to work fine. I can't test this in Excel for values larger than that because of inherent limits in Excel's number handling abilities (anything past the first 15 digits will just come up all zeroes), but I have confidence in it's ability to work with larger numbers.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:

Code: