VBA #value! error in VBA function

Question

so I am attempting to write a function in VBA which will read the value of a cell (CE4) in another spreadsheet and if that value is equal to "Denied", then it will read the value of CE5 and on and on until it reaches a cell in column CE where the value is "Approved", at which point it will output the value of another cell in the same row (in column B). The idea is that I will input the cell references for the starting cells into the formula and if the initial value for CE is "Denied" it will do the process I described above, otherwise it will output the value for the corresponding cell in column B. I have the following code:

Function transfer(ApplicationCell As String, ConditionCell As String) As String

Dim i As Integer

i = 1
With Worksheets("Application Report")
    If .Range(ConditionCell).Value = "Denied" Then
        Do While .Range(ConditionCell).Value = "Denied"
            .Range(ConditionCell).Value = .Range(ConditionCell).Offset(i, 0).Value
            .Range(ApplicationCell).Value = .Range(ApplicationCell).Offset(i, 0).Value
            i = 1 + 1
        Loop
    End If
    transfer = .Range(ApplicationCell).Value
End With

End Function

The initial value for ApplicationCell is B4 and the initial value for ConditionCell is CE4. However, whenever I attempt to input the values into the function, I get #VALUE!. I know I'm doing something wrong but I am new to VBA so I am unsure of where I am going wrong. Any help would be appreciated!

I assume you are calling the function from the sheet. [You shouldn't](http://stackoverflow.com/a/3622544/11683). — GSerg, Jun 25 '16 at 19:20
You have two objects with conflicting names: a function `transfer` and a variable `transfer` — noumenal, Jun 25 '16 at 19:26
Perhaps you could locate the error better if you simplify: `Range r1 = Worksheets("Application Report").Range(ConditionCell)` and then `r1.value` etc. — noumenal, Jun 25 '16 at 19:30
@noumenal The returning variable of a function is the function's name. So, there is not conflict here. — Ralph, Jun 25 '16 at 19:36
Under normal circumstances a UDF cannot change the value of another cell when called as a function from a worksheet (as noted before by @GSerg). Yet, you could bypass that if you really need to:: http://stackoverflow.com/questions/8520732/i-dont-want-my-excel-add-in-to-return-an-array-instead-i-need-a-udf-to-change/8711582#8711582 — Ralph, Jun 25 '16 at 19:40
@Ralph There are [easier ways](http://stackoverflow.com/q/23433096/11683). — GSerg, Jun 25 '16 at 20:17
As pointed out by @noumenal, you most likely are entering the cell address as a range object, and not as a string. Since you have declared it as a string, the argument will take the value of the `.Value` property and not the address of the range. So your line: `If .Range(ConditionCell).Value = "Denied" Then` will have an invalid argument for `Range` and cause your UDF to crash. And any lines that require knowledge of the address of the cells in your argument will also crash your UDF. — Ron Rosenfeld, Jun 25 '16 at 20:32

score 0 · Answer 1 · answered Jun 26 '16 at 02:00

You could always use the Worksheet_Change event to handle your updating.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Integer
    Dim ApplicationCell As Range, ConditionCells As Range
    Set ApplicationCell = Range("B4")

    Application.EnableEvents = False
    If Not Intersect(Target, ApplicationCell) Is Nothing Then
        i = 1
        Set ConditionCells = Worksheets("Application Report").Range("CE4")

        Do While ConditionCells.Value = "Denied"
            ConditionCells.Value = ConditionCells.Offset(i, 0).Value
            ApplicationCell.Value = ApplicationCell.Offset(i, 0).Value
            i = 1 + 1
        Loop

        Worksheets("???").Range("???") = ApplicationCell.Value

    End If
    Application.EnableEvents = True
End Sub

VBA #value! error in VBA function

1 Answers1