VBA Hacks and Trickery Grab Bag

Hi, I’m here to torture you with more VBA! Run away! Run away! This is part two of the tales from the Programmer’s Purgatory. And once again, no it is not hell. When you are in Programmer’s Hell you spend all your time in meetings, and/or doing team building exercises *shudder*.

This time I’m unloading few random snippets of code that I produced during my sentence in the purgatory. For example, there is a vba macro that is supposed to extract some data from an excel file with ~20 worksheets. The users need to be able to add new worksheets to it, or move and modify existing ones (that is an absolute requirement, and arguing about it is pointless) so you can’t really protect the workbook. There are really two ways to access any given sheet:

  1. By it’s name: ThisDocument.Sheets(“Accounts Receivable 10″)
  2. By position: ThisDocument.Sheets(10)

There is of course an issue here. If you try to call worksheets by name, the users will promptly rename every single one. If you try to get it by position, they will rearrange the whole workbook. It is a lose-lose situation and almost every day our helpdesk has this very conversation:

luser: “Hi, the import macro is broken”
helpdesk: “Did you rename any of the worksheets in your excel file?”
luser: “Well, yes…”
helpdesk: “Ok, did you see the big warning in red letters that says DO NOT RENAME OR DELETE THIS WORKSHEET on top of each sheet?”
luser: “Well, yes…”
helpdesk: “So why did you rename them…”
luser: “Well… You know… So, um… What do I need to do now?”
helpdesk: “Rename them back?”
luser: “Ok, I’ll email you the file and you do it for me cause I don’t know how to do that”
helpdesk: *shoots self*

We decided to be tricksy Hobbits and added the following snippet to every single sheet in the document that should not be renamed:

Sub Worksheet_Deactivate()
    Me.Name = "The Name of the Worksheet"
End Sub

It’s funny because it is so subtle. They can rename the worksheet all they want, but as soon as they click on another sheet the name will just quietly revert to what it was supposed to be in the first place. No matter what they do to it, it won’t keep the new name. I imagine they will eventually give up, or call the Helpdesk and then we can tell them in our best BOFH voice “Read what it says in red on top of the worksheet. Do not rename!”

Of course techno-idioticus luseratis is the most ingenious animal on the planet. While they are unable to perform the simplest tasks in Windows or Office without assistance, they show almost limitless resourcefulness when it comes to circumventing the tricks we use to stop them from breaking things.

So if they can’t rename the worksheet they will delete and recreate it or do something equally silly. So we came up with an insidious way to make their life harder. Naturally, it couldn’t be easy because the fuckers at Microsoft decided that there is no reason to fire some sort of an event when a worksheet is deleted. I mean, who would ever want to capture such an insignificant event? No one, that is who.

So there is no real way of preventing the user from deleting a worksheet other than protecting it, or protecting the workbook but that naturally won’t work for two reasons. One reason is that they would often need to un-protect it in order to add/modify certain sheets. The second reason is that most of the time you can remove protection using tools like this one.

So we figured that we’ll just disable the Delete button on relevant Sheets. The user will still be able to get in trouble by moving the worksheet to another workbook, but at least the most obvious way to delete things will be disabled. This is a bit convoluted, but I blame Microsoft for not making a Worksheet_Delete method.

' Put this in it's own module or Workbook module
Sub DeleteButton(ByVal enable As Boolean)
  Dim CommBarTmp, Commbar As CommandBar
  For Each Commbar In Application.CommandBars
    Set CommBarTmp = Commbar.FindControl(ID:=847, _
       recursive:=True)
    If Not CommBarTmp Is Nothing Then _ 
      CommBarTmp.Enabled = enable
  Next
End Sub
 
' On each sheet put the following two subroutines:

' When browsing away, enable the Delete button
Private Sub Worksheet_Deactivate()
    DeleteButton True
End Sub
 
' When activated, disable the Delete button
Private Sub Worksheet_Activate()
    DeleteButton False
End Sub
 
' Add these two in the Workbook module:

' Enable the button before closing excel
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DeleteButton True
End Sub
 
' Enable the button when you switch to another workbook
Private Sub Workbook_Deactivate()
    DeleteButton True
End Sub

Note that you could use the Workbook_SheetActivate method to activate this behavior for all worksheets instead of using separate Worksheet_Activate and Worksheet_Deactivate methods. We didn’t do that because only some sheets need to be protected this way, while others are free game.

Finally, here is another frequent Helpdesk conversation that happens at least twice a day:

luser: “Um… Is there a way to make Word recover a document that wasn’t saved?”
helpdesk: “Not sure what you mean… What happened?”
luser: “Well, I was working on this file, then I closed Word and it asked me if I want to save changes so I clicked no. Now all my changes are gone. I lost 6 hours of work.”
helpdesk: “Why did you click on no?”
hluser: “Um… Because I realized that I forgot to save the document and wanted to go back to do Save As”
helpdesk: *bangs head against the keyboard*

So here is yet another trick – we will forcefully save the document even if the user resists:

' Put all of these in ThisDocument module
Sub AutoOpen()
    WaitAndSave
End Sub
 
' Wait 5 minutes
Sub WaitAndSave()
    Application.OnTime Now + TimeValue("00:5:00"), "Saver"
End Sub
 
' Save the doc and wait again
Sub Saver()
    ThisDocument.Save
    WaitAndSave
End Sub

This way, the lusers can’t claim they lost 5 hours of work because Word sucks or because some macro crashed their Office or because there was a power outage. Now they can only lose up to 5 minutes of real work. I’m also considering combining this with the versioning feature of Word 2003 (and higher). This way if the user fucks something up they will be able to roll back their changes in 5 minute increments.

I hope you enjoyed this brief excursion into the terrifying world of madness which is VBA. Let’s hope there won’t be part 3, because even right now I feel that my sanity is slipping.

This entry was posted in programming and tagged . Bookmark the permalink.



5 Responses to VBA Hacks and Trickery Grab Bag

  1. jambarama UNITED STATES K-Meleon Windows Terminalist says:

    Oh man I hear you. At one of my jobs, once people found out I wasn’t a tard with programming, they asked me to write all kinds of excel macros. One particular annoyance I had was with people with high security settings who wouldn’t turn them down (even with explicit directions on how to do it) then complain that it isn’t working. I wrote this beauty to combat that problem. Basically if you set it to autorun, when the user closes the xls file thsi macro hides every worksheet except one with a warning telling them to enable macros. Then when the next person opens it, if macros can run – this unhides everything, if macros can’t run, all the user will see is the warning telling them how to enable macros.

    Option Explicit
     
    Private Sub UnhideSheets()
        Dim Sheet As Object
        .ScreenUpdating = False
        For Each Sheet In Sheets
            If Not Sheet.Name = "InfoSheet" Then
                Sheet.Visible = xlSheetVisible
            End If
        Next
        Sheets("InfoSheet").Visible = xlSheetVeryHidden
        Set Sheet = Nothing
        ActiveWorkbook.Saved = True
        .ScreenUpdating = True
    End Sub
     
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        With Application
            .ScreenUpdating = False
            Call HideSheets
            .ScreenUpdating = True
        End With
    End Sub
     
    Private Sub HideSheets()
        Dim Sheet As Object
        With Sheets("InfoSheet")
             If ThisWorkbook.Saved = True Then .[Q200] = "Saved"
            .Visible = xlSheetVisible
            For Each Sheet In Sheets
                If Not Sheet.Name = "InfoSheet" Then
                    Sheet.Visible = xlSheetVeryHidden
                End If
            Next
            If .[Q200] = "Saved" Then
                .[Q200].ClearContents
                ThisWorkbook.Save
            End If
            Set Sheet = Nothing
        End With
    End Sub
    Reply  |  Quote
  2. jambarama UNITED STATES K-Meleon Windows Terminalist says:

    Right, I forgot to paste in the trigger subs. These had a lot of other stuff in them that I had to remove (it was particular to the data) but this is the gist:

    Private Sub Workbook_Open()
        With Application
            Call UnhideSheets
        End With
    End Sub
     
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        With Application
            Call HideSheets
        End With
    End Sub
    Reply  |  Quote
  3. jambarama UNITED STATES K-Meleon Windows Terminalist says:

    Sorry to post so much, but it looks like my code brackets didn’t include everything within, so the formatting & such is fugly. My apologies.

    Reply  |  Quote
  4. Luke Maciak UNITED STATES Mozilla Firefox Ubuntu Linux Terminalist says:

    @jambarama: No worries – I fixed it. The code tags do not get the special formating on here. See that little “Code” button above the edit box? If you click it it will insert

    <pre lang="java">

    into your comment. You can change the lang parameter to a specific language (in this case “vb”) and the WP-Syntax plugin will pick it up and apply appropriate highlighting. If your language is not supported by WP-Script it will not apply any color highlighting, but it will maintain indentation and escape < and >. Once you are done just close the pre tag and you are all set. That’s how I do all my code snippets here.

    I have yet to figure out whether or not I can force WP-Syntax to work with the code tags.

    Oh, and you are a hero for making this. I love it! :)

    This whole macro security bullshit is annoying. Everyone wants to have macros in their spreadsheets! But every time we send out a memo showing people how to lower the macro security so that they can run them, someone gets on a soapbox and cries that his brothers’ second cousin’s former room mate allegedly said your macro security must be set to High at all times, no matter what and that we must be bunch of talentless hacks and clueless retards for recommending otherwise.

    Can’t win. :P

    Reply  |  Quote
  5. jambarama UNITED STATES Epiphany Linux Terminalist says:

    Syntax highlighting, what a fancy WP plugin! I’ll remember that next time I post a chunk of VB. Actually, since I haven’t touched VBA (or any coding) in a year and a half (since starting grad school), I’m glad some of my old stuff might be still useful somewhere.

    I know my old company is still using a metric ton of VBA I wrote for the finance guys, the management, the travel coordinators, and the administrative assistants, but I hope the coders in the company have replaced it with apps more suited to the tasks.

    Anyhow, thanks for the VBA posts, they take me back to a darker time… ;)

    Reply  |  Quote

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>