What is the proper way to pass a cell as a parameter in a UDF in VBA?

Question

I'm trying to create a UDF which takes the value of a referenced cell, tests that value against a range, and depending on whether it fits within the range, will result in an "X" or an "O". Here's the only way I can get it to work:

Code:

Public Function ASE(cellRef As Variant)

    Dim setpointU As Integer, setpointL As Integer
    Dim enabled As String, disabled As String

    cellValue = Range(cellRef).Value
    setpointL = 50
    setpointU = 100
    enabled = "O"
    disabled = "X"

        If cellValue >= setpointL Or cellValue <= setpontU Then
            ASE = enabled
        Else
            ASE = disabled
        End If

End Function

Function in the Formula Bar: =AsE("D7") //(it automatically makes the s lowercase, for some reason)
Result: X
Other info that may be useful: the value of D7 is 24, which is coming from the cell's function, =VALUE(Imported!B5)

To elaborate, the ONLY WAY I can get it to technically work is when two conditions are met: 1) when I use quotation marks in the function around the referenced cell, and 2) when the parameter cellRef is Variant. If I do not use quotation marks [=ASE(D7) instead of =ASE("D7")], it does not work. If I change cellRef's data type to Range or String, it does not work. It will only return the #VALUE! error.

How am I supposed to format this so that I do not have to use quotations around the referenced cell?

to answer your question about work arounds from your now deleted other Q, [see here](https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) — chris neilsen, May 11 '20 at 18:41
@chrisneilsen Thank you, for finding me and sharing that link. After running into similar questions with the same answer, I deleted it for the sake of redundancy. — Colin B, May 14 '20 at 05:03

score 2 · Accepted Answer · answered May 10 '20 at 07:13

To pass the cell itself (with no quotes):

Public Function ASE(cellRef As Range)

    Dim setpointU As Integer, setpointL As Integer
    Dim enabled As String, disabled As String

    cellValue = cellRef.Value
    setpointL = 50
    setpointU = 100
    enabled = "O"
    disabled = "X"

        If cellValue >= setpointL Or cellValue <= setpointU Then
            ASE = enabled
        Else
            ASE = disabled
        End If

End Function

score 2 · Answer 2 · answered May 10 '20 at 08:12

You are up against two trouble makers and a few problems.

One is a memory your worksheet has of the capitalization of AsE. It's a problem I have come across frequently and I have no idea how to cure it. Even if I give the function another name, save, close Excel, restart, and change the name back, Excel will remember the wrong capitalization. So, what I do is to change the name permanently.

The other is in the ambiguity of cellRef As Variant. As you know, a variant could be anything and Excel, it seems, doesn't make up its mind until it hits cellValue = Range(cellRef).Value. At that moment it needs to be a string and, therefore, embraced by quotation marks. D7 is a range, of course. But then cellValue = Range(cellRef).Valuedoesn't make sense. It should be cellValue = cellRef.Value if cellRef is a range. You left the choice to Excel and, admit it, it did its best under the circumstances.

There is a problem with the missing Option Explicit at the top of your code. It would alert you to the missing declaration of cellValue and the misspelling in the variable name at Or cellValue <= setpontU Then. Open the Tools menu of the VB Editor. Select the Editor tab from the Options menu and check "Require variable declaration". VBA will automatically insert Option Explicit in all code sheets created thereafter. Only then may you safely forget about it.

Finally, please bear in mind that the return of a function also must have a data type. Since you don't declare any the return will be a variant. Obviously, your function returns a string. Yes, Excel and VBA will sort it out. But they could do better with a little cooperation from you. At least you could share what you know. Once you do that your code might look like this.

Function ASE(cellRef As Range) As String

    Dim cellValue As Variant
    Dim upperSetpoint As Integer, lowerSetpoint As Integer

    cellValue = cellRef.Value
    lowerSetpoint = 50
    upperSetpoint = 100

    If cellValue >= lowerSetpoint And cellValue <= upperSetpoint Then
        ASE = "O"                   ' enabled
    Else
        ASE = "X"                   ' disabled
    End If
End Function

Note that the logical error in your condition has also been found. Any value must be either >=SetpointL or <=setpointU. Therefore your function can't return anything but "O". It's different if you apply both conditions as prerequisite using the logical And.

As a closing note, all procedures are "Public" unless they are declared as "Private". What then is the point of declaring a procedure as "Public"? It's much clearer to only mark those which are not "Public".

Trust me, I was wondering about a lot of the things you mentioned as well, but I know the questions need to stay a bit more focused on Stack Overflow, so I stuck with the #1 issue. That being said, I'm really thankful you took the time to give explanations and answer all the "why's" for me, and point out those obvious syntax/logic errors. Thank you, great info! — Colin B, May 10 '20 at 08:46

What is the proper way to pass a cell as a parameter in a UDF in VBA?

2 Answers2