VBA Assessment

Patrick O’Beirne sent me a link to Alex Palfi’s VBA Assessment. I assumed I would ace it, but I got 7 wrong. That’s 80%. I knew I was in trouble when I read the first question.

The following figure illustrates a help system in Excel.

The user can click on a tab topic and see the help for that topic. How would this functionality be achieved?

  • A:In Excel - by using conditional formatting
  • B:In Visual Basic - by detecting a SheetSelection Event
  • C:In Visual Basic - by adding an in-sheet menu.

I’m not sure what “in-sheet menu” means. There was some others that I simply had to guess on. For instance, there were two questions testing what keyboard combinations you could assign to recorded macros. I assumed that meant through the Options button on the Tools - Macros - Macro dialog box. And I honestly don’t remember what keys it limits you to. Of course I could use Application.OnKey to set it to whatever I want. Maybe I was over-thinking the question. I did not refer to Excel or anything else during the quiz, relying solely on my memory.

There are some questions that are worded such that I don’t understand them. And there are some questions that are worded such that, to me, it gives the answer away. That might just be me, though. Also, I can’t find where it tells me which questions I got wrong. I’m probably not going to look them all up, but there are few I’m curious to see if I guessed right.

I think writing an assessment like this is very difficult. This one seems to be pretty well done. While I don’t consider myself to be at the 80th percentile of VBA users, I think a passing grade on this assessment would give you an indication of someone’s familiarity with VBA.

If you take it, post your score in the comments. If you know of other online assessments for VBA, post a link.

List Custom Lists

DataPig posted about Custom Lists in Excel and his inability to delete them. I agree on all points; I’ve never needed Sun-Sat and we should be able to delete them if we want.

Here’s a macro to list all the Custom Lists to the Immediate Window.

Sub FindCustomListNumber()
   
    Dim i As Long, j As Long
    Dim sList As String
    Dim vList As Variant
   
    For i = 1 To Application.CustomListCount
        vList = Application.GetCustomListContents(i)
        sList = ""
        For j = LBound(vList) To UBound(vList)
            sList = sList & vList(j) & ","
        Next j
        Debug.Print i, UBound(vList), sList
    Next i
   
End Sub

Apparently I’ve used custom lists in the past because there are two there that I made. Number 5 is clearly payroll item types from Quickbooks, but I don’t recall why I would put them in a Custom List. And the last one is the first 9 days of every month of the year; also a complete mystery as to why I would need that.

Google Chrome

This week I made the switch from Firefox to Google Chrome. The latest stable release of Chrome supports “extensions” so all those pesky annoyances that kept me away from Chrome in the past can be fixed. My only complaint with Firefox is that I have to restart it a couple of times a day because the memory usage goes through the roof.

After a week of usage, I find that Chrome memory usage continually grows just like Firefox. Chrome seems to open web pages more quickly, but I haven’t actually measured (and I wouldn’t know how). I found one website where the navigation didn’t work with Chrome. I’m not sure what they’re using that’s causing the problem. And I can’t send you there unless you happen to have an HSA account at Mellon Bank.

The two extensions that sealed the deal for me are Type Ahead Find and Reader Background View. Type Ahead Find mostly works like Firefox’s “Search for text when I start typing” feature under Tools - Options - Advanced - General. Without this feature, I would have to quite using the Internet or write my own browser.

One nice thing (for me) about Firefox is about:config where I can fiddle with a lot of settings. For instance, I want links that create diverted tabs to open the background. Chrome doesn’t do that. The only place it really bothers me is in Google Reader. When I’m on a post and press V, I want that diverted tab to open in the background so I can read it later. The Reader Background View extension provides a Shift+V option that does that. It takes some getting used to, but I almost remember to use Shift+V every time now. Almost.

There are a few other annoyances. When I download something, a download bar appears across the bottom of the browser and the only way to dismiss it is to click a button. It should go away automatically after a time or at least give me a keyboard shortcut to dismiss it. The only Delicious extension I could find that had a keyboard shortcut uses Alt+D. I’ve been using Alt+D to go to the address bar since the Second World War II. I’m training myself to use Control+L, but after five days I’m only remembering it about 5% of the time. Yep, 1 time in 20 is not good.

I’m going to give Chrome another week to grow on me, then switch back to Firefox if I’m not sold. I think Chrome will be a fine browser for the vast majority of people. But I’m definitely one of those people who likes to tweak the seemingly mundane settings and Firefox provides that. Is anyone else using Chrome?

ISO Year

Ron de Bruin has an ISO Date page for computing ISO Week numbers, start dates, and end dates. I needed to compute the ISO year, which should just be the YEAR() function wrapped around the end date formula. But I was getting strange results for the first three days of January this year, so I modified the formulas

B2: =DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-MOD(DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-2,7)+(7*(MOD(DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-2,7)>3))
C2: =DATE(YEAR(A2-WEEKDAY(A2-1)+4)+1,1,1)-MOD(DATE(YEAR(A2-WEEKDAY(A2-1)+4)+1,1,1)-2,7)+(7*(MOD(DATE(YEAR(A2-WEEKDAY(A2-1)+4)+1,1,1)-2,7)>3))-1
D2: =YEAR(DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-MOD((DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-2),7)+
         (7*IF(MOD((DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-2),7)>3,1,0))+7)

They’re the same as Ron’s except it uses the WEEKDAY() offset in the DATE() calculations. The purpose of these is to supply a date and return the start of the ISO year, the end of the ISO year, and the ISO year number, respectively.

Perfect Training

Have you even attended a really good training session? What about it made it good?

I’d like to develop some training sessions. I get a lot out of preparing and presenting (and I hope the attendees get something too). Rather than developing training that looks like every other session I’ve attended, I thought it would be worth while to take a critical look at what I like and dislike.

One of my recent training experiences was very typical. A presenter read PowerPoint slides for eight hours. I could try to explain this more deeply in my usual acerbic tone, but I think everyone reading has been to this session. Quite simply, it sucks. I have no idea who would like this type of training, but I suspect it’s nobody. It’s just a lazy presenter, I guess.

Saul Griffith - Pop!Tech 2008

Last year, Mike Alexander and I did some training in Dallas. I wasn’t enrolled in the class, but I sat it on some of his sessions. They are terrific, but they are that way because of Mike. He can’t hand his materials over to someone else and get the same effect. He has a special ability to speak in a language that the attendees understand and he has a great sense of humor. While I’m not a wet blanket, I’m not even in the same league as Mike. Few are.

A few years ago I went to a training session in Jackson Hole. It wasn’t software training. Rather it was leadership training, or some other warm, fuzzy thing. I knew it was going to be terrible. It was the opposite of terrible. Every activity was thought provoking and engaging. We were drawing on big whiteboards or hiking through the woods or stacking rocks on top of each other. They intermixed the lessons with this physical activity and nobody was ever bored. Not for a second. Not even a little. It would be tough to do the same with an Excel class, but I need to figure out a way to introduce some level of physical activity.

Those are three training scenarios that are swimming around in my head. I’ll use the first one to remind me what not to do. Even if I can’t be as witty and funny as Mike, I can make sure I’m speaking to my audience in a language they understand, like he does. I also find his examples to be very realistic, which is important. Finally I want to be inspired by my Jackson Hole experience to try new things.

Here is what I think I want in a training session:
Road map: I want to know what we’re going to accomplish and on what schedule. I never want to wonder how long this session lasts or what we’ll cover in the next session.
Bite-sized: I want the information broken up so that nothing lasts more than an hour. Even if it’s artificially broken up, I think it’s better. Rainier Wolfcastle says that what we like about music is the notes. I think the rests are equally as important (and easier to play).
Firehose: I want so much information that I can’t possibly remember it all. And I want it delivered at a frantic pace. One problem with classroom training is that you have to move at the speed of the second slowest person in the room. There’s always one person who is in way over his head and pretty much gives up. But that second slowest guy isn’t going to give up and, therefore, he won’t let you get too far ahead. That’s good for him; less good for the lady in the front row who is playing solitaire while the rest of the people catch up. I don’t know how to solve it, but I find myself in the latter person’s role more than the former.

I think there are some givens that aren’t worth discussing. It has to be hands-on, learn as you do, learning. The facilities have to be accommodating. And various other things that we can assume.

I’m going to throw some ideas out there just to see who salutes.

  • Stand up desks - would you go to a training session where you stood up half of the day? Where could the session be held that could accommodate that?
  • Prep work - To attend, you have to submit some homework. It would ensure that your competence level was appropriate for the class. It could eliminate some of the tedium so the class could focus on important/interesting issues.
  • Group projects - Oh boy I hated group projects in college. Remember those? What if I took a 20 person class split it into four groups and gave each group a project. Then after 30 minutes, that group would “teach” their project to the other groups. Hmmm…crowdsourcing training classes.
  • Big project - If I was whipping up a training course right now I might do an hour on pivot tables, an hour on sorting, an hour on array formulas, etc. What if instead, I set up a big project, say, preparing the quarterly production reports. I’d set up all the sample data and even have a copy of last quarter’s report handy. For eight hours we prepare this quarter’s report. Along the way, we have to learn how to bring data in from Access, create a pivot table, read in a text file, and even record a macro. Maybe more engaging than if those topics are discussed discretely?

I haven’t really thought through all of those; I’m just brain dumping them to see what stinks and what doesn’t. Tell me what you love and hate about classroom training. Tell me about a really good or really bad session you attended.

MZ Tools Non-Modal Dialogs

I’ve recently rebuilt my computer, which means I get to figure out all those little settings that I’m accustomed to. Since re-installing MZ Tools, the accelerator keys don’t work on any of the dialog boxes. Take the Add Procedure dialog, for instance

With this open, I click Alt+A and instead of actuating the Add button, it opens the Add-in Manager button on the VBE toolbar. This is definitely not the way it worked for me before. Has anyone else experienced this?

Declaring API functions in 64 bit Office

Hi everyone,

The release date of Office 2010 is closing in and with this new version we’ll have a new programming challenge. Office 2010 comes in a 32 bit and a 64 bit version.
Especially API function declarations need to be adjusted for the 64 bit environment.
We’ll have to change a declaration like this one:

    Private Declare Function GetWindowLongptr Lib "USER32" Alias _
"GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long

To:

    Private Declare PtrSafe Function GetWindowLongptr Lib "USER32" Alias _
"GetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr

So how do we know whether a Long needs to be changed to LongPtr, or to LongLong, or maybe even can be left unchanged? I decided it would be useful to gather a list of declarations on my website so we have a one-stop place where we can find the proper syntax for these things.

See:

Declaring API functions in 64 bit Office

If you have some additional API functions I could include, please let me know!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

VBA Page of Pages in a Cell

In Page of Pages in a Cell, Jan Karel uses defined names and Excel 4 Macro commands to return the current page and total pages in a workbook. The problem is that it doesn’t work properly when the cell is repeated (e.g. File - Page Setup - Sheet - Rows to Repeat at Top). So I set about writing a VBA function that would do the same work.

Like Jan Karel’s, mine doesn’t work with repeated cells either. I don’t know why I thought it would. Once I was done with it and tested it, it became pretty obvious that it wasn’t going to work. Unlike Jan Karel’s, mine is really, really slow.

So why post it? Because I spent a bunch of time on it, that’s why. They can’t all be winners.

Function ThisPageNum() As Long
   
    Dim rCell As Range
    Dim vpb As VPageBreak
    Dim lVert As Long, lVertCount As Long
    Dim hpb As HPageBreak
    Dim lHoriz As Long, lHorizCount As Long
    Dim lReturn As Long
       
    Application.Volatile
   
    Set rCell = Application.Caller
    lReturn = 1 'if everything fails, return 1
   
    'If there are no page breaks, 1 will be returned
   If ActiveSheet.VPageBreaks.Count > 0 Then
        lVert = ActiveSheet.VPageBreaks.Count + 1 'the last vertical page in case
                                                 'is one more than the last page
                                                 'breaks
       For Each vpb In ActiveSheet.VPageBreaks
            lVertCount = lVertCount + 1
            With ActiveSheet
                'if the cell is left of the break, count it
               If Not Intersect(rCell, .Range(.Cells(1, 1), _
                    .Cells(1, vpb.Location.Column)).EntireColumn) Is Nothing Then
                   
                    lVert = lVertCount
                    Exit For
                End If
            End With
        Next vpb
       
        'All the same as above, just 90 degrees different
       lHoriz = ActiveSheet.HPageBreaks.Count + 1
        For Each hpb In ActiveSheet.HPageBreaks
            lHorizCount = lHorizCount + 1
            If Not Intersect(rCell, ActiveSheet.Range("1:" & hpb.Location.Row)) Is Nothing Then
                lHoriz = lHorizCount
                Exit For
            End If
        Next hpb
       
        'Convert lvert and lhoriz into real page numbers
       lReturn = ((lVert - 1) * (ActiveSheet.HPageBreaks.Count + 1)) + lHoriz
    End If
       
    ThisPageNum = lReturn
   
End Function