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

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:

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

Enjoy,

Stephen Bullen

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:

And we can close it using:

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

David Gainer, the Group Program Manager for Excel at Microsoft, has just started a blog about Excel 12, in which he’ll be introducing all the new features that Excel 12 will bring. See it now at http://blogs.msdn.com/excel/default.aspx

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

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

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:

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):

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

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

A (great!) review of Professional Excel Development was posted on Slashdot yesterday and has triggered a very interesting discussion about the pros and cons of Excel development, and why people would create applications using Excel. Take a look.

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