The Ken Puls Blog

On Friday night, I began reading my way through PED 2^nd Edition. After something weird happened with Excel, (I think because I have Excel 2003 and 2007 installed,) I got the bright idea to create a very simple Windows Forms application. I figured that I could make a really easy math game for my daughter. (I’ll see about posting it over the next few days.)

So cool thing here… with very minimal VB.NET experience, I was able to knock up a fully working simple math game in about 3 hours that will install on Windows XP and Windows Vista. I was pretty damn impressed.

At any rate, here I am today, back working in Excel. And I can now say that I’m rather disgruntled with the state of the IDE for Excel VBA.

Despite gnashing my teeth on certain stuff in VB.NET, the IDE in VS2008 is awesome. Maybe it’s for the big things, and I’m sure that there will be VBA IDE things I’ll miss, but it’s the little things that made my day. Little things like this:

• You can collapse procedures/function and even entire regions of code. That seriously rocks!
• You type in With “whatever”, and it automatically puts in the End With. Actually, all constructs are like this… For Each, Select Case, Try/Catch and so on. Very, very cool!
• The indenting everywhere is automatic.
• The intellisense always suggests available items. I find this somewhat hit and miss in Excel’s IDE for some reason.

I really wish that they add the features above into our tired old IDE. It would make things so much faster for typing up code from scratch!

My wife sent me a message today to let me know that a review copy of Professional Excel Development – 2^nd Edition had just arrived at my door.

The last version of the book was excellent: the only book on the market of its type, in fact. I’m expecting nothing less from this one, indeed I’m actually expecting more. Dennis Wallentin (XL-Dennis) was added to the team this time to bring in the .NET aspect, and I’m really looking forward to exploring those chapters. Hopefully I’ll actually be able to make a .NET add-in that actually works now!

I haven’t had a chance to dig into it yet, but one thing I’ll say about the book right now is that it should be able to hold your house down if a tornado hits it. This is one big book!

I should also mention that Dick Kusleika has acquired an extra copy to give away. You can find his competition here.

Over the weekend I've spent a significant amount of time working in Visual Studio 2005 with VB.net. The more I work with it, the more I like the IDE, and some of the cool things that I can do in it. This post, though, is going to focus on some of the differences between VBA and .Net that I'm playing with.

First thing to notice in importance is the existence of Option Strict. As VBA'ers, we're used to using Option Explicit (at least you should be), but Option Strict adds another dimension to your code. As I believe in following best practices, I am coding with both Option Explicit (OE) and Option Strict (OS) set to On.

It should also be noted that while I am quite proficient with VBA, I am certainly not an expert on VB.Net, and I am not trying to pass myself off as such. It's very likely that I will get some things wrong in this, and if I do, feel free to comment on the post. I'm doing this to learn, and blogging to share my experiences with those who have both gone before, and may choose to come after me on this road.

Okay, so here we go...

Early vs Late Binding

In VBA, we have the option of Early or Late binding. Generally, I develop using late binding, mainly because I work in an environment of mixed Office versions. There is nothing worse than having an Excel 2003 user open a file, have the references upgraded, and the 97 user can no longer get in. In VB.Net, with OS on, you must use Early binding.

In addition, OS demands very strict Type casting. I'm used to the following to create an instance of Word in VBA:

This does not work in VB.Net though, as "Option Strict On disallows implicit conversions from 'Object' to 'Microsoft.Office.Interop.Word.Application'."

Instead, we need to use the following to explicitly cast the Type:

Functions...

In VBA, we'd use our Right() function to return x number of characters from the right of a string. In VB.Net, we need to preface that with something more:

Type Casting

Probably the biggest issue that I've run into is casting types. VB.Net (OS on) will NOT convert data from one type to another. This is evident in the binding example above, as well as many other things. Fortunately Option Strict kicks up little error messages to tell you what's up, and after a bit you can generally make some sense of how to fix them. Usually it involves a converting something to a type that we're used to... CStr, CBool, etc... What really threw me, though, was how to type data as an Object. The secret is the Imports statement... For example, look at the MessageBox constants (more on this below).

With a msgbox, you can't just use the constants like vbYesno anymore. You need to use Microsoft.Office.Core.MsgBoxStyle.YesNo. Now that's a bit of a pain, but there is an easier way. Thanks to Mike Rosenblum, who explained this to me in great detail, you can do this:

At the top of your module, insert the following statement:

Now you can refer to that constant as:

MessageBox Constants
In addition to the issue with Type Casting above, the messagebox also changed a bit in that you don't add your options together in .Net as we did in VBA. Let's look at the differences:

Notice that it is an Or statement, not a +. This seems weird, but I have tested it, and it does work. Seems counter intuitive, I'll grant, but what are you going to do.

Final Word

Well, that's my observations so far on language differences. There's way more, obviously, and I'm sure I'll share more as time goes along.

I found this kind of interesting, but somewhat strange...

A couple of nights ago I spent a significant amount of wasted time trying to get a callback to work in Visual Studio 2005. The problem was that I was trying to adapt my Dynamic Menu example to VB.Net, and the callback signatures for VB.Net and VBA are different. That's not really a big secret, and most of the callback signatures (in VB.Net, VBA, C# and C++) can be found in MSDN's Customizing the Office (2007) Ribbon User Interface for Developers (Part 3 of 3) article. Most, but not all currently.

To my chagrin, there was at least one callback signature missing... the getContent callback. For populating the dynamicMenu control, this is essential. That omission has been brought to Microsoft's attention, and hopefully the article will be updated soon.

Now, what's interesting about all of this... here's the signatures:

I'm fairly comfortable programming in VBA, but to me the VB.Net callback is far more intuitive to what I would have expected. In truth, despite working with the VBA signature for a while, I still scratched my head on why I was setting the value of "returnedVal" to a parameter. I knew that it was working, but was not sure why it was not looking like the VB.Net version, and why it was working.

The fact that it works makes sense, though, once you realize what is happening. I should have seen this way earlier, but I've never passed parameters like this. For my own purposes, I think I would have realized what was going on if the VBA version was written as:

Do you see it? I added the ByVal keyword to the first control. To me, this would have probably alerted me to consider this... It's the ByRef keyword that does it.

By default, all objects are passed to and from subroutines and functions ByVal, and the keyword is therefore usually omitted. The key difference between ByVal and ByRef, though, is that when you pass a parameter ByVal, a copy of the object is passed, not the original object. This means that anything you do to the object is done on the copy only, and when the procedure or function ends, the temporary object goes out of scope and the changes are lost. By contrast, when you pass a parameter ByRef, it passes the original variable. Any changes made to that variable are done on the original, and this is how the callback works... changes are made to the original "returnedVal" object that was passed as a parameter ByRef to the subroutine.

Personally, I don't really follow why the VBA callbacks would have been set up as subroutines with the "returnedVal" object passed ByRef. To me this seems like the perfect use for a function, which is obviously the route that Microsoft decided to take with VB.Net.

Why the difference? Is there a good reason why they would choose to go one route with VBA vs the other with VB.Net?

I've been moving around in Visual Studio for the last hour, trying to get familiar with it.  As always, I feel that the best way to learn to use a program is to start with a project, so I decided to re-write my templates add-in for Excel 2007.  In truth, I would prefer to re-write my Favourites add-in, but it will be more difficult, so I'm using the templates one as a learning ground.

So here are my first impressions of Visual Studio 2005 from the point of view of someone who is pretty comfortable in the Excel VBE:

• Overwhelming
• Powerful
• Exciting

That is exactly the order that my feelings went, with about 20 minutes between each...

There are so many commands in here, that it's hard to figure out what I'm trying to do.  It took me 10 minutes to figure out how to change the name of my project (right click on it and say "rename"... don't I feel like an idiot now!)   I may have complicated things though, as I installed MZ Tools immediately, just because I didn't have enough tools to get lost in right away. 

To further complicate things, (as if the new UI wasn't enough to deal with,) the code is all VB.Net.  This means that I'm going to have to convert some of my code (how much remains to be seen) to VB.Net from VBA.  The first example I came across was a Property statement.  In VBA, we'd have a Property Let and Property Get routine.  In VB.Net though, it's all in one routine:

Now I haven't even got to the point where I can attempt to debug this yet, so it could be totally wrong, but it does look like the help file example.    If it is right, I actually like this better.  It  sure makes it easier to keep straight, and keeps the code collected better.

Now, back to the Visual Studio IDE... here's some of the things that strike me as really cool:

• You can collapse blocks of code.  In the example above, I can collapse the Get portion, the Set portion, the comment portion, or the whole routine.  Very cool!
• Adding code to deal with the Ribbon is as easy as choosing Project|Add Module|Ribbon support.  (Actually, this is a VSTO template, not strictly VS alone.)  As soon as you do, *poof!*, you have a module set up with the code to create a sample ribbonUI.  In fact, the RibbonX code is in another module, so you can edit it there.  All the info in one place, rather than flipping back and forth between Excel and the CustomUI editor.
• An error list at the bottom of the screen highlights all the errors in your code.  (I have a ton right now!)  This is how I knew that my Property routines were at issue.
• The help files seem to have a lot of information and it's actually helpful.  That may sound a little cheeky, but I always found the help files in Excel 2002 and prior quite frustrating.  2003 was okay, but 2007 seems better from what I've seen.  I may change my opinion on this, but the Property help page was quite easy to use.

At any rate, that's about as far as I've got with it so far, but I like what I see.  I do understand why Microsoft is pushing developers to use this product, but I still feel that they should give the tools to those developing within Excel from start to finish.  The reason I'm in this program at all is because the only hope I have of populating a RibbonX group on the fly is by writing a COM add-in, and that shouldn't be the case.

Just my 2 cents on the matter. 

Well, I've finally done it. I've just finished installing Visual Studio 2005 Professional, complete with patches, the MSDN Library files and VSTO 2005 SE Beta.
I figured that I might as well just bite the bullet on it, as I've been getting ready to re-write my XLG Favourites add-in for Office 2007. News I got today seems to point to the fact that I can't design it the way I want from VBA alone, so a COM add-in will be necessary. I can't say that I'm crazy about needing another program to do this, as it means that I'm staring up from the bottom of a big learning curve, but if we stayed in our comfort zone all the time we'd learn very little.

The install actually went pretty easy, except for one small thing. When I installed Office 2007, I didn't have the .Net framework installed. (I'm running on Win XP now, and hadn't needed it to this point.) Because the .Net framework wasn't installed, all the options for .Net programming compatibility were unavailable. I really didn't think anything of it at the time, as I wasn't really planning on jumping in to this just yet. The problem I ran into was, when I started an Excel project, it referenced certain Primary Interop Assemblies. The issue, though, was that I had an error telling me that the "Primary Interop Assemblies are not installed".

So here I am, looking at the UI of a program that is completely new to me, trying to figure out what to do. I did a full install of every component, and I'd forgotten about my .Net issue. Fortunately Google is my friend, and I found a nice little MSDN article on How to: Install Office Primary Interop Assemblies. (Someone please tell me why Microsoft would create a component with the acronym PIA? Don't they realize that it stands for something else?)

At any rate, I didn't bother to download the redistributable package, as that article somehow triggered my memory on the .Net stuff I didn't install. I hate installing extra stuff that I can avoid (yes, I know I did a full install that I'll probably never use all of, thanks), so I just went back to Office and modified the install. There were about 8 places that I had to check to install .Net programability support.

Now, here's what I don't get. Visual Studio, during the install, checks to see if you have the .Net framework installed, and will install it if you don't. Why does VSTO, when you install it, not check to see if you have the .Net PIA's installed for the installed Office apps and install them if you don't? It would seem to make logical sense, wouldn't it?

At any rate, it's time to start a new journey.