Archive for the ‘Excel UDF’ tag
Excel: Navigate a range
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.
LIKE operator in Excel
While discussing the limitations of wild card comparisons in the Microsoft Excel’s IF
function, I resolved to share my ISLIKE
function. It acts like the LIKE
operator in SQL. You can’t use the word like as the function name because it’s a reserved word in Visual Basic for Applications (VBA).
The function lets you compare any string to see if it is contained in another string. The default mode is case-insensitive, and the override mode case-sensitive. The optional third element lets you override the default comparison method.
You should remember or note that a number 0
is always evaluated as false, and any other number (negative or positive) is always true. You can put the ISLIKE
function in a macro sheet or an Add-in library. I recently posted instructions on how to create your own Add-in library.
Here is the VBA code for the user defined ISLIKE
function:
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 | Function ISLIKE( inValue As Variant _ , hasValue As Variant _ , Optional caseSensitive As Integer = 0) ' Define a logical return variable and assign a default value. Dim logicReturn As Boolean logicReturn = False If caseSensitive Then ' Finds a case-insensitive value as a component in a larger value. If inValue Like "*" + hasValue + "*" Then logicReturn = True End If Else ' Finds a case-sensitive value as a component in a larger value. If UCase(inValue) Like "*" + UCase(hasValue) + "*" Then logicReturn = True End If End If ' Return the logical value. ISLIKE = logicReturn End Function |
Line numbers 12 and 19 use the "*"
, which is equivalent to the regular expression of "\.*"
. I’ve no idea why they made this choice, and it’s not well explained on Microsoft’s web site covering SmartTags.
You can shorten the program and make it slightly less efficient by performing the case sensitive match twice. The following doesn’t rely on nested block IF
statements. You also must change the third parameter to a Boolean
or the Not
operator won’t work correctly.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | Function ISLIKE( lookupValue As Variant _ , containsValue As Variant _ , Optional caseSensitive As Boolean = 0) ' Define a logical return variable and assign a default value. Dim logicReturn As Boolean logicReturn = False ' Finds a case-insensitive value as a component in a larger value. If caseSensitive And _ lookupValue Like "*" + containsValue + "*" Then logicReturn = True ' Finds a case-sensitive value as a component in a larger value. ElseIf Not caseSensitive And _ UCase(lookupValue) Like "*" + UCase(containsValue) + "*" Then logicReturn = True End If ' Return the logical value. ISLIKE = logicReturn End Function |
Somebody asked me, “How this can be useful?” That meant from their perspective, how is it useful beyond a comparison of cell values. A quick example of that additional utility can be demonstrated from an early post. In that post on replacing a VLOOKUP
function with a UDF, there’s compound logic used to find the occurrence of a substring in a string. The ISLIKE
function can simplify that logic.
Here’s that snippet of the code from that referenced post. The snippet evaluates whether the base string is greater than one character and checks the second character of the string to see if it is a -
(minus sign).
58 | ElseIf Len(LetterGrades(i)) > 1 And Mid(LetterGrades(i), 2, 1) = "-" Then |
You can replace the compound logic with a call to the LIKE
function. It would replace line 58 as follows:
58 | ElseIf ISLIKE(LetterGrades(i),"-") Then |
The logic of the function examines to see if the first parameter contains the second parameter. This is what is meant by like. In the following illustration, you examine if a string contains a substring (or is like a substring). The cell A2
is also assigned a Range name. Range names can increase the readability of your models and functions. All but the last one perform case insensitive comparisons that find whether the substring in cell A2
is found with cell range of the B5:B10
. The B10
formula performs a case sensitive match.
Another logic comparison uses grades to see if a minus sign is found in them. The example compares the cell values in A1
against the and
A2B1
and B2
values. Returning true for one and false for the other cases in column C
of the respective rows.
Here are the calls to the ISLIKE
user defined function. The first one is case-insensitive and the second case-sensitive, but there’s no case sensitivity with a minus sign, -
.
Spreadsheet Functions | |
---|---|
Cell | Formula |
C1 | =ISLIKE(A1,B1) |
C2 | =ISLIKE(A2,B2,TRUE) |
As always, I hope this helps. If you’ve comments or alternative approaches, please let me know.
Excel dynamic ranges
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.
Excel UDF Tutorial
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.
- Displaying Excel’s Developer Tab
There are four steps to make this visible. They are:
- Choose the Office Button. You’ll see the following:
- Click the Excel Options button.
- Click the Popular tab if not highlighted (it’s the default). Inside the right side pane, click the Show Developer tab in the Ribbon check box to enable it, and click the OK button to set it.
- Click the Developer ribbon tab. Click the left most icon to launch the Visual Basic Editor screen.
- Open a Module in the Visual Basic Editor by clicking the Insert menu item and choosing the Module element.
- 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.
I struggled to come up with a simple function to illustrate this and how you debug UDFs. A word to the wise, you can’t use a numeric variable inside a MsgBox
by itself. If you attempt it, Excel will return a #Value!
error in the cell where you call the UDF. You must include a numeric variable as an argument (also known as a call parameter) to the CStr()
function. An example is: Cstr(myVariable)
. You call this sample function by entering:
=monthEnd(cell_reference) |
When you pass a date (actually an integer in the spreadsheet), the function handles it as a string. While no data type is assigned the dateIn
variable in the example, variables without an explicit data type are always of the Variant
data type. The Variant
data type is a master data type and can hold any other type, which makes it like the Object
data type in Java.
This program parses the string, then uses the DateSerial()
function to return it as a number. It’s critical to note that the last line returns the value in the newSerialDate
variable, and that variable must always be the function name. Place a single quote mark before all MsgBox()
function calls after verifying that the function works.
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 |
Aside from the fact that all this parsing isn’t really necessary because the problem is much simpler and cleaner. At least, it becomes so when you understand the breadth of built-in functions in VBA. You can solve the problem by designating the formal parameter as a Double
like the example below.
Function newSerialDate(dateIn As Double) newSerialDate = dateIn End Function |
Alternatively, you can accept a Variant
, which maps to a String
. Then, you convert it to a Date
like this:
Function newSerialDate(dateIn) newSerialDate = DateValue(dateIn) End Function |
- 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.
Spreadsheet Functions | |
---|---|
Cell | Formula |
A2 | =newSerialDate(A1) |
C1 | =VLOOKUP(TYPE(A1),$E$3:$F$7,MATCH("Meaning",$E$2:$F$2,FALSE)) |
C2 | =VLOOKUP(TYPE(A2),$E$3:$F$7,MATCH("Meaning",$E$2:$F$2,FALSE)) |
- 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.
- 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.
- 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 aSampleFunction.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.