+ Reply to Thread
Results 1 to 12 of 12

VBA Column Letter from Column Number. Explained.

  1. #1
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    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.....
    Last edited by Doc.AElstein; 10-13-2015 at 03:11 PM. Reason: Accredited shg as he requested

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,373

    Re: VBA Column Letter from Column Number. Explained.

    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
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Column Letter from Column Number. Explained.

    Quote Originally Posted by shg View Post
    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:

    Quote Originally Posted by Excel Fox View Post
    ........ 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-
    Last edited by Doc.AElstein; 10-13-2015 at 12:03 PM.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,373

    Re: VBA Column Letter from Column Number. Explained.

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

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Column Letter from Column Number. Explained.

    Hi shg,
    Quote Originally Posted by shg View Post
    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

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,373

    Re: VBA Column Letter from Column Number. Explained.

    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
    Last edited by shg; 10-13-2015 at 02:22 PM.

  7. #7
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Column Letter from Column Number. Explained.

    Hi shg. Thanks again.
    1 )
    Quote Originally Posted by shg View Post
    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:
    Quote Originally Posted by Doc.AElstein View Post
    .............
    .......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:

    Quote Originally Posted by Doc.AElstein View Post
    ..........
    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

  8. #8
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Column Letter from Column Number. Explained.

    Hi Alan,
    Quote Originally Posted by Doc.AElstein View Post
    .......
    _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
    Last edited by Doc.AElstein; 10-20-2015 at 07:23 PM.

  9. #9
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Column Letter from Column Number. Explained.

    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.
    _ ....................

  10. #10
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Column Letter from Column Number. Explained.

    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
    Last edited by Doc.AElstein; 10-25-2015 at 05:34 PM.

  11. #11
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,919

    Re: VBA Column Letter from Column Number. Explained.

    Is there any reason for overcomplicating the method used?

    I've not found anything that simple address manipulation can't handle.

  12. #12
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Column Letter from Column Number. Explained.

    Hi jason.b75
    Thanks for the reply.
    Quote Originally Posted by jason.b75 View Post
    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
    Last edited by Doc.AElstein; 10-25-2015 at 06:14 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1