Personal.xls

I’ve been saying for years that I was going to get rid of Personal.xls and put all that code into add-ins. Well, I finally did it. Last week, Code for Excel and Outlook posted Which Macro Shortcut Keys Do You Use. That seems like a good opportunity to layout what I did.

I ended up with four add-ins: BlogHelpers, QBHelpers, UIHelpers, and VBHelpers. I had some other code that was for work only that went into other add-ins, but I don’t discuss that here.

BlogHelpers

In addition to the FTP code, I have these two procedures;

Sub ShowFormulas()
   
    Dim rCell As Range
   
    For Each rCell In ActiveSheet.UsedRange
        If rCell.HasFormula Then
            Debug.Print “<code>” & rCell.Address(0, 0) & “: “ & rCell.Formula & “</code>”
        End If
    Next rCell
   
End Sub

Sub ListFormulas()
   
    Dim rCell As Range
   
    If TypeName(Selection) = “Range” Then
        For Each rCell In Selection.Cells
            Debug.Print String(4, ” “) & rCell.Address(0, 0), rCell.Formula
        Next rCell
    End If
   
End Sub

(Note: I don’t know how to show code tags inside of code tags, so you get the escape characters that don’t escape.) The first one prints the cell address and formula for all cells on the sheet. I use that for this blog. The second does almost the same thing, but puts four spaces in front of it and only does the selection. That one is for posting at stackoverflow.com. I also have this one:

Sub CreateDownloadGraphic()
   
    Dim sURL As String
    Dim sHTML As String
    Dim sFile As String
   
    sURL = Application.InputBox(“Enter URL to download file”, “URL”)
    sFile = Mid(sURL, InStrRev(sURL, “/”) + 1, Len(sURL))
   
    sHTML = “<a href=”“” & sURL & “”“><img src=”“http://www.dailydoseofexcel.com/blogpix/DownICO.gif”” height=”“32”” width=”“32”” alt=”“”” class=”“imgnomargin”” /></a>You can <a href=”“” & sURL & “”“>download “ & sFile & “</a>”
   
    Debug.Print sHTML
   
End Sub

If I paste a URL into the Inputbox, it spits out HTML for a download graphic and links.

QBHelpers

This one opens the newest csv and creates a querytable from an Access database automatically. I changed one line in that ‘newest file’ code since I posted it. From

If fsoFile.DateCreated > dtNew And fsoFile.Type = sCSVTYPE Then

to

If fsoFile.DateLastModified > dtNew And fsoFile.Type = sCSVTYPE Then

UIHelpers

Filtering on months

Sub CopyPasteValues()

    If TypeName(Selection) = “Range” Then
        Selection.Copy
        Selection.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End If
   
End Sub

Incrementing Dates – I use this one a lot!
Formatting Taskpane
Select Adjacent Cells
Fill Series

This one copies the UNC path to the clipboard:

Sub GetUNCAddress()
   
    Dim doClip As DataObject
    Dim wb As Workbook
    Dim sText As String
   
    Set doClip = New DataObject
   
    Application.ScreenUpdating = False
        Set wb = Workbooks.Add
        wb.Close False
    Application.ScreenUpdating = True
   
    sText = Application.CommandBars(“Web”).Controls(“&Address:”).Text
   
    doClip.SetText sText
    doClip.PutInClipboard
   
    Set doClip = Nothing
   
End Sub

Get Unique List
Join Range

This is the only addin with keyboard shortcuts, to wit:

Sub Auto_Open()
   
    Application.OnKey “^%{DOWN}”, “SelectAdjacentCol”
    Application.OnKey “+^%{RIGHT}”, “FillSeries”
    Application.OnKey “^m”, “MakeComma”
    Application.OnKey “^;”, “IncrementDate”
    Application.OnKey “^+;”, “DecrementDate”
    Application.OnKey “^+v”, “CopyPasteValues”
    Application.OnKey “^1”, “ShowFormatting”
    CreateToolbars
   
End Sub

^ = Control, % = Alt, + = Shift

VBHelpers

Reset VBE State
List Userform Hotkeys
Create Parent Class

What’s in your Personal.xls?

Posted in Uncategorized

29 thoughts on “Personal.xls

  1. I use Excel 2002 at work, and these are my top 3 helpers (attached as buttons on my toolbar).

    Sub ResetExcel()
        On Error Resume Next
        With Application
            .Calculation = xlCalculationAutomatic
            .StatusBar = False
            .EnableEvents = True
            .Cursor = xlDefault
            .ReferenceStyle = xlA1
            .EnableSelection = xlNoRestrictions
            .ClearArrows
            .DisplayFormulaBar = True
            .DisplayAutomaticPageBreaks = False

            Application.DisplayAlerts = False
            wksHelper.Cells(1, 1).Value = “a” & vbTab & “b”
            wksHelper.Cells(1, 1).TextToColumns Destination:=wksHelper.Cells(1, 1), DataType:=xlDelimited, _
                TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, TrailingMinusNumbers:=True
            Application.DisplayAlerts = True

        End With
    End Sub

    I have a routine for formatting a sheet just the way I like it.

    Public Sub Format_ActiveSheet_As_List()
        Dim rng As Range

        With ActiveWindow
            If Not ActiveSheet Is Nothing Then
                .Zoom = 80
                .SplitRow = 1
                .FreezePanes = True
                With .ActiveSheet
                    .Rows(1).Font.Bold = True
                    .Cells.EntireColumn.AutoFit
                    For Each rng In .UsedRange.EntireColumn
                        If rng.ColumnWidth < 15 Then rng.ColumnWidth = 15
                    Next
                    .Cells(1).Select
                End With
            End If
        End With
    End Sub

    Formatting a column according to their datatype (there are many functions, one for each major datatype) Date, DateTime, Currency, Number, Text

    Public Sub Format_Selected_Column_As_DateTime()
        If TypeName(Selection) = “Range” Then
            With Selection.EntireColumn
                .NumberFormat = “dd-mmm-yyyy hh:mm:ss”
                .HorizontalAlignment = xlRight
            End With
        End If
    End Sub
  2. Something I use fairly often now when I open a workbook in 2003 after having worked on it in 2010.

    Sub RemoveStyles()
    ‘ RemoveStyles – for use when importing sheets from other workbooks
    ‘ that may contain unwanted styles
    ‘ 27/07/99 by Ed Ferrero
       Dim styl As Style

        ‘ deletes all but the four common styles
       For Each styl In ActiveWorkbook.Styles
            If styl.Name <> “Normal” And styl.Name <> “Comma [0]” _
                And styl.Name <> “Comma” And styl.Name <> “Currency” _
                And styl.Name <> “Currency [0]” And styl.Name <> “Percent” _
                Then
               
                styl.Delete
            End If
        Next

    End Sub

  3. Nothing in Personal.xls – everything in an Addin called MyTools.xla that has grown over the years

    1) Ctrl+L – Toggle Filter
    2) Ctrl+Shift+L – Advanced Filter
    3) Ctrl+J – Show All
    4) Ctrl+M – Make values (Works on multiple areas as well)
    5) Ctrl+Shift+G – Goto Special
    6) Ctrl+Shift+N – Apply normal style(remove all formating)
    7) Ctrl+Shift+K – Fill blanks with cell above
    8) Ctrl+Alt+K – Fill series (Works for numbers as well as custom lists)
    9) Ctrl+Shift+A – Select All data
    10) Ctrl+Shift+R – Remove hyperlinks
    11) Shift+Alt+Right/Down arrow – Extend to last column/row
    11a) Ctrl+Alt+Down – Extend to last adj column
    12) Ctrl+Shift+V – Paste exact Formula
    13) Ctrl+Shift+T – Paste Transpose and Link
    14) Ctrl+ T – Go to the Top sheet
    15) Ctrl+ E – Go to the End Sheet
    16) Ctrl+q – Display a list of sheets – This ones the most challenging to code – We need to check not only the version of excel but also the version of windows!
    17) Alt+N – Display list of Windows(Open files)
    18) Alt+S+ K/F/E/N/T/V – Quick select BlanKs/Formulas/Errors/Numbers/Text/Validation
    19) Alt+R – Apply Dynamic Range Names based on Headers selected
    20) Ctrl+Shift+Z – SynchroniZe Sheets
    21) Alt+Right Arrow/Left Arrow – Expand/Collapse a Subotal list by one level
    22) Alt+UpArrow – Remove subtoals
    23) Ctrl+Shift+D – go to Direct dependents on another sheet
    24) Ctrl+Shift+I – Insert sheet to the right
    25) Cltr+Shift+W- Insert multiple worksheets to the right

  4. @Ed – why check each name,

    For Each styl In ActiveWorkbook.Styles
    If styl.BuiltIn = False Then styl.Delete
    Next

  5. Doug: wksHelper is the one and only sheet in my addin. It’s blank.
    I have a naughty program that changes Excels text to columns defaults. It causes tab delimited text from the clipboard to paste in the same cell.

  6. Quicker & doesn’t sully the clipboard:

    Sub CopyPasteValues()

        If TypeName(Selection) = “Range” Then
            Selection.Value = Selection.Value
        End If
       
    End Sub

  7. Most used:
    – a dialog/macro combo for selecting multiple worksheets then unhiding them;
    – a dialog/macro combo for regular expression find & replace;
    – a macro which swaps cell contents and cell comments (mostly to store external reference formulas in comments);
    – a macro which toggles inserting/deleting a | before the first = in cells containing formulas;
    – a macro which resets column widths to standard width.

    And far & away the most useful statement in my Personal.XLS’s Workbook_Open event is the one which maps the [F1] key to opening my saved copy of the Excel 97 help files rather than the @#$% help pane in Excel 2003.

  8. @Jon,
    1) Selection.Value = Selection.Value is Slow on large Datasets. Try selecting the entire sheet and run your macro.
    2) Wont work for multiple areas
    3) Will need to include check for 8192 areas for Excel 2007 and below

    Sub Make_Values()
    Dim cRng As Range
    Dim aCount As Long

    With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    End With

    If Application.Version 1 And Selection.Areas.Count < aCount Then
    For Each cRng In Selection
    cRng.Value = cRng.Value
    Next cRng
    Else
    With Selection
    .Copy
    .PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
    End If

    With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With

    End Sub

  9. oops missed a few lines

    Sub Make_Values()
    Dim cRng As Range
    Dim aCount As Long

    With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    End With

    If Application.Version 1 And Selection.Areas.Count < aCount Then
    For Each cRng In Selection
    cRng.Value = cRng.Value
    Next cRng
    Else
    With Selection
    .Copy
    .PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
    End If

    With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With

    End Sub

  10. Sub Make_Values()
    Dim cRng As Range
    Dim aCount As Long

    With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    End With

    If Application.Version < 14 Then
    aCount = 8193
    Else
    aCount = 8589934592#
    End If

    If Selection.Areas.Count > 1 And Selection.Areas.Count < aCount Then
    For Each cRng In Selection
    cRng.Value = cRng.Value
    Next cRng
    Else
        With Selection
        .Copy
        .PasteSpecial Paste:=xlPasteValues
        End With
        Application.CutCopyMode = False
    End If

    With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With

    End Sub

    .

  11. I keep my tools in a menu called myBar. One routine that I used a lot saves the current file and then makes a copy on an attached USB, building the equivalent folder structure if it’s not already there. I recently wrote a VB.Net utility to replace this that detects USBs as they’re plugged in, browses files or folders to back up, and keeps them as choices for next time.
    Another tool from years ago is a form for resizing columns, complete with slider, Shift key accelerator (for those really wiiide columns) and a “Size to Largest” button. I made it so that people could resize columns on protected worksheets, which wasn’t really necessary after XL XP (I think) added “format columns” to the protection dialog.

  12. @Dick:

    We have a winner:

    maps the [F1] key to opening my saved copy of the Excel 97 help files

    My eyes lit up too!

  13. fzz: And far & away the most useful statement in my Personal.XLS’s Workbook_Open event is the one which maps the [F1] key to opening my saved copy of the Excel 97 help files rather than the @#$% help pane in Excel 2003.

    I love that! I always believed I wasn’t alone in thinking that the Help gets worse with every release.

  14. My personal.xls has accumulated a load of crap that I should also think about moving to a dedicated add-in.

    GeneralTools
    Bits of code that either power my own toolbar, or that I call from/copy into projects as and when I need them. Things like:
    * A VBA state rest (ScreenUpdating, Calculation, EnableEvents, DisplayPageBreaks)
    * Automatic freezing & filtering of row 1
    * Toggling of row/column headings
    * A workflow for creation of dynamic ranges
    * A process that updates a read-only workbook, in the process archiving off the current version to a central location
    * A simple Twitter updater
    * Name rearrange function that turns ‘Dick Kusleika’ into ‘KUSLEIKA, Dick’ or vice versa – used a lot here
    And a few other bits and pieces that are specific to some of the projects I work on.

    ThirdPartyTools
    Bits of code for the commandbar that aren’t my own work. Kept separate so I don’t forget and try and pass them off as my own at some point in the future.
    * WiteComments by Nick Hodge – prints all comments into a separate file
    * MakeLastCell by David McRitchie – Resets the last used cell in a workbook – so handy!
    * AllInternalPasswords by Bob McCormick, Norman Harker & JE McGimpsey

    SQL
    All the subs & functions that interface with databases. I have an xlt that I use as a base for any project that uses SQL, but for some reason I duplicate them here? (need to sort this)

    TempTest
    Trial code that I may or may not end up integrating into the main sections.

  15. Dick wrote: “I’ve been saying for years that I was going to get rid of Personal.xls and put all that code into add-ins. Well, I finally did it.”

    Congratulations! You are all grown up now. {gdr}

  16. Sorry guys, as this is a real noob question, but would someone explain the benefits of using add-ins over personal.xls, and how you use it once created? Do you have to run it on startup?

    Sorry for being so basic; I’ve been doing VBA for a while, but not dipped into add-ins yet.

    Thanks for any tips and/or links to help me learn more.

  17. You don’t have to run anything on start-up.

    When Excel starts it automatically opens all files in its general start-up folder and your personal start-up folder. The general start-up folder is usually the subdirectory named XLSTART in the directory which contains EXCEL.EXE. You set your start-up folder in Excel options. Excel also open files for which there are Open* values under the HKCUSoftwareMicrosoftOffice11.0ExcelOptions key in your registry, which is how most add-ins are loaded. If you clear out the start-up folders and delete Excel’s Open* registry keys, Excel won’t run anything automatically.

    Macros in add-ins don’t appear in the Macros dialog, which has its pluses and minuses. You’d need to use an add-in’s own Open event handler to assign keyboard shortcuts (via Application.OnKey) to macros in the add-in. Alternatively, you could create menus or submenus within the worksheet menu or separate toolbars to access add-in macros. OTOH, udfs in add-ins may be used without prefixing the function name with the file and module names.

    So add-ins are a bit more work for macros, but are much more convenient for udfs. Add-ins are also a bit more secure, but still not truly secure (nothing which can be opened without a password is truly secure in Excel). Still, it’s a bit like deciding between defining environment variables in CONFIG.SYS or AUTOEXEC.BAT back in the old days.

  18. @Toby

    You can install an add-in from a central location rather than from the user’s default add-in folder. This allows you to update the add-in and have all users working off the most recent version available when they opened Excel.

    If you absolutely need them to be using the most up to date version then you can store a current version number in a database table or text file somewhere and have the add-in check it is up to date and warn/disallow progress until it is reloaded.

  19. I am new to excel macro’s. Have been reading Jelen’s Macro’s & VBA for Excel 2007. I am running into some very strange behavior before I even start. After I name the macro and give it a short cut, if I ask it to save to my personal macro workbook. I get a password prompt titled VBAProject Password. I haven’t ever recorded a macro so I don’t think I have password protected anything. Any suggestions would be appreciated.

  20. So I was inspired by y’all to create my own .xla using Excel 2003 which I use at work and home.

    I have a couple of macro’s that I use all the time and one or two that are workbook specific. But instead of having hot keys I add them as menu items…

    Sub Auto_Open()

    On Error Resume Next
    AddErixMenu

    End Sub

    Sub AddErixMenu()

    ‘This subroutine adds in the ‘Erix’ menu item to the ‘Worksheet menu Bar’ commandbar

    Dim myMenuBar As CommandBar
    Dim newMenuControl As CommandBarControl
    Dim ctrl1 As CommandBarControl

    On Error GoTo AEM_Err

    DeleteErixMenu

    Set myMenuBar = Application.CommandBars(“Worksheet Menu Bar”)
    Set newMenuControl = myMenuBar.Controls.Add(Type:=msoControlPopup, _
    Temporary:=True)
    newMenuControl.Caption = “Eri&x”

    ‘Delete Erix Menu
    Set ctrl1 = newMenuControl.Controls _
    .Add(Type:=msoControlButton, _
    ID:=1)
    ctrl1.Caption = “&Delete Erix Menu”
    ctrl1.TooltipText = “Gets Rid of the Erix Menu Item”
    ctrl1.Style = msoButtonCaption
    ctrl1.OnAction = “DeleteErixMenu”

    ‘Copy the above ctrl1 lines for additional menu items

    GoTo AEM_Exit

    AEM_Err:
    MsgBox “Unknown Error in Subroutine AddErixMenu”, _
    vbOKOnly, _
    “Error Report”

    AEM_Exit:

    Set ctrl1 = Nothing
    Set newMenuControl = Nothing
    Set myMenuBar = Nothing

    End Sub

  21. Oh yeah, there’s a DeleteErixMenu sub that makes sure that the menu is gone before the latest version is put in ~ Eric

Leave a Reply

Your email address will not be published. Required fields are marked *