Yesterday, I talked about how you don’t have to know how to code in order to highly leverage VBA. All you need to know is how to Google, Cut, and Paste. As discussed then, I ‘volunteered’ KV under pain of exposure to empty the contents of his secret satchel onto the virtual table, so that we can rummage through it. So without further ado, please put your hands together and give a warm Chandoo welcome to secret agent KV.
[Secret transmission starts…]
Hello, this is my first guest post on Chandoo.org (or any Excel website for that matter), and I will try to keep it simple, but useful for our readers.
I have been using spreadsheets since 1990, and Excel since 1995 – which sort of makes me a veteran in this sphere of business applications 🙂
One of my favorite topics in Excel is – “How can I make my day-to-day tasks in Excel easier and faster ?”. In fact, this is a topic that I think about in everything to do with computers.
There are many ways one can do this in Excel, but among the more effective and scalable ones, is storing commonly used macros in your Personal Macro Workbook.
This post is about some of the stuff that I have put in my Personal Macro Workbook over the years. You can read more about how to set up a Personal Macro Workbook, in this excellent tutorial on Ron de Bruin’s website. Like nuclear war, It’s a one-time exercise. And you can easily port it to any other computers that you use – or even share it with your friends and allied spooks.
This is the first bunch of macros which I use most frequently. Hopefully I will get a chance to post some more if this post is found to be good enough 🙂
So here goes.
1: Find the value of ActiveCell within selection, or in the whole sheet
This is a very useful macro which helps to search for the value in the ActiveCell within the selected range or the whole worksheet (if only ActiveCell is selected).
Sub SearchOnActiveCellContents()
' Keyboard Shortcut: Ctrl+Shift+G
On Error GoTo NotFound
If Selection.Cells.Count > 1 Then
Selection.Cells.Find _
(What:=ActiveCell.Value, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Else
Cells.Find _
(What:=ActiveCell.Value, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End If
Exit Sub
NotFound:
MsgBox "No cells found with this cell's contents"
End Sub
As you will notice, the macro checks whether the selection is 1 cell or multiple cells, and accordingly executes the Cells.Find command.
2: Filter on value NOT equal to ActiveCell value
This is another handy macro, which filters the current column based on the value of the active cell, except that the filter is applied as “show records NOT equal to the value of the active cell”
The macro itself is a fairly simple one-line command :
Sub AutoFilterSelectionNOT()
' Keyboard Shortcut: Ctrl+Shift+K
Dim lField As Long
lField = ActiveCell.Column - ActiveCell.CurrentRegion.Column + 1
If TypeName(Selection) <> "Range" Then Exit Sub
Selection.AutoFilter Field:=lField, Criteria1:="<>" & ActiveCell.Value
End Sub
3. Show or Hide zeros in active sheet
This macro toggles the display of zero-value cells on the active sheet.
Sub Hide_Zeros()
' Keyboard Shortcut: Ctrl+Shift+Z
If TypeName(Selection) <> "Range" Then Exit Sub
ActiveWindow.DisplayZeros = Not ActiveWindow.DisplayZeros
End Sub
4: Show or Hide page-breaks in active sheet
This macro toggles the display of page-breaks on the active sheet.
Sub ShowHidePageBreaks()
' Keyboard Shortcut: Ctrl+Shift+J
If TypeName(Selection) <> "Range" Then Exit Sub
ActiveSheet.DisplayPageBreaks = Not
ActiveSheet.DisplayPageBreaks
End Sub
As the name suggests , this macro will show or hide the display of page breaks on the active sheet.
5: Display the 'GoTo special' xldialog
Quite often I find myself needing to use the GoTo Special command.
Of course, you can do it the way it was designed in Excel – press F5 to display the GoTo dialog box, and click on the Special… button. This takes one keystroke and a mouse-click; or 3 keystrokes (if you don’t use the mouse) 🙂
Or you can display the Goto > Special… dialog box (using a macro) with just 1 click of the mouse or 2 keystrokes (if you pin it on the QAT) !
Sub xlSelectSpecial()
On Error GoTo NotFound
If Selection.Cells.Count = 1 Then
MsgBox "Select more than 1 cell...", vbExclamation, "Select more cells..."
Exit Sub
End If
Application.Dialogs(xlDialogSelectSpecial).Show
Exit Sub
NotFound:
myMsgText = "No such cells found"
myTitle = "Not found"
myConfig = vbOKOnly + vbExclamation
myMessage = MsgBox(myMsgText, myConfig, myTitle)
End Sub
As you will notice, the macro has an error-checking line in case the type of ‘special cell’ you selected is not found. E.g. if you’re looking for blank cells in the selection, and all the cells in it are non-blank, the macro will display a message accordingly.
The macro also checks whether more than one cell is selected before executing the dialog. The reason for this is that if a single cell is selected, many of the options in the GoTo Special dialog box will execute on the entire ‘UsedRange’ of the spreadsheet, instead of the selected range.
If you wish, you can comment out the If … End If construct and test the macro to see what I mean.
6: Zoom-in / Zoom-out
These macros zoom in or zoom out on the worksheet, in increments of 5%.
Sub MyZoomIn()
' Keyboard Shortcut: Ctrl+E
Dim ZP As Integer
ZP = ActiveWindow.Zoom
If ZP >= 400 Then
ZP = 400
Else
ZP = ZP + 5
End If
ActiveWindow.Zoom = ZP
End Sub
Sub MyZoomOut()
' Keyboard Shortcut: Ctrl+Shift+E
Dim ZP As Integer
ZP = ActiveWindow.Zoom
If ZP <= 10 Then
ZP = 10
Else
ZP = ZP - 5
End If
ActiveWindow.Zoom = ZP
End Sub
As you will notice, will increase or decrease the zoom percentage by 5 points each time the macro is executed. The If… Then… Else… constructs are there to prevent an error if the current zoom percentage is already at the maximum or minimum level, when the macro is executed.
That’s all for this post from my side. I hope you will find it useful.
I welcome comments, suggestions for improvement & criticisms from readers on this topic, and the macros I have shared in this post.
[Secret transmission ended.]
Hey, thanks KV for sharing those shortcut-charged shortcuts. I look forward to torturing some more of that ill-gotten wisdom out of you. (While I don’t condone torture, I hate inefficient use of Excel even more. So while it’s going to hurt you more than me, it’s for the greater good.)
About the Author
KV is an undercover secret agent who spends his time rescuing the world from the crushing weight of evil, bloated spreadsheets.
His mild-mannered alter ego - Khushnood Viccaji - is a freelance professional and an expert in Management Information Systems and Business Applications with a focus on Data Management, Analytics, Transformation, Auditing, and Reporting.
Both these chaps have a flair for understanding and applying technology in business processes and an ability to present business information in many different ways. And one of them wears lycra.
23 Responses to “Secret Agent KV’s Chops…what’s in HIS Personal Macro Workbook?”
Hello KV... is this line secure...? anyways I better hurry before the evil villain eavesdrop on this.
Good job showing off your secret macros. I like 1,2 & 4. May be I will send a copy of my personal macro workbook... but again, I am not sure if it will reach you safely before the bad guys add their macros to it. I hear they are planning to implant
sub crashWindows()in to a workbook for a while...Anyways, I am out now. See you in the field sometime.
Chandoo out...
Thanks for the 'warm' welcome Chandoo, Jeff 🙂
I'll await your PMW via 'secure line' !
How many of those macros have Kill in them, Chandoo?
tks for this, this looks useful. have you got a sample worksheet with data to see these working. as I am a bit unsure of what the data should look like for some of them. Also how do you get the
' Keyboard Shortcut: Ctrl+Shift+E
to work?
Tks again
you need to add it via the macro dialog (Alt +F8) then choose the macro and click options
Kevin - see yesterday's post.
Thanks for sharing KV... I'll send a copy of my 'convenient' macro collection to Chandoo and leave it to him to decide if some of them are worth sharing out.
Here is my very first functional macro (made possible thanks to Chandoo.org)
Sub DoProper()
' Applies the "Proper" function on the selected cell or cells
' Ctrl + shift + F
Dim ActSheet As Worksheet
Dim SelRange As Range, cell As Range
Set ActSheet = ActiveSheet
Set SelRange = Selection
ActSheet.Select
SelRange.Select
For Each cell In SelRange
cell = WorksheetFunction.Proper(cell)
Next
End Sub
@Xiq
Congrats on your start in VBA
As a comment, you don't need a few of the lines
Which allows you to simplify your code to:
Sub DoProper()
' Applies the “Proper” function on the selected cell or cells
' Ctrl + shift + F
Dim cell As Range
For Each cell In Selection
cell = WorksheetFunction.Proper(cell)
Next
End Sub
This assumes that the Active sheet and range are already selected, which they must be anyway
Using the inbuilt functions like Selection is a great way of simplifying your code
@Hui
Thanks for the feedback!
This is further to Xiq's sample code (which I think was typed in, not recorded).
This is for the people who are just beginning to use VBA.
Whenever you record a macro, a lot of 'extra' code is recorded in the macro which is not strictly necessary.
E.g.
Range("A8:G8").Select
Selection.Font.Bold = True
Now this code can be shortened to
Range("A8:G8").Font.Bold = True
The point is : as you gain more experience in recording and writing macros, you should start looking for places where the code can be optimized.
Such optimization not only reduces the run-time of longer macros, it is also easier to read and more efficient to maintain in future.
@secret agent KV : Thank you for the sharing 🙂
I just needed a function to unhide a number of hidden sheets. I hadn't thought of putting these in the Personal workbook.
Thanks!!
Sub UnhideAllSheets()
'Unhide all sheets in workbook.
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub
I have a macro called "Format_Heading" that I have distributed to many people in my company. Since I import data into Excel from various sources many times a day, this simple macro really helps me. Assumption: Only one line of heading
It performs these basic functions:
1. Make top line bold
2. Place boxes around all values in top line
3. Add gray shading to values in top line
4. Autofit worksheet
5. Freezepanes at cell A2
Set a button on your QAT and assign to this macro and you're just a click away from a better looking spreadsheet.
walt kominski says:
Would really be able to use your Format_Heading macro. Is it available anywhere?
Sub Header_Format()
'
' Header_Format Macro
' Macro recorded 10/15/2003 by Ronald Wilson
'
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Interior.ColorIndex = 15
Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.CenterHeader = "&""Arial,Bold""&14COMPANY NAME"
.LeftFooter = "&8Company Confidential"
.CenterFooter = "&8&D"
.RightFooter = "&8Page &P of &N"
.CenterHorizontally = True
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 999
End With
End Sub
Thank you!
H...i.., a..l..l..,
t..h..e.. l..in..e ..it's..a..bit..distu..rbed...now it's better..
my first post here on Chandoo, I'm a secret agent from Italy and I found confirmation to my orginal suspect: "This is the best site to become awesome in Excel!"
KV your macros are very inspirational!So inspirational that I've mixed them up (6+1) to obtain a new one that increase or decrease the dimension of charachter :
Sub character_plus()
On Error GoTo notfound
Dim chr As Integer
chr = Selection.Font.Size
If chr >= 50 Then
chr = 50
Else
chr = chr + 1
End If
Selection.Font.Size = chr
Exit Sub
notfound:
Selection.Font.Size = 10
End Sub
Sub character_minus()
On Error GoTo notfound
Dim chr As Integer
chr = Selection.Font.Size
If chr <= 6 Then
chr = 6
Else
chr = chr - 1
End If
Selection.Font.Size = chr
Exit Sub
notfound:
Selection.Font.Size = 10
End Sub
I added the if error condition (taken from your 1st macro ) to avoid the problem to have cells with different character dimension inside the range selected. In this manner I make them even and then the macro works as usual (ref. your 6th macro). I'm pretty sure there're better methods out there but I found stimulating use only the code that I've learnt from your macros.
@Xiq
Xiq, as you, I've approched VBA after Chandoo's posts that have made it very clear!
A quick tip for your macro:
Sub doproper()
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula Then cell = WorksheetFunction.Proper(cell)
Next
End Sub
I've added only the if not condition to avoid that when you run the macro it pastes the formulas inside the range selected. I noticed this after I've played a bit with it.
I've always been a taker here, but here are my personal favourites: Add_IFERROR_Selection, (and very similar) Add_ROUND_Selection, AddMonths(), protection() and remove_potection().
Many thanks to the guys posting on your forums, MrExcel, Chip pearson and so many more that Google found.
Sub Add_IFERROR_Selection()
Dim myCell As Range
For Each myCell In Selection.Cells
If myCell.HasFormula And Not myCell.HasArray Then
myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ",0)"
End If
Next
End Sub
Sub Add_ROUND_Selection()
Dim myCell As Range
For Each myCell In Selection.Cells
If myCell.HasFormula And Not myCell.HasArray Then
myCell.Formula = "=round(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ",0)"
End If
Next
End Sub
this one is fun, but very helpful when puting in dates.
Public Sub Addmonths()
If ActiveCell.Value = "" Then ActiveCell.Value = "Jan"
Dim d As Date
On Error Resume Next
d = CDate(ActiveCell.Value)
If Err.Number 0 Then
With Range(ActiveCell, ActiveCell.Offset(0, 11))
.DataSeries , xlAutoFill
.HorizontalAlignment = xlRight
End With
Else
With Range(ActiveCell, ActiveCell.Offset(0, 11))
.DataSeries Rowcol:=xlRows, Type:=xlChronological, Date:=xlMonth, Trend:=False
.HorizontalAlignment = xlRight
End With
End If
End Sub
Private Sub PROTECTION()
ActiveSheet.Unprotect '(easier than puting an error handler in)
ActiveSheet.Protect ("password")
End Sub
Private Sub REMOVE_PROTECTION()
ActiveSheet.Unprotect ("password")
End Sub
Good stuff, Stephen. Love the IFERROR one especially.
I have a much more complex Protection/Unprotection routine that I'll do a post on soon.
Hi all,
New member - can't keep away from the place!
May I humbly offer a tiny contribution to the perfect undercover personal macro folder - only saves three or four clicks but I regularly find it useful.
If you enter a lot of data manually you may find yourself changing the direction of cursor travel following a return - usually down or to the right.
It's usually a case (in Excel 2010 at least) of Files - Options - Advanced - drop down arrow & select your preferred direction.
The following toggles the cursor direction between down & right -
Sub ToggleCursorDirection()
If Application.MoveAfterReturnDirection = xlDown Then
Application.MoveAfterReturnDirection = xlToRight
Else
Application.MoveAfterReturnDirection = xlDown
End If
End Sub
I have this attached to a button on my quick access toolbar so it reduces the process to a single click.
I hope someone finds this useful.
Thanks to everyone here for an excellent resource.
Hi David. Thanks for the contribution. Note that when entering data, if you push Tab instead of Enter then it moves you to the right. So no macro required.
Be sure to go back through the archives of this blog. I recently worked my way forward from the very first entry. Interesting to see just how the focus of the blog has changed over many many years.
I have a couple that I use for exporting the tabs as individual files, one for CSV file type and one for Excel File Type
I need CSV for some programs so it gets its own Macro, and the excel type is kept because it's useful
These came from different sources so they are a bit different
First as Excel
Sub SaveTabsAsExcel()
'Creates an individual workbook for each worksheet in the active workbook.
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Object 'Could be chart, worksheet, Excel 4.0 macro,etc.
Dim strSavePath As String
On Error GoTo ErrorHandler
Application.ScreenUpdating = False 'Don't show any screen movement
strSavePath = "C:\Users\anicholson\Documents\Exports\" 'Change this to suit your needs
Set wbSource = ActiveWorkbook
For Each sht In wbSource.Sheets
sht.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close 'Remove this if you don't want each book closed after saving.
Next
Application.ScreenUpdating = True
Exit Sub
ErrorHandler: 'Just in case something hideous happens
MsgBox "An error has occurred. Error number=" & Err.Number & ". Error description=" & Err.Description & "."
End Sub
And this is the one for as CSV files
Public Sub SaveWorksheetsAsCsv()
Dim ws As Excel.Worksheet
Dim SaveToDirectory As String
Application.ScreenUpdating = False 'Don't show any screen movement
SaveToDirectory = "C:\Users\anicholson\Documents\Exports\" 'Change this to suit your needs
For Each ws In ActiveWorkbook.Worksheets
ws.SaveAs SaveToDirectory & ws.Name, xlCSV
Next
Application.ScreenUpdating = True
End Sub
I took someone else's macro so I thought it would be only fair to leave a few of the ones I use