Mister Ian's Weblog

Blog

  • Table Cleanup

    This is a moderately generic macro to clean up tables.

    Sub tfixtest()
'
' tfixtest Macro
'
 
On Error GoTo ErrorHandler
    Selection.Tables(1).Select
    Selection.Rows.HeightRule = wdRowHeightAuto
    Selection.Rows.AllowBreakAcrossPages = False
       
    ' Selection.SelectCell
    Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
   
    With Selection.ParagraphFormat
        ' .RightIndent = InchesToPoints(0)
        ' .LeftIndent = InchesToPoints(0)
       
        .SpaceBefore = 1
        .SpaceBeforeAuto = False
        .SpaceAfter = 1
        .SpaceAfterAuto = False
        .CharacterUnitRightIndent = 0
               
        
    End With
   
    
    Selection.Cells.VerticalAlignment = wdCellAlignVerticalTop
    Selection.Tables(1).AutoFitBehavior (wdAutoFitWindow)
    Selection.Tables(1).AutoFitBehavior (wdAutoFitWindow)
    Selection.Font.Name = "Arial"
    Selection.Font.Size = 10
   
    
    
    With Selection.Tables(1)
        .TopPadding = InchesToPoints(0.04)
        .BottomPadding = InchesToPoints(0.04)
        .LeftPadding = InchesToPoints(0.06)
        .RightPadding = InchesToPoints(0.06)
        .Spacing = 0
        .AllowPageBreaks = True
        .AllowAutoFit = True
    End With
   

    ' Selection.Style = ActiveDocument.Styles("Table Text Left (Alt-T)")
       
    
ErrorHandler:
 
      
End Sub

    Below is some stuff that I was messing with but isn’t fully sorted out yet.

    Sub tablefun()
 
Dim oTable As Table
Dim oCell As Variant
 
oTable.Select
 
 
For Each oCell In oTable.Range.Cells
    oCell.Width = InchesToPoints(1)
    oCell.Shading.BackgroundPatternColorIndex = wdBlue
    oCell.Range.Font.Name = "Arial"
    oCell.Range.Font.Size = 20
    oCell.Text = "ack"
Next oCell
 
 
End Sub
 
Sub FixAllTables()
     Dim tbl As Table
    
     For Each tbl In ActiveDocument.Tables
    
         Selection.Tables(1).Select
        
         
'         For Each oCell In oTable.Range.Cells
'  oCell.Width = InchesToPoints(1)
'  oCell.Shading.BackgroundPatternColorIndex = wdBlue
'  oCell.Range.Font.Name = "Arial"
'  oCell.Range.Font.Size = 20
' Next oCell
 
         Selection.Tables(1).Select
        
         With tbl.Cells(1)
        
      '   With Selection.Cells(1)
            .VerticalAlignment = wdCellAlignVerticalTop
            .TopPadding = InchesToPoints(0.06)
            .BottomPadding = InchesToPoints(0.06)
            .LeftPadding = InchesToPoints(0.04)
            .RightPadding = InchesToPoints(0.04)
            .WordWrap = False
            .FitText = False
         End With
        
         Selection.Tables(1).AutoFitBehavior (wdAutoFitWindow)
       
         Selection.Rows.HeightRule = wdRowHeightAuto
         Selection.Rows.Height = InchesToPoints(0)
         Selection.Rows.AllowBreakAcrossPages = False
                    
         Next
End Sub

  • Trim Function

    This is to remove excess characters from a string. It can be enhanced with options or changed depending if you want spaces or not etc.

    Function fixchar(s As Variant) As Variant
 
Dim a, b, i As Integer
    fixchar = ""
   
    
    If Len(s) = 0 Then Exit Function
     
    For i = 1 To Len(s)
        b = Mid(s, i, 1)
       
        
        ' No Spaces: "[A-Z,a-z,0-9]"
        ' Spaces OK: "[A-Z,a-z,0-9 ]"
       
        If b Like "[:,/,A-Z,a-z,0-9 ]" Then
            fixchar = fixchar & b
        End If
    Next i

End Function

    There is a more sophisticated function here where you can just run a macro on a selection instead of using a function as above.

  • Excel Conditional Formatting

    I’m always forgetting the syntax and also here’s some tricks.

    1. How to highlight cells based on another cell’s value in Excel 2011 – Ask Different
    2. Format cell color based on value in another sheet and cell – Stack Overflow
    3. Conditional format cells based on match in another sheet – Stack Overflow

  • Excel VLOOKUP with Multiple Criteria

    How to VLOOKUP with Multiple Criteria Using INDEX and MATCH in Excel

  • Directory Tree Builder Add-In for Excel

    Directory Tree Builder Add-In for Excel.

    DirTree will create a tree-like hierarchical list of all the folders and (optionally) files contained within a specified directory. When you load the Add-In, an item with the caption Build Directory Tree is created on your Tools menu. When you click that item, the Directory Tree Configuration screen, described below, will appear. You can set the various options of the tree from the configuration screen.

    DirTreeMainDepth

  • Reverse String

    Function revstr(s As String) As String 
'
' revstr Macro
'

revstr = ""
 
If s = "" Then Exit Function
 
For j = Len(s) To 1 Step -1   
    revstr = revstr & Mid(s, j, 1)
Next

End Function