MacLochlainns Weblog

Somebody posted a comment on my Excel UDF Tutorial, and on reflection it appears he’d like to navigate a range in VBA. While I don’t see a purpose in that because it duplicates either the VLOOKUP or HLOOKUP function (depending on whether you’re navigating a column or row).

Anyway, here’s how you’d do it in VBA:

Function Finding(lookupWord As String, lookupRange As Range, lookupColumn As Integer) As Integer
 
  ' Define variables.
  Dim lookupIndex As Integer
  Dim lookupMax As Integer
  Dim returnValue As Integer
 
  ' Assign values to variables.
  lookupIndex = 1
  lookupMax = lookupRange.Rows.Count
  returnValue = 1
 
  ' Print starting and ending index values.
  MsgBox ("[" + CStr(lookupIndex) + "][" + CStr(lookupMax) + "]")
 
  ' Loop until found, but exit when not found.
  Do Until lookupRange.Cells(lookupIndex, 1).Value = lookupWord
    ' Print diagnostic value to determine what's evaluated.
    MsgBox ("[" + CStr(lookupIndex) + "] [" + CStr(lookupRange.Cells(lookupIndex, 1).Value) + "]")
 
    ' Fail safe exit when no matching value is found.
    If lookupIndex = (lookupMax + 1) Then
      ' Assign a 0 (zero) as a false return flag value.
      returnValue = 0
      Exit Do
    End If
 
    ' Increment counting index value.
    lookupIndex = lookupIndex + 1
 
  Loop
 
  'Return a 1 when the value is found and zero when it isn't.
  Finding = returnValue
End Function

I only chose a DO UNTIL loop because that was in the comment. It would be more appropriate to, from my perspective, use a FOR range loop, like this:

  For lookupIndex = 1 To lookupMax
    ' Loop contents.
  Next lookupIndex

Hope this helps.

Debbie asked a question about why a comparison wasn’t working in the Microsoft Excel SUMIF function when the initial array value was empty. This post answers that question.

The SUMIF function takes a lookup range, which anticipates a one column with a lookup value and another column with a value. The SUMIFS function works along the same line but accepts multiple lookups against a series of lookup list columns.

This shows a simple model of the SUMIF function:

The formula in cell G3 is:

=SUMIF(B$3:B$5,"<"&$E$3,C$3:C$5)

If you left off the $ symbol and made the comparison "<"&E3, you could add the sums for any number of values that are less than a value in the same row (or counting index with named ranges). You could also make the inequality symbol a parameter with this modified formula:

=SUMIF(B$3:B$5,$E$5&$E$3,C$3:C$5)

Now, your analysis model lets you choose whether you want to look for the sum greater than or less than a specific number in the lookup range. Named ranges compare index values between the lookup range and comparison range values, which allows them to be in different rows or spreadsheets. The only problem is that the label has an inverted inequality sign. You can fix that by constantly editing the description or by simply replace the text of the label with a dynamic text string, like this:

=CONCATENATE("Comparison """,$E$5,""" Value")

After applying the fix to the label, it looks like:

When the lookup column contains all nulls, the same formula returns a zero, as shown in the illustration.

You'll also get an error flag, unless you unchecked the Flag formulas that refer to empty cells in the error checking options. The IFERROR function can't be used to suppress this type of error.

Why doesn't the error return a null value? That's because an equality operator compares values in the array against a static or dynamic lookup value. While equality operators don't implicit cast values for comparison, they also don't compare null values. Only the ISBLANK function lets you find an empty cell and it can't be used inside a SUMIF function call.

Somebody was trying to query Oracle via Microsoft Excel 2007, and didn’t have an Oracle ODBC Data Source that enable them to choose Oracle. I’d referred them to this post on adding MySQL’s ODBC driver to Windows 7 but that didn’t help enough. Posting this screen shot may not have helped either.

It appears I assumed too, like everyone would know that it’s on Windows 7 when Oracle 11g (or another Oracle database) is installed locally. The person who posed the question doesn’t have Oracle installed locally and can’t install it.

For those who don’t have an Oracle instance running locally, you can download the Oracle Data Access Components for Windows from the Oracle web site. It’s in the driver sections, as shown in the screen capture below:

This driver contains the necessary OLE DB and ODBC drivers, as you can see in this screen shot.

Once you’ve downloaded it, you can return to this post where you set up an Excel query against a remote Oracle database. You should take note that Microsoft’s future direction adopts Oracle ODBC, like the approach they’ve chosen with MySQL’s ODBC driver. As always, I hope this helps.

Quite some time ago, summer 2008, I wrote a post about how you could embed an image in a cell comment. It was for the then current version of the product – Excel 2007. Here’s a User-Defined Function (UDF) in VBA to perform that trick that works in Excel 2010/2011. A comment on that older post fed my disappointment that Excel 2011 doesn’t even support the navigation but it does perform it with VBA. This includes the UDF to add an image and an ordinary Excel Macro to remove the image when you want to preserve the text.

Let’s assume you have a list of image files in a directory and that you’ve entered their fully qualified or absolute path values column B of your worksheet. Now you want to load them as comment images in Column A and insert a value in each column A cell that describes the comment image.

Unfortunately, as I’ve explained before you can’t assign the image as a property of the cell (or more precisely, I’ve never found a way to do it). If this is wrong, please post the magic here for all to enjoy without a fee or registration. 😉

The following UDF takes a string value to describe the image and a cell reference that holds a string value that holds an absolute file name, which is a logical drive letter (C:\), a file path, and file name.

Function InsertCommentImage(title As String, cellAddress As Range)
  Dim commentBox As comment
  ' Clear any comments before attempting to add them.
  Application.ActiveCell.ClearComments
  ' Define the comment as a local variable and assign the file name from the
  ' cellAddress input parameter to the comment of a cell.
  Set commentBox = Application.ActiveCell.AddComment
  With commentBox
    .Text Text:=""
    With .Shape
      .Fill.UserPicture (cellAddress.Value)
      .ScaleHeight 3#, msoFalse, msoScaleFormTopLeft
      .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft
    End With
    ' Set the visible to True when you always want the image displayed, and
    ' to False when you want it displayed only when you click on the cell.
    .Visible = False
  End With
  InsertCommentImage = title
End Function

A fully qualified address for the cellAddress parameter on a PC would look like this in let’s say cell B1:

C:\Data\Images\WizardChess.png

While it would be like this for the cellAddress parameter on a Mac OS X installation in cell B1:

Macintosh HD:Users:mclaughlinm:Desktop:WizardChess.png

You would call this from a cell like this when the text is provided as a string and fully qualified file name is in cell B1 of a worksheet named ImageSource:

=InsertCommentImage("Wizard Chess",B1)

Alternatively, you rewrite InsertCommentImage() as follows, which takes a string for the cell value and a string for the absolute file name:

Function InsertCommentImage(title As String, absoluteFileName As String)
  Dim commentBox As Comment
  ' Clear any comments before attempting to add them.
  Application.ActiveCell.ClearComments
  ' Define the comment as a local variable and assign the file name from the
  ' cellAddress input parameter to the comment of a cell.
  Set commentBox = Application.ActiveCell.AddComment
  With commentBox
    .Text Text:=""
    With .Shape
      .Fill.UserPicture (absoluteFileName)
      .ScaleHeight 3#, msoFalse, msoScaleFormTopLeft
      .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft
    End With
    ' Set the visible to True when you always want the image displayed, and
    ' to False when you want it displayed only when you click on the cell.
    .Visible = False
  End With
  InsertCommentImage = title
End Function

With the change of the second parameter, you would call the InsertCommentImage() function with two strings, like:

=InsertCommentImage("Wizard Chess","C:\Data\Images\WizardChess.png")

Here’s how it would look if you’d put the formula in cell A1:

This is a simple Excel macro for which you could create a button. You run it when you want to delete only the image comment from a cell. The macro works by highlighting the cell and running it. Naturally, you could wrap it in another Excel macro to navigate through the list and clean up a bunch of comment images at one time.

Sub RemoveComment()
  Application.ActiveCell.ClearComments
End Sub

If you want to allow the macro to detach all comments for a range, you would rewrite it this way:

Sub RemoveComment()
  Application.Selection.ClearComments
End Sub

As always, I hope this helps and furthers sharing information.

The original blog post with the images was causing a page loading delay, so I’ve moved the post to a page of it’s own. This link send you to the original post with how you setup MySQL Connector/ODBC driver, add a new data source in Windows 7, and a new data import source in Excel 2007. As with other “how-to” posts, it includes screen shots to clear up any ambiguity. I apologize for any inconvenience caused by moving the content.

If you want instructions for using Excel to access an Oracle database, check this older post. I’m working on documentation for a native Mac OS X and Excel 2011 solution and will update this when it’s ready.

As always, I hope this helps those trying to sort through how this works. Naturally, a Visual Basic for Applications (VBA) solution is a better alternative once you’ve set up the data source. This is also on my calendar for documentation.

A second Excel 2011 question in as many days, February is off and running. The question posed: “How do you create a data validation list without referring to a range of cells?”

You can create a data validation list without referring to a cell range. There’s a small trick to it. You enter the list of values as comma delimited strings, but you don’t enclose them in double quotes. Excel reads a set of literal text strings as if they’re a list of cell values, which is why you don’t provide the double quotes that you use in every function.

The desired list was either TRUE or FALSE, and the required behavior was that they function like Boolean variables. Fortunately, that’s straightforward when you remember that the case insensitive text strings TRUE and FALSE are Boolean values.

You create a data validation list by navigating to the Data Tab (Windows Excel 2010 it’s a Ribbon), and choose Validate. You’ll see the following list. Click on Data Validation… in the list, as shown below.

The selection launches the Data Validation dialog box. Choose List from the Allow poplist. Then, enter TRUE,FALSE in the Source entry field, like this:

Now click OK and you have a poplist. Hope this answer the question for others too.

I had an interesting reaction when I told my students they had to create a dynamic Excel model that required them to enter the months of the year without AutoFill feature. They were stumped. They couldn’t find a function to perform it for them.

It’s really quite easy, the EDATE function lets you find it directly. Enter any day in the current month as the first parameter and one as the number of months to add as the second parameter to the function. Voilà, you have a function to add a month to any date. The only time it returns the first of the next month is when the source date was the first day of the current month.

Here’s the solution when cell B1 contains January 14, 2011 and you want cell C1 calculated as the fourteenth day of February in the same year or February 14, 2011:

=EDATE(B1,1)

Here’s the solution when cell B1 contains January 14, 2011 and you want cell C1 calculated as the fourteenth day of December in the prior year or December 14, 2010:

=EDATE(B1,-1)

You can also use the EOMONTH function to find the first or last day of the month. It lets you find it the first day of the next month regardless of the start date. All you need is a simple trick.

Enter any day in the current month as the first parameter and zero as the number of months to add as the second parameter to the function, and then add one.

Here’s the solution when cell B1 contains any day in January and you want cell C1 calculated as the last day of January in the same year (rounding up).

=EOMONTH(B1,0)

Here’s the solution when cell B1 contains any day in January and you want cell C1 calculated as the first day of January in the same year (rounding down).

=EOMONTH(B1,-1)+1

A subsequent question asked how to calculate the 15th day of the current month, and here’s the formula:

=EOMONTH("10-Jul-2012",-1)+15

Hope this helps them and others looking for the trick.

While the VLOOKUP and HLOOKUP functions are powerful tools in Excel, they limit their search to the first column or row of a range. This post shows you how to leverage the COUNTIFS function to perform an inclusive between search against the first two columns or rows of a range.

Moreover, the VLOOKUP function searches the first column of a range and looks for an approximate or exact match. The approximate match search throws an exception when the lookup key’s value precedes the first item in the range, and picks up the row immediately less than the lookup key. This type of search requires two condition. The data must be presorted into an ascending order for the sort column, the range must be contiguous, and two matching keys shouldn’t exist. That means that each search column or row cell points to the row of interest for any search key greater than it and less than the next. An exact match search finds the row that matches the lookup key and throws an error when there isn’t an exact match.

Neither of these allow for range searches between non-contiguous sets, like the one below. The date ranges where a value should be found are from the 16th of a month to the end of a month rather than the range between the 16th of one month to the 15th of the next. While this could be done by structuring a row with zeros the gap periods, a more effective solution is possible by using the COUNTIFS. At least, this is true from Excel 2007 forward.

The solution to this problem starts with recognizing how a COUNTIFS works. The COUNTIF provides the opportunity to compare a range of values against a single value, and the COUNTIFS allows multiple comparisons of values against ranges of values. The COUNTIFS function returns the number of matches that meet all conditions. Therefore, when a value is found in only one of the ranges the COUNTIFS function returns a 1, and when a value is found n times it returns n as a number.

The formula in cell F2 checks for the number of times the value in F1 exists:

=COUNTIFS($A$1:$A$13,"<="&F1,$B$1:$B$13,">="&F1)

If you evaluate when the foregoing function returns 1 before performing a VLOOKUP function, you can guarantee a match within a non-contiguous range of values. That formula is:

=IF(COUNTIFS($A$1:$A$13,"<="&F1,$B$1:$B$13,">="&F1)=1,VLOOKUP(F1,A2:C13,3),0)

Hope this helps some folks, as always …

While working on a VBA write-up for some documentation on Excel 2007/2010, I ran into some interesting parameter validation rules for Excel User-Defined Functions (UDFs). I found that optional values are suppressed when you pass a cell reference that points to an empty cell.

Let’s say you develop a simple test function like the following. You may expect that it returns the number passed as a call parameter or the default value of the opt variable, 22, but it doesn’t.

1
2
3
4

Function OptionalOverride(Optional opt As Variant = 22)
  ' Return the unfiltered call parameter.
  OptionalOverride = opt
End Function

A call to an OptionalOverride function like this works when the cell reference points to a value, but fails when it points to an empty cell. It returns a value of 0 with a numeric data type, as verified by the built-in TYPE function.

=OptionalOverride(D1)

This behavior means you always need to check for empty cell references and reassigns them a value inside an if-block. That is if you really want the default value to apply in all cases. The function includes the explicit call parameter assignment in the modified function.

1
2
3
4
5
6
7
8

Function OptionalOverride(Optional opt As Variant = 22)
  ' Explicit assignments required when a cell reference points to an empty cell.
  If IsEmpty(opt) Then
    opt = 21
  End If
  ' Return the unfiltered call parameter unless empty then return 21.
  OptionalOverride = opt
End Function

The modified function returns (a) the call parameter value, (b) 21 when the call parameter points to (references) an empty cell, (c) 22 when you exclude the variable from the call parameter list, or (d) any string value found in the call parameter. The return of a string value is clearly not the desired behavior.

You must modify the if-block by checking whether the call parameter is some data type other than a number before assigning a default value. The following demonstrates the final parameter validating function.

1
2
3
4
5
6
7
8

Function OptionalOverride(Optional opt As Variant = 22)
  ' Explicit assignments required when a cell reference points to an empty cell.
  If IsEmpty(opt) Or Not IsNumeric(opt) Then
    opt = 21
  End If
  ' Return the filtered call parameter.
  OptionalOverride = opt
End Function

Hope this helps a few folks trying to avoid that ugly #VALUE! error returning from your UDFs.

Microsoft Excel has many Visual Basic for Application (VBA) features that are seemingly not well understood. One of these features is passing a range value into a VBA procedure (a Sub) or function. The following example demonstrates how to pass a dynamic range to a local multiple dimensional array, and process the uploaded data in a VBA function.

My sample spreadsheet looks like the screen shot below. The formula call is in cell A11 and the text of the formula is in cell B11.

I left a debug MsgBox() call that demonstrates how you size a range. This shows the range above based on zero-based numbering, which means 5 rows are reported as 4 rows because 0 contains a row, and 3 columns are reported as 2 columns for the same reason. If the dialog looks strange to a Windows user, that’s because it’s one generated on a Mac OS X running Excel 2011. 🙂

I kept this as simple as possible to demonstrate the how to do this. Unfortunately, feedback required adding more extensive comments and making it a bit more bulletproof on concepts. The variable names were chosen to help read the syntax. Two loops are used to demonstrate (a) how you assign the range values to a multidimensional array, and (b) how you read the array values in your VBA code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

Function dynamicArray(lookupValue As String, table As Range)
 
  ' Define the row starting cell, Row n maps to the first row where the range is found in the spreadsheet.
  ' Row 1 is the first row and 1,048,576 is the last possible row.
  Dim rowStart As Integer
  Dim rowSize As Integer
 
  ' Define the column starting cell, Column A maps to 1, Column B maps to 2, et cetera.
  ' Column A is the first column and XFD is the last possible column.
  Dim columnStart As Integer
  Dim columnSize As Integer
 
  ' Create a dynamic multiple dimension array without any physical size.
  Dim multidimensionArray As Variant
 
  ' Define and declare a local returnValue variable.
  Dim returnValue As Boolean
  returnValue = False
 
  ' Assign the starting row and column values, and the length of values.
  ' Since you need to add the row and column to the starting grid coordinates, you need to use 0-based numbering,
  ' which means you subtract one from the length.
  rowStart = table.Row
  rowSize = table.Rows.Count - 1
  columnStart = table.Column
  columnSize = table.Columns.Count - 1
 
  ' This demonstrates that the range starts in the row and column, and
  ' the length and width of the multiple dimension array.
  ' ----------------------------------------------------------------------
  ' Insert single quotes for the next two lines to suppress testing the program with variables.
  MsgBox ("(RowStart [" + CStr(rowStart) + "], (ColStart [" + CStr(columnStart) + "]) " + _
    "(RowSize [" + CStr(rowSize) + "] ColSize [" + CStr(columnSize) + "])")
  ' ----------------------------------------------------------------------
 
  ' Redimension the arrays maximum size, rows first, columns second.
  ReDim multidimensionArray(rowSize, columnSize)
 
  ' Read through the range and assign it to a local and dynamically sized array variable.
  ' An important note to those unfamilar with the Cells function, it works on the active worksheet and uses two
  ' parameters, the absolute row and column number or relative row and column numbers. In this sample, the
  ' easiest solution is to use absolute row an dolumn numbers.
  For i = 0 To rowSize
    For j = 0 To columnSize
      multidimensionArray(i, j) = CStr(Cells(rowStart + i, columnStart + j))
    Next j
  Next i
 
  ' Read through the local variable range and view the content set.
  For i = 0 To rowSize
    For j = 0 To columnSize
      ' Check if the lookupValue has been found and return true.
      If lookupValue = CStr(multidimensionArray(i, j)) Then
        returnValue = True
        Exit For
      End If
    Next j
  Next i
 
  ' Return a Boolean value: true when found and false when not found.
  dynamicArray = returnValue
End Function

You would call it with syntax like that below. The range can be any valid range value in Excel, which is any two cell references separated by a colon.

=dynamicArray("LookupString",A1:G5)

As always, I hope this helps others looking for a way to perform this task natively in Microsoft Excel. As an addendum, I thought the absence of a code example for VB on the MSDN web page was quite interesting.