VBA Function

[ziad alsayed]

dear all

i am using the below function and it is working, but if i use in a cell that is formatted is percentage (60%, 80%....) it will not work.

appreciate any assistance

Function GetRemarks(rng As Range) As String
Dim Appraisal As Byte
Appraisal = rng.Value
Select Case Appraisal
    Case 60 To 79
    GetRemarks = "Meet Expectation"
    Case Is > 80
    GetRemarks = "Above Expectation"
    Case Is < 60
    GetRemarks = "Below Expectation"
End Select
End Function

 

[shg]

Try this:

Function GetRemarks(v As Variant) As String
    Select Case v
        Case Is >= 0.8
            GetRemarks = "Above Expectation"
        Case Is >= 0.6
            GetRemarks = "Meet Expectation"
        Case Else
            GetRemarks = "Below Expectation"
    End Select
End Function

 

[shg]

Or ...

Function GetRemarks(v As Variant) As String
    GetRemarks = WorksheetFunction.Lookup(v, Array(0, 0.6, 0.8), _
                                          Array("Below", "Meet", "Above")) & _
                                          " Expectation"
End Function

 

[ziad alsayed]

thanks Shg it is working, but if i am using as per below it is not, can you please explain why it is not working, it is only giving meet expectation

Function GetRemarks(rng As Range) As String
Dim Appraisal As Byte
Appraisal = rng.Value
Select Case Appraisal
    Case 0.6 To 0.79
    GetRemarks = "Meet Expectation"
    Case Is > 0.8
    GetRemarks = "Above Expectation"
    Case Is < 0.6
    GetRemarks = "Below Expectation"
End Select
End Function

 

[shg]

Type Byte stores unsigned integers 0 to 255, not decimal values.

 

[ziad alsayed]

thanks again , i like the second code, i hope one day i will able to write such code

i really appreciate your assistance.

 

[p45cal]

if i use in a cell that is formatted is percentage (60%, 80%....) it will not work.

20% is really 0.2 (format the cell as General and you'll see)
Compounded by using the Byte data type which will change values below 1 to 0. Use Double or Single instead?
Also what if your value is 0.8 (80%)? It's not catered for. Try

Function GetRemarks(rng As Range) As String
Dim Appraisal As Double
Appraisal = rng.Value
Select Case Appraisal
  Case Is < 0.6
    GetRemarks = "Below Expectation"
  Case Is < 0.8
    GetRemarks = "Meet Expectation"
  Case Else
    GetRemarks = "Above Expectation"
End Select
End Function

 

[ziad alsayed]

thanks Shg, after your remarks i check excel help and dim the variable as variant

At this time the Decimal data type can only be used within a Variant, that is, you cannot declare a variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function

.

 

[ziad alsayed]

dear P45cal

Long time, hope you are fine and doing well.

i tried it as double or single and it works also.

thanks for drawing my attention to the 80%, you are right , i changed the condition

thanks

 

[shg]

You don't need a variable at all ...