May 042015
 

So there I was, trying to remember the password to one of the Excel script files I had but nothing was coming to me. So obviously the solution was Google.

http://superuser.com/questions/807926/how-to-bypass-the-vba-project-password-from-excel

http://blog.anidear.com/2013/10/unlock-protected-excel-file.html

  1. If it’s a .XLSM file, you can rename it to a .ZIP,
  2. extract the .\xl\vbaProject.bin,
  3. Rename the .bin to a .txt
  4. Edit in a Text editor (that can handle binary files as text)
  5. Search for DPB=
  6. Replace with DPx=
  7. Save the .txt
  8. Rename back to a .bin
  9. Copy into the .ZIP
  10. Rename the .ZIP to a .XLSM
  11. Open the Excel document in Excel
  12. Bypass the error
  13. Edit the project (Alt-F11)
  14. Right-click the project in the Project Explorer
  15. Choose VBAProject Properties
  16. Select Protection (tab)
  17. Leave Lock project for viewing checked
  18. Enter a new password
  19. Select OK
  20. Exit the VBA for Applications editor
  21. Save the Excel
  22. Re-open
  23. Alt-F11 to enter the VBA Project, use the password entered in Step 18
  24. Your project is now recovered

Other solutions apply for other file extension or for older Excel files.

Note to self: If you require true encryption or better protection for your VBA applications, create a better security strategy.

Jun 222012
 

This time with comments:

Sub NoteTable()
    ' new subroutine, named NoteTable
    ' declare variable oOuterTable
    Dim oOuterTable As Word.Table
    Dim oInnerTable As Word.Table
    Dim oColumn1 As Word.Range
    ' Create a new table, 2 columns and 1 row, at the current cursor location, and assign the memory reference to the oOuterTable variable
    Set oOuterTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, NumColumns:=2)
    ' set the context to oOuterTable
    With oOuterTable
        ' Set the width on the first column of the table to 1.1 inches
        .Columns(1).SetWidth ColumnWidth:=InchesToPoints(1.1), RulerStyle:=wdAdjustNone
        ' Set the width of the second column of the table to 5.5 inches
        .Columns(2).SetWidth ColumnWidth:=InchesToPoints(5.5), RulerStyle:=wdAdjustNone
        ' Center the contents of the first column (two steps)
        .Cell(1, 1).VerticalAlignment = wdCellAlignVerticalCenter
        .Cell(1, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        ' Remove paragraph spacing before/after the text in the second column and set the vertical alignment to center
        ' (We assume left justification on the paragraph)
        .Cell(1, 2).Range.ParagraphFormat.SpaceBefore = 0
        .Cell(1, 2).Range.ParagraphFormat.SpaceAfter = 0
        .Cell(1, 2).VerticalAlignment = wdCellAlignVerticalCenter
        ' Set oColumn1 variable to the memory reference for cell 1,1
        Set oColumn1 = .Cell(1, 1).Range
        ' Add a nested table within cell 1,1 with one column and one row, and assign the memory reference to the oInnerTable variable
        Set oInnerTable = ActiveDocument.Tables.Add(Range:=oColumn1, NumRows:=1, NumColumns:=1)
    End With
    '  set the context to oInnerTable
    With oInnerTable
        ' Center the text horizontally and vertically (two step)
        .Cell(1, 1).VerticalAlignment = wdCellAlignVerticalCenter
        .Cell(1, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        ' Set the font color to white
        .Cell(1, 1).Range.Font.ColorIndex = wdWhite
        ' Set the cell shading to a dark blue (Dark Blue, Text 2, Lighter 40%)
        .Cell(1, 1).Shading.BackgroundPatternColor = -553582695
        ' Set paragraph spacing before and after the text to ensure uniform borders around the text
        .Cell(1, 1).Range.ParagraphFormat.SpaceBefore = 6
        .Cell(1, 1).Range.ParagraphFormat.SpaceAfter = 6
        ' Fill cell 1,1 (the inner table) with the text "NOTE"
        .Cell(1, 1).Range.Text = "NOTE"
    End With
    ' Select the second column in the outer table (a time saver so I don't have to select this to start typing the notes,
    ' I can just fire the macro and keep typing.)
    oOuterTable.Cell(1, 2).Select
    ' Clear the undo history so that all the steps taken by the macro are removed from the undo history
    ' NOTE: This will clear the entire undo history. There does not appear to be a method for pausing the undo history
    ' then resuming once the macro completes its execution.
    ActiveDocument.UndoClear
End Sub

The next step will be to update the macro so that I can highlight some text, then cut that text, build the table and paste the text into the second column of the outer table. That way, I can go back through old documentation and clean up my notes quickly.

Jun 202012
 

I worked at a law firm a few years back and they had some absolutely fabulous formatting for internal documentation. There was just a ton of stuff I learned about how to nicely format documentation, and I picked up a number of styles that I’ve carried forward since. An example is:

NOTE
Example notation. Basically this is a outer table (two columns) and an inner table (in the left column cell). The inner table is shaded and contains the NOTE/WARNING text, and the right column of the outer table contains all the notes or warnings (this text.)

I think it looks attractive and improves documentation readability. But, I’ve had to custom make this notation text from one document to the next because I didn’t have any kind of standardization, or I had to open an old document and copy the formatted table from one document to the next. I’ve been wondering how to automate that… So I did some poking around with Word 2010 today, recording a sample VBA script and then doing lots of Google-fu to research how to customize a short script to do exactly what I want.

Now, I have two scripts that do exactly that, and I’ve added them to my Word 2010 ribbon to quickly build the NOTE or WARNING tables so they look exactly and consistantly the way I want. Wheee!

Sub NoteTable()
    Dim oOuterTable As Word.Table
    Dim oInnerTable As Word.Table
    Dim oColumn1 As Word.Range
    Set oOuterTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, NumColumns:=2)
    With oOuterTable
        .Columns(1).SetWidth ColumnWidth:=InchesToPoints(1.1), RulerStyle:=wdAdjustNone
        .Columns(2).SetWidth ColumnWidth:=InchesToPoints(5.5), RulerStyle:=wdAdjustNone
        .Cell(1, 1).VerticalAlignment = wdCellAlignVerticalCenter
        .Cell(1, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        .Cell(1, 2).Range.ParagraphFormat.SpaceBefore = 0
        .Cell(1, 2).Range.ParagraphFormat.SpaceAfter = 0
        .Cell(1, 2).VerticalAlignment = wdCellAlignVerticalCenter
        Set oColumn1 = .Cell(1, 1).Range
        Set oInnerTable = ActiveDocument.Tables.Add(Range:=oColumn1, NumRows:=1, NumColumns:=1)
    End With
    With oInnerTable
        .Cell(1, 1).VerticalAlignment = wdCellAlignVerticalCenter
        .Cell(1, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        .Cell(1, 1).Range.Font.ColorIndex = wdWhite
        .Cell(1, 1).Shading.BackgroundPatternColor = -553582695
        .Cell(1, 1).Range.ParagraphFormat.SpaceBefore = 6
        .Cell(1, 1).Range.ParagraphFormat.SpaceAfter = 6
        .Cell(1, 1).Range.Text = "NOTE"
    End With
    oOuterTable.Cell(1, 2).Select
    ActiveDocument.UndoClear
End Sub
Sub WarningTable()
    Dim oOuterTable As Word.Table
    Dim oInnerTable As Word.Table
    Dim oColumn1 As Word.Range
    Set oOuterTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1, NumColumns:=2)
    With oOuterTable
        .Columns(1).SetWidth ColumnWidth:=InchesToPoints(1.1), RulerStyle:=wdAdjustNone
        .Columns(2).SetWidth ColumnWidth:=InchesToPoints(5.5), RulerStyle:=wdAdjustNone
        .Cell(1, 1).VerticalAlignment = wdCellAlignVerticalCenter
        .Cell(1, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        .Cell(1, 2).Range.ParagraphFormat.SpaceBefore = 0
        .Cell(1, 2).Range.ParagraphFormat.SpaceAfter = 0
        .Cell(1, 2).VerticalAlignment = wdCellAlignVerticalCenter
        .et oColumn1 = .Cell(1, 1).Range
        .et oInnerTable = ActiveDocument.Tables.Add(Range:=oColumn1, NumRows:=1, NumColumns:=1)
    End With
    With oInnerTable
        .Cell(1, 1).VerticalAlignment = wdCellAlignVerticalCenter
        .Cell(1, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        .Cell(1, 1).Range.Font.ColorIndex = wdWhite
        .Cell(1, 1).Shading.BackgroundPatternColor = -721371137
        .Cell(1, 1).Range.ParagraphFormat.SpaceBefore = 6
        .Cell(1, 1).Range.ParagraphFormat.SpaceAfter = 6
        .Cell(1, 1).Range.Text = "WARNING"
    End With
    oOuterTable.Cell(1, 2).Select
    ActiveDocument.UndoClear
End Sub