CODEWITHSUNDEEP
excelvba
Randy D
Randy D

Reputation: 203

Custom Function with cell reference inputs

I'm new to excel custom functions and trying to create a custom Excel function that outputs a "Y" or "N" and takes four arguments. The error I'm getting on the other end is #VALUE! with no further explanation. I tried looking at the Range object in the MSDN docs and can't understand why my function isn't reading the values.

My function takes four arguments and the datatypes in the cells are median: Double, base: Double, hours: Integer, and exemption: String (it takes a letter "E" or "N"). I get those arguments from four separate cells in my worksheet.

Function ToIncrease(median As Range, base As Range, hours As Range, exemption As Range)

    ToIncrease = "N"
    exempt = exemption.Value

    If exempt.Equals("N") Then
        baseSal = base.Value * hours.Value
        medianSal = median.Value * hours.Value
        If medianSal > baseSal Then
            ToIncrease = "Y"
            End If
    End If

    If exempt.Equals("E") Then
        If median.Value > base.Value Then
            ToIncrease = "Y"
        End If
    End If

End Function

I first tried exemption.Value.Equals("N") and it didn't work, and then I tried to declare the exempt variable as Dim exempt As String = exemption.Value and got an error about expecting the end of a statement.

Upvotes: 1

Views: 1804

Answers (1)

Gary's Student
Gary's Student

Reputation: 96781

First replace lines like:

If exempt.Equals("N") Then

with

If exempt = "N" Then

(there may be other problems as well)

It is MUCH easier to debug VBA code as a Sub rather than a Function() because the error messages are much better.

Upvotes: 1

Related Questions