Convert bytes to string

[Doc.AElstein]

possible to use SQL or RegEx to avoid looping?

Hi Lisa,
I know nothing about that stuff
I expect the area of ADODB and SQL or related stuff is likely to be a better option for things related to lists and getting things from them. ( Someone over at VBForums has been trying stear me towards ADODB and SQL away from some of the Index one liner things…)
But I know nothing about that stuff: Its another thing on my list of things to get around to learning about..

to avoid looping?

We aren’t really looping anything anymore, at least for the main coding. We got past that problem.

The only thing thing I was still looping, was the simple macro for getting the single row list of Characters. But that was just something to be done once to get the “help” worksheet.

Code: Select all

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

After your post I took another look to see if I could change that simple macro to the Evalute one liner , just for completeness.
I think I must have been careless the first time, as on second glance, it works quite easily. ….it did not seem to work when I first tried … I must have previuopusly made an error or two….
This simple macro can be used instead to get the ASCII list…..

Code: Select all

Sub EvaluateRowOfChrASCII()  '    http://www.eileenslounge.com/viewtopic.php?p=276830#p276830
 ActiveSheet.Range("A3:IU3").Value = Evaluate("=If({1},Char(Column(A:IU)))") '  IU  is column number  255
End Sub

That immediately gives us another compact option……_( all be it only for 255 ASCII characters )

[Doc.AElstein]

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

_...................Here is another compact option that I missed…..

Code: Select all

 Sub ConvertBytesToString6()  '   http://www.eileenslounge.com/viewtopic.php?p=276834#p276834
Dim Ay() As Variant: Let Ay() = Array(89, 97, 115, 115, 101, 114)
Dim Es As String
 Let Es = Join(Application.Index(Evaluate("=If({1},Char(Column(A:IU)))"), 1, Ay()), "")
End Sub

But note
I am using the Excel Function Char . - This is the equivalent to VBA Chr - This will only get you 255 ASCII characters.
In VBA you have the ChrW for more than 255 character things. Unfortunately there is no VBA equivalent.
So this solution is limited to 255 ASCII characters

[HansV]

Why not simply

Code: Select all

Sub EvaluateRowOfChrASCII()
    Range("A2:IU2").Value = Evaluate("=CHAR(COLUMN(A2:IU2))")
End Sub

[Doc.AElstein]

Hi Hans
I think we are back to that issue of the changes that were made to Office from about 2016 onwards …. ( http://www.eileenslounge.com/viewtopic. ... 30#p262102 )

For earlier versions you need mostly that extra bit, ( If({1}, _______ ) ) , or something similar , or else you only get one value out.
I probably should have mentioned that for later Office versions you may not need the extra bit.

In my Excel 2003 2007 and 2010 I need that extra bit or else I get just one value out. I expect that is also the case with Excel 2013

I think we concluded that from about 2016 Microsoft started introducing the changes which finally resulted in the Spill functionality in the newest versions. - Even though Spill functionality is not available from 2016, some array related things are different. A result of this is that from approximately 2016 , usually you don’t need those extra bits to make things return a full array.
We are not sure if it is dependant on updates etc, that's why I say from approximately 2016...

I tend to make things like this work in the earlier versions, assuming / hoping in going forwards in versions ** it will still work. So far that has always been the case**


Alan

BTW
**_ Does the Range("A2:IX2").value=Evaluate(“=If({1},Char(Column(A:IX))”) also work for you?
_ What Office version are you using?

[HansV]

No, since it contains curly quotes instead of straight quotes. But when I change that, it works (I used columns A:IU instead of A:IX)

[HansV]

And I'm using Office 2019, not 365, so I don't have the new dynamic array functionality.

[Doc.AElstein]

Yes it should be IU ( column 255 ) , - I think mostly I used IU, the IX was an occasional typo,
_.______________________________________

No, since it contains curly quotes instead of straight quotes. But when I change that, it works …

Ah, probably a copy from word, I often mess that up. I meant straight quotes..
_.____________________________

using Office 2019, not 365, so I don't have the new dynamic array functionality.

I think we concluded that, although you don’t have spill / dynamic array functionality , there is a good chance that you do have some of the changes necessary for that , which effects array things. - Those things seem to have been added from approximately 2016, so if you have 2019 then that all makes sense

[HansV]

That might well be. The click-to-run versions of Office 2016 and Office 2019 are identical to Office 365, only with all new functionality disabled.