Changing the Function Based on Inputs

[Whylucky]

I am stuck on a couple of problems but they generally boil down to 1 thing. Is it possible to have VBA change the function based on the inputs it receives? So if i have a " - " somewhere in my inputs it knows that it needs to run a different calculation? or even simply display the " - " in the answer box? Any help would be much appreciated. Thank you in advance.

 

[Andrew Poulsom]

Welcome to MrExcel.

Please post your function.

 

[fibonacci1101]

Acctualy you can achieve it using simple formula:

=IF(IFERROR(SEARCH(" - ",B2),0)," - ",RANDBETWEEN(0,9))

Where you have your values in cell B2. If the value in B2 contains " - " you will get " - " and if it does not then you will get random number between 0 and 9. Of course you can replace randbetween function with any other function. If you can do it using formulas than for sure you can do it using VBA

 

[Whylucky]

Function Bearing(od As Double, odt As Double, id As Double, idt As Double, yield As Double) As Double


    If od = "-" Then
        Bearing = "-"
        
    Else
        O = od - odt
        i = id + idt
        area = pi / 4 * (O ^ 2 - i ^ 2)
        Bearing = yield * area
    End If
    
End Function

 

[Andrew Poulsom]

What do you actually have in the cells that you are passing to the function? Not how they appear in the cell but the value that appears in the Formula Bar.

 

[Whylucky]

They are physical numbers, unless it isnt a bearing in which case it has a - mark. so the function calculates it unless it has a - then it gives me error, when i want it to just reply -

 

[Andrew Poulsom]

Try:

Function Bearing(od As Variant, odt As Double, id As Double, idt As Double, yield As Double) As Variant
    If od = "-" Then
        Bearing = "-"
    Else
        O = od - odt
        i = id + idt
        area = Pi / 4 * (O ^ 2 - i ^ 2)
        Bearing = yield * area
    End If
End Function

 

[Whylucky]

in the cell it still gives the answer as "#VALUE!"

 

[Whylucky]

fibanocci1101 your idea worked excluding spaces around the "-", i would still like to get it working in the VBA code though so it looks cleaner

 

[Andrew Poulsom]

Does it work with this line?

If Instr(od, "-") > 0 Then