Is there a way to assess the position of text within a cell in VBA?

[TomCon]

I have a macro that puts a small circle in some cells on a sheet, as illustrated in this screen snap.

But, as you see, when the value in the cell has this many digits (it does not always), i would like the dot a little more to the left. Is there a way to query in VBA, "where is the left edge of the centered text in this cell" so that i could then position my little circle where i want it?

Thank you!

 

[Dan_W]

Short answer = not that I'm aware of. Longer answer = yes-ish but its a bit hacky and annoyingly complicated.

I'm wondering if there is a better way of doing this. What is the purpose of the dot? How have you currently positioned it?

 

[TomCon]

Short answer = not that I'm aware of. Longer answer = yes-ish but its a bit hacky and annoyingly complicated.

I'm wondering if there is a better way of doing this. What is the purpose of the dot? How have you currently positioned it?

Oh i'm being a little geeky, just a spreadsheet that i like to be "perfect". The dot shows where, in a row of numbers, "today's value" falls (updated once a day). I assess all the column widths so i can resize columns and the dots are still ok. A dot to the left of the number says it is less than that number, but closer to that number than the number in the same row in the column just to the left. A dot to the right of a number shows it is greater than that number, but closer to it than the number in the same row in the column to the right. So, it gives you a visual indication of where today's value falls among the range of values that it could fall. But i have a "standard" offset of the positioning of the dot based on the widths of the columns. In some rows the numbers have few digits and some have more. Ideally i would like to position each dot within the column but respecting the text that is there as well. I sort of thought it might be annoyingly complicated

 

[Dan_W]

I'm going to need to read that a few more times to wrap my head around it

While I do that... what is that dot? Is it a shape? And you're using VBA to position it? My initial reaction on reading your question was perhaps this could be a custom number format solution (Excel's wildly underused and thoroughly underrated functionality), but now I'm thinking your requirements might be too complicated for that.... can you show a larger screen capture of where the dots sit (on left and right) for a bunch of the cells?

 

[TomCon]

I'm going to need to read that a few more times to wrap my head around it

While I do that... what is that dot? Is it a shape? And you're using VBA to position it? My initial reaction on reading your question was perhaps this could be a custom number format solution (Excel's wildly underused and thoroughly underrated functionality), but now I'm thinking your requirements might be too complicated for that.... can you show a larger screen capture of where the dots sit (on left and right) for a bunch of the cells?

OK here it is.

Yes, its a shape (very small circle), and yes, i am positioning it daily with VBA.
In the first row, the "today value" (not shown) is between -0.01 and 2.45, but closer to -0.01.
In the second row, the today value is between -3.12 and -1.64, but closer to -1.64.
In the third row the value is between 289 and 516, but closer to 289.
Etc

Thanks for taking a look at this perhaps "odd" type of thing. But thats one thing great about Excel, you can even do odd stuff with it!

 

[Dan_W]

Ok, yes, I follow now. This is very helpful, thank you.

The tricky thing here is that you're using a font with varying widths, that are centered. I suppose my immediate thoughts are: (1) wouldn't this be solved by just giving a bit more width to each of the columns? You can measure the width of text technically, I just don't think it makes sense to do it here because of all the variables mentioned above - and we're talking the difference of pixels here... on worksheet that deals in points (not pixels). You could guesstimate a width of the text by getting the length of the value as thought it were a string. That could give you a rough idea of how 'wide', but again, it won't be very scientific. I'm just trying to think if there is a better/easier way of doing this.

And by the way, I only ever do 'odd' things on Excel, so this is completely fine!

 

[Dan_W]

Here is one option. This is the dodgy hacky way of doing it that I used to use a long time ago.

So to explain: A2 has the number 172.2, and beneath it is a shape that autofits to its content (here, 172.2). Then in A5 I have a VBA function being called from the worksheet, which I've displayed in A6. Then I've done the same for each of the three other numbers.

Essentially, what you are doing is drawing a shape (rectangle) on a sheet, and then going in and changing the properties of the shape so it resizes to fit the text and has no margins at all. And then you're left with the above. From that point, it is very straight foward to get the width of the resulting shape, as per the function in my screen capture. You also need to set the shapes 'formula' to the range with the target text in it, so that it automatically updates, but there are other ways of doing it.

Just a thought.

 

[TomCon]

Thanks...thats very interesting. It seems like it would work. I'll give it a shot! The reason i don't make teh columns wider is just this is a "dashboard" type sheet, and i am trying to cram as much into it as possible. Sized at ~80% its a whole big screen of numbers. Some of the cells i concatenate 2 (or even 3) nubmers into. So, I am trying to keep the columns very narrow. But, its a very cool workaround to get a value for the size of the txt, and thanks for a very clear explanation!