VBA Excel ascii to hex

Question

I am having trouble with ascii to hex conversion with VBA. I need to convert a String, which combines letters and numbers to hex. However my code converts only about half of the string.

I have in A1 following Sting: BFEBFBFF000406E3

The code I have fills in A6: 42464542464246400000000000000000

I am using http://www.convertstring.com/cs/EncodeDecode/HexDecode to check if it is ok and there it translates as: 42464542464246463030303430364533DA

What do I have wrong please?

Sub strg()

Dim strg As String
Dim tmp As String

strg = Worksheets("List1").Range("A1")
Worksheets("List1").Range("A5").Value = strg

tmp = ""
For I = 1 To Len(strg)
  tmp = tmp & hex((Asc(Mid(strg, I, 1))))
Next


Worksheets("List1").Range("A6").Value = tmp

End Sub

Thank you for any help.

Shouldn't the For Next loop have a step 2 so that it reads 2 characters at a time? — MiguelH
– MiguelH, Commented Mar 26, 2018 at 14:59
Looks like the string is converted to a Double which truncates the number to 15 digits. Add a quote in front: .Value = Chr(39) & tmp — Florent B.
– Florent B., Commented Mar 26, 2018 at 15:40
or put Worksheets("List1").Range("A6").NumberFormat = "@" before assigne tmp to the cell. — Scott Craner
– Scott Craner, Commented Mar 26, 2018 at 15:44
I think you are referring to the HexEncode page. And when I run it, the result does not include the DA at the end. And, since tmp contains the correct result, either of the two options above to write the result as text to the cell should work. — Ron Rosenfeld
– Ron Rosenfeld, Commented Mar 26, 2018 at 15:55
DA in hex is 13 (ASCII carriage return) and 10 (ASCII line feed), so you're picking up the end of line marker from wherever you copied the string that you pasted into that web page. — Patrick Wynne
– Patrick Wynne, Commented Mar 26, 2018 at 16:56

Fuca · Accepted Answer · 2018-03-27 07:19:58Z

1

Worksheets("List1").Range("A6").NumberFormat = "@" & tmp works prefectly, thank you Scott Craner

answered Mar 27, 2018 at 7:19

Fuca

351 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Excel ascii to hex

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related