1

I am trying to write an Excel VBA function that will return one of two input parameters as the formula for the cell it is input in based on a global setting somewhere else in the sheet. IE, somewhere else in the sheet someone sets A or B, then formulas that call the functon AorB will return A or B based on what the global setting is.

Something like this:

Function AOrB(A As Variant, B As Variant) As Variant
    If someSetting = A Then
        AOrB.formula = A
    ElseIf someSetting = B Then
        AOrB.formula = B
    End If
End Function

I've scrounged pretty heavily but I just don't have the knowledge of what types to input and return in order to allow the function to return.

Any help would be appreciated.

Community
  • 1
  • 1
DPierce
  • 111
  • 3
  • 1
    You're not going to be able to return a formula that way, only a value. The value can be calculated two ways depending on your setting, but the function will just return a value. – nutsch Oct 30 '12 at 22:21
  • Unless you get really funky - http://stackoverflow.com/questions/8520732/i-dont-want-my-excel-add-in-to-return-an-array-instead-i-need-a-udf-to-change - as nutsch says this this can only return a value – brettdj Oct 30 '12 at 22:27

2 Answers2

2

Changing a formula from a UDF is not a good idea. It's possible but complicated.

I believe the proper way to do what you are explaining is via an IF in your formula:

=IF(someSetting = A, AFormula, If(someSetting = B,BFormula,""))

If someSetting is something that you are storing in code, you can make a UDF to retrieve that value. Like so:

Private mySetting As Variant
Function SomeSetting() As Variant
    SomeSetting = mySetting
End Function
Daniel
  • 12,982
  • 3
  • 36
  • 60
  • "You cannot change a cell formula from a UDF" - you can. But it's complex. See my link above – brettdj Oct 30 '12 at 22:27
  • I was trying to reduce the complexity of the sheet somewhat by developing a UDF to avoid using many IF statements. As it is, I've got hundreds of cells that all begin with IF(somesetting = A......... and wanted to simplifiy and clean up that process. It may be more trouble than it is worth, however. – DPierce Oct 31 '12 at 13:29
  • Well... I'm not sure if you would consider this simpler, but you could modify the UDF SomeSetting to retrieve all of your settings by passing a variable to it. – Daniel Oct 31 '12 at 14:01
1

Give the cell which will hold the global setting a name, like "ABChoice". (You can do this by clicking to the left of the formula bar, where the current cell location is displayed, and entering the desired name).

Then re-write the formula:

Function AOrB()
    If ThisWorkbook.Range("ABChoice") = "A" Then
        AOrB = "A"
    ElseIf ThisWorkbook.Range("ABChoice") = "B" Then
        AOrB = "B"
    Else
        AOrB = "A" 'could switch to B, whichever is the default
    End If
End Function

You could use Data Validation (under the Data menu) to ensure the user can only enter "A" or "B", but using the final Else clause makes that unnecessary, assuming you have a chosen default state.

This really is no different than just naming the global cell and referencing that in later formulas, which doesn't require VBA. I'm assuming your function is a little more complicated behind the scenes, which is requiring a UDF. If not, just use the named range.

I would actually suggest you use 1 or 2 instead of "A" or "B", because then you could use the CHOOSE function when you need to reference the global setting, which has a simpler syntax than having to use the IF function.

ExactaBox
  • 3,235
  • 16
  • 27