vba Automatically scale REPT-based in-cell chart to column width?

[k1s]

Hi, does anybody know how to do this?:

Say I've got an in-cell chart using the REPT function
=REPT(" ",(B12/$C$3))&"|"&REPT(".",(C12/$C$3))&CHAR(149)&REPT(".",(D12/$C$3))&"|"

...where c3 is

I'd like to have scaling factor in C3 automatically adjusted so that the chart always stays within the width of columns B & C, irrespective of the values in in B12, C12 & D12.

Is it possible?

 

[Hui]

B7: should be =REPT(" ",(B12*$C$3))&"|"&REPT(".",(C12*$C$3))&CHAR(149)&REPT(".",(D12*$C$3))&"|"

then change C3 to 2.5

 

[k1s]

B7: should be =REPT(" ",(B12*$C$3))&"|"&REPT(".",(C12*$C$3))&CHAR(149)&REPT(".",(D12*$C$3))&"|"
then change C3 to 2.5

Hi,

The formula works OK (I don't think it will matter for this purpose whether the scale factor is < 0 and a divisor or > and a multiplier).

What I'm trying to work out out is whether some vba can be constructed to read the values in B12:D12 and 'realise' that those values would mean that the repeated characters would extend past the given width of the [unwrapped] columns B & C, and so automatically adjust the scale factor in C3 so that the number of characters repeated stays within the cell width.

 

[vletm]

So far Your B12:D12 has been fixed values!
Then sometimes any formula (original nor others)
do not give Your wanted (possible) result
if adjust only factor C3.
Where comes those B12:D12-values?

 

[k1s]

Yes that's right, the values in B12:D12 will be entered or looked up from somewhere else but can vary. The examples I gave shows that with the cell width as 9, and the scale factor as 0.3, the values in B12:D12 break the boundary of column C. In the example in picture below, the scale factor would need to be adjusted to 0.41 to make the cell fit in the fixed column width.

One thing I've noticed though, is that even though I set the column width to 10 (by right clicking on the column header), the width reported by the function =cell(width) seems to change by itself...

 

[vletm]

I tested that =cell("width") and for me it won't work as needed!
Sometimes it gives 'correct' value ... normally not!
Do You want solution which uses VBA?

 

[k1s]

Same for me...strange.

Yes I assumed it would need to be VBA (but I'm not very good at it ).

 

[vletm]

@k1s
... not ready!
but You could get some ideas .. maybe?
There are many many possible changing variables ... like font...

 

[k1s]

Thanks. I'm not quite sure what's going with the sheet. I can see in rows 24:26 you're comparing the three types of repeated character, for repeat values around 26-29....and at row 17-18 charting sample column widths or 10-30 with potential scale factors...

...but I'm not really sure where that takes me (sorry I'm slow at this)

 

[vletm]

... maybe this would be 'more clear'?
It would give many times 'possible answers'.

There are still few challenges!
Like 'how to know correct(useful) column width?
If another font ...
but
This would be more clear if use 'flying boxes' instead of 'chars!
>> Ideas ... Questions ... Answers?

 

[KenU]

k1s,

This won't ever be exact because there are a lot of things that can alter the appearance on screen. However, I have attached a modified version of your sample with some changes and explanation that should get you close and may spark some other thoughts for you or others.

In this case, I don't know that VBA would solve the problem either using the character approach (perhaps a shape would get you closer).

Hope this helps.

Regards,
Ken

 

[k1s]

Thanks guys. Ken's rounding approach looks like it's going to work for my data.

I looked up that =CELL("width") returns the Column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size.

And for the vba Range.ColumnWidth Property
...For proportional fonts, the width of the character 0 (zero) is used.

...which still doesn't explain why the value would keep changing even if nothing in the column has changed. Oh well.