• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Interpolate and/or Extrapolate alpha-numeric values.

sumitbansal

sumitbansal

Member
Hi,
Recently I finished one of my vb projects and wanted to sell it. For that I have to generate keys for activation. Interpolation and Extrapolation is a good method of generating version specific keys. uptill now, I was using only numeric keys. However, now i want to make them more robust by making the keys alpha-numeric. So, for that I wanted to know how can we interpolate and/or extrapolate alpha-numeric values in excel. As far as I know, it is quite simple using only numerical values. However, with alpha-numeric values, i am out of guesses. Please help.

Thanks,
Sumit.
 
Hi Sumit ,

I have not understood what you want ; can you give an example of what you mean by extrapolating alphan umeric values ?

Narayan
 
NARAYANK991 said:
Hi Sumit ,

I have not understood what you want ; can you give an example of what you mean by extrapolating alphan umeric values ?

Narayan
Click to expand...


Hi NarayanK991,
All I want is to be able to generate keys for my software using excel. The keys would be 20 characters in length and alpha-numeric with 4 sets of 5 characters (or simply 20 characters long). Now, I may pre-define the starting key and the ending key and interpolate the remaining pre-defined number of keys depending upon these two values.

Now, I may release further updates/versions of the software and for that I choose to differ the keys generated thereof (Extrapolate), keeping the base method of defining keys same.

I hope I am able to give you and insight about my requirements.

Thanks,
Sumit
 
example may be something like this:
First Key: J61GQ-S49SX-1CGUU-WCFZQ
Last Key: AYQ7T-PF6T8-X4JY8-TNH1H

Now, I want to create another 100 different keys using these two values (Interpolate).

The concept of first and the last key is that I want to keep track of the licenses sold in a particular version of software. But, with each version, the base difference of the keys may be there which would allow old keys not to work in this new version BUT would allow new keys to work with previous versions of the software (Extrapolate).
 
Hi Sumit ,

I am nowhere closer to understanding the meaning of interpolation or extrapolation.

When we have two numeric values , say 3 and 7 , we can easily say that a value of 5 is an interpolation , whereas a value of 11 is an extrapolation.

With reference to your example of a starting key of J61GQ-S49SX-1CGUU-WCFZQ and an ending key of AYQ7T-PF6T8-X4JY8-TNH1H , what is the logic by which any key will be :

a. A valid key
b. An interpolated key or an extrapolated key

Narayan
 
Hi,
The Starting Key and the Last Key would be something without logic like just a conglomerate of numbers (Eg. 3 and 15). Now, If I have to create 29 more keys using these two number then the interpolation variable would be 0.4. Now, if we use these 29+2 = 31 keys then these are interpolated keys. However, if I create a new version of software/set of 29+2 = 31 keys, using say 20 and 32, then also the interpolation variable would be 0.4. As, these new set of keys also depend on the interpolation variable of 0.4, but, are not related to the original set of 3 and 15; So, in a sense, they are extrapolated values of the original set but with different start and end points.
And this interpolation value of 0.4 will define if the keys are valid or not.
 
Hi Sumit,
Can you define interpolation and extrapolation without using those words in the definitions? Then provide some examples of how you created such keys. (Don't omit any steps... what you might assume is clear to others is not clear to others at all.)

-Sajan.
 
Hi, sumitbansal!

I'd discard the flying pig of inter/extra-polating non numeric values since regarding Excel subject is like Santa or Wonder Woman: they don't exist.
I'll go for something like a Base64 encoding that generates 4 bytes for each 3 bytes of input, so with a 15 char length entry you'll get the 20 length output.
The code is widely available (Google it) and it converts each byte to its binary value (8 bits) and then groups them each 6 bytes (2^6=64), assigning this chars for values from 0-63:
A-Z,a-z,0-9,+/
You can replace "+" and "/" by any others, like "A" and "0" for example.

Regards!
 
Sajan said:
Hi Sumit,
Can you define interpolation and extrapolation without using those words in the definitions? Then provide some examples of how you created such keys. (Don't omit any steps... what you might assume is clear to others is not clear to others at all.)

-Sajan.
Click to expand...
Hi Sajan,
I am trying to make a detailed step-by-step procedure. will be back with it soon.

Thanks,
Sumit.
 
Debraj said:
Hi Sumit,

I hope, you have already visited those site.. if not.. then have a look..

http://stackoverflow.com/questions/453030/how-can-i-create-a-product-key-for-my-c-sharp-app
http://www.serialkeymaker.com/how_to_implement_serial_key_maker.aspx
http://social.msdn.microsoft.com/Fo...-a-step-in-your-installer?forum=winformssetup

Still, I also want to have a look of those words or logic.. :)
interpolation and extrapolation
Click to expand...
Hi Debraj,
Thanks for the links... I never thought there would be links for this. Going through these now... will get back after going through them...

Regards,
Sumit.
 
SirJB7 said:
Hi, sumitbansal!

I'd discard the flying pig of inter/extra-polating non numeric values since regarding Excel subject is like Santa or Wonder Woman: they don't exist.
I'll go for something like a Base64 encoding that generates 4 bytes for each 3 bytes of input, so with a 15 char length entry you'll get the 20 length output.
The code is widely available (Google it) and it converts each byte to its binary value (8 bits) and then groups them each 6 bytes (2^6=64), assigning this chars for values from 0-63:
A-Z,a-z,0-9,+/
You can replace "+" and "/" by any others, like "A" and "0" for example.

Regards!
Click to expand...
Hi SirJB7,
I know inter/extra-polating non numeric values is something not present in Excel as we know. that is the main reason, I wanted to create a macro to do it, using some logic which i was not able to develop. However, the Base64 method you described seems interesting... will Google it and then get back with the results.

Thanks,
Sumit.
 
Hi, sumitbansal!

I found a long time ago developed Base64 module, with functions for coding and decoding. Give a look at the uploaded file.

It uses 4 named ranges:
BaseNCell: 2,4,8,16,32,64,128,256
AlphabetCell: string of at least BaseNCell characters
SeparatorNoCell: number of grouped characters (0 if no separators)
SeparatorCharCell: group separator string

This is the code:
Code: 
Option Explicit

' constants
Global Const gksWS = "Hoja1"
Global Const gksBaseN = "BaseNCell"
Global Const gksAlphabet = "AlphabetCell"
Global Const gksSeparatorNo = "SeparatorNoCell"
Global Const gksSeparatorChar = "SeparatorCharCell"
Global Const gksZero = "0"

Public Function sBaseNCode(psInput As String, piBase As Integer) As String
    '
    ' constants
    '
    ' declarations
    Dim sAlphabet() As String
    Dim iSeparatorNo As Integer, sSeparatorChar As String
    Dim sText As String, sBinary As String, sBase As String, iChunk As Integer
    Dim I As Integer, J As Integer, K As Integer, A As String
    '
    ' start
    '  params
    sText = psInput
    With Worksheets(gksWS)
        A = .Range(gksAlphabet)
        iSeparatorNo = .Range(gksSeparatorNo)
        sSeparatorChar = .Range(gksSeparatorChar)
    End With
    '  alphabet
    ReDim sAlphabet(piBase)
    For I = 1 To piBase
        sAlphabet(I) = Mid(A, I, 1)
    Next I
    '  chunk
    iChunk = Round(Log(piBase) / Log(2), 0)
    '
    ' process
    '  build binary
    sBinary = ""
    For I = 1 To Len(sText)
        A = ""
        K = Asc(Mid(sText, I, 1))
        For J = 7 To 0 Step -1
            A = A & Sgn(K And 2 ^ J)
        Next J
        sBinary = sBinary & A
    Next I
    K = (Len(sBinary) Mod iChunk)
    If K <> 0 Then sBinary = sBinary & String(iChunk - K, gksZero)
    '  chunk each N
    sBase = ""
    For I = 1 To Len(sBinary) Step iChunk
        A = String(8 - iChunk, gksZero) & Mid(sBinary, I, iChunk)
        K = 0
        For J = 7 To 0 Step -1
            K = K + Val(Mid(A, 8 - J, 1)) * 2 ^ J
        Next J
        sBase = sBase & sAlphabet(K + 1)
    Next I
    '  format
    If iSeparatorNo <> 0 Then
        A = ""
        K = Int((Len(sBase) + iSeparatorNo - 1) / iSeparatorNo)
        For I = 1 To K
            If I <> 1 Then A = A & sSeparatorChar
            A = A & Mid(sBase, (I - 1) * iSeparatorNo + 1, iSeparatorNo)
        Next I
        sBase = A
    End If
    '
    ' end
    sBaseNCode = sBase
    '
End Function

Public Function sBaseNDecode(psInput As String, piBase As Integer) As String
    '
    ' constants
    '
    ' declarations
    Dim sAlphabet() As String
    Dim iSeparatorNo As Integer, sSeparatorChar As String
    Dim sText As String, sBinary As String, sBase As String, sWork As String, iChunk As Integer
    Dim I As Integer, J As Integer, K As Integer, A As String
    '
    ' start
    '  params
    sBase = psInput
    With Worksheets(gksWS)
        A = .Range(gksAlphabet)
        iSeparatorNo = .Range(gksSeparatorNo)
        sSeparatorChar = .Range(gksSeparatorChar)
    End With
    '  alphabet
    ReDim sAlphabet(piBase)
    For I = 1 To piBase
        sAlphabet(I) = Mid(A, I, 1)
    Next I
    '  chunk
    iChunk = Round(Log(piBase) / Log(2), 0)
    '
    ' process
    '  unformat
    If iSeparatorNo <> 0 Then
        A = ""
        K = Int((Len(sBase) + iSeparatorNo - 1) / iSeparatorNo)
        J = Len(sSeparatorChar)
        For I = 1 To K
            A = A & Mid(sBase, (I - 1) * (iSeparatorNo + J) + 1, iSeparatorNo)
        Next I
        sBase = A
    End If
    '  build binary
    sBinary = ""
    For I = 1 To Len(sBase)
        A = Mid(sBase, I, 1)
        For J = 1 To piBase
            If sAlphabet(J) = A Then Exit For
        Next J
        K = J - 1
        A = ""
        For J = iChunk - 1 To 0 Step -1
            A = A & Sgn(K And 2 ^ J)
        Next J
        sBinary = sBinary & Left(A, iChunk)
    Next I
    sBinary = Left(sBinary, Int(Len(sBinary) / 8) * 8)
    '  build text
    sText = ""
    For I = 1 To Len(sBinary) Step 8
        A = Mid(sBinary, I, 8)
        K = 0
        For J = 7 To 0 Step -1
            K = K + Val(Mid(A, 8 - J, 1)) * 2 ^ J
        Next J
        sText = sText & Chr(K)
    Next I
    '
    ' end
    sBaseNDecode = sText
    '
End Function

It implements 2 functions:
=sBaseNCode(<input_string>,<base>)
=sBaseNDecode(<input_string>,<base>)

You should set the base set characters number (2-256), provide the alphabet to be used (at least of the base number), and the optional grouping characters and its separator.

Just advise if any issue.

Regards!
 

Attachments

  • How to Interpolate and_or Extrapolate alpha-numeric values. (for sumitbansal at chandoo.org).xlsm
    23.2 KB · Views: 10
Last edited:
SirJB7 said:
Hi, sumitbansal!

I found a long time ago developed Base64 module, with functions for coding and decoding. Give a look at the uploaded file.

It uses 4 named ranges:
BaseNCell: 2,4,8,16,32,64,128,256
AlphabetCell: string of at least BaseNCell characters
SeparatorNoCell: number of grouped characters (0 if no separators)
SeparatorCharCell: group separator string

This is the code:
Code: 
Option Explicit

' constants
Global Const gksWS = "Hoja1"
Global Const gksBaseN = "BaseNCell"
Global Const gksAlphabet = "AlphabetCell"
Global Const gksSeparatorNo = "SeparatorNoCell"
Global Const gksSeparatorChar = "SeparatorCharCell"
Global Const gksZero = "0"

Public Function sBaseNCode(psInput As String, piBase As Integer) As String
    '
    ' constants
    '
    ' declarations
    Dim sAlphabet() As String
    Dim iSeparatorNo As Integer, sSeparatorChar As String
    Dim sText As String, sBinary As String, sBase As String, iChunk As Integer
    Dim I As Integer, J As Integer, K As Integer, A As String
    '
    ' start
    '  params
    sText = psInput
    With Worksheets(gksWS)
        A = .Range(gksAlphabet)
        iSeparatorNo = .Range(gksSeparatorNo)
        sSeparatorChar = .Range(gksSeparatorChar)
    End With
    '  alphabet
    ReDim sAlphabet(piBase)
    For I = 1 To piBase
        sAlphabet(I) = Mid(A, I, 1)
    Next I
    '  chunk
    iChunk = Round(Log(piBase) / Log(2), 0)
    '
    ' process
    '  build binary
    sBinary = ""
    For I = 1 To Len(sText)
        A = ""
        K = Asc(Mid(sText, I, 1))
        For J = 7 To 0 Step -1
            A = A & Sgn(K And 2 ^ J)
        Next J
        sBinary = sBinary & A
    Next I
    K = (Len(sBinary) Mod iChunk)
    If K <> 0 Then sBinary = sBinary & String(iChunk - K, gksZero)
    '  chunk each N
    sBase = ""
    For I = 1 To Len(sBinary) Step iChunk
        A = String(8 - iChunk, gksZero) & Mid(sBinary, I, iChunk)
        K = 0
        For J = 7 To 0 Step -1
            K = K + Val(Mid(A, 8 - J, 1)) * 2 ^ J
        Next J
        sBase = sBase & sAlphabet(K + 1)
    Next I
    '  format
    If iSeparatorNo <> 0 Then
        A = ""
        K = Int((Len(sBase) + iSeparatorNo - 1) / iSeparatorNo)
        For I = 1 To K
            If I <> 1 Then A = A & sSeparatorChar
            A = A & Mid(sBase, (I - 1) * iSeparatorNo + 1, iSeparatorNo)
        Next I
        sBase = A
    End If
    '
    ' end
    sBaseNCode = sBase
    '
End Function

Public Function sBaseNDecode(psInput As String, piBase As Integer) As String
    '
    ' constants
    '
    ' declarations
    Dim sAlphabet() As String
    Dim iSeparatorNo As Integer, sSeparatorChar As String
    Dim sText As String, sBinary As String, sBase As String, sWork As String, iChunk As Integer
    Dim I As Integer, J As Integer, K As Integer, A As String
    '
    ' start
    '  params
    sBase = psInput
    With Worksheets(gksWS)
        A = .Range(gksAlphabet)
        iSeparatorNo = .Range(gksSeparatorNo)
        sSeparatorChar = .Range(gksSeparatorChar)
    End With
    '  alphabet
    ReDim sAlphabet(piBase)
    For I = 1 To piBase
        sAlphabet(I) = Mid(A, I, 1)
    Next I
    '  chunk
    iChunk = Round(Log(piBase) / Log(2), 0)
    '
    ' process
    '  unformat
    If iSeparatorNo <> 0 Then
        A = ""
        K = Int((Len(sBase) + iSeparatorNo - 1) / iSeparatorNo)
        J = Len(sSeparatorChar)
        For I = 1 To K
            A = A & Mid(sBase, (I - 1) * (iSeparatorNo + J) + 1, iSeparatorNo)
        Next I
        sBase = A
    End If
    '  build binary
    sBinary = ""
    For I = 1 To Len(sBase)
        A = Mid(sBase, I, 1)
        For J = 1 To piBase
            If sAlphabet(J) = A Then Exit For
        Next J
        K = J - 1
        A = ""
        For J = iChunk - 1 To 0 Step -1
            A = A & Sgn(K And 2 ^ J)
        Next J
        sBinary = sBinary & Left(A, iChunk)
    Next I
    sBinary = Left(sBinary, Int(Len(sBinary) / 8) * 8)
    '  build text
    sText = ""
    For I = 1 To Len(sBinary) Step 8
        A = Mid(sBinary, I, 8)
        K = 0
        For J = 7 To 0 Step -1
            K = K + Val(Mid(A, 8 - J, 1)) * 2 ^ J
        Next J
        sText = sText & Chr(K)
    Next I
    '
    ' end
    sBaseNDecode = sText
    '
End Function

It implements 2 functions:
=sBaseNCode(<input_string>,<base>)
=sBaseNDecode(<input_string>,<base>)

You should set the base set characters number (2-256), provide the alphabet to be used (at least of the base number), and the optional grouping characters and its separator.

Just advise if any issue.

Regards!
Click to expand...

Hi SirJB7,
This code is fantastic... It saved me from a lot of trouble. This base64 method is exactly what I was trying to achieve in the first place and I was totally ignorant of this. You enlightened me. Now, I don't have to go in the hassles of interpolation/extrapolation (which i still believe is a valid and good logic for generating and validating keys).
I am implementing this now in my program.
Thanks again,
Sumit.

P.S. I consider this solved.
 
Debraj said:
Hi Sumit,

I hope, you have already visited those site.. if not.. then have a look..

http://stackoverflow.com/questions/453030/how-can-i-create-a-product-key-for-my-c-sharp-app
http://www.serialkeymaker.com/how_to_implement_serial_key_maker.aspx
http://social.msdn.microsoft.com/Fo...-a-step-in-your-installer?forum=winformssetup

Still, I also want to have a look of those words or logic.. :)
interpolation and extrapolation
Click to expand...

Hi Debraj,
These links you provided gave me a better outlook and widened my horizon...
Thanks for that.

Sumit.
 
Sajan said:
Hi Sumit,
Can you define interpolation and extrapolation without using those words in the definitions? Then provide some examples of how you created such keys. (Don't omit any steps... what you might assume is clear to others is not clear to others at all.)

-Sajan.
Click to expand...
Hi Sajan
it took me some time, however, I just finished the steps that "to me" is interpolation. It was the basis of the macro that I wanted to make. I will be as basic as possible. Please bear with me.

Step 1
Open up a new Microsoft Excel 2010 spreadsheet.

Step 2
Click on cell "A1" and enter the first of the two values that you want to use for your interpolation( in this case it would be 3 - as stated in my example in the posts above).

Step 3
Scroll down a number of cells equal to the number of steps you want in your interpolation, then select the cell and enter the second of your two values (This would be 15). For example, if you want to interpolate the values for 30 keys, you would scroll down to cell A31 and enter your final value (which is 15).

Step 4
Click on cell "B1" and type in the following formula:
=(A31-A1)/(ROW(A31)-ROW(A1))
Press "Enter" to complete the formula.

Step 5
Select cell "A2," type in the following formula and press "Enter":
=A1+B$1

Step 6
Auto-complete the rest of the cells till A31 by dragging the formula for other cells. The step values for your interpolation will appear between the two numbers you entered.

Now, All the values that appear in the cells A2 to A30 are the interpolated keys of 3 and 15 with interpolation value as the same as in the cell B2 (which in this case would be 0.4).

This was quite simple as long as i use only numbers, however, if i try to use alpha-numeric values, this formula or method does not work.

Now, the difference between the two consecutive keys being 0.4 or its multiple would validate the keys in my case. Hence, it was also easy to validate keys for me untill now.

Now, suppose I release another version of my software and now I want to change the set of keys to new, then, also I will use another starting key at random - say 20 keeping the interpolated value same as 0.4. So, these new keys would be extrapolated keys of the original set of keys, yet with different start and end points (keys).

So, this was all I was looking for - with alpha-numeric values.
I hope, I was able to make it less confusing.

Thanks,
Sumit.
 
sumitbansal said:
...
This code is fantastic... It saved me from a lot of trouble. This base64 method is exactly what I was trying to achieve in the first place and I was totally ignorant of this. You enlightened me. Now, I don't have to go in the hassles of interpolation/extrapolation (which i still believe is a valid and good logic for generating and validating keys).
I am implementing this now in my program.
Click to expand...
sumitbansal said:
example may be something like this:
First Key: J61GQ-S49SX-1CGUU-WCFZQ
Last Key: AYQ7T-PF6T8-X4JY8-TNH1H
Click to expand...
Hi, submitbansal!

Base64: I don't think you should use base64 but base32 instead, since with the 1st method you'd get the plus and slash chars (+/) which you don't want, despite of the fact of the lowercase letters a-z. And if you either try a conversion to uppercase or follow my 1st advice of replacing them by A and zero, be aware the the decode function won't work.

I still believe is a valid and good logic: I'd pay for giving a look at a complete and detailed procedure (not necessarily code, otherwise you wouldn't have posted this here, so it might be written in plain English with the detailed specs) that interpolates and extrapolates between the upwards values you provided as sample. But I'd charge you the double if the method isn't bullet proof. Are you in?

Regards!
 
Last edited:
You must log in or register to reply here.
Back
Top