Excel User Group

UK Excel Developers Conference

Nick Hodge — Tue, 15 Jun 2010 21:35:00 GMT

All

Wanted to pass along details of a great event happening for Excel Developers in London on July 12th 2010.

With confirmed presenters of Simon Murphy and Charles Williams and focussing on high-end developers in Excel including VSTO, Excel 2010 and VS2010, it will be well worth the £150 fee for the day.

Detail Here

World Cup Fantasy Football Model

Admin — Thu, 10 Jun 2010 22:43:00 GMT

Written for Excel 2007 and 2010, I have uploaded a model which allows you to run a fantasy football league for friends, colleagues, etc. You just add their names, enter the scores and the rest is done.

automatically updates and sorts league tables and all formulas and code is open for all to see

Have fun (I dare say this could be adapted for future tournaments?)

You Need To Buy This…

Nick Hodge — Tue, 03 Nov 2009 16:11:09 GMT

All

John Walkenbach is having a blow-out sale on his Power Utility Pak for Excel with 80% off!

To be sure of getting your copy you need to head over here between the hours of 4pm and 6pm (GMT) to qualify.

Excel User Conference next week

Nick Hodge — Fri, 02 Oct 2009 06:08:19 GMT

As some of you will know, the boys (Bob, Charles, Simon, Andy, Roger and myself) are presenting another FREE series of courses at Microsoft’s Office in Cardinal Place, London for 2 days on October 7th/8th.

This is a repeat of the hugely oversubscribed event in April where we had over 120 reserves who places couldn’t be found for. So far we have around 100 attending so places left for around another 20 on either or both days.

If you fancy coming along to this great FREE event (except accommodation and evening entertainment) then drop a line to bookings at excelusergroup dot org. (re-assemble as indicated)

Should be great fun

Useless Fact Of The Day

Nick Hodge — Mon, 06 Jul 2009 06:27:51 GMT

Excel uses a date system based on it's official start date of 01/01/1900. Days are whole numbers and decimals are time values, so, for example if you type 01/01/1900 12:00 into Excel and then go to Format it as 'General' you will see behind the date and time sits a value, which should be 1.5 (Day 1 and half a day in time).

Why all this nauseam? Well, today is 40,000. Having struggled with Excel through the 30,000's today feels like the first day of the rest of my life ;-)

Caveat: It isn't actually the 40,000 day since 01/01/1900 as Microsoft, to keep compatibility with Lotus 1-2-3 which was the leading spreadsheet of the day, duplicated the error that Lotus had that 1900 was a leap year and included 29th February. Of course it wasn't so I guess day 40,000 is actually tomorrow????

For loads of other great time and date stuff go to:

www.cpearson.com/excel/datetime.htm

Office 2007 SP2 Released

Nick Hodge — Wed, 29 Apr 2009 20:39:00 GMT

Office SP2 has been released officially here. The list of changes against Excel seems fairly small but having been running the beta for some time it does seem quite a bit better, additionally there are improvements across the suite, probably most noticeably the speed in Outlook.

Additionally support has been added for the Open Document Format (ODF) which is 'compatible' with stuff like Google Documents, although there are some areas where that just doesn't work, but expect that to get better over time. Here is the link to what has been changed

UPDATE: I'm grateful to Stan Scott of New York, who forwarded me a document containing all the changes to all applications in Office 2007 SP2

Excel User Conference Piccies

Nick Hodge — Sun, 05 Apr 2009 01:59:00 GMT

Bob Phillips kindly took a few pictures of the recent conference which by his own admission he should have taken more of. It shows all the presenters, except, of course Bob and gives you a flavour that there where plenty of delegates and lots of 'action'. What was Simon Murphy doing? Although he is an 'all action' speaker.

Thanks for taking those Bob and I agree, more next time.

For those delegates wondering, the links to the slide decks and the files should be uploaded by tomorrow, (Monday 6th April) and we will email with the link(s)

Here is the URL (for the photos)

Excel User Conference 2009

Nick Hodge — Tue, 31 Mar 2009 20:57:43 GMT

Tomorrow sees the start of the two day event at Microsoft's cardinal Place site in London. We have some great speakers and topics lined up and the event has been oversubscribed since about a week after it was announced. That I think is a measure of what a great event it should be.

Watch this space for updates afterwards and hopefully we will also get some files too.

Today's Large Data Set

Nick Hodge — Tue, 24 Feb 2009 08:38:58 GMT

Now, since Excel 2007 was announced to be having over 1 million rows, I made the statement, "Be careful what you wish for". Excel is not a database, there are programs far more suited to that task such as Access, SQL Server, SAP, Oracle, etc. Excel is a great reporting tool for those databases and can do fast and quite complex calculations much better than a database can.

What I am conceding more and more now though, is that as my familiarity with Excel is far greater than with SQL Server. I find myself taking large data sets out of SQL to do stuff that either the database is not good at, or more often where my T-SQL language skills let me down.

One such occurrence yesterday was trying to get a twelve month running average over weekly data for 30,000 items (per week) a data set which was currently around 850,000 records. The weekly update each Saturday night, being added to the total was not the problem, the setting up of the historical data was. Either the server choked on the physical task, or the data was not quite what I wanted. (Back to my T-SQL inadequacies).

What would have been impossible before 2007, well not impossible, but messy. Was to take out the data into Excel, work with it there and then, after saving it back as a .txt, tab delimited file. (SQL Server drivers seem to not yet cope with the new Excel formats), upload it back into SQL Server, either directly updating the original table, or more often to a temporary table which can then simply be linked to the original table and the data updated or added, very simple and painless.

A couple of caveats though. Much of my work is on 'one-offs' to get me out of a corner and I use a lot of VBA snippets work the data. On this occasion I was having to drop in different worksheet functions based on the data. I wrote my 10-12 lines of code to run and set it off. Now, when I am running code over large data sets, although it has an overhead, I like to see where the code has got to, so, for example, in a for each...next loop, I always write the row number to the status bar. I then get an idea how long I need to walk away from my machine. (Coffee break, lunch or vacation?), like so.

For Each myCell in Range("D1:D856456")
'Do stuff here...
Application.Statusbar = myCell.Row
Next myCell

In the data I was working the other day it was a simple AVERAGE w/s function, but was taking a very long time. Of course, I had forgotten to turn calculation to manual at the start of my code so stopped the code (vacation otherwise) and added the line

Application.Calculation=xlCalculationManual

Remembering to switch it back on afterwards, although Excel will take the setting from the first workbook opened in a session, so we should be OK.

I ran it again and still noticed it was dragging a little and a quick sum showed I should take a half day off ;-)

What I had forgotten, is that, as I was inserting random ranges in the AVERAGE function based on other data, Excel was trying to set the 'error' flags, (The little green triangle in the top right corner of a cell when the formula does not match those of the cells around) on each cell as the code ran. Turning those off and we were away. The code now taking less than 20 minutes (a long coffee break) to run. The line to turn them off

application.ErrorCheckingOptions.BackgroundChecking=False

Again remember to turn them back on. (If that's what you want)

So, are 1,000,000+ rows in Excel a good thing? Not really, but it doesn't half come in handy. (With caveats)

Excel User Conference (an update)

Nick Hodge — Tue, 27 Jan 2009 03:34:00 GMT

The guys who are organising this event. (Bob Phillips, Roger Govier, Andy Pope, Charles Williams, Simon Murphy and I), have been overwhelmed by the response for the forthcoming event in London on April 1st and 2nd.

We have been 'technically full' almost from the start with floods of delegates and are operating a reserve list. (We have even had to turn down adverts on the likes of Office Online, etc). As it is a free event however, people do not have huge amounts of 'skin' in the day, so we are emailing regularly to ascertain if people can still come and then promote from the 'wait list'. (MS advise us that up to 40% can drop out of free events, although we have more than that on reserve too).

It should be a fantastic couple of days for the 104 delegates anyhow, whoever they may be.

The update is that, 'the guys' are also talking about a repeat in the autumn with priority given to people on the reserve list. We have not all agreed yet and not approached MS, but this is the current thinking. It certainly could be filled, no doubt!

My First 15 Minutes?

Nick Hodge — Wed, 10 Dec 2008 19:32:05 GMT

Everyone will be famous for 15 minutes... dunno who said that but as many of you know I live a double life as downright nice, jolly chap on one hand and geek on the other!

Some time ago I wrote a blog about getting External Data into the new 2007 version of Excel. Well, it grew a little into a full-blown article and it has just been published on the main Office Online web site at Microsoft.com.

The article is here

...end of 15 minutes and back to obscurity!

UK Excel User Group Conference

Nick Hodge — Fri, 05 Dec 2008 17:47:00 GMT

A meeting of the above group will take place on Wednesday 1st and Thursday 2nd April 2009 in London. This FREE event has some fantastic speakers from the Excel community.

The agenda for the two days is outlined below and you can book for either or both days by emailing bookings@excelusergroup.org.

Microsoft will be providing the venue and the hospitality and we look forward to a great and informative couple of days. The agenda is below, but if you want the full version with session details and speaker bios, you can download the Word document file here.

Venue:
Microsoft London (Cardinal Place)
100 Victoria Street
London SW1E 5JL
Tel: 0870 60 10 100

Agenda:

Wednesday 1st April 2009

Time	Event	Speaker
9:15am - 9:45am	Registration & coffee
9:45am - 10:00am	Welcome	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	TBA	Microsoft
2:45pm - 3:30pm	Tips and tricks in charting	Andy Pope
3:30pm - 4:00pm	Coffee
4:00pm - 5:00pm	Pivot tables	Roger Govier
5:00pm - 5:30pm	Pre-submitted Q&A	All
5:30pm - Late	Dinner, drinks, etc	All (Inc. Delegates)

Thursday 2nd April 2009

Time	Event	Speaker
8:30am - 9:00am	Registration & coffee
9:00am - 9:15am	Welcome	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	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

Excel 2007 - Getting External Data

Nick Hodge — Tue, 04 Nov 2008 19:31:15 GMT

On of the most visited parts of my web site is the part on external data. Most of it was based on Excel 2003 and before, so I thought I should update it for Excel 2007.

INTRODUCTION

The first thing you will notice about 2007 is the ease in which you can find the data 'movement' tools. Now, say what you think about the ribbon in Excel 2007, but finding where to import and export data is not one of them! It's on the Data tab. (as below)

I have only shown you the section above. The tab extends beyond this with groups for sorting and filtering, validation and de-duping tools and grouping stuff. We will only be using these two groups. (Get External Data and Connections)

SIMPLE IMPORTS

The first three buttons from the left are easily discoverable and ready-rolled versions of the old commands available through the Data>Get External Data>New Database Query... (See below)

The 'From Access' and 'From Text' options, simply throw up an 'Open' dialog to allow you to navigate to your selected file. It should be noted that the Access variant is compatible with versions of Access pre-2007, allowing extensions of *.mdb (2003 and before) and *.accdb (2007), as well as their 'project' equivalents (*.mde and *.accde). The text import version allows import of *.prn (space delimited), *.txt (tab, or other delimiter) and *.csv (comma delimited).

IMPORTANT TIP: There is also an option, when selecting text, to select *.* all files. (see below).

This is important when you have columns with data that may begin with a zero, for example part numbers like 000123456. Using any other driver for import will have Excel simply strip these off, leaving you with 123456. This will be a real problem when you come to do any VLOOKUPs or similar as one will not see the other.

So, the trick is, remove whatever extension you have completely. (To do this in later versions of windows you will likely have to go to any Windows Explorer window and select 'Folder Options...' (In Vista this is under the 'Organise' option) and on the 'View' tab, de-select to 'Hide known file extensions'. (In other words to show them), see below).

You will get warned about changing file extensions being dangerous and you can ignore that! You will then end up with the usual icon for a file that windows shows when it has an unknown file extension.

Taking this route will launch the Import Wizard, allowing you to set the part number column (for example) as text, thus keeping your leading zeroes (more on this option later). This is the only successful way I know of doing this as Excel, through any of it's proprietary drivers, does not seem to honour field 'types'.

IMPORTING FROM ACCESS (or most other OLEDB data sources)

So, taking the first option will throw up the dialog to navigate to your database file (as outlined above), once you have done this you will be presented with a dialog, showing the tables and queries (views) in that database file, select the relevant one and click 'OK'.

You will notice from the dialog below that I name my Tables, tblXXXXX and my Queries, qryXXXXX. This helps me to easily see them and also sorts them neatly. However, if you do not do this, you should note the icon for each is different, with the one for Queries or Views looking like two overlaid table icons. (as below)

Select your table and you then get loads of options to place your data into the workbook. (see below)

Most of these will be self-explanatory but listed you can have the data as a new 2007 formatted table (the default), a pivot table, or a pivot chart and pivot table. Equally you can decide whether you want the data placed in the existing worksheet (and where on that sheet) or a new sheet. Don't forget also the 'Properties...' button that will throw up a dialog, as below, to turn on, or off, some features of the resulting connection to your data source. Key on the usage tab is a descriptive name and description, as this will now appear under the 'Connections' button on the 'Connections' group on the 'Data' tab. A feature allowing future manipulation of this connection which was difficult, or near impossible in earlier versions of Excel. Also, you may like to set the 'Enable background refresh' (default) so that you can work with Excel while the data is refreshed in the background. This becomes relevant if you set the data to refresh every nth seconds. Probably the most useful setting to ensure 'one version of the truth', is the 'refresh data when opening file' setting. Even though Excel will fire the security bar to warn that the data may be unsafe (unless you have trusted the location or similar), as soon as you 'release' this the data will update with the live data in the source.

The definition tab (below) allows you to change the connection string, which is handy if you move the file or similar, it also is a good way to get the string if you are using it in VBA or .NET code.

Finishing all these dialogs will return your data to the Excel worksheet as a table or pivot table template, as below

Once you have your data on the sheet you will notice that not only are the 'Refresh All' and 'Connections' button on the Connections group 'lit' (as top), but also the Properties one is also. If specifically you are in a new Excel 2007 table, this shows some nice features to expand column widths, include row numbers from the data source and whether you want you data replacing and deleting the existing data or appending it.

Bear in mind, once you have your external data in Excel, you are only a right click away (in the table or Pivot) from selecting 'refresh' to get an updated view of the data.

Much of what you have learned under this Access heading will stand you in good stead for all other sources as in fact it is using an OLEDB data source 'under the covers', the same as you will use for SQL, Oracle, or most other modern data sources. The only difference will be if you are using a server technology, is you will not navigate to a file, you will use a server name.

IMPORTING FROM THE WEB

Once you use this technology it is surprising how much you will use it in the future. It is great for grabbing for example order screens as receipts and also any kind of list. I suspect however that most people use it to keep up to date with stocks and shares and, providing the site owner doesn't mess with the URLs you need only open Excel and refresh each time to see the latest data, as with any other data source. This is the example we will be using. (Using Microsoft's Stock Symbol on Yahoo (MSFT).

Here is the URL (Today)

Firstly we will go to the 'From Web' button. (See above). This effectively launches a browser window into which you can either enter the URL or paste it as with any other browser. (see below)

Notice above that this control 'senses' the table in the data and places small yellow boxes with right facing arrows to show them. All you do now is simply click on any table you want to select, which will turn the yellow marker to green with a tick and click 'Import'. Now, when doing this with just these default settings, Excel imports a pretty bland, text representation of the data, (see below)

To alter this you should, before importing press the 'Options...'button, top right of the dialog, (see above). This will give you several choices, (see 1st below), the two most crucial of which are, 'Full HTML formatting' and 'Disable date recognition'. The former ends up with the data looking far prettier and much like it is on the web page itself (see 2nd below), the second will stop data such as 1-1-00 (possibly financial data, from being converted to 01/01/2000 (as a date).

Now, as with any other data on the worksheet, you can simply either press the 'Refresh' button on the 'Data' tab or right click on the data and select 'Refresh'

IMPORTING FROM TEXT

Remember, that a csv file (comma separated values) is a native form to Excel. You will notice this by looking at the file icon. It is like a normal one but with a letter 'a' and a comma. If you double-click directly on a csv file, excel will open it. Sometimes this is undesirable, so I suggest you take the 'Important Tip' route above.

If you have any other text file format than csv, you should open it using the 'From Text' button. Remember too, that even if Excel doesn't open the file format you have (Quicken for example), you will nearly always be able to save as some text format within the source program, so you should do that.

Pressing the button simply presents a navigation dialog to go to the file, as below

Once again, you have the option in the bottom right to show all files (*.*), so if you file does not have a *.prn, *.txt or *.csv extension you will need to use that.

Pressing the 'Open' button presents you with the three step Text Import Wizard allowing you to specify the data steps. The three dialogs are shown below

Step 1 allows you to specify whether the file is delimited (has a character of some type between each column (field), e.g. comma, tab, space (you will notice the commas separating my data) and the other usual option allows you to start the import at a certain row. This is handy when certain systems simply output a paper report to text file and the report has headings as this will allow you to strip those rows out.

Step 2, if you have, as I have selected 'delimited' in step one, will allow you to select the delimiter. In my case this is comma but if you have any other you can select from the choices or select 'other' and enter the character in the box. If you had selected 'fixed width' in step one. (That is field one is say always 10 characters, field 2. 30 characters and so on), then this step allows you to click at the fixed widths of your data to set the delimiting 'bars'.

If you are likely to have consecutive delimiters, e.g. an address file that is comma delimited may have commas at the end of an address line, then you should select to 'Treat consecutive delimiters as one'. Some data systems also output text data, as opposed to numeric data, surrounded by double quotes, if so, select that from the 'Text qualifier' drop down.

The last step allows you to set the columns as certain data types or not import at all. This is where, as I have done in column 5, you can set you data as text and retain you leading zeroes. Equally, as you can see in column 8, I have selected 'Do not import column' and it has placed 'Skip Column' in the heading and it will not be imported.

Clicking finish returns the data to the worksheet and you are done!

IMPORTING FROM OTHER SOURCES

This is where Excel uses it's OLEDB or ODBC drivers that are loaded on the machine

IMPORTANT: It is possible that your driver list will not match mine and you may not be able to import from an AS400 DB2 for example. This is because your machine is not loaded with the driver for that data source, nothing to do with your excel installation. Remember, if you are setting this up for someone else, their machine too must have the relevant driver and connection file in most cases.

For purposes of this demonstration, I will be using SQL Server as my data source, but frankly, if a driver is available, it could be just about anything. Generally you may prefer to use the OLEDB drivers, which will create a transferable connection file, but if all else fails, you can use MS Query, an age old tool in Excel that is a little like Access and uses ODBC as it's connection technology. It's a bit like dropping back to VGA if you have trouble with hi performance video drivers!

Below are your menu choices from the 'From Other Sources' button.

Selecting SQL Server presents us with a Data Connection Wizard which you can use to set the necessary settings for your connection. The three steps are outlined below.

The first step allows you to specify the server name. (my example, (local), connects to the local machine server). I most instances Windows Authentication will be OK. This is a system whereby Windows manages your passwords, meaning your network logon suffices for SQL, email, etc, etc. On occasions, each user will have a logon specific to the data source or even a table. If that is the case,as will often be on remote/shared databases, you would select the other option and enter a user name and password.

The second step offers you the specific database in the named server you want to connect to. Changing this will present you with a list of table and queries (views) in that particular database, on that named server. Equally, if you want to select this each time you use the connection then you can uncheck the 'Connect to a specific table' check box and you will be prompted each time. As before the icons for Tables and views are different to ease identification. (I use a different naming convention in SQL server, uv-xxx-xxxxxxx is a user view (query) and the tables are named in plain English. Don't ask me why, I've always been a little quirky!)

Step three allows you to name the connection file and give it a description to help in search as as an aide memoir on what it does. There are tons of other metadata stuff you could put here, but that's about it.

From here on in returning data to the worksheet follows the same route as under the Access section.

USING PARAMETERS IN QUERIES AND MS QUERY (ODBC)

NOTE: This is a repeat of the same subject in another post on my blog, but as that one was set in XL2003 and is the most hit entry, I thought I would update it for 2007.

If all the above fails, you can drop back to the 'age old' method of MS Query which uses ODBC technology. The operation of this can be seamless, without you even seeing an interface, but what I would like to show is a way you can use dynamic parameters on your worksheet to choose the data shown.

NOTE: A standard install of Excel may not install this feature (can't remember), if you do not have this option, re-insert your installation media and chose to 'add and remove features' and select all the data options you can see!

So, let's start.

When I am intending to work with parameters on a worksheet, I tend to enter the placeholders for these first. this will help when the interface asks for the parameters and tries to retrieve the data. (I am going to pull data between 2 dates, remember I am in the UK locale so our dates are formatted dd/mm/yyyy). So the worksheet would look like below.

I am hoping when we connect to the data (table or query) we will bring through initially all catalogues with a mail date between 1st Jan 07 and 1st Sep 07.

Next we will select the 'From Microsoft Query' option from the 'Other Sources' button, (see above). This will launch a dialog asking us to choose a data source (see below). (Data sources are files stored on you machine (or in the file) that are available to connect to data, you will recognise some in the screen shot, but some are specific source files on my own machine. the location of these files on your machine can be accessed by clicking the Options... button).

We are connecting to a SQL server, so I will select the top option of 'New Data Source...' as I don't currently have a connection I need. This will launch another dialog to name the data source and select the driver.(see below), again, if you don't see what you need you will need to install the driver for that data source from the relevant program vendor.

I have selected SQL Server from the drop down and called it Local SQL (Any name you will recognise will do)

Next you will need to connect to that data source, so clicking 'Connect...' in the dialog above will present another dialog (below) to do that. (I have pressed the 'Options>>' button 9dimmed in the shot below), which expands the options to connect to the database. this is a good idea if this connection is only needed to connect to one. In my case AS400UK). I have selected the (local) server, (That's the one on my machine) and used my trusted 'single windows sign-on' connection.

Once you clear the dialog above, you will be re-presented with the 'Create New Data Source' dialog (two above), with the option to select a specific table or query (view) in the database. I leave this blank to give me a utility connection that I can select tables from as we need. Equally we will now be returned to the first dialog in this section, but this time we will have our new source of Local SQL in the list. (see below)

If we click OK we will get a wizard taking us through the steps of selecting a table, the fields we require from that table and filtering or sorting them. For the purposes of this we will not be filtering or sorting at this stage (steps 2 and 3), but below is the first screen with the CatHeader table selected and four fields within that table moved across to appear in our data.

On the fourth screen we could simply select to return the data to Excel and that would be done, with any filtering and sorting done earlier. We want to apply parameters to our data though, so we need to select 'View data or edit query in Microsoft Query' at this stage. (As below).

Clicking finish gives us our first view of the MS Query interface with our data in place.

We need now to set our parameters and to do that we go to the add criteria button (above) which adds a grid to the interface (below). In here we need to select the field we wish to add the parameters to and enter the parameters. To enter parameters you simply put the square braces around where you want the parameters values to be placed, so in our case of wanting to get data between two dates we can enter:

Between [Start] And [End]

It matters not what you call the placeholders in the query, we could have replaced 'Start' with 'a' for example. (without the quotes)

The values we enter in the worksheet will replace the Start and End parameters. It ends up looking like below.

Next we go to 'File' and select 'Return data to Microsoft Office Excel'. This will fire a dialog to enter each of the parameter values (See below). As we are getting these from the worksheet you should enter nothing and just click 'OK' to all prompts.

After this you will be asked where in the worksheet you wish to place the data or whether you want to put it on another worksheet, but... WE DON'T WANT TO DO THAT YET!

Before doing that your should press the 'Properties...' button in the dialog (1st below) and on the resulting dialog's 'Definition' tab (2nd below), you will notice the 'Parameters...' button has 'lit up'.

Press this and you get a dialog giving you three options. (See below)

Prompt for value using the following string (new in 2007), allows you to have Excel fire a dialog and for you to have a customised message, say... "Enter the Start Date"
Use a 'hard coded' value every time
Get the value from a worksheet cell. (This is the option we want). You simply enter a cell reference to the data for each parameter in the list on the left and that's it!

To make the data refresh each time the parameter changes you should check the box at the bottom (I always do this, but if you have a very long running query it could be a problem)

Simply then, you just need to change the values in the cells and the data below will reflect the new values. (see below)

That's about it for data interchange in Excel, remember though that in Excel 2007, you also, unlike previous versions can easily re-use data connections and edit them, right here on the 'Data' tab of the new ribbon.

Excel List of Lists

Nick Hodge — Mon, 08 Sep 2008 07:55:00 GMT

John Walkenbach on his new Spreadsheet Page has posted what he believes is a comprehensive list of Excel Blogs with RSS feeds.

You can find it here

Thankfully I made the list, but can you point us toward any that are missing?

It's a nice job where you can select the blog in the left side bar and see the latest posts and comments all in one place.

There are certainly several others on here he has missed but not to many that are active :-(

Deploying VSTO Add-In To Multiple Users On Windows XP

Nick Hodge — Fri, 22 Aug 2008 07:37:00 GMT

Matthew from Australia kindly dropped by to outline a solution to a tricky situation with VSTO deployment to multiple users on Windows XP.

I don't profess to know the issue, but it seems like it should get a wider audience, hence the post.

Here is the link to Matthew's solution.

My Coding Technique

Nick Hodge — Fri, 15 Aug 2008 07:01:31 GMT

I have recently been doing most of my coding in Visual Studio, doing some 'real' work for a change, coding using VB.net, ASP.net and ADO.net.

The environment in VS2008 is much improved over the VBE in MS Office (VBA). For example, it auto-indents and, with the addition of Developer Express's Coderush product it even adds helpful lines between these indents. In large code projects it becomes a real chore to keep pressing the tab key to indent, but it is pretty essential if you are going to be able to easily 'read' and debug your code. Below is an example of my code in VS2008.

Another thing you also soon notice is the huge nature of the .NET framework meaning that if you don't 'type' your variables (that is declare them strongly as a certain 'type') you get little or no help at all and that makes the whole scenario impossible. (Well not impossible, but improbable if you are not the sort of person who can memorise Pi to 120 places or something!).

This brings me to my point in VBA.

When I first started coding, as with most I suspect, you simply recorded the code with the macro recorder and then amended that to get rid of all the unnecessary defaults it records. You then move onto hand coding but a little like this. (...again I suspect)

Sub Demo()
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveWorkbook.Worksheets("Sheet1").Name = "Data"
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value = "Nick"
ActiveWorkbook.Worksheets("Sheet1").Range("B1").Value = "Hodge"
'Now you could risk this...
Range("C1").Value = Range("A1").Value & " " & Range("B1").Value
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Now, the above code will work (I know, why the application..... It's just to prove a point), but two finger typing makes it very slow and painful to write and often, when using the objects, intellisense gives you no clues as to what properties, methods, or child objects are available to you. It also makes it very laborious to debug.

The Range("C1")... is also very dangerous as if you have multiple workbooks open, or your workbook has multiple worksheets, you may find that Range("C1")... does not refer to the C1 you think it does as your code may have made another workbook active. (Remember, you are just using ActiveWorkbook).

Now consider this code, which does the same.

Sub Demo()
Dim wb As Workbook
Dim wks As Worksheet

Set wb = ActiveWorkbook
Set wks = wb.Worksheets("Sheet1")

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    
    With wks
        .Name = "Data"
        .Range("A1").Value = "Nick"
        .Range("B1").Value = "Hodge"
        .Range("C1").Value = .Range("A1").Value & " " & .Range("B1").Value
    End With
    
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

First you will notice we declare two variables (wb and wks (could be anything really)) to 'carry' the two objects (the Workbook and the Worksheet respectively). In VBA, if you have an 'Object' variable you have to 'Set' it (unlike 'value' variables which can just be assigned e.g. myVal=0) and we do this by using the ActiveWorkbook (hopefully we are sure that the activeworkbook is the correct one at this point!) and then we 'Set' the Worksheet object variable by assigning the Sheet1 worksheet. (Note that I am using wb in front of this assignment. That's because I know that wb refers to the correct workbook).

What I am sure of now is that whatever I do with wb or wks will always refer to those two objects. Whatever's active, without activating them and without selecting. Your code will run quicker and there will be no screen flashing (hence I really have no need for the Application. ScreenUpdating, etc).

The second and third benefits of this approach are that I now only refer to Workbook.Worksheets("Sheet1") as wks and, while typing wks and the period to use properties and methods of the Worksheet, I am sure to get a 'clue' from the VBE's intellisense, as below. This does not always happen when using full notation as VBA seems to lose track of the object it is using. By declaring it in a 'strong typed' way, it knows and guides you through.

The last construct that I use all the time is the With...End With block. This gives you a further shortcut when you intend to make a lot of changes to a single object, (I also use it to make changes to objects 'around' the referenced one, see Offset(...) below)

Option Explicit

Sub Demo()
Dim wb As Workbook
Dim wks As Worksheet
Dim rng As Range

Set wb = ActiveWorkbook
Set wks = wb.Worksheets("Sheet1")
Set rng = wks.Range("A1")

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    
wks.Name = "Data"
    
    With rng
        .Value = "Nick"
        .Offset(0, 1).Value = "Hodge"
        .Offset(0, 2).Value = .Value & " " & .Offset(0, 1).Value
        With .Font
            .Bold = True
            .Color = RGB(0, 0, 0)
        End With
        .Interior.Color = RGB(255, 0, 0)
    End With
    
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

You'll notice we've added a rng variable, that refers to a Range object and then we 'set' that to the range A1 on Sheet1 (using our wks variable that we know refers to that sheet in the ActiveWorkbook (wb)). We then use that rng object in a With...End With block to assign stuff to that object. (notice too I use an internal With...End With block to assign stuff to the Font object). You'll also notice I moved the wks.Name line out of the block as it is no longer referring to the wks but the rng. Of course I could have incorporated the .Name into the rng block, by using

.Parent.Name="Data"

As the Worksheet (wks), is the 'Parent' object of the Range (rng) object.

Maybe the image below will help to show the components parts.

That's just a few pointers in what I do to make my code shorter, more readable, efficient and easier to debug. It also has the spin off in making the VBE tool work for you instead of against you.

Just my slant on things... Comments.

Alternative Pivot Tables

Nick Hodge — Thu, 24 Jul 2008 22:05:25 GMT

As many of you know, I don't do anything with technology 'in earnest', but love tinkering and it is great to have a 'project' with a functional 'end game'.

Recently, I have been a little 'off' my usual Excel activity and developing (for fun) in the evening, on trains, etc, some additional reporting for our Company Intranet, using VB.Net, ASP.Net and SQL Server. (I had already done some, but it called an iSeries server in the US, from the UK and as it has become very popular with staff I have decided to get the data onto a local SQL server and work it from there).

As an MVP I am lucky to be given an NFR licence for a great set of .Net controls from DevExpress and during the development of this stuff I have used their ASPxPivotGrid control. The features of this thing are amazing and the developer story is excellent. Now, I'm not trying to put the Excel unit down, as that for intuition, usability, etc is excellent, particularly in it's 2007 implementation, but let me run you down a few of the things this grid has that Excel could certainly do with.

Linked Fields

The ability to join two or more fields together in a 'group'. This feature ensures that fields which should be linked stay that way. For example a UserID and Name which should never be allowed separated. The small line, shown below, denotes they are a 'group'.

Multi-User

Just by the fact this is a web control and sits on the server it works great in a multi-user environment, The second benefit to it using the server is the speed of refresh which will pretty well always be quicker on the server that any client.

Developer Restrictable Zones

Each field can be set to show in all zones or only selected ones. This is great to stop a user adding days to the column area which can result in hundreds or thousands of columns, not too desirable. For example, in the demo here, you cannot add the 'Date' field to the 'Value' or 'Column' areas.

Single-Click Sorting

Click on a header and the column or row are sorted ascending, click again and it's done descending.

Paging

With the pager you can simply restrict the 'depth' to which the data goes down. That's an essential on the web, to keep stuff 'above the fold', but wouldn't that be nice in excel too?

Separate Field, DataField and Caption Properties

How often have you either had to 'put up' with either a duff name in the data source, or the same in the Pivot Table itself as Excel allows only unique names in fields, so for example, OrderValue in the database, becomes say 'Sum of OrderValue' in the table, it's long and ugly, so you try just changing it in the P/T back to OrderValue and Bang! In this grid you can display the caption property, have another (or same) name for the DataField property and yet another as the fieldname to refer to in code.

Drag and Drop

Using Ajax callbacks you can simply drag any field to any zone (if allowed by the developer) and when you see the little arrows, drop it and you field will appear there.

Obviously there are standard things like right click and collapse or expand a fields or all fields and filtering with a drop-down on what we would know as 'page fields' in excel (now called report filters)

The demo does not have any formatting but there are tons of 'skins' for it or you can use your own CSS. I'm not a design guy, more interested in the data, but I have it looking really attractive on our intranet.

As a developer story the possibilities are almost endless with so many 'switches' on each column, the whole grid and events on the client (JavaScript) or the server (any .Net language).

I have to say, I was impressed with the Excel P/T and still am. I love them, but this has a great UI story and most importantly, a great developer one too. Try out my basic demo using some (slightly weird) stats from this site (live) but go and look at the full potential at their site

Thank you DevExpress!

COUNTIF SUMIF Limitations

Nick Hodge — Fri, 27 Jun 2008 18:05:00 GMT

I'm sure many of you knew this but it was new to me and I came across it today, so in an effort to get it out there a bit more, i document it here.

It concerns the fact that COUNTIF and SUMIF (and their 2007 plural counterparts SUMIFS and COUNTIFS) carry forward the limitation that Excel has of 15 significant digits. (As does 1-2-3 and OpenOffice)

http://support.microsoft.com/kb/269370

I knew this limitation, but didn't realise the two functions concerned followed this limit'

I had to build an automated reconciliation model to delete off matching records. It operates through several iterations but my first one was to concatenate Date, Customer Number and Credit/Debit amount and then do a COUNTIF on that 'Key'. If this resulted in 2, then I could presume a 'match' and delete the record.

Imagine this then. I have data as the screenshot below

In D3 is the formula =A3&B3&C3
In E3 (as you can see) is the formula =COUNTIF(D:D,D3)
In F3 is effectively the same formula as in E3 but using the 2007 function COUNTIFS() (using just one criteria)
In G3 is the formula =SUMIF(D:D,D3,C:C)
In H3 is the formula =SUMIFS(C:C,D:D,D3) (Notice how in this 2007 version the parameter order is changed)
In I3 is the formula =SUMPRODUCT(--($D$3:$D$4=D3))
In J3 is the formula =SUMPRODUCT(($D$3:$D$4=D3)*($C$3:$C$4))

What COUNTIF and SUMIF (and their plural Excel 2007 equivalents) are seeing is a string in both cells (D3 and D4) that are the same as they are just taking the first 15 digits (200806101234561). Plainly, in my scenario this is incorrect. SUMPRODUCT however sees all the digits and therefore overcomes this limitation. Correctly seeing a COUNT of 1 each and a SUM of just the single values.

For a complete explanation of SUMPRODUCT you could do worse than look here

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

BTW: As a sideline, the formula using SUMPRODUCT worked about twice as fast

Summit in Seattle

Nick Hodge — Mon, 14 Apr 2008 12:45:00 GMT

It's been some time since I posted a blog post and this is a little lame when it comes to Excel, but we are all (MVPs) in Seattle at the Global Summit this week and if there is anything we are allowed to post under our NDAs I will try and post it here.

Never know until we get there what will be allowed public domain and we have to be real careful, but I will certainly be pointing the Excel Team to the v.Next forum if you still have some ideas for that.

Looking forward to a great week at the 'Excel coal face'

Playing with OpenAsTextStream

Nick Hodge — Thu, 13 Mar 2008 19:35:00 GMT

We produce individual text files for new items to go into our catalogue. These are sent to the creative people for laying out on the page. One file contains the Item#, Description, Text and Bullets for one item and is named with that item's number. Of course, for page layout it is good for them to copy and paste the text this way. It looks as below:

12345E

2500PSI 15LPM Petrol-Powered Pressure Washer

This pressure washer, powered by a Honda GX160 engine, will blast away even the most stubborn dirt with ease. Supplied with reinforced rubber hose and lance with four interchangeable nozzles.
* 2500PSI
* 15 LPM
* Comet Axial pump
* Downstream Chemical Injector
* Ship Wt: 68kg

Of course, when it comes to the website, we need a much more formal record/field (row/column) structure. The challenge today was to parse the files (iterating over a folder containing the files), which also have random blank lines into a sensible structure in Excel for uploading onto the web. Whilst not in my job description, it was a challenge I wanted to take as it was a long time since I worked with text files and I don't think I've ever worked with the FileSystem object from the Microsoft Scripting Runtime Library.

Of course, with any parsing job, the data is never straightforward. I've already mentioned the blank lines, but they also wanted the * changed to <li> to create an html bullet and the Ship Wt: line stripped off as that has it's own field. The split was across three rows, containing Item#, Item Title and Web Text.

When coding I always prefer to use early binding, where you manually set a reference via the VBE to the library you want to use, in this case the Microsoft Scripting Runtime. I do this as it gives you the advantage of intellisense if you are not sure of the object, methods, etc. So my code at development time would look like this

Sub ParseFile()
Dim fsys As FileSystemObject, fsfile As File, stream As TextStream

    'Set FileSystemObject variables (Early binding)
    Set fsys = New FileSystemObject
    Set fsfile = fsys.GetFile(sFolderName & sParseFile)
    Set stream = fsfile.OpenAsTextStream(1, 0)

End Sub

This way, just typing fsys. in the future code will fire up the properties, methods, etc of the FileSystemObject, very handy when you don't know them. Generally though I find deploying early binding is a pain, as you have to remember to set the reference on each machine before running the code, so at runtime I change the variable types to generic 'Objects' and then use the CreatObject function to create the FileSytemObject as below

Sub ParseFile()
Dim fsys As Object, fsfile As Object, stream As Object

    'Set FileSystemObject variables (Late binding)
    Set fsys = CreateObject("Scripting.FileSystemObject")
    Set fsfile = fsys.GetFile(sFolderName & sParseFile)
    Set stream = fsfile.OpenAsTextStream(1, 0)

End Sub

The OpenAsTextStream method of the File object is very nice to use, you can either read or write the file, you can check each character, complete lines or all the text at once. You get AtEndOfLine and AtEndOfStream boolean properties of the stream object so you can process until one and/or both of these properties are true.

Now I'm making no claims for quality of coding, but the below is the full, deployed code that works for the files we produce. In testing too, it parsed the data from 150 txt files into the correct columns and rows in just a few seconds, much faster than the old copy and paste!

Option Explicit

Sub ParseFile()
'**************************************************************************
' Code written by Nick Hodge 12th March 2008
'V1.0 into production same day. versions below.
'V1.1 added error handling and check for file name ending in 'E' (13/3/2007)
'V1.2 added checking for * as first character of line and change it to <li>
'for an html bullet, also strip out line starting Ship Wt. (13/3/2007)
'**************************************************************************

'Navigate to a folder and the code will iterate over any text files taking the first
'line with text in it and assigning it to the strLine1 variable. It does the same for
'second and third lines and then assigns them to columns 1, 2, 3 in a new workbook
'laying them out in proper field/record format for importing to web
'File format should have item number as first line (no matter if blank lines above
'Product title on second line, no matter if blanks above and rest of copy after that

'File and folder name variables
Dim sFullPathFilename As String, sFolderName As String, sParseFile As String
'New workbook and worksheet variables
Dim wb As Workbook, wks As Worksheet
'Counter to count iterations, used in row placement on sheet and counter for postion
'of slash in path name (before filename)
Dim lCounter As Long, lSlashPosition As Long, iwksCount As Integer
'Filesytem object variables to hold file, textstream, etc.
Dim fsys As FileSystemObject, fsfile As File, stream As TextStream
'Variable to hold lines of text for later assignment to worksheet
Dim strLine1 As String, strLine2 As String, strLine3 As String, strTemp As String
'Booleans to stop loop after line 1 found, etc.
Dim bLine1Fnd As Boolean, bLine2Fnd As Boolean

On Error GoTo errHandler

'Get folder to use by clicking on file
sFullPathFilename = Application.GetOpenFilename("Text Files, *.txt", , "Select any file in the folder")

'Check to see selection made
If sFullPathFilename = "False" Then
    MsgBox "You must click on any file in the folder", vbOKOnly, "No Folder Selected"
    Exit Sub
End If

'Get position of last slash and parse folder from this
lSlashPosition = InStrRev(sFullPathFilename, "\", -1)
If lSlashPosition = 0 Then
    MsgBox "We cannot recognise the folder name, exiting procedure", vbOKOnly + vbExclamation, "Error"
    Exit Sub
End If

'Open workbook and set variables
Set wb = Workbooks.Add
Set wks = wb.Worksheets("Sheet1")

'Change sheet name
wks.Name = "Data"

'Switch off application events
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
   
'Delete surplus worksheets
With wb
    For iwksCount = .Worksheets.Count To 1 Step -1
        If .Worksheets(iwksCount).Name <> "Data" Then
        .Worksheets(iwksCount).Delete
        End If
    Next iwksCount
End With

'Set headings and bold
With wks
    .Range("A1").Value = "ItemNo"
    .Range("B1").Value = "Title"
    .Range("C1").Value = "WebText"
    .Rows("1:1").Font.Bold = True
End With

sFolderName = Left(sFullPathFilename, lSlashPosition)

'Start iteration of folder using folder name and files ending in E.txt
sParseFile = Dir(sFolderName & "*E.txt")

'Intialise counter to 2 which starts it on second row, below titles
lCounter = 2

'Do for each file Dir function is called recursively and returns "" when no more files match
Do While sParseFile <> ""

'Initialise booleans
bLine1Fnd = False
bLine2Fnd = False

    'Set FileSystemObject variables (Late binding)
    Set fsys = CreateObject("Scripting.FileSystemObject")
    Set fsfile = fsys.GetFile(sFolderName & sParseFile)
    Set stream = fsfile.OpenAsTextStream(1, 0)

    'Do so long as the endoffile marker is false, sets first line of text
    'Accounts for blank lines
    Do While stream.AtEndOfStream = False
            Do While bLine1Fnd = False
            strTemp = stream.ReadLine
                If strTemp <> "" Then
                    strLine1 = strTemp
                    bLine1Fnd = True
                    strTemp = ""
                End If
            Loop
            'sets second line of text, accounts for blank lines
            Do While bLine2Fnd = False
            strTemp = stream.ReadLine
                If strTemp <> "" Then
                    strLine2 = strTemp
                    bLine2Fnd = True
                    strTemp = ""
                End If
            Loop
            'Once 1st and 2nd lines are full then take averything else, line by line
            strTemp = stream.ReadLine
            'Check if line is ship wt., if so discard
                If Left(UCase(strTemp), 6) = "* SHIP" Then strTemp = ""
                'Build 3rd lines changing * to <li> for html bullet, else just add the next line to the copy
                If strTemp <> "" Then
                    If Left(strTemp, 1) = "*" Then
                        strTemp = "<li>" & Mid(strTemp, 2, Len(strTemp) - 1)
                        strLine3 = strLine3 & strTemp & Chr(10)
                    Else
                        strLine3 = strLine3 & strTemp & Chr(10)
                    End If
                End If
                
    Loop
    
    'Close stream and release object variables
    stream.Close

    Set stream = Nothing
    Set fsfile = Nothing
    Set fsys = Nothing
    
    'Assign the strings to worksheet
    With wks
        .Cells(lCounter, 1).Value = strLine1
        .Cells(lCounter, 2).Value = strLine2
        .Cells(lCounter, 3).Value = Left(strLine3, Len(strLine3) - 1)
        .Columns.AutoFit
        .Columns(3).ColumnWidth = 200
        .Rows.AutoFit
    End With
    
    'Clear strings for next iteration
    strLine1 = ""
    strLine2 = ""
    strLine3 = ""
    
    'Increment counter so text is placed on next row
    lCounter = lCounter + 1

    'Start next iteration of files in folder
    sParseFile = Dir()

Loop
    
    'Turn application events back on
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

MsgBox " Successfully parsed " & lCounter - 2 & " *.txt files" & Chr(13) & "From " & sFolderName, _
    vbInformation + vbOKOnly, "Parsed Successfully"

Exit Sub

'Error handling routine to exit gracefully
errHandler:

MsgBox "There has been an error, please advise add-in creator" & Chr(13) & _
    "The routine failed on: " & sParseFile, vbOKOnly + vbCritical

If Not wb Is Nothing Then
    wb.Close SaveChanges:=False
End If

With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

Exit Sub

End Sub

If you want to download and try the file (Excel 2007 Only) I have attached the working xlsm file to this post (It is actually deployed as an xlam add-in) and 20 demo text files. Just place the text files in a folder and then go to the Marketing tab (Yes, I even wrote a ribbon customisation for it!) and press the button. It will ask you to navigate to any file in the folder and the will take each file in turn using the Dir() function and parse the text into a new Excel workbook.

Questions on customising the ribbon

Nick Hodge — Sun, 02 Mar 2008 10:52:00 GMT

I have, via the blog contact form received three questions from Milan, these were

Can you remove the Office Button?
Can you link a combobox (on the ribbon) to a cell/range?
Can you link a label (on the ribbon) to a cell?

The answer to No.1 was answered in the 2nd of my series on customising the ribbon. In the section under Full Dictator Application.

No. 2 is yes you very much can but it is not a simple process like having a 'link' property or similar. The write-up below demonstrates how you would take a range of cells (BoxList @ $A$!:$A$10) to fill the combobox and then display the selected item in a cell on the worksheet ($D$1).

A comboBox has a DropDown (so is similar to a dropDown ribbon control), but allows extra entries to be typed in at runtime.

The first thing we need to do is set up the RibbonX in the file (for detail, see here). For this demo I am setting startFromScratch="true", so all we should have from the below RibbonX is a single tab, with one group and one comboBox. Notice too, that at this stage the comboBox is empty. This is because we will be building this when the workbook opens, using a Workbook_Open() event

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="true"> 
        <tabs> 
            <tab id="myTab1" label="Demo Tab" visible="true">
                    <group id="Group1" label="Combobox" visible="true">
                    <comboBox id="Combobox1" label="Cell Link" getItemID="cmb_getItemID"
                        getItemLabel="cmb_getItemLabel" getItemCount="cmb_itemCount" 
                            onChange="cmb_onChange"/>
                    </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

Notice that we are using four 'callback' attributes to populate the comboBox and to 'link' it to a cell. These are used for the following reasons:

getItemID() = Used to 'set' the unique ID necessary for every control on the ribbon (It is tough to think that the names 'getxxx' are actually better though of as 'set'. It is meaning the Ribbon needs to 'get' rather than you need to 'set' if that makes sense?)
getItemLabel() = Used to 'set' the text of each item displayed in the control
getItemCount() = Used to 'set' the number of items the control should expect to display.
onChange()= Used to provide the 'link' to a cell on the worksheet. This fires each time the control is changed.

Next we generate the callback signatures for these by pressing the button in the Custom UI Editor. You will notice below that the UI editor does not generate a getItemID callback. this is a bug, the signature is given below to help you.

'Callback for Combobox1 getItemID (NOT GIVEN BY CALLBACK GENERATOR)
Sub cmb_getItemID(control As IRibbonControl, index As Integer, ByRef ID)
End Sub

'Callback for Combobox1 getItemLabel
Sub cmb_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
End Sub

'Callback for Combobox1 getItemCount
Sub cmb_itemCount(control As IRibbonControl, ByRef returnedVal)
End Sub

'Callback for Combobox1 onChange
Sub cmb_onChange(control As IRibbonControl, text As String)
End Sub

We will use a Workbook_Open() event to build the array of items to load into the control. This event, as the name implies, fires when the workbook is opened. It is placed in the 'ThisWorkbook" module.

Option Explicit
'There are two global variables at the top of a standard module
'iItemcount and vRngValues

Private Sub Workbook_Open()
'Counter variable
Dim x As Integer
'Counts the cells in the range, used later to set no. of Items in control.
iItemcount = Worksheets("Sheet1").Range("BoxList").Cells.Count - 1
'Re-Dimension the dynamic array to it's actual size.
ReDim vRngValues(iItemcount)
'Build the array
For x = 0 To iItemcount
    vRngValues(x) = Worksheets("Sheet1").Cells(1 + x, 1).Value
Next x
End Sub

Now we will have the range 'BoxList' assigned to a one-dimensional array and the count of items, in public variables available to all code in the project. (remember this for later)

First let's advise the control how many items we will need in the control, using the global integer variable iItemcount (built in the Workbook_Open() event) and the getItemCount() callback, as below. (The comments explain what the procedure does, in fact we are using just one line of code!). These callbacks are all placed in a standard module.

'Callback for Combobox1 getItemCount
Sub cmb_itemCount(control As IRibbonControl, ByRef returnedVal)
'Use the global variable collected in the Workbook_Open()
'assigning it to the returnedVal parameter
    returnedVal = iItemcount
End Sub

Next, as each item in the control (and the control itself for that matter) need a unique ID we will build this next using the getItemID callback, as below. Again the comments explain what is happening.

'Callback for Combobox1 getItemID
Sub cmb_getItemID(control As IRibbonControl, index As Integer, ByRef ID)
'Set the ID parameter in the callback signature to each 'index'
'in the signature. We are adding cmbBox text to make it unique
'as using just a number may clash with other controls/items
'indexes in controls are zero based
    ID = "cmbBox" & index
End Sub

Next we will write the last piece of code to actually build it (the fourth will be used as we use the control). This is the getItemLabel callback (below) that 'sets' the actual text that will be displayed in the combobox. (Again, just one line of code!)

'Callback for Combobox1 getItemLabel
Sub cmb_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
'Sets the returnVal parameter, which carries the text back to the control
'This fires for each control 'item', so the 'index' parameter
'provides a zero based index for the current item, which we use in the code
    returnedVal = vRngValues(index)
End Sub

Lastly, we will use the onChange() callback to assign the selected option from the comboBox, to a cell (D1) on our worksheet.

'Callback for Combobox1 onChange
Sub cmb_onChange(control As IRibbonControl, text As String)
'Set the 'text' parameter to a cell value
    Worksheets("Sheet1").Range("D1").Value = text
End Sub

That's all you need, job done! The image below shows the result.

Lastly, question No.3. I am hoping that the questioner was referring to the labelControl. This is used simply as a textual control that can act as a label to anything else on a group. If so then yes, all we need to do is set the control's getLabel attribute using it's callback. I have added the ribbonX to the previous example, (it's just one line) and the callback, added to a standard module is below that, which picks up the value from D2.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="true"> 
        <tabs> 
            <tab id="myTab1" label="Demo Tab" visible="true">
                    <group id="Group1" label="Combobox" visible="true">
                    <comboBox id="Combobox1" label="Cell Link" getItemID="cmb_getItemID"
                        getItemLabel="cmb_getItemLabel" getItemCount="cmb_itemCount" onChange="cmb_onChange"/>
                    <labelControl id="myLabel" getLabel="lbl_getLabel"/>
                    </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

The getLabel VBA callback

'Callback for myLabel getLabel
Sub lbl_getLabel(control As IRibbonControl, ByRef returnedVal)
'Assigns the value in cell D2 to the returnVal parameter
    returnedVal = Worksheets("Sheet1").Range("D2").Value
End Sub

The resulting label from cell D2

Hopefully this goes someway to answering the OP questions and helps to further everyone's understanding of customising the Ribbon in Office 2007.

More to come.

Maybe faulting add-ins are a thing of the past?

Nick Hodge — Fri, 22 Feb 2008 09:12:00 GMT

I was intrigued to see, under the threat from competition commissions around the World, that Microsoft are opening up parts of their code-base and engineering to competitors to make the whole operating system and main applications (Office is mentioned), work better with 3rd party products.

I for one hope people like Adobe gets involved with this as a good proportion of the posts in the public GrashesGPFs group on MSFT's servers, relates to faulting add-ins opening with Excel.

Hopefully good news?

Here is the article from Reuters.

http://uk.reuters.com/article/technologyNews/idUKWNAS191120080222?feedType=nl&feedName=ukmorningdigest

Ribbon, Step-by-Step. Part 4 (Exploring Other Controls)

Nick Hodge — Thu, 14 Feb 2008 23:56:36 GMT

So we've explored the file formats, created the shell for a dictator application, made some basic ribbon, tab, group and control changes and customised the Office menu. We've re-purposed some Microsoft controls to our own use and now we will be running at pace through some of the other controls available to you. I doubt we will cover all of them, but you can download a bunch of my files here, which demonstrate more of what you are learning here and also contain a 'popular' control and attribute listing. So...let's get started.

The Core Of Our Model

As usual we will start with our basic building blocks of declaring a customUI and the ribbon itself, it's startFromScratch attribute and a custom tab, which we will place after the built-in add-ins tab, like so:

<customUI onLoad="loadRibbon" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon startFromScratch="false">
    <tabs>
      <tab id="myTab1" label="Nick's Tab" insertAfterMso="TabAddIns" visible="true" keytip="Z">
    </tabs>
  </ribbon>
</customUI>

Notice that for the first time we have used the onLoad attribute of the customUI which will fire the callback VBA called loadRibbon. This will enable us to capture the ribbon object and change stuff later 'on the fly' by invalidating it.

Hopefully by now you are able to read and understand this basic format that makes the core of any customisation, remember, you must have an id for every control, within a collection, (remember the ribbon can only be singular, so doesn't need one), and this must be unique in your project and either is a custom (created by you) id, or a built-in (created by Microsoft) idMso. You will find this rule throughout your customisations, for example, above we use insertAfterMso meaning insert this tab after the built-in 'add-ins' one.

Adding A Group and Button

To the same code above, we can add our first custom group with a custom button. This button has it's own onAction callback to VBA to handle the code when the button is pressed. The code at this stage is as below:

<customUI onLoad="loadRibbon" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon startFromScratch="false">
    <tabs>
      <tab id="myTab1" label="Nick's Tab" insertAfterMso="TabAddIns" visible="true" keytip="Z">
        <group id="Group1" label="Button" visible="true">
            <button id="Button1" imageMso="FileOpen" keytip="A" label="Button 1" 
                onAction="OpenFileButton" screentip="This is the screentip of button1" 
                    size="large" supertip="This is the supertip of button1"/>
         </group>
    </tab>
    </tabs>
  </ribbon>
</customUI>

 visible="true">
            <button id="Button1" imageMso="FileOpen" keytip="A" label="Button 1" 
                onAction="OpenFileButton" screentip="This is the screentip of button1" 
                    size="large" supertip="This is the supertip of button1"/>
         </group>
    </tab>
    </tabs>
  </ribbon>
</customUI>

The group has an id, is visible and has a label, which is the description that sits at the bottom on the group, as the below image of our 'growing tab shows. Hopefully you can also pick the other 'bits' from the code that created it, e.g. The tab label, where it's positioned, the size of the button, (large), that we have 'nicked' the imageMso for file>open and what a screentip and supertip look like.

Having a 'large' button exposes another feature of the ribbon and that is re-sizing. It will endeavour, as above to show it using it's full size, but if the overall size of the application window does not allow, it will first drop it's label and then shrink it's size down to small.

At this stage of course, loading the file will fire the onLoad callback and as there is no code it will return an error that the relevant code is not available. We'll take a look at the callback code once we have built our tab.

Buttons, Boxes and Separators.

One button in a group, as in the previous section is fine, but as soon as you add more buttons in a group, coupled with the 're-size' effect and we must use some 'hidden' controls, to stop the controls from flowing in undesirable directions. We use Boxes to achieve that, together with separators. Below is the code to add a new group ("Group2") and two 'sets' of three buttons, in vertical boxes with a separator dividing the two 'sets' of three. Whilst you cannot see the boxes, I have drawn them in the resulting tab image below the code, so you can visualise it in your own mind. You can of course see the 'vertical' separator. (It is only a vertical separator as it sits between two vertically aligned boxes, even in the code as you can see!).

<customUI onLoad="loadRibbon" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="false"> 
        <tabs> 
            <tab id="myTab1" label="Nick's Tab" insertAfterMso="TabAddIns" visible="true" keytip="Z">
                <group id="Group1" label="Button" visible="true"> 
                    <button id="Button1" imageMso="FileOpen" keytip="A" label="Button 1"
                        onAction="OpenFileButton" 
                        screentip="This is the screentip of button1" 
                        size="large" supertip="This is the supertip of button1"/> 
                </group>
    
                <group id="Group2" label="Boxes and Buttons">
                    <box id="box1" boxStyle="vertical"> 
                        <button id="button1B" label="Button 1" onAction="ButtonPressedMsgBox"/> 
                        <button id="button2B" label="Button 2" onAction="ButtonPressedMsgBox"/>
                        <button id="button3B" label="Button 3" onAction="ButtonPressedMsgBox"/>
                    </box> 
                    <separator id="separator1"/> 
                    <box id="box2" boxStyle="vertical">
                        <button id="buttonA" label="Button A" onAction="ButtonPressedMsgBox"/>
                        <button id="buttonB" label="Button B" onAction="ButtonPressedMsgBox"/> 
                        <button id="buttonC" label="Button C" onAction="ButtonPressedMsgBox"/>
                    </box> 
                    </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

If we now do the same with two sets of buttons in horizontal boxes, you will hopefully see the difference, both in the code and the ribbon image. Remember, horizontal buttons do not have a corresponding horizontal separator, separators do not have a style attribute...maybe v.Next!

<customUI onLoad="loadRibbon" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="false"> 
        <tabs> 
            <tab id="myTab1" label="Nick's Tab" insertAfterMso="TabAddIns" visible="true" keytip="Z">
                <group id="Group1" label="Button" visible="true"> 
                    <button id="Button1" imageMso="FileOpen" keytip="A" label="Button 1"
                        onAction="OpenFileButton" 
                        screentip="This is the screentip of button1" 
                        size="large" supertip="This is the supertip of button1"/> 
                </group>
    
                <group id="Group2" label="VBoxes and Buttons">
                    <box id="box1" boxStyle="vertical"> 
                        <button id="button1B" label="Button 1" onAction="ButtonPressedMsgBox"/> 
                        <button id="button2B" label="Button 2" onAction="ButtonPressedMsgBox"/>
                        <button id="button3B" label="Button 3" onAction="ButtonPressedMsgBox"/>
                    </box> 
                    <separator id="separator1"/> 
                    <box id="box2" boxStyle="vertical">
                        <button id="buttonA" label="Button A" onAction="ButtonPressedMsgBox"/>
                        <button id="buttonB" label="Button B" onAction="ButtonPressedMsgBox"/> 
                        <button id="buttonC" label="Button C" onAction="ButtonPressedMsgBox"/>
                    </box>
                    </group>

                <group id="Group3" label="HBoxes and Buttons">
                    <box id="box3" boxStyle="horizontal"> 
                        <button id="button1C" label="Button H1" imageMso="FileOpen" onAction="ButtonPressedMsgBox"/> 
                        <button id="button2C" label="Button H2" imageMso="FileOpen" onAction="ButtonPressedMsgBox"/>
                    </box>
                    <box id="box4" boxStyle="horizontal">
                        <button id="buttonHA" label="Button HA" onAction="ButtonPressedMsgBox"/>
                        <button id="buttonHB" label="Button HB" onAction="ButtonPressedMsgBox"/> 
                    </box>
                    <box id="box5" boxStyle="horizontal">
                        <button id="buttonHZ" label="Button HZ" onAction="ButtonPressedMsgBox"/>
                        <button id="buttonHX" label="Button HX" onAction="ButtonPressedMsgBox"/> 
                    </box>
                    </group>

            </tab>
        </tabs>
    </ribbon>
</customUI>

You may notice in the above code snippets that we are using the same callback on the button onAction attribute. This allows us to make a decision based on the button pressed, using the passed 'parent buttons' id, like so. For a button callback there are three captured properties, id, context and tag. Any or all of these can be used, stored in variables, on worksheets, in range names, etc.

Sub OpenFileButton(control As IRibbonControl)
    If control.ID = "button1" Then
        'do this
    Else
        'do this
    End If
End Sub

The Gallery and dialogLauncher Controls.

Lastly for this part, we will look at two new controls in this first iteration of the ribbon. The gallery, (this is like the styles dropdown image store) and the dialogLauncher, which is a container control, carrying a button, that looks like a little diagonal arrow in the bottom right hand corner of a group, allowing you to fire a dialog (built-in or custom) on it being pressed.

The Gallery

Currently you cannot customise built-in galleries, but you can build your own and load your image collateral through the Custom UI Editor. This part is simple, just open the editor, click on the image toolbar button, navigate to your image files and select OK. This will load them in the side of the UI editor so they an be selected and loaded into the file. A gallery is once again a container control, with 'items' below it that actually hold the images and fire callbacks, etc. It can also contain buttons, but these must appear below any items and they are the menu options that sit below the gallery items, like 'New Cell Style,,,' on the cell style gallery on the home tab.

The added code is below, with the image of the resulting controls below that:

<customUI onLoad="loadRibbon" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="false"> 
        <tabs> 
            <tab id="myTab1" label="Nick's Tab" insertAfterMso="TabAddIns" visible="true" keytip="Z">
                <group id="Group1" label="Button" visible="true"> 
                    <button id="Button1" imageMso="FileOpen" keytip="A" label="Button 1"
                        onAction="OpenFileButton" 
                        screentip="This is the screentip of button1" 
                        size="large" supertip="This is the supertip of button1"/> 
                </group>
    
                <group id="Group2" label="VBoxes and Buttons">
                    <box id="box1" boxStyle="vertical"> 
                        <button id="button1B" label="Button 1" onAction="ButtonPressedMsgBox"/> 
                        <button id="button2B" label="Button 2" onAction="ButtonPressedMsgBox"/>
                        <button id="button3B" label="Button 3" onAction="ButtonPressedMsgBox"/>
                    </box> 
                    <separator id="separator1"/> 
                    <box id="box2" boxStyle="vertical">
                        <button id="buttonA" label="Button A" onAction="ButtonPressedMsgBox"/>
                        <button id="buttonB" label="Button B" onAction="ButtonPressedMsgBox"/> 
                        <button id="buttonC" label="Button C" onAction="ButtonPressedMsgBox"/>
                    </box>
                    </group>

                <group id="Group3" label="HBoxes and Buttons">
                    <box id="box3" boxStyle="horizontal"> 
                        <button id="button1C" label="Button H1" imageMso="FileOpen" onAction="ButtonPressedMsgBox"/> 
                        <button id="button2C" label="Button H2" imageMso="FileOpen" onAction="ButtonPressedMsgBox"/>
                    </box>
                    <box id="box4" boxStyle="horizontal">
                        <button id="buttonHA" label="Button HA" onAction="ButtonPressedMsgBox"/>
                        <button id="buttonHB" label="Button HB" onAction="ButtonPressedMsgBox"/> 
                    </box>
                    <box id="box5" boxStyle="horizontal">
                        <button id="buttonHZ" label="Button HZ" onAction="ButtonPressedMsgBox"/>
                        <button id="buttonHX" label="Button HX" onAction="ButtonPressedMsgBox"/> 
                    </box>
                    </group>

                    <group id="Group4" label="Gallery">
                    <gallery id="Gallery1" label="Pictures" imageMso="HeaderFooterPictureInsert"
                        screentip="What have we here?" onAction="ClickImage" columns="2" rows="3" 
                        itemWidth="150" showItemLabel="false" itemHeight="150" size="large">
                        <item id="pic1" image="pic1" label="Picture 1" screentip="This is picture1" />
                        <item id="pic2" image="pic2" label="Picture 2" screentip="This is picture2" />
                        <item id="pic3" image="pic3" label="Picture 3" screentip="This is picture3" />
                        <item id="pic4" image="pic4" label="Picture 4" screentip="This is picture4" />
                        <item id="pic5" image="pic5" label="Picture 5" screentip="This is picture5" />
                        <item id="pic6" image="pic6" label="Picture 6" screentip="This is picture6" />
                        <!--Buttons at bittom of gallery menu-->
                        <button id="galleryMenuButton1" label="Fetch pictures..." imageMso="PictureInsertFromFile" 
                            onAction="FetchPictures"/>
                        <button id="galleryMenuButton2" label="Become an MVP..." image="mvp" 
                            onAction="BecomeMVP"/>
                    </gallery>
                    </group>

            </tab>
        </tabs>
    </ribbon>
</customUI>

We can add a dialogLauncher to the group that contains this gallery control now. IT MUST BE THE LAST CONTROL IN THE GROUP! It must also of course have a button to create the action required. The additional code is below (just the gallery control part with it added) and the resultant control in the corner of the group (with the cursor next to it).

<group id="Group3" label="Gallery">
<gallery id="Gallery1" label="Pictures" imageMso="HeaderFooterPictureInsert" 
    screentip="What have we here?" onAction="ClickImage" columns="2" rows="3" 
    itemWidth="150" showItemLabel="false" itemHeight="150" size="large">
    <item id="pic1" image="pic1" label="Picture 1" screentip="This is picture1" />
    <item id="pic2" image="pic2" label="Picture 2" screentip="This is picture2" />
    <item id="pic3" image="pic3" label="Picture 3" screentip="This is picture3" />
    <item id="pic4" image="pic4" label="Picture 4" screentip="This is picture4" />
    <item id="pic5" image="pic5" label="Picture 5" screentip="This is picture5" />
    <item id="pic6" image="pic6" label="Picture 6" screentip="This is picture6" />
    <!--Buttons at bittom of gallery menu-->
    <button id="galleryMenuButton1" label="Fetch pictures..." imageMso="PictureInsertFromFile" onAction="FetchPictures"/>
    <button id="galleryMenuButton2" label="Become an MVP..." image="mvp" onAction="BecomeMVP"/>
</gallery>
  <!--This group has a dialogBoxLauncher and button control which adds-->
  <!--the little arrow on bottom right of group Make it last control element in group-->
  <dialogBoxLauncher>
    <button id="myLauncher" label="Dialog Launcher" screentip="This is a screentip" supertip="This is a supertip"
    onAction="LaunchDialog" />
    <!--The dialogBoxLauncher is a container control-->
    <!--It needs a button control to add an onAction attribute-->
  </dialogBoxLauncher>
</group>

That's going to be all for this part, in the final part we will explore the remaining popular controls, take a little more look at the callback signatures and attempt to invalidate the ribbon and change controls on the fly. Hopefully you're enjoying it...stay tuned!

Ribbon, Step-by-Step. Part 3 (The Office Menu and Re-purposing)

Nick Hodge — Sun, 03 Feb 2008 22:54:00 GMT

In our first two parts we covered the file structure of Excel 2007 and some very simple customisations which, in the main, do not require any VBA code. in this part we are going to try and accomplish two things.

Customisation of the Office Menu (under the office button)
Re-purposing built-in controls to do other things.

In the next one or two parts we will cover more details on the various controls, including Galleries and Contextual tabs and then how we can capture the ribbon object and change controls 'on the fly', so... on with Part 3

Customising the Office Menu

As we have already discovered, the access point to this menu, the Office Button, cannot currently be modified (although as we saw in Part 2, it can be removed), beneath this button is the Office Menu, which is analogous with the 'File' menu in previous versions.

In it's standard form, it looks as below. The controls with a simple image, such as 'New', are 'button' controls. Those with an arrow at the side are defined as 'splitButton', with 'menu' and 'button' controls on the fly-out menu presented from the 'splitButton' deployment.

As with all customUI code, the RibbonX starts with <customUI></customUI> tags and beneath that <ribbon></ribbon> tags. The <officeMenu></officeMenu> tags sit just below this and before any <tab></tab> members, like so: (notice we have left startFromScratch attribute off, so the code presumes "false", e.g. keep the existing ribbon):

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

The controls we require on the Office Menu, will sit between the <officeMenu></officeMenu> tags. in this example we will add one simple button, one split button with a 'sub menu' beneath, with button and checkbox and disable one of the built-in buttons.

To add a button we simply specify a new 'button' control and set a unique id, like so: (The label attribute sets the actual text that appears on the 'menu' item).

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <officeMenu>
    <button id="OfficeButton0" insertBeforeMso="FileSave" imageMso="HappyFace" label="New Button" onAction="MBox"/>
    </officeMenu>
</ribbon>
</customUI>

NOTE:You may be wondering where you get all the existing control names, such as the "FileSave" above. It is actually pretty simple. If you go to Office Button>Excel Options>Customize, you can navigate to the command you want and hover over it and the tooltip that appears in braces (FileSave), is exactly (including case) how the control should be addressed as a parameter to the attribute.. below is the tooltip. You can use the same name for any imageMso, to get the built-in icon too.

Now as we grow in confidence, we will add the two 'splitButtons', with their related 'menu' and button' controls below. both these buttons are custom ones, declared with just an id (as apposed to a built-in one that would be declared idMso). Here is the code complete:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <officeMenu>
    <button id="OfficeButton0" insertBeforeMso="FileSave" imageMso="FileSave" 
            label="New Button" onAction="MBox"/>
      <splitButton idMso="FilePrintMenu">
        <menu>
          <button id="OfficeButton1" insertBeforeMso="FilePrintPreview" imageMso="HappyFace"
            label="Happy Print" description="Prints a random joke in the footer of each document" onAction="MBox"/>
        <checkBox idMso="ViewGridlinesToggleExcel"/>
        </menu>
      </splitButton>
    <button idMso="FileClose" enabled="false"/>
    </officeMenu>
</ribbon>
</customUI>

So, reading the code top down we have

A button (OfficeButton0), inserted before the 'Save' button with the 'Save' icon.
A sub menu on the splitButton 'Print' menu, with a button (OfficeButton1), inserted before the print preview button, with a Happy Face built-in icon.
The same 'sub-menu' also has a checkbox, which is the built-in one to display Excel Grid Lines. (see the idMso means a built-in control)
The 'Close' button ("FileClose") at the base of the Office menu is disabled.

The image below show the 'New Button', the disabled 'Close' button, the HappyFace button and checkbox on the 'sub menu'

You will have noticed the first use of 'Callbacks' in the parameters above, e.g. 'onAction'. There are many of these, but let's settle on the main one at the minute which is onAction. This defines the name of the VBA code that should run when the control (button, etc) is pressed, clicked or whatever the default 'action' is.

Working with 'callbacks' could not be simpler, when using the Office 2007 Custom UI editor. You simply press the 'callback' toolbar button and it will create a new tab with all the code for all the 'action' callbacks found in the RibbonX code. Now you can simply copy this from the callback pane and paste it into either standard or class modules in your workbook and write code between the stubs to connect actions to the controls.. (when pressing the callbacks button in our example I got the following code stub).

'Callback for OfficeButton0 onAction
Sub MBox(control as IRibbonControl)
End Sub

Why only one stub when there are two 'onAction' parameters? It's because all the 'onAction' parameters are asking for the same VBA code (MBox), so we can use just one code stub. This gives us an IRibbonControl object as a variable called 'control', which we can use to interrogate which control fired the MBox code, using the id property, thus:

'Callback for OfficeButton0 onAction
Sub MBox(control As IRibbonControl)
If control.ID = "OfficeButton1" Then
    MsgBox "Why did the chicken cross the road?", vbQuestion + vbOKOnly, control.ID
Else
    MsgBox "Nicole Smith is Dead!", vbExclamation + vbOKOnly, control.ID
End If
End Sub

This would give us the relevant message box depending on the id of the 'pressed' button.

Re-Purposing Existing Controls.

As you will hopefully be starting to realise, many things you want to do with the Ribbon are starting to work out fairly simply and re-purposing existing commands is no different. You will see a clue in the text just gone. The member we will be dealing with here is the 'command', which, because Excel obviously has many 'commands' the 'command' member 'nests' within the 'commands' collection member. (Like a 'tab' is contained within the 'tabs' collection).

You identify the commands in exactly the same way as we did above, by looking for it's idMso in the customize dialog's list. Once you have this idMso, there is little left to learn. The code below disables the 'Italic' functionality on the ribbon, by setting it's 'enabled' attribute to "false" and then re-purposes the 'Save' (FileSave) menu with a custom 'OnAction' attribute. This will override the built-in save functionality in Excel. Below that is a screen shot of the disabled Italics button and the dialog 'fired' when pressing 'Save'.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <commands>
        <command idMso="Italic" enabled="false"/>
        <command idMso="FileSave" onAction="MySave"/>
    </commands>
</customUI>

I hope, now we have covered RibbonX a few times, that little explanation is needed for this procedure, but for completeness, below is the VBA code 'callback' generated by the CustomUI tool and placed in a standard module in the workbook.

'Callback for Save onAction
Sub MySave(control As IRibbonControl, ByRef cancelDefault)
    MsgBox "Sorry, like me, it can't be saved", vbExclamation + vbOKOnly, "Bog Off"
End Sub

As you can see, you will get an IRibbonControl object to identify the firing control and a parameter enabling you to enable or disable the default action (default is cancel=true). This enables you to only re-purpose on the pressing of a certain custom control, for example.

This will be enough for this part. next time we will extend our knowledge of other controls and then in the final part we will move on to changing control state on the fly by capturing the Ribbon object and invalidating it.

Stay tuned for the final one or two episodes! ;-)

Ribbon, Step-by-Step. Part 2 (Begin Customisation)

Nick Hodge — Thu, 31 Jan 2008 23:46:00 GMT

In the last part of this series, we went through the different file formats as a precursor to starting Ribbon customisation. You should by now have downloaded the Office 2007 Custom UI Editor, as this is the tool we will be using to customise our RibbonX and generate callbacks for VBA. We'll start with a run down of the tool itself. Remember, you need to have the .Net 2.0 framework, or above loaded on your development machine.

The Office 2007 Custom UI Editor

Without worrying too much about the code currently in the RibbonX pane (It is much easier to read when made wider, but the confines of this blog needs it narrower and causes the code to wrap), let's just familiarise ourselves with the various parts of the tool. This shouldn't take long as the interface is basic and un-cluttered with just the very necessary of options. Of note is the image store, this is where the collateral for new controls, like the 'gallery' are stored for placing in the correct place in the file. The toolbar is pretty self-explanatory, except maybe the 'Validate' button, which goes off to the framework and validates your RibbonX code for valid members and attributes and that it is well 'formed'.

Then there's the 'Generate Callback' button, which scans your code for 'action' attributes, like getImage, and generates code stubs for them all. More on that later, but below is the 'Callback' tab that is generated by pushing this button and the code 'stubs' that can just be cut and pasted.

Lastly, before we progress with our first code, I want to draw attention to the 'Sample' menu option (see below). When you install the tool a few are there, but by navigating to the 'Samples' folder in the installation directory, you can place XML files which will appear as sample in the list. (On my machine this is C:\Program Files\CustomUIEditor\Samples), a handy feature to store your frequently used RibbonX.

Components of the Ribbon

Below is a (rather squat) version of the ribbon, demonstrating the components we will be working with.

Ribbon

Although not detailed on the above image the ribbon is the entire area, containing Tabs, Groups, controls, etc. When we come to our first coding, you will see that this member sits right at the top of the hierarchy.

Tab

The tab is the way of navigating between different 'topics' on the ribbon, Home, Insert, Page Layout, etc.

Groups

Groups sit on tabs and contain other controls, allowing you to 'group' like controls, as such, the 'Group has no attributes, just children like buttons, boxes, etc.

Control(s)

Contained on Groups or Menus (Office Menu), common types are button, gallery, dropDown, splitButton, etc

Tab Set

This is a container that holds contextual tabs, This groups the contextual tabs that are shown when the focus (cursor) has a certain selection, such as in this instance, when a pivot table is selected. This is without doubt a step forward in the ribbon, hiding the features only until the user needs it. (Excel 2003 has about 39 toolbars most of which users never see)

Office Menu (see below)

This is similar in functionality to the old 'File' menu and contains, New, Save, Save as..., Print, Excel Options (the old Tools>Options... menu choice) and the Most Recently Used (MRU) list, (excuse mine!)

Dialog Launcher

This little button, which is actually just a container and needs a button child to action it, will fire the 'old style' dialog or your custom UserForms, MsgBoxs, etc., so if we look at the dialog launcher highlighted in the Ribbon image above, it will launch the dialog currently accessed through Format>Cells, focused on the 'font' tab.

Lastly, the interesting part is while Microsoft dumped 'CommandBars' for the Ribbon, the old StatusBar, which has now got much more functionality, is now...a CommandBar! (CommandBars("Status Bar")).

Our First Customisation

startFromScratch

The most basic thing you can do is to remove the standard ribbon, almost entirely. This will not be necessary in all code situations as you will be sometimes just adding to existing tabs, etc. You should know however that startFromScratch="true" is necessary if you want to author anything to do with the QAT. (Personally, I think the QAT should be left alone as it is very personal to the user).

For this first example we will do this in numbered steps so you can hopefully follow along.

IMPORTANT: All RibbonX members, etc are typed in camel-case. this says that they have no spaces, start with a lower-case letter and then have the first letter of each new word capitalised, so onAction, startFromScratch, insertAfterMso, etc. There are some names which defy this logic <group idMso="GroupFont"> (note the GroupFont capitalises both words), so you must be careful, but certainly try camelCase first.

NOTE: A big advantage of what we are about to do is that all the code to customise is contained in the workbook, close the workbook or unload the add-in and all your Ribbon will return exactly as was, you cannot damage anything, unlike sloppy coders who often left CommandBar litter after their code exited.

NOTE: Throughout all of this Ribbon development, particularly when we get on to files containing VBA code, I would set up a folder somewhere on your local machine and set it as a 'trusted location' under, Office Button>Excel Options>Trust Center>Trust Center Settings... This will prevent you having to enable macros each time you test your code. (Obviously, (apart from the 'Full' dictator app below, that has VBA code), you will not need that for any workbooks in this second part, as these are code-less demonstrations)

So...

Open a new Excel File and save it as test.xlsx.
Close this file (this step is a little annoying each time you want to test, but essential so that the Custom UI Editor can open it, without a share violation)
Open the Custom UI Editor
In the editor, press the open button and navigate to your new file. You should at this stage see nothing.
In the RibbonX pane, type the namespace identifier (this tells the code where in the framework to look for the 'hooks' and starts every piece of RibbonX) and a ribbon member, followed by the startFromScratch attribute, as below.
Press the 'Validate' button to check compliance
Press Save and Close
Now open your workbook!

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="true">
    </ribbon>
</customUI>

If everything went to plan, you should have virtually no Ribbon left, not quite a dictator application, but equally not bad from one line of code. If you look carefully, you will find the Office Menu has shrunk to a few very basic features and that's about it. Compare the picture below with the one above.

If you still have Ribbon, then you have not got the RibbonX quite right and it is not very forgiving in this way. Generally it will be a case issue (camelCase), but it does not throw a syntax error, just doesn't display or change the Ribbon, so always check that if the unexpected happens.

As we move on, we will add other elements, etc between the start <ribbon> and end </ribbon> tags, which makes sense as everything is 'contained' in/by the ribbon.

Full 'Dictator' Application

Many of you will not call the code above a dictator application as there are still some elements of the Excel UI that can be 'played' with by users. I am grateful to Jim Rech for the lead in discovering how to do this and big surprise, the only way to do it is to return to pre-VBA days of Excel4 macros, which is totally amazing! If you paste the code below into a new workbook and run it, you will see an Excel 2007 full dictator application with VBA (running XL4 macros)

Sub RemoveAll()
'ExecuteExcel4Macro portion by Jim Rech
'Other code Nick hodge
    With Application
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
        .CommandBars("Status Bar").Visible = False
        .DisplayFormulaBar = False
        .DisplayScrollBars = False
        .Caption = "It's all gone!"
        .ActiveWindow.DisplayHeadings = False
        .ActiveWindow.DisplayWorkbookTabs = False
    End With
End Sub

Lastly for today, we will go back to RibbonX and remove the existing Ribbon, create a custom tab, with a built in group, that just works, no code and saved as an xlsx

Open a new file and save off somewhere as an xlsx and close
Open the file in the Custom UI Editor
Enter the code below into the RibbonX pane
Validate the code
Close the file and the Custom UI Editor
Open the workbook and it should now have one tab, called favourites and a group (GroupFont) that if you try the controls on it, will just work.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="true">
    <tabs>
      <tab id="myTab" label="Favourites">
        <group idMso="GroupFont">
        </group>
      </tab>
    </tabs>
    </ribbon>
</customUI>

Hopefully this is still fairly simple, note that you have a ,<tabs></tabs> tag as a ribbon can have multiple tabs and then below that is a <tab></tab> tag. This sets our single tab (remember we removed the existing with the startFromScratch attribute), between these tags, as they are 'contained' on the tab, we use the <group></group> tags to bring in Microsoft's 'Font' group with all it's standard controls, that just work!

The only attribute a member needs is either an id as in the tab that uniquely identifies it, or an idMso, as in the group, which identifies a built-in tab, group, control, etc. basically, if Microsoft made it, it's idMso, if you do, it's just id.

So that's enough for today, but you just removed the existing ribbon, replaced it with a custom tab, called favourites, placed on it a working 'copy' of the font group, all with 10 lines of code. (and that's generous as I have to have opening and closing tags, so it's really five!)

More to come... Stay tuned for part 3!