Archive for the ‘User Defined Functions’ Category

HVLookup UDF

The following function can be used to perform a simultaneous Horizontal and Vertical Lookup on a matrix:

Public Function HVLookup(RowLabelValue As Variant, _
ColHeaderValue As Variant, _
objRange As Object, _
Optional vntShowErrBox As Variant) As Variant
'Pass in the values to be matched in matrix column
'header (ColHeaderValue), the matrix row label (RowLabelValue), the
'entire matrix range (objRange), and, optionally, whether or not you
'want to see an error message pop up if not match is found

Dim vntPosHoriz As Variant
Dim vntResult As Variant
Dim strSheet As String

With Application
'Set to volatile so that this function will auto update
'like a native function.

strSheet = objRange.Parent.Name
'Name of the worksheet on which the function is located (only
'needed for error message).

vntPosHoriz = .Match(ColHeaderValue, objRange.Rows(1), 0)
If IsError(vntPosHoriz) Then
If Not IsMissing(vntShowErrBox) Then
MsgBox CStr(ColHeaderValue) & " Does Not Exist in " _
& "Range [Sheet='" & strSheet & "']", _
32, "HVLookup Function Error"
End If
HVLookup = 0
Exit Function
vntResult = .VLookup(RowLabelValue, objRange, vntPosHoriz, False)
End If
End With

'Return result
If IsError(vntResult) Then
HVLookup = 0
HVLookup = vntResult
End If

End Function


Read Full Post »

This User Defined Function is designed to be used with Excel 2003, or earlier, to mimic the IFERROR functionality built in to Excel 2007 or later.

Public Function IFERROR(ToEvaluate As Variant, Default As Variant) As Variant
'Duplicates Excel 2007 "IFERROR" functionality.
If IsArray(ToEvaluate) Then
  IFERROR = IIf(IsError(ToEvaluate(1)), Default, ToEvaluate)
  IFERROR = IIf(IsError(ToEvaluate), Default, ToEvaluate)
End If
End Function 

Read Full Post »