14 digit Hex to decimal in Excel
up vote
0
down vote
favorite
I have 14 digit Hexadecimal numbers in Excel which I want to convert to a decimal number.
For example in C2 the number is 0438E96A095180 and that has to be 1188475064373632 in decimal.
I have tried a module in VBA but that is not working:
' Force explicit declaration of variables
Option Explicit
' Convert hex to decimal
' In: Hex in string format
' Out: Double
Public Function HexadecimalToDecimal(HexValue As String) As Double
' If hex starts with 0x, replace it with &H to represent Hex that VBA will understand
Dim ModifiedHexValue As String
ModifiedHexValue = Replace(HexValue, "0x", "&H")
HexadecimalToDecimal = CDec(ModifiedHexValue)
End Function
With that I get the decimal number 1188475064373630 instead of 1188475064373632.
What am I doing wrong?
microsoft-excel vba hexadecimal
edited Nov 21 at 15:04
phuclv
8,83063788
asked Nov 21 at 9:53
Ben
11
add a comment |
up vote
0
down vote
favorite
I have 14 digit Hexadecimal numbers in Excel which I want to convert to a decimal number.
For example in C2 the number is 0438E96A095180 and that has to be 1188475064373632 in decimal.
I have tried a module in VBA but that is not working:
' Force explicit declaration of variables
Option Explicit
' Convert hex to decimal
' In: Hex in string format
' Out: Double
Public Function HexadecimalToDecimal(HexValue As String) As Double
' If hex starts with 0x, replace it with &H to represent Hex that VBA will understand
Dim ModifiedHexValue As String
ModifiedHexValue = Replace(HexValue, "0x", "&H")
HexadecimalToDecimal = CDec(ModifiedHexValue)
End Function
With that I get the decimal number 1188475064373630 instead of 1188475064373632.
What am I doing wrong?
microsoft-excel vba hexadecimal
edited Nov 21 at 15:04
phuclv
8,83063788
asked Nov 21 at 9:53
Ben
11
You might use theCIntfunction.
– harrymc
Nov 21 at 11:07
Thank you AFH, I have made the changes you suggested. Unfortunatly it still does not work. all the last numbers end with a 0 instead of the number it should be.
– Ben
Nov 21 at 11:24
Try to use thevariantdatatype instead ofDouble.
– harrymc
Nov 21 at 11:34
Excel only has 15 digit decimal precision, although VBA can have higher precision using the Decimal datatype. To return a value with greater than 15 digits, you will need to return a string. eg:HexadecimalToDecimal = CStr(CDec(ModifiedHexValue)). And you may need to ensure a starting&Hin all cases.
– Ron Rosenfeld
Nov 21 at 12:10
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have 14 digit Hexadecimal numbers in Excel which I want to convert to a decimal number.
For example in C2 the number is 0438E96A095180 and that has to be 1188475064373632 in decimal.
I have tried a module in VBA but that is not working:
' Force explicit declaration of variables
Option Explicit
' Convert hex to decimal
' In: Hex in string format
' Out: Double
Public Function HexadecimalToDecimal(HexValue As String) As Double
' If hex starts with 0x, replace it with &H to represent Hex that VBA will understand
Dim ModifiedHexValue As String
ModifiedHexValue = Replace(HexValue, "0x", "&H")
HexadecimalToDecimal = CDec(ModifiedHexValue)
End Function
With that I get the decimal number 1188475064373630 instead of 1188475064373632.
What am I doing wrong?
microsoft-excel vba hexadecimal
edited Nov 21 at 15:04
phuclv
8,83063788
asked Nov 21 at 9:53
Ben
11
I have 14 digit Hexadecimal numbers in Excel which I want to convert to a decimal number.
For example in C2 the number is 0438E96A095180 and that has to be 1188475064373632 in decimal.
I have tried a module in VBA but that is not working:
' Force explicit declaration of variables
Option Explicit
' Convert hex to decimal
' In: Hex in string format
' Out: Double
Public Function HexadecimalToDecimal(HexValue As String) As Double
' If hex starts with 0x, replace it with &H to represent Hex that VBA will understand
Dim ModifiedHexValue As String
ModifiedHexValue = Replace(HexValue, "0x", "&H")
HexadecimalToDecimal = CDec(ModifiedHexValue)
End Function
With that I get the decimal number 1188475064373630 instead of 1188475064373632.
What am I doing wrong?
microsoft-excel vba hexadecimal
microsoft-excel vba hexadecimal
edited Nov 21 at 15:04
phuclv
8,83063788
asked Nov 21 at 9:53
Ben
11
edited Nov 21 at 15:04
phuclv
8,83063788
asked Nov 21 at 9:53
Ben
11
edited Nov 21 at 15:04
phuclv
8,83063788
edited Nov 21 at 15:04
phuclv
8,83063788
edited Nov 21 at 15:04
phuclv
8,83063788
8,83063788
asked Nov 21 at 9:53
Ben
11
asked Nov 21 at 9:53
Ben
11
asked Nov 21 at 9:53
Ben
11
11
You might use theCIntfunction.
– harrymc
Nov 21 at 11:07
Thank you AFH, I have made the changes you suggested. Unfortunatly it still does not work. all the last numbers end with a 0 instead of the number it should be.
– Ben
Nov 21 at 11:24
Try to use thevariantdatatype instead ofDouble.
– harrymc
Nov 21 at 11:34
Excel only has 15 digit decimal precision, although VBA can have higher precision using the Decimal datatype. To return a value with greater than 15 digits, you will need to return a string. eg:HexadecimalToDecimal = CStr(CDec(ModifiedHexValue)). And you may need to ensure a starting&Hin all cases.
– Ron Rosenfeld
Nov 21 at 12:10
add a comment |
You might use theCIntfunction.
– harrymc
Nov 21 at 11:07
Thank you AFH, I have made the changes you suggested. Unfortunatly it still does not work. all the last numbers end with a 0 instead of the number it should be.
– Ben
Nov 21 at 11:24
Try to use thevariantdatatype instead ofDouble.
– harrymc
Nov 21 at 11:34
Excel only has 15 digit decimal precision, although VBA can have higher precision using the Decimal datatype. To return a value with greater than 15 digits, you will need to return a string. eg:HexadecimalToDecimal = CStr(CDec(ModifiedHexValue)). And you may need to ensure a starting&Hin all cases.
– Ron Rosenfeld
Nov 21 at 12:10
You might use the
CInt function.– harrymc
Nov 21 at 11:07
You might use the
CInt function.– harrymc
Nov 21 at 11:07
Thank you AFH, I have made the changes you suggested. Unfortunatly it still does not work. all the last numbers end with a 0 instead of the number it should be.
– Ben
Nov 21 at 11:24
Thank you AFH, I have made the changes you suggested. Unfortunatly it still does not work. all the last numbers end with a 0 instead of the number it should be.
– Ben
Nov 21 at 11:24
Try to use the
variant datatype instead of Double.– harrymc
Nov 21 at 11:34
Try to use the
variant datatype instead of Double.– harrymc
Nov 21 at 11:34
Excel only has 15 digit decimal precision, although VBA can have higher precision using the Decimal datatype. To return a value with greater than 15 digits, you will need to return a string. eg:
HexadecimalToDecimal = CStr(CDec(ModifiedHexValue)). And you may need to ensure a starting &H in all cases.– Ron Rosenfeld
Nov 21 at 12:10
Excel only has 15 digit decimal precision, although VBA can have higher precision using the Decimal datatype. To return a value with greater than 15 digits, you will need to return a string. eg:
HexadecimalToDecimal = CStr(CDec(ModifiedHexValue)). And you may need to ensure a starting &H in all cases.– Ron Rosenfeld
Nov 21 at 12:10
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
You will need to return the value as a string, at least where the result has more than 15 digit precision.
eg:
Option Explicit
' Convert hex to decimal
' In: Hex in string format
' Out: Decimal in string format
Public Function HexadecimalToDecimal(HexValue As String) As String
' If hex starts with 0x, remove it to represent Hex that VBA will understand
Dim ModifiedHexValue As String
ModifiedHexValue = "&H" & Replace(HexValue, "0x", "")
HexadecimalToDecimal = CDec(ModifiedHexValue)
End Function
I will leave it to you to test the length and decide if you want to return a string or a number; and you will note that I modified your &H adding routine a bit.
answered Nov 21 at 12:19
Ron Rosenfeld
1,9002610
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
You will need to return the value as a string, at least where the result has more than 15 digit precision.
eg:
Option Explicit
' Convert hex to decimal
' In: Hex in string format
' Out: Decimal in string format
Public Function HexadecimalToDecimal(HexValue As String) As String
' If hex starts with 0x, remove it to represent Hex that VBA will understand
Dim ModifiedHexValue As String
ModifiedHexValue = "&H" & Replace(HexValue, "0x", "")
HexadecimalToDecimal = CDec(ModifiedHexValue)
End Function
I will leave it to you to test the length and decide if you want to return a string or a number; and you will note that I modified your &H adding routine a bit.
answered Nov 21 at 12:19
Ron Rosenfeld
1,9002610
add a comment |
up vote
1
down vote
You will need to return the value as a string, at least where the result has more than 15 digit precision.
eg:
Option Explicit
' Convert hex to decimal
' In: Hex in string format
' Out: Decimal in string format
Public Function HexadecimalToDecimal(HexValue As String) As String
' If hex starts with 0x, remove it to represent Hex that VBA will understand
Dim ModifiedHexValue As String
ModifiedHexValue = "&H" & Replace(HexValue, "0x", "")
HexadecimalToDecimal = CDec(ModifiedHexValue)
End Function
I will leave it to you to test the length and decide if you want to return a string or a number; and you will note that I modified your &H adding routine a bit.
answered Nov 21 at 12:19
Ron Rosenfeld
1,9002610
add a comment |
up vote
1
down vote
up vote
1
down vote
You will need to return the value as a string, at least where the result has more than 15 digit precision.
eg:
Option Explicit
' Convert hex to decimal
' In: Hex in string format
' Out: Decimal in string format
Public Function HexadecimalToDecimal(HexValue As String) As String
' If hex starts with 0x, remove it to represent Hex that VBA will understand
Dim ModifiedHexValue As String
ModifiedHexValue = "&H" & Replace(HexValue, "0x", "")
HexadecimalToDecimal = CDec(ModifiedHexValue)
End Function
I will leave it to you to test the length and decide if you want to return a string or a number; and you will note that I modified your &H adding routine a bit.
answered Nov 21 at 12:19
Ron Rosenfeld
1,9002610
You will need to return the value as a string, at least where the result has more than 15 digit precision.
eg:
Option Explicit
' Convert hex to decimal
' In: Hex in string format
' Out: Decimal in string format
Public Function HexadecimalToDecimal(HexValue As String) As String
' If hex starts with 0x, remove it to represent Hex that VBA will understand
Dim ModifiedHexValue As String
ModifiedHexValue = "&H" & Replace(HexValue, "0x", "")
HexadecimalToDecimal = CDec(ModifiedHexValue)
End Function
I will leave it to you to test the length and decide if you want to return a string or a number; and you will note that I modified your &H adding routine a bit.
answered Nov 21 at 12:19
Ron Rosenfeld
1,9002610
answered Nov 21 at 12:19
Ron Rosenfeld
1,9002610
answered Nov 21 at 12:19
Ron Rosenfeld
1,9002610
answered Nov 21 at 12:19
Ron Rosenfeld
1,9002610
1,9002610
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1377228%2f14-digit-hex-to-decimal-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You might use the
CIntfunction.– harrymc
Nov 21 at 11:07
Thank you AFH, I have made the changes you suggested. Unfortunatly it still does not work. all the last numbers end with a 0 instead of the number it should be.
– Ben
Nov 21 at 11:24
Try to use the
variantdatatype instead ofDouble.– harrymc
Nov 21 at 11:34
Excel only has 15 digit decimal precision, although VBA can have higher precision using the Decimal datatype. To return a value with greater than 15 digits, you will need to return a string. eg:
HexadecimalToDecimal = CStr(CDec(ModifiedHexValue)). And you may need to ensure a starting&Hin all cases.– Ron Rosenfeld
Nov 21 at 12:10