Secret Agent KV’s Chops…what’s in HIS Personal Macro Workbook?

Posted on November 19th, 2013 in Posts by KV , VBA Macros - 22 comments

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.
kv_Casual

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.
kv_Smart

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.

Your email address is safe with us. Our policies

Written by Jeff Weir
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

22 Responses to “Secret Agent KV’s Chops…what’s in HIS Personal Macro Workbook?”

  1. Chandoo says:

    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…

  2. Jeff Weir says:

    How many of those macros have Kill in them, Chandoo?

  3. Kevin says:

    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

  4. Jeff Weir says:

    Kevin – see yesterday’s post.

  5. Shah says:

    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.

  6. Xiq says:

    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

    • Hui... says:

      @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

      • Xiq says:

        @Hui
        Thanks for the feedback!

      • Khushnood Viccaji (KV) says:

        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.

  7. H.Satour says:

    @secret agent KV : Thank you for the sharing :)

  8. Tonie Salzano says:

    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

  9. Ronnie says:

    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:

      walt kominski says:
      Would really be able to use your Format_Heading macro. Is it available anywhere?

      • Ronnie says:

        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

  10. Claudio says:

    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.

  11. Stephen Elliott says:

    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

  12. David Clapperton says:

    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.

    • Jeff Weir says:

      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.

Leave a Reply