UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Wiki:base Ten To Base Letter    
 
   
Gustav
post Sep 13 2012, 07:46 AM
Post#1


UtterAccess VIP
Posts: 2,163
Joined: 21-February 07



I browsed this but the Base26 functions are really not this but rather functions to enumerate Excel coloumns. The reason is that A is not used for zero, thus it becomes a mix of 26/27 base.
I'm not familiar with Wiki editing (neither have the patience) but I have brushed up some old code to create two functions for Base26 conversions for anyone to add to the Wiki page. Note that they can handle quite large numbers:
CODE
Public Function Base26LetterToBase10(ByVal strInput As String) As Variant
'   Converts a string of letters from A to Z representing a Base26 number
'   to its standard Base10 numeric value.
'
'   Examples:
'       varBase10 = Base26LetterToBase10("CPEV")
'       returns 45417
'       varBase10 = Base26LetterToBase10("aaaaCPEV")
'       returns 45417
'       varBase10 = Base10ToBase26Letter("LHZSXOXGQYJXDMDFMYD")
'       returns 333333333333333333333333339
'
'   2012-09-13. Cactus Data ApS, CPH.
    Const cstrZeroChar      As String = "A"
    Const cintLetterBase    As Integer = 26
    
    Dim intChar             As Integer
    Dim intZeroAsc          As Integer
    Dim varValue            As Variant
    Dim intInputLen         As Integer
  
    strInput = UCase(strInput)
    intZeroAsc = Asc(cstrZeroChar)
    varValue = CDec(0)
    intInputLen = Len(strInput)
    For intChar = 1 To intInputLen
        varValue = varValue + (Asc(Mid(strInput, intChar, 1)) - intZeroAsc) * CDec(cintLetterBase ^ (intInputLen - intChar))
    Next intChar
    Base26LetterToBase10 = varValue
    
End Function
Public Function Base10ToBase26Letter(ByVal varInput As Variant) As String
'   Converts a numeric value to its Base26 value
'   represented by a string of letters from A to Z.
'   Input values beyond 999999999999999 must be passed as of type Decimal.
'
'   Examples:
'       strBase26 = Base10ToBase26Letter(45417)
'       returns "CPEV"
'       strBase26 = Base10ToBase26Letter(000045417)
'       returns "CPEV"
'       strBase26 = Base10ToBase26Letter(CDec("333333333333333333333333339"))
'       returns "LHZSXOXGQYJXDMDFMYD"
'
'   2012-09-13. Cactus Data ApS, CPH.
    
    Const cstrZeroChar      As String = "A"
    Const cintLetterBase    As Integer = 26
    
    Dim intZeroAsc          As Integer
    Dim strValue            As String
    Dim varValue            As Variant
    Dim intChars            As Integer
    Dim intChar             As Integer
    Dim varPower            As Variant
    Dim bytValue            As Byte
    
    If IsNumeric(varInput) Then
        varValue = CDec(Int(varInput))
        If varValue < 0 Then
            ' Exit.
        Else
            intZeroAsc = Asc(cstrZeroChar)
            If varValue > 0 Then
                intChars = Int(Log(varValue) / Log(cintLetterBase))
            End If
            intChars = intChars + 1
            strValue = Space(intChars)
            For intChar = 1 To intChars - 0
                varPower = CDec(cintLetterBase ^ (intChars - intChar))
                bytValue = CByte(Int(varValue / varPower))
                If varPower <= varValue Then
                    varValue = varValue - varPower * CDec(bytValue)
                End If
                Mid(strValue, intChar, 1) = Chr(intZeroAsc + bytValue)
            Next
        End If
    End If
    
    Base10ToBase26Letter = strValue
    
End Function

You will se no Excel equivalent as the coloumn system is 1-based and not 0-based which messes up a true base 26 system. Using @ to Z, you could create a base 27 system, but no @ coloumn exists in Excel.
To summarise, Doctor9's original functions should be rephrased to simulate Excel coloumn numbering while mine are for Base26 conversion.
/gustav
Go to the top of the page
 
jleach
post Sep 14 2012, 03:41 AM
Post#2


UtterAccess Editor
Posts: 9,806
Joined: 7-December 09
From: Staten Island, NY, USA


Good point - hadn't considered that it's not a true Base26 system (the wiki mods are going to make note of that in the wiki entry, so I hear).
I have to wonder what type of situation would call for a true Base26 system though? I've wracked my brain a bit but can't seem to come up with anything. The "false" base26, the excel style, works well enough for me for inventory locations, being that the counting for those is 1-based.
Go to the top of the page
 
Gustav
post Sep 14 2012, 04:18 AM
Post#3


UtterAccess VIP
Posts: 2,163
Joined: 21-February 07



No, it's more for the fun.
However, it could be used as a simple number to letter encoding/decoding system.
gustav
Go to the top of the page
 
BananaRepublic
post Sep 14 2012, 06:13 AM
Post#4


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Sure, but I think even ROT13, a popular encoding system treats A as 1 for purpose of shifting the letter by 13 positions.
Go to the top of the page
 
Gustav
post Sep 14 2012, 03:49 PM
Post#5


UtterAccess VIP
Posts: 2,163
Joined: 21-February 07



Well, but ROT13 is a simple symmetric substitution method not related to a number system.
gustav
Go to the top of the page
 
Galaxiom
post Oct 4 2012, 07:21 PM
Post#6



Posts: 542
Joined: 19-October 11



If anyone is interested I have reformulated doctor9's Baseletter functions in a more efficient mathematically oriented structure avoiding some of the loops. I have been careful with the design and verified the results on key values. However someone else should probably check them too.
Go to the top of the page
 
jleach
post Oct 5 2012, 04:50 AM
Post#7


UtterAccess Editor
Posts: 9,806
Joined: 7-December 09
From: Staten Island, NY, USA


I saw that and had been meaning to take a closer look, but haven't gotten around to it quite yet. I'm curious to compare the two methods (I don't recall offhand how it was previously, but it's in the history) and run some benchmark tests for both personal interest as well as verification of the changes.
Cheers,
Go to the top of the page
 
Galaxiom
post Oct 5 2012, 06:38 AM
Post#8



Posts: 542
Joined: 19-October 11



Base10ToBaseLetter
changed the loop that looked for the Most Significant Digit place to a direct calculation of the 26th root. This is the riskiest change considering the scheme is not truly Base26.
Oused an array of integers to represent the Base26 value during the processing while Dennis went directly to a string and then manipulated on the basis of characters.
Dennis worked from MSD to LSD to "carry" when reconciling the missing zeros requiring a loop to cascade the change on each step. I work from LSD to MSD and "borrow" which works on a single pass.
Otherwise it is fundamentally Dennis's concept implemented at the numeric level before converting to alpha.
The more mathematically oriented implementation avoids the little bugs too.
BaseLetterToBase10
I added lower case input acceptance and the concept of the negative return value indicating the character position where the problem occurred.
Why? Because I could. It was returning a Long and there was no valid meaning for 2^31-1 values so why not use a few at zero cost to the efficiency of the function. The counter output costs virtually nothing.
Go to the top of the page
 
Galaxiom
post Oct 5 2012, 06:45 AM
Post#9



Posts: 542
Joined: 19-October 11



I had never contributed to the wiki before and was quite surprised to end up here and find that there was discussion on this function already happening.
posted from the feature page and my post never appeared. I had assumed it was being moderated so I have no idea what is suppoed to be happening.
Go to the top of the page
 
jleach
post Oct 5 2012, 06:47 AM
Post#10


UtterAccess Editor
Posts: 9,806
Joined: 7-December 09
From: Staten Island, NY, USA


We use this board as a place for discussion on specific wiki articles as well as general Q & A regarding the Wiki. Some of the previous posts in this thread were pulled out of the Featured Content news post as well, being that they're more related to the article itself than discussion about the concept of Featured Content for the wiki.
Go to the top of the page
 
Galaxiom
post Oct 19 2012, 07:06 AM
Post#11



Posts: 542
Joined: 19-October 11



I ran some tests on the recent versions of Base10ToBaseLetter. The new version is about one third faster.
Go to the top of the page
 
jleach
post Oct 19 2012, 07:30 AM
Post#12


UtterAccess Editor
Posts: 9,806
Joined: 7-December 09
From: Staten Island, NY, USA


Interesting... I was just looking at this. On my home Win7 machine which is pretty well set up, for 100,000 iterations I got the newer version at 0.78 seconds and the older version at 1.06 seconds. I brought it to work and tested on a choked up XP VM I use to run one piece of legacy software and get 0.515 and 0.687, respectively. I wouldn't have guessed that an XP machine with just enough resources to run a few simple apps would perform nearly twice as fast as a newer Win7 with far more capability. Overhead of Win7 maybe?
Go to the top of the page
 
J.D.
post Oct 19 2012, 08:29 AM
Post#13


UtterAccess VIP
Posts: 4,506
Joined: 31-January 00
From: Columbus, OH USA


Jack,
Reading your test results and the unexpected results between a Win7 machine and XP got me thinking of a thread I was a part of sometime back that dealt with performance. I have no idea if this would pertain to your testing, but the OP worked with MS and there was an identifiable issue. Have a read, particular that of the OP as his follow ups to see if maybe it relates to what you are seeing in your testing.
http://www.UtterAccess.com/forum/Slow-Quer...7-t1968457.html
hat_tip.gi<!--IBF.ATTACHMENT_2295520--></div></td></tr><tr><td class=
Go to the top of the page
 
jleach
post Oct 19 2012, 08:39 AM
Post#14


UtterAccess Editor
Posts: 9,806
Joined: 7-December 09
From: Staten Island, NY, USA


Nice thread, thanks. Not sure if that effects me or not though, being this was strictly VBA, no Jet involved. My guess is more along the lines that the newer OS's may have a few more layers to go through before it gets around to actually processing. No biggie either way, just curious.
Cheers
Go to the top of the page
 
Galaxiom
post Oct 19 2012, 08:06 PM
Post#15



Posts: 542
Joined: 19-October 11



I did my tests on a busy 2.8GHz XP machine running values up to 2 ^ 22 ( ~ 4.2 million). The new version 24 seconds against 36 on the previous.
had been concerned about the possibility of an error by using Int() on the result of a Log() and a real division. Real divisions can sometimes give results just short of what should be an integer so applying Int() can miss the correct result by one.
I ran the loop to 2 ^ 25 and checked every result converted back correctly to base10.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 03:56 PM