My first VB.NET application

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.

VBA IDE vs VS2008 IDE

On Friday night, I began reading my way through PED 2nd 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!

Thanks a lot, macro recorder!

I think it’s pretty well known that the Excel macro doesn’t always record code when you’d like it to.  I know that there’s been several times I’ve recorded something to get a syntax, and it’s been an empty stub after I’m done.  This one was a new one to me though…

I tried to record the creation of a conditional format in 2007 and here’s what I got:

  1. Sub Macro4()
  2. '
  3. ' Macro4 Macro
  4. '
  5.  
  6. '
  7. Selection.FormatConditions.Add Type := xlExpression, Formula1 :=
  8. Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  9. With Selection.FormatConditions(1).Font
  10. .Bold = True
  11. .Italic = False
  12. .ThemeColor = xlThemeColorDark1
  13. .TintAndShade = 0
  14. End With
  15. With Selection.FormatConditions(1).Interior
  16. .PatternColorIndex = xlAutomatic
  17. .Color = 255
  18. .TintAndShade = 0
  19. End With
  20. Selection.FormatConditions(1).StopIfTrue = False
  21. End Sub

The first line was even highlighted red. Seriously, it could get the rest, but not the formula?

Demonstration of RibbonX add-in conflict dangers

Further to yesterday’s post, I’ve found that the issue with onLoad conflicts is much bigger than I thought. As Bob Phillips pointed out, this isn’t actually a new issue, or one that is strictly an issue with RibbonX, but it is definitely worth mentioning.

To me, this is a pretty serious issue. In the RibbonX book, Robert & I picked a standard name for callbacks and used those throughout. For those of you who have created add-ins using our standard signatures, and distributed them to others, be aware that you may find that your ribbon hijacks someone else’s, or vice versa. I discovered this after re-writing an old add-in and creating a new, unrelated one. My steps for both were to come back to my blog to pick up some framework code, and away I went. It started with the onLoad not working, but today I discovered that this is much bigger.

As it turns out, it appears that the FIRST add-in loaded gets the jump an all others with regards to callbacks. I’ve built and attached a simple demonstration of how easy this is to replicate, and that’s what I’m going to discuss here.

Creating the test Add-ins:

  1. I created two add-ins and called them something really creative; ConflictAdd-in 1.xlam and ConflictAdd-in2.xlam
  2. I created a the RibbonX structure for both Add-ins that looks like this:

    ConflictAdd-in1.xlam

    <customUI

    xmlns=”http://schemas.microsoft.com/office/2006/01/customui”>

        <ribbon>

            <tabs>

                <tab id=”rxtabCustom1″

                    label=”Add-in 1″

                    insertAfterMso=”TabHome”>

                    <group id=”rxGrpConflict1″

                        label=”Conflict Test – Add-in 1″>

                        <button id=”rxbtnConflict1″

                            label=”I am in Add-in 1″

                            size=”large”

                            onAction=”rxbtnHandler_click”

                            imageMso=”TentativeAcceptInvitation” />

                    </group>

                </tab>

            </tabs>

        </ribbon>

    </customUI>

    ConflictAdd-in2.xlam

    <customUI

    xmlns=”http://schemas.microsoft.com/office/2006/01/customui”>

        <ribbon>

            <tabs>

                <tab id=”rxtabCustom2″

                    label=”Add-in 2″

                    insertAfterMso=”TabHome”>

                    <group id=”rxGrpConflict2″

                        label=”Conflict Test – Add-in 2″>

                        <button id=”rxbtnConflict2″

                            label=”I am in Add-in 2″

                            size=”large”

                            onAction=”rxbtnHandler_click”

                            imageMso=”TentativeAcceptInvitation” />

                    </group>

                </tab>

            </tabs>

        </ribbon>

    </customUI>

    Take a careful look through the XML and you’ll notice that:

  • The tab, group and button ID’s are different, as are the button labels.
  • The onAction callback names are the same

     

  1. I created the VBA callback signatures for the add-ins, declaring them private, and also using the ‘Option Private Module’ keywords:

    ConflictAdd-in1.xlam

    Option Explicit

    Option Private Module

     

    Private Sub rxbtnHandler_click(control As IRibbonControl)

    ‘Callback for rxbtnConflict1 onAction

    MsgBox “You triggered a the routine in Add-in 1!” & vbNewLine & vbNewLine & _

    “And FYI, Add-in 1’s code uses a Private Sub” & vbNewLine & _

    “in a module marked ‘Option Private Module’!”

    End Sub

    ConflictAdd-in2.xlam

    Option Explicit

    Option Private Module

     

    Private Sub rxbtnHandler_click(control As IRibbonControl)

    ‘Callback for rxbtnConflict2 onAction

    MsgBox “You triggered a the routine in Add-in 2!” & vbNewLine & vbNewLine & _

    “And FYI, Add-in 2’s code uses a Private Sub” & vbNewLine & _

    “in a module marked ‘Option Private Module’!”

    End Sub

    Again, a close examination of the code will show you:

  • The module has been declared as private
  • The callback has been declared as private
  • The only difference is the text about which module the routine was called from

     

  1. I then added a line to the Workbook_Open event in each Add-in to let me know when it had been fired.

    ConflictAdd-in1.xlam

    Private Sub Workbook_Open()

    Debug.Print “Add-in 1 has been loaded…”

    End Sub

    ConflictAdd-in2.xlam

    Private Sub Workbook_Open()

    Debug.Print “Add-in 2 has been loaded…”

    End Sub

     

  2. Finally I installed both add-ins, closed Excel and re-opened it

The issue demonstrated:

So after opening Excel with the add-ins checked, I went into the VBE to check the load order of the add-ins:

We can also see that Add-in 1 loaded first in the image below, since it was loaded “AfterMSO” TabHome, then Add-in2 was as well.

At any rate, I then went to the Add-in 1 tab and clicked the button to see the results:

Okay, no problem. We are getting the callback from Add-in1 to fire without issue. Let’s check Add-in2’s button:

Wait a minute… this is Add-in2 calling Add-in1’s procedure!

Just to prove out my theory, I unloaded Add-in1 at this point, and tried Add-in2’s button again:

Okay, all good this time. So I re-loaded Add-in1 and tried both buttons again:

So here’s my questions:

  • Why does the RibbonX callback look outside its own workbook for the code?
  • We have an IdQ attribute that is specifically designed for sharing RibbonX solutions across multiple workbooks. This really just reiterates my first question…
  • I thought the entire point of marking your module as “Option Private Module” was to avoid hide your code from other workbooks?
  • How can I prevent someone from accidentally (or intentionally) interfering with my add-ins callbacks?
  • How are we supposed to put in a corporate standard for coding RibbonX solutions now?
  • Do you feel that this is a bug, or a feature?
  • Would it break any of your solutions if this cross workbook handling were removed?

Any way you slice this, it isn’t good. As developers, we need to ability and assurance to make sure that other projects don’t hijack, interfere or conflict with the code that we write.

Suggested solutions:

At this point the only solution that I can see working is to standardize on naming your add-ins with something that others won’t use. I could preface all my callbacks with something like “XLGFT” for “ExcelGuru File Tools”, for example:

onAction=”XLGFT_btnHandler_click”

The chances are much lower that I’d run into a conflict, but it would certainly still be possible if I happened to come across the same nomenclature as someone else.

A possible use?

I can see one possible use that could be made from this. Theoretically, you could break your UI apart from the code that supports it. So you’d put all your callbacks in one master add-in, then load/unload the individual UI components through one means or another. The maintenance of it could be tough, but I guess it’s not that far off what I do with controlling workbook templates from one central add-in. I just means you would be controlling sub add-ins from a central add-in…

Sample Files:

Conflict Add-ins Demo Files

Debugging RibbonX Invalidate/InvalidateControl failures…

I ran into this the other day when I was working on a file and it drove me nuts.

Background:

I always use a custom property to contain my RibbonUI object. It’s set up like this:

The following code goes in the ThisWorkbook class module:

Private pRibbonUI As IRibbonUI

Public Property Let ribbonUI(iRib As IRibbonUI)
‘Set RibbonUI to property for later use
Set pRibbonUI = iRib
End Property

Public Property Get ribbonUI() As IRibbonUI
‘Retrieve RibbonUI from property for use
Set ribbonUI = pRibbonUI
End Property

And then the onLoad statement, placed in a standard module, looks like this:

Private Sub rxIRibbonUI_OnLoad(ribbon As IRibbonUI)
‘Set the RibbonUI to a workbook property for later use
ThisWorkbook.ribbonUI = ribbon
End Sub

At that point I can invalidate the entire ribbon by using:

ThisWorkbook.ribbonUI.Invalidate

Or just a single control:

ThisWorkbook.ribbonUI.InvalidateControl “ControlName”

The Issue:

This particular problem started for me when my InvalidateControl methods weren’t working. This was irritating, but I’ve debugged a lot of RibbonX before, so I thought it may be the control name. After checking the XML I was pretty sure it wasn’t, but just for grins I switched to try to Invalidate the entire ribbon. That didn’t work either…

Next up on the debugging mission was to step through the code, at which point I discovered that I had no reference to the RibbonUI object. As you’ve no doubt figured out if you worked with the ribbon, you can’t invalidate your controls without a RibbonUI object to work with. So somewhere I thought I must be triggering an error which caused the RibbonUI to lose scope. Despite a ton of searching through the VBA though, I couldn’t find anything that would trigger this kind of error.

Given that my code all looked good, I added a single line into my OnLoad statement so that it read as follows:

Private Sub rxIRibbonUI_OnLoad(ribbon As IRibbonUI)
‘Set the RibbonUI to a workbook property for later use
Debug.Print “Fired!”
ThisWorkbook.ribbonUI = ribbon
End Sub

I then opened the file again, and found no message in the immediate window. Hmmm… The callback was obviously never called at all. This is a little strange, as this callback should be triggered as soon as the file was opened. So then I checked the remaining things that could cause these kind of issues:

  • I checked the Trust Center to ensure that:
    • macros were set to “Disable With Notification”
    • The folder where the file was stored (on my network) was a Trusted Location (and that subfolders were also trusted)
  • I checked that the onLoad callback matched that generated by the customUI Editor
  • I check that UI errors would be displayed (just in case my XML wasn’t valid). You can find this under OfficeàExcel OptionsàAdvanced near the bottom of the list:

None of these earned me any joy though. Things still didn’t work. I then emailed the files to a friend and guess what… they worked!

With the problem isolated to my machine, I then disabled all my other add-ins and opened Excel fresh. Lo and behold, it worked!

The Fix:

When we wrote the RibbonX book, Robert and I standardized on naming the onLoad callback rxIRibbonUI_OnLoad. I’m starting to rethink that advice a bit…

In my case, I had two add-ins with this same onLoad name, one of which had the onLoad callback declared as a Public routine, rather than private. This caused a conflict when I wrote a new add-in and used the exact same procedure/callback name. Just to make this clear:

Add-in #1 used:    Public Sub rxIRibbonUI_onLoad(ribbon as IRibbonUI)

Add-in #2 used:    Private Sub rxIRibbonUI_onLoad(ribbon as IRibbonUI)

Even though Add-in #2’s onLoad was correctly written, at load time it did not run. I didn’t actually test this to be totally sure, but I’m 99.9% confident that Add-in #1’s onLoad DID fire instead.

So some suggested rules for you if you’re building an add-in:

  • Code to avoid conflicts. Even if you are only writing one add-in, you never know if you’ll end up installing one from somewhere else. If both you and the other author followed our naming convention, and the other author screwed up their callback by declaring it as Public, YOUR add-in will not run correctly. (Nice, eh?)
  • Declare (ALL) your RibbonX callbacks as Private. This will avoid pushing this issue on someone else. By default, the customUI editor authors its callbacks as Sub blahblah(). Omitting the Private keyword by default leaves it as Public.
  • Forget about Option Private Module. If you think you can use Public callbacks and just declare the module private with “Option Private Module”, see rule #2. (Trust me. That one I did test, and is what led to this whole mess.)
  • Add a project specific name to the end of the callback. E.g. “rxIRibbonUI_onLoad_FWBudgetTools“. While I still believe in standards, you need to tag it with differentiating text to avoid conflicts with other add-ins. Oh sure, it may look long and unwieldy but let’s face it — you’re only going to write this routine once per project and forget all about it after that!


Windows 7 install

Last night I decided to install Windows7 RC on a spare laptop hard drive. So I pulled my Vista drive out of the laptop, slipped in the other drive and away I went.

For reference, I found the install very slow compared to XP or Vista. It seemed to take forever and a day to install it. Oh, and just as a note, you need to license code before it will boot into Windows (duh!). I had to run to my wife’s computer to grab a code from MSDN as I kind of forgot that little part.

I’d already installed this on a desktop PC, and found some interesting new features that look nice:

  • Screen Magnifier tool (no more need for the magnifier that comes with my mouse software?)
  • Sticky Notes (stick right on the desktop)
  • Live Preview of application pages/windows from the task bar
  • Clipping tools (competes with SnagIt/GrabIt)

Interestingly enough the screen magnifier and live preview did NOT work well on my laptop, where they did work on the desktop.

I’ll also say that the version of IE8 included with Win7 sucks. On both the desktop and laptop it crashed repeatedly. I ended up restarting IE8, clearing 2 error messages and going straight to Firefox.com to download a useable web browser.

Windows Live Mesh will not install on Windows7 at this time. That’s a bit of a problem for me as I keep some pretty critical documents there to make sure I have a backup of them.

Upon shutting the laptop down last night I also ended up triggering the blue screen of death… I haven’t actually seen one of those in a long time.

Of course, I had to work today, so I pulled the Windows 7 drive out and put my old Vista one back in… and just about had a heart attack! The BitLocker drive encryption screen came up. I’ve never actually seen that before, as it doesn’t actually appear to do anything when you turn it on. The momentary panic passed though when I verified that the key I had tucked away was the right one, and I’m up and running again.

Breaking text to two lines

I was working on my budget package today, and decided that I wanted to break the text so that it showed on two lines. For many novice users this means that they put one or two words in one cell, then the remaining text in the cell below. Personally I try to avoid that, as I find it unnecessary.

So here’s what my output might look like:

I sized the column for illustration only, and would normally have it much narrower.

It’s true that you could accomplish this by checking the Wrap Text box (Format CellsàAlignment), but that only works if the column is at the right width to have the wrap where you want it. That’s not always good enough for me when I’m sizing my columns to hold the required numbers, and my headers might look all messy. By controlling where the text breaks, I can get it to look exactly how I want.

So how did I do this? After all, when you press Enter the data is committed to the cell and you move down a row.

The answer is to press ALT+ENTER together. The text will then break exactly where your cursor is.

Now that’s all good, but what about when you are filling the cell contents with a formula? Let’s look at the following example:

Have a look at the contents of C1. In this case it is actually driven by the following formula:

=YEAR(B5) & ” Budget vs ” & YEAR(B4) & ” Projected Actuals”

Now this is all good, but I’d like to force the 2009 to be on the second line. I can’t go in and type an ALT+ENTER in this case. Actually, I can but all it does is split my formula to show on two lines in the formula bar like this:

While that’s really helpful for auditing formulas, it doesn’t achieve the desired effect. (I believe that the option to expand the formula bar was added in Excel 2007 – just left click and drag down when your mouse turns into the up and down arrows on its bottom border – but in previous versions you’d have to edit the cell and use your arrow keys to see the different lines.)

The secret to breaking the text in a formula is to use the CHAR function to enter a non-printing character; a hard return. In this case CHAR(10) will do the job quite nicely. So I’d edit the formula to read as follows:

=YEAR(B5) & ” Budget vs ” & CHAR(10) & YEAR(B4) &” Projected Actuals”

And it would get me the following result:

Just a quick note as well… I use the & sign to join text instead of the horrible CONCATENATE function. It’s not required to have spaces each side of the & character, but it doesn’t hurt either. I normally wouldn’t put them in, but I did it to make the formula a bit more readable.

And for the record, this should work as far back as at least Excel 97, if not further.

Professional Excel Development – 2nd Edition

My wife sent me a message today to let me know that a review copy of Professional Excel Development – 2nd 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.

Brutal Date Format

We recently updated our main property management system and are now going through the first month since. Over and over again now I’m running up against an issue in the way the vendor decided to start treating dates. In the past they just used “MMM DD’, which was fine. These converted into valid dates in Excel that assumed they were in the current year. All good!

In the recent update, the vendor decided to add the year, but not in a good way. Now my data looks like this:

MAY9/09

MAY10/09

MAY11/09

 

Unfortunately this does NOT translate well into a date format automatically. You’d think that it would convert when you pulled it through the text import wizard, but it doesn’t. Had the dates had 2 digit days consistently, or a delimiter of some kind between the month and day I believe it would have worked. As it is, the MDY format messes up the dates with 2 digit days and ignores the single digit days completely, giving me an even bigger mess. But pulling them in as text means that they can’t be used to drive date dependant formulas, and sort like this:

MAY1/09

MAY10/09

MAY11/09

MAY2/09

MAY20/09

 

This is very irritating, and many users would tell you that this isn’t trivial to fix. I worked up the following formula so that I could convert the dates into real dates:

=DATEVALUE(LEFT(A9,3)&” “&MID(A9,4,FIND(“/”,A9,1)-4)&”, “&2000+RIGHT(A9,2))

(It assumes that the date is in A9)

This works by feeding the DateValue function the date in a “MMM DD, YYYY” format, which it can interpret. Here’s the breakdown:

  • Month: LEFT(A9,3) &” “ returns the left 3 characters of the text string followed by a single space
  • Day: MID(A9,4,FIND(“/”,A9,1)-4)&”, “ returns the string in the middle starting with the 4th character, returning the number of characters between the slash and the 3rd character, plus a comma and a space
  • Year: 2000+RIGHT(A9,2) returns 2000 plus the right two characters

Hopefully we’re not using this spreadsheet in 2100… I can safely say that it won’t be my issue if we are. J

We have lodged a complaint with the vendor as this is stupid. Any financial program that exports financial data should export dates in a format that is compliant with the biggest spreadsheet program out there. I should not have had to waste time writing a formula like that above.