Excel Formulas are Like Latin

I'm doing a spot of data manipulation and importing for MotorShout at the moment and am currently importing every Mercedes-Benz ever. Sounds like fun? Not really. Anyway, the source data is in an Excel spreadsheet which I'm modifying to match up with our existing SQL Server input table. The input data for Mercedes-Benz is missing the car name’s prefixes. e.g. "CLK500" is missing the “CLK” but I do have the "500" and the “CLK-Class” family name in other columns. I want to merge the “500” with the “CLK” from the family name to get the model name. Easy peasy? Hardly!

Here is the alien Excel formula that rubs my 15 years of coding experience the wrong way:

=CONCATENATE(IF(ISERROR(SEARCH("-Class",B2)),"",SUBSTITUTE(B2, "-Class","")), D2)

Why is this alien? Well for a start a simple Replace() method would have done this job fine except Excel already has a REPLACE() function... which replaces text at the given indexes, WTF? OK, so they have SUBSTITUTE() that does what I want, fair enough, let's get on with it.

OK, now what if the family name doesn't contain "-Class"? I don't want to prefix the model name in that instance so my first thought is I'd use an InStr(), or IndexOf() or Contains() method to check if the string is in the family name, sorry wrong again! I have to use Excel's SEARCH() function. But do you know what makes SEARCH() oh so special? If the string isn't found in the source it THROWS AN ERROR! The endlessly helpful #VALUE! error, fun times indeed. In their wisdom Microsoft therefore ensured Excel has an ISERROR() function which, you guessed it, checks for an error. Sorted!

Finally, a simple IF() statement wrapping that ISERROR() condition and we're almost there. Just need to concatenate (yes, the function uses that name) the newly trimmed out family name with the model name and voila! Simple!

*sigh*

This is what I would have liked to write (C# mashed into Excel please!):

=B2.Contains("-Class") ? B2.Replace("-Class", " ") : "" + D2;

Welcome To the Future

I had a taste of the future today, the future that we already live in. It all started when Microsoft's Larry "Major Nelson" Hryb started tweeting about behind-the-scenes goings on at the lead up to Microsoft's CES Keynote. His tweet read, "Steve B is just walked by me...wearing a very red sweater . We are getting ready to start". Steve B in this instance is Steve "Developers, developers, developers" Ballmer, CEO of Microsoft, who was to do the talking at the keynote.

Things got more interesting as I started to watch the live stream of the keynote which was being pushed out across the internet from Las Vegas to anyone with a browser and the Silverlight plugin. The audio level was pretty low and I had to bump my system volume up to maximum in order to hear what was going on. Then Major Nelson tweeted this, "Told the stream dudes about the audio level. They're fixing"... was he in my head? No, other watchers had tweeted back to Major Nelson about the audio level.

The left channel dropped out then came back as the right channel then dropped out. When the audio stream returned in stereo at a much improved level The Major asked, "Let me know how the audio is....they tweaked it." Myself, and I'm sure many others, replied to say that all was good which prompted Larry to offer up a final tweet on the subject, "I brought the streaming to my PC to see your tweets about audio. You guys helped fix it :)"

It was at that moment that I really thought about what was going on. It might sound like ordinary day-to-day goings on but seriously, I was watching a live video of Microsoft's head honcho half way around the world giving a speech to thousands of people while at the same time I was communicating directly with another Microsoft senior employee who was doing techy stuff in the wings. Can you imagine doing that just a few years ago? We really are living in the future.

Sony To Post First Loss In 14 Years

According to news.com.au's sources in Japan Sony, the electronics giant, will be posting its first loss in 14 years. Sony is expected to "post an operating loss of ¥100 billion" ($1.6 billion AUD) due to falling demand and a stronger yen the Nikkei business daily reported.

Personally I'm only surprised it hasn't happened sooner. Sony have a tendency to come out with wonderful new gadgets that cost an arm and a leg then not bother advertising them or they sabotage themselves in other ways.

Back in the day I bought (for a ridiculous amount of hard earned cash) the first "portable" MiniDisc recorder, the MZ1. It was huge but dear to my heart but not once did I see a Sony advertisement for MiniDisc other than on English soccer jerseys. By the time other people knew about MiniDiscs the MP3 revolution had arrived burying what could have been a large money spinner and really a handy product.

Now we have the PS3 which is leaning heavily on its Bluray capabilities to convince people that it's worth twice as much as an Xbox 360 which does essentially the same thing. Obviously not everyone is convinced.

It's about time Sony had a big rethink. Maybe they should use Microsoft as an example after all the positive hype around Windows 7 compared to the negative hype surrounding Vista prior to release.

View Code Without Right Clicking

Here's a little Visual Studio 2008 tip for you. I find myself right-clicking ASCX/ASPX files and selecting "View Code" frequently during the day and today I just had enough of it. So I went a hunting on the Googley internet and didn't find much of anything useful (mostly just hints on using F7 and Shift-F7 to switch between Code View and Design View, i.e. not what I was after).

Using the search box in the Visual Studio Keyboard options to narrow down the list I found this little gem (click the image to see the full size version). Just select "OtherContextMenus.SchemaExplorer.ViewCode" and assigned it to your hotkey of choice. I used F12 F1 but I may change this later on if I find it too cumbersome. Don't forget to hit the "Assign" button or nothing will happen.

So now when I'm editing the front-end code and want to get to the back end code I just whack the old F12 F1 key and voila!

EDIT: Dammit, I just realised that F12 is the hot key to go to a method's definition so I'm now giving F1 a go seeing as I never use the inbuilt help. You can get your answer via a Google search before the inbuilt MSDN help has even loaded.

EDIT: If you accidentally assigned F12 to View Code you can set it back to its original setting by assigning it back to Edit.GoToDefinition in the Keyboard settings.

I Shall Call Him... Mini Me

After all the hype and excitement building up to the release of the new Xbox 360 dash (the total opposite to the lack of communication for the Facebook redesign) we have this little guy. Say hello to my new Xbox 360 Avatar.

While the new dash is fun to play with it's these Avatars which were always going to be the big thing. We all know Microsoft took the success of Nintendo's Miis and ran with it. Miis are basic. How basic? Miis are so basic they'd make a Lego man blush.

The Wii offers more options for rearranging facial features on your Mii than the 360's Avatars but the rest of your Mii is basically choosing a colour for your top (including you ball hands). The Avatars feel more like Sims and look a lot better with their detailed cloth textures and far surperior lighting. Another benefit is it's all hooked into your online presence via Xbox Live/Windows Live (take a look at my gamer tag over to the right there) so your little console guy is available in the "real" world.

Of course Microsoft plan to take this funky little idea and make money out of it with a limited selection of tops, pants, shoes and accessories available for dressing your little You up. How will they make money? By offering extra items like themed shirts/outfits for a small payment, e.g. a Guitar Hero t-shirt for 100 points. The most obvious instance of forcing this is in the ladies shoe department where a total of 8 shoes are offered. Ladies don't like shoes do they? I'm hanging out for the Prada shoe pack! I'm sure it'll only be 15,000 points... bargain!

Anyway, it's all good fun and the new themes are pretty funky too... oh, and if you're lucky to live in certain countries you get the Netflix integration where you can watch HD movies directly through your 360.

Amazing what you can get for free these days (not the movies).

Video Playback, Windows 7 FTW

If you've ever tried to set up a computer to play the varying forms of videos you can get on the internet these days then you probably know how much of a headache it can be. Codec packs have been the answer for a few years now (I'm currently using K-Lite for my home theatre PC) but news today may make them a thing of the past.

On Long Zheng's blog, I Started Something, he has posted an item detailing the Codec support found in Windows 7 and how Microsoft's "Vista 2.0" will play almost everything out of the box. Great news for those of you who "acquire" TV episodes or "backup" your own DVDs as most MPEG4 formats are supported (XVID/DIVX/MP4 et al) plus those whacky 3GP Codecs your mobile phone is so fond of.

What interests me the most is the support of the H264 (MPEG-4 AVC) Codec, which is the current standard for high definition video encoding, and the total lack of support for the Matroska (MKV) container format (just like the Xbox 360/PS3) which appears to be totally intentional on Microsoft's part.

I'll keep my eye on this one and props to Microsoft for at least making our Codec Pack horrors slightly less painful.

Enough Already, Stop Using IE6

As most of you know I run a little Facebook application called Mood Stones which attracts a wide variety of users. I rely heavily on alpha transparency in the PNGs I use and Internet Explorer 6, which doesn't do alpha transparency in PNGs, has been a thorn in my side for ages.

Earlier this year I added in a little bit of JavaScript which displays a small message telling IE6 users that they should upgrade and, while the percentage of IE6 users has dropped since then, there are still a lot of them.

In the first 3 months of 2008, 75.4% of Mood Stones users were running various flavours of IE with 28.4% of those running IE6 (21.4% of the total). Flash forward to the past 3 months (ending October) IE's overall usage has dropped to 67.4% of the total with 22.9% of those running IE6 (15.4% of the total). The usage stats on this blog are slightly different (more Firefox oriented) but still there are enough IE6 users to worry about.

I've wondered why those people running IE6 aren't up in arms because so many things on the internet would error or just plain not work with their browser of "choice". But then I had a support request at the weekend which shone a light on the situation.

One Mood Stones user said her stone image was over the top of the text on her Facebook profile. I immediately thought of IE6 and its total lack of CSS standards support and asked her which browser she was running. She said it was IE6 so I suggested she upgrade to IE7, Firefox or even Safari. I then got the following response:

"Hi Michael THANKS SO MUCH!! I upgraded to IE7 and it's working great now...so is everything else...lol...never realized how good some things on the internet were supposed to work!! thanks again!!"

At this point I had a moment of clarity and thought that perhaps most IE6 users don't realise there's something wrong with their browser and their the assumption is that the internet is inherently broken and most stuff just doesn't work. Well, if you're using IE6, the internet isn't broken, your browser is. It was released 7 years ago and you should get IE7 or Firefox or Safari or even Chrome... just get rid of IE6.

Lazy Friday Mega Post

Too many good things to post about today so I'm just going to list a few.

• Hungarian School Teacher Strips For Students (video, semi-NSFW)
• David Tennant Leaving Doctor Who 2010
• Samsung FTW With 40" 1080p OLED Prototype Screen
• Microsoft Makes Uber Cool "Surface v2.0" With More Surface (video)

*phew*

Windows 7 Will Do Stuff

Sorry for the day-late post but here's where you can get all you need to know about the new version of Windows, Windows 7. To me it looks like a point release for Vista but then again, it's not out for another year so who knows what'll happen between now and then.

Some highlights:

The icon-only items in the taskbar, which is now very square looking, are highlighted with a dynamic colour based on the main colour in the icon.

The Sidebar is gone and gadgets are now dumped on the desktop.

Hovering over an item in the taskbar brings that application into focus by making the other windows transparent (kind of like switching to it without switching to it). You can also do this with the Desktop so you can see your gadgets without having to minimize everything.

Supports up to 256 CPUs but can run on a pissy little eePC's CPU plus new DirectX enhancements means 50% less memory usage for the UX.

Lots more Windows 7 info can be found over on Long Zheng's blog.

Live ID, Now With More Open-ness

Great news today from the Windows Live Dev site where they announced that pretty soon you'll be able to use your Windows Live ID (MSN Messenger, Hotmail, Xbox Live etc.) to authenticate with Open ID 2.0 sites.

"The Windows Live ID OpenID Provider (OP) enables anyone with a Windows Live ID account to set up an OpenID alias and to use that alias for identification at an increasing number of OpenID 2.0 relying party sites". JOY!

For those unsure of what Open ID is Wikipedia says, "OpenID is a shared identity service, which allows Internet users to log on to many different web sites using a single digital identity, single sign-on, eliminating the need for a different user name and password for each site."

I already have an Open ID set up with another provider but if the Windows Live community of users has easy access to such a wonderful technology things might start heating up. Aren't you sick of trying to remember usernames and passwords for heaps of sites?

Quake In Your Browser Not Your Boots

It looks like some whacky folks have gone and ported ye olde Quake over to Microsoft's Silverlight. You can sit and watch the demo run but as soon as you try to load up a new game it just hangs. No audio yet either. Still, pretty cool.

Check it out on the Innoveware website: http://www.innoveware.com/ql/640/QuakeLight.html, or watch the video below.

Note, this isn't video playback it's the actual game engine running in Silverlight. You can read more about it here.

Silverlight 2 Is Out, Go Get It!

Quick, before they release another version! Go get your fresh copy of the FULL RELEASE of Microsoft Silverlight 2. Yes, Microsoft has finally finished fluffing around with beta version after beta version and have cooked up this final release of their multimedia web delivery thing.

Please note, if you do any Silverlight development you'll need to install the final release of the Silverlight 2 Tools for Visual Studio 2008 SP1 before attempting to install Silverlight 2..

SHanselman Needs You

If you're a .Net developer then Scott Hanselman wants to hear your opinion. He's created a 1-question survey where you tick the boxes of the architectural features of .Net that you use.

Scott, a Microsoft Senior Program Manager, says "...one of the things I like to do is take your comments, questions and feedback directly to the right folks inside Microsoft.".

He then goes on to say, "They have all sorts of scientific methods, analysts, and smarties that give them information, usage details, etc. Sometimes, however, I like to throw the product planners the occasional totally RAW web survey, just to make sure that there's fewer layers between me and the bosses."

So go and give Scott a hand, it'll only take you 10 seconds... http://www.tinyurl.com/dotnetsurvey

Go! Give feedback!

New XBox 360 Dash, November 19th!

News has hit the streets after the Microsoft press event at this year's Tokyo Game Show that the eagerly awaited "reboot" of the Xbox 360's dashboard (AKA "XBE") will be available for download on November 19th. No mention of it being geographically based or if it's just a big global release but I assume the Netflix integration will only apply to countries that already have it... i.e. not Australia.

Thanks for the heads up Engadget (and others).

So, will you make your "Avatar" look like you in real life or will you go all out to make something whacky to hide behind?

I'm A PC

As a sort of follow on from the Jerry Seinfeld/Bill Gates ads comes this new angle on PC advertising (remember, PC isn't just Microsoft, it's everyone except Apple... which apparently doesn't make personal computers). According to the new ad lots of people have PCs, who would have guessed!? I'm a PC and I don't have a beard.

Jussy, King Of The Australian Nerds

Just as a follow on from my previous post highlighting Jussy's kingness in the land of nerds comes this new shot taken from a certain video (which you may see at a later date) of the man in action.

This is from the Australian Tech.Ed 2008 Keynote... I'm quite pleased how well the voice-to-text system built into my camera worked too.

Tech.Ed Summary

Well, Tech.Ed Australia 2008 has come and gone leaving me with a bursting brain and a bursting belly.  Instead of going into detail about what is currently at the front of my mind I'm going to list a few bullet points in no particular order.  I may expand on these at a later date...

• RFID is a good idea, if it works
  
• The best thing you can do for UX is to chuck everything out that you don't need (i.e. the KISS principle) and to START with the design.
• Empty drink fridges for most of Thursday are NOT cool
• I'd like to see something ELSE running on the Microsoft Surface other than the photo app
  
• Tips of the Show: Ctrl-. and Copy/Paste References in Visual Studio 2008
  
• Full Text Search in SQL Server 2008 kicks ass
  
• Scott Hanselmann is an absolute pisser and knows his stuff/audience
  
• The Windows Mobility team are insane
  
• Dr Neil tells it like it is (and is a bit of a stirrer)
• I still prefer LINQ to SQL over LINQ to Entities
• I can't decide if Adam Cogan was acting stoned

All up, it was well worth the time and expense (thanks J&A!) and I can't wait to get in to work on Monday to start fiddling with stuff.  Chucking my database on 2008 and the site on IIS7 (bai bai Cassini!). *fingers crossed*

Microsoft Wants You To Touch Its Ball

You've probably heard about Microsoft's giant multi-touch screen Surface project. You may have even been fortunate enough to play with it. Well that's all passe now as Microsoft Research has decided that a coffee-table sized flat surface just doesn't cut it any more, now it's all about spheres.

The ingeniously named Sphere is coming out on Tuesday at Microsoft's Faculty Summit in Redmond and Engadget says "...the prototype relies upon an infrared system to detect hands, fingers, and objects as well as an internal (not external like Surface) projection system developed by Global Imagination (and customized by Microsoft) to deliver its multi-touch UI. At the moment, Microsoft is demonstrating photo manipulation, a spinning globe, 360-degree video, and a simple Pong-like game, the latter giving us a taste of future gaming possibilities."

Fun?

Games For Windows Live Free To All + DX11

Snubbing E3 Microsoft announced at their own GameFest gaming conference that, as of now, Games For Windows Live (the PC equivalent of Xbox Live) multiplayer features are now free to all.

"The move to free means that achievements, enhanced "truskill" matchmaking, cross-platform play with the Xbox 360, voice and text chat, friends lists, are all now free to Windows gamers. This change to free is for all Games for Windows LIVE titles, past and future."

Also at their love fest, Microsoft announced Direct X 11.

The company calls DirectX 11 a "big step forward for gaming, adding features onto existing DirectX 10".

Key components of DirectX 11, will include:

• Full support (including all DX11 hardware features) on Windows Vista as well as future versions of Windows
  
• Compatibility with DirectX 10 and 10.1 hardware, as well as support for new DirectX 11 hardware
• New compute shader technology that lays the groundwork for the GPU to be used for more than just 3D graphics, so that developers can take advantage of the graphics card as a parallel processor
• Multi-threaded resource handling that will allow games to better take advantage of multi-core machines
• Support for tessellation, which blurs the line between super high quality pre-rendered scenes and scenes rendered in real-time, allowing game developers to refine models to be smoother and more attractive when seen up close

That's great and all but will we ever see games using it? There are only 3 games I can think of off the top of my head that use DX10 and the only one I played is Crysis. Meh... let's wait and see.

Xbox 360 Dashboard Overhaul

You've probably already heard about the fall update (that'd be spring here on the good side of the planet) to the Xbox 360 dashboard. You may have even already seen the screenshots and funky little video. Well screw you, here they are again!

For everyone else, this is what the crusty old dashboard will be replaced with in about 3 months. A more 3 dimensional and clean look with the new avatars making an appearance. Of course knowing Microsoft there'll be ads all over it when we do eventually see it.