User Defined Functions

[nathansizemore]

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:

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...

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.

 

[mikerickson]

For the names situation you could use worksheet formulas like

=SUMIF(B:B, "Stacy", A:A) to get the value of "cell to the left".

What are N4, P15 in the OP code?

 

[nathansizemore]

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.

I understand why it would be considered a bad practice, but I couldn't think of another way to do it without buying a VBA book and learning the entire language, to understand all options available to me. I just do not have the time because after work, I have started development on an Android project, and learning Android is taking all my free time at the moment.
I have limited time to search Google and various forums to find quick solutions to ideas/models I want to put into Excel. I started using Excel maybe two months ago, and am learning new things about it everyday, but still very newbish when it comes to how powerful this software can be.
That being said, I cannot seem to find a solution to adding a cell as an argument to this function.
Here is the idea breakdown:
I have X number of teams in my shop. All of different sizes and capabilities. Mainly made up of two parts, assembly and welding. Each team has a different number of each type. I want to take a 'job', assign it to 'X' team. Based on the assembly and welding hours budgeted to the job, spit me out the number of days it will take to complete that job.
Example:
Day is 10 working hours. Job 1 has 10 welding hours. Team 'X' has 2 welders, Team 'Y' has 1 welder.
I have a list that indicates how many assemblers and welders they have in their team. Which is the [NX] or [MX] in the previous code.
Now, how could I possibly tell the function which one to use without manually changing the value based on what team is selected.
Because if Team 'X' is going to do the Job 1, it will take .5 days to complete, but if Team 'Y' does Job 1, it will take 1 day.
Based on scheduling conflicts, I might need to change what team is doing what, in order to get the work done on time. So, using your model, if I changed the team, I would ultimately have to go and change what cell the hours are divided by. Which, I do not want to change two items, every time I change one. I want to be able to select which team is doing what, and everything else is changed.
I have more than 7 options, so nested if statements are out of the question, and the Case thing was the best solution I came across searching Google. Now, I am sure my search keywords are limited, because of my limited knowledge of Excel, but as is, everything works and changes properly.

' 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(0, -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


' Calculates the number of days needed to complete Pipe Welding
' Based on the Team assigned the skid
'
'
Function Pipe_Days(Name As String) As Double
 
    Dim CellValueOnLeft As Variant
 
    CellValueOnLeft = Application.Caller.Offset(, -1).Value
 
    Select Case Name
        Case Is = "Staci"
            Pipe_Days = (CellValueOnLeft / [M4]) / [P15]
        Case Is = "Chris"
            Pipe_Days = (CellValueOnLeft / [M5]) / [P15]
        Case Is = "Mike"
            Pipe_Days = (CellValueOnLeft / [M6]) / [P15]
        Case Is = "Darren"
            Pipe_Days = (CellValueOnLeft / [M7]) / [P15]
        Case Is = "Oaks"
            Pipe_Days = (CellValueOnLeft / [M8]) / [P15]
        Case Is = "Ryan"
            Pipe_Days = (CellValueOnLeft / [M9]) / [P15]
        Case Is = "Gayheart"
            Pipe_Days = (CellValueOnLeft / [M10]) / [P15]
        Case Is = "Staci + Ryan"
            Pipe_Days = (CellValueOnLeft / [M11]) / [P15]
        Case Is = "Chris + Gayheart"
            Pipe_Days = (CellValueOnLeft / [M12]) / [P15]
    End Select
End Function


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

Why exactly is this a bad practice? Other than the obvious fact that if I used this model in another sheet, The appropriate number of available man hours and productivity rate would have to be in the M,N, and P columns...?

 

[nathansizemore]

What are N4, P15 in the OP code?

See the screen shot of my sheet in the OP

 

[mikerickson]

Then it looks like
=SUMIF(B:B, "Stacy", A:A)/VLOOKUP("Stacy",L4:N1000,3,False)/$P$14

would return the value you want from the second UDF

 

[nathansizemore]

Then it looks like
=SUMIF(B:B, "Stacy", A:A)/VLOOKUP("Stacy",L4:N1000,3,False)/$P$14

would return the value you want from the second UDF

What if I wanted to to change, say "Chris" as the team to do this job. I would then have to re-write the function with "Chris" in place of "Staci", and change the respective cell values, yes?

 

[mikerickson]

You could replace both instances of "Stacy" with a cell reference to a cell that contained the string "Stacy"