Passing Named Range into User Defined Function

[mcm91201]

Passing Named Range into User Defined Function


MrExcel.com | Excel Resources | Excel Seminars | Excel Products mcm91201


Depending on time of day and computer I am sitting in front of I am using:


WinXP Pro SP2 with Excel 2003
Win7 Pro SP2 Excel 2007
Win7 Pro SP2 Excel 2010 on PC
Win7 Pro SP2 Excel 2010 on Mac Mini running Boot Camp
OSX Excel for Mac 2011


I have only tried this on Win7 Pro SP2 Excel 2007 but need it to work on all.


I enter the values 0, 1, 2 ... 89, 90 in cells A1 to A91


I select A1:A91 and name the range 'angle'


I create a user defined function:


Public function sindeg(value As Double) as Double
sindeg = sin(worksheetfunction.radians(value))
end


I want 'value' for the function in a cell to be replaced by the corresponding value in the same row (or column) in the named range 'angle'. For example (using commas as column separators). This works for Excel functions like sin, cos, radians, etc.


********** Worksheet Contents **********


A1 = 00, B1 = sin(radians(0)), C1 = sin(radians(A1)), D1 = sin(radians(angle)), E1 = sindeg(0), F1 = sindeg(A1), G1 = sindeg(angle)


A2 = 01, B2 = sin(radians(1)), C2 = sin(radians(A2)), D2 = sin(radians(angle)), E2 = sindeg(1), F2 = sindeg(A2), G2 = sindeg(angle)


A3 = 02, B3 = sin(radians(2)), C3 = sin(radians(A3)), D3 = sin(radians(angle)), E3 = sindeg(2), F3 = sindeg(A3), G3 = sindeg(angle)


......


A91 = 90, B91 = sin(radians(90)), C91 = sin(radians(A91)), D91 = sin(radians(angle)), E91 = sindeg(90), F91 = sindeg(A91), G91 = sindeg(angle)


**********


Column A = input. Columns B, C, D, E and F all calculate the same value by row. Column G fails with a #VALUE. In row 1 the value of angle[1] = 0 therefore column D = C = B = sin(0) = 0. In row 2 angle[2] = 1 therefore B = C = D = 0.017452


How can I get the user defined function sindeg(value) in column G to accept the named range variable 'angle' like the Excel function radians(value) accepted it in column D?


This functionality should work horizontally as well as vertically. For example enter 'angle' A1 to CM1 then have sindeg(angle) filled from A2 to CM2. It should also work in the case where the named range 'angle' is a single cell.


I am sure that this is a simple variable type definition problem in my user defined function: should the input variable be defined as type Range? Or something more exotic?


The brute force approach is to have the function determine the input value by passing in the named range, working out dimensions, calculating offset between the cell the function is in and top (left) of named range, then counting down (right) to pick the correct value. However I cannot see adding all that code to EVERY function. Occam's Razor says there has to be an easier way since Excel built in functions seem to do it readily.


This is a problem that I have been wanting to solve for awhile. I know there is something simple I am missing, I hope I now know where to ask.

 

[Jerry Sullivan]

Hi and Welcome to the Board,

I don't work with those geometric functions much, and I wouldn't have guessed that they handle vector input parameters like that. Thanks for sharing that.

Here's a UDF that emulates that behavior....

Public Function sindeg(vInput As Variant) As Variant
    Dim rThisCell As Range, rValue As Range

      
    If TypeName(vInput) = "Range" Then
        Set rThisCell = Application.Caller
        If Not Intersect(rThisCell, vInput) Is Nothing Then
            sindeg = CVErr(vInput)
            Exit Function
        End If
        If vInput.Count = 1 Then Set rValue = vInput
    End If

    
    '---If vInput is multi-cell range
    If rValue Is Nothing Then
        '--Test if Vector
        If vInput.Rows.Count > 1 And vInput.Columns.Count > 1 Then
            sindeg = CVErr(xlErrValue)
            Exit Function
        End If
        '--find intersection of calling cell and vector, if any
        If vInput.Rows.Count > 1 Then
            Set rValue = Intersect(vInput, rThisCell.EntireRow)
        Else
            Set rValue = Intersect(vInput, rThisCell.EntireColumn)
        End If
    End If

    
    '---At this point just handle value or single cell
    Select Case True
        Case rValue Is Nothing: sindeg = CVErr(xlErrValue)
        Case IsError(rValue): sindeg = CVErr(rValue)
        Case IsNumeric(rValue): sindeg = Sin(Application.Radians(rValue))
        Case Else: sindeg = CVErr(xlErrValue)
    End Select
End Function

 

[mcm91201]

Jerry,


Many thanks. I am stuck on an iPad now but will try test later today. I believe most, if not all, MS-Excel functions work this way. It works very well for the case where you want to build up a grid.




exx = 0 2 4 6 8 10 12 14 .....
why
=
0 excel_function(exx,why)
2
4


Your code will be an excellent template. Thank you again. But ... this is the part that bothers me (not a criticism of your work but a comment on MS-Excel) ... to use this as a template means that I have to go into every function and edit 6 or 7 lines. This is where I think that there is some trick that performs this so that I only have to write the actual calculation line of the function and not the entire wrapper. Again very much appreciated, I will start using this immediately. Thank you

Mike

 

[mcm91201]

Jerry,

Thank you - works great. I very much like that the error messages match what Excel proper does. Can I trouble you with some follow up questions for my education?

rThisCell = Application.Caller

Is Application.Caller giving you the address(row,column) of the cell in which the UDF is sitting?

The function then checks to see if that cell lines up with anything in the range vInput(rows,columns)?

*Edit - Aha! (figured this out after I submitted original reply and am now editing). The first check is for recursion. You make sure that the function is not calling itself. Brilliant. I still want to know what Application.Caller actually gives (is it an object? what colour? how big? what does it look like?)*

Lower down you check the row/column count to make sure that you are not trying to pull rValue out of a 2D array. Should the 2D check be done before the first check? I believe the answer is no (just trying to understand programmer's logic) - the first check just says "nothing lines up - don't waste time" and bails to speed things up.

*Edit - Answer is definitely no because first check is a recursion check which bails to speed things up. *

Finally, error statements.

Case rValue Is Nothing: sindeg = CVErr(xlErrValue)

Does this display the #VALUE in the cell when there is something wrong with input data value or type? Assume yes.

Is xlErrValue a variable defined elsewhere or is it just a keyword meaning display #VALUE?

Case IsError(rValue): sindeg = CVErr(rValue)

This is saying that if the input is pointing to a cell that has an error in it, it will just echo that error in the sindeg cell?

Thank you again.

Stop by the Blind Lady Ale House in SD and enjoy a cold one for me.

Mike

 

[Jerry Sullivan]

Hi Mike,

Those are good observations. It looks like you answered most of your follow up questions in the process of asking them.

While starting a response to your few open questions, I realized the function didn't handle non-Range inputs as I'd originally intended. (I had that working in my first pass at the code and lost it in my attempt to streamline).

Here is a revised version. Hopefully I didn't introduce any other gremlins, but please test well before using in the real world.

Public Function sindeg(vInput As Variant) As Variant
    Dim rThisCell As Range, rValue As Range
    Dim vAdjusted As Variant

    On Error GoTo ErrorValue
    If TypeName(vInput) = "Range" Then
        Set rThisCell = Application.Caller
        If Not Intersect(rThisCell, vInput) Is Nothing Then
            sindeg = CVErr(vInput)
            Exit Function
        End If
        If vInput.Count = 1 Then
            vAdjusted = vInput.Text
        Else '---vInput is multi-cell range- test if Vector
            If vInput.Rows.Count > 1 And vInput.Columns.Count > 1 Then GoTo ErrorValue
            '--find intersection of calling cell and vector, if any
            If vInput.Rows.Count > 1 Then
                Set rValue = Intersect(vInput, rThisCell.EntireRow)
            Else
                Set rValue = Intersect(vInput, rThisCell.EntireColumn)
            End If
            If rValue Is Nothing Then
                GoTo ErrorValue
            Else
                vAdjusted = rValue.Text
            End If
        End If
    Else
        vAdjusted = vInput
    End If
    '---At this point just handle resulting vAdjusted
    Select Case True
        Case IsNumeric(vAdjusted): sindeg = Sin(Application.Radians(vAdjusted))
        Case IsError(vAdjusted): sindeg = CVErr(vAdjusted)
        Case Else: sindeg = CVErr(xlErrValue)
    End Select
    Exit Function
ErrorValue:
    sindeg = CVErr(xlErrValue)
End Function

I'm off to dinner. I'll post a separate follow up post with responses to your questions a bit later.

 

[Jerry Sullivan]

Mike,

Here's a few comments in response to your questions....

I still want to know what Application.Caller actually gives (is it an object? what colour? how big? what does it look like?)*

Application.Caller returns the Object that called the function. In your normal use of this UDF that will be a Range Object; however a Button, Shape or other Object could also call a UDF.

Case rValue Is Nothing: sindeg = CVErr(xlErrValue)

Does this display the #VALUE in the cell when there is something wrong with input data value or type? Assume yes.

That check will handle the scenario that the Parameter is a Range Vector that doesn't align with the calling cell. An example from your original post description would be a call from D100.

Is xlErrValue a variable defined elsewhere or is it just a keyword meaning display #VALUE?

It's a constant defined in Excel VBA. You can find a list of the error constants in VBA help under: XlCVError Enumeration

 

[mcm91201]

Jerry,

Many thanks. Maybe you can teach an old dog new tricks. Very much appreciate your help. Hope you don't mind if I put your name in the "Very Helpful People" section of my address book.

Mike

 

[Jerry Sullivan]

Jerry,

Many thanks. Maybe you can teach an old dog new tricks. Very much appreciate your help. Hope you don't mind if I put your name in the "Very Helpful People" section of my address book.

Mike

Mike, You taught me a new trick about the way some functions handle Vector Parameters!

Hope to see more of you on this Forum.

 

[Jerry Sullivan]

Mike, I noticed that I hadn't addressed this question of yours in my earlier replies.

But ... this is the part that bothers me (not a criticism of your work but a comment on MS-Excel) ... to use this as a template means that I have to go into every function and edit 6 or 7 lines. This is where I think that there is some trick that performs this so that I only have to write the actual calculation line of the function and not the entire wrapper.

You're correct. One option is to take the parts of the code that would be repeated in many of your custom functions and break that out as function that could be called by each of those functions.

Here's one way that modular code concept might be applied to your example.

Place this functionality in a separate Function....

Public Function getVal(vInput As Variant, rCell As Range) As Variant
'--returns value resulting from evaluating vInput in  relation to rCell.
'--to emulate behavior of some worksheet functions like Radians()
    Dim rValue As Range
    Dim vAdjusted As Variant

    On Error GoTo ErrorValue
    If TypeName(vInput) = "Range" Then
        If Not Intersect(rCell, vInput) Is Nothing Then
            getVal = CVErr(vInput)
            Exit Function
        End If
        If vInput.Count = 1 Then
            vAdjusted = vInput.Text
        Else '---vInput is multi-cell range- test if Vector
            If vInput.Rows.Count > 1 And vInput.Columns.Count > 1 Then GoTo ErrorValue
            '--find intersection of calling cell and vector, if any
            If vInput.Rows.Count > 1 Then
                Set rValue = Intersect(vInput, rCell.EntireRow)
            Else
                Set rValue = Intersect(vInput, rCell.EntireColumn)
            End If
            If rValue Is Nothing Then
                GoTo ErrorValue
            Else
                getVal = rValue.Text
                Exit Function
            End If
        End If
    Else
        getVal = vInput
    End If

    Exit Function
ErrorValue:
    getVal = CVErr(xlErrValue)
End Function

The getVal Function can then be called from many different "wrapper" functions with minimal editing.

Public Function sindeg(vInput As Variant) As Variant
    Dim vAdjusted As Variant, vReturn As Variant
    vAdjusted = getVal(vInput, Application.Caller)

    Select Case True
        Case IsNumeric(vAdjusted): vReturn = [COLOR="#0000CD"]Sin(Application.Radians(vAdjusted))[/COLOR]
        Case IsError(vAdjusted): vReturn = CVErr(vAdjusted)
        Case Else: vReturn = CVErr(xlErrValue)
    End Select
    [COLOR="#0000CD"]sindeg [/COLOR]= vReturn
End Function

Public Function cosdeg(vInput As Variant) As Variant
    Dim vAdjusted As Variant, vReturn As Variant
    vAdjusted = getVal(vInput, Application.Caller)

    Select Case True
        Case IsNumeric(vAdjusted): vReturn = [COLOR="#0000CD"]Cos(Application.Radians(vAdjusted))[/COLOR]
        Case IsError(vAdjusted): vReturn = CVErr(vAdjusted)
        Case Else: vReturn = CVErr(xlErrValue)
    End Select
    [COLOR="#0000CD"]cosdeg [/COLOR]= vReturn
End Function

As an added bonus, the helper function getVal can also be used as a stand-alone WorksheetFunction.
In your original example setup, this formula will return 90 (the value at the intersection between named range angle and D91's Row).

=getVal(angle,D91)

 

[psdennis]

Further question on how to use Named Ranges as Parameters for functions please.
Have written the following code and it works perfectly fine when entered in the same worksheet as the selected Named Range. Unfortunately it falls apart when the selected Named Range is in another worksheet (even though all ranges that I'm using as parameters are "Workbook Specific").
In this particular line:

<code>Set FullRange = NamedRange</code>

Have been experimenting for some time trying to create a reference that would work from any worksheet but unfortunately with no luck.
Any help will be much appreciated!
The concept is to declare a Named Range as parameter. In turn, the function finds this Named Range and creates a new range from a part of it (see SpecificRange). Finally it performs certain calculations with the data from this new range. All of these work fine but only in the same worksheet... Here is the code:

<code>Function myResult(NamedRange As Range, Vessel As String, FromDate As Date, ToDate As Date)

'declare variables in addition to the function parameters declared above
Dim SpecificRange As Range
Dim FullRange As Range
Dim Result As Double
Dim i As Byte

'find the row within the declared "NamedRange" range which contains information for the declared "Vessel"
Set FullRange = NamedRange
Set SpecificRange = Range(FullRange.Find(Vessel, , xlValues, xlWhole).Address, FullRange.Find(Vessel, , xlValues, xlWhole).Offset(0, FullRange.Columns.Count - 1).Address)

i = 1
Result = 0

For i = 1 To FullRange.Columns.Count - 2
    If FullRange(2, i) = "Date" Then
        With WorksheetFunction
            Result = Result + .Max(0, .Min(ToDate, SpecificRange(1, i + 2).Value) - .Max(FromDate, SpecificRange(1, i).Value)) * SpecificRange(1, i + 1).Value
        End With
    End If
Next

myResult = Result

End Function</code>