Conditional formatting and the N() function

Just discovered something new with Conditional Formatting. Maybe you Excel gurus already know about the N() worksheet function, but figured I'd share anyway, just in case. Feel free to scoff at my unworthy Excel skills.

In my project, I have a standard output worksheet set up with references to a linked sheet. I want to use conditional formatting to highlight any value greater than 0. Simple right? Well, when I use the following setting:



The result looked like this:



That's not what I wanted. The blank cells shouldn't be highlighted. So it's a simple change to check for the blank cells, right?



This didn't work either. The cells $M3:$M10 are not blank, they contain a formula that references the linked sheet. So the 1st condition returns false.

And unfortunately, Conditional Formatting doesn't let you reference another worksheet within a formula, so I couldn't use =ISBLANK('report(tblAnalyzerStats)'!M3)

The solution was to use the N() worksheet function. I'd never heard of that before:



The results ended up exactly like I wanted:



Hope this is new for at least one of you. Have a great weekend!

VBA and VSTO

(originally posted 5/2/07)
_____________________________________________
From: SD-Jim Sawyer
Sent: Wednesday, May 02, 2007 2:23 PM
To: SD-Dave Brann; SD-Kevin Bennett; NS-Geoff Skipton; SD-Emily Ligotti
Cc: NS-Jess Bardin; CD-Jason Moline; SD-Brian Mascarenhas; SD-Ian Anderson
Subject: RE: VB6 retirement

(broadening the audience…)

Eliminate VBA? You can do it already!

As Kevin pointed out, "Visual Studio Tools for Office" has been around since 2003, with a major upgrade in 2005, and it's the recommended way to create document-specific solutions (like our Excel UI). In Office 2007, VBA is still supported for convenience, but VSTO is where Microsoft is investing for the future.

Why would we want to do this? Here's 9 "compelling" reasons:

1. Managed code beats unmanaged code
2. Object-oriented programming beats procedural programming
3. .NET Framework beats… not having the .NET Framework
4. A better development environment
5. Code reusability beats rewriting every time
6. Web Services is the best way to integrate
7. Security - To protect your users and your code
8. Ease of deployment - No more emailing patches
9. Robust and programmable error handling

To me, #3, #4, and especially #8 are key for our work. It's been so nice not to have to deal with the DLL hell that we used to on installs.

Here's a detailed example of migrating from VBA to VSTO.

And here's a less rosy look at the VBA -> VSTO migration, for equal time (the comments are pretty good too).

Sure, there are a lot of "yes, but…" For example, you could say it's pretty convenient to have a code editor and compiler (interpreter, technically) within Excel itself so you don't have to work "outside" of the application. Personally, I think Visual Studio is great and wish I could write every application or simulation model within that IDE.

For the Excel UI specifically, there is an open question if it is worth spending any time migrating the platform when we are really only down to a couple of users (literally. 2 project managers). I ran the upgrade wizard on it a couple years ago, and let's just say more work would be needed. I wrote a development plan and roadmap for doing this work, but never convinced myself of the business need. Modeling Studio is just a better place for us to spend our time.


James T. Sawyer
Senior Analyst
TranSystems | Automation Associates, Inc.

Where should I deploy my files?

(originally posted 5/1/07)
______________________________________________
From: SD-Jim Sawyer
Sent: Tuesday, May 01, 2007 12:43 PM
To: Everyone-NS; Everyone-SD
Subject: Where should I install my projects on the customer's machine?

Hey all --

Amy just brought up a good reminder for all of us: Let's try to be consistent in our deployment of our projects to customers.

Ideally, our customers would have a similar user experience when executing any TranSystems model or software application, regardless of which person, team, or office wrote the project. This helps provide our models and applications with a common "branding" across our user base -- one small way our "simulators and animators" can chip in to support TranSystems' overall corporate branding initiative. It was a big motivation for creating the Modeling Studio in the first place!

It starts with the small stuff -- where does the user find the project on his or her computer?

If we're installing projects onto a customer's computer, the preferred installation folder should be:

C:\Program Files\TranSystems\ProjectName

For example, our clients at OCD have multiple models they work with:

C:\Program Files\TranSystems\AutoVue Modeling Studio
C:\Program Files\TranSystems\4,3 Modeling Studio
C:\Program Files\TranSystems\Valuator Modeling Studio

Or, for those projects that prefer the Excel UI:

C:\Program Files\TranSystems\My Old-School Simulation

This means that the shortcuts off of the Start Menu look like:
Start >> Programs >> TranSystems >> AutoVue Modeling Studio

Thank goodness we no longer have to worry about the "Program Files\AAI" vs. "Program Files\Automation Associates" vs. "Program Files\Automation Associates, Inc."… See how much simpler our lives have become as part of TranSystems!

It's open for debate whether or not the icon that starts the application should be in the TranSystems folder, or within a subfolder specific to the project. Microsoft has contradicting examples of this. Their published guidelines on "Designed for Windows XP" don't call this out specifically. I personally use subfolders…. But I won't be mad if you don't. :-)

Amy and Geoff are working on a .WSI file (Wise Installation template) to share with us as a starting point. We'll upload it into the Modeling Studio folder on SourceSafe when it's complete.

Thank you Amy, for bringing this to our attention! It's worth a refresher every now and then.

-- Jim

P.s. to developers only -- your kind-of sort-of regularly scheduled blog will return soon

James T. Sawyer
Senior Analyst
TranSystems | Automation Associates, Inc.

Task estimating: I'll be done in exactly 2.75 hours!

(originally posted 3/30/07)
______________________________________________
From: SD-Jim Sawyer
Sent: Friday, March 30, 2007 5:19 PM
To: SD-Dave Brann; SD-Kevin Bennett; SD-Emily Ligotti; NS-Geoff Skipton; NS-Jess Bardin; CD-Jason Moline; SD-Brian Mascarenhas; SD-Ian Anderson
Subject: Task estimating: I'll be done in exactly 2.75 hours!

Hey kids,

There's one question that we developers hear at least once on every single project, that most of us find mildly uncomfortable, and some of you find absolutely terrifying -- a vile and cruel form of torture worse than chugging a jar of warm mayonnaise, being forced to see a chick flick with your mother, or having an afternoon colonoscopy (Dave B. excluded). No, I don't mean "Why aren't you finished yet?" although that's a good topic for a later day.

I mean the infamous: "How long is it going to take you to do Task X"?

The wheels start spinning…

"...OK, this is pretty straightforward, probably a few hours' work, maybe 2-3. What should I say? Better give myself a little extra time -- I don't have all the information anyway and who knows what might come up. 5 hours. Oh, maybe I should say a really really big number so that when I complete it in less time, everyone will be impressed with how awesome I am? 77 hours. Hmmm, but the PM won't go for that, they know enough about what needs to be done to be dangerous, but they just don't know all the details like I do… oh what the heck, let's just multiply by 3 to be safe…"

"8 hours".

So what does this number really mean? As we get more and more experienced, we're supposed to get better and better at the accuracy of estimating our tasks. But bad things can and do happen during the course of a project, and our estimates don't always line up with what it takes to do the work in reality.

This can be frustrating for us and for the PM, especially when it starts blowing the budget. But why does it happen?

Maybe it's not just that we're horrible estimators (though all of us need to continue to improve in this area), but that there's something inherently funky about the act of estimating itself. This article has one interesting answer… and actually shows how simulation (yes, simulation) might be used to help get a better sense of project completion.

http://www.stickyminds.com/BetterSoftware/magazine.asp?fn=cifea


Excerpt:
Software estimates actually contain some uncertainty. It is a little bit too simple to say that a given task is going to take "eight hours." It is far more realistic to say things like, "There is a 50 percent probability that we can get this done in eight hours." That is a very different statement!

"OK, so I might go over," you say. "But I might also go under, and on a long project all the tasks that came in late will be balanced out by the tasks that came in early!"

Aha!

That, precisely, is where you are wrong.




James T. Sawyer
Senior Analyst
TranSystems | Automation Associates, Inc.

What is my PATH and what do I need it for?

(originally posted 3/5/07)
______________________________________________
From: SD-Jim Sawyer
Sent: Monday, March 05, 2007 12:55 PM
To: SD-Dave Brann; SD-Kevin Bennett; SD-Emily Ligotti; NS-Geoff Skipton; NS-Jess Bardin; CD-Jason Moline
Subject: What is my PATH and what do I need it for?

Hey gang,

Sorry it's been a while since the last blog… They ain't billable after all! But this one just came up today and I figured it was a good refresher.

Back in the dark days of software development, we had to use the Command Prompt to compile and build our applications. Real Men and Women used the keyboard only and the fastest typer was clearly the alpha geek. There was no pointy arrow on the screen. "Menus"? Bah who needs em. To "Rebuild All"… you typed in separate commands to compile each source code file, link them together, and create the executable.

(I won't mention the even darker days of punch cards)

Today, great IDEs like Visual Studio (and pretty good IDEs like in our simulation software) have made things a helluva lot easier on us developers. However, even in 2007, there are still times we'll want to use the command prompt.

For example, for you AnyLogic developers, the Java SDK contains a number of potentially useful utilities that are run from the command line. (e.g. I was looking at http://java.sun.com/developer/technicalArticles/Security/Signed/ this morning)Same with Visual Studio. And for Arena developers, the command-line compilers model.exe, expmt.exe, and linker.exe can still be useful for debugging certain esoteric Arena error messages. I'm sure there's some use in Automod too.

One of the key concepts in running applications from the command line is the idea of setting the PATH. The PATH is a system setting that specifies the set of directories used to search for executable files. This means that instead of typing
> C:\Program Files\Java\jdk1.6.0\bin\javac.exe MyClass
I only have to type
> javac MyClass.

Your PATH normally comes set up with some default paths to Windows folders, and some installation packages will also update the PATH. You can modify the PATH yourself from within Windows too.

1. On the Start menu, right-click My Computer and select Properties
2. Select the Advanced tab.
3. Click on Environment Variables.
4. In the second group box called System Variables, scroll down until you see a setting for PATH.
5. Click on the Edit button and type in the location of the new folders.

For example, I added the folders for the Java JDK and the Siman compilers to my PATH, as you can see below. The other folders were added when various programs were installed.

C:\Program Files\Rockwell Software\RSCommon;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\Intel\Wireless\Bin\;C:\Program Files\ThinkPad\ConnectUtilities;C:\Program Files\Common Files\Lenovo;C:\Program Files\Lenovo\Client Security Solution;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\Autodesk\DWG TrueView\;C:\Program Files\Java\jdk1.6.0\bin;C:\Program Files\Rockwell Software\Arena 11.0

Well hopefully you'll be spending the majority of your work time in modern IDEs, but if you ever decide to go old school, we can get you a copy of "4DOS"...



James T. Sawyer
Senior Analyst
TranSystems | Automation Associates, Inc.