Addition in Base 5

A

ANWAR ALAM

New Member
Joined
Mar 26, 2021
Messages
18
Office Version
  1. 2010
Platform
  1. Windows
hi, how can we add 2 to 5 numbers(which are in base 5 and are written inside textboxes on Userform). What can be the possible VBA code behind a command button taken on the same Userform. i am using Excel 2010 ver. Any help in this regard will highly be appreciated. Thanks in anticipation,,,,,,,best regards,,,,,Anwar
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
And there it is, the what if Base 5 was the desired question?

I will give you some code that I have found that should get you started. The Code is actually from @Rick Rothstein by the way.

Two UDF's that you can put into a module to allow you to work with any Base up to 36.

VBA Code: 
Function Dec2Base(DecimalValue As Variant, Base As Long) As String
  Const PossibleDigits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  DecimalValue = CDec(DecimalValue)
  Do Until DecimalValue = 0
    Dec2Base = Mid(PossibleDigits, CDec(DecimalValue) - Base * _
               Int(DecimalValue / Base) + 1, 1) & Dec2Base
    DecimalValue = Int(CDec(DecimalValue) / Base)
  Loop
End Function



Function Base2Dec(BaseDigits As String, BaseNumber As Long) As Variant
    Dim X As Long, Z As Long, DigitVal As Variant, Power As Variant
    Const PossibleDigits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    If Not UCase(BaseDigits) Like "*[!" & Left(PossibleDigits, BaseNumber) & "]*" Then
        For X = 0 To Len(BaseDigits) - 1
            DigitVal = UCase$(Mid(BaseDigits, Len(BaseDigits) - X, 1))
            DigitVal = InStr(PossibleDigits, DigitVal) - 1
            Power = 1
            For Z = 1 To X
              Power = CDec(Power) * BaseNumber
            Next
            Base2Dec = Base2Dec + DigitVal * Power
        Next
    Else
        Err.Raise 9999, , "Bad base digit specified"
    End If
End Function

With that you could you could perform the following examples:

VBA Code: 
MsgBox Dec2Base(Base2Dec("1", 5) + Base2Dec("4", 5) + Base2Dec("0", 5),5)    '  = 10 in Base 5

Or a cell value could be set to:
VBA Code: 
=Dec2Base(Base2Dec("1", 5) + Base2Dec("4", 5) + Base2Dec("0", 5),5)    '  = 10 in Base 5
 
Upvote 0
Solution
johnnyL said:
johnnyL said:
And there it is, the what if Base 5 was the desired question?

I will give you some code that I have found that should get you started. The Code is actually from @Rick Rothstein by the way.

Two UDF's that you can put into a module to allow you to work with any Base up to 36.

VBA Code: 
Function Dec2Base(DecimalValue As Variant, Base As Long) As String
  Const PossibleDigits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  DecimalValue = CDec(DecimalValue)
  Do Until DecimalValue = 0
    Dec2Base = Mid(PossibleDigits, CDec(DecimalValue) - Base * _
               Int(DecimalValue / Base) + 1, 1) & Dec2Base
    DecimalValue = Int(CDec(DecimalValue) / Base)
  Loop
End Function



Function Base2Dec(BaseDigits As String, BaseNumber As Long) As Variant
    Dim X As Long, Z As Long, DigitVal As Variant, Power As Variant
    Const PossibleDigits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    If Not UCase(BaseDigits) Like "*[!" & Left(PossibleDigits, BaseNumber) & "]*" Then
        For X = 0 To Len(BaseDigits) - 1
            DigitVal = UCase$(Mid(BaseDigits, Len(BaseDigits) - X, 1))
            DigitVal = InStr(PossibleDigits, DigitVal) - 1
            Power = 1
            For Z = 1 To X
              Power = CDec(Power) * BaseNumber
            Next
            Base2Dec = Base2Dec + DigitVal * Power
        Next
    Else
        Err.Raise 9999, , "Bad base digit specified"
    End If
End Function

With that you could you could perform the following examples:

VBA Code: 
MsgBox Dec2Base(Base2Dec("1", 5) + Base2Dec("4", 5) + Base2Dec("0", 5),5)    '  = 10 in Base 5

Or a cell value could be set to:
VBA Code: 
=Dec2Base(Base2Dec("1", 5) + Base2Dec("4", 5) + Base2Dec("0", 5),5)    '  = 10 in Base 5
Click to expand...

And there it is, the what if Base 5 was the desired question?

I will give you some code that I have found that should get you started. The Code is actually from @Rick Rothstein by the way.

Two UDF's that you can put into a module to allow you to work with any Base up to 36.

VBA Code: 
Function Dec2Base(DecimalValue As Variant, Base As Long) As String
  Const PossibleDigits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  DecimalValue = CDec(DecimalValue)
  Do Until DecimalValue = 0
    Dec2Base = Mid(PossibleDigits, CDec(DecimalValue) - Base * _
               Int(DecimalValue / Base) + 1, 1) & Dec2Base
    DecimalValue = Int(CDec(DecimalValue) / Base)
  Loop
End Function



Function Base2Dec(BaseDigits As String, BaseNumber As Long) As Variant
    Dim X As Long, Z As Long, DigitVal As Variant, Power As Variant
    Const PossibleDigits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    If Not UCase(BaseDigits) Like "*[!" & Left(PossibleDigits, BaseNumber) & "]*" Then
        For X = 0 To Len(BaseDigits) - 1
            DigitVal = UCase$(Mid(BaseDigits, Len(BaseDigits) - X, 1))
            DigitVal = InStr(PossibleDigits, DigitVal) - 1
            Power = 1
            For Z = 1 To X
              Power = CDec(Power) * BaseNumber
            Next
            Base2Dec = Base2Dec + DigitVal * Power
        Next
    Else
        Err.Raise 9999, , "Bad base digit specified"
    End If
End Function

With that you could you could perform the following examples:

VBA Code: 
MsgBox Dec2Base(Base2Dec("1", 5) + Base2Dec("4", 5) + Base2Dec("0", 5),5)    '  = 10 in Base 5

Or a cell value could be set to:
VBA Code: 
=Dec2Base(Base2Dec("1", 5) + Base2Dec("4", 5) + Base2Dec("0", 5),5)    '  = 10 in Base 5
Click to expand...
PROMPTLY to send you my good wishes and lots of thanks and warm salutation and .... ....... ...... .........i m really really grateful to you and Rick Rothstein both. You are great people. i salute you.
 
Upvote 0
ANWAR ALAM said:
PROMPTLY to send you my good wishes and lots of thanks and warm salutation and .... ....... ...... .........i m really really grateful to you and Rick Rothstein both. You are great people. i salute you.
Click to expand...
I'll take minimal credit for paying forward what I have found in my previous research. The credit for the code to handle all the Bases that you probably would never need in your lifetime goes to @Rick Rothstein. I'm sure @Rick Rothstein, or someone else for that matter, could shorten the code up to be more specific for handling Base 5 only.

Glad it worked for you.
 
Upvote 0
Here is some more code that I came up with from previous searches...
VBA Code: 
  Const PossibleDigits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
' By Rick Rothstein
'
Function Dec2Base(DecimalValue As Variant, Base As Long) As String              ' 7 lines of code
'
    DecimalValue = CDec(DecimalValue)
'
    Do Until DecimalValue = 0
        Dec2Base = Mid(PossibleDigits, CDec(DecimalValue) - Base * Int(DecimalValue / Base) + 1, 1) & Dec2Base
        DecimalValue = Int(CDec(DecimalValue) / Base)
    Loop
End Function
'
'--------------------------------------------------------------------------------------------
'
' Code inspired By Ruddles
'
Public Function ConvertFromBase(ByVal aValue As Variant, ByVal Base As Integer) As Variant     ' 13 lines of code
'
    Dim temp As Variant
'
    ConvertFromBase = CVErr(xlErrValue)
'
    If Base < 2 Or Base > 36 Then Exit Function
    If Base <> Int(Base) Then Exit Function
'
    aValue = UCase(aValue)
'
    ConvertFromBase = 0
'
    Do Until Len(aValue) = 0
        temp = Left(aValue, 1)
        aValue = Mid(aValue, 2)
        ConvertFromBase = ConvertFromBase * Base + (InStr(PossibleDigits, temp) - 1)
    Loop
End Function

VBA Code: 
msgbox Dec2Base(convertfrombase(1, 5) + convertfrombase(4, 5) + convertfrombase(0, 5),5)

Or a cell could be set to:
VBA Code: 
= Dec2Base(convertfrombase(1, 5) + convertfrombase(4, 5) + convertfrombase(0, 5),5)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
ComboBox and TextBox
Replies
0
Views
161
Cr4ZyPP
C
S
  • Question
how to enter a value in a specific cell based on column and row headers
Replies
4
Views
115
Shazzj001
S
P
  • Question
Userform Listbox Edit Rows Within Sheet
Replies
1
Views
436
pure vito
P
W
  • Locked
  • Question
additional pop up msgbox dependent on specific text entered into userform combobox, and other questions
Replies
2
Views
261
Peter_SSs
Peter_SSs
P
  • Solved
Userform textbox populated with listbox data how to edit
Replies
6
Views
506
pure vito
P

Forum statistics

Threads
1,214,979
Messages
6,122,550
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top