Convert bytes to string

[YasserKhalil]

Hello everyone
I am trying to convert bytes to string and this is my try

Code: Select all

    Dim en As ASCIIEncoding, myArr() As Byte
    Set en = New ASCIIEncoding
    myArr = Array(89, 97, 115, 115, 101, 114)
'    myArr(0) = 89
'    myArr(1) = 97
'    myArr(2) = 115
'    myArr(3) = 115
'    myArr(4) = 101
'    myArr(5) = 114
    Debug.Print en.GetString(myArr)

But I got type mismatch error. How can I fix it?

[HansV]

Which Library do we need for ASCIIEncoding? It is not standard VBA.

[LisaGreen]

HAns...

From a google search....
https://stackoverflow.com/questions/369 ... ding-ascii

It mentions a library... mscorlib

[HansV]

That's fine, but Yasser should have provided that information instead of leaving all the work to us.

[LisaGreen]

Agree.

Yasser!!! Don't be so Lazy!!!!!

[YasserKhalil]

Why do you mention I am lazy ..? I have tried the code I posted in the thread and I am stuck at using the array as Byte not as Variant
I already could solve it using loops but needs to avoid loops if possible (Need to see if there is an alternative approach)

Code: Select all

Sub Demo()
    Dim v, en As ASCIIEncoding, myArr() As Byte, i As Long
    Set en = New ASCIIEncoding
    v = Array(89, 97, 115, 115, 101, 114)
    ReDim myArr(0 To UBound(v))
    For i = LBound(v) To UBound(v)
        myArr(i) = v(i)
    Next i
    Debug.Print en.GetString(myArr)
End Sub

[HansV]

The code as posted won't compile since ASCIIEncoding is not a built-in VBA object. We should set a reference in Tools > References... but you haven't told us which one.

[YasserKhalil]

The reference is mscorlib.dll

[HansV]

What is its name in Tools > References...?

[YasserKhalil]

I remembered I have used Browse for it and select the dll file from the Windows folder

[HansV]

That doesn't work for me. I get "ActiveX component can't create object".

[YasserKhalil]

I am using Windows 64 Bit and office 365 32 Bit. I am not sure what's wrong on your side.

[HansV]

The code from LisaGreen's link doesn't work for me either, so I cannot help you with this.

[Doc.AElstein]

Hello,
The macro in post #6 ( Yasser’s second post ) works for me on computers with Office 2003, 2007 and 2010, if I
_ Early Binding:- get the reference to that mscorlib thing one way or another. ( I think it is some sort of systems collection thing. So its just some sort of simple List I guess, wrapped up in confusing computer terminology )
_ Late binding:- works for me with CreateObject("System.Text.ASCIIEncoding")

_._______________________-


Regarding the question… I am not sure if we are talking here about
_a) converting ASCII number things to a string , ( possibly without looping )
or
_b) getting a 1 dimensional array filled , that has Elements of the Byte type in some way other than looping.

Maybe in the meantime it’s a bit of both:
_b)
That .GetString( ) thing seems to want a 1 dimensional array in that ( ) whose Elements have the Byte type. It doesn’t seem happy with a 1 D array of other types.
It won’t even accept a 1 D array of Variant types that have Byte types in it. What I mean is that this won’t work either:

Code: Select all

Sub BytesInVariant()  '  http://www.eileenslounge.com/viewtopic.php?f=30&t=35600
Dim EnKrouptASCII As ASCIIEncoding: Set EnKrouptASCII = New ASCIIEncoding
Dim myArr() As Variant
Dim a As Byte, b As Byte, c As Byte, d As Byte, e As Byte, f As Byte
 Let a = 89: b = 97: c = 115: d = c: e = 101: f = 114
 Let myArr() = Array(a, b, c, d, e, f)   '    BytesInVariant.jpg    https://imgur.com/yOcQVTr   https://i.imgur.com/yOcQVTr.jpg
On Error GoTo Bed
 Debug.Print EnKrouptASCII.GetString(myArr())
Exit Sub
Bed:         '    BytesInVariantError.JPG    https://imgur.com/D04Iw8j    https://i.imgur.com/D04Iw8j.jpg
MsgBox prompt:=Err.Number & vbCr & vbLf & Err.Description: Debug.Print Err.Number & vbCr & vbLf & Err.Description
End Sub

Coming back to _a)
I can’t think of any simple way to do that without looping. Maybe I could if there was a list somewhere of ASCII characters in an array, which was accessible via VBA. Maybe there is one I don’t know about?
If I make a list in a worksheet, looking something like this.._
ChrWinColumnListRow.JPG : https://imgur.com/Ie1VxXc https://i.imgur.com/Ie1VxXc.jpg http://i.imgur.com/Ie1VxXc.jpg

ChrWinColumnListRow.JPG

( see worksheet “Oct2020” in file ArabicChrs.xls : https://app.box.com/s/pgpux44mjw3haqvcli0cxf21fz4dt7ow and also in uploaded file )
_.. then I can do a Index one liner to get a string from a Variant type array like Array(89, 97, 115, 115, 101, 114)

_b)
Working backwards as it were, I can get my Byte array using something that seems to ba available in that ASCIIEncoding stuff
.GetBytes_4()

_.__
Here a demo of _a) and _b) :-

Code: Select all

Sub KroutchoMarx()  '  
' Late Binding                https://stackoverflow.com/questions/36927304/access-2007-vba-system-text-encoding-ascii
' Dim EnKrouptASCII As Object: Set EnKrouptASCII = CreateObject("System.Text.ASCIIEncoding")

' Early binding - you need                    https://i.imgur.com/UeCHRWl.jpg
' Tools > References > mscorlib.dll
' or
' Tools > Brouse to   C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb
' or
'  Run  Sub EarlyLaterlyBinding()  before running this macro
Dim EnKrouptASCII As ASCIIEncoding: Set EnKrouptASCII = New ASCIIEncoding

Rem _a)  Convert bytes to string
Dim arrAscW() As Variant
 Let arrAscW() = Array(89, 97, 115, 115, 101, 114)
Dim arrVar1D() As Variant
 Let arrVar1D() = Application.Index(Cells, 1, arrAscW())  '   this returns a 1D array    https://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html#post5408376
Dim GetMyString As String
 Let GetMyString = Join(arrVar1D(), "")
' or in one go
 Let GetMyString = Join(Application.Index(Cells, 1, Array(89, 97, 115, 115, 101, 114)), "")
 Debug.Print GetMyString

Rem _b)  getting a 1 dimensional array filled , that has Elements of the Byte type in some way other than looping
Dim myArr() As Byte
 Let myArr() = EnKrouptASCII.GetBytes_4(GetMyString)
 Let myArr() = EnKrouptASCII.GetBytes_4(Join(Application.Index(Cells, 1, Array(89, 97, 115, 115, 101, 114)), ""))
 Debug.Print EnKrouptASCII.GetString(myArr())
End Sub

Sub BytesInVariant()  '  http://www.eileenslounge.com/viewtopic.php?f=30&t=35600
Dim EnKrouptASCII As ASCIIEncoding: Set EnKrouptASCII = New ASCIIEncoding
Dim myArr() As Variant
Dim a As Byte, b As Byte, c As Byte, d As Byte, e As Byte, f As Byte
 Let a = 89: b = 97: c = 115: d = c: e = 101: f = 114
 Let myArr() = Array(a, b, c, d, e, f)   '    BytesInVariant.jpg    https://imgur.com/yOcQVTr   https://i.imgur.com/yOcQVTr.jpg
On Error GoTo Bed
 Debug.Print EnKrouptASCII.GetString(myArr())
Exit Sub
Bed:         '    BytesInVariantError.JPG    https://imgur.com/D04Iw8j    https://i.imgur.com/D04Iw8j.jpg
MsgBox prompt:=Err.Number & vbCr & vbLf & Err.Description: Debug.Print Err.Number & vbCr & vbLf & Err.Description
End Sub

Sub EarlyLaterlyBinding()   '    https://excelfox.com/forum/showthread.php/2240-VBA-referring-to-external-shared-Libraries-1)-Early-1-5)-Laterly-Early-and-2)-Late-Binding-Techniques
 On Error GoTo Bed
 ThisWorkbook.VBProject.References.AddFromFile "C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb"
' or
 ThisWorkbook.VBProject.References.AddFromguid "{BED7F4EA-1A96-11D2-8F08-00A0C9A6186D}", 2, 4
Exit Sub
Bed:  ' If you already have  mscorlib.dll  checked, then you will likely get  32813 Name conflicts with an existing module, project or object library      32813 Name steht in Konflikt mit vorhandenem Modul, Projekt oder vorhandener Objektbibliothek
 MsgBox prompt:=Err.Number & vbCr & vbLf & Err.Description: Debug.Print Err.Number & vbCr & vbLf & Err.Description
End Sub

Sub ASCIIW() ' make a simple  ChrW  column list row
Dim Cnt
    For Cnt = 1 To 200
     Cells(1, Cnt) = ChrW(Cnt)
    Next
End Sub

_.__________________________________-

I don’t know if all that is any use as I am not quite sure what is wanted…

_.__________________________________


Ref
https://www.snb-vba.eu/VBA_Arraylist_en.html

[YasserKhalil]

Thanks a lot Mr. Alan. You do that in an awesome approach
To have a compact code (that I was searching for), here's my final code

Code: Select all

    Dim a, objASCII As Object, s As String
    Set objASCII = CreateObject("System.Text.ASCIIEncoding")
    a = Array(89, 97, 115, 115, 101, 114)
    s = Join(Application.Index(Cells, 1, a), "")
    Debug.Print s

[Doc.AElstein]

But remember it will only work if first you have made a worksheet made from like

Code: Select all

Sub ASCIIW() ' make a simple  ChrW  column list row
Dim Cnt
    For Cnt = 1 To 200
     Cells(1, Cnt) = ChrW(Cnt)
    Next
End Sub

[YasserKhalil]

Thanks a lot for referring to that point. But in that case we are back to the same issue. As I have to have a worksheet with the first row filled with values. Hope to find a way not depending on a worksheet.

[Doc.AElstein]

Thanks a lot for referring to that point. ….. As I have to have a worksheet with the first row filled with values. Hope to find a way not depending on a worksheet.

I think if you understood what I was doing, then you can see that
_ I am using a worksheet,
but
_ I am only doing that for convenience to get a horizontal list of ASCII characters…
_.________________________________________

....a compact code (that I was searching for), .....

Like this, maybe

Code: Select all

  Es = Join(Application.Index(Array(ChrW(65), ChrW(66), ChrW(67), ChrW(68), ChrW(69), ChrW(70), ChrW(71), ChrW(72), ChrW(73), ChrW(74), ChrW(75), ChrW(76), ChrW(77), ChrW(78), ChrW(79), ChrW(80), ChrW(81), ChrW(82), ChrW(83), ChrW(84), ChrW(85), ChrW(86), ChrW(87), ChrW(88), ChrW(89), ChrW(90), ChrW(91), ChrW(92), ChrW(93), ChrW(94), ChrW(95), ChrW(96), ChrW(97), ChrW(98), ChrW(99), ChrW(100), ChrW(101), ChrW(102), ChrW(103), ChrW(104), ChrW(105), ChrW(106), ChrW(107), ChrW(108), ChrW(109), ChrW(110), ChrW(111), ChrW(112), ChrW(113), ChrW(114), ChrW(115), ChrW(116), ChrW(117), ChrW(118), ChrW(119), ChrW(120), ChrW(121), ChrW(122), ChrW(123), ChrW(124), ChrW(125), ChrW(126)), 1, Evaluate("={89, 97, 115, 115, 101, 114}-64")), "") 

_._____________________________________

To explain
The point of the Cells of the worksheet used , ( the worksheet where I had previously pasted a ASCII character horizontal list ) , was to be somewhere to pick out from the characters from their ASCII number which for convenience correspond to the column number
In other words what we generally want is a “horizontal” array or range of ASCII characters in their usual order of listing
…… X Y Z [ \ ] ^ _ ` a b ….. etc.
That corresponds to the Asc ( or AscW ) numbers
…… 88 89 90 91 92 93 94 95 96 97 98 ….. etc.

For convenience, using Cells in a worksheet where all Characters are listed in a row starting at character 1, Chr(1) ( or ChrW(1) ) , we conveniently have the column number as being = to the character Asc number
( Note: we are missing the first ASCII character, Chr(0) )
Its just for convenience to start at 1. That allows us to use like Ws.Cells in Index, which in Index I have found to be often very efficient and useful. But we can offset and allow for that offset somehow when necessary in any formula

So, we have like pseudo

Index ( [Horizontal Array or range of ASCII characters] , [Row of 1] , [Columns of 89, 97, 115, 115, 101, 114)] )

The whole point of that is to give us a 1 dimensional array, of {"Y", "a", "s", "s", "e", "r"}
Index ( [Horizontal Array or range of ASCII characters] , [Row 1] , [Array(89, 97, 115, 115, 101, 114)] )
=
{"Y", "a", "s", "s", "e", "r"}

That final 1 D array is joined with no separator between, “” , to give Yasser
Join( {"Y", "a", "s", "s", "e", "r"} , “” ) = "Yasser"

We don’t have to use any particular worksheet. We can use any worksheet, or even any range or even a simple array

For example, I can make a long text like
Chr(1) & " " & Chr(2) & " " & Chr(3) & " " & Chr(4) & " " & Chr(5) & " " & Chr(6) & ….etc
and then use Split on that to get an [Array of ASCII characters]

Some examples:
https://excelfox.com/forum/showthread.p ... #post15067
https://excelfox.com/forum/showthread.p ... #post15068

If I don’t need all characters, then I can simplify a bit.
https://excelfox.com/forum/showthread.p ... #post15069

( example coding also in the uploaded .txt file )
_._____________________________________________________

Notes:
_(i) If you use the Split way, then best is to avoid using a single character as the separator. Otherwise you may have problems if you want that same character in your Horizontal Array of ASCII characters because it will be seen as a separator for Split. This means that you will not get that character in your horizontal array listing. Instead you will have 2 extra empty elements in your array, and all characters after where that character should have been will appear offset by one place to the right in the horizontal array

_(ii) You can save some time in typing out the long code lines if you automate the process a bit. The following macro will give you some of the long code line parts, which you can copy from the Immediate window , then possibly paste into a text document or WORD so that you can do some other manipulation, such as the line continuation which you will need if the code line is too long for the code window.
Example:
https://excelfox.com/forum/showthread.p ... #post15070

_(iii) Another idea would be to make your own custom list for the [Horizontal Array or range of ASCII characters]. I expect for you Yasser, that would probably be a good idea, since you seem to play around a lot with ASCII things
https://www.snb-vba.eu/VBA_Excel_customlist_en.html
http://www.eileenslounge.com/viewtopic.php?f=27&t=34426
http://www.eileenslounge.com/viewtopic. ... 98#p265298
( Don’t ask me to do that. - I have never used custom lists, but it looks at first glance very simple, so you should be able to do it )


Alan

[LisaGreen]

Not studied this really but a shot in the dark is I wonder if it's possible to use SQL or RegEx to avoid looping?

Lisa

[YasserKhalil]

Thank you very much Mr. Alan for this detailed and awesome explanation. Now it is solved.
Best and Kind Regards