Who's Bored? (1 Viewer)

pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 15:30
Joined
Nov 30, 2011
Messages
8,494
Howdy peeps,

This is not an urgent or pushing matter. I am just having a slow/boring day - so thought I would tease my brain. So trying something bizarre. This is what I thought I would do, a logical challenge - Get Sequence String based on position.

The String sequence is A, B, C, D.... Z, AA, AB, AC.... AZ, BA, BB, BC.. ZZ, AAA, AAB, AAC... ZZZ (you get the idea !)

Input : Number - (zero bound) example 5, 51
Output : String - example (for the input 5) F, (for the input 51) AZ

So I have two option (the very basic/lazy Case and Loop).
Code: 
Public Function getStringSeq(tmpVar As Long) As String
    Select Case tmpVar
        Case Is <= 25
            getStringSeq = Chr(tmpVar + 65)
        Case 26 To 51
            getStringSeq = Chr(65) & Chr(tmpVar + 65 - 26)
        Case 52 To 77
            getStringSeq = Chr(66) & Chr(tmpVar + 65 - 52)
        Case 78 To 103
            getStringSeq = Chr(67) & Chr(tmpVar + 65 - 78)
        Case 104 To 129
            getStringSeq = Chr(68) & Chr(tmpVar + 65 - 104)
        Case 130 To 155
            getStringSeq = Chr(69) & Chr(tmpVar + 65 - 130)
        Case 156 To 181
            getStringSeq = Chr(70) & Chr(tmpVar + 65 - 156)
        Case 182 To 207
            getStringSeq = Chr(71) & Chr(tmpVar + 65 - 182)
        Case 208 To 233
            getStringSeq = Chr(72) & Chr(tmpVar + 65 - 208)
        Case 234 To 259
            getStringSeq = Chr(73) & Chr(tmpVar + 65 - 234)
        Case 260 To 285
            getStringSeq = Chr(74) & Chr(tmpVar + 65 - 260)
        Case 286 To 311
            getStringSeq = Chr(75) & Chr(tmpVar + 65 - 286)
        Case 312 To 337
            getStringSeq = Chr(76) & Chr(tmpVar + 65 - 312)
        Case 338 To 363
            getStringSeq = Chr(77) & Chr(tmpVar + 65 - 338)
        Case 364 To 389
            getStringSeq = Chr(78) & Chr(tmpVar + 65 - 364)
        Case 390 To 415
            getStringSeq = Chr(79) & Chr(tmpVar + 65 - 390)
        [COLOR=Green]'Case can go on and on and on.....[/COLOR]
    End Select
End Function

Public Function getStringSeq2(tmpVar As Long) As String
    Dim iCtr As Long, depthInt As Long
    Dim preFix As String, midCtr As Long
    Dim tmpStr As String, tickCtr As Long
    Dim loopCtr As Long
    depthInt = 1
    
    For iCtr = 1 To tmpVar
        [COLOR=Green]'Determine the Base 0-25 is A-Z.
        'When it hits 26 and multiplies of 26. The sequence will increase the previous length.[/COLOR]
        tickCtr = iCtr Mod 26
        If tickCtr = 0 Then
            loopCtr = loopCtr + 1
            [COLOR=Green]'Get the number of depths it needs to go 1'st time it would be 2, second 3 etc.[/COLOR]
            depthInt = depthInt + (iCtr Mod (26 * loopCtr))
            [COLOR=Green]'Make sure the loop is not repeated until the next 26th factor.[/COLOR]
            tickCtr = tickCtr + 1
            preFix = vbNullString
            For midCtr = 1 To depthInt
                preFix = preFix & Chr(65 + loopCtr - midCtr)
            Next
        End If
        tmpStr = preFix & Chr(65 + tickCtr)
    Next
    getStringSeq2 = tmpStr
End Function
So testing,
Code: 
? getStringSeq(401)
OL
? getStringSeq2(401)
OL
Although I have a feeling, this could be solved by simple mathematical/arithmetic calculations. Anyone have alternatives? ;)
 
B

BlueIshDan

&#9760;
Local time
Today, 11:30
Joined
May 15, 2014
Messages
1,122
Re: Who's bored?

I LIKE IT! You're my Friday's saviour from boredom!
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 15:30
Joined
Nov 30, 2011
Messages
8,494
Re: Who's bored?

BlueIshDan said:
I LIKE IT! You're my Friday's saviour from boredom!
Click to expand...
Ha Ha ! I was expecting your reply ! Anyway, the two methods have their disadvantages.

getStringSeq - If the number is > 415, you need to add another case. So every time you need to get a String sequence you need to hardcode the Case.

getStringSeq2 - Loops IMVHO, a bit more complicated. Even I (because I wrote the code) could not at a later stage understand why I needed them in the first place. Also so many variables involved.

So looking for an alternative suggestion.
 
B

BlueIshDan

&#9760;
Local time
Today, 11:30
Joined
May 15, 2014
Messages
1,122
Re: Who's bored?

You're going to want to think of learning to count in a way where you roll over at 26

Like counting in octal and binary.
 
B

BlueIshDan

&#9760;
Local time
Today, 11:30
Joined
May 15, 2014
Messages
1,122
Re: Who's bored?

(26^5)(26^4)(26^3)(26^2)(26^1) for each placement then compare your number to each.
 
Last edited:
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 15:30
Joined
Nov 30, 2011
Messages
8,494
Re: Who's bored?

BlueIshDan said:
(26^5)(26^4)(26^3)(26^2)(26^1) for each placement then compare your number to each.
Click to expand...
I do not get it..

Well maybe it is my fault. The problem is the Sequence does not exist anywhere; it is not in table or array. Its arbitrary, based on the number it should be able to pick the appropriate String.

Think of it as an Excel sheet, and if you say 1'st row and 26'th column the cell range would be Z1. I would want to say 25 and it should be able to give me Z.
 
B

BlueIshDan

&#9760;
Local time
Today, 11:30
Joined
May 15, 2014
Messages
1,122
Re: Who's bored?

Just replace the 8 with 26(27?) and away you go with replacing each number with their respective character.

Buzzle
Code: 
Conversion From Decimal to Octal

It is one of the most commonly explained problems in computer basics. An octal number can be converted to a decimal number using the following formula:

Decimal Form = Ʃ(ai x 8i)

In this formula, 'a' is the individual digit being converted, while 'i' is the position of the digit counting from the right-most digit in the number, the right-most digit being position 0. (This means from the decimal point. We will get to converting octal fractions later)

Here's how to do it step-by-step, using the octal number 765:
Figure out how many digits there are in the number. 765 has 3 digits.
Then take each digit and multiply it with 8(n-1), where 'n' is the position of the digit from the right. So 7 will be multiplied by 8(3-1), which is 82, or 64. And 7 x 64= 448.
Similarly, you take 6 x 8(2-1) (= 48), 5 x (1-1) (= 5), then add all three results to get the decimal number. So 448 + 48 + 5 = 501.
Thus (765)8 = (501)10
Here's another example:
Read more at Buzzle: http://www.buzzle.com/articles/octal-to-decimal-how-to-convert.html
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 15:30
Joined
Nov 30, 2011
Messages
8,494
Re: Who's bored?

Yes, well vaguely. What does that have to do with this code?

It would be great if I understood what in_number, index and GetAlpha does. ;)
 
B

BlueIshDan

&#9760;
Local time
Today, 11:30
Joined
May 15, 2014
Messages
1,122
Re: Who's bored?

because essentialy what you're doing is counting in 26 instead of 8. Then replacing each digit with their respective character.
 
B

BlueIshDan

&#9760;
Local time
Today, 11:30
Joined
May 15, 2014
Messages
1,122
Re: Who's bored?

Shit that formula is from octal to decimal ffs. We need decimal to octals o that we can modify it to 26 instead of 8
 
B

BlueIshDan

&#9760;
Local time
Today, 11:30
Joined
May 15, 2014
Messages
1,122
Re: Who's bored?

Done: FIXED



Code: 
Private Sub Command5_Click()
    
    If Len(txtNumber.Value & vbNullString) > 0 Then
        If IsNumeric(txtNumber.Value) Then
            lblResult.Caption = DecimalToHexavigesimal(CLng(txtNumber.Value), 26, 5)
        End If
    End If

End Sub

Private Function DecimalToHexavigesimal(ByVal dec As Double, ByVal base As Integer, ByVal fill_digits As Integer) As String

    Dim decimalNumber, i, j As Integer: i = 0
    Dim baseNumber(100) As String
    
    If dec = 0 Then
        baseNumber(j) = 0
        i = i + 1
    End If
        
    While dec > 0
        
        baseNumber(i) = dec Mod base
        
        dec = IIf(dec >= base, CDbl(Split(CStr(dec / base), ".")(0)), 0)
        
        i = i + 1
    Wend

    i = i - 1
    
    For j = i To 0 Step -1
        DecimalToHexavigesimal = DecimalToHexavigesimal& Chr(CInt(65 + baseNumber(j)))
    Next
    
    For i = 0 To fill_digits - Len(DecimalToHexavigesimal)
        DecimalToHexavigesimal= "A" & DecimalToHexavigesimal
    Next
    
End Function
 
Last edited:
B

BlueIshDan

&#9760;
Local time
Today, 11:30
Joined
May 15, 2014
Messages
1,122
Re: Who's bored?

For some reason it goes all wonky after 39 but it's a start xD
Edit: Fixed the above.
 
Last edited:
V

vbaInet

AWF VIP
Local time
Today, 15:30
Joined
Jan 22, 2010
Messages
26,374
Re: Who's bored?

You two are just nerds! :)

Why all the octal business anyway? Just do it recursively.
 
B

BlueIshDan

&#9760;
Local time
Today, 11:30
Joined
May 15, 2014
Messages
1,122
Re: Who's bored?

Because it's quicker to work with Hexavigesimal conversion than to do it recursively when working with larger numbers.
 
B

BlueIshDan

&#9760;
Local time
Today, 11:30
Joined
May 15, 2014
Messages
1,122
Re: Who's bored?

At least I think it's called Hexavigesimal hahaha
 
V

vbaInet

AWF VIP
Local time
Today, 15:30
Joined
Jan 22, 2010
Messages
26,374
Re: Who's bored?

Hexavi?? what? :)

If it's for Excel there would be a limit so the number of times it calls would be negligible.
 
B

BlueIshDan

&#9760;
Local time
Today, 11:30
Joined
May 15, 2014
Messages
1,122
Re: Who's bored?

He was so bored he went home after planting this seed in my mind?
 
You must log in or register to reply here.

Similar threads

isladogs
Translate & Speak
Replies
17
Views
4,130
oleronesoftwares
oleronesoftwares
A
Stuck At Code That Read Value From Given Array
Replies
6
Views
865
The_Doc_Man
The_Doc_Man
A
VBA barcode code generator not working
2 3
Replies
55
Views
6,985
arnelgp
arnelgp
Gasman
Not in List event?
Replies
10
Views
1,326
moke123
moke123
E
Converting nested Access queries into raw nested SQL queries
Replies
0
Views
2,760
ElaineBaker
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom