Excel VBA determine amount of characters

[Keebellah]

Hi, in 2022 this post (Determine Point of Cell Word Wrap of A String (max length of string permitted for a static sized cell) ) more or less asks the same thing.
I have managed using a code snippet posted elsewhere that calculates the pixel width of a single character using the font name, size, bold true or false, italics true or false. For example a Z returns 6 for a PT Sans narrow bold 10.
Using the cell’s EntireColumn.Width which I assume gives me the column in pixels.
I am trying to calculate max number of characters I can pass to the cell to avoid word wrap.
The next step would be to calculate the number of characters across 5 columns and add a carriage return if the text length is longer than the 5 column widths added up
I do not like merged cells and would then place word wrap in the first cell and the other 4 will never contain text so it looks like a merged cell
Probably a confusing explanation but the gist of this all is that based on the single character’s pixel width calculated the max characters before a carriage return would be needed.
Creativity is in MHO the limit of VBA programming

 

[Jaafar Tribak]

I would use a temporary helper TextBox shape, adjust its width, height and margines to the size of the input cell then copy the cell text to it or just add the text taking the font attributes into account.

Once the text has been added to the temporary textbox it will be automatically & nicely wrapped and each line of text will correspond to the text in each column.

This hack and all of the above steps should be easily performed with vba.

 

[Keebellah]

Hi, thank you for the tip, but that's what I want to avoid. This is a manual action which is not an option.
Like I mentioned, based upon the Font Name, the size and the type (if it's bold, italics, underscored or not) the code I found in the post I mentioned already tells me what the width of a single character is in that cell.
The cell's font is PT Sans Narrow Bold 12
If I take an uppercase a, the value I get as a result is 7
If I take an uppercase i, the value I get as a result is 6
What I need to know is if the cell's column width = 10 how many characters it takes to fill this column without the need to Wrap the text.
The reason I am trying to find this out is that I do not like or merged cells across columns but sometimes I need to display the text across let's say 5 columns and then add a carriage line feed if the total text string is longer that the 5 columns together.
The macros I have already add the 5 column's width, so I know the total width of these added up.
I need to know the X number or characters that fill this total and then programmatically add the carriage return and line feed so the cell's row height which is set to autofit will increase or decrease depending on the number of carriage-returns
Hope I've been able to explain myself, this explains my dilemma