Issue with Using Contents of Cell Relative to Active Cell as a Condition in a Custom If Function

Question

I am attempting to create my first user defined function in the Excel Visual Basic Editor. I am using Excel 2011 on a Macbook Pro 2011 with OS X El Capitan Version 10.11.1

I am working on creating a workbook that will use my functions to figure results for a barrel race in a 4 Division format. The divisions are as follows:

The first division or "1D" are the fastest times up to 0.499 seconds off of the fastest time.

The second division or "2D" are the next fastest times between 0.5 and 0.999 seconds off of the fastest time.

The third division or "3D" times are between 1 second and 1.499 seconds off of the fastest time.

Lastly, the fourth division or "4D" times are 1.5 or more seconds off of the fastest time.

This is the current code I have to figure these divisions:

Function DIV(pVal)
If pVal = "Z" Then
DIV = ""
ElseIf pVal = 999 Then
DIV = "DQ"
ElseIf pVal > 100 Then
DIV = "NT"
ElseIf pVal < (Range("B2") + 0.5) Then
DIV = "X1D"
ElseIf ActiveCell.Offset(-1, 0) = "X1D" And pVal < (Range("B2") + 1) Then
DIV = "2D"
ElseIf pVal < (Range("B2") + 1) Then
DIV = "X2D"
ElseIf pVal < (Range("B2") + 1.5) Then
DIV = "X3D"
ElseIf pVal > (Range("B2") + 1.499) Then
DIV = "X4D"
Else
DIV = "Incorrect"
End If
End Function

Now, I want only the first time in each division to result in "2D", "3D" & "4D" and the rest of the times in each division to result in "X1D", "X2D", "X3D" & "X4D" because I will be applying a conditional formatting so that anything beginning with "X" will appear in white text and therefore be invisible when looking at it, however, I will still be able to use it for future functions when I move on to the placings in each division.

So for example, I'm currently working on the 2D and I want all times that are between 0.5 and 0.999 seconds off of the fastest time to return a result of "X2D" except for the first one which would mean that the cell above it would say "X1D".

Everything in the code above is working except for the ElseIf that should be returning the "2D". It is coming back as false and moving on to the next ElseIf where it then returns "X2D".

I have removed parts of the code one at a time and tested them. I have narrowed the problem down to the ActiveCell.Offset section.

I've searched all over the internet for the past three days trying to figure out what I'm missing and I just can't find it. The following are all the ways I've tried typing it based on what I've found online and so far nothing has worked.

ElseIf ActiveCell.Offset(rowOffset:=-1, columnOffset:=0) = "X1D" And pVal < (Range("B2") + 1) Then DIV = "2D"

ElseIf pVal < (Range("B2") + 1) And ActiveCell.Offset(-1, 0) = "X1D" Then DIV = "2D"

ElseIf pVal < (Range("B2") + 1) And ActiveCell.Offset(-1, 0).Select = "X1D" Then DIV = "2D"

ElseIf pVal < (Range("B2") + 1) And ActiveCell.Offset(-1, 0).Range = "X1D" Then DIV = "2D"

ElseIf pVal < (Range("B2") + 1) And ActiveCell.Offset(-1, 0).Activate = "X1D" Then DIV = "2D"

I have tried one other way of coding it based on what someone suggested on another site and that was this :

Function DIV(pVal)

If pVal = "Z" Then DIV = ""
If pVal = 999 Then DIV = "DQ"
If pVal > 100 Then DIV = "NT"
If pVal < (Range("B2") + 0.5) Then DIV = "X1D"
If ActiveCell.Offset(-1, 0) = "X1D" And pVal < (Range("B2") + 1) Then DIV = "2D"
If pVal < 100 Then DIV = "Correct"
If DIV = "" Then DIV = "Incorrect"

End Function

When I tried this one it made the entire function stop working and all results came back as "Correct".

I am at a complete loss as to what I should do to fix this and if anyone has any suggestions I would be very appreciative.

Thank you!

EDIT** This function will only be used in all of Column C except for C1 & C2 because C1 is a header cell and C2 will always be 1D.

Specific example of how the function will be used:

Let's say there are 12 times, 3 in each division.

The fast time which will always be in B2 is 15.000.

The time in B5 is 15.5 which is the first 2D time.

The function is in C5 and should first ask if B5 is = "Z", if true it should leave the cell blank, if not true it should then ask if B5 is = 999, if true it should return "DQ", if not true it should then ask if B5 is > 100, if true it should return "NT", if not true it should then ask if B5 is < B2 + 0.5, if true it should return "X1D", if not true it should then ask if B5 is < B2 + 0.999 AND C4 = "X1D", if true it should return "2D", if not true it should then ask if B5 < B2 + 0.999, if true it should return "X2D", if not true it should go on to ask about the next divisions. If I can figure out how to do this with the 2nd division I can learn from that and be able to finish the other divisions and hopefully create new functions for the other things I want the workbook to do.

I hope this helps!

You should pass all three values directly to the function from the cell - `pval`, the value in the cell above and the value in B2. Otherwise you will need to make your function volatile. Using `ActiveCell` is **almost never** a good idea in a UDF since you have no idea what cell might be active when the function calculates. — Rory, May 09 '16 at 08:02
The ActiveCell property does not belong in a UDF. See [UDF returns the same value everywhere](http://stackoverflow.com/questions/35910683/udf-returns-the-same-value-everywhere/35956748#35956748). — , May 09 '16 at 08:17
Thank you for your replies. Like I said, I'm new at creating a UDF. I've never even seen the VBA before this weekend so I'm not sure what you mean when you say I should pass all three values directly to the function? — MJJB99, May 09 '16 at 08:31

SierraOscar · Accepted Answer · 2016-05-09T10:14:07.883

0

You need to re-design your function's logic, but also when designing a UDF pass all variables to it rather than assigning them within the function. This is what I think your function should look like:

Public Function DIV(pVal As Double, testRng As Range, testX1D As Range) As String

    Dim ret As String

    If testX1D.value = "X1D" Then
        If pVal < testRng.value + 1 Then
            DIV = "2D"
            Exit Function
        End If
    End If

    Select Case CVar(pVal)
        Case "Z":                           ret = vbNullString
        Case 999:                           ret = "DQ"
        Case Is > 100:                      ret = "NT"
        Case Is < (testRng.value + 0.5):    ret = "X1D"
        Case Is < (testRng.value + 1):      ret = "X2D"
        Case Is < (testRng.value + 1.5):    ret = "X3D"
        Case Is > (testRng.value + 1.499):  ret = "X4D"
        Case Else:                          ret = "Incorrect"
    End Select

    DIV = ret

End Function

It's also worth noting that you have to specify the order of your test very carefully when comparing values in this way. For example if the value of pVal is 999 in this scenario:

Case 999:                           ret = "DQ"
Case Is > 100:                      ret = "NT"

It will return "DQ" because that was the first test, however if these were the other way around:

Case Is > 100:                      ret = "NT"
Case 999:                           ret = "DQ"

You would get "NT" because pVal is also greater than 100. Just something to bear in mind...

To use this UDF

example, formula entered in C5 where pVal is value in B5

=DIV(B5,C4,$B$2)

(note the absolute reference for B2)

edited May 09 '16 at 10:14

answered May 09 '16 at 08:30

SierraOscar

17,507
6
40
68

I tried using the function you created and now all of the results are coming back as "#VALUE!". I was hoping if it worked I could use it as a starting point to learn more about creating a UDF. Also as far as the side note, if the value is equal to testrng.value +1.499 then it should come back as X3D because it is < 1.5 right? If it makes any difference the reason I used B2 as the cell to compare them all to is because I know that it will ALWAYS be the fastest time. And this function will only be used in this specific workbook on this specific sheet. – MJJB99 May 09 '16 at 08:43
How are you calling the function? – SierraOscar May 09 '16 at 08:45
I don't mean to sound like an idiot and I'm sorry if it's frustrating, I just really don't know anything about creating a UDF besides what I've learned the past three days from trying to create this one. So, I'm not sure what you mean when you say how am I calling the function. I was initially just using nested if functions along with and & or functions to do all of this which was working great until I got to the more complicated part of the process which includes figuring placings and payout and then my functions got too long. – MJJB99 May 09 '16 at 08:51
See my edit above - I've included how you should be calling the function, does this make sense? – SierraOscar May 09 '16 at 08:52
Okay I understand what you're asking now. And I was doing that wrong. I changed it and it's still saying the #VALUE! but I think we're getting closer to the answer. My next question is why is B1 being used? It is just a header cell. I'm going to add a specific example of how this function will be used in my original question. I think it may help – MJJB99 May 09 '16 at 09:02
I've used B1 as an example, this is supposed to negate the need for the `Offset` argument - it should point to whatever cell you were 'offsetting' to originally – SierraOscar May 09 '16 at 09:05
I think I get what you're saying, however, I was trying to offset to the cell above the function. I updated my question, gave an example and said exactly what I'm trying to get it to do. I hope it helps! – MJJB99 May 09 '16 at 09:22
So if you're putting the formula in C4 and `pVal` is in B4 it should be `=DIV(B4,$B$2,C3)` and copy it down. If you're putting the formula in C5 then it would be `=DIV(B5,$B$2,C4)` etc... does that make sense? – SierraOscar May 09 '16 at 09:36
Yes I got it now and put those in. That all makes sense now. However, it's still not working. It's now only checking to see if C4 says "X1D" and if it does it returns 2D. It's basically not asking anything else. And none of the select case part of it is working. Is there a reason that I couldn't take out the if parts of it and just use the select case? – MJJB99 May 09 '16 at 09:48
`It's now only checking to see if C4 says "X1D" and if it does it returns 2D` That's not the case - step through the code (use F8) - if C4 says "X1D" then it does a further test to see if pVal < (B2 + 1). Only if this condition is _also_ met will it return "2D". What is the value of pVal when you get this value? – SierraOscar May 09 '16 at 09:52
@MJJB99 Sorry, I see what you mean about the select case not working - that was my bad, I've updated the code to use the `CVar()` method which fixes that. Also I realise I had the example use in the wrong order. See updated answer and how to use – SierraOscar May 09 '16 at 10:14
This worked perfect. And I really understand the coding part a lot better now! Thank you so much! – MJJB99 May 09 '16 at 10:35

Issue with Using Contents of Cell Relative to Active Cell as a Condition in a Custom If Function

1 Answers1