Get string width in Excel 1

[groston]

I have a macro that fills out an Excel spreadsheet. The layout of the spreadsheet (fonts, column widths, etc.) is locked. The data is dynamic and is extracted from a database.

In the macro, I build a string, which must then be placed into a cell. How can I determine the length of the string in pts (twips, inches, anything)? Why? If the string is longer than the cell into which it is to be placed, I will need to manually break the string and place it into more than one cell.

Thanks.

 

[SkipVought]

Hi,

Function TextPoints(MyText As String) As Single
    Application.DisplayAlerts = False
    Worksheets.Add
    With ActiveSheet.[A1]
     .Value = MyText
     .EntireColumn.AutoFit
     TextPoints = .Width
    End With
    ActiveSheet.Delete
    Application.DisplayAlerts = True
End Function

This function will return the width of your test.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[groston]

Skip,

I certainly hope you were laughing your ass off when you wrote that. I certainly give you points for cleverness - your approach will work. But, is it posisble to contrive a more round-about, convoluted method than that???

Thanks!

 

[SkipVought]

Well G, I like to crack a pun from time to time, or tell a great drawn out "shaggy dog story" when the opportunity presents itself.

But I nary cracked a smile on this one.

You've got all sorts of proportional fonts and bold and italic...

Mebe you kin find a fancy algorythm to use. I'd ruther wip up a quik s'lution usin' the tools at hand, an' use the rest of my lesure time, thinkin' 'bout udder tings!

Hope ah dint disappoint yer! ;-)



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com