This is cool. I just came across a commandline tool for creating and listing
file associations in windows. The
assoc command will list all the
assoc .=txtfile will make all files without extensions text
files (ie, README). Cygwin doesn’t seem to be able to find
assoc so it must
be run from a cmd window.
I finally created an add-in for Excel that includes many of the tools that I use all the time and have outlined on this site. The add-in will create a new menu in Excel and setup a number of shortcuts. Here are some of the more useful ones:
When I feel like getting fancy, it can be nice to include a bulleted list in an Excel sheet to describe assumptions, etc. This is actually pretty easy to do, but requires adding some odd characters. This macro will add a character and change the font of a cell to create a bullet. If you run this macro on a cell which already contains a bullet, an arrow shaped sub-bullet is inserted instead.
' Toggles a bullet and an arrow ' Copyright under GPL by Mark Grimes ' Keyboard Shortcut: Crtl+Shift+B ' Sub mgBullet() If ActiveCell.Formula = "l" Then Selection.Font.Name = "Wingdings" ActiveCell.FormulaR1C1 = "bullet" ' Replace the text bullet with the bullet symbole from Wingdings ' Found that others don't have wingdings 3, it's sub-bullet was better ' Selection.Font.Name = "Wingdings 3" ' ActiveCell.FormulaR1C1 = "}" Else Selection.Font.Name = "Wingdings" ActiveCell.FormulaR1C1 = "l" End If With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = xlHorizontal End With End Sub
This is one of my favorites. It saves a copy of the current file in the ‘Backup’ directory if one exists under the directory in which the file is currently saved. It saves the files with an incrementing two digit number after the filename (before the .xls extension). A cap of 50 backups is imposed just to keep from taking up too much disk space (my models tend to be BIG).
' Save a copy of the current file. ' Copyright under GPL by Mark Grimes ' Keyboard Shortcut: Crtl+Shift+S ' Will save in the "Backup" subdirectory if it exists. ' Will attempt to add an index number upto 50. ' Sub mgSaveBackup() p0$ = ActiveWorkbook.Path If Dir(p0$ & "\Backup", vbDirectory) <> "" Then p$ = p0$ & "\Backup" End If n0$ = ActiveWorkbook.Name If Right(n0$, 4) <> ".xls" And Right(n0$, 4) <> ".XLS" Then MsgBox "File must be a previously saved '.xls' file." End End If n$ = Left(n0$, Len(n0$) - 4) i = 0 Do i = i + 1 Loop Until (Dir(p$ & "\" & n$ & "." & Application.Text(i, "00") & ".xls") = "") Or (i > 50) If i > 50 Then MsgBox "No more than 50 backup's can be made." End End If response = MsgBox("File to be backed-up as:" & Chr(10) _ & p$ & "\" & n$ & "." & Application.Text(i, "00") & ".xls", vbOKCancel) If response = vbOK Then 'FileCopy p0$ & "\" & n0$, p$ & "\" & n$ & "." & i & ".xls" ActiveWorkbook.SaveCopyAs p$ & "\" & n$ & "." & Application.Text(i, "00") & ".xls" Else MsgBox "Backup aborted!" End If End Sub
When formatting a Excel sheet underlining or overlining (which appears as if you underlined the cell above) a cell often looks much better than just underlining the contents of the cell (ctrl-u). This macro will toggle the under/overlines for all the selected sells.
' Toggles Underlines ' [% coypright %] ' Keyboard Shortcur: Crtl+Shift+U ' Sub mgSetUnderline() If Selection.Borders(xlBottom).LineStyle = xlNone Then With Selection.Borders(xlBottom) .Weight = xlThin .ColorIndex = xlAutomatic End With Else Selection.Borders(xlBottom).LineStyle = xlNone End If End Sub
' Toggles Overlines ' Copyright under GPL by Mark Grimes ' Keyboard Shortcur: Crtl+Shift+O ' Sub mgSetAnOverline() If Selection.Borders(xlTop).LineStyle = xlNone Then With Selection.Borders(xlTop) .Weight = xlThin .ColorIndex = xlAutomatic End With Else Selection.Borders(xlTop).LineStyle = xlNone End If End Sub
The spam filters that we use at work, process all the messages in a particular folder to train the filter. Rather than drag and drop messages, I use the following code to move the selected or active message into the target folder. For each of the public subs, I have a toolbar button which runs the code.
I often like to have narrow empty columns between data columns just to make things look nice (cell underlining looks better that way in my opinion). This macro will prompt you for a number of columns per group and then it selects one column per group for the currently selected range (i.e. selecting A5:G5, running the macro and entering 2 would result in columns B, D, and F being selected). Then you can quickly resize those columns to make everything look real pretty.
' Select every other column ' Copyright under GPL by Mark Grimes ' Sub mgSelectEOther() Dim i, mult As Integer Dim r, cst As String mult = Application.InputBox(prompt:="Select every x columns:", default:=2, Type:=1) r = "" i = 0 For Each c In Selection i = i + 1 If i Mod mult = 0 Then If (c.Column > 26) Then ' tx = c.Column & ": A=" & Asc("A") & ", " & Int(c.Column / 26) & ", " & (c.Column Mod 26) ' MsgBox tx cst = Chr(Asc("A") - 1 + Int(c.Column / 26)) & Chr(Asc("A") + (c.Column Mod 26) - 1) Else cst = Chr(Asc("A") + c.Column - 1) End If r = r & "," & cst & ":" & cst End If Next r = Right(r, Len(r) - 1) ' MsgBox r ActiveSheet.Range(r).Select End Sub
Here are two routines that pull the formula from a note and put the formula in a note. I had a very specific need for this, but I can’t recall why now.
' Creates a formula from the Note ' Copyright under GPL by Mark Grimes Sub mgNote2Formula() For Each c In Selection.Cells c.Formula = c.NoteText Next End Sub ' ' Put the formula in the note ' Copyright under GPL by Mark Grimes Sub mgFormulaToNote() For Each c In Selection.Cells c.NoteText (c.Formula) Next End Sub
For the previous hack, I often had a hard time finding the correct folder to monitor. This bit of code will list all the top level folders for you.
' Copyright under GPL by Mark Grimes ' list folders by poping up msg box windows Private Sub ListFolders() Dim objNS As NameSpace Dim objFolders, objFolder Set objNS = Application.GetNamespace("MAPI") ' instantiate Items collections for folders we want to monitor Set objFolders = objNS.Folders For Each objFolder In objFolders MsgBox objFolder.Name Next Set objNS = Nothing End Sub
This routine combines the selected cells into one long string in the current cell.
' Combine cells ' Copyright under GPL by Mark Grimes Sub mgCombineCells() t = "" For Each c In Selection.Cells t = t & Trim(c.Formula) & " " Next t = Left(t, Len(t) - 1) ActiveCell.Formula = t End Sub