User Defined Functions

nathansizemore · Jan 30, 2012

Hello, I am back for some more help...

I am working on a scheduling Spreadsheet. I have more than 7 conditions, so I cannot do an IF. I have been reading around on how to do one testing String conditions, and have found a reply to a question that I think I can use, problem is I need it do something a bit different...

This is the thing I found:

Code:

Function Grades(Letter As String) As Integer
Select Case Letter
   Case Is = "A+"
       Grades = 15
   Case Is = "A"
       Grades = 14
   Case Is = "A-"
       Grades = 13
   Case Is = "B+"
       Grades = 12
   Case Is = "B"
       Grades = 11
   Case Is = "B-"
       Grades = 10
   Case Is = "C+"
       Grades = 9
   Case Is = "C"
       Grades = 8
   Case Is = "C-"
       Grades = 7
   Case Is = "D+"
       Grades = 6
   Case Is = "D"
       Grades = 5
   Case Is = "D-"
       Grades = 4
   Case Is = "F+"
       Grades = 3
   Case Is = "F"
       Grades = 2
   Case Is = "F-"
       Grades = 1
End Select
End Function

Now, I am sure you Excel gurus know what that will do, so no need to explain. I have a similar case where I think I can use something like this, just unsure of the syntax involved.
Depending on what String is in the check field, I need it to do a calculation, but it needs to pull a number from the cell directly to its left, every time. Not sure on how to accomplish that. This is what I have so far...

Code:

Function Days(name As String) As Double
Select Case Name
	Case Is = "Staci"
		Days = (CELL_DIRECTLY_LEFT/N4)/P14
	Case Is = "Chris"
		Days = (CELL_DIRECTLY_LEFT/N5)/P14
	Case Is = "Mike"
		Days = (CELL_DIRECTLY_LEFT/N6)/P14
....and so on...

This is a screen shot of my file...
<a href="http://www.flickr.com/photos/61552010@N08/6790957731/" title="Sampledata by nathansizemore, on Flickr"><img src="http://farm8.staticflickr.com/7167/6790957731_a503b6f4c7.jpg" width="500" height="281" alt="Sampledata"></a>

The Days cell displays the number of days it takes to complete a task based on the team size. The user defined function determines the variable for each team, but how do I make it pull the value from the Hours? I have the two cells highlighted in the picture for better understanding. I will be using this function at various points in the spreadsheet. The columns always stay the same, but the number of rows between the cells with data vary.

Thanks in advance for any help. I am using Excel 2007.

Norie · Jan 30, 2012

Don't know about the 2nd part but for the first you could probably use a formula like this.

=16-MATCH(A1,{"A+";"A";"A-";"B+";"B";"B-";"C+";"C";"C-";"D+";"D";"D-";"F+";"F";"F-"},0)

Where the grade is in A1.

Jonmo1 · Jan 30, 2012

This will refer to the cell to the left of the cell containing the function call..

Application.Caller.Offset(0, -1)

However, I would recommend to add the desired cell as an argument to the function..

Code:

Function Days(name As String, celltoleft As Range) As Double
Select Case Name
    Case Is = "Staci"
        Days = (celltoleft/N4)/P14

Then if you put the formula in say G2, then write
=DAYS("some string", F2)

Rick Rothstein · Jan 30, 2012

nathansizemore said:
This is the thing I found:
Depending on what String is in the check field, I need it to do a calculation, but it needs to pull a number from the cell directly to its left, every time. Not sure on how to accomplish that. This is what I have so far...

Code:

Function Days(name As String) As Double Select Case Name Case Is = "Staci" Days = (CELL_DIRECTLY_LEFT/N4)/P14 Case Is = "Chris" Days = (CELL_DIRECTLY_LEFT/N5)/P14 Case Is = "Mike" Days = (CELL_DIRECTLY_LEFT/N6)/P14 ....and so on...

Try using Application.Caller to retrieve the cell reference for the cell containing your formula and then offset one column to the left to retrieve the value in the cell immediately to the left of it. Something like this structure should work for you...

Code:

Function Days(name As String) As Double
 
  Dim CellValueOnLeft As Variant
 
  CellValueOnLeft = Application.Caller.Offset(, -1).Value
 
  Select Case name
    Case Is = "Staci"
      Days = (CellValueOnLeft / N4) / P14
    Case Is = "Chris"
      Days = (CellValueOnLeft / N5) / P14
    Case Is = "Mike"
      Days = (CellValueOnLeft / N6) / P14
....and so on...

nathansizemore · Jan 30, 2012

Thanks for all the help guys!

nathansizemore · Jan 30, 2012

Ok, tried this and it just returns #Value

Code:

' Calculates the number of days needed to complete Mechanical Assembly
' Based on the Team assigned to skid
'
'
Function Mechanical_Days(Name As String) As Double
 
    Dim CellValueOnLeft As Variant
 
    CellValueOnLeft = Application.Caller.Offset(, -1).Value
 
    Select Case Name
        Case Is = "Staci"
            Mechanical_Days = (CellValueOnLeft / N4) / P15
        Case Is = "Chris"
            Mechanical_Days = (CellValueOnLeft / N5) / P15
        Case Is = "Mike"
            Mechanical_Days = (CellValueOnLeft / N6) / P15
        Case Is = "Darren"
            Mechanical_Days = (CellValueOnLeft / N7) / P15
        Case Is = "Oaks"
            Mechanical_Days = (CellValueOnLeft / N8) / P15
        Case Is = "Ryan"
            Mechanical_Days = (CellValueOnLeft / N9) / P15
        Case Is = "Gayheart"
            Mechanical_Days = (CellValueOnLeft / N10) / P15
        Case Is = "Staci + Ryan"
            Mechanical_Days = (CellValueOnLeft / N11) / P15
        Case Is = "Chris + Gayheart"
            Mechanical_Days = (CellValueOnLeft / N12) / P15
    End Select
End Function

The Strings can only be selected from a list, so I know they are exact matches. Not sure why it will not work? Thoughts?
It says a value used in this function is of the wrong data type.

Rick Rothstein · Jan 30, 2012

nathansizemore said:

Ok, tried this and it just returns #Value

Code:

' Calculates the number of days needed to complete Mechanical Assembly
' Based on the Team assigned to skid
'
'
Function Mechanical_Days(Name As String) As Double
 
    Dim CellValueOnLeft As Variant
 
    CellValueOnLeft = Application.Caller.Offset(, -1).Value
 
    Select Case Name
        Case Is = "Staci"
            Mechanical_Days = (CellValueOnLeft / N4) / P15
        Case Is = "Chris"
            Mechanical_Days = (CellValueOnLeft / N5) / P15
        Case Is = "Mike"
            Mechanical_Days = (CellValueOnLeft / N6) / P15
        Case Is = "Darren"
            Mechanical_Days = (CellValueOnLeft / N7) / P15
        Case Is = "Oaks"
            Mechanical_Days = (CellValueOnLeft / N8) / P15
        Case Is = "Ryan"
            Mechanical_Days = (CellValueOnLeft / N9) / P15
        Case Is = "Gayheart"
            Mechanical_Days = (CellValueOnLeft / N10) / P15
        Case Is = "Staci + Ryan"
            Mechanical_Days = (CellValueOnLeft / N11) / P15
        Case Is = "Chris + Gayheart"
            Mechanical_Days = (CellValueOnLeft / N12) / P15
    End Select
End Function

The Strings can only be selected from a list, so I know they are exact matches. Not sure why it will not work? Thoughts?
It says a value used in this function is of the wrong data type.

Just to double-check, what cells are your =Mechanical_Days(..) formula in and what cells are the values you are retrieving in? And while it seems that VBA does not mind it, I would not use Name as a variable or argument name... it is a keyword in the VB programming language for the Name..As statement.

JoeMo · Jan 30, 2012

If N4, P15, etc are cells, try putting them in square braces like [N4], [P15], etc within the Function.

Rick Rothstein · Jan 30, 2012

JoeMo said:
If N4, P15, etc are cells, try putting them in square braces like [N4], [P15], etc within the Function.

...or alternately (my preference

), wrap them in Range property calls so than N4 would, as but one example, be...

Range("N4").Value

tusharm · Jan 30, 2012

It is extremely bad design to create a user defined function that references cells that Excel is unaware of. I am surprised that people with experience are recommending such an approach. Follow the recommended design guidelines and pass the ranges of interest as arguments to the function; *do not* refer to application.caller.offset() or [n4] or any such references.

The most likely scenario is that next you will find that changing those cells does not cause your UDF to recalculate. Then, you will be told to add a 'Application.Volatile' statement to your UDF. Then, you will find that the UDF recalculates even when no change is made to any related cells, with a potentially negative performance impact.

I would go a step further. There has to be a pattern to what you want to do. That pattern can, in most likelihood, be described in a table. You can then use a VLOOKUP or INDEX(MATCH()) to do your work using Excel-native formulas.

nathansizemore said:
Hello, I am back for some more help...

I am working on a scheduling Spreadsheet. I have more than 7 conditions, so I cannot do an IF. I have been reading around on how to do one testing String conditions, and have found a reply to a question that I think I can use, problem is I need it do something a bit different...

This is the thing I found:

Code:

Function Grades(Letter As String) As Integer Select Case Letter Case Is = "A+" Grades = 15 Case Is = "A" Grades = 14 Case Is = "A-" Grades = 13 Case Is = "B+" Grades = 12 Case Is = "B" Grades = 11 Case Is = "B-" Grades = 10 Case Is = "C+" Grades = 9 Case Is = "C" Grades = 8 Case Is = "C-" Grades = 7 Case Is = "D+" Grades = 6 Case Is = "D" Grades = 5 Case Is = "D-" Grades = 4 Case Is = "F+" Grades = 3 Case Is = "F" Grades = 2 Case Is = "F-" Grades = 1 End Select End Function

Now, I am sure you Excel gurus know what that will do, so no need to explain. I have a similar case where I think I can use something like this, just unsure of the syntax involved.
Depending on what String is in the check field, I need it to do a calculation, but it needs to pull a number from the cell directly to its left, every time. Not sure on how to accomplish that. This is what I have so far...

Code:

Function Days(name As String) As Double Select Case Name Case Is = "Staci" Days = (CELL_DIRECTLY_LEFT/N4)/P14 Case Is = "Chris" Days = (CELL_DIRECTLY_LEFT/N5)/P14 Case Is = "Mike" Days = (CELL_DIRECTLY_LEFT/N6)/P14 ....and so on...

This is a screen shot of my file...
<a href="http://www.flickr.com/photos/61552010@N08/6790957731/" title="Sampledata by nathansizemore, on Flickr"><img src="http://farm8.staticflickr.com/7167/6790957731_a503b6f4c7.jpg" width="500" height="281" alt="Sampledata"></a>

The Days cell displays the number of days it takes to complete a task based on the team size. The user defined function determines the variable for each team, but how do I make it pull the value from the Hours? I have the two cells highlighted in the picture for better understanding. I will be using this function at various points in the spreadsheet. The columns always stay the same, but the number of rows between the cells with data vary.

Thanks in advance for any help. I am using Excel 2007.

User Defined Functions

nathansizemore

New Member

Excel Facts

Norie

Well-known Member

Jonmo1

MrExcel MVP

Rick Rothstein

MrExcel MVP

nathansizemore

New Member

nathansizemore

New Member

Rick Rothstein

MrExcel MVP

JoeMo

MrExcel MVP

Rick Rothstein

MrExcel MVP

tusharm

MrExcel MVP

Similar threads

Forum statistics

Share this page

User Defined Functions

New Member

Excel Facts

Well-known Member

MrExcel MVP

MrExcel MVP

New Member

New Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock