# Design Help Required For: ConvertNumberToDefinition Function (1 Viewer)

#### BlueIshDan

##### &#9760;
Hello There,

Sometime in the past year, I posted a function that converted non-decimal based string values. Like HEX "0123456789ABCDEF" and/or any other numbering definition you can think of.

I have recently come across another application that requires a custom format of numbering. Bringing me to revise the old function with the new, containing a formula that I wrote out.

The Revised function:

Code:
``````[COLOR="SeaGreen"]'---------------------------------------------------------------------------------------------------'
'|
'| Date Created: Feb, 27 2015
'| BY: Daniel Couillard
'| Written In: Microsoft Visual Basic for Applications (VBA) 7.0
'|
'| Revisions:
'|
'| Date Revised: Dec, 21 2015
'| BY: Daniel Couillard
'| Revision:
'|  - Created Mathmatical formula to shorten code.
'|  - Re-commented with respect to the changes.
'|
'| .-------------------------------------.
'| | Function  -  ConvertStringToDecimal |
'| `-------------------------------------`
'|
'|  PARAMETERS:
'|
'|       * str - Contains the value to be converted to decimal.
'|
'|       * def - Contains the definition that describes the value's
'|               format.
'|
'|               For example, HEX's definition would be: "0123456789ABCDEF"
'|
'|  RETURN:
'|
'|      * variant - _
'|
'|          The return value is a variant because it can contain one of the two following results:
'|
'|              1. (STRING) Error Message.
'|              2. (DECIMAL) Calculated Value.
'|                 We use the Decimal data type because of its abillity to store such a large number.
'|
'|
'|  NOTE:
'|
'|      FURTHER EXPLAINATION CAN BE FOUND WITHIN THE FUNCTION
'|
'---------------------------------------------------------------------------------------------------'[/COLOR]

Public Function ConvertStringToDecimal(ByVal str As String, _
ByVal def As String) As Variant

[COLOR="seagreen"]    ' ------------------------------------- '
'           Parse Parameters.           '
' ------------------------------------- '[/COLOR]
If Len(Trim(str) & vbNullString) = 0 Then: ConvertStringToDecimal = "No value has been entered.": Exit Function
If Len(Trim(def) & vbNullString) < 2 Then: ConvertStringToDecimal = "Number definition must have 2 or more characters.": Exit Function
[COLOR="SeaGreen"]    ' ------------------------------------- '[/COLOR]

[COLOR="seagreen"]    ' ------------------------------------- '
' Variable Declaration & Initialization '
' ------------------------------------- '[/COLOR]
Dim LD As Integer: LD = Len(def)        [COLOR="seagreen"]' - Length of Definition[/COLOR]
Dim LV As Integer: LV = Len(str)        [COLOR="seagreen"]' - Length of Value[/COLOR]
Dim N As Variant: N = CDec(0)           [COLOR="seagreen"]' - The sum of the calculations.[/COLOR]
Dim I As Integer                        [COLOR="seagreen"]' - Increment.[/COLOR]
Dim P As Integer                        [COLOR="seagreen"]' - Position of Value[i] in Reverse Definition.[/COLOR]
[COLOR="seagreen"]    ' ------------------------------------- '[/COLOR]

[COLOR="SeaGreen"]    ' Reverse string for correct digit position calculation allows for
' minimization in calculations when traversing through string position
' & Or simplifies visualiztion of the code for the reader.[/COLOR]
str = ReverseString(str)

[COLOR="seagreen"]    ' Traverse through the characters(digits) of the string in
' an incremental order.[/COLOR]
For I = 1 To LV

P = InStr(1, def, Mid(str, I, 1))

If P > 0 Then: N = N + CDec((P - 1) * (LD ^ (I - 1)))

Next

[COLOR="seagreen"]    ' Return the calculated value.[/COLOR]
ConvertStringToDecimal = N

End Function``````

What I would like to request your help with is the design of a Convert decimal to number definition function. I have a working one, but I can't seem to figure out if it is the most effective way to go about doing it.

This function will retrieves a decimal based number and converts it to a definition string:

number: "12345"
def: (hex) "0123456789ABCDEF"
or (binary) "01"
or (custom) "!@#\$%^&*()1234567890ABCDEFGHIJKLMNOP"

Here she is:

Code:
``````Public Function ConvertNumberToDefinition(ByVal number As String, _
ByVal def As String) As String

Dim length_of_def As Integer: length_of_def = Len(def)
Dim dec_number As Variant: dec_number = CDec(number)

Dim I As Variant: I = CDec(0)
Dim j As Variant: j = CDec(0)

Dim val As String
Dim temp As Variant: temp = CDec(0)
Dim index_value As Variant: index_value = CDec(0)

[COLOR="Red"]    For I = 0 To dec_number

If (length_of_def ^ I > dec_number) Then[/COLOR]

For j = I - 1 To 0 Step -1

index_value = length_of_def ^ j

temp = dec_number Mod index_value

val = val & Mid(def, ((dec_number - temp) / index_value) + 1, 1)

dec_number = temp

Next

Exit For
End If

Next

ConvertNumberToDefinition = val

End Function``````

Thanks,
Dan

Last edited:

#### arnelgp

i am confused with custom definition. how do you define it?
dec is base10
oct is base8
hex is base16
binary is base2

while the above common bases can be derived from Excel using worksheet function.

#### BlueIshDan

##### &#9760;
This is for all common, and custom base numbering systems.
A numbering system can be define by placing each of its characters into a string in order from value 0 to Max.

"01" Binary
"01234567" Octal
"0123456789" Dec
"0123456789ABCDEF" Hex

"!@#\$%^&*()ABCDEFGHIJKLMNOP0123456789" Custom

#### BlueIshDan

##### &#9760;
Its more or less defining a custom set of characters that hold values with respect to their position in the definition. Which follows the same design as what we use today, its just not commonly thought of in that way. 1 is a character and can contain any value that we think of it containing. Just as Ö is a character that can contain any value. Example

"9876543210" The character 9's value would be 0 if it was the first character in a numbering definition.

"ABCDÖEFGH!" The character Ö's value would be 4, ÖG would be 48 if I'm not wrong.

Hope this helps at all! lol

The reason I am created a function is because I will be working with a lot of custom definitions.

Last edited:

#### arnelgp

then you must search for cryptography or something. since the base is unknown.

#### BlueIshDan

##### &#9760;
I have it working, I just was wondering if anyone knew of a formula that would be as minimized as the first function's formula. Rather than looping to the highest known factor of the definition length.

Replies
5
Views
62
Replies
6
Views
155
Replies
19
Views
248
Replies
21
Views
196
Replies
36
Views
314