The Ken Puls Blog

Hello everyone,

At the last MVP summit I had discussions with a couple of people about something that has been burning away in the back of my mind for quite some time… creating my own Excel forums.

I’ve been absent from the online forums for the last few years, and I’ve greatly missed it. With the amount of teaching I’ve done over the past few years I’ve constantly been referring people to other forums and blogs, and I’ve received several comments from those people asking about a forum on my own site. So during this time I’ve been musing it over, wondering if there is enough differentiation, or enough reason to take on the task of building and maintaining another forum out on the interweb.

Despite missing a few of my good friends at the last summit, it was one of the most exhilarating ones I’ve been too, for a variety of reasons. Some of it was technology based, of course, but there was some based on conversations with people that I never expected to have. The compounding of all of it together made me realize that I have been putting this off for too long, and that I actually needed to do it, to bring back the piece of me that I’ve put off for my job over the past few years.

To this end, I’ve begun the process of building another forum on the web, one that focuses on Excel and Excel users. I am hoping to grow it with the style of friendly community that was fostered at VBAExpress.com, but have the focus strictly on Excel users from start to finish, no matter the platform. There are forums there for Excel Client focused areas, Programming (VBA, .NET, XML, etc), PowerPivot and data needs, the recently released Excel WebApp and even Sharepoint. As I said, I want this to be a one stop portal for anything Excel related.

In addition to the Excel focused stuff, I’ve also added a simple section on the other Office apps. The reason for this is simple; every Excel user encounters the other Office apps, and runs into issues. I think it’s important that we have a place to ask/answer those too.

Oh, and naturally there is a chat forum, as no forum would be complete without it.

The forums are, of course, completely free to use. Simply sign up for an account, and post your questions. You’ll be notified immediately by email when someone replies, so you can jump right back to the forum and post further information if required, or see the solution that an expert has helped you build. You can even upload your own files to the forum so that people can see EXACTLY what you’re trying to do. After all, a picture saves 1000 words, right?

And if you’re an expert, or even if you’d just like to help, then post some answers! We love help, and encourage you to take part. Speaking from my own experience, I can tell you that there is no better way to better your own skillset than by trying to help others. You won’t always have perfect answers, but nor do we expect that. This is all about people helping people. The intentions are what is important.

You can find them at http://www.excelguru.ca/forums. (The registration button is in the top right of the screen.) Or, if you’d prefer, you can jump straight to the Registration link to create your account.

I look forward to seeing you in the forums!

As I mentioned in this post, I started working my way through the .NET Hello World example in PED 2nd Edition. I ran into a funny issue though, where it kept opening up my application in Excel 2003 instead of 2007. I fired off a quick email to Dennis who was kind enough to give me some pointers.

As it turns out, my “Current Version” of Excel on my laptop was set to 11 (Excel 2003). My attempts at running Office 2007′s Detect and Repair feature, to reset this to 12, were causing the Microsoft Bootstrapper to crash. (So it wasn’t completing.)

Today I decided to install Acronis TrueImage to take a backup before I mucked with this any further and, lo and behold, it pointed out that it couldn’t install until it killed the suspended install of Office 2007. I gave it the assent to kill the job, and then my Detect and Repair ran fine. I’m now back up to Excel.12 as my current version, and my “Hello World” application works nicely.

I’m only about ½ a chapter into PED 2^nd edition, but the .NET stuff is working for me so far. I’m looking forward to digging a little deeper into this now, and trying my hand at a full managed COM Add-in.

After playing around in Visual Studio, and taking my first foray into the VB.NET world, I thought it might be kind of interesting to try and see just how portable .NET code really is. So I decided to port the little Math Game to my Windows Mobile 6 phone.

Guess what! I did it, and it wasn’t actually all that difficult to do.

The conversion of the code was pretty simple. I just exported the userform from my original Windows project and imported it into a new Windows Mobile 6 application. (Err… after I downloaded the Windows 6 SDK which doesn’t ship with VS2008 Pro.)

It worked just fine in the Emulator, so I then set about the deployment process.

It took way longer to get the deployment done than to write the project, I’ll tell you that right now. You can’t install an exe file on Windows Mobile 6, but rather have to package it up in a cab file. Great, but how do you do that?

After some searching, I finally found http://msdn.microsoft.com/en-us/library/zcebx8f8.aspx, which takes you right through the process. Fortunately I do have a digital signature at work, so I was able to use that to sign the code as well. I’m not sure if the certificate would be trusted on anyone else’s device or not, but it did work on mine.

So once I deployed the cab file to my phone, the install was easy, and the app works. It just goes to show you just how portable simple .NET code can be, which is really cool.

Despite all the goodness here, there are a couple of quirks that I ran into…

• In the emulator, I can get my shortcut to show up in the Games folder off the start menu. For some reason I can’t get that shortcut to show on the actual device though. Instead I have to dig down through the File ExploreràProgram FilesàGames. Oh… wait… I think I got it. I was setting up a shortcut in the Games folder, but it looks like it needs to be in the “Games Files” folder.
• My icon file only shows up when the shortcut is selected. When it’s not selected it looks like a DOS box.
• I can’t figure out how to turn on the NumLock when the game starts. (I’m sure it’s easy when you know what you’re doing.) This means that you have to hold down the FN key in order to get a number into the box.
• On my phone it also enters the numbers in a pop-up box, which you have to confirm, before you can press enter. (Nevermind… that’s the XT9 Predictive Text feature.)
• I wish I could stop the incessant beeping when you click enter and the messagebox pops up, but don’t know how to do that.

At any rate, on the whole it works, and that’s the good thing.

I have no idea if this works on Windows 6.1 or Windows 6.5, but it seems to work on Windows 6.0 for me. I’d be really curious to know if it installs for anyone else. If you’d like to give it a try, you can get it here.

Okay, it’s a simple application. Really simple.

I wrote this as an easy project to break into VB.NET. I’m actually feeling pretty good about it though, as it took me a couple of hours to code a working game, never really having done a lot in VB.NET before. (Most of that time was futzing around with the syntax.)

Based on my tests it even installs in Windows XP and Windows Vista without a hitch too. I was pretty surprised, as I’ve heard horror stories about deployment, but I think those might have been related more to VSTO.

At any rate, here’s a picture of just how complex this is:

It generates a random equation which can be addition or subtraction. And it tracks the player’s stats for the current session only. It was designed for a 6 year old to play, and so far the testing has been positive… although it needs some kind of excitement factor as after about 20 questions it loses its luster.

It does require the .NET 3.5 SP1 framework to be installed. That takes forever compared to the install of this little thing.

At any rate, if you want to try it out, you can download it here:  mathgame.zip.

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.