RibbonX Sample Chapter from Excel 2007 VBA Programmer’s Reference

The Excel 2007 VBA Programmer’s Reference is now on its way to the printers, due to hit the shelves on March 19th. John Green, Rob Bovey, Michael Alexander and I have been working hard with Excel 2007 to write this update to the 2002 edition. Most of the chapters have been significantly revised and we have written completely new chapters on Lists, Charts, the XML file formats, RibbonX, Data Access, Managing External Data, OLAP Data Sources and the Trust Center. To whet your appetite, Wrox Press has kindly given me permission to make the RibbonX chapter available for download from my web site. That chapter goes into quite a bit of detail about how to set up and manipulate the Ribbon using VBA.

Enjoy,

Stephen Bullen

Move a userform by dragging within the form

We’re all used to moving a form around the screen by dragging the title bar. But what if you’ve used some API calls to remove the title bar from the form? The answer is to add a little bit of VBA that allows the user to move the form by dragging within the form area itself:

‘Remember where we started
Dim mdOriginX As Double
Dim mdOriginY As Double

‘Store where we started
Private Sub UserForm_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    mdOriginX = X
    mdOriginY = Y
End Sub

‘Move the form as the mouse moves with the left button down
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Button And 1 Then
        Me.Left = Me.Left + (X – mdOriginX)
        Me.Top = Me.Top + (Y – mdOriginY)
    End If
End Sub

This code can also be easily adapted to allow users to drag controls around the form at run-time.

Enjoy,

Stephen Bullen

ThisWorkbook and the VBE Immediate Window

I’ve no idea how many of you know this little gem, but it’s something I use a great deal. If it’s common knowledge, forgive me for wasting your time!

When we’re in the VBE’s Immediate Window and code is not running, the term <em>ThisWorkbook</em> refers to the workbook that contains the VBA project currently selected in the Project Explorer. This is particularly useful when dealing with addins or hidden workbooks, as we can easily check or change their properties, or close them, directly in the Immediate window. For example, we can see exactly where an addin is loaded from by selecting it’s project in the Project Explorer and typing the following in the Immediate Window:

?ThisWorkbook.FullName

And we can close it using:

ThisWorkbook.Close

Just be careful about which project is selected at the time, as clicking a code pane window will select that project in the Project Explorer! Obviously, when VBA code is running and we’re in debug mode, ThisWorkbook refers to the workbook containing the executing code, regardless of which one is selected in the Project Explorer

Exposing VBA code through Excel objects

If we add some VBA code to a worksheet’s code module, such as:

Public Sub ShowName()
    MsgBox “I am “ & Me.Name
End Sub

we can call that procedure from a different module (but within the same project) using code like:

Sheet1.ShowName

Now, as well as exposing the ShowName method through the internal Sheet1 object, Excel also makes it available through the Worksheet object, so we can call the same routine from any module in any workbook using code like:

Workbooks(“Book1.xls”).Worksheets(“Sheet1”).ShowName

We can also use object variables to call the method, but only if they’re declared As Object (it works with .Worksheets() as that returns a generic Object type rather than a Worksheet type):

Dim objTheSheet As Object
Set objTheSheet = Workbooks(“Book1.xls”).Worksheets(“Sheet1”)
objTheSheet.ShowName

Similarly, any public procedures we add to the ThisWorkbook class can be accessed through the Excel workbook object, so we can do things like:

‘In a workbook’s ThisWorkbook module
Public Property Get Mine() As Boolean
    Mine = True
End Property

Public Sub ShowName()
    MsgBox “I am “ & Me.Name
End Sub

‘In a standard module in a different workbook
Sub ShowMyBooks()
    Dim objBook As Object
    Dim bMine As Boolean

    For Each objBook In Workbooks

        ‘Does it have a “Mine” property, returning True?
       bMine = False
        On Error Resume Next
        bMine = objBook.Mine
        On Error Goto 0

        ‘Yes, so it’s one of ours that we can do stuff with
       If bMine Then
            objBook.ShowName
        End if
    Next
End Sub

As well as being more object-orientated (for what that’s worth), this neatly avoids some of the pitfalls of using Application.Run, such the (in)ability to pass parameters ByRef. We also avoid relying on custom document properties or defined names to identify the workbook/sheet as one of ‘ours’ (both of which can easily be broken by the user).

Regards

Stephen Bullen

Support Classic VB

As I’m sure most of you know, the future of VBA and VB.NET for Office Developers is still undecided. The Visual Studio Tools for Office has introduced Office to those that have adopted .NET, but has done nothing to introduce .NET to the traditional Office/VBA developer. VSTO has also unearthed some fundamental issues that effectively prevent us from controlling Excel from .NET reliably, which have yet to be fixed. I’m sure you’re also aware of the amazing synergy between VBA and Visual Basic 6 (aka ‘Classic VB’); many VB6 code snippets can be dropped into a VBA project and used without any changes, and it’s extremely easy to move VBA code into VB6 DLLs for better performance, improved security and better handling of class modules. Looking back, we can readily see that VBA has tended to follow in VB’s footsteps and it’s not difficult to predict that the same will continue into the future.

And that gives us all a very big problem.

When Microsoft introduced .NET, development of Classic VB stopped and the product entered the support phase of its lifecycle. Development of VBA stopped at the same time; Office 2000, XP and 2003 all have exactly the same VBA IDE. Right now, we’re all developing Office-based applications using a language and development environment that hasn’t changed in five years, and will probably never be updated. What is likely to happen is that VBA (and the VBA IDE) will continue to exist in its current state and Microsoft will introduce a new way for us to use .NET to program Office – perhaps using the Visual Studio.NET IDE, perhaps creating a brand new IDE just for Office. What is critical to us all is how that is done, such that we will be able to adopt .NET and use it alongside and integrated with the millions of lines of working VBA code we already have. If VBA follows the VB6 experience, we have a bleak future.

It’s almost unanimously agreed that Microsoft got it wrong when introducing VB.NET, by not providing an easy route for all the existing VB6 developers to start to include VB.NET into their applications. Microsoft effectively gave them an “all or nothing” choice – keep working in VB6 or rewrite your application in VB.NET. (Sure, they made an attempt at a code converter and provided the ability to interop between VB6 and VB.NET dlls, but both those options could only be used by a small fraction of VB6 developers).

What you might not know is that mainstream support for Visual Basic 6 ends on March 31. To mark this event, lots of MVPs past and present have put together a petition urging Microsoft to reconsider their past mistake and reintroduce ‘Classic VB’ as a mainstream language incorporated into Visual Studio, alongside VB.NET. They point out that (unmanaged) C++ coexists happily with (managed) C# and that by supporting both (unmanaged) Classic VB and (managed) VB.NET, Microsoft would finally be preserving all the investment their customers have made in their VB6 applications, provide an up to date language and IDE for maintaining those assets going forward and (perhaps most importantly) provide a platform for the gradual and managed migration of that VB6 code to VB.NET, where (and if) that makes sense.

If Classic VB is included in the Visual Studio IDE, it would be a very small step to also support VBA and thereby provide a clear path for us all to tread, allowing us to decide when, if and how to include .NET code in our Office-based applications. Microsoft already knows that an extremely high level of VBA/VB.NET interoperability is a ‘must have’ if they want their VBA customers to adopt .NET; the MVPs’ petition suggests a way in which that can be accomplished.

The Coca-Cola company corrected their big mistake by reintroducing ‘Classic Coke’. Please sign the petition to ask Microsoft to do the same, and give ‘Classic VB’, VBA and all our existing code a future.

Thanks

Stephen Bullen

Book review – Excel 2003 Programming, A Developer’s Notebook

If you believe the hype from Microsoft about Office 2003, you’ll want to store your data in SharePoint lists rather than Excel workbooks, transfer that data between Excel and SharePoint using XML and SharePoint Web Services, use InfoPath for your data-entry forms and control the whole lot using VB.NET! This book, by Jeff Webb, does a great job explaining – and demonstrating – how all these components can fit together. Jeff uses a very “hand’s-on” style, so you’ll learn most by working through his examples, rather than reading the book cover-to-cover. The examples give a good introduction to the various components, probably just sufficient enough to get you started on your own projects. At only 275 pages, there’s neither room for a step-by-step approach, nor for detailed explanations of what’s happening behind the scenes or the gotcha’s you’re likely to encounter. In that respect, the book reads a lot like a series of MSDN articles. I think he’s just about hit the sweet spot between too much and too little information, though that will obviously depend on what you like to read. Personally, I like a book to be a reference that I can come back to, as much as a set of instructions to get me started. So for me, this was a very good book to read and work through, giving me a good appreciation of what Excel, SharePoint, InfoPath and VB.Net can do together. Once the labs are done, though, there’s little to come back to, so it doesn’t earn permanent shelf space. But I’ll be keeping my copy in storage, in case I’ve forgotten it all by the time I meet a client interested in using Excel 2003 and SharePoint like this.

Regards

Stephen Bullen