REPT Function Bar Chart

[preitano]

Hi, I'm trying to use the REPT function to create bar charts in cells to show project completion status. This works fine when I place the
█ character in a cell and then use a reference to that cell in the REPT function. However, I would prefer to use CHAR to locate the character so that there are fewer dangling components that other uses may step on. Is this possible, or is CHAR limited to a set that does not include the wide block (I prefer the look without whitespace between the characters) I am targeting?

I tried to insert an image of what I'm building, but it does not appear to be working.

https://drive.google.com/file/d/0ByxpD-m3hGZSRmZrTldaTTB1Rkk/view?usp=sharing

<tbody>
[TD="class: xl63, width: 72"][/TD]

</tbody>

 

[Jon Peltier]

I don't know what character that is, or what font you're using. When I paste it into a cell and test the character number, it tells me it's character 63, which is also the question mark.

=CODE(A1) returns the character number of the first character in cell A1.

I've often used the pipe character (|), CHAR(124), in the Brittanic Bold font. It's thinner than the character you've posted, but your formula should handle this with no problem.

 

[preitano]

Apparently, it is the character known as a "full block" because it fills the width of the font space, which makes it look so great with these character bar charts (it's popular with the ASCII art people for the same reason). It's typed in by using [Alt 219], but it must be out of the range of what CHAR can accept.

 

[Jon Peltier]

I hadn't come across that one. Looks like VBA can't get it, either.

 

[pgc01]

or is CHAR limited to a set that does not include the wide block (I prefer the look without whitespace between the characters) I am targeting?

Yes, CHAR() is an old function that is limited to the first 256 character, like its counterpart CODE()

In vba you have the corresponding Chr() and Asc()

Excel supports Unicode and so you can use in vba ChrW() and AscW() to get the codes of any Unicode character or to get any Unicode symbol given its code.

Unfortunately the worksheet functions have been slow to catch up and only now in excel 2013 do you have the corresponding functions UNICHAR() and UNICODE().
For older versions of excel you can write small udf's that have the same functionality.


An example with your Full Block:

Range("A1").Value = ChrW(&H2588)

Hope this helps.

 

[Jon Peltier]

ChrW, AscW, UNICODE, UNICHAR. Okay, PGC, you've taught me something useful today. Thanks.

 

[pgc01]

Hi Jon

Some hundreds more like this one and we are even.