Archive for June 2008

Identify Empty Cells in VBA

Whenever I need to determine if a cell is blank, I call the IsEmpty function on the Value property.

If IsEmpty(ActiveCell.Value) Then

This anonymous blogger uses vbNullString.

If ActiveCell.Value = vbNullString

In other news: In Firefox, Cntl+Click on a hyperlink and it will open that link in a new tab. Except for the aforementioned blog's heading. When I Cntl+Click on the blog's header link, it opens a new tab and it follows the link in the current tab. It seems to work normally for any other link on that site. I've never seen that behavior before.

Create a QueryTable from an Excel Source in VBA

Elton is trying to create a QueryTable based on a named range in a closed Excel file. When the code tries to refresh the QueryTable object, he gets an SQL Syntax Error message. Although he's sure his SQL syntax is correct, it's almost certainly not. The syntax you get from the macro recorder works, but it's easy to get a stray quote or something in there when you start messing around with it.

I always rewrite my SQL statements from scratch. The macro recorder and MSQuery make sure the SQL statement is rock-solid by prefixing each field even if there's only one table and by including the path to the table, which already exists in the Connection string.

In this example, I have a workbook name TheDataBook.xls and a named range of TheData.

This code creates a new sheet and adds the QueryTable.

Sub MakeExcelQT()
   
    Dim sConn As String
    Dim sSQL As String
    Dim oQt As QueryTable
    Dim sh As Worksheet
   
    sConn = "ODBC;DSN=Excel Files;DBQ=Z:\TheDataBook.xls;"
    sConn = sConn & "DefaultDir=Z:;DriverId=1046;"
    sConn = sConn & "MaxBufferSize=2048;PageTimeout=5;"
   
    sSQL = "SELECT Name, Number FROM TheDataBook.xls.TheData WHERE Number>=2 ORDER BY Name DESC"
   
    Set sh = ThisWorkbook.Worksheets.Add
   
    Set oQt = sh.QueryTables.Add(sConn, sh.Range("A1"), sSQL)
   
    oQt.Refresh
   
End Sub

Notice the nice clean SQL statement. Due to persistant errors I can't show you what the macro recorder would have returned, but it would have looked something like

.CommandText = Array("SELECT TheData.Name, TheData.Number FROM 'Z:\TheDataBook'.xls.TheData TheData WHERE (TheData.Number>=2) ORDER BY TheData.Name DESC;")

Yuck. Here's the results from the above code.

Using an Analysis Template — Updating the data source

The motivation for this quick and incomplete draft was Dick Kusleika's post on distributing/communicating a PivotTable template (http://www.dailydoseofexcel.com/archives/2008/05/21/pivottable-markup-language/).

In the approach I've adopted for the past few years, the basic idea is to separate the data from the analysis and presentation. Then, a simple piece of code lets one update the data connections in the analysis workbook to point to the data source of interest. This lets me distribute an analysis/presentation template (with the associated add-in) to my clients knowing that they will be able to analyze any compatible data source with the template.

For details see
Designing an Analysis Template
http://www.tushar-mehta.com/publish_train/data_analysis/22.htm

Excel Blog Happenings

The Contextures Blog has moved off of the wordpress.com domain and onto the contextures.com domain. Check it out if you haven't, update your feeds if you have.

PTS Blog is holding a contest for physics and engineering spreadsheet models. Since I've mentioned it on my blog, I get to submit six entries. I'm not a physicist nor an engineer, so I won't be submitting any.

On-the-fly data entry form

Hi all,

So after Dicks (nice to read) Alive and Well , let's do some Excel/VBA stuff again.

I intend to get a bit of discussion on this one, so bear with me.

I'm currently developing an Excel workbook for a customer. One of the interesting things with this project is that it consists of multiple data tables, each in its own worksheet. The customer needs to be able to edit the data in these tables.

One of these might contain these fields:

empId
empCompanyId
empRegNo
empFirstName
empLastName
empDeptId
empFunctionId
empDOB
empGender
empTitle
empFunctionGroup
....

The way I would normally have done this is by creating a userform with a control for each field and all the coding that is needed to handle record selection and stuff. (and yes, I do know there is MS-Access :-))

But since I have an odd 5 worksheets to handle I decided it would be nice to have a generic data entry form that would build itself using a companion worksheet for each data sheet.

For each worksheet that requires data entry I inserted a companion sheet with this information:

dataentrysettings1.gif

My VBA code inside the userform's code module reads this sheet and builds the controls accordingly.
The form has a couple of properties I can set to control appearance. All it now takes to show the data entry form for worksheet "oSh" is this bit of code:

Set frmDataEntry = New ufDataEntry
With frmDataEntry
Set .Source = oSh
Set .SourceSettings = ThisWorkbook.Worksheets(oSh.Name & "_Fields")
.Title = sTitle
.RowCount = 14
.FieldWidth = 120
.LabelWidth = 150
.Label2FieldMargin = 12
.VertMargin = 3
.HorMargin = 6
.Initialise
.CurrentDataRow = 1
.Show

I've got this all up and running, including Validation and all (and yes: I'll be writing this all up in detail some day).

Now to the questions of the day:

What do you think about the method I chose?
What alternative solutions have you come up with in the past?

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Alive and Well

Howdy. It's been quiet at the ol' DDoE lately, but I thought I would check in and let you know I'm still around. I generally slow down a little in the Summer, but this Summer has been surprisingly busy. I have some nice Excel posts in the queue. In the mean time I thought I'd tell you what I've been up to.

Golf, of course. My goals this year are 1) No less than five rounds under 80, 2) no more than five rounds over 90, and 3) an index under 10. After 10 rounds (about 1/3 done), I have one round under 80 (75 - best round ever) and four rounds over 90. My index, due to that stellar round, is down to 9.1

It's headed back above 10, sadly, unless I can shoot better than 84 this weekend.

A couple weekends ago I went to Branson, MO for a family reunion. It was great to see some of my family that I don't see often. I played golf at Branson Creek, which is an outstanding course. Other than that, I don't see myself making it back to Branson. It's just not my kind of place. On our way into town, my son said "They should call this place advertisingville" referring to all the billboards lining the highway. When ads for comedy shows have this guy

I assume it lacks the edge I require in a comedy show. We did see Kirby, the Prince of Magic. I think it was a good magic show and that I just don't like magic shows. I had the over/under at five bible verse quotes and he only did one, so there's that (it's really an uber-Christian area of the country). Kirby's wife Bambi is nails, which I think constitutes coveting on my part.

While I was in Branson a tornado hit near my house. We sent someone by to check it out and she told us we had a hole in our roof. I started to freak out, but someone else went by and said it was just a few shingles missing. Whew. Unfortunately, my basketball hoop blew through the windshield of my neighbors car.

Tornadoes are the best kind of storms. With floods, everyone in the flood plain is affected. You can't get away from a Hurricane if it hits where you live. Blizzards cover a huge area and spare nobody. Tornadoes, on the other hand, can demolish my neighbors house and leave mine untouched. Oh, their deadly, don't get me wrong. I just like those odds.

A while back I read about stand up desks. I didn't pay much attention to it at the time. Recently on the stackoverflow podcast Joel Spolsky was saying he was buying adjustable height desks so his coders could stand or sit as they wished. It dawned on me that I had an adjustable height desk. I know this because I bang my knee against the switch about once a week and I have to return the desk to its original height.

Last Friday I decided I was going to give the whole standing thing a try. I raised my Steelcase desk as high as it would go. It doesn't go high enough. Why can I lower this desk to 20" off the floor, but I can't raise it so that the monitors are at eye level? Perhaps I just don't get it. I ended up raising the desk as high as it will go and tilting the monitors upward so that I'm looking down at them. My laptop monitor tilts, obviously, but my second monitor didn't. I grabbed an unused monitor that does and now my laptop and the new monitor don't line up. Finally I ended up with the desk at max height, the keyboard tray three inches above the desk, the laptop on a phone book (with rubber grommets as feet to allow air flow) and the laptop and monitor tilted.

The result: After two days (I went to the College World Series yesterday), I have concluded that my feet and back hurt when I stand and they don't when I sit. My plan is to mostly stand half of the day and mostly sit half of the day. Only the computer part of my desk is raised, so if I'm doing tree-based paper work, I'm sitting at the normal-height portion of my desk. I'm not sure what the schedule will be, but currently I'm standing all morning and sitting all afternoon. I think I need to stand from 8-10, sit from 10-12, stand from 1-3, sit from 3-5. I'll give it two weeks and see what I come up with. I predict the hurt feet and back will go away and I'll be standing most of the day.

Finally, I'm still mowing with my robot mower. I could swear that John Walkenback said it would be more work than it's worth, but I can't find the quote. Anyway, I want to make sure he gets credit if he deserves it. After 1.5 years, I think it's more work than it's worth. I didn't properly store the battery this Winter (totally my fault) and now it doesn't last nearly long enough to do a good job. That means that I have to buy a new battery. My wife is telling me to admit failure and buy a regular mower. Of course that's not going to happen. I've ordered a new battery and I'm going to get this year and next, at least, out of this mower. Then the boy will be old enough to mow the grass and I can eschew the robot and get something more traditional.