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.

I had a chance (30 minutes) to play with Excel 2011 on the Mac OS X today. I was shocked to discover that Excel 2011 didn’t appear to support User-Defined Functions (UDFs) like Excel 2010 for Windows. My understanding was that this release would be one where it implemented Visual Basic for Applications (VBA) like Windows. Initially I thought it didn’t but I bought my own copy, did a full install with Visual Basic, and it appears that Microsoft has delivered. Oops, my bad for assuming the machine I played on originally had a solid installation. It appears to have had only a standard installation.

Watch out because UDFs fail with a #NAME! error on a standard install of Excel 2011. While they’re found in the Insert Function dialog in both cases, they only appear to work with a full installation. The downside for Insert Function is that, like Excel 2008, it has no quick poplist to narrow the function choices to groups. We have the big list once more.

Here are my test functions:

Function hello()
  hello = "Hello World!"
End Function
 
Function hellowhom(nameIn As Variant)
  hellowhom = "Hello " + CStr(nameIn) + "!"
End Function

I think I found the trick to get Excel 2011 recognize and run User-Defined Functions. Make sure you do a custom installation and check Visual Basic for Application. Anyway, you can test these functions like that shown below. Column A contains the text of the formulas (a preceding single quote does that), and column B contains actual function calls.

Enabling the Developer ribbon took a few moments when I got my own copy. I figured that updating this was better than making a new post on the blog and linking them. It’s a three step process.

1. Navigate to the Excel menu item and select Preferences…, as shown below.

2. In the Excel Preferences shown below, click the Ribbon icon.

3. In the Show or hide tabs, or drag them into the order you prefer: box shown below, enable the Developer checkbox.

It’s awesome, now accountants and economists can switch to Mac OS X without having to host a virtual machine with Microsoft Excel.

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.

Microsoft Excel supports macros but it also supports library functions, known as User Defined Functions (UDF). Library functions are less risky than macros because they must return a value to a cell. This is a quick tutorial, mostly for my students, but as usual for anybody who’s interested.

Microsoft Excel User Defined Functions (UDFs) are different than standard VBA macros. They’re behavior is restricted. You can’t access other cells in a workbook, and may only return a value (also known as an expression) to the cell that uses the formula. That having been said, they can dramatically hide the complexity of mega-formulas and remove them from the editing control of users.

Here are the steps to configure your Excel 2007 environment to work with Visual Basic for Applications (VBA) and UDFs. They show you how to display the developer ribbon, open a module for editing, save VBA code into a library (*.xlam file), and add the library file as an “Add-in” library to your Excel 2007 installation. The name of the library will be the same as the Workbook where you created it.

1. Displaying Excel’s Developer Tab

2. Click the Developer ribbon tab. Click the left most icon to launch the Visual Basic Editor screen.

3. Open a Module in the Visual Basic Editor by clicking the Insert menu item and choosing the Module element.

4. Copy the following function definition into the open Module. The name of the UDF will be the case-sensistive name of the function in the VBA module. That means you’ll now have a newSerialDate function in your list of functions when you click the insert function button.

=monthEnd(cell_reference)

Public Function newSerialDate(dateIn)
 
  ' Define local variables.
  Dim day As String
  Dim month As String
  Dim year As String
  Dim startPosition As Integer
  Dim endPosition As Integer
  Dim length As Integer
 
  ' Initialize local variables and parse the month from the left.
  startPosition = 1
  endPosition = InStr(startPosition, dateIn, "/")
  month = Left(dateIn, endPosition - 1)
 
  ' This is a debugging tool to track variables during execution.
  MsgBox ("Month [" + month + "]")
 
  ' Shift the start position and parse the day from the middle.
  startPosition = endPosition + 1
  endPosition = InStr(startPosition, dateIn, "/")
  day = Mid(dateIn, startPosition, endPosition - startPosition)
 
  ' This is a debugging tool to track variables during execution.
  MsgBox ("Day [" + day + "]")
 
  ' Get the remainder of the string.
  startPosition = endPosition
  length = Len(dateIn) - startPosition
  year = Right(dateIn, length)
 
  ' This is a debugging tool to track variables during execution.
  MsgBox ("Year [" + year + "]")
 
  ' Return a value from a User Defined Function (UDF) by using
  ' the function name as the return variable.
  newSerialDate = DateSerial(year, month, day)
 
End Function

Function newSerialDate(dateIn As Double)
  newSerialDate = dateIn
End Function

Function newSerialDate(dateIn)
  newSerialDate = DateValue(dateIn)
End Function

5. Create the following spreadsheet, the formula values are noted below the screen shot. You should be able to copy and past them into the spreadsheet. After you’ve created the spreadsheet, entering a new date in cell A1 causes the UDF to run. When the UDF runs “as-is”, you’ll see message dialog boxes that show values set during runtime.

6. You can now save this as an Add In library but comment out those debug MsgBox() function calls. Click the Office Button and click SaveAs in the menu, then accept Excel Workbook initially. When you get to the SaveAs dialog, choose Excel Add-In as the file type. Below is a screen capture of the drop down selection box.

7. Open a new Excel Workbook. Click the Excel Options button. Click the Add-Ins tab. Inside the right side pane, make sure the Manage drop down says Excel Add-ins before you click the Go button.

8. Check the Samplefunction check box as shown below. Samplefunction is the name of the Workbook that contains the module, and it is saved as an *.xlam file. Click the OK button to add the library. You’ve now created, and added an Add-In library to your new spreadsheet. It will create a SampleFunction.xlam file in the users directory.

The file is found by default in:

C:\Users\UserName\AppData\Roaming\Microsoft\AddIns

Ranjit asked how you could call a UDF from inside a module. In the answer noted below, I show how to do it with the Unary function:

Function Unary(number As Integer)
  Unary = number + 1
End Function

Then, enter the following ordinary Increment macro:

Sub Increment()
  ActiveCell.FormulaR1C1 = Unary(ActiveCell.Value())
End Sub

Enter a number in a cell, then navigate to Tools -> Macro -> Macros… and choose the increment macro, which increments the value previously in the cell by calling the Unary function.

As always, I hope this is helpful to a few folks.