Adjust text spacing to length of words using CharKerning

[zabpeter]

Hello!

I would like to ask for help! I have a table where I want to adjust the text spacing to the length of the words. I have a well-functioning macro, but unfortunately it is very slow. Do you have any ideas to possibly speed it up? Thanks

for x = 0 to 20
for y = 0 to 40
hossz = len(ThisComponent.Sheets.getbyname("Munkalap1").getcellbyposition(x, y).string)
meret = ThisComponent.Sheets.getbyname("Munkalap1").getColumns.getByIndex(x).width
if hossz > 0 then
k = meret/hossz/2.5
ThisComponent.Sheets.getbyname("Munkalap1").getcellbyposition(x, y).createtextcursor.charkerning = - k
end if
next y
next x

Edit: Changed subject, was CharKerning  Make your post understandable by others  -- MrProgrammer, forum moderator

[Zizi64]

Your macro will apply individual formatting properties for every cells.

Your method gives me an ugly result with congested, overlapping characters.

If you want to resize the cell contents for the existing cell width, then it is better to apply a user defined cell style with shrinked font property. Or - if it is permissible in this task - resize the columns to the cell contents.

[Zizi64]

Can you upload a sample file: what content (which lenght of the contents) want you to format?

[Lupp]

Macros doing something for ranges but needing to work with properties only available per single cell tend to be slow. Creating access to a specific cell anew more than once is inefficient and slows theese macros down additionally.
Often you can avoid the need to loop through many cells by ruling out complete ranges not needing to be processed. In your case e.g. ranges of the sheet to which you want to apply your "AutoKerning" (under conditions) may be identified by a specific substring of the applied cell style.
The attached example is containing a Sub bound to the sheet event onContentChanged. The sheet also has some ranges of cells with the cell style named cs_AutoKerning. If any cells having assigned this style are edited, the Sub treats them (finally one by one) and adapts the character kerning.
The mentioned identification of cells needing treatment is implemented testing for "_AutoKerning" as a sub-string of the name of the cell style.

aoo108721verySpecialKerning.ods

(57.46 KiB) Downloaded 51 times

[JeJe]

I get the same ugly result. Your code can be simplified a bit but I doubt it will make much difference to the speed.

Code: Select all

sheet = ThisComponent.Sheets.getbyname("Munkalap1")
for x = 0 to 20
meret = sheet.getColumns.getByIndex(x).width 'only need to call this once for each x
for y = 0 to 40
cell=sheet.getcellbyposition(x, y)
hossz = len(cell.string)
if hossz > 0 then
k = meret/hossz/2.5
cell.createtextcursor.charkerning = - k
end if
next y
next x

[Lupp]

Code: Select all

k = meret/hossz/2.5

'meret' is the width of the column and 'hossz' the length (counted characters) of the contained string. This means that the amount of the negative kerning (making the string narrower) is increased if more space is available and reduced if more space is needed. That's counter-action. Read my Sub and try its working.