Steve Hansen : VBA, Excel

VBA to VSTO: A resolution for the new year

hansen — Tue, 09 Jan 2007 00:38:00 GMT

Recently a friend of mine asked me for help with a data management issue. Same story, different day - he had numerous Excel files (about 8-10 he tells me) with sales prospects that he needed to combine and clean. This friend is former professional water skier and national champion with a warm and sunny Florida residence on a private lake. As an avid tournament waterskier and resident of the freezing tundra that is Minnesota, I thought I'd offer a trade - I'll clean up your data (actually I volunteered my wife) and you provide some coaching in the warm waters of Florida. "Done!" he replies.

Then I get them. The files. 25 of them. 1000's of records (~30,000). No common structure. Gulp. I quickly realized that either my wife is going to be busy for a very, very long time or I better intervene and build her some tools. So, did I use all of the goodness of VSTO for this or VBA?

VBA in a heartbeat.

As much as I like VSTO and developing in Visual Studio rather than the VBA IDE, I find myself still using VBA for a lot of "ad-hoc" development tasks when time is of the essence. The reason: a decade old library of tried and true VBA code that I can draw on to accomplish tasks rapidly. Find the code (or template) I need, add the bits specific to the task at hand, connect all the dots, done. My VSTO library pales by comparison.

One of the things I'd like to accomplish this year is migrate all my "old faithful" VBA code to .NET. As I progress on this journey, I hope to fulfill another resolution - share more of what I know and learn through regular blogging and additions to Office Zealot.

Back to my immediate problem for a minute. I decided the first major task was to somehow get all of this into a database. The problem was that I didn't even know what I had - none of the files shared a layout much less common fields. One of the tools in my VBA toolkit is an Excel batch processing utility that I use whenever I need to run some VBA process against a set of workbooks. I've used and refined this for at least seven years. It's really not a lot of code, but it is robust, works exactly like I want it, and is simple to use. Just add the specific processing code and you're done. Anyway, I added grabbed this utility and added workbook processing code to collect the field information (23 of the files at least had the fields in one of the first three rows) and put it all on a common worksheet so I could try and make sense of it all.

Based on the collection of fields, I created two tables. One table contained all of the common fields. A second table was for the "miscellaneous" data that he wanted to keep "just in case". This table was merely a collection of name/value records.

Next, I used the batch processing utility to insert a data mapping row in each workbook/worksheet that needed to be processed. Finally, I used the batch processing utility again to cycle through all the workbooks and load the data.

I bring all of this up to point out the usefulness of this batch processing utility. I use this all the time to do things in minutes that would otherwise take hours or days. My journey begins with this utility...

Retrieve the login name of the current user in Excel using VBA (with Vista/Excel 2007 screencast)

hansen — Wed, 03 Jan 2007 16:43:00 GMT

How do I retrieve the login name of the current user in Excel or VBA? This has to be one of the most common questions asked by people building Office-based applications. Though Charles covered it pretty throughly a couple of years ago, I thought I'd give it some more coverage. Seems as though many people still go the API route when the VBA Environ function is so simple and handy.

The short answer is to write a simple VBA function that calls Environ:

Function UserName() As String
UserName = Environ("USERNAME")
End Function

Just for fun, I created a screencast that shows how to do this. If you're never written a VBA macro in your life, check it out as it'll show you step by step what you need to do. If you know VBA like the back of your hand but haven't checked out Windows Vista or Excel 2007, you might want to check it out to see what they look like.

Truly bad book or simple misunderstanding?

hansen — Wed, 04 Jan 2006 17:01:00 GMT

There are a lot of authors (some with books in the works) that maintain blogs that grace the pages of OfficeZealot.com including Chris Webb (book in the works), Mauro Cardarelli (book in the works), Ed Bott, Eric Carter, Andrew Whitechapel and yes, yours truly. There is even an editor – Melanie Spiller who can attest to the following: a lot of effort and eyeballs go into any book. Nonetheless, errors are bound to make it into the printed version. Also, most authors I know are perfectly happy to answer any questions readers have about a book, either responding to errors, helping clarify sections of the book, or answering other questions.

That said, on behalf of everyone who has spent ungodly hours writing a book (for not a whole lot of money I might add), I’d ask one favor of those folks who choose to leave comments on Amazon and Barnes and Noble. Please be factual and give the author a chance to clarify something if you find an error or problem. I’ll be the first to admit that when I’m learning something new, chances are that when I’m having problems it is due to incomplete knowledge, misunderstanding, or misinterpretation.

Recently, a reader of my Excel VBA book got me a bit flustered when he left borderline defamatory comments on Amazon and Barnes and Noble. To make things worse, he also contacted me and asked me his question. It’s perhaps this fact that got me so flustered considering I promptly (within an hour) and politely (I thought anyway) answered his question. I thought I did the right thing, but perhaps the reader just has an ax to grind. His email to me said:

I just got your book: “Mastering Excel 2003 Programming with VBA”, and I think it's great. I tried to implement the program you have listed on page 147 (Listing 7.9: Alphabetizing Worksheets in a Workbook). I keyed in the macro word for word, and could not get it to work. Excel could not “see” the macro if (wb As Workbook) was in the parens instead of (). So, I downloaded the example files off of dakotatechgroup.com, and tried to run listing 7.9. Although it was in the module if I engaged Alt-F11, Excel could not “see” the macro and thus not run it.

Can you tell me what I need to do to run the listing 7.9 macro?

Thank you!

The listing in question simply alphabetizes worksheets in a workbook using a simple bubble sort.

' performs a simple bubble sort to
' sort the worksheets in the workbook
Sub AlphabetizeWorksheets(wb As Workbook)
    Dim bSorted As Boolean
    Dim nSheetsSorted As Integer
    Dim nSheets As Integer
    Dim n As Integer

    nSheets = wb.Worksheets.Count
    nSheetsSorted = 0

    Do While (nSheetsSorted < nSheets) And Not bSorted
        bSorted = True
        nSheetsSorted = nSheetsSorted + 1
        For n = 1 To nSheets - nSheetsSorted
            If StrComp(wb.Worksheets(n).Name, _
                       wb.Worksheets(n + 1).Name, _
                       vbTextCompare) > 0 Then

                ' out of order - swap the sheets
                wb.Worksheets(n + 1).Move _
                    before:=wb.Worksheets(n)
                bSorted = False

            End If
        Next
    Loop

End Sub

I took the time to respond to him that since this procedure requires a parameter, it can not be executed directly from the Macro dialog box, you need to call it from another procedure such as:

Sub Test()
AlphabetizeWorksheets ThisWorkbook
End Sub

Given his email, I can only assume that his simple misunderstanding of how parameterized procedures are used and the fact that they do not show up in the Macro dialog box caused him to conclude that none of the procedures in the book worked because they wouldn’t show up in the Macro dialog box. He must have skipped over the section of the book that talked about using parameters with procedures and other basic VBA coverage.

On a side note, I would recommend that as a rule of thumb, most of the procedures in your VBA project should NOT be visible in the Macro dialog box. The only procedures that should be visible in the Macro dialog box are procedures that you want the user to directly execute. Even then, I typically hide these procedures, preferring instead to attach them to a user interface element. In order to prevent non-parameterized sub routines from showing up in the Macro dialog box you can declare the sub routine as Private such as:

Private Sub Test()
AlphabetizeWorksheets ThisWorkbook
End Sub

Alternatively, you can use the Option Private Module statement at the top of the module. Note that even if you use Option Private Module, you can still hook up user interface elements to the sub routines in the module that are not marked as Private and do not contain parameters.

Oh well. I guess there is something to be learned about this. While Amazon’s “Real-Name” feature is a step in the right direction, you still have to take these things with a grain of salt.

Anyway, here is an open invitation to Mr. Reynolds – I’m sorry my book wasn’t helpful to you. Send it back to me and I will personally refund your money including the cost of you shipping it to me:

13335 Palomino Drive Suite 204
Apple Valley, MN 55124

Part II: Analysis Services Reporting in Excel

hansen — Thu, 03 Nov 2005 16:20:00 GMT

In Part I, I discussed how to set the worksheet up, now it’s time to look at some code. Building a report consists of three logical operations: determining the correct MDX that will yield the desired results, fetching the data, and displaying the data. In this post I’ll focus on the first task – determining the correct MDX. Before I jump into that task, I’d like to show the main procedure that glues these three tasks together along with some module level information.

LISTING 1:
Option Explicit

'/ Module variable
Dim msMDX As String

'/ Connection String - modify data source property appropriately
Private Const CONN = "Data Source=;Initial Catalog=foodmart 2000;" & _
"Provider=msolap;Execution Location=3;" & _
"Default Isolation Mode=1;"

Public Enum OPERATION
    REFRESH_REPORT
    DEFAULT_REPORT
    PREVIOUS_REPORT
    REBUILD_REPORT_WS
    REBUILD_REPORT_CODE
End Enum

Public Sub DefaultReport()
GetReport DEFAULT_REPORT
End Sub

Public Sub PreviousReport()
GetReport PREVIOUS_REPORT
End Sub

Private Sub GetReport(opType As OPERATION)
    Dim cst As ADOMD.Cellset
    Dim sMDX As String
    Dim ws As Worksheet
    Dim nMonth As Integer

    On Error GoTo ErrHandler

    Set ws = ThisWorkbook.Worksheets("Report")

    '/ Build the MDX statement
    Select Case opType
        Case REFRESH_REPORT
            sMDX = ws.Range("CurrentMDX")
        Case DEFAULT_REPORT
            sMDX = BuildMDX(ws, True)
        Case REBUILD_REPORT_WS
            sMDX = BuildMDX(ws, False)
        Case PREVIOUS_REPORT
            sMDX = ws.Range("PriorMDX")
        Case REBUILD_REPORT_CODE
            sMDX = msMDX
    End Select
    SaveMDX ws, sMDX

    '/ Get the data
    Set cst = GetCellset(sMDX)
    If cst.State = 0 Then Exit Sub

    '/ Display the results
    ws.Range(ws.Range("TopLeft").Offset(1, 0), _
             ws.Range("R65536")).ClearContents
    DisplayCellset cst, ws, ws.Range("TopLeft").Offset(1, 0)

ExitPoint:
    Set ws = Nothing
    Set cst = Nothing
    Exit Sub
ErrHandler:
    If Err.Number <> 18 Then
        Beep
        MsgBox "The Following Error has occurred:" & vbCrLf & _
           Err.Description, vbCritical, " Error!"
    End If
    Resume ExitPoint
End Sub

In Part I I put two drawing objects on the worksheet called “Default Report” and “Previous Report”. These “buttons” are hooked up to the DefaultReport and PreviousReport procedures respectively. As you can see, all they do is call the GetReport procedure with the correct Operation. In Part I, I only showed off three types of Operations: DEFAULT_REPORT, PREVIOUS_REPORT, and REBUILD_REPORT_CODE (I didn’t mention this explicitly, but this type of operation occurs when you drill down on an item). You could add a simple procedure to call GetReport specifying the REFRESH_REPORT operation. All this does is build the report using the MDX stored in the CurrentMDX named range. For this example, I didn’t implement the REBUILD_REPORT_WS operation so specifying this operation results in the same functionality as the DEFAULT_REPORT operation. The idea of REBUILD_REPORT_WS is to figure out what the MDX should be based on the items on the worksheet. This allows the user to build some “free-form” reports. For example, they can just enter the stores they want to see sales numbers for (without drilling to the correct spot in the hierarchy) and the procedure figures out the correct MDX.

For the purposes of this example, I simplified the BuildMDX function and its related functions. Here is an elementary implementation.

LISTING 2: Building the MDX
Private Function BuildMDX(ws As Worksheet, bDefault As Boolean) As String

    BuildMDX = "SELECT " & _
           ColumnMDX() & _
           RowMDX(ws, bDefault) & _
           "FROM [Sales] " & _
           SlicerMDX

End Function

Private Function ColumnMDX() As String
ColumnMDX = "{[Time].[1997].[Q1], [Time].[1997].[Q2]} on columns, "
End Function

Private Function RowMDX(ws As Worksheet, bDefault As Boolean) As String
    Dim sMDX As String
    Dim nRow As Integer
    Dim sCurrent As String
    Dim sPrevious As String
    Dim sDefaultMDX As String

    sDefaultMDX = _
        "NON EMPTY { (Descendants([Store].[USA], 1, SELF_AND_BEFORE)) }"

    If bDefault Then
        sMDX = sDefaultMDX & " ON ROWS " & vbCrLf
    Else
        sMDX = "NON EMPTY { "
        nRow = ws.Range("TopLeft").Row + 1
        sPrevious = ""
        If IsEmpty(ws.Cells(nRow, 1)) Then
            sMDX = "NON EMPTY " & sDefaultMDX & " ON ROWS " & vbCrLf
        Else
            Do Until IsEmpty(ws.Cells(nRow, 1))
                sCurrent = Trim(ws.Cells(nRow, 1).Value)
                If sCurrent <> sPrevious Then
                    sMDX = sMDX & _
                           "([Store].[" & Trim(ws.Cells(nRow, 1).Value) & "]),"
                End If
                sPrevious = sCurrent
                nRow = nRow + 1
            Loop
            sMDX = Left(sMDX, Len(sMDX) - 1)
            sMDX = sMDX & "} ON ROWS" & vbCrLf
        End If
    End If
    RowMDX = sMDX
End Function

Private Function SlicerMDX() As String
SlicerMDX = "WHERE([Measures].[Store Sales])"
End Function

I could have simplified this some more by just folding everything into one or two procedures. For example, ColumnMDX and SlicerMDX simply return a static string – why break those out into separate functions? The reason I left them as separate functions is because normally you’ll want to build these dynamically as well. It would be a trivial exercise to modify these functions so that the user could select which measure they want to see (SlicerMDX) or to offer the ability to navigate across the time periods (ColumnMDX).

The RowMDX function is the only procedure that does any real work here. Most of the procedure handles the case where the MDX needs to be determined based on the items on the worksheet. As you can see, this implementation is fairly primitive – all it does is loop until the first empty cell is found, adding the value of each cell as a member name item in the row specification.

This is probably a good time to address a potential security concern. Is this code subject to a SQL Injection attack? The simple answer is yes. It would be possible to break this code by crafting a special snippet of MDX and placing it in one of the worksheet cells. On the other hand, MDX and Analysis Services is a bit different than SQL and a database. Most cubes I come across are write-only – you cannot modify the values or add new values to the cube. If you do have a cube with writeback enabled, the potential risk is much higher. Also, note that you cannot use the CommandText and Parameters.AddWithValue method that is used with SQL statements in ADO. The ICommandWithParameters interface is not supported by the MSOLAP provider. That said, I’d strongly recommend that you check any values you read off the spreadsheet before using them in your MDX. Maybe I’ll post on this subject in an upcoming post.

Though handling the drill up/down could probably be considered part of determining the correct MDX, I save this piece for a future post. Next up in this series: fetching the data and displaying the results.

Part I: Analysis Services Reporting in Excel

hansen — Thu, 27 Oct 2005 20:10:00 GMT

The main objectives in this post are to show some screenshots that demonstrate what you can expect when you complete the exercise and to do all of the preliminary setup on the workbook. The goal of this example is to show how you can create reports in Excel that consume Analysis Services data and provide basic drill up/drill down functionality.

Functionality Overview

Figure 1 shows the empty worksheet I setup to demonstrate basic drilling functionality.

Figure 1: The empty worksheet used by this exercise.

When you click “Default Report”, the report will execute a default Multidimensional Expression (MDX) statement against the Sales cube in the Food Mart 2000 database and return the results to the worksheet. MDX is to Analysis Services what SQL is to SQL Server.

Figure 2: The results of the default report.

In Figure 2 you can see the results of the default report and the MDX used to retrieve data from Analysis Services. One thing you’ll notice here is that the different levels of the Store hierarchy are displayed in a single column. This has advantages over the PivotTable method where levels are displayed in separate columns. One immediate advantage is that the fact columns are always in the same column no matter how many levels are displayed. For example, Figures 3 and 4 demonstrate the column shift that occurs in PivotTables when you drill down/up.

Figure 3: A PivotTable report showing the same data as Figure 2. Notice that Q1 and Q2 are in columns B & C respectively.

Figure 4: Drilling down in a PivotTable causes the columns to shift over to

accommodate the new level that appears. Notice that Q1 and Q2 are in columns C & D.

Back to the default report for this exercise, if you double-click on OR, the report drills down on the data underneath OR.

Figure 5: Drilling down on Oregon

Notice that the MDX from the prior report was copied to the PriorMDX range. This makes it extremely easy to provide a level of undo (aka FlashBack for you Essbase diehards). If you double-click on OR again, the report will drill up which results in the same view as in Figure 2.

Figure 6: Simple drill up/down allows users to determine the amount of detail they want

Setting up the Report Worksheet

The first thing you can do is set the worksheet up as shown in Figure 1. If you’re going to literally follow along, begin by naming the worksheet “Report”.

The code that I’ll be presenting requires three named ranges: CurrentMDX, PriorMDX, and TopLeft. You do not need to populate these cells with any data.

1) Select cell A1

2) Enter ‘Report’!CurrentMDX in the name box and press Enter.

3) Select cell A2

4) Enter ‘Report’!PriorMDX in the name box and press Enter.

5) Select cell A7

6) Enter ‘Report’!TopLeft in the name box and press Enter.

The code that I’ll present later on in the series will refer to these named ranges. The CurrentMDX and PriorMDX are just persistent storage places for MDX statements. I put them directly on the Report worksheet to keep things simple and to provide a live glimpse of the MDX that gets constructed by your code for learning (and occasional debugging J). Normally, I’d use a separate worksheet for the MDX statements and set this worksheet to xlSheetVeryHidden. The TopLeft name is just a “stake in the ground” so that the process that displays the results knows where to dump the data.

For this example, the columns returned are always Q1 and Q2. The display process will not build the column headings, so you should go ahead and enter Q1 in cell B8 and enter Q2 in cell C8.

In the upper right corner of the sheet, I placed two simple drawing objects and added the text “Default Report” to one and “Previous Report” to the other. You’ll attach a macro to each of these in the future. Alternatively, you could use a command button.

That's it for setting up the reporting worksheet. Starting with the next post, I'll go over the code required to make this happen.