Link to home
Start Free TrialLog in
Avatar of tonygardner1
tonygardner1Flag for United States of America

asked on

VB Script to Create Center Tabs Over Words

Hello Again Experts!

I have an interesting idea for a VB script macro that would tremendously help with a very tedious task. Here goes...

Imagine an arbitrary line of text containing words of various sizes:
'Canary Aardvark Lion Giraffe Hippopotamus Cat Centipede Finch'

Open in new window

Now insert a line with numbers over each of the words:
'1      2        3    4       5            6   7         8    '
'Canary Aardvark Lion Giraffe Hippopotamus Cat Centipede Finch'

Open in new window

The objective here is to replace the spaces between each of the numbers with tabs, but keep the numbers' positions the same. Using the assumption that the numbers are always above the first letter of each word, it should be possible to create a VB script that would create left justified tabs above the first letter of every word.

Now suppose that the numbers need to be centered over each of the words:
'  1       2       3     4         5        6      7       8  '
'Canary Aardvark Lion Giraffe Hippopotamus Cat Centipede Finch'

Open in new window

I imagine that would require the ability to analyze the length of each of the words, divide by two, and create center tabs at those positions.

I am very open to any and all suggestions the EE community might have for accomplishing the above tasks (creating 'intelligent' left and center tabs). I am also open to any VB scripting tips for how to do any single task, such as determining what position the cursor is at, or determining the length of a word.

I'm pretty sure this one will be fairly complicated, so I'm going to set the value to 500 points, which will be split amongst the brave souls who participate.

Tony G.
Avatar of GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

It sounds a bit strange.

The first difficultly that comes to mind is that the exact position of the words in the line is not solely a function of the character count, but depends on the font used. Most fonts vary the horizontal spacing according to the character - an 'm', for instance taking up more space than an 'i'.

The final result could be achieved with a borderless two-row table
Bearing in mind the caveats in my previous comment, here is a VBA macro that tries to do exactly what you suggest. It works with proportional fonts, but isn't dynamic in any way, i.e. it would need to be rerun for any changes to the text or the font.
Sub MakeTabs()
    Dim wrd As Range
    Dim para As Paragraph
    Dim rngStart As Range
    Dim rngEnd As Range
    Dim para1 As Paragraph
    Dim i As Integer
    Dim j As Integer
    Dim sngStart As Single
    Dim sngEnd As Single
    Dim strText As String
    Dim sngTabPosition As Single
    Set para = ActiveDocument.Range.Paragraphs.First
    ActiveDocument.Range(0, 0).Text = vbCr
    Set para1 = ActiveDocument.Range.Paragraphs.First
    For Each wrd In para.Range.Words
        If wrd.Text = vbCr Then
            Exit For
        End If
        Set rngStart = wrd.Duplicate
        Set rngEnd = wrd.Duplicate
        sngStart = rngStart.Information(wdHorizontalPositionRelativeToPage)
        rngEnd.Collapse wdCollapseEnd
        sngEnd = rngEnd.Information(wdHorizontalPositionRelativeToPage)
        sngTabPosition = ((sngStart + sngEnd) / 2) - ActiveDocument.PageSetup.LeftMargin
        If sngEnd < sngStart Then
            Exit For
        End If
        i = i + 1
        para1.TabStops.Add sngTabPosition, wdAlignTabCenter
    Next wrd
    For j = 1 To i
        strText = strText & vbTab & j
    Next j
    ActiveDocument.Range(0, 0).Text = strText
End Sub

Open in new window

This shows how to create a similar layout with a table:
Sub MakeTable()
    Dim rng As Range
    Dim tbl As Table
    Dim brd As Border
    Dim c As Integer
    Set rng = ActiveDocument.Paragraphs.First.Range
    Set tbl = rng.ConvertToTable(" ")
    tbl.AutoFitBehavior wdAutoFitContent
    tbl.Rows.Add tbl.Rows.First
    For c = 1 To tbl.Columns.Count
        tbl.Cell(1, c).Range.Text = c
    Next c
    For Each brd In tbl.Borders
        brd.LineStyle = wdLineStyleNone
    tbl.Range.Paragraphs.Alignment = wdAlignParagraphCenter
    ActiveWindow.View.TableGridlines = False
End Sub

Open in new window

Avatar of tonygardner1


Wow, I'm impressed. This looks very promising! I'm wondering if it's possible to make Just a couple of "tweaks"... Just for clarity sake, I'm placing the cursor on the line just  above the words where the numbers are when running the script.

Here's the resulting output:
   1       2      3      4         5        6      7    8
1      2        3    4       5            6   7         8
Canary Aardvark Lion Giraffe Hippopotamus Cat Centipede Finch

Open in new window

If it's possible, I would like to replace the original line of numbers with the new one that has the tabs, so it's not duplicated. Also, you might notice that the last word didn't quite get its corresponding number centered over the word, but rather on the left. Maybe if we let it go through the loop one more time, or possibly put a dummy placeholder just after the last word...

By the way, I can't really use the table method for various reasons I can't really go into, so let's just see if it's possible to tidy up the two issues, and I think it will be a done deal!

Tony G.
Avatar of GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

Your assistance with this project has been OUTSTANDING. I would like to close out this Q and award you the 500 points. I will be opening another Q shortly as I have some additional modifications/enhancements to make to this project, and your continued assistance would be very much appreciated.

Tony G.
GrahamSkan's assistance with this project has been outstanding. A multitude of thanks!