VBA Column Letter from Column Number. Explained.

VBA Column Letter from Column Number. Explained.

I needed to do the above quite a bit. I could not always understand the methods I googled, so i wrote a few of my own and tried to explain them ans some i googled in detail. I thought I would share my Fuctions and will do some speed tests for comparing the methods. All the codes are explained in as much detail as possible in the ‘Comments in the codes.

_ 1) Address Methods
These methods are based on Manipulating the string representing a cell Address, say $D$1, obtained using something of this form Cells(1, 4 ).Address. A few example codes are given for manipulating such a string to give the required column here
http://www.excelforum.com/developmen...ml#post4213969


_ .............................................

_ 2) Character CHR Methods.
Characters A B C ... etc can conveniently be referred to in VBA by Chr(65) Chr(66) Ch(67) ... etc .So with a bit of mathematics based on the column of interest a variable, n, of type whole number ( such as Long) can be calculated, which then using Chr(n) returns us the required Column Letter ( Or Letters )
I think the key to understanding the next few codes is putting into words how the column Letters are organised. For The first 26 it is simply A B C... etc. For every further 26 going to the right we add an extra letter ( At the left ) AA AB AC.... BA BB BC ... etc. ( At the left ) until we go past ZZ. Then we start again AAA AAB AAC.... etc until XFD ( column 16384 ) ( For Excel from XL 2007 )

Note the following codes may look complicated, but there could be advantages over the above methods which interact with the worksheet and so could cause speed issues when working with final codes.
http://www.excelforum.com/developmen...ml#post4213978
http://www.excelforum.com/developmen...ml#post4213980



_ ............................................

_ 3) “Recursion Function method”
I think I may have understood this. (After about week of thinking about it..). An interesting Example of recursion where the Function calling itself ( That is the definition of Recursion ) does this calling in the code line which actually finally gives the returning value for the Function. I am not sure if this explainable, and certainly not in ' comments as the comments would be rewritten every time the function repeats!!!
Maybe I try to explain in words, for the example of trying to get the column letters ABC for Column Number 731

Here is the simplified Code

Please Login or Register  to view this content.

A fuller code which may help along with my attemted explanation below is given here:
http://www.excelforum.com/developmen...ml#post4213887


So. In that code we look for the example of column Number 731. The working code bit as regards which gives a letter has been encounterered in the various _ 2) Character CHR Methods.

Please Login or Register  to view this content.

However in this code that is the only thing done. ( we do not here work out any Unint26 or Unint26x26 , that is to say we do not work out how many times a 26 or a 26 x 26 is in the Column number, lclm)
The trick of this code is how to get that bit above to work 3 times to get us in this case A then B then C
At line 60 in that code the Fuction “goes off” and does itself 3 times .
The Function then “Starts” effectivelly in total 4 times. The Debug Lines i used to produce the Tables in the above Link show how VBA then “stacks” The same variable names, as they are used in each run and held effectively in a register with different values for each run.

After the condition is no longer met for the code line 60 to go off again , then the program ends sequentially each function ( starting with the one last started.. ) , coming back to the code line where it went off, 3 times, and that code line is carried out 3 times sequentially, but IMPORTANTLY, at each “End” the variable ( lclm ) takes its value from the “stack” “row” for that “run” number. Hence The Letter A then B then C is added to the variable

I guess really going through the code carefully in Debug Mode ( F8 ) is the best way to understand. Unless anyone else has any better explanation.. ... or any other code variations.....

Please Login or Register  to view this content.

It would be a courtesy to correct the attribution of that code -- it's not from excelfox.

http://www.mrexcel.com/forum/excel-q...ml#post3091294

Originally Posted by shg

Please Login or Register  to view this content.

It would be a courtesy to correct the attribution of that code -- it's not from excelfox.

http://www.mrexcel.com/forum/excel-q...ml#post3091294

... Done ! Thanks for the Link. Sorry I missed that Thread

- But note in that link you are clearly attributed in the Thread and the Code:

Originally Posted by Excel Fox

........ Due credit to shg for the letter converter

Please Login or Register  to view this content.

Sorry i missed that particulear MrExcel Thread. - As I had already mentioned per PM, I expected you were the Author, having seen you often giving that code, soI included that link where you were referrenced, but was not sure and was one reason i PMed you.

Alan

P.s. I mentioned both you and Norrie referrences in comments in codes etc. ,
Only the first of codes 1 and all codes 2 are my " inventions", as i said originally i wrote a few of my own and tried to explain others i googled-

So is your question sorted? And if not, what is the question?

Hi shg,

Originally Posted by shg

So is your question sorted? And if not, what is the question?

Thanks for the reply, I guess as it is in Tips and Tutorials, I was not here really directly asking a question.

Per PM I was just asking if you had any comments on my explanation of what you have confirmed is your code. ( Code _3 ). Or if you could explain it better. I think that could be a great insight into how VBA “works” in such a Recursion process, if that is indeed what it is.

Thanks
Alan

A column is named like this:

[A-Z][A-Z]...[A-Z]

... which is sort of like a base 26 notation, except there is no symbol for 0; it's actually a base 26 bijective numeration:

[1-26][1-26]...[1-26]

Since the symbols in a bijective numeration go 1 to N (where N is the base), instead of the usual 0 to N-1 (in decimal, for example, 0 to 9), we subtract 1 from each symbol:

[0-25][0-25]...[0-25]

... where 0 => A and 25 => Z

The function says that the rightmost letter is (number-1) modulo 26, preceded by the symbols associated with the quotient of (number-1) divided by 26. That's applied recursively until the quotient is 0.

Via formula,

Row\Col	A	B	C	D
1
2	16384	D	A1: Input	B2: =IF(A2, CHAR(MOD(A2-1, 26) + 65), "") & B1
3	630	FD	A2: =QUOTIENT(A1-1, 26)
4	24	XFD
5	0	XFD

Hi shg. Thanks again.
1 )

Originally Posted by shg

A column is named like this:
[A-Z][A-Z]...[A-Z]
... which is sort of like a base 26 notation, except there is no symbol for 0; it's actually a base 26 bijective numeration:
.........

I think that sort of ties up with my general idea here:

Originally Posted by Doc.AElstein

.............
.......So. In that code we look for the example of column Number 731. The working code bit as regards which gives a letter has been encounterered in the various _ 2) Character CHR Methods.

Please Login or Register  to view this content.

However in this code that is the only thing done. ( we do not here work out any Unint26 or Unint26x26 , that is to say we do not work out how many times a 26 or a 26 x 26 is in the Column number, lclm)...........

but more professionally said, thanks, that is very helpful!!!! And Thanks for the extra demo with the formula.
So we are in agreement with the working part of the code line and you have made it crystal clear , thanks

_2) The second part of the explanation is how the “Recurssing Function” then works. My summarised explanation:

Originally Posted by Doc.AElstein

..........
The trick of this code is how to get that bit above to work 3 times to get us in this case A then B then C
At line
If lclm > 0 Then ColLtr = ColLtr((lclm - 1) \ 26) & Chr(65 + ((lclm - 1) Mod 26))
in the shg code the Function “goes off” and does itself 3 times . (Starts 3 times )
The Function then “Starts” effectively in total 4 times.

After the condition is no longer met for the code line
If lclm > 0 Then ColLtr = ColLtr((lclm - 1) \ 26) & Chr(65 + ((lclm - 1) Mod 26))
to go off again , then the program ends sequentially each function ( starting with the one last started.. ) , coming back to the code line where it went off, and that code line is carried out 3 times sequentially, but IMPORTANTLY, at each “End” the variable ( lclm ) takes its value from the “stack” “row” for that “run” number. Hence The Letter A then B then C is added to the variable
......

If you could comment on that and maybe say that again ,more clearly / professionally , then i think that would really wrap it up great

Thanks for the continued help ( and patience )
Alan

Hi Alan,

Originally Posted by Doc.AElstein

.......
_2) The second part of the explanation is how the “Recurssing Function” then works. My summarised explanation:
.............
If you could .....maybe say that again ,more clearly ...... , then i think that would really wrap it up great.....

Some last comments

_ 1) Your recursion explanation is probably OK, but just confused a bit by also doing the “base 26 bijective numeration stuff”. So do it again for the recursion alone.

_ 2 ) Using the shg Formulas from the end of post #6 you can develop another code line versions, say codes 4, or call it “FucshgMaths”

_ 3 ) Where are your speed tests?..

_ ...................... So:

_1 ) Consider the Demo Sub and Fuc below. It mirrors the original demo code,
http://www.excelforum.com/developmen...ml#post4213887
but concentrating on the recursion. The shg Recursion idea is used to produce some arbitrary concatenation of Strings.
To understand the principal it is necessary to have a basic understanding of the difference between Global And normal variables. The global variable here, FucRunGlob, defined at the top outside any Fuc or Sub, is, as its name suggests global, that is to say it can be accessed or changed in any Sub or Fuc and will retain that value even after any VBA code ends. We use this here to indicate how many times are function is running, or more precisely it indicates how many times are function starts.
Now the thing about recursion. Basically this process is a routine ( A Sub or Fuc ) that calls itself. VBA allows you to do that. So in this case we may have as many number of ( within reason I guess until “Stack overflow..?? ) the same function running at the same time, that is to say started, as we like. Any variable defined within the fuction will be specific to the Fuction number it was declared in ( and will “live” as long as that Fuction does, that is to say until that Function ends. )
At the start of a routine, VBA makes a copy of that Function, technically said a “copy of the code ( Sub, or whatever Fuc ) has been added to some Stack”in the order of starting running”. . This stack keeps track of the running codes in order that they started, and impotently in our case, any variable defined in the Fuctions are held with any value defined in that code. So as the general rule is, these variable are specific to the Function run in which they were declared. VBA does not then get confused that the name is the same: In each “stack” or “register” the variable and any value is recorded.
Any code is only removed from the “stack” when either End Sub or End Function has executed. ( And any variables declared in it will “die”. ) Then the previous macro will start running again. , .... and so no.... until finally the first Code started is the last to be ended.
So the above can be demonstrate by running the following code, preferably in F8, debug Mode.

Please Login or Register  to view this content.

The results given by the message boxes can be summarised by the following tables which can also be thought of as the “stack” or register that is to say the value held for the current variable FucRun and the current value given to the Fuction SHimpfGlified_RecursionCon. ( Note in this simplified case no Variables are taken into the Fuc routine and a Sub routine would have worked just as well. ).

Function Starts

Code line SHimpfGlified_RecursionCon=	Run Number ( code copy number )
Code line ( First Copy ) Not yet completed	1
Code line ( Second Copy ) Not yet completed	2
Code line ( Third copy ) Not yet completed	3
Code line ( 4th copy ) skipped over	4

Function Ends

Code line SHimpfGlified_RecursionCon=	Run Number ( code copy number )
Code line ( 4th copy ) was not done	4
Code line ( 3rd copy ) =” Fuc No. 3” & …	3
Code line ( 2nd copy ) =” Fuc No. 2 Fuc No. 3” & …	2
Code line ( first copy ) =” Fuc No. 1 Fuc No. 2 Fuc No. 3”	1

In this example the 4th run does not do the
SHimpfGlified_RecursionCon =
( 4th copy ) Code line, and ends leaving
SHimpfGlified_RecursionCon ( 4th copy code line ) =””
An important but very difficult concept to grasp at this point is that although we can start the function as many times as we like, the Function itself is “pseudo” a Global variable. There is only one Function, SHimpfGlified_RecursionCon

The third run is then next completed – it then comes back to that part of that code line where it went off,
the code line
SHimpfGlified_RecursionCon =
( third copy ) Gives then
SHimpfGlified_RecursionCon =” Fuc No. 3”
Which had effectively concatenating ” Fuc No. 3” onto the next run number down the line going backwards which had concatenated ” Fuc No. 2” onto the next run number down the line going backwards which had concatenated on ” Fuc No. 1” which then caries on until it finishes where it went off, which is in the code line in the calling Sub routine
What made the last point difficult to grasp is that while Going through in F8 debug mode we were not actually stepping through the same code, - we were stepping through the various copies of the code for the 4 copies of the Function, starting at the first, going sequentially to the forth, then back to the third, then sequentially back down to the first.
It might therefore be useful to take a look at the “Psuedo” Codes here :
http://www.excelforum.com/developmen...ml#post4221211
These represent how VBA holds a copy of the 4 Functions, at the point, say, when the 4th copy of the Function has started, at which point all Function Code copies are active.
Further, the “pseudo” code here:
http://www.excelforum.com/developmen...ml#post4221234
is intended to demonstrate how VBA progresses from the initial start of the calling Sub, going through all Fucs and finally returning to and ending with the calling Sub. Effectively then this is one code which progresses from top to bottom.

_ ................

The Functions shgColLtr , ColLtrSHimfGlified, ColLtr are a variation of the above. The important variable within the Copies of the code which changes each time is the lclm which then also has mathematics done to it to satisfy the “((base 26 bijective numeration) -1 stuff)”. The variable in the copies of the code is that taken in by the function which effectively is also held in the “stack” register with the value which is given differently for each copy of the code.
The important recursion line is Line 60. For a three letter Column there are 4 copies of the code run. At the end of the running of the Third copy, the ( global )
shgColLtr
was given C ( concatenated on the end ) of the second code copy which now ends at the point it went off at returning the B ( concatenated on the end ) of the first code copy which now ends at the point it went off in the calling Sub routine returning the A
As with the previous simplified code, the Function ColLtrSHimfGlified

Please Login or Register  to view this content.

Is demonstrated via an opened up single “Pseudo” Code here:
http://www.excelforum.com/developmen...ml#post4221316

Gruß Gott
Codes 4
_ 2) Codes 4

shg maths Formulas Evaluate and VBA

From Post # 6
http://www.excelforum.com/tips-and-t...ml#post4214733
one can start with the formula in cell B4, and subsitute all Cell Address referrences with the actual formula in those cells to finally get a one line formula
Using Excel 2007

=IF(QUOTIENT(QUOTIENT(16384-1, 26)-1, 26)<>0,, CHAR(MOD(QUOTIENT(QUOTIENT(16384-1, 26)-1, 26)-1, 26) + 65), "") & IF(QUOTIENT(16384-1, 26)<>0, CHAR(MOD(QUOTIENT(16384-1, 26)-1, 26) + 65), "") & IF(16384<>0,, CHAR(MOD(16384-1, 26) + 65), "") & ""

Allowing for use of a variable for the column of interest, lclm, along with some consideration of the use of Quotes in VBA,
http://www.excelfox.com/forum/f2/spe....html#post9517
http://www.mrexcel.com/forum/excel-q...ml#post4283381
gives us a string to be used in the VBA Evalute Method

Please Login or Register  to view this content.

and with some simplification to

Please Login or Register  to view this content.

and finally a code of this form

Please Login or Register  to view this content.

_ .....................

Further The Evaluate formula can be written in a VBA Form

Please Login or Register  to view this content.

_ ....................

Speed tests

_ ... Some speed test results. These are some averages on a few fairly old computers. The code used for the speed tests are given at the end of this Thread. In that code the time is measured for filling a 1 Dimensional Array with all column letters ( 16384 for XL 2007 and above )

_ ..All codes ( that is to say the called Sub routines) are to be found in the appropriate links given in this Thread, EDIT: except the latest shg Maths codes given in the last Post #9 )

_ .. I had heard that the recursion is inefficient, but clearly for a short process such as this it works very quickly. The simple formula in VBA based on the formula in the recursion codes is also very fast. But generally speed seems in most cases not really to be an issue, assuming it would probably be done less than 16384 times anyway. Even the methods based on Cell Address manipulation are fast. - Accessing different Range Properties yields different times, and possibly the Address property is a fairly quick one.
( One last point worth noting is that the with the exception of theAddress manipulation and recursion codes, all codes would need to be modified should Excel ever extent its column number to that requiring more than 3 Letters )
_. The Do While Looping code appears the fastest, and supports what one hears that the looping is a bit quicker usually than a recursion code working otherwise similarly. This is also supported by the slightly modified shg code, ColLtrshgAlan, where the last run of the Function is eliminated.

Time in Seconds:

	XL 2007 Computer 1	XL 2010 Computer 2	XL 2007 Computer 3
Cell Address manipulation
Noriespdevito3LetterFromRangeAddress	0.46	0.33	0.19
AlanFukABitOfADonkey	0.94	0.56	0.37
RepRepAddress	.49
Chr() Calculations
AlanBiggerFukADonkey	1.64	1.39	1.02
AlanBiggerFukADonkeyOneliner	0.79	0.8	0.56
FukOutChrWithDoWhile	0.05
shg Recursion
shgColLtr	0.09	0.087	0.061
ColLtrSHimfGlified	0.072	0.086	0.046
ColLtrshgAlan	0.060
shg Formulas
FucshgMathsEval	15.12	7.05	6.5
FucshgMathsVBA	0.13	0.125	0.07

Code:

Please Login or Register  to view this content.

[/color][/code]

BTW.. A File with all the codes in it ( somewhere! )

https://app.box.com/s/lts3h2ejf33yj2i2xmaq5zrq784qwwl9


Alan

Hi jason.b75
Thanks for the reply.

Originally Posted by jason.b75

Is there any reason for overcomplicating the method used?
I've not found anything that simple address manipulation can't handle.

I just wanted to compare methods, understand them as I do not like using any code i do not understand, and share the ( little! ) VBA knowledge I have.
Of course “Complicated” here is how ( or who/ what) is looking at the code. What we “see” and VBA “sees” may be different, which was one reason for doing some speed tests. ( I have been amazed how a complicated VBA Array Code can “knock the socks off a much “simpler looking” Spreadsheet interaction code, for example. ). I expected therefore the FucshgMathsVBA Function might have a parallel hear with great speed advantages, and I expected the Address manipulation might therefore be slow. But as i already commented it appears not to be, and I suggested reasons why.

At the end of the day no big difference is to be seen, but before doing the tests i did not know that. I suppose the FukOutChrWithDoWhile is he best and not one of the simplest looking, but the performance increase is clearly splitting hairs. As always, IMO, use what you understand and are happy with. VBA seems to have endless variations on how to do things.

The main point again was understanding, hence the “Explained” in the Thread Title. Most codes or similar versions are to be found in the Web, but I have never seen them explained. I only “invented” a few of the codes, and clearly not the better ones!!! ). I learnt at last what recursion was about, - as with everything, not at all difficult once you know.

I am just a sucker for knowing how things work sometimes even if at the end of the day i may not ever use them. ( But someone should try every method, IMO, - Occasionally unexpected results come up )

Alan