VBA IIf() as a ternary conditional expression (sometimes)

VBA doesn't have a ternary conditional expression (e.g. C-style ?:), which leads to a long-winded conditional statement like this when you just want assign a value to the same variable based on a condition:

If <condition> Then
  x = 1
Else
  x = 2
End If

In C-style languages, you would just write x = <condition> ? 1 : 2. You can mimic the ?: ternary operator using VBA's IIf(expr, truepart, falsepart) function, x = IIf(<condition>, 1, 2), which reduces the verbosity of VBA code.

There are some limitations, though. Unlike the C-style ?: operator which only evaluates either the truepart or falsepart depending on the condition, IIf() is a function call so all its arguments are evaluated and this evaluation strategy can lead to side-effects or cause an unexpected error message.

Side-effects: x = IIf(condition, function1, function2) would cause both function1 and function2 to be called, so this statement would be slower than the equivalent If condition Then function1 Else function2 End If, where either function1 or function2 are called.

Unexpected error message: You may encounter an unexpected error message if you rely on the value of a variable in truepart or falsepart. For example, the following Excel VBA script searches for a value in a worksheet range B1:B8 and prints its address if it is found, otherwise it prints "Not found".

Sub TestFind()
  Dim wks As Worksheet
  Set wks = Sheet1
  Dim rng As Range
  Set rng = wks.[B1:B8].Find(What:="z")
  Debug.Print IIf(Not rng Is Nothing, "Found " & rng.Address, "Not found")
End Sub

If z is in the range, the script would execute without any errors. On the other hand, if it is not, you may see this unhelpful error: Run-time error '91': Object variable or With block variable not set. The reason is that rng is Nothing, so evaluating the second argument, "Found " & rng.Address, causes an error.

To sum up, you can use IIf() as a exact substitute for ?: if both truepart and falsepart can always be evaluated (e.g. literals, constants or variables).

Optimising VBA for Excel 2003: Avoid EntireRow.Insert

While optimising an Excel 2003 VBA program to write thousands of rows of data, I noticed that Excel's EntireRow.Insert consumed most of the program's processing time. To quantify the time, here's a simple test that just inserts rows into a workbook ...

Option Explicit

Sub Insert10kRows()
  Dim wks As Worksheet
  Set wks = Sheets("Sheet1")
  Dim intI As Integer
  For intI = 1 To 10000
    wks.Range("A1").EntireRow.Insert
    wks.Cells(1, 1).Value = "Test"
  Next intI
End Sub

Sub TestInsert()
  Dim douStart As Double
  Dim i As Integer
  For i = 1 To 6
    douStart = Timer
    Insert10kRows
    Debug.Print i & "0000:" & Timer - douStart
  Next i
End Sub

... and the results:

Rows Inserted Time
10000 7.703125
20000 11.125
30000 14.75
40000 18.9375
50000 23.40625
60000 28.140625

On the other hand, if you just write rows and advance a row counter, like this ...

Sub Write10kRows()
  Dim wks As Worksheet
  Set wks = Sheets("Sheet1")
  Dim intI As Integer
  For intI = 1 To 10000
    wks.Cells(intI, 1).Value = "Test"
  Next intI
End Sub

Sub TestWrite()
  Dim douStart As Double
  Dim i As Integer
  For i = 1 To 6
    douStart = Timer
    Write10kRows
    Debug.Print i & "0000:" & Timer - douStart
  Next i
End Sub

... you get much better performance (low numbers are better).

Rows Written Time
10000 0.6875
20000 0.6875
30000 0.703125
40000 0.6875
50000 0.6875
60000 0.6875

Another observation is that the more rows exist in a worksheet, the longer Excel takes to insert rows (hence the nested loops in the sample code). I can imagine that Excel is spending a lot of time just shuttling data from one block of memory to another to make room for new rows.

For my program, the solution was to refactor the code to replace row insertion with a row counter and to write data to the appropriate row. It's a little more complicated but the program runs at least 10x faster.

SharpDevelop MSB3105 Error for duplicate form

When you create a new Windows Application project in SharpDevelop 3.1, the IDE creates a default form called 'MainForm'. If you add an existing form with the same name to this project then build this project, you may get this error: The item "MainForm.Designer.cs" was specified more than once in the "Sources" parameter. Duplicate items are not supported by the "Sources" parameter. (MSB3105).

SharpDevelop displays a line number (though the line number is a furphy) but not the file with the error. It turns out that when you add the form, the IDE appends another <Compile Include="MainForm.Designer.cs"> entry in the .csproj file. The solution is to delete the duplicate entry. Note that if you use Visual Studio, there is no duplicate entry in the .csproj file.

Local data files in Visual C# Express 2008

While learning data binding for Windows.Forms, I noticed that changes made to data in the DataGridView weren't written back into the local SQL Server database file when the form was closing, even after calling TableAdapter.Update(). Turns out that Visual C# Express 2008 (and VS 2008 in general) copies your local data file each time you build your project to the output folder. The original version of the database file is only modified if you change the schema or edit data using VS. This also means that if you make changes to the data while programming, then change the database schema in VS, the changes you have made in the database file in the output folder would be overwritten.

See Also

• Local Data Overview
• How to: Manage Local Data Files in Your Project

Microsoft Word MailMerge Run-time error '5922'

When a MS-Access 2003 VBA script tried to open MS-Word and run the mail merge function, VBA displayed this error message:

Run-time error '5922':
Word was unable to open the data source

The problem was tracked to this statement:

    Dim objWordApp As Object
    Set objWordApp = CreateObject("Word.Application")
    With objWordApp
      ...
      .ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, SQLStatement:=sQuery, SubType:=wdMergeSubTypeWord2000

There's plenty of different solutions to this problem on the web. In this case, the problem was due to the second argument, the SQL query, sQuery, which had an error.

I spent some time browsing the web to investigate the run-time error, when it would have been more effective to first check the arguments in the subroutine call. Live and learn.

Visual Studio 2008 Express Data Source Limitations

Visual Studio Express 2008 for C# or VB only allows you to choose a limited number of data sources in the IDE. Open the Choose Data Source dialog using menu item Tools / Connect to Database, and in the Data Source list, there are only three items:

• Microsoft Access Database File
• Microsoft SQL Server Compact 3.5
• Microsoft SQL Server Database File

According to VS 2005 express: ".NET Framework Data Provider for ODBC" does not show up in Choose/Change Data Source dialog :(:

The data designers in the Express editions of Visual Studio only allow you to connect to SQL Server Express and Access (Jet) databases. I've passed your feedback along to the people who own these components in the hopes of getting this limitation clearly documented. As Ralph noted, you can still use the various .NET Data Providers at run-time. David Sceppa
ADO.NET Program Manager
Microsoft

I checked VisualStudio2008-ProductComparison-v1.08.pdf from Visual Studio 2008 Product Comparison Guide and there's no specific mention of this limitation for VS 2008 Express editions, other than Data Sources window displays the data sources in your project for creating data-bound controls.

Of course, I can work around this limitation programmatically but the learning value of VS Express editions falls when I can't match text and images from tutorials with what I see on screen.

Visual Basic short-circuit evaluation of expressions

Visual Basic seems to be the only language that evaluates all the operands of AND and OR operators instead of implementing short-circuit evaluation. In all other programming languages, such as C# or Java, expressions are evaluated from left to right, and the evaluation of expressions in statements exp1 AND exp2 and exp3 OR exp4 stop when exp1 = false or exp3 = true, respectively. This difference does surprise you if you come to VB from another programming language and you first write a conditional statement that relies on short-circuit evaluation, like this: If s IsNot Nothing And s.Length > 0 Then …

Two new keywords, AndAlso and OrElse were introduced in VB.Net to provide similar semantics to other languages supported by .Net. Working in more than one programming language at a time, I would have preferred some sort of backward compatibility compiler Option rather than adding new keywords to a language that already has too many.