VBA code to convert hex to string

Based on the data submitted, please post a sample of the expected output.

Please see attached the expected output.
expectedoutput.xls

See here for a function that will do tghis:
http://bytes.com/topic/visual-basic-net/answers/381413-how-convert-hex-string

Thank you for the link.  I am copying and pasting but certain portions of the code lines in my VB window appear in red...................????

Public Function ToHexString(ByVal sText As String) As String
Dim arrBytes As Integer() = CharsToBytes(sText)
Dim sb As StringBuilder = New StringBuilder
For i As Integer = 0 To arrBytes.Length - 1
sb.Append(String.Format("{0:x2}", Hex(arrBytes(i))))
Next
Return sb.ToString()
End Function
Everthing is in red in the vba window except for Public function...,Next, and end function........

Boag2000
I've tried several examples from the link, but certain portions of the code get highlighted in red.  I am not good at VB and so do not know what is going on.............sorry.  Perhaps you can help me............?

checking now...

Thank you very much!  I will follow up with you tomorrow.  10:08PM here in the U.S.......

First if you were able to get all the values in ExpectedOutput.xls, then why not use whatever method you used to create that???

Sorry, the code in the link was for vb.net

Researching VBA/VB6 functions now.

Hi Boag2000:
I got this code from bytes.com.  I do not get an error when I use the code as a function called in my query.  However, I do not see any string or text either....
Sanj

Public Function Converthex(strHexString As String) As String
Dim intLenOfString As Integer
Dim intCounter As Integer
Dim strBuild As String
  
'Hex String must have a valid length, and it must be an even length
If Len(strHexString) = 0 Or Len(strHexString) Mod 2 <> 0 Then Exit Function
  
intLenOfString = Len(strHexString)
  
For intCounter = 1 To Len(strHexString)
  If intCounter Mod 2 <> 0 Then     'need Hex pairs
    'Retrieve the Value of the Hex Pair, then Convert to a Character,
    'then Append to a Base String
    strBuild = strBuild & Chr$(Val("&H" & Mid$(strHexString, intCounter, 2)))
  End If
Next
 
End Function

Select allOpen in new window

Hi Boag2000:
So I added an extra code line called Converthex = strBuild.  It seems to be working.  I realized I have an additional problem.  Some of the hex text length are 255 characters (an odd number that cannot be converted to hex because conversion occurs in even pairs correct?).  So what I want to do is modify the attached code to first check to see if the hex integer length is 255.  If it is 255, I want to drop the first two characters and the last character.  Then, I want to perform the conversion.....Could you help me with this modification?
sanj
 

Public Function Converthex(strHexString As String) As String
Dim intLenOfString As Integer
Dim intCounter As Integer
Dim strBuild As String
  
'Hex String must have a valid length, and it must be an even length
If Len(strHexString) = 0 Or Len(strHexString) Mod 2 <> 0 Then Exit Function
  
intLenOfString = Len(strHexString)
  
For intCounter = 1 To Len(strHexString)
  If intCounter Mod 2 <> 0 Then     'need Hex pairs
    'Retrieve the Value of the Hex Pair, then Convert to a Character,
    'then Append to a Base String
    strBuild = strBuild & Chr$(Val("&H" & Mid$(strHexString, intCounter, 2)))
    Converthex = strBuild
  End If
Next
 
End Function

Select allOpen in new window

Thanks for your time.

;-)

Thanks for an interesting and thought provoking question