I’ve just had to work out how to change the cell drop down source of an Excel cell based on the value of another cell. In this case I had one drop down with the Australian states in it and I wanted this to drive a second dropdown that allows users to select the Federal Electorate. It turned out to be pretty easy (and quite nifty). My spreadsheet is setup with a series of named ranges containing the state electorates. So, for example, there’s a named range called ElectoratesNSW containing all the electorates for NSW. Just add the following to the sheet where you want the data validation magic to happen.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sState As String
  Dim rngTmp As Range
  '
  ' Cell in row 7 and column 3 is the State dropdown
  '
  If Target.Row = 7 And Target.Column = 3 Then
    '
    'Cell in row 7, column 4 is the electorate dropdown
    '
    Set rngTmp = Me.Cells(7, 4)
    If Len(Target.Value) > 1 Then
      sState = Target.Value
      rngTmp.Formula = ""
      With rngTmp.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & "Electorates" & UCase(sState)
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
      End With
    Else
      rngTmp.Validation.Delete
      rngTmp.Formula = ""
    End If
  End If

End Sub

Excel is fabulous. It’s wonderful. It’s without doubt the most useful software tool I’ve ever used since I started using computers. But oh my can it be annoying. Right now I am trying use the TRIM() function to remove the leading spaces from some text strings. And it doesn’t work. Why doesn’t it work? Well because the strings I am trying to trim seem to start with ASCII character 160 rather than the regular ASCII 32 space character. I confirmed this with the Excel formula CODE(LEFT(A1,1)) where cell A1 contained the vexatious string in question.

ASCII 160 is the breaking space character and for whatever reason Excel displays it as a space but doesn’t trim it with the TRIM() function. Stupid. Stupid. Stupid Excel. So to trim the Excel string I need to do this:

TRIM(SUBSTITUTE(A1,CHAR(160),""))

Did I mention Excel was stupid?

p>If you’re working with DateTmePickers on a VBA form in Excel 2010 be aware that there’s a fairly serious bug with assigning date values affecting the actual system time. For example the following code will change the system time of your PC to 1/1/1899!

  Date = DateSerial(1899,1,1)

This is a big security hole in Office 2010, at least on Windows7 x64 Professional (which is the only place I tested it on). Being able to change the system time so easily is ridiculous. Up until now I’ve had to set the system time from VBA (or VB6 for that matter) by making use of the SetSystemTime API function doing someting like this:

 Public Declare Function SetSystemTime Lib "kernel32" _
   (lpSystemTime As SYSTEMTIME) As Long
   
Public Type SYSTEMTIME
  wYear As Integer
  wMonth As Integer
  wDayOfWeek As Integer
  wDay As Integer
  wHour As Integer
  wMinute As Integer
  wSecond As Integer
  wMilliseconds As Integer
End Type

Public Type TIME_ZONE_INFORMATION
    Bias As Long
    StandardName(31) As Integer
    StandardDate As SYSTEMTIME
    StandardBias As Long
    DaylightName(31) As Integer
    DaylightDate As SYSTEMTIME
    DaylightBias As Long
End Type

Public Declare Function getTimeZoneInformation Lib "kernel32" _
    Alias "GetTimeZoneInformation" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Private Declare Function GetTimeZoneInformationAny Lib "kernel32" Alias _
    "GetTimeZoneInformation" (buffer As Any) As Long

Public Function GetTimeDifference() As Long
   Const TIME_ZONE_ID_INVALID& = &HFFFFFFFF
   Const TIME_ZONE_ID_STANDARD& = 1
   Const TIME_ZONE_ID_UNKNOWN& = 0
  
   Const TIME_ZONE_ID_DAYLIGHT& = 2
    'Returns  the time difference between
    'local & GMT time in seconds.
    'If the  result is negative, your time zone
    'lags behind GMT zone.
    'If the  result is positive, your time zone is ahead.
    
    Dim tz As TIME_ZONE_INFORMATION
    Dim retcode As Long

    Dim Difference As Long
    
    'retrieve the time zone information
    retcode = getTimeZoneInformation(tz)
    
    'convert to seconds

    Difference = -tz.Bias * 60
    'cache the result

    GetTimeDifference = Difference
    
    'if we are in daylight  saving time, apply the bias.
    If retcode = TIME_ZONE_ID_DAYLIGHT& Then

        If tz.DaylightDate.wMonth <> 0 Then
            'if tz.DaylightDate.wMonth = 0 then the daylight
            'saving time change doesn't occur
            GetTimeDifference = Difference - tz.DaylightBias * 60
        End If

    End If
    
End Function

Public Function LocalToUtc(ByVal vdtLocal As Date) As Date   
    Dim Differerence As Long
    Differerence = GetTimeDifference()
    LocalToUtc = DateAdd("s", -1 * Differerence, vdtLocal)
End Function

Public Function setSystemClock(datSet As Date) As Boolean
  Dim ST As modGlobals.SYSTEMTIME
  Dim datUTC As Date
  
  datUTC = modGlobals.LocalToUtc(datSet)
  
  With ST
     .wYear = Year(datUTC)
     .wMonth = Month(datUTC)
     .wDay = Day(datUTC)
     .wHour = Hour(datUTC)
     .wMinute = Minute(datUTC)
     .wSecond = Second(datUTC)
  End With
  If SetSystemTime(ST) Then
    setSystemClock = True
  Else
    setSystemClock = False
  End If
End Function

Several years ago I decided to make use of digital code signing certificates to sign the executable files of all my software products and also to sign the installers. As I’ve just had to renew my certificate I thought I’d write about it quickly here. Basically a digital code signing certificate allows you to fingerprint an executable file to assure you that a given file is from a given software publisher. Furthermore, it also checksums a file to prevent the executable file from being modified after it was signed.

Both of these are desirable from a user level because it helps to assure you that a file that is being downloaded is actually from who you expect it to be AND it hasn’t been modified to include something nefarious (such as virii or spyware). As a software publisher both of these things are useful because potential customers are more likely to actually install your software after they’ve downloaded it once it’s been signed correctly. It’s easy to tell if an executable file is digitally signed by the publisher. When you try to run the file after you’ve downloaded it from the internet Microsoft Windows will generally show you a message like this:

Windows Installation Warning Message

If the file is digitally signed then the name of the publisher will appear here. If you want to dig down deeper you can view the file properties of a signed file and you’ll see something like this:

Signed File Properties

I use Comodo as the issuing body for the code signing certificates for my software. Rather than purchase directly from them I use one of their re-sellers, K-Software. The ordering process is painless and once you’ve placed an order Comodo will contact you for some identity documents to prove who you are. This is usually business registration documents but being in Australia I had to provide a bit more information and Comodo rang me as a further verification of my business identity. It should be comforting to the consumer that they go through this sort of process, as Comodo really does want to ensure that they only issue certificates to legitimate businesses.

One tip to anyone wanting to get one of these certificates is to not use Google Chrome or Mozilla Firefox when it comes time to actually download and install your code signing certificate. This is simply because the process within IE8 or better is fully automated and painless. Chrome and Firefox require you to jump through a few extra hoops to do the actual certificate installation and based on past experience it’s just not worth the hassle. Another tip is to ensure that you backup your certificate (which can be done via IE internet properties) somewhere safe so that you can move it to a new PC if required.

Actually signing your executable file and installer is pretty simple if you’re using Windows. I use the Microsoft command line tool signtool.exe that can be used to digitally sign files. This tool is part of the .NET SDK so you’ll need to ensure you’ve got that installed. Actually using signtool.exe is easy, there’s a line in all of my software build scripts that signs both the program executable file and software installer. The line looks something like this:

c:\code signing\signtool.exe" sign /a /t http://timestamp.comodoca.com/authenticode "C:\PathTo\Exe File\ProductName.exe

I spent a few months late last year re-writing one of my products, Timesheets Lite, from the ground up in C# and WinForms. The old version was written in Visual Basic 6 and was working just fine but in the interests of “future-proofing” the product and learning a modern language I decided to re-write it.

The process was fairly smooth apart from a large number of inexplicable (and seemingly pointless) changes to the way that OleDB connections work. This has thrown up a lot of problems that the old VB6 version never experienced and it’s proving to be a frustrating experience getting to the bottom of them. One that was thrown up yesterday was one user who was getting the following error message:

Product: Timesheets Lite NET
Version: 3.3.4.0
Error:Query is too complex.
Source:Microsoft JET Database Engine
Raised by:Void ExecuteCommandTextErrorHandling(System.Data.OleDb.OleDbHResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteScalar()
at timesheetslite.appClass.iExecuteScalarQuery(String sSQL, DateTime[] datParameterValues)

The error was being thrown in a method that checked for employee timesheet activity across a time period and for a (potentially large) number of projects. The large number of projects was the problem as It turns out that there’s an upper limit on the number of AND’s and OR’s that can be used as criteria in an OleDb select statement. Supposedly it’s something around 40. So, I’ve had to re-write the routine to break up the select statements into blocks of 20 projects. It works fine now but you have no idea how annoying it is to see new error messages from a modern programming language when an archaic language like VB6 had no problems with the same thing. Anyway, for those who are interested here’s the code.

            string sSql = string.Empty;
            string sProjectCriteria = string.Empty;
            Int32 iProjectCount = 0;
            DateTime[] parameters = new DateTime[2];
            Int32 iRecords = 0;
            Int32 iCriteriaCount = 0;
            Int32 iCriteriaLimit = 20;

            parameters[0] = this.getStartOfDay(pStart);
            parameters[1] = this.getEndOfDay(pEnd);

            if (projects.Count > 0)
            {
                foreach (Project project in projects)
                {
                    if (iCriteriaCount == 0)
                    {
                        sProjectCriteria = " AND (";
                    }

                    sProjectCriteria += "lngProjectID=" + project.ID.ToString();

                    iProjectCount++;
                    iCriteriaCount++;

                    if (iProjectCount != projects.Count && iCriteriaCount<iCriteriaLimit)
                        sProjectCriteria += " OR ";

                    if (iCriteriaCount == iCriteriaLimit) //break up query into blocks to stop the query too complex error
                    {
                        iCriteriaCount = 0;
                        sProjectCriteria += ") ";
                        sSql = "SELECT count(lngID) as time_records from tblTimes where datDate>=@Parm0 and datDate<=@Parm1 and lngEmployeeID=" + this.ID.ToString() + sProjectCriteria;

                        iRecords = app.iExecuteScalarQuery(sSql, parameters);

                        if (iRecords > 0)
                            return true;

                    }
                }

                sProjectCriteria += ") ";
            }

            return false;

I’m going back to my roots on a small consulting job and hacking together a tracking system in Excel with some VBA forms thrown in for giggles. I’ve come across a redraw bug when you put a ListView control on a MultiPage control. For whatever reason the first redraw of the form displays the ListView at position 0,0 of the MultiPage regardless of where you positioned it in the designer. This ListView moves to the correct location when you either change the current page of the MultiPage or drag the form but clearly this isn’t a great solution. I came up with another solution that seems to have worked pretty well. Just force a re-draw of the ListView whenever the MultiPage is clicked.

Private Sub MultiPage1_Click(ByVal Index As Long)
  '
  'workaround for redraw problem with listview in multipage
  '
  Me.lvwResearchers.Visible = False
  Me.lvwResearchers.Visible = True
End Sub