CODEWITHSUNDEEP
excelvbafunction
user12839601
user12839601

Reputation:

Function using VBA

I want to convert this code into a function format. I've tried inputting function instead of sub version, but when I type =version into a cell nothing shows up. any help is appreciated 

Sub Version()

    If Range("B2").Value = 20.2 Then
        Range("B22").Value = 20.3

    If Range("B2").Value = 20.3 Then
        Range("B22").Value = 20.1
    End If
    End If

End Sub

Upvotes: 0

Views: 55

Answers (2)

Damian
Damian

Reputation: 5174

To use this function you need to write =Version(B2) on your cell B22:

Option Explicit
Function Version(Target As Range) As Single

    Select Case Target.Value
        Case 20.2
            Version = 20.3
        Case 20.3
            Version = 20.1
        '...
    End Select

End Function

Example:

Exmaple

Upvotes: 3

rmacey
rmacey

Reputation: 657

It's been a while since I've written VBA. Your subroutine suggests some weird, and so I suspect unintended logic. I suspect you mean that if the value is 20.2, return 20.3 and if it's 20.3, return 20.1.

As it's written, it will NEVER set B22 to 20.1 because B2 would have to be equal to both 20.2 and 20.3 because of the nested ifs.

What I offer is a function that takes a range as input (presumably cell B2) and returns a value. If the input is 20.2, you get 20.3. If it's 20.3, you get 20.1. Otherwise you get whatever was in B2. You could put the formula = customFunc(B2) in cell B22 to get the result.

Public Function customFunc(rngIn As Range)
    custFunc = rngIn.Value
    If rngIn.Value = 20.2 Then
        customFunc = 20.3
    End If
    If rngIn.Value = 20.3 Then
        customFunc = 20.1
    End If
End Function

Upvotes: 0

Related Questions