Search results for 'app:weblogs' matching tag 'excel'

Pride Dispensed, Pride Promised, And Pride To Go

bobphillips — Sat, 10 Oct 2009 05:00:00 GMT

Normal 0 false false false MicrosoftInternetExplorer4

Just got back from London yesterday after the UK Excel Conference.

A successful couple of days I think, we had a large audience, few no-shows, and a general good time.

As this was organised mainly to avoid disappointing the many wait-listed people from the previous, April, conference, the agenda was largely the same as before. We had one new session on ‘Excel as a BI Tool’, which I think that many delegates found enlightening; to see Excel being used as an Enterprise reporting tool, querying huge amounts of data very quickly was an eye-opener.

It was good to see the presenters again, and we had a good first evening prior to the conference - at least until Roger persuaded me to have a pint of Timothy Taylor's which was not nice, especially after the Pride!

As would be expected with an audience of circa 100 people, we had a mixed bunch. There were some company men/women, some consultants; bankers, auditors, and tax inspectors; a guy who works for Apple; we even had a pig consultant – who would have thought that a pig consultant is a big Excel user.

Another good dinner at Zsi-Zsi’s with some of the delegates; a decent Chardonnay at Ha-Ha’s’ on the last evening; a good meal at a most unexpected looking Indian restaurant in Victoria that Simon, Andy and I went to; and good hosting from Microsoft.

To cap it all, I missed the last train home because I was too busy talking with Simon, I got on the wrong tube line.

Class Conscious

bobphillips — Fri, 02 Oct 2009 05:00:00 GMT

Normal 0 false false false MicrosoftInternetExplorer4

This is going to be the first in a series of items about Code Generation, automatically generating your code. I have posted a couple of times previously on this topic, here and here, but I thought a series of posts would be useful, so here is the first.

The Last One

I remember back in the 80s a UK company built a program called ‘The Last One’, its premise being that it was the last computer program because it would write every program from thereon after. Now, after you have picked yourself up off of the floor and stopped laughing, think about it somewhat objectively. Remember, this was in the days of largely batch programs, they started at the beginning, went through a file of instructions or updates, and reacted to each item encountered. As such, with some very clear rules, it shouldn’t be too hard to write a program to read those rules and then process the data files according to those rules. Of course, it would all soon get messy, the types of rules are infinite, and the types of data is also infinite, so some severe constraints would have to be imposed, but wouldn’t a program that can handle multiple applications, even if those applications have to adapt somewhat to that program, be beneficial (of course, you could say this is exactly the same as the SAP paradigm, but please, let’s not talk about SAP when I am feeling good). Event driven applications add a whole new, more difficult dimension of course, but still…

Automate It

I am a great fan of code generation, using it frequently, and always look for further opportunities to use it. Why do I think it is so good? There are many reasons:

maintenance is reduced, make a small change and rerun and all code affected by that change will be re-generated
the quality is improved, all changes are re-generated, no need to remember what needs changing
the work is done in the more critical and interesting areas, in analysis and design.

To me that third point is the most important. Code generation makes you think about the application requirements and how they should be implemented, something you should do by right, but something that cannot be avoided or done half-heartedly when using code generation. As the design is so critical, and as I find it more interesting than cutting code, this suits me perfectly.

The greatest exponent of code generation that I am aware of is Kathleen Dollard. Her book, Code Generation in Microsft .NET, is the definitive work on the subject, a remarkably comprehensive work that provide end-to-end solutions using XSLT-based code generation based upon a series of templates. As she says … Code generation will turbo-charge your development cycles by offering speed, reusability, agility, and consistency.

Whilst this is a remarkable piece of work, I think that many might find it daunting, even off-putting. The solution is comprehensive, but it is based upon .NET and XML, and dare I say, can even been too purist for the tyros amongst us. For this reason, I will address code generation from a much simpler starting point, show how you can use code generation today using Excel, and build upon it over the next few weeks,

Excel & Code Generation

What I have described so far are full-blown applications, with their attendant complexity, which might discourage you from going any further. But let’s step back a bit, and think about ways in which we can generate code in small doses. Later items will go further, but we can show immediate benefits. In these examples, we will use Excel as the code generating engine. Excel is a superb tool for this, as it is with so many other things, with the grid for our data input, and VBA to handle any complex code generating algorithms.

You may not be aware of it, but maybe you are already using Excel to do code generation. If you ever have a list in Excel, and use formulas and functions to create some more descriptive text that you cut and paste into a code module, that is a simple form of code generation.

Let’s take a simple example. I use class modules a lot, and I find that creating properties is a pain, you need to define a private variable where the actual value is stored, and then Get and Let properties so that the property can be read from and written to in the class (that is assuming two things, your property is read/write, they may not all be so; that you don’t use Public variables for read/write properties – I don’t, I always declare Get and Let).

Using code generation, all we need are two pieces of information, the property name and its data type. If we want to cater for some properties being read only, we need a third denoting the access mode.

So, we can create a spreadsheet with 3 columns

- column A - property name – free format text

- column B - data type – Long, Double, String, Boolean, Range (should suffice for now) – an ideal candidate for a data validation list

- column C - access mode – R, W, or RW – again another dv list

It would be wise to add a fourth, column D, derived column, that takes the property name and replaces any spaces with _, and removes any special characters, as this will be used in the class.

With a few simple formulae, we can easily generate all 3 elements of the property that the class needs,

- Private Variable

="Private m_"&D2&" As "&D2

- Read Property

=IF(ISNUMBER(SEARCH("R",$C2)),
"Public Property Get "&$D2&"() As " &$B2&CHAR(10)&
" "&$D2&" = m_"&$D2&CHAR(10)&"End Property",
"")

- Write Property

=IF(ISNUMBER(SEARCH("W",$C2)),
"Public Property Let "&$D2&"() As " &$B2&CHAR(10)&
" m_ "&$D2&" = "&$D2&CHAR(10)&"End Property",
"")

Correction

=IF(ISNUMBER(SEARCH("W",$C2)),
"Public Property Let "&$D2&"(ByVal val As " &$B2&")"&CHAR(10)&
" m_"&$D2&" = val"&CHAR(10)&"End Property",
"")

That is all there is to it. Create a list of properties with their attributes, and Excel generates the class code for you, all you need to do is cut and paste it into the class module.

Next time, we will show how to use VBA to write back this data into the class code, after all, one of the primary aims of code generation is to save us from the boring bits. We will also look at a slightly more complex generation.

For RSS subscribers, this code is attached.

xldynamic Returns

bobphillips — Mon, 28 Sep 2009 05:00:00 GMT

Normal 0 false false false MicrosoftInternetExplorer4

I have a website http://www.xldynamic.com/ which tries to provide some useful Excel information, different to the many other Exel websites. This site includes what is probably the most useful SUMPRODUCT discussion on the web.

Around about August 2008, my host provider, 123-reg.co.uk, started to tear down my site on or around the 20th of every month, due to exceeding my bandwidth limit (I admit, until then I had not been aware I had a limit). I was confused as to why this should happen, my site was popular, but was never one of the most frequented sites, and I had no new material to tempt anyone. Unfortunately, seeking clarification from 123-reg was a frustrating exercise, I was just bounced back with cut-and-pasted responses that did little to address my actual question.

So, when my renewal came up, I was looking to be off. I found a new provider, and wanted to move my site across. Again, 123-reg were just as unhelpful in transferring my domain as they had been in helping me to understand the problems, so it has taken a while.

All of this is a long preamble to saying my site is back in operation, and it looks as it did before. It is my hope and intention to give it a refresh when the time allows, make it more dynamic, and to add some new content, but for now I am just happy for it to be back up.

One thing I have managed to do is to update the SUMPRODUCT page. This page has been split into two parts, the first part deals with the mechanics of SUMPRODUCT, how it works, and considerations of this, whilst the second part has all of the examples. In addition, the first part has 3 new sections:

- Conditional Counting And Summing in VBA – using SUMPRODUCT in VBA

- SUMPRODUCT and Excel 2007 – the implications of COUNTIFS and SUMIFS on SUMPRODUCT

- Performance considerations – of using SUMPRODUCT

This page is going to get another overhaul soon, but in the meantime I hope that these changes are useful.

I would just add that my new host provider orcsWeb, has been immeasurably better in providing technical support. So far, it seems to be a great service.

Shreds And Patches

bobphillips — Sun, 27 Sep 2009 05:00:00 GMT

"Man is physically as well as metaphysically a thing of shreds and patches, borrowed unequally from good and bad ancestors, and a misfit from the start" (Ralph Waldo Emerson)

A number of us British Excel’ers (I nearly said English, Roger would never forgive me) have been holding Excel conferences for a few years. It had been a paid event, but there was no such event in 2008 as the 2007 event was not well enough attended. In 2009, we decided to try something different, and make the event free! Naturally, interest was high, and at the conference (held at Microsoft’s Victoria Street offices) we had over 100 delegates each day, and had to wait-list another 100+. Because of the fantastic interest, and the general good reception to the first event, we have decided to hold a second conference in October, offering first choice to those wait-listed at the March event.

Conferences are great opportunities for like minded saddos like us to get together and talk about the things our partners just pretend to be interested in. Microsoft have very generously provided a room with all of the presentation facilities needed, and provide coffee and lunch on both days. This support from Microsoft is what makes it possible to hold a free event, conference facilities are very expensive, and to have a central London location is fantastic.Again, many thanks to Microsoft, and the UK MVP leads, Vicki Collins and Akim Boukhelif, for their help in setting this all up.

The conference is free, but of course the cost of accommodation and meals, outside of the lunch and coffees provided during the event, are the delegate's responsibility. This applies to the presenters as well, we are not subsidised by Microsoft or anyone else (of course, if any corporation wishes to do so ...) , we bear our own costs. We like these events; we like the socialising and meeting Excel colleagues that we may know virtually but have not yet met in person, and of course, talking Excel over a beer in the evening is no bad thing.

As socialising, or to use that hideous term, networking, is an important facet of these event, we will be organising a group meal (again at own expense) on the Wednesday evening so that we can carry on great conversations that develop throughout these days.

The details of the event are:

Microsoft London (Cardinal Place)

100 Victoria Street
London SW1E 5JL
Tel: 0870 60 10 100

The agenda is below

Agenda Wednesday 7th October 2009

Time	Event	Speaker
9:15am - 9:45am	Registration & Coffee
9:45am - 10:00am	Welcome and Housekeeping	Nick Hodge
10:00am - 10:45am	Working Smart with the Excel Grid	Simon Murphy
10:45am - 11:45am	Intermediate Functions	Bob Phillips
11:45am - 12:15pm	Coffee
12:15pm - 1:15pm	What's in a Name?	Charles Williams
1:15pm - 2:00pm	Lunch
2:00pm - 2:45pm	Excel as an Enterprise BI Tool	Carl Mackinder
2:45pm - 3:30pm	Tips and Tricks in Charting	Andy Pope
3:30pm - 4:00pm	Tea & Coffee
4:00pm - 5:00pm	Pivot Tables	Roger Govier
5:00pm - 5:30pm	Pre- submitted Q&A	All
5:30pm – Late	Drinks, Dinner and Socialising	All (Inc Delegates)

Agenda Thursday 8th October 2009

Time	Event	Speaker
8:30am - 9:00am	Registration & Coffee
9:00am - 9:15am	Welcome and Housekeeping	Nick Hodge
9:15am - 10:00am	Data Exchange	Nick Hodge
10:00am - 11:00am	VBA - It's in Everyone's Reach	Simon Murphy
11:00am - 11:30am	Coffee
11:30am - 12:45pm	Information - Visualising Data In Excel	Andy Pope & Bob Phillips
12:45pm - 1:30pm	Lunch
1:30pm - 2:15pm	Arrays and Array Functions	Bob Phillips
2:15pm - 3:15pm	Speeding up Excel	Charles Williams
3:15pm - 3:45pm	Tea & Coffee
3:45pm - 4:30pm	Pre-submitted Q&A	All
4:30pm	Close

I hope to see you there.

Ribbons - Gain Without The Pain

bobphillips — Wed, 09 Sep 2009 05:00:00 GMT

Normal 0 false false false MicrosoftInternetExplorer4

If you are in the habit of building or customising Excel 2007 Ribbons, there is an indispensable new tool that has just hit the market. Andy Pope has released his RibbonX Visual Designer, which can be found here.

I have blogged previously about how I find cutting XML tedious, and the inadequate tools available, so this is a more than useful addition to make ribbon design and coding simpler. It provides a GUI to facilitate the Ribbon build, simple point and click rather than the tedious line coding. The big advantage of this approach is that you can see the hierarchy of your ribbon, and visualise what it will finally look like. To make it even better, there is a preview option, which will build a workbook and inject the XML so that you can ACTUALLY SEE what it will look like.

The tool adds a group to the Developer ribbon tab (so make sure that you make this visible)

and here is a screenshot of the designer, with a few example controls added.

In addition, you can load an existing workbook, and amend its ribbon. It has many other goodies which I won’t list here, check out the link above to read about them all.

As I said, a great tool, and at a good price - free!

Conditional Formatting In Excel 2007 - Chav or Mini?

bobphillips — Tue, 08 Sep 2009 05:00:00 GMT

Yesterday I had an incredibly frustrating time.

I was updating my RibbonX Autogen routine to handle more types of controls. This is driven from a worksheet, some columns of which are generated, some of which depend upon user input. So being a helpful sort of guy, I have decided to shade the input cells as a visual aid. But in some circumstances, an input cell may not be required (for instance, a group does not require an image, so suggesting that it does by shading is a tad misleading). Being the helpful sort that I alluded to earlier, I decided that I would use conditional formatting, to test those conditions, and remove the shading where appropriate. Which is where I came up against the MS designers, who seem to me to live in a land far from reality, totally unaware of what people really do.

Okay, we all know that conditional formatting was limited, 3 conditions was never enough, and that the CF dialog was rubbish, but why didn't they just address those issues? Why re-design the whole way it works? Don't MS realise that evolution is just as valid an approach as revolution, change the things that really requirie changing first, see how it gets used, and then gradually build upon that improve. In other words, let the real users have some influence.

The first problem that I came across was the new behaviour whereby if you create a condition, and then paste the format of another cell over the top, another cell that also has CF, the target cell adds those format conditions. No options, you can't say you don't want to add, it just does it. Of course, unless you are aware of this, you may find your cells reacting to a differenet condition, and you then start wondering why the CF no longer works.

Next, I added some CF conditions to a number of rows over 2 columns. That was fine until I decided that I wanted one of the columns to have an extra test in the condition. So I selected the cells in that column, and edited the rule to include the new test. Lo and behold, the other column also adopted these tests. When you set conditional formatting on a range, Excel remembers which cells are using that rule, and so change the rule in any cell and they all get the change. Again, no choice that I can see, I can't tell it what I want, I get what MS decide because MS knows best. In the end the only solution that I could find was by deleting the formatting on both columns and then recreating singly on each.

And of course, when you are in a cell that was part of a range that has been conditionally formatted, the formula is no longer adjusted to that cell as it was it previous versions of Excel, it shows the actual formula you applied. So I am in P15, and in the rule I see the formula =OR($A3="break",$B3,AND($A3="",$M3<>"")). The first time I encountered this it really threw me. What possible purpose can this change serve? I cannot see it.

Add to all of this rubbish the new, glitzy, childish icon sets, the fact that the formula box still defaults to input (so you have to F2 to actually use it), the fact that the dialog is not resizeable in order to better view your formula; I can only conclude that this is another very bad user dialog.

Considering that Excel 2007 was supposed to be simpler to use, and remove some of the mysteries of Excel, I think the designers of Conditional Formatting in Excel 2007 have failed abysmally. As seems to be the way with MS and Excel, too much effort was spent on adding unnecessary and distracting glitz, and very little on real usability.

Autogen’ed Ribbon Code

bobphillips — Mon, 24 Aug 2009 05:00:00 GMT

Normal 0 false false false MicrosoftInternetExplorer4

In a previous post, XML Is Such A Pain, I showed the technique that I use to simplify the creation of XML code for custom ribbons.

This is a handy technique, as it means that creating the XML is very simple, it is more or less a cut-and-paste job, and I spend very little time thereafter with the XML, which I like as the tools for XML in Excel 2007 are not great.

As much as I like this technique, it still requires a lot of VBA code. Whilst VBA is a great deal easier as a development tool than anything we have with XML, there are many areas where errors can creep in, both compilation and runtime errors.

In the old world of commandbars, I used a table driven method of generating the commandbars. It is possible to use a similar technique with Excel 2007, many such have been demonstrated.

I prefer to generate the code, from a similar setup table. This way I can generate all of the code components; it only needs to be run when anything changes; and I can view the code that is generated. I like code generation.

In the technique that I will demonstrate here, I generate all of the required code from the setup table

the ribbon XML
the VBA callback procedures
VBA constants used in the callback procedures.

In addition, the VBA code is automatically inserted into the target workbook. As of yet, the XML is not automatically added as the workbook is open at the time, but it should be possible to extend the technique to write the XML file.

As in the previous post, all of the code shown here relates to the following custom ribbon tab.

In that earlier post, you can view all of the XML and VBA code, so it will not be repeated here, but rather this post will cover the generation of that code. The autogeneration code here only caters for button controls, but it can easily be extended for other controls such as menus, toggles, and so on.

Autogen Setup Table

The table used to drive the autogeneration is structured as follows:

Type – a simple Data Validation list of tab, group, separator, or button
Tag – an id for the control that is used to generate unique control ids
Tag Upper - an upper case copy of Tag
Group – the Tag Upper value of the current group
Separator – the Tag Upper of the previous separator within this group (if applicable)
Group/Sep/Tag – concatenation of Group & Separator & Tag Upper

=SUBSTITUTE(D6&"_"&E6&"_"&C6,"__","_")

Control Id – a derived id for this control

=IF(A3="group","grp",IF(A3="button","btn",IF(A3="separator","sep",A3)))&$B$2&IF(A3="button",PROPER(E3),"")&B3

Caption – a value to assign as the control label
Procedure – the OnAction procedure name
Image File – the idMso of the required image
Image Size – a simple Data Validation list of small or large
Screentip – a value to assign as the control screentip
Supertip – a value to assign as the control supertip
Keytip – a value to assign as the control Keyttip

The example shown above consists of

a tab control
a group control
two button controls within that group
a second group control
a third button control within the second group

The table for this example is shown below. I have transposed it (columns to rows) in order to show it on this page, but that aside, you should get the principle.

Autogen Overview

The autogeneration works in the following way:

read the setup table and create a staging array of details
initialise the constants array, setting up on oversized array
initialise the output XML array with the namespace etc.
initialise the VBA code output arrays with function signature (a public ribbon variable and an onLoad callback procedure)
read the staging array and create items for each line of XML code
create output arrays for
- procedure VBA code lines
- label VBA code lines
- image VBA code lines
- image size VBA code lines
- screentip VBA code lines
- supertip VBA code lines
- keytip VBA code lines
create constants arrays for
- procedure ids
- label VBA ids
- image ids
- image size ids
- screentip ids
- supertip ids
- keytip ids
finalise the output XML array with the closing tags, and write the array to a text file (this is an area to be improved)
finalise the global constants output arrays by resizing the arrays to the appropriate size and write the arrays to a code module
finalise the VBA code output arrays by adding closing function statements, resizing the arrays, and write the arrays to a code module
write a procedure to the code module to invalidate the ribbon

In its present release, the XML code is written to a text file. It needs to be copied to the custom UI file, either by unzipping the file, then opening the customUI.xml file and replacing the XML within there, or by using the CustomUI editor to replace the XML.

There is nothing particularly illuminating in this code, it is a very straight-forward loop, depending entirely upon the setup table being correctly formatted. It should be noted that some control types do not create all of the array data, for instance a separator does not create constants or VBA code, tabs do not create image code and so on.

Considerations

As this technique writes VBA code on the fly, trusted access to VBA has to be granted, but this is only required in the development environment, if a workbook that this technique is applied to is given to a colleague, that colleague does not to grant trusted access.

This was a relatively simple project to build, there were not many problems encountered. The oddest involved the image size. In the XML, the image size is normal or large, so you would naturally assume that the value to be assigned in the callback procedure was normal or large. My original attempt at the code assigned these values, but the images always showed as normal size, I couldn’t get large images. Finally, I contacted Ken Puls a co-author on the RibbonX book, which was and is my main reference in working with the Ribbon. Ken’s book seemed to suggest that normal and large was the correct values to use in the callback procedures, but he also found that this wasn’t working. Luckily he found the values to use, which are 0 and 1. Ken has blogged this bug here.

Code Writing Code

The code that writes the VBA code on the fly is given below. There is not much code as there is no decision logic required in this code, the setup code creates complete output lines, apart from a test as to whether the code is to be written new or appended.

Option Explicit
Option Private Module

Public Enum ModuleTypes
    vbext_ct_StdModule = 1
    vbext_ct_ClassModule = 2
    vbext_ct_MSForm = 3
End Enum

Public Function WriteCode( _
    ByVal CodeArray As Variant, _
    ByVal Module As String, _
    Optional ByVal Append As Boolean)
Dim mpModule As Object
Dim mpInsertLine As Long
Dim i As Long
    
    On Error Resume Next
    Set mpModule = ActiveWorkbook.VBProject.VBComponents(Module)
    On Error GoTo 0

    If Not Append Then
    
        If Not mpModule Is Nothing Then ActiveWorkbook.VBProject.VBComponents.Remove mpModule
        Set mpModule = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
        mpModule.Name = Module
        
        mpModule.CodeModule.InsertLines 1, _       
                    "'== Generated Cube Formula Module " & _
                    Format(Date, "dd mmm yyyy") & " " & Format(Time(), "hh:mm:ss") & _
                    " By " & Environ("UserName") & _
                    " on " & Environ("ComputerName") & vbCrLf & _
                    "'== " & vbCrLf & _
                " "
    End If
    
    With mpModule.CodeModule
        
        mpInsertLine = .CountOfLines + 2
        For i = LBound(CodeArray) To UBound(CodeArray)
        
            .InsertLines mpInsertLine, CodeArray(i)
            mpInsertLine = mpInsertLine + 1
        Next i
    End With
    
End Function

Example File

For RSS subscribers, you can download a zip file containing the Autogen addin, and the example file shown above.

XML Is Such A Pain

bobphillips — Tue, 18 Aug 2009 05:00:00 GMT

Normal 0 false false false MicrosoftInternetExplorer4

Creating a custom ribbon tab in Excel 2007 is a well known technique by now, and relatively easy to achieve. I will not go into the details here, there are many examples scattered across the web, I would suggest Ron de Bruin’s pages at http://www.rondebruin.nl/ribbon.htm.

The biggest pain for me with custom ribbon code is the XML. As an example, to create a custom ribbon tab such as this

requires the following XML code

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="rxDemoRibbonOnLoad">

    <ribbon startFromScratch="false">

        <tabs>
            <tab id="tabDemo" getLabel="Ribbon Demo" insertAfterMso="TabHome">

                <group id="grpDemoGroup1" getLabel="Formatting Group">

                    <button id="btnDemoButton1" 
                        label="Currency" 
                        image="DollarSign" 
                        imageSize="Large" 
                        screentip="Button 1" 
                        superTip="Format activecell as currency"
                        keytip="" 
                        onAction="procButton1" />

                    <button id="btnDemoButton2" 
                        label="Percentage" 
                        image="PercentSign" 
                        imageSize="Large" 
                        screentip="Button 2"
                        supertip="Format activecell as percent" 
                        keytip="" 
                        onAction="procButton2" />
                </group>

                <group id="Text Group" getLabel="rxDemoGetLabel">

                    <button id="btnDemoButton3" 
                        label="Text Wrap" 
                        getImage="WrapText" 
                        getSize="Large" 
                        getScreentip="Button 3" 
                        getSupertip="Format activecell with text wrap"
                        getKeytip="" 
                        onAction=" procButton " />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

I have used all of the attributes in this example for the buttons, as I prefer to be explicit, not leave to defaults.

So far, this piece of XML is not too bad. But I find the maintenance of this XML extremely tedious, adding more controls and/or changing any of the attributes of these controls can be long winded and extremely error prone. The tools for creating and maintaining the XML are very limited, the best is the CustomUI Editor, but this suffers from having no recent used file list, and has an irritating habit of moving the changed line to the bottom of the window (whoever thought that was a good idea?). Maintaining the XML involves switching between the CustomUI Editor and Excel 2007, and you have to close down the Excel file in between.

Because of this, I have adopted the practice of not putting any of the values within the XML, but rather I use callback code to set the values. As an example, instead a line such as

label="Text Wrap"

I would setup the XML as follows

getLabel="rxDemoGetLabel"

and within my Excel file I would have a procedure that loads that label, such as

Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)

    Select Case control.Id
        Case "tabDemo":                    returnedVal = "Ribbon Demo"
    End Select
End Function

I do this for all labels, and the other attributes Image, ImageSize and so on (onAction has to be a callback of course). Using this technique, the XML code now looks like

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="rxDemoRibbonOnLoad">

    <ribbon startFromScratch="false">

        <tabs>
            <tab id="tabDemo" getLabel="rxDemoGetLabel" insertAfterMso="TabHome">

                <group id="grpDemoGroup1" getLabel=" rxDemoGetLabel ">

                    <button id="btnDemoButton1" 
                        getLabel="rxDemoGetLabel" 
                        getImage="rxDemoGetImage" 
                        getSize="rxDemoGetImageSize" 
                        getScreentip="rxDemoGetScreenTip" 
                        getKeytip="rxDemoGetKeyTip" 
                        onAction="rxDemoGetAction" />

                    <button id="btnDemoButton2" 
                        getLabel="rxDemoGetLabel" 
                        getImage="rxDemoGetImage" 
                        getSize="rxDemoGetImageSize" 
                        getScreentip="rxDemoGetScreenTip" 
                        getKeytip="rxDemoGetKeyTip" 
                        onAction="rxDemoGetAction" />
                </group>

                <group id="grpDemoGroup2" getLabel="rxDemoGetLabel">

                    <button id="btnDemoButton3" 
                        getLabel="rxDemoGetLabel" 
                        getImage="rxDemoGetImage" 
                        getSize="rxDemoGetImageSize" 
                        getScreentip="rxDemoGetScreenTip" 
                        getKeytip="rxDemoGetKeyTip" 
                        onAction="rxDemoGetAction" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

This means that I need to change the XML code far less frequently, if I wish to change a label it is all done in code, and so on. Adding a new button or group requires adding to the XML, but the additional code would be very similar to the existing code, just the control type and its id need specific values, so the maintenance is very simple.

This does mean that we need code to set the attribute values. In the rxDemoGetLabel example above, the tab’s label is assigned. As we have more controls with more labels assigned, we need more code to manage them, but this is simple extra case statements. For example

Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "tabDemo":                    returnedVal = "Ribbon Demo"
        Case "grpDemoGroup1":      returnedVal = "Formatting Group"
        Case "btnDemoButton1":      returnedVal = "Currency"
        Case "btnDemoButton2":      returnedVal = "Percentage"
        Case "grpDemoGroup2":      returnedVal = "Text Group"
        Case "btnDemoButton3":      returnedVal = "Text Wrap"
    End Select
End Function

As we can see, the code is very simple, very maintainable. The other callbacks would be very similar.

One other thing that I do is to create a set of global constants for all of the values, again to make maintenance simpler. For the labels I would have the following set of constants

Global Const LABEL_DEMO As String = "Ribbon Demo"
Global Const LABEL_GROUP1_GROUP1 As String = "Formatting Group"
Global Const LABEL_GROUP1_BUTTON1 As String = "Currency"
Global Const LABEL_GROUP1_BUTTON2 As String = "Percentage"
Global Const LABEL_GROUP2_GROUP2 As String = "Text Group"
Global Const LABEL_GROUP2_BUTTON3 As String = "Text Wrap"

And the callback code would be

Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "tabDemo":                    returnedVal = LABEL_DEMO
        Case "grpDemoGroup1":      returnedVal = LABEL_GROUP1_GROUP1
        Case "btnDemoButton1":      returnedVal = LABEL_GROUP1_BUTTON1
        Case "btnDemoButton2":      returnedVal = LABEL_GROUP1_BUTTON2
        Case "grpDemoGroup2":      returnedVal = LABEL_GROUP2_GROUP2
        Case "btnDemoButton3":      returnedVal = LABEL_GROUP2_BUTTON3
    End Select
End Function

I find this a lot easier to setup, and much simpler to maintain. It does mean that I have to invalidate the ribbon so as to refresh the attributes, but this is done on opening the file, and should not be noticeable. For info, this is the code I add to invalidate the ribbon

Public mgrxDemoIRibbonUI As IRibbonUI

Public Function rxDemoRibbonOnLoad(ribbon As IRibbonUI)
    Set mgrxDemoIRibbonUI = ribbon
    Call RibbonSetup
End Function

Public Function RibbonSetup()
    mgrxDemoIRibbonUI.Invalidate
End Function

The full code for the constants is shown below

Option Explicit

Global Const PROC_GROUP1_BUTTON1 As String = "procButton1"
Global Const PROC_GROUP1_BUTTON2 As String = "procButton2"
Global Const PROC_GROUP2_BUTTON3 As String = "procButton3"

Global Const LABEL_DEMO As String = "Ribbon Demo"
Global Const LABEL_GROUP1_GROUP1 As String = "Formatting Group"
Global Const LABEL_GROUP1_BUTTON1 As String = "Currency"
Global Const LABEL_GROUP1_BUTTON2 As String = "Percentage"
Global Const LABEL_GROUP2_GROUP2 As String = "Text Group"
Global Const LABEL_GROUP2_BUTTON3 As String = "Text Wrap"

Global Const IMAGE_GROUP1_GROUP1 As String = ""
Global Const IMAGE_GROUP1_BUTTON1 As String = "DollarSign"
Global Const IMAGE_GROUP1_BUTTON2 As String = "PercentSign"
Global Const IMAGE_GROUP2_GROUP2 As String = ""
Global Const IMAGE_GROUP2_BUTTON3 As String = "WrapText"

Global Const IMAGESIZE_GROUP1_GROUP1 As String = "0"
Global Const IMAGESIZE_GROUP1_BUTTON1 As String = "1"
Global Const IMAGESIZE_GROUP1_BUTTON2 As String = "1"
Global Const IMAGESIZE_GROUP2_GROUP2 As String = "0"
Global Const IMAGESIZE_GROUP2_BUTTON3 As String = "1"

Global Const SCREENTIP_GROUP1_GROUP1 As String = ""
Global Const SCREENTIP_GROUP1_BUTTON1 As String = "Button1 "
Global Const SCREENTIP_GROUP1_BUTTON2 As String = "Button 2"
Global Const SCREENTIP_GROUP2_GROUP2 As String = ""
Global Const SCREENTIP_GROUP2_BUTTON3 As String = "Button 3"

Global Const SUPERTIP_GROUP1_GROUP1 As String = ""
Global Const SUPERTIP_GROUP1_BUTTON1 As String = ""
Global Const SUPERTIP_GROUP1_BUTTON2 As String = ""
Global Const SUPERTIP_GROUP2_GROUP2 As String = ""
Global Const SUPERTIP_GROUP2_BUTTON3 As String = ""

Global Const KEYTIP_GROUP1_GROUP1 As String = ""
Global Const KEYTIP_GROUP1_BUTTON1 As String = ""
Global Const KEYTIP_GROUP1_BUTTON2 As String = ""
Global Const KEYTIP_GROUP2_GROUP2 As String = ""
Global Const KEYTIP_GROUP2_BUTTON3 As String = ""

and for the callback code

Option Explicit
Option Private Module

'-----------------------------------------------------------------
' Publicly exposed callbacks
'-----------------------------------------------------------------
' No values are set in the XML apart from ids, all labels, images,
' tooltips and actions are assigned in the callback code
'-----------------------------------------------------------------

Public mgrxDemoIRibbonUI As IRibbonUI

Public Function rxDemoRibbonOnLoad(ribbon As IRibbonUI)
    Set mgrxDemoIRibbonUI = ribbon
    Call RibbonSetup
End Function

Public Function rxDemoGetAction(control As IRibbonControl)
    Select Case control.Id
        Case "btnDemoButton1":      Application.Run PROC_GROUP1_BUTTON1
        Case "btnDemoButton2":      Application.Run PROC_GROUP1_BUTTON2
        Case "btnDemoButton3":      Application.Run PROC_GROUP2_BUTTON3
    End Select
End Function

Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "tabDemo":                    returnedVal = LABEL_DEMO
        Case "grpDemoGroup1":      returnedVal = LABEL_GROUP1_GROUP1
        Case "btnDemoButton1":      returnedVal = LABEL_GROUP1_BUTTON1
        Case "btnDemoButton2":      returnedVal = LABEL_GROUP1_BUTTON2
        Case "grpDemoGroup2":      returnedVal = LABEL_GROUP2_GROUP2
        Case "btnDemoButton3":      returnedVal = LABEL_GROUP2_BUTTON3
    End Select
End Function

Public Function rxDemoGetImage(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "btnDemoButton1":      returnedVal = IMAGE_GROUP1_BUTTON1
        Case "btnDemoButton2":      returnedVal = IMAGE_GROUP1_BUTTON2
        Case "btnDemoButton3":      returnedVal = IMAGE_GROUP2_BUTTON3
    End Select
End Function

Public Function rxDemoGetImageSize(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "btnDemoButton1":      returnedVal = IMAGESIZE_GROUP1_BUTTON1
        Case "btnDemoButton2":      returnedVal = IMAGESIZE_GROUP1_BUTTON2
        Case "btnDemoButton3":      returnedVal = IMAGESIZE_GROUP2_BUTTON3
    End Select
End Function

Public Function rxDemoGetScreentip(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "grpDemoGroup1":      returnedVal = SCREENTIP_GROUP1_GROUP1
        Case "btnDemoButton1":      returnedVal = SCREENTIP_GROUP1_BUTTON1
        Case "btnDemoButton2":      returnedVal = SCREENTIP_GROUP1_BUTTON2
        Case "grpDemoGroup2":      returnedVal = SCREENTIP_GROUP2_GROUP2
        Case "btnDemoButton3":      returnedVal = SCREENTIP_GROUP2_BUTTON3
    End Select
End Function

Public Function rxDemoGetSupertip(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "grpDemoGroup1":      returnedVal = SUPERTIP_GROUP1_GROUP1
        Case "btnDemoButton1":      returnedVal = SUPERTIP_GROUP1_BUTTON1
        Case "btnDemoButton2":      returnedVal = SUPERTIP_GROUP1_BUTTON2
        Case "grpDemoGroup2":      returnedVal = SUPERTIP_GROUP2_GROUP2
        Case "btnDemoButton3":      returnedVal = SUPERTIP_GROUP2_BUTTON3
    End Select
End Function

Public Function rxDemoGetKeytip(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "grpDemoGroup1":      returnedVal = KEYTIP_GROUP1_GROUP1
        Case "btnDemoButton1":      returnedVal = KEYTIP_GROUP1_BUTTON1
        Case "btnDemoButton2":      returnedVal = KEYTIP_GROUP1_BUTTON2
        Case "grpDemoGroup2":      returnedVal = KEYTIP_GROUP2_GROUP2
        Case "btnDemoButton3":      returnedVal = KEYTIP_GROUP2_BUTTON3
    End Select
End Function

Public Function RibbonSetup()
    mgrxDemoIRibbonUI.Invalidate
End Function

I think you can see this is a simpler method than holding the attribute values in the XML, all of the working code is in one place, and it makes for easier setup and maintenance.

This overall approach requires the setting of all of the values for the global constants and the code. In a future post I will describe how I have automated this.

Using Linq with Microsoft Word and Excel

DeborahK — Fri, 14 Aug 2009 05:00:00 GMT

Some of the collections in the Microsoft Office object models implement IEnumerable. The IEnumerable interface provides the ability to perform a for/each against the collection. With .NET 3.5, a Cast extension method of IEnumerable allows you to work with these collections using Linq.

Microsoft Word

For example, say you want to bind the set of open Word document names in a ComboBox.

In C#:

// Add to the top of the code file
using Word = Microsoft.Office.Interop.Word;

// Add to a subroutine
Word.Application Wd;
Word.Document doc;
Word.Document doc2;
object missingValue = Missing.Value;

// Start Word and get Application object
Wd = new Word.Application();

// Define documents
doc = Wd.Documents.Add(ref missingValue,ref missingValue,
ref missingValue,ref missingValue );
doc2 = Wd.Documents.Add(ref missingValue, ref missingValue,
ref missingValue, ref missingValue);

// Use Linq to access the document names.
var query = from d in Wd.Documents.Cast<Word.Document>()
select d.Name;
comboBox1.DataSource = query.ToList();

// Or use Lambda expressions
var query2 = Wd.Documents.Cast<Word.Document>().Select(d=> d.Name);
comboBox1.DataSource = query2.ToList();

// Close
doc.Close(ref missingValue, ref missingValue, ref missingValue);
doc = null;
doc2.Close(ref missingValue, ref missingValue, ref missingValue);
doc2 = null;
Wd.Quit(ref missingValue, ref missingValue, ref missingValue);

// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect() ;

In VB:

' Add to the top of the code file
Imports Word = Microsoft.Office.Interop.Word

' Add to a subroutine
Dim Wd As Word.Application
Dim doc As Word.Document
Dim doc2 As Word.Document

' Start Word and get Application object
Wd = New Word.Application

' Define documents
doc = Wd.Documents.Add
doc2 = Wd.Documents.Add

' Use Linq to access the document names.
Dim query = From d In Wd.Documents.Cast(Of Word.Document)() _
Select d.Name
ComboBox1.DataSource = query.ToList

' Or use Lambda expressions
Dim query2 = Wd.Documents.Cast(Of Word.Document) _
.Select(Function(d) d.Name)
ComboBox1.DataSource = query2.ToList

' Close
doc.Close()
doc = Nothing
doc2.Close()
doc2 = Nothing
Wd.Quit()

' Clean up
' NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()

In both of these examples, the code starts Word, creates two Word documents, uses either Linq or a Lambda expression to define a query and then binds the resulting set of document names to a Combo Box.

Notice the missingValue variable in the C# code that is not in the VB code. VB supports default parameters, but C# does not. So any time a parameter is defined for a Word method, C# must provide it. VB will use the default parameter values.

NOTE: A new feature in C# 4.0 (Visual Studio 2010) allows for default parameters in C# as well, dramatically simplifying the C# code that interacts with Word or Excel.

As another example, the following code retrieves all of the words from the defined Word document.

In C#:

var query = from w in doc.Words.Cast<Word.Range>()
select w.Text;
comboBox1.DataSource = query.ToList();

In VB:

Dim query = From w In doc.Words.Cast(Of Word.Range)() _
Select w.Text
ComboBox1.DataSource = query3.ToList

This code retrieves all of the words in the document defined by the doc variable. Instead of selecting the list of words, you could use any Linq feature such as finding only a specific set of words that match a criteria or counting the number of occurrences of a given word.

Microsoft Excel

This technique works with Excel as well. Say you want to bind the list of spreadsheets in an Excel workbook.

In C#:

// Add to the top of the code file
using Excel = Microsoft.Office.Interop.Excel;

// Add to a subroutine
Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;

// Start Excel and get Application object.
oXL = new Excel.Application();

// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);

// Get the active sheet and change its name
oSheet = (Excel.Worksheet)oWB.ActiveSheet ;
oSheet.Name = "Test";

// Use Linq to access the spreadsheet names.
var query = from s in oXL.Worksheets.Cast<Excel.Worksheet>()
select s.Name;
comboBox1.DataSource = query.ToList();

// Or use Lambda expressions.
var query2 = oXL.Worksheets.Cast<Excel.Worksheet>()
.select(s => s.Name);
comboBox1.DataSource = query2.ToList();

// Close
oSheet = null;
oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
oXL.Quit();

// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();

In VB:

' Add to the top of the code file
Imports Excel = Microsoft.Office.Interop.Excel

' Add to a subroutine
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet

' Start Excel and get Application object.
oXL = New Excel.Application

' Get a new workbook.
oWB = oXL.Workbooks.Add

' Get the active sheet and change its name
oSheet = DirectCast(oWB.ActiveSheet, Excel.Worksheet)
oSheet.Name = "Test"

' Use Linq to access the spreadsheet names.
Dim query = From s In oXL.Worksheets.Cast(Of Excel.Worksheet)() _
Select s.Name
ComboBox1.DataSource = query.ToList

' Or use Lambda expressions
Dim query2 = oXL.Worksheets.Cast(Of Excel.Worksheet) _
.Select(Function(s) s.Name)
ComboBox1.DataSource = query2.ToList

' Close
oSheet = Nothing
oWB.Close()
oWB = Nothing
oXL.Quit()

' Clean up
' NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()

In both examples, the code starts Excel, changes the name of the active sheet, uses either Linq or a Lambda expression to define a query and then binds the resulting set of sheet names to a Combo Box.

Enjoy!

The End Of VBA

bobphillips — Fri, 14 Aug 2009 05:00:00 GMT

The title was just to get your attention, it really should be entitled something along the lines of “What Made VBA The Success It Is Today?’, as this post is more about what VBA is and isn't, albeit somewhat precipitated by the thought that VBA must be close to its end game.

I recently saw a thread in a forum where one of the responses was along the lines that "VBA was not designed for programmers - i.e. MS dumbed down requirements ...". I found this to be a very facile argument and said so in my response. I won't bother retracing that thread here, but I thought it raises an interesting question.

When MicroSoft introduced VBA into Office, just what was their objective, how did they envisage its use, and what concessions did they make in their implementation? BTW, although it was introduced into Office, I will concentrate solely on Excel, for obvious reasons.

Although Excel already had a macro language, these were not the easiest to use, and so, for whatever reason that it was done, introducing VBA into Excel 97 was a masterstroke, it opened all sorts of possibilities to Excel users. Like all projects, I am sure that MS' implementation of VBA had some key must-do objectives, some secondary should do objectives, and some nice to haves; some of those objectives would be customer focussed (such as giving users a simpler development tool), some would be MS focussed (for example, as it was a superset of VB, they would probably want to keep it as closely aligned to VB as is possible, controlling development and maintenance costs). And like all projects, the timeframes and the budgets would be balanced, large enough to suggest that a great product was achievable within the projects goals, but not so large that everything was easy and achievable - that is just the way of the world.

I have no inside knowledge, but I would think that when MS decided to give the world VBA, they were not looking at IT shops, they were specifically looking to give real users extra capability. They probably anticipated macros that formatted worksheets, shunted data around a sheet/book, all of the standard, simple one-off macro solutions. I very much doubt that they anticipated quite how its usage would take-off, how Excel especially has morphed into a development platform capable of running highly complex applications.

The sheer creativity and inventiveness displayed in VBA is staggering. In Excel alone, there have been some amazing development, from the simple to the hugely complex. We have seen a whole gamut of solutions, for example

simple, but highly effective, tools that make using Excel functions simpler, such as Rob Bovey's Chart Labeller
tools that extend Excel's native functionality and help spreadsheet users to use Excel more efficiently, such as Jan Karel Pieterse's NameManager
true developer tools for spreadsheet users that help better spreadsheet development, such as Charles Williams FastExcel
some incredible tools that do things previously thought not possible, many such examples but a classic is Stephen Bullen's PastePicture
giving us back functions that we used to have, such as Andy Pope's Pattern Filler utility in Excel 2007
database applications where Excel is providing a presentation and data management layer, and a database, Access or SQL Server, maintains the data and provides the richness of a relational database
ETL applications where Excel harvests data from disparate sources to provide a composite picture, using tables, charts, dashboards etc.
end-to-end data capture, data management, and reporting applications using multiple technologies and multiple layers.

What is even more impressive is that these solution have often been produced by one man outfits, no big IT departments to support the developer here, and often by developers that are mainly business users, people who would never claim to be coders (one of the best Sparklines implementations I have seen was by a guy who made no claim to being a coder, indeed he said that he built this implementation to help develop his VBA skills).

Did MS envisage all of this when they were looking to add VBA? Maybe, but somehow I doubt it. As I said previously, users building macros to automate a few tasks, formatting, capturing data, and so on, is a far more realistically envisaged deployment, presumably they felt that just bringing VB into the Office apps would be a lot simpler for users to get to grips with. If they had envisaged all of this use, I would have hoped that they would have been far more ambitious in their objectives, but I don't believe this so it is a sterile argument for me.

As I mentioned above, VBA would have been a project, and as such, it would have been subject to typical project constraints. This must have meant that, throughout the course of the project, implementation decisions would have had to be made. Some of these decisions probably resulted in cutting or constraining some of the functionality that was originally envisaged. I am sure this would have been done for all of the right reasons, and that the VBA developers and project leaders would have justified it in their minds, always taking into consideration what they knew or thought at that time. Sitting here in 2009, we might argue that some of those constraints were ill-advised, but that is hindsight, and it is certainly worlds away from saying that MS dumbed down VBA.

Sure there are some things that are better in VB than they are in VB6.

forms are better in VB, you have a built-in menu facility, and control arrays, but both of these can be emulated in VBA, with varying degrees of success and completeness
you can create stand-alone executables in VB6, but not in VBA - this is true but when VBA was introduced I am sure this was not even a consideration, without being able to foresee the types of addins deployed today and the security issues that have manifested, it seems impossible to think back then that this was necessary or even desirable
packaging and deploying - again, VBA is by default packaged within its host container application, targeted at individual users, how could MS have envisaged this need, and anyway, there are many good third party tools that provide this capability

Some things are simpler in VBA, working with Excel for example in Excel, by virtue of its implementation, just as some things are harder by virtue of its implementation.

Acknowledging some things are easier in VB is a completely different matter from saying that MS dumbed down VBA. I no more believe that MS deliberately dumbed down VBA than I believe that many of the techniques that have been developed over the years to make Excel such a versatile development platform were deliberately introduced by MS, it is just consequences of the original implementation.

To me, this is a relevant topic today as we stand on the brink of moving, either willingly or not so willingly, to the .Net world. It originally struck me that MS were not prepared to make any concessions to the world of VBA and the millions of users that have built solutions using VBA. My impression was that they were trying to dig their heels in and say that it is .Net in the future or nothing. I believe that attitude has changed somewhat, not enough yet, but at least they concede that these users have to be accommodated or else they just will not, or cannot, upgrade. The interesting thing will be to look back in 5 years time and reflect on what we think that MS have and have not done well in introducing .Net into Office. I for one feel that we are on the brink of a much better experience with .Net, and am cautiously looking forward to it.

In the meantime, let us all celebrate VBA, the way it has been stretched, bent and moulded over the years by the thousands of real users who just wanted to be able to do something, and do it today; by talented Excel developers like those mentioned above, and by all of the 'novice users' who have dared to try and do something in Excel and VBA that helped them to work faster, less, and/or better. Of course, there have been some horror stories along the way, I have seen a few myself, but there have been many successes. Excel has not become the de-facto application development platform that it is today (what other desktop application could have a book like PED written about it?) because MS dumbed down VBA, but because Excel has an incredible function set, and VBA has exposed that function set to all manner of users, and it is they that have shown the world just what is possible.