Daily Dose of Excel

It’s spreadsheet week at the Dilbert comic strip. Start with Monday’s strip for spreadsheet hilarity.

I was reviewing some old code recently that uses a two-dimensional array to store some data. Figuring out what was in the array, then remembering what was in the array as I went through the code, was the biggest challenge. It seemed to me that a custom class would have made the code easier to understand. Here’s a snippet of the old code:

Here’s what that would look like if I had created a class:

This example may be a bit oversimplified, but it makes the point. There’s no way I’ll remember what aSolutions(2,i) is, but there’s at least a chance I’ll know what clsSol.LabID is. I’m reusing some of this code on a new project, and I’m getting rid of the multi-dimensional arrays and replacing them with collections of custom classes. So much for the efficiency of code reuse.

From Carlos:

Carlos Quintero (MVP) has just released MZ-Tools 6.0 for VS.NET (http://www.mztools.com/v6/mztools6.aspx). MZ-Tools is a productivity add-in for Visual Studio .NET that supports VB.NET, C#, Visual J# and C++ (partial support) and adds 40+ features to the IDE to locate code faster, to code faster, to design faster, to generate documentation and to enhance your IDE experience. This new version targets VS.NET 2002, 2003, 2005 and 2008 (aka “Orcas”) in a single version.

I use the free 3.0 version for VBA and it’s great. You can get both at mztools.com.

IsMissing is a built-in function that can be used to see if an optional, Variant argument was passed to a sub or function. Since optional arguments are, well, optional, your code needs to determine if they’ve been supplied. If the optional argument is declared as a Variant data type, the IsMissing function will tell you if the argument has been supplied or omitted.

Tony Toews recently blogged about IsMissing.

I’m sure I’ve used IsMissing in the past, but it’s very rare. There’s a trade-off. To use IsMissing, the argument must be declared as a Variant, and no other data type. In that case, I lose the benefits of strong data typing. For instance, I would have to declare a string as a Variant just to use IsMissing.

The other side is that VBA provides defaults for strongly typed optional arguments. If my optional argument is typed as Long and it’s omitted, it becomes a zero. If my optional argument is typed as String and omitted, it becomes a zero length string. That’s not necessarily bad, unless I need to distinguish if a zero was passed or the argument was omitted. For Long optional arguments, there’s no way to tell the difference.

A third possibility is supplying my own defaults, which I almost always do. I can declare optional argument thusly:

All I’ve done is replace the default default with my own default, that is a zero length string with Joe. I still have the problem that I won’t know if Joe was passed or the argument was omitted, but since I have a specific case as the default, it’s less likely to matter.

Over the past few weeks, I’ve had reason to explore the use of VBA to access information on web pages and through web services using both InternetExplorer and XMLHttp. While my study of the two is far from exhaustive, I decided to document the research for general consumption expecting it to take a few hours. As I wrote more issues cropped up and the “few hours” project took several days. But, it’s finally uploaded at
VBA & web services
http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/index.htm

Sam Radakovitz said:

And that’s about everything this date picker can do in a nut shell. There are some enhancements I’d like to make, not sure if I’ll get to them though:

* Update it for Excel 2007 & create nice ribbon buttons
* Support for setting time, like 2:30pm
* If a cell has a formula of =A1, and A1 is just a date, then I’d like to set A1 instead of blowing away the formula that refers to A1
* More ‘semi-smart’ detection for when to show the icon in the cell based on the data or objects around it
* Multiple visible months (previous and next to the left and right of the current month)
* Ability to limit the days available to choose from based on a cell reference
* Make a managed code version of it, right now its VBA and User Forms
* International support

I recently had to add a time control to a form. I used the DTPicker, which allows the user to increment the hours and minutes separately using spin buttons. Similar to the windows Date and Time Properties box.

I long for a better way. I want a clock with one hand that I can move around. For 2:30, I’d put it between the 2 and the 3. Whipping the hand around once would switch between AM and PM. There’s no built in control that comes close to that, I think, so I thought I’d try something else.

The scrollbar is set up with these properties:
Small scroll = 1
Large scroll = 15
Min = 1
Max = 1440
Value = 720 (always start at noon)

The code behind the form is:

The scroll bar suffers from a problem that would also plague my one-handed clock. Granularity. I can move that scroll bar and watch the time zip from 4:00 AM to about 10:00 PM with ease. But I can’t stop at exactly 10:00 PM with anything close to ease. I need a control that gets more precise as I slow down the movement, like my mouse does (yes, I do have one). Now, I get close and then click on the ends to small-scroll one minute at a time.

If you quit watching Dave Gainer’s blog, you might start again. It’s now the Excel Team Blog.

First, that activity is going to pick up again – we plan on having 1-2 posts per week. Second, the posts are going to come from all over the Excel team. The content will remain the same – Excel 2007 and Excel Services – and team members will be writing posts about how they use features, tips and tricks, etc.

Do you know what’s worse than having a bunch of data in an Excel workbook that really should be in a relational database? Having a bunch of data in multiple Excel workbooks that really should be in a relational database. It’s the Excel-workbook-as-a-record model and it’s how we currently store our quotes.

Each quote is a separate Excel file and all the files are stored in a folder. I need some information out of these files, so I have to create my own table. I set a reference to the Microsoft Scripting Runtime dll so that I can use the FileSystemObject.

Now I can loop through all the files in the folder.

I only want Excel files, and get them by looking at the last three characters of the file name. I also only want those files that were created in the last month, or so. This was the primary reason why I didn’t use Application.FileSearch. FileSearch has a LastModified property, but it doesn’t seem very flexible. I could use msoLastModifiedLastMonth, and that would have worked well today, but it would not have worked so well on, say, August 15th. I’m also not really that interested in the date it was last modified and I didn’t see any facility for creation date in FileSearch.

The down side to using the FileSystemObject is that I end up looping through 2,500 files. I’m not sure how to limit the number it loops through.

Here’s the WriteQuotes sub, although not really that interesting:

Note that the Path property of the Scripting.File object includes the filename, which is different than the Path property of the Excel.Workbook object.