YasserKhalil wrote: ↑31 Oct 2020, 13:09
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…
_.________________________________________
YasserKhalil wrote: ↑31 Oct 2020, 12:52
....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
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also