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;
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:
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
to
UIHelpers
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:
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
This is the only addin with keyboard shortcuts, to wit:
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?
I use Excel 2002 at work, and these are my top 3 helpers (attached as buttons on my toolbar).
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.
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
If TypeName(Selection) = “Range” Then
With Selection.EntireColumn
.NumberFormat = “dd-mmm-yyyy hh:mm:ss”
.HorizontalAlignment = xlRight
End With
End If
End Sub
Rob, I’ll bite. What kind of sheet is wksHelper and why the fancy footwork to put an “a” in A1 and a “b” in B1?
Something I use fairly often now when I open a workbook in 2003 after having worked on it in 2010.
‘ 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
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
@Ed – why check each name,
For Each styl In ActiveWorkbook.Styles
If styl.BuiltIn = False Then styl.Delete
Next
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.
Quicker & doesn’t sully the clipboard:
If TypeName(Selection) = “Range” Then
Selection.Value = Selection.Value
End If
End Sub
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.
@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
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
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
.
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.
We have a winner:
maps the [F1] key to opening my saved copy of the Excel 97 help files
@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!
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.
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.
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}
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.
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.
Thanks, that’s a great help.
How can i share (or sync) VBA macro between users (PCs)?
Thanks
@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.
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.
[…] problems on the job. His blog posts inspire lots of discussion, and his post on moving from the Personal.xls workbook to personal add-ins was one of my favourites. Dick’s sample code was great, and there are […]
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
Oh yeah, there’s a DeleteErixMenu sub that makes sure that the menu is gone before the latest version is put in ~ Eric
Eric: Yeah, using menus/ribbon & userforms is the way to go. I am constantly amazed at the amount of energy some invest in memorizing shortcut keys.
Hi,
how can I assign a VBA macro from .xla file to the Quick Access Toolbar in XL 2007 or 2010?