SubscribeDeveloper: “Hello World” in Excel, PowerPoint and Word with VBA (Part 3)
My last two posts explained how to modify the Office UI using RibbonX embedded in a document or template. Today, I am going to discuss how to react to an event from a UI element in VBA. As example, I will display a message box with the word “Hello World” when the user clicks the “Hello World” button created in the first two parts of this topic.
Before I can get started on this though, I need to talk a little bit about Macro Security. In Office 2007, documents and templates that contain macros are trusted automatically if they are stored in a Trusted Location. To alter your Trusted Locations, go into Office Button, Options, Trust Center, Trust Center Settings, Trusted Locations. There are two observations noteworthy here: First, you “My Documents” folder is not trusted automatically, nor are any of its subfolders. Secondly, by default, you cannot add a network location as a trusted location unless you specifically enable this in this dialog. If you don’t want to alter your Trusted Locations, you can always press “Enable Content” after you loaded a document or template with a macro into Office (you have to do this every time you open the file). Explore the rest of the Trust Center and see what other options you have for security and privacy settings there when you have some time.
If you remember my overview post, the third step was the actual add-in being used via callbacks. Today, I am going to discuss how to specify a callback for a button in RibbonX and how to implement the callback in VBA.
Let me start out by showing the function in VBA that the button “Hello World” will trigger:
Sub onHelloWorld(control As IRibbonControl)
MsgBox (”Hello World”)
End Sub
The only noteworthy thing about this function is its argument control. This argument lets you know the UI element that triggered this particular callback. With control.Id e.g. you can figure out the id of it. You have to include this argument, otherwise the signature of the function is incorrect and it will not be called.
To tell Office to call “onHelloWorld” when the “Hello World” button is pressed, modify the RibbonX file with the Office 2007 Custom UI Editor:
The modification I made was to insert onAction=”onHelloWorld” as an attribute of our button. The attribute “onAction” was defined by Microsoft and specifies the callback for when the button is clicked. Please notice that I only specified the function name and not its full signature in the attribute. Office knows what the signature of the function has to be and therefore it is sufficient to only specify the function name.
Once you modified the file and open it in Word, you should be looking at the dialog box, when you press the button:
You can also download the sample.
I will take a break from my Hello World series with my next post and instead focus on how to best integrate your add-in into the new UI from a conceptual/style point of view.
June 6th, 2006 at 13:27
Patrick,
Thanks for this series it has really been helpful and makes me feel that while it isn’t easy, as compared to the older interface, it isn’t actually impossible either.
Backt to an earlier question. Do you know where to find a list of the different types of controls in the Ribbon and what attributes they can have. I mean I know that you can have a tab, group, and button from your example. But can a button have a border? Can the label have a color, etc. Is this information listed somewhere? Thanks
June 6th, 2006 at 13:33
Hi Greg,
sorry for not replying earlier. Customizing the Office (2007) Ribbon User Interface for Developers (Part 2 of 2) at http://msdn2.microsoft.com/en-us/ms406047.aspx is what you are looking for.
Patrick
June 6th, 2006 at 17:20
Yes that is helpful. What would be even more helpful would to be able to find, open and view the XML file that actualy builds the default RibbonUI.
Thanks.
June 6th, 2006 at 17:49
Take a look at: http://pschmid.net/office2007/download/WordDocumentUIB2.xml
It only covers tabs and groups. I haven’t bothered throwing controls in there yet and MS doesn’t seem to be very helpful in providing actual RibbonX code instead of text files no matter how many times I ask.
Patrivk
June 6th, 2006 at 21:34
Patrick,
This is a big help and it would be nice to see the complete Ribbon code laid out from start to finish. I think by seeing it that it would be more understandable to a novice like me. What you have put together in this blog so far has certainly helped clear the fog. It is very helpful. Just keep asking for that code and maybe MS will come through one day
.
June 6th, 2006 at 21:38
Greg,
if they don’t, I won’t have any other choice but convert text files into usable RibbonX code. I am definitely not looking forward to that…
June 7th, 2006 at 13:12
However you obtain it I hope that you will post it here. Thanks again.
June 27th, 2006 at 19:13
Patrick,
I have a hidden workbook from Excel 2003 that loads from the startup folder. This workbook contains scores of macros that we use in our business. In the past I created toolbars to buttons to call the macros. I tried calling one of them with the onHelloWorld macro, but could not get it to run. Can you give me any guidance on how to do this? It seems I could then create the buttons in the RibbonX and load that sheet as hidden also. I guess otherwise I will have to move all the macros to a new sheet that is loaded everytime I start Excel.
Thank you for all of your assistance in your blog and the Office Beta newsgroups.
June 27th, 2006 at 19:55
Bill,
The main problem you are probably hitting is that you need to put the RibbonX code somewhere. If you put it in a worksheet, then it will be only active when that worksheet is in the foreground. I haven’t tried this with hidden worksheets, but I would be extremely surprised if RibbonX customizations in those are visible for all worksheets.
You can do this in two different ways:
a) Create an Excel add-in (XLAM) that contains the RibbonX only and store all the macros in your hidden worksheet. The hidden worksheet can even be in the XLS format, so you could just use one sheet for all Excel versions. If you include the filename in your callbacks in the XLAM (filename!methodname), then you should be able to send all RibbonX callbacks from the XLAM to the XLS macros (except the ones that deal directly with RibbonX issues, such as onLoad; you need to use Variant in the method signatures in VBA). For a great example on how to do this, look into the Excel beta newsgroup. The thread “scoping of RibbonX callbacks in VBA - security issue?” started by me on 6/23 has a post by Jim Rech explaining how to do this.
b) Just throw everything (RibbonX and VBA implementations of callbacks) into an Excel add-in. This is certainly the easier method to program, but it requires you to keep two different versions for 2007 and non-2007.
Patrick